![6e6c32db643fae61a5ea20bd7d72a92d.png](https://i-blog.csdnimg.cn/blog_migrate/c7a051ac27e3422b1c81efca34a5c839.jpeg)
--表格相加(类似集合相加,除非加上关键词all)
--操作:表-复制表-结构和数据,对新表重命名为course1;打开course1,重新编辑需要的数据
select * from course union select * from course1--或将union改为union all表全部显示包括两表相同内容
--表格联结(关系数据库中表格的关联)(关系即数据库能对应的匹配列,关系数据库中该正式名为‘联结-join’;总为主键;联结是利用表间的匹配关系合并表格)
--1.交叉联结(笛卡尔积)(cross join):将表中每一行都与另一表中的每一行合并;联结行数=表A行*表B行,eg.扑克的花色和号;其他联结即在交叉联结基础上加过滤条件
--2.内联结:分别取出符合条件的行;将取出的行进行交叉联结
--from后不只一张表,对表取短别名方便使用类似关联子查询;inner join
--注意select后区别使用列名时注意是哪张表中的该列,由于sql先执行from所以select中表名需用更新后的别名
--on相当于指明沟通表间的‘桥梁’是哪一列
--eg.内联结score和student
select a.学号,a.姓名,b.课程号,b.成绩
from student as a
inner join score as b
on a.学号=b.学号
--3.左联结:将左表作为主表;取出主表全部数据,右边表中只选出与主表有相同元素的行;将取出的两类数据按交叉联结规则进行合并,缺值的地方即null
--eg.以student为左表,即写在前面的student自己位于左侧,score为待匹配表
select a.学号,a.姓名,b.课程号,b.成绩 from student as a left join score as b on a.学号=b.学号
--eg.只得出student中存在而score中不存在的学生成绩信息
--即在左联结基础上去掉公共部分(值不为null),即用where注明次表中对应匹配列的值不存在
select a.学号,a.姓名,b.课程号,b.成绩 from student as a left join score as b on a.学号 = b.学号 where b.学号 is NULL
--4.右联结:将右表作为主表,取出主表中全部值,左表中只选对应主表相同行的值
--eg.以student为次表,即写在前面的student自己位于右侧,score为主表
select a.学号,a.姓名,b.课程号,b.成绩 from student as a right join score as b on a.学号=b.学号
--eg.只得出score中存在而student中不存在的学生成绩信息
select a.学号,a.姓名,b.课程号,b.成绩 from student as a right join score as b on a.学号=b.学号 where a.学号 is NULL
--5.全联结:mysql不支持,即两表中所有行都选出,匹配不了的值记为null
--6.需要固定行数的表单或已知需要某表中全部名单时用左右联结;其他情况用内联结
--联结案例
--eg1.查询所有学生的学号、姓名、选课数、总成绩
--学号、姓名来自student,选课数总成绩都要在score总按学号分组后进行两次求和
select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩 from student as a left join score as b on a.学号 = b.学号 group by a.学号
--注意:1.count(b.列) 2.这里因为有汇总函数count、sum且对应多个学生,因此需要分组,分组依据为学号
--eg2.查询平均成绩>85的所有学生学号、姓名、平均成绩
--查出主表学号、姓名,查对应次表中算平均成绩,对于汇总函数筛选用having而不能用where
select a.学号,a.姓名,avg(b.成绩) as 平均成绩
from student as a left join score as b --联结;由于每个学生都要所以左联结
on a.学号 = b.学号 --如何联结
group by a.学号 --平均成绩要分组
having avg(b.成绩) > 85 --条件
--由于avg汇总函数对应多个学号,这里依然需要分组;否则avg(b.成绩)输出于全部学生总成绩的平均值
--eg3.查询学生的选课情况:学号、姓名、课程号、课程名称
select a.学号,a.姓名,b.课程号,c.课程名称 from student as a left join score as b left join course as c on a.学号 = b.学号,b.课程号 = c.课程号
--错误:student和course由于两表没有直接对应列,要在score基础上建立关系;且注意此处应用inner join而不是左右联结,要求查找在学生在三表内都有记录
select a.学号,a.姓名,b.课程号,c.课程名称
from student as a inner join score as b inner join course as c
on a.学号 = b.学号,b.课程号 = c.课程号
--错误:一个join要对应一个on
select a.学号,a.姓名,b.课程号,c.课程名称
from student as a inner join score as b on a.学号 = b.学号
inner join course as c on b.课程号 = c.课程号 --已联结的a,b作为整体去联结c
--case表达式(当有多种情况时,相当于条件判断函数作用)when用以判断某列数据是否符合条件,该列定在select选出列之内
--每次运行只会返回一个结果,该结果作为一新列:已满足第一个when情形,则运行then再直接到end,不查找是否符合后面的when;依次运行后面的when,不满足所有when的情况,则为else,不到end
--eg1.判断每个学生在特定课程是否及格
select 学号,课程号,成绩,
(case when 成绩 >= 60 then '及格'
when 成绩 < 60 then '不及格'
else null
end) as 是否及格
from score
--eg2.每门课程的及格人数和不及格人数
select 课程号, count(
case when 成绩 >= 60 then 1
else null
end) as 及格人数,
count(
case when 成绩 < 60 then 1
else NULL
end) as 不及格人数
from score
group by 课程号
--易错:注意按课程号分组;虽然这里用count未报错,但count用于计算行数,sum才用于对数值类型的列中数据求和;应为else 0
--注意:如果else子句不写,即默认为else null;由于when是一个依次查询,end不可不写;case表达式不只可用于select中
--eg3.统计每门课各分数段人数:<60,[60,70),[70,85),[85,100)
--要求自定义表达式的,用case when;成绩在score中,但课程名称在course中对应,且要查找course中全部数据,所以course作为主表,而score需写在前,所以使用右联结
select a.课程号,a.课程名称,
sum(
case when 成绩 < 60 then 1
end) AS 不及格人数,
sum(
case when 成绩 >= 60 and 成绩 < 70 then 1
end) AS 一般人数
from score as b right join course as a
on a.课程号 = b. 课程号
group by a.课程号
or group by a.课程号,b.课程名称
--select中列名只能是group by 子句中列名,要让查询结果同时显示号和名称,所以group by 后应加上两个分组标准,且两个分组标准一一对应一起使用不影响分组结果(如一个名称对应多个代号,则系统只有名称和代号对应的值完全相同才会归为一组)
--不能60 <= 成绩 < 70 而要 成绩 >= 60 and 成绩 < 70;对象写在比较符前,数值写在比较符后
--不同列间一定要用, 子句之间可用空格或分行
注意分组汇总的查询要加group by