SQL

SQL 是訪問和處理關系數據庫的計算機標準語言。

概述

應用程序不需要自己管理數據，而是通過數據庫軟件提供的接口來讀寫數據。至於數據本身如何存儲到文件，那是數據庫軟件的事情，應用程序自己並不關心：

┌──────────────┐
│ application  │
└──────────────┘
       ▲│
       ││
   read││write
       ││
       │▼
┌──────────────┐
│   database   │
└──────────────┘

這樣一來，編寫應用程序的時候，數據讀寫的功能就被大大地簡化了。

數據模型

數據庫按照數據結構來組織、存儲和管理數據，實際上，數據庫一共有三種模型：層次模型、網狀模型和關系模型。

層次模型

層次模型就是以「上下級」的層次關系來組織數據的一種方式，層次模型的數據結構看起來就像一顆樹：

            ┌─────┐
            │     │
            └─────┘
               │
       ┌───────┴───────┐
       │               │
    ┌─────┐         ┌─────┐
    │     │         │     │
    └─────┘         └─────┘
       │               │
   ┌───┴───┐       ┌───┴───┐
   │       │       │       │
┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐
│     │ │     │ │     │ │     │
└─────┘ └─────┘ └─────┘ └─────┘

網狀模型

網狀模型把每個數據節點和其它很多節點都連接起來，它的數據結構看起來就像很多城市之間的路網：

     ┌─────┐      ┌─────┐
   ┌─│     │──────│     │──┐
   │ └─────┘      └─────┘  │
   │    │            │     │
   │    └──────┬─────┘     │
   │           │           │
┌─────┐     ┌─────┐     ┌─────┐
│     │─────│     │─────│     │
└─────┘     └─────┘     └─────┘
   │           │           │
   │     ┌─────┴─────┐     │
   │     │           │     │
   │  ┌─────┐     ┌─────┐  │
   └──│     │─────│     │──┘
      └─────┘     └─────┘

關系模型

關系模型把數據看作是一個二維表格，任何數據都可以通過行號+列號來唯一確定，它的數據模型看起來就是一個 Excel 表：

┌─────┬─────┬─────┬─────┬─────┐
│     │     │     │     │     │
├─────┼─────┼─────┼─────┼─────┤
│     │     │     │     │     │
├─────┼─────┼─────┼─────┼─────┤
│     │     │     │     │     │
├─────┼─────┼─────┼─────┼─────┤
│     │     │     │     │     │
└─────┴─────┴─────┴─────┴─────┘

數據類型

關系數據庫支持的標準數據類型包括數值、字符串、時間等：

名稱	類型	說明
INT	整型	4 字節整數類型，範圍約 +/-21 億
BIGINT	長整型	8 字節整數類型，範圍約 +/-922 億億
REAL/FLOAT(24)	浮點型	4 字節浮點數，範圍約 +/-1038
DOUBLE	浮點型	8 字節浮點數，範圍約 +/-10308
DECIMAL(M,N)	高精度小數	由用戶指定精度的小數，例如，DECIMAL(20,10) 表示一共 20 位，其中小數 10 位，通常用於財務計算
CHAR(N)	定長字符串	存儲指定長度的字符串，例如，CHAR(100) 總是存儲 100 個字符的字符串
VARCHAR(N)	變長字符串	存儲可變長度的字符串，例如，VARCHAR(100) 可以存儲 0~100 個字符的字符串
BOOLEAN	布爾類型	存儲 True 或者 False
DATE	日期類型	存儲日期，例如，2016-06-22
TIME	時間類型	存儲時間，例如，12:20:59
DATETIME	日期和時間類型	存儲日期+時間，例如，2018-06-22 12:20:59

選擇數據類型的時候，要根據業務規則選擇合適的類型。通常來說，BIGINT 能滿足整數存儲的需求，VARCHAR(N)能滿足字符串存儲的需求，這兩種類型是使用最廣泛的。

SQL 是結構化查詢語言的縮寫，用來訪問和操作數據庫系統。SQL 語句既可以查詢數據庫中的數據，也可以添加、更新和刪除數據庫中的數據，還可以對數據庫進行管理和維護操作。

