使用数据库创建和删除表

举报
xcc-2022 发表于 2022/07/24 11:10:35 2022/07/24
【摘要】 8.1　创建数据表在MySQL中创建完数据库后，需要先使用“USE 数据库名”的形式指定在哪个数据库中进行操作，然后再执行创建数据表的SQL语句，或者直接使用“数据库名.数据表名”的形式创建数据表。8.1.1　创建空数据表MySQL中创建数据表需要遵循一定的语法格式，这些语法格式为创建数据表提供了模板依据。同时，遵循一定的语法格式创建数据表，也降低了出错的概率。1．语法格式在MySQL中创建...

8.1　创建数据表

在MySQL中创建完数据库后，需要先使用“USE 数据库名”的形式指定在哪个数据库中进行操作，然后再执行创建数据表的SQL语句，或者直接使用“数据库名.数据表名”的形式创建数据表。


8.1.1　创建空数据表

MySQL中创建数据表需要遵循一定的语法格式，这些语法格式为创建数据表提供了模板依据。同时，遵循一定的语法格式创建数据表，也降低了出错的概率。

1．语法格式

在MySQL中创建表使用的是CREATE TABLE语句，语法格式如下：

CREATE TABLE [IF NOT EXISTS] 表名(
  字段1, 数据类型 [约束条件] [默认值],
  字段2, 数据类型 [约束条件] [默认值],
  字段3, 数据类型 [约束条件] [默认值],
  ……
  [表约束条件]
  );

在创建数据表时，必须指定数据表的表名称，表名称在Windows操作系统上不区分大小写，在Linux操作系统上区分大小写。如果需要在Linux操作系统上不区分大小写，则需要在MySQL的配置文件my.cnf中添加一项配置。

lower_case_table_names=1

另外，在创建数据表时，还需要指定数据表中每一列的名称和数据类型，多个列之间需要以逗号进行分隔。

2．简单示例

在第7章中创建的名称为goods的数据库中，创建名称为t_goods_category1的商品类别表。

名称为t_goods_category1的商品类别数据表的表结构如表8-1所示。

表8-1　商品类别表结构

首先，需要使用“USE 数据库名”的形式指定需要在哪个数据库下创建数据表。

mysql> USE goods;
  Database changed

接下来，查看goods数据库中存在的所有数据表。

mysql> SHOW TABLES;
  Empty set (0.03 sec)

目前goods数据库中不存在数据表。

在goods数据库中创建名称为t_goods_category1的数据表。

mysql> CREATE TABLE t_goods_category1(
      -> id INT(11),
      -> t_category VARCHAR(30),
      -> t_remark VARCHAR(100)
      -> );
  Query OK, 0 rows affected, 1 warning (0.36 sec)

创建名称为t_goods_category1的数据表的SQL语句在MySQL命令行执行成功。

细心的读者可以发现，在执行创建t_goods_category1数据表的SQL语句时，MySQL虽然没有报错，但是有一个警告信息。

1 warning (0.36 sec)

使用如下SQL语句查看MySQL的警告信息。

mysql> SHOW WARNINGS;
  +--------+------+------------------------------------------------------------------------------+
  |  Level  | Code |                                     Message                                  |
  +--------+------+------------------------------------------------------------------------------+
  | Warning | 1681 | Integer display width is deprecated and will be removed in a future release. |
  +--------+------+------------------------------------------------------------------------------+
  1 row in set (0.00 sec)

在MySQL 8.x版本中，不再推荐为INT类型指定显示长度，并在未来的版本中可能去掉这样的语法。因此这里去掉INT类型的显示长度，重新创建名称为t_goods_category1的数据表。

mysql> CREATE TABLE t_goods_category1(
      -> id INT,
      -> t_category VARCHAR(30),
      -> t_remark VARCHAR(100) 
      -> );
  Query OK, 0 rows affected (0.01 sec)

此时MySQL不再显示警告信息。在MySQL 8.x版本中创建数据表时，建议读者不要再为INT类型指定显示长度。

接下来再次查看goods数据库中存在的数据表。

mysql> SHOW TABLES;
  +--------------------+
  | Tables_in_goods    |
  +--------------------+
  | t_goods_category1  |
  +--------------------+
  1 row in set (0.00 sec)

此时，goods数据库中已经存在t_goods_category1数据表，说明商品类别数据表t_goods_category1创建成功。

当数据库中已经存在相关的数据表而再次使用“CREATE TABLE 数据表”的语法格式创建数据表时，会显示如下错误信息。

ERROR 1050 (42S01): Table 't_goods_category1' already exists

此时可以使用“CREATE TABLE IF NOT EXISTS 数据表名”的语法格式创建数据表。

mysql> CREATE TABLE IF NOT EXISTS t_goods_category1 (
      -> id INT,
      -> t_category VARCHAR(30),
      -> t_remark VARCHAR(100) 
      -> );
  Query OK, 0 rows affected (0.00 sec)

加上IF NOT EXISTS关键字后，即使数据库中已经存在要创建的数据表，MySQL也不会抛出任何错误了。

在创建数据表时，如果加上了IF NOT EXISTS关键字，则表示的含义为：如果当前数据库中不存在要创建的数据表，则创建数据表；如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。


8.1.2　创建数据表时指定主键

在MySQL中创建数据表时，可以为数据表指定主键。主键又被称为主码，包含表中的一列或者多列，能够唯一标识表中的一行记录。同时，主键列的数据必须唯一，并且不能为空。主键可以分为单列主键和多列联合主键。

