SQL经典50题(MySQL版)

表结构

1、学生表（学生编号，学生姓名，学生生日，学生性别）
student(sno, sname, birth, gender)
2、课程表（课程编号，课程名称，教师编号）
course(cno, cname, tno)
3、教师表（教师编号，教师姓名）
teacher(tno, tname)
4、成绩表（学生编号，课程编号，分数）
sc(sno, cno, score)

创建测试数据

-- 创建学生表
DROP TABLE IF EXISTS student;
CREATE TABLE IF NOT EXISTS student(
    sno INT(11) NOT NULL AUTO_INCREMENT,
    sname VARCHAR(20) NOT NULL,
    birth DATE DEFAULT NULL,
    gender ENUM('男', '女', '保密') DEFAULT '保密',
    PRIMARY KEY(sno) 
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 插入数据
INSERT INTO student VALUES(1, '赵雷', '1990-01-01', '男');
INSERT INTO student VALUES(2, '钱电', '1990-12-21', '男');
INSERT INTO student VALUES(3, '孙风', '1990-05-20', '男');
INSERT INTO student VALUES(4, '李云', '1990-08-06', '男');
INSERT INTO student VALUES(5, '周梅', '1991-12-01', '女');
INSERT INTO student VALUES(6, '吴兰', '1992-03-01', '女');
INSERT INTO student VALUES(7, '郑竹', '1989-07-01', '女');
INSERT INTO student VALUES(8, '王菊', '1990-01-20', '女');


-- 创建教师表
DROP TABLE IF EXISTS teacher;
CREATE TABLE IF NOT EXISTS teacher(
    tno INT(11) NOT NULL AUTO_INCREMENT,
    tname VARCHAR(20) NOT NULL,
    PRIMARY KEY(tno)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 插入数据
INSERT INTO teacher VALUES(1, '张三');
INSERT INTO teacher VALUES(2, '李四');
INSERT INTO teacher VALUES(3, '王五');


-- 创建课程表
DROP TABLE IF EXISTS course;
CREATE TABLE IF NOT EXISTS course(
    cno INT(11) NOT NULL AUTO_INCREMENT,
    cname VARCHAR(20) NOT NULL,
    tno INT(11),
    PRIMARY KEY(cno),
    FOREIGN KEY(tno) REFERENCES teacher(tno)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 插入数据
INSERT INTO course VALUES(1, '语文', 2);
INSERT INTO course VALUES(2, '数学', 1);
INSERT INTO course VALUES(3, '英语', 3);


-- 创建成绩表
DROP TABLE IF EXISTS sc;
CREATE TABLE IF NOT EXISTS sc(
    sno INT(11) NOT NULL AUTO_INCREMENT,
    cno INT(11) NOT NULL,    
    score INT(11),
    FOREIGN KEY(sno) REFERENCES student(sno),
    FOREIGN KEY(cno) REFERENCES course(cno)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 插入数据
insert into sc values(1, 1, 80);
insert into sc values(1, 2, 90);
insert into sc values(1, 3, 99);
insert into sc values(2, 1, 70);
insert into sc values(2, 2, 60);
insert into sc values(2, 3, 80);
insert into sc values(3, 1, 80);
insert into sc values(3, 2, 80);
insert into sc values(3, 3, 80);
insert into sc values(4, 1, 50);
insert into sc values(4, 2, 30);
insert into sc values(4, 3, 20);
insert into sc values(5, 1, 76);
insert into sc values(5, 2, 87);
insert into sc values(6, 1, 31);
insert into sc values(6, 3, 34);
insert into sc values(7, 2, 89);
insert into sc values(7, 3, 98);


-- 查看表中数据
SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM sc;

1、查询"1"课程比"2"课程成绩高的学生的信息及课程分数

-- 方法一：
SELECT stu.*, s1.cno, s1.score, s2.cno, s2.score
FROM student stu
    JOIN sc s1 ON stu.sno = s1.sno
    JOIN sc s2 ON stu.sno = s2.sno
WHERE s1.cno = 1 AND s2.cno = 2 AND s1.score > s2.score;

-- 方法二：使用子查询
-- 先找到这两门课程的成绩
select sno, cno, score from sc where cno = 1;
SELECT sno, cno, score FROM sc WHERE cno = 2;
-- 下一步使用join把这两个查询结果连接起来
select stu.*, t1.cno, t1.score1, t2.cno, t2.score2
from student stu
    join (
        select sno, cno, score as score1
        from sc 
        where cno = 1
    ) t1
    on stu.sno = t1.sno
    join (
        select sno, cno, score as score2
        from sc
        where cno = 2
    ) t2
    on stu.sno = t2.sno
where t1.score1 > t2.score2;

2、查询"1"课程比"2"课程成绩低的学生的信息及课程分数

-- 方法一：连接三张表
SELECT stu.*, s1.cno, s1.score, s2.cno, s2.score
FROM student stu
    JOIN sc s1 ON stu.sno = s1.sno AND s1.cno = 1
    JOIN sc s2 ON stu.sno = s2.sno AND s2.cno = 2
WHERE s1.score < s2.score;

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

-- 先查询平均成绩大于60的学生编号和平均成绩
SELECT sno, AVG(score) AS avgScore
FROM sc
GROUP BY sno
HAVING avgScore >= 60;
-- 然后和student表连接
SELECT stu.sno, stu.sname, t.avgScore
FROM student stu
    JOIN (
        SELECT sno, AVG(score) AS avgScore
        FROM sc
        GROUP BY sno
        HAVING avgScore >= 60   
    ) t
    ON stu.sno = t.sno;

-- 方法二：
SELECT stu.sno, stu.sname, t.avgScore
FROM (
    SELECT sno, AVG(score) AS avgScore
    FROM sc 
    GROUP BY sno
    HAVING AVG(score) >= 60
) t
    LEFT JOIN student stu ON t.sno = stu.sno;
    
-- 方法三：
SELECT stu.sno, stu.sname, AVG(s.score) AS avgScore
FROM student stu
    JOIN sc s ON stu.sno = s.sno
GROUP BY s.sno
HAVING AVG(s.score) >= 60;

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

SELECT stu.sno, stu.sname, AVG(s.score) AS avgScore
FROM student stu
    JOIN sc s ON stu.sno = s.sno
GROUP BY s.sno
HAVING AVG(s.score) < 60;

5、查询所有学生的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 NULL )

-- 方法一：使用join连接两张表，然后按学生学号分组
SELECT stu.sno, stu.sname, COUNT(s.cno) sumCourse, SUM(s.score) sumScore
FROM student stu
    LEFT JOIN sc s ON stu.sno = s.sno
GROUP BY stu.sno;

-- 方法二：
-- 使用子查询查出每个学生的选课总数和所有课程的总成绩
-- 然后再和student表连接
SELECT stu.sno, stu.sname, t.sumCourse, t.sumScore
FROM student stu
    LEFT JOIN (
        SELECT sno, COUNT(cno) AS sumCourse, SUM(score) AS sumScore
        FROM sc 
        GROUP BY sno
    ) t
    ON stu.sno = t.sno;

6、查询「李」姓老师的数量

SELECT COUNT(*)
FROM teacher
WHERE tname LIKE "李%";

7、查询学过「张三」老师授课的同学的信息

-- 方法一：使用子查询
-- 先查询 张三 老师的教师编号
SELECT tno
FROM teacher
WHERE teacher.tname = '张三';
-- 查询 张三 老师所教授的课程编号
SELECT cno
FROM course
WHERE course.tno = (
    SELECT tno
    FROM teacher
    WHERE teacher.tname = '张三'
);
-- 完整查询语句
SELECT stu.*
FROM student stu
    JOIN sc s ON stu.sno = s.sno
WHERE s.cno IN (
    SELECT cno
    FROM course
    WHERE course.tno = (
        SELECT tno
        FROM teacher
        WHERE teacher.tname = '张三'
    )
);

-- 方法二：使用多重join连接
SELECT stu.*
FROM student stu
    JOIN sc s ON stu.sno = s.sno
    JOIN course c ON s.cno = c.cno
    JOIN teacher t ON c.tno = t.tno
WHERE t.tname = '张三';

8、查询没学过"张三"老师授课的同学的信息

-- 思路：先查询学过张三老师授课的同学的学号，然后查询除了这些学号的同学
SELECT *
FROM student stu
WHERE sno NOT IN (
    SELECT stu.sno
    FROM student stu
        JOIN sc s ON stu.sno = s.sno
        JOIN course c ON s.cno = c.cno
        JOIN teacher t ON c.tno = t.tno
    WHERE t.tname = '张三'
);

9、查询学过编号为"1"并且也学过编号为"2"的课程的同学的信息

-- 方法一：
SELECT stu.*, s1.cno, s1.score, s2.cno, s2.score
FROM student stu
    JOIN sc s1 ON stu.sno = s1.sno
    JOIN sc s2 ON s1.sno = s2.sno
WHERE s1.cno = 1 AND s2.cno = 2;

-- 方法二：
SELECT stu.*, t1.cno, t1.score, t2.cno, t2.score
FROM student stu
    JOIN (
        SELECT * 
        FROM sc WHERE cno = 1
    ) t1
    ON stu.sno = t1.sno
    JOIN (
        SELECT *
        FROM sc WHERE cno = 2
    ) t2
    ON t1.sno = t2.sno;

-- 方法三：
SELECT * 
FROM student stu
WHERE stu.sno IN (
    SELECT sno 
    FROM sc 
    WHERE cno = 1 
) AND stu.sno IN (
    SELECT sno 
    FROM sc 
    WHERE cno = 2
);

10、查询学过编号为"1"但是没有学过编号为"2"的课程的同学的信息(不存在显示null)

-- 方法一：
SELECT * 
FROM student stu
WHERE stu.sno IN (
    SELECT sno 
    FROM sc 
    WHERE cno = 1 
) AND stu.sno NOT IN (
    SELECT sno 
    FROM sc 
    WHERE cno = 2
);

11、查询没有学全所有课程的同学的信息

-- 思路：反向思维，先查询学全所有课程的同学的学号
SELECT s.sno
FROM sc s
GROUP BY s.sno
HAVING COUNT(cno) = (
    SELECT COUNT(*)
    FROM course
);
-- 然后选择不再这些学号范围的同学
SELECT stu.*
FROM student stu
WHERE stu.sno NOT IN (
    SELECT s.sno
    FROM sc s
    GROUP BY s.sno
    HAVING COUNT(cno) = (
        SELECT COUNT(*)
        FROM course
    )
);

-- 方法二：
-- 先查询所有课程的总数
SELECT COUNT(*)
FROM course;
-- 查询所学课程数量小于课程总数的学生信息
SELECT stu.*
FROM student stu
    LEFT JOIN sc s ON stu.sno = s.sno
GROUP BY stu.sno
HAVING COUNT(cno) < (
    SELECT COUNT(cno)
    FROM course
);

12、查询至少有一门课与学号为"1"的学生所学相同的学生的信息

-- 先查询学生编号为1的学生所学的课程编号
SELECT cno
FROM sc
WHERE sno = 1;
-- 再查询学了以上结果集中的课程的学生信息并排除sno=1的同学
SELECT DISTINCT stu.*
FROM student stu
    JOIN sc s ON stu.sno = s.sno AND stu.sno <> 1
WHERE s.cno IN (
    SELECT cno
    FROM sc
    WHERE sno = 1
);

-- 方法二：
SELECT stu.*
FROM student stu
WHERE stu.sno IN (
    SELECT DISTINCT s1.sno
    FROM sc s1
    WHERE s1.cno IN (
        SELECT s2.cno
        FROM sc s2
        WHERE s2.sno = 1
    ) 
)AND stu.sno <> 1;

13、查询和"1"号的学生学习的课程完全相同的其他学生的信息

-- 查询1号学生所学的课程
SELECT cno
FROM sc 
WHERE sno = 1;
-- 查询1号学生所学的课程的总数
SELECT COUNT(*)
FROM sc
WHERE sno = 1;
-- 查询所学的课程以及课程总数符合上述结果集的学生
SELECT stu.*
FROM student stu
    JOIN sc s ON stu.sno = s.sno
WHERE s.cno IN (
    SELECT cno
    FROM sc 
    WHERE sno = 1
)
AND stu.sno <> 1
GROUP BY stu.sno
HAVING COUNT(s.cno) = (
    SELECT COUNT(*)
    FROM sc
    WHERE sno = 1
);

-- 方法二
SELECT stu.*
FROM student stu
WHERE stu.sno IN (
    SELECT sno
    FROM (
        SELECT *
        FROM sc s
        WHERE s.cno IN (
            SELECT cno
            FROM sc
            WHERE sno = 1
        )
    ) t
    GROUP BY t.sno
    HAVING COUNT(cno) = (
        SELECT COUNT(cno)
        FROM sc s
        WHERE sno = 1
    )
)
AND stu.sno <> 1;

14、查询没学过"张三"老师讲授的任一门课程的学生姓名

-- 查询张三老师的教师编号
SELECT tno
FROM teacher
WHERE tname = '张三';
-- 查询张三老师教授的课程
SELECT cno
FROM course
WHERE tno = (
    SELECT tno
    FROM teacher
    WHERE tname = '张三'
);
-- 查询学过李建民老师的课程的学生编号
SELECT sno
FROM sc
WHERE cno IN (
    SELECT cno
    FROM course
    WHERE tno IN (
        SELECT tno
        FROM teacher
        WHERE tname='张三'
    )
);
-- 完整查询：
SELECT *
FROM student stu
WHERE stu.sno NOT IN (
    SELECT sno
    FROM sc
    WHERE cno = (
        SELECT cno
        FROM course
        WHERE tno = (
            SELECT tno
            FROM teacher
            WHERE tname='张三'
        )
    )
);

-- 方法二：
SELECT *
FROM student stu
WHERE stu.sno NOT IN (
    SELECT s.sno
    FROM (
        SELECT c.cno, c.cname
        FROM teacher t, course c
        WHERE t.tno = c.tno AND t.tname = "张三"
    ) t, sc s
    WHERE t.cno = s.cno
);

-- 方法三：
SELECT *
FROM student stu
WHERE stu.sno NOT IN (
    SELECT s.sno
    FROM sc s
        LEFT JOIN course c ON s.cno = c.cno
        LEFT JOIN teacher t ON c.tno = t.tno
    WHERE t.tname = "张三"
);

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT stu.sno, stu.sname,  AVG(s.score) AS avgScore
FROM student stu
    JOIN sc s ON stu.sno = s.sno
WHERE s.score < 60
GROUP BY stu.sno
HAVING COUNT(s.cno) >= 2;

16、检索"1"课程分数小于60，按分数降序排列的学生信息

SELECT stu.*
FROM student stu
    JOIN sc s ON stu.sno = s.sno
WHERE s.cno = 1 AND s.score < 60
ORDER BY s.score DESC;

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

-- 1）要求显示所有学生，因此需要使用外联
-- 2）平均成绩：理解为每个学生的所有课程的平均成绩
-- 查询每个学生的平均成绩
SELECT stu.sno, AVG(s.score) AS avgScore
FROM student stu
    LEFT JOIN sc s ON stu.sno = s.sno
GROUP BY stu.sno;
-- 查询所有学生的所有课程成绩并与上面的平均成绩表连接
SELECT stu.sno, stu.sname, s.cno, s.score, t.avgScore
FROM student stu
    LEFT JOIN sc s ON stu.sno = s.sno
    LEFT JOIN (
        SELECT stu.sno, AVG(s.score) AS avgScore
        FROM student stu
            LEFT JOIN sc s ON stu.sno = s.sno
        GROUP BY stu.sno
    ) t
    ON s.sno = t.sno
ORDER BY t.avgScore DESC;


-- 方法二：
SELECT stu.*, t2.cno, t2.score, t2.avgScore
FROM student stu
    JOIN (
        SELECT s.sno, s.cno, s.score, t.avgScore
        FROM sc s
            LEFT JOIN (
                SELECT s2.sno, AVG(s2.score) avgScore
                FROM sc s2
                GROUP BY s2.sno
            ) t
            ON s.sno = t.sno
    ) t2
    ON stu.sno = t2.sno
ORDER BY t2.avgScore DESC;


-- 方法三：
SELECT s.sno, 
    (SELECT score FROM sc WHERE sno = s.sno AND cno = 1) AS "语文",
    (SELECT score FROM sc WHERE sno = s.sno AND cno = 2) AS "数学",
    (SELECT score FROM sc WHERE sno = s.sno AND cno = 3) AS "英语",
    AVG(s.score) AS avgScore
FROM sc s
GROUP BY s.sno
ORDER BY avgScore DESC;

18、查询各科成绩最高分、最低分和平均分：

-- 以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
-- 方法一：
SELECT c.cno, c.cname, 
    COUNT(s.cno) AS "选修人数",
    MAX(s.score) AS "最高分", 
    MIN(s.score) AS "最低分", 
    AVG(s.score) AS "平均分",
    SUM(CASE WHEN s.score >= 60 THEN 1 ELSE 0 END)/COUNT(1) AS "及格率",
    SUM(CASE WHEN s.score BETWEEN 70 AND 80 THEN 1 ELSE 0 END)/COUNT(1) AS "中等率",
    SUM(CASE WHEN s.score BETWEEN 80 AND 90 THEN 1 ELSE 0 END)/COUNT(1) AS "优良率",
    SUM(CASE WHEN s.score >= 90 THEN 1 ELSE 0 END)/COUNT(1) AS "优秀率"
FROM course c
    JOIN sc s ON c.cno = s.cno
GROUP BY c.cno, c.cname
ORDER BY COUNT(s.cno) DESC, c.cno;

-- 方法二：
SELECT c.cno, c.cname,
    (SELECT MAX(score) FROM sc WHERE cno = c.cno) AS "最高分",
    (SELECT MIN(score) FROM sc WHERE cno = c.cno) AS "最低分",
    (SELECT CAST(AVG(score) AS DECIMAL(18,2)) FROM sc WHERE cno = c.cno) AS "平均分",
    (SELECT COUNT(1) FROM sc WHERE cno = c.cno AND score >= 60)/(SELECT COUNT(1) FROM sc WHERE cno = c.cno) "及格率",
    (SELECT COUNT(1) FROM sc WHERE cno = c.cno AND score BETWEEN 70 AND 80)/(SELECT COUNT(1) FROM sc WHERE cno = c.cno) "中等率",
    (SELECT COUNT(1) FROM sc WHERE cno = c.cno AND score BETWEEN 80 AND 90)/(SELECT COUNT(1) FROM sc WHERE cno = c.cno) "优良率",
    (SELECT COUNT(1) FROM sc WHERE cno = c.cno AND score >= 90)/(SELECT COUNT(1) FROM sc WHERE cno = c.cno) "优秀率"
FROM course c;

-- 方法三：
SELECT t.cno,
    COUNT(t.sno),
    MAX(t.score),
    MIN(t.score),
    AVG(t.score),
    SUM(及格)/COUNT(t.sno) AS 及格率, 
    SUM(中等)/COUNT(t.sno) AS 中等率,
    SUM(优良)/COUNT(t.sno) AS 优良率,
    SUM(优秀)/COUNT(t.sno) AS 优秀率
FROM (
    SELECT *,
        CASE WHEN score >= 60 THEN 1 ELSE 0 END AS "及格",
        CASE WHEN score BETWEEN 70 AND 80 THEN 1 ELSE 0 END AS "中等",
        CASE WHEN score BETWEEN 80 AND 90 THEN 1 ELSE 0 END AS "优良",
        CASE WHEN score >= 90 THEN 1 ELSE 0 END AS "优秀"
    FROM sc
) t
GROUP BY t.cno
ORDER BY COUNT(t.sno) DESC, t.cno;

19、按各科成绩进行排序，并显示排名

SELECT s.*, 
    (SELECT COUNT(1) FROM sc WHERE cno = s.cno AND score > s.score) + 1 AS px
FROM sc s
ORDER BY s.score;

20、查询学生的总成绩并进行排名

SELECT t.*, @rank:=@rank + 1 AS rank
FROM (
    SELECT s.sno, SUM(score)
    FROM sc s
    GROUP BY s.sno
    ORDER BY SUM(score) DESC
) t, (SELECT @rank:=0) r;

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

SELECT t.tno, t.tname, c.cname, AVG(s.score)
FROM sc s, course c, teacher t
WHERE s.cno = c.cno AND c.tno = t.tno
GROUP BY s.cno
ORDER BY AVG(s.score) DESC;

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

-- 先查询每个课程的成绩第2名到第3名的学生信息及该课程成绩
-- 然后使用union 联合
SELECT st.*, t.cno, t.score
FROM student st
    JOIN (
        SELECT *
        FROM sc s WHERE s.cno = 1
        ORDER BY s.score DESC
        LIMIT 1,2
    ) t
    ON st.sno = t.sno
UNION
SELECT st.*, t.cno, t.score
FROM student st
    JOIN (
        SELECT *
        FROM sc s WHERE s.cno = 2
        ORDER BY s.score DESC
        LIMIT 1,2
    ) t
    ON st.sno = t.sno
UNION
SELECT st.*, t.cno, t.score
FROM student st
    JOIN (
        SELECT *
        FROM sc s WHERE s.cno = 3
        ORDER BY s.score DESC
        LIMIT 1,2
    ) t
    ON st.sno = t.sno;

23、统计各科成绩各分数段人数：

-- 课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT s.cno, c.cname,
    SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS '[100-85]',
    SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END)/COUNT(s.sno) AS '[100-85]百分比',
    SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS '[85-70]',
    SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END)/COUNT(s.sno) AS '[85-70]百分比',
    SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS '[70-60]',
    SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END)/COUNT(s.sno) AS '[70-60]百分比',
    SUM(CASE WHEN score BETWEEN 0 AND 60 THEN 1 ELSE 0 END) AS '[60-0]',
    SUM(CASE WHEN score BETWEEN 0 AND 60 THEN 1 ELSE 0 END)/COUNT(s.sno) AS '[60-0]百分比'
