子查询返回结果形式不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)
子查询在主查询中出现的位置不同:
select后面:
仅仅支持标量子查询
from后面:
支持表子查询
where或having后面:★
标量子查询(单行) √
列子查询 (多行) √
行子查询
exists后面(相关子查询)
表子查询
子查询和操作符:
单行操作符<,>,=,<=,>=,<>和标量子查询一起使用;
any,all,in,not in和列子查询一起使用,=any形式也可;
SELECT查询养成去重的习惯SELECT distinct;
视图和子查询
视图和子查询是SQL查询语句中功能性和灵活性更强的语句,可以理解成小模块。那么,学习这种具有独立功能的模块,应该做到以下几点——
是什么。如何使用。为什么用。注意事项。举例说明。
子查询涉及到更多的条件,条件之间彼此牵连, 难在分析清楚条件中内含的嵌套逻辑。
这一部分进阶促使我意识到复杂SQL查询需要更多的实战和练习,才能加深理解,提高熟练度,要结合更多的案例才有可能驾轻就熟。
主要包括视图,子查询,使用它们解决问题的思路,常用函数
一、视图(视图很像origin里的模板,科研狗的胡言乱语.....)
- 视图不存放实际数据,存放的是sql语句,能够创建一个新的临时表;
- 创建视图时,视图列名的顺序应该和查询语句中的列的顺序对应;
create view 视图名称(视图列名1,视图列名2,.....)
as
select 列名1,列名2,...
from 表名
......
练习
#创建视图,按性别汇总人数
![76f4d239395f012745b0642fa69f61ea.png](https://img-blog.csdnimg.cn/img_convert/76f4d239395f012745b0642fa69f61ea.png)
- 使用视图时,查询语句的字段名应该使用创建视图时用的视图列名。
select 视图列名1,视图列名2,....
from 视图名称;
(在from子句中,使用视图名称代替原表名称。)
练习
#使用视图,使用“按性别汇总”视图汇总各性别的人数
![a9707444f2e0165cfd1350429b69057a.png](https://img-blog.csdnimg.cn/img_convert/a9707444f2e0165cfd1350429b69057a.png)
注意事项:
避免多个视图嵌套,会影响SQL的性能;视图中不能插入数据;
子查询
- 在from子句中直接写定义视图的sql查询语句,就形成了一个创建临时表子查询;
- 执行sql语句时,先运行内部sql子查询语句,在运行外部sql查询语句;
- 子查询可以放在from子句中,也可以放在where子句中,视具体情况而定;
select 列名1,列名2,...
from (select 查询语句)
as X;
练习
#使用子查询按性别汇总人数
![ae276827d9362d57a91364b0901668e7.png](https://img-blog.csdnimg.cn/img_convert/ae276827d9362d57a91364b0901668e7.png)
- 使用子查询时常常和in,all,any结合使用,....in(子查询);....all(子查询);....any(子查询);.....between(子查询) and(子查询);
练习1
#找出每个课程里成绩最低的学号,思路:1、按课程分组找到各自最低成绩;2、查询组内最低成绩的学号,组内比较;(这是个关联子查询)
![ca5e7800a04585d63a7d554a55cfcb3e.png](https://img-blog.csdnimg.cn/img_convert/ca5e7800a04585d63a7d554a55cfcb3e.png)
![6a556a921dbb19781d81af1bbb089ac3.png](https://img-blog.csdnimg.cn/img_convert/6a556a921dbb19781d81af1bbb089ac3.png)
更正如下:
![9fab36a723608c82e850ea59948b00b5.png](https://img-blog.csdnimg.cn/img_convert/9fab36a723608c82e850ea59948b00b5.png)
![67412152d7a5933d0cd7501c1e9b4a03.png](https://img-blog.csdnimg.cn/img_convert/67412152d7a5933d0cd7501c1e9b4a03.png)
练习2、3
#查询哪些学生的成绩比课程0002的全部成绩里的任意一个高,思路分两步:
![003b1a71026ac21ba82bd803cafdc668.png](https://img-blog.csdnimg.cn/img_convert/003b1a71026ac21ba82bd803cafdc668.png)
#查询哪些学生的成绩比课程0002的全部成绩都高,思路分两步:
![988339463c80fdef46647785d6cf711c.png](https://img-blog.csdnimg.cn/img_convert/988339463c80fdef46647785d6cf711c.png)
注意事项:
- all,any(结果)中是集合,要避免算术运算误用;
- 子查询和视图一样避免嵌套,影响性能又难于理解;可以使用as为子查询命名;
![9aa01854cd7d65ca535186ee2f32ebe4.png](https://img-blog.csdnimg.cn/img_convert/9aa01854cd7d65ca535186ee2f32ebe4.png)
包含子查询的SQL语句的运行顺序:
![385f638d07641c95424b5682f4c3f56d.png](https://img-blog.csdnimg.cn/img_convert/385f638d07641c95424b5682f4c3f56d.png)
标量子查询——是指只返回一个值的子查询,可作为单独的一个结果使用。
- 它可能会用到分组函数,比如使用avg函数,但用了分组函数的子查询未必就是标量子查询,因为分组后可能有多个结果。
练习
#大于平均成绩的学生的学号和成绩
![11d609f7aa89b89bd441c20fab0766fd.png](https://img-blog.csdnimg.cn/img_convert/11d609f7aa89b89bd441c20fab0766fd.png)
#成绩位于差生<=60和优等生>80成绩之间的学生的学号和成绩
![12771875a5d610333cba1d6dced1ec5b.png](https://img-blog.csdnimg.cn/img_convert/12771875a5d610333cba1d6dced1ec5b.png)
注意事项:
- 标量子查询仅仅返回一个结果,所以不能错误的把分组函数和group by结合起来一起使用。
√select 学号,成绩,(select avg(成绩)from score);
×select 学号,成绩,(select avg(成绩)from score group by 课程号);
关联子查询——关联子查询有一点绕人,结合sql语句的执行顺序和底层指令来理解
- 前面提到标量子查询返回单一值;若是需要多个组里的内容自行比较,则需要分成多个组,而组内又需要单一值来实现算术比较时——这时即用到关联子查询。
练习
#查找每个课程中大于对应课程平均成绩的学生
![9238371e0e8744bc04cc4b723f05a371.png](https://img-blog.csdnimg.cn/img_convert/9238371e0e8744bc04cc4b723f05a371.png)
因为之前通读过必知必会,此处乍一看关联子查询很像表的自连接,但是两者并不相同,自连接是为了避免歧义导致SQL解析出错;关联条件实际上是通过外内表的关联对内表列值进行过滤。
个人理解为,SQL进行列值比对时并不是各行同时进行比对,举个例子:(以下图为例)
![18a54c1fa637296da1213de157d14fa1.png](https://img-blog.csdnimg.cn/img_convert/18a54c1fa637296da1213de157d14fa1.png)
s1.课程号的第一行'0001'逐一与s2.课程号的每一行进行匹配,并返回相匹配的行们,随后接着向下执行group by子句等;
s1.课程号的第二行'0002'逐一与s2.课程号的每一行进行匹配,并返回相匹配的行们,随后接着向下执行group by子句等;
..........由此每次子查询每次只返回单一值,与外部表相应列值进行比较。
- 该关联条件每次筛选出和s1中某一行课程号同一组的课程号,因此子查询只返回某一组相同课程号的一个平均成绩。这个某一组就是由关联条件限定出来的———这个where子句的机制,正是体现了1、SQL的语句执行顺序;2、SQL中表之间过滤数据时怎么工作的;
- 上面例子中的关联子查询group by去掉也不影响查询结果,group by使语句更规范更易于理解。它是一个内外分开执行的过程,类似for语句的嵌套循环,豁然开朗。
视图、子查询关系图
![660a96091a5cde8145cbb031675328e9.png](https://img-blog.csdnimg.cn/img_convert/660a96091a5cde8145cbb031675328e9.png)
常用函数——(SQL中很多函数可以在需要时搜索使用)
- 字符串函数;数值函数;日期函数;
- 汇总函数;
SQL ZOO练习
- 因为sql语句的丰富性,同样的要求往往可以使用不同的思路解决。不同子句,异曲同工。
![2073cdbfa75aa3441dcf5d1abc117038.png](https://img-blog.csdnimg.cn/img_convert/2073cdbfa75aa3441dcf5d1abc117038.png)
![22f67c74fed2900aa571fb92a3bacd48.png](https://img-blog.csdnimg.cn/img_convert/22f67c74fed2900aa571fb92a3bacd48.png)
![218e5130ff36826da7a3c9704b0c135a.png](https://img-blog.csdnimg.cn/img_convert/218e5130ff36826da7a3c9704b0c135a.png)
![376620a452d4746700e375cbad2307d0.png](https://img-blog.csdnimg.cn/img_convert/376620a452d4746700e375cbad2307d0.png)
![53bb6aa00f0c6ff0ac82bacea73a3c57.png](https://img-blog.csdnimg.cn/img_convert/53bb6aa00f0c6ff0ac82bacea73a3c57.png)
![14bfc539c0d41bdc37ea8656f00d5624.png](https://img-blog.csdnimg.cn/img_convert/14bfc539c0d41bdc37ea8656f00d5624.png)
![430466b7a84b83cf250b27796f761107.png](https://img-blog.csdnimg.cn/img_convert/430466b7a84b83cf250b27796f761107.png)
![2c569446cb11e540636a889375719bec.png](https://img-blog.csdnimg.cn/img_convert/2c569446cb11e540636a889375719bec.png)
![c69bb554cc4fd492b02c6ff99e85a1f0.png](https://img-blog.csdnimg.cn/img_convert/c69bb554cc4fd492b02c6ff99e85a1f0.png)
![bcd91b3dc8b59da5f3503291121cc9f5.png](https://img-blog.csdnimg.cn/img_convert/bcd91b3dc8b59da5f3503291121cc9f5.png)