在前面创建数据表的基础上，接下来我们进行多表关联查询练习

查询1 查询每个学生的总分

USE `数据查询练习`;SELECT *,语文+数学+英语+物理 AS 总分FROM students;

错误写法：

SELECT *,SUM(语文,数学,英语,物理) AS 总分FROM students;

在 SQL 中，你不能直接在 SELECT 语句的 SUM 函数中那样将多个列名作为参数直接相加。SUM 函数通常用于对某一列中的所有值进行求和，而不能用于多列的求和。

另外,我们还可以新增一列总分列（建议用这种方式）

ALTER TABLE students ADD 总分 INT;UPDATE studentsSET 总分=语文+数学+英语+物理;

查询2 查询高三2班的各科成绩

SELECT c.`班级名称`, s.`姓名`,s.`学号`,s.`语文`,s.`数学`,s.`英语`,s.`物理`FROM students sINNER JOIN classes cON c.`班级ID`=s.`班级ID`WHERE c.`班级名称`='高三2班';

查询结果

查询3 查询高三5班的学生人数

SELECT c.`班级名称`, count(s.`姓名`) AS 班级人数FROM students sINNER JOIN classes cON c.`班级ID`=s.`班级ID`WHERE c.`班级名称`='高三5班';

查询结果

查询4 查询高三4班的女生人数

SELECT c.`班级名称`, count(s.`姓名`) AS 班级女生人数FROM students sINNER JOIN classes cON c.`班级ID`=s.`班级ID`WHERE c.`班级名称`='高三4班'GROUP BY s.`性别`HAVING s.`性别`='女';

查询效果

查询5 高三3班的英语老师是谁？

SELECT 英语教师FROM classesWHERE 班级名称='高三3班';

查询6 查询高三1班的各科任老师有谁？

SELECT *FROM classesWHERE 班级名称='高三1班';

查询7 查询张三老师所任教的班级

SELECT 班级ID, 班级名称  FROM classes  WHERE 语文教师 = '张三' OR        数学教师 = '张三' OR        英语教师 = '张三' OR        物理教师 = '张三';

查询8 查询教师ID为tea08的老师，任教的班级

SELECT       c.班级ID,      c.班级名称FROM       classes c  WHERE       (c.语文教师 = (SELECT 姓名 FROM teachers WHERE 教师ID = 'tea08' AND 任教学科 = '语文')) OR      (c.数学教师 = (SELECT 姓名 FROM teachers WHERE 教师ID = 'tea08' AND 任教学科 = '数学')) OR      (c.英语教师 = (SELECT 姓名 FROM teachers WHERE 教师ID = 'tea08' AND 任教学科 = '英语')) OR      (c.物理教师 = (SELECT 姓名 FROM teachers WHERE 教师ID = 'tea08' AND 任教学科 = '物理'));

查询结果

查询9 查询教师ID为tea08的老师，任教的班级及任教的学科

SELECT       DISTINCT c.班级ID,      c.班级名称,      CASE           WHEN t.任教学科 = '语文' AND c.语文教师 = t.姓名 THEN '语文'          WHEN t.任教学科 = '数学' AND c.数学教师 = t.姓名 THEN '数学'          WHEN t.任教学科 = '英语' AND c.英语教师 = t.姓名 THEN '英语'          WHEN t.任教学科 = '物理' AND c.物理教师 = t.姓名 THEN '物理'          ELSE NULL      END AS 任教学科  FROM       classes c  CROSS JOIN       (SELECT 姓名, 任教学科 FROM teachers WHERE 教师ID = 'tea08') t  WHERE       (t.任教学科 = '语文' AND c.语文教师 = t.姓名) OR      (t.任教学科 = '数学' AND c.数学教师 = t.姓名) OR      (t.任教学科 = '英语' AND c.英语教师 = t.姓名) OR      (t.任教学科 = '物理' AND c.物理教师 = t.姓名);

注意：

（1）CROSS JOIN在这里用于生成classes表和teachers表中教师ID为tea08的行的所有可能组合。然后，我们使用WHERE子句来过滤出那些匹配的行。

（2）DISTINCT用于消除可能的重复行，因为CROSS JOIN可能会产生重复。

（3）CASE语句用于根据匹配的教师和任教学科来确定任教学科。

查询结果

查询10 查询教师ID为tea08的老师，任教的班级及任教的学科的平均分

WITH TeacherSubjects AS (      SELECT 任教学科    FROM teachers		WHERE 教师ID='tea08'),  TeacherClasses AS (      SELECT c.班级ID, c.班级名称, ts.任教学科     FROM classes c      JOIN TeacherSubjects ts ON           (c.语文教师 = (SELECT 姓名 FROM teachers WHERE 教师ID = 'tea08' AND 任教学科 = ts.任教学科) OR           c.数学教师 = (SELECT 姓名 FROM teachers WHERE 教师ID = 'tea08' AND 任教学科 = ts.任教学科) OR           c.英语教师 = (SELECT 姓名 FROM teachers WHERE 教师ID = 'tea08' AND 任教学科 = ts.任教学科) OR           c.物理教师 = (SELECT 姓名 FROM teachers WHERE 教师ID = 'tea08' AND 任教学科 = ts.任教学科))  ),  AverageScores AS (      SELECT tc.班级ID, tc.班级名称, tc.任教学科,          AVG(CASE WHEN tc.任教学科 = '语文' THEN s.语文 ELSE NULL END) AS 语文平均分,          AVG(CASE WHEN tc.任教学科 = '数学' THEN s.数学 ELSE NULL END) AS 数学平均分,          AVG(CASE WHEN tc.任教学科 = '英语' THEN s.英语 ELSE NULL END) AS 英语平均分,          AVG(CASE WHEN tc.任教学科 = '物理' THEN s.物理 ELSE NULL END) AS 物理平均分      FROM TeacherClasses tc      JOIN students s ON tc.班级ID = s.班级ID      GROUP BY tc.班级ID, tc.班级名称, tc.任教学科  )  SELECT 班级ID, 班级名称, 任教学科,         CASE 任教学科             WHEN '语文' THEN 语文平均分             WHEN '数学' THEN 数学平均分             WHEN '英语' THEN 英语平均分             WHEN '物理' THEN 物理平均分             ELSE NULL         END AS 平均分  FROM AverageScores;

查询11 查询每个学生的等级，假设总分240以下为“不及格”，总分240以上，少于280分为“及格”；总分在280分以上，低于340分为“良好”；340分以上的为优秀。

SELECT *,CASE 	WHEN 总分<240 THEN	'不及格'	WHEN 总分>=240 AND 总分<280 THEN '及格'	WHEN 总分>=280 AND 总分<340 THEN '良好'	WHEN 总分>=340 THEN '优秀'	ELSE NULLEND AS 等级FROM students;

查询12 查询每个等级的人数

SELECT      CASE           WHEN 总分 < 240 THEN '不及格'          WHEN 总分 >= 240 AND 总分 < 280 THEN '及格'          WHEN 总分 >= 280 AND 总分 < 340 THEN '良好'          WHEN 总分 >= 340 THEN '优秀'      END AS 等级,      COUNT(*) AS 学生数量  FROM      students  GROUP BY      CASE           WHEN 总分 < 240 THEN '不及格'          WHEN 总分 >= 240 AND 总分 < 280 THEN '及格'          WHEN 总分 >= 280 AND 总分 < 340 THEN '良好'          WHEN 总分 >= 340 THEN '优秀'      END;

查询结果