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;
