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

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

图片

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 表的主键字段。这三个表的结构关系图如下:

图片

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)

发表评论

登录后才能评论
网站客服
网站客服
申请收录 侵权处理
分享本页
返回顶部