Open in app

Sign in

Write

Sign in

超經典MySQL練習50題，做完這些你的SQL就過關了

數據分析那些事
18 min readDec 24, 2021

相信大多學習了 Mysql 資料庫語言的同學都會上網找練習來練手，而大部分的人肯定知道有一篇 Mysql 經典練習題50題的帖子，上面的題目基本上涵蓋了 Mysql 查詢語句的關鍵知識點。

筆者近期對又將這 50 題進行了練習，同時整理了相關的參考答案，有自己的思路和方法，也有參考大神們的。不得不說，這50題對SQL的提升真的很有幫助！

筆者使用的 MySQL 版本 是 MySQL 5.7.28 。

鑑於 50 題篇幅太長，本文只展示了其中10題及筆者的思考，50 題完整版練習題以及筆者的答案實踐已整理在pdf檔案中，共有100多頁，在文末提供獲取的方法。

建表和插入資料

在開始之前，先建立本文所需要的資料表格：

-- 建表
-- 學生表
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);

題目1

題目要求

查詢”01"課程比”02"課程成績高的學生的資訊及課程分數

SQL實現

-- 方法1
select 
	a.*
	,b.s_score as 1_score  
	,c.s_score as 2_score
from Student a
join Score b on a.s_id = b.s_id  and b.c_id = '01'   -- 方法1兩個表透過學號連線，指定01
left join Score c on a.s_id = c.s_id and c.c_id='02' or c.c_id is NULL -- 指定02，或者c中的c_id直接不存在
-- 為NULL的條件可以不存在，因為左連線中會直接排除c表中不存在的資料，包含NULL
where b.s_score > c.s_score;   -- 判斷條件


-- 方法2：直接使用where語句
select 
	a.*
	,b.s_score as 1_score
	,c.s_score as 2_score
from Student a, Score b, Score c
where a.s_id=b.s_id   -- 列出全部的條件
and a.s_id=c.s_id
and b.c_id='01'
and c.c_id='02'
and b.s_score > c.s_score;   -- 前者成績高

第二種方法實現：

題目2

題目要求

查詢”01"課程比”02"課程成績低的學生的資訊及課程分數（題目1是成績高）

SQL實現

類比題目1的實現過程

-- 方法1：透過連線方式實現
select 
	a.*
	,b.s_score as 1_score
	,c.s_score as 2_score 
from Student a
left join Score b on a.s_id=b.s_id and b.c_id='01' or b.c_id=NULL   -- 包含NULL的資料
join score c on a.s_id=c.s_id and c.c_id='02' 
where b.s_score < c.s_score;

-- 透過where子句實現
select 
	a.*
	,b.s_score as 1_score
	,c.s_score as 2_score
from Student a, Score b, Score c
where a.s_id=b.s_id
and a.s_id=c.s_id
and b.c_id='01'
and c.c_id='02'
and b.s_score < c.s_score;   -- 前者比較小

題目3

題目需求

查詢平均成績大於等於60分的同學的學生編號和學生姓名和平均成績

SQL實現

-- 執行順序：先執行分組，再執行avg平均操作
select 
	b.s_id
	,b.s_name
	,round(avg(a.s_score), 2) as avg_score
from Student b
join Score a
on b.s_id = a.s_id
group by b.s_id   -- 分組之後查詢每個人的平均成績
having avg_score >= 60;

-- 附加題：總分超過200分的同學
select 
	b.s_id
	,b.s_name
	,round(sum(a.s_score),2) as sum_score    -- sum求和
from Student b 
join Score a 
on b.s_id=a.s_id 
group by b.s_id 
having sum_score > 200;

附加題：總分超過200分的同學

題目4

題目要求

查詢平均成績小於60分的同學的學生編號和學生姓名和平均成績（包括有成績的和無成績的）

SQL實現1-兩種情況連線

平均分小於60

select 
	b.s_id
	,b.s_name
	,round(avg(a.s_score), 2) as avg_score   -- round四捨五入函式
