mysql 多个数相加_mysql 多表查询

--表格相加（类似集合相加，除非加上关键词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