mysql简单例子_MySQL简单例子

连表查询select sid,score from sc where cid = 1;

select sid,score from sc where cid = 2;

select a.sid,a.score,b.score from (select sid,score from sc where cid = 1) a,(select sid,score from sc where cid = 2) b where a.sid = b.sid and a.score > b.score;

或者自连查询select sc1.sid,sc1.score,sc2.score from sc sc1,sc sc2 where sc1.cid = 1 and sc2.cid = 2 and sc1.sid = sc2.sid and sc1.score > sc2.score;

select s.sid,s.sname,avg(sc.score) from student s,sc where s.sid = sc.sid group by s.sid having avg(sc.score) >= 60;

select s.sid,s.sname,count(sc.cid),sum(sc.score) from student s,sc where s.sid = sc.sid group by s.sid;

select distinct sc.sid from teacher t,course c,sc where t.tid = c.tid and c.cid = sc.cid and t.tname = 'zou';

—–distinct 查询结果去重 ⬆️ 为嵌套查询，⬇️ 也是，不过优先用上面的

select distinct sc.sid from sc,(select c.cid from course c,teacher t where c.tid = t.tid and t.tname = 'zou') a where sc.cid = a.cid;

select sc1.sid from sc sc1,sc sc2 where sc1.sid = sc2.sid and sc1.cid = 1 and sc2.cid = 2;

select x.sid,a.tcnt from sc x,(select count(cid) tcnt from course) a group by x.sid having count(x.cid) < a.tcnt-3;

select sname from student where sid not in (select sc.sid from teacher t,course c,sc where sc.cid =c.cid and t.tid = c.tid and t.tname = 'zou');

select s.sname,s.sid,avg(sc.score) from student s,sc,(select sid,count(cid) cnt from sc where sc.score < 80 group by sid having count(cid) >= 2) a where s.sid = a.sid and s.sid = sc.sid group by s.sid;

select cid, sum(case when score >= 85 then 1 else 0 end) '【100-85】', sum(case when score >= 70 and score < 85 then 1 else 0 end) '【85-70】', sum(case when score >= 60 and score < 70 then 1 else 0 end) '【70-60】', sum(case when score <60 then 1 else 0 end) '【60-0】' from sc group by cid;

update sc,(select sc.cid,c.cname,avg(sc.score) ag from sc,course c,teacher t where sc.cid = c.cid and c.tid = t.tid and t.tname = 'zou' group by sc.cid) a set sc.score = a.ag where sc.cid = a.cid;

select * from student order by sid desc limit 0,1;

select sid,count(sc.cid) ant from sc group by sid having count(cid) = (select count(cid) from sc where sid = 2);

——-查询选课数与 id 为 2 的学生数量一致

select sid,count(cid) bnt from sc where cid in (select cid from sc where sid = 2) group by sid;

——-查询选课为 id 为 2 的学生所选课相同的学生

select a.sid from (select sid,count(sc.cid) ant from sc group by sid having count(cid) = (select count(cid) from sc where sid = 2)) a,(select sid,count(cid) bnt from sc where cid in (select cid from sc where sid = 2) group by sid) b where a.sid = b.sid and a.ant = b.bnt and a.sid != 2;