SQL復習

MySQL

SQL介紹

SQL

SQL的全拼是什么？

SQL全拼：Structured Query Language，也叫結構化查詢語言。

SQL92和SQL99有什么區別呢？

SQL92和SQL99分別代表了92年和99年頒布的SQL標準。

在 SQL92 中采用（+）代表從表所在的位置，而且在SQL92 中，只有左外連接和右外連接，沒有全外連接。

LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的標準中，在 SQL92 中不存在，只能用（+）表示。

SQL99 的外連接有哪些形式？

SQL99 的外連接包括了三種形式：

1. 左外連接：LEFT JOIN 或 LEFT OUTER JOIN
2. 右外連接：RIGHT JOIN 或 RIGHT OUTER JOIN
3. 全外連接：FULL JOIN 或 FULL OUTER JOIN

SQL語言按照功能劃分為哪四部分？它們分別是什么作用？

SQL語言按照功能劃分為DDL、DML、DCL、DQL四部分。

DDL：Data Definition Language，數據定義語言。用于定義數據庫對象，包括數據庫、數據表和列。

DML：Data Manipulation Language，數據操作語言。用于增加、刪除、修改數據表中的記錄。

DCL：Data Control Language，數據控制語言。用于定義訪問權限和安全級別。

DQL：Data Query Language，數據查詢語言。用于查詢想要的記錄。

什么是ER圖？

ER圖即Entity Relationship Diagram，也叫實體-關系圖。是用于描述現實世界的概念模型，主要包含三個要素：實體、屬性、關系。

實體：我們要管理的對象；

屬性：每個實體的屬性；

關系：對象之間的關系。

SQL的單行注釋怎么寫？多行注釋怎么寫？

-- 單行注釋
#單行注釋
/* 
多行注釋	
*/

SELECT、FROM、WHERE、HAVING、ORDER BY、LIMIT、GROUP BY七個關鍵字之間的執行順序是什么？

執行順序是：FROM→ WHERE →GROUP BY → HAVING→SELECT→ORDER BY→LIMIT。

為表添加了別名之后，還能使用原來的表名嗎？

一旦設置別名，就不能再直接使用表名了。

int(11)中的11是什么意義？

int代表整數類型，11代表顯示長度為11位，即最大有效顯示長度，與類型包含的數值范圍大小無關。

varchar(255)中的255是什么含義？

255表示可變字符串類型的最大長度為255。

DCL

DCL是？作用是什么？主要關鍵字有哪些？

DCL（Data Control Language），是數據庫控制語言。

主要用于管理數據庫用戶、控制用戶數據庫訪問權限。

主要關鍵字包括：GRANT、REVOKE等。

如何查詢MySQL中的所有用戶？重點

SELECT * FROM mysql.user;

如何創建用戶？重點

CREATE USER '用戶名'@'主機名' IDENTIFIED BY '密碼';

如何修改用戶密碼？重點

ALTER USER '用戶名'@'主機名' IDENTIFIED WITH mysql_native_password BY '新密碼' ;

如何刪除用戶？重點

DROP USER '用戶名'@'主機名' ;

如何查詢用戶的權限？重點

SHOW GRANTS FOR '用戶名'@'主機名' ;

如何授予用戶權限？重點

GRANT 權限列表 ON 數據庫名.表名 TO '用戶名'@'主機名';

如何撤銷用戶權限？重點

REVOKE 權限列表 ON 數據庫名.表名 FROM '用戶名'@'主機名';

CREATE USER 'itsy'@'localhost' IDENTIFIED BY '123456';
#以上語句的作用是什么？

創建用戶itsy, 只能夠在當前主機localhost訪問, 密碼123456。

CREATE USER 'sycoder'@'%' IDENTIFIED BY '123456';
#以上語句的作用是什么？

創建用戶sycoder, 可以在任意主機訪問該數據庫, 密碼123456。

ALTER USER 'sycoder'@'%' IDENTIFIED  WITH mysql_native_password BY '111111' ;
#以上語句的作用是什么？

修改用戶sycoder的訪問密碼為111111。

DROP USER 'itsy'@'localhost';
#以上語句的作用是什么？

刪除 itsy@localhost 用戶。

DDL

什么是DDL？DDL的作用是什么？主要關鍵字包括哪些？

DDL全拼為Data Definition Language，也就是數據定義語言。

用于定義數據庫對象，包括數據庫、數據表和列。

主要關鍵字包括：CREATE、DROP、USE、SHOW、ALTER、TRUNCATE等。

如何查看當前有哪些數據庫？

SHOW databases;#查看哪些數據庫

如何查詢當前數據庫？

SELECT database();

如何創建數據庫？

create database [ if not exists ] 數據庫名 [ default charset 字符集 ] [ collate 排序 規則 ] ;

如果創建的數據庫已經存在，如何避免出現報錯ERROR 1007？

加上可選參數if not exists，可以解決這個問題

如何刪除數據庫？

drop database [ if exists ] 數據庫名; 

如果刪除的數據庫本身就不存在，如何避免報錯？

加上參數IF EXISTS。

DROP DATABASE IF EXISTS itsy; 

如何切換數據庫？

use 數據庫名;

如何查看當前數據庫中的所有表？

show tables;

如何查看指定表的結構？

desc 表名;

如何查詢指定表的建表語句？

show create table 表名;

如何創建表？

CREATE TABLE 表名( 字段1 字段1類型 	[COMMENT 字段1注釋 ],字段2 字段2類型 	[COMMENT 字段2注釋 ], 字段3 字段3類型 	[COMMENT 字段3注釋 ], ...... 字段n 字段n類型 	[COMMENT 字段n注釋 ] ) 
[ COMMENT 表注釋 ];

如何向表中添加字段？

ALTER TABLE 表名 ADD 字段名 類型 (長度)[ COMMENT 注釋 ] [ 約束 ]; 

如何修改字段的數據類型？

ALTER TABLE 表名 MODIFY 字段名 新數據類型 (長度);

如何修改字段名和字段類型？

ALTER TABLE 表名 CHANGE 舊字段名 新字段名 類型 (長度) [ COMMENT 注釋 ] [ 約束 ];

如何刪除字段？

ALTER TABLE 表名 DROP 字段名;

如何修改表名？

ALTER TABLE 表名 RENAME TO 新表名;

如何刪除表？

DROP TABLE [ IF EXISTS ] 表名;

如何刪除指定表，并創建新表？

TRUNCATE TABLE 表名;

DML

什么是DML？DML的作用是什么？主要關鍵字是哪些？

DML是Data Manipulatioin Language，也叫數據操作語言。

主要用于對數據的增加、刪除和修改。

主要關鍵字包括：INSERT、UPDATE、DELETE。

如何向指定的表中添加數據？

向指定的表中添加數據時，分為兩種情況：

