sql 相加_SQL:多表查询

一，表的加法

在原有school数据库里再创建一张跟course结构相同的表course1，可以【右击course】,【点复制表】-【选结构和顺序】，然后把course1里按照要求修改数据

完成操作后发现course和course1 结构是一样的，列和列的数据类型是一致的，不同的是红色框的数据

练习：将course和course1相加，用union将两张表的查询语句结合一起

文氏图：加法（Union）

图一

表的加法会把表里重复的数据删除，只保留一个（图一）;若想要保留2张表里重复的行，在Union后加all 即可（图二）

图二

二，表的连接

School数据库里4张表

学生表和成绩表之间有什么关系呢？

student和score这两张表通过学号关联起来，学号0001的成绩，可以通过成绩表里查学号0001的行，一共发现3行，对应是找到了学号0001三门课程的成绩。

School数据库里四张表之间的关系

4张表联结关系图

• 交叉联结cross join：(将一个表的每一行 与 另一表中的每一行 合并在一起）

表1 三行数据；表2 两行数据 交叉联结=3*2 六行数据

生活中典型的交叉联结 ：扑克牌

13张牌（A,1,2,3,4,5,6,7,8,9,10,J,Q,K）和 四种花色（♠,）交叉联结 13*4=52张牌

【注】：交叉联结实际业务用的比较少(耗时成本;没有实际价值)，交叉联结是所有联结的基础

• 内联结inner join：（查找出同时存在于两张表的数据）

• 左联结left join: (将左侧的表作为主表,将左表数据全部取出,右边表只选出和左边表相同列名的行)

• 右联结right join:(将右侧表的数据全部取出，将左侧表中与右侧表相同列名的行取出）

• 全联结 full join (查询结果返回左表和右表中的所有行。当某行和另一表中有匹配的时，两行进行合并；若某行跟另一表中没有匹配时，另一表中对应的值用空值来填充）

【注】：MySQL是不支持全联结，理解概念即可

所有SQL联结：

复习SQL运行顺序：

• 先运行子查询
• 每个查询语句中的运行顺序
  • 1，先运行篮框的子句
  • 2，select子句
  • 3，最后运行红框的子句

三，联结应用案列

如何用SQL解决业务问题

1. 翻译成大白话
2. 写出分析思路
3. 写出对应的SQL语句

问题1：查询所有学生的学号、姓名、选课数、总成绩？

1：翻译为大白话

• 学号、姓名: 学生表student
• 选课数: 每个学生的选课数:成绩表 (按学号分组，对课程号计数count）
• 总成绩：每个学生的总成绩:成绩表（按学号分组，对组里成绩求和sum）

2：写出分析思路

• select 查询结果 (学号、姓名、选课数、总成绩)
• from 从哪张表查找数据 （ 学生表、成绩表）【2张表 用什么联结，哪种联结】
• where查询条件（无）
• group by 分组（学号)
  • 选课数: 每个学生的选课数:成绩表 (按学号分组，对课程号计数count）
  • 总成绩：每个学生的总成绩:成绩表（按学号分组，对组里成绩求和sum）
• having 对分组结果指定条件（无）
• order by 对查询结果排序（无）
• limit 从查询结果中取出指定行（无）

3:写出SQL语句

问题2：查询平均成绩大于85的所有学生的 学号、姓名、平均成绩？
1：翻译为大白话

• 查询所有学生的学号，姓名，平均成绩（学号、姓名 在 学生表student; 平均成绩 在 成绩表score [ 按学号分组，avg(成绩) ]）
• 平均成绩>85 （分组后）

2：写出分析思路

• select 查询结果 (学号，姓名，平均成绩)
• from 从哪张表查找数据 （ 学生表、成绩表）【2张表 用什么联结，哪种联结】
• where查询条件（无）
• group by 分组（学号)
  • 平均成绩: 按学号分组，计算avg(成绩）
• having 对分组结果指定条件（avg(成绩)>85）
• order by 对查询结果排序（无）
• limit 从查询结果中取出指定行（无）

3:写出SQL语句

问题3：查询所有学生的 选课情况：学号，姓名，课程号，课程名称?

1：翻译为大白话

• 学号、姓名: 学生表student
• 课程号，课程名称：课程表course

【注】：学生表student 和 课程表course 是需要通过 中间的成绩表建立关系(3张表联结)

2：写出分析思路

• select 查询结果 (学号，姓名，课程号，课程名称)
• from 从哪张表查找数据 (学生表,成绩表,课程表）【3张表 用什么联结，哪种联结】
• where查询条件（无）
• group by 分组（无)
• having 对分组结果指定条件（无）
• order by 对查询结果排序（无）
• limit 从查询结果中取出指定行（无）

3:写出SQL语句

四，Case表达式

问题1：查询成绩表里的成绩是否及格？

问题2：查询每门课程的 及格人数 和 不及格人数？

分析思路：

• 查询 每门课程 的 人数 （以课程号分组）
• 及格人数 和 不及格人数 （用sum函数和case表达式）

case表达式 注意事项：

• else子句可以省略不写，默认空值，为了更好的SQL书写习惯，建议不要省略
• end 是不可以省略不写的
• case表达式放在select子句中对查询结果进行判断，其实case表达式可以写sql任意子句里

case表达式有何作用，何时使用呢：当有多重情况需要判断时，或者需要自定义分组

问题3：使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩,分别统计各分段人数：课程 号和课程名称

• 查询出各分段人数：成绩表score (设及 自定义分组，用case表达式)
• 课程号，课程名称：课程表course

SQLZOO (Join)练习：