mysql高階數據庫面試題（mysql高階數據庫面試題答案）

本文目錄一覽：

• 1、「春招系列」MySQL面試核心25問（附答案）
• 2、MySQL面試題（無答案版） 中高級必看
• 3、mysql數據庫面試題（學生表_課程表_成績表_教師表）
• 4、15個 MySQL 基礎面試題，DBA 們準備好了嗎
• 5、【MySQL】20個經典面試題
• 6、sql面試題50題（mysql版）

「春招系列」MySQL面試核心25問（附答案）

篇幅所限本文只寫了MySQL25題，像其他的Redis，SSM框架，算法，計網等技術棧的面試題後面會持續更新，個人整理的1000餘道面試八股文會放在文末給大家白嫖，最近有面試需要刷題的同學可以直接翻到文末領取。

如果表使用自增主鍵，那麼每次插入新的記錄，記錄就會順序添加到當前索引節點的後續位置，當一頁寫滿，就會自動開闢一個新的頁。如果使用非自增主鍵（如果身份證號或學號等），由於每次插入主鍵的值近似於隨機，因此每次新紀錄都要被插到現有索引頁得中間某個位置， 頻繁的移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結構，後續不得不通過OPTIMIZE TABLE（optimize table）來重建表並優化填充頁面。

Server層按順序執行sql的步驟為：

簡單概括：

可以分為服務層和存儲引擎層兩部分，其中：

服務層包括連接器、查詢緩存、分析器、優化器、執行器等 ，涵蓋MySQL的大多數核心服務功能，以及所有的內置函數（如日期、時間、數學和加密函數等），所有跨存儲引擎的功能都在這一層實現，比如存儲過程、觸發器、視圖等。

存儲引擎層負責數據的存儲和提取 。其架構模式是插件式的，支持InnoDB、MyISAM、Memory等多個存儲引擎。現在最常用的存儲引擎是InnoDB，它從MySQL 5.5.5版本開始成為了默認的存儲引擎。

Drop、Delete、Truncate都表示刪除，但是三者有一些差別：

Delete 用來刪除表的全部或者一部分數據行，執行Delete之後，用戶需要提交(commmit)或者回滾(rollback)來執行刪除或者撤銷刪除，會觸發這個表上所有的delete觸發器。

Truncate 刪除表中的所有數據，這個操作不能回滾，也不會觸發這個表上的觸發器，TRUNCATE比Delete更快，佔用的空間更小。

Drop 命令從數據庫中刪除表，所有的數據行，索引和權限也會被刪除，所有的DML觸發器也不會被觸發，這個命令也不能回滾。

因此，在不再需要一張表的時候，用Drop；在想刪除部分數據行時候，用Delete；在保留表而刪除所有數據的時候用Truncate。

隔離級別臟讀不可重複讀幻影讀 READ-UNCOMMITTED 未提交讀 READ-COMMITTED 提交讀 REPEATABLE-READ 重複讀 SERIALIZABLE 可串行化讀

MySQL InnoDB 存儲引擎的默認支持的隔離級別是 REPEATABLE-READ （可重讀）

這裡需要注意的是 ：與 SQL 標準不同的地方在於InnoDB 存儲引擎在 REPEATABLE-READ（可重讀）事務隔離級別 下使用的是 Next-Key Lock 鎖 算法，因此可以避免幻讀的產生，這與其他數據庫系統(如 SQL Server)是不同的。所以 說InnoDB 存儲引擎的默認支持的隔離級別是 REPEATABLE-READ（可重讀） 已經可以完全保證事務的隔離性要 求，即達到了 SQL標準的SERIALIZABLE(可串行化)隔離級別。

因為隔離級別越低，事務請求的鎖越少，所以大部分數據庫系統的隔離級別都是READ-COMMITTED(讀取提交內 容):，但是你要知道的是InnoDB 存儲引擎默認使用 REPEATABLE-READ（可重讀）並不會有任何性能損失 。

InnoDB 存儲引擎在分佈式事務 的情況下一般會用到SERIALIZABLE(可串行化)隔離級別。

