【MySQL 系列】MySQL 语句篇_DQL 语句

以你之姓@ 2024-04-06 15:37 101阅读 0赞

> DQL（Data Query Language），即数据查询语言，用来查询数据记录。DQL 基本结构由 `SELECT FROM`、`WHERE`、`JOIN` 等子句构成。
> 
> DQL 语句并不会改变数据库，而是让数据库将查询结果发送结果集给客户端，返回的结果是一张虚拟表。

--------------------

#### 文章目录 ####

*   *   *   *  1、MySQL 中的 DQL 语句
             *   *  1.1、数据查询语言--DQL
                 *  1.2、DQL 语句的格式
             *  2、MySQL 中 的 DQL 语句详解
             *   *  2.1、DQL 语句：SELECT FROM
                 *   *  2.1.1、关键字 AS
                     *  2.1.2、关键字 DISTINCT
                 *  2.2、DQL 子句：JOIN
                 *   *  2.2.1、关于示例
                     *  2.2.2、交叉连接（CROSS JOIN）
                     *  2.2.3、内连接（INNER JOIN JOIN）
                     *  2.2.4、左连接（LEFT JOIN）
                     *  2.2.5、右连接（RIGHT JOIN）
                 *  2.3、DQL 子句：WHERE
                 *   *  2.3.1、运算符 AND
                     *  2.3.2、运算符 OR
                     *  2.3.3、运算符 IN
                     *  2.3.4、运算符 NOT IN
                     *  2.3.5、运算符 BETWEEN
                     *  2.3.6、运算符 LIKE
                     *  2.3.7、运算符 NULL
                     *  2.3.8、操作符 EXISTS
                 *  2.4、DQL 子句：GROUP BY
                 *  2.5、DQL 子句：ORDER BY
                 *   *  2.5.1、使用 CASE 实现自定义排序
                     *  2.5.2、使用 FIELD 函数实现自定义排序
                 *  2.6、DQL 子句：LIMIT
                 *  2.7、DQL子查询
                 *  2.8、DQL集合操作符：UNION

--------------------

##### 1、MySQL 中的 DQL 语句 #####

###### 1.1、数据查询语言–DQL ######

DQL（Data Query Language），即数据查询语言，用来查询数据记录。DQL 基本结构由 `SELECT FROM`、`WHERE`、`JOIN` 等子句构成。

DQL 语句并不会改变数据库，而是让数据库将查询结果发送结果集给客户端，返回的结果是一张虚拟表。

###### 1.2、DQL 语句的格式 ######

DQL 的语法格式：

SELECT *|columns_list   							 								 # columns_list 查询字段（或表达式）列表 ，* 代表全部字段
    FROM table_name							 													 # table_name 表名
    [(LEFT | RIGHT | INNER | CROSS) JOIN join_table_name]  # join_table_name 联合查询表表名
    [WHERE query_condition]																 # query_condition 是查询条件
    [GROUP BY group_columns_list [HAVING group_clause]]    # group_columns_list 分组字段列表 ，group_clause 分组条件
    [ORDER BY order_columns_list [ASC|DESC]]							 # order_columns_list 排序字段列表 ，ASC 升序 ，DESC 降序
    [LIMIT row_count OFFSET offset]												 # row_count 执行返回的最大行数 ，offset 指定返回的第一行的偏移量

--------------------

##### 2、MySQL 中 的 DQL 语句详解 #####

###### 2.1、DQL 语句：SELECT FROM ######

`SELECT FROM` 语句用于从一个或多个表中检索数据，是 MySQL 中使用最多的语句。

以下是 `SELECT FROM` 语句的语法：

SELECT *|columns_list														# columns_list 查询字段（或表达式）列表 ，* 代表全部字段
    FROM table_name [;]															# table_name 表名 ，分号 ; 表示语句结束

语句说明：① 关键字 `SELECT` 后跟着一个或多个数据表的列；② `columns_list` 为查询字段（或表达式）的列表，可以有多个列，列之间需要用逗号 “`,`” 分隔；③ 当要检索数据表中的所有列的时候，使用星号 “`*`” 表示全部字段；④ 关键字 `FROM` 后跟着要从中检索数据的表名；⑤ 分号 “`;`” 表示语句的结束，它是可选的。如果有两条或更多条语句，则需要使用分号 “`;`” 将它们分开，以便 MySQL 单独执行每条语句。

举例：

