时间：2021-07-01 10:21:17 帮助过：46人阅读
)
INSERT INTO score (student_id, corse_id, number) VALUE (1, 1, 60),(1, 2, 59), (2, 2, 100);
#1t生物成绩比体育成绩高的学生学号
select A.student_id from
(select score.sid ,score.student_id,cname,number from scourse
left join score on score.corse_id = scourse.cid
where cname = "生物"）as A
left join
(select score.sid ,score.student_id,cname,number from scourse
left join score on score.corse_id = scourse.cid
where cname = "体育") as B
on A.student_id = B.student_id
where A.number < B.number
#2查询平均成绩大于60分的同学的学号和平均成绩
SELECT student_id ,AVG(number) FROM score GROUP BY student_id HAVING AVG(number)>60
加上学生姓名
select cname ,student_id from
（SELECT student_id ,AVG(number) FROM score GROUP BY student_id HAVING AVG(number)>60 ）as A
left join student on student.sid = A.studend_id
#3.所有同学的学号 姓名 课程数目
select student_id ,sname ,count(1 ) from score left join student on student.sid = score.student_id group by student_id
#4查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select student_id ,sname from
(select student_id from score where corse_id =1 or corse_id =2 group by corse_id having count (1) >1) as A
left join student on student.sid = A.student_id
#5查询有课程成绩小于60分的同学的学号、姓名；
select student_id ,sname from
(select student_id from score where number <60) as A
left join student on student.sid = A.student_id
#6删除“001”同学的“001”课程的成绩；
DELETE FROM score WHERE corse_id=1 AND student_id=1;
#7查询一门课程成绩最好的前两名；
select student_id , corse_id,
(select number from score as b where b.corse_id =a.corse_id group by b.number order by b.number desc limit 0,1 ) as first,
(select number from score as b where b.corse_id = a.corse_id group by b.number order by b.number desc limit 1,1 ) as second
from score as a;
#8查询男生、女生的人数；
select gendert ,count(1) from student group by gender
#9查询出只选修了一门课程的全部学生的学号和姓名；
select student_id,sname from
(select * from score group by corse_id having count(1) =1) as A
left join student on student.sid = A.student_id
#10查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
SELECT student_id,MAX(number),MIN(number) ,AVG(number) FROM score
LEFT JOIN student ON score.student_id=student.sid
LEFT JOIN scourse ON score.corse_id = scourse.cid
LEFT JOIN class ON student.class_id = class.cid
LEFT JOIN teacher ON scourse.teacher_id = teacher.tid
GROUP BY corse_id
#11课程平均分从高到低显示（现实任课老师）；
select corse_id,avg(number) from score group by corse_id order by avg(number ) desc
#12所有同学的学号 姓名 课程数目
SELECT student_id , student.sname , COUNT(1) FROM score
LEFT JOIN student ON student.sid = score.student_id GROUP BY student_id
#13查询那个同学没上过某某老师的课
select * from score where corse_id not in
(select cid from scourse left join teacher on scourse.teacher_id = teacher.tid where teacher.tname = "波动")
#14查询选过某某老师全部课程的同学学号
SELECT student_id FROM score WHERE corse_id IN
(SELECT cid FROM scourse LEFT JOIN teacher ON teacher.tid = scourse.teacher_id WHERE teacher.tname = ‘波动‘)
GROUP BY student_id HAVING COUNT(1) = (SELECT COUNT(1) FROM scourse LEFT JOIN teacher ON teacher.tid = scourse.teacher_id WHERE teacher.tname = ‘波动‘)
#15查询不及格的同学
SELECT student_id FROM score WHERE number < ‘60‘ GROUP
#16 查询没有学全的同学
SELECT student_id,COUNT(1) FROM score
GROUP BY (student_id) HAVING COUNT(1) <
(SELECT COUNT(1) FROM scourse )
#17 查询至少学过一门课和学号为1的同学所学的课程相同的同学
SELECT student_id FROM score WHERE student_id !=1
AND corse_id IN (SELECT corse_id FROM score WHERE student_id =1 ) GROUP BY student_id
#18 查询至少学过一门课和学号为1的同学所学的课程所有课程的同学
SELECT student_id,count(1) FROM score WHERE student_id !=1
AND corse_id IN (SELECT corse_id FROM score WHERE student_id =1 ) GROUP BY student_id
HAVING COUNT(1)=(SELECT count(corse_id) FROM score WHERE student_id =1 )
#19查询和“001”号的同学学习的课程完全相同的其他同学学号和姓名
第一步 找出课程个数相同的同学
select studednt_id from score where student_id != 1 group by student_id having
count(1) = (select count(1) from score where student_id = 1)
第二步 个数相同且课程相同的同学
select * from score where student_id in (
select studednt_id from score where student_id != 1 group by student_id having
count(1) = (select count(1) from score where student_id = 1)
) and corse_id in ( select corse_id from score where student_id =1 )
#20
向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；
insert into score(student_id,corse_id,number)
select student_id, 2 , (SELECT AVG(number) FROM score WHERE corse_id =2 ) from score where corse_id !=2
#21、
按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
select
student_id,
(select number from score as b where b.student_id = a.student_id and corse_id = 1 ) as 语文,
(SELECT number FROM score AS b WHERE b.student_id = a.student_id AND corse_id = 2 ) as 数学,
(SELECT number FROM score AS b WHERE b.student_id = a.student_id AND corse_id = 3 )as 英语
from score as a group by student_id
#22、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分（）
select corse_id , max(number) , min(number ),
case when min(number)=59 then 60 else min(number) end as sise秘密
from score group by student_id
#23、按各科平均成绩从低到高和及格率的百分数从高到低顺序；、
select corse_id ,avg(number),
sum(case when number >= 60 then 1 else 0 end )/sum(1) as 及格率
from socre group by corse_id order by avg(number ) asc ， 及格率 desc;
#24、课程平均分从高到低显示（现实任课老师）；
select corse_id ,avg(number) , teacher.tname from score
left join scourse on scourse.cid = score.corse_id
left join teacher on teacher.tid = scourse.teacher_id
group by corse_id
#25、查询各科成绩前两 名的记录:(不考虑成绩并列情况)
（
select
student_id,
corse_id,
(select number from score as b where b.corse_id=a.corse_id group by b.number order by b.number desc limit 0,1 ) as first ,
(SELECT number FROM score AS b WHERE b.corse_id=a.corse_id GROUP BY b.number ORDER BY b.number DESC LIMIT 1,1 )as second
from score as a;
）
#26、查询出只选修了一门课程的全部学生的学号和姓名；
select student_id, count(1) from score group by student_id having count(1) =1
#27、查询姓“张”的学生名单；
select sname from student where sname like "钢%"
#28、查询同名同姓学生名单，并统计同名人数；
select sname,count(1) from student group by sname
#29、查询全部学生都选修的课程的课程号和课程名；
select * from score as a, score as b where
a.sid != b.sid and a.corse_id = b.corse_id group by corse_id
select corse_id from score group by corse_id
having count(corse_id)=
(select count(sname) from student )
#30、查询没学过“叶平”老师讲授的任一门课程的学生姓名；
select * from student where sid not in
(select student_id from score
left join student on student.sid = score.student_id
WHERE corse_id IN
(SELECT cid FROM scourse
LEFT JOIN teacher ON teacher.`tid`= scourse.`teacher_id`
WHERE tname= "波动")
)
Mysql学习日记-03sql语句练习
标签：group by 老师 ref sum delete sele count style when