主要原因：B+樹只要遍歷葉子節點就可以實現整棵樹的遍歷，而且在數據庫中基於範圍的查詢是非常頻繁的，而B樹只能中序遍歷所有節點，效率太低。

文件與數據庫都是需要較大的存儲，也就是說，它們都不可能全部存儲在內存中，故需要存儲到磁盤上。而所謂索引，則為了數據的快速定位與查找，那麼索引的結構組織要盡量減少查找過程中磁盤I/O的存取次數，因此B+樹相比B樹更為合適。數據庫系統巧妙利用了局部性原理與磁盤預讀原理，將一個節點的大小設為等於一個頁，這樣每個節點只需要一次I/O就可以完全載入，而紅黑樹這種結構，高度明顯要深的多，並且由於邏輯上很近的節點(父子)物理上可能很遠，無法利用局部性。

最重要的是，B+樹還有一個最大的好處：方便掃庫。

B樹必須用中序遍歷的方法按序掃庫，而B+樹直接從葉子結點挨個掃一遍就完了，B+樹支持range-query非常方便，而B樹不支持，這是數據庫選用B+樹的最主要原因。

B+樹查找效率更加穩定，B樹有可能在中間節點找到數據，穩定性不夠。

B+tree的磁盤讀寫代價更低：B+tree的內部結點並沒有指向關鍵字具體信息的指針(紅色部分)，因此其內部結點相對B 樹更小。如果把所有同一內部結點的關鍵字存放在同一塊盤中，那麼盤塊所能容納的關鍵字數量也越多。一次性讀入內存中的需要查找的關鍵字也就越多，相對來說IO讀寫次數也就降低了；

B+tree的查詢效率更加穩定：由於內部結點並不是最終指向文件內容的結點，而只是葉子結點中關鍵字的索引，所以，任何關鍵字的查找必須走一條從根結點到葉子結點的路。所有關鍵字查詢的路徑長度相同，導致每一個數據的查詢效率相當；

視圖是一種虛擬的表，通常是有一個表或者多個表的行或列的子集，具有和物理表相同的功能 游標是對查詢出來的結果集作為一個單元來有效的處理。一般不使用游標，但是需要逐條處理數據的時候，游標顯得十分重要。

而在 MySQL 中，恢復機制是通過回滾日誌（undo log）實現的，所有事務進行的修改都會先記錄到這個回滾日誌中，然後在對數據庫中的對應行進行寫入。當事務已經被提交之後，就無法再次回滾了。

回滾日誌作用：1)能夠在發生錯誤或者用戶執行 ROLLBACK 時提供回滾相關的信息 2) 在整個系統發生崩潰、數據庫進程直接被殺死後，當用戶再次啟動數據庫進程時，還能夠立刻通過查詢回滾日誌將之前未完成的事務進行回滾，這也就需要回滾日誌必須先於數據持久化到磁盤上，是我們需要先寫日誌後寫數據庫的主要原因。

InnoDB

MyISAM

總結

數據庫並發會帶來臟讀、幻讀、丟棄更改、不可重複讀這四個常見問題，其中：

臟讀 ：在第一個修改事務和讀取事務進行的時候，讀取事務讀到的數據為100，這是修改之後的數據，但是之後該事務滿足一致性等特性而做了回滾操作，那麼讀取事務得到的結果就是臟數據了。

幻讀 ：一般是T1在某個範圍內進行修改操作（增加或者刪除），而T2讀取該範圍導致讀到的數據是修改之間的了，強調範圍。

丟棄修改 ：兩個寫事務T1 T2同時對A=0進行遞增操作，結果T2覆蓋T1，導致最終結果是1 而不是2，事務被覆蓋

不可重複讀 ：T2 讀取一個數據，然後T1 對該數據做了修改。如果 T2 再次讀取這個數據，此時讀取的結果和第一次讀取的結果不同。