SQL 語言關鍵字不區分大小寫。但是，針對不同的數據庫，對於表名和列名，有的數據庫區分大小寫，有的數據庫不區分大小寫。同一個數據庫，有的在 Linux 上區分大小寫，有的在 Windows 上不區分大小寫。

MySQL

MySQL 是目前應用最廣泛的開源關系數據庫。要在 Windows 或 Mac 上安裝 MySQL，首先從 MySQL 官方網站下載最新的 MySQL Community Server 版本：https://dev.mysql.com/downloads/mysql/

MySQL 在安裝過程中會自動創建一個 root 用戶，並提示輸入 root 口令。要在 Linux 上安裝 MySQL，可以使用發行版的包管理器。例如，Debian 和 Ubuntu 用戶可以簡單地通過命令 apt-get install mysql-server 安裝最新的 MySQL 版本。

MySQL 安裝後會自動在後臺運行。為了驗證 MySQL 安裝是否正確，需要通過 mysql 這個命令行程序來連接 MySQL 服務器。在命令提示符下輸入 mysql -u root -p，然後輸入口令，如果一切正確，就會連接到 MySQL 服務器，同時提示符變為 mysql>。假設遠程 MySQL Server 的 IP 地址是 10.0.1.99，那麽就使用-h 指定 IP 或域名：mysql -h 10.0.1.99 -u root -p。輸入 exit 退出 MySQL 命令行。註意，MySQL 服務器仍在後臺運行。

# 列出所有數據庫
SHOW DATABASES;

# 創建一個新數據庫
CREATE DATABASE 數據庫名稱;

# 刪除一個數據庫
DROP DATABASE 數據庫名稱;

# 將一個數據庫切換為當前數據庫
USE 數據庫名稱

# 列出當前數據庫的所有表
SHOW TABLES;

# 查看一個表的結構
DESC 表名稱;

# 創建表
CREATE TABLE 表名稱;

# 刪除表
DROP TABLE 表名稱;

修改表就比較復雜。

要給 students 表新增一列 birth，使用：

1	ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;

要修改 birth 列，例如把列名改為 birthday，類型改為 VARCHAR(20)：

1	ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;

要刪除列，使用：

1	ALTER TABLE students DROP COLUMN birthday;

關系模型

關系數據庫是建立在關系模型上的。而關系模型本質上就是若幹個存儲數據的二維表，可以把它們看作很多 Excel 表。表的每一行稱為記錄（Record），記錄是一個邏輯意義上的數據。表的每一列稱為字段（Column），同一個表的每一行記錄都擁有相同的若幹字段。

字段定義了數據類型（整型、浮點型、字符串、日期等），以及是否允許為 NULL。註意 NULL 表示字段數據不存在。一個整型字段如果為 NULL 不表示它的值為 0，同樣的，一個字符串型字段為 NULL 也不表示它的值為空串 ‘ ‘。通常情況下，字段應該避免允許為 NULL。不允許為 NULL 可以簡化查詢條件，加快查詢速度，也利於應用程序讀取數據後無需判斷是否為 NULL。

和 Excel 表有所不同的是，關系數據庫的表和表之間需要建立「一對多」，「多對一」和「一對一」的關系，這樣才能夠按照應用程序的邏輯來組織和存儲數據。

在關系數據庫中，關系是通過主鍵和外鍵來維護的。

主鍵

在關系數據庫中，一張表中的每一行數據被稱為一條記錄。一條記錄就是由多個字段組成的。每一條記錄都包含若幹定義好的字段。同一個表的所有記錄都有相同的字段定義。

對於關系表，有個很重要的約束，就是任意兩條記錄不能重復。不能重復不是指兩條記錄不完全相同，而是指能夠通過某個字段唯一區分出不同的記錄，這個字段被稱為主鍵。

對主鍵的要求，最關鍵的一點是：記錄一旦插入到表中，主鍵最好不要再修改，因為主鍵是用來唯一定位記錄的，修改了主鍵，會造成一系列的影響。

