MYSQL常见25题【适合入门】

糖逗

USE test50; #定位在该数据库下进行操作
#创建表的结构
DROP TABLE IF EXISTS student;
CREATE TABLE student(
        学号 VARCHAR(255) PRIMARY KEY,
        姓名 VARCHAR(255) NOT NULL,
        出生日期 DATE NOT NULL,
        性别 VARCHAR(255) NOT NULL
);
DESC student;

DROP TABLE IF EXISTS score;
CREATE TABLE score(
        学号 VARCHAR(255),
        课程号 VARCHAR(255),
        成绩 FLOAT(3) NOT NULL,
        PRIMARY KEY(学号, 课程号)
);
DESC score;

DROP TABLE IF EXISTS course;
CREATE TABLE course(
        课程号 VARCHAR(255) PRIMARY KEY,
        课程名称 VARCHAR(255) NOT NULL,
        教师号 VARCHAR(255) NOT NULL
);
DESC course;

DROP TABLE IS EXISTS teacher;
CREATE TABLE teacher(
        教师号 VARCHAR(20) PRIMARY KEY;
        教师姓名 VARCHAR(20),
);
DESC teacher;

#向表中添加数据
INSERT INTO student
VALUES('0001' , '猴子' , '1989-01-01' , '男'),
('0002' , '猴子' , '1990-12-21' , '女'),
('0003' , '马云' , '1991-12-21' , '男'),
('0004' , '王思聪' , '1990-05-20' , '男');

INSERT INTO score
VALUES('0001' , '0001' , 87),
('0001' , '0002' , 45),
('0001' , '0003' , 45),
('0002' , '0002' , 99),
('0002' , '0003' , 98),
('0003' , '0001' , 88),
('0003' , '0002' , 80),
('0003' , '0003' , 80);
('0003' , '0003' , 23);


INSERT INTO course
VALUES('0001' , '语文' , '0002'),
('0002' , '数学' , '0001'),
('0003' , '英语' , '0003');


INSERT INTO teacher
VALUES('0001' , '孟扎扎'),
('0002' , '马化腾'),
('0003' , NULL),
('0004' , '');



#1.查询姓“孟”老师的个数
SELECT COUNT(*) AS 老师人数
FROM teacher 
WHERE 教师姓名 LIKE "孟%";


#2.查询课程编号为“0002”的总成绩
SELECT SUM(成绩)
FROM score
WHERE 课程号 = '0002';

#3.查询选了课程的学生人数
SELECT COUNT(DISTINCT 学号) 
FROM score;

#4.查询各科成绩最高和最低的分， 以如下的形式显示：课程号，最高分，最低分
SELECT 课程号, MAX(成绩) AS 最高分, MIN(成绩) AS 最低分
FROM score
GROUP BY 课程号;

#5.查询每门课程被选修的学生数
SELECT 课程号, COUNT(DISTINCT 学号)AS 学生人数
FROM score 
GROUP BY 课程号;

#6.查询男生、女生人数
SELECT 性别, COUNT(学号) AS 人数
FROM student
GROUP BY 性别;

#7.查询平均成绩大于60分学生的学号和平均成绩
SELECT 学号, ROUND(AVG(成绩), 1) AS 平均成绩
FROM score
GROUP BY 学号
HAVING AVG(成绩) > 60;

#8.查询至少选修两门课程的学生学号
SELECT 学号
FROM score
GROUP BY 学号
HAVING COUNT(DISTINCT 课程号) >= 2;

#9.查询同名同性学生名单并统计同名人数
SELECT 姓名, COUNT(*)
FROM student
GROUP BY 姓名
HAVING COUNT(*) > 1;

#10.查询不及格的课程并按课程号从大到小排列
SELECT 课程号, 成绩
FROM score
WHERE 成绩 < 60
ORDER BY 课程号 DESC;

#11.查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
SELECT 课程号, ROUND(AVG(成绩), 1) AS 平均成绩
FROM score
GROUP BY 课程号
ORDER BY AVG(成绩) ASC, 课程号 DESC;

#12.检索课程编号为“0004”且分数小于60的学生学号，结果按按分数降序排列
SELECT 学号, 成绩
FROM score
WHERE 课程号 = '0004' AND 成绩 < 60
ORDER BY 成绩 DESC;


#13.统计每门课程的学生选修人数(超过2人的课程才统计)要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序
SELECT 课程号, COUNT(学号) AS 选修人数
FROM score
GROUP BY 课程号
HAVING COUNT(学号) > 2
ORDER BY 选修人数 DESC, 课程号 ASC;

#14.查询两门以上不及格课程的同学的学号及其平均成绩
#（1）筛选出不及格超过2门的同学学号
SELECT 学号
FROM score
WHERE 成绩 < 60
GROUP BY 学号
HAVING COUNT(课程号) > 2;
#（2）得到最终结果
SELECT 学号, ROUND(AVG(成绩), 1) AS 平均成绩
FROM score
WHERE 学号 IN(
        SELECT 学号
        FROM score
        WHERE 成绩 < 60
        GROUP BY 学号
        HAVING COUNT(课程号) > 2
)
GROUP BY 学号;