第一個事務首先讀取var變量為50，接着準備更新為100的時，並未提交，第二個事務已經讀取var為100，此時第一個事務做了回滾。最終第二個事務讀取的var和數據庫的var不一樣。

T1 讀取某個範圍的數據，T2 在這個範圍內插入新的數據，T1 再次讀取這個範圍的數據，此時讀取的結果和和第一次讀取的結果不同。

T1 和 T2 兩個事務都對一個數據進行修改，T1 先修改，T2 隨後修改，T2 的修改覆蓋了 T1 的修改。例如：事務1讀取某表中的數據A=50，事務2也讀取A=50，事務1修改A=A+50，事務2也修改A=A+50，最終結果A=100，事務1的修改被丟失。

T2 讀取一個數據，T1 對該數據做了修改。如果 T2 再次讀取這個數據，此時讀取的結果和第一次讀取的結果不同。

悲觀鎖，先獲取鎖，再進行業務操作，一般就是利用類似 SELECT … FOR UPDATE 這樣的語句，對數據加鎖，避免其他事務意外修改數據。當數據庫執行SELECT … FOR UPDATE時會獲取被select中的數據行的行鎖，select for update獲取的行鎖會在當前事務結束時自動釋放，因此必須在事務中使用。

樂觀鎖，先進行業務操作，只在最後實際更新數據時進行檢查數據是否被更新過。Java 並發包中的 AtomicFieldUpdater 類似，也是利用 CAS 機制，並不會對數據加鎖，而是通過對比數據的時間戳或者版本號，來實現樂觀鎖需要的版本判斷。

分庫與分表的目的在於，減小數據庫的單庫單表負擔，提高查詢性能，縮短查詢時間。

通過分表 ，可以減少數據庫的單表負擔，將壓力分散到不同的表上，同時因為不同的表上的數據量少了，起到提高查詢性能，縮短查詢時間的作用，此外，可以很大的緩解表鎖的問題。分表策略可以歸納為垂直拆分和水平拆分:

水平分表 ：取模分表就屬於隨機分表，而時間維度分表則屬於連續分表。如何設計好垂直拆分，我的建議：將不常用的字段單獨拆分到另外一張擴展表. 將大文本的字段單獨拆分到另外一張擴展表, 將不經常修改的字段放在同一張表中，將經常改變的字段放在另一張表中。對於海量用戶場景，可以考慮取模分表，數據相對比較均勻，不容易出現熱點和並發訪問的瓶頸。

庫內分表 ，僅僅是解決了單表數據過大的問題，但並沒有把單表的數據分散到不同的物理機上，因此並不能減輕 MySQL 服務器的壓力，仍然存在同一個物理機上的資源競爭和瓶頸，包括 CPU、內存、磁盤 IO、網絡帶寬等。

分庫與分錶帶來的分佈式困境與應對之策 數據遷移與擴容問題—-一般做法是通過程序先讀出數據，然後按照指定的分表策略再將數據寫入到各個分表中。分頁與排序問題—-需要在不同的分表中將數據進行排序並返回，並將不同分表返回的結果集進行匯總和再次排序，最後再返回給用戶。

不可重複讀的重點是修改，幻讀的重點在於新增或者刪除。

視圖是虛擬的表，與包含數據的表不一樣，視圖只包含使用時動態檢索數據的查詢；不包含任何列或數據。使用視圖可以簡化複雜的 sql 操作，隱藏具體的細節，保護數據；視圖創建後，可以使用與表相同的方式利用它們。

視圖不能被索引，也不能有關聯的觸發器或默認值，如果視圖本身內有order by 則對視圖再次order by將被覆蓋。

創建視圖：create view xxx as xxxx

對於某些視圖比如未使用聯結子查詢分組聚集函數Distinct Union等，是可以對其更新的，對視圖的更新將對基表進行更新；但是視圖主要用於簡化檢索，保護數據，並不用於更新，而且大部分視圖都不可以更新。

B+tree的磁盤讀寫代價更低，B+tree的查詢效率更加穩定 數據庫索引採用B+樹而不是B樹的主要原因：B+樹只要遍歷葉子節點就可以實現整棵樹的遍歷，而且在數據庫中基於範圍的查詢是非常頻繁的，而B樹只能中序遍歷所有節點，效率太低。

