【1】-Mysql之超經(jīng)典SQL練習(xí)題

文章下方有視頻鏈接

一、數(shù)據(jù)表

--1.學(xué)生表 Student(SId,Sname,Sage,Ssex)
--SId 學(xué)生編號(hào),Sname 學(xué)生姓名,Sage 出生年月,Ssex 學(xué)生性別
--2.課程表 Course(CId,Cname,TId) --CId --課程編號(hào),Cname 課程名稱,TId 教師編號(hào)
--3.教師表 Teacher(TId,Tname) --TId 教師編號(hào),Tname 教師姓名
--4.成績(jī)表 SC(SId,CId,score) --SId 學(xué)生編號(hào),CId 課程編號(hào),score 分?jǐn)?shù)

二、創(chuàng)建測(cè)試數(shù)據(jù)

1.學(xué)生表 Student

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '趙雷' , '1990-01-01' , '男');
insert into Student values('02' , '錢(qián)電' , '1990-12-21' , '男');
insert into Student values('03' , '孫風(fēng)' , '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('09' , '張三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2017-12-30' , '女');
insert into Student values('12' , '趙六' , '2017-01-01' , '女');
insert into Student values('13' , '孫七' , '2018-01-01' , '女');
Student

2.科目表 Course

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10))
insert into Course values('01' , '語(yǔ)文' , '02');
insert into Course values('02' , '數(shù)學(xué)' , '01');
insert into Course values('03' , '英語(yǔ)' , '03');
course

3.教師表 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' , '王五');
teacher

4.成績(jī)表 SC

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1))
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);
sc

三、參考答案

1.查詢" 01 "課程比" 02 "課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)

解題思路：此題用的是99連接法(就是9*9乘法公式一樣的邏輯)。先找出同時(shí)選修了01課程和02課程的學(xué)生編號(hào)和課程對(duì)應(yīng)的成績(jī)，再用where設(shè)置相同條件。

select *
from (select SId ,score from sc where sc.CId='01') as t1 , 
(select SId ,score from sc where sc.CId='02') as t2
where t1.SId=t2.SId
and   t1.score>t2.score

當(dāng)然也可以用內(nèi)聯(lián)查詢，我們來(lái)試一試：

select * from 
(select sid,cid,score from sc where cid = '01') t1 inner join (select sid,cid,score from sc where cid ='02') t2
on t1.sid=t2.sid and t1.score>t2.score;

1.1 查詢同時(shí)存在" 01 "課程和" 02 "課程的情況

選修了01課程的學(xué)生與選修了02課程的學(xué)生的交集即是同時(shí)選修了01、02兩門(mén)課程的學(xué)生。

select *
from (select SId ,score from sc where sc.CId='01')as t1 , (select SId ,score from sc where sc.CId='02') as t2
where t1.SId=t2.SId

另外一種表達(dá)也可以

select *
from (select SId ,score from sc where sc.CId='01')as t1 inner join (select SId ,score from sc where sc.CId='02') as t2
on t1.SId=t2.SId

再看一種寫(xiě)法：

select *
from (select SId ,score from sc where sc.CId='01')as t1 left  join (select SId ,score from sc where sc.CId='02') as t2
on t1.SId=t2.SId where t2.score is not null;
1.2 查詢存在" 01 "課程但可能不存在" 02 "課程的情況(不存在時(shí)顯示為 null )

思路：此題為典型的左連接題，可以將選修了01課程的學(xué)生的成績(jī)信息左連接于選修了02課程的學(xué)生的成績(jī)信息。由于采用左連接，所以02課程不存在時(shí)自動(dòng)補(bǔ)充null值。

select *
from (select SId ,score from sc where sc.CId='01')as t1 left join (select SId ,score from sc where sc.CId='02') as t2
on t1.SId=t2.SId

1.3 查詢不存在" 01 "課程但存在" 02 "課程的情況

