msql经典五十题

最新推荐文章于 2024-06-21 15:34:27 发布

leolon_

最新推荐文章于 2024-06-21 15:34:27 发布

阅读量81

点赞数

分类专栏：学习笔记文章标签：数据库 sql

本文链接：https://blog.csdn.net/leolon_/article/details/127731824

版权

学习笔记专栏收录该内容

7 篇文章 1 订阅

订阅专栏

建表语句
学生表 Student

create table Student(
    SId varchar(10),
    Sname varchar(10),
    Sage datetime,
    Ssex varchar(10)
);

课程表 Course

create table Course(
    CId varchar(10),
    Cname nvarchar(10),
    TId varchar(10)
);

教师表 Teacher

create table Teacher(
    TId varchar(10),
    Tname varchar(10)
);

成绩表 SC

create table SC(
    SId varchar(10),
    CId varchar(10),
    score decimal(18,1)
);

插入数据
注意这里插入数据的时候，里面可能含有隐藏字符，出现显示不出的数据手动重新打一下再插入即可

学生表 Student

-- 学生表 Student
insert into Student values('01' , '赵雷' , '1990-01-01' , '男'); 
insert into Student values('02' , '钱电' , '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('09' , '张三' , '2017-12-20' , '女'); 
insert into Student values('10' , '李四' , '2017-12-25' , '女'); 
insert into Student values('11' , '李四' , '2012-06-06' , '女'); 
insert into Student values('12' , '赵六' , '2013-06-13' , '女'); 
insert into Student values('13' , '孙七' , '2014-06-01' , '女');

课程表 Course

-- 科目表 Course 
insert into Course values('01' , '语文' , '02'); 
insert into Course values('02' , '数学' , '01'); 
insert into Course values('03' , '英语' , '03');

教师表 Teacher

-- 教师表 Teacher 
insert into Teacher values('01' , '张三'); 
insert into Teacher values('02' , '李四'); 
insert into Teacher values('03' , '王五');

成绩表 SC

-- 成绩表 SC 
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' , '02' , 30); 
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);

-- 查询" 01 “课程比” 02 “课程成绩高的学生的信息及课程分数
select tt1.SId,tt2.score,tt3.Sname,tt3.Sage,tt3.Ssex from (
select t1.Sid from 
(select * from SC where CId = '01') t1
left join 
(select * from SC where CId = '02') t2
on t1.SId = t2.SId
where t1.score > t2.score) tt1
join SC tt2 on tt1.SId = tt2.SId
join Student tt3 on tt1.SId = tt3.SId; 

-- 2 查询同时存在” 01 “课程和” 02 “课程的情况
select tt1.SId,tt2.Sname,tt2.Sage,tt3.CId,tt3.score FROM 
(select t1.SId from
(select * from SC where CId = '01' )t1
JOIN 
(select * from SC where CId = '02')t2
on t1.SId = t2.SId)tt1
join Student tt2 on tt1.SId = tt2.SId
join SC tt3 on tt1.SId = tt3.SId;

-- 3 查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )
select 
		t1.SId
       ,t1.CId
       ,t1.score
       ,t2.CId AS t2CId
       ,t2.score AS t2Score
from
(select * from SC where CId = '01' )t1
left JOIN 
(select * from SC where CId = '02')t2
on t1.SId = t2.SId;

-- 4 查询不存在” 01 “课程但存在” 02 "课程的情况
select 
		t1.SId
       ,t1.CId
       ,t1.score
       ,t2.CId AS t2CId
       ,t2.score AS t2Score
from
(select * from SC where CId = '01' )t1
right JOIN 
(select * from SC where CId = '02')t2
on t1.SId = t2.SId;

-- 5.查询平均成绩⼤于等于 60 分的同学的学生编号和学生姓名和平均成绩
select tt1.SId,avg(score) from 
(select t1.SId,t1.Sname,score,t2.CId from 
(select * from Student) t1
right join 
(select * from SC) t2
on t1.SId = t2.SId) tt1  group by tt1.SId;

select * FROM 
(select SId,AVG(score) as avg_score  from SC group by SId HAVING avg_score >=60) t1
join Student t2
on t1.SId = t2.SId;

-- 6.查询在 SC 表存在成绩的学生信息

