【面试题】MYSQL查询每个学生所选择的课程和选择了所有课程的学生

– 创建学生表
CREATE TABLE student (
Sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20)
);

• 创建课程表
  CREATE TABLE course(
  Cid INT PRIMARY KEY AUTO_INCREMENT,
  cname VARCHAR(20)
  );
  – 创建学生表和课程表的三方外键关联表
  CREATE TABLE stu_course(
  sid INT,
  cid INT,
  FOREIGN KEY(sid)REFERENCES student (sid),
  FOREIGN KEY(cid)REFERENCES course(cid)
  );
  – 向学生表中插入数据
  INSERT INTO student VALUES(NULL,‘张三’),(NULL,‘李思思’),(NULL,‘李虎’);
  – 向课程表中插入数据
  INSERT INTO course (cname)VALUES (’ JAVA’),(’ ANDROID’), (’ IOS’), (’ PHP’), (’ C++');
  –向三方外键关联表中插入数据、
  INSERT INTO stu_course (sid, cid) VALUES (1, 1);
  INSERT INTO stu_course (sid, cid) VALUES (1, 3);
  INSERT INTO stu_course (sid, cid) VALUES (1, 5);
  INSERT INTO stu_course (sid, cid) VALUES (1, 2);
  INSERT INTO stu_course (sid, cid) VALUES (1, 4);
  INSERT INTO stu_course (sid, cid) VALUES (2, 1);
  INSERT INTO stu_course (sid, cid) VALUES (2, 2);
  INSERT INTO stu_course (sid, cid) VALUES (3, 2);
  INSERT INTO stu_course (sid, cid) VALUES (3, 3);
  INSERT INTO stu_course (sid, cid) VALUES (3, 5);

问题:
(1)查询每个学生所选择的课程(5分)

(2)选择了所有课程的学生(6分)

解决：

1、先观察表数据

(1)查询每个学生所选择的课程(5分)

SELECT s.sname AS 学生姓名, c.cname AS 所选课程
FROM student s
JOIN stu_course sc ON s.Sid = sc.sid
JOIN course c ON sc.cid = c.Cid;

注释：

– 选择每个学生所选的课程，通过连接学生表、三方外键关联表和课程表
SELECT s.sname AS 学生姓名, c.cname AS 所选课程

FROM student s – 从学生表中选择数据，将其别名为s

JOIN stu_course sc ON s.Sid =sc.sid – 将学生表和三方外键关联表连接，使用学生ID进行匹配

JOIN course c ON sc.cid = c.Cid; – 将课程表和三方外键关联表连接，使用课程ID进行匹配

(2)选择了所有课程的学生(6分)

SELECT s.sname AS 学生姓名
FROM student s
WHERE NOT EXISTS (
    SELECT c.Cid
    FROM course c
    WHERE NOT EXISTS (
        SELECT sc.cid
        FROM stu_course sc
        WHERE sc.cid = c.Cid AND sc.sid = s.Sid
    )
);

注释：

SELECT s.sname AS 学生姓名  -- 选择学生表中的学生姓名
FROM student s  -- 从学生表中选择数据，将其别名为s
WHERE NOT EXISTS (  -- 判断以下子查询是否为空集合
    SELECT c.Cid  -- 选择课程表中的课程ID
    FROM course c  -- 从课程表中选择数据，将其别名为c
    WHERE NOT EXISTS (  -- 判断以下子查询是否为空集合
        SELECT sc.cid  -- 选择三方外键关联表中的课程ID
        FROM stu_course sc  -- 从三方外键关联表中选择数据，将其别名为sc
        WHERE sc.cid = c.Cid AND sc.sid = s.Sid  -- 匹配学生ID和课程ID
    )
);

第二种解法：推荐

SELECT 
    sc.sid AS StudentID,
    s.sname AS StudentName
FROM 
    stu_course sc
JOIN 
    student s ON sc.sid = s.sid
GROUP BY 
    sc.sid
HAVING 
    COUNT(sc.cid) = (SELECT COUNT(*) FROM course);

注释：
这个查询语句首先通过stu_course关联表和student表连接来获取所有选课的学生。然后，通过GROUP BY对学生ID进行分组，并使用HAVING子句来确保只有选择了与course表中课程总数一样多的课程的学生被选出。子查询(SELECT COUNT(*) FROM course)用于获取课程的总数。

第三种解法：

SELECT 
    s.sname AS StudentName
FROM 
    student s
JOIN 
    stu_course sc ON s.sid = sc.sid
GROUP BY 
    s.sid, s.sname
HAVING 
    COUNT(sc.cid) = (SELECT COUNT(DISTINCT cid) FROM course);

注释：
这个查询语句的逻辑是：
从student表中选择学生姓名。
通过JOIN操作与stu_course表关联，以获取每个学生所选的课程。
使用GROUP BY对学生ID和姓名进行分组，因为我们需要按学生来筛选。
使用HAVING子句来筛选出那些选课数量等于总课程数量的学生。这里使用子查询(SELECT COUNT(DISTINCT cid) FROM course)来获取course表中不同课程的总数。
请注意，COUNT(DISTINCT cid)确保我们统计的是不同的课程数量，而不是课程的总记录数（如果有重复的课程ID则需要去重）。

第四种解法：

SELECT 
    s.sname AS StudentName
FROM 
    student s
WHERE 
    NOT EXISTS (
        SELECT 1
        FROM course c
        WHERE NOT EXISTS (
            SELECT 1
            FROM stu_course sc
            WHERE sc.sid = s.sid AND sc.cid = c.cid
        )
    );

注释：

1. 从student表中选择学生姓名。
2. 对于每个学生，使用NOT EXISTS子句来检查是否存在至少一门课程，该学生没有选。
3. 内层的NOT EXISTS子句检查course表中的每一门课程，看是否在stu_course表中与当前学生的ID关联。
4. 如果对于course表中的任何课程，都没有找到与当前学生ID关联的记录，那么这个学生就没有选择所有课程，因此这个子句的结果为TRUE，外层的SELECT将排除这个学生。
5. 只有当所有课程都被至少选了一次时，NOT EXISTS子句的结果才为FALSE，这样学生才会被包括在外层查询的结果中。

这种方法使用了逻辑上的否定，通过排除那些没有选择所有课程的学生来找出那些选择了所有课程的学生。