常见SQL总结二
-- 查询没有学全所有课的同学的学号、姓名；
SELECT sid,sname FROM student stu,
(SELECT COUNT(sid) as num,student_id FROM score GROUP BY student_id)t1 , -- 查出每个人的学习课程数量
(SELECT COUNT(cid)as num FROM course)t2 -- 再查出所有课程数量
WHERE
t2.num>t1.num and stu.sid=t1.student_id -- 比较当前人的学习数量 找出学习不完整的;
-- 查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名
select DISTINCT student_id FROM score
WHERE corse_id in (SELECT corse_id FROM score WHERE student_id=2) and student_id !=1;
-- 查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名
-- 查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名
select score_1.student_id,score_1.corse_id FROM
(select corse_id FROM score WhERE student_id=2) a
LEFT JOIN
score score_1
on a.corse_id=score_1.corse_id -- 找出 学生2所学的课程以及学生
WHERE score_1.student_id !=2 -- 排除2本人
GROUP BY score_1.student_id -- 去重复（按照课程查会查出多个人）
HAVING
COUNT(score_1.corse_id) -- 计数
=
(select COUNT(corse_id) FROM score WhERE student_id=2) -- 学员2的学习数量
;
-- 向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；
INSERT INTO score(student_id,corse_id,number)
select sid as student_id,2,(select AVG(number) FROM score WHERE corse_id=2) FROM student WHERE sid not in (select student_id FROM score WHERE corse_id=2)
-- 按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,物理,生物,有效课程数,有效平均分；
select aaa.sid as 学生id,
bbb.avstu as 平均分,
(select number FROM score WHERE score.student_id=aaa.sid and corse_id=1 ) as 生物,
(select number FROM score WHERE score.student_id=aaa.sid and corse_id=2 ) as 物理,
(select number FROM score WHERE score.student_id=aaa.sid and corse_id=4 ) as 语文
,bbb.stu_sid as 数量
FROM
(SELECT DISTINCT sid FROM student)aaa
INNER JOIN
(SELECT AVG(number) as avstu,COUNT(sid) as stu_sid,student_id FROM score GROUP BY student_id)bbb
on aaa.sid=bbb.student_id ORDER BY 平均分;
-- 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
SELECT bb.corse_id as 课程id, MAX(bb.number)as 最高分 ,MIN(bb.number) as 最低分 FROM
(select * FROM course) aa
LEFT JOIN
(SELECT * FROM score) bb
on aa.cid=bb.corse_id GROUP BY bb.corse_id
-- 按各科平均成绩从低到高和及格率的百分数从高到低顺序
--
-- COALESCE()该函数处理非空数据
SELECT
COALESCE(AVG(bb.number),0) as 平均,
100*SUM(CASE
WHEN COALESCE(bb.number,0)>=60 THEN
1
ELSE
0
END
)/COUNT(*) as 及格百分比
FROM
(select * FROM course) aa
LEFT JOIN
(SELECT * FROM score) bb
on aa.cid=bb.corse_id GROUP BY bb.corse_id ORDER BY 及格百分比
-- 课程平均分从高到低显示（现实任课老师）
SELECT tt.tname ,bb.num FROM
(select * FROM teacher) tt
INNER JOIN
(select * FROM course) aa on tt.tid=aa.tearch_id
INNER JOIN
(SELECT AVG(number) as num,corse_id FROM score GROUP BY corse_id)bb
on aa.cid=bb.corse_id ORDER BY bb.num desc
