
## 目錄
[TOC]
## mysql認知

## 三大范式是什么?
* 第一范式:數據表中的每一列(每個字段)必須是不可拆分的最小單元,也就是確保每一列的原子性;
* 第二范式(2NF):滿足1NF后,要求表中的所有列,都必須依賴于主鍵,而不能有任何一列與主鍵沒有關系,也就是說一個表只描述一件事情;
* 第三范式:必須先滿足第二范式(2NF),要求:表中的每一列只與主鍵直接相關而不是間接相關,(表中的每一列只能依賴于主鍵);
## 怎么區分三大范式?
? 第?一范式和第二范式在于有沒有分出兩張表,第二范式是說一張表中包含了所種不同的實體屬性,那么要必須分成多張表, 第三范式是要求已經分成了多張表,那么一張表中只能有另一張表中的id(主鍵),而不能有其他的任何信息(其他的信息一律用主鍵在另一表查詢)。
## 數據庫五大約束是什么?
1.primary KEY:設置主鍵約束;
2.UNIQUE:設置唯一性約束,不能有重復值;
3.DEFAULT 默認值約束,height DOUBLE(3,2)DEFAULT 1.2 height不輸入是默認為1,2
4.NOT NULL:設置非空約束,該字段不能為空;
5.FOREIGN key :設置外鍵約束。
## 主鍵是什么,怎么設置主鍵?
主鍵默認非空,默認唯一性約束,只有主鍵才能設置自動增長,自動增長一定是主鍵,主鍵不一定自動增長;
在定義列時設置:ID INT PRIMARY KEY
在列定義完之后設置:primary KEY(id)
## 數據庫的外鍵是什么?
只有INNODB的數據庫引擎支持外鍵。
不見已使用基于mysql的物理外鍵,這樣可能會有超出預期的后果。推薦使用邏輯外鍵,就是自己做表設計,根據代碼邏輯設定的外鍵,自行實現相關的數據操作。
## innodb和myisam有什么區別?
* InnoDB支持事務,而MyISAM不支持事物,崩潰后無法安全恢復,表鎖非常影響性能
* InnoDB支持行級鎖,而MyISAM支持表級鎖
* InnoDB支持MVCC,實現了四個標準的隔離級別 而MyISAM不支持
* InnoDB 表是基于聚族索引建立的,聚族索引對主鍵查詢有很高的性能
* InnoDB支持外鍵,而MyISAM不支持
* MyISAM 存儲引擎已經有了20年的歷史,在1995年時,MyISAM 是 MySQL 唯一的存儲引擎,服務了20多年,即將退居二線。隨著mysql5.7,8版本的提升,myisam優點已經逐漸被 InnoDB 實現了。比如全文索引,表空間優化,臨時表優化,高效的count(*)
## 什么是索引?
索引是一種數據結構,可以幫助我們快速的進行數據的查找.
## 索引是個什么樣的數據結構呢?
索引的數據結構和具體存儲引擎的實現有關, 在MySQL中使用較多的索引有Hash索引,B+樹索引等,而我們經常使用的InnoDB存儲引擎的默認索引實現為:B+樹索引.
## innodb索引的實現原理是什么?
InnoDB使用的是聚簇索引,將主鍵組織到一棵B+樹中,而行數據就儲存在葉子節點上,若使用"where id = 14"這樣的條件查找主鍵,則按照B+樹的檢索算法即可查找到對應的葉節點,之后獲得行數據。若對Name列進行條件搜索,則需要兩個步驟:第一步在輔助索引B+樹中檢索Name,到達其葉子節點獲取對應的主鍵。第二步使用主鍵在主索引B+樹種再執行一次B+樹檢索操作,最終到達葉子節點即可獲取整行數據。
## btree和hash類型的索引有什么不同?
首先要知道Hash索引和B+樹索引的底層實現原理:
hash索引底層就是hash表,進行查找時,調用一次hash函數就可以獲取到相應的鍵值,之后進行回表查詢獲得實際數據.B+樹底層實現是多路平衡查找樹.對于每一次的查詢都是從根節點出發,查找到葉子節點方可以獲得所查鍵值,然后根據查詢判斷是否需要回表查詢數據.
那么可以看出他們有以下的不同:
* hash索引進行等值查詢更快(一般情況下),但是卻無法進行范圍查詢.
因為在hash索引中經過hash函數建立索引之后,索引的順序與原順序無法保持一致,不能支持范圍查詢.而B+樹的的所有節點皆遵循(左節點小于父節點,右節點大于父節點,多叉樹也類似),天然支持范圍.
* hash索引不支持使用索引進行排序,原理同上.
* hash索引不支持模糊查詢以及多列索引的最左前綴匹配.原理也是因為hash函數的不可預測.**AAAA**和**AAAAB**的索引沒有相關性.
* hash索引任何時候都避免不了回表查詢數據,而B+樹在符合某些條件(聚簇索引,覆蓋索引等)的時候可以只通過索引完成查詢.
* hash索引雖然在等值查詢上較快,但是不穩定.性能不可預測,當某個鍵值存在大量重復的時候,發生hash碰撞,此時效率可能極差.而B+樹的查詢效率比較穩定,對于所有的查詢都是從根節點到葉子節點,且樹的高度較低.
因此,在大多數情況下,直接選擇B+樹索引可以獲得穩定且較好的查詢速度.而不需要使用hash索引.
## 什么是覆蓋索引?
簡單的說,select的數據列只用從索引中就能夠取得,不必從數據表中讀取,換句話說查詢列要被所使用的索引覆蓋。
覆蓋索引必須要存儲索引列的值,而哈希索引、空間索引和全文索引不存儲索引列的值,所以mysql**只能用B-tree索引**做覆蓋索引。
## B+樹在滿足聚簇索引和覆蓋索引的時候不需要回表查詢數據,什么是聚簇索引?
在B+樹的索引中,葉子節點可能存儲了當前的key值,也可能存儲了當前的key值以及整行的數據,這就是聚簇索引和非聚簇索引. 在InnoDB中,只有主鍵索引是聚簇索引,如果沒有主鍵,則挑選一個唯一鍵建立聚簇索引.如果沒有唯一鍵,則隱式的生成一個鍵來建立聚簇索引.
當查詢使用聚簇索引時,在對應的葉子節點,可以獲取到整行數據,因此不用再次進行回表查詢.
## 在建立索引的時候,都有哪些需要考慮的因素呢?
建立索引的時候一般要考慮到字段的使用頻率,經常作為條件進行查詢的字段比較適合.如果需要建立聯合索引的話,還需要考慮聯合索引中的順序.此外也要考慮其他方面,比如防止過多的所有對表造成太大的壓力.這些都和實際的表結構以及查詢方式有關.
## 聯合索引/多列索引的注意事項是什么?
MySQL可以使用多個字段同時建立一個索引,叫做聯合索引.在聯合索引中,如果想要命中索引,需要按照建立索引時的字段順序挨個使用,否則無法命中索引.
具體原因為:
MySQL使用索引時需要索引有序,假設現在建立了"name,age,school"的聯合索引,那么索引的排序為: 先按照name排序,如果name相同,則按照age排序,如果age的值也相等,則按照school進行排序.
當進行查詢時,此時索引僅僅按照name嚴格有序,因此必須首先使用name字段進行等值查詢,之后對于匹配到的列而言,其按照age字段嚴格有序,此時可以使用age字段用做索引查找,,,以此類推.因此在建立聯合索引的時候應該注意索引列的順序,一般情況下,將查詢需求頻繁或者字段選擇性高的列放在前面.此外可以根據特例的查詢或者表結構進行單獨的調整.
## 導致索引失效的原因有哪些?
* 列參與了數學運算或者函數;
* 如果條件中有or,即使其中有條件帶索引也不會使用(這也是為什么盡量少用or的原因);
* 對于多列索引,不符合最左匹配的命中規則;
* like查詢是以%開頭;
* 如果直接查比用索引快,那么數據庫會自動選擇最優方式,不用索引;
* in 和 not in 也要慎用,否則會導致全表掃描。
## Explain 怎么用來做sql優化?
EXPLAIN 命令用法十分簡單, 在 SELECT 語句前加上 Explain 就可以了, 例如:
~~~
EXPLAIN?SELECT?*?from?user_info?WHERE?id <?300;
~~~
EXPLAIN 命令的輸出內容大致如下:
~~~
mysql> explain select * from user_info where id = 2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user_info
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
~~~
各列的含義如下:
* id: SELECT 查詢的標識符. 每個 SELECT 都會自動分配一個唯一的標識符.
* select_type: SELECT 查詢的類型.
* table: 查詢的是哪個表
* partitions: 匹配的分區
* type: 判斷此次查詢是`全表掃描`還是`索引掃描`
* possible_keys: 此次查詢中【可能】選用的索引
* key: 此次查詢中【確切】使用到的索引.
* key_len: 使用了索引的字節數,這個字段可以評估組合索引是否完全被使用。
* ref: 哪個字段或常數與 key 一起被使用
* rows: 顯示此查詢一共掃描了多少行. 這個是一個估計值.越小性能越好。
* filtered: 表示此查詢條件所過濾的數據的百分比
* extra: 額外的信息,
需要重點關注的列:select_type,type,possible_keys,keys,key_len, rows,Extra。
(1)type 類型的性能比較:
`ALL < index < range ~ index_merge < ref < eq_ref < const < system`
`ALL`類型因為是全表掃描, 因此在相同的查詢條件下, 它是速度最慢的.
而`index`類型的查詢雖然不是全表掃描, 但是它掃描了所有的索引, 因此比 ALL 類型的稍快.
后面的幾種類型都是利用了索引來查詢數據, 因此可以過濾部分或大部分數據, 因此查詢效率就比較高了.
`system`: 表中只有一條數據. 這個類型是特殊的`const`類型。
`const`: 針對主鍵或唯一索引的等值查詢掃描, 最多只返回一行數據. const 查詢速度非常快, 因為它僅僅讀取一次即可.
`range`: 表示使用索引范圍查詢。
(2)rows基本是第一眼要看的指標。
(3)全表掃描時, possible_keys 和 key 字段都是 NULL。
(4)當 Extra 顯示:
* Using filesort
表示 MySQL 需額外的排序操作, 不能通過索引順序達到排序效果. 一般有`Using filesort`, 都建議優化去掉, 因為這樣的查詢 CPU 資源消耗大。
* Using index
"覆蓋索引掃描", 表示查詢在索引樹中就可查找所需數據, 不用掃描表數據文件, 往往說明性能不錯
* Using temporary
查詢有使用臨時表, 一般出現于排序, 分組和多表 join 的情況, 查詢效率不高, 建議優化.
## 主鍵使用自增ID還是UUID?
推薦使用自增ID,不要使用UUID.
因為在InnoDB存儲引擎中,主鍵索引是作為聚簇索引存在的,也就是說,主鍵索引的B+樹葉子節點上存儲了主鍵索引以及全部的數據(按照順序),如果主鍵索引是自增ID,那么只需要不斷向后排列即可,如果是UUID,由于到來的ID與原來的大小不確定,會造成非常多的數據插入,數據移動,然后導致產生很多的內存碎片,進而造成插入性能的下降.
總之,在數據量大一些的情況下,用自增主鍵性能會好一些.
## 字段為什么要求定義為not null?
MySQL官網這樣介紹:
> NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.
null值會占用更多的字節,且會在程序中造成很多與預期不符的情況.
## drop、delete與truncate分別在什么場景之下使用?
我們來對比一下他們的區別:
drop table
* 1)屬于DDL
* 2)不可回滾
* 3)不可帶where
* 4)表內容和結構刪除
* 5)刪除速度快
truncate table
* 1)屬于DDL
* 2)不可回滾
* 3)不可帶where
* 4)表內容刪除
* 5)刪除速度快
delete from
* 1)屬于DML
* 2)可回滾
* 3)可帶where
* 4)表結構在,表內容要看where執行的情況
* 5)刪除速度慢,需要逐行刪除
總結:
**不再需要一張表的時候,用drop**
**想刪除部分數據行時候,用delete,并且帶上where子句**
**保留表而刪除所有數據的時候用truncate**
## MySQL中的varchar和char有什么區別?
1. char的長度是不可變的,而varchar的長度是可變的?。
2. 定義一個char\[10\]和varchar\[10\],如果存進去的是‘abcd’,那么char所占的長度依然為10,除了字符‘abcd’外,后面跟六個空格,而varchar就立馬把長度變為4了,取數據的時候,char類型的要用trim()去掉多余的空格,而varchar是不需要的,
3. char的存取速度比varchar要快得多,因為其長度固定,方便程序的存儲與查找;但是char也為此付出的是空間的代價,因為其長度固定,所以難免會有多余的空格占位符占據空間,可謂是以空間換取時間效率,而varchar是以空間效率為首位的。
4. char的存儲方式是,對英文字符(ASCII)占用1個字節,對一個漢字占用兩個字節;而varchar的存儲方式是,對每個英文字符占用2個字節,漢字也占用2個字節,兩者的存儲數據都非unicode的字符數據。
5. char適合存儲長度固定的數據,varchar適合存儲長度不固定的。
## varchar(10)和int(10)代表什么含義?
varchar的10代表了申請的空間長度,也是可以存儲的數據的最大長度,而int的10只是代表了展示的長度,不足10位以0填充.也就是說,int(1)和int(10)所能存儲的數字大小以及占用的空間都是相同的,只是在展示時按照長度展示.
## 超大分頁怎么處理?
超大的分頁一般從兩個方向上來解決.
* 數據庫層面,這也是我們主要集中關注的(雖然收效沒那么大),類似于`select * from table where age > 20 limit 1000000,10`這種查詢其實也是有可以優化的余地的. 這條語句需要load1000000數據然后基本上全部丟棄,只取10條當然比較慢. 當時我們可以修改為`select * from table where id in (select id from table where age > 20 limit 1000000,10)`.這樣雖然也load了一百萬的數據,但是由于索引覆蓋,要查詢的所有字段都在索引中,所以速度會很快. 同時如果ID連續的好,我們還可以`select * from table where id > 1000000 limit 10`,效率也是不錯的,優化的可能性有許多種,但是核心思想都一樣,就是減少load的數據.
* 從需求的角度減少這種請求....主要是不做類似的需求(直接跳轉到幾百萬頁之后的具體某一頁.只允許逐頁查看或者按照給定的路線走,這樣可預測,可緩存)以及防止ID泄漏且連續被人惡意攻擊.
解決超大分頁,其實主要是靠緩存,可預測性的提前查到內容,緩存至redis等k-V數據庫中,直接返回即可.
在阿里巴巴《Java開發手冊》中,對超大分頁的解決辦法是類似于上面提到的第一種.
## 關心過業務系統里面的sql耗時嗎?統計過慢查詢嗎?對慢查詢都怎么優化過?
在業務系統中,除了使用主鍵進行的查詢,其他的我都會在測試庫上測試其耗時,慢查詢的統計主要由運維在做,會定期將業務中的慢查詢反饋給我們.
慢查詢的優化首先要搞明白慢的原因是什么? 是查詢條件沒有命中索引?是load了不需要的數據列?還是數據量太大?
所以優化也是針對這三個方向來的,
* 首先分析語句,看看是否load了額外的數據,可能是查詢了多余的行并且拋棄掉了,可能是加載了許多結果中并不需要的列,對語句進行分析以及重寫.
* 分析語句的執行計劃,然后獲得其使用索引的情況,之后修改語句或者修改索引,使得語句可以盡可能的命中索引.
* 如果對語句的優化已經無法進行,可以考慮表中的數據量是否太大,如果是的話可以進行橫向或者縱向的分表.
## 上面提到橫向分表和縱向分表,可以分別舉一個適合他們的例子嗎?
橫向分表是按行分表,假設我們有一張用戶表,主鍵是自增ID且同時是用戶的ID.數據量較大,有1億多條,那么此時放在一張表里的查詢效果就不太理想。我們可以根據主鍵ID進行分表,無論是按尾號分,或者按ID的區間分都是可以的,假設按照尾號0-99分為100個表,那么每張表中的數據就僅有100w,這時的查詢效率無疑是可以滿足要求的。
縱向分表是按列分表。假設我們現在有一張文章表,包含字段`id-摘要-內容`,而系統中的展示形式是刷新出一個列表。列表中僅包含標題和摘要,當用戶點擊某篇文章進入詳情時才需要正文內容。此時,如果數據量大,將內容這個很大且不經常使用的列放在一起會拖慢原表的查詢速度,我們可以將上面的表分為兩張。`id-摘要`,`id-內容`.當用戶點擊詳情,那主鍵再來取一次內容即可。而增加的存儲量只是很小的主鍵字段.代價很小。
當然,分表其實和業務的關聯度很高。在分表之前一定要做好調研以及benchmark.不要按照自己的猜想盲目操作、
## LEFT JOIN 、RIGHT JOIN、INNER JOIN 區別?
* LEFT JOIN(左連接):獲取左表所有記錄,即使右表沒有對應匹配的記錄
* RIGHT JOIN(右連接): 與 LEFT JOIN 相反,用于獲取右表所有記錄,即使左表沒有對應匹配的記錄
* INNER JOIN(內連接):獲取兩個表中字段匹配關系的記錄
## UNION、UNION ALL區別?
union:對兩個結果集進行并集操作,不包括重復行,同時進行默認規則的排序;
union All:對兩個結果集進行并集操作,包括重復行,不進行排序;
## 說一說常用 的MySQL 函數
數學函數
* floor(x) 返回不大于 x 的最大整數值
* ceil/ceiling(x) 返回不小于 x 的最小整數
* round(x) 四舍五入
* rand() 隨機函數\[0, 1)
* abs(x) 返回 x 的絕對值
字符串函數
* concat(str1, str2, ...) 將參數連接成字符串返回
* length(str) 返回字符串長度
日期和時間函數
* now() 當前時間
* curdate() 當前日期
~~~
SELECT UNIX_TIMESTAMP('2019-05-07 22:55:00'); #1557240900
SELECT FROM_UNIXTIME(1557240900); #2019-05-07 22:55:00
~~~
系統信息函數
* VERSION() 返回數據庫的版本號
* LAST\_INSERT\_ID() 返回最后生成的 AUTO\_INCREMENT 值
加密函數
* PASSWORD(str) 對字符串 str 進行加密
* MD5(str) 對字符串 str 進行加密
格式化函數
* FORMAT(x, n) 可以將數字 x 進行格式化,保留到小數點后 n 位,四舍五入
~~~
SELECT FORMAT(2.7895, 2); #2.79
~~~
獲取當前時間(年月日時分秒)
~~~
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-05-22 10:39:23 |
+---------------------+
~~~
獲取當前時間戳
~~~
mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
| 1590115200 |
+-----------------------+
1 row in set (0.00 sec)
~~~
獲取年月日
~~~
mysql> SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2020-05-22 |
+------------+
1 row in set (0.00 sec)
~~~
## 你的sql優化常用技巧有哪些?
1. 使用參數化查詢:防止SQL注入,預編譯SQL命令提高效率
2. 去掉不必要的查詢和搜索字段
3. 爭取命中索引,或者根據已有的查詢情景簡歷合理的索引
4. 不要使用select \*:不要使用select \*,以提高查詢效率,減少輸出的數據量,提高傳輸速度
5. 盡量避免向客戶端返回大數據量,若數據量過大,應該考慮相應需求是否合理
6. 減少訪問數據庫的次數,業務代碼邏輯優化,避免for循環
7. 使用表的別名(Alias):當在SQL語句中連接多個表時, 減少解析的時間,減少那些由Column歧義引起的語法錯誤
8. 使用列的別名:當列的名稱很長的時候,使用簡短的列的別名可以查詢結果更清晰,更簡潔
9. 狀態,類型等字段使用tinyint類型
10. 字段設計not null
11. 索引可以提高相應的 select 的效率,但同時也降低了 insert 及 update 的效率,因此索引并非越多越好,合理有效的使用
12. 盡量避免大事務操作,提高系統并發能力
已上要點,回答幾個應該也就OK了,畢竟面試時間不能總是在聊一道題。
## 同一個字段,用 int 還是 char 查詢效率高?
從效率來說,INT效率更高。查詢速度也和是否建立索引,字段長度占用的空間大小有關系。
## SQL編寫題,一些實例,考察一下
給定四個表:
* `student`(學生表)
* `teacher`(老師表)
* `course`(課程表)
* `sc`(成績表)
**「表結構以及部分數據如下圖展示」**