1．单列主键

单列主键只包含数据表中的一个字段，可以在定义数据列的同时指定主键，也可以在定义完数据表中的所有列之后指定主键。

（1）在定义列的同时指定主键。

语法格式如下：

字段 数据类型 PRIMARY KEY [默认值]

简单示例如下：

创建名称为t_goods_category2的数据表，将id字段指定为主键。

mysql> CREATE TABLE t_goods_category2 (
      -> id INT PRIMARY KEY,
      -> t_category VARCHAR(30),
      -> t_remark VARCHAR(100)
      -> );
  Query OK, 0 rows affected (0.02 sec)

在创建数据表t_goods_category2时，定义id字段的同时将id字段指定为数据表的主键，此时，id字段列的数据唯一并且不能为空，能够唯一标识t_goods_category2数据表中的一行记录。

（2）定义完数据表中的所有列之后指定主键。

语法格式如下：

[CONSTRAINT 约束条件名] PRIMARY KEY [字段名]

简单示例如下：

创建名称为t_goods_category3的数据表，在定义完表中的所有列之后，指定id字段为表的主键。

mysql> CREATE TABLE t_goods_category3 (
      -> id INT,
      -> t_category VARCHAR(30),
      -> t_remark VARCHAR(100),
      -> PRIMARY KEY(id)
      -> );
  Query OK, 0 rows affected (0.10 sec)

2．多列联合主键

MySQL中除了支持单列主键之外，还支持多个字段共同组成MySQL数据表的主键，也就是多列联合主键。多列联合主键只能在定义完数据表的所有列之后进行指定。

语法格式如下：

PRIMARY KEY [字段1, 字段2, 字段3…., 字段n]

简单示例如下：

在商城系统中，一个店铺往往有自己的商品类别信息，同时，系统中也会提供默认的商品类别信息，商家可以在自己的店铺中选择系统提供的默认类别信息，也可以根据自身店铺的需要，自定义商品类别信息。

这就要求商城系统在设计商家店铺相关的商品类别数据表时，合理设计数据表的主键。此时，一种设计方案就是将商品类别id和商家店铺id设置为联合主键。

在MySQL的goods数据库中，创建名称为t_goods_category4的商家店铺商品类别数据表，数据表结构如表8-2所示。

表8-2　商家店铺商品类别数据表结构

从表8-2中可以看出，名称为t_goods_category4的数据表的主键为t_category_id字段与t_shop_id字段组成的多列联合主键。

在MySQL中创建名称为t_goods_category4的数据表。

mysql> CREATE TABLE t_goods_category4 (
      -> t_category_id INT,
      -> t_shop_id INT,
      -> t_category VARCHAR(30),
      -> t_remark VARCHAR(100),
      -> PRIMARY KEY (t_category_id, t_shop_id)
      -> );
  Query OK, 0 rows affected (0.02 sec)

由SQL语句的执行结果可以看出，创建名称为t_goods_category4的数据表时，成功将t_category_id字段与t_shop_id字段指定为数据表的联合主键。


8.1.3　创建数据表时指定外键

外键可以关联数据库中的两张表，其对应的是数据库中的参照完整性。一张表的外键可以为空，也可以不为空，当外键不为空时，则每一个外键的值必须等于另一张表的主键的某个值。一张表的外键可以不是本表的主键，但其对应着另一张表的主键。在一张表中定义了外键之后不允许删除另一张表中具有关联关系的行数据。

由外键引申出两个概念，分别是主表（父表）和从表（子表）。

·主表（父表）：两个表具有关联关系时，关联字段中主键所在的表为主表（父表）。

·从表（子表）：两个表具有关联关系时，关联字段中外键所在的表为从表（子表）。

1．语法格式

创建外键的语法格式如下：

[CONSTRAINT 外键名] FOREIGN KEY 字段1 [, 字段2, 字段3, …]
  REFERENCES 主表名 主键列1 [, 主键列2, 主键列3, …]

·外键名：定义外键时为数据表指定的外键名称。在同一张数据表中，外键的名称必须唯一。也就是说，在同一张数据表中，不能有相同名称的外键名称。

·FOREIGN KEY：指定外键包含哪些字段，可以是一个字段，也可以是多个字段的组合。

·REFERENCES：指定关联的主表名称。

·主表名：主键所在的表名称。

·主键列：主表中定义的主键字段，可以是一个字段，也可以是多个字段的组合。

2．简单示例

首先，按照表8-1的结构创建商品类别表t_goods_category，并指定id为表的主键。

mysql> CREATE TABLE t_goods_category (
      -> id INT PRIMARY KEY,
      -> t_category VARCHAR(30),
      -> t_remark VARCHAR(100)
      -> );
  Query OK, 0 rows affected (0.01 sec)

接下来创建商品信息表t_goods，其中，商品信息表t_goods中的外键为t_category_id字段，表结构如表8-3所示。

表8-3　商品信息表结构

创建商品信息表t_goods，使其t_category_id字段关联到t_goods_category表的主键id。

mysql> CREATE TABLE t_goods(
      ->  id INT PRIMARY KEY,
      -> t_category_id INT,
      -> t_category VARCHAR(30),
      -> t_name VARCHAR(50),
      -> t_price DECIMAL(10,2),
      -> t_stock INT,
      -> t_upper_time DATETIME,
      -> CONSTRAINT foreign_category FOREIGN KEY(t_category_id) REFERENCES t_goods_category(id)
      -> );
  Query OK, 0 rows affected (0.12 sec)

