深耕MySQL - 50道SQL練習題

1. 資料準備

1、有以下4張表：

學生表：student(學號,學生姓名,出生年月,性別)

成績表：score(學號,課程號,成績)

課程表：course(課程號,課程名稱,教師號)

教師表：teacher(教師號,教師姓名)

2、建立表結構並插入資料：

drop table if exists Student ;

-- 建立學生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);

-- 課程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
-- 教師表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
-- 成績表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
-- 插入學生表測試資料
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' , '女');
-- 課程表測試資料
insert into Course values('01' , '語文' , '02');
insert into Course values('02' , '數學' , '01');
insert into Course values('03' , '英語' , '03');

-- 教師表測試資料
insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

-- 成績表測試資料
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

3、資料庫表：

2. 50道SQL面試題

1、查詢課程編號為「01」的課程比「02」的課程成績高的所有學生的學號（難）

select distinct a.s_id 
from ( select * from score where c_id=01) a 
join ( select * from score where c_id=02) b 
on a.s_score>b.s_score 
where a.s_id=b.s_id;

思路：

(1) 先求出課程編號為「01」的學生課程成績

(2) 再求出課程編號為「02」的學生課程成績

(3) 將這兩張表連線查詢，條件是a.s_id = b.s_id，代表是同一個學生

2、查詢平均成績大於60分的學生的學號和平均成績

(1) 求每個學生的平均成績
(2) 使用having對每個學生的平均成績過濾

3、查詢所有學生的學號、姓名、選課數、總成績

select a.s_name,b.s_id,b.count,b.sum_score 
from student a 
left join (select s_id,count(*) count,sum(s_score) sum_score 
           from score 
           group by s_id) b 
on a.s_id=b.s_id;

思路：

(1) 先查詢出所有學生的學號，選課數，總成績

(2) 將步驟1中查詢出來的這張表作為一個字表與student表進行連線查詢

簡化查詢：可以不使用子查詢，直接將兩張表連線查詢

select a.s_name,b.s_id,count(b.s_id) count,sum(b.s_score) sum_score 
from student a  
left join score b 
on a.s_id=b.s_id 
group by s_id;

4、查詢姓「猴」的老師的個數

mysql> select count(t_name) from teacher where t_name='猴';

5、查詢沒學過「張三」老師課的學生的學號、姓名

select s_id,s_name from student where s_id  not in (select a.s_id
from score a 
join student b on a.s_id=b.s_id
join course c on a.c_id=c.c_id
join teacher d on c.t_id=d.t_id
where d.t_name='張三');

思路：

(1) 首先，將4張表連線，查詢出選過張三老師課的學生的學號

select a.s_id
from score a 
join student b on a.s_id=b.s_id
join course c on a.c_id=c.c_id
join teacher d on c.t_id=d.t_id
where d.t_name='張三';

(2) 將步驟1中的查詢作為一個子查詢，與student表精進行連線查詢

6、查詢學過「張三」老師所教的所有課的同學的學號、姓名

select b.s_id,b.s_name 
from score a 
join student b on a.s_id=b.s_id 
join course c on a.c_id=c.c_id 
join teacher d on c.t_id=d.t_id 
where d.t_name='張三';

7、查詢學過編號為「01」的課程並且也學過編號為「02」的課程的學生的學號、姓名

方法1 ：

select s_id,s_name 
from student 
where s_id in(select s_id 
              from score 
              where c_id='01')  
and s_id 
           in(select s_id 
              from score 
              where c_id='02');

思路：
(1) 分別查詢學過編號為「01」 和「02」的課程的學生的學號

select s_id from score where c_id='01'
select s_id from score where c_id='02'

(2) 學過編號為「01」的課程並且也學過編號為「02」的課程的學生的學號、姓名

方法2 ：

select distinct s.s_id,s.s_name from score a
join (select s_id from score where c_id='01') b on a.s_id=b.s_id
join (select s_id from score where c_id='02') c on a.s_id=c.s_id
join student s on a.s_id=s.s_id;

思路：

(1) 先將三張表連線查詢學過編號為「01」的課程並且也學過編號為「02」的課程的學生的學號

select distinct a.s_id 
from score a 
join (select s_id from score where c_id='01') b on a.s_id=b.s_id 
join (select s_id from score where c_id='02') c on a.s_id=c.s_id;

