子查询

  • 子查询指一个查询语句嵌套在另一个查询语句内部的查询
  • 子查询中常用的操作符有 ANY（SOME）、ALL、IN 和 EXISTS。
  • 子查询可以添加到 SELECT、UPDATE 和 DELETE语句中，而且可以进行多层嵌套。子查询也可以使用比较运算符，如“<”、“<=”、“>”、“>=”、“！=”等。 子查询中常用的运算符

子查询中常用的运算符

  1. IN子查询
    结合关键字 IN 所使用的子查询主要用于判断一个给定值是否存在于子查询的结果集中。其语法格式为：<表达式> [NOT] IN <子查询>
  • <表达式>：用于指定表达式。当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字NOT，则返回的值正好相反。
  • <子查询>：用于指定子查询。这里的子查询只能返回一列数据。对于比较复杂的查询要求，可以使用 SELECT 语句实现子查询的多层嵌套。
  1. 比较运算符子查询
    比较运算符所使用的子查询主要用于对表达式的值和子查询返回的值进行比较运算。其语法格式为：
    <表达式> {= | < | > | >= | <= | <=> | < > | != } { ALL | SOME | ANY} <子查询>
  • <子查询>：用于指定子查询。
  • <表达式>：用于指定要进行比较的表达式。
  • ALL、SOME 和 ANY：可选项。用于指定对比较运算的限制。其中，关键字 ALL用于指定表达式需要与子查询结果集中的每个值都进行比较，当表达式与每个值都满足比较关系时，会返回 TRUE，否则返回 FALSE；关键字SOME 和 ANY 是同义词，表示表达式只要与子查询结果集中的某个值满足比较关系，就返回 TRUE，否则返回 FALSE。
  1. EXIST子查询
    关键字 EXIST 所使用的子查询主要用于判断子查询的结果集是否为空。其语法格式为：
EXIST <子查询>

若子查询的结果集不为空，则返回 TRUE；否则返回 FALSE。

子查询的应用

mysql> SELECT name FROM tb_students_info
    -> WHERE dept_id IN
    -> (
    -> SELECT dept_id
    -> FROM tb_departments
    -> WHERE dept_type = 'A'
    -> );
+-------+
| name  |
+-------+
| Dany  |
| Henry |
| Jane  |
| Jim   |
| John  |
+-------+
5 rows in set (0.01 sec)
mysql> SELECT name FROM tb_students_info
    -> WHERE dept_id NOT IN
    -> (SELECT dept_id FROM tb_departments WHERE dept_type = 'A');
+--------+
| name   |
+--------+
| Green  |
| Lily   |
| Susan  |
| Thomas |
| Tom    |
+--------+
5 rows in set (0.01 sec)

GROUP BY：分组查询

GROUP BY 子句，将结果集中的数据行根据选择列的值进行逻辑分组，以便能汇总表内容的子集，实现对每个组而不是对整个结果集进行整合。

GROUP BY { <列名> | <表达式> | <位置> } [ASC | DESC]
  • <列名>：指定用于分组的列。可以指定多个列，彼此间用逗号分隔。
  • <表达式>：指定用于分组的表达式。通常与聚合函数一块使用，例如可将表达式 COUNT(*)AS’ 人数 ’ 作为 SELECT
    选择列表清单的一项。
  • <位置>：指定用于分组的选择列在 SELECT 语句结果集中的位置，通常是一个正整数。例如，GROUP BY 2 表示根据 SELECT语句列清单上的第 2 列的值进行逻辑分组。
  • ASC|DESC：关键字 ASC 表示按升序分组，关键字 DESC 表示按降序分组，其中 ASC 为默认值，注意这两个关键字必须位于对应的列名、表达式、列的位置之后。

对于 GROUP BY 子句的使用，需要注意以下几点。

  • GROUP BY 子句可以包含任意数目的列，使其可以对分组进行嵌套，为数据分组提供更加细致的控制。
  • GROUP BY 子句列出的每个列都必须是检索列或有效的表达式，但不能是聚合函数。若在 SELECT 语句中使用表达式，则必须在
    GROUP BY 子句中指定相同的表达式。
  • 除聚合函数之外，SELECT 语句中的每个列都必须在 GROUP BY 子句中给出。
  • 若用于分组的列中包含有 NULL 值，则 NULL 将作为一个单独的分组返回；若该列中存在多个 NULL 值，则将这些 NULL值所在的行分为一组。
mysql> SELECT dept_id , GROUP_CONCAT(name) AS names
    -> FROM tb_students_info
    -> GROUP BY dept_id;
+---------+---------------+
| dept_id | names         |
+---------+---------------+
| 1       | Dany,Jane,Jim |
| 2       | Henry,John    |
| 3       | Green,Thomas  |
| 4       | Susan,Tom     |
| 6       | Lily          |
+---------+---------------+
5 rows in set (0.00 sec)

HAVING：指定过滤条件

HAVING <条件>
  • <条件>指的是指定的过滤条件

HAVING 子句和 WHERE 子句非常相似，HAVING 子句支持 WHERE 子句中所有的操作符和语法，但是两者存在几点差异：

  • WHERE 子句主要用于过滤数据行，而 HAVING 子句主要用于过滤分组，即 HAVING子句基于分组的聚合值而不是特定行的值来过滤数据，主要用来过滤分组。
  • WHERE 子句不可以包含聚合函数，HAVING 子句中的条件可以包含聚合函数。
  • HAVING 子句是在数据分组后进行过滤，WHERE 子句会在数据分组前进行过滤。WHERE 子句排除的行不包含在分组中，可能会影响HAVING 子句基于这些值过滤掉的分组。
mysql>  SELECT dept_id , GROUP_CONCAT(name) AS NAMES
    ->  FROM tb_students_info
    -> GROUP BY dept_id
    -> HAVING  COUNT(name) >1;
+---------+---------------+
| dept_id | NAMES         |
+---------+---------------+
| 1       | Dany,Jane,Jim |
| 2       | Henry,John    |
| 3       | Green,Thomas  |
| 4       | Susan,Tom     |
+---------+---------------+
4 rows in set (0.01 sec)