首先我们创建一个测试的用户表
1
2
3
4
5
6
7
8
9
10
11
12
| CREATE TABLE IF NOT EXISTS `user`(
id SMALLINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT '123123123@qq.com',
age TINYINT UNSIGNED DEFAULT 18,
sex ENUM('男','女','保密') DEFAULT '保密',
addr VARCHAR(200) NOT NULL DEFAULT '北京',
salary FLOAT(6,2),
regTime INT UNSIGNED,
face CHAR(100) NOT NULL DEFAULT 'default.jpg'
)ENGINE=InnoDB CHARSET=UTF8;
|
然后我们查看以下表结构：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| mysql> DESC user;
+
| Field | Type | Null | Key | Default | Extra |
+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| email | varchar(50) | NO | | 123123123@qq.com | |
| age | tinyint(3) unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| addr | varchar(200) | NO | | 北京 | |
| salary | float(6,2) | YES | | NULL | |
| regTime | int(10) unsigned | YES | | NULL | |
| face | char(100) | NO | | default.jpg | |
+
10 rows in set (0.02 sec)
|
可以看到此时已经按照我们的需求创建了表结构，如果此时我们想换一个表名呢？
1、修改表名
1
2
| 1、ALTER TABLE tbl_name RENAME [TO|AS] new_name;
2、RENAME TABLE tbl_name TO new_name;
|
第一个命令的TO和AS是可以省略的，第二个命令不可以，测试一下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
| mysql> ALTER TABLE `user` RENAME TO `user2`;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW TABLES;
+
| Tables_in_demo1 |
+
| cms_cate |
| cms_news |
| user |
+
4 rows in set (0.00 sec)
mysql> RENAME TABLE `user2` TO `user`;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW TABLES;
+
| Tables_in_demo1 |
+
| cms_cate |
| cms_news |
| user |
+
4 rows in set (0.00 sec)
|
2、添加字段
1
| ALTER TABLE tbl_name ADD 字段名称 字段类型 [完整性约束条件] [FIRST|AFTER 字段名称]
|
我们尝试添加一个 card 字段：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| mysql> ALTER TABLE `user` ADD card CHAR(18);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user;
+
| Field | Type | Null | Key | Default | Extra |
+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| email | varchar(50) | NO | | 123123123@qq.com | |
| age | tinyint(3) unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| addr | varchar(200) | NO | | 北京 | |
| salary | float(6,2) | YES | | NULL | |
| regTime | int(10) unsigned | YES | | NULL | |
| face | char(100) | NO | | default.jpg | |
| card | char(18) | YES | | NULL | |
+
11 rows in set (0.00 sec)
|
可以看到默认添加到了最后一个字段，当然也可以指定位置:
1
| ALTER TABLE `user` ADD test CHAR(18) NOT NULL FIRST;
|
批量添加多个字段：
1
2
3
4
| ALTER TABLE `user`
ADD test1 CHAR(50) NOT NULL AFTER password,
ADD test2 FLOAT(6,2) FIRST,
ADD test3 SET('A','B','C');
|
3、删除字段
1
| ALTER TABLE tbl_name DROP 字段名称
|
我们尝试删除test2字段
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
| mysql> ALTER TABLE user DROP test1;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user;
+
| Field | Type | Null | Key | Default | Extra |
+
| test2 | float(6,2) | YES | | NULL | |
| test | char(18) | NO | | NULL | |
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| email | varchar(50) | NO | | 123123123@qq.com | |
| age | tinyint(3) unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| addr | varchar(200) | NO | | 北京 | |
| salary | float(6,2) | YES | | NULL | |
| regTime | int(10) unsigned | YES | | NULL | |
| face | char(100) | NO | | default.jpg | |
| card | char(18) | YES | | NULL | |
| test3 | set('A','B','C') | YES | | NULL | |
+
14 rows in set (0.00 sec)
|
可以看到在password之后的test1字段已经被删除，批量删除字段：
1
2
3
| ALTER TABLE user
DROP test2,
DROP test;
|
我们继续一步添加test字段并删除test3字段：
1
2
3
| ALTER TABLE user
ADD test INT,
DROP test3;
|
4、修改字段
1
| ALTER TABLE tbl_name MODIFY 字段名称 字段类型 [完整性约束条件] [FIRST|AFTER 字段名称]
|
首先看一下user的表结构：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| mysql> DESC user;
+
| Field | Type | Null | Key | Default | Extra |
+
| id | smallint(6) | YES | | NULL | |
| username | varchar(20) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| sex | enum('男','女','保密') | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
| ADDR | varchar(200) | YES | | NULL | |
| birth | year(4) | YES | | NULL | |
| salary | float(8,2) | YES | | NULL | |
| tel | int(11) | YES | | NULL | |
| married | tinyint(1) | YES | | NULL | |
+
10 rows in set (0.01 sec)
|
我们将email字段改为 200 长度：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| mysql> ALTER TABLE user MODIFY email VARCHAR(200);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user;
+
| Field | Type | Null | Key | Default | Extra |
+
| id | smallint(6) | YES | | NULL | |
| username | varchar(20) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| sex | enum('男','女','保密') | YES | | NULL | |
| email | varchar(200) | YES | | NULL | |
| ADDR | varchar(200) | YES | | NULL | |
| birth | year(4) | YES | | NULL | |
| salary | float(8,2) | YES | | NULL | |
| tel | int(11) | YES | | NULL | |
| married | tinyint(1) | YES | | NULL | |
+
10 rows in set (0.00 sec)
|
5、修改字段名称
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
| ALTER TABLE tbl_name CHANGE 旧字段名称 新字段名称 字段类型 [完整性约束条件] [FIRST|AFTER 字段名称]
```
我们将`ADDR`字段改为`addr`:
```SQL
mysql> ALTER TABLE user CHANGE ADDR addr VARCHAR(200);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user;
+
| Field | Type | Null | Key | Default | Extra |
+
| id | smallint(6) | YES | | NULL | |
| username | varchar(20) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| sex | enum('男','女','保密') | YES | | NULL | |
| email | varchar(200) | YES | | NULL | |
| addr | varchar(200) | YES | | NULL | |
| birth | year(4) | YES | | NULL | |
| salary | float(8,2) | YES | | NULL | |
| tel | int(11) | YES | | NULL | |
| married | tinyint(1) | YES | | NULL | |
+
10 rows in set (0.00 sec)
|
6、添加默认值
1
| ALTER TABLE tbl_name ALTER 字段名称 SET DEFAULT 默认值
|
我们将性别sex的默认值设置为保密：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| mysql> ALTER TABLE user ALTER sex SET DEFAULT '保密';
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user;
+
| Field | Type | Null | Key | Default | Extra |
+
| id | smallint(6) | YES | | NULL | |
| username | varchar(20) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| email | varchar(200) | YES | | NULL | |
| addr | varchar(200) | YES | | NULL | |
| birth | year(4) | YES | | NULL | |
| salary | float(8,2) | YES | | NULL | |
| tel | int(11) | YES | | NULL | |
| married | tinyint(1) | YES | | NULL | |
+
10 rows in set (0.00 sec)
|
7、删除默认值
1
| ALTER TABLE tbl_name ALTER 字段名称 DROP DEFAULT
|
我们将性别sex的默认值删除掉：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| mysql> ALTER TABLE user ALTER sex DROP DEFAULT;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user;
+
| Field | Type | Null | Key | Default | Extra |
+
| id | smallint(6) | YES | | NULL | |
| username | varchar(20) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| sex | enum('男','女','保密') | YES | | NULL | |
| email | varchar(200) | YES | | NULL | |
| addr | varchar(200) | YES | | NULL | |
| birth | year(4) | YES | | NULL | |
| salary | float(8,2) | YES | | NULL | |
| tel | int(11) | YES | | NULL | |
| married | tinyint(1) | YES | | NULL | |
+
10 rows in set (0.00 sec)
|
8、添加主键
1
| ALTER TABLE tbl_name ADD [constraint [symbol]] PRIMARY KEY [index_type] (字段名称,...)
|
我们将id添加为主键
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| mysql> ALTER TABLE user ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user;
+
| Field | Type | Null | Key | Default | Extra |
+
| id | smallint(6) | NO | PRI | NULL | |
| username | varchar(20) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| sex | enum('男','女','保密') | YES | | NULL | |
| email | varchar(200) | YES | | NULL | |
| addr | varchar(200) | YES | | NULL | |
| birth | year(4) | YES | | NULL | |
| salary | float(8,2) | YES | | NULL | |
| tel | int(11) | YES | | NULL | |
| married | tinyint(1) | YES | | NULL | |
+
10 rows in set (0.00 sec)
|
9、删除主键
1
| ALTER TABLE tbl_name DROP PRIMARY KEY
|
同样的我们删除主键id测试一下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| mysql> ALTER TABLE user DROP PRIMARY KEY;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+
| Field | Type | Null | Key | Default | Extra |
+
| id | smallint(6) | NO | | NULL | |
| username | varchar(20) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| sex | enum('男','女','保密') | YES | | NULL | |
| email | varchar(200) | YES | | NULL | |
| addr | varchar(200) | YES | | NULL | |
| birth | year(4) | YES | | NULL | |
| salary | float(8,2) | YES | | NULL | |
| tel | int(11) | YES | | NULL | |
| married | tinyint(1) | YES | | NULL | |
+
10 rows in set (0.00 sec)
|
注：当表中的某个字段为自增长切是主键的时候，如果要删除主键，必须先去掉自增长才可以。
10、添加唯一
1
| ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KET] [索引名称] (字段名称,...)
|
还以tel为例：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| mysql> ALTER TABLE user ADD UNIQUE (tel);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user;
+
| Field | Type | Null | Key | Default | Extra |
+
| id | smallint(6) | NO | | NULL | |
| username | varchar(20) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| sex | enum('男','女','保密') | YES | | NULL | |
| email | varchar(200) | YES | | NULL | |
| addr | varchar(200) | YES | | NULL | |
| birth | year(4) | YES | | NULL | |
| salary | float(8,2) | YES | | NULL | |
| tel | int(11) | YES | UNI | NULL | |
| married | tinyint(1) | YES | | NULL | |
+
10 rows in set (0.00 sec)
|
注：当添加唯一的时候不指定索引名称时，系统默认字段名为索引名称
11、删除唯一
1
| ALTER TABLE tbl_name DROP {INDEX|KET} index_name
|
我们删除tel唯一做测试：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| mysql> ALTER TABLE user DROP INDEX tel;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user;
+
| Field | Type | Null | Key | Default | Extra |
+
| id | smallint(6) | NO | | NULL | |
| username | varchar(20) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| sex | enum('男','女','保密') | YES | | NULL | |
| email | varchar(200) | YES | | NULL | |
| addr | varchar(200) | YES | | NULL | |
| birth | year(4) | YES | | NULL | |
| salary | float(8,2) | YES | | NULL | |
| tel | int(11) | YES | | NULL | |
| married | tinyint(1) | YES | | NULL | |
+
10 rows in set (0.00 sec)
|
12、修改表的存储引擎
1
| ALTER TABLE tbl_name ENGINE=存储引擎名称
|
首先我们看一下创建表是的存储引擎：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| mysql> SHOW CREATE TABLE user;
+
| Table | Create Table |
+
| user | CREATE TABLE `user` (
`id` smallint(6) NOT NULL,
`username` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`sex` enum('男','女','保密'),
`email` varchar(200) DEFAULT NULL,
`addr` varchar(200) DEFAULT NULL,
`birth` year(4) DEFAULT NULL,
`salary` float(8,2) DEFAULT NULL,
`tel` int(11) DEFAULT NULL,
`married` tinyint(1) DEFAULT NULL COMMENT '0代表未结婚，非0代表已婚'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+
1 row in set (0.00 sec)
|
可以看到此时的存储引擎为InnoDB,我们将它改为MyISAM后再查看：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
| mysql> ALTER TABLE user ENGINE= MyISAM;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE user;
+
| Table | Create Table |
+
| user | CREATE TABLE `user` (
`id` smallint(6) NOT NULL,
`username` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`sex` enum('男','女','保密'),
`email` varchar(200) DEFAULT NULL,
`addr` varchar(200) DEFAULT NULL,
`birth` year(4) DEFAULT NULL,
`salary` float(8,2) DEFAULT NULL,
`tel` int(11) DEFAULT NULL,
`married` tinyint(1) DEFAULT NULL COMMENT '0代表未结婚，非0代表已婚'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+
1 row in set (0.00 sec)
|
13、设置自增长的值
1
| ALTER TABLE tbl_name AUTO_INCREMENT=值
|
首先我们表中必须有一个自增长值，然后再修改，我们将id设为自增长，然后从100开始：
mysql> ALTER TABLE user MODIFY id SMALLINT UNSIGNED KEY AUTO_INCREMENT;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE user AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE user;
+
| Table | Create Table |
+
| user | CREATE TABLE `user` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`sex` enum('男','女','保密'),
`email` varchar(200) DEFAULT NULL,
`addr` varchar(200) DEFAULT NULL,
`birth` year(4) DEFAULT NULL,
`salary` float(8,2) DEFAULT NULL,
`tel` int(11) DEFAULT NULL,
`married` tinyint(1) DEFAULT NULL COMMENT '0代表未结婚，非0代表已婚',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 |
+
1 row in set (0.00 sec)
我们可以看到自增值已经修改AUTO_INCREMENT=100。