MySQL数据库查询练习（子查询分步教程）

注：数据库表格附录在文末

– 1、与张三同乡的男生姓名
step1:SELECT snativeplace FROM student WHERE sname=‘张三’
step2:SELECT sname FROM student WHERE ssex=‘男’ AND snativeplace=(step1) AND sname != ‘张三’

SELECT sname FROM student WHERE ssex='男' AND snativeplace=(
SELECT snativeplace FROM student WHERE sname='张三') AND sname != '张三'

– 2、选修了马六老师所讲课程的学生人数
step1:SELECT tid FROM teacher WHERE tname=‘马六’
step2:SELECT cid FROM course WHERE tid=(step1)
step3:SELECT COUNT(*) FROM mark WHERE cid=(step2) GROUP BY cid

SELECT  COUNT(*) FROM mark WHERE cid=(
SELECT cid FROM course WHERE tid=(
SELECT tid FROM teacher WHERE tname='马六'))
GROUP BY cid

– 3、查询没学过"马"姓老师课的同学的学号、姓名
step1:SELECT tid FROM teacher WHERE tname LIKE ‘马%’
step2:SELECT cid FROM course WHERE tid in (step1)
step3:SELECT sid FROM mark WHERE cid in (step2)
step4:SELECT sid,sname FROM student WHERE sid NOT in(step3)

SELECT sid,sname FROM student WHERE sid NOT in(
SELECT  sid FROM mark WHERE cid in (
SELECT cid FROM course WHERE tid in (
SELECT tid FROM teacher WHERE tname LIKE '马%')))

– 4、“数学”课程得最高分的学生姓名，性别

step1:SELECT cid FROM course WHERE cname=‘数学’
step2:SELECT MAX(cmark) FROM mark WHERE cid=(step1)
step3:SELECT sid FROM mark WHERE cmark=(stp2) AND cid=(step1)
step4:SELECT sname,ssex from student WHERE sid =(step3)

SELECT sname,ssex from student WHERE sid =(
SELECT sid FROM mark WHERE cmark=(
SELECT MAX(cmark) FROM mark WHERE cid=(
SELECT cid FROM course WHERE cname='数学')) AND cid=(
SELECT cid FROM course WHERE cname='数学'))

– 5、统计每门课程的平均成绩，并按照成绩降序排序
SELECT AVG(cmark ) FROM mark GROUP BY cid ORDER BY AVG(cmark ) DESC

– 6、子查询实现查询“2班”“张三”同学的“英语成绩”
step1:SELECT sid FROM student WHERE sname=‘张三’ AND sclass=‘2班’
step2:SELECT cid FROM course WHERE cname=‘英语’
Step3:SELECT cmark FROM mark WHERE sid=(step1) AND cid=(step2)

SELECT cmark FROM mark WHERE sid=(
SELECT sid FROM student WHERE sname='张三' AND sclass='2班') AND cid=(
SELECT cid FROM course WHERE cname='英语')

– 7、查询“福建”地区学生所选修的全部课程名称
step1:SELECT sid FROM student WHERE snativeplace=‘福建’
step2:SELECT cid FROM mark WHERE sid in(step1)
step3:SELECT cname FROM course WHERE cid in(step2)

SELECT cname FROM course WHERE cid in(
SELECT cid FROM mark WHERE sid in(
SELECT sid FROM student WHERE snativeplace='福建'))

– 10、用子查询实现查询选修“高等数学”课的全部学生的高等数学总成绩
step1:SELECT cid FROM course WHERE cname=‘数学’
step2:SELECT SUM(cmark) FROM mark WHERE cid =(step1)

SELECT SUM(cmark) FROM mark WHERE cid =(
SELECT cid FROM course WHERE cname='数学') 

– 11、用子查询实现查询选修“高等数学”课的全部学生的所有课程总成绩
step1:SELECT cid FROM course WHERE cname=‘数学’
step2:SELECT sid FROM mark WHERE cid=(step1)
step3:SELECT SUM(cmark) FROM mark WHERE sid in(step2)

SELECT SUM(cmark) FROM mark WHERE sid in(
SELECT sid FROM mark WHERE cid=(
SELECT cid FROM course WHERE cname='数学'))

– 12、请用两种方法实现：查找所有成绩都在68分以上的学生姓名
– 第一种方法：多表连接

SELECT sname FROM student s,mark m WHERE s.sid=m.sid GROUP BY s.sid HAVING MIN(cmark)>=68

– 第二种方法：子连接
step1:SELECT sid FROM mark GROUP BY sid HAVING MIN(cmark)>=68
step2:SELECT sname FROM student WHERE sid in(step1)

SELECT sname FROM student WHERE sid in(
SELECT sid FROM mark GROUP BY sid HAVING MIN(cmark)>=68)

