Skip to main content

第 04 篇 管理表

imodouAbout 6 minMySQL

《MySQL 入门教程》第 04 篇 管理表

表（Table）是数据库存储数据的主要形式，由行（Row）和列（Column）组成，类似于常见的电子表格。

image-20230106150829194
image-20230106150829194

MySQL 中的表与其他数据库的最大区别在于它们可以使用不同的存储引擎（Storage Engine）。存储引擎是 MySQL 中用于管理、访问和修改物理数据的组件，不同的存储引擎提供了不同的功能和特性。

从 MySQL 5.5 开始，默认使用 InnoDB 存储引擎；支持事务处理（ACID）、行级锁定、故障恢复、多版本并发控制（MVCC）以及外键约束。大多数情况下，推荐使用默认的 InnoDB 存储引擎。

4.1 创建表

CREATE TABLE语句用于创建表，基本的语法如下：

CREATE TABLE [IF NOT EXISTS] table_name(
   column1 data_type column_constraint,
   column2 data_type,
   ...,
   table_constraints
) ENGINE=storage_engine;

其中，table_name 指定了新建的表名，表名在数据库中必须唯一。如果该表已经存在，系统将会提示错误；此时可以使用 IF NOT EXISTS 选项，创建之前检查是否已经存在同名的表，避免产生错误信息。

然后，在括号中指定字段的名称、数据类型以及字段约束；多个字段使用逗号进行分隔。常见的数据类型包括数字（INT、NUMERIC 等）、字符（VARCHAR、CHAR 等）以及日期时间（DATE、TIME 等）。

字段约束包括非空（NOT NULL）约束、唯一（UNIQUE）约束、主键（PRIMARY KEY）约束、外键（FOREIGN KEY）约束以及检查（CHECK）约束以及默认值（DEFAULT）。在字段定义的最后，可以定义表的约束。表级约束包括：唯一（UNIQUE）约束、主键（PRIMARY KEY）约束、外键（FOREIGN KEY）约束以及检查（CHECK）约束。

📝关于 MySQL 约束的详细介绍，可以参考这篇文章。

最后，使用 ENGINE 选项为表指定一个存储引擎。例如 InnoDB、MyISAM、MEMORY、ARCHIVE 等，不指定的话默认为 InnoDB。

📝使用 show engines; 命令可以查看当前 MySQL 数据库支持的存储引擎。

以下语句用于创建一个名为 users 的用户表：

CREATE TABLE users(
   user_id  INT AUTO_INCREMENT PRIMARY KEY,
   username VARCHAR(50) UNIQUE NOT NULL,
   password VARCHAR(50) NOT NULL,
   email    VARCHAR(256) NOT NULL,
   status   TINYINT NOT NULL,
   created_on TIMESTAMP NOT NULL,
   last_login TIMESTAMP,
   CONSTRAINT uk_email UNIQUE (email)
);

其中，user_id 是一个自动增长的整数，并且是该表的主键。AUTO_INCREMENT 表示自动增长列，插入数据时不需要提供该字段的值，MySQL 自动生成一个从 1 开始的数字序列值。每个表只能有一个自动增长的字段。

username 是一个长度可变的字符串字段，最大长度为 50；UNIQUE 定义了唯一约束，意味着 username 不能出现重复值；NOT NULL 定义非空约束，意味着 username 不能为空。

password 和 email 都是长度可变的字符串字段，不能为空。status 是一个很小的数字，TINYINT 取值范围为 -128 到 127。

created_on 和 last_login 是时间戳类型的字段，TIMESTAMP 包含了年、月、日、时、分、秒。

uk_email 是一个唯一约束，意味着 email 不能出现重复值。

执行以上语句创建 users 表之后，可以使用 DESC 命令查看表的结构：

mysql> DESC users;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| user_id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| username   | varchar(50)  | NO   | UNI | NULL    |                |
| password   | varchar(50)  | NO   |     | NULL    |                |
| email      | varchar(256) | NO   | UNI | NULL    |                |
| status     | tinyint(4)   | NO   |     | NULL    |                |
| created_on | timestamp    | NO   |     | NULL    |                |
| last_login | timestamp    | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

📝使用 show create table users; 命令可以查看 users 表的定义语句。

以下语句创建了两个新表，roles 和 user_roles：

CREATE TABLE roles(
   role_id   INT AUTO_INCREMENT PRIMARY KEY,
   role_name VARCHAR(256) UNIQUE NOT NULL
);