(2) 將student表和步驟1的查詢結果連表查詢出sname

8、查詢課程編號為「02」的總成績

mysql> select sum(s_score) from score group by c_id having c_id='02';

9、查詢所有，課程成績小於60分的學生的學號、姓名

select distinct a.s_id,a.s_name 
from student a 
left join score b on a.s_id=b.s_id 
where b.s_score<60 or a.s_id not in (select s_id from score);

(1) 查詢課程成績小於60分的學生的學號

(2) 將步驟1的查詢結果和student表聯表查詢出姓名，同時考慮沒有考試成績的學生

10、查詢沒有學全所有課的學生的學號、姓名

select a.s_id,a.s_name 
from student a 
left join score b on a.s_id=b.s_id 
group by a.s_id,a.s_name
having count(b.c_id)<(select count(c_id) from course);

11、查詢至少有一門課與學號為「01」的學生所學課程相同的學生的學號和姓名 （難）

select distinct a.s_id,a.s_name 
from student a 
right join score b on a.s_id=b.s_id 
where b.c_id in (select c_id from score where s_id='01') and a.s_id !='01';

(1) 查詢至少有一門課與學號為「01」的學生所學課程相同的學生的學號

(2) 根據步驟1的結果與student表聯表查詢出姓名，同時排除學號1的學生

12、查詢和「01」號同學所學課程完全相同的其他同學的學號（難）

select s_id 
from score where s_id!='01' 
group by s_id 
having count(s_id) = (select count(*) from score where s_id='01');

13、查詢沒學過"張三"老師講授的任一門課程的學生姓名

select s_id,s_name 
from student 
where s_id not in (select a.s_id 
                   from score a 
                   join course b on a.c_id=b.c_id 
                   join teacher c on b.t_id=c.t_id 
                   where c.t_name='張三');

15、查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績

select a.s_id,a.s_name,avg(s_score) avg_score 
from student a 
join score b on a.s_id=b.s_id 
where b.s_score<60 
group by s_id 
having count(b.s_id)>=2;

(1) 求出每個學生成績不及格的課程數量及平均成績

(2) 利用步驟1的結果與student表聯表查詢出學生姓名，其中課程成績不及格數量要大於等於2門

16、檢索"01"課程分數小於60，按分數降序排列的學生資訊

select a.s_id,a.s_name 
from student a 
join score b on a.s_id=b.s_id 
where b.s_score<60 and b.c_id='01' 
order by b.s_score desc;

17、按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績(難)

select a.s_id,a.s_score,b.avg_score
from score a 
left join (select s_id, avg(s_score) avg_score 
           from score 
           group by s_id) b 
on a.s_id =b.s_id 
order by avg_score desc;

注意：上述雖然表達出了題目的意思，但是最好以橫排形式展示，可以使用select巢狀子查詢

方法2 ：

select s_id,s_name,
(select s_score from score where score.s_id=student.s_id and c_id='01') 01_score,
(select s_score from score where score.s_id=student.s_id and c_id='02') 02_score,
(select s_score from score where score.s_id=student.s_id and c_id='03') 03_score,
(select avg(s_score) from score where score.s_id=student.s_id) avg_score
from student 
order by avg_score desc;

使用select子查詢巢狀查詢時，需要將有歧義的欄位區分出來比如： score.s_id=student.s_id

18、查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優良率，優秀率

(1) 查詢各科成績最高分、最低分和平均分

select a.c_id,a.c_name, avg(b.s_score) avg_score,min(b.s_score) min_score,max(b.s_score) max_score 
from course a j
oin score b on a.c_id=b.c_id 
group by b.c_id;

(2) 每一個科目的及格率，中等率，優良率，優秀率需要使用select後巢狀子查詢獲得

select 
b.c_id,b.c_name, avg(a.s_score) avg_score,min(a.s_score) min_score,max(a.s_score) max_score,
-- 注意：這裡相當於將 score a和course b聯表查詢後再與score這張表聯表查詢
(select count(*) from score where c_id=a.c_id and s_score>=60)/count(*) '及格率',
(select count(*) from score where c_id=a.c_id and s_score between 70 and 80)/count(*) '中登率',
(select count(*) from score where c_id=a.c_id and s_score between 80 and 90)/count(*) '優良率',
(select count(*) from score where c_id=a.c_id and s_score>90) /count(*) '優秀率'
from score a
join course b
on a.c_id=b.c_id
group by a.c_id;

