一、Mysql常見筆試題
1、Mysql 中有哪幾種鎖？
(1)表級鎖：開銷小，加鎖快。不會出現死鎖，鎖定粒度大，發生鎖沖突的概率高，并發度低。
(2)行級鎖：開銷大，加鎖慢。會出現死鎖，鎖定粒度小，發生鎖沖突的概率低，并發度高。
(3)頁面鎖：開銷時間、加鎖時間、鎖定粒度在 表級鎖 與 行級鎖 之間，會出現死鎖，并發度中等。
2、CHAR 與 VARCHAR 的區別？
(1)CHAR 長度不可變，范圍 1~255。若存儲長度未達到定義的長度，則以 空格 填充。存取速度快，但容易浪費空間。
(2)VARCHAR 長度可變，范圍 1~65535。若存儲長度未達到定義的長度，則存實際長度數據。存取速度稍慢，但節約空間。
3、ACID 屬性？
事務：數據庫中，對數據的一系列操作可以看成一個整體，稱為事務。這個整體要么全部執行、要么全部不執行。
ACID 屬性的存在確保了 事務的可靠。
(1)Actomicity(原子性)：原子性要求 事務中的操作要么全部完成，要么回退成之前未操作的狀態。即事務中某個操作失敗后，會相當于什么都沒發生，不會出現改了部分數據的情況。
(2)Consistency(一致性)：一致性要求 事務執行前后，數據庫的狀態一致，即從一個一致性狀態切換到另一個一致性的狀態。
(3)Isolation(隔離性)：隔離性要求 并發的事務相互隔離、不可見。即一個事務看不見另一個事務內部的操作以及操作的數據。
(4)Durability(持久性)：持久性要求 事務對數據庫數據的修改是永久的。即數據一旦修改提交后，其狀態將永久不變。
4、并發問題 -- 臟讀、不可重復讀、幻讀?
對于同時運行的多個事務，若這些事務訪問同一數據時，沒有采用必要的隔離機制，則會造成如下的并發問題。
(1)臟讀：臟讀 指的是當一個事務正在訪問某數據，并對這個數據進行的修改，且這條數據還未提交到數據庫中，此時若另一個事務也訪問到這條數據，獲取到的是這條被修改的數據，此時得到的數據不對，即臟讀。
比如：tom 年齡為 22，事務 A 修改 tom 年齡為 30，此時還未提交到數據庫，此時事務 B 獲取 tom 年齡，得到的是 30，事務 A 回滾數據，數據庫的數據依舊是 22，但事務 B 拿到的數據是 30，這就是臟讀，讀錯了數據。
(2)不可重復讀：指一個事務，多次讀取同一條數據，在這個事務還未結束時，另一個事務也訪問該數據并對其修改，那么可能造成事務多次讀取的數據不一致，即不可重復讀。
比如：tom 年齡為 22，事務 A 讀取 tom 年齡為 22，事務未結束。此時事務 B 修改 tom 年齡為 30，并提交到數據庫，當事務 A 再次讀取 tom 年齡為 30，事務 A 兩次讀取的數據不一致，即不可重復讀。
(3)幻讀：指事務并不是獨立執行時產生的現象。一個事務修改某個表，涉及表的所有行，同時另一個事務也修改表，比如增加或刪除一條數據。此時第一個事務發現多出或者少了一條數據。這種情況就是幻讀。
比如：事務 A 查詢當前表的數據總數為 11， 此時事務 B 向表中插入一條數據，事務 A 再次查詢當前表數據總數為 12，即幻讀。
注：
不可重復讀、幻讀理解起來有些類似。
不可重復讀是對一條數據操作，重點在于修改某條數據。
幻讀是對表進行操作，重點在于新增或刪除某條數據。
5、事務的隔離級別？
數據庫系統必須具有隔離并發運行的事務的能力，使各事務間不會相互影響，避免并發問題。
隔離級別：指的是一個事務與其他事務的隔離程度。隔離級別越高，則并發能力越弱。
(1)Read Uncommitted(讀未提交)：即讀取到 未提交的內容。
一般不使用。此隔離級別下，查詢不會加鎖，即可能存在兩個事務操作同一個表的情況。可能會導致 “臟讀”、“不可重復讀”、“幻讀”。
(2)Read Committed(讀提交)：即只能讀取到 已提交的內容。
常用(oracle、SQL Server 默認隔離級別)。此隔離級別下，查詢采用 快照讀 的機制，即不會讀取到未提交的數據，從而避免 “臟讀”，但是仍可能導致 “不可重復讀”、“幻讀”。
(3)Repeatable Read(可重復讀)
常用(mysql 默認隔離級別)。此隔離級別下，查詢采用 快照讀 的機制，且事務啟動后，當前數據不能被修改，從而可以避免 “不可重復讀”，但是仍可能導致 “幻讀”(新增或刪除某條數據)。
(4)Serializable(串行化)
一般不使用。此隔離級別下，事務會串行化執行(排隊執行)，執行效率差、開銷大。可以避免 “臟讀”、“不可重復讀”、“幻讀“。
【舉例：】
select @@transaction_isolation; -- 用于查看當前數據庫的隔離級別(8.0版本)
set session transaction isolation level read committed; --用于設置隔離級別為 read committed
6、Mysql 中表類型 MyISAM 與 InnoDB 的區別？
Mysql 采用 插件式的表存儲引擎 管理數據，基于表而非基于數據庫。
常見存儲引擎(表類型)：MyISAM 與 InnoDB。
(1)MyISAM：不支持事務，但是每次查詢都是原子的。支持表級鎖，即每次操作都是對整個表加鎖。存儲表的總行數。
(2)InnoDB：支持 ACID 屬性，支持事務的四種隔離級別。支持行級鎖以及外鍵約束。不存儲表的總行數。
7、自增主鍵、UUID？
(1)自增主鍵，數據在物理結構上是順序存儲，性能好，占用空間小。可以是 int 和 bigint 類型。int 4字節，bigint 8 字節，項目中理論不應出現 自增主鍵達到最大值的情況，因為數據太大，效率會大大降低，當出現一定的數據量后，應進行分庫分表操作。
(2)UUID，數據在物理結構上是隨機存儲，性能較差，占用空間大。唯一ID，絕不沖突。
8、mysql 的約束分類？
(1)約束的作用：是一種限制，用于限制表中的數據，為了保證數據的準確性以及可靠性。
(2)約束分類：
NOT NULL，非空，用于保證某個字段不為空。支持列級約束。
DEFAULT，默認，用于保證某個字段具有默認值。支持列級約束。
PRIMARY KEY，主鍵，用于保證某個字段具有唯一性且非空。支持列級約束以及表級約束。
UNIQUE，唯一，用于保證某個字段具有唯一性。支持列級約束以及表級約束。
FORGIEN KEY，外鍵，用于限制兩個表間的關系。支持表級約束。
注：
列級約束：指的是定義列的同時指定的約束。
表級約束：指的是列定義之后指定的約束。
外鍵常用于一對多的關系。即表的某條數據，對應另外一張表的多條數據。
將 “一” 的一方稱為 ：主表。將 “多” 的一方稱為 ：從表。
通常將 外鍵 置于從表上，即 從表上增加一列作為外鍵，并依賴于主表的某列。
【舉例：】
員工與部門間的關系。
一個部門可以有多個員工，而一個員工屬于一個部門。此時部門與員工間為 一對多 的關系。
部門表為主表，員工表為從表。外鍵建立在 員工表(從表)上。
CREATE TABLE dept (
-- 此處的 primary key 為 列級約束。
deptId int primary key auto_increment,
deptName varchar(20) not null
);
CREATE TABLE emp (
id int primary key auto_increment,
name varchar(32),
age int,
deptId int,
-- 此處的 foreign key 為表級約束。
foreign key(deptId) references dept(deptId)
);
9、drop、delete 與 truncate 的區別：
(1)格式：
drop table 表名; -- 用于刪除數據表。
truncate table 表名; -- 用于刪除數據表的數據，但保留表結構。
delete from 表名 [where 條件]; -- 用于刪除數據標的數據，但保留表結構，可回滾。
(2)delete 與 truncate 相比較：
delete 可以添加刪除條件，truncate 不可以。
delete 刪除后可以回滾，truncate 不可以。
delete 效率較低，truncate 效率較高。
delete 可以返回受影響的行數，truncate 沒有返回值。
delete 刪除數據后再次插入數據時，標識列從斷點處開始，truncate 標識列從 1 開始。
10、隱式事務、顯式事務？
隱式事務：事務沒有明顯的開啟與關閉的標志。比如 insert、delete、update等語句會自動提交。
顯式事務：事務具有明顯的開啟與關閉的標志，前提需禁用自動提交功能。
show variables like "autocommit"; -- 用于查看自動提交功能是否打開
set autocommit=1; -- 用于打開自動提交功能
set autocommit=0; -- 用于關閉自動提交功能
【顯式事務步驟：】
Step1：開啟事務，關閉自動提交功能。
set autocommit=0;
Step2：編寫事務語句。
select、insert、delete、update。
SAVEPOINT A; -- 可以設置回滾點
Step3：結束事務。
commit; -- 提交事務
rollback; -- 回滾事務
rollback to A; -- 回滾到回滾點
11、視圖
(1)視圖：是一種虛擬存在的表，其數據是使用視圖的過程中動態創建的數據，其只保存 sql 邏輯，不保存查詢的結果數據。
注：
可以理解成 java 的封裝好的一段方法，直接調用即可。
(2)格式：
【創建視圖】
CREATE VIEW 視圖名
AS
查詢語句;
【使用視圖(與使用普通表類似)】
SELECT *
FROM 視圖名
【舉例：】
CREATE VIEW testView
AS
SELECT *
FROM DEPT;
SELECT *
FROM testView;
【修改視圖：(方式一)】
-- 若視圖存在則修改，若視圖不存在則創建
CREATE OR REPLACE VIEW 視圖名
AS
查詢語句;
【修改視圖：(方式二)】
ALTER VIEW 視圖名
AS
查詢語句;
【刪除視圖：】
drop view 視圖名;
(3)好處：
可以重用 sql 語句。
簡化復雜的 sql 操作，不必清楚查詢細節。
保護數據，提高安全性。
12、變量
(1)系統變量：變量由系統提供。可以細分為 全局變量(global，針對數據庫的所有連接))以及 會話變量(session，默認，僅針對當前連接)。
【查看當前所有的變量：】
show [global | session] variables;
【查看部分變量：】
show [global | session] variables like '%transaction%';
【查看具體的變量：】
select @@[global | session].變量名;
【設置具體的變量】
set [global | session] 變量名 = 值;
或者
set @@[global | session].變量名;
(2)自定義變量：變量由用戶自定義。可以細分為 用戶變量(針對當前連接，聲明的位置任意)以及 局部變量(僅在begin ~ end 塊中使用，且聲明的位置為 begin ~ end 塊的第一句話)。
========================用戶變量===========================
【聲明用戶變量并賦值：(三種方式)】
set @變量名=值;
set @變量名:=值;
select @變量名:=值;
【賦值給用戶變量：(通過select into)】
select 字段 into @變量名
from 表;
【查看用戶變量值：】
select @變量名;
【舉例：】
select count(*) into @count
from dept;
select @count;
========================局部變量===========================
【聲明局部變量：】
declare 變量名 類型;
declare 變量名 類型 default 值;
【局部變量賦值：(先聲明再賦值，直接賦值會出錯)】
set 變量名=值;
set 變量名:=值;
select 字段 into 變量名
from 表;
【查看用戶變量值：】
select 變量名;
13、存儲過程
(1)存儲過程：
指的是 一組預先編譯好的 sql 語句的集合，可以理解成批處理語句。類似于 Java 中的方法，使用時調用方法名即可。
(2)好處：
提高了代碼的重用性。
簡化操作。
減少了編譯次數、與數據庫交互的次數，提高了效率。
(3)語法：
【創建存儲過程：】
DELIMITER $
CREATE PROCEDURE 存儲過程名(參數列表)
BEGIN
存儲過程體(一組合法的 sql 語句)
END $
DELIMITER ;
注：
參數列表分三個部分，分別為 參數模式、參數名、參數類型
參數模式：IN、OUT、INOUT。
IN：指該參數可以作為輸入，即接收值(默認)。
OUT：指該參數可以作為輸出，即返回值。
INOUT：指該參數即可作為輸入、又可作為輸出。
存儲過程體中每條語句必須以分號 ; 結尾。
DELIMITER 用于設置結束標記，用于存儲過程末尾，執行到標記處則存儲過程結束。
【調用存儲過程：】
CALL 存儲過程名(參數列表);
【刪除存儲過程：】
DROP PROCEDURE 存儲過程名;
【查看存儲過程結構：】
SHOW CREATE PROCEDURE 存儲過程名;
(4)舉例：
# 創建一個 user 表，若已經存在該表，先刪除
DROP TABLE IF EXISTS user;
CREATE TABLE user(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
password VARCHAR(20)
);
# 用于設置結束標記，此處 sql 結束標記有 ; 改為 $
DELIMITER $
# 創建存儲過程(無參)，用于向 user 表中插入 5 個數據
DROP PROCEDURE IF EXISTS user_no_parameter $
CREATE PROCEDURE user_no_parameter()
BEGIN
INSERT INTO USER(name, password) VALUES('tom', 'tom123'),('jarry', 'jarry123'),('jack', 'jack123'),('tim', 'tim123'),('rose', 'rose123');
END $
# 創建有參存儲過程，根據輸入的用戶名，找到相應的密碼，并返回該用戶的 id。
DROP PROCEDURE IF EXISTS user_parameter $
CREATE PROCEDURE user_parameter(IN name VARCHAR(20), OUT password VARCHAR(20), INOUT id INT)
BEGIN
SELECT user.password, user.id INTO password, id
FROM user
WHERE user.name = name;
END $
# 用于設置結束標記，此處 sql 結束標記有 $ 改為 ;
DELIMITER ;
# 調用無參存儲過程
CALL user_no_parameter();
# 查看當前表數據
SELECT * FROM user;
# 調用有參存儲過程
set @id = 0;
CALL user_parameter('jack', @password, @id);
# 查看有參存儲過程執行后的結果
SELECT @password, @id;
14、函數
(1)函數：
與存儲過程類似，也是一組預先編譯好的 sql 語句的集合。
注：
與存儲過程的區別：
存儲過程可以沒有返回值，可以有多個返回值，適合進行批處理(批量插入、刪除等)。
函數有且僅有一個返回值，一般用于處理數據并返回一個結果。
(2)語法：
【創建函數：】
DELIMITER $
CREATE FUNCTION 函數名(參數列表) RETURNS 返回類型
BEGIN
函數體(一組合法的 sql 語句)
END $
DELIMITER ;
注：
參數列表分兩個部分，分別為 參數名、參數類型。
函數體必須包含 return 語句。
【調用函數：】
SELECT 函數名(參數列表);
【查看函數：】
DROP FUNCTION 函數名;
【刪除函數：】
SHOW CREATE FUNCTION 函數名;
(3)舉例：
# 用于設置結束標記，此處 sql 結束標記有 ; 改為 $
DELIMITER $
# 創建無參函數，若函數已存在，則先刪除再創建
DROP FUNCTION IF EXISTS test1 $
CREATE FUNCTION test1() RETURNS INT
BEGIN
DECLARE a INT DEFAULT 10;
DECLARE b INT DEFAULT 10;
return a + b;
END $
# 創建有參函數，若函數已存在，則先刪除再創建
DROP FUNCTION IF EXISTS test2 $
CREATE FUNCTION test2(a INT, b INT) RETURNS INT
BEGIN
RETURN a - b;
END $
# 用于設置結束標記，此處 sql 結束標記有 $ 改為 ;
DELIMITER ;
# 調用無參函數
SELECT test1();
# 調用有參函數
SELECT test2(20, 10);
注：
若出現錯誤 ERROR 1418 (HY000)，修改 log_bin_trust_function_creators 值即可。
【錯誤：】
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
【解決：】
SET GLOBAL log_bin_trust_function_creators = 1;
15、流程控制語句
(1)IF 語句
【語法1：(IF 函數)】
IF(表達式1, 結果1, 結果2);
注：
表達式 1 成立，則返回 結果1.
表達式 1 不成立，則返回 結果2.
【語法2：需要寫在 BEGIN - END 中】
BEGIN
IF 表達式 1 THEN 結果 1;
ELSEIF 表達式 2 THEN 結果 2;
ELSE 結果 3;
END IF;
END
【舉例：】
DELIMITER $
DROP FUNCTION IF EXISTS test3 $
CREATE FUNCTION test3() RETURNS INT
BEGIN
IF 2 > 3 THEN RETURN 3;
ELSEIF 2 > 4 THEN RETURN 4;
ELSE RETURN 5;
END IF;
END $
DELIMITER ;
SELECT test3() value1, IF(2 > 3, 2, 3) value2;
(2)CASE 語句
【語法1：相當于 Java 中的 switch 語句】
CASE 表達式 | 變量
WHEN 值 1 THEN 返回的結果 1
WHEN 值 2 THEN 返回的結果 2
ELSE 返回的結果 3
END
【語法2：相當于 Java 中的 IF - ELSE 語句】
CASE
WHEN 表達式 1 THEN 返回的結果 1
WHEN 表達式 2 THEN 返回的結果 2
ELSE 返回的結果 3
END
【舉例：】
SELECT (
CASE 2 + 3
WHEN 2 THEN 2
WHEN 3 THEN 3
ELSE 4
END
) value1,
(
CASE
WHEN 2 > 3 THEN 2
WHEN 2 < 3 THEN 3
ELSE 4
END
) value2;
(3)循環
【分類：需要放在 BEGIN - END 里】
while、loop、repeat
【循環標志：】
iterate： 類似于 Java 中的 continue，結束本次循環，進行下一次循環。
leave： 類似于 Java 中的 break，結束當前所有的循環。
【while 語法：(先判斷再執行循環)】
BEGIN
[標簽：] WHILE 循環條件 DO
循環體;
END WHILE [標簽];
END
【loop 語法：(沒有條件的死循環，需使用 leave 退出)】
BEGIN
[標簽：] LOOP
循環體;
END LOOP [標簽];
END
【repeat 語法：(先執行循環再判斷)】
BEGIN
[標簽：] REPEAT
循環體;
UNTIL 結束循環的條件
END REPEAT [標簽];
END
【舉例：】
DELIMITER $
DROP PROCEDURE IF EXISTS test1 $
CREATE PROCEDURE test1(OUT a INT, OUT b INT, OUT c INT)
BEGIN
# 測試 while 循環，temp >= 10 時退出循環。
DECLARE temp INT DEFAULT 0;
testWhile: WHILE temp < 10 DO
SET temp = temp + 1;
# 當 temp = 8 時，給 a 賦值并退出 while 循環
IF temp = 8 THEN SET a = temp; LEAVE testWhile;
END IF;
END WHILE testWhile;
# 測試 repeat 循環， temp >= 10 時退出循環。
SET temp = 0;
testRepeat: REPEAT
SET temp = temp + 1;
# 當 temp = 7 時，給 b 賦值并退出 repeat 循環
IF temp = 7 THEN SET b = temp; LEAVE testRepeat;
END IF;
# 注意 until 是循環結束條件
UNTIL temp >= 10
END REPEAT testRepeat;
# 測試 loop 循環
SET temp = 0;
testLoop: LOOP
SET temp = temp + 1;
# 當 temp = 6 時，給 c 賦值并退出 loop 循環
IF temp = 6 THEN SET c = temp; LEAVE testLoop;
END IF;
END LOOP testLoop;
END $
DELIMITER ;
CALL test1(@a, @b, @c);
SELECT @a, @b, @c;
)
