MYSQL数据库(连接查询和子查询)

Question

MYSQL两个问题:
1.连接查询(图一)
2.子查询(图二)
(图一如下)

(图二如下)

Answer 1

引自强大而免费的微信小程序：皆我百晓生。如有帮助，还望采纳。

---

第一组问题，连接查询：

阶段一:基本查询

1. 查询学员信息，要求显示学生姓名，年龄，性别，入学时间

   SELECT 学生姓名, 年龄, 性别, 入学时间 FROM 学员表;
   

2. 查询班主任信息，要求显示班主任姓名，电话号码

   SELECT 班主任姓名, 电话号码 FROM 班主任表;
   

3. 查询班级表，要求显示班级编号，班级名，年级

   SELECT 班级编号, 班级名, 年级 FROM 班级表;
   

4. 查询学员的人数

   SELECT COUNT(*) AS 学员人数 FROM 学员表;
   

5. 查询女同学的平均年龄和最大年龄

   SELECT AVG(年龄) AS 平均年龄, MAX(年龄) AS 最大年龄 FROM 学员表 WHERE 性别 = '女';
   

6. 查询各学员考试的次数

   SELECT 学生姓名, COUNT(*) AS 考试次数 FROM 考试记录表 GROUP BY 学生姓名;
   

阶段二：多表查询

1. 查询参加了每个学员的姓名，性别，年龄，入学时间，考试编号，考试科目，考试成绩

   SELECT 学生表.学生姓名, 学生表.性别, 学生表.年龄, 学生表.入学时间, 考试记录表.考试编号, 考试记录表.考试科目, 考试记录表.考试成绩
   FROM 学生表
   JOIN 考试记录表 ON 学生表.学生ID = 考试记录表.学生ID;
   

2. 查询每个班的学员信息及班级号

   SELECT 学生表.学生姓名, 学生表.性别, 学生表.年龄, 学生表.入学时间, 班级表.班级编号
   FROM 学生表
   JOIN 班级表 ON 学生表.班级编号 = 班级表.班级编号;
   

3. 查询07034班的学员姓名，性别，年龄，入学时间，班级编号

   SELECT 学生姓名, 性别, 年龄, 入学时间, 班级编号 FROM 学生表 WHERE 班级编号 = '07034';
   

4. 查询每个班主任所带班级信息，要求显示班主任姓名，电话号码，班级编号

   SELECT 班主任表.班主任姓名, 班主任表.电话号码, 班级表.班级编号
   FROM 班主任表
   JOIN 班级表 ON 班主任表.班主任ID = 班级表.班主任ID;
   

5. 查询每个班的信息，要求显示班主任性别，学生姓名，年龄，性别(三表连接)

   SELECT 班主任表.性别, 学生表.学生姓名, 学生表.年龄, 学生表.性别
   FROM 班级表
   JOIN 班主任表 ON 班级表.班主任ID = 班主任表.班主任ID
   JOIN 学生表 ON 班级表.班级编号 = 学生表.班级编号;
   

阶段三:多表查询，聚合函数，分组查询

1. 查询每个学员考试的总成绩

   SELECT 学生表.学生姓名, SUM(考试记录表.考试成绩) AS 总成绩
   FROM 学生表
   JOIN 考试记录表 ON 学生表.学生ID = 考试记录表.学生ID
   GROUP BY 学生表.学生姓名;
   

2. 查询每个班级学员的最小年龄

   SELECT 班级表.班级编号, MIN(学生表.年龄) AS 最小年龄
   FROM 班级表
   JOIN 学生表 ON 班级表.班级编号 = 学生表.班级编号
   GROUP BY 班级表.班级编号;
   

3. 查询考试不及格的人数

   SELECT COUNT(*) AS 不及格人数
   FROM 考试记录表
   WHERE 考试成绩 < 60;
   

4. 查询火云邪神参加了几门考试

   SELECT 学生表.学生姓名, COUNT(*) AS 考试门数
   FROM 学生表
   JOIN 考试记录表 ON 学生表.学生ID = 考试记录表.学生ID
   WHERE 学生表.学生姓名 = '火云邪神'
   GROUP BY 学生表.学生姓名;
   

5. 查询每门功课的平均分，要求显示平均分在80分以上的(包括80分)

   SELECT 考试记录表.考试科目, AVG(考试记录表.考试成绩) AS 平均分
   FROM 考试记录表
   GROUP BY 考试记录表.考试科目
   HAVING 平均分 >= 80;
   

6. 查询所有学员的信息，要求显示:姓名学号，考试科目，考试成绩，并按照考试成绩降序和学号排序升序排序(提示:每个学员的信息都要求显示，不论是否有参加考试)

   SELECT 学生表.学生姓名, 学生表.学生ID, 考试记录表.考试科目, 考试记录表.考试成绩
   FROM 学生表
   LEFT JOIN 考试记录表 ON 学生表.学生ID = 考试记录表.学生ID
   ORDER BY 考试记录表.考试成绩 DESC, 学生表.学生ID ASC;
   

7. 按入学月份分组，查询学员的平均成绩

   SELECT MONTH(学生表.入学时间) AS 入学月份, AVG(考试记录表.考试成绩) AS 平均成绩
   FROM 学生表
   JOIN 考试记录表 ON 学生表.学生ID = 考试记录表.学生ID
   GROUP BY 入学月份;
   

