表结构修改
背景
通常情况下,应该一开始就尽量的设计好,因为数据库一旦设计完成,最好不要大改。
但是,谁都不能保证,我的表永远够用,随时有可能需要改动。
因此,我们还是要了解一下,修改表结构。
注意:表结构修改,一般也不是开发区做。
先准备数据表
与数据
:
CREATE TABLE students (
number CHAR(9), # 学号
name VARCHAR(20), # 姓名
klass VARCHAR(10), # 班级
age INT, # 年龄
birth DATE # 生日
);
INSERT INTO students (number, name, klass, age, birth)
VALUES ('201804001', '刘一', 19, 16, '2002-01-01'),
('201804002', '陈二', 18, 17, '2001-01-02'),
('201804003', '张三', 19, 18, '2000-01-03'),
('201804004', '李四', 19, 19, '2001-01-04'),
('201804005', '王五', 19, 16, '2002-01-05'),
('201804006', '赵六', 18, 19, '1999-01-06'),
('201804007', '孙七', 19, 17, '2001-01-07'),
('201804008', '周八', 19, 18, '2000-01-08'),
('201804009', '吴九', 18, 17, '2001-01-09'),
('201804010', '郑十', 19, 18, '2000-01-10');
mysql> select * from students;
+-----------+--------+-------+------+------------+
| number | name | klass | age | birth |
+-----------+--------+-------+------+------------+
| 201804001 | 刘一 | 19 | 16 | 2002-01-01 |
| 201804002 | 陈二 | 18 | 17 | 2001-01-02 |
| 201804003 | 张三 | 19 | 18 | 2000-01-03 |
| 201804004 | 李四 | 19 | 19 | 2001-01-04 |
| 201804005 | 王五 | 19 | 16 | 2002-01-05 |
| 201804006 | 赵六 | 18 | 19 | 1999-01-06 |
| 201804007 | 孙七 | 19 | 17 | 2001-01-07 |
| 201804008 | 周八 | 19 | 18 | 2000-01-08 |
| 201804009 | 吴九 | 18 | 17 | 2001-01-09 |
| 201804010 | 郑十 | 19 | 18 | 2000-01-10 |
+-----------+--------+-------+------+------------+
10 rows in set (0.00 sec)
补充命令:
- 表描述:
DESC tb_name;
- 表中键:
SHOW KEYS FROM tb_name;
我们要用到得主命令是:ALTER TABLE
修改列
添加列
ADD COLUMN
# 将 gender 列添加到最后一列
ALTER TABLE students
ADD COLUMN gender BOOL;
mysql> ALTER TABLE students ADD COLUMN gender BOOL;
Query OK, 0 rows affected (0.44 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from students;
+-----------+--------+-------+------+------------+--------+
| number | name | klass | age | birth | gender |
+-----------+--------+-------+------+------------+--------+
| 201804001 | 刘一 | 19 | 16 | 2002-01-01 | NULL |
| 201804002 | 陈二 | 18 | 17 | 2001-01-02 | NULL |
| 201804003 | 张三 | 19 | 18 | 2000-01-03 | NULL |
| 201804004 | 李四 | 19 | 19 | 2001-01-04 | NULL |
| 201804005 | 王五 | 19 | 16 | 2002-01-05 | NULL |
| 201804006 | 赵六 | 18 | 19 | 1999-01-06 | NULL |
| 201804007 | 孙七 | 19 | 17 | 2001-01-07 | NULL |
| 201804008 | 周八 | 19 | 18 | 2000-01-08 | NULL |
| 201804009 | 吴九 | 18 | 17 | 2001-01-09 | NULL |
| 201804010 | 郑十 | 19 | 18 | 2000-01-10 | NULL |
+-----------+--------+-------+------+------------+--------+
10 rows in set (0.01 sec)
SHOW CREATE TABLE students\G
*************************** 1. row ***************************
Table: students
Create Table: CREATE TABLE `students` (
`number` char(9) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`klass` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`birth` date DEFAULT NULL,
`gender` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
序位
FIRST
# 将 gender 列添加到第一列 并给其默认值 ALTER TABLE students ADD COLUMN gender BOOL NOT NULL DEFAULT TRUE FIRST;
AFTER
# 将 gender 列添加到 klass 列后面 ALTER TABLE students ADD COLUMN gender BOOL NOT NULL DEFAULT TRUE AFTER klass;
删除列
DROP COLUMN
# 移除 gender 列
ALTER TABLE students
DROP COLUMN gender;
mysql> ALTER TABLE students DROP COLUMN gender;
Query OK, 0 rows affected (0.44 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE students\G
*************************** 1. row ***************************
Table: students
Create Table: CREATE TABLE `students` (
`number` char(9) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`klass` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`birth` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
修改列
仅修改列:
MODIFY COLUMN
# 将 gender 列移动到 age 列后面 ALTER TABLE students MODIFY COLUMN gender BOOL NOT NULL DEFAULT TRUE AFTER age; mysql> ALTER TABLE students MODIFY COLUMN gender BOOL NOT NULL DEFAULT TRUE AFTER age; ERROR 1054 (42S22): Unknown column 'gender' in 'students' mysql> ALTER TABLE students -> ADD COLUMN gender BOOL NOT NULL DEFAULT TRUE AFTER klass; Query OK, 0 rows affected (0.33 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE students MODIFY COLUMN gender BOOL NOT NULL DEFAULT TRUE AFTER age; Query OK, 0 rows affected (0.40 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE students\G *************************** 1. row *************************** Table: students Create Table: CREATE TABLE `students` ( `number` char(9) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `klass` varchar(10) DEFAULT NULL, `age` int(11) DEFAULT NULL, `gender` tinyint(1) NOT NULL DEFAULT '1', `birth` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
带重命名:
CHANGE COLUMN
# 将 birth 列改为 名为birthday的一个 DATETIME 类型列 ALTER TABLE students CHANGE COLUMN birth birthday DATETIME mysql> ALTER TABLE students CHANGE COLUMN birth birthday DATETIME; Query OK, 10 rows affected (0.46 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE students\G *************************** 1. row *************************** Table: students Create Table: CREATE TABLE `students` ( `number` char(9) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `klass` varchar(10) DEFAULT NULL, `age` int(11) DEFAULT NULL, `gender` tinyint(1) NOT NULL DEFAULT '1', `birthday` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
序位
FIRST
AFTER
修改键
添加约束键
ADD PRIMARY KEY
# 为 number 列添加 主键约束(唯一键类似) ALTER TABLE students ADD PRIMARY KEY (number); mysql> ALTER TABLE students -> ADD PRIMARY KEY (number); Query OK, 0 rows affected (0.38 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE students\G *************************** 1. row *************************** Table: students Create Table: CREATE TABLE `students` ( `number` char(9) NOT NULL, `name` varchar(20) DEFAULT NULL, `klass` varchar(10) DEFAULT NULL, `age` int(11) DEFAULT NULL, `gender` tinyint(1) NOT NULL DEFAULT '1', `birthday` datetime DEFAULT NULL, PRIMARY KEY (`number`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
ADD UNIQUE KEY
# 移除 number 列的 主键约束(但是会保留 非空) ALTER TABLE students ADD UNIQUE KEY (number); mysql> ALTER TABLE students ADD UNIQUE KEY (number); Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE students\G *************************** 1. row *************************** Table: students Create Table: CREATE TABLE `students` ( `number` char(9) NOT NULL, `name` varchar(20) DEFAULT NULL, `klass` varchar(10) DEFAULT NULL, `age` int(11) DEFAULT NULL, `gender` tinyint(1) NOT NULL DEFAULT '1', `birthday` datetime DEFAULT NULL, PRIMARY KEY (`number`), UNIQUE KEY `number` (`number`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
删除约束键
删除主键:
DROP PRIMARY KEY
ALTER TABLE students DROP PRIMARY KEY; mysql> ALTER TABLE students DROP PRIMARY KEY ; Query OK, 10 rows affected (2.07 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE students\G *************************** 1. row *************************** Table: students Create Table: CREATE TABLE `students` ( `number` char(9) NOT NULL, `name` varchar(20) DEFAULT NULL, `klass` varchar(10) DEFAULT NULL, `age` int(11) DEFAULT NULL, `gender` tinyint(1) NOT NULL DEFAULT '1', `birthday` datetime DEFAULT NULL, UNIQUE KEY `number` (`number`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
删除唯一键:
DROP KEY
ALTER TABLE students DROP KEY number; mysql> ALTER TABLE students DROP KEY number ; Query OK, 10 rows affected (0.39 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE students\G *************************** 1. row *************************** Table: students Create Table: CREATE TABLE `students` ( `number` char(9) NOT NULL, `name` varchar(20) DEFAULT NULL, `klass` varchar(10) DEFAULT NULL, `age` int(11) DEFAULT NULL, `gender` tinyint(1) NOT NULL DEFAULT '1', `birthday` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
注意:这里的
number
指的是 键得名字,而不是列名
。
无功能键(索引)
ADD KEY
ALTER TABLE students ADD KEY (name); mysql> ALTER TABLE students ADD KEY (name) ; Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE students\G *************************** 1. row *************************** Table: students Create Table: CREATE TABLE `students` ( `number` char(9) NOT NULL, `name` varchar(20) DEFAULT NULL, `klass` varchar(10) DEFAULT NULL, `age` int(11) DEFAULT NULL, `gender` tinyint(1) NOT NULL DEFAULT '1', `birthday` datetime DEFAULT NULL, KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
DROP KEY
ALTER TABLE students DROP KEY name; mysql> ALTER TABLE students DROP KEY name ; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE students\G *************************** 1. row *************************** Table: students Create Table: CREATE TABLE `students` ( `number` char(9) NOT NULL, `name` varchar(20) DEFAULT NULL, `klass` varchar(10) DEFAULT NULL, `age` int(11) DEFAULT NULL, `gender` tinyint(1) NOT NULL DEFAULT '1', `birthday` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
注意:这里的
number
指的是 键得名字,而不是列名
。