萬丈高樓平地起

一、基本概念

數(shù)據(jù)庫術(shù)語

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

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

• 模式（schema） - 關(guān)于數(shù)據(jù)庫和表的布局及特性的信息荆隘。模式定義了數(shù)據(jù)在表中如何存儲针贬，包含存儲什么樣的數(shù)據(jù)徘钥，數(shù)據(jù)如何分解，各部分信息如何命名等信息离赫。數(shù)據(jù)庫和表都有模式芭逝。

• 列（column） - 表中的一個字段。所有表都是由一個或多個列組成的渊胸。

• 行（row） - 表中的一個記錄旬盯。

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

SQL 語法

SQL（Structured Query Language)胖翰，標(biāo)準(zhǔn) SQL 由 ANSI 標(biāo)準(zhǔn)委員會管理接剩，從而稱為 ANSI SQL。

各個 DBMS 都有自己的實現(xiàn)萨咳，如 PL/SQL懊缺、Transact-SQL 等。

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

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

• 子句 - 是語句和查詢的組成成分培他。（在某些情況下鹃两，這些都是可選的。）

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

• 謂詞 - 給需要評估的 SQL 三值邏輯（3VL）（true/false/unknown）或布爾真值指定條件俊扳，并限制語句和查詢的效果，或改變程序流程猛遍。

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

• 語句 - 可以持久地影響綱要和數(shù)據(jù)懊烤，也可以控制數(shù)據(jù)庫事務(wù)梯醒、程序流程、連接腌紧、會話或診斷茸习。

SQL 語法要點

• SQL 語句不區(qū)分大小寫，但是數(shù)據(jù)庫表名寄啼、列名和值是否區(qū)分逮光，依賴于具體的 DBMS 以及配置代箭。

例如：SELECT 與 select 墩划、Select 是相同的。

• 多條 SQL 語句必須以分號（;）分隔嗡综。

• 處理 SQL 語句時乙帮，所有空格都被忽略。SQL 語句可以寫成一行极景，也可以分寫為多行察净。

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

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

• SQL 支持三種注釋

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

SQL 分類

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

數(shù)據(jù)定義語言（Data Definition Language，DDL）是 SQL 語言集中負責(zé)數(shù)據(jù)結(jié)構(gòu)定義與數(shù)據(jù)庫對象定義的語言盼樟。

DDL 的主要功能是定義數(shù)據(jù)庫對象氢卡。

DDL 的核心指令是 CREATE、ALTER晨缴、DROP译秦。

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

數(shù)據(jù)操縱語言（Data Manipulation Language, DML）是用于數(shù)據(jù)庫操作，對數(shù)據(jù)庫其中的對象和數(shù)據(jù)運行訪問工作的編程語句。

DML 的核心指令是 INSERT筑悴、UPDATE们拙、DELETE

DQL數(shù)據(jù)查詢語言(Data Query Language)

DQL的核心指令是SELECT

這四個指令合稱 CURD，即增刪改查阁吝。

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

事務(wù)控制語言 (Transaction Control Language, TCL) 用于管理數(shù)據(jù)庫中的事務(wù)砚婆。

這些用于管理由 DML 語句所做的更改。它還允許將語句分組為邏輯事務(wù)突勇。

TCL 的核心指令是 COMMIT装盯、ROLLBACK。

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

數(shù)據(jù)控制語言 (Data Control Language, DCL) 是一種可對數(shù)據(jù)訪問權(quán)進行控制的指令甲馋，它可以控制特定用戶賬戶對數(shù)據(jù)表验夯、查看表、預(yù)存程序摔刁、用戶自定義函數(shù)等數(shù)據(jù)庫對象的控制權(quán)挥转。

DCL 的核心指令是 GRANT、REVOKE共屈。

DCL 以控制用戶的訪問權(quán)限為主绑谣，因此其指令作法并不復(fù)雜，可利用 DCL 控制的權(quán)限有：CONNECT拗引、SELECT借宵、INSERT、UPDATE矾削、DELETE壤玫、EXECUTE、USAGE哼凯、REFERENCES欲间。

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

（以下為 DML 語句用法）

二猎贴、增刪改查

增刪改查，又稱為 CRUD蝴光，數(shù)據(jù)庫基本操作中的基本操作她渴。

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

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

插入完整的行

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

插入行的一部分

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

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

INSERT INTO user(username)
SELECT name
FROM account;

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

• UPDATE 語句用于更新表中的記錄蔑祟。

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

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

• DELETE 語句用于刪除表中的記錄趁耗。

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

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

DELETE FROM user
WHERE username = 'robot';

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

TRUNCATE TABLE user;

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

• SELECT 語句用于從數(shù)據(jù)庫中查詢數(shù)據(jù)苛败。

