來(lái)自：掘金楷掉，作者：靜默虛空

鏈接：https://juejin.im/post/6844903790571700231

本文針對(duì)關(guān)系型數(shù)據(jù)庫(kù)的一般語(yǔ)法。限于篇幅理疙，本文側(cè)重說(shuō)明用法晕城，不會(huì)展開(kāi)講解特性、原理窖贤。

一砖顷、基本概念

數(shù)據(jù)庫(kù)術(shù)語(yǔ)

• 數(shù)據(jù)庫(kù)（database） - 保存有組織的數(shù)據(jù)的容器（通常是一個(gè)文件或一組文件）。

• 數(shù)據(jù)表（table） - 某種特定類(lèi)型數(shù)據(jù)的結(jié)構(gòu)化清單赃梧。

• 模式（schema） - 關(guān)于數(shù)據(jù)庫(kù)和表的布局及特性的信息滤蝠。模式定義了數(shù)據(jù)在表中如何存儲(chǔ)，包含存儲(chǔ)什么樣的數(shù)據(jù)授嘀，數(shù)據(jù)如何分解物咳，各部分信息如何命名等信息。數(shù)據(jù)庫(kù)和表都有模式粤攒。

• 列（column） - 表中的一個(gè)字段所森。所有表都是由一個(gè)或多個(gè)列組成的囱持。

• 行（row） - 表中的一個(gè)記錄夯接。

• 主鍵（primary key） - 一列（或一組列），其值能夠唯一標(biāo)識(shí)表中每一行纷妆。

SQL 語(yǔ)法

SQL（Structured Query Language)盔几，標(biāo)準(zhǔn) SQL 由 ANSI 標(biāo)準(zhǔn)委員會(huì)管理，從而稱(chēng)為 ANSI SQL掩幢。各個(gè) DBMS 都有自己的實(shí)現(xiàn)逊拍，如 PL/SQL上鞠、Transact-SQL 等。

SQL 語(yǔ)法結(jié)構(gòu)

image

SQL 語(yǔ)法結(jié)構(gòu)包括：

• 子句 - 是語(yǔ)句和查詢的組成成分芯丧。（在某些情況下芍阎，這些都是可選的。）

• 表達(dá)式 - 可以產(chǎn)生任何標(biāo)量值缨恒，或由列和行的數(shù)據(jù)庫(kù)表

• 謂詞 - 給需要評(píng)估的 SQL 三值邏輯（3VL）（true/false/unknown）或布爾真值指定條件谴咸，并限制語(yǔ)句和查詢的效果，或改變程序流程骗露。

• 查詢 - 基于特定條件檢索數(shù)據(jù)岭佳。這是 SQL 的一個(gè)重要組成部分。

• 語(yǔ)句 - 可以持久地影響綱要和數(shù)據(jù)萧锉，也可以控制數(shù)據(jù)庫(kù)事務(wù)珊随、程序流程、連接柿隙、會(huì)話或診斷叶洞。

SQL 語(yǔ)法要點(diǎn)

• SQL 語(yǔ)句不區(qū)分大小寫(xiě)，但是數(shù)據(jù)庫(kù)表名优俘、列名和值是否區(qū)分京办，依賴(lài)于具體的 DBMS 以及配置。

例如：SELECT 與 select 帆焕、Select 是相同的惭婿。

• 多條 SQL 語(yǔ)句必須以分號(hào)（;）分隔。

• 處理 SQL 語(yǔ)句時(shí)叶雹，所有空格都被忽略财饥。SQL 語(yǔ)句可以寫(xiě)成一行，也可以分寫(xiě)為多行折晦。

-- 一行 SQL 語(yǔ)句
UPDATE user SET username='robot', password='robot' WHERE username = 'root';

-- 多行 SQL 語(yǔ)句
UPDATE user
SET username='robot', password='robot'
WHERE username = 'root';

• SQL 支持三種注釋

## 注釋1
-- 注釋2
/* 注釋3 */

SQL 分類(lèi)

數(shù)據(jù)定義語(yǔ)言（DDL）

數(shù)據(jù)定義語(yǔ)言（Data Definition Language钥星，DDL）是 SQL 語(yǔ)言集中負(fù)責(zé)數(shù)據(jù)結(jié)構(gòu)定義與數(shù)據(jù)庫(kù)對(duì)象定義的語(yǔ)言。

DDL 的主要功能是定義數(shù)據(jù)庫(kù)對(duì)象满着。

DDL 的核心指令是 CREATE谦炒、ALTER、DROP风喇。

數(shù)據(jù)操縱語(yǔ)言（DML）

數(shù)據(jù)操縱語(yǔ)言（Data Manipulation Language, DML）是用于數(shù)據(jù)庫(kù)操作宁改，對(duì)數(shù)據(jù)庫(kù)其中的對(duì)象和數(shù)據(jù)運(yùn)行訪問(wèn)工作的編程語(yǔ)句。

