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.