练习一:视图
从SQL的角度来看,视图和表是相同的,两者的区别在于表中保存的是实际的数据,而视图中保存的是SELECT语句(视图本身并不存储数据)。
在使用视图时,视图会先运行其保存的SQL语句,从表里查找结果并生成一张临时表。
创建视图:
Create view 视图名称(视图的列名在这里定义)
As
Select查询语句(select语句中列的排列顺序和视图中列的排列顺序相同)
视图的用法:在from子句中使用视图名称代替表名称
SELECT 性别,人数
FROM 按性别汇总;
删除视图:
方法一:在客户端中选中要删除的视图,右键并选择删除视图
方法二:用SQL语句删除
Drop view 视图名称
视图的优点:
- 可以将频繁使用的SQL语句保存成视图,以后使用的时候不用重新书写,从而提高效率
- 视图中的数据会随原表的变化自动更新,保持数据的最新状态
- 视图不保存数据,可以节省储存设备的容量
使用视图的注意事项:
- 避免在视图的基础上再创建视图,多重视图会降低SQL运行性能
- 通过汇总得到的视图无法进行更新
练习二:子查询
子查询是一次性视图,在select语句执行完之后就会消失。
子查询将用来定义视图的select语句直接用在from子句后面,即在select查询语句中嵌套了另一个select查询语句。
SQL运行顺序:先运行子查询,再运行子查询外面的语句。
SELECT 性别,人数
FROM (
SELECT 性别,COUNT(*) AS 人数
FROM student
GROUP BY 性别) AS 按性别汇总
子查询不仅可以放在from子句后面,也可以放在where子句后面,具体是放在in,any(有任何一个满足就返回true),all(全部都满足才返回true)后面的括号里构成复杂的查询条件。
Any和all要与比较运算符使用。Any和some用法相同。
any的用法(哪些学生的成绩比课程号0002的全部成绩里的任意一个高)
SELECT 学号,成绩
FROM score
where 成绩> ANY(
SELECT 成绩
FROM score
WHERE 课程号='0002');
all的用法(哪些学生的成绩比课程号0002的全部成绩里的都高)
SELECT 学号,成绩
FROM score
WHERE 成绩>ALL(
SELECT 成绩
FROM score
WHERE 课程号='0002');
偶尔使用的SQL查询语句可以使用子查询,不用保存为视图。
使用子查询的注意事项:
- 如果两个数值进行比较,比如all得到的是数据,不能写a>3*all(b),正确的写法是a/3>all(b)
- 避免使用多层嵌套子查询
- 子查询的 as 和子查询名称可以省略,但是最好不要省略,养成良好的书写习惯,以便所有人能够看懂子查询语句的意思。
练习三:标量子查询
标量子查询必须且只能返回一行一列的结果。返回单一的值。所以可以和比较运算符一起使用。
标量子查询的运用
SELECT 学号,成绩
FROM score
WHERE 成绩>(SELECT AVG(成绩)
FROM score);
成绩在差生平均成绩和优等生平均成绩之间的学生有哪些
SELECT 学号,成绩
FROM score
WHERE 成绩 BETWEEN
(SELECT AVG(成绩)
FROM score
WHERE 成绩<=60) AND
(SELECT AVG(成绩)
FROM score
WHERE 成绩>=80);
标量子查询的书写位置不局限在where查询语句中,通常任何可以使用单一值的位置都可以使用。能够使用常数或者列名的地方,无论是select子句、group by子句、having子句、order by子句,几乎所有的地方都可以使用。
标量子查询用在select子句中
查询学号、成绩和平均成绩
SELECT 学号,成绩,(SELECT AVG(成绩)
FROM score) AS '平均成绩'
FROM score;
标量子查询用在having子句中
查询成绩大于平均成绩的学生
SELECT 学号,课程号,成绩
FROM score
GROUP BY 课程号
HAVING AVG(成绩)>(SELECT AVG(成绩)
FROM score);
什么时候用标量子查询:
当我们需要单一值的时候。
注意事项:
不能返回多行结果。
练习四:关联子查询
在细分的组内进行比较时,可以使用关联子查询。关联子查询起关键作用的是子查询语句中的where子句。
关联子查询语句
查找每个课程中大于对应课程平均成绩的学生
SELECT 学号,课程号,成绩
FROM score AS s1
WHERE 成绩>(SELECT AVG(成绩)
FROM score AS s2
WHERE s1.课程号=s2.课程号);
关联子查询语句的执行顺序:
1、先执行外层查询,查找出score表里所有的学号、课程号、成绩,结果如下:
SELECT 学号,课程号,成绩
FROM score AS s1
2、上图中的第一条结果进入子查询,因为子查询里通过where语句关联了两个表,子查询得出课程号0001的平均成绩80。
(SELECT AVG(成绩)
FROM score AS s2
WHERE 课程号=0001)
3、将子查询得出的结果80返回外层查询where语句中进行比较,得出执行结果。
SELECT 学号,课程号,成绩
FROM score AS s1
WHERE 学号=0001 AND 课程号=001 AND 成绩>80;
可以看出第一条记录不满足查询条件,查询结果中将不显示此条记录。
4、score表中后面的每行数据进行同样的执行顺序,得出最终结果。
使用关联子查询的注意事项:
- 在上面的案例中,因为比较对象是同一张表score,因此为了进行区别,分别使用了s1和s2两个别名。在使用关联子查询时,需要在表所对应的列名之前加上表的别名,以<表名>.<列名>的形式记录
- 子查询内部设定的关联名称,只能在子查询内部使用。(内部可以看到外部,外部看不到内部)。所以结合条件一定要写在子查询中。
练习五:如何用SQL解决业务问题
- 翻译成能看明白的大白话
- 写出分析思路
- 写出对应的SQL语句
哪些学生的成绩比课程0002的全部成绩里的任意一个高?
第一步:找出课程0002的全部成绩
SELECT 成绩
FROM score
WHERE 课程号='0002';
第二步:将某个学生的成绩与第一步得出的成绩相比较
SELECT 学号,成绩
FROM score
WHERE 成绩 >90 OR 成绩>60 OR 成绩>80;
可以用子查询将以上两步整合在一起:
SELECT 学号,成绩
FROM score
WHERE 成绩 >ANY(SELECT 成绩
FROM score
WHERE 课程号='0002');
练习六:综合练习
视图及子查询适用场景:
图片来源:猴子聊人物
SQL中的函数包含汇总函数、算术函数、字符串函数、日期函数,包含的具体函数见下图(在实际工作中,可以在使用函数时通过搜索引擎查找使用哪个函数,不用每个都记着):
图片来源:猴子聊人物
图片来源:猴子聊人物
图片来源:猴子聊人物
图片来源:猴子聊人物
SQLzoo练习题:
1
select name
from world
where population >(select population
from world
where name='Russia');
2
select name
from world
where gdp/population >(select gdp/population
from world
where name='United Kingdom') and continent='Europe';
3
select name,continent
from world
where continent in (select continent
from world
where name in ('Argentina','Australia'))
order by name;
4
select name,population
from world
where population>(select population
from world
where name='Canada')
and population <(select population
from world
where name='Poland');
或者
select name,population
from world
where population between (select population
from world
where name='Canada')+1
and (select population
from world
where name='Poland')-1;
5
select name,concat(round(population/(select population
from world
where name='Germany')*100,0),'%') as 'population(percentages)'
from world
where continent='Europe';
6
select name
from world
where gdp > all(select gdp
from world
where continent='Europe' and gdp>0);
7
select continent,name,area
from world as w1
where area=(select max(area)
from world as w2
where w1.continent=w2.continent and area>0);
或者
select continent,name,area
from world as w1
where area>=all(select area
from world as w2
where w1.continent=w2.continent and area>0);
8
select continent,name
from world as w1
where name=(select name
from world as w2
where w1.continent=w2.continent
order by name
limit 1);
或者
select continent,name
from world as w1
where name <= all(select name
from world as w2
where w1.continent=w2.continent);
9
select name,continent,population
from world as w1
where 25000000>=all(select population
from world as w2
where w1.continent=w2.continent);
10
select name,continent
from world as w1
where population/3 >all(select population
from world as w2
where w1.continent=w2.continent and w1.name<>w2.name);