天天看点
返回

SQL语句大全

参考网址

https://www.lintcode.com/

LEVEL1

(1)输出 Hello SQL：

SELECT "Hello SQL!";

(2)使用 SELECT DISTINCT 查询不同行：

SELECT DISTINCT column_name　FROM table_name；

(3)使用 SELECT WHERE 对行进行筛选过滤：常用的有等于 =、小于 < 、大于 > 、不等于<> 或 !=

(4)使用 INSERT INTO 在不指定列的情况下插入数据:

INSERT INTO `table_name` 
  VALUES (value1, value2, value3,...);

(5)使用 INSERT INTO 在指定的列中插入数据:

INSERT INTO `table_name`
  (`column1`, `column2`, `column3`,...) 
  VALUES (value1, value2, value3,...);

(6)使用 UPDATE 更新数据：

UPDATE `table_name`　
  SET `column1`=value1,`column2`=value2,...　
  WHERE `some_column`=some_value;

(7)使用 DELETE 删除数据:

DELETE FROM `table_name`
   WHERE `some_column` = `some_value`;

LEVEL2

(1) 比较运算符:

A operator B

,常用的比较运算符有 =（等于） 、!=（不等于）、 <>（不等于）、<（小于）、<=（小于等于）、>（大于）、>=（大于等于），其中 != 和 <> 在特殊情况下用法是不同的，这里暂时不提。

(2)使用 AND 连接多条件:

SELECT `column_name` 
  FROM `table_name` 
  WHERE condition1 AND condition2;

(3)使用 OR 连接多个条件:

SELECT `column_name` 
  FROM `table_name` 
  WHERE condition1 or condition2;

(4)使用 NOT 过滤不满足条件的数据

SELECT `column_name` 
  FROM `table_name` 
  WHERE NOT `condition`;
//举例
  SELECT *
  FROM `teachers`
  WHERE NOT (`age` > 20 AND `country` = 'CN');

(5)使用 IN 查询多条件:当我们需要查询单个表条件过多时，就会用多个 'OR' 连接或者嵌套，这会比较麻烦，现在我们有 'IN' 能更方便的解决这一问题。

SELECT *
  FROM `table_name`
  WHERE `column_name` IN `value`;
//举例
  SELECT *
  FROM `teachers`
  WHERE `country` IN ('CN', 'UK');

