15、MySQL-修改数据表

来源：互联网发布：用java编写的大型游戏编辑：程序博客网时间：2024/05/20 18:55

当数据表结构需要改变时就需要修改数据表

结构

ALTER TABLE 语句

功能：

添加、删除字段
修改字段类型、位置、名称
添加、删除主键约束
添加、删除默认值
添加、删除唯一
修改表名

1、添加字段（ADD）

ALTER TABLE tbl_name ADD 字段名称字段类型约束条件 [FIRST|AFTER 字段名称];

mysql> CREATE TABLE testAlert(
-> id TINYINT
-> );

mysql> ALTER TABLE testAlert ADD username VARCHAR(20) NOT NULL;
mysql> -- 将字段添加到表字段的第一个位置上
mysql> ALTER TABLE testAlert ADD uuid VARCHAR(20) NOT NULL FIRST;
mysql> -- 将字段添加在id之后
mysql> ALTER TABLE testAlert ADD age TINYINT UNSIGNED AFTER id;

mysql> -- 一次添加多个字段
mysql> ALTER TABLE testAlert ADD test1 VARCHAR(20) NOT NULL,
-> ADD test2 DOUBLE,
-> ADD test3 INT;

mysql> DESC testAlert;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| uuid     | varchar(20)         | NO   |     | NULL    |       |
| id       | tinyint(4)          | YES |     | NULL    |       |
| age      | tinyint(3) unsigned | YES |     | NULL    |       |
| username | varchar(20)         | NO   |     | NULL    |       |
| test1    | varchar(20)         | NO   |     | NULL    |       |
| test2    | double              | YES |     | NULL    |       |
| test3    | int(11)             | YES |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
7 rows in set (0.02 sec)

2、删除指定字段（DROP）

ALTER TABLE tbl_name DROP [COLUMN] 字段名称;

mysql> -- 将test1字段删除
mysql> ALTER TABLE testAlert DROP COLUMN test1;
mysql> -- 将test2字段删除
mysql> ALTER TABLE testAlert DROP COLUMN test2;
mysql> -- 同时删除多个字段
mysql> ALTER TABLE testAlert DROP test3,
    -> DROP uuid,
    -> Add test4 INT;

mysql> DESC testAlert;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| id       | tinyint(4)          | YES |     | NULL    |       |
| age      | tinyint(3) unsigned | YES |     | NULL    |       |
| username | varchar(20)         | NO   |     | NULL    |       |
| test4    | int(11)             | YES |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+

3、修改字段定义（MODIFY）

ALTER TABLE tbl_name MODIFY [COLUMN] 字段名称字段类型约束条件 [FIRST|AFTER 字段名称];

mysql> -- 修改字段test4的列定义
mysql> ALTER TABLE testAlert MODIFY test4 VARCHAR(20) NOT NULL DEFAULT 'HAHA!' AFTER age;
mysql> DESC testAlert;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| id       | tinyint(4)          | YES |     | NULL    |       |
| age      | tinyint(3) unsigned | YES |     | NULL    |       |
| test4    | varchar(20)         | NO   |     | HAHA!   |       |
| username | varchar(20)         | NO   |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

4、修改字段名称（CHANGE）

ALTER TABLE tbl_name CHANGE 原字段名称新字段名称字段类型约束条件 [FIRST|AFTER 字段名称];

mysql> -- 修改字段test4的名称
mysql> ALTER TABLE testAlert CHANGE test4 test2 VARCHAR(20);
mysql> -- 修改字段test2的名称并修改数据类型
mysql> ALTER TABLE testAlert CHANGE test2 test1 INT;
mysql> -- 修改字段id的属性
mysql> ALTER TABLE testAlert CHANGE id id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY;
mysql> DESC testAlert;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| age      | tinyint(3) unsigned | YES |     | NULL    |                |
| test1    | int(11)             | YES |     | NULL    |                |
| username | varchar(20)         | NO   |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+

5、添加主键（ADD PRIMARY KEY）

ALTER TABLE tbl_name ADD PRIMARY KEY (字段名称);

mysql> -- 为表添加主键

mysql> ALTER TABLE testAlert ADD PRIMARY KEY(id);
mysql> DESC testAlert;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | tinyint(4) | NO | PRI | 0 | |
+-------+------------+------+-----+---------+-------+

6、删除主键（DROP PRIMARY KEY）

ALTER TABLE tbl_name DROP PRIMARY KEY;

注意：需要先将AUTO_INCREMENT去掉才能删除主键

ALTER TABLE tbl_name MODIFY 主键字段名主键字段属性;

mysql>   ALTER TABLE testAlert DROP PRIMARY KEY;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> DESC testAlert;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | tinyint(4) | NO   |     | 0       |       |
+-------+------------+------+-----+---------+-------+

