MYSQL常见25题【适合入门】

糖逗 · 发表于 2020-6-6 02:13:33

马上注册，结交更多好友，享用更多功能^_^

您需要登录才可以下载或查看，没有账号？立即注册

x

本帖最后由糖逗于 2020-6-6 02:16 编辑

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

糖逗 · 发表于 2020-6-6 02:14:18

Rorbort · 发表于 2020-7-2 09:14:49

很详细谢谢

账号		自动登录	找回密码
密码			立即注册

MYSQL常见25题【适合入门】

马上注册，结交更多好友，享用更多功能^_^

本帖被以下淘专辑推荐: