MySQL集｜每日一题：使用 SQL，操作学生成绩相关数据

程序员星河
640 阅读4分钟

教师表（teacher）

（教师编号）id（教师姓名）name
1张三
2李四
3王五

课程表（course）

（课程编号）id（课程名称）name（教师编号）t_id
1语文2
2数学1
3英语3

学生表（student）

（学生编号）id（学生姓名）name（出生日期）birth（性别）sex（身份证号）id_no
1王小明110102199001011619
2张益达110104199012211630
3张小红110103199005206122
4李云龙110104199008061173
5楚佳佳110101199112018221
6赵云飞110109199203014722
7李信110228198907018304
8杨萍110111199001201382
9李晶110114199401201677

成绩表（score）

（学生编号）s_id（课程编号）c_id（分数）s_score
1180
1290
1399
2170
2260
2380
3180
3280
3380
4150
4230
4320
5176
5287
5395
6131
6288
6334
7166
7289
7398
8159
8288
9267
9388

1.按照身份证号的规则（从身份证号第 7 位到第 14 位表示出生年月日），将学生的出生日期计算出来，以 date 格式，存储到 birth 字段中。

UPDATE
  student 
SET 
  birth = SUBSTR(id_no, 7, 8);

2.按照身份证号的规则（倒数第 2 位如果是单数，则是男生。如果是双数，则是女生），将学生的性别计算出来，并存储到 sex 字段中。

UPDATE
  student
SET
  sex = CASE WHEN LEFT(RIGHT(id_no, 2), 1) % 2 = 0 THEN '女' ELSE '男' END;

3.查询出生日期在 1990-07-01 到 1992-02-01 中的学生。

SELECT
  *
FROM
  student
WHERE
  birth BETWEEN '1990-07-01' AND '1992-02-01';

4.查询每门课程的总成绩以及平均成绩，按课程编码，课程名称，总成绩、平均成绩显示。

SELECT
  c.id, c.name, SUM(s.s_score), AVG(s.s_score)
FROM
  score s
  LEFT JOIN course c ON s.c_id = c.id
GROUP BY s.c_id;

5.给教师表，增加一个 salary 字段，字段要求是浮点数，保留两位小数。

ALTER TABLE teacher ADD salary DECIMAL(18, 2);

6.查询语文课程的成绩单，要求显示学生编号、学生姓名、成绩分数，并进行排名。

SELECT
  stu.id, stu.name, s.s_score
FROM
  score s
  LEFT JOIN student stu ON s.s_id = stu.id
  LEFT JOIN course c ON s.c_id = c.id
WHERE
  c.name = '语文'
ORDER BY s.s_score DESC;

7.查询语文课程比数学课程成绩高的学生的姓名、身份证号和课程分数。

SELECT
  stu.name, stu.id_no, s1.s_score
FROM
  student stu,
  (SELECT s.s_id, s.s_score FROM score s LEFT JOIN course c ON s.c_id = c.id WHERE c.name = '语文') s1,
  (SELECT s.s_id, s.s_score FROM score s LEFT JOIN course c ON s.c_id = c.id WHERE c.name = '数学') s2
WHERE
  stu.id = s1.s_id
  AND stu.id = s2.s_id
  AND s1.s_score > s2.s_score;

8.查询平均成绩大于等于 60 分的同学的学生编号、学生姓名和平均成绩。

SELECT
  stu.id, stu.name, AVG(s.s_score)
FROM
  score s
  LEFT JOIN student stu ON s.s_id = stu.id
GROUP BY s.s_id
HAVING AVG(s.s_score) >= 60;

9.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩。

SELECT
  stu.id, stu.name, COUNT(1), SUM(s_score)
FROM
  score s
  LEFT JOIN student stu ON s.s_id = stu.id
GROUP BY s.s_id;

10.查询王姓老师的数量。

SELECT
  COUNT(1)
FROM
  teacher
WHERE
  name LIKE '王%';

11.查询学过张三老师授课的同学的姓名。

SELECT
  stu.name
FROM
  score s
  LEFT JOIN student stu ON s.s_id = stu.id
  LEFT JOIN course c ON s.c_id = c.id
WHERE
  c.name = (SELECT c.name FROM course c LEFT JOIN teacher t ON c.t_id = t.id WHERE t.name = '张三');

12.查询没学过李四老师授课的同学的姓名。

SELECT
  stu.name
FROM
  student stu
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      score s,
      course c
    WHERE
      s.s_id = stu.id
      AND s.c_id = c.id
      AND c.name = (SELECT c.name FROM course c LEFT JOIN teacher t ON c.t_id = t.id WHERE t.name = '李四')
  );

13.查询学过编号为 1 并且也学过编号为 2 的课程的同学的姓名。

SELECT
  name
FROM
  student
WHERE
  id IN (SELECT s_id FROM score WHERE c_id = 1)
  AND id IN (SELECT s_id FROM score WHERE c_id = 2);

14.按平均成绩从高到低，显示所有学生的所有课程的成绩以及平均成绩。

SELECT
  s.*, s1.avgScore 
FROM
  score s,
  (SELECT s_id, AVG(s_score) avgScore FROM score GROUP BY s_id) s1
WHERE
  s.s_id = s1.s_id
ORDER BY s1.avgScore DESC;

15.查询各科成绩的课程编号，课程名称，最高分，最低分，平均分，及格率，中等率，优良率，优秀率。

tips：及格为 >= 60，中等为 7080，优良为 8090，优秀为 >= 90

SELECT
  c.id '课程编号',
  c.name '课程名称',
  MAX(s.s_score) '最高分',
  MIN(s.s_score) '最低分',
  AVG(s.s_score) '平均分',
  SUM(CASE WHEN s.s_score >= 60 THEN 1 ELSE 0 END) / COUNT(s.s_id) * 100 '及格率',
  SUM(CASE WHEN s.s_score >= 70 AND s.s_score < 80 THEN 1 ELSE 0 END) / COUNT(s.s_id) * 100 '中等率',
  SUM(CASE WHEN s.s_score >= 80 AND s.s_score < 90 THEN 1 ELSE 0 END) / COUNT(s.s_id) * 100 '优良率',
  SUM(CASE WHEN s.s_score >= 90 THEN 1 ELSE 0 END) / COUNT(s.s_id) * 100 '优秀率'
FROM
  score s
  LEFT JOIN course c ON s.c_id = c.id
GROUP BY s.c_id;

友情提示： 题目来源于各家真实企业，以上回答仅供参考，不能确定是否符合出题人要考查的知识点！

avatar
文章
阅读
粉丝