7、添加唯一约束(ADD UNIQUE（字段名称...）)

ALTER TABLE tbl_name ADD UNIQUE [KEY|INDEX] [index_name] (字段名称)

mysql> -- 将字段添加成唯一
mysql> ALTER TABLE testAlert ADD UNIQUE (test1);
mysql> -- 将字段添加成唯一索引
mysql> ALTER TABLE testAlert ADD UNIQUE username(test2);

mysql> DESC testAlert;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | tinyint(4) | NO   |     | 0       |       |
| test1 | varchar(20) | NO   | PRI | NULL    |       | -- 看到唯一约束已经添加
| test2 | double      | YES | UNI | NULL    |       |
| test3 | int(11)     | YES |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
mysql> SHOW CREATE TABLE testAlert;
| testAlert | CREATE TABLE `testalert` (
`id` tinyint(4) NOT NULL DEFAULT '0',
`test1` varchar(20) NOT NULL,
`test2` double DEFAULT NULL,
`test3` int(11) DEFAULT NULL,
UNIQUE KEY `test1` (`test1`),
UNIQUE KEY `username` (`test2`) -- 看到索引名称以修改
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

1 row in set (0.00 sec)

8、删除唯一约束(DROP INDEX index_name )

ALTER TABLE tbl_name DROP INDEX index_name;

mysql> -- 删除索引
mysql> ALTER TABLE testAlert DROP INDEX test1;
mysql> DESC testAlert;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | tinyint(4) | NO   |     | 0       |       |
| test1 | varchar(20) | NO   |     | NULL    |       |
| test2 | double      | YES | UNI | NULL    |       |
| test3 | int(11)     | YES |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

9、添加默认约束

ALTER TABLE tbl_name ALTER 字段名称 SET DEFAULT 值;

mysql> ALTER TABLE testAlert ALTER test1 SET DEFAULT 'WAHAHA！';
mysql> DESC testAlert;
+-------+-------------+------+-----+----------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+----------+-------+
| id    | tinyint(4) | NO   |     | 0        |       |
| test1 | varchar(20) | NO   |     | WAHAHA！ |       |
| test2 | double      | YES | UNI | NULL     |       |
| test3 | int(11)     | YES |     | NULL     |       |
+-------+-------------+------+-----+----------+-------+

10、删除默认约束

ALTER TABLE tbl_name ALTER 字段名称 DROP DEFAULT 值;

mysql> ALTER TABLE testAlert ALTER test1 DROP DEFAULT;
mysql> DESC testAlert;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | tinyint(4) | NO   |     | 0       |       |
| test1 | varchar(20) | NO   |     | NULL    |       |
| test2 | double      | YES | UNI | NULL    |       |
| test3 | int(11)     | YES |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

11、修改表名

方式1：ALTER TABLE tbl_name RENAME [TO|AS] new_name;

方式2：RENAME TABLE tbl_name TO new_name;

mysql> ALTER TABLE testAlert RENAME TO testAlter;
mysql> show tables;
+--------------------+
| Tables_in_learndb1 |
+--------------------+
...
| testalter |
...
+--------------------+
22 rows in set (0.00 sec)

12、修改数据表的存储引擎

ALTER TABLE tbl_name ENGINE=存储引擎名称;

mysql> ALTER TABLE testAlter ENGINE=MyISAM;
mysql> SHOW CREATE TABLE testAlter;
| testAlter | CREATE TABLE `testalter` (
`id` tinyint(4) NOT NULL DEFAULT '0',
`test1` varchar(20) NOT NULL,
`test2` double DEFAULT NULL,
`test3` int(11),
UNIQUE KEY `username` (`test2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)

13、修改自增长值

ALTER TABLE tbl_name AUTO_INCREMENT=值;

mysql> ALTER TABLE testAlter MODIFY id TINYINT NOT NULL KEY AUTO_INCREMENT;
mysql> ALTER TABLE testAlter AUTO_INCREMENT=100;
mysql> DESC testAlter;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | tinyint(4) | NO   | PRI | NULL    | auto_increment |
| test1 | varchar(20) | NO   |     | NULL    |                |
| test2 | double      | YES | UNI | NULL    |                |
| test3 | int(11)     | YES |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

mysql> INSERT testAlter(test1,test2,test3) VALUES('HHHHH',22.33,2);
mysql> INSERT testAlter(test1,test2,test3) VALUES('HHHHH',2.33,2);

mysql> SELECT * FROM testAlter;
+-----+-------+-------+-------+
| id | test1 | test2 | test3 |
+-----+-------+-------+-------+
| 100 | HHHHH | 22.33 | 2 |
| 102 | HHHHH | 2.33 | 2 |
+-----+-------+-------+-------+

0 0