-- 建表
-- 学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
-- 课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
-- 教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
-- 成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
-- 学生表测试数据
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-12-01' , '女');
INSERT INTO Student VALUES('06' , '吴兰' , '1992-03-01' , '女');
INSERT INTO Student VALUES('07' , '郑竹' , '1989-07-01' , '女');
INSERT INTO Student VALUES('08' , '王菊' , '1990-01-20' , '女');
SELECT * FROM Student
-- 课程表测试数据
INSERT INTO Course VALUES('01' , '语文' , '02');
INSERT INTO Course VALUES('02' , '数学' , '01');
INSERT INTO Course VALUES('03' , '英语' , '03');
SELECT * FROM Course
-- 教师表测试数据
INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');
SELECT * FROM Teacher
-- 成绩表测试数据
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' , 31);
INSERT INTO Score VALUES('06' , '03' , 34);
INSERT INTO Score VALUES('07' , '02' , 89);
INSERT INTO Score VALUES('07' , '03' , 98);
SELECT * FROM Score
四张表关系图
学生表
+------+--------+-----------+-------+
| s_id | s_name | s_brith | s_sex |
+------+--------+-----------+-------+
| 01 | 赵雷 | 1990-01-01 | 男 |
| 02 | 钱电 | 1990-12-21 | 男 |
| 03 | 孙风 | 1990-05-20 | 男 |
| 04 | 李云 | 1990-08-06 | 男 |
| 05 | 周梅 | 1991-12-01 | 女 |
| 06 | 吴兰 | 1992-03-01 | 女 |
| 07 | 郑竹 | 1989-07-01 | 女 |
| 08 | 王菊 | 1990-01-20 | 女 |
+------+--------+-----------+-------+
课程表
+------+--------+-------+
| c_id | c_name | t_id |
+------+--------+-------+
| 01 | 语文 | 02 |
| 02 | 数学 | 01 |
| 03 | 英语 | 03 |
+------+--------+-------+
教师表
+------+--------+
| t_id | t_name |
+------+--------+
| 01 | 张三 |
| 02 | 李四 |
| 03 | 王五 |
+------+--------+
成绩表
+------+------+-------+
| s_id | c_id |s_score|
+------+------+-------+
| 01 | 01 | 80 |
| 01 | 02 | 90 |
| 01 | 03 | 99 |
| 02 | 01 | 70 |
| 02 | 02 | 60 |
| 02 | 03 | 80 |
| 03 | 01 | 80 |
| 03 | 02 | 80 |
| 03 | 03 | 80 |
| 04 | 01 | 50 |
| 04 | 02 | 30 |
| 04 | 03 | 20 |
| 05 | 01 | 76 |
| 05 | 02 | 87 |
| 06 | 01 | 31 |
| 06 | 03 | 34 |
| 07 | 02 | 89 |
| 07 | 03 | 98 |
+------+------+-------+
题目索引
- 1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
- 2. 查询"01"课程比"02"课程成绩低的学生的信息及课程分数
- 3. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
- 4. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
- 5. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
- 6. 查询"李"姓老师的数量
- 7. 查询学过"张三"老师授课的同学的信息
- 8. 查询没学过"张三"老师授课的同学的信息
- 9. 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
- 10. 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
- 11. 查询没有学全所有课程的同学的信息
- 12. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息
- 13. 查询和"01"号的同学学习的课程完全相同的其他同学的信息
- 14. 查询没学过"张三"老师讲授的任一门课程的学生姓名
- 15. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
- 16. 检索"01"课程分数小于60，按分数降序排列的学生信息
- 17. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
- 18. 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率。及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
- 19. 按各科成绩进行排序，并显示排名（实现不完全）
- 20. 查询学生的总成绩并进行排名
- 21. 查询不同老师所教不同课程平均分从高到低显示
- 22. 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
- 23. 统计各科成绩各分数段人数：课程编号、课程名称、[100-85]、[85-70]、[70-60]、[0-60]及所占百分比
- 24. 查询学生平均成绩及其名次
- 25. 查询各科成绩前三名的记录
- 26. 查询每门课程被选修的学生数
- 27. 查询出只有两门课程的全部学生的学号和姓名
- 28. 查询男生、女生人数
- 29. 查询名字中含有"风"字的学生信息
- 30. 查询同名同性学生名单，并统计同名人数
- 31. 查询1990年出生的学生名单
- 32. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
- 33. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
- 34. 查询课程名称为"数学"，且分数低于60的学生姓名和分数
- 35. 查询所有学生的课程及分数情况；
- 36. 查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
- 37. 查询不及格的课程
- 38. 查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
- 39. 求每门课程的学生人数
- 40. 查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
- 41. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
- 42. 查询每门功成绩最好的前两名
- 43. 统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
- 44. 检索至少选修两门课程的学生学号
- 45. 查询选修了全部课程的学生信息
- 46. 查询各学生的年龄
- 47. 查询本周过生日的学生
- 48. 查询下周过生日的学生
- 49. 查询本月过生日的学生
- 50. 查询下月过生日的学生
1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT st.*, sc.s_score AS `01分数`, sc2.s_score AS `02分数`
FROM student st
JOIN score sc
ON st.s_id = sc.s_id
AND sc.c_id = '01'
LEFT JOIN score sc2
ON st.s_id = sc2.s_id
AND sc2.c_id = '02'
WHERE sc.s_score > sc2.s_score
+------+--------+-----------+-------+--------+--------+
| s_id | s_name | s_brith | s_sex | 01分数 | 02分数 |
+------+--------+-----------+-------+--------+--------+
| 02 | 钱电 | 1990-12-21 | 男 | 70 | 60 |
| 04 | 李云 | 1990-08-06 | 男 | 50 | 30 |
+------+--------+-----------+-------+--------+--------+
2. 查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT st.*, sc.s_score AS `01分数`, sc2.s_score AS `02分数`
FROM student st
JOIN score sc
ON st.s_id = sc.s_id
AND sc.c_id = '01'
LEFT JOIN score sc2
ON st.s_id = sc2.s_id
AND sc2.c_id = '02'
WHERE sc.s_score < sc2.s_score
+------+--------+-----------+-------+--------+--------+
| s_id | s_name | s_brith | s_sex | 01分数 | 02分数 |
+------+--------+-----------+-------+--------+--------+
| 01 | 赵雷 | 1990-01-01 | 男 | 80 | 90 |
| 05 | 周梅 | 1991-12-01 | 女 | 76 | 87 |
+------+--------+-----------+-------+--------+--------+
3. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT st.s_id, st.s_name
, ROUND(AVG(sc.s_score), 2) AS `平均成绩`
FROM student st
JOIN score sc ON st.s_id = sc.s_id
GROUP BY st.s_id
HAVING AVG(sc.s_score) >= 60
+------+--------+---------+
| s_id | s_name | 平均成绩 |
+------+--------+---------+
| 01 | 赵雷 | 89.67 |
| 02 | 钱电 | 70.00 |
| 03 | 孙风 | 80.00 |
| 05 | 周梅 | 81.50 |
| 07 | 郑竹 | 93.50 |
+------+--------+---------+
4. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
SELECT st.s_id, st.s_name
, ROUND(AVG(sc.s_score), 2) AS `平均成绩`
FROM student st
LEFT JOIN score sc ON st.s_id = sc.s_id
GROUP BY st.s_id
HAVING AVG(sc.s_score) <= 60
OR AVG(sc.s_score) IS NULL
+------+--------+---------+
| s_id | s_name | 平均成绩 |
+------+--------+---------+
| 04 | 李云 | 33.33 |
| 06 | 吴兰 | 32.50 |
| 08 | 王菊 | NULL |
+------+--------+---------+
5. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT st.s_id, st.s_name, COUNT(sc.c_id) AS `选课总数`
, SUM(sc.s_score) AS `总成绩`
FROM student st
LEFT JOIN score sc ON sc.s_id = st.s_id
GROUP BY st.s_id
+------+--------+---------+--------+
| s_id | s_name | 选课总数 | 总成绩 |
+------+--------+---------+--------+
| 01 | 赵雷 | 3 | 269.0 |
| 02 | 钱电 | 3 | 210.0 |
| 03 | 孙风 | 3 | 240.0 |
| 04 | 李云 | 3 | 100.0 |
| 05 | 周梅 | 2 | 163.0 |
| 06 | 吴兰 | 2 | 65.0 |
| 07 | 郑竹 | 2 | 187.0 |
| 08 | 王菊 | 0 | NULL |
+------+--------+---------+--------+
6. 查询"李"姓老师的数量
SELECT COUNT(*)
FROM teacher
WHERE t_name LIKE '李%';
+--------------+
| COUNT(t_name)|
+--------------+
| 1 |
+--------------+
7. 查询学过"张三"老师授课的同学的信息
SELECT st.*
FROM teacher te, course co, student st, score sc
WHERE te.t_name = '张三'
AND te.t_id = co.t_id
AND sc.s_id = st.s_id
AND sc.c_id = co.c_id
+------+--------+-----------+-------+
| s_id | s_name | s_brith | s_sex |
+------+--------+-----------+-------+
| 01 | 赵雷 | 1990-01-01 | 男 |
| 02 | 钱电 | 1990-12-21 | 男 |
| 03 | 孙风 | 1990-05-20 | 男 |
| 04 | 李云 | 1990-08-06 | 男 |
| 05 | 周梅 | 1991-12-01 | 女 |
| 07 | 郑竹 | 1989-07-01 | 女 |
+------+--------+-----------+-------+
8. 查询没学过"张三"老师授课的同学的信息
SELECT *
FROM student
WHERE s_id NOT IN (
SELECT st.s_id
FROM teacher te, course co, student st, score sc
WHERE te.t_name = '张三'
AND te.t_id = co.t_id
AND sc.s_id = st.s_id
AND sc.c_id = co.c_id
)
+------+--------+-----------+-------+
| s_id | s_name | s_brith | s_sex |
+------+--------+-----------+-------+
| 06 | 吴兰 | 1992-03-01 | 女 |
| 08 | 王菊 | 1990-01-20 | 女 |
+------+--------+-----------+-------+
9. 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT *
FROM student
WHERE s_id IN (
SELECT sc1.s_id
FROM score sc1, score sc2
WHERE sc1.c_id = '01'
AND sc2.c_id = '02'
AND sc1.s_id = sc2.s_id
)
+------+--------+-----------+-------+
| s_id | s_name | s_brith | s_sex |
+------+--------+-----------+-------+
| 01 | 赵雷 | 1990-01-01 | 男 |
| 02 | 钱电 | 1990-12-21 | 男 |
| 03 | 孙风 | 1990-05-20 | 男 |
| 04 | 李云 | 1990-08-06 | 男 |
| 05 | 周梅 | 1991-12-01 | 女 |
+------+--------+-----------+-------+
10. 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT *
FROM student
WHERE s_id IN (
SELECT s_id
FROM score
WHERE c_id = '01'
AND s_id NOT IN (
SELECT s_id
FROM score
WHERE c_id = '02'
)
)
+------+--------+-----------+-------+
| s_id | s_name | s_brith | s_sex |
+------+--------+-----------+-------+
| 06 | 吴兰 | 1992-03-01 | 女 |
+------+--------+-----------+-------+
11. 查询没有学全所有课程的同学的信息
SELECT st.*
FROM student st
WHERE st.s_id IN (
SELECT s_id
FROM score
WHERE s_id NOT IN (
SELECT sc1.s_id
FROM score sc1
JOIN score sc2
ON sc1.s_id = sc2.s_id
AND sc2.c_id = '02'
JOIN score sc3
ON sc1.s_id = sc3.s_id
AND sc3.c_id = '03'
WHERE sc1.c_id = '01'
)
)
+------+--------+-----------+-------+
| s_id | s_name | s_brith | s_sex |
+------+--------+-----------+-------+
| 05 | 周梅 | 1991-12-01 | 女 |
| 06 | 吴兰 | 1992-03-01 | 女 |
| 07 | 郑竹 | 1989-07-01 | 女 |
+------+--------+-----------+-------+
12. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECT st.*
FROM student st
LEFT JOIN score sc ON st.s_id = sc.s_id
WHERE sc.c_id IN (
SELECT c_id
FROM score
WHERE s_id = '01'
)
GROUP BY st.s_id
+------+--------+-----------+-------+
| s_id | s_name | s_brith | s_sex |
+------+--------+-----------+-------+
| 01 | 赵雷 | 1990-01-01 | 男 |
| 02 | 钱电 | 1990-12-21 | 男 |
| 03 | 孙风 | 1990-05-20 | 男 |
| 04 | 李云 | 1990-08-06 | 男 |
| 05 | 周梅 | 1991-12-01 | 女 |
| 06 | 吴兰 | 1992-03-01 | 女 |
| 07 | 郑竹 | 1989-07-01 | 女 |
+------+--------+-----------+-------+
13. 查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT *
FROM student
WHERE s_id IN (
SELECT s_id
FROM score
WHERE c_id IN (
SELECT c_id
FROM score
WHERE s_id = '01'
)
AND s_id <> '01'
GROUP BY s_id
HAVING COUNT(c_id) >= 3
)
+------+--------+-----------+-------+
| s_id | s_name | s_brith | s_sex |
+------+--------+-----------+-------+
| 02 | 钱电 | 1990-12-21 | 男 |
| 03 | 孙风 | 1990-05-20 | 男 |
| 04 | 李云 | 1990-08-06 | 男 |
+------+--------+-----------+-------+
14. 查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT s_name
FROM student
WHERE s_name NOT IN (
SELECT st.s_name
FROM student st, course co, teacher te, score sc
WHERE st.s_id = sc.s_id
AND sc.c_id = co.c_id
AND co.t_id = te.t_id
AND te.t_name = '张三'
)
+--------+
| s_name |
+--------+
| 吴兰 |
| 王菊 |
+--------+
15. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT st.s_id, st.s_name, AVG(sc.s_score) AS `平均成绩`
FROM student st, score sc
WHERE st.s_id = sc.s_id
AND sc.s_score < 60
GROUP BY sc.s_id
HAVING COUNT(sc.s_score) >= 2
+------+--------+------------+
| s_id | s_name | 平均成绩 |
+------+--------+------------+
| 04 | 李云 | 33.33333 |
| 06 | 吴兰 | 32.50000 |
+------+--------+------------+
16. 检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT st.*, sc.s_score
FROM student st, score sc
WHERE sc.c_id = '01'
AND sc.s_score < 60
AND sc.s_id = st.s_id
ORDER BY sc.s_score DESC
+------+--------+-----------+-------+---------+
| s_id | s_name | s_brith | s_sex | s_score |
+------+--------+-----------+-------+---------+
| 04 | 李云 | 1990-08-06 | 男 | 50 |
| 06 | 吴兰 | 1992-03-01 | 女 | 31 |
+------+--------+-----------+-------+---------+
17. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT st.*, SUM(CASE
WHEN c_id = 1 THEN s_score
ELSE NULL
END) AS `语文`, SUM(CASE
WHEN c_id = 2 THEN s_score
ELSE NULL
END) AS `数学`
, SUM(CASE
WHEN c_id = 3 THEN s_score
ELSE NULL
END) AS `英语`, AVG(s_score) AS `平均成绩`
FROM student st
LEFT JOIN score sc ON sc.s_id = st.s_id
GROUP BY s_id
ORDER BY AVG(s_score) DESC
+------+--------+-----------+-------+------+------+------+---------+
| s_id | s_name | s_brith | s_sex | 语文 | 数学 | 英语 | 平均成绩 |
+------+--------+-----------+-------+------+------+------+---------+
| 07 | 郑竹 | 1989-07-01 | 女 | NULL | 89.0 | 98.0 | 93.5000 |
| 01 | 赵雷 | 1990-01-01 | 男 | 80.0 | 90.0 | 99.0 | 89.6667 |
| 05 | 周梅 | 1991-12-01 | 女 | 76.0 | 87.0 | NULL | 81.5000 |
| 03 | 孙风 | 1990-05-20 | 男 | 80.0 | 80.0 | 80.0 | 80.0000 |
| 02 | 钱电 | 1990-12-21 | 男 | 70.0 | 60.0 | 80.0 | 70.0000 |
| 04 | 李云 | 1990-08-06 | 男 | 50.0 | 30.0 | 20.0 | 33.3333 |
| 06 | 吴兰 | 1992-03-01 | 女 | 31.0 | NULL | 34.0 | 32.5000 |
| 08 | 王菊 | 1990-01-20 | 女 | NULL | NULL | NULL | NULL |
+------+--------+-----------+-------+------+------+------+---------+
18. 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率。及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
SELECT co.c_id AS `课程ID`, co.c_name AS `课程name`, MAX(s_score) AS `最高分`
, MIN(s_score) AS `最低分`, AVG(s_score) AS `平均分`
, SUM(CASE
WHEN s_score >= 60 THEN 1
ELSE 0
END) / COUNT(*) AS `及格率`
, SUM(CASE
WHEN s_score >= 70
AND s_score < 80 THEN 1
ELSE 0
END) / COUNT(*) AS `中等率`
, SUM(CASE
WHEN s_score >= 80
AND s_score < 90 THEN 1
ELSE 0
END) / COUNT(*) AS `优良率`
, SUM(CASE
WHEN s_score >= 90 THEN 1
ELSE 0
END) / COUNT(*) AS `优秀率`
FROM score sc, course co
WHERE co.c_id = sc.c_id
GROUP BY co.c_id
ORDER BY co.c_id ASC
+-------+----------+-------+--------+---------+--------+--------+--------+--------+
| 课程ID | 课程name | 最高分 | 最低分 | 平均分 | 及格率 | 中等率 | 优良率 | 优秀率 |
+-------+----------+-------+--------+---------+--------+--------+--------+--------+
| 01 | 语文 | 80.0 | 31.0 | 64.5000 | 0.6667 | 0.3333 | 0.3333 | 0.0000 |
| 02 | 数学 | 90.0 | 30.0 | 72.6667 | 0.8333 | 0.0000 | 0.5000 | 0.1667 |
| 03 | 英语 | 99.0 | 20.0 | 68.5000 | 0.6667 | 0.0000 | 0.3333 | 0.3333 |
+-------+----------+-------+--------+---------+--------+--------+--------+--------+
19. 按各科成绩进行排序，并显示排名（实现不完全）
SELECT co.c_id, co.c_name, st.s_name, (
SELECT COUNT(s_score)
FROM score sc2
WHERE sc1.c_id = sc2.c_id
AND sc1.s_score < sc2.s_score
) + 1 AS `名次`
FROM score sc1
JOIN student st ON sc1.s_id = st.s_id
JOIN course co ON co.c_id = sc1.c_id
ORDER BY c_id, s_score DESC
+------+--------+--------+------+
| c_id | c_name | s_name | 名次 |
+------+--------+--------+------+
| 01 | 语文 | 赵雷 | 1 |
| 01 | 语文 | 孙风 | 1 |
| 01 | 语文 | 周梅 | 3 |
| 01 | 语文 | 钱电 | 4 |
| 01 | 语文 | 李云 | 5 |
| 01 | 语文 | 吴兰 | 6 |
| 02 | 数学 | 赵雷 | 1 |
| 02 | 数学 | 郑竹 | 2 |
| 02 | 数学 | 周梅 | 3 |
| 02 | 数学 | 孙风 | 4 |
| 02 | 数学 | 钱电 | 5 |
| 02 | 数学 | 李云 | 6 |
| 03 | 英语 | 赵雷 | 1 |
| 03 | 英语 | 郑竹 | 2 |
| 03 | 英语 | 钱电 | 3 |
| 03 | 英语 | 孙风 | 3 |
| 03 | 英语 | 吴兰 | 5 |
| 03 | 英语 | 李云 | 6 |
+------+--------+--------+------+
20. 查询学生的总成绩并进行排名
SELECT st.s_id, st.s_name, SUM(sc.s_score) AS `总成绩`
FROM score sc
LEFT JOIN student st ON sc.s_id = st.s_id
GROUP BY s_id
ORDER BY `总成绩` DESC
+------+--------+-------+
| s_id | s_name | 总成绩 |
+------+--------+-------+
| 01 | 赵雷 | 269 |
| 03 | 孙风 | 240 |
| 02 | 钱电 | 210 |
| 07 | 郑竹 | 187 |
| 05 | 周梅 | 163 |
| 04 | 李云 | 100 |
| 06 | 吴兰 | 65 |
+------+--------+-------+
21. 查询不同老师所教不同课程平均分从高到低显示
SELECT te.*, co.c_name, AVG(s_score) AS `课程平均分`
FROM course co
LEFT JOIN teacher te ON te.t_id = co.t_id
LEFT JOIN score sc ON sc.c_id = co.c_id
GROUP BY sc.c_id
ORDER BY AVG(s_score) DESC
+------+--------+--------+-----------+
| t_id | t_name | c_name | 课程平均分 |
+------+--------+--------+-----------+
| 01 | 张三 | 数学 | 72.6667 |
| 03 | 王五 | 英语 | 68.5000 |
| 02 | 李四 | 语文 | 64.5000 |
+------+--------+--------+-----------+
22. 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
SELECT st.*, co.c_name, s_score, `名次`
FROM (
SELECT c_id, s_id, s_score
, IF(@prec = c_id, @rank := @rank + 1, @rank := 1) AS `名次`
, @prec := c_id
FROM score
ORDER BY c_id, s_score DESC
) t
JOIN student st ON st.s_id = t.s_id
JOIN course co ON co.c_id = t.c_id
WHERE `名次` = 2
OR `名次` = 3;
+------+--------+-----------+-------+--------+---------+-----+
| s_id | s_name | s_brith | s_sex | c_name | s_score | 名次 |
+------+--------+-----------+-------+--------+---------+-----+
| 03 | 孙风 | 1990-05-20 | 男 | 语文 | 80 | 2 |
| 05 | 周梅 | 1991-12-01 | 女 | 语文 | 76 | 3 |
| 05 | 周梅 | 1991-12-01 | 女 | 数学 | 87 | 2 |
| 07 | 郑竹 | 1989-07-01 | 女 | 数学 | 89 | 3 |
| 02 | 钱电 | 1990-12-21 | 男 | 英语 | 80 | 2 |
| 07 | 郑竹 | 1989-07-01 | 女 | 英语 | 98 | 3 |
+------+--------+-----------+-------+--------+---------+-----+
23. 统计各科成绩各分数段人数：课程编号、课程名称、[100-85]、[85-70]、[70-60]、[0-60]及所占百分比
SELECT co.c_id AS `课程编号`, co.c_name AS `课程名称`, A.*
FROM course co, (
SELECT sc.c_id, SUM(CASE
WHEN sc.s_score > 85
AND sc.s_score <= 100 THEN 1
ELSE 0
END) / COUNT(*) AS `[100-85]`
, SUM(CASE
WHEN sc.s_score > 70
AND sc.s_score <= 85 THEN 1
ELSE 0
END) / COUNT(*) AS `[85-70]`
, SUM(CASE
WHEN sc.s_score > 60
AND sc.s_score <= 70 THEN 1
ELSE 0
END) / COUNT(*) AS `[70-60]`
, SUM(CASE
WHEN sc.s_score > 0
AND sc.s_score <= 60 THEN 1
ELSE 0
END) / COUNT(*) AS `[0-60]`
FROM score sc
GROUP BY sc.c_id
) A
WHERE co.c_id = A.c_id
+---------+---------+------+----------+---------+---------+--------+
| 课程编号 | 课程名称 | cid | [100-85] | [85-70] | [70-60] | [0-60] |
+---------+---------+------+----------+---------+---------+--------+
| 01 | 语文 | 01 | 0.0000 | 0.6667 | 0.0000 | 0.3333 |
| 02 | 数学 | 02 | 0.5000 | 0.1667 | 0.1667 | 0.1667 |
| 03 | 英语 | 03 | 0.3333 | 0.3333 | 0.0000 | 0.3333 |
+---------+---------+------+----------+---------+---------+--------+
24. 查询学生平均成绩及其名次
SELECT s_name, 平均分, @num := @num + 1 AS '名次'
FROM (
SELECT st.*, AVG(sc.s_score) AS 平均分
, (
SELECT @num := 0
) AS '名次'
FROM score sc
LEFT JOIN student st ON st.s_id = sc.s_id
GROUP BY sc.s_id
ORDER BY AVG(sc.s_score) DESC
) t
+--------+--------+------+
| s_name | 平均分 | 名次 |
+--------+--------+------+
| 郑竹 | 93.5000 | 1 |
| 赵雷 | 89.6667 | 2 |
| 周梅 | 81.5000 | 3 |
| 孙风 | 80.0000 | 4 |
| 钱电 | 70.0000 | 5 |
| 李云 | 33.3333 | 6 |
| 吴兰 | 32.5000 | 7 |
+--------+--------+------+
25. 查询各科成绩前三名的记录
SELECT st.s_id, st.s_name, co.c_name, t.s_score, `名次`
FROM (
SELECT c_id, s_id, s_score
, IF(@prec = c_id, @rank := @rank + 1, @rank := 1) AS `名次`
, @prec := c_id
FROM score sc, (
SELECT @rank := 0
) r, (
SELECT @prec := -1
) p
ORDER BY c_id, s_score DESC
) t
JOIN student st ON st.s_id = t.s_id
JOIN course co ON co.c_id = t.c_id
WHERE `名次` = 2
OR `名次` = 1
OR `名次` = 3;
+------+--------+---------+---------+-----+
| s_id | s_name | c_name | s_score | 名次 |
+------+--------+---------+---------+-----+
| 01 | 赵雷 | 语文 | 80 | 1 |
| 03 | 孙风 | 语文 | 80 | 2 |
| 05 | 周梅 | 语文 | 76 | 3 |
| 01 | 赵雷 | 数学 | 90 | 1 |
| 05 | 周梅 | 数学 | 87 | 2 |
| 07 | 郑竹 | 数学 | 89 | 3 |
| 01 | 赵雷 | 英语 | 99 | 1 |
| 02 | 钱电 | 英语 | 80 | 2 |
| 07 | 郑竹 | 英语 | 98 | 3 |
+------+--------+---------+---------+-----+
26. 查询每门课程被选修的学生数
SELECT co.c_name, COUNT(s_id) AS `学生数`
FROM score sc
LEFT JOIN course co ON sc.c_id = co.c_id
GROUP BY sc.c_id
+--------+-------+
| c_name | 学生数 |
+--------+-------+
| 语文 | 6 |
| 数学 | 6 |
| 英语 | 6 |
+--------+-------+
27. 查询出只有两门课程的全部学生的学号和姓名
SELECT sc.s_id, st.s_name
FROM score sc
JOIN student st ON st.s_id = sc.s_id
GROUP BY s_id
HAVING COUNT(DISTINCT c_id) = 2
+------+--------+
| s_id | s_name |
+------+--------+
| 05 | 周梅 |
| 06 | 吴兰 |
| 07 | 郑竹 |
+------+--------+
28. 查询男生、女生人数
SELECT COUNT(DISTINCT t1.s_id) AS `男生人数`, COUNT(DISTINCT t2.s_id) AS `女生人数`
FROM (
SELECT s_id
FROM student
WHERE s_sex = '男'
) t1, (
SELECT s_id
FROM student
WHERE s_sex = '女'
) t2
+---------+---------+
| 男生人数 | 女生人数 |
+---------+---------+
| 4 | 4 |
+---------+---------+
29. 查询名字中含有"风"字的学生信息
SELECT *
FROM student
WHERE sname LIKE '%风%'
+------+--------+-----------+-------+
| s_id | s_name | s_brith | s_sex |
+------+--------+-----------+-------+
| 03 | 孙风 | 1990-05-20 | 男 |
+------+--------+-----------+-------+
30. 查询同名同性学生名单，并统计同名人数
SELECT st1.s_name, st1.s_sex, COUNT(st1.s_name) AS `同名人数`
FROM student st1
JOIN student st2
ON st1.s_id != st2.s_id
AND st1.s_name = st2.s_name
AND st1.s_sex = st2.s_sex
GROUP BY st1.s_name, st1.s_sex
+--------+-------+---------+
| s_name | s_sex | 同名人数 |
+--------+-------+---------+
31. 查询1990年出生的学生名单
SELECT *
FROM student
WHERE YEAR(s_birth) = 1990
+------+--------+-----------+-------+
| s_id | s_name | s_brith | s_sex |
+------+--------+-----------+-------+
| 01 | 赵雷 | 1990-01-01 | 男 |
| 02 | 钱电 | 1990-12-21 | 男 |
| 03 | 孙风 | 1990-05-20 | 男 |
| 04 | 李云 | 1990-08-06 | 男 |
| 08 | 王菊 | 1990-01-20 | 女 |
+------+--------+-----------+-------+
32. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT c_id, AVG(s_score) AS `平均成绩`
FROM score
GROUP BY c_id
ORDER BY AVG(s_score) DESC, c_id;
+------+---------+
| c_id | 平均成绩 |
+------+---------+
| 02 | 72.6667 |
| 03 | 68.5000 |
| 01 | 64.5000 |
+------+---------+
33. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT st.s_id, st.s_name, AVG(sc.s_score) AS `平均成绩`
FROM score sc
JOIN student st ON st.s_id = sc.s_id
GROUP BY sc.s_id
HAVING AVG(sc.s_score) >= 85
+------+--------+---------+
| s_id | s_name | 平均成绩 |
+------+--------+---------+
| 01 | 赵雷 | 89.6667 |
| 07 | 郑竹 | 93.5000 |
+------+--------+---------+
34. 查询课程名称为"数学"，且分数低于60的学生姓名和分数
SELECT st.s_name, co.c_name, sc.s_score
FROM score sc
JOIN student st ON st.s_id = sc.s_id
JOIN course co ON co.c_id = sc.c_id
WHERE co.c_name = '数学'
AND sc.s_score < 60
+--------+--------+---------+
| s_name | c_name | s_score |
+--------+--------+---------+
| 李云 | 数学 | 30 |
+--------+--------+---------+
35. 查询所有学生的课程及分数情况；
SELECT st.s_name, co.c_name, sc.s_score
FROM score sc
JOIN student st ON st.s_id = sc.s_id
JOIN course co ON co.c_id = sc.c_id
+--------+--------+---------+
| s_name | c_name | s_score |
+--------+--------+---------+
| 赵雷 | 语文 | 80 |
| 赵雷 | 数学 | 90 |
| 赵雷 | 英语 | 99 |
| 钱电 | 语文 | 70 |
| 钱电 | 数学 | 60 |
| 钱电 | 英语 | 80 |
| 孙风 | 语文 | 80 |
| 孙风 | 数学 | 80 |
| 孙风 | 英语 | 80 |
| 李云 | 语文 | 50 |
| 李云 | 数学 | 30 |
| 李云 | 英语 | 20 |
| 周梅 | 语文 | 76 |
| 周梅 | 数学 | 87 |
| 吴兰 | 语文 | 31 |
| 吴兰 | 英语 | 34 |
| 郑竹 | 数学 | 89 |
| 郑竹 | 英语 | 98 |
+--------+--------+---------+
36. 查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
SELECT st.s_name, co.c_name, sc.s_score
FROM score sc
JOIN student st ON st.s_id = sc.s_id
JOIN course co ON co.c_id = sc.c_id
WHERE sc.s_score >= 70
+--------+--------+---------+
| s_name | c_name | s_score |
+--------+--------+---------+
| 赵雷 | 语文 | 80 |
| 赵雷 | 数学 | 90 |
| 赵雷 | 英语 | 99 |
| 钱电 | 语文 | 70 |
| 钱电 | 英语 | 80 |
| 孙风 | 语文 | 80 |
| 孙风 | 数学 | 80 |
| 孙风 | 英语 | 80 |
| 周梅 | 语文 | 76 |
| 周梅 | 数学 | 87 |
| 郑竹 | 数学 | 89 |
| 郑竹 | 英语 | 98 |
+--------+--------+---------+
37. 查询不及格的课程
SELECT sc.s_id, st.s_name, co.c_name, sc.s_score
FROM score sc
JOIN student st ON sc.s_id = st.s_id
JOIN course co ON sc.c_id = co.c_id
WHERE sc.s_score < 60
+------+--------+--------+---------+
| s_id | s_name | c_name | s_score |
+------+--------+--------+---------+
| 04 | 李云 | 语文 | 50 |
| 04 | 李云 | 数学 | 30 |
| 04 | 李云 | 英语 | 20 |
| 06 | 吴兰 | 语文 | 31 |
| 06 | 吴兰 | 英语 | 34 |
+------+--------+--------+---------+
38. 查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
SELECT s_id, s_name
FROM student st
WHERE s_id IN (
SELECT s_id
FROM score sc
WHERE sc.c_id = '01'
AND sc.s_score >= 80
)
+------+--------+
| s_id | s_name |
+------+--------+
| 01 | 赵雷 |
| 02 | 钱电 |
+------+--------+
39. 求每门课程的学生人数
SELECT co.c_name, COUNT(*) AS `学生人数`
FROM score sc
LEFT JOIN course co ON sc.c_id = co.c_id
GROUP BY co.c_name;
+--------+---------+
| c_name | 学生人数 |
+--------+---------+
| 语文 | 6 |
| 数学 | 6 |
| 英语 | 6 |
+--------+---------+
40. 查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT st.*, co.c_name, sc.s_score
FROM student st
LEFT JOIN score sc ON st.s_id = sc.s_id
LEFT JOIN course co ON sc.c_id = co.c_id
LEFT JOIN teacher te ON te.t_id = co.t_id
WHERE te.t_name = '张三'
ORDER BY sc.s_score DESC
LIMIT 1
+------+--------+-----------+-------+--------+---------+
| s_id | c_name | s_brith | s_sex | c_name | s_score |
+------+--------+-----------+-------+--------+---------+
| 01 | 赵雷 | 1990-01-01 | 男 | 数学 | 90 |
+------+--------+-----------+-------+--------+---------+
41. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT sc.*
FROM score sc, (
SELECT *
FROM score
GROUP BY s_id
HAVING COUNT(DISTINCT c_id) > COUNT(DISTINCT s_score)
) t
WHERE sc.s_id = t.s_id
+------+------+-------+
| s_id | c_id |s_score|
+------+------+-------+
| 03 | 01 | 80 |
| 03 | 02 | 80 |
| 03 | 03 | 80 |
+------+------+-------+
42. 查询每门功成绩最好的前两名
SELECT co.c_name, st.s_name, sc1.s_score
FROM score sc1
JOIN course co ON co.c_id = sc1.c_id
JOIN student st ON st.s_id = sc1.s_id
WHERE (
SELECT COUNT(*)
FROM score sc2
WHERE sc1.c_id = sc2.c_id
AND sc1.s_score <= sc2.s_score
) <= 2
ORDER BY sc1.c_id, sc1.s_score DESC;
+--------+--------+---------+
| c_name | c_name | s_score |
+--------+--------+---------+
| 语文 | 赵雷 | 80 |
| 语文 | 孙风 | 80 |
| 数学 | 赵雷 | 90 |
| 数学 | 郑竹 | 89 |
| 英语 | 赵雷 | 99 |
| 英语 | 郑竹 | 98 |
+--------+--------+---------+
43. 统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT co.c_id AS `课程ID`, COUNT(*) AS `选修人数`
FROM score sc
LEFT JOIN course co ON co.c_id = sc.c_id
GROUP BY co.c_id
ORDER BY COUNT(*) DESC, co.c_id ASC
+-------+----------+
| 课程ID | 选修人数 |
+-------+----------+
| 01 | 6 |
| 02 | 6 |
| 03 | 6 |
+-------+----------+
44. 检索至少选修两门课程的学生学号
SELECT st.s_id
FROM student st
LEFT JOIN score sc ON sc.s_id = st.s_id
GROUP BY sc.s_id
HAVING COUNT(sc.c_id) > 1;
+------+
| s_id |
+------+
| 01 |
| 02 |
| 03 |
| 04 |
| 05 |
| 06 |
| 07 |
+------+
45. 查询选修了全部课程的学生信息
SELECT st.*
FROM student st
LEFT JOIN score sc ON sc.s_id = st.s_id
GROUP BY s_id
HAVING COUNT(DISTINCT c_id) = (
SELECT COUNT(DISTINCT c_id)
FROM course
)
+------+--------+-----------+-------+
| s_id | s_name | s_brith | s_sex |
+------+--------+-----------+-------+
| 01 | 赵雷 | 1990-01-01 | 男 |
| 02 | 钱电 | 1990-12-21 | 男 |
| 03 | 孙风 | 1990-05-20 | 男 |
| 04 | 李云 | 1990-08-06 | 男 |
+------+--------+-----------+-------+
46. 查询各学生的年龄
SELECT s_name, YEAR(NOW()) - YEAR(s_birth) AS age
FROM student
+--------+------+
| s_name | age |
+--------+------+
| 赵雷 | 31 |
| 钱电 | 31 |
| 孙风 | 31 |
| 李云 | 31 |
| 周梅 | 30 |
| 吴兰 | 29 |
| 郑竹 | 32 |
| 王菊 | 31 |
+--------+------+
47. 查询本周过生日的学生
SELECT *
FROM student
WHERE WEEK(NOW()) = WEEK(s_birth)
+------+--------+-----------+-------+
| s_id | s_name | s_brith | s_sex |
+------+--------+-----------+-------+
48. 查询下周过生日的学生
SELECT *
FROM student
WHERE WEEK(NOW()) + 1 = WEEK(s_birth)
+------+--------+-----------+-------+
| s_id | s_name | s_brith | s_sex |
+------+--------+-----------+-------+
49. 查询本月过生日的学生
SELECT *
FROM student
WHERE MONTH(NOW()) = MONTH(s_birth)
+------+--------+-----------+-------+
| s_id | s_name | s_brith | s_sex |
+------+--------+-----------+-------+
| 04 | 李云 | 1990-08-06 | 男 |
+------+--------+-----------+-------+
50. 查询下月过生日的学生
SELECT *
FROM student
WHERE MONTH(NOW()) + 1 = MONTH(s_birth)
+------+--------+-----------+-------+
| s_id | s_name | s_brith | s_sex |
+------+--------+-----------+-------+
版权声明：本文为weixin_43847613原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。