B+樹的特點

在最頻繁使用的、用以縮小查詢範圍的字段,需要排序的字段上建立索引。不宜：1）對於查詢中很少涉及的列或者重複值比較多的列 2）對於一些特殊的數據類型，不宜建立索引，比如文本字段（text）等。

如果一個索引包含（或者說覆蓋）所有需要查詢的字段的值，我們就稱 之為「覆蓋索引」。

我們知道在InnoDB存儲引 擎中，如果不是主鍵索引，葉子節點存儲的是主鍵+列值。最終還是要「回表」，也就是要通過主鍵再查找一次,這樣就 會比較慢。覆蓋索引就是把要查詢出的列和索引是對應的，不做回表操作！

舉例 ：

學號姓名性別年齡系別專業 20020612李輝男20計算機軟件開發 20060613張明男18計算機軟件開發 20060614王小玉女19物理力學 20060615李淑華女17生物動物學 20060616趙靜男21化學食品化學 20060617趙靜女20生物植物學

主鍵為候選鍵的子集，候選鍵為超鍵的子集，而外鍵的確定是相對於主鍵的。

MySQL面試題（無答案版） 中高級必看

1、mysql記錄存儲：mysql的數據是怎麼組織的

2、頁內記錄的維護（順序保證/插入策略/頁內查詢）

3、MySQL內存管理（頁面管理、頁面淘汰、LRU）:全表掃描對內存有什麼影響? 如何避免熱數據被淘汰? 沒有空閑頁怎麼辦？

4、InnoDB 加鎖的過程是如何實現的？常見鎖問題有那些？

5、MVCC是什麼？如何實現多版本控制？如何解決寫衝突？

6、回滾日誌Undo log如何實現多版本控制與保證事務的原子性？

7、undo log如何清理，為何InnoDB select count(*)  這麼慢？

8、重做日誌Redo log如何實現事務持久性？

9、InnoDB行級鎖、間隙鎖、表級鎖如何實現的？

10、InnoDB加鎖過程如何實現的？

11、海量數據下 主鍵如何設計？

12、聚集索引、二級索引與聯合索引具備哪些特點？

13、在進行索引優化時應該注意哪些問題/

14、MySQL如何進行庫表的優雅設計？

15、如何實現數據備份之延時庫部署

16、MySQL如何高效實現數據冗餘部署

17、MySQL高可用方案有哪些

mysql數據庫面試題（學生表_課程表_成績表_教師表）

Student(Sid,Sname,Sage,Ssex)學生表

Sid：學號

Sname：學生姓名

Sage：學生年齡

Ssex：學生性別

Course(Cid,Cname,Tid)課程表

Cid：課程編號

Cname：課程名稱

Tid：教師編號

SC(Sid,Cid,score)成績表

Sid：學號

Cid：課程編號

score：成績

Teacher(Tid,Tname)教師表

Tid：教師編號：

Tname：教師名字

1、插入數據

2、刪除課程表所有數據

3、將學生表中的姓名 張三修改為張大山

或者

4、查詢姓』李』的老師的個數：

5、查詢所有課程成績小於60的同學的學號、姓名：

6、查詢沒有學全所有課的同學的學號、姓名

7、查詢平均成績大於60分的同學的學號和平均成績

8、查詢學過「100」並且也學過編號「101」課程的同學的學號、姓名

9、查詢「100」課程比「101」課程成績高的所有學生的學號

10、查詢課程編號「100」的成績比課程編號「101」課程高的所有同學的學號、姓名

11、查詢學過「魯迅」老師所教的所有課的同學的學號、姓名

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

13、查詢至少有一門課與學號為「1」同學所學相同的同學的學號和姓名

14、把「SC」表中「魯迅」老師教的課的成績都更改為此課程的平均成績，

錯誤

15、查詢和「2」學號的同學學習的課程完全相同的其他同學學號和姓名