第一種：只給指定的字段添加數據（其它字段采用默認值）。

第二種：給全部字段添加數據。

當我們為全部字段賦值時，可以省去字段名的內容。

#給指定的字段添加數據：
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
#給全部字段添加數據：
INSERT INTO 表名 VALUES (值1, 值2, ...);

如何修改指定數據的字段內容？

UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 條件 ] ;

如何刪除指定表中的指定數據？如果刪除表中的數據時，沒有添加WHERE關鍵字，會有什么后果？

如果米有WHERE關鍵字，會將表中的數據全部刪除。

DELETE FROM 表名 [ WHERE 條件 ] 

DQL

什么是DQL？DQL有什么作用？DQL的關鍵字有哪些？

DQL（Data Query Language），即數據庫查詢語言。

DQL主要用于數據的查詢。

DQL的關鍵字包括：SELECT、WHERE、GROUP、ORDER BY、FROM、DISTINCT、HAVING、LIMIT。

為列添加別名使用什么關鍵字？

為列添加別名需要使用AS關鍵字，但其實AS關鍵字可以省略。

WHERE和HAVING的區別是什么？重點

兩者的區別主要體現在兩個方面：

1. 兩者執行時間不同：WHER在分組之前執行，不參與分組；HAVING在分組之后執行。
2. 判斷條件不同：WHERE不能對聚合函數進行判斷，但是HAVING可以。

使用ORDER BY對查詢結果進行排序時，如果不指定排序方式，則默認排序方式是什么？

ORDER BY的排序方式有兩種，DESC和ASC。

默認的排序方式是ASC，也就是升序排序。

LIMIT的作用是什么？起始索引是從幾開始的？

LIMIT用于分頁查詢。起始索引是從0開始的。

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查詢記錄數 ;

LIMIT的起始索引什么情況下可以省略？

如果只查詢第一頁的數據，可以省略LIMIT的起始索引。

SELECT查詢

在SELECT查詢中，關鍵字的順序是什么？

關鍵字的順序是絕對不能顛倒的：

SELECT...FROM...WHERE...GROUP BY...HAVING...ORDER BY...

查詢常數

為什么要對常數進行查詢呢？

常數查詢用于整合不同的數據源時，作為標記數據源來源的標記。如：

SELECT '王者榮耀' as platform,name FROM heros;

在這段SQL語句中，我們虛構了一個platform字段，并將其設置為固定值“王者榮耀”。

所以查詢結果中，將會增加一個字段platform，其內容均為“王者榮耀”。

使用常數查詢時，有哪些需要注意的地方？

需要注意，如果常數是一個字符串，則必須使用單引號‘’，沒有單引號的常數，會被SQL當作列名進行檢查，從而造成“找不到列”的錯誤。

但如果常數是一個數字，則可以直接寫數字，不會造成報錯。

---

去除重復行

去除重復行使用的關鍵字是什么？

去除重復行需要使用DISTINCT關鍵字。

DISTINCT關鍵字的作用是什么？如何使用？

DISTINCT用于去除查詢結果中的重復數據，其去重的對象是所有的列。

在SELECT關鍵字后，所有列名前使用。如：

SQL：SELECT DISTINCT attack_range FROM heros

如果DISTINCT的位置錯誤，則會造成報錯。

排序

對查詢結果進行排序時需要使用的關鍵字是什么？

使用ORDER BY對查詢的結果進行排序。

ORDER BY語句有什么地方需要掌握？

ORDER BY有以下幾處需要掌握：

1. ORDER BY后可以有一個或多個列名。對多個列的排序會按照先后順序進行。
2. OREDR BY可以設置排序規則，ASC表示遞增，DESC表示遞減。默認按照ASC進行排序。
3. ORDER BY可以對非選擇列進行排序，即：即使SELECT后沒有此列，也可以放在ORDER BY后參與排序。
4. ORDER BY語句通常位于SELECT語句的最后一條子句，否則會報錯。

如果我們需要對不同的列采用不同的排序規則（A列升序，B列降序），應該怎么寫？

對不同的列進行排序時，可以直接在ORDER BY語句后的列名后增加排序規則。

如：

SQL：SELECT name, hp_max FROM heros ORDER BY mp_max, hp_max DESC  

此時，查詢結果將會按照mp_max升序，hp_max降序的方式排序。

---

約束返回結果數

如何約束返回結果的數量？

在MySQL中，使用LIMIT關鍵字來約束返回結果的數量。

注意，在不同的DBMS中，使用的關鍵字是不同的。

約束返回結果有什么好處？

約束返回結果可以減少數據表的網絡傳輸量，提升查詢效率。避免全表掃描。

---

執行順序

SQL中，SELECT語句的執行順序是什么樣的？重點

注意，在MySQL和Oracle中，SELECT的執行順序基本相同。

FROM → WHERE → GROUP BY → HAVING → SELECT的字段 → DISTINCT → ORDER BY → LIMIT

具體如下：

SELECT DISTINCT player_id, player_name, count(*) as num #順序5
FROM player JOIN team ON player.team_id = team.team_id #順序1
WHERE height > 1.80 #順序2
GROUP BY player.team_id #順序3
HAVING num > 2 #順序4
ORDER BY num DESC #順序6
LIMIT 2 #順序7

完整的 SELECT 語句內部的執行順序是什么樣的？重點

一條完整的 SELECT 語句內部的執行順序是這樣的：

1. FROM 子句組裝數據（包括通過 ON 進行連接）；
2. WHERE 子句進行條件篩選；
3. GROUP BY 分組 ；
4. 使用聚集函數進行計算；
5. HAVING 篩選分組；
6. 計算所有的表達式；
7. SELECT 的字段；
8. ORDER BY 排序；
9. LIMIT 篩選。

SELECT *

SELECT *有什么缺點？

使用SELECT * 會增加數據庫負擔。在實際工作中，應盡量寫出所需要的列名，生產環境下，盡量不要使用SELECT *進行查詢。

通配符

如何查詢英雄中，包含“太”字的英雄都有哪些？

使用通配符查詢。

SQL：SELECT name FROM heros WHERE name LIKE '%太%'

為什么要盡量少的使用通配符進行查詢？

1. 通配符需要消耗較長的時間來進行匹配。
2. 如果LIKE檢索的字段有索引，那么模糊查詢時，索引可能失效。

太%和%太有什么區別？

1. 首先兩者最大的區別在于查詢的對象不同，前者指以“太”開頭的元素，后者指以“太”結尾的元素。
2. 其次，當對應字段存在索引時，前者不會進行全表掃描，后者會進行全表掃描。

比較運算符

SQL中的比較運算符有哪些？

SQL中的運算符有10種：

>、>=、<、<=、=、<>或!=、BETWEEN AND、IN()、LIKE 占位符、IS NULL和IS NOT NULL。

