MySQL 或 SQL 基础知识

最后更新： 2018-01-05

阅读次数： 次

许可协议： 知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议

SQL（Structured Query Language），结构化查询语言，是一种用于操作关系数据库的语言。

声明：本文的语法以 MySQL（版本: 5.7.14） 关系数据库为标准。对于其他的关系数据库，请注意以下的区别。

• 对于不同的数据库系统，SQL 的语法可能不大一样
• 同一类型不同版本的数据库系统的 SQL 语法也可能不一样
• 同一类型同一版本不同引擎的数据库系统的 SQL 语法也可能不一样（比如 MySQL 的 MyISAM 引擎就不支持事务处理机制），所以应该在创建表的时候指定其默认的数据库引擎
• 具体的 SQL 语法标准，请以自己所使用的数据库系统对应版本的官方文档为标准

数据库的基本概念

• 数据库管理系统（Database Management System，缩写：DBMS）： 是一种针对对象数据库，为管理数据库而设计的大型电脑软件管理系统。典型的代表有 Oracle、Microsoft SQL Server、Access、MySQL 及 PostgreSQL 等。

• 数据库（database）： 指的是以一定方式储存在一起的数据集合。

• 数据库表（table）： 是一系列二维数组的集合，由行和列组成。

• 数据库模式（database schema）： 描述数据库表的特性的那一行

• 列（column）： 数据库表由一列或多列组成。列也叫字段（field）
• 行（row）： 一行表示数据库表中的一个记录，因此行也叫记录（record）

• 一个数据库管理系统下可以有多个数据库
• 一个数据库下可以有多个数据库表
• 一个数据库表下可以有多个列和多个行

SQL 语句书写约定

• 将 SQL 语句关键字统一以大写形式进行书写。虽然 SQL 的命令大小写不敏感，即 SELECT 与 select 效果一样
• 在每条 SQL 语句后都加上分号，表示结束该语句
• 每创建一个新的数据库表时，必须指定其使用的数据库引擎和使用的字符集
• 每创建一个新的数据库表时，必须为其指定一个主键
• 更新或删除数据时，一定要使用 WHERE 语句进行限制，否则会更新或删除所有的数据

SQL 的注释

-- 单行注释

/*
   多行注释
*/

SQL 的常用数据类型

• 全部数据类型看这里

《MySQL 必知必会》书中给出的数据类型的长度限制与现在的实际限制可能差距很大，比如书中说 VARCHAR 最多不超过 255 字节，而最新版本的官方文档明确说明 VARCHAR 最多不超过 65535 字节。所以具体限制以官方文档为标准。

• 字符串类型

  • CHAR：1-255 个字符的固定长度的字符串
  • VARCHAR：可变字符串，长度限制为 0-65535（2^16-1） 字符
  • TEXT：可变字符串，长度限制 0-65535 字符
  • TINYTEXT：可变字符串，长度限制 0-255（2^8-1） 字符
  • MEDIUMTEXT：可变字符串，长度限制 0-16,777,215(2^24 − 1) 字符
  • LONGTEXT：可变字符串，长度限制 0-4,294,967,295 or 4GB (2^32 − 1) 字符

• 数值类型

  • BOOLEAN（或 BOOL）：布尔标志，值为 1 或 0
  • INT：整数，范围限制 -2147483648-2147483647
  • TINYINT：整数，范围 -128-127
  • DECIMAL(M, D)：精度可变的浮点数，M 定义精度，D 定义精确度
    • The column definition follows the format DECIMAL(M, D) where M is the maximum number of digits (the precision) and D is the number of digits to the right of the decimal point (the scale).

• 时间日期类型

  • DATE：日期，表示 1000-01-01 ~ 9999-12-31 的日期，格式为 YYYY-MM-DD
  • TIME：时间，格式为 HH:MM:SS

