MySQL练习题
SELECT * FROM course
SELECT * FROM score
SELECT * FROM teacher
SELECT * FROM student
1 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
第一步：查询"01"课程的信息
SELECT * FROM score WHERE c_id ='01'
第二步： 查询"02"课程的信息
SELECT * FROM score WHERE c_id='02'
第三步： 查询"01"课程比"02"课程成绩高的学生号(左连接)
SELECT a.s_id
FROM
(SELECT * FROM score WHERE c_id ='01') AS a
LEFT JOIN
(SELECT * FROM score WHERE c_id='02') AS b
ON a.s_id=b.s_id
WHERE a.s_score>b.s_score OR b.s_id IS NULL
第四步：根据第三步的学生号查询学生的信息及课程号、课程分数
SELECT student.*,score.c_id,score.s_score
FROM student
JOIN score
ON student.s_id=score.s_id
WHERE student.s_id IN(SELECT a.s_id
FROM (SELECT * FROM score WHERE c_id ='01') AS a
LEFT JOIN (SELECT * FROM score WHERE c_id='02') AS b
ON a.s_id=b.s_id
WHERE a.s_score>b.s_score OR b.s_id IS NULL)
2查询"01"课程比"02"课程成绩低的学生的信息及课程分数
第一步
SELECT * FROM student WHERE c_id='01'
第二步
SELECT * FROM student WHERE c_id='02'
第三步（右连接）
SELECT b.s_id
FROM (SELECT * FROM score WHERE c_id='01') AS a
RIGHT JOIN (SELECT * FROM score WHERE c_id='02') AS b
ON a.s_id =b.s_id
WHERE a.s_score<b.s_score OR a.s_id IS NULL
第四步
SELECT student.*,score.c_id,score.s_score
FROM student
JOIN score
ON student.s_id=score.s_id
WHERE student.s_id IN(SELECT b.s_id
FROM (SELECT * FROM score WHERE c_id='01') AS a
RIGHT JOIN (SELECT * FROM score WHERE c_id='02') AS b
ON a.s_id =b.s_id
WHERE a.s_score<b.s_score OR a.s_id IS NULL)
3平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
内连接
SELECT s.s_id,s.s_name,AVG(sc.s_score)
FROM student AS s
JOIN score AS sc
ON s.s_id = sc.s_id
GROUP BY s.s_id
HAVING AVG(sc.s_score)>=60
4查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
(包括有成绩的和无成绩的)
(左连接)
SELECT s.s_id,s.s_name,AVG(sc.s_score)
FROM student AS s
LEFT JOIN score AS sc
ON s.s_id=sc.s_id
GROUP BY s.s_id
HAVING AVG(sc.s_score)<60 OR AVG(sc.s_score) IS NULL
5所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT s.s_id,s.s_name,COUNT(*) AS 选课总数,SUM(sc.s_score) AS 总成绩
FROM student AS s
JOIN score AS sc
ON s.s_id=sc.s_id
GROUP BY s.s_id
6查询"李"姓老师的数量
SELECT COUNT(*) AS 数量
FROM teacher
WHERE t_name LIKE '李%'
7查询学过"张三"老师授课的同学的信息
第一步：找出学过“张三老师”的学生学号
SELECT sc.s_id
FROM score AS sc,teacher AS t,course AS c
WHERE sc.c_id=c.c_id
AND t.t_id=c.t_id
AND t.t_name='张三'
第二步
SELECT s.*
FROM student AS s
WHERE s.s_id IN
(SELECT sc.s_id
FROM score AS sc,teacher AS t,course AS c
WHERE sc.c_id=c.c_id
AND t.t_id=c.t_id
AND t.t_name='张三')
8查询没学过"张三"老师授课的同学的信息
第一步：找出学过“张三老师”的学生学号
SELECT sc.s_id
FROM score AS sc,teacher AS t,course AS c
WHERE sc.c_id=c.c_id
AND t.t_id=c.t_id
AND t.t_name='张三'
第二步
SELECT s.*
FROM student AS s
WHERE s.s_id NOT IN
(SELECT sc.s_id
FROM score AS sc,teacher AS t,course AS c
WHERE sc.c_id=c.c_id
AND t.t_id=c.t_id
AND t.t_name='张三')
9学过编号为"01"并且也学过编号为"02"的课程的同学的信息
第一种写法
第一步
SELECT s_id FROM score WHERE c_id='01'
第二步
SELECT s_id FROM score WHERE c_id='02'
第三步
SELECT a.s_id
FROM (SELECT s_id FROM score WHERE c_id='01') AS a
JOIN (SELECT s_id FROM score WHERE c_id='02') AS b
ON a.s_id=b.s_id
第四步
SELECT student.*
FROM student
WHERE s_id IN(SELECT a.s_id
FROM (SELECT s_id FROM score WHERE c_id='01') AS a
JOIN (SELECT s_id FROM score WHERE c_id='02') AS b
ON a.s_id=b.s_id)
第二种写法
SELECT s.*
FROM student AS s,
(SELECT score.s_id
FROM score
WHERE score.c_id='01') AS a,
(SELECT score.s_id
FROM score
WHERE score.c_id='02') AS b
WHERE s.s_id=a.s_id
AND s.s_id=b.s_id
10学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
第一步
SELECT s_id FROM score WHERE c_id='01'
第二步
SELECT s_id FROM score WHERE c_id='02'
第三步:(左连接，找出学过‘01’但没有学过‘02’的学生编号)
SELECT a.s_id
FROM (SELECT s_id FROM score WHERE c_id='01') AS a
LEFT JOIN (SELECT s_id FROM score WHERE c_id='02') AS b
ON a.s_id=b.s_id
WHERE b.s_id IS NULL
第四步
SELECT student.*
FROM student
WHERE s_id IN(SELECT a.s_id
FROM (SELECT s_id FROM score WHERE c_id='01') AS a
LEFT JOIN (SELECT s_id FROM score WHERE c_id='02') AS b
ON a.s_id=b.s_id
WHERE b.s_id IS NULL)
11查询没有学全所有课程的同学的信息
第一步
SELECT s_id
FROM score
GROUP BY s_id
HAVING COUNT(*)!=(SELECT COUNT(DISTINCT c_id) FROM course)
第二步
SELECT student.*
FROM student
WHERE s_id IN(SELECT s_id
FROM score
GROUP BY s_id
HAVING COUNT(*)!=(SELECT COUNT(DISTINCT c_id) FROM course))
12查询至少有一门课与学号为"01"的同学所学相同的同学的信息
第一步
SELECT c_id
FROM score
WHERE s_id='01'
第二步
SELECT student.*
FROM student
JOIN score
ON student.s_id=score.s_id
WHERE score.c_id IN (SELECT c_id FROM score WHERE s_id='01')
AND student.s_id !='01'
GROUP BY student.s_id
13查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT *
FROM student
JOIN score
WHERE student.s_id=score.s_id
GROUP BY student.s_id
HAVING score.c_id IN (SELECT c_id FROM score WHERE s_id='01')
AND student.s_id !='01'
AND COUNT(score.c_id)=(SELECT COUNT(score.c_id) FROM score WHERE s_id='01')
14查询没学过"张三"老师讲授的任一门课程的学生姓名
第一步
SELECT sc.s_id
FROM teacher AS t,course AS c,score AS sc
WHERE t.t_id=c.t_id
AND sc.c_id=c.c_id
AND t.t_name='张三'
第二步
SELECT student.*
FROM student
WHERE student.s_id NOT IN
(SELECT sc.s_id
FROM teacher AS t,course AS c,score AS sc
WHERE t.t_id=c.t_id
AND sc.c_id=c.c_id
AND t.t_name='张三')
15查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
第一步
SELECT s_id
FROM score
WHERE s_score<60
GROUP BY s_id
HAVING COUNT(*)>=2
第二步
SELECT s.s_id,s.s_name,AVG(sc.s_score)
FROM student AS s
JOIN score AS sc
ON s.s_id=sc.s_id
GROUP BY s.s_id
HAVING s.s_id IN
(SELECT s_id
FROM score
WHERE s_score<60
GROUP BY s_id
HAVING COUNT(*)>=2)
16检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT student.*
FROM student
JOIN score
ON student.s_id=score.s_id
WHERE score.c_id='01'
AND score.s_score<60
ORDER BY score.s_score DESC
17按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT s_id AS sid,
(SELECT s_score FROM score WHERE s_id=sid AND c_id='01') AS 语文,
(SELECT s_score FROM score WHERE s_id=sid AND c_id='02') AS 数学,
(SELECT s_score FROM score WHERE s_id=sid AND c_id='03') AS 英语,
AVG(s_score) AS 平均成绩
FROM score
GROUP BY s_id
ORDER BY 平均成绩 DESC
18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
SELECT
sc.c_id,
c.c_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 AS sc
JOIN course AS c
ON sc.c_id=c.c_id
GROUP BY sc.c_id
19按各科成绩进行排序，并显示排名(实现不完全)
20查询学生的总成绩并进行排名
21查询不同老师所教不同课程平均分从高到低显示
SELECT t.t_id, c.c_id,AVG(sc.s_score) AS 平均成绩
FROM score AS sc,teacher AS t,course AS c
WHERE sc.c_id=c.c_id
AND t.t_id=c.t_id
GROUP BY t.t_id,c.c_id
ORDER BY 平均成绩 DESC
22查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
23统计各科成绩各分数段人数,课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT COUNT(*),
c.c_id,
c.c_name,
SUM(CASE WHEN sc.s_score>=85 AND sc.s_score<=100 THEN 1 ELSE 0 END)/COUNT(*) AS '[85-100]',
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 AS sc
JOIN course c
ON c.c_id=sc.c_id
GROUP BY c.c_id
24查询学生平均成绩及其名次
25查询各科成绩前三名的记录
SELECT c_id AS cid,
(SELECT s_score FROM score WHERE c_id =cid ORDER BY s_score DESC LIMIT 1) AS 第一名,
(SELECT s_score FROM score WHERE c_id =cid ORDER BY s_score DESC LIMIT 1,1) AS 第二名,
(SELECT s_score FROM score WHERE c_id =cid ORDER BY s_score DESC LIMIT 2,1) AS 第三名
FROM score
GROUP BY cid
26查询每门课程被选修的学生数
SELECT c_id,COUNT(*) AS 人数
FROM score
GROUP BY c_id
27查询出只有两门课程的全部学生的学号和姓名
SELECT s.s_id,s.s_name
FROM student AS s
JOIN score AS sc
ON s.s_id=sc.s_id
GROUP BY s.s_id
HAVING COUNT(*)=2
28查询男生、女生人数
SELECT s_sex,COUNT(*)
FROM student
GROUP BY s_sex
29查询名字中含有"风"字的学生信息
SELECT *
FROM student
WHERE s_name LIKE '%风%'
30查询同名同性学生名单，并统计同名人数
SELECT a.s_name,a.s_sex,COUNT(*) AS 同名人数
FROM student AS a
JOIN student AS 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
31查询1990年出生的学生名单
第一种表达方式：
SELECT student.*
FROM student
WHERE YEAR(s_birth)=1990
第二种表达方式 :
select student.*
FROM student
WHERE DATE_FORMAT(s_birth,'%Y')=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 s.s_id,s.s_name,AVG(sc.s_score)
FROM student AS s
JOIN score AS sc
ON s.s_id=sc.s_id
GROUP BY s.s_id
HAVING AVG(sc.s_score)>=85
34查询课程名称为"数学"，且分数低于60的学生姓名和分数
SELECT s.s_name,sc.s_score
FROM student AS s
JOIN score AS sc
ON s.s_id=sc.s_id
JOIN course AS c
ON c.c_id=sc.c_id
WHERE c.c_name='数学'
AND sc.s_score<60
35查询所有学生的课程及分数情况
SELECT student.s_id AS sid,
student.s_name,
(SELECT s_score FROM score WHERE s_id=sid AND c_id='01') AS 语文,
(SELECT s_score FROM score WHERE s_id=sid AND c_id='02') AS 数学,
(SELECT s_score FROM score WHERE s_id=sid AND c_id='03') AS 英语
FROM student
JOIN score
ON student.s_id=score.s_id
GROUP BY student.s_id
36查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
SELECT s.s_name,c.c_name,sc.s_score
FROM student AS s
JOIN score AS sc
ON s.s_id =sc.s_id
JOIN course AS c
ON c.c_id=sc.c_id
WHERE sc.s_score>70
37查询不及格的课程
SELECT s.s_name, c.c_name, sc.s_score
FROM score AS sc
JOIN course AS c
ON sc.c_id=c.c_id
JOIN student AS s
ON sc.s_id=s.s_id
WHERE sc.s_score<60
38查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
SELECT s.s_id,s.s_name
FROM student AS s
JOIN score AS sc
ON s.s_id=sc.s_id
WHERE sc.c_id='01'
AND sc.s_score>80
39求每门课程的学生人数
SELECT c_id,COUNT(*) AS 学生人数
FROM score
GROUP BY c_id
40查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT student.*,MAX(score.s_score)
FROM student
JOIN score
ON student.s_id=score.s_id
WHERE student.s_id IN(SELECT sc.s_id
FROM course AS c
JOIN teacher AS t
ON c.t_id=t.t_id
JOIN score AS sc
ON c.c_id=sc.c_id
WHERE t.t_name='张三')
41查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT DISTINCT a.s_id,a.c_id,a.s_score
FROM score AS a
JOIN score AS b
ON a.s_id=b.s_id
WHERE a.c_id!=b.c_id
AND a.s_score=b.s_score
42查询每门功成绩最好的前两名
SELECT c_id AS cid,
(SELECT s_score FROM score WHERE c_id=cid ORDER BY s_score DESC LIMIT 1) AS 第一名,
(SELECT s_score FROM score WHERE c_id=cid ORDER BY s_score DESC LIMIT 1,1) AS 第二名
FROM score
GROUP BY c_id
43统计每门课程的学生选修人数（超过5人的课程才统计）
要求输出课程号和选修人数，查询结果按人数降序排列
若人数相同，按课程号升序排列
SELECT c_id,COUNT(*) AS 选修人数
FROM score
GROUP BY c_id
HAVING COUNT(*)>5
ORDER BY 选修人数 DESC,c_id ASC
44检索至少选修两门课程的学生学号
SELECT s_id
FROM score
GROUP BY s_id
HAVING COUNT(*)>=2
45查询选修了全部课程的学生信息
SELECT student.*
FROM student
JOIN score
ON student.s_id=score.s_id
GROUP BY student.s_id
HAVING COUNT(*)=(SELECT COUNT(DISTINCT c_id)
FROM course)
46查询各学生的年龄
按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
SELECT s_name,s_birth,
(DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(s_birth,'%Y')-
(CASE WHEN DATE_FORMAT(NOW(),'%m%d')<DATE_FORMAT(s_birth,'%m%d') THEN 1 ELSE 0 END)) AS 年龄
FROM student
47查询本周过生日的学生
SELECT student.*
FROM student
WHERE WEEK(s_birth,1)=WEEK(CURDATE(),1)
SELECT DATE(NOW())
SELECT CURDATE()
48查询下周过生日的学生
SELECT student.*
FROM student
WHERE WEEK(s_birth,1)=WEEK(CURDATE(),1)+1
49查询本月过生日的学生
SELECT student.*
FROM student
WHERE MONTH(s_birth)=MONTH(CURDATE())
50查询下月过生日的学生
SELECT student.*
FROM student
WHERE DATE_FORMAT(s_birth,'%m')=DATE_FORMAT(NOW(),'%m')+1
SELECT student.*
FROM student
WHERE MONTH(s_birth)=MONTH(CURDATE())+1
SELECT(NOW())
SELECT(DATE(NOW()))
SELECT(YEAR(NOW()))
SELECT(MONTH(NOW()))
SELECT(DAY(NOW()))
SELECT(TIME(NOW()))
SELECT(DATE_FORMAT(NOW(),'%Y'))
SELECT(YEAR(NOW()))
