Mysql进⾏复杂查询
1.查询“⽣物”课程⽐“物理”课程成绩⾼的所有学⽣的学号；
思路：（1）获取所有选了⽣物课程的学⽣的成绩（学号，成绩） --临时表
  （2）获取所有选了物理课程的学⽣的成绩（学号，成绩） --临时表
  　（3）根据学号连接两张临时表（学号，⽣物成绩，物理成绩），加条件进⾏查询
SELECT
A.student_id AS学号,
sw AS⽣物,
wl AS物理
FROM
(
SELECT
student_id,
num AS sw
FROM
score
LEFT JOIN course urse_id = course.cid
WHERE
courseame ='⽣物'
) AS A
LEFT JOIN (
SELECT
student_id,
num AS wl
FROM
score
mysql group by order byLEFT JOIN course urse_id = course.cid
WHERE
courseame ='物理'
) AS B ON A.student_id = B.student_id
WHERE
sw >
IF (isnull(wl), 0, wl);
View Code
2.查询平均成绩⼤于60分的同学的学号和平均成绩；
思路：（1）根据学号分组
  （2）使⽤avg（）聚合函数计算平均成绩
  （3）通过having对平均成绩进⾏筛选
SELECT student_id,avg(num) FROM score
LEFT JOIN course
ON score.student_id=course.cid
GROUP BY student_id
HAVING avg(num)>60
View Code
3.查询所有同学的学号、姓名、选课数、总成绩；
思路：根据学号分组，使⽤count（）对选课数计数，sum（）计算总成绩
SELECT
student_id,
sname,
count(student_id),
sum(num)
FROM
score LEFT JOIN student ON score.student_id = student.sid
GROUP BY student_id
View Code
4.查询姓“李”的⽼师的个数；
思路：使⽤like及通配符匹配，count（）进⾏计数
SELECT count(tid) FROM teacher
WHERE tname LIKE'李%'
View Code
5.查询没学过“李平”⽼师课的同学的学号、姓名；
思路：（1）连接成绩表课程表教师表得到选了李平⽼师课程的学⽣
（2）再通过学⽣表筛选结果
SELECT sid,sname FROM student
WHERE sid not IN
(SELECT student_id FROM
(SELECT cid,teacher_id,student_id,course_id
FROM score
LEFT JOIN course urse_id=course.cid
) AS A
LEFT JOIN teacher
acher_id=teacher.tid ame='李平⽼师'GROUP BY student_id
)
View Code
6.查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
思路：（1）筛选出学过001课程的学⽣或学过002课程的学⽣
  （2）根据学⽣分组，如果学⽣数量等于2，则该学⽣选择了以上两门课程
SELECT student.sid,student.sname FROM
(SELECT student_id,count(student_id) FROM score LEFT JOIN course urse_id=course.cid  WHERE course.cid='001'or course.cid='002'GROUP BY student_id HAVING count(student_id)>1 ) AS A LEFT JOIN student ON A.student_id=student.sid;
View Code
7.查询学过“李平”⽼师所教的所有课的同学的学号、姓名；
思路：（1）查询李平⽼师所教的课程
（2）在成绩表中筛选出学⽣选择的课程 in 李平⽼师的课程
SELECT student_id,sname FROM
(SELECT student_id FROM score
WHERE course_id IN
(SELECT cid FROM teacher LEFT JOIN course ON teacher.acher_id WHERE tname='李平⽼师')
GROUP BY student_id
) AS B
LEFT JOIN student
ON B.student_id=student.sid
View Code
8.查询课程编号“002”的成绩⽐课程编号“001”课程低的所有同学的学号、姓名；
思路：（1）分别获取选择了课程001和002的学⽣和成绩；
  （2）连接两张表，筛选出001的成绩⼤于002成绩的学⽣