select * from Student s right join (select DISTINCT SId from SC ) sc on s.SId = sc.SId; 

-- 7查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩，没成绩的显示为 null

select * from 
(select * from Student )t1
left join (select count(CId),SId,SUM(score) as sum_score from SC group by SId) t2
on t1.SId = t2.SId 
order by sum_score;


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

select count(*) from Teacher t where t.Tname like '李%';


-- 9.查询学过「张三」老师授课的同学的信息
select * from Student ttt2 join 
(select SId from SC sc join (select c.CId from Course c join (select TId from Teacher where Tname = '张三') t where t.TId = c.TId) tt1 on tt1.CId = sc.CId) ttt1
on ttt1.SId = ttt2.SId ;

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

select tt1.SId,tt1.Sname,count(tt1.CId) as cnt from 
	(select s.SId,s.Sname,sc.CId from 
		(select * from Student) s
	left join 
		(select * from SC) sc
	on  s.SId = sc.SId ) tt1
	group by tt1.SId,tt1.Sname
	Having cnt < (SELECT  count(*) FROM Course);

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


select * from Student s join 
(select distinct  SId from SC sc2 join (select sc.CId from SC sc where sc.SId ='01') t1 where sc2.CId =t1.CId) tt1
ON s.SId =tt1.SId;

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

select * FROM 
(select t1.SId,COUNT(t2.CId) as t2_count  from 
	(select * from SC sc1 where sc1.SId !='01') t1 
join 
	(select * from SC sc where sc.SId ='01') t2 
on 
	t2.CId =t1.CId 
GROUP by 
	t1.SId) tt1
join 
(select SId,count(SId) as sc_count from SC where SId = '01' group by SId) tt2
on tt1.t2_count= tt2.sc_count;

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

select s.SId ,s.Sname from Student s 
where s.SId not IN 
(select SId from 
(select CId from 
(select * from Teacher t where t.Tname ='张三') t1 
join Course c 
on t1.TId = c.TId) tt1
join SC sc on tt1.CId = sc.CId
group by SId) 
group by SId,Sname;

-- 14.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select s.SId ,s.Sname,t1.avg_score  from Student s join
(select sc.SId,count(sc.CId) as cid_count,AVG(sc.score) as avg_score from SC sc where score <60 group by sc.SId HAVING cid_count >=2) t1
on s.SId = t1.SId;


-- 15.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
	select * from Student s join
	(select * from SC sc where sc.CId = '01' and sc.score <60 order by sc.score DESC ) t1
	on s.SId =t1.Sid;
	
-- 16.按平均成绩从⾼到低显示所有学生的所有课程的成绩以及平均成绩
	select s.Sname ,t1.avg_score,t2.score,t3.score,t4.score from Student s 
	  join (select sc.SId ,AVG(sc.score) as avg_score from SC sc group by sc.SId ) t1 on s.SId =t1.SId
	left join (select sc.SId,sc.score from SC sc where CId = '01') t2 on s.SId  = t2.SId
	left join (select sc.SId,sc.score from SC sc where CId = '02') t3 on s.SId  = t3.SId
	left join (select sc.SId,sc.score from SC sc where CId = '03') t4 on s.SId  = t4.SId
	order by avg_score DESC;
	
-- 17.查询各科成绩最⾼分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最⾼分，最低分，平均分，及格率，中等率，优良率，
--    优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列


select 
sc.CId,
c.Cname,
max(score)as '最高分',
min(score)as '最低分',
avg(score) as '平均分',
concat(sum(if(score>=60,1,0))*100/count(score),"%") as '及格率',
concat(sum(if(score>=70 && score<80 ,1,0))*100/count(score),"%") as '中等率',
concat(sum(if(score>=80 && score<90 ,1,0))*100/count(score),"%") as '优良率',
concat(sum(if(score>=90,1,0))*100/count(score),"%") as '优秀率',
count(score) as '人数'
from SC sc
join Course c 
on sc.CId =c.CId 
group by sc.CId,c.Cname order by '人数' DESC ; 

-- 18.按各科平均成绩进行排序，并显示排名

	select t1.CId,t1.avg_score,@i:=@i+1 as '排名' FROM 
	( select sc.CId,avg(score) as avg_score from SC sc group by sc.CId order by avg_score DESC ) t1 ,(select @i := 0) z;

