前言
本篇文章主要记录了近段时间为了学习MySQL数据库而做的一些习题，大概应用到的知识点有：常用函数的使用、子查询、连表查询以及它们的综合运用。
提示：以下是本篇文章正文内容，下面案例可供参考
一、使用到的表和数据
1.使用的表
下面的表依次是：学生表、教师表、课程表、成绩表。
2.表数据
二、习题与解答
#学生表
SELECT * FROM student;
#教师表
select * from teacher;
#课程表
select * from course;
#成绩表
select * from sc;
-- 1.0 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
SELECT s.sid,s.sname,s.ssex,tmp.s01,tmp.s02 FROM
(SELECT t1.sid,t1.s01,t2.s02 FROM
(SELECT sc.sid,sc.score as s01
FROM sc
where cid='01') t1,
(SELECT sc.sid,sc.score as s02 FROM sc where cid='02') t2
WHERE t1.sid=t2.sid AND t1.s01>t2.s02) tmp,student s
WHERE tmp.sid=s.sid;
#1.1 查询同时存在" 01 "课程和" 02 "课程的情况
-- 法一：
select a1.sid,a1.score 课程1,a2.score 课程2 from
(select * from sc where cid='01') as a1,
(select *from sc where cid='02') as a2
where a1.sid=a2.sid;
-- 法二：
SELECT t1.sid,t1.s01,t2.s02
FROM
(SELECT cs.sid,cs.score as s01
FROM sc cs
where cid='01') t1,
(SELECT cs.sid,cs.score as s02
FROM sc cs
where cid='02') t2
WHERE t1.sid=t2.sid;
#1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select *from
(select * from sc where cid='01') as a1
left join
(select *from sc where cid='02') as a2
on a1.sid=a2.sid;
#1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
select *from sc where sid not in(
select sid from sc where cid='01'
)
and cid='02';
#1.4查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT s.sid,s.sname,ROUND(AVG(sc.score),2) 平均成绩
FROM sc ,student s
where sc.sid = s.sid
GROUP BY sc.sid
HAVING AVG(sc.score)>60;
#1.5查询在 SC 表存在成绩的学生信息
-- 法一：子查询
select * from student where sid in(
select sid from sc
)
-- 法二：连表查询
select distinct stu.* from student stu
inner join sc s
on stu.sid=s.sid;
-- 法三：连表查询（distinct：去重复）
select distinct student.* from student,sc where student.sid=sc.sid;
-- 法四：连表，分组查询
SELECT s.sid,s.sname,s.ssex
FROM student s, sc
WHERE s.sid = sc.sid
GROUP BY s.sid,s.sname;
#1.6查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
-- 左外连接，子查询
select s.sid 学生编号, s.sname 学生姓名,c.coursenum 选课总数,c.scoresum 所有课程的总成绩 from (
(select sid,sname from student)s
left join
(select sid, sum(score) as scoresum, count(cid) as coursenum from sc group by sid)c
on s.sid = c.sid
);
-- 左外连接
select s.sid 学生编号, s.sname 学生姓名,count(cs.cid) 选课总数,sum(cs.score)所有课程的总成绩
from student s
left join sc cs
on s.sid = cs.sid
group by s.sid,s.sname;
#1.7 查有成绩的学生信息
select * from student where sid in(
select sid from sc
)
#1.8 查询「李」姓老师的数量
select count(*) from teacher where tname like '李%';
#1.9 查询学过「张三」老师授课的同学的信息
-- 子查询
select *from student where sid in(
select sid from sc where cid=(
select cid from course where tid=(
select tid from teacher where tname='张三'
)
)
)
-- 连表查询(inner join ....on)
select stu.sname,t.tname from student stu
inner join sc s on stu.sid=s.sid
inner join course c on s.cid=c.cid
inner join teacher t on c.tid=t.tid
where t.tname='张三';
-- 连表查询(,a,b,c where a.id=bid and b.id=c.id )
select stu.sname,t.tname from student stu ,
sc s , course c,teacher t
where stu.sid=s.sid and s.cid=c.cid and c.tid=t.tid
and t.tname='张三';
#2.0 查询没有学全所有课程的同学的信息
select * from student where sid not in (
select sid from sc group by sid
having
count(cid)= (select count(cid) from course)
);
#2.1 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
-- -- 法一：连表查询
select s.sid,s.sname,s.sage,s.ssex
from student s
inner join
(select a2.sid,a2.score from
(select cs.cid from sc cs where cs.sid='01') as a1,sc as a2
where a1.cid=a2.cid
group by a2.sid
having count(a1.cid)>=1) t1
on t1.sid=s.sid;
-- -- 法二：子查询
select * from student where student.sid in (
select sc.sid from sc where sc.cid in(
select sc.cid from sc where sc.sid = '01'
)
);
#2.2 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
select * from student where sid in
(select sid from
(select sid,cid from sc where sid in
(select sid from sc where sid !='01' group by sid
having count(*)=(select count(*) from sc where sid='01' group by sid ))) as t1
inner join (select cid from sc where sid='01') as t2
on t1.cid = t2.cid
group by t1.sid
having count(*)=(select count(*) from sc where sid='01')
)
#2.3 查询没学过"张三"老师讲授的任一门课程的学生姓名
select * from student where sid not in(
select sid from sc where cid=(
select cid from course where tid=(
select tid from teacher where tname='张三'
)
)
)
#2.4 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
-- 法一：
select stu.sid,stu.sname,ROUND(t1.ascore,2) 平均成绩 from student stu
inner join (
select s.sid,avg(score) ascore from sc s
where s.score<60 group by sid
having count(*)>=2
)t1 on stu.sid=t1.sid;
-- 法二：
select student.sid, student.sname, ROUND(AVG(sc.score),2) 平均成绩 from student,sc
where student.sid = sc.sid and sc.score<60
group by sc.sid
having count(*)>1;
#2.5 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
-- 法一：连表查询，子查询
select stu.sid,stu.sname,t1.cid,t1.score from student stu
inner join(
select * from sc s where s.cid='01' and s.score<60 order by s.score desc
)t1 on stu.sid=t1.sid;
-- 法一：连表查询
select student.*, sc.score from student, sc
where student.sid = sc.sid
and sc.score < 60
and cid = "01"
ORDER BY sc.score DESC;
#2.6 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
-- 经典行转列
sid 01 02 03 avgs
01 80 94 99 98
02 81 90 99 97
# 1）.显示所有学生的所有课程的成绩
select s.sid, c.cname, s.score
from sc s inner join course c on s.cid = c.cid;
# 2）.显示所有学生的所有课程的平均成绩
select s.sid, '平均分', round(avg(s.score))
from sc s
group by s.sid;
# 3）.所有课程的成绩以及平均成绩
select s.sid, c.cname, s.score
from sc s inner join course c on s.cid = c.cid
UNION
select s.sid, '平均分', round(avg(s.score))
from sc s
group by s.sid;
# 4）.行转列
select t1.sid,
MAX(CASE cname WHEN '语文' THEN score ELSE 0 END ) 语文,
MAX(CASE cname WHEN '数学' THEN score ELSE 0 END ) 数学,
MAX(CASE cname WHEN '英语' THEN score ELSE 0 END ) 英语,
MAX(CASE cname WHEN '平均分' THEN score ELSE 0 END ) 平均分
from
(
select s.sid, c.cname, s.score
from sc s inner join course c on s.cid = c.cid
UNION
select s.sid, '平均分', round(avg(s.score))
from sc s
group by s.sid
) t1
group by t1.sid;
# 2.7.显示学生姓名及排序
select s1.sid, s1.sname, t2.语文,t2.数学,t2.英语,t2.平均分
from student s1 inner join
(
select t1.sid,
MAX(CASE cname WHEN '语文' THEN score ELSE 0 END ) 语文,
MAX(CASE cname WHEN '数学' THEN score ELSE 0 END ) 数学,
MAX(CASE cname WHEN '英语' THEN score ELSE 0 END ) 英语,
MAX(CASE cname WHEN '平均分' THEN score ELSE 0 END ) 平均分
from
(
select s.sid, c.cname, s.score
from sc s inner join course c on s.cid = c.cid
UNION
select s.sid, '平均分', round(avg(s.score))
from sc s
group by s.sid
) t1
group by t1.sid
) t2 on s1.sid = t2.sid
order by t2.平均分;
#2.8查询各科成绩最高分、最低分和平均分：
select cs.cid,max(cs.score) 最高分,min(cs.score) 最低分,round(avg(cs.score),2) 平均分
from sc cs
group by cs.cid;
#2.9 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
#及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
#要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select sc.CId ,max(sc.score)as 最高分,min(sc.score)as 最低分,AVG(sc.score)as 平均分,count(*)as 选修人数,
sum(case when sc.score>=60 then 1 else 0 end )/count(*)as 及格率,
sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end )/count(*)as 中等率,
sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end )/count(*)as 优良率,
sum(case when sc.score>=90 then 1 else 0 end )/count(*)as 优秀率
from sc
GROUP BY sc.CId
ORDER BY count(*)DESC, sc.CId ASC;
#3.0 按各科成绩进行排序，并显示排名， Score 重复时合并名次
select course.cname, course.cid,
sum(case when sc.score<=100 and sc.score>85 then 1 else 0 end) as "[100-85]",
sum(case when sc.score<=85 and sc.score>70 then 1 else 0 end) as "[85-70]",
sum(case when sc.score<=70 and sc.score>60 then 1 else 0 end) as "[70-60]",
sum(case when sc.score<=60 and sc.score>0 then 1 else 0 end) as "[60-0]"
from sc left join course
on sc.cid = course.cid
group by sc.cid;
#3.1 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select a.cid, a.sid, a.score, count(b.score)+1 as ranking
from sc as a
left join sc as b
on a.score<b.score and a.cid = b.cid
group by a.cid, a.sid,a.score
order by a.cid, ranking ASC;
#3.2 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
-- 法一：
set @ranking=0;
select cs.sid,sum(cs.score) 总成绩,@ranking:=@ranking+1 名次
from sc cs
group by cs.sid
order by sum(cs.score) desc;
-- 法二：
set @ranking=0;
select t1.sid, t1.sun 总成绩, @ranking := @ranking +1 as 名次 from(
select sc.sid, sum(sc.score) as sun from sc
group by sc.sid
order by sun desc) t1;
#3.3 查询每门课程被选修的学生数
select cs.cid,c.cname,count(cs.sid) from sc cs,course c
where c.cid=cs.cid
group by cs.cid;
#3.4 查询出只选修两门课程的学生学号和姓名
select stu.sid,stu.sname,t1.c 课程 from student stu ,(
select s.sid,count(s.cid) c from sc s
group by s.sid
having count(s.cid)=2
)t1 where stu.sid=t1.sid;
#3.5 查询男生、女生人数
select ssex,count(*) from student stu group by ssex;
#3.6 查询名字中含有「风」字的学生信息
select *from student where sname like '%风%';
#3.7 查询同名同性学生名单，并统计同名人数
select stu.sname,count(stu.sname) from student stu
group by stu.sname
having count(stu.sname)>1;
#3.8 查询 1990 年出生的学生名单
select s.sname from student s where year(s.sage)='1990';
#3.9 查询各学生的年龄，只按年份来算
select s.sid,s.sname,s.sage,year(NOW())-year(s.sage) from student s;
#4.0 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一;
#DATE_FORMAT()：将日期转换成字符串
#STR_TO_DATE()：将字符串转换成日期
select s.sname,s.sage,YEAR(now())-YEAR(s.sage)+
if(date_format(now(),'%m-%d')-date_format(s.sage,'%m-%d')<0,-1,0)
from student s;
#4.1 查询本月过生日的学生
select *from student s where month(now())=month(s.sage);
#4.2 查询下月过生日的学生
-- 法一：
select *from student s where month(now())=month(s.sage)-1;
-- 法二：
select *from student s where month(date_add(NOW(), interval 1 MONTH))=month(s.sage);
# 4.3.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select stu2.sid,stu2.sname, savg
from
(
select stu.sid, avg(s.score) savg
from student stu inner join sc s on stu.sid = s.sid
group by stu.sid
having avg(s.score)>=85
) t1 inner join student stu2 on t1.sid = stu2.sid;
#4.4.查询课程名称为「数学」，且分数低于 60 的学生姓名和分
select stu.*,s.*,c.*
from student stu inner join sc s on stu.sid = s.sid
inner join course c on s.cid = c.cid
where c.cname = '数学' and s.score < 60;
#4.5.查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名
select stu.*,s.*
from student stu inner join sc s on stu.sid = s.sid
where s.cid = '01' and s.score >= 80;
1703
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
