子查询用in运算符与用=any的效果是一样的。_复杂查询

主要学习以下知识点：

• 视图
• 子查询
• 标量子查询
• 关联子查询
• 如何用SQL解决业务问题
• 各种函数

一、视图

学习重点

• 从SQL的角度来看，视图和表是相同的，两者的区别在于表中保存的是实际的数据，而视图中保存的是SELECT语句（视图本身并不存储数据）。
• 使用视图，可以轻松完成跨多表查询数据等复杂操作。
• 可以将常用的SELECT语句做成视图来使用。
• 创建视图需要使用CREATE VIEW语句。
• 视图包含“不能使用ORDER BY”和“可对其进行有限制的更新”两项限制。
• 删除视图需要使用DROP VIEW语句。

（1）什么是视图？

视图里面存放的不是数据，是sql查询语句。

（2）如何使用视图？

（3）视图有什么用？

视图有以下这些好处：

1. 如何有些sql语句，你需要频繁的去使用，你就可以保存成视图，这样就不用每次都去重新写一次sql。使用视图可以帮助我们提高效率。
2. 视图中的数据，会随着原表格的变化，自动更新，可以保证数据的最新状态。因为视图存放的不是数据，是sql查询语句。
3. 视图他不需要保存数据，可以减少存放数据设备的空间。

（4）使用视图的注意事项

1. 避免在视图的基础上，再去创建视图。因为多重视图会降低sql的性能和效率。
2. 不能往视图里面插入数据，不然会报错。

二、子查询

学习重点

• 一言以蔽之，子查询就是一次性视图（SELECT语句）。与视图不同，子查询在SELECT语句执行完毕之后就会消失。
• 由于子查询需要命名，因此需要根据处理内容来指定恰当的名称。
• 标量子查询就是只能返回一行一列的子查询。

（1）什么是子查询?

可以把子查询看成一个临时表，在sql语句运行结束后，这个临时表就消失了

sql运行顺序: 先运行子查询

(2) 如何使用子查询

• in (子查询)
• ang(子查询)
• all(子查询)

1. in(子查询)

这里需要用到下方介绍的关联子查询

写法：

SELECT 课程号,学号 as 课程里成绩最低的学号, 成绩
from score as a
where 成绩 in(

select min(成绩)
from score as b
where b.课程号 = a.课程号
group by 课程号)
order by 课程号;

结果:

2. any(子查询)

any子查询必须和 比较运算符一起使用

• 练习1：哪些学生的成绩 比 课程0002的全部成绩里的 任意一个高呢？

思路：

第1步：课程0002的全部成绩，比如是（90，60，80）

第2步：某个学生的成绩 大于 任意一个第1步里面的成绩，就符合条件

写法：

select 学号,成绩
from score 
where 成绩 >any(
select 成绩
from score
where 课程号 ='0002'
);

结果：

3. all(子查询)

all子查询和any子查询一样，都是必须和比较运算符一起使用。

• 练习1：哪些学生的成绩 比 课程0002的全部成绩 都 高呢？

把上面的any换成all

写法：

select 学号,成绩
from score
where 成绩> all(
select 成绩
from score
where 课程号 ='0002'
);

结果：

（3）子查询有什么用？

如果频繁使用某个sql语句，将它保存成 视图 使用比较好

但如果只是偶尔使用，使用子查询

（4）使用子查询的注意事项

1. 不能写成 a> 3*all(b), 要写成： a/3 > all(b) ，因为all得到的不是数据，而是集合，是N行数据
2. 避免使用多层嵌套子查询
3. （select ... from ...子查询 as 子查询名称） 的 as 是可以省略的，但是尽量避免省略

标量子查询

(1) 什么是标量子查询

标量子查询，是在子查询上面做了特殊的限制，只能返回一行一列的查询结果，也就是只能返回单一的值。

注意：不能直接 where 成绩 > avg(成绩)，因为where不能和汇总函数一起用

（2）如何使用标量子查询

标量子查询的书写位置，不仅仅能在where子句里，任何使用单一值的地方，都可以使用这个标量子查询。

写法：

select 学号,成绩,(select avg(成绩)
from score) as 平均成绩
from score;

结果：

（3）标量子查询有什么用?

标量子查询返回的是单一的值，所以可以和比较运算符、in、any、all、between一起用

（4）使用标量子查询注意事项

该子查询不能返回多行结果，如果子查询返回了多行，那就不是标量子查询，而是普通子查询

三、关联子查询

学习重点

• 关联子查询会在细分的组内进行比较时使用。
• 关联子查询和GROUP BY子句一样，也可以对表中的数据进行切分。
• 关联子查询的结合条件如果未出现在子查询之中就会发生错误。
• 普通的子查询和关联子查询的区别

（1）什么是关联子查询

对于外部查询返回的每一行数据，内部查询都要执行一次。在关联子查询中是信息流是双向的。外部查询的每行数据传递一个值给子查询，然后子查询为每一行数据执行一次并返回它的记录。然后，外部查询根据返回的记录做出决策。

（2）如何使用关联子查询

查找出每个课程中 大于 对应课程平均成绩的学生

子查询保证了，每一组的成绩，进行比较

需要注意的是，关联条件一定要写在子查询里面，b仅在子查询里有效，所以b可以看到外面的a, a看不到b

写法：

select 学号,课程号,成绩
from score as a
where 成绩 > (select avg(成绩)
from score as b
where a.课程号=b.课程号
group by 课程号
);

结果：

（3）关联子查询有什么用？

在每个组里进行比较的时候，就要使用关联子查询

四、如何用SQL解决业务问题

• 翻译成大白话
• 写出分析思路
• 写出对应的sql语句

五、各种函数

函数的3个特点：

1. 具备每个功能，就是函数的名称，方便使用它的人，实现特定的某个事情。

2. 具有输入端，用于接收，调用函数的人，给它的数据。输入的数据叫做参数

3. 具有输出端，用于将处理完的数据返回给你

常用的函数：汇总函数、算数函数、字符串函数、日期函数

• 汇总函数-对某列数据进行操作

• 算数函数-对数值进行计算

• 字符串函数-对字符串进行操作

• 日期函数-对日期进行操作

总结:

标量子查询可以和比较运算符，in, any, all, between 一起使用

最后练习 sqlzool里面- SELECT IN SELECT的题目

表列名含义：

name:国家名称
continent:该国家属于哪个洲
area:面积
population:人口
gdp:国内

• 题目8：列出洲份名称，和每个洲份中国家名字按子母顺序是排首位的国家名。(即每洲只有列一国)

select continent,name
from world as x
where name <= (
select name
from world as y
where y.continent = x.continent
group by continent);

• 题目9：列出洲份名称，和每个洲份中国家名字按子母顺序是排首位的国家名。(即每洲只有列一国)

select name,continent,population
from world as x
where 25000000 >= all(
select population
from world as y
where y.continent = x.continent
group by continent);

• 题目10

有些国家的人口是同洲份的所有其他国的3倍或以上。列出 国家名字name 和 洲份 continent

select name,continent
from world as x
where population > all(
select 3*population
from world  as y
where y.continent = x.continent and x.name <> y.name
group by continent);

注：学会看懂报错信息，抓住报错信息进行修改