这次的多表查询内容跟上次的复杂查询比起来,内容相对容易理解,是原来知识的一个拓展,开始学习几个表之间一起查询出结果的问题,主要内容有
1 表的加法
2 表的联结
3 联结应用案例
4 case表达式
1 表的加法是应用于表结构相同的情况,是将数据按行合并在一起,加法(union),重复数据 删除,如果保留重复行,则用union all
2 表的联结
![11d67577d8c80ff18f0a2f272a6b7d39.png](https://img-blog.csdnimg.cn/img_convert/11d67577d8c80ff18f0a2f272a6b7d39.png)
![3c7f067daccd2bb3a3651e1c47b6af9c.png](https://img-blog.csdnimg.cn/img_convert/3c7f067daccd2bb3a3651e1c47b6af9c.png)
3 case表达式
![367de6e748dc999765c5f7791921707e.png](https://img-blog.csdnimg.cn/img_convert/367de6e748dc999765c5f7791921707e.png)
else可以不写,但最好写上,end必须写,case表达式可以写在任何字句里,如果有多种情况需要判断可以使用。
练习
一 表的加法
SELECT 课程编号,课程名称 from course
UNION
select 课程编号,课程名称 from course1;
![d126dec378a76eb2f1e5006e8dd9c1bd.png](https://img-blog.csdnimg.cn/img_convert/d126dec378a76eb2f1e5006e8dd9c1bd.png)
SELECT 课程编号,课程名称 from course
UNION all
select 课程编号,课程名称 from course1;
![1d6043b86990a185c3c73f9239a21d8d.png](https://img-blog.csdnimg.cn/img_convert/1d6043b86990a185c3c73f9239a21d8d.png)
二 表的联结-内联结
select a.学号,a.姓名,b.课程编号 from student as a inner join score as b
on a.学号=b.学号;
![8c8cc5fa71419776320800b54a9e7001.png](https://img-blog.csdnimg.cn/img_convert/8c8cc5fa71419776320800b54a9e7001.png)
二 表的联结-左联结
select a.学号,a.姓名,b.课程编号 from student as a left join score as b
on a.学号=b.学号 where b.学号=NULL;
![ee439108a39de1af0c4e9f56f3c8055d.png](https://img-blog.csdnimg.cn/img_convert/ee439108a39de1af0c4e9f56f3c8055d.png)
三 联结应用案例-查询所有学生的学号,姓名,选课数,总成绩
select a.学号,姓名,count(课程编号) as 选课数,sum(成绩) as 总成绩 from student as a left join score as b
on a.学号=b.学号 GROUP BY a.学号;
![309250c27b6888710771334921fc0024.png](https://img-blog.csdnimg.cn/img_convert/309250c27b6888710771334921fc0024.png)
四 联结应用案例-查询平均成绩大于85的所有学生的学号,姓名,和平均成绩
select a.学号,姓名,avg(成绩) as 平均成绩 from student as a LEFT JOIN score as b
on a.学号=b.学号 group by a.学号 having avg(成绩)>85;
![c9d24e6d8b1cff54609bd45ec32238a4.png](https://img-blog.csdnimg.cn/img_convert/c9d24e6d8b1cff54609bd45ec32238a4.png)
五 联结应用案例-查询学生的选课情况:学号,姓名,课程号,课程名称
select a.学号,a.姓名,c.课程编号,c.课程名称 from student as a inner JOIN score as b on a.学号=
b.学号 inner join course as c on b.课程编号=c.课程编号;
![dea7acdf164a9657b3bd668e11c30718.png](https://img-blog.csdnimg.cn/img_convert/dea7acdf164a9657b3bd668e11c30718.png)
六 case表达式-显示是否及格
select 学号,课程编号,成绩,
(case when 成绩>=60 then '及格'
when 成绩<60 then '不及格'
else NULL
end)as 是否及格
from score;
![7a0cfc6d58a808a7b9d2dd2a70f144f5.png](https://img-blog.csdnimg.cn/img_convert/7a0cfc6d58a808a7b9d2dd2a70f144f5.png)
七 case表达式-查询出每门课程的及格人数和不及格人数
select 课程编号,
sum(case when 成绩>=60 then 1
else 0
end) as 及格人数,
sum(case when 成绩<60 then 1
else 0
end) as 不及格人数 from score group by 课程编号;
![c438f170edc752bd3adba28edb3ad39c.png](https://img-blog.csdnimg.cn/img_convert/c438f170edc752bd3adba28edb3ad39c.png)
八 case表达式-使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩,分别统计:各分数段人数,课程编号和课程名称
select a.课程编号,b.课程名称,
sum(case when 成绩 between 85 and 100
then 1 else 0 end) as'[100-85]',
sum(case when 成绩>=70 and 成绩<85 then 1
else 0 end) as'[85-70]',
sum(case when 成绩>=60 and 成绩<70 then 1
else 0 end) as'[70-60]',
sum(case when 成绩<60 then 1 else 0 end) as '[<60]'
from score as a right join course as b
on a.课程编号=b.课程编号
group by a.课程编号,b.课程名称;
![6adcec7f23c850511975ca6c34090818.png](https://img-blog.csdnimg.cn/img_convert/6adcec7f23c850511975ca6c34090818.png)
需要注意,group by a.课程编号,b.课程名称 这里, 当用多个列来分组时,这几个列的值全部相同才算一组
补充一下 group by 应用需要注意的
group by 一般和聚合函数一起使用才有意义,比如 count sum avg等,使用group by的两个要素: (1) 出现在select后面的字段 要么是是聚合函数中的,要么就是group by 中的. (2) 要筛选结果 可以先使用where 再用group by 或者先用group by 再用having。
sqlzoo练习
![983875a16e5cee814d52283ecd5f4ab4.png](https://img-blog.csdnimg.cn/img_convert/983875a16e5cee814d52283ecd5f4ab4.png)
![2e2112fc9b92b234a047d7358503cec8.png](https://img-blog.csdnimg.cn/img_convert/2e2112fc9b92b234a047d7358503cec8.png)
![ff76079d69881a8773e8c620b5676828.png](https://img-blog.csdnimg.cn/img_convert/ff76079d69881a8773e8c620b5676828.png)
![2f4cc17f3070d2a47f9d9410237e8fee.png](https://img-blog.csdnimg.cn/img_convert/2f4cc17f3070d2a47f9d9410237e8fee.png)
![a8f227598c6a44e4d68eb7d8ab29c1ac.png](https://img-blog.csdnimg.cn/img_convert/a8f227598c6a44e4d68eb7d8ab29c1ac.png)
![7207fc3f41a1cdf9fc8a012c62bf4efa.png](https://img-blog.csdnimg.cn/img_convert/7207fc3f41a1cdf9fc8a012c62bf4efa.png)
![b5afce0428b7e71cda3568be10f943b4.png](https://img-blog.csdnimg.cn/img_convert/b5afce0428b7e71cda3568be10f943b4.png)
![c9e8be80c1cdcd696093767dba056b79.png](https://img-blog.csdnimg.cn/img_convert/c9e8be80c1cdcd696093767dba056b79.png)
![c85700624942d5425db1467c90858efa.png](https://img-blog.csdnimg.cn/img_convert/c85700624942d5425db1467c90858efa.png)
这个题目一开始不理解为什么按teamname进行count,仔细思考后的结果是因为进行内连接,
因此不同的进球就会有很多相同的球队,因此通过count teamname来算出进球数,因此这个题目要对内连接的共同的部分交叉连接方式和sql运行顺序有了解。另外需要注意,交叉连接和全连接是不同的,一个表的1行,对另一个表的a.b行都做交叉,是交叉连接,而全连接,如果不是公共的部分,则连接后,另一部分不做交叉,只显示null(此部分是我的理解,以后需要再考虑看看)。
![b13b9b88030e29d647471f5fdb4acff2.png](https://img-blog.csdnimg.cn/img_convert/b13b9b88030e29d647471f5fdb4acff2.png)
![dbc94d244313f4af44535d1177b68291.png](https://img-blog.csdnimg.cn/img_convert/dbc94d244313f4af44535d1177b68291.png)
![3ebbdc01181c673e78bf1deaadb151f1.png](https://img-blog.csdnimg.cn/img_convert/3ebbdc01181c673e78bf1deaadb151f1.png)
![00491e0744a6ef0542be4b7602de7dc6.png](https://img-blog.csdnimg.cn/img_convert/00491e0744a6ef0542be4b7602de7dc6.png)
![c73b681fdbcc9e8279a1fff887c5a7c2.png](https://img-blog.csdnimg.cn/img_convert/c73b681fdbcc9e8279a1fff887c5a7c2.png)
13题是case的应用。
这些题做下来感觉还是需要多练习才能掌握,光是理解是不够的,希望自己多加练习,才能理解的更好。