我们以 MySQL 官方示例数据库 Employees 数据库为例，分别使用星号 “`*`” 的方式查询 “employees（国家）表” 的全部字段和使用指定字段的方式查询 “employees” 表的部分字段。

SELECT * FROM employees;     											# 使用 * 查询全部字段

SELECT
    	emp_no,																				# 使用指定字段的方式查询字段
    	birth_date,																		# 效果与使用 * 相同
    	first_name,
    	last_name,
    	gender,
    	hire_date 
    FROM
    	employees

![image-20240305101215085][]

###### 2.1.1、关键字 AS ######

关键字 `AS` 可以用来给 列、表、派生表或者计算结果 取别名。

以下是关键字 `AS` 语句的语法：

SELECT column1,column2 AS "column2_alias" 
    FROM table_name;																	# 可给列取一个新别名
    --
    SELECT column1,column2 
    FROM table_name AS "table_name_alias";						# 可给表/派生表取一个新别名
    --
    SELECT column1 + column2 AS "alias"
    FROM table_name;																	# 可给计算｜总结的结果用另外一个新名称来代替

语句说明：①、`AS` 关键字后面跟的是列的别名 `alias`；②、当别名 `alias` 中包含空格时，必须使用将别名引起来，即 `alias`；③ `AS` 关键字是可选的；④ 除了为字段指定别名，还可以为表达式指定别名。例如： `SELECT NOW();` `Current Time;` `FROM dual;`。

举例：

SELECT
    	emp.emp_no,
    	YEAR ( birth_date ) AS YEAR,									# 给计算表达式取别名
    	first_name AS NAME 														# 给列取别名
    FROM
    	employees AS emp;															# 给表取别名

![image-20240305103228542][]

根据结果我们可以看到，当我们使用 `AS` 关键字表示查询字段或计算结果别名时，查询的结果返回字段名称将为别名名称。

###### 2.1.2、关键字 DISTINCT ######

关键词 `DISTINCT` 用于返回唯一不同的值。`SELECT DISTINCT` 表示查询结果中，去掉了重复的行。

以下是关键字 `DISTINCT` 的语法：

SELECT DISTINCT columns_list FROM table_name			# 去掉查询返回的记录结果中重复的记录

注意：`SELECT` 语句不加关键字 `DISTINCT` 时，是有一个默认关键字 `ALL` 存在的，表示返回所有的记录。

举例：

我们以 MySQL 官方示例数据库 Employees 数据库为例，使用 `DISTINCT` 关键字查询 “employees” 表中存在重复的字段。

SELECT DISTINCT gender FROM employees;

![image-20240305103838057][]

根据结果我们可以看到，重复的记录已经被去除。

###### 2.2、DQL 子句：JOIN ######

在 MySQL 中，`JOIN` 语句用于将数据库中的两个表或者多个表组合起来。

MySQL 目前支持 4 种类型的连接：交叉连接（CROSS JOIN）、内联接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）。

MySQL 目前不支持全连接（FULL OUTER JOIN）。

![image-20240305104515557][]

以下是 MySQL 包含 `JOIN` 子句的 `SELECT` 语句语法：

# LEFT JOIN 左关联，此时 a 表为主表，a、b 关联后的临时表中 a 表所有数据均存在
    SELECT * FROM a LEFT JOIN b ON a.id = b.id
     --
    # RIGHT JOIN 右关联，此时 b 表为主表，a、b 关联后的临时表中 b 表所有数据均存在
    SELECT * FROM a RIGHT JOIN b ON a.id = b.id
    --
    # INNER JOIN == JOIN 等值关联，返回两个表中关联字段相等的数据行
    SELECT * FROM a INNER JOIN b ON a.id = b.id

语句说明：①、`ON` 用来生成关联临时表，生成的临时表会将主表的所有记录保存下来，等待 `WHERE` 语句的进一步筛选；②、两表以上关联时需要用到多条 `JOIN` 语句，一般用 `LEFT JOIN` 确立唯一的主表。

###### 2.2.1、关于示例 ######

在 `JOIN` 相关的以下示例中，我们将使用 `student` 和 `student_score` 两个表来完成，下面是两张表的结构创建和数据插入语句。

CREATE TABLE `student` (`student_id` int NOT NULL , `name` varchar(45) NOT NULL , PRIMARY KEY (`student_id`));
    CREATE TABLE `student_score` (`student_id` int NOT NULL , `subject` varchar(45) NOT NULL , `score` int NOT NULL);
    ---
    INSERT INTO `student` (`student_id`, `name`) VALUES (1,'Tim') , (2,'Jim') , (3,'Lucy');
    INSERT INTO `student_score` (`student_id`, `subject`, `score`) VALUES (1,'English',90) , (1,'Math',80) , (2,'English',85) , (2,'Math',88) , (5,'English',92);