16、刪除學習「魯迅」老師課的SC表記錄

17、向SC表中插入一些記錄，這些記錄要求符合以下條件：沒有上過編號「003」課程的同學學號、002號課的平均成績

18、查詢各科成績最高和最低的分：以如下的形式顯示：課程ID，最高分，最低分

19、按各科平均成績從低到高和及格率的百分數從高到低順序

20、查詢如下課程平均成績和及格率的百分數(用」1行」顯示): 數學（100），語文（101），英語（102）

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

23、查詢如下課程成績第3名到第6名的學生成績單：數學（100），語文（101），英語（102）

23、統計下列各科成績，各分數段人數：課程ID，課程名稱，[100-85],[85-70],[70-60],[ 小於60]

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

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

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

27、查詢出只選修一門課程的全部學生的學號和姓名

28、查詢男生、女生人數

29、查詢姓「張」的學生名單

30、查詢同名同姓的學生名單，並統計同名人數

31、1981年出生的學生名單（註：student表中sage列的類型是datetime）

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

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

34、查詢課程名稱為「英語」，且分數低於60的學生名字和分數

35、查詢所有學生的選課情況

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

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

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

39、求選了課程的學生人數：

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

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

42、查詢全部學生都選修的課程的課程號和課程名(1.一個課程被全部的學生選修，2.所有的學生選擇的所有課程)

43、查詢沒學過「魯迅」老師講授的任一門課程的學生姓名

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

45、檢索「101」課程分數小於60，按分數降序排列的同學學號

46、刪除「2」同學的「101」課程的成績

15個 MySQL 基礎面試題，DBA 們準備好了嗎

高級操作 1.Mysql 主從同步配置 2.Mysql 雙master 配置 3.Mysql 雙master+叢庫+keepalived 配置高可用數據庫 客戶端 作為DBA，一定不要用各種GUI工具，mysql自帶命令行client才是你的最佳武器 可選項 1.mysql 分區配置

【MySQL】20個經典面試題

原文鏈接：

基本原理流程，3個線程以及之間的關聯；

(1)、varchar與char的區別

(2)、varchar(50)中50的涵義

(3)、int（20）中20的涵義

(4)、mysql為什麼這麼設計

2.InnoDB支持行級鎖，而MyISAM支持表級鎖

3.InnoDB支持MVCC, 而MyISAM不支持

4.InnoDB支持外鍵，而MyISAM不支持

5.InnoDB不支持全文索引，而MyISAM支持。

(2)、innodb引擎的4大特性

插入緩衝（insert buffer),二次寫(double write),自適應哈希索引(ahi),預讀(read ahead)

(3)、2者selectcount(*)哪個更快，為什麼 myisam更快，因為myisam內部維護了一個計數器，可以直接調取。

(1)、您是選擇拆成子表，還是繼續放一起；

(2)、寫出您這樣選擇的理由。

開放性問題：據說是騰訊的

sql面試題50題（mysql版）

–插入學生表測試數據

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″課程成績高的學生的信息及課程分數

select c.*,a.s_score as 01課程score,b.s_score as 02課程score from

score a,score b

left join student c

on b.s_id = c.s_id

where a.s_id = b.s_id and a.c_id = ’01’ and b.c_id = ’02’ and a.s_score b.s_score;

— 2、查詢”01″課程比”02″課程成績低的學生的信息及課程分數

select a.* ,b.s_score as 01課程,c.s_score as 02課程 from student a

join score b

on a.s_id=b.s_id and b.c_id = ’01’

left join score c

on b.s_id = c.s_id and c.c_id = ’02’

where b.s_score c.s_score ;

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

select a.s_id,a.s_name,round(avg(b.s_score),2) as 平均成績 from student a

join score b

on a.s_id = b.s_id

group by b.s_id having 平均成績 = 60;

備註：round[avg(成績),1]里,round是四捨五入函數，1代表保留1位小數

— 4、查詢平均成績小於60分的同學的學生編號和學生姓名和平均成績