• 二进制数据类型（与普通的字符串类型一样，有一套对应的数据类型）

  • BINARY：1-255 个字节的固定长度的字节串
  • VARBINARY：可变字节串，长度限制为 0-65535（2^16-1） 字节
  • BLOB：可变字节串，长度限制 0-65535 字节（64KB）
  • TINYBLOB：可变字节串，长度限制 0-255（2^8-1） 字节
  • MEDIUMBLOB：可变字节串，长度限制 0-16,777,215(2^24 − 1) 字节（16MB）
  • LONGBLOB：可变字节串，长度限制 0-4,294,967,295 (2^32 − 1) 字节（4GB）

二进制数据类型和普通的字符串类型本质上都是一种序列，所以他们都有一套类似的数据类型。只不过二进制数据类型针对的是字节数，而普通字符串针对的是字符数。

创建或删除数据库

-- 创建数据库
CREATE DATABASE dbname;

-- 创建数据库，并指定其默认的字符集
CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

-- 删除数据库
DROP DATABASE dbname;

-- 设置当前使用的数据库
USE dbname;

创建或操作数据库表

• 创建一个数据库表（students）
  • 数据库表的名称必须唯一
  • AUTO_INCREMENT： 只能用于主键所在的列，用来在插入新行时自动增量
  • DEFAULT： 用来声明该列的默认值
  • PRIMARY KEY： 用来指定主键所在的列
    • 主键可以由一列或几列组成
    • 每一行的主键必须是唯一的
    • 主键不允许是 NULL 值
    • 包含主键的列不允许修改或更新
  • ENGINE： 用来指定该数据库表使用的数据库引擎，MySQL 的默认引擎为 MyISAM
  • DEFAULT CHARACTER SET： 指定该数据库表使用的字符集。若不指定，则采用默认的字符集。MySQL 的默认字符集为 latin1_swedish_ci（瑞典语），若表中出现中文字符，使用该字符集会出现乱码

CREATE TABLE students_list(
    id         INT          NOT NULL  AUTO_INCREMENT,
    full_name  VARCHAR(10)  NOT NULL,
    gender     VARCHAR(10)  NULL,
    age        INT          NOT NULL  DEFAULT 20,
    PRIMARY KEY(id)
) ENGINE=InnoDB AUTO_INCREMENT=20170001 DEFAULT CHARACTER SET utf8;

-- 根据上面的设置，主键默认以 20170001 开始自增

• 创建一个新表，并将查询到的数据插入到表中
  • 这个新表中没有主键，需要额外的语句重新指定主键
  • 这个新表中使用默认的数据库引擎和字符集，除非显式地指定它

CREATE TABLE new_table AS
SELECT * FROM students WHERE gender='女';

-- 显式地指定数据库引擎和字符集
CREATE TABLE new_table ENGINE=InnoDB DEFAULT CHARACTER SET utf8 AS
SELECT * FROM students WHERE gender='女';

• 更新表（重命名、增加或删除列）

-- 重命名表
RENAME TABLE students_list TO students;    -- 方法一
ALTER TABLE students_list RENAME students; -- 方法二

-- 新增列
ALTER TABLE students ADD city VARCHAR(10);

-- 重命名列
ALTER TABLE students CHANGE COLUMN city new_city VARCHAR(20);

-- 删除列
ALTER TABLE students DROP COLUMN new_city;

• 删除表

DROP TABLE students;

插入数据 INSERT INTO

• 插入一个新行（新的记录）

-- 不指定字段，则需要插入所有字段的数据
INSERT INTO students
VALUES (20170001,'盖伦','男','24');

-- 只插入指定字段的数据
INSERT INTO students(full_name,gender,age)
VALUES ('盖伦','男','24');

• 插入多行数据

-- 方法一，重复执行上面插入一行的语句

-- 方法二
INSERT INTO students(full_name,gender,age)
VALUES
('德莱厄斯','男','25'),
('寡妇收割者','女',22),
('光辉女神','女',18),
('赵信','男',21),
('曙光女神','女','22');

• 插入查询出的数据