#15.查询所有课程成绩小于60分学生的学号、姓名
#（1）查询所有成绩及格的学号
SELECT DISTINCT 学号
FROM score
WHERE 成绩 >= 60;
#（2）得到最终结果
SELECT DISTINCT s1.学号, 姓名
FROM student s1 RIGHT JOIN score s2
ON s1.学号 = s2.学号
WHERE s1.学号 NOT IN (
        SELECT DISTINCT 学号
        FROM score
        WHERE 成绩 >= 60
);

#16.查询没有学全所有课的学生的学号、姓名
#（1）查询所有课程数
SELECT COUNT(*)
FROM course;
#（2）查询没有学满所有课程的学生学号、姓名
SELECT s2.学号, 姓名
FROM score s1 RIGHT JOIN student s2
ON s1.学号 = s2.学号
GROUP BY s2.学号
HAVING COUNT(DISTINCT 课程号) < (SELECT COUNT(*)FROM course);

#17.查询出只选修了两门课程的全部学生的学号和姓名
SELECT s1.学号, 姓名
FROM score s1 LEFT JOIN student s2
ON s1.学号 = s2.学号
GROUP BY s1.学号
HAVING COUNT(课程号) = 2;


#18.1990年出生的学生名单
SELECT *
FROM student
WHERE YEAR(出生日期) = 1990;

#19.查询各科成绩前两名的记录(同分都算为一个等级)*****
#（1）查询各科成绩排名
SELECT s1.课程号, s1.成绩,COUNT(DISTINCT s2.成绩) + 1 AS 排名
FROM score s1 LEFT JOIN score s2
ON s1.课程号 = s2.课程号 AND s1.成绩 < s2.成绩 AND s1.学号 != s2.学号
GROUP BY s1.课程号, s1.成绩
ORDER BY 课程号 ASC, 成绩 DESC;
#（2）得到最终的结果
SELECT *
FROM score
WHERE (课程号, 成绩) IN (
        SELECT s1.课程号, s1.成绩
        FROM score s1 LEFT JOIN score s2
        ON s1.课程号 = s2.课程号 AND s1.成绩 < s2.成绩 AND s1.学号 != s2.学号
        GROUP BY s1.课程号, s1.成绩
        HAVING COUNT(DISTINCT s2.成绩) + 1 <= 2
        
)
ORDER BY 课程号 ASC, 成绩 DESC;


#20.查询所有学生的学号、姓名、选课数、总成绩
SELECT student.学号, 姓名, COUNT(DISTINCT 课程号) AS 选课数, IF(ISNULL(SUM(成绩)), 0, SUM(成绩)) AS 总成绩
FROM student LEFT JOIN score
ON student.学号 = score.学号
GROUP BY student.学号;

#21.查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT  score.学号, 姓名, AVG(成绩) AS 平均成绩
FROM student RIGHT JOIN score
ON student.学号 = score.学号
GROUP BY score.学号
HAVING AVG(成绩) > 85;


#22.查询学生的选课情况：学号，姓名，课程号，课程名称
SELECT score.学号, 姓名, score.课程号, 课程名称
FROM score LEFT JOIN student 
ON student.学号 = score.学号 
LEFT JOIN course
ON score.课程号 = course.课程号;

#23.查询出每门课程的及格人数和不及格人数 *****
SELECT score.课程号, 课程名称, SUM(IF(成绩 < 60, 1, 0)) AS 不及格人数
FROM score RIGHT JOIN course
ON score.课程号 = course.课程号
GROUP BY score.课程号, 课程名称;

#24.使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称
#（1）做分数段标记
SELECT 课程号, 成绩, CASE WHEN 成绩 >= 85 THEN "[100-85]" 
            WHEN 成绩 >= 70 THEN "[85-70]"
            WHEN 成绩 >= 60 THEN "[70-60]"
            ELSE "[<60]"
            END AS 成绩分段
FROM score
#（2）最终得到的结果
SELECT score.课程号, 课程名称, 
        CASE WHEN 成绩 >= 85 THEN "[100-85]" 
        WHEN 成绩 >= 70 THEN "[85-70]"
        WHEN 成绩 >= 60 THEN "[70-60]"
        ELSE "[<60]"
        END AS 成绩分段, COUNT(*) AS 分段人数
FROM score LEFT JOIN course
ON score.课程号 = course.课程号
GROUP BY score.课程号, 课程名称, 成绩分段;
                                
#25.查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名
SELECT score.学号, 姓名
FROM student RIGHT JOIN score
ON student.学号 = score.学号
WHERE 课程号 = "0003" AND 成绩 > 80

参考链接：https://zhuanlan.zhihu.com/p/38354000

糖逗

Rorbort

很详细谢谢