MySQL的DDL

添加列，修改列，表名等等。

mysql> create table sss(id int , name varchar(255),sex enum("m","w"),age int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into sss values (1000,"sam","m",19);
Query OK, 1 row affected (0.00 sec)

mysql> insert into sss values (1001,"kitty","w",20);
Query OK, 1 row affected (0.00 sec)

mysql> select * from sss;
+------+-------+------+------+
| id   | name  | sex  | age  |
+------+-------+------+------+
| 1000 | sam   | m    |   19 |
| 1001 | kitty | w    |   20 |
+------+-------+------+------+
2 rows in set (0.00 sec)

创建一个表，然后添加几个数据。

alter table 表名 rename 修改后的名

修改表名

mysql> alter table sss rename xuesheng;

mysql> select * from xuesheng;
+------+-------+------+------+
| id   | name  | sex  | age  |
+------+-------+------+------+
| 1000 | sam   | m    |   19 |
| 1001 | kitty | w    |   20 |
+------+-------+------+------+
2 rows in set (0.00 sec)
//表示是上边的表

添加表的列

要指定数据类型

mysql> alter table xuesheng add column likefoods varchar(255);

mysql> select * from xuesheng ;
+------+-------+------+------+-----------+
| id   | name  | sex  | age  | likefoods |
+------+-------+------+------+-----------+
| 1000 | sam   | m    |   19 | NULL      |
| 1001 | kitty | w    |   20 | NULL      |
+------+-------+------+------+-----------+
2 rows in set (0.00 sec)

因为是新添加的，列为空。

删除列

mysql> alter table xuesheng drop likefoods;

mysql> alter table xuesheng drop column likefoods;
//下面这个是规范的写法。

mysql> select * from xuesheng;
+------+-------+------+------+
| id   | name  | sex  | age  |
+------+-------+------+------+
| 1000 | sam   | m    |   19 |
| 1001 | kitty | w    |   20 |
+------+-------+------+------+
2 rows in set (0.00 sec)

修改列的数据类型：

mysql> desc xuesheng ;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id        | int(11)       | YES  |     | NULL    |       |
| name      | varchar(255)  | YES  |     | NULL    |       |
| sex       | enum('m','w') | YES  |     | NULL    |       |
| age       | int(11)       | YES  |     | NULL    |       |
| likefoods | varchar(255)  | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)


mysql> alter table xuesheng modify likefoods char(10);
//修改数据类型
或者这么写：alter table xuesheng change likefoods likefoods  char(10);  

mysql> desc xuesheng;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id        | int(11)       | YES  |     | NULL    |       |
| name      | varchar(255)  | YES  |     | NULL    |       |
| sex       | enum('m','w') | YES  |     | NULL    |       |
| age       | int(11)       | YES  |     | NULL    |       |
| likefoods | char(10)      | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改列名

mysql> alter table xuesheng change column likefoods lfoods varchar(10);


mysql> desc xyesheng;
ERROR 1146 (42S02): Table 'db1.xyesheng' doesn't exist
mysql> desc xuesheng;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | int(11)       | YES  |     | NULL    |       |
| name   | varchar(255)  | YES  |     | NULL    |       |
| sex    | enum('m','w') | YES  |     | NULL    |       |
| age    | int(11)       | YES  |     | NULL    |       |
| lfoods | varchar(10)   | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)