第四关目录大纲:
1.视图
2.子查询
3.标量子查询
4.关联子查询
5.如何用sql解决业务问题
6.各种函数
1.视图
1.1什么是视图
创建视图
例如:
1.2如何使用视图
使用视图:定义好视图可以直接使用,student中的信息更新后,视图也会自动更新
删除视图:
1.3视图有什么用
频繁使用情景中视图会用到,复杂的查询条件会来提高效率,节省存储设备空间
1.4注意事项
避免视图基础上再创建视图会降低效率,不能再视图里插入数据不然会报错
2.子查询
2.1什么是子查询
一次性的视图,在sql查询子句中直接写定义视图的sql查询语句,即在一个select查询语句中嵌套了另一个select查询语句。
sql运行顺序:先运行子查询
2.2如何使用子查询
子查询可放在:from子句、where子句(搭配in、any、all关键字)
例1. in子查询:找出每个课程里成绩最低的学号
思路:先找出每个课程成绩最低的成绩,再在成绩表查找这些值对应的学号
例2.any子查询:哪些学生的成绩比课程002的全部成绩里的任意一个高呢?
思路:先查询课程0002的全部成绩(60,80),大于任意一个第一步结果的成绩,就符合条件
例3.all子查询:哪些学生的成绩比课程002的全部成绩里的都高呢?
2.3子查询有什么用
2.4注意事项
SQL运行顺序:先运行子查询,剩余按正常顺序
3.标量子查询
3.1什么是标量子查询
where子句中不能用汇总函数,会报错,这时候就要发挥标量子查询作用了
子查询会返回多行结果,也可能返回一行数据,而标量子查询只能返回单一值(一行一列),可以和比较运算符“>、=、<”一起使用
3.2如何使用标量子查询
标量子查询不只仅限于where里面,任何使用单一值的地方都可以使用标量子查询,如select子句中:
标量子查询只有一个值,而平均成绩这列都是81.125:因为select子句中后面跟的三个列名,在查询结果中每一列都是完整的一行,每一行的平均成绩这列每一行都是标量子查询的结果
3.3标量子查询有什么用
标量子查询是子查询的一种,子查询会返回多行,而标量子查询只返回一个值,所以可以和比较运算符及in、any、between一起使用,当需要使用单一值就可以使用标量子查询了。
3.4注意事项
如果子查询返回多行,就不再是一个标量子查询了,而变成了普通的子查询
select子查询返回多行,在一行select子句中不能使用多行数据,因此会报错
4.关联子查询(难)
【查找每个课程中大于对应课程平均成绩的学生】
在每个组里进行比较,用到关联条件
关联条件放进子查询中
6.各种函数
sqlzoo练习:
1.列出符合条件的国家名称,条件:国家人口大于俄罗斯(Russia)的人口
【知识点】标量子查询
2.列出欧洲每个国家的人均GDP,其中人均GDP要高于英国(United Kingdom)
【知识点】比较运算符,逻辑运算符(and),标量子查询
3.在阿根廷(Argentina)和澳大利亞(Australia)所在的洲份中的国家有哪些?查找出国家名称和洲名称,并按国家名称排序
【知识点】在运算符in里使用子查询
4.查找符合下面条件的国家名称和人口:国家的人口比加拿大(Canada)的多,但比波兰(Poland)的少
【知识点】如果使用between和and,边界值包括了边界值,所以要+1,和-1去掉边界值
5.德国(Germany)在欧洲(Europe)國家的人口最多。奧地利(Austria)拥有德国总人口的11%。
查找欧洲的国家名称和每个国家的人口,其中人口以德国人口的百分比来显示人口数
【知识点】标量子查询,字符串连接函数concat,浮点数保留多少位round函数
6.哪些国家的GDP比欧洲(Europe)的全部国家都要高呢? (有些国家的记录中,GDP是空值NULL,没有填入资料)
【知识点】all的用法,子查询,条件中gdp>0用来去掉空值的情况
7.在每一个州中找出最大面积的国家,查找出洲, 国家名字,面积。 (有些国家的记录中,面积是空值NULL,没有填入资料)
【知识点】all的用法,关联子查询
select continent,name,area
from world as x
where area =
(select max(area)
from world as y
where y.continent=x.continent)
8.列出洲份名称和国家名称,其中每个洲只取出一个国家(条件:该国家排序在这个洲的首位)
【知识点】all的用法,关联子查询
select continent, name
from world as x
where name <= all
(select name
from world as y
where y.continent=x.continent)
9.找出符合条件的洲和国家名称,条件:该洲中的全部国家人口都有少于或等于 25000000 人口)
【知识点】all的用法,关联子查询
select name, continent, population
from world as x
where 25000000 >= all
(select population
from world as y
where y.continent=x.continent)
10.有些国家的人口是同洲份的所有其他国的3倍或以上。列出这些国家的名称和洲
【知识点】【知识点】all的用法,关联子查询
如果是两个数字比较,a > 3b 等价于 a/3 > b 。但是,在mysql里all得到的不是一个数字,是一个集合,也就是得到的是n行数据,所以不能写a > 3all(b),语法只能是 a/3 > all(b)。
7-10题总结:all与子查询的语法如下:
select 列名1 from 表名1 where 列名1 > all (子查询)