根據題目要求,寫出SQL語句。
**「問題列表」**
~~~
1、查詢每個學生的學號、姓名和每門課的成績
2、查詢都學過2號同學(sid=2)學習過的課程的同學的學號
3、查詢“語文(cid=1)”課程比“數學(cid=2)”課程成績高的所有學生的學號;
4、查詢平均成績大于60分的同學的學號和平均成績;
5、查詢所有同學的學號、姓名、選課數、總成績;
6、查詢姓“李”的老師的個數;
7、查詢沒學過“葉平”老師課的同學的學號、姓名;
8、查詢學過“語文(cid=1)”并且也學過“數學(cid=2)”課程的同學的學號、姓名;
9、查詢學過“葉平”老師所教的所有課的同學的學號、姓名;
10、查詢課程編號“數學(cid=2)”的成績比課程編號“語文(cid=1)”課程低的所有同學的學號、姓名;
11、查詢所有課程成績小于60分的同學的學號、姓名;
12、查詢沒有學全所有課的同學的學號、姓名;
13、按平均成績從高到低顯示所有學生的“語文“、“數學”、“英語”三門的課程成績,按如下形式顯示: 學生
ID,語文,數學,英語,有效課程數,有效平均分
14、查詢各科成績最高和最低的分:以如下形式顯示:課程ID,最高分,最低分
15、查詢男生、女生人數, 以如下形式顯示:男生人數,女生人數
16、查詢課程名稱為“數學”,且分數低于60的學生姓名和分數
17、查詢兩門以上不及格課程的同學的學號及其平均成績;
18、檢索“cid=4”課程分數小于60,按分數降序排列的同學學號
~~~
### **1、查詢每個學生的學號、姓名和每門課的成績;**
(1)利用隱式內聯,只輸出兩表中都有的字段
~~~
SQL語句:
SELECT
student.sid AS 學生ID,
student.sname AS 學生姓名,
(SELECT course.cname FROM course WHERE course.cid=sc.cid) AS 課程名稱,
sc.score AS 成績
FROM student,sc
WHERE student.sid = sc.sid
~~~
(2)利用左連接,假如左表有,右表沒有,則按左表為準,右表缺省字段置為NULL
SQL語句:
~~~
SELECT
student.sid AS 學生ID,
student.sname AS 學生姓名,
(SELECT course.cname FROM course WHERE course.cid=sc.cid) AS 課程名稱,
sc.score AS 成績
FROM student
LEFT JOIN sc
ON student.sid = sc.sid
~~~
因為 student 表 和 sc 表沒有冗余字段,所以(1)和(2)兩種查詢方法結果一致。
### **2、查詢都學過2號同學(sid=2)學習過的課程的同學的學號**
SQL語句:
~~~
SELECT sid
FROM sc
WHERE cid IN (SELECT cid FROM sc WHERE sid=2)
GROUP BY sid
HAVING COUNT(*) = (SELECT COUNT(*) FROM sc WHERE sid=2)
~~~
### **3、查詢“語文(cid=1)”課程比“數學(cid=2)”課程成績高的所有學生的學號;**
SQL語句:
~~~
SELECT a.sid
FROM
(SELECT sid, score FROM sc WHERE cid=1) a,
(SELECT sid, score FROM sc WHERE cid=2) b
WHERE a.sid=b.sid AND a.score > b.score;
~~~
### **4、查詢平均成績大于60分的同學的學號和平均成績;**
SQL語句:
~~~
SELECT sid, AVG(score)
FROM `sc`
GROUP BY sid
HAVING AVG(score) > 60;
~~~
### **5、查詢所有同學的學號、姓名、選課數、總成績;**
SQL:
~~~
SELECT a.sid, a.sname, COUNT(a.cid), SUM(a.score)
FROM
(SELECT student.sid, student.sname, sc.cid, sc.score FROM student JOIN sc WHERE student.sid=sc.sid) a
GROUP BY sid;
~~~
### **6、查詢姓“周”的老師的個數;**
SQL:
~~~
SELECT COUNT(*) FROM `teacher` WHERE tname LIKE '周%';
~~~
### **7、查詢沒學過“葉平”老師課的同學的學號、姓名;**
SQL:
~~~
# 參考答案
SELECT student.sid,student.sname
FROM student
WHERE sid NOT IN
(
SELECT DISTINCT(sc.sid)
FROM sc, course, teacher
WHERE sc.cid=course.cid AND teacher.tid=course.tid AND teacher.tname='葉平'
);
~~~
### **8、查詢學過“語文(cid=1)”并且也學過“數學(cid=2)”課程的同學的學號、姓名;**
SQL:
~~~
SELECT student.sid,student.sname
FROM student, sc
WHERE student.sid=sc.sid AND sc.cid=1
AND
EXISTS
( SELECT * FROM sc AS sc_2 WHERE sc_2.sid=sc.sid AND sc_2.cid=2);
~~~
### **9、查詢學過“葉平”老師所教的所有課的同學的學號、姓名;**
SQL:
~~~
SELECT student.sid,student.sname
FROM student
WHERE sid IN
(
SELECT sid
FROM sc ,course ,teacher
WHERE sc.cid=course.cid AND teacher.tid=course.tid AND teacher.tname='葉平'
GROUP BY sid
HAVING COUNT(sc.cid)=
(
SELECT COUNT(cid)
FROM course,teacher
WHERE teacher.tid=course.tid AND teacher.tname='葉平'
)
);
~~~
### **10、查詢課程編號“數學(cid=2)”的成績比課程編號“語文(cid=1)”課程低的所有同學的學號、姓名;**
SQL:
~~~
SELECT sid,sname
FROM student
WHERE sid=
(
SELECT a.sid
FROM
(SELECT sid, score FROM sc WHERE cid=1) a,
(SELECT sid, score FROM sc WHERE cid=2) b
WHERE a.sid=b.sid AND a.score > b.score
)
~~~
### **「11」、查詢所有課程成績小于60分的同學的學號、姓名;**
SQL:
~~~
SELECT sid,sname
FROM student
WHERE sid NOT IN
(
SELECT student.sid FROM student AS s,sc WHERE s.sid=sc.sid AND score>60
);
~~~
### **12、查詢沒有學全所有課的同學的學號、姓名;**
SQL:
~~~
SELECT student.sid, student.sname
FROM student,sc
WHERE student.sid=sc.sid
GROUP BY sid
HAVING COUNT(cid) < (SELECT COUNT(cid) FROM course)
~~~
### **13、按平均成績從高到低顯示所有學生的“語文“、“數學”、“英語”三門的課程成績,按如下形式顯示: 學生ID,語文,數學,英語,有效課程數,有效平均分**
SQL:
~~~
SELECT sid AS 學生ID,
(SELECT score FROM sc WHERE sc.sid=sc_2.sid AND cid=1) AS 語文,
(SELECT score FROM sc WHERE sc.sid=sc_2.sid AND cid=2) AS 數學,
(SELECT score FROM sc WHERE sc.sid=sc_2.sid AND cid=3) AS 英語,
COUNT(*) AS 有效課程數,
AVG(score)
FROM sc AS sc_2
GROUP BY sid
ORDER BY AVG(sc_2.score)
~~~
### **14、查詢各科成績最高和最低的分:以如下形式顯示:課程ID,最高分,最低分**
SQL:
~~~
SELECT cid AS 課程ID, MAX(score) AS 最高分, MIN(score) AS 最低分
FROM sc
GROUP BY cid
~~~
### **15、查詢男生、女生人數 :以如下形式顯示:男生人數,女生人數**
SQL:
~~~
SELECT
(SELECT COUNT(ssex) FROM student GROUP BY ssex HAVING ssex='男') AS 男生人數,
(SELECT COUNT(ssex) FROM student GROUP BY ssex HAVING ssex='女') AS 女生人數;
~~~
### **16、查詢課程名稱為“數學”,且分數低于60的學生姓名和分數**
SQL:
~~~
SELECT student.sid, student.sname
FROM student,sc
WHERE student.sid=sc.sid AND cid=(SELECT cid FROM course WHERE cname='數學') AND score > 60
~~~
### **17、查詢兩門及兩門以上不及格課程的同學的學號及其平均成績**
SQL:
~~~
SELECT sid, AVG(score) FROM sc WHERE sid IN (SELECT sid FROM sc WHERE score < 60 GROUP BY sid HAVING COUNT(*)>1) GROUP BY sid;
~~~
### **18、檢索“cid=4”課程分數小于60,按分數降序排列的同學學號**
SQL:
~~~
SELECT sid FROM sc WHERE cid=4 AND score < 60 ORDER BY score DESC
~~~
## hash索引的實現原理是什么?
哈希索引的實現基于哈希算法。哈希算法是一種常用的算法,時間復雜度為`O(1)`。它不僅應用在索引上,各個數據庫應用中也都會使用。
InnoDB存儲引擎使用哈希算法來對字典進行查找,哈希碰撞采用轉鏈表解決。所以當hash碰撞過多的時候,查詢效率就會降低很多。
## 講一下你理解的B+樹索引是怎么實現的?
B+樹中的B不是代表的二叉(Binary) ,而是代表平衡(Balance),因為B+樹是從最早的平衡二叉樹演化而來,但是B+樹不是一個二叉樹。
**一步一步的迭代**
1. 二叉樹:相當于一個二分查找,二叉查找能大大提升查詢的效率,但是極端情況下,二叉樹會變成一個線性鏈表結構。
2. 平衡二叉樹:通過旋轉讓失衡二叉樹恢復平衡。缺點是數據量達到幾百萬后,樹的高度會很恐怖,導致搜索效率不足。其二,存儲的數據內容太少,沒有很好利用操作系統和磁盤數據交換特性。
3. 多路平衡查找樹(Balance Tree,也叫B-tree):
B-Tree是為磁盤等外存儲設備設計的一種平衡查找樹。B-Tree結構的數據可以讓系統高效的找到數據所在的磁盤塊。
B-Tree相對于AVLTree縮減了節點個數,使每次磁盤I/O取到內存的數據都發揮了作用,從而提高了查詢效率。
4. B+TREE:
? ?B+Tree是在B-Tree基礎上的一種優化,使其更適合實現外存儲索引結構,在B+Tree中,所有數據記錄節點都是按照鍵值大小順序存放在同一層的葉子節點上,而非葉子節點上只存儲key值信息,這樣可以大大加大每個節點存儲的key值數量,降低B+Tree的高度。
**B+Tree相對于B-Tree有幾點不同:**
1. **非葉子節點只存儲鍵值信息。**
2. **所有葉子節點之間都有一個鏈指針。**
3. **數據記錄都存放在葉子節點中**
可以參考:https://blog.csdn.net/qq_36098284/article/details/80178336
InnoDB存儲引擎就是用B+Tree實現其索引結構。
## 索引是如何存儲在磁盤上的?
數據庫中的B+Tree索引可以分為聚集索引(clustered index)和輔助索引(secondary index)。

