MySQL 练习题目 二刷

Log：2020-6-24 23:18 三刷，并重新整理了下题目

PS：练习过程中，为了更好地查看查询效果，会对数据做一些修改符合题目，请注意 

建表的过程

create table student(
sid int not null primary key,
sname varchar(20) not null,
sborn date,
ssex varchar(20) not null);


create table course(
cid int not null primary key,
cname varchar(20) not null,
tid int not null);


create table teacher(
tid int not null primary key,
tname varchar(20));


create table sc(
sid int not null,
cid int not null,
score int not null,
primary key( sid, cid)   );

 

 

 

插入数据到student表

insert into Student values(1 , '赵雷' , '1990-01-01' , '男');
insert into Student values(2 , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('08' , '张三' , '2017-12-20' , '女');
insert into Student values('9' , '李四' , '2017-12-25' , '女');
insert into Student values('10' , '李四' , '2012-06-06' , '女');
insert into Student values('11' , '赵六' , '2013-06-13' , '女');
insert into Student values('12' , '孙七' , '2014-06-01' , '女');

 

insert into Course values('01' , '语文' , '02'),('02' , '数学' , '01'),('03' , '英语' , '03'),(4,'物理',4);

 

insert into Teacher values('01' , '张三'),('02' , '李四'),('03' , '王五'),(4,'孙杨');

 

insert into SC values('01' , '01' , 80),
('01' , '02' , 90),
('01' , '03' , 99),
(1,4,46),
('02' , '01' , 70),
('02' , '02' , 60),
('02' , '03' , 80),
(2,4,76),
('03' , '01' , 80),
('03' , '02' , 80),
('03' , '03' , 80),
('04' , '01' , 50),
('04' , '02' , 30),
('04' , '03' , 20),
(4,4,87),
('05' , '01' , 76),
('05' , '02' , 87),
('06' , '01' , 31),
('06' , '03' , 34),
(6,4,93),
('07' , '02' , 89),
('07' , '03' , 98);

 

 

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

select student.*,a.score

from  (sc a  join sc b  on  a.sid=b.sid 
                        and a.cid=1 
                        and b.cid=2  
                        and a.score>b.score)
      join student on a.sid=student.sid;

 或

select student.* , a.score
from (sc a left join sc b on a.sid=b.sid) inner join student on a.sid=student.sid
where a.cid=1 and b.cid=2 and a.score>b.score;

 

2、查询同时选修" 01 "课程和" 02 "课程的学生情况

select  student.*
from sc left join student on sc.sid=student.sid
where cid=1 or cid=2   #注意这里只能用or，不能用and
group by sc.sid
having count(cid)=2;

或

select student.*

from (sc  a join sc b on a.sid=b.sid and a.cid=1 and b.cid=2) join student on a.sid=student.sid;

 

 

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

#思路:题目的意思是选修1号课程的学生必须全部列出来，同时查看一下选修了1号课程的学生中有哪些选修了2号课程

select * from

(select *  from sc  where cid=1 ) a  left join  (select * from sc where cid=2) b on a.sid=b.sid ;

 

 

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

select a.sid  from 

(select sid from sc where cid=2 ) a left join (select sid from sc where cid=1) b on a.sid=b.sid
where b.sid is null;

 

 

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

select  distinct sid
from sc
where cid in (select cid from sc where sid=1) and sid!=1;

OR  

select  distinct t2.sid
from
(select * from sc where sid=1 ) t1 left join (select * from sc where sid<>1) t2
on t1.cid=t2.cid;

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

 PS：使用了group_concat( )函数，将分组后指定字段的值连接起来，感觉使用时最好对连接的字段排序，以免出错

select student.*
from 
(select sid, group_concat(cid order by cid) as  tt from sc  where sid=1  group by sid) a   #1号同学选修的课程
        #为了避免cid插入顺序引起的group_concat()结果有差异，所以对cid进行了排序
left join 

( select sid, group_concat(cid order by cid) as rr  from sc  where sid!=1  group by sid) b    on a.tt=b.rr  
 #除1号同学外，其他同学选修的课程，将两张表进行连接，按照选修课程相同为条件进行连接

join student on b.sid=student.sid;

 中间过程解析：

select sid, group_concat(cid order by cid) as  '1号同学选课情况' from sc  where sid=1  group by sid

运行结果：

select sid, group_concat(cid order by cid) as '除1号同学的选课情况'  from sc  where sid!=1  group by sid;

运行结果：

7、检索" 01 "课程分数小于 60的学生，按分数降序排列学生

select student.*
from sc join student on sc.sid=student.sid
where sc.cid=1 and sc.score<60
order by sc.score desc;

 

8、按平均成绩降序查询所有学生的课程成绩，按如下形式显示：学号、姓名、课程名、总成绩、课程数、平均成绩

select sid, sum(score) as '总成绩', avg(score) as '平均成绩'
from sc
group by sid
order by  平均成绩  desc;

select  student.sid as '学号',student.sname as '姓名',
max(case when cname='语文' then  sc.score else  NULL end) as '语文', #已经按学号分组了，所以每组就是每个同学的所有课程成绩
max(case when cname='数学' then  sc.score else NULL end ) as '数学',
max(case when cname='英语' then  sc.score else NULL end) as '英语',
max(case when cname='物理' then  sc.score else NULL end) as '物理',
max(case when cname='音乐' then  sc.score else NULL end) as '音乐',
sum(score) as '总成绩',
count(sc.cid) as '选课数',
round(avg(score),2) as '平均成绩'

from  (student left join sc on student.sid=sc.sid) left join course on sc.cid=course.cid
group by sc.sid ,student.sname
order by avg(score) desc;

9、查询「李」姓老师的数量

select count(tid) as '姓李的老师个数'
from teacher 
where tname like '李%' ;

 PS：这里的%可以指代多个字符

 

 

10、查询名字中含有「风」字的学生信息

select *
from student
where sname like '%风%'; 

或使用正则表达式 REGEXP

select *
from student
where sname regexp  '风';

 运行结果：

 

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

select student.*
from (( sc left join course on sc.cid=course.cid ) 
left join teacher on course.tid=teacher.tid) left join student on sc.sid=student.sid
where tname="张三"
group by sc.sid

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

#差集, SC表里的所有学生-选修了张三老师课程的学生,即为没有选修过张三老师任一课程的学生
select student.* 
from 
(select distinct sid from sc ) a  left join   #SC表全部的学号，去重

(select  sc.sid from ((sc left join course on sc.cid=course.cid) left join  teacher on course.tid=teacher.tid) 
where teacher.tname='张三')  b
 on a.sid=b.sid   

left join student on a.sid=student.sid

where b.sid is null;

 或

select distinct sid
 from sc
 where sc.sid not in 
 (select  sid
from ( sc left join course on sc.cid=course.cid ) left join teacher on course.tid=teacher.tid
where tname="张三") ;

13、查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

select sid,score
from sc
where score=
(select max(sc.score)
from ( sc left join course on sc.cid=course.cid) left join teacher on course.tid=teacher.tid
where tname="张三") ;

14、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

 PS：不明白它的这个平均成绩，假如1号同学选了4门课，其中有3门不及格，那么这个平均成绩是指这4门的，还是指这3门不及格的平均成绩

下面两种方法都可以选出不及格的同学，但是计算的平均成绩有所不同

 一：不及格同学的所有选修课平均成绩

#这种方法计算的是每个同学所有选修课的平均成绩
select student.sid, student.sname, avg(sc.score) as '平均分'
from sc join student  on sc.sid=student.sid
group by sc.sid
having   sum(sc.score<60)>=2;  # 注意:使用的是sum()函数，不能使用count()函数
如果想用count，括号里需写成形式为 count(case when score<60 then 1 else null end)

 二：不及格同学的选修课中，不及格部分课程的平均成绩

#这种方法计算的是不及格同学中所选课程，不及格部分的平均成绩
select student.sid, student.sname, avg(sc.score) as '平均分' 
from sc join student  on sc.sid=student.sid
where sc.score<60   # 先筛选出不及格的
group by sc.sid
having count(sc.cid)>=2;  #这里sum， count都可以

15、以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

PS：round( )函数将小数保留2位，concat( )函数连接字符串

select  sc.cid, course.cname, max(sc.score) as '最高分',  min(sc.score) as '最低分', round(avg(sc.score),2) as '平均分',
count(sc.sid) as '人数',
concat(round(sum(case when sc.score>=60 and sc.score<70 then 1 else 0 end) /count(sc.sid)*100,2),'%')  as '及格率',
concat(round(sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end)/count(sc.sid)*100,2),'%') as '中等率',
concat(round(sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end)/count(sc.sid)*100,2),'%') as '优良率',
concat(round(sum(case when sc.score>=90 then 1 else 0 end )/count(sc.sid)*100,2),'%') as '优秀率'
from sc join course on sc.cid=course.cid
group by  sc.cid;

