本文用到的数据(如下图)

一. 视图

1.1 如何创建视图

公式：create view 视图名称(<视图列名1>,<视图列名2>,......) as <select 查询语句>;

• 创建视图:创建视图名称为'按性别汇总'的学生表中查询性别列行数并用性别进行分组

【create view 按性别汇总(性别,人数) as select 性别,count(*) from student group by 性别;】输出结果男=3、女=1

1.2 如何使用视图

1. 创建的视图会跟随着原始数据的变动实时更新
2. 不能在SQL视图数据中插入数据，否则会报错
3. 使用select查询语句选择要查询的数据表也可使选择对已生成的视图查询
4. 若要查询的数据表为某种经常要查询的数据可建立为SQL视图，避免每次重复查询
5. 避免在SQL视图的基础上再次新建SQL视图，会降低SQL的效能

【select 性别,人数 from 按性别汇总;】输出结果男=3、女=1

二. 子查询

2.1 子查询定义

• 子查询可以返回多行结果，返回的是多行数据
• 在SQL查询语句中直接写定义视图的SQL查询语句(即一个select查询语句中嵌套另一个select查询语句，且第二个嵌套的select查询语句要定义子查询的视图名称 as '子查询名称'
• as '按性别汇总：子查询的名称，即从建立的视图中查询所需要的数据
• 子查询运行顺序：先运行子查询语句，在运行外部查询语句

【select 性别,人数 from (select 性别,count(*) as 人数 from student group by 性别) as 按性别汇总;】输出结果男=3、女=1

2.2 使用子查询

• in子查询：找出每个课程里成绩最低的学号

【select 学号,课程号,成绩 from score where 成绩 in (select min(成绩) from score group by 课程号);】

• any子查询：任意一个查询数据，且与some(子查询相同)
• 在学生成绩表中哪些学生的成绩比课程0002的全部成绩里的任意一个高？

【select 学号,成绩 from score where 成绩 > any(select 成绩 from score where 课程号 = '0002');】

• all子查询：全部查询数据
• 在学生成绩表中哪些学生的成绩比课程0002的全部成绩里的(全部)都高？

【select 学号,成绩 from score where 成绩 > all(select 成绩 from score where 课程号 = '0002');】输出结果0001=99

2.3 注意事项

1. 偶尔查询的数据可用子查询，频繁查询某个数据最好使用视图
2. 避免使用多层嵌套子查询，会影响SQL查询效能
3. 查询的as'子查询名称'子查询名称可以省略(尽量写上)
4. 在使用all(子查询)时不能用运算符。因为all(子查询)得到的是N行数据，不是一个数值型数据。例如a>3*all(子查询)不可以，a/3>all(子查询)可以

三. 标量子查询

3.1 定义

1. 标量子查询不能返回多行结果，返回的只能是单一的值(一行一列定位的值)
2. 任何存在单一值的地方都可以使用标量子查询

• 大于平均成绩学生的学号和成绩，平均成绩是81.125

【select 学号,成绩 from score where 成绩 >(select avg(成绩) from score);】输出结果0001=90、0001=99

• 定义差生为成绩<=60，优等生为成绩>=80。between...and...
• 成绩查询介于差生平均成绩和优等生平均成绩的学生的学号和成绩有哪些

select 学号,成绩 from score where 成绩 between(
select avg(成绩) from score where 成绩<=60) and (
select avg(成绩) from score where 成绩>=90
);

• 查询成绩表中的平均成绩(标量子查询可以放在存在单一值的地方)

【select 学号,成绩,(select avg(成绩) from score) as 平均成绩 from score;】输出结果学号=成绩=平均成绩81.125

3.2 注意事项

• 以下为错误语法示范：标量子查询不能返回多行数据

select 学号,成绩,(select avg(成绩) from score group by 课程号) as 平均成绩 from score;

四. 关联子查询

4.1 定义

1. 当数据表中多个数据组使用子查询里的关联条件进行比较查询

• 查找出每个课程中大于每门课程平均成绩的学生
• 子查询的关联条件：where s1.课程号 = s2.课程号
• 课程号1的平均成绩=80，课程号2的平均成绩=76.67，课程号3的平均成绩=86.33
• s2仅在子查询里有效，所以外部的表别名s1可以在子查询里识别出来

select 学号,课程号,成绩 from score as s1 where 成绩 > (select avg(成绩) from score as s2 where s1.课程号 = s2.课程号 group by 课程号);
输出结果0001=0002=90、0001=0003=99、0003=0002=80

4.2 总结

偶尔查询的数据使用：

1. 多行子查询
2. 标量子查询(in，any，all，between) 单一值
3. 关联子查询(数据组比较) 关联条件

经常查询的数据使用：

1. 使用创建视图create view

五. 函数总结

• 查找1990年出生的学生名单

【select 学号,姓名 from student where year(出生日期)=1990; 】输出结果0002=李彦宏、0004=王思聪