mysql常見筆試題-有解無憂

一、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;

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/72694.html

標籤：MySQL

上一篇：MySQL 索引

下一篇：mysql-調優