天天看點
傳回

SQL語句大全

參考網址

https://www.lintcode.com/

LEVEL1

(1)輸出 Hello SQL：

SELECT "Hello SQL!";

(2)使用 SELECT DISTINCT 查詢不同行：

SELECT DISTINCT column_name　FROM table_name；

(3)使用 SELECT WHERE 對行進行篩選過濾：常用的有等于 =、小于 < 、大于 > 、不等于<> 或 !=

(4)使用 INSERT INTO 在不指定列的情況下插入資料:

INSERT INTO `table_name` 
  VALUES (value1, value2, value3,...);

(5)使用 INSERT INTO 在指定的列中插入資料:

INSERT INTO `table_name`
  (`column1`, `column2`, `column3`,...) 
  VALUES (value1, value2, value3,...);

(6)使用 UPDATE 更新資料：

UPDATE `table_name`　
  SET `column1`=value1,`column2`=value2,...　
  WHERE `some_column`=some_value;

(7)使用 DELETE 删除資料:

DELETE FROM `table_name`
   WHERE `some_column` = `some_value`;

LEVEL2

(1) 比較運算符:

A operator B

,常用的比較運算符有 =（等于） 、!=（不等于）、 <>（不等于）、<（小于）、<=（小于等于）、>（大于）、>=（大于等于），其中 != 和 <> 在特殊情況下用法是不同的，這裡暫時不提。

(2)使用 AND 連接配接多條件:

SELECT `column_name` 
  FROM `table_name` 
  WHERE condition1 AND condition2;

(3)使用 OR 連接配接多個條件:

SELECT `column_name` 
  FROM `table_name` 
  WHERE condition1 or condition2;

(4)使用 NOT 過濾不滿足條件的資料

SELECT `column_name` 
  FROM `table_name` 
  WHERE NOT `condition`;
//舉例
  SELECT *
  FROM `teachers`
  WHERE NOT (`age` > 20 AND `country` = 'CN');

(5)使用 IN 查詢多條件:當我們需要查詢單個表條件過多時，就會用多個 'OR' 連接配接或者嵌套，這會比較麻煩，現在我們有 'IN' 能更友善的解決這一問題。

SELECT *
  FROM `table_name`
  WHERE `column_name` IN `value`;
//舉例
  SELECT *
  FROM `teachers`
  WHERE `country` IN ('CN', 'UK');