-- 下面的语句将 SELECT 查询出的数据插入到指定表中
INSERT INTO students(full_name,gender,age)
SELECT full_name,gender,age FROM some_table
WHERE gender='女';

查询数据 SELECT

• 查询数据，或提取数据，利用 SELECT 语句从数据库中提取出相关的数据

普通查询

• 查询一列

SELECT full_name FROM students;

• 查询多列

SELECT full_name,gender,age FROM students;

• 查询所有列

SELECT * FROM students;

• 限制查询结果的数量

-- 不设置偏移量
SELECT * FROM students LIMIT 10;

-- 设置偏移量
SELECT * FROM students LIMIT 10 OFFSET 5;

• 去掉重复的查询记录（SELECT DISTINCT）

-- 去掉 full_name 重复的查询记录
SELECT DISTINCT full_name FROM students;

为查询结果排序

-- 默认，升序（ASC）
SELECT * FROM students
ORDER BY id;

-- 降序（DESC）
SELECT * FROM students
ORDER BY id DESC;

过滤查询结果

• 最基本的操作符有：>、<、=、!=、>=、<=、BETWEEN、IS NULL、IS NOT NULL

SELECT * FROM students
WHERE age>22;

SELECT * FROM students
WHERE age=22;

SELECT * FROM students
WHERE age BETWEEN 18 AND 22;

SELECT * FROM students
WHERE gender IS NULL;

SELECT * FROM students
WHERE gender IS NOT NULL;

• 逻辑操作符：AND、OR、NOT
  • 注意 NOT 操作符的位置，它用来否定后面的条件语句

SELECT * FROM students
WHERE age=22 AND gender='女';

SELECT * FROM students
WHERE age=22 OR gender='女';

SELECT * FROM students
WHERE NOT age=22;

-- 组合条件
SELECT * FROM students
WHERE (age=22 OR gender='女') AND full_name!='曙光女神';

• IN 操作符

SELECT * FROM students
WHERE age IN (18,23,24,25);

• 使用通配符进行过滤（LIKE 语句）
  • %：匹配出现任意次数的任意字符。注意它不能匹配 NULL 值
  • _：匹配任意的单个字符
  • []：一个字符集，匹配其中的任意一个字符，[] 之间的字符好像不支持中文，因为下面的测试中未得到期望的反馈

SELECT * FROM students
WHERE full_name LIKE '%神%';

SELECT * FROM students
WHERE full_name LIKE '_辉女神';

-- 注意，下面的例子未能正确查询出数据，可能是因为 [] 字符集不支持中文吧
SELECT * FROM students
WHERE full_name LIKE '[光曙][辉光]女神';

-- LIKE 默认无视大小写
SELECT * FROM table_books WHERE book_title LIKE '%J%S%';

-- LIKE 区别大小写
SELECT * FROM table_books WHERE book_title LIKE BINARY '%J%S%';

创建计算字段

计算字段并不存在于数据库表中，它只是由 SELECT 语句运行时通过某种方式计算后创建的。（计算包括普通的算术运算，还有字符串的格式化运算等）

-- 格式化字符串，返回 'name(gender)' 格式的数据
SELECT CONCAT(full_name,'(',gender,')') FROM students;

-- 赋予计算字段新的字段名
SELECT CONCAT(full_name,'(',gender,')') AS format_name FROM students;

-- 执行算术计算
-- 假设现在有一个表，有单价 price，数量 num，我们现在需要返回它的总价
SELECT price,num,price*num AS total_price FROM product;

• 在计算字段中使用数据库系统内置函数（不同数据库系统的内置函数可能不同），下面给出的链接列出了 MySQL 官方文档的一些内置函数
  • Chapter 13 Functions and Operators： 总列表
  • String Functions
    • CONCAT(str1,str2,…)： 连接多个字符串
    • LOWER(str)： 将字符串转为小写
    • UPPER(str)： 将字符串转为大写
    • TRIM(str)： 去掉字符串首尾的空格
    • CHAR_LENGTH(str)： 返回 str 的长度（以字符进行计数）
    • LENGTH(str)： 返回 str 的长度（以字节进行计数）
  • Numeric Functions and Operators
  • Date and Time Functions
    • CURDATE()： 返回当前日期
    • CURTIME()： 返回当前时间
  • Control Flow Functions
  • Aggregate (GROUP BY) Function Descriptions： 聚集函数，对列进行某些计算，通常用来汇总数据
    • AVG()： 返回某列的平均值
    • COUNT()： 返回某列的行数
    • MAX()： 返回某列的最大值
    • MIN()： 返回某列的最小值
    • SUM()： 返回某列值之和