— (包括有成績的和無成績的)

select b. ,round(avg(a.s_score),2) as 平均成績 from

student b

left join score a on b.s_id = a.s_id group by a.s_id having 平均成績 60

union

select b. ,0 as 平衡成績 from student b where b.s_id not in (select s_id from score);

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

select a.s_id,a.s_name,count(b.c_id) as 選課總數 ,sum(b.s_score) as 總分 from student a

left join score b

on a.s_id = b.s_id group by s_id ;

— 6、查詢”李”姓老師的數量

select count(*) as 李姓老師數量 from teacher where t_name like ‘李%’;

— 7、查詢學過”張三”老師授課的同學的信息

select a.* from student a join score b

on a.s_id = b.s_id

where b.c_id in (select c.c_id from course c

join teacher d on c.t_id = d.t_id where d.t_name = ‘張三’);

— 8、查詢沒學過”張三”老師授課的同學的信息

select a.* from student a left join score b on a.s_id = b.s_id where a.s_id not in

(select s_id from score where c_id =

(select c_id from course where t_id =

(select t_id from teacher where t_name = ‘張

三’))) group by a.s_id;

— 9、查詢學過編號為”01″並且也學過編號為”02″的課程的同學的信息

select * from student where s_id in

(select a.s_id from score a join score b on a.s_id = b.s_id

where a.c_id = ’01’ and b.c_id = ’02’);

— 10、查詢學過編號為”01″但是沒有學過編號為”02″的課程的同學的信息

select * from student where s_id in

(select s_id from score where c_id = ’01’ )

and s_id not in (select s_id from score where c_id = ’02’ );

— 11、查詢沒有學全所有課程的同學的信息

select * from student where s_id not in

(select s_id from score group by s_id having count(c_id) = 3);

— 12、查詢至少有一門課與學號為”01″的同學所學相同的同學的信息

select distinct a.* from student a left 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’ ;

注意：distinct是去重的

— 13、查詢和”01″號的同學學習的課程完全相同的其他同學的信息

select * from student where s_id in

(select s_id from score group by s_id having count(c_id) =

(select count(c_id) from score where s_id = ’01’) and s_id not in

(select s_id from score where c_id not in

(select c_id from score where s_id = ’01’)) and s_id != ’01’);

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

select s_name from student where s_id not in

(select s_id from score where c_id in

(select c_id from course where t_id in

(select t_id from teacher where t_name =’張三’)));

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

select a.s_id ,b.s_name,round(avg(a.s_score),2) as 平均成績 from score a

left join student b on a.s_id = b.s_id

where s_score 60 group by s_id having count(1) =2;

或者試試

select a.s_id ,b.s_name,round(avg(a.s_score),2) as 平均成績 from score a

left join student b on a.s_id = b.s_id

where a.s_score 60 group by a.s_id having count(*) =2;

— 16、檢索”01″課程分數小於60，按分數降序排列的學生信息

select a.* ,b.c_id ,b.s_score from student a

left join score b on a.s_id = b.s_id

where b.c_id = ’01’ and b.s_score 60

order by b.s_score desc;

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

select a.s_name ,

sum(case when b.c_id = ’01’ then s_score else null end ) as 語文,

sum(case when b.c_id = ’02’ then s_score else null end ) as 數學,

sum(case when b.c_id = ’03’ then s_score else null end ) as 英語,

round(avg(s_score),2) as 平均成績

from student a left join score b on a.s_id = b.s_id group by a.s_name

order by 平均成績 desc;

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

–及格為=60，中等為：70-80，優良為：80-90，優秀為：=90

select b.c_id,b.c_name,

max(a.s_score) as 最高分,

min(a.s_score) as 最低分,

round(avg(a.s_score),2) as 平均分,

round(sum(case when a.s_score= 60 then 1 else 0 end)/count(s_id),2) as 及格率 ,

round(sum(case when a.s_score= 70 and a.s_score 80 then 1 else 0 end)/count(s_id),2) as 中等率,

