DDL(Data Defination Language)是数据定于语言的缩写。简单来说 , 就是对数据库内部的对象进行创建、删除、修改等操作的语言。它和DML语言的最大区别是DML只是对表内部数据操作 , 而不涉及表的定义 , 结构的修改, 更不会涉及其他对象。DDL语句更多的由数据库管理员(DBA)使用 , 开发人员一般很少使用。例, CREATE、DROP 、ALTER。
本节主要介绍DDL中的"增删改查" , 对应关键字 :
增 :
Create删 :
Drop/Truncate改 :
Alter + add/drop/modify/change查 :
Show / Desc
1. 创建表
直接创建
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options] CREATE TABLE [IF NOT EXISTS] ‘tbl_name’ (col1 type1 修饰符, col2 type2 修饰符, ...) #字段信息 col type1 PRIMARY KEY(col1,...) INDEX(col1, ...) UNIQUE KEY(col1, ...) #表选项 : ENGINE [=] engine_name ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}create_definitioncreate_definition: col_name column_definition | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ... | {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ... | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ... | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ... | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition | CHECK (expr) column_definition: data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}] [STORAGE {DISK|MEMORY|DEFAULT}] [reference_definition] reference_definition: REFERENCES tbl_name (index_col_name,...) [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION table_optionstable_options: table_option [[,] table_option] ... table_option: ENGINE [=] engine_name | AUTO_INCREMENT [=] value | AVG_ROW_LENGTH [=] value | [DEFAULT] CHARACTER SET [=] charset_name | CHECKSUM [=] {0 | 1} | [DEFAULT] COLLATE [=] collation_name | COMMENT [=] 'string' | CONNECTION [=] 'connect_string' | DATA DIRECTORY [=] 'absolute path to directory' | DELAY_KEY_WRITE [=] {0 | 1} | INDEX DIRECTORY [=] 'absolute path to directory' | INSERT_METHOD [=] { NO | FIRST | LAST } | KEY_BLOCK_SIZE [=] value | MAX_ROWS [=] value | MIN_ROWS [=] value | PACK_KEYS [=] {0 | 1 | DEFAULT} | PASSWORD [=] 'string' | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}] | UNION [=] (tbl_name[,tbl_name]...)partition_optionspartition_options: PARTITION BY { [LINEAR] HASH(expr) | [LINEAR] KEY(column_list) | RANGE{(expr) | COLUMNS(column_list)} | LIST{(expr) | COLUMNS(column_list)} } [PARTITIONS num] [SUBPARTITION BY { [LINEAR] HASH(expr) | [LINEAR] KEY(column_list) } [SUBPARTITIONS num] ] [(partition_definition [, partition_definition] ...)] partition_definition: PARTITION partition_name [VALUES {LESS THAN {(expr | value_list) | MAXVALUE} | IN (value_list)}] [[STORAGE] ENGINE [=] engine_name] [COMMENT [=] 'comment_text' ] [DATA DIRECTORY [=] 'data_dir'] [INDEX DIRECTORY [=] 'index_dir'] [MAX_ROWS [=] max_number_of_rows] [MIN_ROWS [=] min_number_of_rows] [TABLESPACE [=] tablespace_name] [NODEGROUP [=] node_group_id] [(subpartition_definition [, subpartition_definition] ...)] subpartition_definition: SUBPARTITION logical_name [[STORAGE] ENGINE [=] engine_name] [COMMENT [=] 'comment_text' ] [DATA DIRECTORY [=] 'data_dir'] [INDEX DIRECTORY [=] 'index_dir'] [MAX_ROWS [=] max_number_of_rows] [MIN_ROWS [=] min_number_of_rows] [TABLESPACE [=] tablespace_name] [NODEGROUP [=] node_group_id]注意
Storage Engine是指表类型 , 也即在表创建时指明其使用的存储引擎同一库中不同表可以使用不同的存储引擎
同一个库中表建议要使用同一种存储引擎类型
范例 : 创建表
MariaDB [test]> CREATE TABLE student ( -> id int UNSIGNED AUTO_INCREMENT PRIMARY KEY , -> name VARCHAR(20) NOT NULL , -> age tinyint UNSIGNED, -> gender ENUM('M','F') default 'M' -> )ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.07 sec) MariaDB [test]> DESC student; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | gender | enum('M','F') | YES | | M | | +--------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) MariaDB [test]> insert student (name,age)values('xiaoming',20); Query OK, 1 row affected (0.01 sec) MariaDB [test]> select * from student; +----+----------+------+--------+ | id | name | age | gender | +----+----------+------+--------+ | 10 | xiaoming | 20 | M | +----+----------+------+--------+ 1 row in set (0.00 sec) MariaDB [test]> insert student (name,age,gender)values('xiaohong',18,'f'); Query OK, 1 row affected (0.02 sec) MariaDB [test]> select * from student; +----+----------+------+--------+ | id | name | age | gender | +----+----------+------+--------+ | 10 | xiaoming | 20 | M | | 11 | xiaohong | 18 | F | +----+----------+------+--------+ 2 rows in set (0.00 sec)范例:
auto_increment属性MariaDB [test]> SHOW VARIABLES LIKE 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set (0.00 sec) #设置步长 MariaDB [test]> SET @@auto_increment_increment=10; Query OK, 0 rows affected (0.00 sec) #设置起始值 MariaDB [test]> SET @@auto_increment_offset=3; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> SHOW VARIABLES LIKE 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 10 | | auto_increment_offset | 3 | +--------------------------+-------+ 2 rows in set (0.00 sec) MariaDB [test]> CREATE TABLE autoinc1 (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY); Query OK, 0 rows affected (0.03 sec) MariaDB [test]> INSERT INTO autoinc1 VALUES (NULL),(NULL),(NULL),(NULL); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [test]> SELECT col from autoinc1; +-----+ | col | +-----+ | 3 | | 13 | | 23 | | 33 | +-----+ 4 rows in set (0.00 sec)通过查询现存表创建 ; 新表会被直接插入查询而来的数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [partition_options] select_statement select_statement: [IGNORE | REPLACE] [AS] SELECT ... (Some valid select statement)范例 :
MariaDB [test]> create table user select user,host,password from mysql.user; Query OK, 3 rows affected (0.15 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [test]> show tables; +----------------+ | Tables_in_test | +----------------+ | autoinc1 | | student | | t1 | | user | +----------------+ 4 rows in set (0.00 sec) MariaDB [test]> desc user; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | user | char(16) | NO | | | | | host | char(60) | NO | | | | | password | char(41) | NO | | | | +----------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) MariaDB [mysql]> select user,host,password from mysql.user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | localhost | *9092E6448E8E10E8BAC1C290E16D0D2E36759BAA | | root | 127.0.0.1 | *9092E6448E8E10E8BAC1C290E16D0D2E36759BAA | | root | ::1 | *9092E6448E8E10E8BAC1C290E16D0D2E36759BAA | +------+-----------+-------------------------------------------+ 3 rows in set (0.00 sec) MariaDB [test]> select * from user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | localhost | *9092E6448E8E10E8BAC1C290E16D0D2E36759BAA | | root | 127.0.0.1 | *9092E6448E8E10E8BAC1C290E16D0D2E36759BAA | | root | ::1 | *9092E6448E8E10E8BAC1C290E16D0D2E36759BAA | +------+-----------+-------------------------------------------+ 3 rows in set (0.00 sec)通过复制现存的表的表结构创建 , 但不复制数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }范例 :
MariaDB [test]> desc student; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | gender | enum('M','F') | YES | | M | | +--------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) MariaDB [test]> create table teacher like student; Query OK, 0 rows affected (0.04 sec) MariaDB [test]> desc teacher; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | gender | enum('M','F') | YES | | M | | +--------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
1.1 约束条件
约束条件: 一种限制 , 用于限制表中的数据 , 为了保证表中的数据的准确和可靠性。MySQL数据库通过约束(constraints)防止无效的数据进入到表中 , 以保护数据的实体完整性。
在MySQL中 , 主要有六种约束:
NOT NULL: 非空约束 ,用于约束该字段的值不能为空。比如姓名、学号等。DEFAULT: 默认值约束 , 用于约束该字段有默认值 , 约束当数据表中某个字段不输入值时 , 自动为其添加一个已经设置好的值。比如性别。PRIMARY KEY: 主键约束 , 用于约束该字段的值具有唯一性 , 至多有一个 , 可以没有 , 并且非空。比如学号、员工编号等。UNIQUEL 唯一约束 , 用于约束该字段的值具有唯一性 , 可以有多个 , 可以没有 , 可以为空。比如座位号。CHECK: 检查约束 , 用来检查数据表中 , 字段值是否有效。比如年龄、性别。FOREIGN KEY: 外键约束 , 外键约束经常和主键约束一起使用 , 用来确保数据的一致性 , 用于限制两个表的关系 , 用于保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束 , 用于引用主表中某列的值。比如学生表的专业编号 , 员工表的部门编号 , 员工表的工种编号。
主要归类为列级约束和表级约束
实例
列约束 : 在字段名和类型后面追加约束类型即可
表约束 : 在各个列字段的最下面 , CONSTRAINT 开头进行约束
CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
)创建表时的约束
-- 列约束
mysql> CREATE TABLE teachers(
-> id INT PRIMARY KEY,
-> stuName VARCHAR(20) NOT NULL UNIQUE,
-> gender CHAR(1) CHECK(gender='W' OR gender='M'),
-> seat INT UNIQUE,
-> age INT DEFAULT 18
-> );
Query OK, 0 rows affected (0.04 sec)
-- 列约束和表约束
mysql> CREATE TABLE students (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(20) NOT NULL,
-> age INT NOT NULL DEFAULT 18,
-> gender CHAR CHECK(gender IN ('W','M')),
-> tid INT,
-> CONSTRAINT fk FOREIGN KEY(tid) REFERENCES teachers(id)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> show index from teachers;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| teachers | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| teachers | 0 | stuName | 1 | stuName | A | 0 | NULL | NULL | | BTREE | | |
| teachers | 0 | seat | 1 | seat | A | 0 | NULL | NULL | YES | BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
mysql> show index from students;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| students | 1 | fk | 1 | tid | A | 0 | NULL | NULL | YES | BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
修改表时修改约束
mysql> CREATE TABLE info (
-> id INT,
-> name VARCHAR(20),
-> age INT,
-> gender CHAR,
-> tid INT
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> ALTER TABLE info MODIFY COLUMN id INT PRIMARY KEY; # 添加列主键约束
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE info MODIFY COLUMN name VARCHAR(20) NOT NULL; # 添加列非空约束
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE info MODIFY COLUMN age INT DEFAULT 18; # 添加列默认约束
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE info MODIFY COLUMN tid INT UNIQUE; # 添加列唯一约束
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| age | int(11) | YES | | 18 | |
| gender | char(1) | YES | | NULL | |
| tid | int(11) | YES | UNI | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table info drop primary key; # 删除列主键约束
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE info ADD PRIMARY KEY(id); # 添加“表”主键约束
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE info ADD UNIQUE(tid); # 添加“表”唯一约束
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> ALTER TABLE info ADD CONSTRAINT ue UNIQUE(tid); # 添加“表”唯一约束
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> desc info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| age | int(11) | YES | | 18 | |
| gender | char(1) | YES | | NULL | |
| tid | int(11) | YES | UNI | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> ALTER TABLE info ADD CONSTRAINT ck CHECK(gender IN ('W','M')); # 添加“表”检查约束
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE info ADD CONSTRAINT fy FOREIGN KEY(tid) REFERENCES teachers(id); # 添加“表”外键约束
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0修改表时删除约束
mysql> ALTER TABLE info MODIFY COLUMN name varchar(20) NULL; # 删除列非空约束
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE info MODIFY COLUMN age INT(11); # 删除列默认约束
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE info DROP PRIMARY KEY; # 删除“表”主键约束
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE info DROP INDEX tid; # 删除“表”唯一约束
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table info \G
*************************** 1. row ***************************
Table: info
Create Table: CREATE TABLE `info` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`gender` char(1) DEFAULT NULL,
`tid` int(11) DEFAULT NULL,
UNIQUE KEY `tid_2` (`tid`),
UNIQUE KEY `ue` (`tid`),
CONSTRAINT `fy` FOREIGN KEY (`tid`) REFERENCES `teachers` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)
mysql> ALTER TABLE info DROP INDEX ue; # 删除“表”唯一约束
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE info DROP FOREIGN KEY fy; # 删除“表”外键约束
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table info \G
*************************** 1. row ***************************
Table: info
Create Table: CREATE TABLE `info` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`gender` char(1) DEFAULT NULL,
`tid` int(11) DEFAULT NULL,
UNIQUE KEY `tid_2` (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)
mysql> desc info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| tid | int(11) | YES | UNI | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)主键和唯一的区别
1.2 CHECK约束
对于任何应用 , 都对数据的正确性有要求。比如 , 用户的年龄必须是大于零的 , 用户的登录名中不能包含空格 , 用户的密码必须满足一定的复杂度 ,等等。
对于这些要求,虽然我们可以在应用界面来对用户输入的数据进行验证 , 但是这并不能替代数据库层面的数据验证。这能增加应用的安全性。
MySQL 提供了 CHECK 约束来保证存储到表中的数据是符合你的要求的。不符合 CHECK 约束的数据会被拒绝。
请注意 , 直到 MySQL 8.0.16 , MySQL 才真正的支持 CHECK 约束。在更早的版本中 , 您只能通过触发器或者带有 WITH CHECK OPTION 的视图来模拟 CHECK 约束。
1.2.1 CHECK语法
下面是 MySQL CHECK 的语法:
CHECK(expr)这里 , expr 是一个布尔表达式 , 此表达式针对一行的数据进行计算。如果返回为真 , 则MySQL 允许此行插入到表中 , 否则MySQL拒绝此行插入到表中并给出错误。
可以在 CREATE TABLE 语句中的列定义或者约束定义中使用 CHECK 约束或者在 ALTER TABLE 语句 中添加 CHECK 约束。
如果你在列定义中使用 CHECK 约束 , 则 CHECK 表达式只能引用此列。
如果你在使用独立的 CHECK 约束 , 则 CHECK 表达式可以应用表上的所有列。
下面以age列需要大于0为例 , 使用不同的方法添加此约束:
在创建表的时候在列定义中使用
CHECK约束CREATE TABLE user ( id INT AUTO_INCREMENT PRIMARY KEY, age INT NOT NULL CHECK(age > 0) );在创建表的时候在表上定义
CHECK约束CREATE TABLE user ( id INT AUTO_INCREMENT PRIMARY KEY, age INT NOT NULL, CONSTRAINT CHECK(age > 0) );使用修改表语句的为表添加
CHECK约束ALTER TABLE user ADD CONSTRAINT CHECK(age > 0);
1.2.2 CHECK约束实例
通过下面的例子 , 你会很容易理解 MySQL CHECK 约束的用法和作用。
假设 , 您需要一个表存储用户的名称 、登录名、密码 , 且需要符合以下要求 :
用户的名称不能为空。
登录名的长度不少于 4 个字符。
密码的长度不少于 8 个字符。
密码不能和登录名相同。
您可以使用以下的 CREATE TABLE 语句创建表:
CREATE TABLE user (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(45) NOT NULL,
login_name VARCHAR(45) NOT NULL CHECK(length(login_name) >= 4),
password VARCHAR(45) NOT NULL CHECK(length(password) >= 8),
CONSTRAINT CHECK(login_name <> password)
);这里 , 在 CREATE TABLE 语句中有 3 个 CHECK 约束 :
在
login_name列定义中的CHECK(length(login_name) >= 4)保证登录名的长度不小于 4。在
password列定义中的CHECK(length(password) >= 8)保证登录名的长度不小于 8。在表上的约束
CONSTRAINT CHECK(login_name <> password)保证密码不能和登录名相同。
您可以通过以下 SHOW CREATE TABLE 语句查看表 user 上的约束:
SHOW CREATE TABLE user\G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`login_name` varchar(45) NOT NULL,
`password` varchar(45) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `user_chk_1` CHECK ((length(`login_name`) >= 4)),
CONSTRAINT `user_chk_2` CHECK ((length(`password`) >= 8)),
CONSTRAINT `user_chk_3` CHECK ((`login_name` <> `password`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci您能够在上面的输出中发现, user 表中有 3 个 CHECK 约束。约束的名称都是 MySQL 按默认规则生成的。
注意, name 列的NOT NULL 也是一种特殊的约束。
要验证有关登录名长度的 CHECK 约束是否生效 , 请使用下面的 INSERT 语句尝试插入一行:
INSERT INTO user (name, login_name, password)
VALUES ('Tim', 'tim', 'timisok');由于上面语句中给出的登录名 tim 的长度小于 4 , 因此 MySQL 会给出以下错误:
ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.要验证有关密码长度的 CHECK 约束是否生效 , 请使用下面的 INSERT 语句尝试插入一行:
INSERT INTO user (name, login_name, password)
VALUES ('Tim', 'tim1', 'timisok');由于上面语句中给出的密码 timisok 的长度小于 8 , 因此 MySQL 会给出以下错误:
ERROR 3819 (HY000): Check constraint 'user_chk_2' is violated.要验证密码不能和登录名相同的 CHECK 约束是否生效 , 请使用下面的 INSERT 语句尝试插入一行:
INSERT INTO user (name, login_name, password)
VALUES ('Tim', 'timisgood', 'timisgood');由于上面语句中给出的登录名和密码都是 timisgood , 因此 MySQL 会给出以下错误:
ERROR 3819 (HY000): Check constraint 'user_chk_3' is violated.您可以使用下面的语句插入一个完全符合 CHECK 约束的行。
INSERT INTO user (name, login_name, password)
VALUES ('Tim', 'hitim', 'timisgood');这一行成功插入到了 user 表中。
1.3 生成列
1.3.1 什么是生成列
在 MySQL 中 , 生成列(GENERATED COLUMN)是一个特殊的列 , 它的值会根据列定义中的表达式自动计算得出。并且 , 你不能直接写入或更新生成列的值。
生成列有 2 种类型 :
虚拟生成列: 列值不会被存储下来。当读取该列时,
MySQL自动计算该列的值。存储生成列: 当插入或修改数据时 ,
MySQL自动计算该列的值并存储在磁盘上。
1.3.2 生成列语法
要创建生成列 , 请使用以下语法 :
col_name data_type
[GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED]
[NOT NULL | NULL]
[UNIQUE [KEY]]
[[PRIMARY] KEY]
[COMMENT 'string']请注意第二行的 [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED] :
GENERATED ALWAYS关键字指示此列是一个生成列。它是可选的。AS (expr)设置此生成列的表达式。VIRTUAL或STORED关键字指示了是否存储列值。这是可选的。VIRTUAL: 列值不会被存储。当读取该列时 , MySQL 自动计算该列的值。它是默认值。STORED: 当插入或修改数据时 , MySQL 自动计算该列的值并存储下来。
你可以在通过 CREATE TABLE语句创建表时定义生成列 , 或者通过ALTER TABLE 语句添加一个生成列。
1.3.3 生成列实例
假设 , 你有个订单明细表 , 具有如下结构 :
CREATE TABLE order_item (
order_item_id INT AUTO_INCREMENT PRIMARY KEY,
goods VARCHAR(45) NOT NULL,
price DECIMAL NOT NULL,
quantity INT NOT NULL
);让我们插入几行测试数据 :
INSERT INTO order_item (goods, price, quantity)
VALUES ('Apple', 5, 3), ('Peach', 4, 4);让我们看一下 order_item 表中的所有数据 :
SELECT * FROM order_item;+----------+-------+-------+----------+
| order_id | goods | price | quantity |
+----------+-------+-------+----------+
| 1 | Apple | 5 | 3 |
| 2 | Peach | 4 | 4 |
+----------+-------+-------+----------+
2 rows in set (0.00 sec)现在 , 我们使用如下 SQL 查询每个订单项目的总金额 :
SELECT
goods,
price,
quantity,
(price * quantity) AS total_amount
FROM order_item;这里 , 我们通过 price * quantity 计算总金额 , 并使用了别名 total_amount。
+-------+-------+----------+--------------+
| goods | price | quantity | total_amount |
+-------+-------+----------+--------------+
| Apple | 5 | 3 | 15 |
| Peach | 4 | 4 | 16 |
+-------+-------+----------+--------------+
2 rows in set (0.00 sec)毫无疑问 , 这是正确的。
MySQL 生成列可以简化我们的工作 , 让你不用写这么复杂的 SELECT 语句。现在我们要通过以下语句添加一个生成列:
ALTER TABLE order_item
ADD COLUMN total_amount DECIMAL
GENERATED ALWAYS AS (price * quantity) STORED;这里 , 我们添加了一个 total_amount 列。它是一个生成列 , 它的计算表达式是 price * quantity。
现在看一下 order_item 的结构 :
DESC order_item;+--------------+---------------+------+-----+---------+------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+------------------+
| order_id | int | NO | PRI | NULL | auto_increment |
| goods | varchar(45) | NO | | NULL | |
| price | decimal(10,0) | NO | | NULL | |
| quantity | int | NO | | NULL | |
| total_amount | decimal(10,0) | YES | | NULL | STORED GENERATED |
+--------------+---------------+------+-----+---------+------------------+在这里 , 我们发现 , total_amount 是一个存储的生成列。
我们通过以语句查询表中的数据 :
SELECT
goods,
price,
quantity,
total_amount
FROM order_item;+-------+-------+----------+--------------+
| goods | price | quantity | total_amount |
+-------+-------+----------+--------------+
| Apple | 5 | 3 | 15 |
| Peach | 4 | 4 | 16 |
+-------+-------+----------+--------------+
2 rows in set (0.00 sec)1.3.4 更新生成列
你不能直接写入或者更新生成列的值。这会引发错误。我们尝试一下 ,看看究竟会发生什么。
让我们先试着插入一个带有生成列值的数据 :
INSERT INTO order_item (goods, price, quantity, total_amount)
VALUES ('Banana', 6, 4, 24);这返回了错误 : ERROR 3105 (HY000): The value specified for generated column 'total_amount' in table 'order_item' is not allowed.。
让我们再尝试修改生成列的值:
UPDATE order_item
SET total_amount = 30
WHERE goods = 'Apple';这返回了相同的错误 : ERROR 3105 (HY000): The value specified for generated column 'total_amount' in table 'order_item' is not allowed.。
1.3.5 VIRTUAL vs STORED
生成列有两种类型 : VIRTUAL 和 STORED。他们之间有些不同 :
虚拟生成列不需要存储空间 ; 存储生成列需要存储空间。
虚拟生成列的值在每次读操作的时候都会被重新计算 ; 存储生成列的值在插入行或者修改行的时候被计算。
如果数据经常发生变动 , 请考虑使用虚拟生成列 ; 如果数据在创建后不经常变动 , 请考虑使用存储生成列。
像上面的例子中 , 一个订单被创建后一般不会再改动 , 这里很适合使用存储生成列。
1.4 自增列
在MySQL中 , 如果需要一个列的值为一个有序的整数序列 , 请使用自增列。
自增列是MySQL中的一个特殊的列 , 该列的值可由MySQL服务器自动生成 , 并且是一个按升序增长的正整数序列。自增列能够被用来为表的新行产生唯一的标识。
1.4.1 语法
要定义一个自增列 , 请使用 AUTO_INCREMENT 关键字。定义自增列的语法如下 :
column_name data_type AUTO_INCREMENT
...这里 :
AUTO_INCREMENT关键字标识该列是一个自增列。自增列不能使用全部的数据类型 , 它只适用于整数或者浮点数类型 , 包括 :
TINYINT,SMALLINT,INT,MEDIUMINT,BIGINT,DECIMAL,FLOAT,DOUBLE。自增列必须是主键或者唯一键。
自增列默认是
NOT NULL的。每个表中只能有一个自增列。
自增列的初始值是 1。每插入一行到表中 , 该列的值自动增加 1。你可以在创建表的时候设置自增列的初始值 , 也可以修改自增列的值。
不像生成列 , 在插入新行时你可以为自增列指定一个值。
删除一些行后 , 删除的自增列的值不能重用。
1.4.2 自增列实例
让我们看一些实际的例子来了解自增列的用法。
以下语句创建一个名为 user 的表 , 该表的 user_id 是自增列 , 也是主键。
CREATE TABLE `user` (
`user_id` INT AUTO_INCREMENT,
`name` VARCHAR(45),
PRIMARY KEY (`user_id`));在
user表中插入两行数据 :INSERT INTO `user` (`name`) VALUES ('Tim'), ('Jim');注意 , 在这个语句中 , 我们并没有使用
user_id列。这个列的值会自动生成。现在 , 让我们看一下
user表中的行 :SELECT * FROM `user`; +---------+------+ | user_id | name | +---------+------+ | 1 | Tim | | 2 | Jim | +---------+------+ 2 rows in set (0.00 sec)这里 ,
MySQL为user_id列自动生成了两个连续的值 :1和2。删除
user_id是2的行 :DELETE FROM `user` WHERE `user_id` = 2;插入一个新行 :
INSERT INTO `user` (`name`) VALUES ('Jim');现在 , 让我们看一下
user表中的行 :SELECT * FROM `user`; +---------+------+ | user_id | name | +---------+------+ | 1 | Tim | | 3 | Jim | +---------+------+ 2 rows in set (0.00 sec)注意 : 新行的
user_id不是2而是3。这是因为MySQL不会重用删除的序列值。让我们插入数据时为自动列指定一个值。
INSERT INTO `user` (`user_id`, `name`) VALUES (20, 'Tom');然后 , 插入一行数据 :
INSERT INTO `user` (`name`) VALUES ('Lucy');现在 , 让我们看一下
user表中的行 :SELECT * FROM `user`; +---------+------+ | user_id | name | +---------+------+ | 1 | Tim | | 3 | Jim | | 20 | Tom | | 21 | Lucy | +---------+------+ 4 rows in set (0.00 sec)
1.4.3 如何查看自增列的值
要查看自增列的下一个值 , 可采用如下两种方法 :
从
SHOW CREATE TABLE语句输出的表定义中查看自增列的值。SHOW CREATE TABLE `user`\G*************************** 1. row *************************** Table: user Create Table: CREATE TABLE `user` ( `user_id` int NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, PRIMARY KEY (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci通过最后一行中的
AUTO_INCREMENT=22代表了表中自增列的下一个值是22。如果自增列的下一个值是
1, 则不会输出AUTO_INCREMENT=1。从数据库信息表中查询自增列的值:
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'testdb' AND TABLE_NAME = 'user';+----------------+ | AUTO_INCREMENT | +----------------+ | 22 | +----------------+ 1 row in set (0.00 sec)这种方式有时候并不准确。因为当重置或修改了表的
AUTO_INCREMENT值之后 , 这里并不会立刻更新。
1.4.4 自定自增列初始值
自增列的初始值默认是 1 , 但我们可以在创建表的时候设置一个自定义的值。如下:
CREATE TABLE `user` (
`user_id` INT AUTO_INCREMENT,
`name` VARCHAR(45),
PRIMARY KEY (`user_id`)
) AUTO_INCREMENT = 10;这里 , 我们创建了一个表 , 该表的自增列的初始值被指定为 10。
1.4.5 修改表的自增值
如果想重置或者修改表的自增值 , 可以使用 ALTER TABLE 语句修改表定义。如下 :
ALTER TABLE `user` AUTO_INCREMENT = 30;注意 :
如果这里设置的值小于等于表中自增列的最大值 , 最终设置的值还是这个最大值的下一个数值。
比如 , 在
user表中 , 我们修改AUTO_INCREMENT为1, 但user表的AUTO_INCREMENT的值还是22。当修改了表的
AUTO_INCREMENT值之后 ,INFORMATION_SCHEMA.TABLES表中的AUTO_INCREMENT列并不会立刻更新。你可以使用SHOW CREATE TABLE语句查看。
1.5 外键约束
MySQL外键约束(FOREIGN KEY)是表的一个特殊字段 , 经常与主键约束一起使用。对于两个具有关联关系的表而言 , 相关联字段中主键所在的表就是主表（父表） , 外键所在的表就是从表（子表）。
外键用来建立主表与从表的关联关系 , 为两个表的数据建立连接 , 约束两个表中数据的一致性和完整性。比如 , 一个水果摊 , 只有苹果、桃子、李子、西瓜等 4 种水果 , 那么 , 你来到水果摊要买水果就只能选择苹果、桃子、李子和西瓜 , 其它的水果都是不能购买的。
主表删除某条记录时 , 从表中与之对应的记录也必须有相应的改变。一个表可以有一个或多个外键 , 外键可以为空值 , 若不为空值 , 则每一个外键的值必须等于主表中主键的某个值。
定义外键时 , 需要遵守下列规则 :
主表必须已经存在于数据库中 , 或者是当前正在创建的表。如果是后一种情况 , 则主表与从表是同一个表 , 这样的表称为自参照表 , 这种结构称为自参照完整性。
必须为主表定义主键。
主键不能包含空值 , 但允许在外键中出现空值。也就是说 , 只要外键的每个非空值出现在指定的主键中 , 这个外键的内容就是正确的。
在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
外键中列的数目必须和主表的主键中列的数目相同。
外键中列的数据类型必须和主表主键中对应列的数据类型相同。
有外键约束的表 , 必须是
innodb引擎 , 不支持MyISAM引擎。在引用表中 , 必须有一个索引 , 外键列以同样的顺序被列在其中作为第一列。这样一个索引如果不存在 ,它必须在引用表里被自动创建。
在引用表中 ,必须有一个索引 , 被引用的列以同样的顺序被列在其中作为第一列。
不支持对外键列的索引前缀。这样的后果之一是BLOB和TEXT列不被包括在一个外键中 ,这是因为对这些列的索引必须总是包含一个前缀长度。
mysql外键的名字[CONSTRAINT symbol]在数据库内要是全局唯一的 , 如果它没有被给出 ,InnoDB自动创建这个名字。
1.5.1 在创建表时设置外键约束
在CREATE TABLE语句中 , 通过 FOREIGN KEY 关键字来指定外键 , 具体的语法格式如下:
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [ , 字段名2 , …]
REFERENCES <主表名> 主键列1 [ , 主键列2 , …]
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]外键维护数据完整性的5种方式
CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都可用。在两个表之间 , 你不应定义若干在父表或子表中的同一列采取动作的ON UPDATE CASCADE子句。SET NULL: 从父表删除或更新行 , 并设置子表中的外键列为NULL。如果外键列没有指定NOT NULL限定词 , 这就是唯一合法的。ON DELETE SET NULL和ON UPDATE SET NULL子句被支持。NO ACTION: 无动作 , 默认值。在ANSI SQL-92标准中 ,NO ACTION意味这不采取动作 , 就是如果有一个相关的外键值在被参考的表里 , 删除或更新主要键值的企图不被允许进行(Gruber, 掌握SQL,2000:181）。InnoDB拒绝对父表的删除或更新操作。RESTRICT: 拒绝对父表的删除或更新操作。NO ACTION和RESTRICT都一样 , 删除ON DELETE或ON UPDATE子句。(一些数据库系统有延期检查 , 并且NO ACTION是一个延期检查。在MySQL中 , 外键约束是被立即检查的 , 所以NO ACTION和RESTRICT是同样的)。SET DEFAULT: 这个动作被解析程序识别 , 但InnoDB拒绝包含ON DELETE SET DEFAULT或ON UPDATE SET DEFAULT子句的表定义。
该语法可以在CREATE TABLE和ALTER TABLE时使用 , 如果不指定CONSTRAINT symbol , MYSQL会自动生成一个名字。指定约束名称可以方便维护。
示例
alter table permission_role add constraint permission_role_role_id_foreign foreign key(role_id) REFERENCES roles(id) ON DELETE CASCADE ON UPDATE CASCADE;如果不指定约束名称(constraint permission_role_role_id_foreign) , 那么mysql会自动给新建的这个外键设置一个名称 , 规则是:[table_name]_ibfk_[index]。
其中ibfk是ib (InnoDB)fk(foreign key) 的缩写。比如permission_role_ibfk_1 , permission_role_ibfk_2 。
例 1
为了展现表与表之间的外键关系 , 本例在test_db数据库中创建一个部门表tb_dept1 , 表结构如下表所示。
创建tb_dept1的SQL语句和运行结果如下所示。
mysql> CREATE TABLE tb_dept1
-> (
-> id INT(11) PRIMARY KEY,
-> name VARCHAR(22) NOT NULL,
-> location VARCHAR(50)
-> );
Query OK, 0 rows affected (0.37 sec)创建数据表tb_emp6 , 并在表tb_emp6上创建外键约束 , 让它的键deptId作为外键关联到表tb_dept1的主键 id, SQL 语句和运行结果如下所示。
mysql> CREATE TABLE tb_emp6
-> (
-> id INT(11) PRIMARY KEY,
-> name VARCHAR(25),
-> deptId INT(11),
-> salary FLOAT,
-> CONSTRAINT fk_emp_dept1
-> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
-> );
Query OK, 0 rows affected (0.37 sec)
mysql> DESC tb_emp6;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(25) | YES | | NULL | |
| deptId | int(11) | YES | MUL | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (1.33 sec)以上语句执行成功之后 , 在表tb_emp6上添加了名称为fk_emp_dept1 的外键约束 , 外键名称为deptId , 其依赖于表tb_dept1的主键 id。
注意 : 从表的外键关联的必须是主表的主键 , 且主键和外键的数据类型必须一致。例如 , 两者都是
INT类型 , 或者都是CHAR类型。如果不满足这样的要求 , 在创建从表时 , 就会出现“ERROR 1005(HY000): Can't create table”错误。
1.5.2 在修改表时添加外键约束
外键约束也可以在修改表时添加 , 但是添加外键约束的前提是 : 从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。
在修改数据表时添加外键约束的语法格式如下:
ALTER TABLE <数据表名> ADD CONSTRAINT <外键名>
FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);例 2
修改数据表tb_emp2 , 将字段deptId设置为外键 , 与数据表tb_dept1的主键 id 进行关联 , SQL 语句和运行结果如下所示。
mysql> ALTER TABLE tb_emp2
-> ADD CONSTRAINT fk_tb_dept1
-> FOREIGN KEY(deptId)
-> REFERENCES tb_dept1(id);
Query OK, 0 rows affected (1.38 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE tb_emp2\G
*************************** 1. row ***************************
Table: tb_emp2
Create Table: CREATE TABLE `tb_emp2` (
`id` int(11) NOT NULL,
`name` varchar(30) DEFAULT NULL,
`deptId` int(11) DEFAULT NULL,
`salary` float DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_tb_dept1` (`deptId`),
CONSTRAINT `fk_tb_dept1` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.12 sec)注意 : 在为已经创建好的数据表添加外键约束时 , 要确保添加外键约束的列的值全部来源于主键列 , 并且外键列不能为空。
1.5.3 删除外键约束
当一个表中不需要外键约束时 , 就需要从表中将其删除。外键一旦删除 , 就会解除主表和从表间的关联关系。
删除外键约束的语法格式如下所示:
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;例 3
删除数据表tb_emp2 中的外键约束fk_tb_dept1 , SQL 语句和运行结果如下所示。
mysql> ALTER TABLE tb_emp2
-> DROP FOREIGN KEY fk_tb_dept1;
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE tb_emp2\G
*************************** 1. row ***************************
Table: tb_emp2
Create Table: CREATE TABLE `tb_emp2` (
`id` int(11) NOT NULL,
`name` varchar(30) DEFAULT NULL,
`deptId` int(11) DEFAULT NULL,
`salary` float DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_tb_dept1` (`deptId`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)可以看到 , tb_emp2中已经不存在FOREIGN KEY , 原有的名称为fk_emp_dept的外键约束删除成功。
1.5.4 FOREIGN KEY实例
使用SQL 创建user表
CREATE TABLE `user` (
`user_id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL, PRIMARY KEY (`user_id`));1.5.4.1 CASCADE 策略
如果外键的 ON DELETE 和 ON UPDATE 使用了 CASCADE 策略 :
当父表的行被删除的时候 , 子表中匹配的行也会被删除。
当父表的行的键值更新的时候 , 子表中匹配的行的字段也会被更新。
使用以下SQL创建 user_hobby 表 , 它的外键采用 CASCADE 策略。
DROP TABLE IF EXISTS user_hobby;
CREATE TABLE `user_hobby` (
`hobby_id` INT NOT NULL AUTO_INCREMENT,
`user_id` INT NOT NULL,
`hobby` VARCHAR(45) NOT NULL,
PRIMARY KEY (`hobby_id`),
CONSTRAINT `fk_user`
FOREIGN KEY (`user_id`)
REFERENCES `user` (`user_id`)
ON DELETE CASCADE
ON UPDATE CASCADE);
向两个表中插入数据 :
DELETE FROM user;
DELETE FROM user_hobby;
INSERT INTO user (user_id, name)
VALUES (1, "Tim");
INSERT INTO user_hobby (hobby_id, user_id, hobby)
VALUES (1, 1, 'Football'), (2, 1, 'Swimming');此时 user_hobby 表中的数据 :
+----------+---------+----------+
| hobby_id | user_id | hobby |
+----------+---------+----------+
| 1 | 1 | Football |
| 2 | 1 | Swimming |
+----------+---------+----------+让我们看一下对父表进行 UPDATE 和 DELETE 操作引起的子表的关联操作 :
对父表进行
UPDATE操作我们将父表
user中的键user_id的值从1修改为100:UPDATE user SET user_id = 100 WHERE user_id = 1;此时
user_hobby表中的数据 :+----------+---------+----------+ | hobby_id | user_id | hobby | +----------+---------+----------+ | 1 | 100 | Football | | 2 | 100 | Swimming | +----------+---------+----------+ 2 rows in set (0.00 sec)我们发现 ,
user_hobby表中与user表中user_id = 1匹配的的那些行的列的值也被修改为100。对父表进行
DELETE操作DELETE FROM user WHERE user_id = 100;此时
user_hobby表中的数据 :Empty set (0.00 sec)我们发现 ,
user_hobby表中与user表中user_id = 100匹配的那些行都被删除了。
1.5.4.2 RESTRICT策略
如果外键的 ON DELETE 和 ON UPDATE 使用了 RESTRICT 策略 :
MySQL禁止删除父表中与子表匹配的行。MySQL禁止删除父表中与子表匹配的行的键的值。
使用以下 SQL 创建 user_hobby 表 , 它的外键采用 RESTRICT 策略。
DROP TABLE IF EXISTS user_hobby;
CREATE TABLE `user_hobby` (
`hobby_id` INT NOT NULL AUTO_INCREMENT,
`user_id` INT NOT NULL,
`hobby` VARCHAR(45) NOT NULL,
PRIMARY KEY (`hobby_id`),
CONSTRAINT `fk_user`
FOREIGN KEY (`user_id`)
REFERENCES `user` (`user_id`)
ON DELETE RESTRICT
ON UPDATE RESTRICT);向两个表中插入数据 :
DELETE FROM user;
DELETE FROM user_hobby;
INSERT INTO user (user_id, name)
VALUES (1, "Tim");
INSERT INTO user_hobby (hobby_id, user_id, hobby)
VALUES (1, 1, 'Football'), (2, 1, 'Swimming');此时 user_hobby 表中的数据 :
+----------+---------+----------+
| hobby_id | user_id | hobby |
+----------+---------+----------+
| 1 | 1 | Football |
| 2 | 1 | Swimming |
+----------+---------+----------+让我们看一下对父表进行 UPDATE 和 DELETE 操作的结果 :
对父表进行
UPDATE操作UPDATE user SET user_id = 100 WHERE user_id = 1;MySQL 服务器返回了如下的错误 :
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (testdb.user_hobby, CONSTRAINTfk_userFOREIGN KEY (user_id) REFERENCESuser(user_id) ON DELETE RESTRICT ON UPDATE RESTRICT)对父表进行
DELETE操作DELETE FROM user WHERE user_id = 1;MySQL 服务器返回了如下的错误 :
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (testdb.user_hobby, CONSTRAINTfk_userFOREIGN KEY (user_id) REFERENCESuser(user_id) ON DELETE RESTRICT ON UPDATE RESTRICT)
1.5.4.3 SET NULL策略
如果外键的 ON DELETE 和 ON UPDATE 使用了 SET NULL 策略 :
当父表的行被删除的时候 , 子表中匹配的行的列的值被设置为
NULL。当父表的行的键值被更新的时候 , 子表中匹配的行的列的值被设置为
NULL。
使用以下 SQL 创建 user_hobby 表 , 它的外键采用 SET NULL 策略。
DROP TABLE IF EXISTS user_hobby;
CREATE TABLE `user_hobby` (
`hobby_id` INT NOT NULL AUTO_INCREMENT,
`user_id` INT,
`hobby` VARCHAR(45) NOT NULL,
PRIMARY KEY (`hobby_id`),
CONSTRAINT `fk_user`
FOREIGN KEY (`user_id`)
REFERENCES `user` (`user_id`)
ON DELETE SET NULL
ON UPDATE SET NULL);向两个表中插入数据 :
DELETE FROM user;
DELETE FROM user_hobby;
INSERT INTO user (user_id, name)
VALUES (1, "Tim");
INSERT INTO user_hobby (hobby_id, user_id, hobby)
VALUES (1, 1, 'Football'), (2, 1, 'Swimming');让我们看一下对父表进行 UPDATE 和 DELETE 操作引起的子表的关联操作 :
对父表进行
UPDATE操作UPDATE user SET user_id = 100 WHERE user_id = 1;此时
user_hobby表中的数据 :+----------+---------+----------+ | hobby_id | user_id | hobby | +----------+---------+----------+ | 1 | NULL | Football | | 2 | NULL | Swimming | +----------+---------+----------+ 2 rows in set (0.00 sec)更新父表中的
user_id = 1的行的user_id列的值后 ,user_hobby表中那些user_id = 1的行的user_id列的值被设置为NULL。对父表进行
DELETE操作由于上面实例将表的数据修改了 , 我们重新初始化两个表的数据 :
DELETE FROM user; DELETE FROM user_hobby; INSERT INTO user (user_id, name) VALUES (1, "Tim"); INSERT INTO user_hobby (hobby_id, user_id, hobby) VALUES (1, 1, 'Football'), (2, 1, 'Swimming');DELETE FROM user WHERE user_id = 1;此时
user_hobby表中的数据 :+----------+---------+----------+ | hobby_id | user_id | hobby | +----------+---------+----------+ | 1 | NULL | Football | | 2 | NULL | Swimming | +----------+---------+----------+ 2 rows in set (0.00 sec)删除父表中的
user_id = 1的行后 ,user_hobby表中那些user_id = 1的行的user_id列的值被设置为NULL。
1.5.5 子引用外键
有时 , 子表和父表可能是同一个表。这种表中的外键被称为自引用外键。
通常 , 自引用外键定义在表示树形数据结构的表中。比如一个代表分类的表 :
CREATE TABLE category (
category_id INT AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(45),
parent_category_id INT,
CONSTRAINT fk_category FOREIGN KEY (parent_category_id)
REFERENCES category (category_id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);在这个表中 , parent_category_id 列是一个外键。它引用了 category 表的 category_id 列。
这个表实现了一个无限层级的分类树。一个分类可以有多个子分类 , 一个子分类可以有 0 个或者 1 个父类 ;
1.5.6 禁用外键检查
有时禁用外键检查非常有用 , 例如 , 当您将CSV文件中的数据导入表格时。如果不禁用外键检查 , 则必须将数据加载到正确的顺序 , 即必须先将数据加载到父表中 , 然后再加载子表 , 这可能很繁琐。但是 , 如果禁用外键检查 , 则可以按任何顺序加载数据。
另一个示例是 , 除非禁用外键检查 , 否则不能删除外键约束引用的表。删除表时 , 也会删除为表定义的任何约束。
要禁用外键检查 , 请使用以下语句 :
SET foreign_key_checks = 0; 当然 , 您可以使用以下语句启用它 :
SET foreign_key_checks = 1; MariaDB [testdb]> SHOW VARIABLES LIKE 'foreign_key_checks';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| foreign_key_checks | ON |
+--------------------+-------+
1 row in set (0.001 sec)2. 表查看
查看支持的
engine类型SHOW ENGINES;查看表:
SHOW TABLES [FROM db_name];范例
MariaDB [test]> SHOW TABLES; +----------------+ | Tables_in_test | +----------------+ | autoinc1 | | student | | t1 | | teacher | | user | +----------------+ 5 rows in set (0.00 sec)查看表结构:
DESC [db_name.]tb_name SHOW COLUMNS FROM [db_name.]tb_name范例:
MariaDB [test]> desc student; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | gender | enum('M','F') | YES | | M | | +--------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) MariaDB [test]> SHOW COLUMNS FROM student; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | gender | enum('M','F') | YES | | M | | +--------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)查看表创建命令
SHOW CREATE TABLE tbl_name范例:
MariaDB [test]> show create table student; +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | student | CREATE TABLE `student` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `age` tinyint(3) unsigned DEFAULT NULL, `gender` enum('M','F') DEFAULT 'M', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 | +---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)查看表状态
SHOW TABLE STATUS LIKE 'tbl_name';范例:
MariaDB [test]> SHOW TABLE STATUS LIKE 'student'\G *************************** 1. row *************************** Name: student Engine: InnoDB Version: 10 Row_format: Compact Rows: 2 Avg_row_length: 8192 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 9437184 Auto_increment: 12 Create_time: 2022-02-24 17:13:27 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)查看库中所有表状态
SHOW TABLE STATUS FROM db_name范例:
MariaDB [test]> SHOW TABLE STATUS FROM test \G *************************** 1. row *************************** Name: autoinc1 Engine: InnoDB Version: 10 Row_format: Compact Rows: 4 Avg_row_length: 4096 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 9437184 Auto_increment: 43 Create_time: 2022-02-24 17:35:21 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: *************************** 2. row *************************** Name: student Engine: InnoDB Version: 10 Row_format: Compact Rows: 2 Avg_row_length: 8192 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 9437184 Auto_increment: 12 Create_time: 2022-02-24 17:13:27 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: *************************** 3. row *************************** Name: t1 Engine: InnoDB Version: 10 Row_format: Compact Rows: 2 Avg_row_length: 8192 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 9437184 Auto_increment: 4294967296 Create_time: 2022-02-24 15:03:21 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: *************************** 4. row *************************** Name: teacher Engine: InnoDB Version: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 9437184 Auto_increment: 1 Create_time: 2022-02-24 18:00:04 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: *************************** 5. row *************************** Name: user Engine: InnoDB Version: 10 Row_format: Compact Rows: 3 Avg_row_length: 5461 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 9437184 Auto_increment: NULL Create_time: 2022-02-24 17:49:30 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 5 rows in set (0.00 sec)
3. 修改和删除表
删除表
DROP TABLE [IF EXISTS] 'tbl_name'修改表。通过
ALTER TABLE语句 , 您可以重命名表、重命名列、添加列、删除列、修改列的属性等。ALTER TABLE table_name [alter_action options], ...ADD关键字可用来添加列、索引、约束等 , 包括:ADD [COLUMN]: 添加列ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ] ADD [COLUMN] (col_name column_definition,...)ADD INDEX: 添加索引ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ...ADD PRIMARY KEY: 添加主键ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ...ADD FOREIGN KEY: 添加外键ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definitionADD UNIQUE INDEX: 添加唯一索引ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ...ADD CHECK: 添加检查约束
DROP关键字可用来删除列、索引、约束等 , 包括 :DROP [COLUMN] col_name: 删除列DROP INDEX index_name: 删除索引DROP PRIMARY KEY: 删除主键DROP FOREIGN KEY fk_symbol: 删除外键
MODIFY关键字用来修改列的定义。与CHANGE关键字不同 , 它不能重命名列。例如:MODIFY [COLUMN] col_name column_definition。CHANGE关键字用来修改列的定义。与MODIFY关键字不同 , 它可以重命名列。例如:CHANGE [COLUMN] old_col_name new_col_name column_definition。ALTER关键字用来修改字段默认值。ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}其他调整命令。
ALTER TABLE table_name MAX_ROWS = rows #设置表格最大行数 ALTER TABLE table_name RENAME [TO|AS] new_tbl_name #修改原表名称 ALTER TABLE table_name ORDER BY col_name [, col_name] ... #表格中指定按照col_name列排序 ALTER TABLE table_name CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] #将字符类型修改为指定的字符名称 ALTER TABLE table_name [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name #设置表格默认的字符类型 #修改表时删除非空约束 ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NULL;
查看修改表帮助
HELP ALTER TABLE范例
#将students 表名修改为s1 ALTER TABLE students RENAME s1; #在name列后添加一列phone ALTER TABLE s1 ADD phone varchar(11) AFTER name; MariaDB [test]> desc s1; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | gender | enum('M','F') | YES | | M | | +--------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) MariaDB [test]> ALTER TABLE s1 ADD phone varchar(11) AFTER name; Query OK, 2 rows affected (0.06 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [test]> desc s1; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | phone | varchar(11) | YES | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | gender | enum('M','F') | YES | | M | | +--------+---------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) #将phone列的存储类型修改为int ALTER TABLE s1 MODIFY phone int; MariaDB [test]> ALTER TABLE s1 MODIFY phone int; Query OK, 2 rows affected (0.08 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [test]> desc s1; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | phone | int(11) | YES | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | gender | enum('M','F') | YES | | M | | +--------+---------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) #将表中phone列名修改为mobile,存储类型修改为char(11) ALTER TABLE s1 CHANGE COLUMN phone mobile char(11); MariaDB [test]> ALTER TABLE s1 CHANGE COLUMN phone mobile char(11); Query OK, 2 rows affected (0.06 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [test]> desc s1; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | mobile | char(11) | YES | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | gender | enum('M','F') | YES | | M | | +--------+---------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) #删除表中mobile列 ALTER TABLE s1 DROP COLUMN mobile; MariaDB [test]> ALTER TABLE s1 DROP COLUMN mobile; Query OK, 2 rows affected (0.06 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [test]> desc s1; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | gender | enum('M','F') | YES | | M | | +--------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) #将表s1的存储字符集字符修改为utf8 ALTER TABLE s1 character set utf8; ALTER TABLE s1 change name name varchar(20) character set utf8; ALTER TABLE students ADD gender ENUM('m','f'); #将id修改为sid,并修改该列的属性 ALTER TABLE students CHANGE id sid INT UNSIGNED NOT NULL PRIMARY KEY; #删除students 中age列; ALTER TABLE students DROP age; #查看表students 的表结构 DESC students; #新建表无主键,添加和删除主键 CREATE TABLE t1 SELECT * FROM students; ALTER TABLE t1 ADD PRIMARY KEY (stuid); ALTER TABLE t1 DROP PRIMARY KEY; #同时添加多列 ALTER TABLE user ADD age INT, ADD email VARCHAR(50); #修改数据表 tb_dept1 , 指定部门的名称唯一 , SQL 语句和运行结果如下所示。 mysql> ALTER TABLE tb_dept1 -> ADD CONSTRAINT unique_name UNIQUE(name); Query OK, 0 rows affected (0.63 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC tb_dept1; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(22) | NO | UNI | NULL | | | location | varchar(50) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) #删除数据表 tb_dept1 中的唯一约束 unique_name mysql> ALTER TABLE tb_dept1 -> DROP INDEX unique_name; Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC tb_dept1; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(22) | NO | | NULL | | | location | varchar(50) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
3.1 修改表名
MySQL 提供了 RENAME TABLE 语句用以重命名表。除此之外 , 您还可以使用 ALTER TABLE 语句重命名表。
在重命名一个表的时候 , 此表中不能有未完成的事务 , 并且此表不能是锁定状态。
3.1.1 重命名表的注意事项
重命名表是一个简单的动作 , 但是它可能会带来一系列的问题。如果您没有同步修改那些用到此表的代码 , 则他们可能不能正常运行。
您需要同步修改的代码可能包括 :
那些使用了此表的存储过程。
那些使用了此表的视图。
那些使用了此表的函数。
那些使用了此表的触发器。
那些使用了此表的外键约束(在较旧的 MySQL 版本中)。
那些使用了此表的应用程序。
因此 , 当您打算修改一个表名的时候 , 您需要首先从整体上进行评估。然后 , 再决定是否进行重命名表。一旦您决定了要重命名一个表 , 您需要把需要同步修改的地方整理清楚。
3.1.2 重命名表的语法
您可以按照如下的语法使用 RENAME TABLE 语句重命名一个表:
RENAME TABLE
old_table_name TO new_table_name
[, old_table_name2 TO new_table_name2];您也可以按照如下语法使用 ALTER TABLE 语句重命名表 :
ALTER TABLE old_table_name
RENAME TO new_table_name;这里 :
old_table_name是需要重命名的表。new_table_name是新的表名。
RENAME TABLE 语句和 ALTER TABLE 语句也可以用来重命名视图。
RENAME TABLE 语句和 ALTER TABLE 语句也存在一些不同 :
RENAME TABLE语句更加简洁。您可以在一个
RENAME TABLE语句中同时重命名多个表。RENAME TABLE语句不可以用来重命名临时表 , 而ALTER TABLE语句可以用来重命名临时表。
3.1.3 重命名表实例
这个实例演示了如何在 MySQL 中重命名一个表。
假设 , 我们有一个用户表 user。 因为您所在的团队制定了新的命名规则 , 所有的实体表需要以复数形式命名 , 因此您需要将 user 表重命名为 users。
我们使用以下语句在 testdb 数据库中 创建一个 user 表 以实践我们的实例:
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`age` int NOT NULL DEFAULT '20',
`email` varchar(255) NOT NULL,
`phone` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);如果您没有 testdb 数据库 , 请先使用如下语句创建数据库并选择数据库 :
CREATE DATABASE testdb;
use testdb;创建表后 , 您可以使用 SHOW TABLES 语句查看表是否创建成功 :
SHOW TABLES LIKE 'user%';+--------------------------+
| Tables_in_testdb (user%) |
+--------------------------+
| user |
+--------------------------+要将 user 表重命名为 users , 请运行以下语句 :
RENAME TABLE user TO users;创建表后 , 您可以使用 SHOW TABLES 语句查看表 :
SHOW TABLES LIKE 'user%';+--------------------------+
| Tables_in_testdb (user%) |
+--------------------------+
| users |
+--------------------------+现在 , 我们看到 testdb 数据库中已经没有了 user 表 , 而只有 users 表。
当然 , 您也可以使用 ALTER TABLE 语句将 user 表重命名为 users , 如下 :
ALTER TABLE user RENAME TO users;