BETWEEN AND包含最大值和最小值嗎？

BETWEEN AND包含最大值和最小值。

邏輯運算符

SQL中的邏輯運算符有哪些？

SQL中的邏輯運算符有四種：IN、AND或&&、OR或||、NOT或!。

當 WHERE 子句中同時存在 OR 和 AND 的時候，哪個優先級會更高？

當 WHERE 子句中同時存在 OR 和 AND 的時候，AND 執行的優先級會更高，即： SQL 會優先處理 AND 操作符，然后再處理 OR 操作符。

所以當WHERE子句中同時出現AND和OR的時候，一定要注意執行的先后順序。

分組

GROUP BY是什么？有什么地方需要注意？

GROUP BY用于對查詢到的數據進行分組。使用GROUP BY進行分組時，字段的值為NULL的數據也會被分為一組。

WHERE和HAVING的區別是什么？

1. WHERE 是用于過濾數據行，而 HAVING 則用于分組。
2. HAVING一般和GROUP BY組合使用。

DBMS

DBMS

常見的DBMS有哪些？哪些是關系型數據庫？哪些是非關系型數據庫？重點

常見的DBMS有MySQL、SQL Server、Redis、Elasticsearch、DB2和MongoDB。

關系型數據庫：Oracle、MySQL、SQL Server、DB2。

非關系型數據庫：MongoDB、Redis、Elasticsearch。

其中，MongoDB是文檔型數據庫，Redis是鍵值型數據庫，Elasticsearch是搜索引擎。

DBMS是什么？

DBMS即DataBase Management System，也叫數據庫管理系統。

DBS和DBMS哪一個概念更大？重點

DBS即DataBase System，也叫數據庫系統，包括了數據庫、數據庫管理系統以及數據庫管理人員DBA。

所以DBS是比DBMS更大的概念。

NoSQL數據庫有哪些類型？重點

NoSQL類型眾多，包括：鍵值型數據庫、搜索引擎、文檔型數據庫、列存儲和圖型數據庫等。

鍵值型數據庫的優缺點是什么？重點

鍵值型數據庫常用于內容緩存。

優點：查詢速度快。

缺點：無法自由使用條件過濾。

如果不知道數據的鍵，就需要遍歷所有的鍵來查詢。

Redis是最流行的鍵值型數據庫。

什么是文檔型數據庫？

文檔型數據庫將文檔作為處理信息的基本單位，一個文檔就相當于一條記錄。MongoDB是最流行的文檔型數據庫。

什么是搜索引擎？

搜索引擎的優勢在于采用了全文搜索的技術，相對關系型數據庫，有更高的全文檢索效率。

什么是列存儲？

“列式存儲”式相對于“行式存儲”而言的，常見的Oracle、MySQL、SQL Server等都是“行式存儲數據庫”，而列式存儲強調“將數據按照列存儲到數據庫中”。

列存儲的優勢在于大大降低系統的I/O，適用于分布式文件系統。

為什么列存儲能降低系統的I/O？

因為采用列存儲時，相鄰數據的數據類型是相同的，這就為壓縮提供了方便。壓縮之后，自然也就可以降低系統的I/O。

什么是圖型數據庫？

利用圖的數據結構實現了實體之間的關系，數據模型主要以節點和邊（也叫關系）來實現，優勢在于能高效地解決復雜的關系問題。

Oracle

Oracle

---

Oracle通過什么來判斷是否存在緩存和執行計劃，從而決定使用硬解析還是軟解析的？

Oracle通過共享池來判斷。

Oracle中的SQL是如何執行的？

Oracle中的SQL的執行分為六個步驟：

語法檢查→語義檢查→權限檢查→共享池檢查→優化器→執行器

什么是語法檢查？什么是語義檢查？

語法檢查指：檢查SQL的拼寫是否正確，當SQL的拼寫有問題時，Oracle會報語法錯誤。

語義檢查指：檢查SQL中的訪問對象是否存在。

語法檢查和語義檢查共同保證SQL語句沒有錯誤。

什么是權限檢查？

權限檢查指：檢查用戶是否具備訪問該數據的權限。

什么是共享池檢查？

共享池（Shared Pool）是一塊內存池，主要用于緩存SQL語句和該SQL語句的執行計劃。

Oracle通過檢查共享池中是否存在SQL語句的執行計劃，來判斷進行軟解析還是硬解析。

具體的流程是：Oracle首先對SQL語句進行Hash運算，根據得出的Hash值在庫緩存（Library Cache）中查找，如果存在該SQL語句的執行計劃，就按既有的執行計劃執行，跳過優化器環節，直接進入執行器環節，這就是軟解析；如果不存在該SQL語句的執行計劃，就進入優化器環節，創建該SQL語句的解析樹，生成執行計劃。

什么是優化器？什么是執行器？

優化器：會進行硬解析，創建解析樹，生成執行計劃。

執行器：SQL語句的具體執行位置。

什么是硬解析？什么是軟解析？

硬解析和軟解析離不開共享池。

對于一個SQL語句，Oracle會先計算該SQL語句的Hash值，根據此Hash值，判斷共享池中是否有對應的執行計劃。如果有，就會執行軟解析；相反，如果沒有，就會進行硬解析。

軟解析即利用共享池中的現有的執行計劃執行SQL語句；

硬解析指共享池的緩存中，沒有SQL語句的執行計劃，需要利用優化器創建解析樹對SQL語句進行解析，然后生成執行計劃。

庫緩存區的作用是什么？

庫緩存區主要用于緩存SQL語句和執行計劃。

數據字典緩沖區的作用是什么？

數據字典緩沖區用于Oracle中的對象定義，如：表、視圖、索引等對象。

對SQL語句進行解析時，需要從數據字典緩沖區中獲取。

綁定變量

---

Oracle中的綁定變量有什么作用？有什么弊端？

綁定變量可以讓我們減少硬解析，減少優化器的解析工作量。

但綁定變量本質是動態SQL的方式，參數的變化會對SQL執行的效率造成影響，同時，綁定變量也會對SQL優化造成一定的影響。

MySQL

MySQL

MySQL有什么特點？

MySQL 不支持全外連接。

SQL和MySQL有什么區別？

SQL是結構化查詢語言，Structured Query Language。

MySQL是數據庫管理系統DBMS。

如何檢查你本機有沒有安裝MySQL？

待定

my.ini文件有什么作用？

my.ini文件用于對MySQL進行配置，如：修改默認存儲引擎、修改默認事務隔離級別等。

MySQL中的SQL是如何執行的？SQL語句在MySQL中的執行流程是什么？

在MySQL中，SQL語句是在mysqld中的SQL層執行的，具體流程為：

SQL語句→查詢緩存→解析器→優化器→執行器。

MySQL的體系架構是什么？

MySQL采用的是C/S架構，即：Client/Server。