SELECT student_id,sname FROM
(SELECT A.student_id FROM
(SELECT student_id,num FROM score WHERE course_id=001) AS A
LEFT JOIN
(SELECT student_id,num FROM score WHERE course_id=002) AS B
ON A.student_id=B.student_id
WHERE A.num>B.num) AS C
LEFT JOIN student
ON C.student_id=student.sid
View Code
9.查询有课程成绩⼩于60分的同学的学号、姓名；
思路：（1）筛选出成绩⼩于60的学⽣，并通过学⽣分组  --临时表
  （2）在学⽣表中筛选出 in 临时表中的学⽣
SELECT sid,sname FROM student WHERE sid IN
(SELECT student_id FROM score WHERE num<60GROUP BY student_id)
View Code
10.查询没有学全所有课的同学的学号、姓名；
思路：（1）统计出总课程数
（2）成绩表中，通过学⽣分组，统计出每个学⽣的课程数，如果课程数等于总课程数，则表⽰选择了所有课程
SELECT sid,sname FROM student WHERE sid not IN
(SELECT student_id
FROM score
GROUP BY student_id HAVING count(course_id) = (SELECT count(cid) FROM course))
View Code
11.查询⾄少有⼀门课与学号为“001”的同学所学课程相同的同学的学号和姓名；
思路：（1）查学号001同学所学的所有课程  --临时表
  （2）其他学⽣所学的课程如果在临时表中，则符合条件
