MySQL: DDL ( Data Definition Language) for Column Management

wcdz

　　1. Create table
　　create table table_name(
　　column_name column_type [, column_attribute] [, column_default_value],  --> Column Declaration
　　column_name column_type [, column_attribute] [, column_default_value],  --> Column Declaration
　　......
　　)[charset=utf8/gbk];
　　Eg:

create table user(
user_id int primary key auto_increment,
user_name varchar(20) not null default '',
user_gender varchar(20) not null default 'male'
);
desc user;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| user_id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| user_name   | varchar(20) | NO   |     |         |                |
| user_gender | varchar(20) | NO   |     | male    |                |
+-------------+-------------+------+-----+---------+----------------+
　　
　　2. Alter table --->( Insert Column & Drop Column & Update Column & Retrieve Column)
　　1) Insert Column: alter table table_name add Column Declaration;  ---> The new column is added as the last column of the table by default. We can use after column_name to set its position manually.
　　---> What if the new added column should be the first column? alter table table_name add Column Declaration first
　　2) Drop Column:  alter table table_name drop [column] column_name;
　　3) Update Column: alter table table_name change target_column_name new_column_declaration;
　　4) Retrieve Column: desc table_name;
　　Eg:

#1. Insert Column
alter table user add user_age int not null default 0;
desc user;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| user_id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| user_name   | varchar(20) | NO   |     |         |                |
| user_gender | varchar(20) | NO   |     | male    |                |
| user_age    | int(11)     | NO   |     | 0       |                |
+-------------+-------------+------+-----+---------+----------------+
#2. Drop Column
alter table user drop column user_age;
desc user;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| user_id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| user_name   | varchar(20) | NO   |     |         |                |
| user_gender | varchar(20) | NO   |     | male    |                |
+-------------+-------------+------+-----+---------+----------------+
alter table user drop user_gender;
desc user;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| user_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| user_name | varchar(20) | NO   |     |         |                |
+-----------+-------------+------+-----+---------+----------------+
#3. Update Column
alter table user change user_gender user_gender varchar(30) not null default 'female';
desc user;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| user_id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| user_name   | varchar(20) | NO   |     |         |                |
| user_gender | varchar(30) | NO   |     | female  |                |
+-------------+-------------+------+-----+---------+----------------+
#4. Retrieve Column
desc user;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| user_id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| user_name   | varchar(20) | NO   |     |         |                |
| user_gender | varchar(30) | NO   |     | female  |                |
+-------------+-------------+------+-----+---------+----------------+
　　Comments:
　　1) Know what is Column Declaration! And Column Declaration is very import in column DDL.
　　2) Know what is DDL and DML.
　　3) Know how to manage column using DDL.