(6）使用 NOT IN 排除

SELECT *
  FROM `table_name`
  WHERE `column_name` NOT IN value;

（7）使用 BETWEEN AND 查詢兩值間的資料範圍:

  • BETWEEN AND 會選取介于兩個值之間的資料範圍。這些值可以是數值、文本或者日期。
  • 請注意，在不同的資料庫中，BETWEEN 操作符會産生不同的結果！
  • 在某些資料庫中，BETWEEN 選取介于兩個值之間但不包括兩個測試值的字段。
  • 在某些資料庫中，BETWEEN 選取介于兩個值之間且包括兩個測試值的字段。
  • 在某些資料庫中，BETWEEN 選取介于兩個值之間且包括第一個測試值但不包括最後一個測試值的字段。
  • 是以，請檢查您的資料庫是如何處理 BETWEEN 操作符！
  • 我們這裡選用的是 MySQL 的支援，BETWEEN 選取介于兩個值之間且包括兩個測試值的字段，即BETWEEN 200 AND 250 選取結果會包括 200 和 250 
　SELECT *
　FROM `table_name`
　WHERE `column_name` BETWEEN `value` AND `value`;

（8）使用 IS NULL 查詢空資料：NULL 值代表遺漏的未知資料。預設的，表的列可以存放 NULL 值。

注意：

  • NULL 用作未知的或不适用的值的占位符。
  • 無法比較 NULL 和 0；它們是不等價的。
  • 無法使用比較運算符來測試 NULL 值，比如 =、!= 或 <>。
  • 我們必須使用 IS NULL 和 IS NOT NULL操作符。 
SELECT *
  FROM `table_name`
  WHERE `column_name` IS NULL;

(9)使用 LIKE 模糊查詢

NUM 通配符 描述
1 % 替代 0 個或多個字元
2 _ 替代一個字元
3 [charlist] 字元列中的任何單一字元
4 或 [!charlist] 不在字元列中的任何單一字元 
SELECT *
FROM `table_name`
WHERE `column_name` LIKE  `value`;

(10)使用 ORDER BY 對資料進行排序

ORDER BY 關鍵字用于對結果集按照一個列或者多個列進行排序，其具有 ASC（升序）和 DESC（降序）兩個關鍵字，且預設按照升序排列。

  • ASC ：按升序排列，ORDER BY 預設按照升序對記錄進行排序，是以升序的關鍵字 ASC 可以省去不寫。
  • DESC：按降序排列，如果需要按照降序對記錄進行排序，可以使用 DESC 關鍵字。 
SELECT `column_name`, `column_name`
FROM `table_name`
ORDER BY `column_name`, `column_name` ASC|DESC;

(11)使用 LIMIT 限制輸出行數:

LIMIT 子句用于 SELECT 中，對輸出結果集的行數進行限制，LIMIT 接收2個參數 offset 和 count，兩個參數都是整型數字，但通常隻用一個。

  • offset ：是傳回集的初始标注，起始點是0，不是1
  • ount ：制定傳回的數量
  • LIMIT 關鍵字的位置，需要放在 ORDER BY 關鍵字的後面，否則會報錯。 
SELECT `column_name`, `column_name`
FROM `table_name`
LIMIT `offset` , `count`;

LEVEL3

算數函數（1）

(1) 使用 AVG() 函數求數值列的平均值:

  • 平均函數 AVG() 是平均數 AVERAGE 的縮寫，它用于求數值列的平均值。它可以用來傳回所有列的平均值，也可以用來傳回特定列和行的平均值。

  • 具體的計算過程為：其通過對表中行數計數并計算特定數值列的列值之和，求得該列的平均值。

    *　但是當參數 ｀column_name｀ 列中的資料均為空時，結果會傳回 NULL。

SELECT AVG(`column_name`) 
  FROM `table_name`;
//舉例，其中 AS 關鍵字的作用是賦予 AVG(student_count) 計算結果列顯示在清單中的别名。
  SELECT AVG(`student_count`) AS `average_student_count`
  FROM `courses`;

何為别名？

别名是一個字段或值的替換名，由關鍵字 AS 賦予。别名還有其他用途，常見的用途包括在實際的表列名包含不符合規定的字元（如空格）時重新命名它，在原來的表列名含糊或者容易誤解時擴充它等。

别名常與函數聯用，給使用函數之後的新計算列一個名字，友善我們檢視和使用。我們會在後續的學習中經常見到它。

(2)使用 MAX() 函數傳回指定列中的最大值:它隻有一個參數 column_name ，表示指定的列名。但是當參數 column_name 列中的資料均為空時，結果會傳回 NULL。

SELECT MAX(`column_name`) 
  FROM `table_name`;

(3)使用 MIN() 函數傳回指定列中的最小值

SELECT MIN(`column_name`) 
  FROM `table_name`;

(4)使用 SUM() 函數統計數值列的總數:

SELECT SUM(`column_name`) 
  FROM `table_name`;

算數函數（2）

(1) 使用 ROUND() 函數将數值四舍五入：

ROUND()

函數用于把數值字段舍入為指定的小數位數。

  • ROUND(X)

     :傳回參數 X 四舍五入後的一個整數。

  • ROUND(X, D)

     :傳回參數 X 四舍五入且保留 D 位小數後的一個數字。如果 D 為 0，結果将沒有小數點或小數部分。

  • ROUND()

     傳回值資料類型會被變換為一個 BIGINT 。 
SELECT ROUND(`column_name`, `decimals`) 
  FROM `table_name`;

(2)使用 NULL() 函數判斷空值

  • ISNULL():

    ISNULL()

     函數用于判斷字段是否為 NULL，它隻有一個參數

    column_name

    為列名，根據

    column_name

     列中的字段是否為 NULL 值傳回 0 或 1。 
SELECT ISNULL(`column_name`)
  FROM `table_name`;
  • IFNULL():

    IFNULL()

     函數也用于判斷字段是否為NULL，但是與

    ISNULL()

    不同的是它接收兩個參數，第一個參數

    column_name

     為列名，第二個參數

    value

    相當于備用值。

    其中：

    (1) 如果 column_name 列中的某個字段是 NULL 則傳回 value 值，不是則傳回對應内容。

    (2) COALESCE(column_name, value) 函數也用于判斷字段是否為NULL，其用法和 IFNULL() 相同。

SELECT IFNULL(`column_name`, `value`)
  FROM `table_name`;
//舉例
  SELECT `name`, `email`, ISNULL(`email`), IFNULL(`email`, 0), COALESCE(`email`, 0) 
  FROM `teachers`;

(3)使用 COUNT() 函數計數:當

COUNT()

中的參數不同時，其的用途也是有明顯的不同的，主要可分為以下三種情況：COUNT(column_name) 、COUNT( * ) 和 COUNT(DISTINCT column_name) 。

  • COUNT( column_name ):

    • COUNT(column_name) 函數會對指定列具有的行數進行計數，但是會除去值為 NULL 的行。該函數主要用于檢視各列資料的數量情況，便于統計資料的缺失值。

      *　假如出現某一列的資料全為 NULL 值的情況，使用COUNT( column_name ) 函數對該列進行計數，會傳回 0。

SELECT COUNT(`column_name`) 
  FROM `table_name`;
  • COUNT():COUNT() 函數會對表中行的數目進行計數，包括值為 NULL 所在行和重複項所在行。該函數主要用于檢視表中的記錄數。 
SELECT COUNT(*) 
  FROM `table_name`;

注意：COUNT(column_name) 與 COUNT(*) 的差別

  • COUNT(column_name)

     中，如果

    column_name

     字段中的值為 NULL，則計數不會增加，而如果字段值為空字元串""，則字段值會加 1

  • COUNT(*)

     中，除非整個記錄全為

    NULL

     ，則計數不會增加，如果存在某一個記錄不為

    NULL

     ，或者為空字元串""，計數值都會加 1。正常來說，表都會有主鍵，而主鍵不為空，是以

    COUNT(*)

     在有主鍵的表中等同于

    COUNT(PRIMARY_KEY)

     ，即查詢有多少條記錄。 
SELECT COUNT(*) 
  FROM `table_name`;

時間函數（1）

（1）使用 NOW() 、 CURDATE()、CURTIME() 擷取目前時間

  • NOW()

     可以用來傳回目前日期和時間 格式：YYYY-MM-DD hh:mm:ss

  • CURDATE()

     可以用來傳回目前日期 格式：YYYY-MM-DD

  • CURTIME()

     可以用來傳回目前時間 格式：hh:mm:ss 
//舉例  使用 NOW() 向記錄表 records 中插入目前的時間（精确到毫秒）
 INSERT INTO `records` VALUES (NOW(3));

(4)使用 DATE()、TIME() 函數提取日期和時間

使用

DATE()

、

TIME()

函數分别将 '2021-03-25 16:16:30' 這組資料中的日期于時間提取出來，并用 date 、time 作為結果集列名。

SELECT DATE('2021-03-25 16:16:30') AS `date`,TIME('2021-03-25 16:16:30')  AS `time`;

運作結果如下：

date time
2021-03-25 16:16:30
(5)使用 EXTRACT() 函數提取指定的時間資訊:

EXTRACT()

 函數用于傳回日期/時間的單獨部分，如 YEAR (年)、MONTH (月)、DAY (日)、HOUR (小時)、MINUTE (分鐘)、 SECOND (秒)。 
SELECT EXTRACT(unit FROM date)
  FROM `table`
  • date 參數是合法的日期表達式。
  • unit 參數是需要傳回的時間部分，如

    YEAR

    MONTH

    DAY

    HOUR

    MINUTE

    SECOND

     等。
  • 在一般情況下，

    EXTRACT(unit FROM date)

     與

    unit()

     的結果相同。 
//舉例
  SELECT `name`, EXTRACT(HOUR FROM `created_at`) AS `created_hour`
  FROM `courses`;

（6）使用 DATE_FORMAT() 格式化輸出日期:

SELECT DATE_FORMAT(date,format);
  • 我們在 SQL 中使用 DATE_FORMAT() 方法來格式化輸出 date/time。

  • 需要注意的是 DATE_FORMAT() 函數傳回的是字元串格式。

    舉例：

SELECT DATE_FORMAT(`created_at`, '%Y %m') AS `DATE_FORMAT`
FROM `courses`;

其中

%m

表示月份，

%d

表示日期，

%Y

表示年份，

%w

表示星期。

時間函數（2）

（1）使用 DATE_ADD() 增加時間：

DATE_ADD()

函數是常用的時間函數之一，用于向日期添加指定的時間間隔

SELECT DATE_ADD(date, INTERVAL expr type)
  FROM table_name
  • date 指代希望被操作的有效日期，為起始日期
  • expr 是希望添加的時間間隔的數值（expr 是一個字元串，對于負值的間隔，可以以 ”-“ 開頭）
  • type 是具體的資料類型，表示加上時間間隔的機關（可以是 MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , YEAR 等） 
//舉例
SELECT `name`, DATE_ADD(`created_at`, INTERVAL 1 YEAR) AS `new_created`
     FROM `courses`;

(2)使用 DATE_SUB() 減少時間:

DATE_SUB()

函數是常用的時間函數之一，用于從日期減去指定的時間間隔。它與

DATE_ADD()

函數具有相似的用法。

SELECT DATE_SUB(date, INTERVAL expr type)
FROM table_name
  • date 指代希望被操作的有效日期
  • expr 是希望添加的時間間隔
  • type 是具體的資料類型（可以是 MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , YEAR 等）

(3)使用時間函數 DATEDIFF() 和 TIMESTAMPDIFF() 計算日期差:DATEDIFF() 常用的日期差，在 MySQL 中預設隻能計算天數差。

示例代碼

[3.1] DATEDIFF()

  • DATEDIFF()

     用法：

    DATEDIFF（時間1，時間2）
  • 代碼文法:

    SELECT DATEDIFF(時間1,時間2) AS date_diff FROM courses;

  • DATEDIFF()

     內插補點計算規則：時間 1 - 時間 2,

    date_diff

    為傳回結果列名稱

    舉例:

SELECT DATEDIFF(created_at,'2018-01-13') AS date_diff FROM courses;

[3.2] TIMESTAMPDIFF()

  • 查詢功能多的 MySQL 自帶的日期函數，可以計算兩個日期相差的年(YEAR，時間1,時間2)，月(MONTH，時間1,時間2)，周(WEEK，時間1,時間2)，日(DAY，時間1,時間2)，小時(HOUR，時間1,時間2)。

  • TIMESTAMPDIFF()

    TIMESTAMPDIFF (類型,時間1,時間2)

  • SELECT TIMESTAMPDIFF (類型,時間1,時間2) AS year_diff；

  • TIMESTAMPDIFF()

    內插補點計算規則：時間 2 - 時間 1 
SELECT name AS `courses_name` ,created_at AS `courses_created_at`,TIMESTAMPDIFF(YEAR, `created_at`, '2021-04-01') AS `year_diff`
FROM `courses`;

限制

（1）限制：在 SQL 中，限制是規定表中的資料規則。若存在違反限制的行為，行為就會阻止。它能幫助管理者更好地管理資料庫，并且確定資料庫中資料的正确性和有效性。例如在背景的資料庫設計中對要輸入的資料進行核查或判斷，再決定是否寫入資料庫，這都是限制的應用。

（2）非空限制 NOT NULL：NOT NULL 限制強制列不接受 NULL 值，強制字段始終包含值，這意味着，如果不向字段添加值，就無法插入新紀錄或者更新記錄。

//舉例
  CREATE TABLE `Persons` (
     `ID` int NOT NULL,
     `LastName` varchar(255) NOT NULL,
     `FirstName` varchar(255) NOT NULL,
     `Age` int
  );
  • 不要把 NULL 值與空串相混淆。NULL 值是沒有值，
  • 它不是空串。如果指定' '（兩個單引号，其間沒有字元），這
  • 在 NOT NULL 列中是允許的。空串是一個有效的值，它不是無值。
  • NULL 值用關鍵字 NULL 而不是空串指定。

(3)唯一限制 UNIQUE

  • UNIQUE 限制唯一辨別資料庫表中的每條記錄
  • UNIQUE 和 主鍵限制均為列或列集合提供了唯一性的保證

  • 主鍵限制會自動定義一個 UNIQUE 限制，或者說主鍵限制是一種特殊的 UNIQUE 限制。但是二者有明顯的差別：每個表可以有多個 UNIQUE 限制，但隻能有一個主鍵限制。

    用法一：CREATE TABLE 時的 UNIQUE 限制

//(1)MySQL
  CREATE TABLE `Persons`
  (
  `P_Id` int NOT NULL,
  ......
  UNIQUE (`P_Id`)
  )
//(2)SQL Server / Oracle / MS Access
  CREATE TABLE `Persons`
  (
  `P_Id` int NOT NULL UNIQUE,
  ......
  )
//(3)MySQL / SQL Server / Oracle / MS Access
//命名 UNIQUE 限制，并定義多個列的 UNIQUE 限制:
  CREATE TABLE `Persons`
  (
   `P_Id` int NOT NULL,
   `LastName` varchar(255) NOT NULL,
    ......
   CONSTRAINT uc_PersonID UNIQUE (`P_Id`,`LastName`)
  )

用法二：ALTER TABLE 時的 UNIQUE 限制

//(1)MySQL / SQL Server / Oracle / MS Access
  ALTER TABLE `Persons`
  ADD UNIQUE（`P_Id`）
//(2)MySQL / SQL Server / Oracle / MS Access 定義多個列的 UNIQUE 限制
  ALTER TABLE `Persons`
  ADD CONSTRAINT uc_PersonID UNIQUE (`P_Id`,`LastName`)

用法三:撤銷 UNIQUE 限制

//(1)MySQL
  ALTER TABLE `Persons`
  DROP INDEX uc_PersonID
//(2)SQL Server / Oracle / MS Access
  ALTER TABLE `Persons`
  DROP CONSTRAINT uc_PersonID

(4)主鍵限制 PRIMARY KEY

  • PRIMARY KEY 限制唯一辨別資料庫表中的每條記錄 ，簡單的說，PRIMARY KEY = UNIQUE + NOT NULL
  • 從技術的角度來看，PRIMARY KEY 和 UNIQUE 有很多相似之處。但還是有以下差別：
    • NOT NULL UNIQUE 可以将表的一列或多列定義為唯一性屬性，而 PRIMARY KEY 設為多列時，僅能保證多列之和是唯一的，具體到某一列可能會重複。

    • RIMARY KEY 可以與外鍵配合，進而形成主從表的關系，而 NOT NULL UNIQUE 則做不到這一點

      如：

      表一：使用者 id (主鍵)，使用者名

      表二: 銀行卡号 id (主鍵)，使用者 id (外鍵)

      則表一為主表，表二為從表。

  • 更大的差別在邏輯設計上。

    PRIMARY KEY

     一般在邏輯設計中用作記錄辨別，這也是設定

    PRIMARY KEY

     的本來用意，而

    UNIQUE

    隻是為了保證域/域組的唯一性。

    用法一:CREATE TABLE 時 添加 PRIMARY KEY 限制

//(1)MYSQL
  CREATE TABLE `Persons`
  (
    `P_Id` int NOT NULL,
    ......
    PRIMARY KEY (`P_Id`)
  );
//(2)SQL Server / Oracle / MS Access
  CREATE TABLE `Persons`
  (
    `P_Id` int NOT NULL PRIMARY KEY,
    ......
  )
//(3)如需命名并定義多個列的 PRIMARY KEY 限制，請使用下面的 SQL 文法：
  CREATE TABLE `Persons`
  (
     `P_Id` int NOT NULL,
     `LastName` varchar(255) NOT NULL,
     ......
     CONSTRAINT pk_PersonID PRIMARY KEY (`P_Id`,`LastName`)
  )

在上面的執行個體（3）中，隻有一個主鍵 PRIMARY KEY（pk_PersonID）。然而，pk_PersonID 的值是由兩個列（P_Id 和 LastName）組成的。

用法二：ALTER TABLE 時添加主鍵限制

//(1)MySQL / SQL Server / Oracle / MS Access
  ALTER TABLE `Persons`
  ADD PRIMARY KEY (`P_Id`)
//(2)MySQL / SQL Server / Oracle / MS Access
//如需命名并定義多個列的 PRIMARY KEY 限制
  ALTER TABLE `Persons`
  ADD CONSTRAINT pk_PersonID PRIMARY KEY (`P_Id`,`LastName`)

用法三：撤銷 PRIMARY KEY

如需撤銷 PRIMARY KEY 限制，我們可以通過将上述 ALTER TABLE 和 DROP 實作：

//(1)MYSQL
  ALTER TABLE `Persons`
  DROP PRIMARY KEY
//(2)SQL Server / Oracle / MS Access
  ALTER TABLE `Persons`
  DROP CONSTRAINT pk_PersonID

(5)外鍵限制 FOREIGN KEY

外鍵概念：一個表中的 FOREIGN KEY 指向另一個表中的 UNIQUE KEY 。

讓我們看了例子，如果一個字段 X 在一張表（表 1 ）中是關鍵字，而在另一張表（表 2 ）中不是關鍵字，則稱字段 X 為表 2 的外鍵。

外鍵作用：外鍵最根本的作用：保證資料的完整性和一緻性。這麼說可能有些同學無法了解，接下來通過一個例子來深入了解一下。

外鍵限制:外鍵限制是指用于在兩個表之間建立關系，需要指定引用主表的哪一列。

用法一：CREATE TABLE 時的 SQL FOREIGN KEY 限制

//(1)MySQL 在 "Orders" 表建立時在 "P_Id" 列上建立 FOREIGN KEY 限制：
CREATE TABLE `Orders`
(
`O_Id` int NOT NULL,
`OrderNo` int NOT NULL,
`P_Id` int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
//(2)SQL Server / Oracle / MS Access
CREATE TABLE `Orders`
(
`O_Id` int NOT NULL PRIMARY KEY,
`OrderNo` int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

其中,NOT NULL 表示該字段不為空.REFERENCES 表示 引用一個表.

如需命名 FOREIGN KEY 限制，并定義多個列的 FOREIGN KEY 限制:其中,CONSTRAINT 表示限制，後面接限制名稱，常用于建立限制和删除限制；

CREATE TABLE `Orders`
(
`O_Id` int NOT NULL,
`OrderNo` int NOT NULL,
`P_Id` int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

用法二: ALTER TABLE 時的 SQL FOREIGN KEY 限制

//(1)MySQL / SQL Server / Oracle / MS Access
ALTER TABLE `Orders`
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
//如需命名 FOREIGN KEY 限制，并定義多個列的 FOREIGN KEY 限制:
//(2)MySQL / SQL Server / Oracle / MS Access
ALTER TABLE `Orders`
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

用法三:撤銷 FOREIGN KEY 限制

//(1)MySQL
ALTER TABLE `Orders`
DROP FOREIGN KEY fk_PerOrders
//(2)SQL Server / Oracle / MS Access
ALTER TABLE `Orders`
DROP CONSTRAINT fk_PerOrders

(6)檢查限制 CHECK

CHECK 限制用于限制列中的值的範圍，評估插入或修改後的值。滿足條件的值将會插入表中，否則将放棄插入操作。 可以為同一列指定多個 CHECK 限制。

CHECK

限制既可以用于某一列也可以用于某張表：

  • 如果對單個列定義 CHECK 限制，那麼該列隻允許特定的值。

  • 如果對一個表定義 CHECK 限制，那麼此限制會基于行中其他列的值在特定的列中對值進行限制。

    定義 CHECK 限制條件在某種程度上類似于編寫查詢的 WHERE 子句，使用不同的比較運算符（例如 AND、OR、BETWEEN、IN、LIKE 和 IS NULL）編寫其布爾表達式，該布爾表達式将傳回 TRUE、FALSE 或 UNKNOWN 。

    (1)當條件中存在 NULL 值時，CHECK限制将傳回 UNKNOWN 值。

    (2)CHECK 限制主要用于通過将插入的值限制為遵循定義的值、範圍或格式規則的值來強制域完整性。

CREATE DATABASE IF NOT EXISTS hardy_db default character set utf8mb4 collate utf8mb4_0900_ai_ci;
USE hardy_db;
DROP TABLE IF EXISTS lesson;

建立表結構時可以使用 CHECK 限制，也可以給已建立的表增加 CHECK 限制。

舉例：假如我們想建立一個簡單的課程表 courses ，表中每一條資料記錄着課程編号 id、課程名稱 name 、學生總數 student_count 、建立課程時間 created_at 以及授課教師編号 teacher_id。其中課程編号 id 為主鍵。

根據基本常識，學生總數 student_count 一定是非負值，在這裡我們設定它必須為正整數，可以使用 CHECK 限制。

->->->是以，在不同的 SQL 軟體中，文法會有些不同，在本文中會介紹 CHECK 限制在各個 SQL 軟體中的使用。

用法一：建立表（CREATE TABLE）時添加 CHECK限制

  • 在建立課程表 courses 時，給學生總數 student_count 字段加上一個大于 0 的限制。

    MYSQL：

CREATE TABLE `courses`
(
`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CHECK (`student_count` > 0)
)

SQL Server / Oracle / MS Access：

CREATE TABLE `courses`
(
`id` int
CHECK (`student_count` > 0),
`name` varchar(255),`student_count` int,
`created_at` date,
`teacher_id` int
)
  • 為多個列添加 CHECK 限制

MySQL / SQL Server / Oracle / MS Access：

CREATE TABLE `courses`
(
`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CHECK (`student_count` > 0 AND `teacher_id` > 0)
)

如果想為一個表中多個字段添加限制，直接在 CHECK 關鍵字後的括号内添加，兩個限制間使用 AND 關鍵字連接配接。

  • 為 CHECK 限制命名 
CREATE TABLE `courses`
(
`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CONSTRAINT chk_courses CHECK (`student_count` > 0) ;

核心部分的基本文法為：

[CONSTRAINT <constraint name>] CHECK (<condition>)
  • 限制關鍵字 CONSTRAINT：表示其後面接的内容為限制
  • constraint name：為限制名稱
  • 關鍵字 CHECK：表示檢查限制

  • condition：被限制内容

    用法三：表已存在時添加 CHECK 限制

  • 課程表 courses 已存在的情況下為學生總數 student_count 字段添加一個大于 0 的 CHECK 限制。 
ALTER TABLE `courses` 
ADD CHECK ( `student_count` > 0);

或

ALTER TABLE `courses`  
ADD CONSTRAINT chk_courses CHECK ( `student_count` > 0 AND `teacher_id` > 0);
  • ALTER TABLE 關鍵字：表示修改表的定義

  • ADD 關鍵字：表示增加

    用法四：撤銷 CHECK 限制

    如果想要撤銷 CHECK 限制，可以使用 DROP 關鍵字。

ALTER TABLE `courses` 
DROP CHECK chk_courses
           
ALTER TABLE `courses` 
DROP CONSTRAINT chk_courses

(7)DEFAULT 限制----預設限制。

預設值（Default）”的完整稱呼是“預設值限制（Default Constraint）”。MySQL 預設值限制用來指定某列的預設值。

用法一：DEFAULT 限制用法

  • DEFAULT 限制用于向列中插入預設值。
  • 如果沒有規定其他的值，那麼會将預設值添加到所有的新記錄。

  • 例如女同學較多，性别就可以預設為“女”，如果插入一條新的記錄時沒有為這個字段指派，那麼系統會自動為這個字段指派為“女”。

    用法二：CREATE TABLE 時的 DEFAULT 限制

    使用 DEFAULT 關鍵字設定預設值限制，具體的文法規則如下所示：<字段名> <資料類型> DEFAULT <預設值>

    舉例：下面的 SQL 在 Persons表建立時在 City 列上建立 DEFAULT 限制：

    MYSQL / SQL Server / Oracle / MS Access

CREATE TABLE `Persons`
(
    `P_Id` int NOT NULL,
    `LastName` varchar(255) NOT NULL,
    `FirstName` varchar(255),
    `Address` varchar(255),
    `City` varchar(255) DEFAULT 'Sandnes'
)

通過使用類似 GETDATE() 這樣的函數, DEFAULT 限制也可以用于插入系統值：

CREATE TABLE `Orders`
(
    `O_Id` int NOT NULL,
    `OrderNo` int NOT NULL,
    `P_Id` int,
    `OrderDate` date DEFAULT GETDATE()
)

用法三：ALTER TABLE 時的 DEFAULT 限制

如果表已被建立時，想要在 City 列建立 DEFAULT 限制，請使用下面的 SQL：

MYSQL

ALTER TABLE `Persons`
ALTER `City` SET DEFAULT 'SANDNES'

SQL Server / MS Access：

ALTER TABLE `Persons`
ADD CONSTRAINT ab_c DEFAULT 'SANDNES' for `City`

用法四：撤銷 DEFAULT 限制

如需撤銷 Persons表的 DEFAULT 限制 :

MYSQL:

ALTER TABLE `Persons`
ALTER `City` DROP DEFAULT
           
ALTER TABLE `Persons`
ALTER COLUMN `City` DROP DEFAULT

多表連結

（1）聯結：聯結中的兩大主角——主鍵（PRIMARY KEY）和外鍵（FOREIGN KEY）

  • 建立聯結:在設定關聯條件時，為避免不同表被引用的列名相同，我們需要使用完全限定列名（用一個點分隔表名和列名），否則會傳回錯誤,用法如下： 
table1`.`common_field` = `table2`.`common_field`

  • JOIN 連接配接子句

    SQL JOIN 連接配接子句用于将資料庫中兩個或者兩個以上表中的記錄組合起來。其類型主要分為 INNER JOIN（内連接配接）、OUTER JOIN（外連接配接）、全連接配接（FULL JOIN）和交叉連接配接（CROSS JOIN），其中 OUTER JOIN 又可以細分為 LEFT JOIN（左連接配接）和 RIGHT JOIN（右連接配接）。

    是以，我們主要使用的 JOIN 連接配接類型如下：

  • INNER JOIN：如果表中有至少一個比對，則傳回行
  • LEFT JOIN：即使右表中沒有比對，也從左表傳回所有的行
  • RIGHT JOIN：即使左表中沒有比對，也從右表傳回所有的行
  • FULL JOIN：隻要其中一個表中存在比對，則傳回行

  • CROSS JOIN：又稱笛卡爾積，兩個表資料一一對應，傳回結果的行數等于兩個表行數的乘積

    (2)内連接配接INNER JOIN

  • 最常用也最重要的多表聯結類型就是 INNER JOIN（内連接配接），有時候也被稱作 EQUIJOIN（等值連接配接）。
  • 内連接配接根據聯結條件來組合兩個表中的字段，以建立一個新的結果表。假如我們想将表 1 和表 2 進行内連接配接，SQL 查詢會逐個比較表 1 和表 2 中的每一條記錄，來尋找滿足聯結條件的所有記錄對。當聯結條件得以滿足時，所有滿足條件的記錄對的字段将會結合在一起構成結果表。

  • 簡單的說，内連接配接就是取兩個表的交集，傳回的結果就是連接配接的兩張表中都滿足條件的部分。

    基本文法

    在對 INNER JOIN（内連接配接）的概念有基本的了解之後，我們再來學習一下它的基本文法。

    基本文法有如下兩種寫法：

SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`
INNER JOIN `table2`
ON `table1`.`common_field` = `table2`.`common_field`;
           
SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`
JOIN `table2`
ON `table1`.`common_field` = `table2`.`common_field`;

注：INNER JOIN 中 INNER 可以省略不寫

其中，文法的核心部分如下所示：

FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field

table1 和 table2 是内連接配接的兩個表名，table1.common_field 和 table2.common_field

需要注意的是，聯結條件需使用特定的 ON 子句給出。

(3)外連接配接 OUTER JOIN

外連接配接在生活中是經常用到的，外連接配接也是針對于兩張表格之間，比如我們實際應用過程會發現，會有一些新任職的教師，還在實習期，并無對應課程安排，那若是按照上一節使用内連接配接的話，這些教師的課程資訊将無法導出來，我們應該如何操作呢？這個就要用到我們的外連接配接，外連接配接可以将某個表格中，在另外一張表格中無對應關系，但是也能将資料比對出來。

在MySQL中，外連接配接查詢會傳回所操作的表中至少一個表的所有資料記錄。在MySQL中，資料查詢通過SQL語句 “OUTER JOIN…ON” 來實作，外連接配接查詢可以分為以下三類：

  • 左外連接配接
  • 右外連接配接

  • 全外連接配接

    外連接配接資料查詢文法如下：

SELECT column_name 1,column_name 2 ... column_name n
    FROM table1
        LEFT | RIGHT | FULL  (OUTER) JOIN table2
        ON CONDITION;

在上述語句中，參數

column_name

表示所要查詢的字段名字，來源于所連接配接的表 table1 和 table2，關鍵字

OUTER JOIN

表示表進行外連接配接，參數

CONDITION

表示進行比對的條件。

(3.1) 左外連接配接 LEFT JOIN

外連接配接查詢中的左外連接配接就是指新關系中執行比對條件時，以關鍵字 LEFT JOIN 左邊的表為參考表。左外連接配接的結果包括 LEFT OUTER 子句中指定的左表的所有行，而不僅僅是連接配接列所比對的行，這就意味着，左連接配接會傳回左表中的所有記錄，加上右表中比對到的記錄。如果左表的某行在右表中沒有比對行，那麼在相關聯的結果行中，右表的所有選擇清單均為空值。

文法：

SELECT column_name 1,column_name 2 ... column_name n
    FROM table1
        LEFT JOIN table2
        ON CONDITION ;

(3.2) 右外連接配接 RIGHT JOIN

外連接配接查詢中的右外連接配接是指新關系中執行比對條件時，以關鍵字 RIGHT JOIN 右邊的表為參考表，如果右表的某行在左表中沒有比對行，左表就傳回空值。

SELECT column_name 1,column_name 2 ... column_name n
    FROM table1
        RIGHT JOIN table2
        ON CONDITION ;

(3.3) 全外連接配接 FULL (OUTER) JOIN

FULL OUTER JOIN 關鍵字隻要左表（table1）和右表（table2）其中一個表中存在比對，則傳回行。FULL OUTER JOIN 關鍵字結合了 LEFT JOIN 和 RIGHT JOIN 的結果。

注：MySQL 資料庫不支援全連接配接，想要實作全連接配接可以使用 UNION ALL 來将左連接配接和右連接配接結果組合在一起實作全連接配接。

UNION ：聯合的意思，即把兩次或多次查詢結果合并起來

要求：兩次查詢的列數必須一緻，同時，每條 SELECT 語句中的列的順序必須相同

推薦：列的類型可以不一樣，但推薦查詢的每一列，相對于的類型應該一樣

可以來自多張表的資料：多次sql語句取出的列名可以不一緻，此時以第一個sql語句的列名為準，即UNION 結果集中的列名總是等于 UNION 中第一個 SELECT 語句中的列名。

如果不同的語句中取出的行，有完全相同(這裡表示的是每個列的值都相同)，那麼 UNION 會将相同的行合并，最終隻保留一行。也可以這樣了解，UNION 會去掉重複的行。

如果不想去掉重複的行，可以使用 UNION ALL 。

如果子句中有 order by,limit，需用括号()包起來。推薦放到所有子句之後，即對最終合并的結果來排序或篩選。

SELECT column_name 1,column_name 2 ... column_name n
    FROM table1
        LEFT JOIN table2 ON CONDITION 
UNION
SELECT column_name 1,column_name 2 ... column_name n
    FROM table1
        RIGHT JOIN table2 ON CONDITION ;

(4) 交叉連接配接 CROSS JOIN

(4.1) 什麼是交叉連接配接

* 與内連接配接和外連接配接相比，交叉連接配接非常簡單，因為它不存在 ON 子句，那怎麼了解交叉連接配接呢？

* 交叉連接配接：傳回左表中的所有行，左表中的每一行與右表中的所有行組合。即将兩個表的資料一一對應，其查詢結果的行數為左表中的行數乘以右表中的行數。

CROSS JOIN（交叉連接配接）的結果也稱作笛卡爾積，我們來簡單了解一下什麼是笛卡爾積：

* 笛卡爾乘積是指在數學中，兩個集合 X 和 Y 的笛卡尓積（Cartesian product），又稱直積，表示為X × Y，第一個對象是 X 的成員而第二個對象是 Y 的所有可能有序對的其中一個成員。

(4.2) 交叉連接配接的兩種定義方式

交叉連接配接有兩種定義方式，分為隐式連接配接和顯式連接配接。兩種定義方式的查詢結果是相同的。

* 隐式交叉連接配接：不需要使用 CROSS JOIN 關鍵字，隻要在 SELECT 語句的 FROM 語句後将要進行交叉連接配接的表名列出即可，這種方式基本上可以被任意資料庫系統支援。

基本文法如下：

SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`,`table2`;

假如我們想将課程表和教師表進行隐式的交叉連接配接，查詢該學期所有開課老師和他們開課的課程，我們可以使用下列 SQL 語句：

SELECT  `courses`.`name` AS `course_name`, `teachers`.`name` AS `teacher_name`
FROM `courses` ,`teachers`;

為了編寫的便利和簡潔，我們一般會給表取别名，如本題中給教師表 courses 取别名為 c，給教師表teachers 取别名為 t：

SELECT  `c`.`name` AS `course_name`, `t`.`name` AS `teacher_name`
FROM `courses` `c`,`teachers` `t`;
           
* 顯式交叉連接配接：與隐式交叉連接配接的差別就是它使用 CROSS JOIN 關鍵字，用法與 INNER JOIN 相似。
           
SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`
CROSS JOIN `table2`;

使用顯式交叉連接配接來解決上文相同的問題，我們可以使用下列 SQL 語句：

SELECT  `courses`.`name` AS `course_name`, `teachers`.`name` AS `teacher_name`
FROM `courses` 
CROSS JOIN `teachers`;
           
SELECT  `c`.`name` AS `course_name`, `t`.`name` AS `teacher_name`
FROM `courses` `c`
CROSS JOIN `teachers` `t`;

LEVEL 5

分組查詢

(1)GROUP BY 子句

我們在日常生活中，常常會将東西分類擺放使其能看起來更井井有條，也在找尋時能更加友善。對于資料，在查詢過程中，我們同樣也會需要對同類的資料進行分類。

GROUP BY 函數就是 SQL 中用來實作分組的函數，其用于結合聚合函數，能根據給定資料列的每個成員對查詢結果進行分組統計，最終得到一個分組彙總表。

SELECT `column_name`, aggregate_function(`column_name`)
FROM `table_name`
WHERE `column_name` operator value
GROUP BY `column_name`;

例如：可以看到我們教師表中的教師來自不同的國家，現需要統計不同國家教師的人數，并将結果按照不同國籍教師人數從小到大排列，請編寫相應的 SQL 語句實作。

使用 SQL 中子查詢的方式如下：

SELECT `country`, COUNT(`country`) AS `teacher_count`
FROM `teachers`
GROUP BY `country`
ORDER BY `teacher_count`, `country`;

(2)GROUP BY 多表執行個體

課程表的每節課程都有對應的一個教師負責授課，而每一個教師對應多門課程，現需要統計每個教師教授課程的學生總數，請編寫相應的 SQL 語句實作。

SELECT T.name AS `teacher_name`, IFNULL(SUM(C.student_count), 0) AS `student_count`
FROM `courses` C
	RIGHT JOIN `teachers` T ON C.teacher_id = T.id
GROUP BY T.id;

(3)HAVING 子句: HAVING 子句在使用時就經常與 GROUP BY 語句搭配使用

目的：我們在使用 WHERE 條件子句時會發現其不能與聚合函數聯合使用，為解決這一點，SQL 中提供了 HAVING 子句。在使用時， HAVING 子句經常與 GROUP BY 聯合使用，HAVING 子句就是對分組統計函數進行過濾的子句。

HAVING 子句對于 GROUP BY 子句設定條件的方式其實與 WHERE 子句與 SELECT 的方式類似，文法也相近，但 WHERE 子句搜尋條件是在分組操作之前，而 HAVING 則是在之後。

光通過以上的描述可能體會不夠，讓我們看一下 HAVING 的實際應用吧！

SELECT   `column_name`, aggregate_function(`column_name`) 
FROM     `table_name` 
WHERE    `column_name` operator value 
GROUP BY `column_name` 
HAVING   aggregate_function(`column_name`) operator value;

例如:

現需要結合教師表與課程表，統計不同教師所開課程的學生總數，對于沒有任課的老師，學生總數計為 0 ，最後查詢學生總數少于 3000 的教師姓名及學生總數 （别名為 student_count ），結果按照學生總數升序排列，如果學生總數相同，則按照教師姓名升序排列。

使用 SQL 中 HAVING 子句查詢的方式如下：

SELECT `T`.`name` AS `name`, IFNULL(SUM(`C`.`student_count`),0) AS `student_count`
FROM `courses` `C` RIGHT JOIN `teachers` `T`
ON `C`.`teacher_id` = `T`.`id`
GROUP BY `T`.`id`
HAVING `student_count` < 3000
ORDER BY `student_count`, `name`;

例題：

請編寫 SQL 語句，查詢 teachers 表中，各個國家所有教師的平均年齡大于所有國家教師的平均年齡的教師資訊。

select *
from teachers 
WHERE country IN
(SELECT country from teachers group by country
having AVG(age)>(select avg(age) FROM teachers))

簡單的子查詢

(1)SELECT 語句中的子查詢

我們之前學的都是在單個表單中進行查詢，那麼，當查詢中需要聯合多個表時，該如何實作呢？

上一節我們學到的解決方式是使用連接配接查詢，這一節我們将介紹第二種方法：子查詢。

首先，什麼是子查詢呢？

當一個查詢是另一個查詢的條件時，稱之為子查詢。

即在查詢語句中的 WHERE 條件子句中，又嵌套了另一個查詢語句。

是以，子查詢本質上就是一個完整的 SELECT 語句，它可以使一個 SELECT、INSERT INTO 語句、DELETE 語句或 UPDATE 語句嵌套在另一子查詢中。子查詢的輸出可以包括一個單獨的值（單行子查詢）、幾行值（多行子查詢）、或者多列資料（多列子查詢）。

這裡，我們先了解一下基礎的 SELECT 語句嵌套子查詢。

SELECT `column_name(s)`
FROM `table_name`
WHERE `column_name` OPERATOR (
    SELECT `column_name(s)`
    FROM `table_name`
);

例如：

小明想了解 Western Venom 老師所教的所有課程的所有資訊，現請你來幫助他查詢相關資訊。

SELECT *
FROM `courses`
WHERE `teacher_id` = (
	SELECT `id`
	FROM `teachers`
	WHERE `name` = 'Western Venom'
);

(2)NSERT 語句中的子查詢:對于 INSERT 語句中的子查詢來說，首先是使用子查詢的 SELECT 語句找到需要插入的資料，之後将傳回的資料插入到另一個表中。在子查詢中所選擇的資料可以用任何字元、日期或數字函數修改。

INSERT INTO `table_name`
	SELECT `colnum_name(s)`
	FROM `table_name`
	[ WHERE VALUE OPERATOR ]

注意：INSERT 語句中的子查詢其實是将一個表中查詢到的資料“複制”到另一個表中，由于主鍵具有唯一性，如果需要僅在單張表中使用 INSERT 子查詢，隻能在無主鍵的單張表中進行操作，否則，需要有兩張表（如隻一張表，則需建立一張表）。

我們可以通過下面的執行個體來感受一下 INSERT 語句中的子查詢 的用法。

小明在整理資料時發現教師表未備份，為了及時完善資料的備份，現在需要将教師表 teachers 中的全部資訊複制到相同表結構的備份表 teachers_bkp 中，請使用相關 SQL 語句完成教師表的備份。

首先我們需要查詢教師表 teachers 中的所有資訊，再将查詢到的資料插入到備份表 teachers_bkp 中。即，查詢教師表 teachers 中的所有資訊為插入備份表 teachers_bkp 中的條件。

是以，這裡我們首先需要通過嵌套子查詢到的資訊為教師表 teachers 中的所有資訊，而整個語句是為了插入資料。

使用 SQL 中子查詢的方式如下：(以下文法類似于複制表)

INSERT INTO `teachers_bkp`
SELECT *
FROM `teachers`;

(3)UPDATE 語句中的子查詢

UPDATE `table_name` 
SET `column_name` = `new_value`
WHERE `column_name` OPERATOR 
   (SELECT `column_name`
   FROM `table_name`
   [WHERE] )
           
注意：在 UPDATE 語句的子查詢中，子查詢 SELECT 語句所用的表和 UPDATE 語句所要更改的表不能是同一張表！

舉例：學校教務處排課時發現教師 Western Venom 建立的課程有誤，現緊急需要将該教師建立的課程名稱修改為 Java，請你使用相關的 SQL 語句完成。

UPDATE `courses`
SET `name` = 'Java'
WHERE `teacher_id` = (
	SELECT `id`
    FROM `teachers`
    WHERE `name` = 'Western Venom'
);

(4)DELETE 語句中的子查詢

對于 DELETE 語句，首先通過 SELECT 語句查詢需要删除的資料，再使用 DELETE 語句對資料進行删除。當通過 DELETE 語句使用子查詢時，可以完成複雜的資料删除控制。

DELETE FROM `table_name`
WHERE `column_name` OPERATOR 
   (SELECT `column_name`
   FROM `table_name`  
   [WHERE] )

舉例：現需要删除課程表中所有教師年齡小于 21 歲（不包括 21 歲）的課程，請你使用相關的 SQL 語句實作

分析：首先我們需要在教師表 teachers 中查詢到教師年齡小于 21 歲的老師的教師 id ，再根據其教師 id 在課程表 course 中查詢該教師 id 所建立的課程并将課程删除。

是以，這裡我們首先需要通過嵌套子查詢到的資訊為符合條件的教師 id，而整個語句是為了删除資料。

DELETE FROM `courses`
WHERE `teacher_id` IN (
		SELECT `id`
		FROM `teachers`
		WHERE `age` < 21
	);

子查詢進階

我們在 Level 1 中有學習到 SELECT 語句的文法，為：SELECT column_name FROM table_name ，結合我們前面學習的子查詢的方法，我們是将子查詢插入到列名 column_name 的位置，将子查詢的結果作為列名，而本節我們将介紹的内聯視圖子查詢，是将子查詢插入到表名 table_name 的位置。

(1)内聯視圖子查詢實際上就是将查詢的結果集作為一個查詢表，繼續進行查詢操作。

我們可以通過下面的執行個體來感受一下 内聯視圖子查詢 的用法。

現需要查詢國籍為美國（USA），且年齡最大的教師，請使用内聯視圖子查詢實作。

本題将從教師表中查詢到的美國教師作為内聯表，再使用 WHERE 子句進行查詢操作。

使用 SQL 中内聯視圖子查詢的方式如下：

SELECT *
FROM (
	SELECT *
	FROM `teachers`
	WHERE `country` = 'USA'
) `T`
WHERE `age` = (
	SELECT MAX(`age`)
	FROM `teachers`
);

(2)IN 操作符的多行子查詢

大家還記得在 Level 2 的特殊條件一章中學到的 IN 操作符嗎？

沒錯！當時我們通過使用 IN 操作符友善地将多個條件連接配接了起來，并對單表進行了查詢。而本節我們将結合 IN 操作符對多行子查詢進行學習。

使用 IN 操作符進行子查詢，其實是将子查詢傳回的集合和外層查詢得到的集合進行交集運算，這個結果可以是零個值，也可以是多個值。由此，最後可以查詢出與清單中任意一個值比對的行。

SELECT `column_name`
FROM `table_name`
WHERE `column_name` IN(
    SELECT `column_name`
    FROM `table_name`
    WHERE `column_name` = VALUE
);

可以通過下面的執行個體來感受一下 IN 操作符多行子查詢的用法。

現需要查詢國籍為美國（USA）的教師所開的所有課程，請使用 IN 操作符進行多行子查詢。

為了得到滿足條件的課程名稱，我們首先需要在教師表 teachers 中進行查詢，查詢資料為國籍 country 為美國（USA）的教師所對應的教師 id，再根據這個 id ，在課程表courses 中進行查詢，最終得到所有的課程名稱 name。

這裡，我們首先需要通過嵌套子查詢到的資訊為國籍為美國的全部教師的教師 id，父查詢為查詢滿足條件的課程名稱。

SELECT `name`
FROM `courses`
WHERE `teacher_id` IN (
	SELECT `id`
	FROM `teachers`
	WHERE `country` = 'USA'
);

(3)ANY 操作符的多行子查詢

在學習了 IN 操作符實作多行子查詢後，我們不禁會産生思考：除了使用 IN 操作符，還有什麼方法能進行多行子查詢呢？

而這一節我們就能夠學習到另一種實作多行子查詢的方式：使用 ANY 操作符進行多行子查詢。

但在學習之前，我們需要了解一下操作符 ANY 。

操作符 ANY 屬于邏輯運算符的一種，與 IN 運算符不同，ANY 必須和其它的比較運算符共同使用，其表示查詢結果中的任意一個。

在子查詢中使用 ANY ，表示與子查詢傳回的任何值比較為真，則傳回真。

SELECT `column_name(s)`
FROM `table_name`
WHERE `column_name` OPERATOR
   ANY(SELECT column_name
   FROM table_name)

舉例：現需要查詢學生上課人數超過 “Eastern Heretic” 的任意一門課的學生人數的課程資訊，請使用 ANY 操作符實作多行子查詢。

本題涉及到多層的嵌套，讓我們來一步步對題目進行分析吧！

第一層的父查詢為在課程表 courses 中查詢滿足條件的全部課程資訊，這個條件由子查詢來完成，即為，查詢學生上課人數超過 ”Eastern Heretic“ 的任意一門課的學生人數。這一部分的子查詢中需要結合 ANY 操作符實作。之後，再将子查詢進行拆分，形成第二層的嵌套子查詢。

第二層的父查詢為在課程表 courses 中根據教師 id 查詢學生上課人數， 其子查詢為在教師表 teachers 中查找教師名 name 為 “Eastern Heretic” 的教師 id。

由于我們最終得到的課程資訊中肯定不包含 “Eastern Heretic” 的課程，是以我們要在 WHERE 條件中再設定一項：不為 “Eastern Heretic” 所開的課程 。

結合以上，使用 SQL 中子查詢的方式如下：

SELECT *
FROM `courses`
WHERE `student_count` > ANY (
		SELECT `student_count`
		FROM `courses`
		WHERE `teacher_id` = (
			SELECT `id`
			FROM `teachers`
			WHERE `name` = 'Eastern Heretic'
		)
	)
	AND `teacher_id` <> (
		SELECT `id`
		FROM `teachers`
		WHERE `name` = 'Eastern Heretic'
	);

(4)ALL 操作符的多行子查詢

除了 IN 、 ANY 外，ALL 也經常在多行子查詢中被使用到。

首先，我需要了解一下 ALL 操作符。

與 ANY 一樣，操作符 ALL 也屬于邏輯運算符的一種，且都須與其它的比較運算符共同使用，其表示查詢結果中的所有。

在子查詢中使用 ALL ，表示與子查詢傳回的所有值比較為真，則傳回真。

SELECT `column_name(s)`
FROM `table_name`
WHERE `column_name` OPERATOR
   ALL(SELECT column_name
   FROM table_name)

經過上一節 ANY 執行個體中的學習，相信你對多層的嵌套有了一定的了解，本題依舊是一道多層嵌套的執行個體題。結合本節知識點，結合操作符 ALL 完成多行子查詢吧！

現需要查詢學生人數超過 ”Western Venom“ 所有課程學生人數的課程資訊，請使用 ALL 操作符實作多行子查詢。

第一層的父查詢為在課程表 courses 中查詢滿足條件的全部課程資訊，這個條件由子查詢來完成，即為，查詢學生人數超過 ”Western Venom“ 所有課程學生人數。這一部分的子查詢中需要結合 ALL 操作符實作。之後，再将子查詢進行拆分，形成第二層的嵌套子查詢。

第二層的父查詢為在課程表 courses 中根據教師 id 查詢學生上課人數， 其子查詢為在教師表 teachers 中查找教師名 name 為 ”Western Venom“ 的教師 id。

SELECT *
FROM `courses`
WHERE `student_count` > ALL (
	SELECT `student_count`
	FROM `courses`
	WHERE `teacher_id` = (
		SELECT `id`
		FROM `teachers`
		WHERE `name` = 'Western Venom'
	)
);

(5)多列子查詢

上一章我們了解了多行子查詢，這一章我們将對多列子查詢進行學習。

對于多列子查詢：

當是單行多列的子查詢時，主查詢語句的條件語句中引用子查詢結果時可用單行比較符号（＝，>，<，>=，<=， <> 等）來進行比較；

當是多行多列子查詢時，主查詢語句的條件語句中引用子查詢結果時必須用多行比較符号（IN，ANY，ALL 等）來進行比較。

可以通過下面的執行個體來感受一下 多列子查詢 的用法。

現需要找到每個國家年齡最大的教師，請編寫 SQL 語句實作多列子查詢

SELECT `name`, `age`, `country` 
FROM `teachers` 
WHERE (`country`, `age`) IN ( 
        SELECT `country`, MAX(`age`) 
        FROM `teachers` 
        GROUP BY `country` 
);

(5)HAVING 子句中的子查詢

當子查詢出現在 HAVING 子句中時，像 HAVING 子句中的任何表達式一樣，表示要進行分組過濾，它被用作行組選擇的一部分，一般傳回單行單列的資料。

👇 我們可以通過下面的執行個體來感受一下 HAVING 子查詢 的用法。

現需要計算每位教師所開課程的平均學生人數與全部課程的平均學生人數，比較其大小，最後傳回超過全部課程平均學生人數的教師姓名，請編寫相應的 SQL 語句實作。

本題需要使用 HAVING 語句根據教師 id 進行分組，實作“計算每位教師所開課程的平均人數”，并使用子查詢實作其與“全部課程的平均人數”的比較。

使用 SQL 中 HAVING 子查詢的方式如下：

SELECT `name`
FROM `teachers`
WHERE `id` IN (
	SELECT `teacher_id`
	FROM `courses`
	GROUP BY `teacher_id`
	HAVING AVG(`student_count`) > (
		SELECT AVG(`student_count`)
		FROM `courses`
	)
);

LEVEL6

MYSQL 事務

本章節将帶着大家學習 MySQL 事務以及如何使用 COMMIT 和 ROLLBACK 語句來管理 MySQL 中的事務。

(1)MySQL 事務語句

MySQL 為我們提供了以下重要語句來控制事務：

  • 為了啟動一個事務，你使用 START TRANSACTION 語句。BEGIN 或 BEGIN WORK 是 START TRANSACTION 的别名。
  • 要送出目前事務并使其變化永久化，你要使用 COMMIT 語句。
  • 要復原目前事務并取消其變化，你可以使用 ROLLBACK 語句。
  • 要禁用或啟用目前事務的自動送出模式，你可以使用 SET autocommit 語句。

預設情況下，MySQL 自動将更改永久性地送出給資料庫。要強迫 MySQL 不自動送出更改，你可以使用以下語句：

SET autocommit = 0;

-- OR --

SET autocommit = OFF

你使用下面的語句來明确地啟用自動送出模式：

SET autocommit = 1;

-- OR --

SET autocommit = ON;

COMMIT 執行個體

為了使用事務，你首先要把 SQL 語句分成邏輯部分，并确定資料何時應送出或復原。

下面說明了建立一個新的銷售訂單的步驟：

  • 首先，通過使用 START TRANSACTION 語句啟動一個事務。
  • 接下來，從 orders 表中選擇最新的銷售訂單号，并使用下一個銷售訂單号作為新的銷售訂單号。
  • 然後，在 orders 表中插入一個新的銷售訂單。
  • 之後，在 orderdetails 表中插入銷售訂單項目。
  • 最後，使用 COMMIT 語句送出該事務。

你可以選擇從 orders 和 orderdetails 表中選擇資料來檢查新的銷售訂單。

下面是執行上述步驟的腳本：

-- 1. start a new transaction
START TRANSACTION;

-- 2. Get the latest order number
SELECT 
    @orderNumber:=MAX(orderNUmber)+1
FROM
    orders;

-- 3. insert a new order for customer 145
INSERT INTO orders(orderNumber,
                   orderDate,
                   requiredDate,
                   shippedDate,
                   status,
                   customerNumber)
VALUES(@orderNumber,
       '2005-05-31',
       '2005-06-10',
       '2005-06-11',
       'In Process',
        145);
        
-- 4. Insert order line items
INSERT INTO orderdetails(orderNumber,
                         productCode,
                         quantityOrdered,
                         priceEach,
                         orderLineNumber)
VALUES(@orderNumber,'S18_1749', 30, '136', 1),
      (@orderNumber,'S18_2248', 50, '55.09', 2); 
      
-- 5. commit changes    
COMMIT;

ROLLBACK 執行個體

首先，登入到 MySQL 資料庫伺服器，從 orders 表中删除資料：

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM orders;
Query OK, 327 rows affected (0.03 sec)

舉例：我們要向 teachers 表中插入一條 Xie Xun 的資訊，其年齡為 49 歲，國籍為 CN，請補充 SQL 語句，來實作插入 Xie Xun 的資訊。

BEGIN;
INSERT INTO `teachers`
  (`name`, `age`, `country`) 
  VALUES ('Xie Xun', 49, 'CN');
COMMIT;

鎖的認識

鎖的解釋

鎖：計算機協調多個程序或線程并發通路某一資源的機制。

鎖的重要性

在資料庫中，除了傳統的計算資源（CPU、RAM、I\O等）的争搶，資料也是一種供多使用者共享的資源。

是以，如何保證資料并發通路的一緻性，有效性，是所有資料庫必須要解決的問題。

鎖沖突也是影響資料庫并發通路性能的一個重要因素，是以鎖對資料庫尤其重要。

鎖的缺點

加鎖是消耗資源的，鎖的各種操作，包括：獲得鎖、檢測鎖是否已經解除、釋放鎖等，都會增加系統的開銷。

舉例:請編寫 SQL 語句，檢視你目前使用的資料庫是什麼事務隔離級别

SHOW VARIABLES like '%isolation%';

鎖的類型

(1)InnoDB 鎖的種類之自增鎖

查詢目前資料庫的自增鎖模式：

show variables like '%innodb_autoinc_lock_mode%';
SQL語句大全
* 0：traditonal 每次 insert 語句執行都會産生表鎖

* 1：consecutive simple insert 會獲得批量的鎖，保證一批插入自增序列的連續性，插入之前提前釋放鎖，在這個模式下你會發現當你 insert 多條資料復原的時候雖然 DB 沒有插入資料，但是自增 ID 已經增長了，也是資料庫預設的級别

* 2：interleaved 不會鎖表，實時插入，并發最高，但是基于主從的複制是不安全的，感興趣可以去查詢 RBR 和 SBR 的優缺點

通過查詢知道，我們的資料庫是開啟 consecutive 級别，simple insert 表示通過分析 insert 語句可以确定插入的數量；如：insert, insert, insert … valies(), values(), values()；相應的其他插入方式還有 Bulk inserts，Mixed-mode inserts 等。

官方原話如下:

自增鎖是一種特殊的表級别鎖（table-level lock），專門針對事務插入 AUTO_INCREMENT 類型的列。最簡單的情況，如果一個事務正在往表中插入記錄，所有其他事務的插入必須等待，以便第一個事務插入的行，是連續的主鍵值。

案例

如果沒有自增鎖的情況下如下表，id 為自增 AUTO_INCREMENT = 4

id name
路飛
索隆
娜美
這時開啟一個事務 A：insert 一條記錄山治，自增序列配置設定到的 id 為 4，事務不送出
又開啟一個事務 B：insert 一條記錄烏索普，自增序列配置設定到的 id 為 5，事務不送出
事務 A 又插入一條記錄，insert 一條記錄為喬巴，這個是後被配置設定到的 id 為 6
由于在讀送出事務隔離級别下不會查詢到其他未送出的事務， 事務 A 此時查詢 id>3 的記錄會傳回 4：山治，6：喬巴。就會有一個事務裡送出的資料自增 id 不是連續的問題産生
此時自增鎖就會幫助搞定這個問題
  • innodb_autoinc_lock_mode = 0

在這一模式下，所有的 insert 語句都要在語句開始的時候得到一個表級的 auto_inc 鎖，在語句結束的時候才釋放

這種模式下 auto_inc 鎖一直要保持到語句的結束，是以這個就影響到了并發的插入

但是主從同步時候是安全的

  • innodb_autoinc_lock_mode = 1

這一模式下去 simple insert 做了優化，由于 simple insert 一次性插入值的個數可以立馬得到确定，是以 MySQL 可以一次生成幾個連續的值，用于這個 insert 語句，也保證主從同步基于語句的複制安全

這一模式也是 MySQL 的預設模式，這個模式的好處是 auto_inc 鎖不要一直保持到語句的結束，隻要語句得到了相應的值後就可以提前釋放鎖

  • innodb_autoinc_lock_mode = 2

無自增鎖，會導緻上面案例的問題，同一批 insert 送出自增不連續

(2)InnoDB 鎖的種類之共享鎖和排他鎖

(2.1)并發控制

提到共享鎖和排它鎖就不得不提并發控制（Concurrency Control），并發控制可以解決臨界資源操作時不一緻的情況産生，保證資料一緻性常見的手段就是鎖和資料多版本（Multi Version）

直接加鎖

這種方式會導緻被加鎖的資源都被鎖住，讀取任務也無法執行直到鎖釋放，所有執行的任務相當于串行化方式，簡單粗暴，不能并發

(2.2)共享鎖（Shared Locks）簡稱為 S 鎖

讀取資料時候可以加 S 鎖。

(2.3)共享 (S) 鎖允許并發事務讀取 (SELECT) 一個資源。資源上存在共享 (S) 鎖時，任何其它事務都不能修改資料。一旦已經讀取資料，便立即釋放資源上的共享 (S) 鎖，除非将事務隔離級别設定為可重複讀或更進階别，或者在事務生存周期内用鎖定提示保留共享 (S) 鎖。

排它鎖 （Exclusive Locks）簡稱為 X 鎖

修改資料時候加 X 鎖。

排它 (X) 鎖可以防止并發事務對資源進行通路。其它事務不能讀取或修改排它 (X) 鎖鎖定的資料。

共享鎖之間可以并行，排它鎖和共享鎖之間互斥，也就是說隻要共享鎖開啟沒有釋放掉的時候，更新鎖是不能搶占的，此時其他讀取同資源的操作可以進行讀取不受限制；同理排它鎖開啟時候隻要沒有釋放其他不管是排它鎖還是共享鎖都不可以搶占資源直到鎖釋放。

(3)InnoDB 鎖的種類之意向鎖、記錄鎖和間隙鎖

(4)InnoDB 鎖的種類之臨鍵鎖和插入意向鎖

如何上鎖

(1) 如何加表鎖

對于行鎖和表鎖的含義差別，在面試中是高頻出現的，我們應該對 MySQL 中的鎖有一個系統的認識，更詳細的需要自行查閱資料，本篇為概括性的總結回答。

MySQL常用引擎有 MyISAM 和 InnoDB，而 InnoDB 是 MySQL 預設的引擎。MyISAM 不支援行鎖，而 InnoDB 支援行鎖和表鎖。

如何加鎖？

隐式上鎖（預設，自動加鎖、自動釋放）

MyISAM 在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執行更新操作（UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖，這個過程并不需要使用者幹預，是以使用者一般不需要直接用

LOCK TABLE

; 指令給 MyISAM 表顯式加鎖。

-- 上讀鎖 --
SELECT 

-- 上寫鎖 --

顯式上鎖（手動）

上共享鎖（讀鎖）的寫法：

lock in share mode

，例如：

select column_name from table_name where lock in share mode；

上排它鎖（寫鎖）的寫法：

for update

select column_name from table_name where conditions for update；

解鎖（手動）

-- 解鎖所有鎖表 --
UNLOCK TABLES;

舉例:通過下面的執行個體來感受一下 MySQL 中表鎖之讀鎖的用法。

假設我們要更新 teachers 表中，Linghu Chong 的郵箱為 [email protected]，但是 teachers 表被上了讀鎖，請編寫 SQL 語句，來實作更新 Linghu Chong 的郵箱。

-- 對 teachers 表上讀鎖 --
LOCK TABLES teachers READ;

-- 解鎖 --
UNLOCK TABLES;

-- 更新郵箱 --
UPDATE teachers 
SET email = '[email protected]'
WHERE name = 'Linghu Chong';

說明：

在上面的 SQL 語句中，由于對 teachers 表加了讀鎖，是以直接使用 UPDATE 語句更新會報錯，因為讀鎖上不能進行寫操作，需要先解鎖，再進行 UPDATE 操作。

下面:

可以通過下面的執行個體來感受一下 MySQL 中表鎖之寫鎖的用法。

假設我們要查詢 teachers 表中所有的資訊，但是，courses 表被加了寫鎖，請編寫 SQL 語句，來實作查詢 teachers 表中所有的資訊。

我們可以使用下面的 SQL 語句：

-- 對 courses 表上寫鎖 --
LOCK TABLES courses WRITE;

-- 解鎖 --
UNLOCK TABLES;

-- 查詢 teachers 表中所有資訊 --
SELECT * 
FROM teachers;

(2) 如何加行鎖

MySQL 存儲引擎的發展是從 MyISAM 到 InnoDB，鎖從表鎖發展到行鎖。行鎖的出現從某種程式上是為了彌補表鎖的不足。比如MyISAM 不支援事務，InnoDB 支援事務。

表鎖雖然開銷小，加鎖快，但高并發下性能低。行鎖偏向于 InnoDB 存儲引擎，雖然開銷大，加鎖慢，鎖定粒度小，會出現死鎖，但高并發下性能更高。

事務和行鎖都是在確定資料準确的基礎上提升并發的處理能力。是以，InnoDB 存儲引擎相比 MyISAM 而言，更适合高并發場景，同時支援事務處理。InnoDB 與 MyISAM 相比最大不同之處也在于這兩點：支援事務、采用行級鎖。

行鎖優缺
劣勢 開銷大、加鎖慢、會出現死鎖
優勢 粒度小、發生鎖沖突幾率小、處理并發能力強
行鎖是 MySQL 中粒度最細的一種鎖機制，隻對目前所操作的行進行加鎖，行鎖發生沖突的機率很低，其粒度最小，但加鎖的代價最大。行鎖有分為共享鎖（S 鎖）和排他鎖（X 鎖）。
行鎖
----
S 鎖 讀鎖，共享鎖
X 鎖 寫鎖，排他鎖
InnoDB 的行鎖是通過給索引項添加鎖來實作的，這一點 MySQL 與 Oracle 不同，Oracle 是通過在資料庫中對相應的資料行加鎖來實作。InnoDB 行鎖隻有在通過索引條件檢索資料時才能使用，否則會直接使用表級鎖。需要注意的是行級鎖一定要使用索引。

InnoDB 行鎖的加鎖的方式是自動加鎖

* 對于 UPDATE、DELETE、INSERT 操作，InnoDB 會自動給涉及資料集添加排他鎖
* 對于 SELECT 操作，InnoDB 不會添加任何鎖

| | 操作 | 語句 | 自動加鎖 |

| ---- | ---- | ---- |

| DQL | SELECT | 無 |

| DML | INSERT/DELETE/UPDATE | X 鎖 |

-- 不會上鎖 --
SELECT

-- 上寫鎖 --
INSERT, UPDATE, DELETE

顯示上鎖（手動）

InnoDB 手工加鎖方式

LOCK IN SHARE MODE

FOR UPDATE

隻能在事務内其作用，以保證目前會話事務鎖定的行不會被其他會話修改。

-- 讀鎖 --
SELECT *
FROM table_name
LOCK IN SHARE MODE;

-- 寫鎖 --
SELECT *
FROM table_name
FOR UPDATE;
           
* 送出事務（commit）
* 復原事務（rollback）
* 阻塞程序（kill）

如何排查鎖

(1)如何排查表鎖

檢視表鎖情況:

SHOW OPEN TABLES;

表鎖分析

SQL語句大全

Table_locks_waited

：出現表級鎖定争用而發生等待的次數（不能立即擷取鎖的次數，每等待一次值加 1），此值高說明存在着較嚴重的表級鎖争用情況

Table_locks_immediate

：産生表級鎖定次數，不是可以立即擷取鎖的查詢次數，每立即擷取鎖加 1

(2)如何排查行鎖

行鎖分析

SQL語句大全
* Innodb_row_lock_current_waits：目前正在等待鎖定的數量
* Innodb_row_lock_time：從系統啟動到現在鎖定總時間長度
* Innodb_row_lock_time_avg：每次等待所花平均時間
* Innodb_row_lock_time_max：從系統啟動到現在等待最長的一次所花時間
* Innodb_row_lock_waits：系統啟動後到現在總共等待的次數

優化建議

* 盡可能讓所有資料檢索都通過索引來完成，避免無索引行鎖更新為表鎖
* 合理設計索引，盡量縮小鎖的範圍
* 盡可能較少檢索條件，避免間隙鎖
* 盡量控制事務大小，減少鎖定資源量和時間長度
* 盡可能低級别事務隔離

樂觀鎖和悲觀鎖

相信大家都遇到這種場景，當很多人（一兩個人估計不行）同時對同一條資料做修改的時候，那麼資料的最終結果是怎樣的呢？

這也就是我們說的并發情況，這樣會導緻以下兩種結果：

* 更新錯誤，你修改之後的資料可能被别人覆寫了，導緻你很懵逼，甚至懷疑自己開發的功能是否有問題；
* 髒讀，資料更新錯誤，導緻讀資料也是錯的，查詢出一些預設奇妙的資料，看到的不是你自己修改的結果。

這樣的問題怎麼解決呢？于是乎，鎖就這樣産生了，鎖分為樂觀鎖和悲觀鎖，它的目的是用來解決并發控制的問題。

MyISAM 引擎不支援事務，是以不考慮它有樂觀鎖和悲觀鎖概念。MyISAM 隻有表鎖，鎖又分為讀鎖和寫鎖。在這裡我們隻讨論InnoDB 引擎。

需要注意的是，樂觀鎖和悲觀鎖并不是解決并發控制的唯一手段（也可以使用消息中間件 kafka，MQ 之類的作為緩沖等等），而且樂觀鎖和悲觀鎖并不僅限制在 MySQL 中使用，它是一種概念，很多其他的應用，如 redis，memcached 等，隻要存在并發情況的，都可以應用這種概念，隻是方式上有些差别而已。

(1)樂觀鎖

樂觀鎖，簡單地說，就是從應用系統層面上做并發控制，去加鎖。

實作樂觀鎖常見的方式：版本号 version

實作方式，在資料表中增加版本号字段，每次對一條資料做更新之前，先查出該條資料的版本号，每次更新資料都會對版本号進行更新。在更新時，把之前查出的版本号跟庫中資料的版本号進行比對，如果相同，則說明該條資料沒有被修改過，執行更新。如果比對的結果是不一緻的，則說明該條資料已經被其他人修改過了，則不更新，用戶端進行相應的操作提醒。

使用版本号實作樂觀鎖

使用版本号時，可以在資料初始化時指定一個版本号，每次對資料的更新操作都對版本号執行 +1 操作。并判斷目前版本号是不是該資料的最新的版本号。

-- 1.查詢出商品資訊 --
select status,version from t_goods where id=#{id};
-- 2.根據商品資訊生成訂單 --
-- 3.修改商品status為2 --
update t_goods
set status=2,version=version+1
where id=#{id} and version=#{version};
SQL語句大全

注意第二個事務執行 update 時，第一個事務已經送出了，是以第二個事務能夠讀取到第一個事務修改的 version。

下面這種極端的情況：

SQL語句大全

我們知道 MySQL 資料庫引擎 InnoDB，事務的隔離級别是 Repeatable Read，是以是不會出現髒讀、不可重複讀。

在這種極端情況下，第二個事務的 update 由于不能讀取第一個事務未送出的資料（第一個事務已經對這一條資料加了排他鎖，第二個事務需要等待擷取鎖），第二個事務擷取了排他鎖後，會發現 version 已經發生了改變進而送出失敗。

(2)悲觀鎖

悲觀鎖，簡單地說，就是從資料庫層面上做并發控制，去加鎖。

悲觀鎖的實作方式有兩種：共享鎖（讀鎖）和排它鎖（寫鎖）

共享鎖（IS鎖），實作方式是在 SQL 語句後加 LOCK IN SHARE MODE，比如 SELECT ... LOCK IN SHARE MODE，即在符合條件的rows 上都加了共享鎖，這樣的話，其他 session 可以讀取這些記錄，也可以繼續添加 IS 鎖，但是無法修改這些記錄直到你這個加鎖的 session 執行完成（否則直接鎖等待逾時）。

排它鎖（IX鎖），實作方式是在 SQL 語句後加

FOR UPDATE

，比如

SELECT ... FOR UPDATE

，即在符合條件的 rows 上都加了排它鎖，其他 session也就無法在這些記錄上添加任何的 S 鎖或 X 鎖。如果不存在一緻性非鎖定讀的話，那麼其他 session 是無法讀取和修改這些記錄的，但是 InnoDB 有非鎖定讀（快照讀并不需要加鎖），

for update

之後并不會阻塞其他

session

的快照讀取操作，除了

select ...lock in share mode

和

select ... for update

這種顯示加鎖的查詢操作。

通過對比，發現

for update

的加鎖方式無非是比

lock in share mode

的方式多阻塞了

select...lock in share mode

的查詢方式，并不會阻塞快照讀。

MySQL InnoDB 引擎預設的修改資料語句：

update，delete，insert

都會自動給涉及到的資料加上排他鎖，

select

語句預設不會加任何鎖類型。

以排它鎖為例

要使用悲觀鎖，我們必須關閉 MySQL 資料庫的自動送出屬性，因為 MySQL 預設使用 auto commit 模式，也就是說，當你執行一個更新操作後，MySQL 會立刻将結果進行送出。

set autocommit=0;
-- 0. 開始事務（三者選一就可以） --
begin;/begin work;/start transaction;
-- 1. 查詢出商品資訊 --
select status from t_goods where id=1 for update; 
-- 2. 根據商品資訊生成訂單 --
insert into t_orders (id,goods_id) values (null,1); 
-- 3. 修改商品 status 為 2 -- 
update t_goods set status=2; 
-- 4. 送出事務 --
commit;/commit work;
SQL語句大全

上面的查詢語句中，我們使用了 select…for update 的方式， 這樣就通過開啟排他鎖的方式實作了悲觀鎖。此時在 t_goods 表中，id 為 1 的 那條資料就被我們鎖定了，其它的事務必須等本次事務送出之後才能執行。這樣我們可以保證目前的資料不會被其它事務修改。

補充：

* MyISAM 在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖,在執行更新操作（UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖。
* MySQL InnoDB 預設行級鎖。行級鎖都是基于索引的，如果一條 SQL 語句用不到索引是不會使用行級鎖的，會使用表級鎖把整張表鎖住。
* 從上面對兩種鎖的介紹，我們知道兩種鎖各有優缺點，不可認為一種好于另一種，像樂觀鎖适用于寫比較少的情況下（多讀場景），即沖突真的很少發生的時候，這樣可以省去了鎖的開銷，加大了系統的整個吞吐量。但如果是多寫的情況，一般會經常産生沖突，這就會導緻上層應用會不斷的進行 retry，這樣反倒是降低了性能，是以一般多寫的場景下用悲觀鎖就比較合适。

認識觸發器

在 MySQL 中，觸發器是一個存儲程式，它對相關表中發生的插入、更新或删除等事件自動調用。

例如，你可以定義一個觸發器，在向表中插入新行之前自動調用。

MySQL 支援響應 INSERT、UPDATE 或 DELETE 事件而調用的觸發器。

SQL 标準定義了兩種類型的觸發器：行級觸發器和語句級觸發器。

* 行級觸發器是為每條插入、更新或删除的記錄激活的。 例如，如果一個表有 100 條記錄被插入、更新或删除，那麼這個觸發器就會為受影響的 100 條記錄自動調用 100 次。
* 語句級觸發器對每個事務執行一次，不管有多少行被插入、更新或删除。

MySQL 隻支援行級觸發器。它不支援語句級觸發器。
SQL語句大全

觸發器的優點

* 觸發器提供了另一種方法來檢查資料的完整性。
* 觸發器可以處理來自資料庫層的錯誤。 
* 觸發器提供了一種運作計劃任務的替代方法。通過使用觸發器，你不需要等待預定事件的運作，因為觸發器會在表内資料發生變化之前或之後自動調用。
* 觸發器對于審計表中的資料變化非常有用。

觸發器的劣勢

* 觸發器隻能提供擴充驗證，而不是所有的驗證。對于簡單的驗證，你可以使用 NOT NULL、UNIQUE、CHECK 和 FOREIGN KEY限制。
* 觸發器可能很難排除故障，因為它們在資料庫中自動執行，而用戶端應用程式可能不知道。
* 觸發器可能會增加 MySQL 伺服器的開銷。

(1) MySQL 建立觸發器

本章節将帶着大家學習如何使用 MySQL CREATE TRIGGER 語句在資料庫中建立一個觸發器。

MySQL CREATE TRIGGER 文法

CREATE TRIGGER 語句建立一個新的觸發器。

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
ON table_name FOR EACH ROW
trigger_body;

在這個文法中：

首先，在` CREATE TRIGGER `關鍵字後面指定要建立的觸發器的名稱。注意，觸發器的名稱在資料庫中必須是唯一的。
接下來，指定觸發器的動作時間，可以是` BEFORE `或` AFTER`，表示在每條記錄被修改之前或之後調用該觸發器。
然後，指定激活觸發器的操作，可以是 `INSERT`、`UPDATE` 或` DELETE`。
之後，在 `ON` 關鍵字後面指定觸發器所屬的表的名稱。
最後，指定觸發器激活時要執行的語句。如果你想執行多條語句，你可以使用` BEGIN END` 複合語句。

觸發器主體可以通路被

DM

L 語句影響的列的值。

為了區分

DML

啟動之前和之後的列的值，你可以使用

NEW

OLD

修飾符。

例如，如果你更新列描述，在觸發器主體中，你可以通路更新前的描述值

OLD.description

和新值

NEW.description

。

下表說明了 OLD 和 NEW 修改器的可用性。

Trigger Event OLD NEW
INSERT No Yes
UPDATE
DELETE
(2)MySQL 删除觸發器 
本章節将帶着大家學習如何使用 MySQL DROP TRIGGER 語句，從資料庫中删除一個觸發器。

MySQL DROP TRIGGER 文法

DROP TRIGGER 語句從資料庫中删除了一個觸發器。

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

首先，在 DROP TRIGGER 關鍵字後面指定要删除的觸發器的名稱。
第二，指定觸發器所屬模式的名稱。如果跳過模式名稱，該語句将删除目前資料庫中的觸發器。
第三，使用 IF EXISTS 選項，在觸發器存在的情況下有條件地删除觸發器。IF EXISTS 子句是可選的。

如果你不使用 IF EXISTS 子句而删除一個不存在的觸發器，MySQL 會發出一個錯誤。然而，如果你使用 IF EXISTS 子句，MySQL 會發出一個注釋。

DROP TRIGGER 要求與觸發器相關的表有 TRIGGER 權限。

注意，如果你删除一個表，MySQL 将自動放棄與該表相關的所有觸發器。

擴充

having

where

 的差別

  • having隻能用在group by之後，對分組後的結果進行篩選(即使用having的前提條件是分組)
  • where 肯定在 group by 之前

  • where 後的條件表達式裡不允許使用聚合函數，而having可以

    當一個查詢語句同時出現了

    where

     ，

    group by

    having

    order by

     的時候，執行順序和編寫順序是：
  • 執行

    where ...

     對全表資料做篩選，傳回第一個結果集
  • 針對第一個結果集使用

    group by

     分組，傳回第二個結果集
  • 針對第二個結果集中的每一組資料執行

    select...

     ，有幾次執行幾次，傳回第三個結果集
  • 針對第三個結果集執行

    having ...

     進行篩選，傳回第四個結果集
  • 針對第四個結果集排序

練習題

第二高的薪水

連結：

https://leetcode-cn.com/problems/second-highest-salary/

(1)思路一：使用子查詢

使用子查詢找出最大的

MAX（Salar）

，然後再找出小于

MAX(Salary)

的最大值就是課程成績的第二高值。

SELECT  DISTINCT MAX(Salary) AS SecondHighestSalary
FROM Employee 
WHERE `Salary` < (
    SELECT MAX(Salary) 
    FROM Employee
);

但是題目中有特殊的輸入情況，遠遠這樣是不夠的

(2)思路二：使用子查詢和

LIMIT

子句

文法:

LIMIT n

子句表示查詢結果傳回前n條資料,

OFFSET x

表示跳過x條語句

=>

LIMIT y OFFSET x

分句表示查詢結果跳過 x 條資料，讀取前 y 條資料;

SELECT DISTINCT
    Salary AS SecondHighestSalary
FROM
    Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1

特殊情況,本表沒有第二高的工資,隻有一項紀錄,為了克服這種情況,我們将此設定為臨時表

SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary;

(3)思路三:判斷空值的函數（IFNULL）

IFNULL(a,b)函數解釋：(1)如果value1不是空，結果傳回a;(2)如果value1是空，結果傳回b

是以，解題思路應當是：

SELECT IFNULL(SQL語句,null) AS 'SecondHighestSalary';

 
SELECT IFNULL(
(SELECT DISTINCT Salary
FROM Employee 
ORDER BY Salary DESC
limit 1,1),NULL
) AS SecondHighestSalary;

第N高的薪水

limit函數結構為：limit m , n,表示從第m+1條資料取出n條資料。

網址：

https://leetcode-cn.com/problems/nth-highest-salary/

參考上面題目的做法，代碼如下

(1)解法一：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  DECLARE m INT;
  SET m = N - 1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT IFNULL(
            (SELECT DISTINCT Salary
            FROM Employee 
            ORDER BY Salary DESC
            LIMIT m,1),NULL
            ) 
  );
END

(2)解法二：單表查詢

若本題采取order by排序加limit限制得到。有兩個細節：

* 同薪同名且不跳級的問題，解決辦法是用group by按薪水分組後再order by

* 排名第N高意味着要跳過N-1個資料，由于無法直接用limit N-1，是以需先在函數開頭處理N為N=N-1。

注：這裡不能直接用limit N-1是因為limit和offset字段後面隻接受正整數（意味着0、負數、小數都不行）或者單一變量（意味着不能用表達式），也就是說想取一條，limit 2-1、limit 1.1這類的寫法都是報錯的。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N := N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
            salary
      FROM 
            employee
      GROUP BY 
            salary
      ORDER BY 
            salary DESC
      LIMIT N, 1
  );
END

連續出現的數字

https://leetcode-cn.com/problems/consecutive-numbers/

(1)官方解法：三表連接配接

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num
;

超過經理收入的員工

https://leetcode-cn.com/problems/employees-earning-more-than-their-managers/

下面代碼是我的解法。

SELECT a.Name AS Employee
FROM Employee a
WHERE ManagerId = (
    SELECT b.Id 
    FROM Employee b
    WHERE b.Id = a.ManagerId
) AND Salary > (
    SELECT Salary
    FROM Employee c
    WHERE c.Id = a.ManagerId
);

因為我的解法實在是不夠簡約，是以參考了官方解法

(2)解法二

SELECT
    a.Name AS 'Employee'
FROM
    Employee AS a,
    Employee AS b
WHERE
    a.ManagerId = b.Id
        AND a.Salary > b.Salary;

(3)解法三：使用

JOIN

語句

實際上， JOIN 是一個更常用也更有效的将表連起來的辦法，我們使用 ON 來指明條件。

SELECT
     a.NAME AS Employee
FROM Employee AS a JOIN Employee AS b
     ON a.ManagerId = b.Id
     AND a.Salary > b.Salary;

查找重複的電子郵箱

https://leetcode-cn.com/problems/duplicate-emails/

(1)解法一：（自己寫的，不推薦）

SELECT DISTINCT a.Email 
FROM  Person a,
      Person b
WHERE a.Email =b.Email and a.Id != b.Id;

(2)解法二：使用

GROUP BY

和臨時表

重複的電子郵箱存在多次。要計算每封電子郵件的存在次數，我們可以使用以下代碼。

select Email, count(Email) as num
from Person
group by Email;

以此作為臨時表，我們可以得到下面的解決方案。

select Email from
(
  select Email, count(Email) as num
  from Person
  group by Email
) as statistic
where num > 1
;

GROUP BY

HAVING

條件

向

GROUP BY

添加條件的一種更常用的方法是使用

HAVING

子句，該子句更為簡單高效。

是以我們可以将上面的解決方案重寫為：

select Email
from Person
group by Email
having count(Email) > 1;

從不訂購的客戶

https://leetcode-cn.com/problems/customers-who-never-order/

(1)解法一:多表連接配接

SELECT c.Name AS Customers
FROM Customers c
LEFT JOIN Orders o
ON c.Id = o.CustomerId
WHERE o.CustomerId is NULL;

(2)解法二:使用子查詢和

NOT IN

 
SELECT `c`.`Name` AS 'Customers'
FROM `Customers` `c`
WHERE `c`.Id NOT IN
(
    SELECT `CustomerId` 
    FROM `Orders`
);

部門工資最高的員工

https://leetcode-cn.com/problems/department-highest-salary/

解法步驟如下：

(1)先建立臨時表，查詢每個部門内的最高工資

SELECT DepartmentId, MAX(Salary)
FROM Employee
GROUP BY DepartmentId

(2)使用

JOIN

IN

 
SELECT  d.Name AS Department,
        e.Name AS Employee,
        e.Salary
FROM 
        Employee e
LEFT JOIN 
        Department d
ON e.DepartmentId = d.Id
WHERE 
(e.DepartmentId , e.Salary) IN 
(
    SELECT DepartmentId, MAX(Salary)
    FROM Employee
    GROUP BY DepartmentId
)

部門工資前三高的所有員工

https://leetcode-cn.com/problems/department-top-three-salaries/

解題步驟

(1)公司裡前 3 高的薪水意味着有不超過 3 個工資比這些值大。

select e1.Name as 'Employee', e1.Salary
from Employee e1
where 3 >
(
    select count(distinct e2.Salary)
    from Employee e2
    where e2.Salary > e1.Salary
)
;

(2)連接配接表

SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
    Employee e1
        JOIN
    Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        )
;

剛開始這道題我還沒有看懂,看了其他大神的解析才略微了解其意

對于步驟一中的SQL語句,可以這麼了解

存在表中有資料:

e1 = e2 = [4,5,6,7,8]

=>那麼

e1.Salary = 4，e2.Salary

可以取值

[5,6,7,8]

count(DISTINCT e2.Salary) = 4

e1.Salary = 5

e2.Salary

[6,7,8]

count(DISTINCT e2.Salary) = 3

e1.Salary = 6，e2.Salary

[7,8]

count(DISTINCT e2.Salary) = 2

e1.Salary = 7

e2.Salary

[8]

count(DISTINCT e2.Salary) = 1

e1.Salary = 8

e2.Salary

[]

count(DISTINCT e2.Salary) = 0

最後

3 > count(DISTINCT e2.Salary)

，是以

e1.Salary

可取值為

[6,7,8]

，即集合前 3 高的薪水

删除重複的電子郵箱

https://leetcode-cn.com/problems/delete-duplicate-emails/

首先：

SELECT 
    p1.*, p2.*
FROM
    Person p1
        JOIN
    Person p2 ON p1.Email = p2.Email
ORDER BY p1.Id;
SQL語句大全

那麼，要删除的，就是其中

p1.id > p2.id

的表資料

(1)符合條件的：

select p1.*
from person p1,
     person p2
where p1.id > p2.id and p1.email=p2.email;

(2)delect

DELETE p1 
FROM Person p1,
     Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id

上升的溫度

https://leetcode-cn.com/problems/rising-temperature/

 
SELECT w2.id
FROM  Weather w1
JOIN  Weather w2
ON DATEDIFF(w2.recordDate,w1.recordDate) = 1 
   AND w2.Temperature >w1.Temperature ;

大的國家

https://leetcode-cn.com/problems/big-countries/

 
SELECT name,population,area
FROM World
WHERE area>=3000000 OR population>=25000000;

(2)解法二：

SELECT
    name, population, area
FROM
    world
WHERE
    area > 3000000

UNION

SELECT
    name, population, area
FROM
    world
WHERE
    population > 25000000
;

有趣的電影

https://leetcode-cn.com/problems/not-boring-movies/

(1)解法：使用 MOD() 函數

select *
from cinema
where mod(id,2) = 1 and description != 'boring'
order by rating desc

超過5名學生的課

https://leetcode-cn.com/problems/classes-more-than-5-students/

(1)解法一：使用 GROUP BY 和 COUNT 獲得每門課程的學生數量。

臨時表

SELECT
    class, COUNT(DISTINCT student)
FROM
    courses
GROUP BY class
;

是以

SELECT
    class
FROM
    (SELECT
        class, COUNT(DISTINCT student) AS num
    FROM
        courses
    GROUP BY class) AS temp_table
WHERE
    num >= 5
;
           
SELECT
    class
FROM
    courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5;
上一篇: 【校驗】校驗彙總
下一篇: Qt qml 下使QtCharts