mysql实验训练2答案_MySQL练习题2

以下操作均在MySQL5.7数据库上实验无误

需要四张表

Student_new(Sid,Sname,Sage,Ssex)学生表

Sid：学号

Sname：学生姓名

Sage：学生年龄

Ssex：学生性别

Course(Cid,Cname,Tid)课程表

Cid：课程编号

Cname：课程名称

Tid：教师编号

SC(Sid,Cid,score)成绩表

Sid：学号

Cid：课程编号

score：成绩

Teacher(Tid,Tname)教师表

Tid：教师编号：

Tname：教师名字

首先是建表与插入数据

CREATE TABLEStudent_new (

SidVARCHAR(20) NOT NULL UNIQUE PRIMARY KEY,

SnameVARCHAR(20) NOT NULL,

Sagedatetime,

SsexVARCHAR(20)

);insert into Student_new values('01' , '赵雷' , '1990-01-01' , '男');insert into Student_new values('02' , '钱电' , '1990-12-21' , '男');insert into Student_new values('03' , '孙风' , '1990-05-20' , '男');insert into Student_new values('04' , '李云' , '1990-08-06' , '男');insert into Student_new values('05' , '周梅' , '1991-12-01' , '女');insert into Student_new values('06' , '吴兰' , '1992-03-01' , '女');insert into Student_new values('07' , '郑竹' , '1989-07-01' , '女');insert into Student_new values('08' , '王菊' , '1990-01-20' , '女');create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));insert into Course values('01' , '语文' , '02');insert into Course values('02' , '数学' , '01');insert into Course values('03' , '英语' , '03');create table Teacher(Tid varchar(10),Tname varchar(10));insert into Teacher values('01' , '张三');insert into Teacher values('02' , '李四');insert into Teacher values('03' , '王五');create table SC(Sid varchar(10),Cid varchar(10),score decimal(18,1));insert into SC values('01' , '01' , 80);insert into SC values('01' , '02' , 90);insert into SC values('01' , '03' , 99);insert into SC values('02' , '01' , 70);insert into SC values('02' , '02' , 60);insert into SC values('02' , '03' , 80);insert into SC values('03' , '01' , 80);insert into SC values('03' , '02' , 80);insert into SC values('03' , '03' , 80);insert into SC values('04' , '01' , 50);insert into SC values('04' , '03' , 20);insert into SC values('05' , '01' , 76);insert into SC values('05' , '02' , 87);insert into SC values('06' , '01' , 31);insert into SC values('06' , '03' , 34);insert into SC values('07' , '02' , 89);insert into SC values('07' , '03' , 98);

问题如下：

1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

selectstudent_new.Sid, student_new.Sname, student_new.Sage,student_new.Ssex, sc.score,sc.Cidfrom student_new,sc where (student_new.Sid =(select a.sid from(select sid,score from sc where cid='01') asa,

(select sid,score from sc where cid='02') asbwhere a.sid = b.sid and a.score>b.score)) and student_new.Sid=sc.Sid;

写得相当啰嗦，但思路很清楚，就是把student_new和sc两张表联合起来查询，大致框架已经有了，那就需要确定Sid学生编号，把课程编号是01和02的单独拿出来进行过滤得到符合要求的Sid

1.1 查询同时存在" 01 "课程和" 02 "课程的情况

select * from(select * from sc where Cid='01') as A, (select * from sc where Cid='02') asBwhere (A.Sid=B.Sid);

或者

select * from sc where (sc.Sid in(select a.Sid from(select * from sc where Cid='01') as A, (select * from sc where Cid='02') asBwhere (A.Sid=B.Sid))) and sc.Cid in ('01','02');

注意这两种得到的结果不完全一样，第一个是把两个课程分数合在一行，第二个是两行

1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

这个时候就要用到left join了

select * from (select * from SC where Cid='01')Aleft join (select * from SC where Cid='02')B on A.Sid=B.Sid

其实1.1也可以用Left join 写，只不过B要加个B.Sid is not NULL的条件

left join 就是返回左表所有行(不满足条件就是NULL)，然后返回右表满足条件的行(不满足就是NULL)

1.3 查询不存在" 01 "课程但存在" 02 "课程的情况

