【MySQL】3、表结构的修改

5. 表结构的修改

5.1 查看表结构

5.1.1 查看表简单结构

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| sid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20) | YES  |     | NULL    |                |
| sex   | char(2)     | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
| addr  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

5.1.2 查看表详细结构

mysql> show create table student\G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(20) DEFAULT NULL,
  `sex` char(2) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `addr` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

5.2 在表结构的最后新增一个字段

mysql> alter table student add cid int;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| sid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20) | YES  |     | NULL    |                |
| sex   | char(2)     | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
| addr  | varchar(20) | YES  |     | NULL    |                |
| cid   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

5.3 在表结构指定位置之后新增一个字段

mysql> alter table student add cid int after sex;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| sid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20) | YES  |     | NULL    |                |
| sex   | char(2)     | YES  |     | NULL    |                |
| cid   | int(11)     | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
| addr  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

5.4 在表结构头部新增一个字段

mysql> alter table student add cid int first;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| cid   | int(11)     | YES  |     | NULL    |                |
| sid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20) | YES  |     | NULL    |                |
| sex   | char(2)     | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
| addr  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

5.5 删除字段

mysql> alter table student drop cid;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| sid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20) | YES  |     | NULL    |                |
| sex   | char(2)     | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
| addr  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

5.6 修改字段类型

mysql> alter table student modify cid varchar(20);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| sid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20) | YES  |     | NULL    |                |
| sex   | char(2)     | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
| addr  | varchar(20) | YES  |     | NULL    |                |
| cid   | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

5.7 修改字段类型及名字

mysql> alter table student change cid cids int;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| sid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20) | YES  |     | NULL    |                |
| sex   | char(2)     | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
| addr  | varchar(20) | YES  |     | NULL    |                |
| cids  | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

5.8 修改默认值约束

mysql> alter table student alter addr set default '深圳';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into student(sname,sex,age,cid) value('王二麻子','男',20,1);
Query OK, 1 row affected (0.00 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| sid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20) | YES  |     | NULL    |                |
| sex   | char(2)     | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
| addr  | varchar(20) | YES  |     | 深圳    |                |
| cids  | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> select * from student;
+-----+----------+------+------+------+------+
| sid | sname    | sex  | age  | addr | cid  |
+-----+----------+------+------+------+------+
|   1 | 张三     | 男   |   20 | 上海 | NULL |
|   2 | 李四     | 男   |   21 | 深圳 | NULL |
|   3 | 王二麻子 | 男   |   20 | 深圳 |    1 |
+-----+----------+------+------+------+------+

5.9 删除主键约束

5.9.1 删除自动增长

mysql> alter table student modify sid int;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid   | int(11)     | NO   | PRI | NULL    |       |
| sname | varchar(20) | YES  |     | NULL    |       |
| sex   | char(2)     | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| addr  | varchar(20) | YES  |     | 深圳    |       |
| cid   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

5.9.2 删除主键定义

mysql> alter table student drop primary key;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid   | int(11)     | NO   |     | NULL    |       |
| sname | varchar(20) | YES  |     | NULL    |       |
| sex   | char(2)     | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| addr  | varchar(20) | YES  |     | 深圳    |       |
| cid   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

5.10 添加主键约束自动增长列

mysql> alter table student modify sid int primary key auto_incre
ment;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| sid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20) | YES  |     | NULL    |                |
| sex   | char(2)     | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
| addr  | varchar(20) | YES  |     | 深圳    |                |
| cid   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)