多表查询:从多张表中获取数据;
1、表的加法(union)
两个表的结构一样,但是红框的数据不一样,利用表的加法,把两张表合并成一张表;
表的加法(union)是将两个表的数据按照行合并到一起,删除重复数据;
要想保留重复数据,用union all
2、表的联结
1)什么是联结
关系就是数据库能对应的匹配,在关系数据库中,叫做联结(join);
联结是通过表和表之间的关系,把表合并到一起的操作;
学生表-成绩表 通过学号联结;成绩表-课程表 通过课程号联结;课程表-教师表 通过教师号联结;
2)常见的联结方式
①交叉联结(笛卡尔积)
是将表中的每一行,都与另一个表的每一行合并在一起;
如图:表1、表2、表3分别和A、B合并在一起;
新表的行数=表1行数*表2行数。
交叉联结在实际工作中用的比较少,结果行数太多,需要花费大量的运算成本和设备的支持,
而且行数太多,实际价值有限;
但交叉联结是后边所有联结的基础,其他的联结都是在交叉连接的基础上+过滤条件。
②内联结(inner join)
查找同时存在在两张表中的数据
内联结的运行:分别从学生表、成绩表中取出符合条件的行 → 交叉联结;
内联结SQL语句:
/*
③左联结(left join)
将表中左侧的数据全部查找出来,左联结是下图中红色的部分;
左联结的运行:通过学号产生匹配关系,左联结会把左边的表作为主表,全部数据读取出来(学生表的数据全部取出来),右边的表只取学号相同的数据,然后交叉联结合并;
左联结SQL语句:
select
如何联结下图的语句,只保留红色区域
左联结+where子句
④右联结(right join)
右侧表中的数据全部取出来,右边0005在左边的表中没有对应的数据,显示null
右联结的运行:通过学号产生匹配关系,把右表的全部数据读取出来(成绩表的数据全部取出来),左边的表只取学号相同的数据,然后交叉联结合并;
右联结SQL语句:
select
右联结基础上,去掉重合的部分
⑤全联结(full join)
返回左表和右表的所有行,当某行和另一个行中数据匹配的时候,两个行进行合并,如果没有匹配的行,对应的地方用空值填充,my sql不支持全联结;
SQL 联结方式总结
3、联结应用案例
翻译成大白话,写出分析思路,写出对应的sql语句;
问题1:查询所有学生的学号、姓名、选课数、总成绩
1)学号、姓名(学生表 student)
2)选课数(每个学生的选课数目:成绩表score,按学号分组,对课程号计数count)
3)总成绩(每个学生的总成绩:成绩表 score,按学号分组,对成绩求和sum)
/*
问题2:查询平均成绩大于85的所有学生的学号、姓名和平均成绩
1)查询出所有学生的学号,姓名,平均成绩;学号,姓名(在学生表student);
平均成绩(每个学生的平均成绩:在成绩表score,按学号分组,平均成绩:avg(成绩));
2)平均成绩>85
select
问题3:查询学生的选课情况:学号,姓名,课程号,课程名称
1)学号,姓名在学生表(student)
2)课程号,课程名称在课程表(course)
学生表和课程表发生联系,需要通过成绩表建立关系
select
4、case 表达式
case when <判断表达式> then <表达式>
when <判断表达式> then <表达式>
when <判断表达式> then <表达式>
......
else <表达式>
end
1)case表达式的作用
当有多种情况需要判断的时候,就需要使用case表达式了;
可以帮助我们解决复杂的查询问题,case表达的作用相当于进行一个条件判断的函数,用来判断每一行,是不是满足某个条件;
如何符合某个条件,就运行后边的then子句,不符合条件继续进行运行when子句,如果还没有查找到合适的数据,就会到else子句。
2)问题1:
select
运行顺序:
第一步:先运行>=60,满足后,显示及格,end;
第二步:在运行<60,满足后,不及格,结束end。
问题2:查询出 每门课程 的 及格人数 和 不及格人数
查询出 每门课程 的人数
select
查询出 每门课程 的 及格人数 和 不及格人数
select
3)case表达式 注意事项
①else可以省略不写,这时候默认else是空值,为了养成更好的书写习惯,不建议省略;
②最后的end不能省略;
③把case表达式放在了select子句中,对查询结果进行了条件判断,其实case表达式可以写字SQL语句的任意子句中。
练习:
使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩,分别统计:
各分数段人数,课程号和课程名称
各分数段人数(成绩表 score),课程号和课程名称(课程表 course)
select
5、SQL Zoo练习
/*