创建商品信息表t_goods时，将表t_goods的t_category_id字段作为外键关联到商品类别表t_goods_category的主键id上。

注意：一张表的外键与其关联的另一张表的主键的数据类型必须相同。


8.1.4　创建数据表时指定字段非空

在MySQL中，可以在创建数据表时指定数据表的某个字段或某些字段的值不能为空。如果将某个字段或某些字段设置为非空约束条件，则在向数据表插入数据时，必须为这些字段指定相应的值，否则MySQL会报错。

1．语法格式

为字段设置为非空约束条件的语法格式如下：

字段名称 数据类型 NOT NULL

即在定义数据表字段后面加上NOT NULL关键字，即可将当前字段设置为非空约束条件。

2．简单示例

创建商品类别表t_goods_category5，并指定其类别名称字段t_category不能为空。

mysql> CREATE TABLE t_goods_category5(
      -> id INT PRIMARY KEY,
      -> t_category VARCHAR(30) NOT NULL,
      -> t_remark VARCHAR(100)
      -> );
  Query OK, 0 rows affected (0.03 sec)

可以看到，SQL语句执行成功，将表t_goods_category的id字段设置为主键，并将t_category字段设置为非空约束条件。


8.1.5　创建数据表时指定默认值

在MySQL中，创建数据表时可以为字段设置默认值。比如将INT类型的默认值可以设置为0，将VARCHAR类型的默认值可以设置为空字符串，将DATETIME类型的默认值可以设置为系统当前时间等。如果为字段指定了默认值，则在向数据表插入数据时，如果没有为当前字段指定任何值，则MySQL会自动为该字段赋值为指定的默认值。

1．语法格式

创建数据表时指定默认值的语法如下：

字段名称 数据类型 DEFAULT 默认值

定义字段时，在字段后面使用关键字DEFAULT，后面跟默认值，即可为当前字段设置默认值。

2．简单示例

创建商家店铺对应的商品类别信息表t_goods_category9，并指定t_shop_id字段的默认值为1。

mysql> CREATE TABLE t_goods_category9(
      -> t_category_id INT,
      -> t_shop_id INT DEFAULT 1,
      -> t_category VARCHAR(30) NOT NULL,
      -> t_remark VARCHAR(100)
      -> );
  Query OK, 0 rows affected (0.02 sec)

可以看到，SQL语句执行成功，此时在数据表t_goods_category9的t_shop_id字段上设置默认值为1。当向表t_goods_category9中插入数据时，没有指定t_shop_id的值，则MySQL会为t_shop_id字段设置默认值1。


8.1.6　创建数据表时指定主键默认递增

MySQL支持将整数类型的主键设置为默认递增类型，这样在向数据表插入数据时，可以不用指定整数类型主键的值，MySQL会将该表的整数类型的主键值自动加1。

1．语法格式

MySQL中设置整数类型的主键值默认递增的语法格式如下：

字段名称 数据类型 AUTO_INCREMENT

可以看出，只需要在整数类型的主键字段后面加上AUTO_INCREMENT关键字，即可将当前整数类型的主键值设置为自动递增。

2．简单示例

创建商品类别数据表t_goods_category10，并指定商品类别id自动递增。

mysql> CREATE TABLE t_goods_category10(           
      -> id INT PRIMARY KEY AUTO_INCREMENT,        
      -> t_category VARCHAR(30),       
      -> t_remark VARCHAR(100)
      -> );
  Query OK, 0 rows affected (0.01 sec)

SQL语句执行成功，此时，数据表t_goods_category10的主键id为自动递增类型。在向数据表t_goods_category10中插入数据时，主键id的值默认从1开始每次插入一条新数据时，id字段的值会自动加1。


8.1.7　创建数据表时指定存储引擎

MySQL支持在创建数据表时为该数据表指定相应的存储引擎。同时，根据具体业务需求，一个数据库中可以包含不同存储引擎的数据表。

1．语法格式

在MySQL中创建数据表时指定存储引擎的语法格式如下：

ENGINE=存储引擎名称

在创建表语句后面使用“ENGINE=存储引擎名称”即可为当前表指定相应的存储引擎。

2．简单示例

创建商品类别信息表t_goods_category11，并为数据表t_goods_category11指定InnoDB存储引擎。

mysql> CREATE TABLE t_goods_category11(
      -> id INT PRIMARY KEY AUTO_INCREMENT,
      -> t_category VARCHAR(30), 
      -> t_remark VARCHAR(100)
      -> )ENGINE=InnoDB;
  Query OK, 0 rows affected (0.01 sec)

在创建数据表t_goods_category11时，明确为数据表t_goods_category11指定了InnoDB存储引擎。


8.1.8　创建数据表时指定编码

MySQL支持在创建数据表时为数据表指定编码格式，也可以在创建数据表后修改数据表的编码格式。

1．语法格式

创建数据表时，为数据表指定编码格式的语法格式如下：

DEFAULT CHARACTER SET 编码 COLLATE 校对规则

也可以使用如下语法格式：

DEFAULT CHARSET=编码 COLLATE=校对规则

2．简单示例

创建商品类别信息表t_goods_category12，并明确指定数据表的编码格式为utf8mb4，校对规则为utf8mb4_0900_ai_ci。

