从零学会SQL：复杂查询

1.视图

1.1 什么是视图

视图中存放的是sql查询语句，使用视图时，会运行视图里的sql查询语句创建出一张临时表。

1.2 如何使用视图

语法：

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

注意：视图列名和select子句的列名是一一对应的

create view 按性别汇总(性别,人数)
as 
select 性别,count(*)
from student
group by 性别;

如何使用：在from子句中使用视图名称代替表名称

select 性别,人数
from 按性别汇总;

1.3 视图有什么用

• 频繁使用某些sql语句时可以使用视图，尤其在进行汇总、复杂查询时，使用视图可以提高效率
• 视图中的数据会随着原表的变化自动更新，保证数据的最新状态
• 视图不保存数据，可以减少存放数据设备的空间

1.4 注意事项

• 注意事项1：避免在视图的基础上再创建视图，多重视图会降低sql的性能和效率
• 注意事项2：不能往视图里面插入数据

2. 练习：如何创建视图

3.子查询

3.1 什么是子查询

子查询是一次性的视图，即在from子句中直接写定义视图的sql查询语句

select 性别,人数
from(
select 性别,count(*) as 人数
from student
group by 性别
)as 按性别汇总;

3.2 如何使用子查询

子查询除了放在from子句中，还可以放在where子句中，与in、any、all搭配使用

3.3 in(子查询)

找出每个课程里成绩最低的学号

-- 找出每个课程里成绩最低的学号
-- 第一步：找出每门课程的最低成绩有哪些值
select 课程号,min(成绩)
from course
group by 课程号;
-- 第二步：在成绩表里查找这些值对应的学号
select 学号,成绩
from course
where 成绩 in(80,60,80);

-- 找出每个课程里成绩最低的学号
select 学号,成绩
from course
where 成绩 in(
select min(成绩)
from course
group by 课程号
);

4.子查询：any和all

any和all子查询需要与比较运算符搭配使用

4.1 any(子查询)/some(子查询)

语法：

select 列名
from 表名1
where 列名>any(子查询);

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

• 第一步：查找出课程号0002的全部成绩
• 第二步：任意一个高——any(子查询)

-- 哪些学生的成绩比课程0002的全部成绩里的任意一个高呢？
select 学号,成绩
from course
where 成绩>any(
select 成绩
from course
where 课程号="0002"
);

4.2 all(子查询)

哪些学生的成绩比课程0002的全部成绩都高呢？

-- 哪些学生的成绩比课程0002的全部成绩都高呢？
select 学号,成绩
from course
where 成绩>all(
select 成绩
from course
where 课程号="0002"
);

4.3 子查询有什么用？

• 偶尔使用sql语句：子查询；频繁使用sql语句：视图

4.4 子查询注意事项

• 注意事项1
  • 错误：a>3*all(b)
  • 正确：a/3>all(b)
• 注意事项2：避免使用多层嵌套子查询，理解困难，性能差
• 注意事项3：as 子查询名称 最好不要省略，便于理解

4.5 sql运行顺序

1. 先运行子查询
2. 每个查询语句里运行顺序：先运行蓝框里的子句→select子句→最后运行红框里的子句

5.标量子查询

5.1 什么是标量子查询

标量子查询的返回结果是单一值

-- 大于平均成绩学生的学号和成绩
select 学号,成绩
from course
where 成绩>(
select avg(成绩)
from course
);

-- 成绩介于差生平均成绩和优等生平均成绩之间的学生？
select 学号,成绩
from course
where 成绩 between
(select avg(成绩)
from course
where 成绩<=60) and
(select avg(成绩)
from course
where 成绩>=80);

5.2 如何使用标量子查询

任何使用单一值得地方都可以使用标量子查询

select 学号,成绩,(select avg(成绩) from course)as 平均成绩
from course;

5.3 标量子查询有什么用

标量子查询是子查询的一种，普通的子查询返回多行结果、单一值或没有结果，标量子查询只可能返回单一值

标量子查询返回单一值，因此可以与in、any、all、between等运算符组合实现复杂的查询条件

5.4 注意事项

如果返回结果是多行，则该查询不是标量子查询，也不能和比较运算符搭配使用

6.练习：案例实战

7. 关联子查询

7.1 什么是关联子查询

7.2 如何使用关联子查询

查找出每个课程中大于对应课程平均成绩的学生

-- 查找出每个课程中大于对应课程平均成绩的学生
select 学号,课程号,成绩
from course as s1
where 成绩>(
select avg(成绩)
from course as s2
where s1.课程号=s2.课程号 #关联条件
group by 课程号
);

注意关联条件一定要写在子查询里面，表别名s1看不到s2

7.3 关联子查询有什么用

在每个组里进行比较的时候，使用关联子查询

8.练习：案例实战

9.如何用sql解决业务问题

9.1 三个步骤

1. 翻译成大白话
2. 写出分析思路
3. 写出对应的sql子句

9.2 实例

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

• 翻译成大白话
  • 第1步：课程0002的全部成绩，比如是(10,30)
  • 第2步：某个学生的成绩大于任意一个第一步里的成绩，就符合条件
• 写出分析思路
  • 第1步：
    • select 查询结果：[成绩]
    • from 从那种表中查找数据：[成绩表]
    • where 查询条件：[课程号是0002]
    • group by 分组：[没有]
    • having 对分组结果指定条件：[没有]
    • order by 对查询结果排序：[没有]
    • limit 从查询结果中取出指定行：[没有];
  • 第2步：比任意一个高——any(子查询)
• 写出对应的sql子句

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

9.3 如何看懂sql报错信息？

逐一排查

10.各种函数

10.1 函数的3个功能

1. 功能
2. 输入
3. 输出

10.2 汇总函数

• count(列名)：求某列的行数,count(*)
• sum(列名)：对某列数据求和，只能对数值类型的列计算
• avg(列名)：对某列数据求平均值，只能对数值类型的列计算
• max(列名)：求某列数据的最大值
• min(列名)：求某列数据的最小值

10.3 算术函数

• round(数值,保留小数的位数)：对数据进行四舍五入
• abs(数值)：绝对值
• mod(被除数,除数)：求余数

round(1234.567,1)
结果：1234.6
round(1234.567,-1)
结果：1230
abs(-100)
结果：100
mod(5,2)
结果：1

10.4 字符串函数

• length(字符串)：字符串长度
• lower(字符串)：大写转换为小写
• upper(字符串)：小写转换为大写
• concat(字符串1,字符串2)：字符串拼接
• replace(字符串,被替换的字符串,用什么字符串替换)：字符串替换
• substring(字符串,截取的起始位置,截取长度)：字符串截取

length('abcde')
结果：5
lower('A')
结果：a
upper('a')
结果：A
concat('调音师','真好看')
结果：调音师真好看
replace('调音师真好看','真好看','真棒')
结果：调音师真棒
substring('abcde',2,3)
结果：bcd

10.5 日期函数

• current_date：当前日期
• current_time：当前时间
• current_timestamp：当前日期和时间
• year(日期)、month(日期)、day(日期)：获取日期的年份、月份、日期
• dayname(日期)：日期对应星期几

current_date
结果：2020-05-20
current_time
结果：10:41:23
current_timestamp
结果：2020-05-20 10:41:23
year('2020-05-20')
结果：2020
dayname('2020-05-20 10:41:23')
结果：星期六