常见sql问题总结（一）

 

问题以及表结构来自：https://www.cnblogs.com/wupeiqi/articles/5729934.html

-- 查询学生本人“生物”课程比“物理”课程成绩高的所有学生的学号；
SELECT * FROM (
(SELECT * FROM score WHERE corse_id in (SELECT cid FROM course WHERE cname='生物')) t1 -- 查出所以生物试卷成绩
LEFT JOIN -- 左关联
(SELECT * FROM score WHERE corse_id in (SELECT cid FROM course WHERE cname='物理')) t2 -- 查出所以物理试卷成绩
on t1.student_id = t2.student_id -- 是同一个人
) WHERE t1.number>t2.number; -- 判断生物成绩>物理成绩

-- 查询平均成绩大于60分的同学的学号和平均成绩； 注意：mysql5.7以后，GROUP BY 必须配合聚合函数使用
SELECT * FROM
(SELECT AVG(number)as num ,student_id FROM score GROUP BY student_id ORDER BY student_id) a -- 计算每个人的平均分 作为中间表
WHERE a.num>60;

-- 查询所有同学的学号、姓名、选课数、总成绩；
SELECT SUM(number)as sun_num ,student_id ,COUNT(sid)as count_id FROM score GROUP BY student_id ;
SELECT SUM(score.number)as sun_num ,student_id ,COUNT(score.sid)as count_id FROM score LEFT JOIN student on score.student_id=student.sid GROUP BY score.student_id ;

-- 查询姓“李”的老师的个数；
SELECT COUNT(tid) FROM teacher WHERE tname LIKE '李%';

-- 查询没学过“张老师”老师课的同学的学号、姓名；
SELECT * FROM student WHERE sid not in(
-- 查询这些课程的所有学生
SELECT student_id FROM score WHERE corse_id in
-- 查询张老师教的所有课
(
SELECT cid FROM course WHERE tearch_id in (SELECT tid FROM teacher WHERE tname='张老师')
)
)
GROUP BY sid ;

-- 查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
SELECT student_id FROM score WHERE corse_id in (SELECT cid FROM course WHERE cid in (1,2)) ;

-- 查询学过“张”老师所教的所有课的同学的学号、姓名 并且成绩大于200；
SELECT sname,sid FROM student WHERE sid
in
(SELECT student_id FROM score WHERE corse_id
in
( select cid from course WHERE tearch_id in
( SELECT tid FROM teacher WHERE tname='张老师'))
and number>200) ;

-- 查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
SELECT * FROM student WHERE sid in (
SELECT t1.student_id FROM(
(select * FROM score WHERE corse_id=2) t2 -- 找到课程2的所以成绩
LEFT JOIN
(select * FROM score WHERE corse_id=1) t1 -- 找到课程1的所以成绩
on t2.student_id = t1.student_id) -- 找到学习了课程2和1的学生
WHERE t2.number < t1.number -- 判断2小于1
) -- 输出

-- 查询有课程成绩小于60分的同学的学号、姓名;
select sname as '名字',sid FROM student WHERE sid in (select student_id FROM score WHERE number<60);