• DISTINCT 用于返回唯一不同的值右冻。它作用于所有列，也就是說所有列的值都相同才算相同著拭。

• LIMIT 限制返回的行數(shù)纱扭。可以有兩個參數(shù)儡遮，第一個參數(shù)為起始行乳蛾，從 0 開始；第二個參數(shù)為返回的總行數(shù)鄙币。

• ASC ：升序（默認）

• 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 查詢。子查詢也稱為內(nèi)部查詢或內(nèi)部選擇十嘿，而包含子查詢的語句也稱為外部查詢或外部選擇因惭。

• 子查詢可以嵌套在 SELECT，INSERT绩衷，UPDATE 或 DELETE 語句內(nèi)或另一個子查詢中蹦魔。

• 子查詢通常會在另一個 SELECT 語句的 WHERE 子句中添加。

• 您可以使用比較運算符咳燕，如 >憔披，<上遥，或 =科展。比較運算符也可以是多行運算符算谈，如 IN，ANY 或 ALL曹货。

• 子查詢必須被圓括號 () 括起來咆繁。

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

子查詢的子查詢

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 子句用于過濾記錄玩般，即縮小訪問數(shù)據(jù)的范圍。

• WHERE 后跟一個返回 true 或 false 的條件蜕衡。

• WHERE 可以與 SELECT壤短，UPDATE 和 DELETE 一起使用设拟。

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

SELECT 語句中的 WHERE 子句

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

UPDATE 語句中的 WHERE 子句

UPDATE CustomersSET cust_name = 'Jack Jones'
WHERE cust_name = 'Kids Place';

DELETE 語句中的 WHERE 子句

DELETE FROM Customers
WHERE cust_name = 'Kids Place';

IN 和 BETWEEN

• IN 操作符在 WHERE 子句中使用纳胧，作用是在指定的幾個特定值中任選一個值镰吆。