服務器端使用的是mysqld。

MySQL的體系結構整體分為四層：

1. 連接層
2. 服務層
3. 引擎層
4. 存儲層

連接層的作用是什么？

服務層的作用是什么？

SQL的分析和優化。

引擎層的作用是什么？

數據的存儲和讀取。

存儲層的作用是什么？

MySQL和Oracle的區別是什么？

MySQL中的存儲引擎采用了插件的形式，每種存儲引擎都對應一種數據庫應用環境。且允許開發人員設置自己的存儲引擎。

MySQL中，常用的存儲引擎有哪些？默認的存儲引擎是哪一個？為什么？

MySQL中常用的存儲引擎有：InnoDB、MyISAM、Memory、NDB、Archive等。

MySQL默認的存儲引擎是InnoDB。

因為相比于MyISAM，InnoDB有三個優點：

1. 支持行級鎖，因此并發性能更高
2. 支持事務。
3. 支持外鍵約束，更能保證數據的完整性和正確性。

數據類型

MySQL中的數據類型分為哪些？

MySQL中的數據類型主要分為三大類：數值類型、字符串類型、日期時間類型。

其中，

數值類型又分為七種：TINYINT、SMALLINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL。

字符串類型分為五種：CHAR、VARCHAR、BLOB、TEXT、LONGTEXT。

日期時間類型分為五種：DATE、TIME、YEAR、DATETIME、TIMESTAMP。

數值類型中，TINYINT的大小是多少？SMALLINT的大小是多少？INT/INTEGER的大小是多少？BIGINT的大小是多少？

在MySQL的數值類型中，TINYINT、SMALLINT、INT、BIGINT四者之間的大小排序是：TINYINT<SMALLINT<INT<BIGINT。

且按照2的整數冪遞增，分別對應1bytes、2bytes、4bytes、8bytes。

數值類型	大小
TINYINT	1bytes
SMALLINT	2bytes
INT/INTEGER	4bytes
BIGINT/Long	8bytes

數值類型中，FLOAT、DOUBLE的大小分別是多少？

兩者都是浮點數值，前者是單精度浮點數，后者是雙精度浮點數。

數值類型	大小
FLOAT	4bytes
DOUBLE	8bytes

字符串類型都有哪些？

字符串類型包括：CHAR、VARCHAR、BLOB、TEXT、LONGTEXT。

CHAR和VARCHAR有什么區別？CHAR的大小是多少？VARCHAR的大小是多少？

CHAR是定長字符串，VARCHAR是變長字符串。

但兩者在使用時都需要指定長度。

字符串類型	大小
CHAR	0-255bytes
VARCHAR	0-65535bytes

BLOB常用于什么場景？什么情況下會使用BLOB？

BLOB是二進制形式的長文本數據，所以經常用于圖片數據等需要用二進制保存的數據。

日期時間類型分為哪幾種？

日期時間類型分為：DATE、TIME、YEAR、DATETIME、TIMESTAMP。

DATE類型、TIME類型、DATETIME類型、TIMESTAMP類型的數據分別是什么格式？

日期時間類型	格式
DATE	YYYY-MM-DD
DATETIME	YYYY-MM-DD HH:MM:SS
TIME	HH:MM:SS
TIMESTAMP	YYYY-MM-DD

mysqld

mysqld的結構是什么樣的？

mysqld分為三層結構：連接層、SQL層、存儲引擎層。

連接層的作用是什么？

連接層負責建立客戶端和服務器端的連接。

連接建立后，客戶端會發送SQL到服務器端。

SQL層的作用是什么？

SQL層負責對SQL語句進行查詢處理。

存儲引擎層的作用是什么？

存儲引擎層負責與數據庫打交道，負責數據的存儲和讀取。

SQL層的結構是什么樣的？

SQL層分為四部分：查詢緩存、解析器、優化器、執行器。

為什么MySQL8.0之后，拋棄了查詢緩存的功能？

拋棄的原因主要在于查詢緩存的效率不高。

查詢緩存的作用是什么？

查詢緩存會保存已經之前查詢過的SQL語句，當這條SQL語句再次出現時，服務器端就會直接將結果返回給客戶端；如果查詢緩存中沒有這條SQL語句，就會進入解析器階段。

文件結構

MySQL文件夾下的tableName.ibd文件有什么作用？

MySQL5.5之后的默認是InnoDB，而InnoDB引擎會為每張表都生成一個對應的表空間文件。

在tableName.ibd中，文件名為表名，后綴即為InnoDB為每個表創建的表空間文件的文件類型。

IBD文件會保存每張表的結構、數據、索引等相關信息。

ibd文件有什么特點？

ibd文件是二進制文件，直接打開將會顯示亂碼，所以不能直接打開。

需要通過idb2sdi tableName.ibd命令查看ibd文件。

存儲結構

MySQL中的數據存儲結構是什么樣的？

MySQL中，數據分為五部分：

表空間、段、區、頁、行。

1. 表空間：tablespace，ibd文件就是表空間文件，表空間文件中包含多個段（Segment）。
2. 段：Segment，包含數據段、索引段、回滾段等，一個段中包含多個區。
3. 區：Extent，表空間的單元結構，大小默認是1M，一個區中包含64頁數據。
4. 頁：Page，InnoDB磁盤管理的最小單元，大小默認是16K。
5. 行：Row，每行即為一條數據。

Navicat

SQL函數

SQL函數

為什么不建議使用SQL函數？

因為不同的DBMS之間的差異很大，大部分DBMS都會有屬于自己的SQL函數，這導致采用SQL函數的代碼的可移植性很差。

聚合函數

什么是聚合函數？常用的聚合函數有哪些？

聚合函數也叫聚集函數，指用于對一組數據進行匯總的函數。參數為一組數據的集合，返回值為單個值。

常用的聚合函數有五個：COUNT、MAX、MIN、AVG、SUM。

WHERE、聚合函數、HAVING三者之間的執行順序是什么？

WHERE→聚合函數→HAVING。

COUNT(*)和COUNT(字段)有什么區別？

1. 前者只是統計數據行，不論該字段的值是否為NULL；
2. 后者會忽略字段的值為NULL的數據。

當MAX或MIN函數的參數為字符串時，如何計算最大值或最小值？

MAX 和 MIN 函數用于字符串類型數據的統計時，如果是英文字母，則按照 A—Z 的順序排列，越往后，數值越大。如果是漢字則按照全拼拼音進行排列，越靠后，值越大。

需要說明，我們需要先把 name 字段（漢字類型）統一轉化為 gbk 類型，使用CONVERT(name USING gbk)，然后再使用 MIN 和 MAX 取最小值和最大值。

數值函數

常用的數值函數有哪些？

常用的數值函數有：ABS、SIGN、SQRT、LEAST、MOD、ROUND。