思路：先找出沒(méi)有選修01課程的學(xué)生的成績(jī)信息，再進(jìn)一步篩選出同時(shí)選修了02課程的學(xué)生的成績(jī)信息。

select *
from sc
where sc.SId not in (select SId from sc where sc.CId='01')
and  sc.CId='02'

2.查詢平均成績(jī)大于等于 60 分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)

select sc.sid,st.sname,avg(score) from 
sc,student as st where sc.sid=st.sid group by sc.sid having avg(score)>=60;

來(lái)看另外一種思路，有點(diǎn)復(fù)雜

select student.*,t1.avgscore
from student inner JOIN(
select sc.SId ,AVG(sc.score)as avgscore
from sc
GROUP BY sc.SId
HAVING AVG(sc.score)>=60)as t1 on student.SId=t1.SId

3.查詢?cè)?SC 表存在成績(jī)的學(xué)生信息

思路：用sc表中的學(xué)生編號(hào)對(duì)student中的學(xué)生信息進(jìn)行篩選。

select DISTINCT student.*
from student ,sc
where student.SId=sc.SId

4.查詢所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名、選課總數(shù)、所有課程的總成績(jī)(沒(méi)成績(jī)的顯示為null)

select st.sid,st.sname,count(sc.cid),sum(sc.score) from student st,sc where st.sid=sc.sid group by sc.sid;

另外一種方法

select student.SId,student.Sname,t1.sumscore,t1.coursecount
from student ,(
select SC.SId,sum(sc.score)as sumscore ,count(sc.CId) as coursecount
from sc
GROUP BY sc.SId) as t1
where student.SId =t1.SId
4.1 查有成績(jī)的學(xué)生信息
select *
from student
where EXISTS(select * from sc where student.SId=sc.SId)
4.2 查詢「李」姓老師的數(shù)量
select count(*)
from teacher
where teacher.Tname like '李%
4.3 查詢學(xué)過(guò)「張三」老師授課的同學(xué)的信息
select student.*
from teacher  ,course  ,student,sc
where teacher.Tname='張三'
and   teacher.TId=course.TId
and   course.CId=sc.CId
and   sc.SId=student.SId
5. 查詢沒(méi)有學(xué)全所有課程的同學(xué)的信息
解法1
select student.*
from sc ,student
where sc.SId=student.SId
GROUP BY sc.SId
Having count(*)<(select count(*) from course)
但這種解法得出來(lái)的結(jié)果不包括什么課都沒(méi)選的同學(xué)。
解法2
select DISTINCT student.*
from
(select student.SId,course.CId
from student,course ) as t1 LEFT JOIN (SELECT sc.SId,sc.CId from sc)as t2 on t1.SId=t2.SId and t1.CId=t2.CId,student
where t2.SId is null
and   t1.SId=student.SId

利用笛卡爾積可以把什么課都沒(méi)選的同學(xué)查詢出來(lái)

6.查詢至少有一門(mén)課與學(xué)號(hào)為" 01 "的同學(xué)所學(xué)相同的同學(xué)的信息

思路：從sc表中先找出學(xué)號(hào)為01的學(xué)生選修的課程的編號(hào)，以選出來(lái)的課程編號(hào)作為條件，再?gòu)膕c表中篩選出至少有一門(mén)課與學(xué)號(hào)為01的同學(xué)所學(xué)相同的同學(xué)的學(xué)生學(xué)號(hào)，最后根據(jù)篩選出來(lái)的學(xué)生編號(hào)從student表中再次篩選出相應(yīng)學(xué)生的信息。

select DISTINCT student.*
from  sc ,student
where sc.CId in (select CId from sc where sc.SId='01')
and   sc.SId=student.SId

7.查詢和" 01 "號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息

這道題目也挺難的，這次單獨(dú)寫(xiě)一篇文章講解查詢和01號(hào)學(xué)生學(xué)習(xí)的課程完全相同的其他同學(xué)的信息