FROM course c
    JOIN sc s ON c.cno = s.cno
GROUP BY s.cno, c.cname;

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

SELECT t.*, @rownum:=@rownum + 1 AS rowNum
FROM (
    SELECT s.sno, AVG(s.score) AS avgScore
    FROM sc s
    GROUP BY s.sno
    ORDER BY avgScore DESC
) t, (SELECT @rownum:=0) r;

25、查询各科成绩前三名的记录

-- 思路：
-- 1.选出s2表比s1表成绩大的所有组
-- 2.选出比当前id成绩大的 小于三个的
SELECT s1.sno, s1.cno, s1.score
FROM sc s1
    LEFT JOIN sc s2 ON s1.cno = s2.cno AND s1.score < s2.score
GROUP BY s1.sno, s1.cno, s1.score
HAVING COUNT(s2.score) < 3
ORDER BY s1.cno, s1.score DESC;

26、查询每门课程被选修的学生数

-- 方法一：显示课程名称
SELECT c.cno, c.cname, COUNT(*) AS "被选修次数"
FROM course c
    LEFT JOIN sc s ON c.cno = s.cno
GROUP BY c.cno;

-- 方法二：只显示课程号
SELECT cno, COUNT(sno) AS "被选修次数"
FROM sc 
GROUP BY cno;

