MySQL ALTER 命令

当我们需要修改数据表名或者修改数据表字段时，就需要使用到 MySQL ALTER 命令

MySQL 的 ALTER 命令用于修改数据库、表和索引等对象的结构

ALTER 命令允许你添加、修改或删除数据库对象，并且可以用于更改表的列定义、添加约束、创建和删除索引等操作

以下示例在之前创建的RoboMaster数据库下进行操作

当前数据库数据

mysql> select * from Username;
+------+------------+-----------+
| id   | Username   | Password  |
+------+------------+-----------+
|    2 | xin        | 123456789 |
|    1 | steins_xin | 123456    |
|    1 | s_xin      | 123456    |
|    2 | steins     | 123456789 |
+------+------------+-----------+
4 rows in set (0.00 sec)

示例：

1. 添加列

ALTER TABLE username
ADD COLUMN birth_date DATE;

以上 SQL 语句在 username 表中添加了一个名为 birth_date 的日期列：

结果：

mysql> ALTER TABLE username
    -> ADD COLUMN birth_date DATE;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from Username;
+------+------------+-----------+------------+
| id   | Username   | Password  | birth_date |
+------+------------+-----------+------------+
|    2 | xin        | 123456789 | NULL       |
|    1 | steins_xin | 123456    | NULL       |
|    1 | s_xin      | 123456    | NULL       |
|    2 | steins     | 123456789 | NULL       |
+------+------------+-----------+------------+
4 rows in set (0.00 sec)

2. 修改列的数据类型

ALTER TABLE username 
MODIFY COLUMN birth_date INT;

以上 SQL 语句将 username 表中 birth_date 数据类型修改为 INT：

结果：

// 修改前
mysql> desc username;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int          | YES  |     | NULL    |       |
| Username   | varchar(255) | YES  |     | NULL    |       |
| Password   | varchar(255) | YES  |     | NULL    |       |
| birth_date | date         | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

// 修改后
mysql> ALTER TABLE username
    -> MODIFY COLUMN birth_date INT;
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc username;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int          | YES  |     | NULL    |       |
| Username   | varchar(255) | YES  |     | NULL    |       |
| Password   | varchar(255) | YES  |     | NULL    |       |
| birth_date | int          | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

可以看见 birth_date 从 date 的数据类型变成了 int 数据类型

3. 修改列名

ALTER TABLE username 
CHANGE COLUMN birth_date birth_char VARCHAR(255);

以上 SQL 语句将 username 表中 birth_date 修改为 birth_char 同时将数据类型变成VARCHAR(255)

结果：

// 修改前
mysql> desc username;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int          | YES  |     | NULL    |       |
| Username   | varchar(255) | YES  |     | NULL    |       |
| Password   | varchar(255) | YES  |     | NULL    |       |
| birth_date | int          | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

// 修改后
mysql> ALTER TABLE username
    -> CHANGE COLUMN birth_date birth_char VARCHAR(255);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc username;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int          | YES  |     | NULL    |       |
| Username   | varchar(255) | YES  |     | NULL    |       |
| Password   | varchar(255) | YES  |     | NULL    |       |
| birth_char | varchar(255) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

4. 删除列

ALTER TABLE username 
DROP COLUMN birth_char;

以上 SQL 语句将 username 表中 birth_char 列删除

结果：

// 删除前
mysql> select * from Username;
+------+------------+-----------+------------+
| id   | Username   | Password  | birth_char |
+------+------------+-----------+------------+
|    2 | xin        | 123456789 | NULL       |
|    1 | steins_xin | 123456    | NULL       |
|    1 | s_xin      | 123456    | NULL       |
|    2 | steins     | 123456789 | NULL       |
+------+------------+-----------+------------+
4 rows in set (0.00 sec)

// 删除后
mysql> ALTER TABLE username
    -> DROP COLUMN birth_char;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from Username;
+------+------------+-----------+
| id   | Username   | Password  |
+------+------------+-----------+
|    2 | xin        | 123456789 |
|    1 | steins_xin | 123456    |
|    1 | s_xin      | 123456    |
|    2 | steins     | 123456789 |
+------+------------+-----------+
4 rows in set (0.00 sec)

5. 添加 PRIMARY KEY

ALTER TABLE username 
ADD PRIMARY KEY (username);

以上 SQL 语句将 username 表中 username 列作为主键

mysql> desc username;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| Username | varchar(255) | YES  |     | NULL    |       |
| Password | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

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

mysql> desc username;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| Username | varchar(255) | NO   | PRI | NULL    |       |
| Password | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

注意：主键是不能重复的，这里数据库里面id有重复，所以选择username作为主键

6. 添加 FOREIGN KEY

ALTER TABLE username 
ADD CONSTRAINT fk_id
FOREIGN KEY (id)
REFERENCES test (id);

以下上 SQL 语句在 username 表中添加了一个外键，关联到 test 表的 id 列：

外键所在表为子表，如下是外键约束的作用：

外键的默认作用有两点：

1.对子表(外键所在的表)的作用：子表在进行写操作的时候，如果外键字段在父表中找不到对应的匹配，操作就会失败。

2.对父表的作用：对父表的主键字段进行删和改时，如果对应的主键在子表中被引用，操作就会失败。

外键的定制作用----三种约束模式：

district：严格模式(默认), 父表不能删除或更新一个被子表引用的记录

cascade：级联模式, 父表操作后，子表关联的数据也跟着一起操作

set null：置空模式,前提外键字段允许为NLL, 父表操作后，子表对应的字段被置空

使用外键的前提：

1. 表储存引擎必须是innodb，否则创建的外键无约束效果

2. 外键的列类型必须与父表的主键类型完全一致

3. 外键的名字不能重复

4. 已经存在数据的字段被设为外键时，必须保证字段中的数据与父表的主键数据对应起来

当前数据表：

mysql> select * from test;
+------+------------+----------+
| id   | Username   | Password |
+------+------------+----------+
|    2 | steins_xin | 123456   |
|    1 | steinsxin  | 123456   |
+------+------------+----------+
2 rows in set (0.00 sec)

mysql> select * from username;
+------+------------+-----------+
| id   | Username   | Password  |
+------+------------+-----------+
|    1 | steins_xin | 123456    |
|    2 | xin        | 123456789 |
+------+------------+-----------+
2 rows in set (0.00 sec)

注意：创建外键时两个表 (如果已经存在) 相应的数据列需要相同，如上的id列

结果：

mysql> ALTER TABLE username
    -> ADD CONSTRAINT fk_id
    -> FOREIGN KEY (id)
    -> REFERENCES test (id);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

username作为子表，test作为父表，在test中没有对应key的时候，username会出现无法插入数据情况

mysql> INSERT INTO username values(3,'test','123456');
ERROR 1062 (23000): Duplicate entry 'test' for key 'username.PRIMARY'

同理，username所存在的key值，test表中不能删除

```sql mysql> DELETE FROM test WHERE id=3; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`robomaster`.`username`, CONSTRAINT `fk_id` FOREIGN KEY (`id`) REFERENCES `test` (`id`)) ```

这里需要特别注意父表和子表之间的操作关系

7. 删除 FOREIGN KEY

ALTER TABLE username DROP FOREIGN KEY fk_id;

结果：

mysql> ALTER TABLE username DROP FOREIGN KEY fk_id;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

8. 修改表名

ALTER TABLE username
RENAME TO user;