一、表的加法
将course表复制结构和数据,并修改内容,成为course1表
union可将表剔除重复记录后合并。这里指定了列名,是考虑到表结构未知的情况。union all将表剔除重复记录后合并
二、表的联结
有5种连接类型:
1)交叉联结 (cross join):笛卡尔积,表A每一行和表B每一行合并。如:表A有3行,表B有2行,合并后有3*2=6行
文氏图
2)内联结 (inner join):查找同时存在于两张表中的数据
文氏图
用内连接命令,从student表中查询学号和姓名,从score表查询课程号
3)左联结 (left join):有两种类型
文氏图(1)
文氏图(2)
具体分3步。1、将左边的表做主表,取其全部。用左右张表共有的某字段做索引,找出右表中对应的信息。2、剔除左表中和右表重合的信息,即两表共同部分。3、将1和2结果做交叉联结
用左连接命令,从student表中查询学号和姓名,从score表查询课程号
因红框内出现null,说明学号为0004,姓名为王思聪,在score表中课程号字段无匹配记录。又因学号是用来联结student、score两表的索引字段,那指定score表中学号为null,再次运行左联结,查询分别存在于2张表的学号、姓名和课程号
说明score表中不存在为0004的学号。注:若写成学号=null,其结果是显示score表中不存在的学号(为null)对应的学号、姓名和课程号内容,并无意义
4)右联结 (right join):有两种类型
文氏图(1):
文氏图(2):
也是3步。1、将右边的表做主表,取其全部。用左右张表共有的某字段做索引,找出左表中对应的信息。2、剔除左表中和右表重合的信息,即两表共同部分。3、将1和2结果做交叉联结
用右连接命令,从student表中查询学号和姓名,从score表查询课程号
换从score表中找到学号和课程号,再展示student表相应的姓名试试
得到的结果相同
同理,在score是主表的条件下,聚焦student表中学号为null的,student表中学号和姓名,score表中课程号字段,再运行右联结
把红框处换成score表,或是把那个is null换成=null,结果都一样。因为不是显示不存在于score表的学号及其他记录,就是显示不存在于student表的学号及其他
5)全联结 (full join):有两种类型
文氏图(1)
返回左表和右表中的所有行。当左表和右表中某行匹配时,两行合并。当左表和右表中某一表中的行不匹配时,不存在的记录处用null填充。MySQL不支持全联结
文氏图(2)
1、查询所有学生的学号、姓名、选课数目和总成绩
思路:学号、姓名、选修课和总成绩——学号是主键,分别存在于student和score表。姓名存在于student表,课程号、成绩存在于score表,应使用表联结;所有学生——因student表中学号数量多而全,left join;选课数目和总成绩——count(课程号)sum(成绩)group by 学号;最后,分别给两表和字段起别名
红框绿框处结果不同,因count(null)恒为0。注:count(1)与count(*)得到的结果一致,包含null值。count(字段)不计算null值
2、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
思路:姓名在student表,平均成绩avg(成绩),成绩在score表;平均成绩大于85的——是先决条件,所以score表是主表,right join。学号是主键,分别存在于student和score表。所有学生的——group by 学号;最后,分别给两表和字段起别名
用score表的学号:
用student表的学号:
结果一样。因学号在两表中都存在,所以需要指定。而姓名字段仅存在于student表,无需申明
3、查询学生的选课情况,包含学号、姓名、课程号、课程名称字段
方法1
思路:学号存在于student和score两表,姓名在student表,课程号在score表和course表两中,课程名称字段在course表中;3表联结;以每个学生为单位——group by 学号,又以课程号为单位——group by 课程号;3表联结均为left join,联结词分别为学号和课程号;给3表分别起别名;学号和课程号两字段,需指明从哪张表中取
再贴一次student、score和course表
注:from后要把联结类型,和用于联结表的共同字段一次写完,才能跟group by。若需按学号分组,再按课程号分组,不能用and联结两个group by,而应该是group by 学号,课程号。红框处取score表,还是course表(用b还是c),结果一样
方法2:
其实,红框处可a可b,绿框处可b可c。显然,和方法1比,这个排序优,但inner join的性质决定其仅显示3表共同部分。也不需要上面group by子句排序
那么,再把方法1排序优化一下
若按score表的学号排序,即替换红框部分为b
因按score表中课程号为null,有优先排序的属性,所以姓名为王思聪的记录最先显示
三、用case表达式查询——当有多种条件判断时使用
1、查询学号、课程号、成绩和是否及格(建立一个名为是否及格的列,将判断该成绩是否及格的文字结果放入该列)
思路:学号、课程号、成绩均在score表里;是否及格——包含不及格(成绩<60)和及格(成绩>=60)两种条件判断,属多种条件判断,用case表达式
红框处null无法省略,且case表达式中的第2个when要和第1个when对齐,else要和when对齐,end要和case对齐,这种缩进无法省略
红框这种方式也不允许,case表达式要独占一行
也不能使用*拼接字段,如红框所示
2、查询每门课程的及格人数和不及格人数
思路:还是score一张表,查询课程和人数——select 课程号,人数;每门课程——group by 课程号;人数——count(学号)as 人数
及格人数和不及格人数——属多条件判断,用case表达式替换红框部分;而count()的括号中仅能用列名或*,无法使用条件判断,需用sum()函数完成计数;只能将及格条件和不及格条件均设定为1,方能计数:when 成绩>=60 then 1, when成绩<60 then 1;要想同时容纳2个设定为1的条件,势必要分写在两套判定条件里,外套2个sum函数,else处设为0:sum(case when 成绩>=60 then 1 else 0 end),sum(case when 成绩<60 then 1 else 0 end);把第1个sum函数的结果起别名为及格人数,把第2个sum函数的结果起别名为不及格人数
使用case表达式注意事项:
1)case表达式中的else后跟null时,这个else null可省略,但为养成良好书写习惯,不要省略
2)case表达式中的end不能省略
3)在SQL语句中case表达式的位置不限
3、查询各科成绩的各分段人数、课程号和课程名称,其中成绩的各分段指:将成绩分成100-85组,85-70组,70-60组,<60组,共4个分段
思路:各科——group by 课程号;各分段人数——多条件判定+计数,sum函数套case表达式;成绩......课程号和课程名称——分处score和course2表,要给2表起别名并建立联结;因2表中含有的课程号一致,所以可左联结,也可右联结;最后给各分段人数起别名
图中a.成绩处的a.可省略,因为成绩字段并非在2表中都出现,无需指明;红框内可换成a.成绩>=85 and a.成绩<=100;黄框内可换成right;蓝框内可换成b;绿框内均为要细致小心避免遗漏处
注:原则上,因select 显示课程号和课程名称两字段,要和最后的group by后的课程号和课程名称两字段分组相对应,方能显示;但因此例中课程号和课程名称是一一对应的,则可将本应是group by a.课程号, b.课程名称句中,最后的b.课程名称省略。若课程号和课程名称非一一对应,则要用group by a.课程号, b.课程名称这种多列分组。而多列分组时,只有这几列的值完全相同才算一组
四、sqlzoo的join练习(为了省事,直接用了英文的)The JOIN operation
注:join即inner join
红框处可换为<>
提示说要在SELECT子句中用到count(*),指的是对goal表中的所有记录计数
红框处可换为*
两红框处要对应,即因select 中出现了两个列名(如第1个红框所示),group by不得不也跟两个相同列名(如第2红框所示)
虽然,红框处写为*可得出同样结果,但指明列名(如红框所示)更严谨
13、查询mdate、team1、score1、team2和score2字段,并用mdate、matchid、team1和team2分类
思路:显示mdate、team1、score1、team2和score2字段——mdate、team1、team2字段在game表中,score1和score2需要从goal表中计算。两表需使用联结,照搬其提示:FROM game JOIN goal ON matchid = id;score1和score2显示进球场次——多条件判定,用CASE表达式。语句参考提示CASE WHEN teamid=team1 THEN 1 ELSE 0 END score1。提示说用sum计数——sum(case表达式)。因有两队,有2个sum(case表达式)。将2个sum(case表达式)分别起别名;用mdate、matchid、team1和team2分类——group by mdate,matchid,team1,team2
有点复杂,把它涉及的2表的内容贴一遍
注意是left join,而不是join,因为game表的id内容比goal表内容多;另,此处示范了如何不换行的书写case表达式