查看两个表的结构：

SELECT * FROM student;																	# 学生信息表

![image-20240221174746742][]

SELECT * FROM student_score;														# 学生成绩表

![image-20240221174843130][]

###### 2.2.2、交叉连接（CROSS JOIN） ######

交叉连接返回两个集合的笛卡尔积。也就是两个表中的所有的行的所有可能的组合。这相当于内连接没有连接条件或者连接条件永远为真。

如果一个有 `m` 行的表和另一个有 `n` 行的表，它们交叉连接将返回 `m * n` 行数据。

举例：

我们以刚才示例中新建的 `student` 和 `student_score` 两个表为例，分别采用显式和隐式两种方式交叉连接 `student` 和 `student_score` 表。

SELECT																									# 显示交叉连接
      student.*,
      student_score.*
    FROM
      student CROSS JOIN student_score;
    ---  
    SELECT																									# 隐示交叉连接（这两种方式的输出一样）
      student.*,
      student_score.*
    FROM
      student, student_score;

![image-20240221175853025][]

根据结果我们可以看到，使用 `CROSS JOIN` 子句的 `SELECT` 语句查询后的结果为两个集合的笛卡尔积。也就是两个表中的所有的行的所有可能的组合。

###### 2.2.3、内连接（INNER JOIN JOIN） ######

内连接基于连接条件组合两个表中的数据。内连接相当于加了过滤条件的交叉连接。

内连接将第一个表的每一行与第二个表的每一行进行比较，如果满足给定的连接条件，则将两个表的行组合在一起作为结果集中的一行。

举例：

我们以刚才示例中新建的 `student` 和 `student_score` 两个表为例，将 `student` 表和 `student_score` 表内连接。

SELECT
      student.*,
      student_score.*
    FROM
      student
      INNER JOIN student_score
      ON student.student_id = student_score.student_id;
    ---
    SELECT																									# 隐藏 INNER JOIN，二者等价
      student.*,
      student_score.*
    FROM
      student, student_score
      WHERE student.student_id = student_score.student_id;

![image-20240221181538727][]

注意输出结果中，`student` 表中 `student_id` 为 `3` 的行和 `student_score` 表中 `student_id` 为 `5` 的行没有出现在输出结果中，这是因为他们没有满足连接条件：`student.student_id = student_score.student_id`。

由于两个表都使用相同的字段进行等值匹配，因此我们还可以使用 `USING` 以下查询中所示的子句：

SELECT
      student.*,
      student_score.*
    FROM
      student
      INNER JOIN student_score USING(student_id);

###### 2.2.4、左连接（LEFT JOIN） ######

左连接是左外连接的简称，左连接需要连接条件。两个表左连接时，第一个表称为左表，第二表称为右表。例如 `A LEFT JOIN B`，`A` 是左表，`B` 是右表。

左连接以左表的数据行为基础，根据连接匹配右表的每一行，如果匹配成功则将左表和右表的行组合成新的数据行返回；如果匹配不成功则将左表的行和 NULL 值组合成新的数据行返回。

举例：

我们以刚才示例中新建的 `student` 和 `student_score` 两个表为例，将 `student` 表和 `student_score` 进行左连接。

SELECT
      student.*,
      student_score.*
    FROM
      student
      LEFT JOIN student_score
      ON student.student_id = student_score.student_id;

![image-20240221182559011][]

根据结果我们可以看到：① 结果集中包含了 `student` 表的所有记录行；② `student_score` 表中不包含 `student_id = 3` 的记录行，因此结果几种最后一行中来自 `student_score` 的列的内容为 `NULL`；③ `student_score` 表存在多条 `student_id` 为 `1` 和 `2` 的记录，因此 `student` 表也产生了多行数据。

由于两个表都使用相同的字段进行等值匹配，因此我们同样可以使用 `USING` 以下查询中所示的子句：

SELECT
      student.*,
      student_score.*
    FROM
      student
      LEFT JOIN student_score USING(student_id);

###### 2.2.5、右连接（RIGHT JOIN） ######

右连接是右外连接的简称，右连接需要连接条件。

右连接与左连接处理逻辑相反，右连接以右表的数据行为基础，根据条件匹配左表中的数据。如果匹配不到左表中的数据，则左表中的列为 `NULL` 值。