27、查询出只有两门课程的全部学生的学号和姓名

-- 方法一：
SELECT st.sno, st.sname
FROM student st
WHERE st.sno IN (
    SELECT sno
    FROM sc s
    GROUP BY s.sno
    HAVING COUNT(s.cno) = 2
);

-- 方法二：
SELECT stu.sno, stu.sname
FROM student stu
    JOIN (
        SELECT sno, COUNT(*) AS c_count
        FROM sc
        GROUP BY sno
    ) t
    ON stu.sno = t.sno
WHERE t.c_count = 2;

28、查询男生、女生人数

-- 方法一：
SELECT gender, COUNT(*) AS "性别"
FROM student stu
GROUP BY gender;

-- 方法二：
SELECT st.gender, COUNT(*) FROM student st WHERE st.gender = "男"
UNION
SELECT st.gender, COUNT(*) FROM student st WHERE st.gender = "女";

29、查询名字中含有"风"字的学生信息

SELECT *
FROM student st
WHERE st.sname LIKE "%风%";

30、查询同名同姓学生名单，并统计同名人数

-- 方法一：
SELECT a.*, b.sameNum
FROM student a
    LEFT JOIN (
        SELECT sname, gender, COUNT(*) AS sameNum
        FROM student
        GROUP BY sname, gender
    ) b
    ON a.sname = b.sname AND a.gender = b.gender