SELECT COUNT(*) AS item_num,
       MIN(price) AS price_min,
       MAX(price) AS price_max,
       AVG(price) AS price_avg
FROM products;

函数就不全列出来了，如有需要，自行查阅官方文档。

八月 MySQL 之常用函数总结，有兴趣的可以看看别人对常用函数的总结

分组数据

分组这个概念一下子也说不清，我们就举个例子来说明。比如我们现在想从上面表最初插入的数据中统计男女英雄各有多少人，我们就可以利用分组像下面这样写。

SELECT gender,COUNT(*) AS count
FROM students
GROUP BY gender;

• 除了聚集计算语句外，SELECT 语句中的每一列都必须在 GROUP BY 子句中给出。

• 过滤分组，使用 HAVING 语句

HAVING 语句与 WHERE 语句的语法基本相同，不同的地方是：WHERE 过滤行，HAVING 过滤分组。并且 WHERE 是在数据分组前进行过滤，而 HAVING 是在数据分组后进行过滤。

-- 统计一个表中的同一商品的购买次数
-- 过滤出购买次数大于等于 2 次的分组
SELECT product_id,COUNT(*) AS count_buy
FROM sell_list
GROUP BY product_id
HAVING COUNT(*) >= 2

SELECT 子句的顺序

• SELECT…FROM…WHERE…GROUP BY…HAVING…ORDER BY…

SELECT id,COUNT(*) AS count
FROM table
WHERE id>1
GROUP BY id
HAVING COUNT(*)>2
ORDER BY count;

子查询

子查询（subquery）， 即嵌套在其它查询中的查询。简单讲就是，从一个查询结果中进行二次查询。

• 作为子查询的 SELECT 语句只能查询单个列。查询多个列将报错。

SELECT * FROM books
WHERE book_id
IN (SELECT book_id FROM sell_books);

联结表

联结（join）是一种机制，用来在一条 SELECT 语句中关联多个表，并且可以联结多个表返回一组数据。

SELECT id,name,score
FROM students,scores
WHERE students.id = scores.id;

-- 上面的语句等价于下面的语句

SELECT id,name,score
FROM students
INNER JOIN scores
ON students.id = scores.id

-- 赋予表新的别名
SELECT id,name,score
FROM students AS std
INNER JOIN scores AS sco
ON std.id = sco.id

• 联结有以下几类
  • 内联结（inner join）： 上面例子的联结就是内联结
  • 自联结（self-join）： 在 SELECT 语句中多次引用相同的表的联结。也可以使用子查询来代替自联结，具体用哪一种，请去测试一下两者的性能。
  • 外联结（outer join）： 这种联结包含了那些在相关表中没有关联行的行。

联结这一块儿是有点难以理解，那就看看别人的解释喽~ A Visual Explanation of SQL Joins

组合查询

组合查询就是利用 UNION 操作符将多个 SELECT 语句组合成一个结果集，然后返回。

• 使用组合查询的规则
  • 必须有两条或两条以上的 SELECT 语句，并使用 UNION 操作符进行连接
  • UNION 中的每个查询必须包含相同的列、表达式、或聚集函数
  • UNION 中的每个查询的列的数据类型必须兼容

-- 默认情况下，UNION 之后重复的行会自动去重
SELECT name,age,phone_number FROM club1
UNION
SELECT name,age,phone_number FROM club2