举例：

我们以刚才示例中新建的 `student` 和 `student_score` 两个表为例，将 `student` 表和 `student_score` 进行右连接。

SELECT
      student.*,
      student_score.*
    FROM
      student
      RIGHT JOIN student_score
      ON student.student_id = student_score.student_id;

![image-20240221182920241][]

根据结果我们可以看到，由于左表中不存在到与右表 `student_id = 5` 匹配的记录，因此最后一行左表的列的值为 `NULL`。

注意：① 右连接其实是左右表交换位置的左连接，即 `A RIGHT JOIN B` 就是 `B LEFT JOIN A`，因此右连接很少使用；② 由于两个表都使用相同的字段进行等值匹配，因此我们依旧可以使用 `USING` 关键字。

###### 2.3、DQL 子句：WHERE ######

默认情况下，`SELECT` 查询数据表中的所有行数。但我们只想查询满足一定条件的数据，就要用到 `WHERE` 子句。

`WHERE` 子句允许您为 `SELECT` 查询指定搜索条件。

以下是包含 `WHERE` 子句的 `SELECT` 语句语法：

SELECT *|columns_list   							 								 # columns_list 查询字段（或表达式）列表 ，* 代表全部字段
    FROM table_name							 													 # table_name 表名
    WHERE query_condition 																 # query_condition 是查询条件

说明：① 其中 `query_condition` 就是查询条件，它的结果是一个布尔值，其值可能为 `TRUE`， `FALSE` 或 `UNKNOWN`。最终， `SELECT` 语句返回的结果集就是满足查询条件结果为 `TRUE` 的记录；② `WHERE` 的查询条件一般用来比较某个字段是否匹配某个值，一般形式为：`column_name = value`；③ 查询条件也可以是使用 `AND` , `OR` 和 `NOT` 逻辑运算符一个或多个表达式的组合；④ 除了用在 `SELECT` 语句之外，`WHERE` 子句还可以用在 `UPDATE` 和 `DELETE` 语句中，用来指定要更新或删除的行。

举例：

我们以 MySQL 官方示例数据库 Employees 数据库为例，使用 `WHERE` 查询指定搜索条件的记录。

SELECT
    	* 
    FROM
    	employees 
    WHERE
    	gender = 'M';																					# 查询性别为 M（male） 的员工

![image-20240305105857403][]

###### 2.3.1、运算符 AND ######

当使用 `SELECT` 查询数据时，如果 `WHERE` 子句中有多个条件，可以根据需要使用 `AND`, `OR`, 或者 `NOT` 运算符将他们组合起来。

`AND` 运算符是一个双目逻辑运算符，用来组合两个操作数。只有当两个操作数都为真时，结果才返回真，否则返回假或者 `NULL`。

`AND` 运算符语法：

a AND b

其中，`a` 和 `b` 是 `AND` 的两个操作数，可以是表达式，也可以是值。不像一些编程语言，MySQL 中没有布尔类型，`AND` 的运算结果是 `1`, `0`, 或着 `NULL`。

说明：① `AND` 是双目运算符，需要两个布尔操作数； ② 如果两个操作数都不为 `0` (`FALSE`) 并且不为 `NULL` 时，则 `AND` 运算的结果为 `1`；③ 如果有一个操作数为 `0` (`FALSE`)，则 `AND` 运算的结果为 `0`；④ 如果两个操作数中有一个为 `NULL`，且另一个不为 `0` (`FALSE`)，则返回 `NULL`；⑤ 两个操作数的前后顺序不影响 `AND` 操作符的运算结果； ⑥ `WHERE` 子句中的 `AND` 连接两个比较条件，过滤那些同时满足两个比较条件的记录行。

###### 2.3.2、运算符 OR ######

`OR` 运算符是一个双目逻辑运算符，用来组合两个操作数。只要两个操作数中的一个为真时，结果就返回真，否则返回假或者 `NULL`。

`OR` 运算符语法：

a AND b

其中，`a` 和 `b` 是 `OR` 的两个操作数，可以是表达式，也可以是值。不像一些编程语言，MySQL 中没有布尔类型，`OR` 的运算结果是 `1`, `0`, 或着 `NULL`。

