目录
2、select子查询(创建新字段域) --- 显示customers 表中每个客户的订单总数
4、复杂子查询实例 --- 查询信用卡PA类型交易记录的交易额
(6)使用 NOT (a = ANY (...)) 而不是 a <> ALL (...) 表达全否定
(7)使用 x = ANY (table containing (1,2)) 而不是 x=1 OR x=2 表达或者
(10)使用诸如BENCHMARK()函数之类的MySQL工具
Mysql复杂查询可分为三大类:子查询(也叫嵌套查询)、连接查询(也叫链接表查询)和 组合查询(也叫联合查询)。
一、子查询(或嵌套查询)
子查询指一个查询语句嵌套在另一个查询语句内部的查询,这个特性从 MySQL 4.1 开始引入。子查询必须写在括号中,可以添加到 SELECT、UPDATE 和 DELETE 语句中,而且可以进行多层嵌套。
子查询的主要优点是:
它们允许结构化的查询,以便可以隔离语句的每个部分。
它们提供了执行操作的替代方法,否则将需要复杂的联接和联合。
许多人发现,子查询比复杂的联接或联合更具可读性。 确实,正是 子查询 的创新使人们有了将早期SQL称为“结构化查询语言”的初衷。
子查询可以返回的结果,如标量(单个值),单行,单列,或表(一个或多个列中的一个或多个行)。
子查询可以包含普通SELECT可以包含的许多关键字或子句,如:DISTINCT,GROUP BY,ORDER BY,LIMIT,联接,索引提示,UNION构造,注释,函数等。
对于HAVING或ORDER BY子句中的子查询,MySQL还在外部选择列表中查找列名。
相关子查询中的聚合函数,可能包含外部引用,但前提是该函数只包含外部引用,并且该函数未包含在另一个函数或表达式中。
推荐参考:https://dev.mysql.com/doc/refman/8.0/en/subqueries.html
1、where子查询(进行条件筛选)
(1) IN子查询
结合关键字 IN 所使用的子查询,主要用于判断一个给定值是否存在于子查询的结果集中。其语法格式为:
<表达式> [NOT] IN <子查询>
语法说明如下。
<表达式>
:用于指定表达式。当表达式与子查询返回的结果集中的某个值相等时,返回 TRUE,否则返回 FALSE;若使用关键字 NOT,则返回的值正好相反。<子查询>
:用于指定子查询。这里的子查询只能返回一列数据。对于比较复杂的查询要求,可以使用 SELECT 语句实现子查询的多层嵌套。
注意: NOT IN和<> ALL是等价的。
行构造器,ROW(1, 2) 和(1, 2)是等价的。下面的(column1,column2,column3),就是一个行构造器构造的行,等价于ROW(column1,column2,column3)。
SELECT column1,column2,column3
FROM t1 WHERE (column1,column2,column3) IN ( SELECT column1,column2,column3 FROM t2 );
(2)比较运算符子查询
比较运算符所使用的子查询,主要用于对表达式的值和子查询返回的值进行比较运算。其语法格式为:
<表达式> {= | < | > | >= | <= | <=> | < > | != }
{ ALL | SOME | ANY} <子查询>
语法说明如下。
<子查询>
:用于指定子查询。<表达式>
:用于指定要进行比较的表达式。ALL
、SOME
和ANY
:可选项。用于指定对比较运算的限制。其中,关键字 ALL 用于指定表达式需要与子查询结果集中的每个值都进行比较,当表达式与每个值都满足比较关系时,会返回 TRUE,否则返回 FALSE;关键字 SOME 和 ANY 是同义词,表示表达式只要与子查询结果集中的某个值满足比较关系,就返回 TRUE,否则返回 FALSE。
注意: NOT IN和<> ALL是等价的。
(3)EXIST子查询
关键字 EXIST 所使用的子查询,主要用于判断子查询的结果集是否为空。其语法格式为:
EXIST <子查询>
若子查询的结果集不为空,则返回 TRUE;否则返回 FALSE。
-
What kind of store is present in one or more cities? EXIST = One or More
SELECT DISTINCT store_type FROM stores WHERE EXISTS (SELECT * FROM cities_stores WHERE cities_stores.store_type = stores.store_type);
-
What kind of store is present in no cities? NOT EXIST = No
SELECT DISTINCT store_type FROM stores WHERE NOT EXISTS (SELECT * FROM cities_stores WHERE cities_stores.store_type = stores.store_type);
-
What kind of store is present in all cities? NOT EXIST nested in NOT EXIST = ALL
SELECT DISTINCT store_type FROM stores s1 WHERE NOT EXISTS ( SELECT * FROM cities WHERE NOT EXISTS ( SELECT * FROM cities_stores WHERE cities_stores.city = cities.city AND cities_stores.store_type = stores.store_type));
The last example is a double-nested NOT EXISTS
query. That is, it has a NOT EXISTS
clause within a NOT EXISTS
clause. Formally, it answers the question “does a city exist with a store that is not in Stores
”? But it is easier to say that a nested NOT EXISTS
answers the question “is x
TRUE
for all y
?”
(4)where子查询的示例
【实例 1】在 tb_departments 表中查询 dept_type 为 A 的学院 ID,并根据学院 ID 查询该学院学生的名字,输入的 SQL 语句和执行结果如下所示。
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)
上述查询过程可以分步执行,首先内层子查询查出 tb_departments 表中符合条件的学院 ID,单独执行内查询,查询结果如下所示。
mysql> SELECT dept_id
-> FROM tb_departments
-> WHERE dept_type='A';
+---------+
| dept_id |
+---------+
| 1 |
| 2 |
+---------+
2 rows in set (0.00 sec)
可以看到,符合条件的 dept_id 列的值有两个:1 和 2。然后执行外层查询,在 tb_students_info 表中查询 dept_id 等于 1 或 2 的学生的名字。嵌套子查询语句还可以写为如下形式,可以实现相同的效果。
mysql> SELECT name FROM tb_students_info
-> WHERE dept_id IN(1,2);
+-------+
| name |
+-------+
| Dany |
| Henry |
| Jane |
| Jim |
| John |
+-------+
5 rows in set (0.03 sec)
上例说明在处理 SELECT 语句时,MySQL 实际上执行了两个操作过程,即先执行内层子查询,再执行外层查询,内层子查询的结果作为外部查询的比较条件。
【实例 2】与前一个例子类似,但是在 SELECT 语句中使用 NOT IN 关键字,输入的 SQL 语句和执行结果如下所示。
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.04 sec)
提示:子查询的功能也可以通过连接查询完成,但是子查询使得 MySQL 代码更容易阅读和编写。
【实例 3】在 tb_departments 表中查询 dept_name 等于“Computer”的学院 id,然后在 tb_students_info 表中查询所有该学院的学生的姓名,输入的 SQL 语句和执行过程如下所示。
mysql> SELECT name FROM tb_students_info
-> WHERE dept_id =
-> (SELECT dept_id
-> FROM tb_departments
-> WHERE dept_name='Computer');
+------+
| name |
+------+
| Dany |
| Jane |
| Jim |
+------+
3 rows in set (0.00 sec)
【实例 4】在 tb_departments 表中查询 dept_name 不等于“Computer”的学院 id,然后在 tb_students_info 表中查询所有该学院的学生的姓名,输入的 SQL 语句和执行过程如下所示。
mysql> SELECT name FROM tb_students_info
-> WHERE dept_id <>
-> (SELECT dept_id
-> FROM tb_departments
-> WHERE dept_name='Computer');
+--------+
| name |
+--------+
| Green |
| Henry |
| John |
| Lily |
| Susan |
| Thomas |
| Tom |
+--------+
7 rows in set (0.00 sec)
【实例 5】查询 tb_departments 表中是否存在 dept_id=1 的供应商,如果存在,就查询 tb_students_info 表中的记录,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT * FROM tb_students_info
-> WHERE EXISTS
-> (SELECT dept_name
-> FROM tb_departments
-> WHERE dept_id=1);
+----+--------+---------+------+------+--------+------------+
| id | name | dept_id | age | sex | height | login_date |
+----+--------+---------+------+------+--------+------------+
| 1 | Dany | 1 | 25 | F | 160 | 2015-09-10 |
| 2 | Green | 3 | 23 | F | 158 | 2016-10-22 |
| 3 | Henry | 2 | 23 | M | 185 | 2015-05-31 |
| 4 | Jane | 1 | 22 | F | 162 | 2016-12-20 |
| 5 | Jim | 1 | 24 | M | 175 | 2016-01-15 |
| 6 | John | 2 | 21 | M | 172 | 2015-11-11 |
| 7 | Lily | 6 | 22 | F | 165 | 2016-02-26 |
| 8 | Susan | 4 | 23 | F | 170 | 2015-10-01 |
| 9 | Thomas | 3 | 22 | M | 178 | 2016-06-07 |
| 10 | Tom | 4 | 23 | M | 165 | 2016-08-05 |
+----+--------+---------+------+------+--------+------------+
10 rows in set (0.00 sec)
由结果可以看到,内层查询结果表明 tb_departments 表中存在 dept_id=1 的记录,因此 EXSTS 表达式返回 TRUE,外层查询语句接收 TRUE 之后对表 tb_students_info 进行查询,返回所有的记录。
EXISTS 关键字可以和条件表达式一起使用。
【实例 6】查询 tb_departments 表中是否存在 dept_id=7 的供应商,如果存在,就查询 tb_students_info 表中的记录,输入的 SQL 语句和执行结果如下所示。
mysql> SELECT * FROM tb_students_info
-> WHERE EXISTS
-> (SELECT dept_name
-> FROM tb_departments
-> WHERE dept_id=7);
Empty set (0.00 sec)
2、select子查询(创建新字段域) --- 显示customers 表中每个客户的订单总数
https://www.jb51.net/article/158459.htm
3、from子查询(对结果再查询)
即子查询在 SELECT 语句的 FROM 部分,实际的语法是:
SELECT ... FROM (subquery) [AS] tbl_name (col_list)
派生表(Derived Tables)是在查询FROM子句范围内生成表的表达式。
[AS] tbl_name子句是强制性的,因为FROM子句中的每个表都必须具有名称。 派生表中的任何列都必须具有唯一的名称。 或者,在tbl_name后面可以有一个带括号的名称列表,用于派生表列。列名的数量必须与字表列的数量相同。
Here is another example: Suppose that you want to know the average of a set of sums for a grouped table. This does not work:
SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;
However, this query provides the desired information:
SELECT AVG(sum_column1)
FROM (SELECT SUM(column1) AS sum_column1
FROM t1 GROUP BY column1) AS t1;
The column names for a derived table come from its select list:
SELECT * FROM (SELECT 1 AS col1, 2 AS col2, 3 AS col3, 4 AS col4) AS dt
SELECT * FROM (SELECT 1, 2, 3, 4) AS dt(col1, col2, col3, col4) #高版本mysql支持语法
A derived table can return a scalar, column, row, or table.
Derived tables are subject to these restrictions:
-
A derived table cannot be a correlated subquery.
-
A derived table cannot contain references to other tables of the same
SELECT
.
Prior to MySQL 8.0.14, a derived table cannot contain outer references. This is a MySQL restriction that is lifted in MySQL 8.0.14, not a restriction of the SQL standard. For example, the derived table dt
in the following query contains a reference t1.b
to the table t1
in the outer query:
SELECT * FROM t1
WHERE t1.d > (SELECT AVG(dt.a)
FROM (SELECT SUM(t2.a) AS a
FROM t2
WHERE t2.b = t1.b GROUP BY t2.c) dt
WHERE dt.a > 10);
The query is valid in MySQL 8.0.14 and higher. Before 8.0.14, it produces an error: Unknown column 't1.b' in 'where clause'
派生表不能通过 optimize 命令来优化,See Section 8.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”.
使用横向衍生表,缓存左侧字表
SELECT
salesperson.name,
max_sale.amount,
max_sale_customer.customer_name
FROM
salesperson,
-- calculate maximum size, cache it in transient derived table max_sale
LATERAL
(SELECT MAX(amount) AS amount
FROM all_sales
WHERE all_sales.salesperson_id = salesperson.id) #salesperson缓存了左侧表的数据
AS max_sale,
-- find customer, reusing cached maximum size
LATERAL
(SELECT customer_name
FROM all_sales
WHERE all_sales.salesperson_id = salesperson.id #salesperson缓存了左侧表的数据
AND all_sales.amount =
-- the cached maximum size
max_sale.amount) #max_sale缓存了之前的子表数据
AS max_sale_customer;
SELECT
salesperson.name,
max_sale.amount,
max_sale.customer_name
FROM
salesperson,
-- find maximum size and customer at same time
LATERAL
(SELECT amount, customer_name
FROM all_sales
WHERE all_sales.salesperson_id = salesperson.id #salesperson缓存了左侧表
ORDER BY amount DESC LIMIT 1)
AS max_sale;
4、复杂子查询实例 --- 查询信用卡PA类型交易记录的交易额
外国信用卡可以退款,退款会在2天内自动完成,产生PC类型的交易记录。
SELECT a.*,
CASE WHEN a.TransType='PA'
THEN IFNULL((
SELECT b.TransAmount
FROM (
SELECT * FROM u04 bb
WHERE bb.TransType = 'PC'
) b
WHERE b.TransTime > a.TransTime
AND b.TransTime <= DATE_ADD(a.TransTime,interval 2 day)
AND b.AccountNumber = a.AccountNumber
AND b.ApproveCode = a.ApproveCode
ORDER BY b.TransTime ASC
LIMIT 1
), IF(
(
SELECT @TEMP_TransTime:=c.TransTime
FROM (
SELECT * FROM u04 cc
WHERE cc.TransType = 'PA'
) c
WHERE c.TransTime > a.TransTime
AND c.TransTime <= DATE_ADD(a.TransTime,interval 2 day)
AND c.AccountNumber = a.AccountNumber
ORDER BY c.TransTime ASC
LIMIT 1
) IS NULL,
(
SELECT SUM(d.TransAmount)
FROM (
SELECT * FROM u04 dd
WHERE dd.TransType='PA' OR dd.TransType='EP'
) d
WHERE d.AccountNumber = a.AccountNumber
AND d.TransTime >= a.TransTime
AND d.TransTime < DATE_ADD(a.TransTime,interval 1 day)
ORDER BY d.TransTime ASC
LIMIT 1
),
(
SELECT SUM(e.TransAmount)
FROM (
SELECT * FROM u04 ee
WHERE ee.TransType='PA' OR ee.TransType='EP'
) e
WHERE e.AccountNumber = a.AccountNumber
AND e.TransTime >= a.TransTime
AND e.TransTime < @TEMP_TransTime
ORDER BY e.TransTime ASC
LIMIT 1)
))
ELSE ''
END
as CompletionAmount
FROM u04 a
WHERE
a.TransTime >= '2019-12-01 00:00:00' AND a.TransTime <= '2019-12-15 00:00:00'
AND a.TransType!='EP' AND a.TransType!='PC' AND a.TransType!='OP'
ORDER BY a.TransTime ASC
#LIMIT 30;
总结:在没有使用from子查询的时候,查询时间为40多秒,使用了from子查询bb, cc, dd, ee后,查询时间为仅为1秒;
二、连接查询(或链接表查询)
https://www.jb51.net/article/158459.htm
https://mp.csdn.net/console/editor/html/101680913 小表驱动大表
三、组合查询(或联合查询)
https://www.jb51.net/article/158459.htm
https://blog.csdn.net/fly910905/article/details/79846949
四、相关优化
开发正在进行中,因此没有长期可靠的优化技巧。 以下列表提供了一些您可能想玩的有趣技巧。
https://dev.mysql.com/doc/refman/8.0/en/optimizing-subqueries.html
https://dev.mysql.com/doc/refman/8.0/en/subquery-optimization.html
(1)使用影响子查询中 行数或顺序 的子查询语句
例如:
SELECT * FROM t1 WHERE t1.column1 IN
(SELECT column1 FROM t2 ORDER BY column1);
SELECT * FROM t1 WHERE t1.column1 IN
(SELECT DISTINCT column1 FROM t2);
SELECT * FROM t1 WHERE EXISTS
(SELECT * FROM t2 LIMIT 1);
(2)用子查询替换联接
例如,尝试以下操作:
SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN (
SELECT column1 FROM t2);
用来替换
SELECT DISTINCT t1.column1 FROM t1, t2
WHERE t1.column1 = t2.column1;
(3)在某些情况下,将子查询转换为联接可能会提高性能
See Section 13.2.11.12, “Rewriting Subqueries as Joins”.
(4)将子句从子查询的外部移到内部
例如,使用以下查询:
SELECT * FROM t1
WHERE s1 IN (SELECT s1 FROM t1 UNION ALL SELECT s1 FROM t2);
用来替换
SELECT * FROM t1
WHERE s1 IN (SELECT s1 FROM t1) OR s1 IN (SELECT s1 FROM t2);
SELECT (SELECT column1 + 5 FROM t1) FROM t2;
用来替换
SELECT (SELECT column1 FROM t1) + 5 FROM t2;
(5)使用行子查询而不是相关子查询
例如,使用以下查询:
SELECT * FROM t1
WHERE (column1,column2) IN (SELECT column1,column2 FROM t2);
用来替换
SELECT * FROM t1
WHERE EXISTS (SELECT * FROM t2 WHERE t2.column1=t1.column1
AND t2.column2=t1.column2);
(6)使用 NOT (a = ANY (...))
而不是 a <> ALL (...)
表达全否定
即,不等于任何一个
(7)使用 x = ANY (table containing (1,2)
)
而不是 x=1 OR x=2 表达或者
table containing (1,2)
)
(8)使用 = ANY
而不是 EXISTS
(9)对于总是返回一行的不相关子查询,IN总是比=慢
例如,使用以下查询:
SELECT * FROM t1
WHERE t1.col_name = (SELECT a FROM t2 WHERE b = some_const);
用来代替
SELECT * FROM t1
WHERE t1.col_name IN (SELECT a FROM t2 WHERE b = some_const);
(10)使用诸如BENCHMARK()函数之类的MySQL工具
See Section 12.15, “Information Functions”.
(11)一些 MySQL 自动优化的情况
-
MySQL 对不相关子查询仅执行一次. 使用
EXPLAIN
工具确保子查询是不相关子查询. -
MySQL 重写
IN
,ALL
,ANY
, andSOME
子查询,以尝试利用对子查询中的select-list列建立索引的可能性。 -
MySQL用索引查找功能替换了以下形式的子查询,EXPLAIN将其描述为特殊的连接类型(unique_subquery或index_subquery):
... IN (SELECT indexed_column FROM single_table ...)
-
MySQL使用涉及MIN()或MAX()的表达式增强以下形式的表达式,除非涉及NULL值或空集:
value {ALL|ANY|SOME} {> | < | >= | <=} (uncorrelated subquery)
For example, this
WHERE
clause:WHERE 5 > ALL (SELECT x FROM t)
might be treated by the optimizer like this:
WHERE 5 > (SELECT MAX(x) FROM t)
See also MySQL Internals: How MySQL Transforms Subqueries.