• BETWEEN 操作符在 WHERE 子句中使用，作用是選取介于某個范圍內(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 是用于對過濾條件的邏輯處理指令蹬耘。

• AND 優(yōu)先級高于 OR，為了明確處理順序减余，可以使用 ()综苔。

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

• OR 操作符表示左右條件滿足任意一個即可位岔。

• NOT 操作符用于否定一個條件如筛。

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 子句中使用，作用是確定字符串是否匹配模式抒抬。

• 只有字段是文本值時才使用 LIKE杨刨。

• LIKE 支持兩個通配符匹配選項：% 和 _。

• 不要濫用通配符擦剑，通配符位于開頭處匹配會非常慢妖胀。

• % 表示任何字符出現(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）

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

• 連接用于連接多個表怕品，使用 JOIN 關(guān)鍵字，并且條件語句使用 ON 而不是 WHERE巾遭。

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

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

• 內(nèi)連接又稱等值連接灼舍，使用 INNER JOIN 關(guān)鍵字吼和。在沒有條件語句的情況下返回笛卡爾積。

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

• 自然連接是把同名列通過 = 測試連接起來的，同名列可以有多個献丑。

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

• 內(nèi)連接提供連接的列末捣，而自然連接自動連接所有同名列。

• 外連接返回一個表中的所有行创橄，并且僅返回來自次表中滿足連接條件的那些行箩做，即兩個表中的列是相等的。外連接分為左外連接妥畏、右外連接邦邦、全外連接（Mysql 不支持）安吁。

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

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

• 連接 vs 子查詢

• 連接可以替換子查詢鬼店，并且比子查詢的效率一般會更快。

內(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

自然連接（NATURAL JOIN）

SELECT *
FROM Products
NATURAL JOIN Customers;

左連接（LEFT JOIN）

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

右連接（RIGHT JOIN）

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

組合（UNION）

• UNION 運算符將兩個或更多查詢的結(jié)果組合起來黔龟，并生成一個結(jié)果集薪韩，其中包含來自 UNION 中參與查詢的提取行。

• UNION 基本規(guī)則

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

• 每個查詢中涉及表的列的數(shù)據(jù)類型必須相同或兼容俘陷。

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

• 默認會去除相同行观谦，如果需要保留相同行拉盾，使用 UNION ALL。

• 只能包含一個 ORDER BY 子句豁状，并且必須位于語句的最后捉偏。

• 應(yīng)用場景

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

• 對一個表執(zhí)行多個查詢泻红，按一個查詢返回數(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)成一個笛卡爾積潮梯。

五、函數(shù)

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

文本處理

其中锉罐， SOUNDEX() 可以將一個字符串轉(zhuǎn)換為描述其語音表示的字母數(shù)字模式帆竹。

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

日期和時間處理

• 日期格式：YYYY-MM-DD

• 時間格式：HH:MM:SS

mysql> SELECT NOW();

2018-4-14 20:25:11

數(shù)值處理

匯總

AVG() 會忽略 NULL 行。

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

SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable

六升酣、排序和分組

ORDER BY

• ORDER BY 用于對結(jié)果集進行排序。

• ASC ：升序（默認）

• DESC ：降序

• 可以按多個列進行排序态罪，并且為每個列指定不同的排序方式

指定多個列的排序方向

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

GROUP BY

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

• GROUP BY 為每個組返回一個記錄。

• GROUP BY 通常還涉及聚合：COUNT复颈，MAX绩聘，SUM，AVG 等耗啦。

• GROUP BY 可以按一列或多列進行分組凿菩。

• GROUP BY 按分組字段進行排序后，ORDER BY 可以以匯總字段來進行排序帜讲。

分組

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 用于對匯總的 GROUP BY 結(jié)果進行過濾衅谷。

• HAVING 要求存在一個 GROUP BY 子句。

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

• HAVING vs WHERE

• WHERE 和 HAVING 都是用于過濾获黔。

• HAVING 適用于匯總的組記錄；而 WHERE 適用于單個記錄在验。

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

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

（以下為 DDL 語句用法）

七玷氏、數(shù)據(jù)定義

DDL 的主要功能是定義數(shù)據(jù)庫對象（如：數(shù)據(jù)庫、數(shù)據(jù)表腋舌、視圖盏触、索引等）。

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

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

CREATE DATABASE test;

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

DROP DATABASE test;

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

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 語句的結(jié)果集的可視化的表块饺。

• 視圖是虛擬的表耻陕，本身不包含數(shù)據(jù)，也就不能對其進行索引操作刨沦。對視圖的操作和對普通表的操作一樣诗宣。

• 作用

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

• 只使用實際表的一部分數(shù)據(jù)召庞；

• 通過只給用戶訪問視圖的權(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）

• 作用

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

• 用戶無法看到索引徘禁，它們只能被用來加速查詢诅诱。

• 注意

• 更新一個包含索引的表需要比更新一個沒有索引的表花費更多的時間，這是由于索引本身也需要更新送朱。因此娘荡，理想的做法是僅僅在常常被搜索的列（以及表）上面創(chuàng)建索引干旁。

• 唯一索引

• 唯一索引表明此索引的每一個索引值只對應(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ù)行為争群，行為會被約束終止。

• 約束可以在創(chuàng)建表時規(guī)定（通過 CREATE TABLE 語句）大年，或者在表創(chuàng)建之后規(guī)定（通過 ALTER TABLE 語句）换薄。

• 約束類型

• NOT NULL - 指示某列不能存儲 NULL 值。

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

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

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

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

• DEFAULT - 規(guī)定沒有給列賦值時的默認值失都。

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

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 語句用法）

八柏蘑、事務(wù)處理

• 不能回退 SELECT 語句，回退 SELECT 語句也沒意義粹庞；也不能回退 CREATE 和 DROP 語句咳焚。

• MySQL 默認是隱式提交，每執(zhí)行一條語句就把這條語句當(dāng)成一個事務(wù)然后進行提交庞溜。當(dāng)出現(xiàn) START TRANSACTION 語句時革半，會關(guān)閉隱式提交；當(dāng) COMMIT 或 ROLLBACK 語句執(zhí)行后流码，事務(wù)會自動關(guān)閉又官，重新恢復(fù)隱式提交。

• 通過 set autocommit=0 可以取消自動提交漫试，直到 set autocommit=1 才會提交六敬；autocommit 標(biāo)記是針對每個連接而不是針對服務(wù)器的。

• 指令

• START TRANSACTION - 指令用于標(biāo)記事務(wù)的起始點驾荣。

• SAVEPOINT - 指令用于創(chuàng)建保留點外构。

• ROLLBACK TO - 指令用于回滾到指定的保留點；如果沒有設(shè)置保留點播掷，則回退到 START TRANSACTION 語句處审编。

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

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

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

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

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

-- 回滾到保留點 updateA
ROLLBACK TO updateA;

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

（以下為 DCL 語句用法）

九垒酬、權(quán)限控制

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

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

• 整個數(shù)據(jù)庫勘究，使用 ON database.*矮湘；

• 特定的表，使用 ON database.table乱顾；

• 特定的列板祝；

• 特定的存儲過程宫静。

• 新創(chuàng)建的賬戶沒有任何權(quán)限走净。

• 賬戶用 username@host 的形式定義，username@% 使用的是默認主機名孤里。

• MySQL 的賬戶信息保存在 mysql 這個數(shù)據(jù)庫中伏伯。

```
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';

十、存儲過程

• 存儲過程可以看成是對一系列 SQL 操作的批處理捌袜；

• 使用存儲過程的好處

• 代碼封裝说搅，保證了一定的安全性；

• 代碼復(fù)用虏等；

• 由于是預(yù)先編譯弄唧，因此具有很高的性能。

• 創(chuàng)建存儲過程

• 命令行中創(chuàng)建存儲過程需要自定義分隔符霍衫，因為命令行是以 ; 為結(jié)束符候引，而存儲過程中也包含了分號，因此會錯誤把這部分分號當(dāng)成是結(jié)束符敦跌，造成語法錯誤澄干。

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

• 給變量賦值都需要用 select into 語句麸俘。

• 每次只能給一個變量賦值，不支持集合的操作惧笛。

創(chuàng)建存儲過程

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 ;

使用存儲過程

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

十一从媚、游標(biāo)

• 游標(biāo)（cursor）是一個存儲在 DBMS 服務(wù)器上的數(shù)據(jù)庫查詢，它不是一條 SELECT 語句患整，而是被該語句檢索出來的結(jié)果集拜效。

• 在存儲過程中使用游標(biāo)可以對一個結(jié)果集進行移動遍歷。

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

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

• 聲明游標(biāo)，這個過程沒有實際檢索出數(shù)據(jù)嘲碧；

• 打開游標(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é)束時的返回值
    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)用存儲過程
call getTotal();

十二望抽、觸發(fā)器

觸發(fā)器是一種與表操作有關(guān)的數(shù)據(jù)庫對象加矛，當(dāng)觸發(fā)器所在表上出現(xiàn)指定事件時，將調(diào)用該對象煤篙，即表的操作事件觸發(fā)表上的觸發(fā)器的執(zhí)行斟览。

可以使用觸發(fā)器來進行審計跟蹤，把修改記錄到另外一張表中辑奈。

MySQL 不允許在觸發(fā)器中使用 CALL 語句 苛茂，也就是不能調(diào)用存儲過程。

BEGIN 和 END

當(dāng)觸發(fā)器的觸發(fā)條件滿足時鸠窗，將會執(zhí)行 BEGIN 和 END 之間的觸發(fā)器執(zhí)行動作妓羊。

?? 注意：在 MySQL 中，分號 ; 是語句結(jié)束的標(biāo)識符稍计，遇到分號表示該段語句已經(jīng)結(jié)束躁绸，MySQL 可以開始執(zhí)行了。因此臣嚣，解釋器遇到觸發(fā)器執(zhí)行動作中的分號后就開始執(zhí)行净刮，然后會報錯，因為沒有找到和 BEGIN 匹配的 END硅则。

這時就會用到 DELIMITER 命令（DELIMITER 是定界符淹父，分隔符的意思）。它是一條命令抢埋，不需要語句結(jié)束標(biāo)識弹灭，語法為：DELIMITER new_delemiter。new_delemiter 可以設(shè)為 1 個或多個長度的符號揪垄，默認的是分號 ;穷吮，我們可以把它修改為其他符號，如 $ - DELIMITER $ 饥努。在這之后的語句捡鱼，以分號結(jié)束，解釋器不會有什么反應(yīng)酷愧，只有遇到了 $驾诈，才認為是語句結(jié)束。注意溶浴，使用完之后乍迄，我們還應(yīng)該記得把它給修改回來。

NEW 和 OLD

• MySQL 中定義了 NEW 和 OLD 關(guān)鍵字士败，用來表示觸發(fā)器的所在表中闯两，觸發(fā)了觸發(fā)器的那一行數(shù)據(jù)褥伴。

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

• 在 UPDATE 型觸發(fā)器中重慢，OLD 用來表示將要或已經(jīng)被修改的原數(shù)據(jù)，NEW 用來表示將要或已經(jīng)修改為的新數(shù)據(jù)逊躁；

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

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

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

提示：為了理解觸發(fā)器的要點稽煤，有必要先了解一下創(chuàng)建觸發(fā)器的指令核芽。

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

語法：

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

說明：

• trigger_name：觸發(fā)器名

• trigger_time: 觸發(fā)器的觸發(fā)時機念脯。取值為 BEFORE 或 AFTER狞洋。

• trigger_event: 觸發(fā)器的監(jiān)聽事件弯淘。取值為 INSERT绿店、UPDATE 或 DELETE。

• table_name: 觸發(fā)器的監(jiān)聽目標(biāo)庐橙。指定在哪張表上建立觸發(fā)器假勿。

• FOR EACH ROW: 行級監(jiān)視，Mysql 固定寫法态鳖，其他 DBMS 不同转培。

• trigger_statements: 觸發(fā)器執(zhí)行動作。是一條或多條 SQL 語句的列表浆竭，列表內(nè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;