ROUND函數有什么作用？

ROUND函數可以對數據進行四舍五入，它有兩個參數，分別表示需要處理的數據和四舍五入的位數。

SELECT ABS(-2)的運行結果是什么？

運行結果為 2。

SELECT MOD(101,3)的運行結果是什么？

運行結果 2。

SELECT ROUND(37.25,1)的運行結果是什么？

運行結果 37.3。

字符串函數

常用的字符串函數有哪些？

常用的字符串函數有：CONCAT、LENGTH、CHAR_LENGTH、LOWER、UPPER、SUBSTRING、REPLACE。

LEENGTH()和CHAR_LENGTH()的區別是什么？

1. 兩個函數的共同點都是計算字段的長度。
2. 在LENGTH()中，一個漢字算作三個字符，數字和字母都算作一個字符。
3. 在CHAR_LENGTH()中，漢字、數字、字母，都算作一個字符。

SELECT CONCAT('abc', 123)的運行結果是什么？

運行結果為 abc123。

SELECT LENGTH('你好')的運行結果是什么？

運行結果為 6。

SELECT CHAR_LENGTH('你好')的運行結果是什么？

運行結果為 2。

SELECT LOWER('ABC')的運行結果是什么？

運行結果為 abc。

SELECT REPLACE('fabcd', 'abc', 123)的運行結果是什么？

運行結果為 f123d。

SELECT SUBSTRING('fabcd', 1,3)的運行結果是什么？

運行結果為 fab。

日期函數

常用的日期函數有哪些？

常用的日期函數有：YEAR、HOUR、QUARTER。

QUARTER函數的作用是什么？

QUARTER函數可以返回日期對應的季度，范圍為1~4。

為什么對日期進行比較時，不能直接使用日期字符串？

我們一般使用DATE函數來對日期進行比較。

一般情況下，我們無法確認birthdate的數據類型是字符串，還是datetime類型，所以，使用DATE函數是比較安全的。

SELECT CURRENT_DATE()的運行結果是什么？

運行結果為 2019-04-03。

SELECT CURRENT_TIME()的運行結果是什么？

運行結果為 21:26:34。

SELECT CURRENT_TIMESTAMP()的運行結果是什么？

運行結果為 2019-04-03 21:26:34。

SELECT EXTRACT(YEAR FROM '2019-04-03')的運行結果是什么？

運行結果為 2019。

SELECT DATE('2019-04-01 12:00:05')的運行結果是什么？

運行結果為 2019-04-01。

流程函數

常見的流程函數有哪些？

常見的流程函數有：IF、IFNULL。

加密解密函數

常見的加密解密函數有哪些？

常見的加密解密函數有：PASSWORD、MD5、SHA、ENCODE、DECODE。

轉換函數

什么是轉換函數？常見的轉換函數有哪些？

轉換函數用于轉換數據之間的類型。

常見的轉換函數有：CAST、COALESCE。

CAST函數的作用是什么？它的參數是什么？

CAST用于數據類型轉換，其參數是一個表達式。

這個表達式通過AS關鍵字分割了兩個參數，分別表示原始數據類型和目標數據類型。

COALESCE函數的作用是什么？

返回第一個非空數值。

SELECT CAST(123.123 AS INT)的運行結果是什么？為什么？

運行結果會報錯。

CAST 函數在轉換數據類型的時候，不會四舍五入，如果原數值有小數，那么轉換為整數類型的時候就會報錯。

SELECT CAST(123.123 AS DECIMAL(8,2))的運行結果是什么？DECIMAL(8,2)是什么含義？

運行結果為 123.12。

DECIMAL(8,2)中，8代表整數部分和小數部分加起來最大的位數，2代表小數的位數。即：精度為8位，小數位數為2位的數據類型。

SELECT COALESCE(null,1,2)的運行結果是什么？

運行結果為 1。

約束

常見的約束有哪些？

常見的約束有七種：非空約束、唯一約束、主鍵約束、默認約束、外鍵約束、檢查約束、索引約束。

六種常見約束對應的關鍵字分別是什么？

約束	關鍵字
非空約束	NOT NULL
唯一約束	UNIQUE
主鍵約束	PRIMARY KEY
外鍵約束	FOREIGN KEY
默認約束	DEFAULT
檢查約束	CHECK
索引約束	INDEX

主鍵約束的作用是什么？

主鍵約束的作用是唯一標識一條記錄，不能重復，不能為空。即：UNIQUE+NOT NULL。

一個數據表的主鍵只能有一個，但是，主鍵可以是一個字段，也可以是多個字段組合。

外鍵約束的作用是什么？

外鍵約束確保了兩個表之間引用的完整性。一個表中的外鍵，對應另一張表中的主鍵。

普通索引和唯一性約束有什么區別？

唯一性約束相當于創建了一個約束和普通索引（NORMAL INDEX），普通索引只是提升數據的檢索速度，不是對字段的唯一性約束。

多表查詢

多表查詢

多表查詢分為哪幾類？

多表查詢分為四類：內連接查詢、外連接查詢、自連接查詢、聯合查詢。

內連接查詢

內連接查詢分為哪幾類？

內連接查詢分為兩類：隱式內連接查詢和顯式內連接查詢。

什么是隱式內連接查詢？

SELECT 字段列表 FROM 表1 , 表2 WHERE 條件 ... ;

什么是顯式內連接查詢？

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 連接條件 ... ;
#INNER可以省略

外連接查詢

外連接查詢分為哪幾類？

外連接查詢分為兩類：左外連接和右外連接。

什么是左外連接？

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 條件 ... ;

什么是右外連接？

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 條件 ... ;

現有表1、表2，兩張表，C是它們的交集，A、B分別是兩表不相交的部分數據，則表1左外連接表2時，查詢結果是什么？表1右外連接表2的結果是什么？

左外連接會將左表的全部數據以及兩表相交的部分數據查詢出來作為結果。

所以表1左外連接表2的結果是A+C。

同理可知，表1右外連接表2的結果是C+B。

自連接查詢

什么是自連接？

SELECT 字段列表 FROM 表A 別名A JOIN 表A 別名B ON 條件 ... ;

聯合查詢

什么是聯合查詢？

SELECT 字段列表 FROM 表名 ...
UNION [ALL]
SELECT 字段列表 FROM 表名 ...

子查詢

標量子查詢、列子查詢、行子查詢、表子查詢的區別是什么？

子查詢分為：標量子查詢、列子查詢、行子查詢、表子查詢。

它們的分類依據是查詢結果的格式。

即：

標量子查詢的結果是單個值；

列子查詢的結果是一列數據；

列行子查詢的結果是一行數據；

表子查詢的結果是多行多列的表。

子查詢相關的常用的關鍵字有哪些？

子查詢常用的關鍵字有：IN、NOT IN、ANY、SOME、ALL。