SELECT student_id,sname FROM student LEFT JOIN score ON score.student_id=student.sid
WHERE course_id in (SELECT course_id FROM score WHERE student_id=001) AND student_id !=001
GROUP BY student_id
View Code
12.查询⾄少学过学号为“001”同学所选课程中任意⼀门课的其他同学学号和姓名；
13.查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
14.删除学习“叶平”⽼师课的成绩表记录；
DELETE FROM score WHERE course_id IN
(SELECT cid FROM course LEFT JOIN teacher acher_id=teacher.tid WHERE tname='叶平⽼师')
View Code
15.向成绩表中插⼊⼀些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插⼊“002”号课程的平均成绩；
16.按平均成绩从低到⾼显⽰所有学⽣的“语⽂”、“数学”、“英语”三门的课程成绩，按如下形式显⽰：学⽣ID，语⽂，数学，英语，有效课程数，有效平均分；
select sc.student_id,
(select num from score left join course urse_id = course.cid where courseame = "⽣物" and score.student_id=sc.student_id) as sy,
(select num from score left join course urse_id = course.cid where courseame = "物理" and score.student_id=sc.student_id) as wl,
(select num from score left join course urse_id = course.cid where courseame = "体育" and score.student_id=sc.student_id) as ty, urse_id),
avg(sc.num)
from score as sc
group by student_id desc
View Code
17.查询各科成绩最⾼和最低的分：以如下形式显⽰：课程ID，最⾼分，最低分；
SELECT course_id,max(num) as最⾼分,min(num) as最低分
FROM score
GROUP BY course_id
View Code
18.按各科平均成绩从低到⾼和及格率的百分数从⾼到低顺序；
思路：三元运算（三⽬运算），case .. when .. then .. else .. end
SELECT course_id,avg(num) AS平均分,sum(CASE WHEN score.num>60THEN1ELSE0END)/count(1)*100AS及格率FROM score GROUP BY course_id
ORDER BY平均分ASC，及格率DESC
19.课程平均分从⾼到低显⽰（显⽰任课⽼师）；
SELECT course_id,avg(num),ame FROM score LEFT JOIN course urse_id=course.cid LEFT JOIN teacher acher_id=teacher.tid
GROUP BY course_id
ORDER BY avg(num) DESC
View Code
20.查询各科成绩前三名的记录:(不考虑成绩并列情况) ;
21.查询每门课程被选修的学⽣数；
SELECT course_id,count(student_id) FROM score
GROUP BY course_id
View Code
22.查询出只选修了⼀门课程的全部学⽣的学号和姓名；
SELECT student_id,student.sname FROM score LEFT JOIN student ON score.student_id=student.sid
GROUP BY student_id
HAVING count(student_id)=1
View Code
23.查询男⽣、⼥⽣的⼈数；
SELECT
(SELECT count(1) FROM student WHERE gender='男') AS男,
(SELECT count(1) FROM student WHERE gender='⼥') As⼥
View Code
24.查询姓“张”的学⽣名单；
SELECT*FROM student WHERE student.sname LIKE'张%'
25.查询同名同姓学⽣名单，并统计同名⼈数；
SELECT sname,count(sname) FROM student GROUP BY sname HAVING count(sname)>1
26.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
SELECT course_id,avg(num) FROM score GROUP BY course_id ORDER BY course_id ASC,course_id DESC
27.查询平均成绩⼤于85的所有学⽣的学号、姓名和平均成绩；
SELECT student_id,sname,avg(num) FROM score LEFT JOIN student ON score.student_id=student.sid
GROUP BY student_id HAVING avg(num)>85
28.查询课程名称为“数学”，且分数低于60的学⽣姓名和分数；
SELECT sname,num FROM score LEFT JOIN course urse_id=course.cid LEFT JOIN student ON score.student_id=student.sid  WHERE cname='数学'AND num>60 29.查询课程编号为003且课程成绩在80分以上的学⽣的学号和姓名；
SELECT student_id,sname FROM score LEFT JOIN student ON score.student_id=student.sid WHERE course_id=003AND num>80
30.求选了课程的学⽣⼈数
select count(distinct student_id) from score
31.查询选修“杨艳”⽼师所授课程的学⽣中，成绩最⾼的学⽣姓名及其成绩；
思路：根据学⽣排序，成绩按从⼤到⼩排序，limit取最⾼的成绩
SELECT sname,max(num) FROM score LEFT JOIN course urse_id=course.cid LEFT JOIN teacher acher_id=teacher.tid
LEFT JOIN student ON score.student_id=student.sid
WHERE tname='张磊⽼师'
GROUP BY student_id
ORDER BY max(num) DESC
LIMIT 1
32.查询各个课程及相应的选修⼈数；
SELECT cid,cname,count(student_id) FROM score LEFT JOIN course urse_id=course.cid
GROUP BY course_id
33.查询不同课程但成绩相同的学⽣的学号、课程号、学⽣成绩；
select urse_urse_id,s1.num,s2.num from
score as s1, score as s2 where s1.num = s2.num urse_id != s2.course_id;
34.查询每门课程成绩最好的前两名；
SELECT*FROM score LEFT JOIN
(SELECT course_id,
(SELECT num FROM score urse_urse_id ORDER BY num DESC LIMIT 0,1) AS'第⼀名',
(SELECT num FROM score urse_urse_id ORDER BY num DESC LIMIT 1,1) AS'第⼆名'
FROM score AS A
GROUP BY course_id) AS B
urse_urse_id
View Code
35.检索⾄少选修两门课程的学⽣学号；
思路：根据学号分组，统计
SELECT student_id,count(course_id) FROM score GROUP BY student_id HAVING count(course_id)>=2
36.查询全部学⽣都选修的课程的课程号和课程名；
思路：从学⽣表中统计出学⽣总数，在成绩表中根据课程分组，如果选择没门课程的⼈数等于学⽣总数，
则符合
SELECT course_id,cname FROM score LEFT JOIN course urse_id=course.cid GROUP BY course_id HAVING count(student_id)=
(SELECT count(sid) FROM student)
37.查询没学过“叶平”⽼师讲授的任⼀门课程的学⽣姓名；
SELECT sname FROM score LEFT JOIN student ON score.student_id=student.sid WHERE course_id NOT IN
(SELECT cid FROM teacher LEFT JOIN course acher_id=teacher.tid WHERE tname='叶平⽼师')
38.查询两门以上不及格课程的同学的学号及其平均成绩；
思路：通过学⽣分组，筛选出不及格课程数
SELECT student_id,avg(num) FROM score WHERE num<60GROUP BY student_id HAVING count(1)>2
39.检索“004”课程分数⼩于60，按分数降序排列的同学学号；
SELECT student_id FROM score WHERE course_id=004AND num<60ORDER BY num DESC
40.删除“002”同学的“001”课程的成绩；
DELETE FROM score WHERE student_id=002AND course_id=001