WHERE b.sameNum > 1;

-- 方法二：
SELECT st.sname, st.gender, COUNT(*) countNum
FROM student st
GROUP BY st.sname, st.gender
HAVING countNum >= 2;

31、查询1990年出生的学生名单(注：student表中birth列的类型是datetime)

-- 方法一：
SELECT *
FROM student st
WHERE YEAR(birth) = 1990;

-- 方法二：
SELECT *
FROM student st
WHERE birth LIKE "1990%";

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

SELECT cno, AVG(score) AS avgScore
FROM sc
GROUP BY cno
ORDER BY avgScore DESC, cno;

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

-- 方法一：使用分组
SELECT stu.sno, stu.sname, AVG(score) avgScore
FROM student stu
    JOIN sc s ON stu.sno = s.sno
GROUP BY stu.sno
HAVING avgScore > 85;

-- 方法二：使用子查询
SELECT stu.sno, stu.sname, t.avgScore 
FROM student stu
    LEFT JOIN (
        SELECT sno, AVG(score) AS avgScore 
        FROM sc 
        GROUP BY sno
    ) t
    ON stu.sno = t.sno
WHERE t.avgScore > 85;

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数

-- 方法一：
SELECT stu.sname, s.score
FROM student stu
    JOIN sc s ON stu.sno = s.sno