DML 的主要功能是 訪問(wèn)數(shù)據(jù)魂莫，因此其語(yǔ)法都是以讀寫(xiě)數(shù)據(jù)庫(kù)為主还蹲。

DML 的核心指令是 INSERT、UPDATE、DELETE谜喊、SELECT潭兽。這四個(gè)指令合稱(chēng) CRUD(Create, Read, Update, Delete)，即增刪改查斗遏。

事務(wù)控制語(yǔ)言（TCL）

事務(wù)控制語(yǔ)言 (Transaction Control Language, TCL) 用于管理數(shù)據(jù)庫(kù)中的事務(wù)山卦。這些用于管理由 DML 語(yǔ)句所做的更改。它還允許將語(yǔ)句分組為邏輯事務(wù)诵次。

TCL 的核心指令是 COMMIT怒坯、ROLLBACK。

數(shù)據(jù)控制語(yǔ)言（DCL）

數(shù)據(jù)控制語(yǔ)言 (Data Control Language, DCL) 是一種可對(duì)數(shù)據(jù)訪問(wèn)權(quán)進(jìn)行控制的指令藻懒，它可以控制特定用戶賬戶對(duì)數(shù)據(jù)表剔猿、查看表、預(yù)存程序嬉荆、用戶自定義函數(shù)等數(shù)據(jù)庫(kù)對(duì)象的控制權(quán)归敬。

DCL 的核心指令是 GRANT、REVOKE鄙早。

DCL 以控制用戶的訪問(wèn)權(quán)限為主汪茧，因此其指令作法并不復(fù)雜，可利用 DCL 控制的權(quán)限有：CONNECT限番、SELECT舱污、INSERT、UPDATE弥虐、DELETE扩灯、EXECUTE、USAGE霜瘪、REFERENCES珠插。

根據(jù)不同的 DBMS 以及不同的安全性實(shí)體，其支持的權(quán)限控制也有所不同颖对。

（以下為 DML 語(yǔ)句用法）

二捻撑、增刪改查

增刪改查，又稱(chēng)為 CRUD缤底，數(shù)據(jù)庫(kù)基本操作中的基本操作顾患。

插入數(shù)據(jù)

• INSERT INTO 語(yǔ)句用于向表中插入新記錄。

插入完整的行

INSERT INTO user
VALUES (10, 'root', 'root', 'xxxx@163.com');

插入行的一部分

INSERT INTO user(username, password, email)
VALUES ('admin', 'admin', 'xxxx@163.com');

插入查詢出來(lái)的數(shù)據(jù)

INSERT INTO user(username)
SELECT name
FROM account;

更新數(shù)據(jù)

• UPDATE 語(yǔ)句用于更新表中的記錄个唧。

UPDATE user
SET username='robot', password='robot'
WHERE username = 'root';

刪除數(shù)據(jù)

• DELETE 語(yǔ)句用于刪除表中的記錄江解。

• TRUNCATE TABLE 可以清空表，也就是刪除所有行坑鱼。

刪除表中的指定數(shù)據(jù)

DELETE FROM user
WHERE username = 'robot';

清空表中的數(shù)據(jù)

TRUNCATE TABLE user;

查詢數(shù)據(jù)

• SELECT 語(yǔ)句用于從數(shù)據(jù)庫(kù)中查詢數(shù)據(jù)膘流。

• DISTINCT 用于返回唯一不同的值。它作用于所有列鲁沥，也就是說(shuō)所有列的值都相同才算相同呼股。

• LIMIT 限制返回的行數(shù)』。可以有兩個(gè)參數(shù)彭谁，第一個(gè)參數(shù)為起始行，從 0 開(kāi)始允扇；第二個(gè)參數(shù)為返回的總行數(shù)缠局。

• ASC ：升序（默認(rèn)）

• DESC ：降序

查詢單列

SELECT prod_name
FROM products;

查詢多列

SELECT prod_id, prod_name, prod_price
FROM products;

查詢所有列

SELECT *
FROM products;

查詢不同的值

SELECT DISTINCT
vend_id FROM products;

限制查詢結(jié)果

-- 返回前 5 行
SELECT * FROM mytable LIMIT 5;
SELECT * FROM mytable LIMIT 0, 5;
-- 返回第 3 ~ 5 行
SELECT * FROM mytable LIMIT 2, 3

三、子查詢

子查詢是嵌套在較大查詢中的 SQL 查詢考润。子查詢也稱(chēng)為內(nèi)部查詢或內(nèi)部選擇狭园，而包含子查詢的語(yǔ)句也稱(chēng)為外部查詢或外部選擇。

