• 作业数据准备。（直接复制进去即可） ``sql -- 创建数据库 DROP DATABASE IF EXISTSfinal_assignment; CREATE DATABASE IF NOT EXISTSfinal_assignmentcharset='utf8mb4'; USEfinal_assignment`;

    — 创建学生表 DROP TABLE IF EXISTS student; CREATE TABLE IF NOT EXISTS student ( sid VARCHAR(10) PRIMARY KEY COMMENT ‘学号’, sname VARCHAR(20) NOT NULL COMMENT ‘姓名’, sbirthday DATE NOT NULL COMMENT ‘生日’, ssex CHAR(3) NOT NULL DEFAULT ‘男’ ); INSERT INTO student VALUES (‘01’, ‘赵雷’, ‘1990-01-01’, ‘男’), (‘02’, ‘钱电’, ‘1990-12-21’, ‘男’), (‘03’, ‘孙风’, ‘1990-05-20’, ‘男’), (‘04’, ‘李云’, ‘1990-08-06’, ‘男’), (‘05’, ‘周梅’, ‘1991-12-01’, ‘女’), (‘06’, ‘吴兰’, ‘1992-03-01’, ‘女’), (‘07’, ‘郑竹’, ‘1989-07-01’, ‘女’), (‘08’, ‘王菊’, ‘1990-01-20’, ‘女’); SELECT * FROM student;

    — 创建教师表 DROP TABLE IF EXISTS teacher; CREATE TABLE IF NOT EXISTS teacher ( tid VARCHAR(10) PRIMARY KEY COMMENT ‘教师编号’, tname VARCHAR(20) NOT NULL COMMENT ‘教师姓名’ ); INSERT INTO teacher VALUES (‘01’, ‘张三’), (‘02’, ‘李四’), (‘03’, ‘王五’); SELECT * FROM teacher;

    — 创建课程表 DROP TABLE IF EXISTS course; CREATE TABLE IF NOT EXISTS course ( cid VARCHAR(10) PRIMARY KEY COMMENT ‘课程编号’, cname VARCHAR(20) NOT NULL COMMENT ‘课程名称’, tid VARCHAR(10) NOT NULL COMMENT ‘老师编号’ ); INSERT INTO course VALUES (‘01’, ‘语文’, ‘02’), (‘02’, ‘数学’, ‘01’), (‘03’, ‘英语’, ‘03’); SELECT * FROM course;

    — 创建成绩表 DROP TABLE IF EXISTS score; CREATE TABLE IF NOT EXISTS score ( sid VARCHAR(10) COMMENT ‘学生编号’, cid VARCHAR(10) COMMENT ‘课程编号’, score FLOAT(4, 2) COMMENT ‘成绩’ ); INSERT INTO score VALUES (‘01’, ‘02’, 90), (‘01’, ‘01’, 80), (‘01’, ‘03’, 99), (‘02’, ‘01’, 70), (‘02’, ‘02’, 60), (‘02’, ‘03’, 80), (‘03’, ‘01’, 80), (‘03’, ‘02’, 80), (‘03’, ‘03’, 80), (‘04’, ‘01’, 50), (‘04’, ‘02’, 30), (‘04’, ‘03’, 20), (‘05’, ‘01’, 76), (‘05’, ‘02’, 87), (‘06’, ‘01’, 31), (‘06’, ‘03’, 34), (‘07’, ‘02’, 89), (‘07’, ‘03’, 98); SELECT * FROM score;

    2. - 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号。
    3. ```sql
    4. SELECT s1.sid FROM score AS s1
    5.     INNER JOIN score AS s2
    6.         ON s1.cid = '01' AND s2.cid = '02'
    7.                          AND s1.sid = s2.sid
    8. WHERE s1.score > s2.score;

    • 查询平均成绩大于60分的学生的学号和平均成绩。

      1. SELECT
      2.   sid,
      3.   ROUND(AVG(score), 2) AS avg_sco
      4. FROM score
      5. GROUP BY sid
      6. HAVING avg_sco > 60;

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

      1. SELECT
      2.   stu.sid,
      3.   stu.sname,
      4.   COUNT(sco.cid) AS 选课数,
      5.   SUM(IFNULL(sco.score, 0)) AS 总成绩
      6. FROM student AS stu
      7.   LEFT OUTER JOIN score AS sco on stu.sid = sco.sid
      8. GROUP BY sco.sid;

    • 查询姓“张”的老师的个数。

      1. SELECT COUNT(*) FROM teacher 
      2. WHERE LEFT(tname, 1) = '张';

    • 查询没学过“张三”老师课的学生的学号、姓名。 ```sql — 先找出学过张三老师课程的学生编号。 SELECT s.sid FROM teacher AS t INNER JOIN course AS c ON t.tid = c.tid INNER JOIN score AS s ON c.cid = s.cid WHERE t.tname = ‘张三’;

    — 然后就可以反向找出没学过的学生信息。 SELECT sid, sname FROM student WHERE sid NOT IN ( SELECT s.sid FROM teacher AS t INNER JOIN course AS c ON t.tid = c.tid INNER JOIN score AS s ON c.cid = s.cid WHERE t.tname = ‘张三’ );

    2. - 查询学过“张三”老师所教的所有课的同学的学号、姓名。
    3. ```sql
    4. -- 先查询出张三老师的学生的学生编号。
    5. SELECT sid FROM teacher
    6.     INNER JOIN course AS c ON teacher.tid = c.tid
    7.     INNER JOIN score AS s ON c.cid = s.cid
    8. WHERE tname = '张三';
    10. -- 接着，按照学号分组，统计出这些学生上了几门张三老师的课。
    11. SELECT
    12.     sid,
    13.     COUNT(s.cid)
    14. FROM teacher
    15.     INNER JOIN course AS c ON teacher.tid = c.tid
    16.     INNER JOIN score AS s ON c.cid = s.cid
    17. WHERE tname = '张三'
    18. GROUP BY sid;
    20. -- 然后查询出张三老师教授课程的数量。
    21. SELECT COUNT(cid) FROM teacher
    22.     INNER JOIN course AS c ON teacher.tid = c.tid
    23. WHERE tname = '张三';
    25. -- 接着过滤出学过张三老师所有课程的学生。
    26. -- 学过张三老师所有课程就是“学生上的张三老师的课数量 = 张三老师教授课程的数量”
    27. SELECT
    28.     sid,
    29.     COUNT(s.cid)
    30. FROM teacher
    31.     INNER JOIN course AS c ON teacher.tid = c.tid
    32.     INNER JOIN score AS s ON c.cid = s.cid
    33. WHERE tname = '张三'
    34. GROUP BY sid
    35. HAVING COUNT(s.cid) = (
    36.     SELECT COUNT(cid) FROM teacher
    37.         INNER JOIN course AS c ON teacher.tid = c.tid
    38.     WHERE tname = '张三'
    39. );
    41. -- 最后，用学号去连接学生表，即可得到最后的结果
    42. SELECT
    43.     sco.sid,
    44.     stu.sname
    45. FROM teacher AS t
    46.     INNER JOIN course AS c ON t.tid = c.tid
    47.     INNER JOIN score AS sco ON c.cid = sco.cid
    48.     INNER JOIN student AS stu on sco.sid = stu.sid
    49. WHERE t.tname = '张三'
    50. GROUP BY sco.sid
    51. HAVING COUNT(sco.cid) = (
    52.     SELECT COUNT(sco.cid) FROM teacher
    53.         INNER JOIN course AS c ON teacher.tid = c.tid
    54.     WHERE tname = '张三'
    55. );
    • 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名。 ```sql — 用内连接查询出学过01和02课程的学生学号。 SELECT s01.sid FROM score AS s01 INNER JOIN score AS s02
      1.   ON s01.cid = '01' AND s02.cid = '02'
      2.          AND s01.sid = s02.sid;

    — 然后连接学生表，获取学生姓名即可。 SELECT s01.sid, s.sname FROM score AS s01 INNER JOIN score AS s02 ON s01.cid = ‘01’ AND s02.cid = ‘02’ AND s01.sid = s02.sid INNER JOIN student AS s ON s01.sid = s.sid;

    2. - 查询课程编号为“02”的总成绩。
    3. ```sql
    4. SELECT SUM(score) FROM score WHERE cid = '02';
    • 查询所有课程成绩小于60分的学生的学号、姓名。 ```sql — 首先获取每个学生的最高成绩。 SELECT sid, MAX(score) FROM score GROUP BY sid;

    — 若该学生的最高成绩小于60，这就意味着这个学生的所有成绩都小于60。 SELECT sid, MAX(score) AS max_sco FROM score GROUP BY sid HAVING max_sco < 60;

    — 接着连接学生表，即可获得结果 — 学号为08的王菊同学在成绩表中没有数据，因此需要处理一下空数据。 SELECT stu.sid, stu.sname FROM score AS sco RIGHT OUTER JOIN student AS stu on sco.sid = stu.sid GROUP BY sco.sid HAVING MAX(IFNULL(sco.score, 0)) < 60;

    2. - 查询没有学全所有课的学生的学号、姓名。
    3. ```sql
    4. -- 查询出课程的数量。
    5. SELECT COUNT(*) FROM course;
    7. -- 查询出每个学生学习的课程数。
    8. SELECT
    9.     sid,
    10.     COUNT(cid) AS course_num
    11. FROM score
    12. GROUP BY sid;
    14. -- 筛选出没有学全所有课的学生信息。
    15. SELECT
    16.     sid,
    17.     COUNT(cid) AS course_num
    18. FROM score
    19. GROUP BY sid
    20. HAVING course_num < (
    21.     SELECT COUNT(*) FROM course
    22. );
    24. -- 连接学生表，得到最终结果
    25. SELECT
    26.     stu.sid,
    27.     stu.sname,
    28.     COUNT(cid) AS course_num
    29. FROM score RIGHT OUTER JOIN student AS stu on score.sid = stu.sid
    30. GROUP BY sid
    31. HAVING course_num < (
    32.     SELECT COUNT(*) FROM course
    33. );
    • 查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名。 ```sql — 找到学号为“01”的学生所学的所有课程号。 SELECT cid FROM score WHERE sid = ‘01’;

    — 查询选择了步骤一结果中的课程号的学生（除了学号为01的之外） SELECT * FROM score WHERE cid IN ( SELECT cid FROM score WHERE sid = ‘01’ ) AND sid != ‘01’;

    — 关联学生表，获取学生姓名。 SELECT DISTINCT stu.sid, stu.sname FROM score AS sco INNER JOIN student AS stu on sco.sid = stu.sid WHERE cid IN ( SELECT cid FROM score WHERE sid = ‘01’ ) AND sco.sid != ‘01’;

    2. - 查询和“01”号同学所学课程完全相同的其他同学的学号。
    3. ```sql
    4. -- 用GROUP_CONCAT连接所有课程号。
    5. -- 连接时为了避免先后顺序不一致，在GROUP_CONCAT函数内进行排序。
    6. SELECT
    7.     sid,
    8.     GROUP_CONCAT(cid ORDER BY cid) AS all_sid
    9. FROM score
    10. GROUP BY sid;
    12. -- 查询出sid=01的所有课程号。
    13. SELECT GROUP_CONCAT(cid ORDER BY cid) FROM score WHERE sid = '01';
    15. -- 筛选出学号不为01，但是课程号与sid=01完全相同的学生学号。
    16. SELECT sid FROM score
    17. GROUP BY sid
    18. HAVING sid != '01' AND GROUP_CONCAT(cid ORDER BY cid) = (
    19.     SELECT GROUP_CONCAT(cid ORDER BY cid) FROM score WHERE sid = '01'
    20. );
    • 把“score”表中“张三”老师教的课的成绩都更改为此课程的平均成绩。 ```sql — 获取张三老师所授课程的平均成绩。 SELECT s.cid, ROUND(AVG(score), 2) AS avg_sco FROM teacher AS t INNER JOIN course AS c on t.tid = c.tid INNER JOIN score AS s on c.cid = s.cid WHERE tname = ‘张三’ GROUP BY c.cid;

    — 修改数据 UPDATE score JOIN ( SELECT s.cid, ROUND(AVG(score), 2) AS avg_sco FROM teacher AS t INNER JOIN course AS c on t.tid = c.tid INNER JOIN score AS s on c.cid = s.cid WHERE tname = ‘张三’ GROUP BY c.cid ) AS t ON score.cid = t.cid SET score.score = t.avg_sco;

    — 验证结果 SELECT * FROM score;

    2. - 查询和“02”号的同学学习的课程完全相同的其他同学学号和姓名。
    3. ```sql
    4. SELECT sid FROM score
    5. GROUP BY sid
    6. HAVING sid != '02' AND GROUP_CONCAT(cid ORDER BY cid) = (
    7.     SELECT GROUP_CONCAT(cid ORDER BY cid) FROM score WHERE sid = '02'
    8. );

    • 检索”01”课程分数小于60，按分数降序排列的学生信息。

      1. SELECT stu.*, cid, score
      2. FROM score AS sco
      3.   INNER JOIN student AS stu on sco.sid = stu.sid
      4. WHERE cid = '01' AND score < 60
      5. ORDER BY score DESC;

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

      1. SELECT
      2.   cid,
      3.   MAX(score) AS max_sco,
      4.   MIN(score) AS min_sco
      5. FROM score
      6. GROUP BY cid;

    • 查询不同老师所教不同课程平均分从高到低显示。

      1. SELECT
      2.   t.tid,
      3.   t.tname,
      4.   c.cid,
      5.   avg(score) AS avg_sco
      6. FROM teacher AS t
      7.   INNER JOIN course AS c ON t.tid = c.tid
      8.   INNER JOIN score AS s ON c.cid = s.cid
      9. GROUP BY t.tid, c.cid
      10. ORDER BY avg_sco DESC;

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

      1. SELECT
      2.   sid,
      3.   AVG(IFNULL(score, 0)) AS avg_soc,
      4.   RANK() OVER (
      5.       ORDER BY AVG(IFNULL(score, 0)) DESC
      6.   ) AS ranking
      7. FROM score
      8. GROUP BY sid;

    • 查询各科成绩前三名的记录。

      1. SELECT cid, sid, ranking FROM (
      2.   SELECT
      3.       *,
      4.       ROW_NUMBER() OVER (
      5.           PARTITION BY cid
      6.           ORDER BY score DESC
      7.       ) AS ranking
      8.   FROM score
      9. ) AS tmp
      10. WHERE ranking <= 3;

    • 查询每门课程被选修的学生数。

      1. SELECT
      2.   cid,
      3.   COUNT(sid) AS count_num
      4. FROM score
      5. GROUP BY cid;

    • 查询出只选修了两门课程的全部学生的学号和姓名。

      1. SELECT
      2.   stu.sid,
      3.   stu.sname
      4. FROM score AS sco
      5.   INNER JOIN student AS stu ON sco.sid = stu.sid
      6. GROUP BY stu.sid
      7. HAVING COUNT(cid) = 2;

    • 查询男生、女生人数。

      1. SELECT
      2.   ssex,
      3.   COUNT(sid) AS count_num
      4. FROM student
      5. GROUP BY ssex;

    • 查询名字中含有“风”字的学生信息。

      1. SELECT * FROM student WHERE INSTR(sname, '风');

    • 查询同名同性学生名单并统计同名人数。

      1. -- 同名就是按照名字分组，同性就是按照性别分组。
      2. -- 若有同名同姓，就代表着同一组的数量要大于等于2。
      3. SELECT
      4.   sname,
      5.   ssex,
      6.   COUNT(*)
      7. FROM student
      8. GROUP BY sname, ssex
      9. HAVING COUNT(*) >= 2;

    • 1990年出生的学生名单。

      1. SELECT * FROM student
      2. WHERE YEAR(sbirthday) = 1990;

    • 查询平均成绩大于85的所有学生的学号、姓名和平均成绩。

      1. SELECT
      2.   stu.sid,
      3.   stu.sname,
      4.   AVG(score) AS avg_sco
      5. FROM student AS stu
      6.   INNER JOIN score AS sco ON stu.sid = sco.sid
      7. GROUP BY stu.sid
      8. HAVING AVG(score) > 85;

    • 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列。

      1. SELECT cid, AVG(score) AS avg_sco
      2. FROM score
      3. GROUP BY cid
      4. ORDER BY avg_sco, cid DESC;

    • 查询课程名称为“数学”且分数低于60的学生姓名和分数。

      1. SELECT
      2.   stu.sname,
      3.   sco.score
      4. FROM student AS stu
      5.   INNER JOIN score AS sco on stu.sid = sco.sid
      6.   INNER JOIN course AS c ON sco.cid = c.cid
      7. WHERE c.cname = '数学' AND sco.score < 60;

    • 查询所有学生的选课情况。

      1. SELECT
      2.   stu.sid,
      3.   stu.sname,
      4.   IFNULL(GROUP_CONCAT(cname), '未选课') AS 选课情况
      5. FROM student AS stu
      6.   LEFT OUTER JOIN score AS sco on stu.sid = sco.sid
      7.   LEFT OUTER JOIN course AS c on sco.cid = c.cid
      8. GROUP BY sco.sid;

    • 查询任何一门课程成绩在70分以上的姓名、课程名称和分数。

      1. SELECT
      2.   stu.sname,
      3.   c.cname,
      4.   sco.score
      5. FROM student AS stu
      6.   INNER JOIN score AS sco on stu.sid = sco.sid
      7.   INNER JOIN course AS c on sco.cid = c.cid
      8. WHERE sco.score > 70;

    • 查询不及格的课程并按课程号从大到小排列。

      1. SELECT DISTINCT cid FROM score
      2. WHERE score < 60
      3. ORDER BY cid DESC;

    • 查询课程编号为03且课程成绩在80分以上的学生的学号和姓名。

      1. SELECT
      2.   stu.sid,
      3.   stu.sname
      4. FROM score AS sco
      5.   INNER JOIN student AS stu ON sco.sid = stu.sid
      6. WHERE cid = '03' AND score > 80;

    • 查询选了课程的学生人数。

      1. SELECT COUNT(DISTINCT sid) FROM score;

    • 查询选修“王五”老师所授课程的学生中成绩最高的学生姓名及其成绩。 ```sql — 先查询每个老师每门课程的最高分 SELECT stu.sid, stu.sname, t.tid, t.tname, c.cname, sco.score, MAX(score) OVER (

      1.   PARTITION BY t.tid, c.cid

      ) AS max_sco FROM teacher AS t INNER JOIN course AS c on t.tid = c.tid INNER JOIN score AS sco on c.cid = sco.cid INNER JOIN student AS stu on sco.sid = stu.sid;

    — 接着查看王五老师每门课的最高分。 SELECT stu.sid, stu.sname, t.tid, t.tname, c.cname, sco.score, MAX(score) OVER (PARTITION BY t.tid, c.cid) AS max_sco FROM teacher AS t INNER JOIN course AS c on t.tid = c.tid INNER JOIN score AS sco on c.cid = sco.cid INNER JOIN student AS stu on sco.sid = stu.sid WHERE t.tname = ‘王五’;

    — 筛选出分数与最高分相等的学生的学生姓名以及成绩。 SELECT * FROM ( SELECT stu.sid, stu.sname, t.tid, t.tname, c.cname, sco.score, MAX(score) OVER(PARTITION BY t.tid, c.cid) AS max_sco FROM teacher AS t INNER JOIN course AS c on t.tid = c.tid INNER JOIN score AS sco on c.cid = sco.cid INNER JOIN student AS stu on sco.sid = stu.sid WHERE t.tname = ‘王五’ ) AS t WHERE score = max_sco;

    2. - 查询各个课程及相应的选修人数。
    3. ```sql
    4. SELECT
    5.     cid,
    6.     COUNT(sid)
    7. FROM score
    8. GROUP BY cid;

    • 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩。

      1. SELECT * FROM score AS s1
      2.   INNER JOIN score AS s2
      3.       ON s1.sid = s2.sid  -- 学生编号相同，代表是同一个人
      4.              AND s1.score = s2.score  -- 两门课的成绩相同
      5.              AND s1.cid != s2.cid;  -- 课程号不同，表示是两门课

    • 查询每门课程成绩最好的前两名。

      1. SELECT * FROM (
      2.   SELECT *, ROW_NUMBER() OVER (
      3.       PARTITION BY cid
      4.       ORDER BY score DESC
      5.   ) AS ranking
      6.   FROM score
      7. ) AS tmp WHERE ranking <= 2;

    • 统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序。

      1. SELECT
      2.   cid,
      3.   COUNT(sid) AS num
      4. FROM score
      5. GROUP BY cid
      6. HAVING num > 5
      7. ORDER BY num DESC, cid;

    • 查询至少选修两门课程的学生学号。

      1. SELECT sid
      2. FROM score
      3. GROUP BY sid
      4. HAVING COUNT(cid) >= 2;

    • 查询选修了全部课程的学生信息。 ```sql — 查询课程总数量。 SELECT COUNT(cid) FROM course;

    — 查询选课数量等于课程总数量的学生学号。 SELECT sid FROM score GROUP BY sid HAVING COUNT(cid) >= ( SELECT COUNT(cid) FROM course );

    — 连接学生表，获取学生信息。 SELECT stu.* FROM score AS sco LEFT OUTER JOIN student AS stu on sco.sid = stu.sid GROUP BY sid HAVING COUNT(cid) >= ( SELECT COUNT(cid) FROM course );

    2. - 查询两门及以上不及格课程的同学的学号及其平均成绩。
    3. ```sql
    4. -- 查询两门及以上不及格课程的学生学号。
    5. SELECT
    6.     sid,
    7.     COUNT(cid) AS fail_num
    8. FROM score
    9. WHERE score < 60
    10. GROUP BY sid
    11. HAVING fail_num >= 2;
    13. -- 查询所有学生的平均成绩。
    14. SELECT sid, AVG(score) AS avg_sco FROM score
    15. GROUP BY sid;
    17. -- 二者连接，获取两门及以上不及格课程的同学的学号及其平均成绩。
    18. SELECT
    19.     fail_stu.sid,
    20.     all_stu.avg_sco
    21. FROM (
    22.     SELECT sid, COUNT(cid) AS fail_num
    23.     FROM score
    24.     WHERE score < 60
    25.     GROUP BY sid
    26.     HAVING fail_num >= 2
    27. ) AS fail_stu LEFT OUTER JOIN (
    28.     SELECT sid, AVG(score) AS avg_sco FROM score
    29.     GROUP BY sid
    30. ) AS all_stu ON fail_stu.sid = all_stu.sid;

    • 检索课程编号为“03”且分数小于60的学生学号，结果按分数降序排列。

      1. SELECT sid FROM score
      2. WHERE cid = '03' AND score < 60
      3. ORDER BY score DESC;

    • 删除学生编号为“02”的课程编号为“01”的成绩。

      1. DELETE FROM score WHERE sid = '02' AND cid = '01';

    • 使用分段[85-100],[70-84],[60-69],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称。 ```sql -- 先对所有成绩进行分段。 SELECT *, IF (score >= 85 AND score <= 100, 1, 0) AS ‘[85-100]’, IF (score >= 70 AND score <= 84, 1, 0) AS ‘[70-84]’, IF (score >= 60 AND score <= 69, 1, 0) AS ‘[60-69]’, IF (score <= 60, 1, 0) AS ‘[<60]’ FROM score AS s INNER JOIN course AS c on s.cid = c.cid;

    — 接着统计每门课成每个分段的人数。（实际上就是把每个分段的0和1都加起来） SELECT c.cid, c.cname, SUM(IF (score >= 85 AND score <= 100, 1, 0)) AS ‘[85-100]’, SUM(IF (score >= 70 AND score <= 84, 1, 0)) AS ‘[70-84]’, SUM(IF (score >= 60 AND score <= 69, 1, 0)) AS ‘[60-69]’, SUM(IF (score <= 60, 1, 0)) AS ‘[<60]’ FROM score AS s INNER JOIN course AS c on s.cid = c.cid GROUP BY c.cid;

    2. - 按各科平均成绩从低到高和及格率的百分数从高到低排列，以如下形式显示：课程号,课程名,平均成绩,及格百分数。
    3. ```sql
    4. -- 合格率 = (该门课程大于60的人数 / 该门课程的总人数) * 100 %
    5. -- 该门课程大于60的人数：SELECT COUNT(score.sid) FROM score WHERE score.cid = c.cid AND score.score > 60
    6.     -- c.cid是从外表传进来的参数
    7. -- 该门课程的总人数：COUNT(sid)
    8. SELECT
    9.     c.cid,
    10.     c.cname,
    11.     AVG(score) AS avg_sco,
    12.     CONCAT(
    13.         ROUND((SELECT COUNT(score.sid) FROM score WHERE score.cid = c.cid AND score.score > 60) / COUNT(sid) * 100, 2),
    14.         '%'
    15.     ) AS pass_rate
    16. FROM score AS s
    17.     INNER JOIN course AS c on s.cid = c.cid
    18. GROUP BY c.cid
    19. ORDER BY avg_sco, pass_rate DESC;

    • 查询本周过生日的学生。

      1. SELECT * FROM (
      2.   SELECT *,
      3.       CONCAT_WS('-', YEAR(NOW()), SUBSTR(sbirthday, 6)) AS new_bir
      4.   FROM student
      5. ) AS tmp
      6. WHERE WEEK(NOW(), 1) = week(new_bir, 1);

    • 查询下周过生日的学生。

      1. SELECT * FROM (
      2.   SELECT *,
      3.       CONCAT_WS('-', YEAR(NOW()), SUBSTR(sbirthday, 6)) AS new_bir
      4.   FROM student
      5. ) AS tmp
      6. WHERE WEEK(NOW(), 1) + 1 = week(new_bir, 1);

    • 查询本月过生日的学生。

      1. SELECT * FROM student
      2. WHERE MONTH(sbirthday) = MONTH(NOW());

    • 查询下月过生日的学生。

      1. SELECT * FROM student
      2. WHERE MONTH(sbirthday) = MONTH(NOW()) + 1;