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)