重庆实惠网站建设,网站外包公司有哪些,绵阳哪个网站做外卖做的好,网站模板怎么套用文章目录 1.简介2.优势3.分类3.1 标量子查询3.2 行子查询3.3 列子查询IN 操作符ALL 操作符ANY/SOME 操作符 3.4 表子查询 4.关联子查询5.EXISTS 和 NOT EXISTS6.横向派生表7.附录参考文献 1.简介
子查询是另一个语句中的 SELECT 语句。
子查询也称为内查询#xff08;Inner … 文章目录 1.简介2.优势3.分类3.1 标量子查询3.2 行子查询3.3 列子查询IN 操作符ALL 操作符ANY/SOME 操作符 3.4 表子查询 4.关联子查询5.EXISTS 和 NOT EXISTS6.横向派生表7.附录参考文献 1.简介
子查询是另一个语句中的 SELECT 语句。
子查询也称为内查询Inner Query必须位于括号之中。包含子查询的查询称为外查询Outer Query。子查询支持多层嵌套也就是子查询可以包含其他子查询。
子查询的外部语句可以是以下任一语句SELECT、INSERT、UPDATE、DELETE、SET 或 DO。
下面是一个示例。
SELECT * FROM t1 WHERE column1 (SELECT column1 FROM t2);在这个例子中SELECT * FROM t1 ...是外查询位于括号中的SELECT column1 FROM t2是子查询。
2.优势
使用子查询的主要优势有
它们允许结构化查询以便可以隔离语句的每个部分。它们提供了需要复杂连接和并集的操作的替代方法。许多人发现子查询比复杂的连接或联合更具可读性。 事实上正是子查询的创新让人们产生了将早期 SQL 称为“结构化查询语言”的最初想法。
3.分类
根据子查询的结果可以将其分为多种类型。
标量子查询Scalar Subquery返回单个值一行一列的子查询。行子查询Row Subquery返回单行结果一行多列的子查询。列子查询Column Subquery返回单列结果一列多行的子查询。表子查询Table Subquery返回一个虚拟表多行多列的子查询。
3.1 标量子查询
标量子查询的结果就像一个常量一样可以用于 SELECT、WHERE、GROUP BY、HAVING 以及 ORDER BY 等子句中。
例如以下语句返回了月薪大于平均月薪的员工
SELECT name, salary
FROM employee
WHERE salary (SELECT AVG(salary)FROM employee
);-----------------
| name | salary |
-----------------
| 刘备 | 1000000 |
| 曹操 | 2000000 |
| 孙权 | 1500000 |
-----------------其中括号内部的子查询用于获得员工的平均月薪外查询用于返回月薪大于平均月薪的员工信息。
3.2 行子查询
行子查询可以当作一个一行多列的临时表使用。
以下语句查找所有与“关羽”在同一个部门并且职级相同的员工
SELECT name, dept_id, job_level
FROM employee
WHERE (dept_id, job_level) (SELECT dept_id, job_levelFROM employeeWHERE name 关羽)
AND name ! 关羽;----------------------------
| name | dept_id | job_level |
----------------------------
| 张飞 | 1 | 2 |
----------------------------3.3 列子查询
列子查询可以当作一个一列多行的临时表使用。
当 WHERE 条件中的子查询返回多行数据时不能再使用普通的比较运算符因为它们不支持单个值和多个值的比较如果想要判断某个字段是否在子查询返回的数据列表中可以将子查询与 IN、ALL、ANY/SOME 操作符配合使用。
operand IN (subquery)
operand comparison_operator ALL (subquery)
operand comparison_operator ANY (subquery)
operand comparison_operator SOME (subquery)其中 comparison_operator 是下面运算符之一 !IN 操作符
IN 操作符表示表达式是否在子查询的结果列中如果在如返回 TRUE。
SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);比如查找“刘备”和“孙权”所在部门的员工
SELECT name, dept_id
FROM employee
WHERE dept_id IN (SELECT dept_idFROM employeeWHERE name IN (刘备,孙权)
)
AND name NOT IN (刘备,孙权);--------------------
| name | dept_id |
--------------------
| 关羽 | 1 |
| 张飞 | 1 |
| 黄月英 | 1 |
| 吕蒙 | 3 |
| 黄盖 | 3 |
--------------------NOT IN 操作符执行和 IN 相反的操作也就是当表达式在列子查询结果中时为 TRUE。
ALL 操作符
除了 IN 运算符之外ALL、ANY/SOME 运算符与比较运算符的结合也可以用于判断子查询的返回结果。
ALL 必须跟在比较运算符之后如果表达式与子查询返回列中的所有值的比较结果为 TRUE则返回 TRUE。
SELECT s1 FROM t1 WHERE s1 ALL (SELECT s1 FROM t2);对于 ALL 操作符有两个需要注意的情况就是子查询结果为空或者存在 NULL 值。
SELECT name, salary
FROM employee
WHERE salary ALL (SELECT 999999 FROM anonymity WHERE 10);以上查询会返回所有员工因为子查询返回结果为空集外查询相当于没有 WHERE 条件。
SELECT name, salary
FROM employee
WHERE salary ALL (SELECT MAX(999999) FROM anonymity WHERE 10);以上查询会返回返回空集因为子查询返回 NULL任何数值和 NULL 比较的结果都是未知unknown。
NOT IN 是 ALL 的别名。 因此这两个语句是相同的
SELECT s1 FROM t1 WHERE s1 ALL (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);ANY/SOME 操作符
SOME 是 ANY 的别名所以 SOME 等同于 ANY。
ANY 关键字必须跟在比较运算符之后如果表达式与子查询返回列中的任何值的比较结果为 TRUE则返回 TRUE。
SELECT s1 FROM t1 WHERE s1 ANY (SELECT s1 FROM t2);ANY 和 IN 操作符等价。
3.4 表子查询
当子查询返回的结果包含多行多列数据时称为表子查询。表子查询通常用于 FROM 子句或者查询条件中。
当子查询出现在 FROM 子句中时相当于创建了一个语句级别的派生表Derived Table。
SELECT ... FROM (subquery) [AS] tbl_name ...JSON_TABLE() 函数生成一个表并提供另一种创建派生表的方法
SELECT * FROM JSON_TABLE(arg_list) [AS] tbl_name ...[AS] tbl_name 子句是强制性的因为 FROM 子句中的每个表都必须有一个名称。 派生表中的任何列都必须具有唯一名称。tbl_name 后面可以跟一个带括号的派生表列名称列表。
SELECT ... FROM (subquery) [AS] tbl_name (col_list) ...列名数量必须与列数量相同。
4.关联子查询
在上面的示例中子查询和外查询之间没有联系可以单独运行。这种子查询也称为非关联子查询Non-correlated Subquery。
另一类子查询会引用外查询中的字段从而与外部查询产生关联也称为关联子查询Correlated Subquery。
例如以下示例通过使用关联子查询获得各个部门的员工数量
SELECT d.name AS 部门名称,(SELECT count(*)FROM employeeWHERE dept_id d.id) as 员工数量
FROM department d;----------------------------
| 部门名称 | 员工数量 |
----------------------------
| 蜀汉部 | 4 |
| 曹魏部 | 3 |
| 孙吴部 | 3 |
----------------------------其中子查询的 WHERE 条件中使用了外查询的部门编号d.id从而与外查询产生关联。该语句执行时外查询先检索出所有的部门数据针对每条记录再将 d.id 传递给子查询子查询返回每个部门的员工数量。
5.EXISTS 和 NOT EXISTS
如果子查询返回任何行则 EXISTS 子查询为 TRUENOT EXISTS 子查询为 FALSE。
SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);传统上EXISTS 子查询以 SELECT * 开头但它也可以以 SELECT 5 或 SELECT column1 或任何其他内容开头。 MySQL 会忽略此类子查询中的 SELECT 列表因此没有区别。
对于前面的示例如果 t2 包含任何行甚至只包含 NULL 值的行则 EXISTS 条件为 TRUE。 这实际上是一个不太可能的例子因为 [NOT] EXISTS 子查询几乎总是包含相关性。
下面看一个更加具体的例子。比如返回了存在女性员工的部门
SELECT d.name
FROM department d
WHERE EXISTS (SELECT *FROM employee eWHERE e.gender 女AND e.dept_id d.id);-----------
| name |
-----------
| 蜀汉部 |
-----------其中EXISTS 之后是一个关联子查询先执行外查询找到 d.dept_id然后依次将 d.dept_id 传递给子查询判断该部门是否存在女性员工如果存在则返回部门信息。
NOT EXISTS 执行相反的操作。如果想要查找不存在女性员工的部门可以将上例中的 EXISTS 替换成 NOT EXISTS。
6.横向派生表
对于派生表而言它必须能够单独运行而不能依赖其他表。
例如以下语句想要返回每个部门内月薪最高的员工
SELECT d.name, t.name, t.salary
FROM department d
LEFT JOIN (SELECT e.dept_id, e.name, e.salaryFROM employee eWHERE e.dept_id d.idORDER BY e.salary DESCLIMIT 1) t ON d.id t.dept_id;
ERROR 1054 (42S22): Unknown column d.id in where clause该语句失败的原因在于子查询 t 不能引用外查询中的 department 表。
从 MySQL 8.0.14 开始派生表支持 LATERAL 关键字前缀表示允许派生表引用它所在的 FROM 子句中的其他表。这种派生表被称为横向派生表Lateral Derived Table。
对于上面的问题可以使用 LATERAL 派生表实现
SELECT d.name, t.name, t.salary
FROM department d
LEFT JOIN LATERAL (SELECT e.dept_id, e.name, e.salaryFROM employee eWHERE e.dept_id d.idORDER BY e.salary DESCLIMIT 1) t on d.id t.dept_id;该语句在 LEFT JOIN 之后加上了一个 LATERAL 关键字使得子查询 t 能够引用前面的 department 表中的字段。
如果你使用的是 MySQL 5.7 以及之前的版本可以利用 MySQL 中的自定义变量实现相同的效果
SELECT d.name dept_name, w.name emp_name, w.salary
FROM department d
LEFT JOIN (SELECT *FROM (SELECT a.*, IF(did a.dept_id, rn : rn1, rn : 1) AS rn, did : a.dept_idFROM(SELECT * FROM employee e ORDER BY dept_id, salary DESC) a) AS tWHERE t.rn 1
) AS w ON d.id w.dept_id;------------------------------
| dept_name | emp_name | salary |
------------------------------
| 蜀汉部 | 刘备 | 1000000 |
| 曹魏部 | 曹操 | 2000000 |
| 孙吴部 | 孙权 | 1500000 |
------------------------------上面的查询语句使用了自定义变量有几处需要特别解释一下。
语句IF(did a.dept_id, rn : rn1, rn : 1) AS rn这是一个 IF 语句用于计算排名。它检查当前行的部门 ID (a.dept_id) 是否与前一行的部门 ID (did) 相同。如果相同则排名 (rn) 自增 1表示同一个部门内的下一个员工。如果部门 ID 不同即进入了新的部门则排名 (rn) 被重置为 1表示这是新部门的第一个员工。AS rn 表示将计算出的排名别名为 rn它将作为结果集的一部分返回。
did : a.dept_id将当前行的部门 ID (a.dept_id) 赋值给用户变量 did。该变量用在前面的 IF 语句中用于给部门内的员工计算排名。
再给每个部门员工按照工资排序并编上部门内部排名 rn 后将结果作为派生表 t通过 SELECT 查询出所有部门内薪资排名第一的员工。
最后和部门表连表查询出每个部门内月薪最高的员工。
7.附录
本文示例用到的员工表employee和部门表deparment建表与数据如下。
员工表employee
CREATE TABLE employee(id INT UNSIGNED NOT NULL AUTO_INCREMENT,name varchar(64) NOT NULL,dept_id INT UNSIGNED,job_level INT UNSIGNED,salary INT UNSIGNED,gender CHAR(1) DEFAULT 男,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4INSERT INTO employee(name,dept_id,job_level,salary,gender)
VALUES
(刘备, 1,1, 1000000, 男),
(关羽, 1,2, 100000, 男),
(张飞, 1,2, 100000, 男),
(黄月英,1,3, 80000, 女),
(曹操, 2,1, 2000000, 男),
(典韦, 2,2, 200000, 男),
(张辽, 2,2, 200000, 男),
(孙权, 3,1, 1500000, 男),
(吕蒙, 3,2, 150000, 男),
(黄盖, 3,2, 150000, 男)部门表deparment
CREATE TABLE department(id INT UNSIGNED NOT NULL AUTO_INCREMENT ,name varchar(64) NOT NULL,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4INSERT INTO department(name)
VALUES
(蜀汉部),
(曹魏部),
(孙吴部)参考文献
MySQL 8.0 Reference Manual :: 13.2.15 Subqueries 《MySQL 入门教程》第 19 篇 子查询 - 不剪发的Tony老师