WHERE s.cno = (
    SELECT cno
    FROM course
    WHERE cname = "数学"
) AND s.score < 60;

-- 方法二：
SELECT stu.sname, t.score
FROM (
    SELECT *
    FROM sc
    WHERE cno = (
        SELECT cno
        FROM course
        WHERE cname = "数学"
    ) AND score < 60  
) t
LEFT JOIN student stu ON t.sno = stu.sno;

35、查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

-- 方法一：显示课程名称
SELECT stu.*, c.cno, c.cname, s.score
FROM student stu
    LEFT JOIN sc s ON stu.sno = s.sno
    LEFT JOIN course c ON s.cno = c.cno
ORDER BY stu.sno;

-- 方法二：只显示课程编号
SELECT stu.*, s.cno, s.score
FROM student stu
    LEFT JOIN sc s ON stu.sno = s.sno;

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

-- 方法一：
SELECT stu.sname, c.cname, s.score
FROM student stu
    JOIN sc s ON stu.sno = s.sno
    JOIN course c ON s.cno = c.cno
WHERE s.score > 70
ORDER BY stu.sno, c.cname;

-- 方法二：
SELECT stu.sname, d.cname, d.score
FROM (
    SELECT b.*, c.cname
    FROM (
        SELECT *
        FROM sc
        WHERE score > 70
    ) b
        LEFT JOIN course c ON b.cno = c.cno
) d
    LEFT JOIN student stu ON d.sno = stu.sno