• 子查詢可以嵌套在 SELECT糊治，INSERT唱矛，UPDATE 或 DELETE 語(yǔ)句內(nèi)或另一個(gè)子查詢中。

• 子查詢通常會(huì)在另一個(gè) SELECT 語(yǔ)句的 WHERE 子句中添加井辜。

• 您可以使用比較運(yùn)算符绎谦，如 >沙咏，<碉钠，或 =碾褂。比較運(yùn)算符也可以是多行運(yùn)算符抚吠，如 IN凑阶，ANY 或 ALL青团。

• 子查詢必須被圓括號(hào) () 括起來(lái)奶躯。

• 內(nèi)部查詢首先在其父查詢之前執(zhí)行倡鲸，以便可以將內(nèi)部查詢的結(jié)果傳遞給外部查詢树灶。執(zhí)行過(guò)程可以參考下圖：

  image

子查詢的子查詢

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
                  FROM orders
                  WHERE order_num IN (SELECT order_num
                                      FROM orderitems
                                      WHERE prod_id = 'RGAN01'));

WHERE

• WHERE 子句用于過(guò)濾記錄搀菩，即縮小訪問(wèn)數(shù)據(jù)的范圍。

• WHERE 后跟一個(gè)返回 true 或 false 的條件破托。

• WHERE 可以與 SELECT肪跋，UPDATE 和 DELETE 一起使用。

• 可以在 WHERE 子句中使用的操作符

SELECT 語(yǔ)句中的 WHERE 子句

SELECT * FROM Customers
WHERE cust_name = 'Kids Place';

UPDATE 語(yǔ)句中的 WHERE 子句

UPDATE Customers
SET cust_name = 'Jack Jones'
WHERE cust_name = 'Kids Place';

DELETE 語(yǔ)句中的 WHERE 子句

DELETE FROM Customers
WHERE cust_name = 'Kids Place';

IN 和 BETWEEN

• IN 操作符在 WHERE 子句中使用，作用是在指定的幾個(gè)特定值中任選一個(gè)值萝映。

• BETWEEN 操作符在 WHERE 子句中使用吴叶，作用是選取介于某個(gè)范圍內(nèi)的值。

IN 示例

SELECT *
FROM products
WHERE vend_id IN ('DLL01', 'BRS01');

BETWEEN 示例

SELECT *
FROM products
WHERE prod_price BETWEEN 3 AND 5;

AND序臂、OR蚌卤、NOT

• AND实束、OR、NOT 是用于對(duì)過(guò)濾條件的邏輯處理指令逊彭。

• AND 優(yōu)先級(jí)高于 OR咸灿，為了明確處理順序，可以使用 ()侮叮。

• AND 操作符表示左右條件都要滿足避矢。

• OR 操作符表示左右條件滿足任意一個(gè)即可。

• NOT 操作符用于否定一個(gè)條件囊榜。

AND 示例

SELECT prod_id, prod_name, prod_price
FROM products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

OR 示例

SELECT prod_id, prod_name, prod_price
FROM products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

NOT 示例

SELECT *
FROM products
WHERE prod_price NOT BETWEEN 3 AND 5;

LIKE

• LIKE 操作符在 WHERE 子句中使用审胸，作用是確定字符串是否匹配模式。

• 只有字段是文本值時(shí)才使用 LIKE卸勺。

• LIKE 支持兩個(gè)通配符匹配選項(xiàng)：% 和 _砂沛。

• 不要濫用通配符，通配符位于開(kāi)頭處匹配會(huì)非常慢曙求。

• % 表示任何字符出現(xiàn)任意次數(shù)尺上。

• _ 表示任何字符出現(xiàn)一次。

% 示例

SELECT prod_id, prod_name, prod_price
FROM products
WHERE prod_name LIKE '%bean bag%';

_ 示例

SELECT prod_id, prod_name, prod_price
FROM products
WHERE prod_name LIKE '__ inch teddy bear';

四圆到、連接和組合

連接（JOIN）

• 如果一個(gè) JOIN 至少有一個(gè)公共字段并且它們之間存在關(guān)系怎抛，則該 JOIN 可以在兩個(gè)或多個(gè)表上工作。

• 連接用于連接多個(gè)表芽淡，使用 JOIN 關(guān)鍵字马绝，并且條件語(yǔ)句使用 ON 而不是 WHERE。

• JOIN 保持基表（結(jié)構(gòu)和數(shù)據(jù)）不變挣菲。

• JOIN 有兩種連接類(lèi)型：內(nèi)連接和外連接富稻。

• 內(nèi)連接又稱(chēng)等值連接，使用 INNER JOIN 關(guān)鍵字白胀。在沒(méi)有條件語(yǔ)句的情況下返回笛卡爾積椭赋。