由於主鍵的作用十分重要，如何選取主鍵會對業務開發產生重要影響。所以，選取主鍵的一個基本原則是：不使用任何業務相關的字段作為主鍵。

聯合主鍵

關系數據庫實際上還允許通過多個字段唯一標識記錄，即兩個或更多的字段都設置為主鍵，這種主鍵被稱為聯合主鍵。對於聯合主鍵，允許一列有重復，只要不是所有主鍵列都重復即可。沒有必要的情況下，盡量不使用聯合主鍵，因為它給關系表帶來了復雜度的上升。

外鍵

外鍵並不是通過列名實現的，而是通過定義外鍵約束實現的：

1	ALTER TABLE students ADD CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES classes (id);

其中，外鍵約束的名稱 fk_class_id 可以任意，FOREIGN KEY (class_id) 指定了 class_id 作為外鍵，REFERENCES classes (id) 指定了這個外鍵將關聯到 classes 表的 id 列（即 classes 表的主鍵）。

通過定義外鍵約束，關系數據庫可以保證無法插入無效的數據。由於外鍵約束會降低數據庫的性能，大部分互聯網應用程序為了追求速度，並不設置外鍵約束，而是僅靠應用程序自身來保證邏輯的正確性。這種情況下，class_id 僅僅是一個普通的列，只是它起到了外鍵的作用而已。

要刪除一個外鍵約束，也是通過 ALTER TABLE 實現的：

1	ALTER TABLE students DROP FOREIGN KEY fk_class_id;

註意：刪除外鍵約束並沒有刪除外鍵這一列。

索引

在關系數據庫中，如果有上萬甚至上億條記錄，在查找記錄的時候，想要獲得非常快的速度，就需要使用索引。索引是關系數據庫中對某一列或多個列的值進行預排序的數據結構。通過使用索引，可以讓數據庫系統不必掃描整個表，而是直接定位到符合條件的記錄，這樣就大大加快了查詢速度。

1	ALTER TABLE students ADD INDEX idx_score (score);

使用 ADD INDEX idx_score (score) 就創建了一個名稱為 idx_score，使用列 score 的索引。索引名稱是任意的，索引如果有多列，可以在括號裏依次寫上，例如：

1	ALTER TABLE students ADD INDEX idx_name_score (name, score);

索引的效率取決於索引列的值是否散列，即該列的值如果越互不相同，那麽索引效率越高。反過來，如果記錄的列存在大量相同的值，例如 gender 列，大約一半的記錄值是 M，另一半是 F，因此，對該列創建索引就沒有意義。

可以對一張表創建多個索引。索引的優點是提高了查詢效率，缺點是在插入、更新和刪除記錄時，需要同時修改索引，因此，索引越多，插入、更新和刪除記錄的速度就越慢。

對於主鍵，關系數據庫會自動對其創建主鍵索引。使用主鍵索引的效率是最高的，因為主鍵會保證絕對唯一。

唯一索引

1	ALTER TABLE students ADD UNIQUE INDEX uni_name (name);

通過 UNIQUE 關鍵字就添加了一個唯一索引。

強製使用指定索引

在查詢的時候，數據庫系統會自動分析查詢語句，並選擇一個最合適的索引。但是很多時候，數據庫系統的查詢優化器並不一定總是能使用最優索引。可以使用 FORCE INDEX 強製查詢使用指定的索引。例如：

1	SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

指定索引的前提是索引 idx_class_id 必須存在。

查詢數據

在關系數據庫中，最常用的操作就是查詢。

通過 mysql -u root -p < init-data.sql 命令導入初始化數據。

-- 如果test數據庫不存在，就創建test數據庫：
CREATE DATABASE IF NOT EXISTS test;

-- 切換到test數據庫
USE test;

-- 刪除classes表和students表（如果存在）：
DROP TABLE IF EXISTS classes;
DROP TABLE IF EXISTS students;