(6）使用 NOT IN 排除

SELECT *
  FROM `table_name`
  WHERE `column_name` NOT IN value;

（7）使用 BETWEEN AND 查询两值间的数据范围:

  • BETWEEN AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
  • 请注意，在不同的数据库中，BETWEEN 操作符会产生不同的结果！
  • 在某些数据库中，BETWEEN 选取介于两个值之间但不包括两个测试值的字段。
  • 在某些数据库中，BETWEEN 选取介于两个值之间且包括两个测试值的字段。
  • 在某些数据库中，BETWEEN 选取介于两个值之间且包括第一个测试值但不包括最后一个测试值的字段。
  • 因此，请检查您的数据库是如何处理 BETWEEN 操作符！
  • 我们这里选用的是 MySQL 的支持，BETWEEN 选取介于两个值之间且包括两个测试值的字段，即BETWEEN 200 AND 250 选取结果会包括 200 和 250 
　SELECT *
　FROM `table_name`
　WHERE `column_name` BETWEEN `value` AND `value`;

（8）使用 IS NULL 查询空数据：NULL 值代表遗漏的未知数据。默认的，表的列可以存放 NULL 值。

注意：

  • NULL 用作未知的或不适用的值的占位符。
  • 无法比较 NULL 和 0；它们是不等价的。
  • 无法使用比较运算符来测试 NULL 值，比如 =、!= 或 <>。
  • 我们必须使用 IS NULL 和 IS NOT NULL操作符。 
SELECT *
  FROM `table_name`
  WHERE `column_name` IS NULL;

(9)使用 LIKE 模糊查询

NUM 通配符 描述
1 % 替代 0 个或多个字符
2 _ 替代一个字符
3 [charlist] 字符列中的任何单一字符
4 或 [!charlist] 不在字符列中的任何单一字符 
SELECT *
FROM `table_name`
WHERE `column_name` LIKE  `value`;

(10)使用 ORDER BY 对数据进行排序

ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序，其具有 ASC（升序）和 DESC（降序）两个关键字，且默认按照升序排列。

  • ASC ：按升序排列，ORDER BY 默认按照升序对记录进行排序，因此升序的关键字 ASC 可以省去不写。
  • DESC：按降序排列，如果需要按照降序对记录进行排序，可以使用 DESC 关键字。 
SELECT `column_name`, `column_name`
FROM `table_name`
ORDER BY `column_name`, `column_name` ASC|DESC;

(11)使用 LIMIT 限制输出行数:

LIMIT 子句用于 SELECT 中，对输出结果集的行数进行约束，LIMIT 接收2个参数 offset 和 count，两个参数都是整型数字，但通常只用一个。

  • offset ：是返回集的初始标注，起始点是0，不是1
  • ount ：制定返回的数量
  • LIMIT 关键字的位置，需要放在 ORDER BY 关键字的后面，否则会报错。 
SELECT `column_name`, `column_name`
FROM `table_name`
LIMIT `offset` , `count`;

LEVEL3

算数函数（1）

(1) 使用 AVG() 函数求数值列的平均值:

  • 平均函数 AVG() 是平均数 AVERAGE 的缩写，它用于求数值列的平均值。它可以用来返回所有列的平均值，也可以用来返回特定列和行的平均值。

  • 具体的计算过程为：其通过对表中行数计数并计算特定数值列的列值之和，求得该列的平均值。

    *　但是当参数 ｀column_name｀ 列中的数据均为空时，结果会返回 NULL。

SELECT AVG(`column_name`) 
  FROM `table_name`;
//举例，其中 AS 关键字的作用是赋予 AVG(student_count) 计算结果列显示在列表中的别名。
  SELECT AVG(`student_count`) AS `average_student_count`
  FROM `courses`;

何为别名？

别名是一个字段或值的替换名，由关键字 AS 赋予。别名还有其他用途，常见的用途包括在实际的表列名包含不符合规定的字符（如空格）时重新命名它，在原来的表列名含糊或者容易误解时扩充它等。

别名常与函数联用，给使用函数之后的新计算列一个名字，方便我们查看和使用。我们会在后续的学习中经常见到它。

(2)使用 MAX() 函数返回指定列中的最大值:它只有一个参数 column_name ，表示指定的列名。但是当参数 column_name 列中的数据均为空时，结果会返回 NULL。

SELECT MAX(`column_name`) 
  FROM `table_name`;

(3)使用 MIN() 函数返回指定列中的最小值

SELECT MIN(`column_name`) 
  FROM `table_name`;

(4)使用 SUM() 函数统计数值列的总数:

SELECT SUM(`column_name`) 
  FROM `table_name`;

算数函数（2）

(1) 使用 ROUND() 函数将数值四舍五入：

ROUND()

函数用于把数值字段舍入为指定的小数位数。

  • ROUND(X)

     :返回参数 X 四舍五入后的一个整数。

  • ROUND(X, D)

     :返回参数 X 四舍五入且保留 D 位小数后的一个数字。如果 D 为 0，结果将没有小数点或小数部分。

  • ROUND()

     返回值数据类型会被变换为一个 BIGINT 。 
SELECT ROUND(`column_name`, `decimals`) 
  FROM `table_name`;

(2)使用 NULL() 函数判断空值

  • ISNULL():

    ISNULL()

     函数用于判断字段是否为 NULL，它只有一个参数

    column_name

    为列名，根据

    column_name

     列中的字段是否为 NULL 值返回 0 或 1。 
SELECT ISNULL(`column_name`)
  FROM `table_name`;
  • IFNULL():

    IFNULL()

     函数也用于判断字段是否为NULL，但是与

    ISNULL()

    不同的是它接收两个参数，第一个参数

    column_name

     为列名，第二个参数

    value

    相当于备用值。

    其中：

    (1) 如果 column_name 列中的某个字段是 NULL 则返回 value 值，不是则返回对应内容。

    (2) COALESCE(column_name, value) 函数也用于判断字段是否为NULL，其用法和 IFNULL() 相同。

SELECT IFNULL(`column_name`, `value`)
  FROM `table_name`;
//举例
  SELECT `name`, `email`, ISNULL(`email`), IFNULL(`email`, 0), COALESCE(`email`, 0) 
  FROM `teachers`;

(3)使用 COUNT() 函数计数:当

COUNT()

中的参数不同时，其的用途也是有明显的不同的，主要可分为以下三种情况：COUNT(column_name) 、COUNT( * ) 和 COUNT(DISTINCT column_name) 。

  • COUNT( column_name ):

    • COUNT(column_name) 函数会对指定列具有的行数进行计数，但是会除去值为 NULL 的行。该函数主要用于查看各列数据的数量情况，便于统计数据的缺失值。

      *　假如出现某一列的数据全为 NULL 值的情况，使用COUNT( column_name ) 函数对该列进行计数，会返回 0。

SELECT COUNT(`column_name`) 
  FROM `table_name`;
  • COUNT():COUNT() 函数会对表中行的数目进行计数，包括值为 NULL 所在行和重复项所在行。该函数主要用于查看表中的记录数。 
SELECT COUNT(*) 
  FROM `table_name`;

注意：COUNT(column_name) 与 COUNT(*) 的区别

  • COUNT(column_name)

     中，如果

    column_name

     字段中的值为 NULL，则计数不会增加，而如果字段值为空字符串""，则字段值会加 1

  • COUNT(*)

     中，除非整个记录全为

    NULL

     ，则计数不会增加，如果存在某一个记录不为

    NULL

     ，或者为空字符串""，计数值都会加 1。正常来说，表都会有主键，而主键不为空，所以

    COUNT(*)

     在有主键的表中等同于

    COUNT(PRIMARY_KEY)

     ，即查询有多少条记录。 
SELECT COUNT(*) 
  FROM `table_name`;

时间函数（1）

（1）使用 NOW() 、 CURDATE()、CURTIME() 获取当前时间

  • NOW()

     可以用来返回当前日期和时间 格式：YYYY-MM-DD hh:mm:ss

  • CURDATE()

     可以用来返回当前日期 格式：YYYY-MM-DD

  • CURTIME()

     可以用来返回当前时间 格式：hh:mm:ss 
//举例  使用 NOW() 向记录表 records 中插入当前的时间（精确到毫秒）
 INSERT INTO `records` VALUES (NOW(3));

(4)使用 DATE()、TIME() 函数提取日期和时间

使用

DATE()

、

TIME()

函数分别将 '2021-03-25 16:16:30' 这组数据中的日期于时间提取出来，并用 date 、time 作为结果集列名。

SELECT DATE('2021-03-25 16:16:30') AS `date`,TIME('2021-03-25 16:16:30')  AS `time`;

运行结果如下：

date time
2021-03-25 16:16:30
(5)使用 EXTRACT() 函数提取指定的时间信息:

EXTRACT()

 函数用于返回日期/时间的单独部分，如 YEAR (年)、MONTH (月)、DAY (日)、HOUR (小时)、MINUTE (分钟)、 SECOND (秒)。 
SELECT EXTRACT(unit FROM date)
  FROM `table`
  • date 参数是合法的日期表达式。
  • unit 参数是需要返回的时间部分，如

    YEAR

    MONTH

    DAY

    HOUR

    MINUTE

    SECOND

     等。
  • 在一般情况下，

    EXTRACT(unit FROM date)

     与

    unit()

     的结果相同。 
//举例
  SELECT `name`, EXTRACT(HOUR FROM `created_at`) AS `created_hour`
  FROM `courses`;

（6）使用 DATE_FORMAT() 格式化输出日期:

SELECT DATE_FORMAT(date,format);
  • 我们在 SQL 中使用 DATE_FORMAT() 方法来格式化输出 date/time。

  • 需要注意的是 DATE_FORMAT() 函数返回的是字符串格式。

    举例：

SELECT DATE_FORMAT(`created_at`, '%Y %m') AS `DATE_FORMAT`
FROM `courses`;

其中

%m

表示月份，

%d

表示日期，

%Y

表示年份，

%w

表示星期。

时间函数（2）

（1）使用 DATE_ADD() 增加时间：

DATE_ADD()

函数是常用的时间函数之一，用于向日期添加指定的时间间隔

SELECT DATE_ADD(date, INTERVAL expr type)
  FROM table_name
  • date 指代希望被操作的有效日期，为起始日期
  • expr 是希望添加的时间间隔的数值（expr 是一个字符串，对于负值的间隔，可以以 ”-“ 开头）
  • type 是具体的数据类型，表示加上时间间隔的单位（可以是 MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , YEAR 等） 
//举例
SELECT `name`, DATE_ADD(`created_at`, INTERVAL 1 YEAR) AS `new_created`
     FROM `courses`;

(2)使用 DATE_SUB() 减少时间:

DATE_SUB()

函数是常用的时间函数之一，用于从日期减去指定的时间间隔。它与

DATE_ADD()

函数具有相似的用法。

SELECT DATE_SUB(date, INTERVAL expr type)
FROM table_name
  • date 指代希望被操作的有效日期
  • expr 是希望添加的时间间隔
  • type 是具体的数据类型（可以是 MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , YEAR 等）

(3)使用时间函数 DATEDIFF() 和 TIMESTAMPDIFF() 计算日期差:DATEDIFF() 常用的日期差，在 MySQL 中默认只能计算天数差。

示例代码

[3.1] DATEDIFF()

  • DATEDIFF()

     用法：

    DATEDIFF（时间1，时间2）
  • 代码语法:

    SELECT DATEDIFF(时间1,时间2) AS date_diff FROM courses;

  • DATEDIFF()

     差值计算规则：时间 1 - 时间 2,

    date_diff

    为返回结果列名称

    举例:

SELECT DATEDIFF(created_at,'2018-01-13') AS date_diff FROM courses;

[3.2] TIMESTAMPDIFF()

  • 查询功能多的 MySQL 自带的日期函数，可以计算两个日期相差的年(YEAR，时间1,时间2)，月(MONTH，时间1,时间2)，周(WEEK，时间1,时间2)，日(DAY，时间1,时间2)，小时(HOUR，时间1,时间2)。

  • TIMESTAMPDIFF()

    TIMESTAMPDIFF (类型,时间1,时间2)

  • SELECT TIMESTAMPDIFF (类型,时间1,时间2) AS year_diff；

  • TIMESTAMPDIFF()

    差值计算规则：时间 2 - 时间 1 
SELECT name AS `courses_name` ,created_at AS `courses_created_at`,TIMESTAMPDIFF(YEAR, `created_at`, '2021-04-01') AS `year_diff`
FROM `courses`;

约束

（1）约束：在 SQL 中，约束是规定表中的数据规则。若存在违反约束的行为，行为就会阻止。它能帮助管理员更好地管理数据库，并且确保数据库中数据的正确性和有效性。例如在后台的数据库设计中对要输入的数据进行核查或判断，再决定是否写入数据库，这都是约束的应用。

（2）非空约束 NOT NULL：NOT NULL 约束强制列不接受 NULL 值，强制字段始终包含值，这意味着，如果不向字段添加值，就无法插入新纪录或者更新记录。

//举例
  CREATE TABLE `Persons` (
     `ID` int NOT NULL,
     `LastName` varchar(255) NOT NULL,
     `FirstName` varchar(255) NOT NULL,
     `Age` int
  );
  • 不要把 NULL 值与空串相混淆。NULL 值是没有值，
  • 它不是空串。如果指定' '（两个单引号，其间没有字符），这
  • 在 NOT NULL 列中是允许的。空串是一个有效的值，它不是无值。
  • NULL 值用关键字 NULL 而不是空串指定。

(3)唯一约束 UNIQUE

  • UNIQUE 约束唯一标识数据库表中的每条记录
  • UNIQUE 和 主键约束均为列或列集合提供了唯一性的保证

  • 主键约束会自动定义一个 UNIQUE 约束，或者说主键约束是一种特殊的 UNIQUE 约束。但是二者有明显的区别：每个表可以有多个 UNIQUE 约束，但只能有一个主键约束。

    用法一：CREATE TABLE 时的 UNIQUE 约束

//(1)MySQL
  CREATE TABLE `Persons`
  (
  `P_Id` int NOT NULL,
  ......
  UNIQUE (`P_Id`)
  )
//(2)SQL Server / Oracle / MS Access
  CREATE TABLE `Persons`
  (
  `P_Id` int NOT NULL UNIQUE,
  ......
  )
//(3)MySQL / SQL Server / Oracle / MS Access
//命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束:
  CREATE TABLE `Persons`
  (
   `P_Id` int NOT NULL,
   `LastName` varchar(255) NOT NULL,
    ......
   CONSTRAINT uc_PersonID UNIQUE (`P_Id`,`LastName`)
  )

用法二：ALTER TABLE 时的 UNIQUE 约束

//(1)MySQL / SQL Server / Oracle / MS Access
  ALTER TABLE `Persons`
  ADD UNIQUE（`P_Id`）
//(2)MySQL / SQL Server / Oracle / MS Access 定义多个列的 UNIQUE 约束
  ALTER TABLE `Persons`
  ADD CONSTRAINT uc_PersonID UNIQUE (`P_Id`,`LastName`)

用法三:撤销 UNIQUE 约束

//(1)MySQL
  ALTER TABLE `Persons`
  DROP INDEX uc_PersonID
//(2)SQL Server / Oracle / MS Access
  ALTER TABLE `Persons`
  DROP CONSTRAINT uc_PersonID

(4)主键约束 PRIMARY KEY

  • PRIMARY KEY 约束唯一标识数据库表中的每条记录 ，简单的说，PRIMARY KEY = UNIQUE + NOT NULL
  • 从技术的角度来看，PRIMARY KEY 和 UNIQUE 有很多相似之处。但还是有以下区别：
    • NOT NULL UNIQUE 可以将表的一列或多列定义为唯一性属性，而 PRIMARY KEY 设为多列时，仅能保证多列之和是唯一的，具体到某一列可能会重复。

    • RIMARY KEY 可以与外键配合，从而形成主从表的关系，而 NOT NULL UNIQUE 则做不到这一点

      如：

      表一：用户 id (主键)，用户名

      表二: 银行卡号 id (主键)，用户 id (外键)

      则表一为主表，表二为从表。

  • 更大的区别在逻辑设计上。

    PRIMARY KEY

     一般在逻辑设计中用作记录标识，这也是设置

    PRIMARY KEY

     的本来用意，而

    UNIQUE

    只是为了保证域/域组的唯一性。

    用法一:CREATE TABLE 时 添加 PRIMARY KEY 约束

//(1)MYSQL
  CREATE TABLE `Persons`
  (
    `P_Id` int NOT NULL,
    ......
    PRIMARY KEY (`P_Id`)
  );
//(2)SQL Server / Oracle / MS Access
  CREATE TABLE `Persons`
  (
    `P_Id` int NOT NULL PRIMARY KEY,
    ......
  )
//(3)如需命名并定义多个列的 PRIMARY KEY 约束，请使用下面的 SQL 语法：
  CREATE TABLE `Persons`
  (
     `P_Id` int NOT NULL,
     `LastName` varchar(255) NOT NULL,
     ......
     CONSTRAINT pk_PersonID PRIMARY KEY (`P_Id`,`LastName`)
  )

在上面的实例（3）中，只有一个主键 PRIMARY KEY（pk_PersonID）。然而，pk_PersonID 的值是由两个列（P_Id 和 LastName）组成的。

用法二：ALTER TABLE 时添加主键约束

//(1)MySQL / SQL Server / Oracle / MS Access
  ALTER TABLE `Persons`
  ADD PRIMARY KEY (`P_Id`)
//(2)MySQL / SQL Server / Oracle / MS Access
//如需命名并定义多个列的 PRIMARY KEY 约束
  ALTER TABLE `Persons`
  ADD CONSTRAINT pk_PersonID PRIMARY KEY (`P_Id`,`LastName`)

用法三：撤销 PRIMARY KEY

如需撤销 PRIMARY KEY 约束，我们可以通过将上述 ALTER TABLE 和 DROP 实现：

//(1)MYSQL
  ALTER TABLE `Persons`
  DROP PRIMARY KEY
//(2)SQL Server / Oracle / MS Access
  ALTER TABLE `Persons`
  DROP CONSTRAINT pk_PersonID

(5)外键约束 FOREIGN KEY

外键概念：一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY 。

让我们看了例子，如果一个字段 X 在一张表（表 1 ）中是关键字，而在另一张表（表 2 ）中不是关键字，则称字段 X 为表 2 的外键。

外键作用：外键最根本的作用：保证数据的完整性和一致性。这么说可能有些同学无法理解，接下来通过一个例子来深入理解一下。

外键约束:外键约束是指用于在两个表之间建立关系，需要指定引用主表的哪一列。

用法一：CREATE TABLE 时的 SQL FOREIGN KEY 约束

//(1)MySQL 在 "Orders" 表创建时在 "P_Id" 列上创建 FOREIGN KEY 约束：
CREATE TABLE `Orders`
(
`O_Id` int NOT NULL,
`OrderNo` int NOT NULL,
`P_Id` int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
//(2)SQL Server / Oracle / MS Access
CREATE TABLE `Orders`
(
`O_Id` int NOT NULL PRIMARY KEY,
`OrderNo` int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

其中,NOT NULL 表示该字段不为空.REFERENCES 表示 引用一个表.

如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束:其中,CONSTRAINT 表示约束，后面接约束名称，常用于创建约束和删除约束；

CREATE TABLE `Orders`
(
`O_Id` int NOT NULL,
`OrderNo` int NOT NULL,
`P_Id` int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

用法二: ALTER TABLE 时的 SQL FOREIGN KEY 约束

//(1)MySQL / SQL Server / Oracle / MS Access
ALTER TABLE `Orders`
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
//如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束:
//(2)MySQL / SQL Server / Oracle / MS Access
ALTER TABLE `Orders`
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

用法三:撤销 FOREIGN KEY 约束

//(1)MySQL
ALTER TABLE `Orders`
DROP FOREIGN KEY fk_PerOrders
//(2)SQL Server / Oracle / MS Access
ALTER TABLE `Orders`
DROP CONSTRAINT fk_PerOrders

(6)检查约束 CHECK

CHECK 约束用于限制列中的值的范围，评估插入或修改后的值。满足条件的值将会插入表中，否则将放弃插入操作。 可以为同一列指定多个 CHECK 约束。

CHECK

约束既可以用于某一列也可以用于某张表：

  • 如果对单个列定义 CHECK 约束，那么该列只允许特定的值。

  • 如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制。

    定义 CHECK 约束条件在某种程度上类似于编写查询的 WHERE 子句，使用不同的比较运算符（例如 AND、OR、BETWEEN、IN、LIKE 和 IS NULL）编写其布尔表达式，该布尔表达式将返回 TRUE、FALSE 或 UNKNOWN 。

    (1)当条件中存在 NULL 值时，CHECK约束将返回 UNKNOWN 值。

    (2)CHECK 约束主要用于通过将插入的值限制为遵循定义的值、范围或格式规则的值来强制域完整性。

CREATE DATABASE IF NOT EXISTS hardy_db default character set utf8mb4 collate utf8mb4_0900_ai_ci;
USE hardy_db;
DROP TABLE IF EXISTS lesson;

创建表结构时可以使用 CHECK 约束，也可以给已创建的表增加 CHECK 约束。

举例：假如我们想创建一个简单的课程表 courses ，表中每一条数据记录着课程编号 id、课程名称 name 、学生总数 student_count 、创建课程时间 created_at 以及授课教师编号 teacher_id。其中课程编号 id 为主键。

根据基本常识，学生总数 student_count 一定是非负值，在这里我们设置它必须为正整数，可以使用 CHECK 约束。

->->->因此，在不同的 SQL 软件中，语法会有些不同，在本文中会介绍 CHECK 约束在各个 SQL 软件中的使用。

用法一：创建表（CREATE TABLE）时添加 CHECK约束

  • 在创建课程表 courses 时，给学生总数 student_count 字段加上一个大于 0 的约束。

    MYSQL：

CREATE TABLE `courses`
(
`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CHECK (`student_count` > 0)
)

SQL Server / Oracle / MS Access：

CREATE TABLE `courses`
(
`id` int
CHECK (`student_count` > 0),
`name` varchar(255),`student_count` int,
`created_at` date,
`teacher_id` int
)
  • 为多个列添加 CHECK 约束

MySQL / SQL Server / Oracle / MS Access：

CREATE TABLE `courses`
(
`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CHECK (`student_count` > 0 AND `teacher_id` > 0)
)

如果想为一个表中多个字段添加约束，直接在 CHECK 关键字后的括号内添加，两个约束间使用 AND 关键字连接。

  • 为 CHECK 约束命名 
CREATE TABLE `courses`
(
`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CONSTRAINT chk_courses CHECK (`student_count` > 0) ;

核心部分的基本语法为：

[CONSTRAINT <constraint name>] CHECK (<condition>)
  • 约束关键字 CONSTRAINT：表示其后面接的内容为约束
  • constraint name：为约束名称
  • 关键字 CHECK：表示检查约束

  • condition：被约束内容

    用法三：表已存在时添加 CHECK 约束

  • 课程表 courses 已存在的情况下为学生总数 student_count 字段添加一个大于 0 的 CHECK 约束。 
ALTER TABLE `courses` 
ADD CHECK ( `student_count` > 0);

或

ALTER TABLE `courses`  
ADD CONSTRAINT chk_courses CHECK ( `student_count` > 0 AND `teacher_id` > 0);
  • ALTER TABLE 关键字：表示修改表的定义

  • ADD 关键字：表示增加

    用法四：撤销 CHECK 约束

    如果想要撤销 CHECK 约束，可以使用 DROP 关键字。

ALTER TABLE `courses` 
DROP CHECK chk_courses
           
ALTER TABLE `courses` 
DROP CONSTRAINT chk_courses

(7)DEFAULT 约束----默认约束。

默认值（Default）”的完整称呼是“默认值约束（Default Constraint）”。MySQL 默认值约束用来指定某列的默认值。

用法一：DEFAULT 约束用法

  • DEFAULT 约束用于向列中插入默认值。
  • 如果没有规定其他的值，那么会将默认值添加到所有的新记录。

  • 例如女同学较多，性别就可以默认为“女”，如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为“女”。

    用法二：CREATE TABLE 时的 DEFAULT 约束

    使用 DEFAULT 关键字设置默认值约束，具体的语法规则如下所示：<字段名> <数据类型> DEFAULT <默认值>

    举例：下面的 SQL 在 Persons表创建时在 City 列上创建 DEFAULT 约束：

    MYSQL / SQL Server / Oracle / MS Access

CREATE TABLE `Persons`
(
    `P_Id` int NOT NULL,
    `LastName` varchar(255) NOT NULL,
    `FirstName` varchar(255),
    `Address` varchar(255),
    `City` varchar(255) DEFAULT 'Sandnes'
)

通过使用类似 GETDATE() 这样的函数, DEFAULT 约束也可以用于插入系统值：

CREATE TABLE `Orders`
(
    `O_Id` int NOT NULL,
    `OrderNo` int NOT NULL,
    `P_Id` int,
    `OrderDate` date DEFAULT GETDATE()
)

用法三：ALTER TABLE 时的 DEFAULT 约束

如果表已被创建时，想要在 City 列创建 DEFAULT 约束，请使用下面的 SQL：

MYSQL

ALTER TABLE `Persons`
ALTER `City` SET DEFAULT 'SANDNES'

SQL Server / MS Access：

ALTER TABLE `Persons`
ADD CONSTRAINT ab_c DEFAULT 'SANDNES' for `City`

用法四：撤销 DEFAULT 约束

如需撤销 Persons表的 DEFAULT 约束 :

MYSQL:

ALTER TABLE `Persons`
ALTER `City` DROP DEFAULT
           
ALTER TABLE `Persons`
ALTER COLUMN `City` DROP DEFAULT

多表连结

（1）联结：联结中的两大主角——主键（PRIMARY KEY）和外键（FOREIGN KEY）

  • 创建联结:在设置关联条件时，为避免不同表被引用的列名相同，我们需要使用完全限定列名（用一个点分隔表名和列名），否则会返回错误,用法如下： 
table1`.`common_field` = `table2`.`common_field`

  • JOIN 连接子句

    SQL JOIN 连接子句用于将数据库中两个或者两个以上表中的记录组合起来。其类型主要分为 INNER JOIN（内连接）、OUTER JOIN（外连接）、全连接（FULL JOIN）和交叉连接（CROSS JOIN），其中 OUTER JOIN 又可以细分为 LEFT JOIN（左连接）和 RIGHT JOIN（右连接）。

    因此，我们主要使用的 JOIN 连接类型如下：

  • INNER JOIN：如果表中有至少一个匹配，则返回行
  • LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
  • RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
  • FULL JOIN：只要其中一个表中存在匹配，则返回行

  • CROSS JOIN：又称笛卡尔积，两个表数据一一对应，返回结果的行数等于两个表行数的乘积

    (2)内连接INNER JOIN

  • 最常用也最重要的多表联结类型就是 INNER JOIN（内连接），有时候也被称作 EQUIJOIN（等值连接）。
  • 内连接根据联结条件来组合两个表中的字段，以创建一个新的结果表。假如我们想将表 1 和表 2 进行内连接，SQL 查询会逐个比较表 1 和表 2 中的每一条记录，来寻找满足联结条件的所有记录对。当联结条件得以满足时，所有满足条件的记录对的字段将会结合在一起构成结果表。

  • 简单的说，内连接就是取两个表的交集，返回的结果就是连接的两张表中都满足条件的部分。

    基本语法

    在对 INNER JOIN（内连接）的概念有基本的了解之后，我们再来学习一下它的基本语法。

    基本语法有如下两种写法：

SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`
INNER JOIN `table2`
ON `table1`.`common_field` = `table2`.`common_field`;
           
SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`
JOIN `table2`
ON `table1`.`common_field` = `table2`.`common_field`;

注：INNER JOIN 中 INNER 可以省略不写

其中，语法的核心部分如下所示：

FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field

table1 和 table2 是内连接的两个表名，table1.common_field 和 table2.common_field

需要注意的是，联结条件需使用特定的 ON 子句给出。

(3)外连接 OUTER JOIN

外连接在生活中是经常用到的，外连接也是针对于两张表格之间，比如我们实际应用过程会发现，会有一些新任职的教师，还在实习期，并无对应课程安排，那若是按照上一节使用内连接的话，这些教师的课程信息将无法导出来，我们应该如何操作呢？这个就要用到我们的外连接，外连接可以将某个表格中，在另外一张表格中无对应关系，但是也能将数据匹配出来。

在MySQL中，外连接查询会返回所操作的表中至少一个表的所有数据记录。在MySQL中，数据查询通过SQL语句 “OUTER JOIN…ON” 来实现，外连接查询可以分为以下三类：

  • 左外连接
  • 右外连接

  • 全外连接

    外连接数据查询语法如下：

SELECT column_name 1,column_name 2 ... column_name n
    FROM table1
        LEFT | RIGHT | FULL  (OUTER) JOIN table2
        ON CONDITION;

在上述语句中，参数

column_name

表示所要查询的字段名字，来源于所连接的表 table1 和 table2，关键字

OUTER JOIN

表示表进行外连接，参数

CONDITION

表示进行匹配的条件。

(3.1) 左外连接 LEFT JOIN

外连接查询中的左外连接就是指新关系中执行匹配条件时，以关键字 LEFT JOIN 左边的表为参考表。左外连接的结果包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是连接列所匹配的行，这就意味着，左连接会返回左表中的所有记录，加上右表中匹配到的记录。如果左表的某行在右表中没有匹配行，那么在相关联的结果行中，右表的所有选择列表均为空值。

语法：

SELECT column_name 1,column_name 2 ... column_name n
    FROM table1
        LEFT JOIN table2
        ON CONDITION ;

(3.2) 右外连接 RIGHT JOIN

外连接查询中的右外连接是指新关系中执行匹配条件时，以关键字 RIGHT JOIN 右边的表为参考表，如果右表的某行在左表中没有匹配行，左表就返回空值。

SELECT column_name 1,column_name 2 ... column_name n
    FROM table1
        RIGHT JOIN table2
        ON CONDITION ;

(3.3) 全外连接 FULL (OUTER) JOIN

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行。FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

注：MySQL 数据库不支持全连接，想要实现全连接可以使用 UNION ALL 来将左连接和右连接结果组合在一起实现全连接。

UNION ：联合的意思，即把两次或多次查询结果合并起来

要求：两次查询的列数必须一致，同时，每条 SELECT 语句中的列的顺序必须相同

推荐：列的类型可以不一样，但推荐查询的每一列，相对于的类型应该一样

可以来自多张表的数据：多次sql语句取出的列名可以不一致，此时以第一个sql语句的列名为准，即UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

如果不同的语句中取出的行，有完全相同(这里表示的是每个列的值都相同)，那么 UNION 会将相同的行合并，最终只保留一行。也可以这样理解，UNION 会去掉重复的行。

如果不想去掉重复的行，可以使用 UNION ALL 。

如果子句中有 order by,limit，需用括号()包起来。推荐放到所有子句之后，即对最终合并的结果来排序或筛选。

SELECT column_name 1,column_name 2 ... column_name n
    FROM table1
        LEFT JOIN table2 ON CONDITION 
UNION
SELECT column_name 1,column_name 2 ... column_name n
    FROM table1
        RIGHT JOIN table2 ON CONDITION ;

(4) 交叉连接 CROSS JOIN

(4.1) 什么是交叉连接

* 与内连接和外连接相比，交叉连接非常简单，因为它不存在 ON 子句，那怎么理解交叉连接呢？

* 交叉连接：返回左表中的所有行，左表中的每一行与右表中的所有行组合。即将两个表的数据一一对应，其查询结果的行数为左表中的行数乘以右表中的行数。

CROSS JOIN（交叉连接）的结果也称作笛卡尔积，我们来简单了解一下什么是笛卡尔积：

* 笛卡尔乘积是指在数学中，两个集合 X 和 Y 的笛卡尓积（Cartesian product），又称直积，表示为X × Y，第一个对象是 X 的成员而第二个对象是 Y 的所有可能有序对的其中一个成员。

(4.2) 交叉连接的两种定义方式

交叉连接有两种定义方式，分为隐式连接和显式连接。两种定义方式的查询结果是相同的。

* 隐式交叉连接：不需要使用 CROSS JOIN 关键字，只要在 SELECT 语句的 FROM 语句后将要进行交叉连接的表名列出即可，这种方式基本上可以被任意数据库系统支持。

基本语法如下：

SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`,`table2`;

假如我们想将课程表和教师表进行隐式的交叉连接，查询该学期所有开课老师和他们开课的课程，我们可以使用下列 SQL 语句：

SELECT  `courses`.`name` AS `course_name`, `teachers`.`name` AS `teacher_name`
FROM `courses` ,`teachers`;

为了编写的便利和简洁，我们一般会给表取别名，如本题中给教师表 courses 取别名为 c，给教师表teachers 取别名为 t：

SELECT  `c`.`name` AS `course_name`, `t`.`name` AS `teacher_name`
FROM `courses` `c`,`teachers` `t`;
           
* 显式交叉连接：与隐式交叉连接的区别就是它使用 CROSS JOIN 关键字，用法与 INNER JOIN 相似。
           
SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`
CROSS JOIN `table2`;

使用显式交叉连接来解决上文相同的问题，我们可以使用下列 SQL 语句：

SELECT  `courses`.`name` AS `course_name`, `teachers`.`name` AS `teacher_name`
FROM `courses` 
CROSS JOIN `teachers`;
           
SELECT  `c`.`name` AS `course_name`, `t`.`name` AS `teacher_name`
FROM `courses` `c`
CROSS JOIN `teachers` `t`;

LEVEL 5

分组查询

(1)GROUP BY 子句

我们在日常生活中，常常会将东西分类摆放使其能看起来更井井有条，也在找寻时能更加方便。对于数据，在查询过程中，我们同样也会需要对同类的数据进行分类。

GROUP BY 函数就是 SQL 中用来实现分组的函数，其用于结合聚合函数，能根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表。

SELECT `column_name`, aggregate_function(`column_name`)
FROM `table_name`
WHERE `column_name` operator value
GROUP BY `column_name`;

例如：可以看到我们教师表中的教师来自不同的国家，现需要统计不同国家教师的人数，并将结果按照不同国籍教师人数从小到大排列，请编写相应的 SQL 语句实现。

使用 SQL 中子查询的方式如下：

SELECT `country`, COUNT(`country`) AS `teacher_count`
FROM `teachers`
GROUP BY `country`
ORDER BY `teacher_count`, `country`;

(2)GROUP BY 多表实例

课程表的每节课程都有对应的一个教师负责授课，而每一个教师对应多门课程，现需要统计每个教师教授课程的学生总数，请编写相应的 SQL 语句实现。

SELECT T.name AS `teacher_name`, IFNULL(SUM(C.student_count), 0) AS `student_count`
FROM `courses` C
	RIGHT JOIN `teachers` T ON C.teacher_id = T.id
GROUP BY T.id;

(3)HAVING 子句: HAVING 子句在使用时就经常与 GROUP BY 语句搭配使用

目的：我们在使用 WHERE 条件子句时会发现其不能与聚合函数联合使用，为解决这一点，SQL 中提供了 HAVING 子句。在使用时， HAVING 子句经常与 GROUP BY 联合使用，HAVING 子句就是对分组统计函数进行过滤的子句。

HAVING 子句对于 GROUP BY 子句设置条件的方式其实与 WHERE 子句与 SELECT 的方式类似，语法也相近，但 WHERE 子句搜索条件是在分组操作之前，而 HAVING 则是在之后。

光通过以上的描述可能体会不够，让我们看一下 HAVING 的实际应用吧！

SELECT   `column_name`, aggregate_function(`column_name`) 
FROM     `table_name` 
WHERE    `column_name` operator value 
GROUP BY `column_name` 
HAVING   aggregate_function(`column_name`) operator value;

例如:

现需要结合教师表与课程表，统计不同教师所开课程的学生总数，对于没有任课的老师，学生总数计为 0 ，最后查询学生总数少于 3000 的教师姓名及学生总数 （别名为 student_count ），结果按照学生总数升序排列，如果学生总数相同，则按照教师姓名升序排列。

使用 SQL 中 HAVING 子句查询的方式如下：

SELECT `T`.`name` AS `name`, IFNULL(SUM(`C`.`student_count`),0) AS `student_count`
FROM `courses` `C` RIGHT JOIN `teachers` `T`
ON `C`.`teacher_id` = `T`.`id`
GROUP BY `T`.`id`
HAVING `student_count` < 3000
ORDER BY `student_count`, `name`;

例题：

请编写 SQL 语句，查询 teachers 表中，各个国家所有教师的平均年龄大于所有国家教师的平均年龄的教师信息。

select *
from teachers 
WHERE country IN
(SELECT country from teachers group by country
having AVG(age)>(select avg(age) FROM teachers))

简单的子查询

(1)SELECT 语句中的子查询

我们之前学的都是在单个表单中进行查询，那么，当查询中需要联合多个表时，该如何实现呢？

上一节我们学到的解决方式是使用连接查询，这一节我们将介绍第二种方法：子查询。

首先，什么是子查询呢？

当一个查询是另一个查询的条件时，称之为子查询。

即在查询语句中的 WHERE 条件子句中，又嵌套了另一个查询语句。

因此，子查询本质上就是一个完整的 SELECT 语句，它可以使一个 SELECT、INSERT INTO 语句、DELETE 语句或 UPDATE 语句嵌套在另一子查询中。子查询的输出可以包括一个单独的值（单行子查询）、几行值（多行子查询）、或者多列数据（多列子查询）。

这里，我们先了解一下基础的 SELECT 语句嵌套子查询。

SELECT `column_name(s)`
FROM `table_name`
WHERE `column_name` OPERATOR (
    SELECT `column_name(s)`
    FROM `table_name`
);

例如：

小明想了解 Western Venom 老师所教的所有课程的所有信息，现请你来帮助他查询相关信息。

SELECT *
FROM `courses`
WHERE `teacher_id` = (
	SELECT `id`
	FROM `teachers`
	WHERE `name` = 'Western Venom'
);

(2)NSERT 语句中的子查询:对于 INSERT 语句中的子查询来说，首先是使用子查询的 SELECT 语句找到需要插入的数据，之后将返回的数据插入到另一个表中。在子查询中所选择的数据可以用任何字符、日期或数字函数修改。

INSERT INTO `table_name`
	SELECT `colnum_name(s)`
	FROM `table_name`
	[ WHERE VALUE OPERATOR ]

注意：INSERT 语句中的子查询其实是将一个表中查询到的数据“复制”到另一个表中，由于主键具有唯一性，如果需要仅在单张表中使用 INSERT 子查询，只能在无主键的单张表中进行操作，否则，需要有两张表（如只一张表，则需新建一张表）。

我们可以通过下面的实例来感受一下 INSERT 语句中的子查询 的用法。

小明在整理数据时发现教师表未备份，为了及时完善数据的备份，现在需要将教师表 teachers 中的全部信息复制到相同表结构的备份表 teachers_bkp 中，请使用相关 SQL 语句完成教师表的备份。

首先我们需要查询教师表 teachers 中的所有信息，再将查询到的数据插入到备份表 teachers_bkp 中。即，查询教师表 teachers 中的所有信息为插入备份表 teachers_bkp 中的条件。

因此，这里我们首先需要通过嵌套子查询到的信息为教师表 teachers 中的所有信息，而整个语句是为了插入数据。

使用 SQL 中子查询的方式如下：(以下语法类似于复制表)

INSERT INTO `teachers_bkp`
SELECT *
FROM `teachers`;

(3)UPDATE 语句中的子查询

UPDATE `table_name` 
SET `column_name` = `new_value`
WHERE `column_name` OPERATOR 
   (SELECT `column_name`
   FROM `table_name`
   [WHERE] )
           
注意：在 UPDATE 语句的子查询中，子查询 SELECT 语句所用的表和 UPDATE 语句所要更改的表不能是同一张表！

举例：学校教务处排课时发现教师 Western Venom 创建的课程有误，现紧急需要将该教师创建的课程名称修改为 Java，请你使用相关的 SQL 语句完成。

UPDATE `courses`
SET `name` = 'Java'
WHERE `teacher_id` = (
	SELECT `id`
    FROM `teachers`
    WHERE `name` = 'Western Venom'
);

(4)DELETE 语句中的子查询

对于 DELETE 语句，首先通过 SELECT 语句查询需要删除的数据，再使用 DELETE 语句对数据进行删除。当通过 DELETE 语句使用子查询时，可以完成复杂的数据删除控制。

DELETE FROM `table_name`
WHERE `column_name` OPERATOR 
   (SELECT `column_name`
   FROM `table_name`  
   [WHERE] )

举例：现需要删除课程表中所有教师年龄小于 21 岁（不包括 21 岁）的课程，请你使用相关的 SQL 语句实现

分析：首先我们需要在教师表 teachers 中查询到教师年龄小于 21 岁的老师的教师 id ，再根据其教师 id 在课程表 course 中查询该教师 id 所创建的课程并将课程删除。

因此，这里我们首先需要通过嵌套子查询到的信息为符合条件的教师 id，而整个语句是为了删除数据。

DELETE FROM `courses`
WHERE `teacher_id` IN (
		SELECT `id`
		FROM `teachers`
		WHERE `age` < 21
	);

子查询进阶

我们在 Level 1 中有学习到 SELECT 语句的语法，为：SELECT column_name FROM table_name ，结合我们前面学习的子查询的方法，我们是将子查询插入到列名 column_name 的位置，将子查询的结果作为列名，而本节我们将介绍的内联视图子查询，是将子查询插入到表名 table_name 的位置。

(1)内联视图子查询实际上就是将查询的结果集作为一个查询表，继续进行查询操作。

我们可以通过下面的实例来感受一下 内联视图子查询 的用法。

现需要查询国籍为美国（USA），且年龄最大的教师，请使用内联视图子查询实现。

本题将从教师表中查询到的美国教师作为内联表，再使用 WHERE 子句进行查询操作。

使用 SQL 中内联视图子查询的方式如下：

SELECT *
FROM (
	SELECT *
	FROM `teachers`
	WHERE `country` = 'USA'
) `T`
WHERE `age` = (
	SELECT MAX(`age`)
	FROM `teachers`
);

(2)IN 操作符的多行子查询

大家还记得在 Level 2 的特殊条件一章中学到的 IN 操作符吗？

没错！当时我们通过使用 IN 操作符方便地将多个条件连接了起来，并对单表进行了查询。而本节我们将结合 IN 操作符对多行子查询进行学习。

使用 IN 操作符进行子查询，其实是将子查询返回的集合和外层查询得到的集合进行交集运算，这个结果可以是零个值，也可以是多个值。由此，最后可以查询出与列表中任意一个值匹配的行。

SELECT `column_name`
FROM `table_name`
WHERE `column_name` IN(
    SELECT `column_name`
    FROM `table_name`
    WHERE `column_name` = VALUE
);

可以通过下面的实例来感受一下 IN 操作符多行子查询的用法。

现需要查询国籍为美国（USA）的教师所开的所有课程，请使用 IN 操作符进行多行子查询。

为了得到满足条件的课程名称，我们首先需要在教师表 teachers 中进行查询，查询数据为国籍 country 为美国（USA）的教师所对应的教师 id，再根据这个 id ，在课程表courses 中进行查询，最终得到所有的课程名称 name。

这里，我们首先需要通过嵌套子查询到的信息为国籍为美国的全部教师的教师 id，父查询为查询满足条件的课程名称。

SELECT `name`
FROM `courses`
WHERE `teacher_id` IN (
	SELECT `id`
	FROM `teachers`
	WHERE `country` = 'USA'
);

(3)ANY 操作符的多行子查询

在学习了 IN 操作符实现多行子查询后，我们不禁会产生思考：除了使用 IN 操作符，还有什么方法能进行多行子查询呢？

而这一节我们就能够学习到另一种实现多行子查询的方式：使用 ANY 操作符进行多行子查询。

但在学习之前，我们需要了解一下操作符 ANY 。

操作符 ANY 属于逻辑运算符的一种，与 IN 运算符不同，ANY 必须和其它的比较运算符共同使用，其表示查询结果中的任意一个。

在子查询中使用 ANY ，表示与子查询返回的任何值比较为真，则返回真。

SELECT `column_name(s)`
FROM `table_name`
WHERE `column_name` OPERATOR
   ANY(SELECT column_name
   FROM table_name)

举例：现需要查询学生上课人数超过 “Eastern Heretic” 的任意一门课的学生人数的课程信息，请使用 ANY 操作符实现多行子查询。

本题涉及到多层的嵌套，让我们来一步步对题目进行分析吧！

第一层的父查询为在课程表 courses 中查询满足条件的全部课程信息，这个条件由子查询来完成，即为，查询学生上课人数超过 ”Eastern Heretic“ 的任意一门课的学生人数。这一部分的子查询中需要结合 ANY 操作符实现。之后，再将子查询进行拆分，形成第二层的嵌套子查询。

第二层的父查询为在课程表 courses 中根据教师 id 查询学生上课人数， 其子查询为在教师表 teachers 中查找教师名 name 为 “Eastern Heretic” 的教师 id。

由于我们最终得到的课程信息中肯定不包含 “Eastern Heretic” 的课程，所以我们要在 WHERE 条件中再设置一项：不为 “Eastern Heretic” 所开的课程 。

结合以上，使用 SQL 中子查询的方式如下：

SELECT *
FROM `courses`
WHERE `student_count` > ANY (
		SELECT `student_count`
		FROM `courses`
		WHERE `teacher_id` = (
			SELECT `id`
			FROM `teachers`
			WHERE `name` = 'Eastern Heretic'
		)
	)
	AND `teacher_id` <> (
		SELECT `id`
		FROM `teachers`
		WHERE `name` = 'Eastern Heretic'
	);

(4)ALL 操作符的多行子查询

除了 IN 、 ANY 外，ALL 也经常在多行子查询中被使用到。

首先，我需要了解一下 ALL 操作符。

与 ANY 一样，操作符 ALL 也属于逻辑运算符的一种，且都须与其它的比较运算符共同使用，其表示查询结果中的所有。

在子查询中使用 ALL ，表示与子查询返回的所有值比较为真，则返回真。

SELECT `column_name(s)`
FROM `table_name`
WHERE `column_name` OPERATOR
   ALL(SELECT column_name
   FROM table_name)

经过上一节 ANY 实例中的学习，相信你对多层的嵌套有了一定的了解，本题依旧是一道多层嵌套的实例题。结合本节知识点，结合操作符 ALL 完成多行子查询吧！

现需要查询学生人数超过 ”Western Venom“ 所有课程学生人数的课程信息，请使用 ALL 操作符实现多行子查询。

第一层的父查询为在课程表 courses 中查询满足条件的全部课程信息，这个条件由子查询来完成，即为，查询学生人数超过 ”Western Venom“ 所有课程学生人数。这一部分的子查询中需要结合 ALL 操作符实现。之后，再将子查询进行拆分，形成第二层的嵌套子查询。

第二层的父查询为在课程表 courses 中根据教师 id 查询学生上课人数， 其子查询为在教师表 teachers 中查找教师名 name 为 ”Western Venom“ 的教师 id。

SELECT *
FROM `courses`
WHERE `student_count` > ALL (
	SELECT `student_count`
	FROM `courses`
	WHERE `teacher_id` = (
		SELECT `id`
		FROM `teachers`
		WHERE `name` = 'Western Venom'
	)
);

(5)多列子查询

上一章我们了解了多行子查询，这一章我们将对多列子查询进行学习。

对于多列子查询：

当是单行多列的子查询时，主查询语句的条件语句中引用子查询结果时可用单行比较符号（＝，>，<，>=，<=， <> 等）来进行比较；

当是多行多列子查询时，主查询语句的条件语句中引用子查询结果时必须用多行比较符号（IN，ANY，ALL 等）来进行比较。

可以通过下面的实例来感受一下 多列子查询 的用法。

现需要找到每个国家年龄最大的教师，请编写 SQL 语句实现多列子查询

SELECT `name`, `age`, `country` 
FROM `teachers` 
WHERE (`country`, `age`) IN ( 
        SELECT `country`, MAX(`age`) 
        FROM `teachers` 
        GROUP BY `country` 
);

(5)HAVING 子句中的子查询

当子查询出现在 HAVING 子句中时，像 HAVING 子句中的任何表达式一样，表示要进行分组过滤，它被用作行组选择的一部分，一般返回单行单列的数据。

👇 我们可以通过下面的实例来感受一下 HAVING 子查询 的用法。

现需要计算每位教师所开课程的平均学生人数与全部课程的平均学生人数，比较其大小，最后返回超过全部课程平均学生人数的教师姓名，请编写相应的 SQL 语句实现。

本题需要使用 HAVING 语句根据教师 id 进行分组，实现“计算每位教师所开课程的平均人数”，并使用子查询实现其与“全部课程的平均人数”的比较。

使用 SQL 中 HAVING 子查询的方式如下：

SELECT `name`
FROM `teachers`
WHERE `id` IN (
	SELECT `teacher_id`
	FROM `courses`
	GROUP BY `teacher_id`
	HAVING AVG(`student_count`) > (
		SELECT AVG(`student_count`)
		FROM `courses`
	)
);

LEVEL6

MYSQL 事务

本章节将带着大家学习 MySQL 事务以及如何使用 COMMIT 和 ROLLBACK 语句来管理 MySQL 中的事务。

(1)MySQL 事务语句

MySQL 为我们提供了以下重要语句来控制事务：

  • 为了启动一个事务，你使用 START TRANSACTION 语句。BEGIN 或 BEGIN WORK 是 START TRANSACTION 的别名。
  • 要提交当前事务并使其变化永久化，你要使用 COMMIT 语句。
  • 要回滚当前事务并取消其变化，你可以使用 ROLLBACK 语句。
  • 要禁用或启用当前事务的自动提交模式，你可以使用 SET autocommit 语句。

默认情况下，MySQL 自动将更改永久性地提交给数据库。要强迫 MySQL 不自动提交更改，你可以使用以下语句：

SET autocommit = 0;

-- OR --

SET autocommit = OFF

你使用下面的语句来明确地启用自动提交模式：

SET autocommit = 1;

-- OR --

SET autocommit = ON;

COMMIT 实例

为了使用事务，你首先要把 SQL 语句分成逻辑部分，并确定数据何时应提交或回滚。

下面说明了创建一个新的销售订单的步骤：

  • 首先，通过使用 START TRANSACTION 语句启动一个事务。
  • 接下来，从 orders 表中选择最新的销售订单号，并使用下一个销售订单号作为新的销售订单号。
  • 然后，在 orders 表中插入一个新的销售订单。
  • 之后，在 orderdetails 表中插入销售订单项目。
  • 最后，使用 COMMIT 语句提交该事务。

你可以选择从 orders 和 orderdetails 表中选择数据来检查新的销售订单。

下面是执行上述步骤的脚本：

-- 1. start a new transaction
START TRANSACTION;

-- 2. Get the latest order number
SELECT 
    @orderNumber:=MAX(orderNUmber)+1
FROM
    orders;

-- 3. insert a new order for customer 145
INSERT INTO orders(orderNumber,
                   orderDate,
                   requiredDate,
                   shippedDate,
                   status,
                   customerNumber)
VALUES(@orderNumber,
       '2005-05-31',
       '2005-06-10',
       '2005-06-11',
       'In Process',
        145);
        
-- 4. Insert order line items
INSERT INTO orderdetails(orderNumber,
                         productCode,
                         quantityOrdered,
                         priceEach,
                         orderLineNumber)
VALUES(@orderNumber,'S18_1749', 30, '136', 1),
      (@orderNumber,'S18_2248', 50, '55.09', 2); 
      
-- 5. commit changes    
COMMIT;

ROLLBACK 实例

首先，登录到 MySQL 数据库服务器，从 orders 表中删除数据：

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM orders;
Query OK, 327 rows affected (0.03 sec)

举例：我们要向 teachers 表中插入一条 Xie Xun 的信息，其年龄为 49 岁，国籍为 CN，请补充 SQL 语句，来实现插入 Xie Xun 的信息。

BEGIN;
INSERT INTO `teachers`
  (`name`, `age`, `country`) 
  VALUES ('Xie Xun', 49, 'CN');
COMMIT;

锁的认识

锁的解释

锁：计算机协调多个进程或线程并发访问某一资源的机制。

锁的重要性

在数据库中，除了传统的计算资源（CPU、RAM、I\O等）的争抢，数据也是一种供多用户共享的资源。

因此，如何保证数据并发访问的一致性，有效性，是所有数据库必须要解决的问题。

锁冲突也是影响数据库并发访问性能的一个重要因素，因此锁对数据库尤其重要。

锁的缺点

加锁是消耗资源的，锁的各种操作，包括：获得锁、检测锁是否已经解除、释放锁等，都会增加系统的开销。

举例:请编写 SQL 语句，查看你当前使用的数据库是什么事务隔离级别

SHOW VARIABLES like '%isolation%';

锁的类型

(1)InnoDB 锁的种类之自增锁

查询当前数据库的自增锁模式：

show variables like '%innodb_autoinc_lock_mode%';
SQL语句大全
* 0：traditonal 每次 insert 语句执行都会产生表锁

* 1：consecutive simple insert 会获得批量的锁，保证一批插入自增序列的连续性，插入之前提前释放锁，在这个模式下你会发现当你 insert 多条数据回滚的时候虽然 DB 没有插入数据，但是自增 ID 已经增长了，也是数据库默认的级别

* 2：interleaved 不会锁表，实时插入，并发最高，但是基于主从的复制是不安全的，感兴趣可以去查询 RBR 和 SBR 的优缺点

通过查询知道，我们的数据库是开启 consecutive 级别，simple insert 表示通过分析 insert 语句可以确定插入的数量；如：insert, insert, insert … valies(), values(), values()；相应的其他插入方式还有 Bulk inserts，Mixed-mode inserts 等。

官方原话如下:

自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入 AUTO_INCREMENT 类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

案例

如果没有自增锁的情况下如下表，id 为自增 AUTO_INCREMENT = 4

id name
路飞
索隆
娜美
这时开启一个事务 A：insert 一条记录山治，自增序列分配到的 id 为 4，事务不提交
又开启一个事务 B：insert 一条记录乌索普，自增序列分配到的 id 为 5，事务不提交
事务 A 又插入一条记录，insert 一条记录为乔巴，这个是后被分配到的 id 为 6
由于在读提交事务隔离级别下不会查询到其他未提交的事务， 事务 A 此时查询 id>3 的记录会返回 4：山治，6：乔巴。就会有一个事务里提交的数据自增 id 不是连续的问题产生
此时自增锁就会帮助搞定这个问题
  • innodb_autoinc_lock_mode = 0

在这一模式下，所有的 insert 语句都要在语句开始的时候得到一个表级的 auto_inc 锁，在语句结束的时候才释放

这种模式下 auto_inc 锁一直要保持到语句的结束，所以这个就影响到了并发的插入

但是主从同步时候是安全的

  • innodb_autoinc_lock_mode = 1

这一模式下去 simple insert 做了优化，由于 simple insert 一次性插入值的个数可以立马得到确定，所以 MySQL 可以一次生成几个连续的值，用于这个 insert 语句，也保证主从同步基于语句的复制安全

这一模式也是 MySQL 的默认模式，这个模式的好处是 auto_inc 锁不要一直保持到语句的结束，只要语句得到了相应的值后就可以提前释放锁

  • innodb_autoinc_lock_mode = 2

无自增锁，会导致上面案例的问题，同一批 insert 提交自增不连续

(2)InnoDB 锁的种类之共享锁和排他锁

(2.1)并发控制

提到共享锁和排它锁就不得不提并发控制（Concurrency Control），并发控制可以解决临界资源操作时不一致的情况产生，保证数据一致性常见的手段就是锁和数据多版本（Multi Version）

直接加锁

这种方式会导致被加锁的资源都被锁住，读取任务也无法执行直到锁释放，所有执行的任务相当于串行化方式，简单粗暴，不能并发

(2.2)共享锁（Shared Locks）简称为 S 锁

读取数据时候可以加 S 锁。

(2.3)共享 (S) 锁允许并发事务读取 (SELECT) 一个资源。资源上存在共享 (S) 锁时，任何其它事务都不能修改数据。一旦已经读取数据，便立即释放资源上的共享 (S) 锁，除非将事务隔离级别设置为可重复读或更高级别，或者在事务生存周期内用锁定提示保留共享 (S) 锁。

排它锁 （Exclusive Locks）简称为 X 锁

修改数据时候加 X 锁。

排它 (X) 锁可以防止并发事务对资源进行访问。其它事务不能读取或修改排它 (X) 锁锁定的数据。

共享锁之间可以并行，排它锁和共享锁之间互斥，也就是说只要共享锁开启没有释放掉的时候，更新锁是不能抢占的，此时其他读取同资源的操作可以进行读取不受限制；同理排它锁开启时候只要没有释放其他不管是排它锁还是共享锁都不可以抢占资源直到锁释放。

(3)InnoDB 锁的种类之意向锁、记录锁和间隙锁

(4)InnoDB 锁的种类之临键锁和插入意向锁

如何上锁

(1) 如何加表锁

对于行锁和表锁的含义区别，在面试中是高频出现的，我们应该对 MySQL 中的锁有一个系统的认识，更详细的需要自行查阅资料，本篇为概括性的总结回答。

MySQL常用引擎有 MyISAM 和 InnoDB，而 InnoDB 是 MySQL 默认的引擎。MyISAM 不支持行锁，而 InnoDB 支持行锁和表锁。

如何加锁？

隐式上锁（默认，自动加锁、自动释放）

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要直接用

LOCK TABLE

; 命令给 MyISAM 表显式加锁。

-- 上读锁 --
SELECT 

-- 上写锁 --

显式上锁（手动）

上共享锁（读锁）的写法：

lock in share mode

，例如：

select column_name from table_name where lock in share mode；

上排它锁（写锁）的写法：

for update

select column_name from table_name where conditions for update；

解锁（手动）

-- 解锁所有锁表 --
UNLOCK TABLES;

举例:通过下面的实例来感受一下 MySQL 中表锁之读锁的用法。

假设我们要更新 teachers 表中，Linghu Chong 的邮箱为 [email protected]，但是 teachers 表被上了读锁，请编写 SQL 语句，来实现更新 Linghu Chong 的邮箱。

-- 对 teachers 表上读锁 --
LOCK TABLES teachers READ;

-- 解锁 --
UNLOCK TABLES;

-- 更新邮箱 --
UPDATE teachers 
SET email = '[email protected]'
WHERE name = 'Linghu Chong';

说明：

在上面的 SQL 语句中，由于对 teachers 表加了读锁，所以直接使用 UPDATE 语句更新会报错，因为读锁上不能进行写操作，需要先解锁，再进行 UPDATE 操作。

下面:

可以通过下面的实例来感受一下 MySQL 中表锁之写锁的用法。

假设我们要查询 teachers 表中所有的信息，但是，courses 表被加了写锁，请编写 SQL 语句，来实现查询 teachers 表中所有的信息。

我们可以使用下面的 SQL 语句：

-- 对 courses 表上写锁 --
LOCK TABLES courses WRITE;

-- 解锁 --
UNLOCK TABLES;

-- 查询 teachers 表中所有信息 --
SELECT * 
FROM teachers;

(2) 如何加行锁

MySQL 存储引擎的发展是从 MyISAM 到 InnoDB，锁从表锁发展到行锁。行锁的出现从某种程序上是为了弥补表锁的不足。比如MyISAM 不支持事务，InnoDB 支持事务。

表锁虽然开销小，加锁快，但高并发下性能低。行锁偏向于 InnoDB 存储引擎，虽然开销大，加锁慢，锁定粒度小，会出现死锁，但高并发下性能更高。

事务和行锁都是在确保数据准确的基础上提升并发的处理能力。因此，InnoDB 存储引擎相比 MyISAM 而言，更适合高并发场景，同时支持事务处理。InnoDB 与 MyISAM 相比最大不同之处也在于这两点：支持事务、采用行级锁。

行锁优缺
劣势 开销大、加锁慢、会出现死锁
优势 粒度小、发生锁冲突几率小、处理并发能力强
行锁是 MySQL 中粒度最细的一种锁机制，只对当前所操作的行进行加锁，行锁发生冲突的概率很低，其粒度最小，但加锁的代价最大。行锁有分为共享锁（S 锁）和排他锁（X 锁）。
行锁
----
S 锁 读锁，共享锁
X 锁 写锁，排他锁
InnoDB 的行锁是通过给索引项添加锁来实现的，这一点 MySQL 与 Oracle 不同，Oracle 是通过在数据库中对相应的数据行加锁来实现。InnoDB 行锁只有在通过索引条件检索数据时才能使用，否则会直接使用表级锁。需要注意的是行级锁一定要使用索引。

InnoDB 行锁的加锁的方式是自动加锁

* 对于 UPDATE、DELETE、INSERT 操作，InnoDB 会自动给涉及数据集添加排他锁
* 对于 SELECT 操作，InnoDB 不会添加任何锁

| | 操作 | 语句 | 自动加锁 |

| ---- | ---- | ---- |

| DQL | SELECT | 无 |

| DML | INSERT/DELETE/UPDATE | X 锁 |

-- 不会上锁 --
SELECT

-- 上写锁 --
INSERT, UPDATE, DELETE

显示上锁（手动）

InnoDB 手工加锁方式

LOCK IN SHARE MODE

FOR UPDATE

只能在事务内其作用，以保证当前会话事务锁定的行不会被其他会话修改。

-- 读锁 --
SELECT *
FROM table_name
LOCK IN SHARE MODE;

-- 写锁 --
SELECT *
FROM table_name
FOR UPDATE;
           
* 提交事务（commit）
* 回滚事务（rollback）
* 阻塞进程（kill）

如何排查锁

(1)如何排查表锁

查看表锁情况:

SHOW OPEN TABLES;

表锁分析

SQL语句大全

Table_locks_waited

：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次值加 1），此值高说明存在着较严重的表级锁争用情况

Table_locks_immediate

：产生表级锁定次数，不是可以立即获取锁的查询次数，每立即获取锁加 1

(2)如何排查行锁

行锁分析

SQL语句大全
* Innodb_row_lock_current_waits：当前正在等待锁定的数量
* Innodb_row_lock_time：从系统启动到现在锁定总时间长度
* Innodb_row_lock_time_avg：每次等待所花平均时间
* Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间
* Innodb_row_lock_waits：系统启动后到现在总共等待的次数

优化建议

* 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
* 合理设计索引，尽量缩小锁的范围
* 尽可能较少检索条件，避免间隙锁
* 尽量控制事务大小，减少锁定资源量和时间长度
* 尽可能低级别事务隔离

乐观锁和悲观锁

相信大家都遇到这种场景，当很多人（一两个人估计不行）同时对同一条数据做修改的时候，那么数据的最终结果是怎样的呢？

这也就是我们说的并发情况，这样会导致以下两种结果：

* 更新错误，你修改之后的数据可能被别人覆盖了，导致你很懵逼，甚至怀疑自己开发的功能是否有问题；
* 脏读，数据更新错误，导致读数据也是错的，查询出一些默认奇妙的数据，看到的不是你自己修改的结果。

这样的问题怎么解决呢？于是乎，锁就这样产生了，锁分为乐观锁和悲观锁，它的目的是用来解决并发控制的问题。

MyISAM 引擎不支持事务，所以不考虑它有乐观锁和悲观锁概念。MyISAM 只有表锁，锁又分为读锁和写锁。在这里我们只讨论InnoDB 引擎。

需要注意的是，乐观锁和悲观锁并不是解决并发控制的唯一手段（也可以使用消息中间件 kafka，MQ 之类的作为缓冲等等），而且乐观锁和悲观锁并不仅限制在 MySQL 中使用，它是一种概念，很多其他的应用，如 redis，memcached 等，只要存在并发情况的，都可以应用这种概念，只是方式上有些差别而已。

(1)乐观锁

乐观锁，简单地说，就是从应用系统层面上做并发控制，去加锁。

实现乐观锁常见的方式：版本号 version

实现方式，在数据表中增加版本号字段，每次对一条数据做更新之前，先查出该条数据的版本号，每次更新数据都会对版本号进行更新。在更新时，把之前查出的版本号跟库中数据的版本号进行比对，如果相同，则说明该条数据没有被修改过，执行更新。如果比对的结果是不一致的，则说明该条数据已经被其他人修改过了，则不更新，客户端进行相应的操作提醒。

使用版本号实现乐观锁

使用版本号时，可以在数据初始化时指定一个版本号，每次对数据的更新操作都对版本号执行 +1 操作。并判断当前版本号是不是该数据的最新的版本号。

-- 1.查询出商品信息 --
select status,version from t_goods where id=#{id};
-- 2.根据商品信息生成订单 --
-- 3.修改商品status为2 --
update t_goods
set status=2,version=version+1
where id=#{id} and version=#{version};
SQL语句大全

注意第二个事务执行 update 时，第一个事务已经提交了，所以第二个事务能够读取到第一个事务修改的 version。

下面这种极端的情况：

SQL语句大全

我们知道 MySQL 数据库引擎 InnoDB，事务的隔离级别是 Repeatable Read，因此是不会出现脏读、不可重复读。

在这种极端情况下，第二个事务的 update 由于不能读取第一个事务未提交的数据（第一个事务已经对这一条数据加了排他锁，第二个事务需要等待获取锁），第二个事务获取了排他锁后，会发现 version 已经发生了改变从而提交失败。

(2)悲观锁

悲观锁，简单地说，就是从数据库层面上做并发控制，去加锁。

悲观锁的实现方式有两种：共享锁（读锁）和排它锁（写锁）

共享锁（IS锁），实现方式是在 SQL 语句后加 LOCK IN SHARE MODE，比如 SELECT ... LOCK IN SHARE MODE，即在符合条件的rows 上都加了共享锁，这样的话，其他 session 可以读取这些记录，也可以继续添加 IS 锁，但是无法修改这些记录直到你这个加锁的 session 执行完成（否则直接锁等待超时）。

排它锁（IX锁），实现方式是在 SQL 语句后加

FOR UPDATE

，比如

SELECT ... FOR UPDATE

，即在符合条件的 rows 上都加了排它锁，其他 session也就无法在这些记录上添加任何的 S 锁或 X 锁。如果不存在一致性非锁定读的话，那么其他 session 是无法读取和修改这些记录的，但是 InnoDB 有非锁定读（快照读并不需要加锁），

for update

之后并不会阻塞其他

session

的快照读取操作，除了

select ...lock in share mode

和

select ... for update

这种显示加锁的查询操作。

通过对比，发现

for update

的加锁方式无非是比

lock in share mode

的方式多阻塞了

select...lock in share mode

的查询方式，并不会阻塞快照读。

MySQL InnoDB 引擎默认的修改数据语句：

update，delete，insert

都会自动给涉及到的数据加上排他锁，

select

语句默认不会加任何锁类型。

以排它锁为例

要使用悲观锁，我们必须关闭 MySQL 数据库的自动提交属性，因为 MySQL 默认使用 auto commit 模式，也就是说，当你执行一个更新操作后，MySQL 会立刻将结果进行提交。

set autocommit=0;
-- 0. 开始事务（三者选一就可以） --
begin;/begin work;/start transaction;
-- 1. 查询出商品信息 --
select status from t_goods where id=1 for update; 
-- 2. 根据商品信息生成订单 --
insert into t_orders (id,goods_id) values (null,1); 
-- 3. 修改商品 status 为 2 -- 
update t_goods set status=2; 
-- 4. 提交事务 --
commit;/commit work;
SQL语句大全

上面的查询语句中，我们使用了 select…for update 的方式， 这样就通过开启排他锁的方式实现了悲观锁。此时在 t_goods 表中，id 为 1 的 那条数据就被我们锁定了，其它的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。

补充：

* MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁,在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁。
* MySQL InnoDB 默认行级锁。行级锁都是基于索引的，如果一条 SQL 语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住。
* 从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行 retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

认识触发器

在 MySQL 中，触发器是一个存储程序，它对相关表中发生的插入、更新或删除等事件自动调用。

例如，你可以定义一个触发器，在向表中插入新行之前自动调用。

MySQL 支持响应 INSERT、UPDATE 或 DELETE 事件而调用的触发器。

SQL 标准定义了两种类型的触发器：行级触发器和语句级触发器。

* 行级触发器是为每条插入、更新或删除的记录激活的。 例如，如果一个表有 100 条记录被插入、更新或删除，那么这个触发器就会为受影响的 100 条记录自动调用 100 次。
* 语句级触发器对每个事务执行一次，不管有多少行被插入、更新或删除。

MySQL 只支持行级触发器。它不支持语句级触发器。
SQL语句大全

触发器的优点

* 触发器提供了另一种方法来检查数据的完整性。
* 触发器可以处理来自数据库层的错误。 
* 触发器提供了一种运行计划任务的替代方法。通过使用触发器，你不需要等待预定事件的运行，因为触发器会在表内数据发生变化之前或之后自动调用。
* 触发器对于审计表中的数据变化非常有用。

触发器的劣势

* 触发器只能提供扩展验证，而不是所有的验证。对于简单的验证，你可以使用 NOT NULL、UNIQUE、CHECK 和 FOREIGN KEY约束。
* 触发器可能很难排除故障，因为它们在数据库中自动执行，而客户端应用程序可能不知道。
* 触发器可能会增加 MySQL 服务器的开销。

(1) MySQL 创建触发器

本章节将带着大家学习如何使用 MySQL CREATE TRIGGER 语句在数据库中创建一个触发器。

MySQL CREATE TRIGGER 语法

CREATE TRIGGER 语句创建一个新的触发器。

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
ON table_name FOR EACH ROW
trigger_body;

在这个语法中：

首先，在` CREATE TRIGGER `关键字后面指定要创建的触发器的名称。注意，触发器的名称在数据库中必须是唯一的。
接下来，指定触发器的动作时间，可以是` BEFORE `或` AFTER`，表示在每条记录被修改之前或之后调用该触发器。
然后，指定激活触发器的操作，可以是 `INSERT`、`UPDATE` 或` DELETE`。
之后，在 `ON` 关键字后面指定触发器所属的表的名称。
最后，指定触发器激活时要执行的语句。如果你想执行多条语句，你可以使用` BEGIN END` 复合语句。

触发器主体可以访问被

DM

L 语句影响的列的值。

为了区分

DML

启动之前和之后的列的值，你可以使用

NEW

OLD

修饰符。

例如，如果你更新列描述，在触发器主体中，你可以访问更新前的描述值

OLD.description

和新值

NEW.description

。

下表说明了 OLD 和 NEW 修改器的可用性。

Trigger Event OLD NEW
INSERT No Yes
UPDATE
DELETE
(2)MySQL 删除触发器 
本章节将带着大家学习如何使用 MySQL DROP TRIGGER 语句，从数据库中删除一个触发器。

MySQL DROP TRIGGER 语法

DROP TRIGGER 语句从数据库中删除了一个触发器。

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

首先，在 DROP TRIGGER 关键字后面指定要删除的触发器的名称。
第二，指定触发器所属模式的名称。如果跳过模式名称，该语句将删除当前数据库中的触发器。
第三，使用 IF EXISTS 选项，在触发器存在的情况下有条件地删除触发器。IF EXISTS 子句是可选的。

如果你不使用 IF EXISTS 子句而删除一个不存在的触发器，MySQL 会发出一个错误。然而，如果你使用 IF EXISTS 子句，MySQL 会发出一个注释。

DROP TRIGGER 要求与触发器相关的表有 TRIGGER 权限。

注意，如果你删除一个表，MySQL 将自动放弃与该表相关的所有触发器。

扩展

having

where

 的区别

  • having只能用在group by之后，对分组后的结果进行筛选(即使用having的前提条件是分组)
  • where 肯定在 group by 之前

  • where 后的条件表达式里不允许使用聚合函数，而having可以

    当一个查询语句同时出现了

    where

     ，

    group by

    having

    order by

     的时候，执行顺序和编写顺序是：
  • 执行

    where ...

     对全表数据做筛选，返回第一个结果集
  • 针对第一个结果集使用

    group by

     分组，返回第二个结果集
  • 针对第二个结果集中的每一组数据执行

    select...

     ，有几次执行几次，返回第三个结果集
  • 针对第三个结果集执行

    having ...

     进行筛选，返回第四个结果集
  • 针对第四个结果集排序

练习题

第二高的薪水

链接：

https://leetcode-cn.com/problems/second-highest-salary/

(1)思路一：使用子查询

使用子查询找出最大的

MAX（Salar）

，然后再找出小于

MAX(Salary)

的最大值就是课程成绩的第二高值。

SELECT  DISTINCT MAX(Salary) AS SecondHighestSalary
FROM Employee 
WHERE `Salary` < (
    SELECT MAX(Salary) 
    FROM Employee
);

但是题目中有特殊的输入情况，远远这样是不够的

(2)思路二：使用子查询和

LIMIT

子句

语法:

LIMIT n

子句表示查询结果返回前n条数据,

OFFSET x

表示跳过x条语句

=>

LIMIT y OFFSET x

分句表示查询结果跳过 x 条数据，读取前 y 条数据;

SELECT DISTINCT
    Salary AS SecondHighestSalary
FROM
    Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1

特殊情况,本表没有第二高的工资,只有一项纪录,为了克服这种情况,我们将此设定为临时表

SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary;

(3)思路三:判断空值的函数（IFNULL）

IFNULL(a,b)函数解释：(1)如果value1不是空，结果返回a;(2)如果value1是空，结果返回b

所以，解题思路应当是：

SELECT IFNULL(SQL语句,null) AS 'SecondHighestSalary';

 
SELECT IFNULL(
(SELECT DISTINCT Salary
FROM Employee 
ORDER BY Salary DESC
limit 1,1),NULL
) AS SecondHighestSalary;

第N高的薪水

limit函数结构为：limit m , n,表示从第m+1条数据取出n条数据。

网址：

https://leetcode-cn.com/problems/nth-highest-salary/

参考上面题目的做法，代码如下

(1)解法一：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  DECLARE m INT;
  SET m = N - 1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT IFNULL(
            (SELECT DISTINCT Salary
            FROM Employee 
            ORDER BY Salary DESC
            LIMIT m,1),NULL
            ) 
  );
END

(2)解法二：单表查询

若本题采取order by排序加limit限制得到。有两个细节：

* 同薪同名且不跳级的问题，解决办法是用group by按薪水分组后再order by

* 排名第N高意味着要跳过N-1个数据，由于无法直接用limit N-1，所以需先在函数开头处理N为N=N-1。

注：这里不能直接用limit N-1是因为limit和offset字段后面只接受正整数（意味着0、负数、小数都不行）或者单一变量（意味着不能用表达式），也就是说想取一条，limit 2-1、limit 1.1这类的写法都是报错的。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N := N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
            salary
      FROM 
            employee
      GROUP BY 
            salary
      ORDER BY 
            salary DESC
      LIMIT N, 1
  );
END

连续出现的数字

https://leetcode-cn.com/problems/consecutive-numbers/

(1)官方解法：三表连接

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num
;

超过经理收入的员工

https://leetcode-cn.com/problems/employees-earning-more-than-their-managers/

下面代码是我的解法。

SELECT a.Name AS Employee
FROM Employee a
WHERE ManagerId = (
    SELECT b.Id 
    FROM Employee b
    WHERE b.Id = a.ManagerId
) AND Salary > (
    SELECT Salary
    FROM Employee c
    WHERE c.Id = a.ManagerId
);

因为我的解法实在是不够简约，所以参考了官方解法

(2)解法二

SELECT
    a.Name AS 'Employee'
FROM
    Employee AS a,
    Employee AS b
WHERE
    a.ManagerId = b.Id
        AND a.Salary > b.Salary;

(3)解法三：使用

JOIN

语句

实际上， JOIN 是一个更常用也更有效的将表连起来的办法，我们使用 ON 来指明条件。

SELECT
     a.NAME AS Employee
FROM Employee AS a JOIN Employee AS b
     ON a.ManagerId = b.Id
     AND a.Salary > b.Salary;

查找重复的电子邮箱

https://leetcode-cn.com/problems/duplicate-emails/

(1)解法一：（自己写的，不推荐）

SELECT DISTINCT a.Email 
FROM  Person a,
      Person b
WHERE a.Email =b.Email and a.Id != b.Id;

(2)解法二：使用

GROUP BY

和临时表

重复的电子邮箱存在多次。要计算每封电子邮件的存在次数，我们可以使用以下代码。

select Email, count(Email) as num
from Person
group by Email;

以此作为临时表，我们可以得到下面的解决方案。

select Email from
(
  select Email, count(Email) as num
  from Person
  group by Email
) as statistic
where num > 1
;

GROUP BY

HAVING

条件

向

GROUP BY

添加条件的一种更常用的方法是使用

HAVING

子句，该子句更为简单高效。

所以我们可以将上面的解决方案重写为：

select Email
from Person
group by Email
having count(Email) > 1;

从不订购的客户

https://leetcode-cn.com/problems/customers-who-never-order/

(1)解法一:多表连接

SELECT c.Name AS Customers
FROM Customers c
LEFT JOIN Orders o
ON c.Id = o.CustomerId
WHERE o.CustomerId is NULL;

(2)解法二:使用子查询和

NOT IN

 
SELECT `c`.`Name` AS 'Customers'
FROM `Customers` `c`
WHERE `c`.Id NOT IN
(
    SELECT `CustomerId` 
    FROM `Orders`
);

部门工资最高的员工

https://leetcode-cn.com/problems/department-highest-salary/

解法步骤如下：

(1)先建立临时表，查询每个部门内的最高工资

SELECT DepartmentId, MAX(Salary)
FROM Employee
GROUP BY DepartmentId

(2)使用

JOIN

IN

 
SELECT  d.Name AS Department,
        e.Name AS Employee,
        e.Salary
FROM 
        Employee e
LEFT JOIN 
        Department d
ON e.DepartmentId = d.Id
WHERE 
(e.DepartmentId , e.Salary) IN 
(
    SELECT DepartmentId, MAX(Salary)
    FROM Employee
    GROUP BY DepartmentId
)

部门工资前三高的所有员工

https://leetcode-cn.com/problems/department-top-three-salaries/

解题步骤

(1)公司里前 3 高的薪水意味着有不超过 3 个工资比这些值大。

select e1.Name as 'Employee', e1.Salary
from Employee e1
where 3 >
(
    select count(distinct e2.Salary)
    from Employee e2
    where e2.Salary > e1.Salary
)
;

(2)连接表

SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
    Employee e1
        JOIN
    Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        )
;

刚开始这道题我还没有看懂,看了其他大神的解析才略微了解其意

对于步骤一中的SQL语句,可以这么理解

存在表中有数据:

e1 = e2 = [4,5,6,7,8]

=>那么

e1.Salary = 4，e2.Salary

可以取值

[5,6,7,8]

count(DISTINCT e2.Salary) = 4

e1.Salary = 5

e2.Salary

[6,7,8]

count(DISTINCT e2.Salary) = 3

e1.Salary = 6，e2.Salary

[7,8]

count(DISTINCT e2.Salary) = 2

e1.Salary = 7

e2.Salary

[8]

count(DISTINCT e2.Salary) = 1

e1.Salary = 8

e2.Salary

[]

count(DISTINCT e2.Salary) = 0

最后

3 > count(DISTINCT e2.Salary)

，所以

e1.Salary

可取值为

[6,7,8]

，即集合前 3 高的薪水

删除重复的电子邮箱

https://leetcode-cn.com/problems/delete-duplicate-emails/

首先：

SELECT 
    p1.*, p2.*
FROM
    Person p1
        JOIN
    Person p2 ON p1.Email = p2.Email
ORDER BY p1.Id;
SQL语句大全

那么，要删除的，就是其中

p1.id > p2.id

的表数据

(1)符合条件的：

select p1.*
from person p1,
     person p2
where p1.id > p2.id and p1.email=p2.email;

(2)delect

DELETE p1 
FROM Person p1,
     Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id

上升的温度

https://leetcode-cn.com/problems/rising-temperature/

 
SELECT w2.id
FROM  Weather w1
JOIN  Weather w2
ON DATEDIFF(w2.recordDate,w1.recordDate) = 1 
   AND w2.Temperature >w1.Temperature ;

大的国家

https://leetcode-cn.com/problems/big-countries/

 
SELECT name,population,area
FROM World
WHERE area>=3000000 OR population>=25000000;

(2)解法二：

SELECT
    name, population, area
FROM
    world
WHERE
    area > 3000000

UNION

SELECT
    name, population, area
FROM
    world
WHERE
    population > 25000000
;

有趣的电影

https://leetcode-cn.com/problems/not-boring-movies/

(1)解法：使用 MOD() 函数

select *
from cinema
where mod(id,2) = 1 and description != 'boring'
order by rating desc

超过5名学生的课

https://leetcode-cn.com/problems/classes-more-than-5-students/

(1)解法一：使用 GROUP BY 和 COUNT 获得每门课程的学生数量。

临时表

SELECT
    class, COUNT(DISTINCT student)
FROM
    courses
GROUP BY class
;

因此

SELECT
    class
FROM
    (SELECT
        class, COUNT(DISTINCT student) AS num
    FROM
        courses
    GROUP BY class) AS temp_table
WHERE
    num >= 5
;
           
SELECT
    class
FROM
    courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5;
上一篇: 【校验】校验汇总
下一篇: Qt qml 下使QtCharts