19、查詢學生的總成績並進行排名

select a.s_id,a.s_name,(case when sum(b.s_score) is null then 0 else sum(b.s_score) end) sum_score 
from student a 
left join score b on a.s_id=b.s_id 
group by b.s_id 
order by sum_score desc;

20、查詢不同老師所教不同課程平均分,從高到低顯示

select c_id,avg(s_score) avg_score 
from score 
group by c_id 
order by avg_score desc;

21、查詢學生平均成績及其名次

select s_id,avg(s_score) avg_score,
row_number () over( order by avg(s_score) desc) row_num
from score 
group by s_id;

22、按各科成績進行排序，並顯示排名(難)

序號函數：row_number() / rank() / dense_rank()

partition子句：視窗按照那些欄位進行分組，視窗函數在不同的分組上分別執行。下面的例子就按照 c_id進行了分組。在每個 c_id上，按照order by的順序分別生成從1開始的順序編號。

order by子句：按照哪些欄位進行排序，視窗函數將按照排序後的記錄順序進行編號。可以和partition子句配合使用，也可以單獨使用。如果沒有partition子句，則會按照所有score排序來生成序號。

-- partition by c_id 按照c_id進行分組
--  order by s_score desc 按照s_score倒敘排序
select c_id,s_id,row_number() over(partition by c_id order by s_score desc) num_row 
from score;

23、查詢每門功課成績最好的前兩名學生姓名

select a.s_id,a.s_name,b.num_row 
from student a 
-- 將select查詢作為一張子表，與student表聯表查詢
join (select 
      s_id,
      c_id,
      row_number() over(partition by c_id order by s_score desc) num_row
      from score) b on a.s_id=b.s_id 
where b.num_row<=2;

24、查詢所有課程的成績第2名到第3名的學生資訊及該課程成績

select a.s_id,a.s_name,b.c_id,b.s_score,b.num_row 
from student a 
join (select 
      s_id,
      c_id,
      s_score,
      row_number() over(partition by c_id order by s_score desc) num_row from score) b 
on a.s_id=b.s_id 
where b.num_row between 2 and 3;

25、查詢各科成績前三名的記錄（不考慮成績並列情況）

select a.s_id,a.s_name,b.c_id,b.s_score,b.num_row 
from student a 
join (select 
      s_id,
      c_id,
      s_score,row_number() over(partition by c_id order by s_score desc) num_row 
      from score) b 
on a.s_id=b.s_id 
where b.num_row<=3;

26、使用分段[100-85],[85-70],[70-60],[<60]來統計各科成績，分別統計各分數段人數：課程ID和課程名稱

select a.c_id,a.c_name,
(select count(*) from score where score.c_id=a.c_id and s_score<60) '小於60',
(select count(*) from score where score.c_id=a.c_id and s_score between 60 and 70) '60-70',
(select count(*) from score where score.c_id=a.c_id and s_score between 70 and 85) '70-85',
(select count(*) from score where score.c_id=a.c_id and s_score between 85 and 100) '85-100'
from course a 
join score b on a.c_id=b.c_id 
group by c_id;

27、查詢每門課程被選修的學生數

mysql> select c_id,count(c_id) count from score group by c_id;

28、查詢出只有兩門課程的全部學生的學號和姓名

select a.s_id,a.s_name,count(b.s_id) count 
from student a 
join score b on a.s_id=b.s_id 
group by s_id 
having count=2;

29、查詢男生、女生人數

mysql> select count(s_sex) count_sex from student group by s_sex;

30、查詢名字中含有"風"字的學生資訊

mysql> select * from student where s_name like '%周%';

31、查詢1990年出生的學生名單

mysql> select s_id,s_name from student where year(s_birth)=1990;

32、查詢平均成績大於等於85的所有學生的學號、姓名和平均成績

select a.s_id,a.s_name,avg(b.s_score) avg_score 
from student a 
join score b on a.s_id=b.s_id 
group by b.s_id 
having avg_score>=85;

33、查詢每門課程的平均成績，結果按平均成績升序排序，平均成績相同時，按課程號降序排列

select c_id, avg(s_score) avg_score from score group by c_id order by avg_score,c_id desc;

34、查詢課程名稱為"數學"，且分數低於60的學生姓名和分數