• 自連接可以看成內(nèi)連接的一種，只是連接的表是自身而已或杠。

• 自然連接是把同名列通過(guò) = 測(cè)試連接起來(lái)的哪怔，同名列可以有多個(gè)。

• 內(nèi)連接 vs 自然連接

• 內(nèi)連接提供連接的列向抢，而自然連接自動(dòng)連接所有同名列认境。

• 外連接返回一個(gè)表中的所有行，并且僅返回來(lái)自次表中滿足連接條件的那些行挟鸠，即兩個(gè)表中的列是相等的叉信。外連接分為左外連接、右外連接艘希、全外連接（Mysql 不支持）硼身。

• 左外連接就是保留左表沒(méi)有關(guān)聯(lián)的行硅急。

• 右外連接就是保留右表沒(méi)有關(guān)聯(lián)的行。

• 連接 vs 子查詢

• 連接可以替換子查詢佳遂，并且比子查詢的效率一般會(huì)更快营袜。

image

內(nèi)連接（INNER JOIN）

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

自連接

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM customers c1, customers c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';

自然連接（NATURAL JOIN）

SELECT *
FROM Products
NATURAL JOIN Customers;

左連接（LEFT JOIN）

SELECT customers.cust_id, orders.order_num
FROM customers LEFT JOIN orders
ON customers.cust_id = orders.cust_id;

右連接（RIGHT JOIN）

SELECT customers.cust_id, orders.order_num
FROM customers RIGHT JOIN orders
ON customers.cust_id = orders.cust_id;

組合（UNION）

• UNION 運(yùn)算符將兩個(gè)或更多查詢的結(jié)果組合起來(lái)，并生成一個(gè)結(jié)果集讶迁，其中包含來(lái)自 UNION 中參與查詢的提取行。

• UNION 基本規(guī)則

• 所有查詢的列數(shù)和列順序必須相同核蘸。

• 每個(gè)查詢中涉及表的列的數(shù)據(jù)類(lèi)型必須相同或兼容巍糯。

• 通常返回的列名取自第一個(gè)查詢。

• 默認(rèn)會(huì)去除相同行客扎，如果需要保留相同行祟峦，使用 UNION ALL。

• 只能包含一個(gè) ORDER BY 子句徙鱼，并且必須位于語(yǔ)句的最后宅楞。

• 應(yīng)用場(chǎng)景

• 在一個(gè)查詢中從不同的表返回結(jié)構(gòu)數(shù)據(jù)。

• 對(duì)一個(gè)表執(zhí)行多個(gè)查詢袱吆，按一個(gè)查詢返回?cái)?shù)據(jù)厌衙。

組合查詢

SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_name = 'Fun4All';

JOIN vs UNION

• JOIN vs UNION

• JOIN 中連接表的列可能不同，但在 UNION 中绞绒，所有查詢的列數(shù)和列順序必須相同婶希。

• UNION 將查詢之后的行放在一起（垂直放置），但 JOIN 將查詢之后的列放在一起（水平放置）蓬衡，即它構(gòu)成一個(gè)笛卡爾積喻杈。

五、函數(shù)

?? 注意：不同數(shù)據(jù)庫(kù)的函數(shù)往往各不相同狰晚，因此不可移植筒饰。本節(jié)主要以 Mysql 的函數(shù)為例。

文本處理

其中秒咐， SOUNDEX() 可以將一個(gè)字符串轉(zhuǎn)換為描述其語(yǔ)音表示的字母數(shù)字模式换棚。

SELECT *
FROM mytable
WHERE SOUNDEX(col1) = SOUNDEX('apple')

日期和時(shí)間處理

• 日期格式：YYYY-MM-DD

• 時(shí)間格式：HH:MM:SS

mysql> SELECT NOW();
2018-4-14 20:25:11

數(shù)值處理

匯總

AVG() 會(huì)忽略 NULL 行。

使用 DISTINCT 可以讓匯總函數(shù)值匯總不同的值歹茶。

SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable

六夕玩、排序和分組

ORDER BY

• ORDER BY 用于對(duì)結(jié)果集進(jìn)行排序你弦。

• ASC ：升序（默認(rèn)）

• DESC ：降序

• 可以按多個(gè)列進(jìn)行排序，并且為每個(gè)列指定不同的排序方式

指定多個(gè)列的排序方向

SELECT * FROM products
ORDER BY prod_price DESC, prod_name ASC;

GROUP BY

• GROUP BY 子句將記錄分組到匯總行中燎孟。

• GROUP BY 為每個(gè)組返回一個(gè)記錄禽作。

• GROUP BY 通常還涉及聚合：COUNT，MAX揩页，SUM旷偿，AVG 等。

• GROUP BY 可以按一列或多列進(jìn)行分組爆侣。

