mysql常用的一些查询

– 数据库表的设计
– student(stu_id,stu_name,Sage,Ssex) 学生表
– course(course_id,course_name,T#) 课程表
– score(score_id,stu_id,course_id,score_cont) 成绩表
– teacher(teacher_id,Tname) 教师表
或者
– student(stu_id,stu_name,Sage,Ssex) 学生表
– course(course_id,course_name,T#) 课程表
– score(score_id,stu_id,course_id,score_cont) 成绩表
– teacher(teacher_id,Tname) 教师表

– 1、查询“001”课程比“002”课程成绩高的所有学生的学号；
SELECT a.stu_id
FROM (SELECT stu_id,score_cont FROM score where course_id=‘1’) a,(SELECT stu_id,score_cont FROM score where course_id=‘2’) b
WHERE a.stu_id=b.stu_id AND a.score_cont>b.score_cont

– 2、查询平均成绩大于60分的同学的学号和平均成绩；
SELECT a.stu_id,AVG(a.score_cont)
FROM score a
GROUP BY a.stu_id HAVING AVG(a.score_cont)>60

– 3、查询所有同学的学号、姓名、选课数、总成绩；
SELECT b.stu_id,b.stu_name, SUM(a.score_cont) AS ‘总分’,COUNT(a.course_id) AS ‘选课数’
FROM score a LEFT JOIN student b on a.stu_id=b.stu_id
GROUP BY a.stu_id

– 4、查询姓“张”的老师的个数；
SELECT COUNT(teacher_name)
FROM teacher a
WHERE a.teacher_name LIKE ‘张%’

– 5、查询没学过“王朝”老师课的同学的学号、姓名；
– 方法一：（建议使用方法一，数据库查询优化策略是减少where查询）
SELECT a.stu_id,a.stu_name
FROM student a
WHERE a.stu_id NOT IN(SELECT DISTINCT(a.stu_id) FROM score a LEFT JOIN course b ON a.course_id=b.course_id LEFT JOIN teacher c ON b.teacher_id=c.teacher_id WHERE c.teacher_name=‘王朝’)

– 方法二：
select Student.S#,Student.Sname
from Student
where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=‘叶平’);

– 6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

– 错误写法1（查询学过任意一个的结果）
SELECT a.stu_id,a.stu_name
FROM student a LEFT JOIN score b on a.stu_id=b.stu_id
WHERE b.course_id=‘1’ and EXISTS (SELECT * from score WHERE course_id=‘3’)

– 错误写法2（查询学过任意一个的结果，where判断）
SELECT a.stu_id,a.stu_name
FROM student a,score b
WHERE a.stu_id=b.stu_id AND b.course_id=‘1’ AND EXISTS(SELECT *
FROM student a,score b
WHERE a.stu_id=b.stu_id AND a.course_id=‘3’)

– 正确写法（exists 返回Boolean）
SELECT a.stu_id,a.stu_name
FROM student a,score b
WHERE a.stu_id=b.stu_id AND b.course_id=‘1’ AND EXISTS(SELECT *
FROM score c
WHERE c.stu_id=b.stu_id AND c.course_id=‘3’)

– 7、查询学过“叶王朝”老师所教的所有课的同学的学号、姓名；

– 方法一：
SELECT *
FROM student
WHERE stu_id IN(
SELECT a.stu_id
FROM score a LEFT JOIN course b ON a.course_id=b.course_id LEFT JOIN teacher c ON b.teacher_id=c.teacher_id
WHERE c.teacher_name=‘王朝’ GROUP BY a.stu_id HAVING COUNT(a.course_id)=(SELECT COUNT(c.course_id) FROM course c,teacher d
WHERE c.teacher_id=d.teacher_id AND d.teacher_name=‘王朝’ )
)
– 方法二：
select S#,Sname
from Student
where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=‘王朝’
group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname=‘王朝’));

– 8、查询所有课程成绩小于60分的同学的学号、姓名；
– (distinct 去重)
– 方法一：
SELECT DISTINCT(b.stu_id),b.stu_name
FROM score a LEFT JOIN student b ON a.stu_id=b.stu_id
WHERE a.stu_id NOT IN(SELECT c.stu_id FROM score c WHERE c.score_cont>60 GROUP BY c.stu_id )
– 方法二：
select S#,Sname
from Student
where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);

– 9.计算每个同学的总成绩并且取排名前两位
SELECT a.stu_id,SUM(a.score_cont)
FROM score a
GROUP BY a.stu_id
ORDER BY SUM(a.score_cont) DESC
LIMIT 2

– 10.列出各门课成绩最好的两个学生(有难度)

– 如果考虑例如有两个同分数的第二名可以用下面的语句
SELECT a.stu_id,a.score_cont,a.course_id
FROM score a
WHERE
(SELECT COUNT(DISTINCT score_cont)
FROM score b
WHERE b.course_id = a.course_id AND b.score_cont >= a.score_cont

)<=2

ORDER BY a.course_id ASC, a.score_cont DESC

– 如果有同分数的两个第二名则删除第二名：
SELECT a.stu_id,a.score_cont,a.course_id
FROM score a
WHERE
(SELECT COUNT(*)
FROM score b
WHERE b.course_id = a.course_id AND b.score_cont >= a.score_cont

)<=2

ORDER BY a.course_id ASC, a.score_cont DESC

– 11.统计优良差的学生数量
SELECT course_id,
(SELECT COUNT(1) FROM score where score_cont<60 AND course_id=a.course_id) AS ‘不及格’,
(SELECT COUNT(1) FROM score where score_cont BETWEEN 60 AND 90 AND course_id=a.course_id) as ‘及格’,
(SELECT COUNT(1) FROM score where score_cont>90 AND course_id=a.course_id) as ‘优秀’
FROM score a
GROUP BY a.course_id