MySQL经典练习题（四）

MySQL是不区分大小写字母的

学生表 student

create table Student(sid varchar(6), sname varchar(10), sage datetime, ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女')

 成绩表 scores

create table scores(sid varchar(10), cid varchar(10), score decimal(18,1));
insert into scores values('01' , '01' , 80);
insert into scores values('01' , '02' , 90);
insert into scores values('01' , '03' , 99);
insert into scores values('02' , '01' , 70);
insert into scores values('02' , '02' , 60);
insert into scores values('02' , '03' , 80);
insert into scores values('03' , '01' , 80);
insert into scores values('03' , '02' , 80);
insert into scores values('03' , '03' , 80);
insert into scores values('04' , '01' , 50);
insert into scores values('04' , '02' , 30);
insert into scores values('04' , '03' , 20);
insert into scores values('05' , '01' , 76);
insert into scores values('05' , '02' , 87);
insert into scores values('06' , '01' , 31);
insert into scores values('06' , '03' , 34);
insert into scores values('07' , '02' , 89);
insert into scores values('07' , '03' , 98);

课程表 course

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');

教师表 teacher

create table teacher(tid varchar(10),tname varchar(10));
insert into teacher values('01' , '张三');
insert into teacher values('02' , '李四');
insert into teacher values('03' , '王五');

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

首先：查询01课程比02课程高的学生

select t1.sid, t1.score as s1, t2.score as s2 
	from (select * from scores where cid='01') as t1 
		inner join 
		(select * from scores where cid='02') as t2 
		on t1.sid = t2.sid
	where t1.score > t2.score;

结果：

# 再结合student表进行查询，其他信息

select student.sid, sname, sage, ssex, s1, s2  
	from student inner join 
		(select t1.sid, t1.score as s1, t2.score as s2  
			from (select * from scores where cid='01') as t1 
				inner join 
				(select * from scores where cid='02') as t2 
			on t1.sid = t2.sid
		where t1.score > t2.score)as t3 
	on student.sid = t3.sid;

结果：

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

# 先算出每个人的平均成绩，并刷选出大于60的

/** 也可以用avg(score)*/
select sc.sid, sum(score)/count(score) as avg_score 
	from 
		scores as sc  
	group by sc.sid
	having avg_score >= 60;

结果：

# 再结合student表，查出姓名

select sc.sid, sname, sum(score)/count(score) as avg_score
	from 
		scores as sc inner join student as st 
		on sc.sid = st.sid   
	group by sc.sid
	having avg_score >= 60;

结果：

题目：3. 查询在 SC 表存在成绩的学生信息

直接查询即可

select * from student
	where 
	sid in (select sid from scores group by sid);

结果：

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

直接写即可

select st.sid, sname, count(cid), sum(score)
	from student as st left join scores as sc on st.sid = sc.sid
	group by st.sid; 

结果：

题目：4.1 查有成绩的学生的id，名字，选课总数，总成绩，01成绩，02成绩，03成绩

select st.sid, st.sname, count(cid), sum(score),
	(case when cid = '01' then score else 0 end) as score1,
	(case when cid = '02' then score else 0 end) as score2,
	(case when cid = '03' then score else 0 end) as score3
	from scores as sc left join student as st
		 on sc.sid = st.sid
	group by st.sid;

结果：

分析：

# 之所有出现，很多0的情况，是由于先group后再执行select，而group后score默认只选用了每个人的第一行
# 下面，我们可以看出，这是3x3的表，而group后只选取了每个人的第一行，为了避免其他值为0的情况
# 因此，我们利用sum，把值都加到第一行

select sid, 
	(case when cid = '01' then score else 0 end) as score1,
	(case when cid = '02' then score else 0 end) as score2,
	(case when cid = '03' then score else 0 end) as score3
	from scores;

# 最终代码

select st.sid, st.sname, count(cid), sum(score),
	sum(case when cid = '01' then score else 0 end) as score1,
	sum(case when cid = '02' then score else 0 end) as score2,
	sum(case when cid = '03' then score else 0 end) as score3
	from scores as sc left join student as st
		 on sc.sid = st.sid
	group by st.sid;

结果： 

本文地址：https://blog.csdn.net/weixin_39562364/article/details/107158336