ORDER BY stu.sno, d.cname;

-- 方法三：
SELECT st.sname, c.cname, s.score
FROM student st, course c, sc s
WHERE st.sno = s.sno AND c.cno = s.cno AND s.score > 70;

37、查询不及格的课程

SELECT DISTINCT c.cname
FROM course c
    JOIN sc s ON c.cno = s.cno 
WHERE s.score < 60;

38、查询课程编号为1且课程成绩在80分以上的学生的学号和姓名

-- 方法一：先连接再过滤
SELECT stu.sno, stu.sname
FROM student stu
    JOIN sc s ON stu.sno = s.sno
WHERE s.cno = 1 AND s.score >= 80;

-- 方法二：先过滤再连接
SELECT stu.sno, stu.sname
FROM (
    SELECT *
    FROM sc
    WHERE score >= 80 AND cno = 1
) t
LEFT JOIN student stu ON t.sno = stu.sno;

-- 方法三：
SELECT st.sno, st.sname
FROM student st, course c, sc s
WHERE st.sno = s.sno AND c.cno = s.cno AND c.cno = 1 AND s.score >= 80;

39、求每门课程的学生人数

SELECT cno, COUNT(*)
FROM sc 
GROUP BY cno;

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

-- 方法一：
SELECT st.*, s.score
FROM student st
    JOIN sc s ON st.sno = s.sno
    WHERE s.cno = (
        SELECT cno
        FROM course c
        WHERE c.tno = (
            SELECT tno
            FROM teacher t
            WHERE t.tname = "张三"
        )
    )