• GROUP BY 按分組字段進(jìn)行排序后萍程，ORDER BY 可以以匯總字段來(lái)進(jìn)行排序。

分組

SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name;

分組后排序

SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name
ORDER BY cust_name DESC;

HAVING

• HAVING 用于對(duì)匯總的 GROUP BY 結(jié)果進(jìn)行過(guò)濾兔仰。

• HAVING 要求存在一個(gè) GROUP BY 子句茫负。

• WHERE 和 HAVING 可以在相同的查詢中。

• HAVING vs WHERE

• WHERE 和 HAVING 都是用于過(guò)濾乎赴。

• HAVING 適用于匯總的組記錄忍法；而 WHERE 適用于單個(gè)記錄。

使用 WHERE 和 HAVING 過(guò)濾數(shù)據(jù)

SELECT cust_name, COUNT(*) AS num
FROM Customers
WHERE cust_email IS NOT NULL
GROUP BY cust_name
HAVING COUNT(*) >= 1;

（以下為 DDL 語(yǔ)句用法）

七榕吼、數(shù)據(jù)定義

DDL 的主要功能是定義數(shù)據(jù)庫(kù)對(duì)象（如：數(shù)據(jù)庫(kù)饿序、數(shù)據(jù)表、視圖羹蚣、索引等）嗤堰。

數(shù)據(jù)庫(kù)（DATABASE）

創(chuàng)建數(shù)據(jù)庫(kù)

CREATE DATABASE test;

刪除數(shù)據(jù)庫(kù)

DROP DATABASE test;

選擇數(shù)據(jù)庫(kù)

USE test;

數(shù)據(jù)表（TABLE）

創(chuàng)建數(shù)據(jù)表

普通創(chuàng)建

CREATE TABLE user (
  id int(10) unsigned NOT NULL COMMENT 'Id',
  username varchar(64) NOT NULL DEFAULT 'default' COMMENT '用戶名',
  password varchar(64) NOT NULL DEFAULT 'default' COMMENT '密碼',
  email varchar(64) NOT NULL DEFAULT 'default' COMMENT '郵箱'
) COMMENT='用戶表';

根據(jù)已有的表創(chuàng)建新表

CREATE TABLE vip_user AS
SELECT * FROM user;

刪除數(shù)據(jù)表

DROP TABLE user;

修改數(shù)據(jù)表

添加列

ALTER TABLE user
ADD age int(3);

刪除列

ALTER TABLE user
DROP COLUMN age;

修改列

ALTER TABLE `user`
MODIFY COLUMN age tinyint;

添加主鍵

ALTER TABLE user
ADD PRIMARY KEY (id);

刪除主鍵

ALTER TABLE user
DROP PRIMARY KEY;

視圖（VIEW）

• 定義

• 視圖是基于 SQL 語(yǔ)句的結(jié)果集的可視化的表。

• 視圖是虛擬的表度宦，本身不包含數(shù)據(jù)踢匣，也就不能對(duì)其進(jìn)行索引操作。對(duì)視圖的操作和對(duì)普通表的操作一樣戈抄。

• 作用

• 簡(jiǎn)化復(fù)雜的 SQL 操作离唬，比如復(fù)雜的聯(lián)結(jié)；

• 只使用實(shí)際表的一部分?jǐn)?shù)據(jù)划鸽；

• 通過(guò)只給用戶訪問(wèn)視圖的權(quán)限输莺，保證數(shù)據(jù)的安全性；

• 更改數(shù)據(jù)格式和表示裸诽。

創(chuàng)建視圖

CREATE VIEW top_10_user_view AS
SELECT id, username
FROM user
WHERE id < 10;

刪除視圖

DROP VIEW top_10_user_view;

索引（INDEX）

• 作用

• 通過(guò)索引可以更加快速高效地查詢數(shù)據(jù)嫂用。

• 用戶無(wú)法看到索引，它們只能被用來(lái)加速查詢丈冬。

• 注意

• 更新一個(gè)包含索引的表需要比更新一個(gè)沒(méi)有索引的表花費(fèi)更多的時(shí)間嘱函，這是由于索引本身也需要更新。因此埂蕊，理想的做法是僅僅在常常被搜索的列（以及表）上面創(chuàng)建索引往弓。

• 唯一索引

• 唯一索引表明此索引的每一個(gè)索引值只對(duì)應(yīng)唯一的數(shù)據(jù)記錄疏唾。

創(chuàng)建索引

CREATE INDEX user_index
ON user (id);

創(chuàng)建唯一索引

CREATE UNIQUE INDEX user_index
ON user (id);

刪除索引

ALTER TABLE user
DROP INDEX user_index;

約束

SQL 約束用于規(guī)定表中的數(shù)據(jù)規(guī)則。

