mysql -sql 语句

1 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`;

---

2 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
• count ：制定返回的数量
• LIMIT 关键字的位置，需要放在 ORDER BY 关键字的后面，否则会报错。

SELECT `column_name`, `column_name`
FROM `table_name`
LIMIT `offset` , `count`;

---

3 LEVEL3

3.1 算数函数（1）

3.1.1 AVG()

(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赋予。

3.1.2 MAX()

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

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

3.1.3 MIN()

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

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

3.1.4 SUM()

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

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

3.2 算数函数（2）

3.2.1 ROUND()

使用 ROUND() 函数将数值四舍五入：ROUND()函数用于把数值字段舍入为指定的小数位数。

• ROUND(X):返回参数 X 四舍五入后的一个整数。
• ROUND(X, D):返回参数 X 四舍五入且保留 D 位小数后的一个数字。如果 D 为 0，结果将没有小数点或小数部分。
• 注意：ROUND() 返回值数据类型会被变换为一个 BIGINT 。

  SELECT ROUND(`column_name`, `decimals`) 
  FROM `table_name`;

3.2.2 NULL()

使用 NULL() 函数判断空值
(1)   ISNULL():  ISNULL()函数用于判断字段是否为 NULL，它只有一个参数 column_name 为列名，根据column_name 列中的字段是否为 NULL 值返回 0 或 1。

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

(2)   IFNULL():  IFNULL()函数也用于判断字段是否为NULL，但是与 ISNULL() 不同的是它接收两个参数，第一个参数 column_name 为列名，第二个参数value相当于备用值。
       其中：
       如果 column_name 列中的某个字段是 NULL 则返回 value 值，不是则返回对应内容。
        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.2.3 COUNT()

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

(1) COUNT( column_name ) :
       COUNT(column_name) 函数会对指定列具有的行数进行计数，但是会除去值为 NULL 的行。该函数主要用于查看各列数据的数量情况，便于统计数据的缺失值。
       假如出现某一列的数据全为 NULL 值的情况，使用COUNT( column_name ) 函数对该列进行计数，会返回 0。

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

(2) 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`;

3.3 时间函数（1）

3.3.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));

(2) 使用 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
使用 EXTRACT() 函数提取指定的时间信息:EXTRACT() 函数用于返回日期/时间的单独部分，如 YEAR (年)、MONTH (月)、DAY (日)、HOUR (小时)、MINUTE (分钟)、 SECOND (秒)。

  SELECT EXTRACT(unit FROM date)
  FROM `table`

(3) 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`;

（4）使用 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 表示星期。

3.4 时间函数（2）

3.4.1 DATE_ADD()

使用 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`;

3.4.2 DATE_SUB()

使用 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.4.3 日期差

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

3.4.3.1 DATEDIFF()

示例代码
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.4.3.1 TIMESTAMPDIFF()

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`;

4 LEVEL4

4.1 约束

在 SQL 中，约束是规定表中的数据规则。若存在违反约束的行为，行为就会阻止。

4.1.1 非空约束

非空约束 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 而不是空串指定。

4.1.2 唯一约束

• 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.1.3 主键约束

主键约束 PRIMARY KEY

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

4.1.4 外键约束

外键概念：一个表中的 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

4.1.5 检查约束

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 约束命名
  MySQL / SQL Server / Oracle / MS Access：

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约束。
  MySQL / SQL Server / Oracle / MS Access：

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 关键字。
  MYSQL：

ALTER TABLE `courses` 
DROP CHECK chk_courses
```sql
SQL Server / Oracle / MS Access：
```sql
ALTER TABLE `courses` 
DROP CONSTRAINT chk_courses

4.1.6 默认约束。

默认值（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

SQL Server / Oracle / MS Access：

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

4.2 多表连结

4.2.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：又称笛卡尔积，两个表数据一一对应，返回结果的行数等于两个表行数的乘积

4.2.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 子句给出。

4.2.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表示进行匹配的条件。

4.2.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 ;

4.2.3.2 右外连接

RIGHT JOIN

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

语法：

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

4.2.3.3 全外连接

FULL (OUTER) JOIN

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

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.2.4 交叉连接 CROSS JOIN

4.2.4.1 概念和理解

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

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

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

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

交叉连接的两种定义方式

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

隐式交叉连接：不需要使用 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`;

5 LEVEL 5

5.1 分组查询 GROUP BY

5.1.1 单表

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

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`;

5.1.2 多表

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

使用 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;

5.2 过滤 HAVING

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))

5.3 简单的子查询

5.3.1 SELECT子查询

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'
);

5.3.2 INSERT子查询

INSERT 语句中的子查询: 对于 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`;

5.3.3 UPDATE子查询

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'
);

5.3.4 DELETE子查询

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，而整个语句是为了删除数据。

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

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

5.4 子查询进阶

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

5.4.1 内联视图子查询

内联视图子查询实际上就是将查询的结果集作为一个查询表，继续进行查询操作。
我们可以通过下面的实例来感受一下 内联视图子查询 的用法。

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

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

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

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

5.4.2 IN子查询

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，父查询为查询满足条件的课程名称。

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

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

5.4.3 ANY子查询

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'
	);

5.4.4 ALL子查询

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。

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

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

5.4.5 多列子查询

多列子查询

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

对于多列子查询：

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

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

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

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

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

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

5.4.5 HAVING 子句中的子查询

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`
	)
);

6 LEVEL6

6.1 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;

7 扩展

having和where的区别

• having只能用在group by之后，对分组后的结果进行筛选(即使用having的前提条件是分组)
• where 肯定在 group by 之前
• where 后的条件表达式里不允许使用聚合函数，而having可以
  注意：
  当一个查询语句同时出现了where，group by，having，order by的时候，执行顺序和编写顺序是：
• 执行where ... 对全表数据做筛选，返回第一个结果集
• 针对第一个结果集使用group by分组，返回第二个结果集
• 针对第二个结果集中的每一组数据执行select...，有几次执行几次，返回第三个结果集
• 针对第三个结果集执行having ... 进行筛选，返回第四个结果集
• 针对第四个结果集排序

8 练习题

8.1 第二高的薪水

链接：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;

8.2 第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

8.3 连续出现的数字

链接：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
;

8.4 超过经理收入的员工

链接：https://leetcode-cn.com/problems/employees-earning-more-than-their-managers/
(1)解法一：
下面代码是我的解法。

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;

8.5 查找重复的电子邮箱

链接：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
;

(3)解法三：使用 GROUP BY 和 HAVING 条件

向 GROUP BY 添加条件的一种更常用的方法是使用 HAVING 子句，该子句更为简单高效。

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

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

8.6 从不订购的客户

链接：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`
);

8.7 部门工资最高的员工

链接：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
)

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

链接：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 高的薪水

8.9 删除重复的电子邮箱

链接：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;

那么，要删除的，就是其中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

8.10 上升的温度

链接：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 ;

8.11 大的国家

链接：https://leetcode-cn.com/problems/big-countries/
(1)解法一：

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
;

8.12 有趣的电影

链接：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

8.13 超过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
;

(2)解法二：

SELECT
    class
FROM
    courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5;

9 参考网址

https://www.lintcode.com/