select t1.* from student t1 inner join
(select sc.sid from sc where cid in 
(select cid from sc where sid ='01') and sid !='01'
group by sc.sid having count(distinct cid) =
(select count(distinct cid) from sc where sid='01')) t2 on t1.sid= t2.sid

8.查詢沒(méi)學(xué)過(guò)"張三"老師講授的任一門(mén)課程的學(xué)生姓名

思路：先查出選了張三將授的課程的學(xué)生，再去跟student表進(jìn)行一個(gè)左連接。

 select * from student where student.sid not in
(select distinct student.sid from student,sc where student.sid= sc.sid and  sc.cid = (select course.cid from course,teacher where course.tid=teacher.tid and tname='張三'));

另一種表達(dá)：

select * from student where student.SId not in
(select student.SId from student left join sc on student.SId=sc.SId
where EXISTS (select * from teacher ,course where teacher.Tname='張三'
and  teacher.TId=course.TId and   course.CId=sc.CId))

9.查詢兩門(mén)及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī)

select student.SId,student.Sname,avg(sc.score)
from student ,sc where student.SId=sc.SId and  sc.score<60
GROUP BY sc.SId  HAVING count(*)>=2

10. 檢索" 01 "課程分?jǐn)?shù)小于 60，按分?jǐn)?shù)降序排列的學(xué)生信息

select student.*
from student,sc
where sc.CId ='01'
and   sc.score<60
and   student.SId=sc.SId

11.按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī)(重點(diǎn))

select sc.SId,sc.CId,sc.score,t1.avgscore
from  sc left join (select sc.SId,avg(sc.score) as avgscore
from sc
GROUP BY sc.SId) as t1 on sc.SId =t1.SId
ORDER BY t1.avgscore DESC
12.查詢各科成績(jī)最高分、最低分和平均分： 以如下形式顯示：課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率 及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90 要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列

select sc.CId ,
max(sc.score)as 最高分,min(sc.score)as 最低分,
AVG(sc.score)as 平均分,count(*)as 選修人數(shù),
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 and sc.score<80 then 1 else 0 end )/count(*)as 優(yōu)良率,
sum(case when sc.score>=90 then 1 else 0 end )/count(*)as 優(yōu)秀率
from sc GROUP BY sc.CId
ORDER BY count(*)DESC,sc.CId asc

13.按各科成績(jī)進(jìn)行排序，并顯示排名， Score 重復(fù)時(shí)保留名次空缺


select sc.CId ,@curRank:=@curRank+1 as rank,sc.score
from (select @curRank:=0) as t ,sc
ORDER BY sc.score desc

14. 按各科成績(jī)進(jìn)行排序，并顯示排名， Score 重復(fù)時(shí)合并名次

select sc.CId , case when @fontscore=score then @curRank when @fontscore:=score then @curRank:=@curRank+1  end as rank,sc.score
from (select @curRank:=0 ,@fontage:=null) as t ,sc
ORDER BY sc.score desc

15.查詢學(xué)生的總成績(jī)，并進(jìn)行排名，總分重復(fù)時(shí)保留名次空缺



select t1.*,@currank:= @currank+1 as rank
from (select sc.SId, sum(score)
from sc
GROUP BY sc.SId
ORDER BY sum(score) desc) as t1,(select @currank:=0) as t

16. 查詢學(xué)生的總成績(jī)，并進(jìn)行排名，總分重復(fù)時(shí)不保留名次空缺

select t1.*, case when @fontscore=t1.sumscore then @currank  when @fontscore:=t1.sumscore  then @currank:=@currank+1  end as rank
from (select sc.SId, sum(score) as sumscore
from sc
GROUP BY sc.SId
ORDER BY sum(score) desc) as t1,(select @currank:=0,@fontscore:=null) as t

17.統(tǒng)計(jì)各科成績(jī)各分?jǐn)?shù)段人數(shù)：課程編號(hào)，課程名稱，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比