-- 19.按各科平均成绩进行排序，并显示排名，重复时不保留名次空缺
SELECT t1.CId
       ,t1.avg_score
       ,@i := @i + 1 AS '排名' 
FROM(
   SELECT CId
          ,AVG(score) avg_score 
FROM SC 
GROUP BY CId 
ORDER BY avg_score DESC
) t1,(select @i :=0) z;

-- 20.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select 
	t1.SId ,t1.Sname ,t1.sum_score,@i := @i+1 as '排名'
from 
(
select 
	s.SId ,s.Sname ,sum(sc.score) as sum_score 
from 
	SC sc 
join 
	Student s 
on 
	sc.SId =s.SId 
group by 
	s.SId,s.Sname 
order by 
	sum_score DESC ) t1,(select @i := 0) z;

select 
t1.SId,
t1.score,
@r :=@r+1,
@score := t1.score,
if(@score = @j,@i,@i:= @r) as '排名',
@j := @score
FROM
(select * from SC sc order by score desc) t1,(select @i := 0,@score := 0,@j := 0,@r := 0) z;


-- 22.统计各科成绩各分数段人数：课程编号，课程名称，[100-85），[85-70），[70-60），[60-0）及所占百分

select c.Cname ,
concat(round(sum(if(sc.score >85,1,0))/count(sc.score),2)*100,"%") as '[100-85）',
concat(round(sum(if(sc.score >85,1,0))/count(sc.score),2)*100,"%") as '[85-70）',
concat(round(sum(if(sc.score >85,1,0))/count(sc.score),2)*100,"%") as '[70-60）',
concat(round(sum(if(sc.score >85,1,0))/count(sc.score),2)*100,"%") as '[60-0）'
from SC sc left join Course c on sc.CId =c.CId group by sc.CId,c.Cname ;

-- 23.查询各科成绩前三名的记录

select tt1.rank,tt1.score,tt2.score,tt3.score FROM 
(select t1.score,@i:=@i+1 as rank FROM (select * from SC sc where sc.CId = 1) t1 ,(select @i := 0) v1 order by t1.score DESC) tt1  
left join (select t1.score,@j:=@j+1 as rank FROM (select * from SC sc where sc.CId = 2) t1 ,(select @j := 0) v1 order by t1.score DESC) tt2 on tt1.rank = tt2.rank 
left join (select t1.score,@k:=@k+1 as rank FROM (select * from SC sc where sc.CId = 3) t1 ,(select @k := 0) v1 order by t1.score DESC) tt3 on tt1.rank = tt3.rank
where tt1.rank <=3;

-- 24.查询每门课程被选修的学生数
select sc.CId , count(sc.SId) from SC sc group by sc.CId;

-- 25.查询出只选修两门课程的学生学号和姓名
select * from Student s join
(select sc.SId ,count(sc.CId ) as count from SC sc group by sc.SId HAVING count= 2) t1 on t1.SId = s.SId;

-- 26.查询男生、女生人数
select s.Ssex,count(s.Sname) from Student s group by s.Ssex;

-- 27.查询名字中含有「风」字的学生信息
select * from Student s where s.Sname  like '%风%';

-- 28.查询同名同性学生名单，并统计同名同性人数
select s.Sname,s.Ssex,count(s.Sname) as sameName from Student s group by s.Sname,s.Ssex HAVING sameName > 1;

-- 29.查询 1990 年出生的学生名单
select * from Student s where s.Sage >='1990-01-01' AND s.Sage <'1991-01-01';

-- 30.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select t1.CId,t1.avg_score,@i:=@i+1 as '排名' FROM 
(select sc.CId,avg(sc.score) as avg_score  from SC sc group by sc.CId ) t1,
(select @i := 0) z order by t1.avg_score,t1.CId;

-- 31.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT * from (select sc.SId,avg(sc.score) as avg_score from SC sc group by sc.SId HAVING avg_score >= 85) t1 join Student s 
on t1.SId = s.SId ;

-- 32.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select * from Course c left join 
SC sc on sc.CId =c.CId left join 
Student s on s.SId  = sc.SId where c.Cname  = '数学' and sc.score <60;

-- 33.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select sc.SId,
sum(case sc.CId when '01' then score else 0 end) as '语文',
sum(case sc.CId when '02' then score else 0 end) as '数学',
sum(case sc.CId when '03' then score else 0 end) as '英语'
from SC sc group by sc.SId ;


-- 34.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select s.Sname ,c.Cname ,sc.score from SC sc left join Student s on s.SId =sc.SId 
left join Course c on c.CId  = sc.CId where sc.score >70;

-- 35.查询不及格的课程
select s.Sname ,c.Cname ,sc.score from SC sc left join Student s on s.SId =sc.SId 
left join Course c on c.CId  = sc.CId where sc.score <60;

-- 36.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select s.Sname,s.SId,sc.CId,sc.score from SC sc left join Student s on s.SId  = sc.SId where sc.score >=80 and sc.CId = 01

-- 37.求每门课程的学生人数
select sc.CId,count(sc.score) from SC sc group by sc.CId;

-- 38.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select s.Sname,sc.score from Teacher t left join Course c on c.TId  = t.TId left join SC sc on sc.CId = c.CId left join Student s on s.SId  = sc.SId 
where t.Tname  = '张三' order by sc.score DESC LIMIT 0,1;

-- 39.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select 
tt1.Sname,
tt1.score,
tt1.rank
from 
(select 
t1.Sname,
t1.score,
@j:=t1.score,
@r := @r +1,
if(@j=@k,@i,@i := @r) as rank,
@k:=t1.score
FROM 
(select s.Sname,sc.score,t.Tname from Teacher t left join Course c on t.TId = c.TId left join SC sc on sc.CId =c.CId left join Student s on s.SId =sc.SId) t1,
(select @i:=0,@j:=0,@k:=0,@r:=0)z
where t1.Tname ='张三'order by t1.score Desc) tt1 where rank = 1;

-- 40.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select * from SC;
select s.Sname,sc1.SId,sc1.CId,sc1.score from SC sc1 join SC sc2 on sc1.score = sc2.score and sc1.CId !=sc2.CId left join Student s on s.SId  = sc1.SId ;

-- 41.查询每门课程成绩最好的前两名
select t1.rank,t1.score,t2.score,t3.score from
(select @i:=@i+1 as rank,sc.score from SC sc ,(select @i := 0)i where sc.CId  = 01 order by sc.score desc) t1
left join (select @j:=@j+1 as rank ,sc.score from SC sc ,(select @j := 0)j where sc.CId  = 02 order by sc.score desc) t2 on t1.rank = t2.rank
left join (select @k:=@k+1 as rank ,sc.score from SC sc ,(select @k := 0)j where sc.CId  = 03 order by sc.score desc) t3 on t1.rank = t3.rank where t1.rank <=2;

-- 42.统计每门课程的学生选修人数（超过 5 人的课程才统计）。
select sc.CId,count(sc.score) as count_num from SC sc group by sc.CId HAVING count_num >5 

-- 43.检索至少选修两门课程的学生学号
select sc.SId,count(sc.score) as count_num from SC sc group by sc.SId HAVING count_num >=2

-- 44.查询选修了全部课程的学生信息
select sc.SId,count(sc.score) as count_num from SC sc group by sc.SId HAVING count_num =(select count(*) from Course)

-- 45.查询各学生的年龄，只按年份来算
SELECT TIMESTAMPDIFF(YEAR,s.Sage ,NOW()) as age
FROM Student s 

-- 46.按照出生日期来算，当前月日 < 出生年月的月日，则年龄减一
select 
IF (DATE_FORMAT(NOW(),'%m%d')-DATE_FORMAT(s.Sage ,'%m%d')>0,
DATE_FORMAT(NOW(),'%Y')-  DATE_FORMAT(s.Sage,'%Y'),
DATE_FORMAT(NOW(),'%Y')-  DATE_FORMAT(s.Sage,'%Y')-1)as age ,
DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(s.Sage ,'%Y') 
from Student s;



-- 47.查询本周过生日的学生
select * from Student s where week(s.Sage )=WEEK(NOW()); 

-- 48.查询下周过生日的学生
select * from Student s where week(s.Sage)= week(NOW())+1;

-- 49.查询本月过生日的学生
select * from Student s where MONTH(s.Sage) = month(NOW());

-- 50.查询下月过生日的学生
select * from Student s where MONTH(s.Sage) = month(NOW())+1;