MySQL数据分析进阶（五）编写复杂查询

※食用指南：文章内容为‘CodeWithMosh’SQL进阶教程系列学习笔记，笔记整理比较粗糙，主要目的自存为主，记录完整的学习过程。（图片超级多，慎看！）

【中字】SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！https://www.bilibili.com/video/BV1UE41147KC/?spm_id_from=333.1007.0.0&vd_source=b287f1f4a1fa54cc438e31a0f87ef4e2

 

 

第五章：编写复杂查询

 

 

1、WRITING COMPLEX QUERIES——编写复杂查询

 

（第一步：重置数据，回到初始化，便于学习）

（之前的坑还是要补了。。。。。。）

 

执行后刷新即可

 

 

2、SUBQUERIES——子查询

返回单一值，再在WHERE子句中和比较运算符使用

执行该这条查询时，首先评估内查询或者子查询，即获得lettuce的unit_price，把结果传给外查询

（前面章节中可以在SELECT、FROM、WHERE中编写子查询）

 

 

练习：

 

 

3、THE IN OPERATOR——IN运算符

写一张可以返回一张有多列表的查询

找到order_items表内所有产品；再写一段查询返回不在这张表里的产品

 

练习：

 

 

4、SUBQUERIES VS JOINS——子查询VS连接

不用子查询，而使用连接来写，如何操作

以上例子如果不用子查询，而使用连接来写，如何操作

选择所有客户，用invoices表连接这些客户

使用内连接，只会获取所有发票的客户；使用外连接（LEFT JOIN）会获取所有客户，不论是否有发票

 

使用子查询还是连接取决于：表现、可读性

如果执行时间一致，应该选择最易读的查询

 

如上例子，第一个查询更直观，和要解决的问题步调更一致，目的是找没有发票的客户（NOT IN），LEFT JOIN和问题有些摩擦（并不总是）

有时添加子查询会使一个查询太过复杂，那时则适合用连接

※永远要花心思在代码的可读性上

 

练习：

 

显然第二种更有意义，因为customers、orders、order_items之间有一种自然的关联

 

 

5、THE ALL KEYWORD——ALL关键字

一般方法：对MAX（invoice_total）单一值比较

ALL关键字：对（invoice_total）列值中最高的进行比较

两种皆可使用看个人习惯（第二种能直观反映问题所在）

 

 

6、THE ANY KEYWORD——ANY关键字

 

以上条件返回client信息

 

client_id=ANY：client_id等于这段查询返回的值里的任何一个，那位客户就会被返回最终结果

※=ANY和IN运算符等效

 

 

7、CORRELATED SUBQUERIES——相关子查询

表中含有员工工资、所在部门、部门1有4位员工，找出这些员工的平均工资，只返回平均线以上的员工；同样找部门2的员工平均工资、平均线以上

所以这里的平均数不是固定不变的

伪代码：长得像代码却不是代码

①如何筛选一个部门的员工：

先给表格起别名employees e，

难点在WHERE office_id=e.office_id：内外查询都使用employees表，为了获取同各部门的员工，在office_id加上前缀

②MySQL执行以上查询逻辑是：

定位employees表，对每位员工执行子查询，计算同一个部门员工

（office_id=e.office_id）的平均工资

如果该员工的工资高于平均值，将被返回到最终结果里

第二条记录也会计算所有同一部门员工的平均工资，

 

因为子查询和外查询存在相关性，内查询中引用了外查询里出现的别名

e.office_id，

所以子查询会在住查询每一行的层面执行

也因此，相关子查询执行得比较慢，数据越多查询更费力，占用更多的存储

 

练习：

发票表中，对每位客户都要找到平均发票额，也就是invoice_toal列的平均，只返回有更高总额的发票，并对每个顾客都重复如上操作，此处用到相关子查询，因没有整张表采用一样的平均值

 

8、THE EXISTS OPERATOR——EXISTS运算符

用上面学过的方法：①IN运算符；②连接

 

使用EXISTS

使用相关子查询把内外连接关联，在子查询中使用client_id，然而主查询WHERE子句没有引用列，而是用EXISTS运算符查看invoices表中是否有符合这个条件的行

MySQL逻辑：对clients表中每个客户都检查是否存在一条符合这个条件的记录

 

IN运算符和EXISTS运算符方法的区别：

IN运算符：MySQL执行子句后结果返回到WHERE子句，如果数据巨大，会妨碍最佳性能

EXISTS运算符：能提高效率，子查询没有给外查询返回一个结果，而是返回一个指令，说明子查询中是否有符合这个搜索条件的行

子查询返回TRUE指令，EXISTS运算符就会在最终结果里添加当前记录（当前客户）

 

练习：

            

 

9、SUBQUERIES IN THE SELECT CLAUSE——SELECT子句中的子查询

不能在表达中使用列的别名

 

练习：

WHERE client_id = c.client_id，子查询就可返回每位顾客的总成交量

 

10、SUBQUERIES IN THE FROM CLAUSE——FROM子句中的子查询

直接将上节的表当作是真实的一张表使用

在选择语句的FROM子句中使用子查询

当在FROM子句中使用子查询，需要给子查询一个别名（必选项），不管会不会使用到别名

暂无变化

 

 

使用该表，并把空值去掉

还可以将这张表与其他表连接使用

 

 

在选择语句中的FROM子句中写子查询，会让主查询变得更复杂，仅限于简单的查询

一个更好的解决方案就是使用视图

将这部分作为视图储存在数据库中，并将视图命名为sales_summary，可以大大简化查询

 

 

————TBC