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默认只选用了每个人的第一行
# 下面，我们可以看出，这是3×3的表，而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