也有两种写法，第一种用left join，左表是存在02的情况，右表是存在01的情况，但是添加一个过滤条件，即右表为NULL，因为left join是左表都会列出来(只要满足on的匹配)，右表为空则为NULL

select * from (select * from sc where Cid ='02') as A left join (select * from sc where Cid='01')Bon (A.Sid=B.Sid) where B.Cid is NULL

第二种，限定Sid不在01的行里面挑出Cid是02的行

select * from SC where Cid='02'and Sid not in(select Sid from SC where Cid='01')

2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

思路很清楚，把Sid和大于60的平均成绩拿出来作为一个表和学生表去连接

select A.Sid, student_new.Sname, A.avg_score from(select Sid,AVG(score) as avg_score from sc group by Sid having AVG(score)>=60) asA, student_newwhere (A.Sid = student_new.Sid);

3、查询在 SC 表存在成绩的学生信息

也很简单，用到一个select distinct，即去重选择

select * from student_new where Sid in (select distinct Sid from SC)

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

同样用left join

select student_new.Sid,student_new.Sname,A.count,A.sum from student_new left join(select Sid,count(Cid) as count,SUM(score) as sum from sc group by Sid) asAon student_new.Sid = A.Sid;

4.1 查有成绩的学生信息

这个跟4就反了一下而已，只要把left join的左右表互换一下位置就好了，有成绩的学生信息一定能查到，或者用right join

select A.Sid,B.Sname,A.选课总数,A.总成绩 from(select Sid,COUNT(Cid) as 选课总数,sum(score) as 总成绩 from sc group by Sid) asAleft join student_new as B on A.Sid=B.Sid

5. 查询「李」姓老师的数量

简单

select count(*) as 李姓老师数量 from teacher where Tname like '李%';

6. 查询学过「张三」老师授课的同学的信息

逻辑很清楚，三个表依次映射过去

select * from student_new where Sid in (select distinct Sid from sc where Cid in(select Cid from course where Tid = (select Tid from teacher where Tname = '张三')))

7. 查询没有学全所有课程的同学的信息

select * from student_new where Sid in(select Sid from sc group by Sid having count(Cid) <3)

注意，HAVING要放到最后面

8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

想到了一种很朴素的方法，把每门课过滤掉‘01’，剩下其他同学，如果不为空，说明这门课是‘01’和其他人一起学的，把三门课的剩下学生加起来去重求并集就行了

select * from student_new where Sid in(select Sid from SC where Cid='01' and Sid <>'01'

UNION

select Sid from SC where Cid='02' and Sid <>'01'

UNION

select Sid from SC where Cid='03' and Sid <>'01')

第二种方法，把‘01’学的三门课求出来，然后直接用去重的方式去选学这门课的学生

select * fromstudent_newwhere Sid in(select distinct Sid from sc where Cid in(select Cid from SC where Sid='01')

)and Sid <> '01'

9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

很屌的一种方法

select a.Sid,s.Sname from(select sid,GROUP_CONCAT(cid order by cid separator ',') ascid_strfrom sc where sid='05')b,

(select sid,GROUP_CONCAT(cid order by cid separator ',') ascid_strfrom sc group bysid)aleft joinstudent_new son a.sid =s.sidwhere a.cid_str = b.cid_str and a.Sid<>'05';

但还是我自己写的好理解一点

select * from student_new where sid in(select a.sid from(select sid,GROUP_CONCAT(cid order by cid separator ',') as cid_str from sc where sid='01') asb,

(select sid,GROUP_CONCAT(cid order by cid separator ',') as cid_str from sc group by sid) asawhere a.cid_str = b.cid_str and a.Sid<>'01')

GROUP_CONCAT可以把一行里面的字符串组合起来，把‘01’的考试情况字符串和其他同学的考试情况字符串进行对比，找出一模一样的同学Sid就行了

10. 查询没学过"张三"老师讲授的任一门课程的学生姓名

select * from student_new where Sid not in (select distinct Sid from sc where Cid in(select Cid from course where Tid = (select Tid from teacher where Tname = '张三')))

在第六题的基础上加个NOT就行了

未完待遇。。。