• 如果存在違反約束的數(shù)據(jù)行為函似，行為會(huì)被約束終止槐脏。

• 約束可以在創(chuàng)建表時(shí)規(guī)定（通過(guò) CREATE TABLE 語(yǔ)句），或者在表創(chuàng)建之后規(guī)定（通過(guò) ALTER TABLE 語(yǔ)句）撇寞。

• 約束類(lèi)型

• NOT NULL - 指示某列不能存儲(chǔ) NULL 值顿天。

• UNIQUE - 保證某列的每行必須有唯一的值。

• PRIMARY KEY - NOT NULL 和 UNIQUE 的結(jié)合蔑担。確保某列（或兩個(gè)列多個(gè)列的結(jié)合）有唯一標(biāo)識(shí)牌废，有助于更容易更快速地找到表中的一個(gè)特定的記錄。

• FOREIGN KEY - 保證一個(gè)表中的數(shù)據(jù)匹配另一個(gè)表中的值的參照完整性钟沛。

• CHECK - 保證列中的值符合指定的條件畔规。

• DEFAULT - 規(guī)定沒(méi)有給列賦值時(shí)的默認(rèn)值局扶。

創(chuàng)建表時(shí)使用約束條件：

CREATE TABLE Users (
  Id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增Id',
  Username VARCHAR(64) NOT NULL UNIQUE DEFAULT 'default' COMMENT '用戶名',
  Password VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT '密碼',
  Email VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT '郵箱地址',
  Enabled TINYINT(4) DEFAULT NULL COMMENT '是否有效',
  PRIMARY KEY (Id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='用戶表';

（以下為 TCL 語(yǔ)句用法）

八恨统、事務(wù)處理

• 不能回退 SELECT 語(yǔ)句，回退 SELECT 語(yǔ)句也沒(méi)意義三妈；也不能回退 CREATE 和 DROP 語(yǔ)句畜埋。

• MySQL 默認(rèn)是隱式提交，每執(zhí)行一條語(yǔ)句就把這條語(yǔ)句當(dāng)成一個(gè)事務(wù)然后進(jìn)行提交畴蒲。當(dāng)出現(xiàn) START TRANSACTION 語(yǔ)句時(shí)悠鞍，會(huì)關(guān)閉隱式提交；當(dāng) COMMIT 或 ROLLBACK 語(yǔ)句執(zhí)行后模燥，事務(wù)會(huì)自動(dòng)關(guān)閉咖祭，重新恢復(fù)隱式提交。

• 通過(guò) set autocommit=0 可以取消自動(dòng)提交蔫骂，直到 set autocommit=1 才會(huì)提交么翰；autocommit 標(biāo)記是針對(duì)每個(gè)連接而不是針對(duì)服務(wù)器的。

• 指令

• START TRANSACTION - 指令用于標(biāo)記事務(wù)的起始點(diǎn)辽旋。

• SAVEPOINT - 指令用于創(chuàng)建保留點(diǎn)浩嫌。

• ROLLBACK TO - 指令用于回滾到指定的保留點(diǎn)；如果沒(méi)有設(shè)置保留點(diǎn)补胚，則回退到 START TRANSACTION 語(yǔ)句處码耐。

• COMMIT - 提交事務(wù)。

-- 開(kāi)始事務(wù)
START TRANSACTION;

-- 插入操作 A
INSERT INTO `user`
VALUES (1, 'root1', 'root1', 'xxxx@163.com');

-- 創(chuàng)建保留點(diǎn) updateA
SAVEPOINT updateA;

-- 插入操作 B
INSERT INTO `user`
VALUES (2, 'root2', 'root2', 'xxxx@163.com');

-- 回滾到保留點(diǎn) updateA
ROLLBACK TO updateA;

-- 提交事務(wù)溶其，只有操作 A 生效
COMMIT;

（以下為 DCL 語(yǔ)句用法）

九骚腥、權(quán)限控制

• GRANT 和 REVOKE 可在幾個(gè)層次上控制訪問(wèn)權(quán)限：

• 整個(gè)服務(wù)器，使用 GRANT ALL 和 REVOKE ALL瓶逃；

• 整個(gè)數(shù)據(jù)庫(kù)桦沉，使用 ON database.*每瞒；

• 特定的表，使用 ON database.table纯露；

• 特定的列剿骨；

• 特定的存儲(chǔ)過(guò)程。

• 新創(chuàng)建的賬戶沒(méi)有任何權(quán)限埠褪。

• 賬戶用 username@host 的形式定義浓利，username@% 使用的是默認(rèn)主機(jī)名。

• MySQL 的賬戶信息保存在 mysql 這個(gè)數(shù)據(jù)庫(kù)中钞速。

```
USE mysql;SELECT user FROM user;復(fù)制代碼
```

創(chuàng)建賬戶

CREATE USER myuser IDENTIFIED BY 'mypassword';

修改賬戶名

UPDATE user SET user='newuser' WHERE user='myuser';
FLUSH PRIVILEGES;

刪除賬戶

DROP USER myuser;

查看權(quán)限

SHOW GRANTS FOR myuser;

授予權(quán)限

GRANT SELECT, INSERT ON *.* TO myuser;

刪除權(quán)限

REVOKE SELECT, INSERT ON *.* FROM myuser;

更改密碼

SET PASSWORD FOR myuser = 'mypass';

十贷掖、存儲(chǔ)過(guò)程

• 存儲(chǔ)過(guò)程可以看成是對(duì)一系列 SQL 操作的批處理；

• 使用存儲(chǔ)過(guò)程的好處

• 代碼封裝渴语，保證了一定的安全性苹威；

• 代碼復(fù)用；

• 由于是預(yù)先編譯驾凶，因此具有很高的性能牙甫。

• 創(chuàng)建存儲(chǔ)過(guò)程

• 命令行中創(chuàng)建存儲(chǔ)過(guò)程需要自定義分隔符，因?yàn)槊钚惺且?; 為結(jié)束符调违，而存儲(chǔ)過(guò)程中也包含了分號(hào)窟哺，因此會(huì)錯(cuò)誤把這部分分號(hào)當(dāng)成是結(jié)束符，造成語(yǔ)法錯(cuò)誤技肩。

• 包含 in且轨、out 和 inout 三種參數(shù)。

• 給變量賦值都需要用 select into 語(yǔ)句虚婿。

• 每次只能給一個(gè)變量賦值旋奢，不支持集合的操作。

創(chuàng)建存儲(chǔ)過(guò)程

DROP PROCEDURE IF EXISTS `proc_adder`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)
BEGIN
    DECLARE c int;
    if a is null then set a = 0;
    end if;

    if b is null then set b = 0;
    end if;

    set sum  = a + b;
