几道简单的 MySQL 面试题

需求

领导让我出几个 MySQL 的面试题，搞硬件运维的，会几个简单的就行，所以上网搜罗自己整理了一下。

现有数据

CREATE DATABASE IF NOT EXISTS TEST DEFAULT CHARSET UTF8MB4 COLLATE UTF8MB4_GENERAL_CI;
USE TEST;
CREATE TABLE IF NOT EXISTS STUDENT (
    ID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    NAME CHAR(10) NOT NULL
);
INSERT INTO STUDENT(NAME) VALUES('张三'),('李四'),('李四'),('王五'),('郑六'),('张三')('二麻子');
CREATE TABLE IF NOT EXISTS COURSE(
ID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
NAME CHAR(20) NOT NULL
);
INSERT INTO COURSE(NAME) VALUES('语文'),('数学');
CREATE TABLE IF NOT EXISTS STUDENT_COURSE(
    SID INT UNSIGNED,
    CID INT UNSIGNED,
    SCORE INT UNSIGNED NOT NULL,
    FOREIGN KEY (SID) REFERENCES STUDENT(ID),
    FOREIGN KEY (CID) REFERENCES COURSE(ID),
    PRIMARY KEY(SID, CID)
);
INSERT INTO STUDENT_COURSE VALUES(1,1,80),(1,2,90),(2,1,90),(2,2,70),(3,1,80),(3,2,90),(4,1,90),(4,2,70),(5,1,80),(5,2,90),(6,1,90),(6,2,70);

题目

查询重名的学生ID和名字

SELECT ID,NAME FROM TEST.STUDENT
WHERE NAME IN
(SELECT NAME FROM TEST.STUDENT GROUP BY NAME HAVING(COUNT(NAME)>1))
ORDER BY NAME;
'''2、查询所有学生是否及格'''
SELECT ID,NAME,AVG(SCORE) AS `AVG_SCORE`,(CASE WHEN AVG(SCORE) < 60 THEN 'NO' ELSE 'YES' END) AS `IS_PASS`
FROM TEST.STUDENT AS T1, TEST.STUDENT_COURSE AS T2
WHERE T1.ID = T2.SID
GROUP BY (NAME)
'''3、查询所有科目成绩均大于80分的学生'''
SELECT DISTINCT ID,NAME FROM TEST.STUDENT AS T1,TEST.STUDENT_COURSE AS T2
WHERE T1.ID = T2.SID
AND SID NOT IN (
    SELECT SID FROM TEST.STUDENT_COURSE
    WHERE SCORE < 80
);

查询每个学生的总成绩，结果列出学生姓名和总成绩 如果使用下面的sql会过滤掉没有成绩的人

SELECT NAME,SUM(SCORE) TOTAL
FROM TEST.STUDENT T1,TEST.STUDENT_COURSE T2
WHERE T1.ID = T2.SID
GROUP BY SID;

更保险的做法应该是使用 左外连接

SELECT NAME,SUM(SCORE)
FROM TEST.STUDENT T1 LEFT JOIN TEST.STUDENT_COURSE T2
ON T1.ID = T2.SID
GROUP BY SID;