關聯子查詢和非關聯子查詢的區別是什么？

關聯子查詢和非關聯子查詢的區別在于是否執行多次子查詢。

非關聯子查詢：子查詢從數據表中查詢了數據結果，這個數據結果只執行一次，然后這個數據結果作為主查詢的條件進行執行。

關聯子查詢：子查詢需要執行多次，即采用循環的方式，先從外部查詢開始，每次都傳入子查詢進行查詢，然后再將結果反饋給外部。

什么是存在性檢測子查詢？

使用EXISTS關鍵字的子查詢即為存在性檢測子查詢。

什么是集合比較子查詢？

存儲引擎

MySQL中，常用的存儲引擎有哪些？

MySQL中常用的存儲引擎有：InnoDB、MyISAM、Memory、NDB、Archive等。

如何查看當前 MySQL 支持的存儲引擎都有哪些？

通過 SHOW ENGINES 命令來查看當前 MySQL 支持的存儲引擎都有哪些，以及這些存儲引擎是否支持事務。

如何選用存儲引擎？

1. 如果我們對數據表的完整性和正確性要求比較高，且需要較高的并發性能，則應選擇InnoDB。
2. 如果我們需要較多的插入和刪除操作，且不要求較高的并發性以及表的完整性，則可以選擇MyISAM。
3. 如果我們只需要緩存數據，且對查詢的響應速度要求較高，則可以使用Memory。

InnoDB

InnoDB的特點是什么？

InnoDB是MySQL5.5版本之后的默認引擎。

最大的特點有三個：

1. 支持事務、
2. 支持行級鎖定（提高并發性能）、
3. 支持外鍵約束。

MyISAM

MyISAM的特點是什么？

MyISAM是MySQL5.5之前的默認存儲引擎，特點是：

1. 不支持事務，
2. 不支持外鍵，
3. 但是訪問速度快且占用的內存少。

MyISAM的鎖機制只能到達表鎖級別，相對于InnoDB的行級鎖，并發性能會低一些。

sdi文件有什么用？

存儲表的結構信息

MYD文件有什么用？

存儲數據

MYI文件有什么用？

存儲索引

Memory

Memory的特點是什么？

Memory的特點是：

1. 使用系統內存作為存儲介質，支持Hash索引，所以可以得到更快的響應速度。
2. 但是，如果mysqld進程崩潰，則會導致所有數據丟失，

所以要慎重使用Memory作為存儲引擎。

其它存儲引擎

NDB的特點是什么？

NDB主要用于MySQL Cluster分布式集群環境。

Archive的特點是什么？

Archive有很好的壓縮機制，用于文件歸檔，在請求寫入時，會進行壓縮，常用來作為倉庫。

連接

內連接：將多個表之間滿足連接條件的數據行查詢出來。它包括了等值連接、非等值連接和自連接。外連接：會返回一個表中的所有記錄，以及另一個表中匹配的行。它包括了左外連接、右外連接和全連接。交叉連接：也稱為笛卡爾積，返回左表中每一行與右表中每一行的組合。在 SQL99 中使用的 CROSS JOIN。

NATURAL JOIN

CROSS JOIN

ON 連接

USING 連接

SQL92和SQL99中的連接有什么區別？

在 SQL92 中采用（+）代表從表所在的位置，而且在SQL92 中，只有左外連接和右外連接，沒有全外連接。

LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的標準中，在 SQL92 中不存在，只能用（+）表示。

SQL99 的外連接有哪些形式？

SQL99 的外連接包括了三種形式：

1. 左外連接：LEFT JOIN 或 LEFT OUTER JOIN
2. 右外連接：RIGHT JOIN 或 RIGHT OUTER JOIN
3. 全外連接：FULL JOIN 或 FULL OUTER JOIN

不同 DBMS 中使用連接需要注意的地方有哪些？

1. 不是所有的 DBMS 都支持全外連接
2. Oracle 沒有表別名 AS
3. SQLite 的外連接只有左連接

關于連接的性能問題有哪些需要注意？

1. 控制連接表的數量
2. 在連接時不要忘記 WHERE 語句
3. 使用自連接而不是子查詢

為什么要使用自連接而不是子查詢？

因為子查詢的判斷對象是未知的數據表；自連接的判斷對象是已知的數據表，

所以大部分 DBMS 中都對自連接處理進行了優化，在許多 DBMS 的處理過程中，對于自連接的處理速度要比子查詢快得多。

事務

事務

MySQL 5.5 版本之前，默認的存儲引擎是什么？為什么要改變默認的存儲引擎？

MySQL 5.5 版本之前，默認的存儲引擎是MyISAM。在 5.5 版本之后默認存儲引擎是 InnoDB。

InnoDB 取代 MyISAM 的重要原因，是因為InnoDB 支持事務，而MyISAM不支持事務。

事務的英文是什么？

事務的英文是transaction。

事務的特性是什么？

事務的特性：ACID

1. A：原子性（Atomicity），事務是不可分割的，它是進行數據處理操作的基本單位。
2. C：一致性（Consistency），數據庫在進行事務操作后，會由原來的一致狀態，變成另一種一致的狀態。當事務提交后，或者當事務發生回滾后，數據庫的完整性約束不能被破壞。
3. I：隔離性（Isolation），每個事務都是彼此獨立的，不會受到其他事務的執行影響。一個事務在提交之前，對其他事務都是不可見的。
4. D：持久性（Durability），事務提交之后對數據的修改是持久性的，即使在系統出故障比如系統崩潰或者存儲介質發生故障，數據的修改依然是有效的。

事務的常用控制語句都有哪些？

1. START TRANSACTION 或者 BEGIN，作用是顯式開啟一個事務。
2. COMMIT：提交事務。當提交事務后，對數據庫的修改是永久性的。
3. ROLLBACK 或者 ROLLBACK TO [SAVEPOINT]，意為回滾事務。意思是撤銷正在進行的所有沒有提交的修改，或者將事務回滾到某個保存點。
4. SAVEPOINT：在事務中創建保存點，方便后續針對保存點進行回滾。一個事務中可以存在多個保存點。
5. RELEASE SAVEPOINT：刪除某個保存點。
6. SET TRANSACTION，設置事務的隔離級別。

使用事務的方式有哪些？有什么區別？

使用事務有兩種方式：隱式事務和顯式事務。

1. 隱式事務：實際上就是自動提交。
2. 顯式事務：需要手寫 COMMIT 命令提交事務。

Oracle默認的事務使用方式是哪一種？MySQL默認的事務使用方式是哪一種？如何設置MySQL的事務提交方式？

Oracle默認的事務使用方式是顯式事務，即不自動提交，需要手動寫COMMIT命令來提交事務

MySQL默認的事務使用方式是隱式事務，即：自動提交。

如果要改變MySQL的事務提交方式，需要配置MySQL的參數：

