MySQL（学生表、教师表、课程表、成绩表）多表查询

江上荷人

已于 2024-05-17 21:38:41 修改

阅读量1.3k

点赞数 22

文章标签： mysql 数据库

于 2024-05-17 11:18:17 首次发布

本文链接：https://blog.csdn.net/m0_66353585/article/details/139000065

版权

表架构

student(s_id,s_name,s_age,s_sex) 学生表
course(c_id,c_name,t_id) 课程表
score(s_id,c_id,s_score) 成绩表
teacher(t_id,t_name) 教师表

数据准备

DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `c_id` int(11) NOT NULL,
  `c_name` varchar(30) DEFAULT NULL,
  `t_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('3001', '语文', '4');
INSERT INTO `course` VALUES ('3002', '数学', '2');
INSERT INTO `course` VALUES ('3003', '英语', '1');
INSERT INTO `course` VALUES ('3004', '物理', '3');

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `s_id` int(11) NOT NULL,
  `c_id` int(11) NOT NULL,
  `s_score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `score` VALUES ('101', '3001', '90');
INSERT INTO `score` VALUES ('102', '3001', '85');
INSERT INTO `score` VALUES ('103', '3001', '76');
INSERT INTO `score` VALUES ('105', '3001', '87');
INSERT INTO `score` VALUES ('106', '3001', '66');
INSERT INTO `score` VALUES ('108', '3001', '96');
INSERT INTO `score` VALUES ('101', '3002', '92');
INSERT INTO `score` VALUES ('102', '3002', '81');
INSERT INTO `score` VALUES ('103', '3002', '93');
INSERT INTO `score` VALUES ('104', '3002', '73');
INSERT INTO `score` VALUES ('105', '3002', '65');
INSERT INTO `score` VALUES ('108', '3002', '96');
INSERT INTO `score` VALUES ('101', '3003', '96');
INSERT INTO `score` VALUES ('102', '3003', '85');
INSERT INTO `score` VALUES ('103', '3003', '76');
INSERT INTO `score` VALUES ('104', '3003', '63');
INSERT INTO `score` VALUES ('105', '3003', '59');
INSERT INTO `score` VALUES ('106', '3003', '56');
INSERT INTO `score` VALUES ('107', '3003', '91');
INSERT INTO `score` VALUES ('108', '3003', '86');
INSERT INTO `score` VALUES ('101', '3004', '100');
INSERT INTO `score` VALUES ('102', '3004', '83');
INSERT INTO `score` VALUES ('103', '3004', '75');
INSERT INTO `score` VALUES ('104', '3004', '69');
INSERT INTO `score` VALUES ('105', '3004', '50');
INSERT INTO `score` VALUES ('106', '3004', '52');
INSERT INTO `score` VALUES ('107', '3004', '87');
INSERT INTO `score` VALUES ('108', '3004', '78');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `s_id` int(11) NOT NULL,
  `s_name` varchar(30) DEFAULT NULL,
  `s_age` int(11) DEFAULT NULL,
  `s_sex` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('101', '龙大', '18', '男');