上面的B+Tree示例圖在數據庫中的實現即為聚集索引,聚集索引的B+Tree中的葉子節點存放的是整張表的行記錄數據。
輔助索引與聚集索引的區別在于:
? ? ? 輔助索引的葉子節點并不包含行記錄的全部數據,而是存儲相應行數據的聚集索引鍵,即主鍵。
當通過輔助索引來查詢數據時,InnoDB存儲引擎會遍歷輔助索引找到主鍵,然后再通過主鍵在聚集索引中找到完整的行記錄數據。
- 導讀
- 簡歷和信心
- 一面(技術基礎)
- PHP
- MySQL-基礎
- MySQL-基礎2
- Nginx
- Redis
- 網絡
- 二面(技術進階)
- PHP
- MySQL
- Nginx
- Redis
- Linux
- 網絡
- 算法
- 操作系統
- 數據結構
- 網絡安全
- 分布式和微服務
- 線上故障處理經驗
- 架構
- 通用型業務的解決方案
- 高并發下如何保持一致性?
- 軟件測試的階段和方法有哪些
- 雪崩效應解決方案
- 兩個海量數據的同構表,如何查詢數據差異?
- 怎么設計一套通信接口的標準?
- 工作中有用到ES么
- 如何設計SKU表結構
- 如何設計RBAC表結構
- 如何設計防超賣的架構
- 如何設計高并發的架構
- 怎么理解SaaS,如何設計SaaS項目的架構
- 如何設計新浪關注feed流的架構
- 怎么設計短url服務
- 如何實現接口冪等性?
- 如何設計高可用的訂單業務架構
- 如何設計單點登錄的架構
- 如何應對大流量高并發?
- 團隊開發中,git分支管理怎么設定策略?
- 項目設計
- 如何設計秒殺架構?
- 有哪些方式生成唯一ID?
- 三面(技術終面)
- 工作素養
- 四面(hr & hrbp)
- 離職前要先找好下家嗎?
- 離職原因
- 談薪定級
- 個人道德、職業素養和服從性測試
- 反問環節
- 注意事項
- 擴展學習