MySQL 练习50题
概述
学习的精髓 = 理论+实操+总结，所有题都亲手敲一遍才会牢记。
下面是我自己写的答案，有些不会写只能空着，以后再补。（建表的SQL语句在文章最后）
1、表结构
2、案例答案
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT
stu.*,
s1.s_score as 01_score,
s2.s_score as 02_score
FROM
Student as stu
left JOIN Score s1 on stu.s_id = s1.s_id
AND s1.c_id = '01'
left JOIN Score s2 on stu.s_id = s2.s_id
AND s2.c_id = '02'
or s2.c_id = NULL
where
s1.s_score > s2.s_score
-- 或者
SELECT
stu.*,
s1.s_score as 01_score,
s2.s_score as 02_score
FROM
Score as s1,
Score as s2,
Student as stu
WHERE
stu.s_id = s1.s_id
AND stu.s_id = s2.s_id
AND s1.c_id = '01'
AND s2.c_id = '02'
AND s1.s_score > s2.s_score
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT
stu.*,
s1.s_score as 01_score,
s2.s_score as 02_score
FROM
Student as stu
left JOIN Score s1 on stu.s_id = s1.s_id
AND s1.c_id = '01'
left JOIN Score s2 on stu.s_id = s2.s_id
AND s2.c_id = '02'
or s2.c_id = NULL
where
s1.s_score < s2.s_score
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT
s2.s_id,
s2.s_name,
ROUND(AVG(s1.s_score),2) as avg_score
FROM
Score AS s1
left JOIN Student AS s2 on s1.s_id = s2.s_id
GROUP BY
s2.s_id
HAVING AVG(s1.s_score) >=60
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
SELECT
s1.s_id,
s1.s_name,
ROUND(AVG(s2.s_score),2) avg_score
FROM
Student AS s1
LEFT JOIN Score s2 on s1.s_id = s2.s_id
GROUP BY
s1.s_id
HAVING AVG(s2.s_score) < 60
union
select
a.s_id,
a.s_name,0 as avg_score
from student a
where a.s_id not in (
select distinct s_id from score
);
-- union用于合并两个或多个结果集，每个结果集必须拥有相同数量的列、列必须拥有相似的数据类型、列的顺序必须相同。
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT
s1.s_id,
s1.s_name,
count(s2.c_id) as '课程数',
SUM(s2.s_score) as '总分'
FROM
Student s1
LEFT JOIN Score s2 on s1.s_id = s2.s_id
GROUP BY
s1.s_id
-- 6、查询"李"姓老师的数量
SELECT count(t.t_id) FROM Teacher t WHERE t.t_name LIKE '李%'
-- 7、查询学过"张三"老师授课的同学的信息
SELECT s1.*
FROM
Student s1,
Score s2
WHERE
s1.s_id = s2.s_id
AND s2.c_id in (
SELECT c.c_id
FROM
Teacher t,
Course c
where t.t_id = c.t_id
AND t.t_name = '张三'
)
-- 8、查询没学过"张三"老师授课的同学的信息
SELECT * FROM Student
WHERE s_id not in (
SELECT s1.s_id
from
Student s1
JOIN Score s2 on s1.s_id = s2.s_id
WHERE s2.c_id in (
select a.c_id
from course a
JOIN teacher b on a.t_id = b.t_id
where t_name ='张三'
)
)
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT stu.*
from
Student stu,
Score s1,
Score s2
WHERE
s1.c_id = 01
AND s2.c_id = 02
AND s1.s_id = stu.s_id
AND s2.s_id = stu.s_id
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT stu.*
FROM Student stu
WHERE
stu.s_id in (
SELECT s_id
FROM Score
where c_id = 01
)
AND stu.s_id not in (
SELECT s_id
FROM Score
where c_id = 02
)
-- 11、查询没有学全所有课程的同学的信息
SELECT stu.*
FROM Student stu
WHERE stu.s_id not in (SELECT s_id
FROM Score
GROUP BY s_id
HAVING count(*) = (
SELECT count(*) FROM Course
)
)
-- 或者
SELECT stu.*
FROM
Student stu
left join Score sc on stu.s_id = sc.s_id
GROUP BY
stu.s_id
HAVING count(sc.s_id) < (
SELECT count(*) FROM Course
)
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECT stu.*
FROM
Student stu,
Score sc
where
stu.s_id = sc.s_id
AND sc.c_id in (
SELECT sc.c_id
FROM Student stu
left join Score sc on stu.s_id = sc.s_id
where stu.s_id = '01'
)
GROUP BY stu.s_id HAVING stu.s_id not in ('01')
-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT stu.*
FROM Student stu
WHERE stu.s_id in ( -- 找到与‘01’同学课程数完全一样的同学
SELECT s_id FROM Score
GROUP BY s_id
HAVING count(s_id) = (
-- 找到'01'同学学习的课程数
SELECT COUNT(c_id) FROM Score WHERE s_id = '01'
)
)
AND stu.s_id not in ( -- 排除学了 '01'同学没学过的课程 的同学
SELECT s_id
FROM Score
WHERE c_id in (
SELECT c_id FROM Score
WHERE c_id not in (
-- 找出‘01’同学学习的课程
SELECT c_id FROM Score WHERE s_id = '01'
)
)
GROUP BY s_id
)
AND stu.s_id not in ('01') -- 排除 '01' 同学自己
-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT s_name
FROM Student
where s_id not in ( -- 排除学过‘张三’老师的课的学生
SELECT sc.s_id
FROM Score sc -- 找出学过‘张三’老是的课学生
where sc.c_id in(
select co.c_id
FROM Course co -- 找出‘张三’老师讲授的所有课程
left join Teacher t on t.t_id = co.c_id
where t.t_name = '张三'
)
)
-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT
stu.s_id,
stu.s_name,
sum(sc.s_score)/(SELECT count(c_id) FROM Course)
from
Student stu
left join Score sc on stu.s_id = sc.s_id
where stu.s_id in(
SELECT s_id
FROM Score
where s_score < 60
GROUP BY
s_id
HAVING count(*) >= 2
)
GROUP BY sc.s_id
-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT stu.*
FROM
Student stu
left join Score sc on stu.s_id = sc.s_id
and sc.c_id = 01
where stu.s_id in (
SELECT s_id
FROM Score
WHERE s_score < 60
)
ORDER BY
sc.s_score desc
-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT
stu.s_id '学号',
stu.s_name '姓名',
sum(
case sc.c_id when '01' then sc.s_score END
) '语文',
sum(
case sc.c_id when '02' then sc.s_score END
) '数学',
sum(
case sc.c_id when '03' then sc.s_score END
) '英语',
round(avg(sc.s_score),2) '平均分'
FROM
Student stu
left join Score sc on stu.s_id = sc.s_id
GROUP BY
stu.s_id
ORDER BY
6 DESC -- 根据第6列进行排序
-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
SELECT
co.c_id,
co.c_name,
max(sc.s_score) '最高分',
min(sc.s_score) '最低分',
sum(sc.s_score)/count(stu.s_id) '平均分',
round(100*count(
case when sc.s_score >= 60 then sc.s_id END
)/count(stu.s_id),2) '及格率(%)',
ROUND(100*count(
case when sc.s_score >= 70 AND sc.s_score < 80 then sc.s_id END
)/count(stu.s_id),2) '中等率(%)',
ROUND(100*count(
case when sc.s_score >= 80 AND sc.s_score < 90 then sc.s_id END
)/count(stu.s_id),2) '优良率(%)',
ROUND(100*count(
case when sc.s_score >= 90 then sc.s_id END
)/count(stu.s_id),2) '优秀率(%)'
FROM
Course co
left join Score sc on sc.c_id = co.c_id
left join Student stu on stu.s_id = sc.s_id
GROUP BY
sc.c_id
-- 20、查询学生的总成绩并进行排名
SELECT
stu.*,
SUM(sc.s_score) as '总分',
rank() OVER(ORDER BY SUM(sc.s_score) desc) as '排名'
FROM
Student stu
left join Score sc on stu.s_id = sc.s_id
GROUP BY
sc.s_id
ORDER BY SUM(sc.s_score) desc
-- 21、查询不同老师所教不同课程平均分从高到低显示
SELECT
co.c_name,
t.t_name,
ROUND(sum(sc.s_score)/count(co.c_id),2) '平均分'
FROM
Course co
left join Score sc on sc.c_id = co.c_id
left join Teacher t on t.t_id = co.t_id
GROUP BY
sc.c_id
ORDER BY 3 desc
-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT
co.c_id,
co.c_name,
count(
case when sc.s_score >= 85 then sc.s_id END
) as '[100-85]',
count(
case when sc.s_score >= 70 AND sc.s_score < 85 then sc.s_id END
) as '[85-70]',
count(
case when sc.s_score >= 60 AND sc.s_score < 70 then sc.s_id END
) as '[70-60]',
count(
case when sc.s_score < 60 then sc.s_id END
) as '[60-0]'
FROM
Course co
left join Score sc on co.c_id = sc.c_id
left join Student stu on stu.s_id = sc.s_id
GROUP BY
sc.c_id
-- 24、查询学生平均成绩及其名次
SELECT
avg(s_score) as '平均成绩',
rank() OVER(ORDER BY AVG(s_score) DESC) as '名次'
FROM
Score
GROUP BY
s_id
-- 25、查询各科成绩前三名的记录
SELECT
a.s_id,
a.c_id,
a.s_score
FROM
score a
LEFT JOIN score b ON a.c_id = b.c_id
AND a.s_score < b.s_score
GROUP BY
a.s_id,
a.c_id,
a.s_score
HAVING
COUNT( b.s_id ) < 3
ORDER BY
a.c_id,
a.s_score DESC
-- 26、查询每门课程被选修的学生数
SELECT
co.c_id '课程ID',
co.c_name '课程名',
count(sc.s_id) '选修人数'
FROM
Course co
LEFT JOIN Score sc on sc.c_id = co.c_id
LEFT JOIN Student stu on stu.s_id = sc.s_id
GROUP BY
sc.c_id
-- 27、查询出只有两门课程的全部学生的学号和姓名
SELECT
stu.s_id '学号',
stu.s_name '姓名'
FROM
Student stu
LEFT JOIN Score sc on sc.s_id = stu.s_id
GROUP BY
sc.s_id
HAVING
count(sc.c_id) = 2
-- 28、查询男生、女生人数
SELECT
s_sex as '性别',
count(s_sex) as '人数'
FROM
Student
GROUP BY
s_sex
-- 29、查询名字中含有"风"字的学生信息
SELECT * FROM Student WHERE s_name like '%风%';
-- 30、查询同名同性学生名单，并统计同名人数
SELECT
a.s_name,
a.s_sex,
count(*)
FROM
Student a
JOIN Student b on a.s_id <> b.s_id
and a.s_name = b.s_name
and a.s_sex = b.s_sex
GROUP BY
a.s_name,
a.s_sex
-- 31、查询1990年出生的学生名单
SELECT * FROM Student where s_birth like '1990%'
-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT
c_id,
avg(s_score)
FROM
Score
GROUP BY
c_id
ORDER BY avg(s_score) desc, c_id asc
-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT
stu.s_id as '学号',
stu.s_name as '姓名',
avg(s_score) as '平均成绩'
FROM
Score sc
LEFT JOIN Student stu on stu.s_id = sc.s_id
GROUP BY
sc.s_id
HAVING avg(s_score) >= 85
-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
SELECT
stu.s_id,
stu.s_name,
sc.s_score
FROM
Student stu
LEFT JOIN Score sc ON sc.s_id = stu.s_id
LEFT JOIN Course co ON sc.c_id = co.c_id
WHERE
co.c_name = '数学'
AND s_score < 60
-- 35、查询所有学生的课程及分数情况；
SELECT
stu.s_id,
stu.s_name,
stu.s_sex,
sc.c_id,
sc.s_score
FROM
Student stu
LEFT JOIN Score sc on sc.s_id = stu.s_id
-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
SELECT
stu.s_name,
co.c_name,
sc.s_score
FROM
Student stu
LEFT JOIN Score sc on stu.s_id = sc.s_id
LEFT JOIN Course co on sc.c_id = co.c_id
where
sc.s_score > 70
ORDER BY sc.s_id
-- 37、查询不及格的课程
SELECT
stu.s_id,
stu.s_name,
co.c_name,
sc.s_score
FROM
Student stu
LEFT JOIN Score sc on stu.s_id = sc.s_id
LEFT JOIN Course co on sc.c_id = co.c_id
where
sc.s_score < 60
ORDER BY sc.s_id
-- 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
SELECT
stu.s_id,
stu.s_name,
sc.s_score
FROM
Student stu
LEFT JOIN Score sc on stu.s_id = sc.s_id
LEFT JOIN Course co on sc.c_id = co.c_id
where
co.c_id = '01'
and sc.s_score >= 80
ORDER BY sc.s_id
-- 39、求每门课程的学生人数
SELECT
c_id,
count(*)
FROM
Score
GROUP BY c_id
-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT
stu.s_name,
sc.s_score
FROM
Student stu
LEFT JOIN Score sc on stu.s_id = sc.s_id
LEFT JOIN Course co on sc.c_id = co.c_id
LEFT JOIN Teacher t on t.t_id = co.t_id
WHERE
t.t_name = '张三'
ORDER BY
s_score DESC
LIMIT 0,1
-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT DISTINCT
a.s_id,
a.c_id,
a.s_score
FROM
Score a
JOIN Score b on a.s_id = b.s_id
and a.s_score = b.s_score
and a.c_id <> b.c_id
ORDER BY a.s_id
-- 42、查询每门课成绩最好的前两名
-- 这写法真牛逼
select
a.s_id,
a.c_id,
a.s_score
from score a
where (
select COUNT(1)
from score b
where
b.c_id=a.c_id
and b.s_score>=a.s_score
)<=2
ORDER BY a.c_id
-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，
-- 若人数相同，按课程号升序排列
SELECT
c_id,
count(s_id)
FROM
Score
GROUP BY
c_id
ORDER BY
count(s_id) desc,
c_id asc
-- 44、检索至少选修两门课程的学生学号
SELECT
sc.s_id '学号',
count(sc.s_id) '课程数'
FROM
Score sc
GROUP BY
sc.s_id
HAVING count(sc.s_id) >= 2
-- 45、查询选修了全部课程的学生信息
SELECT
stu.*
FROM
Student stu
LEFT JOIN Score sc on stu.s_id = sc.s_id
GROUP BY
sc.s_id
HAVING
count(sc.s_id) = (
SELECT count(*) FROM Course
)
-- 46、查询各学生的年龄
SELECT
s_name,
2021 - year(s_birth) '年龄'
FROM
Student
-- 47、查询本周过生日的学生
-- 此处可能有问题,week函数取的为当前年的第几周,2017-12-12是第50周而2018-12-12是第49周,可以取月份,day,星期几(%w),
-- 再判断本周是否会持续到下一个月进行判断,太麻烦,不会写
-- 48、查询下周过生日的学生
-- 49、查询本月过生日的学生
SELECT * FROM Student where MONTH(s_birth) = MONTH(NOW())
-- 50、查询下月过生日的学生
-- 注意:当 当前月为12时,用month(now())+1为13而不是1,可用timestampadd()函数或mod取模
select
*
from
student
where
MONTH(NOW())+1 =MONTH(s_birth)
OR MONTH(NOW())-11 =MONTH(s_birth) -- 本月为12月情况
3、SQL表语句
/*
Navicat Premium Data Transfer
Date: 23/06/2021 17:07:38
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for Course
-- ----------------------------
DROP TABLE IF EXISTS `Course`;
CREATE TABLE `Course` (
`c_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`c_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
`t_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of Course
-- ----------------------------
INSERT INTO `Course` VALUES ('01', '语文', '02');
INSERT INTO `Course` VALUES ('02', '数学', '01');
INSERT INTO `Course` VALUES ('03', '英语', '03');
-- ----------------------------
-- Table structure for Score
-- ----------------------------
DROP TABLE IF EXISTS `Score`;
CREATE TABLE `Score` (
`s_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`c_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`s_score` int(3) DEFAULT NULL,
PRIMARY KEY (`s_id`, `c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of Score
-- ----------------------------
INSERT INTO `Score` VALUES ('01', '01', 80);
INSERT INTO `Score` VALUES ('01', '02', 90);
INSERT INTO `Score` VALUES ('01', '03', 99);
INSERT INTO `Score` VALUES ('02', '01', 70);
INSERT INTO `Score` VALUES ('02', '02', 60);
INSERT INTO `Score` VALUES ('02', '03', 80);
INSERT INTO `Score` VALUES ('03', '01', 80);
INSERT INTO `Score` VALUES ('03', '02', 80);
INSERT INTO `Score` VALUES ('03', '03', 80);
INSERT INTO `Score` VALUES ('04', '01', 50);
INSERT INTO `Score` VALUES ('04', '02', 30);
INSERT INTO `Score` VALUES ('04', '03', 20);
INSERT INTO `Score` VALUES ('05', '01', 76);
INSERT INTO `Score` VALUES ('05', '02', 87);
INSERT INTO `Score` VALUES ('06', '01', 34);
INSERT INTO `Score` VALUES ('06', '03', 34);
INSERT INTO `Score` VALUES ('07', '01', 89);
INSERT INTO `Score` VALUES ('07', '03', 98);
-- ----------------------------
-- Table structure for Student
-- ----------------------------
DROP TABLE IF EXISTS `Student`;
CREATE TABLE `Student` (
`s_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`s_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
`s_birth` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
`s_sex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of Student
-- ----------------------------
INSERT INTO `Student` VALUES ('01', '赵雷', '1990-01-01', '男');
INSERT INTO `Student` VALUES ('02', '钱电', '1990-12-21', '男');
INSERT INTO `Student` VALUES ('03', '孙风', '1990-05-20', '男');
INSERT INTO `Student` VALUES ('04', '李云', '1990-08-06', '男');
INSERT INTO `Student` VALUES ('05', '周梅', '1991-07-01', '女');
INSERT INTO `Student` VALUES ('06', '吴兰', '1992-03-01', '女');
INSERT INTO `Student` VALUES ('07', '郑竹', '1989-06-04', '女');
INSERT INTO `Student` VALUES ('08', '王菊', '1990-01-20', '女');
-- ----------------------------
-- Table structure for Teacher
-- ----------------------------
DROP TABLE IF EXISTS `Teacher`;
CREATE TABLE `Teacher` (
`t_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`t_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`t_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of Teacher
-- ----------------------------
INSERT INTO `Teacher` VALUES ('01', '张三');
INSERT INTO `Teacher` VALUES ('02', '李四');
INSERT INTO `Teacher` VALUES ('03', '王五');
SET FOREIGN_KEY_CHECKS = 1;
参考资料：https://blog.csdn.net/fashion2014/article/details/78826299/