从简单到复杂,从单一到多表,上一关经历复杂,这一次我们开始多表查询了,多表查询的首要任务其实就是把多表变成一个表,然后再继续下一步动作,怎么把多表变成一个表呢?开始我们的学习吧。
1、表的加法(union)
结构一样,但是数据不一样,利用表的加法,把两张表合并成一张表
union:直接将两个表的数据按照行合并到一起,删除重复数据;
union all:将两个表的数据按照行合并到一起,保留重复数据;
2、表的联结(join)
联结是通过表和表之间的关系,把表合并到一起的操作,重点是关系,常用的联结有以下几种:
交叉联结(cross join):两个表没有关系,返回被连接的两个表所有数据行的笛卡尔积;
内联结(inner join):表中至少有一个匹配,则返回数据;
左联结(left join):右表中有没有匹配,都从左表返回数据;
右联结(right join):左表中有没有匹配,都从右表返回数据;
全联结(full join):只要其中一个表有匹配,则返回数据;
实操:
交叉联结(cross join):
交叉联结相当于乘法,所以数据量很大,一般不直接使用,也不常用,不过是所有联结的基础;
内联结(inner join):
左联结(left join)和右联结(right join):
两个原理是一样的只是方向不一样,
加上where是可以取消两表中的重复部分
全联结(full join):
返回左表和右表的所有数据,有匹配就数据合并,没有就用空值填充,my sql不支持全联结;
3、联结应用案例
问题1:查询所有学生的学号、姓名、选课数、总成绩。
分析:
1)学号、姓名(学生表 student)
2)选课数(每个学生的选课数目:成绩表score,按学号分组,对课程号计数count)
3)总成绩(每个学生的总成绩:成绩表 score,按学号分组,对成绩求和sum)
问题2:查询平均成绩大于85的所有学生的学号、姓名和平均成绩
分析:
1)查询出所有学生的学号,姓名,平均成绩;学号,姓名(在学生表student);
平均成绩(每个学生的平均成绩:在成绩表score,按学号分组,平均成绩:avg(成绩));
2)平均成绩>85
问题3:查询学生的选课情况:学号,姓名,课程号,课程名称
分析:
1)学号,姓名在学生表(student)
2)课程号,课程名称在课程表(course)
学生表和课程表没有直接的关系,需要通过成绩表建立关系
4、case 表达式
当有多种情况需要判断的时候,就需要使用case表达式了;
如何符合某个条件,就运行后边的then子句,不符合条件继续进行运行when子句,如果还没有查找到合适的数据,就会到else子句。
问题:判断学生的成绩是否及格
分析:
成绩大于等于60分及格,小于60分就不及格,其他就是空值。
问题:查询出每门课程的及格人数和不及格人数
分析:
统计合格的人数,大于等于60分就统计一个及格人数,没有就是0,然后计算总数
统计不合格的人数,小于60分就统计一个不及格人数,没有就是0,然后计算总数
问题:使用分段{100-85}、{85-70}、{70-60},{<60}来统计各科成绩,根据课程号、课程名称统计各分段人数
分析:
这一题和上面是同样的道理,要判断分段的人数,所以用case语句,因为课程号,课程名和分数不是同一个表,要用到联结。
总结:本次学习的东西信息量挺大的,要多观察多表格之间的联系,而且要熟悉运用联结,这个需要多练习,而且练习之余要多思考,我脑洞大了一下,计算人数,第一时间想到计数函数count,想了老半天,还是觉得sum最合适,这个就是经验了,经验源于苦练,多多练习。
加油!