Modifying Tables

Modifying Column

Modify the data type of a column:

ALTER TABLE tb1C MODIFY name VARCHAR(100);

Add a new column to the end:

ALTER TABLE tb1C ADD birth DATETIME;

Add a new column to the beginning:

ALTER TABLE tb1C ADD birth DATETIME FIRST;

Add a new column to a specific location:

ALTER TABLE tb1C ADD birth DATETIME AFTER empid;

Modify the ordering of columns:

ALTER TABLE tb1C MODIFY birth DATETIME FIRST;

Change the name and date type of a column:

ALTER TABLE tb1C CHANGE birth birthday DATE;

Drop a column:

ALTER TABLE tb1C DROP birthday;

Primary Key and Unique Key

Set primary key (no duplication + no NULL):

CREATE TABLE t_pk (a INT PRIMARY KEY, b VARCHAR(10));

Set unique key (no duplication):

CREATE TABLE t_uniq (a INT UNIQUE, b VARCHAR(10));

Auto-Increment and Default Value

Set auto-increment:

CREATE TABLE t_series (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10));

Reset auto-increment index (deletion won't set the counter to 1):

ALTER TABLE t_series AUTO_INCREMENT=1;

Set default value for a column:

ALTER TABLE tb1G MODIFY name VARCHAR(10) DEFAULT 'nobody';

Index

Create index:

CREATE INDEX my_ind ON tb1G (empid);

Show index (in a nice format):

SHOW INDEX FROM tb1G\G

Drop index:

DROP INDEX my_ind ON tb1G;