【MySql】查询：复杂查询

一、视图

1.视图简介：视图存放的是查询语句，使用试图时，会运行里面的语句，创建一张临时表。

2.创建视图

create view 视图名称（<视图名称1>,<视图名称2>,…） as <select 查询语句>

3.NAVICATE操作步骤

1）新建查询

2）写入语句

/*视图*/
create view 按性别汇总(gender,amount)
AS
select 性别,count(*) from student group by 性别;

3）运行语句

4）刷新视图

5）删除视图

4.视图优点

• 频繁使用的、复杂的查询保存为视图里可以减少重复，提高效率；

• 可以动态更新数据；

• 视图不用保存数据，可以节省空间。

5.注意事项

• 避免视图基础上再创建视图

• 不能往试图里面插入数据

二、子查询

1.简介：一次性的视图，在select语句中嵌套的一个select语句，语句运行结束后就消失了。

2.语法

select 列名 from （子查询）as 子查询名；

/*子查询*/
select gender,amount 
from 
(select 性别 as gender,count(*) as amount from student group by 性别) as 按性别汇总;

3.练习

select 课程号,min(成绩) /*,学号*/ from score GROUP BY 课程号;
#这里学号不参与分组，不参与运算，是无意义的，随机的

-- 查询每门课的最低分，并且将对应的学号也查询出来
select score.学号,最低分表.* FROM
(select 课程号,min(成绩) as 最低分 from score GROUP BY 课程号) as 最低分表
join score 
on 最低分表.最低分=score.成绩
where score.课程号=最低分表.课程号;

4.与in、 any|some、 all关键字结合使用

子查询也可以放在where里面，与in(子查询) any(子查询) all(子查询)

1). in（子查询）

-- 查询平均分大于80的学生信息  in(子查询)
select student.* from student 
where 学号 in (select 学号 from score group by 课程号 having avg(成绩)>80);

2). any|some(子查询) 结合比较运算符

-- 查询只要比003学生至少一门成绩高的所有分数    any(子查询)
select * from score where 成绩>any(select 成绩 from score where 学号=0002);
#其实条件就是成绩大于子查询的最小值

3). all(子查询) 结合比较运算符

-- 查询只要比003学生所有成绩都高的所有分数    all(子查询)
select * from score where 成绩>all(select 成绩 from score where 学号=0002);
#其实条件就是成绩大于子查询的最大值

5.使用场景: 相对于视图，子查询适用于偶尔使用的情况。

6.注意事项

1). 子查询得到的是一个集合，不能直接进行运算

select * from score where 成绩>2*any(select 成绩 from score where 学号=0002);#报错
select * from score where 成绩/2>any(select 成绩 from score where 学号=0002);

2). 子查询嵌套不宜复杂

3). 子查询as语句可以省略，但是as语句方便理解

7.运行顺序

子查询 --> [from–>where–>group by -->having] -->select–>[order by -->limit]

三、标量子查询

/*标量子查询*/
-- 大于平均总成绩的学号和成绩
#select * from score where 成绩>总平均成绩; 
#总平均成绩是一个子查询的单个数值结果，就是标量子查询
select * from score where 成绩>(select avg(成绩) from score);

#标量子查询是单一值，可以进行运算
select * from score where 成绩>0.9*(select avg(成绩) from score); 

#任何需要用到单一数值的地方，都可以使用标量子查询
select *,(select avg(成绩) from score) as 总平均值 from score;

#虽然子查询会被先运行，但是不会影响主语句的运行顺序
select *,(select avg(成绩) from score) as 总平均值 from score where 成绩>总平均值;#报错，总平均值不能再使用在主语句
select *,(select avg(成绩) from score) as 总平均值 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子句中引用了外部查询的表。

-- 查询大于课程平均分的所有数据
#1.先将每门成绩的平均分算出来，添加一列
#子查询只进行单纯的分组会报错，因为子查询的结果是一组数，不适用于标量子查询的条件
select * ,(select avg(成绩) from score group by 课程号) as 分科平均成绩 from score ;#报错

#子查询用having子句通过条件筛选这个group by 数组后，就能使得每一行的数据对应一个avg(成绩)
select * ,(select avg(成绩) from score as s2 group by 课程号 having s1.课程号=s2.课程号 ) as 分科平均成绩 from score as s1;

#这里的where子句可以起到和having一样的效果，where已经起到选组作用，group by 子句可以省略
select * ,(select avg(成绩) from score as s2 where s1.课程号=s2.课程号 /*group by 课程号*/) as 分科平均成绩 from score as s1;

#2.变换语句，将大于分科平均成绩的数据查询出来
select * from score as s1 where 成绩>(select avg(成绩) from score as s2 group by 课程号 having s1.课程号=s2.课程号 );
select * from score as s1 where 成绩>(select avg(成绩) from score as s2 where s1.课程号=s2.课程号 );

应用情况：在每个组里进行比较

-- 练习：查询每门课的最高成绩，并将对应的学号显示出来。
select * from score s1 where 成绩>=all (select 成绩 from score s2 where s1.`课程号`=s2.课程号);

参考阅读：https://blog.csdn.net/weixin_40844116/article/details/92759433

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

1.步骤

a.翻译成大白话

b.写出分析思路

c.写出对应的sql语句

2.实例

问题：哪些学生的成绩比课程0002的全部成绩里的任意一个高？

思路：

a.翻译

只要比0002科目下的成绩中至少一个分数高的就成立

b.思路

step1: 子查询–0002课程的成绩有哪些？

step2: 成绩只要大于子查询中一个就成立，>any(子查询)

c.语句

step1:

select  成绩
from  score表
where  课程号是0002
group by 无
having 无
order by 无
limit 无

step2:

select 学号，成绩
from score表
where 成绩大于集合any()
group by 无
having 无
order by 无
limit 无

c.语句

#step1
select 成绩 from score where 课程号=0002;
#step2
select * from score where 成绩>any(select 成绩 from score where 课程号=0002);

报错信息: 从子查询往外注意排查

六、各种函数

/*函数*/
select CURRENT_DATE();
select dayname(now());
select * from student where year(出生日期)=1990;