本文主要是SQL复杂查询相关内容,包括以下部分。
- 视图
- 子查询
- 标量子查询
- 关联子查询
- 如何使用sql解决业务问题
- 各种函数和SQLZOO练习题
- 总结
一、视图
1、视图是用于存放的是SQL查询语句的,不存放数据,相当于临时存储。
2、视图如何使用
练习题:按性别汇总创建视图
/*创建视图
按性别汇总*/
create view 按性别汇总(性别,人数)
AS
select 性别,count(*)
from student
group by 性别
定义好的视图如何使用?直接引用
select 性别,人数
from 按性别汇总;
3、视图的作用:使用视图可以帮助我们提高效率,有些SQL查询语句需要频繁使用,可以保存为视图。特别是在汇总与复杂查询中,导致SQL查询语句很长,视图可提高效率。视频会随着原表状态自动更新。视图不需要保存数据,可以节省空间。
4、使用视图注意事项
- 避免视图上再使用视图
- 不能往视图里面插入数据
二、子查询
包括什么是子查询,如何使用(案例),作用,注意事项
1、子查询就是一次性视图,在SQL查询语句中直接写定义视图的SQL查询语句。就是在一个select查询语句中嵌套另一个select查询语句。由于该名称是一次性不会像视图一样保存在数据库中,而是在sql查询语句运行后就消失了。子查询可以看成一个临时表(一次性视图)。
2、如何使用子查询
当SQL查询中有子查询,先运行子查询,再运行外部查询语句。
子查询可以应用到where子句中与运算符 in any all等一起使用,构建复杂的查询条件。使用方法 in(子查询) any(子查询)all (子查询)
练习题 :如何找出每个课程里里成绩最低的学号(in子查询应用)
知识点:in(子查询)的应用。在一个查询语句中,嵌套了另一个查询语句。
-- 分析思路:先找出每门课程里成绩最低的值,然后在成绩表查询中找这些学号
select 学号,成绩
from score
where 成绩 in(
select min(成绩)
from score
group by 课程号)
练习题:哪些成绩比课程0002的全部成绩的任意一个/所有 都高呢?
知识点:any(子查询) all (子查询),必须与比较运算符一起使用。
/*拆解问题
第一步:
Select 成绩
From score
Where 课程号='0002’;
第二步:
Select 学号,成绩
From score
Where 成绩>any(子查询);
组合两步即可写出SQL语句*/
select 学号,成绩
from score
where 成绩> any(
Select 成绩
From score
Where 课程号='0002');
select 学号,成绩
from score
where 成绩> all(
Select 成绩
From score
Where 课程号='0002');
3、子查询的作用:子查询就是偶尔使用的 SQL查询语句。在一个查询语句中,嵌套了另一个查询语句。
4、子查询注意事项和运行顺序
注意事项:如果2个数字比较 避免对all运算。all/3>all(b),避免使用多层嵌套,子查询(子查询(子查询)),层层嵌套,给子查询命名,as 子查询名称 可以省略,最好不要省略。
运行顺序
先运行子查询
然后运行from where group by having等
最后运行order by limit 等
三、标量子查询
1、标量子查询:必须而且也只能返回一行一列的查询结果,返回是一个单一的值。返回的单一的值可以和比较运算符一起使用。在where子句中不能使用汇总函数。
2、标量子查询的使用3
练习题:大于平均成绩的学生的学号和成绩
Select 学号,成绩
From score
Where 成绩>(
Select avg(成绩)
From score);
练习题:差生(成绩<=60) 优等生(成绩>80),查找成绩介于差生平均成绩和优等生平均成绩的学生有哪些?
Select 学号,成绩
From score
Where 成绩 between
(select avg(成绩)
From score
Where 成绩<=60)and
(select avg(成绩)
From score
Where 成绩>80);
练习题:查找 学号,成绩、平均成绩
Select 学号,成绩,
(select avg(成绩)
From score ) as 平均成绩
From score
3、标量子查询的作用:标量子查询是子查询的一种,因为返回的单一的值,可以和in all between等一起使用。如果返回多行,就是子查询,不是标量子查询。
4、注意事项:在一行select子句不能使用多行数据。
四、关联子查询
1、关联子查询是在子查询里面有一个关联条件。
2、关联子查询的使用
练习题:查找每个课程中 大于 对应课程平均成绩的学生
Select 学号,课程号,成绩
From score as s1
Where 成绩> (select avg(成绩)
From score as s2
Where s1.课程号 = s2.课程号
Group by 课程号);
Where关联条件:按课程号对成绩表进行分组,同一组数据和这一组的平均成绩进行比较,由于比较对象是同一张表,为了进行区别,分别s1和s2两个别名。关联条件保证了子查询返回的是当前组的数据。
3、当对每个组内进行比较的时候,使用关联子查询。
五、如何使用sql解决业务问题
三步:翻译大白话,写出分析思路,写出sql子句。
案例:哪些学生的成绩比课程0002的全部成绩里的任意一个高?
拆解:
查找出课程0002的全部成绩?
全部学生成绩和上面的全部成绩去比较,大于任意一个就符合条件
Select 学号,成绩
From score
Where 成绩>any
(Select 成绩
From score
Where 课程号= ‘0002’)
如何看懂报错信息
排除法:先运行子查询,逐一排查,然后排查外面的sql。先写子查询试运行。
六、函数的使用和SQLzoo练习题
常用 函数有以下几种,遇到需要使用函数,可利用搜索查找对应函数。
-- 查找1990年出生的学生名单
Select 学号,姓名
From student
Where year(出生日期)=1990;
SQLZOO练习题
SELECT name
FROM world
WHERE population >
(SELECT population
FROM world
WHERE name='Russia')
select name
from world
where continent='Europe'
and gdp/population >(select gdp/population
from world
where name='United Kingdom')
select name,continent
from world
where continent in(select continent
from world
where name='Argentina'or name='Australia')
order by name
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
from world
where gdp >all
(select gdp
from world
where continent='Europe'and gdp>0)
SELECT continent, name, area
FROM world as x
WHERE area >= ALL
(SELECT area FROM world as y
WHERE y.continent=x.continent
AND area>0)
select name, continent
from world as x
where population/3 >
(select max(population)
from world as y
where y.continent=x.continent and x.name <> y.name
group by continent)
七、总结
复杂查询要多实操思考做题,才可能熟练掌握,难度比简单查询难很多。
1、视图等于保存在数据库里的子查询,可以随时调用。
2、子查询:在一个查询里面嵌套另一个查询。分为三种
子查询:返回多行数据。先运行子查询,再运行其他。
标量子查询: 返回单一值数据。返回的单一的值可以和比较运算符一起使用
关联子查询:在每个组里面比较。关联子查询是在子查询里面有一个关联条件。
3、解决问题的方法:翻译成大白话,写出分析思路,写出对应的SQL子句。