from Student b
join Score a
on b.s_id = a.s_id
group by b.s_id   -- 分組之後查詢每個人的平均成績
having avg_score < 60;

結果為：

沒有成績的同學：

select 
	a.s_id
	,a.s_name
	,0 as avg_score
from Student a
where a.s_id not in (  -- 學生的學號不在給給定表的學號中
  select distinct s_id -- 查詢出全部的學號
  from Score   
);

最後將兩個部分的結果連起來即可：透過union方法

SQL實現2-ifnull函式判斷

使用ifnull函式

select 
	S.s_id
	,S.s_name
	,round(avg(ifnull(C.s_score,0)), 2) as avg_score   -- ifnull 函数：第一个参数存在则取它本身，不存在取第二个值0
from Student S
left join Score C
on S.s_id = C.s_id
group by s_id
having avg_score < 60;

使用null判斷

select 
	a.s_id
	,a.s_name
	,ROUND(AVG(b.s_score), 2) as avg_score 
from Student a 
left join Score b on a.s_id = b.s_id 
GROUP BY a.s_id 
HAVING avg_score < 60 or avg_score is null;   -- 最後的NULL判斷

題目5

題目需求

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

SQL實現

select 
 a.s_id
 ,a.s_name
 ,count(b.c_id) as course_number   -- 課程個數
 ,sum(b.s_score) as scores_sum  -- 成績總和
from Student a
left join Score b
on a.s_id = b.s_id
group by a.s_id,a.s_name;

題目6

題目需求

查詢“李”姓老師的數量

SQL實現

select count(t_name) from Teacher where t_name like "李%";   -- 萬用字元

這題怕是最簡單的吧😭

題目7

題目需求

查詢學過張三老師授課的同學的資訊

SQL實現

-- 方法1：透過張三老師的課程的學生來查詢；自己的方法
select * -- 3. 透過學號找出全部學生資訊
from Student
where s_id in (
  select s_id    -- 2.透過課程找出對應的學號
  from Score  S
  join Course C
  on S.c_id = C.c_id  -- 課程表和成績表
  where C.t_id=(select t_id from Teacher where t_name="張三")  -- 1.查詢張三老師的課程
);

-- 方法2：透過張三老師的課程來查詢
select s1.* 
from Student s1
join Score s2 
on s1.s_id=s2.s_id 
where s2.c_id in (
  select c_id from Course c where t_id=(  -- 1. 透過老師找出其對應的課程
    select t_id from Teacher t where t_name="張三"
  )
)

-- 方法3
select s.* from Teacher t
left join Course c on t.t_id=c.t_id  -- 教師表和課程表
left join Score sc on c.c_id=sc.c_id  -- 課程表和成績表
left join Student s on s.s_id=sc.s_id  -- 成績表和學生資訊表
where t.t_name='張三';

自己的方法：

方法2來實現：

方法3實現：

題目8

題目需求

找出沒有學過張三老師課程的學生

SQL實現

select * -- 3. 通过学号找出全部学生信息
from Student
where s_id not in (  -- 2.通过学号取反：学号不在张三老师授课的学生的学号中
  select s_id    
  from Score  S
  join Course C
  on S.c_id = C.c_id
  where C.t_id=(select t_id from Teacher where t_name ="张三")  -- 1.查询张三老师的课程
);

-- 方法2：
select * 
from Student s1
where s1.s_id not in (
  select s2.s_id from Student s2 join Score s3 on s2.s_id=s3.s_id where s3.c_id in(
    select c.c_id from Course c join Teacher t on c.t_id=t.t_id where t_name="張三" 
  )
);

-- 方法3
select s1.* 
from Student s1
join Score s2 
on s1.s_id=s2.s_id 
where s2.c_id not in (
  select c_id from Course c where t_id=(  -- 1. 透過老師找出其對應的課程
    select t_id from Teacher t where t_name="張三"
  )
);

方法2：

題目9

題目需求

查詢學過編號為01，並且學過編號為02課程的學生資訊

SQL實現