-- 使用 UNION ALL 返回所有的匹配行
SELECT name,age,phone_number FROM club1
UNION ALL
SELECT name,age,phone_number FROM club2

更新数据 UPDATE

• 更新数据是按列进行更新的

-- 更新一条信息
UPDATE students
SET full_name='诺克萨斯之手'
WHERE id=20170002;

-- 更新多条信息
UPDATE students
SET
full_name='诺克萨斯之手',
gender='女',
age=2
WHERE id=20170002;

-- 基于原来的数据更新一条信息
UPDATE students
SET age=age+9
WHERE id=20170002;

删除数据 DELETE

• 删除数据是按行进行删除的

DELETE FROM students
WHERE id=20170003;

使用视图

视图是虚拟的表，它与包含数据的数据库表不一样，视图只包含使用时检索数据的查询。视图提供了一种封装 SELECT 语句的层次，可以用来简化数据处理、重新格式化数据或保护基础数据。

通俗地讲，视图就是某个 SELECT 语句查询后的结果集。

• 使用视图的好处

  • 重用 SQL 语句
  • 简化复杂的 SQL 操作。这个本质上也是通过重用 SQL 语句来简化代码复杂度
  • 使用表的一部分，而不是整个表
  • 保护数据。可以授权访问表的一部分，而不是整个表

• 创建或删除视图

-- 创建视图
CREATE VIEW myfirst_view AS
SELECT full_name,age,gender FROM students
WHERE gender = '女';

-- 删除视图
DROP VIEW myfirst_view;

-- 创建视图以后，就可以直接通过 SELECT 语句进行访问了
SELECT * FROM myfirst_view;

使用存储过程

存储过程就是为以后使用而保存的一条或多条 SQL 语句。类似于批处理文件。

通俗地讲，存储过程就是一种函数，所以存储过程名后需要有 ()。

• 使用存储过程的好处

  • 通过把处理封装在一个易用的单元里，从而简化复杂的操作
  • 提高性能，因为使用存储过程比使用单独的 SQL 语句要快

• 创建和删除存储过程（无参数的存储过程）

  • DELIMITER 语句用在命令行下书写存储过程

-- DELIMITER 定义新的语句结束分隔符为 //
DELIMITER //

-- 创建存储过程
CREATE PROCEDURE test()
BEGIN
    SELECT AVG(age) FROM students;
END //

-- DELIMITER 定义语句结束分隔符为最初的分隔符 ;
DELIMITER ;

-- 调用存储过程
CALL test();

-- 删除存储过程
DROP PROCEDURE test;

-- 仅当存在时才删除存储过程
DROP PROCEDURE IF EXISTS test;

• 带参数的存储过程

一般，存储过程并不会像上面一样直接返回结果，而是把结果返回给你指定的变量中。

所有 MySQL 变量都必须以 @ 开始。

• MySQL 支持以下的 3 种参数
  • IN：传递参数给存储过程
  • OUT：从存储过程传出参数
  • INOUT：对存储过程传入或传出

-- 创建存储过程
CREATE PROCEDURE productpricing(
    IN num INT,
    OUT total_price DECIMAL(8,2)
)
BEGIN
    SELECT price*num FROM products
    INTO total_price
END;

-- 调用存储过程
CALL productpricing(100, @total);

-- 使用存储过程传回的变量
SELECT @total;

逻辑良好的存储过程写起来比一般的 SQL 语句复杂的多，所以这里不详细探讨存储过程了。

我偷懒了，不详细总结了，看别人的吧~ mysql 存储过程详细教程

使用游标

不想看那些乱七八糟的解释。只要记住，游标（cursor）类似于 C 语言里文件操作的文件指针（文件指针用来记录当前文件读取到哪里了，而游标用来记录当前数据库数据读取到哪里了）。

MySQL 中的游标只能用于存储过程中。