round(sum(case when a.s_score= 80 and a.s_score 90 then 1 else 0 end)/count(s_id),2) as 優良率,

round(sum(case when a.s_score= 90 then 1 else 0 end)/count(s_id),2) as 優秀率

from score a

left join course b

on a.c_id = b.c_id group by b.c_id;

— 19、按各科成績進行排序，並顯示排名

第一種：

set @pre_c_id:= ’01’;

set @rank:=0;

select tb2.s_id ,tb2.c_id,tb2.s_score,tb2.排名 from

(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,

(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id

from

(select * from score order by c_id,s_score desc) tb1 )tb2;

如果看不懂用第二種方法：

SELECT a.c_id,a.s_id,a.s_score,COUNT(b.s_score)+1 AS 排名

FROM score a LEFT JOIN score b ON a.s_scoreb.s_score AND a.c_id = b.c_id

GROUP BY a.c_id,a.s_id,a.s_score ORDER BY a.c_id,排名,a.s_id ASC

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

set @rank:=0;

select * ,(@rank:=@rank+1) as rank from

(select s_id ,sum(s_score) as 總成績 from score

group by s_id order by 總成績 desc) tb1;

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

select a.c_id, d.t_name,round(avg(a.s_score)) as 平均分 from score a

left join student b on a.s_id = b.s_id

left join course c on a.c_id = c.c_id

left join teacher d on c.t_id = d.t_id group by a.c_id

order by 平均分 desc;

— 22、查詢所有課程的成績第2名到第3名的學生信息及該課程成績

set @pre_c_id:= ’01’;

set @rank:=0;

select b.s_name,tb2.s_id ,tb2.c_id,tb2.s_score,tb2.排名 from

(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,

(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id

from

(select * from score order by c_id,s_score desc) tb1 )tb2 join student b on tb2.s_id = b.s_id where 排名 = 2 or 排名 =3;

— 23、統計各科成績各分數段人數：課程編號,課程名稱,[100-85],(85-70],(70-60],(0-60]及所佔百分比

select b.c_id,b.c_name ,

sum(case when a.s_score =85 then 1 else 0 end) as 100-85 ,

concat(round(100 sum(case when a.s_score =85 then 1 else 0 end)/count( ),2), ‘%’) as 百分比,

sum(case when a.s_score 85 and a.s_score =70 then 1 else 0 end) as 85-70 ,

concat(round(100 sum(case when a.s_score 85 and a.s_score =70 then 1 else 0 end)/count( ),2),’%’) as 百分比,

sum(case when a.s_score 70 and a.s_score =60 then 1 else 0 end) as 70-60 ,

concat(round(100 sum(case when a.s_score 70 and a.s_score =60 then 1 else 0 end)/count( ),2) ,’%’)as 百分比,

sum(case when a.s_score 60 and a.s_score =0 then 1 else 0 end) as 60-0 ,

concat(round(100 sum(case when a.s_score 60 and a.s_score =0 then 1

else 0 end)/count( ),2),’%’) as 百分比

from score a left join course b on a.c_id = b.c_id group by b.c_id;

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

select tb1.*,(@rank:=@rank +1 ) as rank from

(select s_id ,round(avg(s_score),2) as 平均成績 from score

group by s_id order by 平均成績 desc) tb1,(select @rank:=0) b;

— 25、查詢各科成績前三名的記錄

set @pre_c_id:= ’01’;

set @rank:=0;

select b.s_name,tb2.s_id ,tb2.c_id,tb2.s_score,tb2.排名 from

(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,

(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id

from

(select * from score order by c_id,s_score desc) tb1 )tb2 join student b on tb2.s_id = b.s_id where 排名 4;

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

select c_id ,count(s_id) as 選修人數 from score group by c_id;

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

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

— 28、查詢男生、女生人數

select sum(case s_sex when ‘男’ then 1 else 0 end) as 男生人數,

sum(case s_sex when ‘女’ then 1 else 0 end) as 女生人數 from student;

— 29、查詢名字中含有”風”字的學生信息

select * from student where s_name like ‘%風%’;

— 30、查詢同名同性學生名單，並統計同名人數

–略，不想寫

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

select * from student where s_birth like ‘1990%’;

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

select c_id ,round(avg(s_score),2) as 平均成績 from score group by c_id order by 平均成績 desc, c_id asc;

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

select a.s_id,b.s_name ,round(avg(s_score),2) as 平均成績 from score a

left join student b on a.s_id = b.s_id group by a.s_id having 平均成績=85;

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

select b.s_name ,a.s_score from score a

left join student b on a.s_id = b.s_id

where a.c_id=(select c_id from course where c_name = ‘數學’)and a.s_score 60;

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

select b.s_name,

sum(case when a.c_id = ’01’ then a.s_score else null end) as 語文,

sum(case when a.c_id = ’02’ then a.s_score else null end) as 數學,

sum(case when a.c_id = ’03’ then a.s_score else null end) as 英語

from score a right join student b on a.s_id = b.s_id group by b.s_name

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

select b.s_name,

sum(case when a.c_id = ’01’ then a.s_score else null end) as 語文,

sum(case when a.c_id = ’02’ then a.s_score else null end) as 數學,

sum(case when a.c_id = ’03’ then a.s_score else null end) as 英語

from score a right join student b on a.s_id = b.s_id group by b.s_name having 語文= 70 or 數學= 70 or 英語= 70 ;

— 37、查詢不及格的課程

select a.s_id,a.c_id,b.c_name,a.s_score from score a

left join course b on a.c_id = b.c_id where a.s_score60;

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

select a.s_id,b.s_name from score a left join student b on a.s_id = b.s_id where a.c_id = ’01’ and a.s_score=80;

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

select c_id,count(*) as 學生人數 from score group by c_id ;

— 40、查詢選修”張三”老師所授課程的學生中，成績最高的學生信息及其成績

select a.*,b.c_id,max(b.s_score) as 最高成績 from student a

right join score b on a.s_id = b.s_id

group by b.c_id

having b.c_id = (select c_id from course

where t_id = (select t_id from teacher where t_name = ‘張三’));

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

–（這題我搞不清題目是什麼意思,是指查找學生個體參加了的所有課程的成績各不相同的那個學生信息呢？還是所有課程之間做對比呢，我更傾向於理解為前者）

–理解為前者的寫法

select * from

(select * from score group by s_id,s_score) tb1

group by s_id having count(*) = 1;

–理解為後者的寫法

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

— 42、查詢每門課程成績最好的前兩名

set @pre_c_id:= ’01’;

set @rank:=0;

select tb2.s_id ,tb2.c_id,tb2.s_score from

(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,

(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id

from

(select * from score order by c_id,s_score desc) tb1 )tb2

join student b on tb2.s_id = b.s_id where 排名 3;

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

select c_id ,count(*) as 選修人數 from score group by c_id having 選修人數5 order by 選修人數 desc , c_id asc;

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

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

— 45、查詢選修了全部課程的學生信息

select * from student where s_id in

(select s_id from score group by s_id having count(*) = 3)

–46、查詢各學生的年齡

select s_name ,(date_format(now(),’%Y’)-date_format(s_birth,’%Y’) + (CASE when date_format(now(),’%m%d’)=date_format(s_birth,’%m%d’) then 0 else 1 end)) as age

from student

— 47、查詢本周過生日的學生

—(實現得並不完全，因為例如出生月日為『01-01』在每一年可能會輸入不同周）

select * from student where week(date_format(s_birth,’%m%d’))=week(date_format(now(),’%m%d’)) ;

— 48、查詢下周過生日的學生

select * from student

where week(date_format(s_birth,’%m%d’))=week(date_format(date_add(now(),interval 7-dayofweek(now())+1 day),’%m%d’));

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

select * from student where date_format(s_birth,’%m’) = date_format(now(),’%m’)

— 50、查詢下月過生日的學生

select * from student where date_format(s_birth,’%m’) = date_format(date_add(now(),interval 1 month),’%m’)