- 习题准备
- 数据准备
- 数据展示
- 第一题:找出没有选过"黎明"老师的所有学生姓名
- 第二题:列出2门以上(含2门)不及格学生姓名及平均成绩
- 第三题:既学过1号课程又学过2号课程的所有的学生姓名
- 总结
习题准备
有三个表,表S(学生表),C(课程表),SC(学生选课表)
S(SNO,SNAME)代表(学号,姓名)
C(CNO,CNAME,CTEACHER)代表(课号,课名,老师)
SC(SNO,CNO,SCGRADE)代表(学号,课号,成绩)
问题:
1.找出没有选过’‘黎明’'老师的所有学生姓名
2.列出2门以上(含2门)不及格学生姓名及平均成绩
3.既学过1号课程又学过2号课程的所有的学生姓名
数据准备
drop table if exists s; drop table if exists c; drop table if exists sc; create table s( sno int(10) primary key COMMENT '学号', sname varchar(14) COMMENT '姓名' )ENGINE=InnoDB DEFAULT CHARSET=utf8; create table c( cno int(10) primary key COMMENT '课号', cname varchar(14) COMMENT '课名', cteacher varchar(14) COMMENT '老师' )ENGINE=InnoDB DEFAULT CHARSET=utf8; create table sc( sno int(10) COMMENT '学号', cno int(10) COMMENT '课号', scgrade int(10) COMMENT '成绩', primary key(sno,cno), FOREIGN KEY(sno) REFERENCES s(sno), FOREIGN KEY(cno) REFERENCES c(cno) )ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into s(sno,sname) values(1,'a'); insert into s(sno,sname) values(2,'b'); insert into s(sno,sname) values(3,'c'); insert into s(sno,sname) values(4,'d'); insert into c(cno,cname,cteacher) values(1,'java','王老师'); insert into c(cno,cname,cteacher) values(2,'C++','张老师'); insert into c(cno,cname,cteacher) values(3,'C#','李老师'); insert into c(cno,cname,cteacher) values(4,'mysql','周老师'); insert into c(cno,cname,cteacher) values(5,'oracle','黎明'); insert into sc(sno,cno,scgrade) values(1,1,50); insert into sc(sno,cno,scgrade) values(1,2,50); insert into sc(sno,cno,scgrade) values(1,3,50); insert into sc(sno,cno,scgrade) values(2,2,80); insert into sc(sno,cno,scgrade) values(2,3,70); insert into sc(sno,cno,scgrade) values(2,4,59); insert into sc(sno,cno,scgrade) values(3,1,60); insert into sc(sno,cno,scgrade) values(3,2,61); insert into sc(sno,cno,scgrade) values(3,3,99); insert into sc(sno,cno,scgrade) values(3,4,100); insert into sc(sno,cno,scgrade) values(3,5,52); insert into sc(sno,cno,scgrade) values(4,3,82); insert into sc(sno,cno,scgrade) values(4,4,99); insert into sc(sno,cno,scgrade) values(4,5,40);数据展示
SELECT sname FROM s WHERe sno NOT IN (SELECt sno FROM c JOIN sc ON c.cno = sc.cno WHERe c.cteacher = '黎明' )第二题:列出2门以上(含2门)不及格学生姓名及平均成绩
SELECt s.sname, avg(sc.scgrade) FROM sc JOIN s ON s.sno = sc.sno WHERe sc.scgrade < 60 GROUP BY sc.sno HAVINg count(sc.sno) >=2第三题:既学过1号课程又学过2号课程的所有的学生姓名
SELECt s.sname FROM s JOIN sc ON s.sno = sc.sno JOIN sc sc2 ON s.sno = sc2.sno WHERe sc.cno = 1 AND sc2.cno = 2
总结
每个人思路不一样,sql存在差异也很正常,大家可以使用explain查看执行计划,结合执行计划书写出最优sql。