END
;;
DELIMITER ;

使用存儲(chǔ)過(guò)程

set @b=5;
call proc_adder(2,@b,@s);
select @s as sum;

十一然痊、游標(biāo)

• 游標(biāo)（cursor）是一個(gè)存儲(chǔ)在 DBMS 服務(wù)器上的數(shù)據(jù)庫(kù)查詢至朗，它不是一條 SELECT 語(yǔ)句，而是被該語(yǔ)句檢索出來(lái)的結(jié)果集玷过。

• 在存儲(chǔ)過(guò)程中使用游標(biāo)可以對(duì)一個(gè)結(jié)果集進(jìn)行移動(dòng)遍歷爽丹。

• 游標(biāo)主要用于交互式應(yīng)用，其中用戶需要對(duì)數(shù)據(jù)集中的任意行進(jìn)行瀏覽和修改辛蚊。

• 使用游標(biāo)的四個(gè)步驟：

• 聲明游標(biāo)粤蝎，這個(gè)過(guò)程沒(méi)有實(shí)際檢索出數(shù)據(jù)；

• 打開(kāi)游標(biāo)袋马；

• 取出數(shù)據(jù)初澎；

• 關(guān)閉游標(biāo)；

DELIMITER $
CREATE  PROCEDURE getTotal()
BEGIN
    DECLARE total INT;
    -- 創(chuàng)建接收游標(biāo)數(shù)據(jù)的變量
    DECLARE sid INT;
    DECLARE sname VARCHAR(10);
    -- 創(chuàng)建總數(shù)變量
    DECLARE sage INT;
    -- 創(chuàng)建結(jié)束標(biāo)志變量
    DECLARE done INT DEFAULT false;
    -- 創(chuàng)建游標(biāo)
    DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;
    -- 指定游標(biāo)循環(huán)結(jié)束時(shí)的返回值
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
    SET total = 0;
    OPEN cur;
    FETCH cur INTO sid, sname, sage;
    WHILE(NOT done)
    DO
        SET total = total + 1;
        FETCH cur INTO sid, sname, sage;
    END WHILE;

    CLOSE cur;
    SELECT total;
END $
DELIMITER ;

-- 調(diào)用存儲(chǔ)過(guò)程
call getTotal();

十二、觸發(fā)器

觸發(fā)器是一種與表操作有關(guān)的數(shù)據(jù)庫(kù)對(duì)象碑宴，當(dāng)觸發(fā)器所在表上出現(xiàn)指定事件時(shí)软啼，將調(diào)用該對(duì)象，即表的操作事件觸發(fā)表上的觸發(fā)器的執(zhí)行延柠。

可以使用觸發(fā)器來(lái)進(jìn)行審計(jì)跟蹤祸挪，把修改記錄到另外一張表中。