说明：① `OR` 是双目运算符，需要两个布尔操作数；② 如果两个操作数中至少一个操作数为 `1` (`TURE`)，则 `OR` 运算的结果为 `1`；③ 如果两个操作数都为 `0` (`FALSE`)，则 `OR` 运算的结果为 `0`；④ 如果两个操作数有一个为 `NULL`，另一个为 `0` (`FALSE`) 或者 `NULL`，则 `OR` 运算的结果为 `NULL`；⑤ 两个操作数的前后顺序不影响 `OR` 操作符的运算结果；⑥ `AND` 运算符的优先级高于 `OR`。使用括号更改计算顺序；⑦ `WHERE` 子句中的 `OR` 连接两个比较条件，过滤那些至少满足其中一个比较条件的记录行。

###### 2.3.3、运算符 IN ######

在使用 SQL 查询的时候，我们可能需要判断一个字段的值是否位于一组值中，这就需要用到 `IN` 运算符了。`IN` 运算符用来检查一个字段或值是否包含在一个集合中，如果值包含在集合中返回 `1`，否则返回 `0`。

`IN` 是一个双目运算符，它需要 2 个操作数。以下是 `IN` 运算符的语法：

expression IN (value1, value2, ...)										  # expression 可以是一个字段名、值或其他的表达式

说明：① `IN` 是一个双目运算符，需要 2 个操作数；② `IN` 运算符左侧操作数是字段名或者值，右侧操作数是值列表或者子查询结果；③ `IN` 运算符左侧的操作数的值是右侧操作数集合列表中的其中一个时，返回 `1`。否则 返回 `0`；④ `IN` 运算符可以看作是多个 `OR` 运算符组合的简化版本；⑤ `IN` 运算符的否定操作是 `NOT IN`。

###### 2.3.4、运算符 NOT IN ######

`NOT` 是一个逻辑运算符，用来用来否定一个操作。`NOT IN` 是 `IN` 的否定操作符。

`NOT IN` 和 `IN` 用法几乎一样，只是在 `IN` 前面添加一个 `NOT` 关键字，`IN` 的否定操作符。 `NOT IN` 语法如下：

expression NOT IN (value1, value2, ...)									# expression 可以是一个字段名、值或其他的表达式

说明：① `NOT IN` 检查左侧值是否不包含于右侧的值列表中；② `NOT IN` 是 `IN` 运算符的否定操作。

###### 2.3.5、运算符 BETWEEN ######

`BETWEEN` 运算符确定一个值是否介于某两个值之间。`BETWEEN` 运算符常用于比较数字和日期类型的数据。

`BETWEEN` 运算符是一个三目运算符，它需要 3 个操作数。下面 `BETWEEN` 运算符的语法：

expression BETWEEN min AND max													# expression 可以是一个字段名、值或其他的表达式
    																												# min 是范围的最小值 max 是范围的最大值
    expression NOT BETWEEN min AND max										  # NOT 是否定运算符，NOT BETWEEN 与 BETWEEN 相反

说明：① `BETWEEN` 运算符来测试一个值是否位于一个数值区间或者时间区间内；② `BETWEEN min AND max` 中 `min` 是区间的最小值，`max` 是区间的最大值；③ `expression BETWEEN min AND max` 与 `expression >= min AND expression <= max` 结果相同；④ `BETWEEN` 运算符可以结合 `NOT` 运算符进行否定操作；⑤ `expression NOT BETWEEN min AND max` 与 `expression < min OR expression > max` 结果相同。

举例：

我们以 MySQL 官方示例数据库 Employees 数据库为例，使用 `BETWEEN` 操作作为 `WHERE` 查询搜索的条件来查询相关记录。

SELECT
    	* 
    FROM
    	employees 
    WHERE
    	birth_date BETWEEN "1960-01-01" 											# 查询出生日期在 1960 年的员工
    	AND "1960-12-31";

![image-20240305110326931][]

###### 2.3.6、运算符 LIKE ######

在 MySQL 中， `LIKE` 运算符可以根据指定的模式过滤数据。`LIKE` 运算符一般用于模糊匹配字符数据。

`LIKE` 运算符是一个双目比较运算符，需要两个操作数。 `LIKE` 运算符语法如下：

expression 																							# expression 可以是一个字段名、值或其他的表达式
    LIKE pattern																						# pattern 是一个字符串模式

