一、视图
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.课程号);
五、如何用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;