ORDER BY s.score DESC
LIMIT 1;

-- 方法二：
SELECT stu.*, s.score
FROM student stu, sc s, course c, teacher t
WHERE stu.sno = s.sno AND s.cno = c.cno AND c.tno = t.tno AND t.tname = "张三"
ORDER BY s.score DESC
LIMIT 1;

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

SELECT DISTINCT s1.*
FROM sc s1
    JOIN sc s2 ON s1.sno = s2.sno
WHERE s1.score = s2.score AND s1.cno <> s2.cno;

42、查询每门功成绩最好的前两名

-- 方法一：
SELECT s1.*
FROM sc s1
WHERE (
    SELECT COUNT(1)
    FROM sc s2
    WHERE s1.cno = s2.cno AND s1.score <= s2.score
) <= 2
ORDER BY s1.cno;

-- 方法二：
SELECT a.sno, a.cno
FROM sc AS a
    LEFT JOIN sc b ON a.cno = b.cno AND a.score < b.score
GROUP BY a.sno, a.cno
HAVING COUNT(b.cno) < 2
ORDER BY a.cno;

43、统计每门课程的学生选修人数（超过5人的课程才统计）。

-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT s.cno, COUNT(*) AS c_count
FROM sc s
GROUP BY s.cno
HAVING c_count > 5
ORDER BY c_count DESC, s.cno;