select a.c_id,b.c_name,c.s_id,c.s_name,a.s_score
from score a 
join course b on a.c_id=b.c_id 
join student c on a.s_id=c.s_id 
where a.s_score<60 and b.c_name='數學';

35、查詢所有學生的課程及分數情況

select b.s_id,b.s_name,c.c_id,c.c_name,a.s_score 
from score a 
join student b on a.s_id=b.s_id 
join course c on a.c_id=c.c_id ;

36、查詢任何一門課程成績在70分以上的姓名、課程名稱和分數

select c.s_name,b.c_name,a.s_score 
from score a 
join course b on a.c_id=b.c_id 
join student c on a.s_id=c.s_id 
where a.s_score>70;

37、查詢不及格的課程並按課程號從大到小排列

mysql> select distinct c_id from score where s_score<60 order by c_id desc;

38、查詢課程編號為03且課程成績在80分以上的學生的學號和姓名

select a.s_id,a.s_name 
from student a 
join score b on a.s_id=b.s_id 
where b.s_score>80 and b.c_id=03;

39、求每門課程的學生人數

mysql> select c_id, count(*) count from score group by c_id;

40、查詢選修「張三」老師所授課程的學生中成績最高的學生姓名及其成績

select a.s_score,b.s_id,b.s_name 
from score a 
join student b on a.s_id=b.s_id 
join course c on a.c_id=c.c_id 
join teacher d on c.t_id=d.t_id 
where d.t_name='張三' 
order by a.s_score desc 
limit 1;

41、查詢不同課程成績相同的學生的學生編號、課程編號、學生成績 （難）

方法1：利用group by將s_id，c_id，s_score都重複的去除掉

select a.s_id,a.c_id,a.s_score 
from score a 
join score b on a.s_score=b.s_score 
where a.c_id!=b.c_id and a.s_id=b.s_id 
group by a.s_id,a.c_id,a.s_score;

方法2： 利用distinct將s_id，c_id，s_score都重複的去除掉

select distinct a.s_id,a.c_id,a.s_score 
from score a 
join score b on a.s_score=b.s_score 
where a.c_id!=b.c_id and a.s_id=b.s_id ;

42、統計每門課程的學生選修人數（超過5人的課程才統計）。要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列

mysql> select c_id, count(*) count from score group by c_id order by c_id asc;

43、檢索至少選修兩門課程的學生學號

select s_id,count(*) count from score group by s_id having count>=2;

44、查詢選修了全部課程的學生資訊

方法1 ：

select a.s_id,a.s_name,a.s_birth,a.s_sex 
from student a 
join score b on a.s_id=b.s_id 
group by a.s_id,a.s_name,a.s_birth,a.s_sex 
having count(b.s_id) =(select count(*) from course);

方法2 ：

select * from student  a 
-- 查詢score表中s_id=student表中s_id的行數，即統計每個學生的選修課程數量
where (select count(*) from score where s_id = a.s_id)=(select count(*) from course) 

45、查詢各學生的年齡

-- 時間差函數：timestampdiff
select TIMESTAMPDIFF(SECOND,"2020-02-27",NOW())  -- 31680773
select TIMESTAMPDIFF(MINUTE,"2020-02-27",NOW())  -- 528010
select TIMESTAMPDIFF(HOUR,"2020-02-27",NOW())  -- 8800
select TIMESTAMPDIFF(DAY,"2020-02-27",NOW())  -- 366
select TIMESTAMPDIFF(WEEK,"2020-02-27",NOW())  -- 52
select TIMESTAMPDIFF(MONTH,"2020-02-27",NOW())  -- 12
select TIMESTAMPDIFF(QUARTER,"2020-02-27",NOW())  -- 4
select TIMESTAMPDIFF(YEAR,"2020-02-27",NOW())  -- 1

48、查詢兩門以上不及格課程的同學的學號及其平均成績

select s_id,avg(s_score) avg_score 
from score 
where s_score<60 
group by s_id 
having count(*)>=2;

49、查詢本月過生日的學生

mysql> select s_id from student where month(s_birth) = month(now());

50、查詢下一個月過生日的學生

mysql> select s_id from student where month(s_birth) = month(now())+1;

題目參考：https://blog.csdn.net/qiqi123i/article/details/108455130
題目參考：https://zhuanlan.zhihu.com/p/38354000