mysql> set autocommit =0;  //關閉自動提交
mysql> set autocommit =1;  //開啟自動提交

事務相關參數

autocommit參數的作用有哪些？set autocommit =1;有什么作用？

MySQL 中，autocommit的默認值是0，。

autocommit參數有 2 種取值：

1. autocommit=0 時，不論是否采用 START TRANSACTION 或者 BEGIN 的方式來開啟事務，都需要用 COMMIT 進行提交，使用 ROLLBACK 對事務進行回滾。
2. autocommit=1 時，每條 SQL 語句都會自動進行提交。

所以，set autocommit =1;的作用是設置事務的默認提交方式為自動提交。

completion_type 參數的作用有哪些？SET @@completion_type = 1;有什么作用？

MySQL 中，completion_type 的默認值是0。

completion_type 參數有 3 種取值：

1. completion=0：默認情況。當我們執行 COMMIT 的時候會提交事務，在執行下一個事務時，還需要我們使用 START TRANSACTION 或者 BEGIN 來開啟。
2. completion=1：當我們提交事務后，相當于執行了 COMMIT AND CHAIN，開啟一個鏈式事務，即當我們提交事務之后，會自動開啟一個相同隔離級別的事務。
3. completion=2：也就是 COMMIT=COMMIT AND RELEASE，當我們提交后，會自動與服務器斷開連接。

所以SET @@completion_type = 1;的作用是開啟鏈式事務，即：提交了事務之后，自動開啟一個隔離級別相同的新事務。

事務隔離級別

如何查看隔離級別？

SELECT @@TRANSACTION_ISOLATION;

如何設置事務的隔離級別？

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

SESSION和GLOBAL有什么區別？

1. SEEION：會話期間有效。
2. GLOBAL：全局（所有會話）有效。

事務并發處理可能存在的三種異常是什么？

事務并發處理可能存在的三種異常：

1. 臟讀（Dirty Read）
2. 不可重復讀（Nonrepeatable Read）
3. 幻讀（Phantom Read）

四種事務隔離的級別分別是什么？

四種事務隔離的級別分別是：

1. 讀未提交（READ UNCOMMITTED）
2. 讀已提交（READ COMMITTED）
3. 可重復讀（REPEATABLE READ）
4. 串行化（SERIALIZABLE）

臟讀（Dirty Read）是什么？

讀到了其他事務還沒有提交的數據。

不可重復讀（Nonrepeatable Read）是什么？

對某數據進行讀取時，由于有其他事務對這個數據同時進行了修改或刪除，導致兩次讀取的結果不同。

幻讀（Phantom Read）是什么？

當事務A根據指定查詢條件對某數據多次進行讀取時，由于有其他事務更改了符合查詢條件的數據，導致事務A多次得到的數據的數量不同。

四種隔離級別之間的高低關系是什么？

串行化 > 可重復讀 > 讀已提交 > 讀未提交。

讀已提交能解決哪些并發處理異常？

讀已提交只能解決臟讀的問題，無法解決不可重復度和幻讀。

讀未提交能解決哪些并發處理異常？

讀未提交不能解決任何并發處理異常。

可重復讀能解決哪些并發處理異常？

可重復讀可以解決臟讀和不可重復讀問題，不能解決幻讀問題。

回滾日志和重做日志（這似乎屬于MySQL）

持久性是通過事務日志來保證的。當我們通過事務對數據進行修改的時候，首先會將數據庫的變化信息記錄到重做日志中，然后再對數據庫中對應的行進行修改。這樣做的好處是，即使數據庫系統崩潰，數據庫重啟后也能找到沒有更新到數據庫系統中的重做日志，重新執行，從而使事務具有持久性。

索引

索引分為哪幾類？

1. 主鍵索引：PRIMARY
2. 唯一索引：UNIQUE
3. 常規索引
4. 全文索引：FULLTEXT

可以創建多個主鍵索引么？可以創建多個唯一索引嗎？

主鍵索引只能有一個，但是唯一索引、常規索引、全文索引都可以有多個。

索引的優點是什么？缺點是什么？

優點：

1. 降低系統I/O，提高查詢速度
2. 可以根據索引列進行排序，降低了排序的成本

缺點：

1. 降低了UPDATE、INSERT和DELETE的消耗。
2. 維護索引需要一定的內存空間。

常見的索引類型？？？？？

常見的索引類型有哪些？

1. Hash索引：底層使用Hash表實現。Hash索引不支持排序，且必須精確匹配，范圍查詢時無效。
2. B+ Tree索引：最常見的索引類型，大部分引擎都支持B+Tree索引。
3. 全文索引（Full-text）
4. 空間索引（R-Tree）

Hash索引有什么缺點？

Hash索引無法匹配范圍查詢，只能匹配精確查詢。

Memory存儲引擎支持Hash索引。

InnoDB支持哪些索引？

InnoDB只支持B+Tree索引和Full-text索引，不支持Hash索引和R-Tree索引。

MyISAM支持哪些索引？

MyISAM支持B+Tree索引、R-Tree索引和Full-text索引，不支持Hash索引。

索引結構

常見的索引結構有哪些？

常見的索引結構有：

1. Hash表
2. 二叉樹
3. B- Tree樹
4. B+ Tree樹

Hash表的索引結構有什么優點和缺點？

優點：

1. 在不發生哈希沖突的前提下，只需要一次檢索即可查詢到數據，所以查詢速度很快

缺點：

1. 不支持排序
2. 不支持范圍查詢

二叉樹的索引結構有什么缺點和優點？

優點：

1. 不知道

缺點：

1. 按順序插入時，二叉樹會變成鏈表，降低查詢性能
2. 當數據量過大時，查詢深度會增加，降低查詢性能

什么是B樹的度？

B-Tree（B樹）和二叉樹有什么不同?

1. 二叉樹中，每個節點最多有兩個子節點；B-Tree中，每個節點可以有多個子節點。

B樹的葉子節點可以存放數據嗎？

B樹的葉子節點和非葉子節點都可以存放數據。

B樹和B+樹的區別是什么？

1. B樹的葉子節點和非葉子節點都可以存放數據
2. B+樹的葉子節點存放數據，非葉子節點存放索引

MySQL中的B+樹與普通的B+樹有什么區別？

MySQL中對B+樹進行了優化，在B+樹的葉子節點中，增加了指向相鄰葉子節點的指針，提高了訪問的性能。

InnoDB為什么選擇B+樹作為索引結構？

應該從為什么不選擇Hash表、B-樹、二叉樹的角度來回答這個問題：

相比于B+樹，

1. Hash表不支持范圍查詢，不支持排序，
2. B-樹中的數據和索引儲存在一起，會造成更多的內存消耗
3. 二叉樹則在數據量較大時，層級過多，造成讀取效率的降低，以及存在變成鏈表的可能。