select course.CId,course.Cname,t1.*
from course LEFT JOIN (
select sc.CId,CONCAT(sum(case when sc.score>=85 and sc.score<=100 then 1 else 0 end )/count(*)*100,'%') as '[85-100]',
CONCAT(sum(case when sc.score>=70 and sc.score<85 then 1 else 0 end )/count(*)*100,'%') as '[70-85)',
CONCAT(sum(case when sc.score>=60 and sc.score<70 then 1 else 0 end )/count(*)*100,'%') as '[60-70)',
CONCAT(sum(case when sc.score>=0 and sc.score<60 then 1 else 0 end )/count(*)*100,'%') as '[0-60)'
from sc
GROUP BY sc.CId) as t1 on course.CId=t1.CId

18.查詢各科成績(jī)前三名的記錄

思路：前三名轉(zhuǎn)化為若大于此成績(jī)的數(shù)量少于3即為前三名。
select *
from sc 
where  (select count(*) from sc as a where sc.CId =a.CId and  sc.score <a.score )<3
ORDER BY CId asc,sc.score desc

19.查詢每門(mén)課程被選修的學(xué)生數(shù)


select sc.CId,count(*)
from sc
GROUP BY sc.CId

20.查詢出只選修兩門(mén)課程的學(xué)生學(xué)號(hào)和姓名


select student.SId,student.Sname
from sc,student
where student.SId=sc.SId 
GROUP BY sc.SId
HAVING count(*)=2

21.查詢男生、女生人數(shù)

select student.Ssex ,count(*) as 人數(shù)
from student
GROUP BY student.Ssex

22.查詢名字中含有「風(fēng)」字的學(xué)生信息

select *
from student
where student.Sname like '%風(fēng)%'

23.查詢同名同性學(xué)生名單，并統(tǒng)計(jì)同名人數(shù)

select *
from student LEFT JOIN (select Sname,Ssex,COUNT(*)同名人數(shù) from Student group by Sname,Ssex) as t1
on student.Sname =t1.Sname and student.Ssex=t1.Ssex
where t1.同名人數(shù)>1

24.查詢 1990 年出生的學(xué)生名單

select *
from student
where YEAR(student.Sage)=1990

25.查詢每門(mén)課程的平均成績(jī)，結(jié)果按平均成績(jī)降序排列，平均成績(jī)相同時(shí)，按課程編號(hào)升序排列

select sc.CId,AVG(sc.score)
from sc
GROUP BY sc.CId
ORDER BY AVG(sc.score) desc ,sc.CId asc

26.查詢平均成績(jī)大于等于 85 的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)

select student.SId,student.Sname,t1.avgscore
from student INNER JOIN (select sc.SId ,AVG(sc.score) as avgscore from sc GROUP BY sc.SId HAVING AVG(sc.score)>85) as t1 on
student.SId=t1.SId

27.查詢課程名稱為「數(shù)學(xué)」，且分?jǐn)?shù)低于 60 的學(xué)生姓名和分?jǐn)?shù)



select student.Sname ,t1.score
from student INNER JOIN  (select sc.SId,sc.score
from sc,course
where sc.CId=course.CId
and   course.Cname='數(shù)學(xué)'
and   sc.score<60)as t1 on student.SId=t1.SId

28.查詢所有學(xué)生的課程及分?jǐn)?shù)情況（存在學(xué)生沒(méi)成績(jī)，沒(méi)選課的情況）

select student.SId,sc.CId,sc.score from Student  left join sc  on student.SId=sc.SId

29.查詢?nèi)魏我婚T(mén)課程成績(jī)?cè)?70 分以上的姓名、課程名稱和分?jǐn)?shù)

select student.Sname,course.Cname,sc.score
from student , sc  ,course
where sc.score>=70
and  student.SId=sc.SId
and sc.CId=course.CId

30.查詢存在不及格的課程

select DISTINCT sc.CId
from sc
where sc.score <60