结果：

附加：

select sc.cid, course.cname,count(sc.sid) as '该课程的总人数',
sum(case when score>=0 and score<60 then 1 else 0 end) as '[0-60]人数',
concat(round(sum(case when score>=0 and score<60 then 1 else 0 end)/count(sc.sid)*100,2),'%') as '[0-60]',
sum(case when score>=60 and score<70 then 1 else 0 end)  as '[60-70]人数',
concat(round(sum(case when score>=60 and score<70 then 1 else 0 end)/count(sc.sid)*100,2),'%') as '[60-70]',
sum(case when score>=70 and score<85 then 1 else 0 end)  as '[70-85]人数',
concat(round((sum(case when score>=70 and score<85 then 1 else 0 end)/count(sc.sid))*100,2),'%') as '[70-85]',
sum(case when score>=85 and score<100 then 1 else 0 end)  as '[85-100]人数',
concat(round((sum(case when score>=85 and score<100 then 1 else 0 end)/count(sc.sid))*100,2),'%') as '[85-100]'

from sc join course on sc.cid=course.cid
group by sc.cid;

运行结果：

16、要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select cid, count(sid) as '选修人数'
from sc
group by cid
order by 选修人数  desc,cid;  #默认升序

运行结果：

17、按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺rank1、不保留名次空缺rank2