44、检索至少选修两门课程的学生学号

SELECT sno
FROM sc
GROUP BY sno
HAVING COUNT(cno) >= 2;

45、查询选修了全部课程的学生信息

-- 方法一：
SELECT st.*
FROM student st, sc s
WHERE st.sno = s.sno
GROUP BY s.sno
HAVING COUNT(*) = (
    SELECT DISTINCT COUNT(*)
    FROM course
);

-- 方法二：
SELECT stu.*
FROM student stu
    LEFT JOIN sc s ON stu.sno = s.sno
GROUP BY stu.sno
HAVING COUNT(cno) = (
    SELECT DISTINCT COUNT(*)
    FROM course
);

46、查询各学生的年龄

-- 只按年份来算
SELECT
    sno, sname,
    YEAR(NOW()) - YEAR(birth) AS "年龄"
FROM student;

-- 按照出生日期来算，当前月日 < 出生年月的月日则年龄减一
-- 获取当前月日
SELECT DATE_FORMAT(CURRENT_DATE, '%m-%d');
-- 完整语句
SELECT
    stu.sno,
    stu.sname,
    stu.birth,
    YEAR(NOW()) - YEAR(birth) AS "按年计算的年龄", 
    CASE 
        WHEN DATE_FORMAT(CURRENT_DATE, '%m-%d') < DATE_FORMAT(birth, '%m-%d')
        THEN YEAR(NOW()) - YEAR(birth) - 1 
        ELSE YEAR(NOW()) - YEAR(birth) 
        END AS "按日期计算的年龄"
FROM student stu;

47、查询本周过生日的学生

-- 获取第几周
SELECT WEEK(CURRENT_DATE);
-- 查询是否本周过生日
SELECT 
    stu.sname,
    stu.birth,
    WEEK(birth) AS "出生在第几周",
    WEEK(CURRENT_DATE, 1) AS "当前周",
    WEEK(birth) = WEEK(CURRENT_DATE, 1) AS "是否本周过生日"
FROM student stu;
-- 方法一：
SELECT st.*
FROM student st
WHERE WEEK(birth) = WEEK(NOW(), 1);

48、查询下周过生日的学生

SELECT st.*
FROM student st
WHERE WEEK(birth) = WEEK(NOW(), 1) + 1;

49、查询本月过生日的学生

SELECT st.*
FROM student st
WHERE MONTH(birth) = MONTH(NOW());

50、查询下月过生日的学生

SELECT st.*
FROM student st
WHERE MONTH(birth) = MONTH(NOW()) + 1;

更新日期：2020年5月23日