-- 創建classes表：
CREATE TABLE classes (
    id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 創建students表：
CREATE TABLE students (
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL,
    name VARCHAR(100) NOT NULL,
    gender VARCHAR(1) NOT NULL,
    score INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入classes記錄：
INSERT INTO classes(id, name) VALUES (1, '一班');
INSERT INTO classes(id, name) VALUES (2, '二班');
INSERT INTO classes(id, name) VALUES (3, '三班');
INSERT INTO classes(id, name) VALUES (4, '四班');

-- 插入students記錄：
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'M', 90);
INSERT INTO students (id, class_id, name, gender, score) VALUES (2, 1, '小紅', 'F', 95);
INSERT INTO students (id, class_id, name, gender, score) VALUES (3, 1, '小軍', 'M', 88);
INSERT INTO students (id, class_id, name, gender, score) VALUES (4, 1, '小米', 'F', 73);
INSERT INTO students (id, class_id, name, gender, score) VALUES (5, 2, '小白', 'F', 81);
INSERT INTO students (id, class_id, name, gender, score) VALUES (6, 2, '小兵', 'M', 55);
INSERT INTO students (id, class_id, name, gender, score) VALUES (7, 2, '小林', 'M', 85);
INSERT INTO students (id, class_id, name, gender, score) VALUES (8, 3, '小新', 'F', 91);
INSERT INTO students (id, class_id, name, gender, score) VALUES (9, 3, '小王', 'M', 89);
INSERT INTO students (id, class_id, name, gender, score) VALUES (10, 3, '小麗', 'F', 85);

-- OK:
SELECT 'ok' as 'result:';

基本查詢

1	SELECT * FROM <表名>

查詢一個表的所有行和所有列的數據。

條件查詢

SELECT 語句可以通過 WHERE 條件來設定查詢條件，查詢結果是滿足查詢條件的記錄。

1	SELECT * FROM <表名> WHERE <條件表達式>;

SELECT 語句可以通過 WHERE 條件來設定查詢條件，查詢結果是滿足查詢條件的記錄。第一種條件表達式可以用 <條件 1> AND <條件 2> 表達滿足條件 1 並且滿足條件 2。第二種條件是 <條件 1> OR <條件 2>，表示滿足條件 1 或者滿足條件 2。第三種條件是 NOT <條件>，表示「不符合該條件」的記錄。NOT 條件 <>，因此，NOT 查詢不是很常用。

SELECT * FROM students WHERE score >= 80;
SELECT * FROM students WHERE score >= 80 AND gender = 'M';
SELECT * FROM students WHERE score >= 80 OR gender = 'M';

SELECT * FROM students WHERE NOT class_id = 2;
SELECT * FROM students WHERE class_id <> 2;

SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';

投影查詢

使用 SELECT * FROM <表名> WHERE <條件表達式> 可以選出表中的若幹條記錄。返回的二維表結構和原表是相同的，即結果集的所有列與原表的所有列都一一對應。如果只希望返回某些列的數據，而不是所有列的數據，可以用 SELECT 列 1, 列 2, 列 3 FROM …，讓結果集僅包含指定列。這種操作稱為投影查詢。

這樣返回的結果集就只包含了指定的列，並且，結果集的列的順序和原表可以不一樣。

使用 SELECT 列 1, 列 2, 列 3 FROM … 時，還可以給每一列起個別名，這樣，結果集的列名就可以與原表的列名不同。它的語法是 SELECT 列 1 別名 1, 列 2 別名 2, 列 3 別名 3 FROM …。

1
2
3

SELECT id, score, name FROM students;
SELECT id, score points, name FROM students;
SELECT id, score points, name FROM students WHERE gender = 'M';

排序

使用 SELECT 查詢時，查詢結果集通常是按照 id 排序的，也就是根據主鍵排序。可以加上 ORDER BY 子句，選擇排序方式。

-- ASC 表示「升序」
SELECT * FROM <表名> WHERE <條件表達式> ORDER BY <屬性名> ASC;

-- DESC 表示「倒序」
SELECT * FROM <表名> WHERE <條件表達式> ORDER BY <屬性名> DESC;

要進一步排序，可以繼續添加列名。

1 2	-- DESC 表示「倒序」 SELECT * FROM <表名> WHERE <條件表達式> ORDER BY <屬性名1> DESC, <屬性名2> DESC;

表示先按屬性名 1列倒序，如果有相同的，再按屬性名 2列排序：

ORDER BY 子句要放到 WHERE 子句後面

SELECT id, name, gender, score FROM students ORDER BY score ASC;
SELECT id, name, gender, score FROM students ORDER BY score DESC;
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender ASC;
SELECT id, name, gender, score FROM students WHERE class_id = 1 ORDER BY score DESC;

分頁查詢

分頁實際上就是從結果集中「截取」出第 M~N 條記錄。這個查詢可以通過 LIMIT M OFFSET N 子句實現。對結果集從 N 號記錄開始，最多取 M 條。

分頁查詢的關鍵在於，首先要確定每頁需要顯示的結果數量 pageSize，然後根據當前頁的索引 pageIndex（從 0 開始），確定 LIMIT 和 OFFSET 應該設定的值：

LIMIT 總是設定為 pageSize
OFFSET 計算公式為 pageSize * pageIndex

OFFSET 超過了查詢的最大數量並不會報錯，而是得到一個空的結果集。

在 MySQL 中，LIMIT M OFFSET N 還可以簡寫成 LIMIT M, N。

使用 LIMIT M OFFSET N 分頁時，隨著 N 越來越大，查詢效率也會越來越低。

聚合查詢

對於統計總數、平均數這類計算，SQL 提供了專門的聚合函數，使用聚合函數進行查詢，就是聚合查詢，它可以快速獲得結果。

使用 SQL 內置的 COUNT() 函數查詢記錄數量。

1	SELECT COUNT(*) FROM <表名>;

COUNT(*) 表示查詢所有列的行數，要註意聚合的計算結果雖然是一個數字，但查詢的結果仍然是一個二維表，只是這個二維表只有一行一列，並且列名是 COUNT(*)。

通常，使用聚合查詢時，應該給列名設置一個別名，便於處理結果：

1	SELECT COUNT(*) 別名 FROM <表名>;

除了 COUNT() 函數外，SQL 還提供了如下聚合函數：

函數	說明
SUM	計算某一列的合計值，該列必須為數值類型
AVG	計算某一列的平均值，該列必須為數值類型
MAX	計算某一列的最大值
MIN	計算某一列的最小值

要特別註意：如果聚合查詢的 WHERE 條件沒有匹配到任何行，COUNT()會返回 0，而 SUM()、AVG()、MAX()和 MIN()會返回 NULL：

SELECT COUNT(*) FROM students;
SELECT COUNT(*) num FROM students;
SELECT COUNT(*) boys FROM students WHERE gender = 'M';
SELECT AVG(score) average FROM students WHERE gender = 'M';

分組聚合

對於聚合查詢，SQL 還提供了「分組聚合」的功能。

1 2	-- 按屬性名分組 SELECT COUNT(*) 別名 FROM <表名> GROUP BY <屬性名>;

執行該 SELECT 語句時，會把屬性名對應取值相同的列先分組，再分別計算，因此，得到了多行結果。

SELECT COUNT(*) num FROM students GROUP BY class_id;
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;

-- 統計各班的男生和女生人數
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;

多表查詢

SELECT 查詢不但可以從一張表查詢數據，還可以從多張表同時查詢數據。查詢多張表的語法是：SELECT * FROM <表 1>, <表 2>。

這種一次查詢兩個表的數據，查詢的結果也是一個二維表，它是表 1和表 2表的「乘積」，即表 1的每一行與表 2的每一行都兩兩拼在一起返回。結果集的列數是兩表的列數之和，行數是兩表的行數之積。

這種多表查詢又稱笛卡爾查詢，使用笛卡爾查詢時要非常小心，由於結果集是目標表的行數乘積，對兩個各自有 100 行記錄的表進行笛卡爾查詢將返回 1 萬條記錄，對兩個各自有 1 萬行記錄的表進行笛卡爾查詢將返回 1 億條記錄。

SELECT
    表1.屬性1 別名1,
    表1.屬性2,
    表1.屬性3,
    表1.屬性4,
    表2.屬性1 別名2,
    表2.屬性2 別名3
FROM 表1, 表2;

註意，多表查詢時，要使用表名.列名這樣的方式來引用列和設置別名，這樣就避免了結果集的列名重復問題。但是，用表名.列名這種方式列舉兩個表的所有列實在是很麻煩，所以 SQL 還允許給表設置一個別名，讓投影查詢中引用起來稍微簡潔一點：

SELECT
    表別名1.屬性1 別名1,
    表別名1.屬性2,
    表別名1.屬性3,
    表別名1.屬性4,
    表別名2.屬性1 別名2,
    表別名2.屬性2 別名3
FROM 表1 表別名1, 表2 表別名2;

註意到 FROM 子句給表設置別名的語法是 FROM <表名 1> <別名 1>, <表名 2> <別名 2>。

多表查詢也是可以添加 WHERE 條件的：

SELECT
    表別名1.屬性1 別名1,
    表別名1.屬性2,
    表別名1.屬性3,
    表別名1.屬性4,
    表別名2.屬性1 別名2,
    表別名2.屬性2 別名3
FROM 表1 表別名1, 表2 表別名2;
WHERE 表別名1.屬性1 = 'M' AND 表別名2.屬性1 = 1;

1	SELECT * FROM students, classes;

這種一次查詢兩個表的數據，查詢的結果也是一個二維表，它是 students 表和 classes 表的「乘積」，即 students 表的每一行與 classes 表的每一行都兩兩拼在一起返回。結果集的列數是 students 表和 classes 表的列數之和，行數是 students 表和 classes 表的行數之積。

上述查詢的結果集有兩列 id 和兩列 name，兩列 id 是因為其中一列是 students 表的 id，而另一列是 classes 表的 id，但是在結果集中，不好區分。兩列 name 同理。

要解決這個問題，仍然可以利用投影查詢的「設置列的別名」來給兩個表各自的 id 和 name 列起別名：

SELECT
    students.id sid,
    students.name,
    students.gender,
    students.score,
    classes.id cid,
    classes.name cname
FROM students, classes;

還可以使用別名：

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;

連接查詢

連接查詢是另一種類型的多表查詢。連接查詢對多個表進行 JOIN 運算，簡單地說，就是先確定一個主表作為結果集，然後，把其它表的行有選擇性地「連接」在主表結果集上。

例如，想要選出 students 表的所有學生信息，可以用一條簡單的 SELECT 語句完成：

1	SELECT s.id, s.name, s.class_id, s.gender, s.score FROM students s;

但是，假設希望結果集同時包含所在班級的名稱，上面的結果集只有 class_id 列，缺少對應班級的 name 列。

現在問題來了，存放班級名稱的 name 列存儲在 classes 表中，只有根據 students 表的 class_id，找到 classes 表對應的行，再取出 name 列，就可以獲得班級名稱。

這時，連接查詢就派上了用場。先使用最常用的一種內連接 INNER JOIN 來實現：

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;

註意 INNER JOIN 查詢的寫法是：

先確定主表，仍然使用 FROM <表 1> 的語法；
再確定需要連接的表，使用 INNER JOIN <表 2> 的語法；
然後確定連接條件，使用 ON <條件…>，這裏的條件是 s.class_id = c.id，表示 students 表的 class_id 列與 classes 表的 id 列相同的行需要連接；
可選：加上 WHERE 子句、ORDER BY 等子句。

內連接（INNER JOIN）

使用 INNER JOIN 只返回同時存在於兩張表的行數據。INNER JOIN 是最常用的一種 JOIN 查詢，它的語法是 SELECT … FROM <表 1> INNER JOIN <表 2> ON <條件…>；。

左外連接（LEFT OUTER JOIN）

使用 LEFT OUTER JOIN 返回左表都存在的行。如果某一行僅在左表存在，那麽結果集就會以 NULL 填充剩下的字段。

右外連接（RIGHT OUTER JOIN）

使用 RIGHT OUTER JOIN 返回右表都存在的行。如果某一行僅在右表存在，那麽結果集就會以 NULL 填充剩下的字段。

全外連接（FULL OUTER JOIN）

使用 FULL OUTER JOIN，會把兩張表的所有記錄全部選擇出來，並且，自動把對方不存在的列填充為 NULL。

修改數據

關系數據庫的基本操作就是增刪改查，即 CRUD：Create、Retrieve、Update、Delete。而對於增、刪、改，對應的 SQL 語句分別是：

INSERT：插入新記錄；
UPDATE：更新已有記錄；
DELETE：刪除已有記錄。

INSERT

當需要向數據庫表中插入一條新記錄時，就必須使用 INSERT 語句。該語句的基本語法是：

1	INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

例如，向 students 表插入一條新記錄，先列舉出需要插入的字段名稱，然後在 VALUES 子句中依次寫出對應字段的值：

1	INSERT INTO students (class_id, name, gender, score) VALUES (2, '你猜', 'M', 80);

如果一個字段有默認值，那麽在 INSERT 語句中也可以不出現。要註意，字段順序不必和數據庫表的字段順序一致，但值的順序必須和字段順序一致。

還可以一次性添加多條記錄，只需要在 VALUES 子句中指定多個記錄值，每個記錄是由 (…) 包含的一組值：

1
2
3

INSERT INTO students (class_id, name, gender, score) VALUES
  (1, '大寶', 'M', 87),
  (2, '二寶', 'M', 81);

插入或替換

如果希望插入一條新記錄（INSERT），但如果記錄已經存在，就先刪除原記錄，再插入新記錄。此時，可以使用 REPLACE 語句，這樣就不必先查詢，再決定是否先刪除再插入：

1	REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

若 id=1 的記錄不存在，REPLACE 語句將插入新記錄，否則，當前 id=1 的記錄將被刪除，然後再插入新記錄。

插入或更新

如果希望插入一條新記錄（INSERT），但如果記錄已經存在，就更新該記錄，此時，可以使用 INSERT INTO … ON DUPLICATE KEY UPDATE … 語句：

1	INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

若 id=1 的記錄不存在，INSERT 語句將插入新記錄，否則，當前 id=1 的記錄將被更新，更新的字段由 UPDATE 指定。

插入或忽略

如果希望插入一條新記錄（INSERT），但如果記錄已經存在，就啥事也不幹直接忽略，此時，可以使用 INSERT IGNORE INTO … 語句：

1	INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

若 id=1 的記錄不存在，INSERT 語句將插入新記錄，否則，不執行任何操作。

寫入查詢結果集

如果查詢結果集需要寫入到表中，可以結合 INSERT 和 SELECT，將 SELECT 語句的結果集直接插入到指定表中。

例如，創建一個統計成績的表 statistics，記錄各班的平均成績：

CREATE TABLE statistics (
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL,
    average DOUBLE NOT NULL,
    PRIMARY KEY (id)
);

然後就可以用一條語句寫入各班的平均成績：

1	INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;

UPDATE

如果要更新數據庫表中的記錄，就必須使用 UPDATE 語句。該語句的基本語法是：

1	UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

1	UPDATE students SET name='你猜', score=66 WHERE id=1;

在 UPDATE 語句中，更新字段時可以使用表達式。例如，把所有 80 分以下的同學的成績加 10 分：

1	UPDATE students SET score=score+10 WHERE score<80;

UPDATE 語句可以沒有 WHERE 條件，例如：

1	UPDATE students SET score=60;

這時，整個表的所有記錄都會被更新。

DELETE

如果要刪除數據庫表中的記錄，可以使用 DELETE 語句。該語句的基本語法是：

1	DELETE FROM <表名> WHERE ...;

1 2	DELETE FROM students WHERE id=1; DELETE FROM students WHERE id>=5 AND id<=7;

事務

在執行 SQL 語句的時候，某些業務要求，一系列操作必須全部執行，而不能僅執行一部分。數據庫事務可以確保該事務範圍內的所有操作都可以全部成功或者全部失敗。如果事務失敗，那麽效果就和沒有執行這些 SQL 一樣，不會對數據庫數據有任何改動。

可見，數據庫事務具有這 4 個特性：

Atomic，原子性，將所有 SQL 作為原子工作單元執行，要麽全部執行，要麽全部不執行；
Consistent，一致性，事務完成後，所有數據的狀態都是一致的，比如轉賬， A 賬戶只要減去了 100，B 賬戶則必定加上了 100；
Isolation，隔離性，如果有多個事務並發執行，每個事務作出的修改必須與其它事務隔離；
Duration，持久性，即事務完成後，對數據庫數據的修改被持久化存儲。

對於單條 SQL 語句，數據庫系統自動將其作為一個事務執行，這種事務被稱為隱式事務。

要手動把多條 SQL 語句作為一個事務執行，使用 BEGIN 開啟一個事務，使用 COMMIT 提交一個事務，這種事務被稱為顯式事務，例如，把上述的轉賬操作作為一個顯式事務：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

很顯然多條 SQL 語句要想作為一個事務執行，就必須使用顯式事務。COMMIT 是指提交事務，即試圖把事務內的所有 SQL 所做的修改永久保存。如果 COMMIT 語句執行失敗了，整個事務也會失敗。

有些時候，希望主動讓事務失敗，這時，可以用 ROLLBACK 回滾事務，整個事務會失敗：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

數據庫事務是由數據庫系統保證的，開發者只需要根據業務邏輯使用它就可以。

隔離級別

對於兩個並發執行的事務，如果涉及到操作同一條記錄的時候，可能會發生問題。因為並發操作會帶來數據的不一致性，包括臟讀、不可重復讀、幻讀等。數據庫系統提供了隔離級別來讓開發者有針對性地選擇事務的隔離級別，避免數據不一致的問題。

SQL 標準定義了 4 種隔離級別，分別對應可能出現的數據不一致的情況：

Read Uncommitted

Read Uncommitted 是隔離級別最低的一種事務級別。在這種隔離級別下，一個事務會讀到另一個事務更新後但未提交的數據，如果另一個事務回滾，那麽當前事務讀到的數據就是臟數據，這就是臟讀（Dirty Read）。

Read Committed

在 Read Committed 隔離級別下，一個事務可能會遇到不可重復讀（Non Repeatable Read）的問題。

不可重復讀是指，在一個事務內，多次讀同一數據，在這個事務還沒有結束時，如果另一個事務恰好修改了這個數據，那麽，在第一個事務中，兩次讀取的數據就可能不一致。

Repeatable Read

在 Repeatable Read 隔離級別下，一個事務可能會遇到幻讀（Phantom Read）的問題。

幻讀是指，在一個事務中，第一次查詢某條記錄，發現沒有，但是，當試圖更新這條不存在的記錄時，竟然能成功，並且，再次讀取同一條記錄，它就神奇地出現了。

Serializable

Serializable 是最嚴格的隔離級別。在 Serializable 隔離級別下，所有事務按照次序依次執行，因此，臟讀、不可重復讀、幻讀都不會出現。

雖然 Serializable 隔離級別下的事務具有最高的安全性，但是，由於事務是串行執行，所以效率會大大下降，應用程序的性能會急劇降低。如果沒有特別重要的情景，一般都不會使用 Serializable 隔離級別。

（以上內容摘抄於網絡，僅作為個人學習筆記，不適合作為學習教程。）

SQL

概述

數據模型

層次模型

網狀模型

關系模型

數據類型

SQL

MySQL

關系模型

主鍵

聯合主鍵

外鍵

索引

唯一索引

強製使用指定索引

查詢數據

基本查詢

條件查詢

投影查詢

排序

分頁查詢

聚合查詢

分組聚合

多表查詢

連接查詢

內連接（INNER JOIN）

左外連接（LEFT OUTER JOIN）

右外連接（RIGHT OUTER JOIN）

全外連接（FULL OUTER JOIN）

修改數據

INSERT

插入或替換

插入或更新

插入或忽略

寫入查詢結果集

UPDATE

DELETE

事務

隔離級別

Read Uncommitted

Read Committed

Repeatable Read

Serializable

請我喝可樂吧 (●ˇ∀ˇ●)🥤