如果MySQL是8以上版本，可以用这种方式：

select cid,sid ,score, rank()over(partition by cid order by score desc) as rank1,
dense_rank()over (partition by cid order by score desc) as dense_rank2
from sc;

不使用系统自带函数的解决方法：

#rank1保留名次空缺， rank2不保留名次空缺
select a.cid, a.sid, a.score , count(a.score<b.score)+1 as rank1, count(distinct b.score)+1 as rank2
from sc a left join sc b on a.cid=b.cid and a.score<b.score   #笛卡尔积连接, 然后筛选满足a.score<b.score的
group by a.cid, a.sid
order by a.cid, a.score desc;

结果：

18、查询各科成绩前三名的记录

PS：这道题目就是在21,22题的基础上添加一个having子句，筛选名次rank<=3，即前三名

A：名次不连续时的前三名

select a.cid, a.sid, a.score , count(a.score<b.score)+1 as rank  
from sc a left join sc b on a.cid=b.cid and a.score<b.score   #笛卡尔积连接, 然后筛选满足a.score<b.score的
group by a.cid, a.sid
having rank<=3 
order by a.cid, a.score desc;

运行结果：

B：名次连续时的前三名

select a.cid, a.sid, a.score , count(distinct b.score)+1 as rank  #这里使用的是distinct b.score
from sc a left join sc b on a.cid=b.cid and a.score<b.score   #笛卡尔积连接, 然后筛选满足a.score<b.score的
group by a.cid, a.sid
having rank<=3
order by a.cid, a.score desc;

运行结果：

19、查询学生的总成绩，并进行排名，总分重复时名次空缺保留和不保留的两种形式

#rank1保留名次空缺， rank2不保留名次空缺
select a.sid, a.tt,count(a.tt<b.rr)+1 as rank1, count(distinct b.rr)+1 as rank2
from
(select sid, sum(score) as tt from sc group by sid ) a
left join 
(select sid, sum(score) as rr from sc group by sid ) b   on  a.tt<b.rr
group by a.sid
order by rank1;

结果：

20、查询出只选修两门课程的学生学号和姓名

select student.sid, student.sname
from sc join student on sc.sid=student.sid
group by sc.sid
having count(sc.cid)=2;

21、检索至少选修两门课程的学生学号

select  sid
from sc
group by sid
having count(cid)>=2;

22、查询没有学全所有课程的同学的信息

#有一些同学一门课都没有选,所以成绩表SC里面没有这些学生的任何信息，
#现在题目的要求是没有学全，个人理解是SC表里的同学

