学好sql查询:无他,概手熟耳。
学生表:
老师表:
课程表:
成绩表:
select * from 学生 where sname like '%华%'
select sage,count(sid) from 学生 group by sage
select cid,avg(cscore) from 成绩 group by cid order by avg(cscore),cid desc
select sid,avg(cscore) from 成绩 group by sid order by avg(cscore) desc
select sc.cid 课程id, c.cname 课程名, max(sc.cscore) 最高分,min(sc.cscore) 最低分,avg(sc.cscore) 平均分 from 成绩 sc left join 课程 c on sc.cid=c.cid group by sc.cid
select cid, avg(cscore) from 成绩 group by cid order by avg(cscore) desc
select sid 学号,sum(cscore) 总分 from 成绩 group by sid order by sum(cscore) desc
select sid from 成绩 group by sid having count(cid) > 1
select cid,count(cid) from 成绩 group by cid
select * from 成绩 group by cid having cscore < 60 order by cid desc
select sid,sname from 学生 where sid in( select sid from 成绩 group by sid having count(sid) = 1 )
select sid,sname from 学生 where sid in( select sid from 成绩 where cid = 'c01' and cscore>=60 )
select sc1.sid from 成绩 sc1, 成绩 sc2 where sc1.sid = sc2.sid and sc1.cid = 'c01' and sc2.cid = 'c02' and sc1.cscore > sc2.cscore
select sid, avg(cscore) from 成绩 group by sid having avg(cscore) > 60
select count(1) from 教师 where tname like '李%'
select sid, sname from 学生 where sid not in( select sid from 成绩 where cid =( select cid from 课程 where cteacher = '何倩文' ) )
select sid, sname from 学生 where sid in( select sc1.sid from 成绩 sc1 where sid in( select sc2.sid from 成绩 sc2 where cid = 'c01' ) and cid = 'c02' )
或
select sid,sname from 学生 where sid in( select sc1.sid from 成绩 sc1,成绩 sc2 where sc1.sid=sc2.sid and sc1.cid = 'c01' and sc2.cid = 'c02' )
select sid,sname from 学生 where sid not in( select distinct(sid) from 成绩 where cid in ( select cid from 课程 where cteacher = '李征辉' ) )
select sc.sid, s.sname, count(sc.cid), sum(cscore) from 成绩 sc left join 学生 s on sc.sid = s.sid group by sc.sid
select sid, sname from 学生 where sid in( select distinct(sc.sid) from 成绩 sc left join 课程 c on sc.cid=c.cid where c.cteacher = '李征辉' ) order by sid
select sid, sname from 学生 where sid in( select sc1.sid from 成绩 sc1, 成绩 sc2 where sc1.sid = sc2.sid and sc1.cid = 'c01' and sc2.cid = 'c02' and sc1.cscore < sc2.cscore )
select sid, sname from 学生 where sid not in( select distinct(sid) from 成绩 where cscore >= 60 ) and sid in( select sid from 成绩 group by sid having count(sid) != 0 )
或
select sid, sname from 学生 where sid not in( select s.sid from 学生 s left join 成绩 sc on s.sid=sc.sid where cscore > 60 or cscore is null )
或
select sid, sname from 学生 where sid in( select sc.sid from 成绩 sc group by sid having max(sc.cscore)<60 )
select s.sid, s.sname from 学生 s left join 成绩 sc on s.sid=sc.sid group by s.sid having count(s.sid) != ( select count(1) from 课程 )
select sid, sname from 学生 where sid in( select distinct(sid) from 成绩 where cid in ( select cid from 成绩 where sid = 's01' ) and sid != 's01' )
或
select distinct(s.sid),sname from 学生 s left join 成绩 sc on s.sid = sc.sid where sc.cid in( select cid from 成绩 where sid = 's01' )and s.sid != 's01'
select s.sid from 学生 s left join 成绩 sc on s.sid = sc.sid where sc.cid in( select cid from 成绩 where sid = 's01' ) group by s.sid having count(s.sid) = (select count(cid) from 成绩 where sid='s01') and s.sid != 's01'
update 成绩 set cscore = ( select avg(sc.cscore) from 课程 c left join 成绩 sc on c.cid = sc.cid where cteacher = '高磊' ) where cid in ( select cid from 课程 where cteacher = '高磊' )
select s.sid, s.sname, avg(sc.cscore) from 学生 s left join 成绩 sc on s.sid = sc.sid where s.sid in( select sid from 成绩 sc where cscore < 60 group by sid having count(sid) > 2 )
select * from 学生 s left join 成绩 sc on s.sid=sc.sid where sc.cscore < 60 and sc.cid = 'c01' order by sc.cscore desc
select c.cteacher,c.cname, avg(sc.cscore) from 成绩 sc left join 课程 c on sc.cid=c.cid group by sc.cid order by avg(sc.cscore) desc
select s.sname, max(sc.cscore) from 学生 s left join 成绩 sc on s.sid=sc.sid where sc.cid in ( select cid from 课程 where cteacher = '李征辉' )
如对本文有疑问, 点击进行留言回复!!
荐 Mysql2020最新教程(高级部分)之存储引擎与简单的sql优化
大型互联网必问的MySQL面试题:MySQL存储与索引+事务和锁+性能优
网友评论