数据表的增删改查（下）

这一篇呢我们接着上一篇继续对数据表的增删改查进行详细的讲解，废话不多说，直接说正题

修改数据表

如果想对已经创建好的数据表做一些结构上的修改，例如修改数据表名、修改字段类型或字段名、修改字段的排列位置、增加或删除字段、修改或删除表的约束等，可以选择删除原有的数据表，根据新的要求重新创建数据表。但如果原有的数据表中已经存在大量数据，此时选择删除再重新创建数据表，必然要做一些额外工作，例如将原有的数据重新导入数据库、解决用户访问原来数据表造成的影响等。在MySQL中，可以通过ALTER TABLE语句实现数据表结构的修改。ALTERTABLE语句的基本语法格式如下。

ALTER TABLE tbl_name

[alter_option [, alter_option] .…]

[partition_options]

在上述语法格式中，tbl_name表示要修改的数据表的名称，alter_option表示要修改的选项，partition_options表示要修改的分区选项(分区选项相关的内容在本章中不进行讲解)。 alter_option包含的基本选项如下。

{

RENAME[TO | AS]新数据表名

|RENAME COLUMN 旧字段名 TO 新字段名

| AFTER 字段名2]

| AFTER 字段名]

|MODIFY[COLUMN] 字段名1 新数据类型[列级约束］[FIRST AFTER 字段名2]

|CHANGE [COLUMN] 旧字段名 新宁段名 新数据类型[列级约束][FIRST | AFTER 字段名]

|ADD[COLUMN] 字段名 新字段名。数据类型[列级约束］[FIRST | AFTER 字段名]

|ADD [CONSTRAINT] {PRIMARY KEY |UNIQUE}(字段名1[，·..])

|DROP [COLUMN] 字段名|《INDEX|KEY》索引名 |PRIMARY KEY

|table_options

|...

}

上述选项的基本功能说明如下。

• RENAME [TO | AS]新数据表名：修文数据表的名称，T0或AS为可选项，如果选择使用，则任选其一即可；新数据表名指的是修改后的数据表名。
• RENAME COLUMN 旧字段名TO新字段名：重命名字段的名称。
• MODIFY [COLUMN]字段名1新数据类型[列级约束][FIRST|AFTER字段名2]：可以重新定义字段数据类型、列级约束和排列位置，其中列级约束和排列位置都是可选项。可选项“FIRST|AFTER字段名2”中的FIRST指的是将字段名1的位置修改为数据表的第一列，“AFTER字段名2”是将字段名1插到字段名2的后面。需要注意的是，字段的新数据类型必须设置，如果不需要修改字段的数据类型，可以将新数据类型设置成和原来一样。
• CHANGE [COLUMN]旧字段名 新字段名 新数据类型[列级约束] [FIRST AFTER 字段名]：重新命名字段名称并重新定义字段数据类型、列级约束和排列位置。需要注意的是，如果不对字段名称进行重命名，新字段名与旧字段名保持一致即可；如果不需要修改字段的数据类型，也需要将新数据类型设置成和原来一样。
• ADD [COLUMN]字段名 新字段名 数据类型[列级约束] [FIRST |AFTER字段名]：向数据表中插入新字段，如果要在数据表的特定位置添加字段，可以使用可选项 [FIRST|AFTER字段名]实现。
• ADD [CONSTRAINT] {PRIMARY KEY | UNIQUE} (字段名)：为指定的字段设置主键约束或唯一约束。
• DROP [COLUMN] 字段名|索引名|PRIMARY KEY：删除数据表中指定的字段、索引或主键。
• table_options:表选项，与CREATE TABLE语句中的表选项一样，用于设置数据表的相关选项，如字符集、校对集等。

alter_option所包含的选项不只是上述描述的部分，由于篇幅有限，在此只讲解常用选项。

1.修改数据表名

在企业开发中，大部分企业会对开发的内容制定相关规约，如果开发人员创建的数据表的名称不符合公司制定的规约，可以对数据表名进行修改。接下来，通过一个案例演示使用ALTER TABLE语句修改数据表名。

将数据库data中的数据表year的名称修改为dept。在修改数据表名之前，首先使用SHOW TABLES语句查看数据库中的所有数据表，具体SQL语句及执行结果如下。

mysql> show tables;
+----------------+
| Tables_in_data |
+----------------+
| data01         |
| year           |
+----------------+
2 rows in set (0.01 sec)

我再提醒一遍，SQL语句大小写都可以，之前也说过，大写是规范的，这里为了便于大家观看，后面的我都用小写。

上述语句执行完毕后，使用ALTER TABLE语句将数据表year的名称修改为dept，具体SQL语句及执行结果如下。

mysql> alter table year rename to dept;
Query OK, 0 rows affected (0.07 sec)

上面的重命名语句里面的to还可以写为as，在基本功能的第一条已经说过了，这里用代码展示可以更好的理解那些标准术语，TO和AS不写也可以：

mysql> alter table dept rename year;
Query OK, 0 rows affected (0.02 sec)

从上面的代码中可以看出我没有写TO或者AS，SQL语句依然正常执行，这个可以跟大家的习惯去使用，求方便的也可以不写。上面我把year数据表的名称改为了dept，然后又改了回来，下面使用show tables命令查看一下最后的结果：

mysql> show tables;
+----------------+
| Tables_in_data |
+----------------+
| data01         |
| year           |
+----------------+
2 rows in set (0.00 sec)

结果很显然我们修改成功了。

2.修改数据表选项

数据表中的表选项（字符集、校对集及存储引擎）也可以通过ALTER TABLE语句进行修改。下面以修改数据表year的字符集为例演示数据表的选项的修改，具体的SQL语句及执行结果如下。

mysql> show create table year;
+-------+-------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
                                                        |
+-------+-------------------------------------------------------------------------------------------------------------------------------+
| year  | CREATE TABLE `year` (
  `name` varchar(100) DEFAULT NULL,
  `birthday` year DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

上面这个是没修改之前的，下面是修改过后的：

mysql> alter table year character set=gbk;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table year;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
                                                                          |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| year  | CREATE TABLE `year` (
  `name` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `birthday` year DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

这是修改之后的，CHARSET发生了变化，数据表的字符集变为了gbk说明通过ALTER TABLE语句成功修改了数据表year的字符集。

3.修改字段名

ALTER TABLE语句对修改字段名提供了两种方式，分别为使用RENAME COLUMN和CHANGE。这两种方式的区别主要在于：RENAME COLUMN仅可以修改字段名;CHANGE子句不仅可以修改字段名，还可以重新定义字段的数据类型、约束排列位置。如果仅修改字段名，RENAME COLUMN子句则更方便。

接下来，通过一个案例演示使用RENAME COLUMN修改字段名。

将year这个表的name字段修改为new_name，先查看year表的结构：

mysql> desc year;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| name     | varchar(100) | YES  |     | NULL    |       |
| birthday | year         | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

再修改字段名：

mysql> alter table year rename column name to new_name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

字段名修改成功了，我们再看看结果：

mysql> desc year;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| new_name | varchar(100) | YES  |     | NULL    |       |
| birthday | year         | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

显然我们修改成功了。字段名name成功修改为new_name。

4.修改字段的数据类型

创建数据表后，字段的数据类型就已经确定，如果需要对创建好的数据表中字段的数据类型进行修改，可以通过ALTER TABLE语句中的MODIFY和CHANGE完成。其中MODIFY仅可以对字段的数据类型和排列位置重新定义，而CHANGE不仅可以对字段的数据类型和排列位置重新定义，还可以修改字段名。

使用MODIFY和CHANGE修改字段数据类型的效果一样，但使用MODIFY的语法相对简洁。接下来，通过一个案例演示使用MODIFY修改字段的数据类型。

还是year这个数据表，我们的目标是new_name后面的varchar数据类型进行修改，修改为char类型

mysql> desc year;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| new_name | varchar(100) | YES  |     | NULL    |       |
| birthday | year         | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

修改语句：

mysql> alter table year modify new_name char(16);
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

修改成功后查看结果：

mysql> desc year;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| new_name | char(16) | YES  |     | NULL    |       |
| birthday | year     | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

varchar数据类型被修改为了char数据类型，修改成功了。

5.修改字段的排列位置

字段在数据表中的排列位置可以在创建数据表时进行指定，数据表创建后如果想要修改字段的排列位置，ALTER TABLE语句中也提供了MODIFY和CHANGE两种方式。接下来，通过一个案例演示使用这两种方式修改字段排列位置。

(1)使用CHANGE修改字段的排列位置。

将表year中字段birthday的位置修改为数据表的第一个字段，并且在执行ALTER TABLE语句后使用DESC语句查询出数据表year的表结构信息，具体SQL语句及执行结果如下。

mysql> alter table year change birthday birthday year first;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc year;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| birthday | year     | YES  |     | NULL    |       |
| new_name | char(16) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

从DESC语句的执行结果可以看出，birthday字段排在了第一位，但在修改数要注意，需要先输入一个字段名，这个表名是要修改的字段，后面再输入一个字段名和其数据类型，再加一个FIRST，表示第一的意思，只有FIRST,可没有SECOND，小伙伴们需要注意了。

(2)使用MODIFY方式修改字段的排列位置。

将表year中字段new_name的位置修改为数据表的第一个字段（注意，这里的修改相较于CHANGE的修改来说不是直接定位第一位，而是放在某个字段的前面或者后面，灵活空间很大的），并且在执行ALTER TABLE语句后使用DESC语句查询出数据表year的表结构信息，具体SQL语句及执行结果如下。

mysql> alter table year modify birthday year after new_name;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc year;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| new_name | char(16) | YES  |     | NULL    |       |
| birthday | year     | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+

从DESC语句执行结果来看我们成功的把birthday字段放到了new_name字段后面，间接的使得new_name字段排在了第一位。

这里是我把birthday字段放在了new_name字段的后面才使得new_name排在了第一位，而不是直接定位第一的

这两个方法呢各有优劣，如果你是直接定位第一的话用CHANGE方法比较简单，如果要把一个字段放到另一个字段后面用第二种方法比较简单，不过要注意一点，CHANGE方法也有将一个字段放在另一个字段后的语句，不过没有MODIFY的简单，比较麻烦。

6.添加字段

在开发过程中，随着业务的变化，可能需要在已有数据表中添加新字段，例如因公司新规定，如果新招聘人员属于返聘人员，则需要业务负责人特批后才能办理入职手续。为此，在员工管理系统中，需要在已有待入职员工表中新增“是否返聘人员”字段。因此可以通过ALTER TABLE语句对数据表添加字段。

这是介绍一种为什么要增添新字段的原因，便于理解，接下来我们在year数据表中增加一个age新字段，具体语句及执行结果如下：

先使用DESC查看数据表中的结构：

mysql> desc year;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| new_name | char(16) | YES  |     | NULL    |       |
| birthday | year     | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

增加语句：

mysql> alter table year add age int;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

age是新增加的字段，数据类型为int，ADD是增添的命令

7.删除字段

数据表创建成功后，不仅可以修改字段，还可以删除字段。所谓删除字段指的是将某个字段从数据表中删除。接下来，根据ALTER TABLE语句的语法格式，编写一个删除字段的SQL语句，删除数据表year的age字段，具体SQL语句及执行结果如下。

mysql> alter table year drop age;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

这个就是删除字段的语句，删除后用DESC查看一下表的结构：

mysql> desc year;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| new_name | char(16) | YES  |     | NULL    |       |
| birthday | year     | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

没有了age这个字段，很显然我们删除字段成功了。

删除数据表

删除数据表是指删除数据库中已经存在的表。在删除数据表的同时，数据表中存储的数据也会被删除。

在MySQL中，可以使用DROP TABLE语句删除数据表，其基本语法格式如下：

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [,tbl_name]... [RESTRICT | CASCADE];

上述语法格式的具体说明如下：

• TEMPORARY：可选项，表示临时表，字面意思，就是临时的表，如果使用该选项，则表示删除临时表。
• IF EXISTS：这个熟悉吧，放在数据库中就是判断库存不存在，放在数据表中也是判断表存不存在的意思。
• tbl_name：这个更熟悉了吧，数据表的名字，可以同时删除多个数据表。
• RESTRICT | CASCADE：可选项，用于设置外键的级联级别，以确保数据的完整性。如果使用RESTRICT，从表引用了主表的数据时，直接删除主表会报错；如果使用CASCADE，删除主表时，会级联删除从表中引用主表的数据。外键、主表和从表的相关内容会在后面说的。

接下来，根据上述语法格式，编写一个简单的SQL语句，删除我们一直使用的year数据表，里面的数据也会一同被删除，具体SQL语句及执行结果如下：

mysql> drop table if exists year;
Query OK, 0 rows affected (0.01 sec)

这个是删除的语法，如果觉得写if exists麻烦也可以不写，为了验证是否成功删除数据表，使用show tables 查看一下这个库中的所有数据表：

mysql> show tables;
+----------------+
| Tables_in_data |
+----------------+
| data01         |
+----------------+
1 row in set (0.00 sec)

从结果来看，已经没了year这个数据表，说明我们删除成功了。

马上过年了，鄙人提前祝大家新年快乐，心想事成，万事如意。

感谢大家的支持。