MySQL 不允許在觸發(fā)器中使用 CALL 語(yǔ)句 贞间，也就是不能調(diào)用存儲(chǔ)過(guò)程贿条。

BEGIN 和 END

當(dāng)觸發(fā)器的觸發(fā)條件滿足時(shí)，將會(huì)執(zhí)行 BEGIN 和 END 之間的觸發(fā)器執(zhí)行動(dòng)作增热。

?? 注意：在 MySQL 中整以，分號(hào) ; 是語(yǔ)句結(jié)束的標(biāo)識(shí)符，遇到分號(hào)表示該段語(yǔ)句已經(jīng)結(jié)束峻仇，MySQL 可以開(kāi)始執(zhí)行了公黑。因此，解釋器遇到觸發(fā)器執(zhí)行動(dòng)作中的分號(hào)后就開(kāi)始執(zhí)行摄咆，然后會(huì)報(bào)錯(cuò)凡蚜，因?yàn)闆](méi)有找到和 BEGIN 匹配的 END。

這時(shí)就會(huì)用到 DELIMITER 命令（DELIMITER 是定界符豆同，分隔符的意思）番刊。它是一條命令含鳞，不需要語(yǔ)句結(jié)束標(biāo)識(shí)影锈，語(yǔ)法為：DELIMITER new_delemiter。new_delemiter 可以設(shè)為 1 個(gè)或多個(gè)長(zhǎng)度的符號(hào)蝉绷，默認(rèn)的是分號(hào) ;鸭廷，我們可以把它修改為其他符號(hào)，如 $ - DELIMITER $ 熔吗。在這之后的語(yǔ)句辆床，以分號(hào)結(jié)束，解釋器不會(huì)有什么反應(yīng)桅狠，只有遇到了 $讼载，才認(rèn)為是語(yǔ)句結(jié)束。注意中跌，使用完之后咨堤，我們還應(yīng)該記得把它給修改回來(lái)。

NEW 和 OLD

• MySQL 中定義了 NEW 和 OLD 關(guān)鍵字漩符，用來(lái)表示觸發(fā)器的所在表中一喘，觸發(fā)了觸發(fā)器的那一行數(shù)據(jù)。

• 在 INSERT 型觸發(fā)器中嗜暴，NEW 用來(lái)表示將要（BEFORE）或已經(jīng)（AFTER）插入的新數(shù)據(jù)凸克；

• 在 UPDATE 型觸發(fā)器中议蟆，OLD 用來(lái)表示將要或已經(jīng)被修改的原數(shù)據(jù)，NEW 用來(lái)表示將要或已經(jīng)修改為的新數(shù)據(jù)萎战；

• 在 DELETE 型觸發(fā)器中咐容，OLD 用來(lái)表示將要或已經(jīng)被刪除的原數(shù)據(jù)；

• 使用方法： NEW.columnName （columnName 為相應(yīng)數(shù)據(jù)表某一列名）

創(chuàng)建觸發(fā)器

提示：為了理解觸發(fā)器的要點(diǎn)蚂维，有必要先了解一下創(chuàng)建觸發(fā)器的指令疟丙。

CREATE TRIGGER 指令用于創(chuàng)建觸發(fā)器。

語(yǔ)法：

CREATE TRIGGER trigger_name
trigger_time
trigger_event
ON table_name
FOR EACH ROW
BEGIN
  trigger_statements
END;

說(shuō)明：

• trigger_name：觸發(fā)器名

• trigger_time: 觸發(fā)器的觸發(fā)時(shí)機(jī)鸟雏。取值為 BEFORE 或 AFTER享郊。

• trigger_event: 觸發(fā)器的監(jiān)聽(tīng)事件。取值為 INSERT孝鹊、UPDATE 或 DELETE炊琉。

• table_name: 觸發(fā)器的監(jiān)聽(tīng)目標(biāo)。指定在哪張表上建立觸發(fā)器又活。

• FOR EACH ROW: 行級(jí)監(jiān)視苔咪，Mysql 固定寫(xiě)法，其他 DBMS 不同柳骄。

• trigger_statements: 觸發(fā)器執(zhí)行動(dòng)作团赏。是一條或多條 SQL 語(yǔ)句的列表，列表內(nèi)的每條語(yǔ)句都必須用分號(hào) ; 來(lái)結(jié)尾耐薯。

示例：

DELIMITER $
CREATE TRIGGER `trigger_insert_user`
AFTER INSERT ON `user`
FOR EACH ROW
BEGIN
    INSERT INTO `user_history`(user_id, operate_type, operate_time)
    VALUES (NEW.id, 'add a user',  now());
END $
DELIMITER ;

查看觸發(fā)器

SHOW TRIGGERS;

刪除觸發(fā)器

DROP TRIGGER IF EXISTS trigger_insert_user;