– 13、查找至少两门成绩在80分以上的学生姓名
step1:SELECT COUNT(*) b,sid FROM mark WHERE cmark>80 or cmark=80 GROUP BY sid
step2:SELECT sid FROM (step1) a WHERE b>2 OR b=2
step3:SELECT sname FROM student WHERE sid in(step2)

SELECT sname FROM student WHERE sid in(
SELECT sid FROM (
SELECT COUNT(*) b,sid FROM mark WHERE cmark>80 or cmark=80 GROUP BY sid) a WHERE b>2 OR b=2)

– 14、查询至少有一门课与张三同学所学相同的同学的学号和姓名
step1:SELECT sid FROM student WHERE sname=‘张三’
step2:SELECT cid FROM mark WHERE sid=(step1)
step3:SELECT sid FROM mark WHERE cid NOT in(step2)
step4:SELECT sname,sid FROM student WHERE sid not in(step3)

SELECT sname,sid FROM student WHERE sid not in(
SELECT sid FROM mark WHERE cid NOT in(
SELECT cid FROM mark WHERE sid=(
SELECT sid FROM student WHERE sname='张三')) )

– 15、没有选修“数学”课的学生的姓名
step1:SELECT cid FROM course WHERE cname=‘数学’
step2:SELECT sid FROM mark WHERE cid=(step1)
step3:SELECT sname FROM student WHERE sid NOT in(step2)

SELECT sname FROM student WHERE sid NOT in(
SELECT sid FROM mark WHERE cid=(
SELECT cid FROM course WHERE cname='数学'))

– 16、查询个人总成绩小于平均总成绩的学生姓名
step1:SELECT sid,SUM(cmark) b FROM mark GROUP BY sid
step2:SELECT AVG(b) from (step1) a
step3:SELECT sid FROM (step1) a WHERE b>(step2)
step4:SELECT sname FROM student WHERE sid in(step3)

SELECT sname FROM student WHERE sid in(
SELECT sid FROM (
SELECT sid,SUM(cmark) b FROM mark GROUP BY sid) a WHERE b>(
SELECT AVG(b) from (
SELECT sid,SUM(cmark) b FROM mark GROUP BY sid) a ))

– 17、用子查询实现张三在其各科成绩中最高分成绩所对应的课程号和成绩
step1:SELECT sid FROM student WHERE sname=‘张三’
step2:SELECT MAX(cmark) FROM mark WHERE sid=(step1)
step3:SELECT cid,cmark FROM mark WHERE cmark=(step2) AND sid=(step1)

SELECT cid,cmark FROM mark WHERE cmark=(
SELECT MAX(cmark) FROM mark WHERE sid=(
SELECT sid FROM student WHERE sname='张三') ) AND sid=(
SELECT sid FROM student WHERE sname='张三')

– 18、找出张三的最高分和最低分以及对应的课程名
step1:SELECT sid FROM student WHERE sname=‘张三’
step2:SELECT MAX(cmark) x FROM mark WHERE sid=(step1)
step3:SELECT MIN(cmark) x FROM mark WHERE sid=(step1)
step4:SELECT cid FROM mark WHERE (cmark in(step2) OR cmark in(step3)) AND sid=(step1)
step5:SELECT cname,cmark FROM course c,mark m WHILE c.cid=m.cid AND sid=(step1) AND m.cid in(step4)

SELECT cname,cmark FROM course c,mark m WHERE c.cid=m.cid AND sid=(SELECT sid FROM student WHERE sname='张三') AND m.cid in(
SELECT cid FROM mark WHERE (cmark=(
SELECT MAX(cmark) x FROM mark WHERE sid=(SELECT sid FROM student WHERE sname='张三')) OR cmark=(
SELECT MIN(cmark) x FROM mark WHERE sid=(SELECT sid FROM student WHERE sname='张三'))) AND sid=(
SELECT sid FROM student WHERE sname='张三'))

– 19、哪些学生的各科成绩均高于张三
step1:SELECT sid FROM student WHERE sname=‘张三’
step2:SELECT cmark c1,cid FROM mark WHERE sid=(step1)
step3:SELECT DISTINCT sid FROM mark ,(step2) a WHERE mark.cid=a.cid AND cmark<c1
step4:SELECT sname FROM student WHERE sid NOT in(step3) AND sid NOT step1

SELECT sname FROM student WHERE sid NOT in(
SELECT DISTINCT sid FROM mark ,(
SELECT cmark c1,cid FROM mark WHERE sid=(
SELECT sid FROM student WHERE sname='张三')) a WHERE mark.cid=a.cid AND cmark<c1 ) AND sid != (SELECT sid FROM student WHERE sname='张三')

student表：

course表：

teacher表：

mark表部分：