8. 查询每个班主任所带学员信息，要求显示:班主任姓名，班主任联系电话，班级名称，学员姓名，学员学号.(提示: 3表联接)

   SELECT 班主任表.班主任姓名, 班主任表.联系电话, 班级表.班级名称, 学生表.学生姓名, 学生表.学生ID
   FROM 班主任表
   JOIN 班级表 ON 班主任表.班主任ID = 班级表.班主任ID
   JOIN 学生表 ON 班级表.班级编号 = 学生表.班级编号;
   

9. 按班级名称分组，查询每个班级的平均成绩

   SELECT 班级表.班级名称, AVG(考试记录表.考试成绩) AS 平均成绩
   FROM 班级表
   JOIN 学生表 ON 班级表.班级编号 = 学生表.班级编号
   JOIN 考试记录表 ON 学生表.学生ID = 考试记录表.学生ID
   GROUP BY 班级表.班级名称;
   

10. 查询所有3月份入学男学员，按所在班级分组，要求显示每个班级的考试平均分，并按从高到低排列

    SELECT 班级表.班级名称, AVG(考试记录表.考试成绩) AS 平均分
    FROM 学生表
    JOIN 班级表 ON 学生表.班级编号 = 班级表.班级编号
    JOIN 考试记录表 ON 学生表.学生ID = 考试记录表.学生ID
    WHERE MONTH(学生表.入学时间) = 3 AND 学生表.性别 = '男'
    GROUP BY 班级表.班级名称
    ORDER BY 平均分 DESC;
    

11. 按班主任姓名分组，查询所带班级的总成绩分(假定每个班主任只带一个班级)(提示：4表联接)

    SELECT 班主任表.班主任姓名, SUM(考试记录表.考试成绩) AS 总成绩
    FROM 班主任表
    JOIN 班级表 ON 班主任表.班主任ID = 班级表.班主任ID
    JOIN 学生表 ON 班级表.班级编号 = 学生表.班级编号
    JOIN 考试记录表 ON 学生表.学生ID = 考试记录表.学生ID
    GROUP BY 班主任表.班主任姓名;
    

第二组问题，子查询：

1. 查询与学员'火云邪神'属于同一班的学员信息

   SELECT *
   FROM 学员表
   WHERE 班级编号 IN (SELECT 班级编号 FROM 学员表 WHERE 学生姓名 = '火云邪神');
   

2. 查询'07038'班的学员信息

   SELECT *
   FROM 学员表
   WHERE 班级编号 = '07038';
   

3. 查询东方不败的考试成绩信息

   SELECT 考试记录表.*
   FROM 学员表
   JOIN 考试记录表 ON 学员表.学生ID = 考试记录表.学生ID
   WHERE 学员表.学生姓名 = '东方不败';
   

4. 查询与学员'小李飞车'属于同一班且年龄相同的学员信息

   SELECT *
   FROM 学员表
   WHERE 班级编号 IN (SELECT 班级编号 FROM 学员表 WHERE 学生姓名 = '小李飞车')
   AND 年龄 = (SELECT 年龄 FROM 学员表 WHERE 学生姓名 = '小李飞车');
   

5. 使用IN查询参加了考试的学员

   SELECT *
   FROM 学员表
   WHERE 学生ID IN (SELECT 学生ID FROM 考试记录表);
   

6. 使用EXISTS查询参加了考试的学员

   SELECT *
   FROM 学员表
   WHERE EXISTS (SELECT * FROM 考试记录表 WHERE 学员表.学生ID = 考试记录表.学生ID);
   

7. 查询Java考试及格 (>=60分)的学员详细信息

   SELECT *
   FROM 学员表
   WHERE 学生ID IN (SELECT 学生ID FROM 考试记录表 WHERE 考试科目 = 'Java' AND 考试成绩 >= 60);
   

8. 查询男学员中大于男性平均年龄的学员

   SELECT *
   FROM 学员表
   WHERE 性别 = '男'
   AND 年龄 > (SELECT AVG(年龄) FROM 学员表 WHERE 性别 = '男');
   

9. 查询秦天柱老师所带班级的学员信息

   SELECT *
   FROM 学员表
   WHERE 班级编号 IN (SELECT 班级编号 FROM 班级表 WHERE 班主任ID IN (SELECT 班主任ID FROM 班主任表 WHERE 班主任姓名 = '秦天柱'));
   

10. 查询Java考试成绩最高的学员姓名(需要考虑java成绩最高分可能有多人)

    SELECT 学生姓名 FROM 学员表 WHERE 学号 IN (SELECT 学号 FROM 考试表 WHERE 考试科目 = 'Java' AND 考试成绩 = (SELECT MAX(考试成绩) FROM 考试表 WHERE 考试科目 = 'Java'));
    

11. 查询每个班级中大于班级平均年龄的学员

    SELECT 班级编号, 学生姓名 FROM 学员表 WHERE 年龄 > (SELECT AVG(年龄) FROM 学员表 AS t WHERE 学员表.班级编号 = t.班级编号) ORDER BY 班级编号;