31.查詢課程編號(hào)為 01 且課程成績(jī)?cè)?80 分以上的學(xué)生的學(xué)號(hào)和姓名

select student.SId,student.Sname
from student ,sc
where sc.CId='01'
and  student.SId=sc.SId
and  sc.score>80

32.求每門(mén)課程的學(xué)生人數(shù)


select sc.CId,count(*) as 學(xué)生人數(shù)
from sc
GROUP BY sc.CId

33.成績(jī)不重復(fù)，查詢選修「張三」老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生信息及其成績(jī)


select student.*,sc.score
from student ,course ,teacher ,sc
where course.CId=sc.CId
and course.TId=teacher.TId
and teacher.Tname='張三'
and student.SId =sc.SId
LIMIT 1

34.成績(jī)有重復(fù)的情況下，查詢選修「張三」老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生信息及其成績(jī)



select student.*,t1.score
from student INNER JOIN (select sc.SId,sc.score, case when @fontage=sc.score then @rank when @fontage:=sc.score then @rank:=@rank+1 end  as rank
from course ,teacher ,sc,(select @fontage:=null,@rank:=0) as t
where course.CId=sc.CId
and course.TId=teacher.TId
and teacher.Tname='張三'
ORDER BY sc.score DESC) as t1 on student.SId=t1.SId
where t1.rank=1

35.查詢不同課程成績(jī)相同的學(xué)生的學(xué)生編號(hào)、課程編號(hào)、學(xué)生成績(jī)


select *
from sc as t1
where exists(select * from sc as t2 where t1.SId=t2.SId and t1.CId!=t2.CId and t1.score =t2.score )

36.查詢每門(mén)功成績(jī)最好的前兩名

select *
from sc as t1
where (select count(*) from sc as t2 where t1.CId=t2.CId and t2.score >t1.score)<2
ORDER BY t1.CId

37.統(tǒng)計(jì)每門(mén)課程的學(xué)生選修人數(shù)（超過(guò) 5 人的課程才統(tǒng)計(jì)）

select sc.CId as 課程編號(hào),count(*) as 選修人數(shù)
from sc
GROUP BY sc.CId
HAVING count(*)>5

38.檢索至少選修兩門(mén)課程的學(xué)生學(xué)號(hào)

select DISTINCT t1.SId
from sc as t1
where (select count(* )from sc where t1.SId=sc.SId)>=3

39.查詢選修了全部課程的學(xué)生信息


select student.*
from sc ,student
where sc.SId=student.SId
GROUP BY sc.SId
HAVING count(*) = (select DISTINCT count(*) from course )

40.查詢各學(xué)生的年齡，只按年份來(lái)算

select student.SId as 學(xué)生編號(hào),student.Sname  as  學(xué)生姓名,TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) as 學(xué)生年齡
from student

41.按照出生日期來(lái)算，當(dāng)前月日 < 出生年月的月日則，年齡減一



select student.SId as 學(xué)生編號(hào),student.Sname  as  學(xué)生姓名,TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) as 學(xué)生年齡
from student

42.查詢本周過(guò)生日的學(xué)生

select *
from student
where YEARWEEK(student.Sage)=YEARWEEK(CURDATE())

43.查詢下周過(guò)生日的學(xué)生


select *
from student
where YEARWEEK(student.Sage)=CONCAT(YEAR(CURDATE()),week(CURDATE())+1)

44.查詢本月過(guò)生日的學(xué)生

select *
from student
where EXTRACT(YEAR_MONTH FROM student.Sage)=EXTRACT(YEAR_MONTH FROM CURDATE())

45.查詢下月過(guò)生日的學(xué)生

select *
from student
where EXTRACT(YEAR_MONTH FROM student.Sage)=EXTRACT(YEAR_MONTH FROM DATE_ADD(CURDATE(),INTERVAL 1 MONTH))

如果想看視頻講解的話
經(jīng)典50題視頻精講

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容