MySQL第六章: 编写复杂查询
**内容来自: **
1、b站mosh老师的SQL课程(第五章) 【中字】SQL进阶教程 | 史上最易懂SQL教程!10小时零基础成长SQL大师!!_哔哩哔哩_bilibili www.bilibili.com/video/BV1UE41147KC/?p=17&spm_id_from=333.1007.top_right_bar_window_history.content.click&vd_source=171e84ea90c06aa5a434d7fa2502e75c
2、https://zhuanlan.zhihu.com/p/222865842(非常感谢这位知乎大佬,笔记、课件写的很详细)
《SQL必知必会(第五版)》这部分内容这个月后期会统一更新
3、菊花酱数据分析课程
子查询
子查询: 任何一个充当另一个SQL语句的一部分( SELECT……
查询语句)都是子查询,子查询是一个很有用的技巧
在工作中,经常会遇见4种子查询,即:
- 含有比较运算符(>、>=、<、<=、=、!=)
- IN关键词
- ANY/ALL关键词
- EXISTS关键词的嵌套查询
子查询的层级用括号实现。
注意:
如果不确定执行顺序时最好加上括号确保万无一失
比较运算符
案例
题目:
在 products表中,筛选出所有比生菜(id = 3)价格高的记录
提示:
用子查询找到生菜价格
USE sql_store;
SELECT *
FROM products
WHERE unit_price > (
SELECT unit_price
FROM products
WHERE product_id = 3
)
代码解析:
MySQL执行时会先执行括号内的子查询(内查询),将获得的生菜价格作为结果返回给外查询。
注意:
子查询不仅可用在 WHERE …… 中,也可用在 SELECT …… 或 FROM …… 等子句中。
练习1
题目:
在 sql_hr 库 employees表里,选择所有工资超过平均工资的记录。
提示:
由子查询得到平均工资
USE sql_hr;
SELECT *
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
)
为什么不能用
WHERE salary > AVG(salary)
?
WHERE
子句不能直接使用聚合函数来筛选每一行。 因为WHERE
子句用于过滤行,而这个过滤操作发生在行被分组(group by)和聚合之前。由于聚合函数作用于分组后的结果,所以它们不能用于决定哪些行会进入到分组中。补充:
在 SQL 中,聚合函数可以在两种情况下使用:
- 不带
GROUP BY
子句时:聚合函数将作用于整个查询结果集(from table)。这意味着,当您不使用GROUP BY
子句时,聚合函数(如MAX()
,MIN()
,AVG()
,SUM()
,COUNT()
等,但select后面的内容不能有非聚合字段)会处理整个表或满足WHERE
子句条件的所有行。在这种情况下,每个聚合函数都返回一个单一的值,代表整个数据集的相应聚合。- 带有
GROUP BY
子句时:聚合函数将作用于每个分组。GROUP BY
子句会根据指定的列将数据分成多个组,然后聚合函数针对每个组分别计算结果。
练习2
前置准备(来源于菊花酱数据分析课程)
# 创建学员信息表 CREATE TABLE stu_info( id INT AUTO_INCREMENT PRIMARY KEY, iname VARCHAR(20), gender CHAR(1), department VARCHAR(10), age TINYINT, province VARCHAR(10), email VARCHAR(50), mobilephone CHAR(11) ); # 向学员表中插入数据 INSERT INTO stu_info(iname,gender,department,age,province,email,mobilephone) VALUES ('张勇','男','数学系',23,'河南','sfddf123dd@163.com','13323564321'), ('王兵','男','数学系',25,'江苏','lss1993@163.com','17823774329'), ('刘伟','男','计算机系',21,'江苏','qawsed112@126.com','13834892240'), ('张峰','男','管理系',22,'上海','102945328@qq.com','13923654481'), ('董敏','女','生物系',22,'浙江','82378339@qq.com','13428439022'), ('徐晓红','女','计算机系',24,'浙江','xixiaohong@gmail.com','13720097528'), ('赵伊美','女','数学系',21,'江苏','zhaomeimei@163.com','13417723980'), ('王建国','男','管理系',24,'浙江','9213228402@qq.com','13768329901'), ('刘清','女','统计系',23,'安徽','lq1128@gmail.com','17823651180'), ('赵家和','男','计算机系',28,'山东','dcrzdbjh@163.com','13827811311'); # 查看学员表 SELECT * FROM stu_info; # 创建学员成绩表 CREATE TABLE stu_score( id INT , Excel TINYINT, Tableau TINYINT, MySQL TINYINT ); # 向成绩表中插入数据 INSERT INTO stu_score VALUES (1,87,72,88), (3,90,66,72), (2,90,70,86), (4,88,82,76), (8,92,67,80), (10,88,82,89), (5,79,66,60), (7,91,78,90), (6,82,79,88), (9,85,70,85); # 查看成绩表 SELECT * FROM stu_score;
题目:查询年龄不低于所属系平均年龄的学员信息
SELECT * FROM stu_info AS s1
WHERE age>= ( SELECT avg(age) FROM stu_info AS s2
WHERE s1.department = s2.department)
注意:
比较运算符后面的子查询只能返回一个结果
IN运算符
当查询条件涉及某些已知的可枚举离散值的时候,我们就可以选择IN关键词来完成数据的提取。IN关键词有两种用法:
- 将可枚举的离散值直接写在值列表中
- 当离散值是基于其他表的筛选结果时,就可以使用嵌套查询,即把另一个表的查询语句块写在IN关键词后面的括号里
案例
题目:
在 sql_store 库 products 表中找出那些从未被订购过的产品记录
思路:
- order_items 表里有所有产品被订购的记录,用 DISTINCT 去重,得到所有被订购过的产品列表
- 不在这列表里(NOT IN 的使用)的产品即为从未被订购过的产品
**USE sql_store;
SELECT *
FROM products
WHERE product_id NOT IN (
SELECT DISTINCT product_id
FROM order_items
)
练习1
题目:
在 sql_invoicing 库 clients 表中找到那些没有过发票记录的客户记录
思路:
在invoices里用DISTINCT找到所有有过发票记录的客户列表,再用NOT IN来筛选
USE sql_invoicing;
SELECT *
FROM clients
WHERE client_id NOT IN (
SELECT DISTINCT client_id
FROM invoices
)
练习2
查询数学系和计算机系的学员信息 (数据库来源于菊花酱数据分析,见顶上比较运算符的练习2)
SELECT * FROM stu_info
WHERE department IN('数学系','计算机系');
练习3
查询与张勇、刘伟同一个系的学员信息 (数据库来源于菊花酱数据分析,见顶上比较运算符的练习2)
SELECT * FROM stu_info
WHERE department IN (SELECT department FROM stu_info WHERE iname IN('张勇','刘伟'));
练习4
查询MySQL成绩大于85分的学员信息 (数据库来源于菊花酱数据分析,见顶上比较运算符的练习2)
SELECT * FROM stu_info
WHERE id IN (SELECT id FROM stu_score WHERE MySQL > 85);
注意:
在使用IN关键词的嵌套查询的时候,嵌套部分只能返回一个字段的信息(比如上面的department字段或者id字段),如果返回两个及以上字段信息则会出现语法错误
子查询vs连接
子查询(Subquery)是将一张表的查询结果作为另一张表的查询依据并层层嵌套,其实也可以先将这些表链接(Join)合并成一个包含所需全部信息的详情表再直接在详情表里筛选查询。两种方法一般是可互换的,具体用哪一种取决于 效率/性能(Performance) 和 可读性(readability)。
之后会学习执行计划,到时候就知道怎样编写并更快速地执行查询,现在主要考虑可读性
案例
题目:
在 sql_invoicing 库 clients 表中找到那些没有过发票记录的客户记录(用连接表实现)
USE sql_invoicing;
SELECT DISTINCT client_id, name
-- 不能SELECT DISTINCT *
FROM clients
LEFT JOIN invoices USING (client_id)
-- 注意不能用内链接,否则没有发票记录的顾客(我们的目标)直接就被筛掉了
WHERE invoice_id IS NULL
就上面这个案例而言,子查询可读性更好,但有时子查询会过于复杂(嵌套层数过多),用链接表更好。总之在选择方法时,可读性是很重要的考虑因素。
练习
题目:
在 sql_store 中,选出买过生菜(id = 3)的顾客的id、姓和名(分别用子查询法和链接表法实现并比较可读性)。
-- 法1 完全子查询
USE sql_store;
SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id IN (
-- 子查询2:从订单表中找出哪些顾客买过生菜
SELECT customer_id
FROM orders
WHERE order_id IN (
-- 子查询1:从订单项目表中找出哪些订单包含生菜
SELECT DISTINCT order_id
FROM order_items
WHERE product_id = 3
)
)
-- 法2 混合:子查询 + 表连接
USE sql_store;
SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id IN (
-- 子查询:哪些顾客买过生菜
SELECT customer_id
FROM orders
LEFT JOIN order_items USING (order_id)
-- 表连接:合并订单和订单项目表得到 订单详情表
WHERE product_id = 3
)
-- 法3 完全表连接
USE sql_store;
SELECT DISTINCT customer_id, first_name, last_name
-- 注意 DISTINCT 关键字的运用
FROM customers
LEFT JOIN orders USING (customer_id)
LEFT JOIN order_items USING (order_id)
WHERE product_id = 3
ALL关键字
(MAX (……))
和 > ALL(……)
等效可互换,即“比这里面最大的还大” = “比这里面的所有
对于含比较运算符的嵌套查询来说,嵌套部分的查询语句只能返回一个值。那如果子查询返回多个值,就需要用到ALL关键词了。通常,ANY 关键词经常和比较运算符连用,下面是6种比较运算符与ALL 关键词的搭配结果:
组合 | 含义 |
---|---|
>ALL | 大于子查询结果中的所有值 |
>=ALL | 大于等于子查询中的所有值 |
<ALL | 小于子查询结果中的所有值 |
<=ALL | 小于等于子查询结果中的所有值 |
=ALL | 等于子查询结果中的所有值 |
!=ALL | 不等于子查询结果中的所有值 |
案例
题目:
sql_invoicing 库中,选出金额大于3号顾客所有发票金额(或3号顾客最大发票金额) 的发票记录(两种方法)
-- 法1. 用MAX关键字
USE sql_invoicing;
SELECT *
FROM invoices
WHERE invoice_total > (
SELECT MAX(invoice_total)
FROM invoices
WHERE client_id = 3
)
-- 法2. 用ALL关键字
USE sql_invoicing;
SELECT *
FROM invoices
WHERE invoice_total > ALL (
SELECT invoice_total
FROM invoices
WHERE client_id = 3
)
-- 等同于where invoice_total > all(150, 130, 167, ...)
-- 若没有all, MySQL无法将一个发票金额与3号所有发票金额进行比较
注意:
上面代码中没有group by,相当于是处理整个表中满足
WHERE
子句条件的所有记录。
代码解析:
其实就是把内层括号的MAX拿到了外层括号变成ALL(两种方法是完全等效的):
- MAX法是用MAX()返回一个顾客3的最大订单金额,再判断哪些发票的金额比这个值大;
- ALL法是先返回顾客3的所有订单金额,是一列值,再用ALL()判断比所有这些金额都大的发票有哪些。
练习
查询非管理系中比管理系所有学员年龄大的学员信息 (数据库来源于菊花酱数据分析,见顶上比较运算符的练习2)
SELECT * FROM stu_info
WHERE age > ALL (SELECT DISTINCT age FROM stu_info
WHERE department = '管理系')
AND department != '管理系';
代码解析:
- 首先查询管理系中学生的年龄(去重),得到的结果是22和24;
- 查询出非管理系中年龄比22和24都大的学生信息(也就是年龄大于24的非管理系学生信息)
ANY关键字
ANY/SOME (……)
与> (MIN (……))
等效ANY/SOME (……)
与IN (……)
等效
对于含比较运算符的嵌套查询来说,嵌套部分的查询语句只能返回一个值。那如果子查询返回多个值,就需要用到ANY或者ALL关键词了。通常,ANY / ALL 关键词经常和比较运算符连用,下面是6种比较运算符与ANY 关键词的搭配结果
组合 | 含义 |
---|---|
>ANY | 大于子查询结果中的某个值 |
>=ANY | 大于等于子查询中的某个值 |
<ANY | 小于子查询结果中的某个值 |
<=ANY | 小于等于子查询结果中的某个值 |
=ANY | 等于子查询结果中的某个值 |
!=ANY | 不等于子查询结果中的某个值 |
案例1
题目:
sql_invoicing 库中,选出金额大于3号顾客任何发票金额(或最小发票金额) 的发票记录(两种方法实现)
-- 法一
USE sql_invoicing;
SELECT *
FROM invoices
WHERE invoice_total > ANY (
SELECT invoice_total
FROM invoices
WHERE client_id = 3
)
-- 法二
USE sql_invoicing;
SELECT *
FROM invoices
WHERE invoice_total > ( -- 连续值无法用in
SELECT MIN(invoice_total)
FROM invoices
WHERE client_id = 3
)
案例2
题目:
选出至少有两次发票记录的顾客(用两种方法实现)
-- 法一
USE sql_invoicing;
SELECT *
FROM clients
WHERE client_id IN ( -- 或 = ANY (
-- 子查询:有2次以上发票记录的顾客
SELECT client_id
FROM invoices
GROUP BY client_id
HAVING COUNT(*) >= 2 -- 筛选至少有两次发票记录的顾客
)
-- 法二
USE sql_invoicing;
SELECT *
FROM clients
WHERE client_id = ANY(
-- 子查询:有2次以上发票记录的顾客
SELECT client_id
FROM invoices
GROUP BY client_id
HAVING COUNT(*) >= 2
)
练习
查询非管理系中比管理系任意一个学员年龄小的学员信息 (数据库来源于菊花酱数据分析,见顶上比较运算符的练习2)
SELECT * FROM stu_info
WHERE age < ANY(SELECT DISTINCT age FROM stu_info
WHERE department = '管理系')
AND department != '管理系';
代码解析:
- 首先查询管理系中学生的年龄(去重),得到的结果是22和24;
- 查查询出非管理系中年龄比22或24年龄小的学生信息(也就是年龄小于24的非管理系学生信息)
相关子查询
之前都是非关联主/子(外/内)查询,比如子查询先查出整体的某平均值或满足某些条件的一列id,作为主查询的筛选依据,这种子查询与主查询无关,会先一次性得出查询结果再返回给主查询供其使用。
相关子查询就是当你在子查询中使用了外查询中的表里的某一字段去执行子查询里的某个命令的时候,就叫相关子查询。此时外查询跟子查询因为某个条件被连接在了一起有了相关性。
注意:
这种关联查询是在主查询的每一行/每一条记录层面上依次进行的,这一点可以为我们写关联子查询提供线索(注意表别名的使用),另外也正因为这一点,相关子查询会比非关联查询执行起来慢一些。
案例
题目:
选出 sql_hr.employees 里那些工资超过他所在办公室平均工资(而不是整体平均工资)的员工(两种方法))
思路:
子查询要查询的是某员工所在办公室的平均值,子查询是依赖主查询的。给主查询 employees表 设置别名 e,这样在子查询查询平均工资时加上
WHERE office_id = e.office_id
筛选条件即可相关联地查询到目前员工所在地办公室的平均工资
-- 法一
USE sql_hr;
SELECT *
FROM employees e -- 关键 1
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE office_id = e.office_id -- 关键 2(相同的id才能比较)
)
代码解析:
当被查询记录的office_id =e.office_id时,才执行计算平均工资和筛选出高于平均工资的员工;
首先把外部的employees表和子查询的employees表,分别命名为e1和e2两个独立的表格;
代码首先处理外部的where,此时DBMS会一次将e1(外部employees)的每一条记录的salary字段值一次与括号里的输出结果作对比:
- 此时由于外部employees表已经选择了一条记录,此时
e.office_id
也就确定的,此时只需要对e1表(内部employees)筛选出office_id = e.officd_id的记录,并计算字段salary的均值,与外部employees表已选的一条记录的salary字段值进行比较;重复以上过程,知道外部employees表遍历完每一条记录
总结:
当内部表格被查询记录的office_id =e.office_id(对每位员工执行括号里的子查询)时,才执行计算平均工资和筛选出高于平均工资的员工
注意:
- 子查询表字段不用加前缀,主查询表的字段要加前缀,以此区分;
-- 法二
USE sql_hr;
SELECT e.*
FROM employees e -- 关键 1
inner join
(select office_id, avg(salary) as avg_salary
from employees
group by office_id) as p
using (office_id)
where salary > p.avg_salary
代码解析
- 通过group by建立一个新表只包含office_id和avg_salary,然后和原表合并,再用where进行筛选(第一个括号内容就是生成一个表)
- 本质:原表的基础上加了一列
该员工办公室的平均工资
法二的代码来自于b站评论区的一位大神,膜拜大神
练习
题目:
在 sql_invoicing 库 invoices 表中,找出高于每位顾客平均发票金额的发票
-- 法一
USE sql_invoicing;
SELECT *
FROM invoices i
WHERE invoice_total > (
-- 子查询:目前客户的平均发票额
SELECT AVG(invoice_total)
FROM invoices
WHERE client_id = i.client_id
)
-- 法二
use sql_invoicing;
select i.* from invoices as i
left join (
select client_id, avg(invoice_total) as invoice_average
from invoices as i group by client_id) as p
using (client_id)
where invoice_total > invoice_average
/*通过group by建立一个新表只包含client_id和invoice_average,然后和原表合并,再用where将invoice_total与invoice_average筛选。*/
EXISTS运算符
- EXISTS 关键词的作用和 IN关键词非常类似 ,即
IN + 子查询
等效于EXIST + 相关子查询
- 不同的是,通过EXISTS关键词的嵌套查询返回的不是具体的值集合,而是满足条件的逻辑值(也就是True / False)。也就是说,EXISTS的作用就是“判断是否存在满足某种条件的记录”,如果存在这样的记录就返回真(True),如果不存在这样的记录就返回假
(False)
注意:
- 如果前者(any、in)子查询的结果集过大,占用内存,用后者逐条验证更有效率;
- EXIST() 本质上是根据是否为空返回 TRUE 和 FALSE,所以也可以加 NOT 取反;
- 相比于IN(), EXIST() 并没有真的把结果(True/False)返回给外部查询,并且内查询只要有一条记录满足条件就返回结果TRUE给外部查询,效率会快很多。
案例
找出有过发票记录的客户记录(三种方法)。
-- 法1. 子查询
USE sql_invoicing;
SELECT *
FROM clients
WHERE client_id IN (
SELECT DISTINCT client_id
FROM invoices
)
-- 法2. 链接表
use sql_invoicing;
select distinct client_id, name from clients as c
left join invoices as i -- 内连接也可以,只留下有过发票记录的客户
using (client_id)
where invoice_id is not null
-- 法3. EXISTS运算符
SELECT *
FROM clients c
WHERE EXISTS (
SELECT */client_id
/* 就这个子查询的目的来说,SELECT的选择不影响结果,
因为EXISTS()函数只根据是否为空返回 TRUE 和 FALSE */
FROM invoices
WHERE client_id = c.client_id
)
代码解读:
这还是个相关子查询,因为在子查询中引用了主查询的 clients 表。这同样是按照主查询的记录一条条验证执行的。具体说来,对于 clients 表(设置别名为 c)里的每一个顾客,都会检查是否存在一条满足子查询条件的记录,其中子查询在 invoices 表查找这个人的发票记录( 即 client_id = c.client_id 的发票记录),有就返回相关记录否则返回空,然后 EXISTS() 根据是否为空得到 TRUE 和 FALSE(表示此人有无发票记录),然后主查询凭此确定是否保留此条记录。
注意:
法1是用子查询返回一个有发票记录的顾客id列表,如(1,3,8 ……),然后用IN运算符来判断,如果子查询表太大,可能返回一个上百万千万甚至上亿的id列表,这个id列表就会很占内存非常影响性能,对于这种子查询会返回一个很大的结果集的情况,用这里的EXIST+相关子查询逐条筛选会更有效率
练习1
在sql_store中,筛选出从来没有被订购过的产品记录。(两种方法)
-- 法一
USE sql_store;
SELECT *
FROM products
WHERE product_id NOT IN (
SELECT product_id
-- 加不加DISTINCT对小数据集的影响不大
FROM order_items
)
-- 法二
SELECT *
FROM products p
WHERE NOT EXISTS (
SELECT *
FROM order_items
WHERE product_id = p.product_id
)
练习2
查询MySQL成绩大于85分的学员信息 (数据库来源于菊花酱数据分析,见顶上比较运算符的练习2)
SELECT * FROM stu_info
WHERE EXISTS(
SELECT * FROM stu_score
WHERE stu_score.id = stu_info.id AND MySQL> 85
);
注意:
- 使用EXISTS关键词的嵌套语句 WHERE与EXISTS关键词之间没有任何参数,这是因为EXISTS只需要一个参数,通常是在EXISTS右侧加一个子查询语句。
- EXISTS后面的子查询中SELECT后面可以写表中任何一个字段或者星号或者一个常数,因为EXISTS后面的子查询只关心是否存在满足条件的记录
练习3
查询数学系和计算机系之外的学员信息
SELECT * FROM stu_info
WHERE NOT EXISTS(
SELECT * FROM stu_score WHERE department IN('数学系','计算机系')
and stu_score.id = stu_info.id
)
何时用in, 何时用exist?
如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists,即IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况
SELECT子句的子查询
除此之外,子查询还可以放在select语句、from语句、having语句后面。
SELECT选择语句是用来确定查询结果选择包含哪些字段,每个字段都可以是一个表达式,而每个字段表达式里的元素除了可以是原始的列,具体的数值,也同样可以是其它各种花里胡哨的子查询的结果
注意:
- 任何子查询都是简单查询的嵌套;
- 以子查询方式实现在SELECT中使用同级列别名的方法。
案例
题目:
从invoices中筛选出invoice_id, invoice_total, avarege(总平均发票额), difference(前两个值的差)字段。
USE sql_invoicing;
SELECT
invoice_id,
invoice_total,
(SELECT AVG(invoice_total) FROM invoices) AS invoice_average,
/*不能直接用聚合函数,这里有其他非聚合字段,而
用括号+子查询(SELECT AVG(invoice_total) FROM invoices)
将其作为一个数值结果 152.388235 加入主查询语句*/
invoice_total - (SELECT invoice_average) AS difference
/*SELECT表达式里要用原列名,不能直接用别名invoice_average
要用列别名的话用子查询(SELECT 同级的列别名)即可
说真的,感觉这个子查询有点难以理解,但记住会用就行*/
FROM invoices
注意:
(SELECT AVG(invoice_total) FROM invoices) AS invoice_average
类似于前面的'silver' as points
.
- 不能将
(SELECT AVG(invoice_total) FROM invoices) AS invoice_average
改为AVG(invoice_total) AS invoice_average
,因为select后面有非聚合字段。
练习
从clients筛选出client_id, name, total_sales(各个客户的发票总额), average(总平均发票额), difference(前两个值的差)字段。
USE sql_invoicing;
SELECT
client_id,
name,
(SELECT SUM(invoice_total) FROM invoices WHERE client_id = c.client_id) AS total_sales,
-- 要得到【相关】客户的发票总额,要用相关子查询 WHERE client_id = c.client_id
(SELECT AVG(invoice_total) FROM invoices) AS average,
(SELECT total_sales - average) AS difference
/* 如前所述,引用同级的列别名,要加括号和 SELECT,
和前两行子查询的区别是,引用同级的列别名不需要说明来源,
所以没有 FROM …… */
FROM clients c
注意:
在select子句中引用同级的列别名不需要说明来源(from…),但要加括号和select;
引用的列名如果是是聚合函数则需要说明来源(from…),同时加括号和select。
解释同级:
都位于select子句的as后面的列别名中,中间用逗号分隔。
FROM子句的子查询
子查询的结果同样可以充当一个“虚拟表”作为FROM语句中的来源表,即将筛选查询结果作为来源再进行进一步的筛选查询。
注意:
只有在子查询不太复杂时进行这样的嵌套,否则最好用后面要学的的视图先把子查询结果储存储存为叫 sales_summury 的视图,然后再直接使用该视图作为来源表。
案例
题目:
将上一节练习里的查询结果当作来源表,查询其中 total_sales 非空的记录
USE sql_invoicing;
SELECT *
FROM (
SELECT
client_id,
name,
(SELECT SUM(invoice_total) FROM invoices WHERE client_id = c.client_id) AS total_sales,
(SELECT AVG(invoice_total) FROM invoices) AS average,
(SELECT total_sales - average) AS difference
FROM clients c
) AS sales_summury
WHERE total_sales IS NOT NULL
注意:
在FROM中使用子查询,即使用 “派生表” 时,必须给派生表取个别名(不管用不用),这是硬性要求,不写会报错:
Error Code: 1248. Every derived table(派生表、导出表)must have its own alias
.
总结:
查询数据常用套路:
- 从表B中查询的信息与A表格的某个字段进行比较
- 相关子查询
- 非相关子查询
FROM子句的子查询
子查询的结果同样可以充当一个“虚拟表”作为FROM语句中的来源表,即将筛选查询结果作为来源再进行进一步的筛选查询。
注意:
只有在子查询不太复杂时进行这样的嵌套,否则最好用后面要学的的视图先把子查询结果储存储存为叫 sales_summury 的视图,然后再直接使用该视图作为来源表。
案例
题目:
将上一节练习里的查询结果当作来源表,查询其中 total_sales 非空的记录
USE sql_invoicing;
SELECT *
FROM (
SELECT
client_id,
name,
(SELECT SUM(invoice_total) FROM invoices WHERE client_id = c.client_id) AS total_sales,
(SELECT AVG(invoice_total) FROM invoices) AS average,
(SELECT total_sales - average) AS difference
FROM clients c
) AS sales_summury
WHERE total_sales IS NOT NULL
注意:
在FROM中使用子查询,即使用 “派生表” 时,必须给派生表取个别名(不管用不用),这是硬性要求,不写会报错:
Error Code: 1248. Every derived table(派生表、导出表)must have its own alias
.
总结:
查询数据常用套路:
- 从表B中查询的信息与A表格的某个字段进行比较
- 相关子查询
- 非相关子查询
- 表B与表A进行inner join/left join