MySQL: DDL ( Data Definition Language) for Column Management

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.