13.有3个表S(学生表),C(课程表),SC(学生选课表)
S(SNO,SNAME)代表(学号,姓名)
C(CNO,CNAME,CTEACHER)代表(课号,课名,教师)
SC(SNO,CNO,SCGRADE)代表(学号,课号,成绩)
问题:1、找出没选过“黎明”老师的所有学生姓名。2、列出2门以上(含2门)不及格学生姓名及平均成绩。3、即学过1号课程又学过2号课所有学生的姓名。create tables(
snoint(4) primary keyauto_increment,
snamevarchar(32)
);insert into s(sname) values('zhangsan');insert into s(sname) values('lisi');insert into s(sname) values('wangwu');insert into s(sname) values('zhaoliu');create tablec(
cnoint(4) primary keyauto_increment,
cnamevarchar(32),
cteachervarchar(32)
);insert into c(cname,cteacher) values('Java','吴老师');insert into c(cname,cteacher) values('C++','王老师');insert into c(cname,cteacher) values('C##','张老师');insert into c(cname,cteacher) values('MySQL','郭老师');insert into c(cname,cteacher) values('Oracle','黎明');create tablesc(
snoint(4),
cnoint(4),
scgradedouble(3,1),constraint sc_sno_cno_pk primary key(sno,cno),constraint sc_sno_fk foreign key(sno) referencess(sno),constraint sc_cno_fk foreign key(cno) referencesc(cno)
);insert into sc(sno,cno,scgrade) values(1,1,30);insert into sc(sno,cno,scgrade) values(1,2,50);insert into sc(sno,cno,scgrade) values(1,3,80);insert into sc(sno,cno,scgrade) values(1,4,90);insert into sc(sno,cno,scgrade) values(1,5,70);insert into sc(sno,cno,scgrade) values(2,2,80);insert into sc(sno,cno,scgrade) values(2,3,50);insert into sc(sno,cno,scgrade) values(2,4,70);insert into sc(sno,cno,scgrade) values(2,5,80);insert into sc(sno,cno,scgrade) values(3,1,60);insert into sc(sno,cno,scgrade) values(3,2,70);insert into sc(sno,cno,scgrade) values(3,3,80);insert into sc(sno,cno,scgrade) values(4,3,50);insert into sc(sno,cno,scgrade) values(4,4,80);
首先分析下上面表的设计
一个学生可以选修多门课程
同一门课程可以被多个学生选择
学生和课程之间是多对多的关系
所以就要引入第三张中间表来解决学生和课程之间的关系
create tablesc(
snoint(4),
cnoint(4),
scgradedouble(3,1),constraint sc_sno_cno_pk primary key(sno,cno),constraint sc_sno_fk foreign key(sno) referencess(sno),constraint sc_cno_fk foreign key(cno) referencesc(cno)
);
第三张表的sno的值必须来自学生表,使用外键约束
cno必须来自课程表,使用外键约束
然后建立一个sno 和cno的一个复合主键
接下来我们就可以做题了
/*
1、找出没选过“黎明”老师的所有学生姓名。
2、列出2门以上(含2门)不及格学生姓名及平均成绩。
3、即学过1号课程又学过2号课所有学生的姓名。
*/
第一题的第一种做法:
--先找出选过黎明老师的学生编号 -> 黎明老师的授课的编号
select cno from c where cteacher = '黎明';select sno from sc where cno = (select cno from c where cteacher = '黎明');select * from s where sno not in(select sno from sc where cno = (select cno from c where cteacher = '黎明'));
第一题的第二种做法:
第一步:找到黎明老师所上课对应的课程对应的课程编号select cno from c where cteacher = '黎明';
第二步:求出那些学生选修了黎明老师的课程selectsnofromscjoin(select cno from c where cteacher = '黎明') tonsc.cno=t.cno;
第三步:求出那些学生没有选择黎明老师的课selectsno,snamefromswheresnonot in(selectsnofromscjoin(select cno from c where cteacher = '黎明') tonsc.cno=t.cno
);
+-----+---------+
| sno | sname |
+-----+---------+
| 3 | wangwu |
| 4 | zhaoliu |
+-----+---------+
第二题:
2、列出2门以上(含2门)不及格学生姓名及平均成绩。
思路一 :在sc表中首先按照学生编号进行分组,得到哪些学生的有两门以上的成绩低于60分
selectsc.sno ,count(*) asstudentNumfromscwherescgrade< 60
group bysc.snohavingstudentNum>= 2;
第二步:查询出该学生对应的编号selecta.sno , a.snamefromsasajoin(selectsc.sno ,count(*) asstudentNumfromscwherescgrade< 60
group bysc.snohavingstudentNum>= 2)asbona.sno=b.sno;
+-----+----------+
| sno | sname |
+-----+----------+
| 1 | zhangsan |
+-----+----------+
1 row in set
接下来需要获得该学生的平均成绩,我们得到该学生的sno的值是1,我们需要在sc表中求出该学生的平均成绩,首先需要将上面的表和sc表关联起来形成一个临时表,然后对这个临时表按照sno进行group by
我们来看下下面的代码
selectsc.sno ,count(*) asstudentNumfromscwherescgrade< 60
group bysc.snohavingstudentNum>= 2;
第二步:查询出该学生对应的编号selecta.sno , a.snamefromsasajoin(selectsc.sno ,count(*) asstudentNumfromscwherescgrade< 60
group bysc.snohavingstudentNum>= 2)asbona.sno=b.sno;
第三步得到该学生的平均成绩,把上面的表当成临时表mselectm.sno,m.sname,avg(d.scgrade)fromscasdjoin(selecta.sno , a.snamefromsasajoin(selectsc.sno ,count(*) asstudentNumfromscwherescgrade< 60
group bysc.snohavingstudentNum>= 2)asbona.sno=b.sno
)asmonm.sno=d.snogroup byd.sno ;
+-----+----------+----------------+
| sno | sname | avg(d.scgrade) |
+-----+----------+----------------+
| 1 | zhangsan | 64.00000 |
+-----+----------+----------------+
1 row in set
第三题:
selects.snamefromscjoinsonsc.sno=s.snowherecno= 1 and sc.sno in(select sno from sc where cno = 2);
+----------+
| sname |
+----------+
| zhangsan |
| wangwu |
+----------+
不能写成下面的形式会存在错误