CREATE PROCEDURE something()
BEGIN

    -- 创建游标
    DECLARE book_cursor CURSOR
    FOR
    SELECT * FROM table_books
    WHERE book_title LIKE 'J%';

    -- 打开游标
    OPEN CURSOR book_cursor;

    -- FETCH 语句访问游标当前指定的数据
    FETCH book_cursor INTO record;

    -- 从 FETCH 中读取数据
    SELECT * FROM record;

    -- 关闭游标
    CLOSE CURSOR;

END;

事务处理

事务处理是一种机制，用来管理必须成批执行的 MySQL 操作，它规定所有的 MySQL 操作要么全部执行，要么都不执行，从而保证数据库不包含不完整的操作结果。

• 几个事务处理常见的术语
  • 事务（transaction）： 指一组 SQL 语句
  • 回退（rollback）： 指撤销指定 SQL 语句的过程
  • 提交（commit）： 指将未存储的 SQL 语句结果写入到数据库表
  • 保留点（savepoint）： 指事务处理中设置的临时占位符，你可以对它发布回退（这个回退与回退整个事务处理不同）

在开始测试之前，记得查看你现在使用的数据库引擎是哪个引擎，支不支持事务处理（如 InnoDB 引擎支持事务处理，而 MyISAM 引擎却不支持事务处理）

事务处理只能回退 INSERT、DELETE、UPDATE 语句，不能回退 CREATE、DROP、SELECT 语句。

当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭。

/* 下面的事务处理只会删除 id 为 20170001 所在的行 */

-- 更改提交的默认行为，下面语句表示不允许自动提交
SET autocommit=0;

-- 标识事务的开始
START TRANSACTION;

    -- 删除 id 为 20170001 所在的行
    DELETE FROM students
    WHERE id=20170001;

    -- 声明保留点
    SAVEPOINT notDelete;

    -- 删除 id 为 20170002 所在的行
    DELETE FROM students
    WHERE id=20170002;

    -- 回退（或撤销）到保留点
    ROLLBACK TO notDelete;

-- 提交事务，事务关闭
COMMIT;

触发器

触发器是 MySQL 响应任意的 DELETE、INSERT、UPDATE 语句而自动执行的一条 MySQL 语句。（打个比方，触发器类似于 JavaScript 事件处理中的回调函数）

在 MySQL 中，除过上面的三种语句，其他的语句都不支持触发器

• 创建一个触发器的 4 个条件

  • 唯一的触发器名
  • 指定触发器关联的数据库表
  • 指定触发器应该响应的活动（DELETE、INSERT 或 UPDATE）
  • 指定触发器何时执行（处理之前或之后）

-- 下面的触发器会在插入数据前，先将名字改为 'trigger' 然后再插入表中

-- 创建触发器
CREATE TRIGGER first_trigger
BEFORE INSERT ON students
FOR EACH ROW
SET NEW.full_name='trigger';

-- 插入数据
INSERT INTO students(full_name,age,gender)
VALUES('法外狂徒',26,'男');

-- 删除触发器
DROP TRIGGER first_trigger;

• 只有表才支持触发器，视图和临时表都不支持触发器
• 触发器按每个表每个事件每次地定义，每个表每次只允许一个触发器。因此，每个表最多支持 6 个触发器（每条 INSERT、DELETE、UPDATE 语句之前或之后）

• INSERT 触发器

  • INSERT 触发器会在 INSERT 语句执行之前或之后执行。
  • 在 INSERT 触发器的代码内，可引用一个名为 NEW 的虚拟表（这个表是被插入的新行）
  • 在 BEFORE INSERT 触发器中，NEW 中的值可以被更新（就像上面的例子一样）

• DELETE 触发器

  • DELETE 触发器会在 DELETE 语句执行之前或之后执行。
  • 在 DELETE 触发器的代码内，可引用一个名为 OLD 的虚拟表（这个表是被删除的行）

• UPDATE 触发器

  • UPDATE 触发器会在 UPDATE 语句执行之前或之后执行。
  • 在 UPDATE 触发器的代码内，可引用一个名为 OLD 的虚拟表（这个表是被更新前的行），也可以引用一个名为 NEW 的虚拟表（这个表是被更新后的行）
  • 在 BEFORE UPDATE 触发器中，NEW 中的值可以被更新

