Mysql 表操作

# 创建 t_student 表
mysql> create table t_student(id int(3) primary key comment‘学生id‘, name varchar(30) comment‘学生姓名‘)engine=INNODB default CHARSET=utf8 comment‘学生表‘；

# 查看验证 t_student 表创建成功
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| t_student         |
+-------------------+

mysql> desc t_student;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(3)      | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

# 修改 t_student 表名称为 t_stu
mysql> alter table t_student rename t_stu;

# 查看验证修改成功
mysql> show tables;

+-------------------+
| Tables_in_student |
+-------------------+
| t_stu             |
+-------------------+

# 修改 t_stu 表字段 id 为 uid
mysql> alter table t_stu change id uid int(3) comment‘用户id‘;

# 查看验证修改成功
mysql> desc t_stu;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| uid   | int(3)      | NO   | PRI | 0       |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

# 修改主键 uid 设为自增长
mysql> alter table t_stu change uid uid int(3) auto_increment;

# 查看验证修改成功
mysql> desc t_stu;

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(3)      | YES  |     | NULL    |                |
| uid   | int(3)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30) | YES  |     | NULL    |                |
| age   | int(3)      | YES  |     | NULL    |                |
| sex   | varchar(10) | YES  |     | NULL    |                |
| phone | varchar(11) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

# 在 t_stu 第一个字段前添加表字段 id
mysql> alter table t_stu add id int(3) first;

# 查看验证添加成功
mysql> desc t_stu;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(3)      | YES  |     | NULL    |       |
| uid   | int(3)      | NO   | PRI | 0       |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

# 在 t_stu 表中 name 字段后添加 age 字段
mysql> alter table t_stu add age int(3) after name;

# 查看验证添加成功
mysql> desc t_stu;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(3)      | YES  |     | NULL    |       |
| uid   | int(3)      | NO   | PRI | 0       |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int(3)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

# 在表 t_stu 中同时新增 sex 和 phone 两个字段
mysql> alter table t_stu add(sex varchar(10),phone varchar(11));

# 查看验证新增成功
mysql> desc t_stu;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(3)      | YES  |     | NULL    |       |
| uid   | int(3)      | NO   | PRI | 0       |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int(3)      | YES  |     | NULL    |       |
| sex   | varchar(10) | YES  |     | NULL    |       |
| phone | varchar(11) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

# 删除表中 uid 字段 
mysql> alter table t_stu drop uid;

# 验证删除成功
mysql> desc t_stu;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(3)      | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int(3)      | YES  |     | NULL    |       |
| sex   | varchar(10) | YES  |     | NULL    |       |
| phone | varchar(11) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

# 同时删除 sex 和 phone 两个字段
mysql> alter table t_stu drop sex,drop phone;

# 验证删除成功
mysql> desc t_stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(3)      | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int(3)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

drop table t_stu;