说明：① `expression` 可以是一个字段名、值或其他的表达式（比如函数调用、运算等）；② `pattern` 是一个字符串模式。MySQL 字符串模式支持两个通配符：“`%`” 和 “`_`”，且当使用通配符匹配文本时，不区分字母大小写。；③ “`%`” 匹配零或多个任意字符；④ “`_`” 匹配单个任意字符；⑤ 如果需要匹配通配符，则需要使用 “`\`” 转义字符，如 “`\%`” 和 “`\_`”；⑥ 使用通配符匹配文本时，不区分字母大小写；⑦ 如果 `expression` 与 `pattern` 匹配，`LIKE` 运算符返回 1，否则返回 0；⑧ 可使用 `NOT` 运算符来否定 `LIKE` 运算符。

举例：

我们以 MySQL 官方示例数据库 Employees 数据库为例，使用 `LIKE` 操作作为 `WHERE` 查询搜索的条件来查询相关记录。

SELECT * FROM employees WHERE first_name like "a______";				# 查询以 A 开头 7  位 FirstName 的员工

![image-20240305110731554][]

SELECT * FROM employees WHERE last_name like "%ok%";					  # 查询 LasrName 中包含 ok 的员工

![image-20240305110853384][]

###### 2.3.7、运算符 NULL ######

在 MySQL 中，`NULL` 是一个特殊的值，它代表什么都没有。它不是空，也不是 `0`。

`IS NULL` 运算符用来测试一个值是不是 `NULL`，如果是 `NULL` 返回 `1`，否则返回 `0`。

`IS NULL` 是一个单目比较运算符，只需要一个操作数。`IS NULL` 运算符的语法是：

expression IS NULL																		 # expression 可以是一个字段名、一个值或者一个表达式
    expression IS NOT NULL																 # IS NOT NULL 是 IS NULL 的否定运算

说明：① `IS NULL` 和 `IS NOT NULL` 可以用在 [`SELECT`][SELECT] 语句或者 [`WHERE`][WHERE] 子句中；② 当 `IS NULL` 运算符左侧的操作数是 `NULL` 时，`IS NULL` 运算符返回 `1`，否则返回 `0`。

###### 2.3.8、操作符 EXISTS ######

在 MySQL 中，`EXISTS` 操作符用来判断一个子查询是否返回数据行。如果一个子查询返回了至少一个数据行，则 `EXISTS` 的计算结果为 `TRUE`，否则计算结果为 `FALSE`。

SELECT column_name
    FROM table_name
    WHERE EXISTS(subquery);

说明：① `EXISTS` 一般用在 `WHERE` 子句中；② `EXISTS` 是一个单目操作符，它需要一个子查询 `subquery` 作为参数；③ 如果子查询 `subquery` 返回了至少一个数据行，则 `EXISTS` 的计算结果为 `TRUE`，否则计算结果为 `FALSE`；④ `EXISTS` 运算时，一旦子查询找到一个匹配的行，`EXISTS` 运算就会返回。这对提高查询新能很有帮助；⑤ `EXISTS` 不关心子查询中的列的数量或者名称，它只在乎子查询是否返回数据行。所以在 `EXISTS` 的子查询中，无论你是使用 `SELECT 1` 还是 `SELECT *`，亦或是 `SELECT column_list`，都不影响 `EXISTS` 运算的结果；⑥ `NOT EXISTS` 则是 `EXISTS` 的否定操作；⑦ 大多数情况下，使用 `EXISTS` 的语句的性能比对应的使用 `IN` 的语句要好。

举例：

我们以 MySQL 官方示例数据库 Employees 数据库为例，使用 `EXISTS` 操作作为 `WHERE` 查询搜索的条件来查询相关记录。

SELECT e.*#
    FROM employees e
    WHERE EXISTS (																				# 查询当下在职的员工信息
        SELECT 1
        FROM dept_emp de
        WHERE e.emp_no = de.emp_no
        AND de.to_date > NOW()
    );

![image-20240305111950581][]

###### 2.4、DQL 子句：GROUP BY ######

在 MySQL 中， `GROUP BY` 子句用于将结果集根据指定的字段或者表达式进行分组。

有时候，我们需要将结果集按照某个维度进行汇总。这在统计数据的时候经常用到，例如以下的场景：

*  按班级求取平均成绩；
 *  按学生汇总某个人的总分；
 *  按年或者月份统计销售额；
 *  按国家或者地区统计用户数量。

这些正是 `GROUP BY` 子句发挥作用的地方。

`GROUP BY` 子句是 `SELECT` 语句的可选子句。 `GROUP BY` 子句语法如下：

SELECT column1[, column2,...]													# column 为分组依据的字段，至少一个字段，可以多个字段
    			, aggregate_function(ci) 												# 聚合函数。这是可选的，但是一般都用得到
    FROM table
    [WHERE clause]																				# 可选的，用来过滤结果集中的数据
    GROUP BY column1[, column2, ...];
    [HAVING clause]																				# 子句是可选的，用来过滤分组数据

说明：① `GROUP BY` 子句用于将结果集根据指定的字段或者表达式进行分组；② `GROUP BY` 子句的分组字段或表达式至少一个，可以多个；③ 子句是可选的，用来过滤分组数据，需要使用逻辑表达式作为条件，其中逻辑表达式中的字段或表达式只能使用分组使用的字段和聚合函数。 ④ `GROUP BY` 子句经常用于数据统计汇总，通常使用聚合函数；⑤ 经常使用的聚合函数主要有：`SUM()` 求总和 、`AVG()` 求平均值、`MAX()` 求最大值、`MIN()` 求最小值、`COUNT()` 计数 …

举例：

我们以 MySQL 官方示例数据库 Employees 数据库为例，使用 `GROUP BY` 子句对结果集进行分组及聚合计算。

SELECT																								# 计算不同性别员工的平均/最大/最小年龄
    	gender,
    	AVG( DATEDIFF( CURRENT_DATE (), birth_date ) / 365 ) AS avg_age,
    	MAX( DATEDIFF( CURRENT_DATE (), birth_date ) / 365 ) AS max_age,
    	MIN( DATEDIFF( CURRENT_DATE (), birth_date ) / 365 ) AS min_age 
    FROM
    	employees 
    GROUP BY
    	gender;

![image-20240305112933493][]

###### 2.5、DQL 子句：ORDER BY ######

通常情况下，`SELECT` 语句返回的结果集是按照数据库默认的规则排序的。如果我们想按照自定义自定义规则排序结果集，可以使用 `ORDER BY` 子句。

我们可以通过 `ORDER BY` 子句指定排序的字段以及升序排序还是降序排序。

在 `ORDER BY` 子句中，我们可以指定一个或多个排序的字段。 `ORDER BY` 子句的语法如下：

SELECT
       column1, column2, ...
    FROM
       table_name
    [WHERE clause]
    ORDER BY
       column1 [ASC|DESC],
       column2 [ASC|DESC],
       ...;

说明：① `ORDER BY` 子句可以指定一个或多个字段；② `ASC` 代表升序，`DESC` 代表降序，这是可选的；③ 未指定 `[ASC|DESC]` 时，默认值是 `ASC`。即，默认是按指定的字段升序排序；④ 当指定多个列时，首先按照前面的字段排序，其次按照后面的字段排序。 ⑤ 使用 `FIELD()` 函数或者 `CASE` 子句可按照自定义的序列排序；⑥ 升序排序时， `NULL` 在非 NULL 值之前；降序排序时，`NULL` 在非 NULL 值之后。

###### 2.5.1、使用 CASE 实现自定义排序 ######

有时候单纯的按照字段的值排序并不能满足要求，我们需要按照自定义的顺序的排序。即按照列表中元素的索引位置进行排序。我们分别使用 `CASE` 子句或 `FIELD()` 函数实现它。

举例：

我们以 MySQL 官方示例数据库 Employees 数据库为例，使用 `CASE` 自定义 `ORDER BY` 子句对结果集进行排序。

SELECT * FROM departments
    ORDER BY CASE dept_no
        WHEN 'Finance' THEN 1
        WHEN 'Human Resources' THEN 2
        WHEN 'Research' THEN 3
        WHEN 'Development' THEN 4
        WHEN 'Customer Service' THEN 5
    		WHEN 'Sales' THEN 6
    END;

![image-20240305113444767][]

值得注意的是，根据查询结果我们可以看到，未包含在自定义规则内的记录，会在自定义查询时排定义规则内的记录的前面。

###### 2.5.2、使用 FIELD 函数实现自定义排序 ######

对于上面实例中的 `CASE` 语句，我们可以如下的使用 [`FIELD()`][FIELD] 代替。

SELECT DISTINCT Continent FROM country 								# 根据自定义规则对大陆进行排列
    ORDER BY FIELD(rating, 'Finance', 'Human Resources', 'Research', 'Development', 'Customer Service', 'Sales');

输出结果与上面实例完全相同。

###### 2.6、DQL 子句：LIMIT ######

在 MySQL 中，我们使用 `LIMIT` 子句来限定 `SELECT` 语句返回的行的数量。

该 `LIMIT` 子句可用于限制 `SELECT` 语句返回的行数。 `LIMIT` 接受一个或两个非负数正数作为参数。 `LIMIT` 子句的语法如下：

LIMIT [offset,] row_count;
    --- 
    LIMIT row_count OFFSET offset;										  	# 二者效果相同

说明：① `offset` 指定要返回的第一行的偏移量。偏移量是相对于未使用 `LIMIT` 语句时的原始结果集而言的。`offset` 可理解为在原始结果集的基础上跳过的行数；② `row_count` 执行要返回的最大行数；③ `offset` 是可选的。当未指定 `offset` 时，默认的值 `offset` 为 `0`；④ `LIMIT` 一般位于 `SELECT` 语句的最后。

举例：

我们以 MySQL 官方示例数据库 Employees 数据库为例，使用 `LIMIT` 子句限定结果返回数量。

SELECT * FROM employees LIMIT 60,10;								# 限定返回数量 10 ，偏移量 60

![image-20240305113708032][]

###### 2.7、DQL子查询 ######

MySQL 子查询是嵌套一个语句中的查询语句，也被称为内部查询。子查询经常用在 [`WHERE`][WHERE] 子句中。

例如，我前面提到的操作符 `EXISTS` 就属于子查询的范畴。

此外，我们还可以将查询结果作为 `IN` 的列表项以实现最终的查询结果，这里需要注意在子查询中返回的结果必须是一个字段列表项。

但大多数情况下，使用 `IN` 的自查询的性能不如使用操作符 `EXISTS` 的性能的。最后，还存在一种在 `FROM` 中使用子查询的方式，即当一个子查询位于 `FORM` 子句中时，这个子查询被称为派生表。

###### 2.8、DQL集合操作符：UNION ######

`UNION` 是一个集合操作符，它用于合并 2 个结果集中的所有的行

SQL 标准中定义了 3 个集合操作符： `UNION`, `INTERSECT` 和 `MINUS`。目前 MySQL 只支持 `UNION`。

以下是集合操作符 `UNION` 的语法：

SELECT statementA
    UNION [DISTINCT | ALL]																	# UNION 默认为 UNION DISTINCT 会去除重复记录
    SELECT statementB

说明：① `UNION` 为双目操作符，需要两个 `SELECT` 语句作为操作数；② `UNION` 中的 `SELECT` 语句中的列数、列顺序必须相同；③ `UNION` 运算包括 `UNION DISTINCT` 和 `UNION ALL` 两种算法，其中 `UNION DISTINCT` 可以简写为 `UNION`； ④`UNION` 会删除两个结果集中的重复记录行，而 `UNION ALL` 则保留全部记录行；⑤ `UNION` 运算取第一个参与运算的结果集的列名作为最终的列名

[image-20240305101215085]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/4c288506967f4635b17f9738fe10a5e0.png
[image-20240305103228542]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/feb89f9e2f984185a1ba2a29e7a21b7c.png
[image-20240305103838057]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/9247771fae2443cca912371e786b2ee5.png
[image-20240305104515557]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/7e383a92f5d14c93bf432b42fda1eb0a.png
[image-20240221174746742]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/97f3a88a4f0e445f88a850bdc89f718e.png
[image-20240221174843130]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/26bd917e371c4e27abd201ed0f63334b.png
[image-20240221175853025]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/c7799335bcb242a5a2ee45fb231fcdbb.png
[image-20240221181538727]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/a267b867a6884c7190dc2176d9c901aa.png
[image-20240221182559011]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/465bba0ea5384cd7a5c1b0edfd955514.png
[image-20240221182920241]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/5152a724a6154234bf002d275cddc875.png
[image-20240305105857403]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/7297c59d180d4d5d935ed0ca172aa819.png
[image-20240305110326931]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/a2f163ad28c7400a94d4c957a9b6687f.png
[image-20240305110731554]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/2d1b143107ca427f95da06b16e46fe4e.png
[image-20240305110853384]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/6884d7b5a8b941e595f53dd2b42f22d4.png
[SELECT]: https://www.sjkjc.com/mysql/select/
[WHERE]: https://www.sjkjc.com/mysql/where/
[image-20240305111950581]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/b4d3ef2c4c124d40bc59b2c2becb6f09.png
[image-20240305112933493]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/2fb349d0ce894ca087dc215a22022f53.png
[image-20240305113444767]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/d71a9a5527a24801bf0e5de8e0dda12d.png
[FIELD]: https://www.sjkjc.com/mysql-ref/field/
[image-20240305113708032]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/84a63dafb9ca400fb5226e7203634615.png