• 触发器的实际用途

  • 触发器可用来检查数据的一致性，比如在插入或更新数据之前进行检查

约束

通常我们用一个键来建立从一个表到另一个表的引用，约束（constraint）正是用来保证引用的完整性（术语：引用完整性，referential integrity）。

主键

主键是一种特殊的约束，用来保证一列或一组列中的值是唯一的，而且永不改动。

• 使用 PRIMARY KEY 定义主键

• 任意列，只要满足下面的条件，都可以成为主键

  • 主键可以由一列或几列组成
  • 每一行的主键必须是唯一的
  • 主键不允许是 NULL 值
  • 包含主键的列不允许修改或更新

外键

外键是表中的一列，其值必须列在另一表的主键中。外键是保证引用完整性的极其重要部分。

• 使用 REFERENCES 定义外键

-- Customers 表：存储顾客信息（主键是 user_id）
--  Orders   表：存储顾客的订单信息（主键是 order_id）
-- 约束：Orders 表中的 user_id 必须是有效的，即必须存在于 Customers 的 user_id 中

-- 定义外键
CREATE TABLE Orders(
  order_id    INT  NOT NULL,
  order_date  DATE NOT NULL,
  user_id     INT  NOT NULL,
  PRIMARY KEY (order_id),
  FOREIGN KEY (order_id) REFERENCES Customers(user_id)
);

在定义外键后，DBMS 不允许删除在另一个表中具有关联行的行。比如：不能删除有关联订单的顾客，删除该顾客的唯一方法是先删除相关的订单。因此利用外键可以防止意外删除数据。

唯一约束

唯一约束用来保证一列或一组列中的数据是唯一的。

• 唯一约束类似于主键，但又存在以下区别

  • 表中可以有多个唯一约束，但只能有一个主键
  • 唯一约束列可包含 NULL 值
  • 唯一约束列可修改或更新
  • 唯一约束不能用来定义外键

• 使用 UNIQUE 定义唯一约束

-- 定义唯一约束
CREATE TABLE qingong_books(
    book_id     INT           NOT NULL AUTO_INCREMENT,
    book_title  VARCHAR(100)  NOT NULL,
    book_author VARCHAR(100)  NOT NULL,
    book_type   VARCHAR(50)   NOT NULL,
    book_cover  VARCHAR(1000) NOT NULL UNIQUE,
    rest_number INT           NOT NULL,
    PRIMARY KEY(book_id),
    UNIQUE(book_title, book_author)
)ENGINE=InnoDB AUTO_INCREMENT=20170001 DEFAULT CHARACTER SET utf8;

检查约束

注意：检查约束在 MySQL 中会被自动忽略。（下面的解释引用自 CHECK constraint in MySQL is not working）
The CHECK clause is parsed but ignored by all storage engines.

检查约束用来保证一列或一组列中的数据满足一组指定的条件。

• 检查约束的常见用途有以下几点

  • 检查最小或最大值 （比如防止出现零个物品的订单）
  • 指定范围 （比如保证发货日期大于等于今日下订单的日期，但不超过下订单的日期起 7 天后的日期）
  • 只允许特定的值 （比如性别字段只允许 M 或 F）

• 使用 CHECK 定义检查约束

CREATE TABLE students_check(
    id         INT          NOT NULL  AUTO_INCREMENT,
    full_name  VARCHAR(10)  NOT NULL  CHECK(LENGTH(full_name)>0),
    gender     VARCHAR(10)  NULL,
    age        INT          NOT NULL  DEFAULT 20,
    PRIMARY KEY(id),
    CHECK(gender IN ('男','女') AND age>0)
) ENGINE=InnoDB AUTO_INCREMENT=20170001 DEFAULT CHARACTER SET utf8;

---

差不多就到这里了。内容有点多~~

参考资料

• 【书】《SQL 必知必会》
• 【书】《MySQL 必知必会》