聚集索引與非聚集索引

什么是聚集索引？什么是非聚集索引？聚集索引和非聚集索引有什么區別？

聚集索引的數據和索引保存在一起，非聚集索引的數據和索引是分離的。

在SQL中，一個表只能有一個聚集索引，但是可以有多個非聚集索引。

非聚集索引是如何查詢數據的？

非聚集索引的數據查詢需要回表。

如何選擇聚集索引？

1. 當存在主鍵時，選擇主鍵作為聚集索引
2. 當沒有主鍵，但是有唯一索引時，選擇第一個唯一索引作為聚集索引
3. 當既沒有主鍵，也沒有唯一索引時，InnoDB會自動創建一個rowid列作為隱藏的聚集索引

SQL中的索引語法

如何查詢索引？

SHOW INDEX FROM table_name;

如何創建索引？

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name(index_col_name,...)

如何刪除索引？

DROP INDEX index_name ON table_name;

最左前綴法則

什么是最左前綴法則？

最左前綴法則指聯合索引中，查詢從最左邊的列開始，如果跳過其中某些列，將會導致索引失效。

注意：只會導致后面的索引失效，之前的索引不受影響。

索引失效

索引在什么情況下會失效？

索引在這些情況下會失效：

1. 字段類型為字符串，但內容為數字，如：電話號碼、身份證等情況。如果WHERE子句中，沒有為這些字段加上引號''，將會導致索引失效。
2. OR
3. 模糊查詢
4. 對索引使用函數

索引的使用和設計原則

索引的設計原則有哪些？

1. 數據量過大時，一定要建立索引
2. 查詢頻率較高的多列，應該建立聯合索引
3. 當字段類型為字符串時，盡量使用前綴索引，而不是全索引
4. 列中盡量不要有空數據
5. 盡量選擇區分度高的列作為索引

SQL優化

對數據庫進行調優時，都有哪些維度可以選擇？

對數據庫調優時，可以從以下幾個角度入手：

1. 選擇合適的DBMS
2. 優化表設計
3. 優化邏輯查詢
4. 優化物理查詢
5. 使用Redis或Memcached作為緩存
6. 庫級優化

SQL性能分析

如何查看各種SQL語句的執行頻次？

SHOW GLOBAL STATUS LINKE 'COM_類型';

運行結果如下：

通過SHOW GLOBAL STATUS LIKE 'COM______';，我們可以了解到數據庫是以增刪改為主，還是以查詢為主，從而思考如何優化數據庫。

慢查詢日志

慢查詢日志的作用是什么？

慢查詢日志用于記錄執行時間超過指定限時（默認限時是10秒）的查詢語句。

如何設置限時的時長？

通過參數long_query_time設置，它的單位是秒，默認值是10秒。

如何查看慢查詢日志功能是否打開？

利用SQL語句：SELECT variables LIKE ‘slow_query_log’;來查看是否打開了慢查詢日志功能，參數值為ON或1時，即為打開的狀態。

SHOW profiles

Profile的作用是什么？

Profiles用于了解每條SQL語句的時間消耗。

如何設置Profile開啟？

SET [SESSION|GLOBAL] profiling=1;

如何查看Profile是否開啟？

SELECT @@profiling;

如何使用profiles文件查看每條SQL語句的時間消耗？

SHOW profiles;

注意：每次執行這個SQL語句時，結果表中的query_id都會變化。

如何查看某條SQL語句更具體的時間消耗？

SHOW profile for query n;

查看query_id為n的SQL語句的具體執行時間消耗。

explain

explain的作用是什么？如何使用？

用于查看表的索引、連接表等的信息。

使用方式如下：

EXPLAINS 查詢語句;

拓展

視圖

存儲過程

游標

SQL語句練習

查詢比平均薪資高的員工信息

select * from emp where salary > (select avg(salary) from emp)

查詢低于本部門平均工資的員工信息

SELECT * FROM emp e1 where e1.salary < (select avg(e2.salary) from emp e2 where e1.dept_id = e2.dept_id )

查詢 “研發部” 員工的平均工資

SELECT avg(salary) 
FROM emp 
where dept_id = (SELECT id from dept where name = '研發部')

查詢擁有員工的部門ID、部門名稱

SELECT distinct d.id ,d.name
FROM emp e join dept d on e.dept_id = d.id

查詢所有年齡大于40歲的員工, 及其歸屬的部門名稱; 如果員工沒有分配部門, 也需要展示出來

SELECT e.*,d.name '部門名稱'
FROM emp e left join dept d on e.dept_id = d.id
where e.age > 40

查詢所有員工的工資等級

SELECT  e.* ,s.id '薪資等級'
FROM emp e left join salgrade s on  e.salary > s.losal and e.salary <= s.hisal 

查詢主要定位或者次要定位是法師或是射手的英雄，同時英雄的上線時間不在 2016-01-01 到 2017-01-01 之間。

SQL：
SELECT name, role_main, role_assist, hp_max, mp_max, birthdate
FROM heros 
WHERE (role_main IN ('法師', '射手') OR role_assist IN ('法師', '射手')) 
AND DATE(birthdate) NOT BETWEEN '2016-01-01' AND '2017-01-01'
ORDER BY (hp_max + mp_max) DESC

查詢英雄最大生命值的最大值：

SQL：SELECT MAX(hp_max) FROM heros;

查詢英雄的名字，以及他們的名字字數：

SQL：SELECT CHAR_LENGTH(name), name FROM heros

查詢英雄上線日期（對應字段 birthdate）的年份，只顯示有上線日期的英雄即可（有些英雄沒有上線日期的數據，不需要顯示）：

SQL： SELECT name, EXTRACT(YEAR FROM birthdate) AS birthdate FROM heros WHERE birthdate is NOT NULL
#或
SQL: SELECT name, YEAR(birthdate) AS birthdate FROM heros WHERE birthdate is NOT NULL

查詢在 2016 年 10 月 1 日之后上線的所有英雄：

SQL： SELECT * FROM heros WHERE DATE(birthdate)>'2016-10-01'

查詢不同的生命最大值的英雄數量是多少：

SQL: SELECT COUNT(DISTINCT hp_max) FROM heros

統計不同生命最大值英雄的平均生命最大值，保留小數點后兩位：

SQL: SELECT ROUND(AVG(DISTINCT hp_max), 2) FROM heros

查找薪水最高的前 3 名員工。

SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 3;

統計每個部門（dept_id）的平均薪資，并僅顯示平均薪資高于 15000 的部門。

SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 15000;

查詢員工姓名（name）及其所屬部門名稱（dept_name），表結構為 employees（id, name, dept_id）和 departments（id, dept_name）。

SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id;

查詢沒有訂單的客戶（customers 表的 id 不在 orders 表的 customer_id 中）。

SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id
);