mysql> CREATE TABLE t_goods_category12(
      -> id INT NOT NULL AUTO_INCREMENT,
      -> t_category VARCHAR(30),
      -> t_remark VARCHAR(100),
      -> PRIMARY KEY(id)
      -> )ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
  Query OK, 0 rows affected (0.01 sec)

也可以使用如下语句创建数据表：

mysql> CREATE TABLE t_goods_category12(
      -> id INT NOT NULL AUTO_INCREMENT,
      -> t_category VARCHAR(30),
      -> t_remark VARCHAR(100),
      -> PRIMARY KEY(id)
      -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  Query OK, 0 rows affected (0.02 sec)

SQL语句执行成功，为数据表指定的编码格式为utf8mb4，校对规则为utf8mb4_0900_ai_ci。

8.2　查看数据表结构

在MySQL中创建好数据表之后，可以查看数据表的结构。MySQL支持使用DESCRIBE/DESC语句查看数据表结构，也支持使用SHOW CREATE TABLE语句查看数据表结构。


8.2.1　使用DESCRIBE/DESC语句查看表结构

MySQL中可以使用DESCRIBE/DESC语句查看数据表的结构信息，结构信息中包括字段名称、数据类型及是否是主键等信息。

1．语法格式

使用DESCRIBE语句查看表结构的语法格式如下：

DESCRIBE 表名称

使用DESC语句查看表结构的语法格式如下：

DESC 表名称

2．简单示例

查看商品类别表t_goods_category的表结构信息。

mysql> DESCRIBE t_goods_category;
  +------------+--------------+------+-----+---------+-------+
  | Field      | Type         | Null | Key | Default | Extra |
  +------------+--------------+------+-----+---------+-------+
  | id         | int(11)      | NO   | PRI | NULL    |       |
  | t_category | varchar(30)  | YES  |     | NULL    |       |
  | t_remark   | varchar(100) | YES  |     | NULL    |       |
  +------------+--------------+------+-----+---------+-------+
  3 rows in set (0.00 sec)

查看商品信息表t_goods的表结构信息。

mysql> DESC t_goods;
  +---------------+---------------+------+-----+---------+-------+
  | Field         | Type          | Null | Key | Default | Extra |
  +---------------+---------------+------+-----+---------+-------+
  | id            | int(11)       | NO   | PRI | NULL    |       |
  | t_category_id | int(11)       | YES  | MUL | NULL    |       |
  | t_category    | varchar(30)   | YES  |     | NULL    |       |
  | t_name        | varchar(50)   | YES  |     | NULL    |       |
  | t_price       | decimal(10,2) | YES  |     | NULL    |       |
  | t_stock       | int(11)       | YES  |     | NULL    |       |
  | t_upper_time  | datetime      | YES  |     | NULL    |       |
  +---------------+---------------+------+-----+---------+-------+
  7 rows in set (0.00 sec)

其中，每个字段的含义如下：

·Field：数据表中的每个字段。

·Type：数据表中字段的数据类型。

·Null：数据表中的当前字段值是否可以为NULL。

·Key：数据表中的当前字段是否存在索引。PRI表示当前列是主键列，或者是主键的一部分；UNI表示当前列是UNIQUE标识的唯一索引列，或者是唯一索引列的一部分；MUL表示在当前列中的某个值可以出现多次。

·Default：表示当前列是否有默认值，同时会显示当前列的默认值是多少。

·Extra：表示与当前列相关的附件信息。


8.2.2　使用SHOW CREATE TABLE语句查看表结构

MySQL支持使用SHOW CREATE TABLE语句查看数据表的建表语句。通过SHOW CREATE TABLE语句，不仅可以查看创建数据表的SQL语句，还可以查看数据表的存储引擎和字符编码等信息。

1．语法格式

使用SHOW CREATE TABLE语句查看表结构的语法格式如下：

SHOW CREATE TABLE 表名 \G

其中，\G可以使输出结果信息更加美观，便于查看和阅读。

2．简单示例

使用SHOW CREATE TABLE语句查看商品类别数据表t_goods_category的结构信息。

mysql> SHOW CREATE TABLE t_goods_category \G
  *************************** 1. row ***************************
         Table: t_goods_category
  Create Table: CREATE TABLE `t_goods_category` (
    `id` int(11) NOT NULL,
    `t_category` varchar(30) DEFAULT NULL,
    `t_remark` varchar(100) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)

查看商品信息表t_goods的结构信息。

mysql> SHOW CREATE TABLE t_goods \G
  *************************** 1. row ***************************
         Table: t_goods
  Create Table: CREATE TABLE `t_goods` (
    `id` int(11) NOT NULL,
    `t_category_id` int(11) DEFAULT NULL,
    `t_category` varchar(30) DEFAULT NULL,
    `t_name` varchar(50) DEFAULT NULL,
    `t_price` decimal(10,2) DEFAULT NULL,
    `t_stock` int(11) DEFAULT NULL,
    `t_upper_time` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `foreign_category` (`t_category_id`),
    CONSTRAINT `foreign_category` FOREIGN KEY (`t_category_id`) REFERENCES `t_goods_category` (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)

使用SHOW CREATE TABLE语句查看数据表t_goods_category和数据表t_goods时，不仅能够查看数据表的详细建表语句，还能查看数据表的存储引擎和字符编码等信息。

8.3　修改数据表

MySQL中支持创建数据表后对数据表的表结构进行修改。本节简单介绍在创建数据表后如何对数据表进行相应的修改。


8.3.1　修改数据表名称

1．语法格式

在MySQL中修改数据表名称，语法格式如下：

ALTER TABLE 原表名 RENAME [TO] 新表名

其中，TO关键字可以省略。

2．简单示例

首先，在MySQL命令行查看goods数据库下存在的所有数据表。

mysql> USE goods;
  Database changed
  mysql> SHOW TABLES;
  +------------------+
  | Tables_in_goods  |
  +------------------+
  | t_goods          |
  | t_goods_backup   |
  | t_goods_category |
  +------------------+
  3 rows in set (0.01 sec)

可以看到，goods数据库下存在3张数据表，分别是商品信息表t_goods、商品信息备份表t_goods_backup和商品类别信息表t_goods_category。

接下来，将商品信息备份表t_goods_backup的名称修改为t_goods_tmp。

mysql> ALTER TABLE t_goods_backup RENAME TO t_goods_tmp;
  Query OK, 0 rows affected (0.12 sec)

SQL语句执行成功，此时再次查看goods数据库中存在的数据表。

mysql> SHOW TABLES;
  +------------------+
  | Tables_in_goods  |
  +------------------+
  | t_goods          |
  | t_goods_category |
  | t_goods_tmp      |
  +------------------+
  3 rows in set (0.00 sec)

可以看到，goods数据库中的t_goods_backup数据表的名称已经被修改为t_goods_tmp，并且数据表结构与t_goods数据表结构相同。


8.3.2　添加字段

1．语法格式

为数据表添加字段的语法格式如下：

ALTER TABLE 表名 ADD COLUMN 新字段名 数据类型 [NOT NULL DEFAULT 默认值]

2．简单示例

为数据表t_goods_tmp添加一个名称为t_create_time的字段，数据类型为DATETIME，默认值为NULL。

mysql> ALTER TABLE t_goods_tmp ADD COLUMN t_create_time DATETIME DEFAULT NULL;
  Query OK, 0 rows affected (0.01 sec)
  Records: 0  Duplicates: 0  Warnings: 0

SQL语句执行成功，再次查看表t_goods_tmp的表结构信息。

mysql> SHOW CREATE TABLE t_goods_tmp \G
  *************************** 1. row ***************************
         Table: t_goods_tmp
  Create Table: CREATE TABLE `t_goods_tmp` (
    `id` int(11) NOT NULL,
    `t_category_id` int(11) DEFAULT NULL,
    `t_category` varchar(30) DEFAULT NULL,
    `t_name` varchar(50) DEFAULT NULL,
    `t_price` decimal(10,2) DEFAULT NULL,
    `t_stock` int(11) DEFAULT NULL,
    `t_upper_time` datetime DEFAULT NULL,
    `t_create_time` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)

在数据表t_goods_tmp中多了一个字段t_create_time，说明为数据表t_goods_tmp添加字段成功。


8.3.3　添加字段时指定位置

MySQL中不仅支持为数据表添加字段，而且在添加字段时还能指定当前要添加的字段在数据表中的位置。添加字段时指定要添加字段的位置包括：在表的第一列添加字段和在指定字段的后面添加字段。

1．在表的第一列添加字段

语法格式如下：

ALTER TABLE 表名 ADD COLUMN 新字段名 数据类型 [NOT NULL DEFAULT 默认值] FIRST

其中，FIRST关键字指定当前要添加的字段位于当前表的第一个字段的位置。

简单示例如下：

为数据表t_goods_tmp添加修改时间字段t_update_time，数据类型为DATETIME，默认值为系统当前时间，并将字段t_update_time放在表t_goods_tmp中第一个字段的位置。

mysql> ALTER TABLE t_goods_tmp ADD COLUMN t_update_time DATETIME DEFAULT NOW() FIRST;
  Query OK, 0 rows affected (0.31 sec)
  Records: 0  Duplicates: 0  Warnings: 0

使用SHOW CREATE TABLE语句查看数据表t_goods_tmp的表结构信息。

mysql> SHOW CREATE TABLE t_goods_tmp \G
  *************************** 1. row ***************************
         Table: t_goods_tmp
  Create Table: CREATE TABLE `t_goods_tmp` (
    `t_update_time` datetime DEFAULT CURRENT_TIMESTAMP,
    `id` int(11) NOT NULL,
    `t_category_id` int(11) DEFAULT NULL,
    `t_category` varchar(30) DEFAULT NULL,
    `t_name` varchar(50) DEFAULT NULL,
    `t_price` decimal(10,2) DEFAULT NULL,
    `t_stock` int(11) DEFAULT NULL,
    `t_upper_time` datetime DEFAULT NULL,
    `t_create_time` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)

可以看到，在数据表t_goods_tmp中字段t_update_time添加成功，并位于数据表的第一个字段的位置。

2．在指定字段的后面添加字段

语法格式如下：

ALTER TABLE 表名 ADD COLUMN 新字段名 数据类型 [NOT NULL DEFAULT 默认值] AFTER 原有字段名

其中，AFTER关键字指定在数据表的原有字段名之后添加新字段。

简单示例如下：

为数据表t_goods_tmp添加区域字段t_area，数据类型为VARCHAR(100)，默认值为空字符串，并将t_area字段放在库存字段t_stock的后面。

mysql> ALTER TABLE t_goods_tmp ADD COLUMN t_area VARCHAR(100) NOT NULL DEFAULT '' AFTER t_stock;
  Query OK, 0 rows affected (0.03 sec)
  Records: 0  Duplicates: 0  Warnings: 0

SQL语句执行成功。接下来，使用SHOW CREATE TABLE语句查看数据表t_goods_tmp的表结构信息。

mysql> SHOW CREATE TABLE t_goods_tmp \G
  *************************** 1. row ***************************
         Table: t_goods_tmp
  Create Table: CREATE TABLE `t_goods_tmp` (
    `t_update_time` datetime DEFAULT CURRENT_TIMESTAMP,
    `id` int(11) NOT NULL,
    `t_category_id` int(11) DEFAULT NULL,
    `t_category` varchar(30) DEFAULT NULL,
    `t_name` varchar(50) DEFAULT NULL,
    `t_price` decimal(10,2) DEFAULT NULL,
    `t_stock` int(11) DEFAULT NULL,
    `t_area` varchar(100) NOT NULL DEFAULT '',
    `t_upper_time` datetime DEFAULT NULL,
    `t_create_time` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)

在数据表t_goods_tmp中的库存字段t_stock后面多了一个商品区域t_area字段，说明表字段添加成功。


8.3.4　修改字段名称

MySQL支持在创建数据表之后修改字段的名称。这就使开发人员或者数据库维护人员能够根据实际需要将数据库的字段名称修改为更有意义的名称。

1．语法格式

修改数据表字段名称的语法格式如下：

ALTER TABLE 表名 CHANGE 原有字段名 新字段名 新数据类型

在修改数据表字段名称时可以不修改数据类型，此时可以将新字段的数据类型设置成与原有字段的数据类型一样即可，但是新字段名称的数据类型不能为空。

2．简单示例

将数据表t_goods_tmp中的t_update_time字段的名称修改为t_last_modified，数据类型保持不变。

mysql> ALTER TABLE t_goods_tmp CHANGE t_update_time t_last_modified DATETIME;
  Query OK, 0 rows affected (0.04 sec)
  Records: 0  Duplicates: 0  Warnings: 0

SQL语句执行成功。接下来，使用SHOW CREATE TABLE语句查看数据表t_goods_tmp的表结构。

mysql> SHOW CREATE TABLE t_goods_tmp \G
  *************************** 1. row ***************************
         Table: t_goods_tmp
  Create Table: CREATE TABLE `t_goods_tmp` (
    `t_last_modified` datetime DEFAULT NULL,
    `id` int(11) NOT NULL,
    `t_category_id` int(11) DEFAULT NULL,
    `t_category` varchar(30) DEFAULT NULL,
    `t_name` varchar(50) DEFAULT NULL,
    `t_price` decimal(10,2) DEFAULT NULL,
    `t_stock` int(11) DEFAULT NULL,
    `t_area` varchar(100) NOT NULL DEFAULT '',
    `t_upper_time` datetime DEFAULT NULL,
    `t_create_time` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)

在数据表t_goods_tmp中，t_update_time字段已经被修改为t_last_modified字段。


8.3.5　修改字段的数据类型

MySQL支持将当前字段的数据类型修改成另外一种数据类型，修改数据类型也可以使用ALTER TABLE语句。

1．语法格式

修改数据类型的语法格式如下：

ALTER TABLE 表名 MODIFY 字段名 新数据类型 [DEFAULT 默认值]

使用MODIFY关键字指定字段的新数据类型即可。

2．简单示例

将数据表t_goods_tmp中的商品价格字段t_price的数据类型修改为BIGINT类型，存储价格信息时以分为单位进行存储，默认值为0。

mysql> ALTER TABLE t_goods_tmp MODIFY t_price BIGINT DEFAULT 0;
  Query OK, 0 rows affected (0.03 sec)
  Records: 0  Duplicates: 0  Warnings: 0

SQL语句执行成功。接下来使用SHOW CREATE TABLE语句查看数据表t_goods_tmp的表结构信息。

mysql> SHOW CREATE TABLE t_goods_tmp \G
  *************************** 1. row ***************************
         Table: t_goods_tmp
  Create Table: CREATE TABLE `t_goods_tmp` (
    `t_last_modified` datetime DEFAULT NULL,
    `id` int(11) NOT NULL,
    `t_category_id` int(11) DEFAULT NULL,
    `t_category` varchar(30) DEFAULT NULL,
    `t_name` varchar(50) DEFAULT NULL,
    `t_price` bigint(20) DEFAULT '0',
    `t_stock` int(11) DEFAULT NULL,
    `t_area` varchar(100) NOT NULL DEFAULT '',
    `t_upper_time` datetime DEFAULT NULL,
    `t_create_time` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)

数据表t_goods_tmp中的商品价格字段t_price的数据类型由原来的DECIMAL(10,2)类型变成了BIGINT类型，说明修改字段的数据类型成功。


8.3.6　修改字段的位置

MySQL不仅支持在添加字段时指定要添加的字段在数据表中的位置，还支持修改数据表中已经存在的字段在数据表中的位置，并且MySQL支持将字段的位置修改为数据库的第一个字段，以及将当前字段的位置修改到某个字段的后面。

1．将字段的位置修改为数据库的第一个字段

语法格式如下：

ALTER TABLE 表名 MIDIFY 字段名 数据类型 FIRST

使用FIRST关键字标识将当前字段修改为数据表的第一个字段。

简单示例：

将数据表t_goods_tmp中的id字段的位置修改为表中的第一个字段位置。

mysql> ALTER TABLE t_goods_tmp MODIFY id int(11) NOT NULL FIRST;
  Query OK, 0 rows affected, 1 warning (0.07 sec)
  Records: 0  Duplicates: 0  Warnings: 1

SQL语句执行成功，使用SHOW CREATE TABLE语句查看数据表t_goods_tmp的表结构信息。

mysql> SHOW CREATE TABLE t_goods_tmp \G
  *************************** 1. row ***************************
         Table: t_goods_tmp
  Create Table: CREATE TABLE `t_goods_tmp` (
    `id` int(11) NOT NULL,
    `t_last_modified` datetime DEFAULT NULL,
    `t_category_id` int(11) DEFAULT NULL,
    `t_category` varchar(30) DEFAULT NULL,
    `t_name` varchar(50) DEFAULT NULL,
    `t_price` bigint(20) DEFAULT '0',
    `t_stock` int(11) DEFAULT NULL,
    `t_area` varchar(100) NOT NULL DEFAULT '',
    `t_upper_time` datetime DEFAULT NULL,
    `t_create_time` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)

可以看到，此时id字段被修改为t_goods_tmp表中的第一个字段。

2．将当前字段的位置修改到某个字段的后面

语法格式如下：

ALTER TABLE 表名 MODIFY 字段1名称  字段1的数据类型 AFTER 字段2名称

将字段1移动到字段2的后面。

简单示例：

将数据表t_goods_tmp中的t_last_modified字段修改到t_create_time字段的后面。

mysql> ALTER TABLE t_goods_tmp MODIFY t_last_modified datetime DEFAULT NULL AFTER t_create_time;
  Query OK, 0 rows affected (0.13 sec)
  Records: 0  Duplicates: 0  Warnings: 0

接下来查看表t_goods_tmp的表结构。

mysql> SHOW CREATE TABLE t_goods_tmp \G
  *************************** 1. row ***************************
         Table: t_goods_tmp
  Create Table: CREATE TABLE `t_goods_tmp` (
    `id` int(11) NOT NULL,
    `t_category_id` int(11) DEFAULT NULL,
    `t_category` varchar(30) DEFAULT NULL,
    `t_name` varchar(50) DEFAULT NULL,
    `t_price` bigint(20) DEFAULT '0',
    `t_stock` int(11) DEFAULT NULL,
    `t_area` varchar(100) NOT NULL DEFAULT '',
    `t_upper_time` datetime DEFAULT NULL,
    `t_create_time` datetime DEFAULT NULL,
    `t_last_modified` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)

此时t_last_modified字段已经被修改到t_create_time字段的后面了。


8.3.7　删除字段

MySQL支持删除数据表中某个字段的操作，删除字段同样使用ALTER TABLE语句。

1．语法格式

删除表中某个字段的语法格式如下：

ALTER TABLE 表名 DROP 字段名

2．简单示例

删除数据表t_goods_tmp中的t_area字段。

mysql> ALTER TABLE t_goods_tmp DROP t_area;
  Query OK, 0 rows affected (0.03 sec)
  Records: 0  Duplicates: 0  Warnings: 0

可以看到SQL语句执行成功。接下来查看数据表t_goods_tmp的表结构。

mysql> SHOW CREATE TABLE t_goods_tmp \G
  *************************** 1. row ***************************
         Table: t_goods_tmp
  Create Table: CREATE TABLE `t_goods_tmp` (
    `id` int(11) NOT NULL,
    `t_category_id` int(11) DEFAULT NULL,
    `t_category` varchar(30) DEFAULT NULL,
    `t_name` varchar(50) DEFAULT NULL,
    `t_price` bigint(20) DEFAULT '0',
    `t_stock` int(11) DEFAULT NULL,
    `t_upper_time` datetime DEFAULT NULL,
    `t_create_time` datetime DEFAULT NULL,
    `t_last_modified` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)

数据表t_goods_tmp中的t_area字段已经被删除。


8.3.8　修改已有表的存储引擎

MySQL不仅支持在创建数据表的时候为数据表指定存储引擎，还支持修改已有表的存储引擎。

1．语法格式

指定数据表的存储引擎，语法如下：

ALTER TABLE 表名 ENGINE=存储引擎名称

“ENGINE”关键字指定数据表的存储引擎。

2．简单示例

由前面的章节可以知道，数据表t_goods_tmp使用的存储引擎为InnoDB，将其修改为MyISAM存储引擎。

mysql> ALTER TABLE t_goods_tmp ENGINE=MyISAM;
  Query OK, 0 rows affected (0.08 sec)
  Records: 0  Duplicates: 0  Warnings: 0

SQL语句执行成功。接下来查看数据表t_goods_tmp的表结构信息。

mysql> SHOW CREATE TABLE t_goods_tmp \G
  *************************** 1. row ***************************
         Table: t_goods_tmp
  Create Table: CREATE TABLE `t_goods_tmp` (
    `id` int(11) NOT NULL,
    `t_category_id` int(11) DEFAULT NULL,
    `t_category` varchar(30) DEFAULT NULL,
    `t_name` varchar(50) DEFAULT NULL,
    `t_price` bigint(20) DEFAULT '0',
    `t_stock` int(11) DEFAULT NULL,
    `t_upper_time` datetime DEFAULT NULL,
    `t_create_time` datetime DEFAULT NULL,
    `t_last_modified` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)

此时，数据表t_goods_tmp的存储引擎已经被修改为MyISAM了。

最后，将数据表t_goods_tmp的存储引擎再次修改为InnoDB。

mysql> ALTER TABLE t_goods_tmp ENGINE=InnoDB;
  Query OK, 0 rows affected (0.02 sec)
  Records: 0  Duplicates: 0  Warnings: 0


8.3.9　取消数据表的外键约束

1．语法格式

取消数据表的外键约束的语法格式如下：

ALTER TABLE 表名 DROP FOREIGN KEY 外键名

使用DROP FOREIGN KEY关键字删除表中的外键。

2．简单示例

在8.1.3节中我们为商品信息表t_goods指定了一个名称为foreign_category的外键。接下来删除外键约束。

mysql> ALTER TABLE t_goods DROP FOREIGN KEY foreign_category;
  Query OK, 0 rows affected (0.13 sec)
  Records: 0  Duplicates: 0  Warnings: 0

SQL语句执行成功，再次查看数据表t_goods的表结构信息。

mysql> SHOW CREATE TABLE t_goods \G
  *************************** 1. row ***************************
         Table: t_goods
  Create Table: CREATE TABLE `t_goods` (
    `id` int(11) NOT NULL,
    `t_category_id` int(11) DEFAULT NULL,
    `t_category` varchar(30) DEFAULT NULL,
    `t_name` varchar(50) DEFAULT NULL,
    `t_price` decimal(10,2) DEFAULT NULL,
    `t_stock` int(11) DEFAULT NULL,
    `t_upper_time` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `foreign_category` (`t_category_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)

数据表t_goods的外键约束删除成功。

8.4　删除数据表

在MySQL中删除数据表时有两种情况：删除与其他表没有关联关系的数据表，以及删除有外键约束的主表。本节简单介绍两种情况下如何删除数据表。


8.4.1　删除没有关联关系的数据表

在MySQL中，当一张数据表没有与其他任何数据表形成关联关系时，可以将当前数据表直接删除。

1．语法格式

删除没有关联关系的数据表的语法格式如下：

DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n]

2．简单示例

首先，查看商品数据库goods中的所有数据表。

mysql> SHOW TABLES;
  +------------------+
  | Tables_in_goods  |
  +------------------+
  | t_goods          |
  | t_goods_category |
  | t_goods_snapshot |
  | t_goods_tmp      |
  +------------------+
  4 rows in set (0.01 sec)

接下来，删除数据表t_goods_snapshot。

mysql> DROP TABLE t_goods_snapshot;
  Query OK, 0 rows affected (0.01 sec)

再次查看商品数据库goods中的所有数据表。

mysql> SHOW TABLES;
  +------------------+
  | Tables_in_goods  |
  +------------------+
  | t_goods          |
  | t_goods_category |
  | t_goods_tmp      |
  +------------------+
  3 rows in set (0.00 sec)

数据表t_goods_snapshot已经被成功删除。

当数据库中不存在要删除的数据表时，使用“DROP TABLE 表名”的语法删除数据表时MySQL会报错。例如，再次删除商品数据库goods中的t_goods_snapshot数据表。

mysql> DROP TABLE t_goods_snapshot;
  ERROR 1051 (42S02): Unknown table 'goods.t_goods_snapshot'

由于商品数据库goods中已经不存在t_goods_snapshot数据表，所以MySQL抛出未知的数据表错误。此时可以使用“DROP TABLE IF EXISTS 表名”的语法使MySQL不再抛出错误信息。

mysql> DROP TABLE IF EXISTS t_goods_snapshot;
  Query OK, 0 rows affected, 0 warning (0.00 sec)

IF EXISTS的含义为：如果当前数据库中存在相应的数据表，则删除数据表；如果当前数据库中不存在相应的数据表，则忽略删除语句，不再执行删除数据表的操作。


8.4.2　删除有外键约束的主表

删除有外键约束的主表时，如果直接删除主表，MySQL会报错。此时，有两种方式删除有外键约束的主表，一种方式是先删除有外键约束的从表，再删除主表；另一种方式为先解除外键约束，再删除主表。

在大多数情况下，删除有外键约束的主表时需要保留从表，所以本节先简单介绍如何解除外键约束，然后再删除主表。另一种方式是先删除从表，再删除主表，这种方式比较简单，读者只需要按照顺序先删除从表，再删除主表即可。

在8.1.3节中，数据表t_goods_category与数据表t_goods具有外键约束，并且t_goods_category为主表，t_goods为从表。此时，直接删除主表t_goods_category，MySQL会抛出错误信息。

mysql> DROP TABLE t_goods_category;
  ERROR 3730 (HY000): Cannot drop table 't_goods_category' referenced by a foreign key constraint 
  'foreign_category' on table 't_goods'.

可以看到，直接删除主表t_goods_category时，MySQL会抛出“不能删除表t_goods_category，在t_goods中存在外键约束”。

接下来按照8.3.9节中的介绍，取消数据表t_goods_category与数据表t_goods的外键约束。

再次执行删除主表t_goods_category的SQL语句如下：

mysql> DROP TABLE t_goods_category;                          
  Query OK, 0 rows affected (0.00 sec)

SQL语句执行成功，再次查看商品数据库goods中的数据表。

mysql> SHOW TABLES;
  +-----------------+
  | Tables_in_goods |
  +-----------------+
  | t_goods         |
  | t_goods_tmp     |
  +-----------------+
  2 rows in set (0.00 sec)

数据表t_goods_category已经被成功删除。

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MySQL 数据库
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入