select student.*
from sc join student on sc.sid=student.sid
group by sc.sid
having count(sc.cid)<(select count(course.cid)  from course);

23、查询选修了全部课程的学生信息

select student.*
from sc join student on sc.sid=student.sid
group by  sc.sid
having count(cid)=(select count(cid) from  course);

24、统计每门课程的学生选修人数（超过 5 人的课程才统计）

select  cid, count(sid) as '选修人数'
from sc
group by cid
having count(sid)>5;

25、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

# 先查询出有课程小于70的学号，再把这些学号排除
select sc.sid, student.sname,course.cname, sc.score
from sc join student on sc.sid=student.sid join course on sc.cid= course.cid
where sc.sid not in
(select distinct sid from sc
where score<70)  

26、查询每门课程成绩相同的学生信息、查询每个同学成绩相同的课程信息

 A：每门课程下成绩相同的学生信息

select sc.cid , sc.sid, sc.score
from sc join  (select cid, score from sc  group by cid, score having count(sid)>=2) a 
        on sc.cid=a.cid and sc.score=a.score
order by cid ;

运行结果：

 

 B：不同课程下成绩相同的学生信息

select    sc.sid, sc.cid , sc.score
from sc join  (select sid, score from sc  group by sid, score having count(cid)>=2) a 
        on sc.sid=a.sid and sc.score=a.score
order by sid;

运行结果：

 或

# 每位同学有相同成绩的课程信息
select a.sid, a.cid, a.score
from sc a left join sc b on a.cid<>b.cid and a.score=b.score and a.sid=b.sid
where b.sid is not null;


# 每门课程下有相同成绩的学生信息
select a.sid, a.cid, a.score
from sc a left join sc b on a.cid=b.cid and a.score=b.score and a.sid<>b.sid
where b.sid is not null;

27、查询 1990 年出生的学生名单

select  sid, sname
from student
where  year(sborn)='1990';

28、查询各学生的年龄，只按年份来算

select  sid , sname, year(now())-year(sborn) as age
from student;

29、按照出生日期来算，当前月日 < 出生年月的月日，则年龄减一

 timestampdiff函数：日期或日期时间表达式之间的整数差。

 语法：TIMESTAMPDIFF(interval，datetime1，datetime2)   datetime2-datetime1

select sid, sname ,sborn, timestampdiff(year,sborn,date_format(now(),'%Y-%m-%d')) as '题目要求计算的age', 
year(now())-year(sborn) as '上一题的年龄'

from student;

运行结果：

30、查询本周过生日的学生

特别注意： 本周过生日是指，出生日期放到现在的年份下，是哪一周过生日，而不是简单地出生日期的周数等于现在日期的周数就可以了，这一点要注意，很容易混淆。

PS：

a.  在where子句中两端使用的函数要相同，因为week( )函数的周数是从0开始，weekofyear( )函数是从1开始

b. concat( )函数、concat_ws( )函数都可以，

c. 连接中间的年月日时，有两种形式：一种是使用函数date_format( )，另一种是整理出年、月、日，然后进行连接

d. 中间的连接符：-, /, #, * , $，这几个试了都可以

下面的where 子句除了最后一行，任选一行都能运行出正确的结果

select * 
from student 

where weekofyear(concat(year(now()),'-',date_format(sborn,'%m-%d')))=weekofyear(now());  #成功

where weekofyear(concat(year(now()),'/',month(sborn),'/',day(sborn)))=weekofyear(now());

where week(concat(year(now()),'-',month(sborn),'-',day(sborn)))=week(now());

where week(concat_ws('',year(now()),month(sborn),day(sborn)))=week(now());

where week(concat_ws('-',year(now()),date_format(sborn,'%m-%d')))=week(now());

#where  weekofyear(year(now())&"-"&month(sborn)&"-"&day(sborn))=weekofyear(now()); #这种形式的连接报错

31、查询下周过生日的学生

select * 
from student 
where weekofyear(concat_ws('-',year(now()),date_format(sborn,'%m-%d')))=weekofyear(now())+1;

32、查询本月过生日的学生

select  sid , sname, sborn
from student
where month(sborn)=month(now());

33、查询下月过生日的学生

select  sid , sname, sborn
from student
where month(sborn)=month(now())+1;