-- 自己的方法：透過自連線實現
select s1.*
from Student s1
where s_id in (
  select s2.s_id from Score s2
  join Score s3
  on s2.s_id=s3.s_id
  where s2.c_id='01' and s3.c_id='02'
);

-- 方法2：直接透過where語句實現
select s1.*
from Student s1, Score s2, Score s3
where s1.s_id=s2.s_id 
and s1.s_id=s3.s_id
and s2.c_id=01 and s3.c_id=02;

-- 方法3：兩個子查詢
-- 1. 先查出學號
select sc1.s_id
from (select * from Score s1 where s1.c_id='01') sc1,
			(select * from Score s1 where s1.c_id='02') sc2
where sc1.s_id=sc2.s_id;

-- 2.找出學生資訊
select * 
from Student 
where s_id in (select sc1.s_id   -- 指定學號是符合要求的
               from (select * from Score s1 where s1.c_id='01') sc1,
               (select * from Score s1 where s1.c_id='02') sc2
where sc1.s_id=sc2.s_id);

先從Score表中看看哪些人是滿足要求的：01–05同學是滿足的

透過自連線查詢的語句如下：

查詢出學號後再匹配出學生資訊：

透過where語句實現：

方法3的實現：

題目10

題目需求

查詢學過01課程，但是沒有學過02課程的學生資訊（注意和上面👆題目的區別）

SQL實現

首先看看哪些同學是滿足要求的：只有06號同學是滿足的

錯誤思路1

直接將上面一題的結果全部排出，導致那些沒有學過01課程的學生也出現了：07，08

select s1.*
from Student s1
where s_id not in (   -- 直接將上面一題的結果全部排出，導致那些沒有學過01課程的學生也出現了：07，08
  select s2.s_id from Score s2
  join Score s3
  on s2.s_id=s3.s_id
  where s2.c_id='01' and s3.c_id ='02'
);

錯誤思路2

將上面題目中的02課程直接取反，導致同時修過01，02，03或者只修01，03的同學也會出現

select s1.*
from Student s1
where s_id in (
  select s2.s_id from Score s2
  join Score s3
  on s2.s_id=s3.s_id
  where s2.c_id='01' and s3.c_id !='02'   -- 直接取反是不行的，因為修改(01,02,03)的同學也會出現
);

正確思路：https://www.jianshu.com/p/9abffdd334fa

-- 方法1：根據兩種修課情況來判斷

select s1.* 
from Student s1
where s1.s_id in (select s_id from Score where c_id='01')   -- 修過01課程，要保留
and s1.s_id not in (select s_id from Score where c_id='02');  -- 哪些人修過02，需要排除

！！！！！方法2：先把06號學生找出來

select * from Student where s_id in (
  select s_id 
  from Score 
  where c_id='01'   -- 修過01課程的學號
  and s_id not in (select s_id   -- 同時學號不能在修過02課程中出現
                   from Score 
                   where c_id='02')
);

鑑於篇幅，本文只展示了50題中的10道題的答案以及筆者的實踐心得。為方便大家練習，各位也可以關注我的Facebook，評論區回覆 “SQL50”獲取完整的MySQL經典50題及筆者實踐的答案。

立即試用FineBI免費版：

https://intl.finebi.com/zh-tw/trial?utm_source=Medium_Banner

文章推薦

◆SQL零基礎入門必知必會！

◆4 款 MySQL 調優工具，公司大神都在用！

◆零基礎 SQL 資料庫小白，從入門到精通的學習路線與書單

MySQL
經典習題
資料分析

--

--

數據分析那些事

Written by 數據分析那些事

13.2K Followers

這是一個專注於數據分析職場的內容部落格，聚焦一批數據分析愛好者，在這裡，我會分享數據分析相關知識點推送、（工具/書籍）等推薦、職場心得、熱點資訊剖析以及資源大盤點，希望同樣熱愛數據的我們一同進步！ 臉書會有更多互動喔：https://www.facebook.com/shujvfenxi/

Help

Status

About

Careers

Blog

Privacy

Terms

Text to speech

Teams