mysql多表查询计算平均_MYSQL多表查询⼀. 课堂练习以及知识点
表的加法
含义：将两个表按⾏合并在⼀起，⽤union将两个表中的结果合并在⼀起
案例
select 课程号，课程名称
from course
union
select 课程号，课程名称
from course1
⽤union all可以保留重复⾏
select 课程号，课程名称
from course
union all
select 课程号，课程名称
from course1
表的联结
联结(join)-表与表之间通过列产⽣对应关系，联结是将表与表之间关系合并在⼀起的操作
交叉联结表1的第⼀⾏和表2的两⾏合并在⼀起，形成两⾏数据，交叉结果是两个表中⾏数的乘积；
交叉连接在实际业务中⽤的⽐较少，因为结果太多，花费⼤量的成本运算；
内联结含义：同时存在两张表的数据，即交集
⽤inner join联结
关键点from ⼦句中，同时使⽤两张表，需要给表取别名
inner join 表⽰内连接
on 表⽰哪个表中的哪个列匹配，表⽰匹配关系
案例
#查询学⽣的姓名，学号和课程号
SELECT s.姓名,s.学号,sc.课程号
FROM student AS s
INNER JOIN score AS sc
ON s.`学号`=sc.`学号`;
左联结/右联结(以下内容以左联结为例)
⽤left join联结，left左边的表为主表，右边的为从表(right join右边的是主表，左边的是从表)
案例
SELECT s.姓名,s.学号,sc.`课程号`
FROM student AS s
LEFT JOIN score AS sc
ON s.`学号`=sc.`学号`;
SELECT s.姓名,s.学号,sc.`课程号`
FROM student AS s
LEFT JOIN score AS sc
ON s.`学号`=sc.`学号`
WHERE sc.`课程号` IS NULL;
SELECT s.姓名,s.学号,sc.`课程号`
FROM student AS s
RIGHT JOIN score AS sc
ON s.`学号`=sc.`学号`;
全联结全联结⽤full join 关键词联结；
得到两个表中的所有⾏，若匹配不到则返回空值，合并在⼀起；
(mysql中不⽀持该联结，暂不详细说明)
总结
当实际业务中需要⽣成固定数据的表单或者需要指定数据，可以⽤左/右联结，其他情况⽤内联结，取两个表的公共部分。应⽤案例
⽤sql解决问题的步骤：翻译题⽬，分析思路，sql语句
案例1：查询所有学⽣的学号，姓名，选课数，总成绩
翻译题⽬：学号，姓名在student表
选课数(在score表中，计算count(课程号)，对每个学号分组)
总成绩(在score表中，计算sum(成绩)，对每个学号分组)
分析思路FROM学⽣表，成绩表，因为要查到所有学⽣的信息，因此应该让student做主表，通过学号⽤左连接
没有where查询条件
group by：对学号分组，计算count(课程号)；对学号分组，计算sum(成绩)
having没有分组结果的筛选条件
sql中union多表合并
order by没有排序
limit没有指定⾏
sql语句
SELECT a.学号,a.姓名,COUNT(b.`课程号`) AS 选课数量,SUM(b.`成绩`) AS 总成绩
FROM student AS a
LEFT JOIN score AS b
ON a.`学号`=b.`学号`
GROUP BY a.`学号`;
因为要查询所有学⽣信息，因此应该按主表即a表的学号分组
案例2 查询平均成绩⼤于85的所有学⽣的学号，姓名，平均成绩
翻译题⽬以及思路FROM学⽣表，成绩表，因为查询所有学⽣的信息，则学⽣表为主表，通过学号左连接
没有where条件
group by：在成绩表中对学号分组，计算每个学⽣的平均成绩
having: 对平均成绩⽐较，筛选出⼤于85分的
order by：没有排序
limit：没有指定⾏
sql语句
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;
案例3 查询所有学⽣的选课情况：学号，姓名，课程号，课程名称
翻译题⽬在学⽣表中学号，姓名
在课表中课程号，课程名称
思路
from 学⽣表和课程表，但是两个表没有关系条件联结，所以需要通过成绩表进⾏联结，学⽣表和成绩表通过学号联结，课程表和成绩表通过课程号联结
sql语句
SELECT s.学号,s.姓名,c.课程号,c.课程名称
FROM student AS s
LEFT JOIN score AS sc
ON s.`学号`=sc.`学号`
LEFT JOIN course AS c
ON c.`课程号`=sc.`课程号`;
case表达式
case when 判断表达式 then 表达式
when 判断表达式 then 表达式
...
else 表达式
end
案例1： 在查询结果中显⽰成绩是否及格
SELECT 学号，课程号，成绩,(
CASE WHEN 成绩<=60 THEN '不及格'
WHEN 成绩>60 AND 成绩<=80 THEN '中等'
WHEN 成绩>80 THEN '优秀' ELSE '未知' END) AS 是否及格
FROM score;
案例2：查出每门课程的及格⼈数和不及格⼈数
思路：先判断出每门课程及格或者不及格，及格为1，不及格为0，然后对课程号分组统计sum
sql语句：
SELECT 课程号,SUM(
CASE WHEN 成绩>=60 THEN 1 ELSE 0 END) AS 及格⼈数,
SUM(
CASE WHEN 成绩<60 THEN 1 ELSE 0 END) AS 不及格⼈数
FROM score
GROUP BY 课程号;
案例3：使⽤分段85-100, 70-85, 60-70，<60，来统计各科成绩，分别统计：各个分数段⼈数，课程号，课程名称
翻译题⽬根据分段，对成绩进⾏判断
需要对不同分数段分组，计算每个分数段⼈数
⽤课程表查出课程名称，课程号
思路FROM 因为需要成绩则⽤成绩表，需要课程名称则需要课程表，因为统计的是各个科⽬，则课程表做主表，通过课程号左连接，课程表放在左边，如果⽤右联结，course放在右边
没有where条件
group by：对每个分数段进⾏分组，统计总⼈数
having：没有
order by：没有排序
limit：没有指定⾏
sql语句
SELECT s.`课程号`,c.`课程名称`, SUM(
CASE WHEN 成绩<60 THEN 1 ELSE 0 END) AS '[<60]',
SUM(CASE WHEN 成绩>=60 AND 成绩<70 THEN 1 ELSE 0 END) AS '[60-70]',
SUM(CASE WHEN 成绩>=70 AND 成绩<85 THEN 1 ELSE 0 END) AS '[70-85]',
SUM(CASE WHEN 成绩>=85 AND 成绩<=100 THEN 1 ELSE 0 END) AS '[85-100]'
FROM course AS c
LEFT JOIN score AS s ON s.`课程号`=c.`课程号`
GROUP BY s.`课程号`,c.`课程名称`;
注意：select⼦句中的被分组的列应该是group by⼦句中的，因此都要放在group by后边；group by后边⽤多个列分组时，这⼏个列的值全部相同才算⼀组，⽐如‘01’-语⽂ 是⼀组，‘01’-数学 是另⼀组，该案例中课程号和课程名称是⼀对⼀的关系，多列分组不影响结果。
注意事项else可以不写，默认为空值，但是为了⽅便理解，建议书写养成良好习惯
最后的end不能省略
case表达式可以写在任何⼦句中
什么情况下使⽤case表达式：当有多种情况需要判断，则需要CASE表达式
⼆. SQLzoo练习
#1列出 賽事編號matchid和球員名player，該球員代表德國隊Germany⼊球的。要出德國隊球員，要檢查:teamid = 'GER'
SELECT matchid,player
FROM goal
WHERE teamid='GER';
#2由以上查詢，你可⾒Lars Bender's 於賽事 1012⼊球。.現在我們想知道此賽事的對賽隊伍是哪⼀隊。留意在 goal 表格中的欄位matchid ，是對應表格game的欄位id。我們可以在表格 game中出賽事1012的資料。只顯⽰賽事1012的 id, stadium, team1,
team2
SELECT id,stadium,team1,team2
FROM game
WHERE id='1012';
#3顯⽰每⼀個德國⼊球的球員名，隊伍名，場館和⽇期。
SELECT gl.amid,gm.stadium,gm.mdate
FROM game as gm
INNER JOIN goal as gl
ON gm.id=gl.matchid
amid='GER';
#4列出球員名字叫Mario (player LIKE 'Mario%')有⼊球的 隊伍1 team1, 隊伍2 team2 和 球員名 player
am2,gl.player
from game as gm
inner join goal as gl
on gm.id=gl.matchid
where gl.player like 'Mario%';
#5列出每場球賽中⾸10分鐘gtime<=10有⼊球的球員 player, 隊伍teamid, 教練coach, ⼊球時間gtime
SELECT gl.player, gl.teamid, et.ime

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。