INSERT INTO `student` VALUES ('102', '熊二', '19', '男');
INSERT INTO `student` VALUES ('103', '张三', '18', '男');
INSERT INTO `student` VALUES ('104', '李四', '19', '女');
INSERT INTO `student` VALUES ('105', '王五', '20', '男');
INSERT INTO `student` VALUES ('106', '李华', '19', '男');
INSERT INTO `student` VALUES ('107', '李红', '19', '女');
INSERT INTO `student` VALUES ('108', '李明', '20', '男');
INSERT INTO `student` VALUES ('109', '贝贝', '19', '女');
INSERT INTO `student` VALUES ('110', '娜娜', '20', '女');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `t_id` int(11) NOT NULL,
  `t_name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '叶平');
INSERT INTO `teacher` VALUES ('2', '李龙');
INSERT INTO `teacher` VALUES ('3', '李逍遥');
INSERT INTO `teacher` VALUES ('4', '朱钊');

经典问题

查询“3001”课程比“3002”课程成绩高的所有学生的学号与分数；

SELECT sc1.s_id,sc1.s_score '课程1分数', sc2.s_score '课程2分数'
FROM score sc1
JOIN score sc2  on sc1.s_id = sc2.s_id
WHERE sc1.c_id = 3001 and sc2.c_id = 3002 and sc1.s_score > sc2.s_score;

查询平均成绩大于60分的同学的学号和平均成绩；

SELECT s_id, AVG(s_score) 
FROM score
GROUP BY s_id
HAVING AVG(s_score) > 60;

查询所有同学的学号、姓名、选课数、总成绩

SELECT s.s_id, s.s_name, COUNT(sc.c_id) as '选课数', 
	   IFNULL(SUM(sc.s_score),0) as '总成绩'
FROM student s
LEFT JOIN score sc on s.s_id = sc.s_id
GROUP BY s.s_id,s_name

查询姓“李”的老师的个数；

SELECT COUNT(t_id) 
FROM teacher
WHERE t_name like '李%'

查询学过“叶平”老师课的同学的学号、姓名

SELECT s.s_id AS "学号", s.s_name AS "姓名"
FROM student s
JOIN score sc on   s.s_id=sc.s_id
JOIN course c on   c.c_id=sc.c_id
JOIN teacher t on  t.t_id=c.t_id AND t.t_name= "叶平"

查询没有学过“叶平”老师课的同学的学号、姓名

第五题和第六题是互斥的，对于第六题，不能简单的把第五题的判断条件改为 t.t_name <> “叶平”,

这个查询会返回所有学过其他老师课程的学生，即使他们也学过“叶平”老师的课。

所以要用互斥思想，学过"叶平"老师课的 id 直接排除

SELECT s_id AS "学号", s_name AS "姓名"
FROM student 
WHERE s_id NOT IN
(SELECT s.s_id 
FROM student s
JOIN score sc on   s.s_id=sc.s_id
JOIN course c on   c.c_id=sc.c_id
JOIN teacher t on  t.t_id=c.t_id AND t.t_name = '叶平')

查询学过“3001”并且也学过编号“3002”课程的同学的学号、姓名

 SELECT s.s_id AS "学号", s.s_name AS "姓名"
FROM student s
JOIN score sc1 on s.s_id = sc1.s_id and sc1.c_id = 3001
JOIN score sc2 on s.s_id = sc2.s_id and sc2.c_id = 3002

查询学过“叶平”老师所教的所有课的同学的学号、姓名；

首先找出所有选了“叶平”老师课的学生，然后分组并计算他们选了多少门“叶平”老师的课。如果这个数量等于“叶平”老师教的课程总数，那么这个学生就是我们要找的学生。

SELECT s.s_id AS "学号", s.s_name AS "姓名"
FROM student s
JOIN score sc ON s.s_id = sc.s_id
JOIN course c ON c.c_id = sc.c_id
JOIN teacher t ON t.t_id = c.t_id
WHERE t.t_name = "叶平"
GROUP BY s.s_id, s.s_name
HAVING COUNT(DISTINCT c.c_id) = (
    SELECT COUNT(DISTINCT c.c_id)
    FROM course c
    JOIN teacher t ON t.t_id = c.t_id
    WHERE t.t_name = "叶平"
);

查询课程成绩小于60分的同学的学号、姓名

SELECT DISTINCT s.s_id AS "学号", s.s_name AS "姓名"
FROM student s
JOIN score sc ON s.s_id = sc.s_id
WHERE sc.s_score < 60;

查询没有学全所有课的同学的学号、姓名；

SELECT s.s_id as '学号',s.s_name as '姓名'
FROM student s
LEFT JOIN score sc
on s.s_id = sc.s_id
GROUP BY s.s_id
HAVING COUNT(DISTINCT sc.c_id) < (SELECT COUNT(DISTINCT c_id) FROM course)

查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SELECT c_id as '课程id',MAX(s_score) as '最高分',MIN(s_score) as '最低分'
FROM score
GROUP BY c_id

按各科平均成绩从低到高和及格率的百分数从高到低顺序

SELECT sc.c_id as '课程id', c.c_name as '课程名称', AVG(sc.s_score) as '平均成绩',
SUM(CASE WHEN sc.s_score > 60 THEN 1 ELSE 0 END ) / count(sc.s_score)  * 100 as '及格率'
FROM score sc
JOIN course c  on sc.c_id = c.c_id
GROUP BY sc.c_id
ORDER BY AVG(sc.s_score), 
SUM(CASE WHEN sc.s_score > 60 THEN 1 ELSE 0 END ) / count(sc.s_score)  * 100 DESC;

查询所有学生的所有科目的成绩单（学号、姓名、语文、数学、英语、物理、平均分、总分（按照总分由高到低排序））

SELECT s.s_id as '学号',s.s_name as '姓名',
SUM(CASE WHEN c.c_name ='语文' THEN sc.s_score ELSE 0 END) as '语文',
SUM(CASE WHEN c.c_name ='数学' THEN sc.s_score ELSE 0 END) as '数学',
SUM(CASE WHEN c.c_name ='英语' THEN sc.s_score ELSE 0 END) as '英语',
SUM(CASE WHEN c.c_name ='物理' THEN sc.s_score ELSE 0 END) as '物理',
IFNULL(AVG(sc.s_score),0) as '平均成绩',
IFNULL (SUM(sc.s_score),0) as '总成绩'
FROM student s
LEFT JOIN score sc on s.s_id = sc.s_id
LEFT JOIN course c on sc.c_id = c.c_id
GROUP BY s.s_id,s.s_name
ORDER BY SUM(IFNULL(sc.s_score,0)) DESC;

查询总分在200-300（包含200和第300）之间的学生所有成绩单信息

SELECT s.s_id as '学号',s.s_name as '姓名',
SUM(CASE WHEN c.c_name ='语文' THEN sc.s_score ELSE 0 END) as '语文',
SUM(CASE WHEN c.c_name ='数学' THEN sc.s_score ELSE 0 END) as '数学',
SUM(CASE WHEN c.c_name ='英语' THEN sc.s_score ELSE 0 END) as '英语',
SUM(CASE WHEN c.c_name ='物理' THEN sc.s_score ELSE 0 END) as '物理',
IFNULL(AVG(sc.s_score),0) as '平均成绩',
IFNULL (SUM(sc.s_score),0) as '总成绩'
FROM student s
LEFT JOIN score sc on s.s_id = sc.s_id
LEFT JOIN course c on sc.c_id = c.c_id
GROUP BY s.s_id,s.s_name
HAVING IFNULL(SUM(sc.s_score),0) BETWEEN 200 AND 300
ORDER BY SUM(IFNULL(sc.s_score,0)) DESC;

查询总分排名在前四名的学生所有成绩单信息

SELECT s.s_id as '学号',s.s_name as '姓名',
SUM(CASE WHEN c.c_name ='语文' THEN sc.s_score ELSE 0 END) as '语文',
SUM(CASE WHEN c.c_name ='数学' THEN sc.s_score ELSE 0 END) as '数学',
SUM(CASE WHEN c.c_name ='英语' THEN sc.s_score ELSE 0 END) as '英语',
SUM(CASE WHEN c.c_name ='物理' THEN sc.s_score ELSE 0 END) as '物理',
IFNULL(AVG(sc.s_score),0) as '平均成绩',
IFNULL (SUM(sc.s_score),0) as '总成绩'
FROM student s
LEFT JOIN score sc on s.s_id = sc.s_id
LEFT JOIN course c on sc.c_id = c.c_id
GROUP BY s.s_id,s.s_name
ORDER BY SUM(IFNULL(sc.s_score,0)) DESC
LIMIT 0,4

查询总分排名在第二名到第四名的学生所有成绩单信息

SELECT s.s_id as '学号',s.s_name as '姓名',
SUM(CASE WHEN c.c_name ='语文' THEN sc.s_score ELSE 0 END) as '语文',
SUM(CASE WHEN c.c_name ='数学' THEN sc.s_score ELSE 0 END) as '数学',
SUM(CASE WHEN c.c_name ='英语' THEN sc.s_score ELSE 0 END) as '英语',
SUM(CASE WHEN c.c_name ='物理' THEN sc.s_score ELSE 0 END) as '物理',
IFNULL(AVG(sc.s_score),0) as '平均成绩',
IFNULL (SUM(sc.s_score),0) as '总成绩'
FROM student s
LEFT JOIN score sc on s.s_id = sc.s_id
LEFT JOIN course c on sc.c_id = c.c_id
GROUP BY s.s_id,s.s_name
ORDER BY SUM(IFNULL(sc.s_score,0)) DESC
LIMIT 1,3

查询学生平均成绩及其名次

难点在于学生的名次；首先计算每个学生的平均成绩，然后对于每个学生，计算有多少学生的平均成绩高于他，这个数字加一就是学生的排名。

SELECT s.s_id, s.s_name, IFNULL(AVG(sc.s_score),0) AS average_score,
    (SELECT COUNT(*) + 1 FROM 
        (SELECT IFNULL(AVG(s_score),0) as avg_score FROM score GROUP BY s_id) t 
     WHERE t.avg_score > IFNULL(AVG(s_score),0)) AS 'rank'
FROM 
    student s
LEFT JOIN 
    score sc ON s.s_id = sc.s_id
GROUP BY 
    s.s_id
ORDER BY 
    average_score DESC;

# 第二种:使用窗口函数RANK()
SELECT 
    s.student_name, 
    AVG(sc.score) AS average_score,
    RANK() OVER (ORDER BY AVG(sc.score) DESC) as rank
FROM 
    students s
JOIN 
    scores sc ON s.student_id = sc.student_id
GROUP BY 
    s.student_id
ORDER BY 
    average_score DESC;