动力节点 mysql 郭鑫 34道经典的面试题二
13.有3个表S(学生表),C(课程表),SC(学生选课表) S(SNO,SNAME)代表(学号,姓名) C(CNO,CNAME,CTEACHER)代表(课号,课名,教师) SC(SNO,CNO,SCGRADE)代表(学号,课号,成绩) 问题: 1、找出没选过“黎明”老师的所有学生姓名。 2、列出2门以上(含2门)不及格学生姓名及平均成绩。 3、即学过1号课程又学过2号课所有学生的姓名。 create table s( sno int(4) primary key auto_increment, sname varchar(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 table c( cno int(4) primary key auto_increment, cname varchar(32), cteacher varchar(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 table sc( sno int(4), cno int(4), scgrade double(3,1), constraint sc_sno_cno_pk primary key(sno,cno), constraint sc_sno_fk foreign key(sno) references s(sno), constraint sc_cno_fk foreign key(cno) references c(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 table sc( sno int(4), cno int(4), scgrade double(3,1), constraint sc_sno_cno_pk primary key(sno,cno), constraint sc_sno_fk foreign key(sno) references s(sno), constraint sc_cno_fk foreign key(cno) references c(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 = '黎明'; 第二步:求出那些学生选修了黎明老师的课程 select sno from sc join ( select cno from c where cteacher = '黎明' ) t on sc.cno = t.cno; 第三步:求出那些学生没有选择黎明老师的课 select sno,sname from s where sno not in ( select sno from sc join ( select cno from c where cteacher = '黎明' ) t on sc.cno = t.cno );
+-----+---------+ | sno | sname | +-----+---------+ | 3 | wangwu | | 4 | zhaoliu | +-----+---------+
第二题:
2、列出2门以上(含2门)不及格学生姓名及平均成绩。
思路一 :在sc表中首先按照学生编号进行分组,得到哪些学生的有两门以上的成绩低于60分
select sc.sno ,count(*) as studentNum from sc where scgrade < 60 group by sc.sno having studentNum >= 2; 第二步:查询出该学生对应的编号 select a.sno , a.sname from s as a join ( select sc.sno ,count(*) as studentNum from sc where scgrade < 60 group by sc.sno having studentNum >= 2 ) as b on a.sno = b.sno;
+-----+----------+ | sno | sname | +-----+----------+ | 1 | zhangsan | +-----+----------+ 1 row in set
接下来需要获得该学生的平均成绩,我们得到该学生的sno的值是1,我们需要在sc表中求出该学生的平均成绩,首先需要将上面的表和sc表关联起来形成一个临时表,然后对这个临时表按照sno进行group by
我们来看下下面的代码
select sc.sno ,count(*) as studentNum from sc where scgrade < 60 group by sc.sno having studentNum >= 2; 第二步:查询出该学生对应的编号 select a.sno , a.sname from s as a join ( select sc.sno ,count(*) as studentNum from sc where scgrade < 60 group by sc.sno having studentNum >= 2 ) as b on a.sno = b.sno; 第三步得到该学生的平均成绩,把上面的表当成临时表m select m.sno,m.sname,avg(d.scgrade) from sc as d join ( select a.sno , a.sname from s as a join ( select sc.sno ,count(*) as studentNum from sc where scgrade < 60 group by sc.sno having studentNum >= 2 ) as b on a.sno = b.sno ) as m on m.sno = d.sno group by d.sno ;
+-----+----------+----------------+ | sno | sname | avg(d.scgrade) | +-----+----------+----------------+ | 1 | zhangsan | 64.00000 | +-----+----------+----------------+ 1 row in set
第三题:
select s.sname from sc join s on sc.sno = s.sno where cno = 1 and sc.sno in(select sno from sc where cno = 2);
+----------+
| sname |
+----------+
| zhangsan |
| wangwu |
+----------+
不能写成下面的形式会存在错误
posted on 2018-01-30 17:47 luzhouxiaoshuai 阅读(1090) 评论(0) 编辑 收藏 举报