CREATE TABLE user_role
(
  user_id  INT NOT NULL,
  role_id  INT NOT NULL,
  grant_on TIMESTAMP NOT NULL,
  PRIMARY KEY (user_id, role_id),
  CONSTRAINT fk_user_role_userid FOREIGN KEY (user_id)
      REFERENCES users(user_id),
  CONSTRAINT fk_user_role_roleid FOREIGN KEY (role_id)
      REFERENCES roles(role_id)
);

roles 表用于定义角色，比较简单。user_role 是用户对应的角色信息；PRIMARY KEY 定义了一个复合主键，这种情况只能使用表级主键；另外，还定义了两个外键，分别引用了 users 和 roles 表的主键字段。这三个表的结构关系图如下：

image-20230106150844788
image-20230106150844788

4.2 修改表

MySQL 支持 ALTER TABLE 语句，用于修改表的定义。常见的修改操作包括增加列、修改列、重命名列、删除列以及重命名表。

4.2.1 增加列

ALTER TABLE ... ADD ... 语句用于为表增加一个新的字段：

ALTER TABLE table_name ADD column_name data_type column_constraint
    [ FIRST | AFTER other_column];

增加字段时的选项和创建表时的选项相同；FIRST 表示将新增的字段作为表的第一个字段，AFTER 表示在某个字段之后新增一个字段，默认在表的最后新增字段。

以下语句为 users 表增加一个字段 login_failed_times，表示登录密码错误次数：

ALTER TABLE users ADD login_failed_times TINYINT NOT NULL DEFAULT 0;

📝MySQL 支持一次新增多个字段，使用逗号进行分隔，与创建表时定义多个字段类似。

4.2.2 修改列

ALTER TABLE ... MODIFY ... 语句可以修改已有字段的属性：

ALTER TABLE table_name MODIFY column_name data_type column_constraint
    [ FIRST | AFTER other_column];

修改字段的选项与新增字段时相同。以下语句将 users 表的 email 字段长度修改为 512：

ALTER TABLE users MODIFY email VARCHAR(512) NOT NULL;

📝MySQL 支持一次修改多个字段，使用逗号进行分隔，与新增多个字段类似。

4.2.3 重命名列

ALTER TABLE ... RENAME ... 语句用于修改字段的名称：

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

以下语句将 users 表的 login_failed_times 字段重命名为 login_failed_number：

ALTER TABLE users RENAME COLUMN login_failed_times TO login_failed_number;

另外，ALTER TABLE ... CHANGE ... 语句可以同时修改字段的名称和属性：

ALTER TABLE table_name CHANGE COLUMN old_name new_name data_type column_constraint
    [FIRST | AFTER column_name];

4.2.4 删除列

ALTER TABLE ... DROP ... 语句用于删除表中的字段：

ALTER TABLE table_name DROP COLUMN column_name;

以下语句用于删除 users 表的 login_failed_number 字段：

ALTER TABLE users DROP COLUMN login_failed_number;

📝MySQL 支持一次删除多个字段，使用逗号进行分隔多个 DROP COLUMN 选项。

如果删除的字段上存在外键约束，需要先删除外键约束再删除字段，否则将会产生错误：

mysql> alter table user_role drop column user_id;
ERROR 1828 (HY000): Cannot drop column 'user_id': needed in a foreign key constraint 'fk_user_role_userid'

4.2.5 重命名表

ALTER TABLE ... RENAME ... 语句用于修改表的名称：

ALTER TABLE table_name RENAME [TO|AS] new_name；

以下语句将 users 表重命名为 accounts，同时将 user_role 表重命名为 account_role：

ALTER TABLE users RENAME TO accounts;
ALTER TABLE user_role RENAME AS account_role;

另外，MySQL 还支持 RENAME TABLE 语句重命名表：

RENAME TABLE table_name TO new_name;

4.3 删除表

DROP TABLE 语句用于删除已有的表，语法如下：

DROP TABLE [IF EXISTS] table_name [, table_name] ...;

DROP TABLE 将会同时删除表中的数据，删除多个表时使用逗号进行分隔；如果指定了 IF EXISTS 选项，删除一个不存在的表时不会产生错误。删除表时不会删除该表上的访问权限。

以下语句用于删除 roles 表：

mysql> drop table roles;
ERROR 3730 (HY000): Cannot drop table 'roles' referenced by a foreign key constraint 'fk_user_role_roleid' on table 'account_role'.

因为 roles 是 account_role 表的父表，无法直接删除。可以先删除 account_role，或者同时删除这两个表：

mysql> drop table roles, account_role;
Query OK, 0 rows affected (0.06 sec)
Default footer
Copyright © 2024 imodou