前言

MySQL在我們工作中都會用到，那么我們最常接觸的就是增刪改查，而對于增刪改查來說，我們更多的是查詢。但是面試中，面試官又不會問你什么查詢是怎么寫的，都是問一些索引啊，事務啊， 底層結(jié)構(gòu)這些東西，所以我打算分四篇去逐一的過一遍MySQL的知識點。
以下為四篇的傳送門，有需要直接點擊進入，目前還在建設當中，可以關(guān)注作者或?qū)冢掷m(xù)更新，永不收費。

一、函數(shù)

1.單行函數(shù)

因為函數(shù)很多，全部例舉讀者也找不到重點，而且工作中也用不到，沒有必要。所以在此僅列出本人認為比較重要和常用的。您可以在評論區(qū)補充~

1.1 數(shù)值函數(shù)

1.2 字符串函數(shù)

注意：MySQL中，字符串的位置是從1開始的。

1.3 日期和時間函數(shù)

格式符

1.4 流程控制函數(shù)

1.5 加密與解密函數(shù)

2.聚合函數(shù)

• 什么是聚合函數(shù)

聚合函數(shù)作用于一組數(shù)據(jù)，并對一組數(shù)據(jù)返回一個值。

• 聚合函數(shù)類型
  • AVG()
  • SUM()
  • MAX()
  • MIN()
  • COUNT()

2.1 AVG和SUM函數(shù)

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';

2.2MIN和MAX函數(shù)

可以對任意數(shù)據(jù)類型的數(shù)據(jù)使用 MIN 和 MAX 函數(shù)。

SELECT MIN(hire_date), MAX(hire_date)
FROM employees;

2.3COUNT函數(shù)

COUNT(*)返回表中記錄總數(shù)，適用于任意數(shù)據(jù)類型。

SELECT COUNT(*)
FROM employees
WHERE department_id = 50;

COUNT(expr) 返回expr不為空的記錄總數(shù)。

SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 50;

• 問題：用count(*)，count(1)，count(列名)誰好呢?

其實，對于MyISAM引擎的表是沒有區(qū)別的。這種引擎內(nèi)部有一計數(shù)器在維護著行數(shù)。 Innodb引擎的表用count(*),count(1)直接讀行數(shù)，復雜度是O(n)，因為innodb真的要去數(shù)一遍。但好 于具體的count(列名)。

• 問題：能不能使用count(列名)替換count(*)?

不要使用 count(列名)來替代 count() ， count() 是 SQL92 定義的標準統(tǒng)計行數(shù)的語法，跟數(shù) 據(jù)庫無關(guān)，跟 NULL 和非 NULL 無關(guān)。 說明：count(*)會統(tǒng)計值為 NULL 的行，而 count(列名)不會統(tǒng)計此列為 NULL 值的行。

2.4 GROUP BY

2.4.1 基本使用

可以使用GROUP BY子句將表中的數(shù)據(jù)分成若干組

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

結(jié)論1：SELECT中出現(xiàn)的非組函數(shù)的字段必須聲明在GROUP BY中。

? 反之，GROUP BY中聲明的字段可以不出現(xiàn)在SELECT中。

結(jié)論2：GROUP BY聲明在FROM后面、WHERE后面、ORDER BY前面、LIMIT前面。

2.4.2 使用WITH ROLLUP

SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;

注意： 當使用ROLLUP時，不能同時使用ORDER BY子句進行結(jié)果排序，即ROLLUP和ORDER BY是互相排斥的。

2.5. HAVING

2.5.1 基本使用

過濾分組：HAVING子句

1. 行已經(jīng)被分組。
2. 使用了聚合函數(shù)。
3. 滿足HAVING 子句中條件的分組將被顯示。
4. HAVING 不能單獨使用，必須要跟 GROUP BY 一起使用。

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;

要求

• 如果過濾條件中使用了聚合函數(shù)，則必須使用HAVING來替換WHERE。否則，報錯。
• 當過濾條件中沒有聚合函數(shù)時，則次過濾條件聲明在WHERE中或HAVING中都可以。但是，建議聲明在WHERE中的執(zhí)行效率高。
• HAVING必須聲明在GROUP BY 的后面
• 開發(fā)中，我們使用HAVING的前提是SQL中使用了GROUP BY。

2.5.2 WHERE和HAVING的對比

區(qū)別1：WHERE 可以直接使用表中的字段作為篩選條件，但不能使用分組中的計算函數(shù)作為篩選條件； HAVING 必須要與 GROUP BY 配合使用，可以把分組計算的函數(shù)和分組字段作為篩選條件。

這決定了，在需要對數(shù)據(jù)進行分組統(tǒng)計的時候，HAVING 可以完成 WHERE 不能完成的任務。這是因為， 在查詢語法結(jié)構(gòu)中，WHERE 在 GROUP BY 之前，所以無法對分組結(jié)果進行篩選。HAVING 在 GROUP BY 之 后，可以使用分組字段和分組中的計算函數(shù)，對分組的結(jié)果集進行篩選，這個功能是 WHERE 無法完成 的。另外，WHERE排除的記錄不再包括在分組中。

區(qū)別2：如果需要通過連接從關(guān)聯(lián)表中獲取需要的數(shù)據(jù)，WHERE 是先篩選后連接，而 HAVING 是先連接 后篩選。

這一點，就決定了在關(guān)聯(lián)查詢中，WHERE 比 HAVING 更高效。因為 WHERE 可以先篩選，用一 個篩選后的較小數(shù)據(jù)集和關(guān)聯(lián)表進行連接，這樣占用的資源比較少，執(zhí)行效率也比較高。HAVING 則需要 先把結(jié)果集準備好，也就是用未被篩選的數(shù)據(jù)集進行關(guān)聯(lián)，然后對這個大的數(shù)據(jù)集進行篩選，這樣占用 的資源就比較多，執(zhí)行效率也較低。

小結(jié)如下：

開發(fā)中的選擇：

WHERE 和 HAVING 也不是互相排斥的，我們可以在一個查詢里面同時使用 WHERE 和 HAVING。包含分組 統(tǒng)計函數(shù)的條件用 HAVING，普通條件用 WHERE。這樣，我們就既利用了 WHERE 條件的高效快速，又發(fā) 揮了 HAVING 可以使用包含分組統(tǒng)計函數(shù)的查詢條件的優(yōu)點。當數(shù)據(jù)量特別大的時候，運行效率會有很 大的差別。

二、視圖

1.視圖概述

• 視圖是一種 虛擬表 ，本身是不具有數(shù)據(jù)的，占用很少的內(nèi)存空間，它是 SQL 中的一個重要概念。
• 視圖建立在已有表的基礎上, 視圖賴以建立的這些表稱為基表。
• 視圖的創(chuàng)建和刪除只影響視圖本身，不影響對應的基表。但是當對視圖中的數(shù)據(jù)進行增加、刪除和修改操作時，數(shù)據(jù)表中的數(shù)據(jù)會相應地發(fā)生變化，反之亦然。
• 視圖提供數(shù)據(jù)內(nèi)容的語句為 SELECT 語句, 可以將視圖理解為存儲起來的 SELECT 語句
• 在數(shù)據(jù)庫中，視圖不會保存數(shù)據(jù)，數(shù)據(jù)真正保存在數(shù)據(jù)表中。當對視圖中的數(shù)據(jù)進行增加、刪除和修改操作時，數(shù)據(jù)表中的數(shù)據(jù)會相應地發(fā)生變化；反之亦然。
• 視圖，是向用戶提供基表數(shù)據(jù)的另一種表現(xiàn)形式。通常情況下，小型項目的數(shù)據(jù)庫可以不使用視圖，但是在大型項目中，以及數(shù)據(jù)表比較復雜的情況下，視圖的價值就凸顯出來了，它可以幫助我 們把經(jīng)常查詢的結(jié)果集放到虛擬表中，提升使用效率。理解和使用起來都非常方便。

2.創(chuàng)建視圖

CREATE VIEW 視圖名稱 AS 查詢語句

2.1 創(chuàng)建單表視圖

# 方式一：
CREATE VIEW empvu80
AS
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;

# 方式二：
CREATE VIEW empsalary8000(emp_id, NAME, monthly_sal) # 小括號內(nèi)字段個數(shù)與SELECT中字段個數(shù)相同
AS
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > 8000;

2.2 創(chuàng)建多表聯(lián)合視圖

CREATE VIEW empview
AS
SELECT employee_id emp_id,last_name NAME,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;

CREATE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS
SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;

2.3 基于視圖創(chuàng)建視圖

當我們創(chuàng)建好一張視圖之后，還可以在它的基礎上繼續(xù)創(chuàng)建視圖。
舉例：聯(lián)合“emp_dept”視圖和“emp_year_salary”視圖查詢員工姓名、部門名稱、年薪信息創(chuàng)建 “emp_dept_ysalary”視圖。

CREATE VIEW emp_dept_ysalary
AS
SELECT emp_dept.ename,dname,year_salary
FROM emp_dept INNER JOIN emp_year_salary
ON emp_dept.ename = emp_year_salary.ename;

3.更新視圖的數(shù)據(jù)

3.1 一般情況

MySQL支持使用INSERT、UPDATE和DELETE語句對視圖中的數(shù)據(jù)進行插入、更新和刪除操作。當視圖中的 數(shù)據(jù)發(fā)生變化時，數(shù)據(jù)表中的數(shù)據(jù)也會發(fā)生變化，反之亦然。

舉例：UPDATE操作

UPDATE emp_tel SET tel = '13789091234' WHERE ename = '孫洪亮';

舉例：DELETE操作

 DELETE FROM emp_tel WHERE ename = '孫洪亮';

3.2 不可更新的視圖

要使視圖可更新，視圖中的行和底層基本表中的行之間必須存在 一對一 的關(guān)系。另外當視圖定義出現(xiàn)如下情況時，視圖不支持更新操作：

• 在定義視圖的時候指定了“ALGORITHM = TEMPTABLE”，視圖將不支持INSERT和DELETE操作；
• 視圖中不包含基表中所有被定義為非空又未指定默認值的列，視圖將不支持INSERT操作；
• 在定義視圖的SELECT語句中使用了 JOIN聯(lián)合查詢 ，視圖將不支持INSERT和DELETE操作；
• 在定義視圖的SELECT語句后的字段列表中使用了 數(shù)學表達式 或 子查詢 ，視圖將不支持INSERT，也 不支持UPDATE使用了數(shù)學表達式、子查詢的字段值；
• 在定義視圖的SELECT語句后的字段列表中使用 DISTINCT 、 聚合函數(shù) 、 GROUP BY 、 HAVING 、 UNION 等，視圖將不支持INSERT、UPDATE、DELETE；
• 在定義視圖的SELECT語句中包含了子查詢，而子查詢中引用了FROM后面的表，視圖將不支持 INSERT、UPDATE、DELETE；
• 視圖定義基于一個 不可更新視圖 ； 常量視圖。

雖然可以更新視圖數(shù)據(jù)，但總的來說，視圖作為虛擬表 ，主要用于方便查詢 ，不建議更新視圖的數(shù)據(jù)。對視圖數(shù)據(jù)的更改，都是通過對實際數(shù)據(jù)表里數(shù)據(jù)的操作來完成的。

4. 修改、刪除視圖

4.1 修改視圖

方式1：使用CREATE OR REPLACE VIEW 子句修改視圖

CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS
SELECT employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;

說明：CREATE VIEW 子句中各列的別名應和子查詢中各列相對應。

方式2：ALTER VIEW

修改視圖的語法是：

ALTER VIEW 視圖名稱
AS
查詢語句

4.2 刪除視圖

• 刪除視圖只是刪除視圖的定義，并不會刪除基表的數(shù)據(jù)。
• 刪除視圖的語法是：

DROP VIEW IF EXISTS 視圖名稱;

• 舉例：

DROP VIEW empvu80;

• 說明：基于視圖a、b創(chuàng)建了新的視圖c，如果將視圖a或者視圖b刪除，會導致視圖c的查詢失敗。這 樣的視圖c需要手動刪除或修改，否則影響使用。

5. 總結(jié)

5.1 優(yōu)點

1. 操作簡單

將經(jīng)常使用的查詢操作定義為視圖，可以使開發(fā)人員不需要關(guān)心視圖對應的數(shù)據(jù)表的結(jié)構(gòu)、表與表之間的關(guān)聯(lián)關(guān)系，也不需要關(guān)心數(shù)據(jù)表之間的業(yè)務邏輯和查詢條件，而只需要簡單地操作視圖即可，極大簡化了開發(fā)人員對數(shù)據(jù)庫的操作。

2. 減少數(shù)據(jù)冗余

視圖跟實際數(shù)據(jù)表不一樣，它存儲的是查詢語句。所以，在使用的時候，我們要通過定義視圖的查詢語句來獲取結(jié)果集。而視圖本身不存儲數(shù)據(jù)，不占用數(shù)據(jù)存儲的資源，減少了數(shù)據(jù)冗余。

3. 數(shù)據(jù)安全

MySQL將用戶對數(shù)據(jù)的 訪問限制 在某些數(shù)據(jù)的結(jié)果集上，而這些數(shù)據(jù)的結(jié)果集可以使用視圖來實現(xiàn)。用戶不必直接查詢或操作數(shù)據(jù)表。這也可以理解為視圖具有 隔離性 。視圖相當于在用戶和實際的數(shù)據(jù)表之間加了一層虛擬表。

同時，MySQL可以根據(jù)權(quán)限將用戶對數(shù)據(jù)的訪問限制在某些視圖上，用戶不需要查詢數(shù)據(jù)表，可以直接通過視圖獲取數(shù)據(jù)表中的信息。這在一定程度上保障了數(shù)據(jù)表中數(shù)據(jù)的安全性。

4. 適應靈活多變的需求

當業(yè)務系統(tǒng)的需求發(fā)生變化后，如果需要改動數(shù)據(jù)表的結(jié)構(gòu)，則工作量相對較 大，可以使用視圖來減少改動的工作量。這種方式在實際工作中使用得比較多。

5. 能夠分解復雜的查詢邏輯

數(shù)據(jù)庫中如果存在復雜的查詢邏輯，則可以將問題進行分解，創(chuàng)建多個視圖 獲取數(shù)據(jù)，再將創(chuàng)建的多個視圖結(jié)合起來，完成復雜的查詢邏輯。

5.2 不足

如果我們在實際數(shù)據(jù)表的基礎上創(chuàng)建了視圖，那么，如果實際數(shù)據(jù)表的結(jié)構(gòu)變更了，我們就需要及時對相關(guān)的視圖進行相應的維護。特別是嵌套的視圖（就是在視圖的基礎上創(chuàng)建視圖），維護會變得比較復雜， 可讀性不好 ，容易變成系統(tǒng)的潛在隱患。因為創(chuàng)建視圖的 SQL 查詢可能會對字段重命名，也可能包含復雜的邏輯，這些都會增加維護的成本。

實際項目中，如果視圖過多，會導致數(shù)據(jù)庫維護成本的問題。

所以，在創(chuàng)建視圖的時候，你要結(jié)合實際項目需求，綜合考慮視圖的優(yōu)點和不足，這樣才能正確使用視圖，使系統(tǒng)整體達到最優(yōu)。

三、存儲過程

MySQL從5.0版本開始支持存儲過程和函數(shù)。存儲過程和函數(shù)能夠?qū)碗s的SQL邏輯封裝在一起，應用程 序無須關(guān)注存儲過程和函數(shù)內(nèi)部復雜的SQL邏輯，而只需要簡單地調(diào)用存儲過程和函數(shù)即可。

1. 存儲過程概述

1.1 理解

**含義：**存儲過程的英文是 Stored Procedure 。它的思想很簡單，就是一組經(jīng)過 預先編譯的 SQL 語句 的封裝。

執(zhí)行過程：存儲過程預先存儲在 MySQL 服務器上，需要執(zhí)行的時候，客戶端只需要向服務器端發(fā)出調(diào)用存儲過程的命令，服務器端就可以把預先存儲好的這一系列 SQL 語句全部執(zhí)行。

好處：

• 1、簡化操作，提高了sql語句的重用性，減少了開發(fā)程序員的壓力。
• 2、減少操作過程中的失誤，提高效率。
• 3、減少網(wǎng)絡傳輸量（客戶端不需要把所有的 SQL 語句通過網(wǎng)絡發(fā)給服務器）。
• 4、減少了 SQL 語句暴露在 網(wǎng)上的風險，也提高了數(shù)據(jù)查詢的安全性。

和視圖、函數(shù)的對比：

它和視圖有著同樣的優(yōu)點，清晰、安全，還可以減少網(wǎng)絡傳輸量。不過它和視圖不同，視圖是虛擬表 ，通常不對底層數(shù)據(jù)表直接操作，而存儲過程是程序化的 SQL，可以 直接操作底層數(shù)據(jù)表 ，相比于面向集合的操作方式，能夠?qū)崿F(xiàn)一些更復雜的數(shù)據(jù)處理。

一旦存儲過程被創(chuàng)建出來，使用它就像使用函數(shù)一樣簡單，我們直接通過調(diào)用存儲過程名即可。相較于函數(shù)，存儲過程是 沒有返回值 的。

1.2 分類

存儲過程的參數(shù)類型可以是IN、OUT和INOUT。根據(jù)這點分類如下：

1、沒有參數(shù)（無參數(shù)無返回）

2、僅僅帶 IN 類型（有參數(shù)無返回）

3、僅僅帶 OUT 類型（無參數(shù)有返回）

4、既帶 IN 又帶 OUT（有參數(shù)有返回）

5、帶 INOUT（有參數(shù)有返回）

注意：IN、OUT、INOUT 都可以在一個存儲過程中帶多個。

2. 創(chuàng)建存儲過程

2.1 語法分析

語法：

CREATE PROCEDURE 存儲過程名(IN|OUT|INOUT 參數(shù)名 參數(shù)類型,...)
[characteristics ...]
BEGIN
存儲過程體
END

說明：

1、參數(shù)前面的符號的意思

• IN ：當前參數(shù)為輸入?yún)?shù)，也就是表示入?yún)ⅲ?/p>

  存儲過程只是讀取這個參數(shù)的值。如果沒有定義參數(shù)種類， 默認就是 IN ，表示輸入?yún)?shù)。

• OUT ：當前參數(shù)為輸出參數(shù)，也就是表示出參；

  執(zhí)行完成之后，調(diào)用這個存儲過程的客戶端或者應用程序就可以讀取這個參數(shù)返回的值了。

• INOUT ：當前參數(shù)既可以為輸入?yún)?shù)，也可以為輸出參數(shù)。

2、形參類型可以是 MySQL數(shù)據(jù)庫中的任意類型。

3、characteristics 表示創(chuàng)建存儲過程時指定的對存儲過程的約束條件，其取值信息如下：

LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

• LANGUAGE SQL ：說明存儲過程執(zhí)行體是由SQL語句組成的，當前系統(tǒng)支持的語言為SQL。

• [NOT] DETERMINISTIC ：指明存儲過程執(zhí)行的結(jié)果是否確定。DETERMINISTIC表示結(jié)果是確定 的。每次執(zhí)行存儲過程時，相同的輸入會得到相同的輸出。NOT DETERMINISTIC表示結(jié)果是不確定 的，相同的輸入可能得到不同的輸出。如果沒有指定任意一個值，默認為NOT DETERMINISTIC。

• { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使 用SQL語句的限制。

  • CONTAINS SQL表示當前存儲過程的子程序包含SQL語句，但是并不包含讀寫數(shù)據(jù)的SQL語句；
  • NO SQL表示當前存儲過程的子程序中不包含任何SQL語句；
  • READS SQL DATA表示當前存儲過程的子程序中包含讀數(shù)據(jù)的SQL語句；
  • MODIFIES SQL DATA表示當前存儲過程的子程序中包含寫數(shù)據(jù)的SQL語句。
  • 默認情況下，系統(tǒng)會指定為CONTAINS SQL。

• SQL SECURITY { DEFINER | INVOKER } ：執(zhí)行當前存儲過程的權(quán)限，即指明哪些用戶能夠執(zhí)行當前存儲過程。

  • DEFINER 表示只有當前存儲過程的創(chuàng)建者或者定義者才能執(zhí)行當前存儲過程；
  • INVOKER 表示擁有當前存儲過程的訪問權(quán)限的用戶能夠執(zhí)行當前存儲過程。

• COMMENT ‘string’ ：注釋信息，可以用來描述存儲過程。

4、存儲過程體中可以有多條 SQL 語句，如果僅僅一條SQL 語句，則可以省略 BEGIN 和 END

1. BEGIN…END：BEGIN…END 中間包含了多個語句，每個語句都以（;）號為結(jié)束符。
2. DECLARE：DECLARE 用來聲明變量，使用的位置在于 BEGIN…END 語句中間，而且需要在其他語句使用之前進
行變量的聲明。
3. SET：賦值語句，用于對變量進行賦值。
4. SELECT… INTO：把從數(shù)據(jù)表中查詢的結(jié)果存放到變量中，也就是為變量賦值。

5、需要設置新的結(jié)束標記

DELIMITER 新的結(jié)束標記

因為MySQL默認的語句結(jié)束符號為分號‘;’。為了避免與存儲過程中SQL語句結(jié)束符相沖突，需要使用 DELIMITER改變存儲過程的結(jié)束符。

比如：“DELIMITER //”語句的作用是將MySQL的結(jié)束符設置為//，并以“END //”結(jié)束存儲過程。存儲過程定 義完畢之后再使用“DELIMITER ;”恢復默認結(jié)束符。DELIMITER也可以指定其他符號作為結(jié)束符。

當使用DELIMITER命令時，應該避免使用反斜杠（‘\’）字符，因為反斜線是MySQL的轉(zhuǎn)義字符。

示例：

DELIMITER $
CREATE PROCEDURE 存儲過程名(IN|OUT|INOUT 參數(shù)名 參數(shù)類型,...)
[characteristics ...]
BEGIN
sql語句1;
sql語句2;
END $

2.2 代碼舉例

舉例1：創(chuàng)建存儲過程select_all_data()，查看 emps 表的所有數(shù)據(jù)

DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
SELECT * FROM emps;
END $
DELIMITER ;

舉例2：創(chuàng)建存儲過程avg_employee_salary()，返回所有員工的平均工資

DELIMITER //
CREATE PROCEDURE avg_employee_salary ()
BEGIN
SELECT AVG(salary) AS avg_salary FROM emps;
END //
DELIMITER ;

3. 調(diào)用存儲過程

3.1 調(diào)用格式

存儲過程有多種調(diào)用方法。存儲過程必須使用CALL語句調(diào)用，并且存儲過程和數(shù)據(jù)庫相關(guān)，如果要執(zhí)行其他數(shù)據(jù)庫中的存儲過程，需要指定數(shù)據(jù)庫名稱，例如CALL dbname.procname。

CALL 存儲過程名(實參列表)

格式：

1、調(diào)用in模式的參數(shù)：

CALL sp1('值');

2、調(diào)用out模式的參數(shù)：

SET @name;
CALL sp1(@name);
SELECT @name;

3、調(diào)用inout模式的參數(shù)：

SET @name=值;
CALL sp1(@name);
SELECT @name;

3.2 代碼舉例

舉例1：

DELIMITER //
CREATE PROCEDURE CountProc(IN sid INT,OUT num INT)
BEGIN
SELECT COUNT(*) INTO num FROM fruits
WHERE s_id = sid;
END //
DELIMITER ;

調(diào)用存儲過程：

CALL CountProc (101, @num);

查看返回結(jié)果：

SELECT @num;

**舉例2：**創(chuàng)建存儲過程，實現(xiàn)累加運算，計算 1+2+…+n 等于多少。具體的代碼如下：

DELIMITER //
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
DECLARE i INT;
DECLARE sum INT;
SET i = 1;
SET sum = 0;
WHILE i <= n DO
SET sum = sum + i;
SET i = i +1;
END WHILE;
SELECT sum;
END //
DELIMITER ;

直接使用 CALL add_num(50); 即可。這里我傳入的參數(shù)為 50，也就是統(tǒng)計 1+2+…+50 的積累之和。

3.3 如何調(diào)試

在 MySQL 中，存儲過程不像普通的編程語言（比如 VC++、Java 等）那樣有專門的集成開發(fā)環(huán)境。因 此，你可以通過 SELECT 語句，把程序執(zhí)行的中間結(jié)果查詢出來，來調(diào)試一個 SQL 語句的正確性。調(diào)試 成功之后，把 SELECT 語句后移到下一個 SQL 語句之后，再調(diào)試下一個 SQL 語句。這樣 逐步推進 ，就可以完成對存儲過程中所有操作的調(diào)試了。當然，你也可以把存儲過程中的 SQL 語句復制出來，逐段單獨 調(diào)試。

4. 存儲函數(shù)的使用

4.1 語法分析

學過的函數(shù)：LENGTH、SUBSTR、CONCAT等

語法格式：

CREATE FUNCTION 函數(shù)名(參數(shù)名 參數(shù)類型,...)
RETURNS 返回值類型
[characteristics ...]
BEGIN
函數(shù)體 #函數(shù)體中肯定有 RETURN 語句
END

說明：

1、參數(shù)列表：指定參數(shù)為IN、OUT或INOUT只對PROCEDURE是合法的，F(xiàn)UNCTION中總是默認為IN參數(shù)。

2、RETURNS type 語句表示函數(shù)返回數(shù)據(jù)的類型； RETURNS子句只能對FUNCTION做指定，對函數(shù)而言這是 強制 的。它用來指定函數(shù)的返回類型，而且函 數(shù)體必須包含一個 RETURN value 語句。

3、characteristic 創(chuàng)建函數(shù)時指定的對函數(shù)的約束。取值與創(chuàng)建存儲過程時相同，這里不再贅述。

4、函數(shù)體也可以用BEGIN…END來表示SQL代碼的開始和結(jié)束。如果函數(shù)體只有一條語句，也可以省略 BEGIN…END。

4.2 調(diào)用存儲函數(shù)

在MySQL中，存儲函數(shù)的使用方法與MySQL內(nèi)部函數(shù)的使用方法是一樣的。換言之，用戶自己定義的存儲函數(shù)與MySQL內(nèi)部函數(shù)是一個性質(zhì)的。區(qū)別在于，存儲函數(shù)是 用戶自己定義 的，而內(nèi)部函數(shù)是MySQL 的 開發(fā)者定義 的。

SELECT 函數(shù)名(實參列表)

4.3 代碼舉例

舉例1：

創(chuàng)建存儲函數(shù)，名稱為email_by_name()，參數(shù)定義為空，該函數(shù)查詢Abel的email，并返回，數(shù)據(jù)類型為字符串型。

DELIMITER //
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN
RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //
DELIMITER ;

調(diào)用：

SELECT email_by_name();

舉例2：

創(chuàng)建存儲函數(shù)，名稱為email_by_id()，參數(shù)傳入emp_id，該函數(shù)查詢emp_id的email，并返回，數(shù)據(jù)類型 為字符串型。

DELIMITER //
CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN
RETURN (SELECT email FROM employees WHERE employee_id = emp_id);
END //
DELIMITER ;

調(diào)用：

SET @emp_id = 102;
SELECT email_by_id(@emp_id);

注意：

若在創(chuàng)建存儲函數(shù)中報錯“ you might want to use the less safe log_bin_trust_function_creators variable ”，有兩種處理方法：

• 方式1：

  加上必要的函數(shù)特性“[NOT] DETERMINISTIC”和“{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}”

• 方式2：

SET GLOBAL log_bin_trust_function_creators = 1;

4.4 對比存儲函數(shù)與存儲過程

此外，存儲函數(shù)可以放在查詢語句中使用，存儲過程不行。反之，存儲過程的功能更加強大，包括能夠 執(zhí)行對表的操作（比如創(chuàng)建表，刪除表等）和事務操作，這些功能是存儲函數(shù)不具備的。

5. 存儲過程和函數(shù)的查看、修改、刪除

5.1 查看

創(chuàng)建完之后，怎么知道我們創(chuàng)建的存儲過程、存儲函數(shù)是否成功了呢？

MySQL存儲了存儲過程和函數(shù)的狀態(tài)信息，用戶可以使用SHOW STATUS語句或SHOW CREATE語句來查 看，也可直接從系統(tǒng)的information_schema數(shù)據(jù)庫中查詢。這里介紹3種方法。

1. 使用SHOW CREATE語句查看存儲過程和函數(shù)的創(chuàng)建信息

SHOW CREATE {PROCEDURE | FUNCTION} 存儲過程名或函數(shù)名

2. 使用SHOW STATUS語句查看存儲過程和函數(shù)的狀態(tài)信息

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

3. 從information_schema.Routines表中查看存儲過程和函數(shù)的信息

MySQL中存儲過程和函數(shù)的信息存儲在information_schema數(shù)據(jù)庫下的Routines表中。可以通過查詢該表的記錄來查詢存儲過程和函數(shù)的信息。其基本語法形式如下：

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存儲過程或函數(shù)的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];

說明：如果在MySQL數(shù)據(jù)庫中存在存儲過程和函數(shù)名稱相同的情況，最好指定ROUTINE_TYPE查詢條件來 指明查詢的是存儲過程還是函數(shù)。

5.2 修改

修改存儲過程或函數(shù)，不影響存儲過程或函數(shù)功能，只是修改相關(guān)特性。使用ALTER語句實現(xiàn)。

ALTER {PROCEDURE | FUNCTION} 存儲過程或函數(shù)的名 [characteristic ...]

其中，characteristic指定存儲過程或函數(shù)的特性，其取值信息與創(chuàng)建存儲過程、函數(shù)時的取值信息略有不同。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

• CONTAINS SQL ，表示子程序包含SQL語句，但不包含讀或?qū)憯?shù)據(jù)的語句。

• NO SQL ，表示子程序中不包含SQL語句。

• READS SQL DATA ，表示子程序中包含讀數(shù)據(jù)的語句。

• MODIFIES SQL DATA ，表示子程序中包含寫數(shù)據(jù)的語句。

• SQL SECURITY { DEFINER | INVOKER } ，指明誰有權(quán)限來執(zhí)行。

  • DEFINER ，表示只有定義者自己才能夠執(zhí)行。
  • INVOKER ，表示調(diào)用者可以執(zhí)行。

• COMMENT ‘string’ ，表示注釋信息。

修改存儲過程使用ALTER PROCEDURE語句，修改存儲函數(shù)使用ALTER FUNCTION語句。但是，這兩 個語句的結(jié)構(gòu)是一樣的，語句中的所有參數(shù)也是一樣的。

5.3 刪除

刪除存儲過程和函數(shù)，可以使用DROP語句，其語法結(jié)構(gòu)如下：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存儲過程或函數(shù)的名

6. 關(guān)于存儲過程使用的爭議

6.1 優(yōu)點

1、存儲過程可以一次編譯多次使用。存儲過程只在創(chuàng)建時進行編譯，之后的使用都不需要重新編譯， 這就提升了 SQL 的執(zhí)行效率。

2、可以減少開發(fā)工作量。將代碼 封裝 成模塊，實際上是編程的核心思想之一，這樣可以把復雜的問題 拆解成不同的模塊，然后模塊之間可以 重復使用 ，在減少開發(fā)工作量的同時，還能保證代碼的結(jié)構(gòu)清 晰。

3、存儲過程的安全性強。我們在設定存儲過程的時候可以 設置對用戶的使用權(quán)限 ，這樣就和視圖一樣具 有較強的安全性。

4、可以減少網(wǎng)絡傳輸量。因為代碼封裝到存儲過程中，每次使用只需要調(diào)用存儲過程即可，這樣就減 少了網(wǎng)絡傳輸量。

5、良好的封裝性。在進行相對復雜的數(shù)據(jù)庫操作時，原本需要使用一條一條的 SQL 語句，可能要連接 多次數(shù)據(jù)庫才能完成的操作，現(xiàn)在變成了一次存儲過程，只需要 連接一次即可 。

6.2 缺點

阿里開發(fā)規(guī)范 【強制】禁止使用存儲過程，存儲過程難以調(diào)試和擴展，更沒有移植性。

1、可移植性差。存儲過程不能跨數(shù)據(jù)庫移植，比如在 MySQL、Oracle 和 SQL Server 里編寫的存儲過 程，在換成其他數(shù)據(jù)庫時都需要重新編寫。

2、調(diào)試困難。只有少數(shù) DBMS 支持存儲過程的調(diào)試。對于復雜的存儲過程來說，開發(fā)和維護都不容 易。雖然也有一些第三方工具可以對存儲過程進行調(diào)試，但要收費。

3、存儲過程的版本管理很困難。比如數(shù)據(jù)表索引發(fā)生變化了，可能會導致存儲過程失效。我們在開發(fā) 軟件的時候往往需要進行版本管理，但是存儲過程本身沒有版本控制，版本迭代更新的時候很麻煩。

4、它不適合高并發(fā)的場景。高并發(fā)的場景需要減少數(shù)據(jù)庫的壓力，有時數(shù)據(jù)庫會采用分庫分表的方式，而且對可擴展性要求很高，在這種情況下，存儲過程會變得難以維護， 增加數(shù)據(jù)庫的壓力 ，顯然就不適用了。

6.3 小結(jié)

存儲過程既方便，又有局限性。盡管不同的公司對存儲過程的態(tài)度不一，但是對于我們開發(fā)人員來說， 不論怎樣，掌握存儲過程都是必備的技能之一。

四、觸發(fā)器

在實際開發(fā)中，我們經(jīng)常會遇到這樣的情況：有 2 個或者多個相互關(guān)聯(lián)的表，如 商品信息 和 庫存信息 分別存放在 2 個不同的數(shù)據(jù)表中，我們在添加一條新商品記錄的時候，為了保證數(shù)據(jù)的完整性，必須同時 在庫存表中添加一條庫存記錄。

這樣一來，我們就必須把這兩個關(guān)聯(lián)的操作步驟寫到程序里面，而且要用事務包裹起來，確保這兩個操作成為一個 原子操作 ，要么全部執(zhí)行，要么全部不執(zhí)行。要是遇到特殊情況，可能還需要對數(shù)據(jù)進行手動維護，這樣就很容易忘記其中的一步 ，導致數(shù)據(jù)缺失。

這個時候，咱們可以使用觸發(fā)器。你可以創(chuàng)建一個觸發(fā)器，讓商品信息數(shù)據(jù)的插入操作自動觸發(fā)庫存數(shù)據(jù)的插入操作。這樣一來，就不用擔心因為忘記添加庫存數(shù)據(jù)而導致的數(shù)據(jù)缺失了。

1. 觸發(fā)器概述

觸發(fā)器是由 事件來觸發(fā) 某個操作，這些事件包括 INSERT 、 UPDATE 、 DELETE 事件。所謂事件就是指用戶的動作或者觸發(fā)某項行為。如果定義了觸發(fā)程序，當數(shù)據(jù)庫執(zhí)行這些語句時候，就相當于事件發(fā)生 了，就會 自動 激發(fā)觸發(fā)器執(zhí)行相應的操作。

當對數(shù)據(jù)表中的數(shù)據(jù)執(zhí)行插入、更新和刪除操作，需要自動執(zhí)行一些數(shù)據(jù)庫邏輯時，可以使用觸發(fā)器來實現(xiàn)。

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

2.1 語法

CREATE TRIGGER 觸發(fā)器名稱
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
觸發(fā)器執(zhí)行的語句塊

說明：

• 表名 ：表示觸發(fā)器監(jiān)控的對象。
• BEFORE|AFTER ：表示觸發(fā)的時間。BEFORE 表示在事件之前觸發(fā)；AFTER 表示在事件之后觸發(fā)。
• INSERT|UPDATE|DELETE ：表示觸發(fā)的事件。
  • INSERT 表示插入記錄時觸發(fā)；
  • UPDATE 表示更新記錄時觸發(fā)；
  • DELETE 表示刪除記錄時觸發(fā)。
• 觸發(fā)器執(zhí)行的語句塊 ：可以是單條SQL語句，也可以是由BEGIN…END結(jié)構(gòu)組成的復合語句塊。

2.2 代碼舉例

舉例1：

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

CREATE TABLE test_trigger (
id INT PRIMARY KEY AUTO_INCREMENT,
t_note VARCHAR(30)
);

CREATE TABLE test_trigger_log (
id INT PRIMARY KEY AUTO_INCREMENT,
t_log VARCHAR(30)
);

2. 創(chuàng)建觸發(fā)器：創(chuàng)建名稱為before_insert的觸發(fā)器，向test_trigger數(shù)據(jù)表插入數(shù)據(jù)之前，向 test_trigger_log數(shù)據(jù)表中插入before_insert的日志信息。

DELIMITER //
CREATE TRIGGER before_insert
BEFORE INSERT ON test_trigger
FOR EACH ROW
BEGIN
    INSERT INTO test_trigger_log (t_log)
    VALUES('before_insert');
END //
DELIMITER ;

3. 向test_trigger數(shù)據(jù)表中插入數(shù)據(jù)

INSERT INTO test_trigger (t_note) VALUES ('測試 BEFORE INSERT 觸發(fā)器');

4. 查看test_trigger_log數(shù)據(jù)表中的數(shù)據(jù)

mysql> SELECT * FROM test_trigger_log;
+----+---------------+
| id | t_log |
+----+---------------+
| 1 | before_insert |
+----+---------------+
1 row in set (0.00 sec)

舉例2：

定義觸發(fā)器“salary_check_trigger”，基于員工表“employees”的INSERT事件，在INSERT之前檢查 將要添加的新員工薪資是否大于他領(lǐng)導的薪資，如果大于領(lǐng)導薪資，則報sqlstate_value為’HY000’的錯 誤，從而使得添加失敗。

DELIMITER //
CREATE TRIGGER salary_check_trigger
BEFORE INSERT ON employees FOR EACH ROW
BEGIN
    DECLARE mgrsalary DOUBLE;
    SELECT salary INTO mgrsalary FROM employees WHERE employee_id = NEW.manager_id;
    IF NEW.salary > mgrsalary THEN
    	SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪資高于領(lǐng)導薪資錯誤';
    END IF;
END //
DELIMITER ;

上面觸發(fā)器聲明過程中的NEW關(guān)鍵字代表INSERT添加語句的新記錄。

3. 查看、刪除觸發(fā)器

3.1 查看觸發(fā)器

查看觸發(fā)器是查看數(shù)據(jù)庫中已經(jīng)存在的觸發(fā)器的定義、狀態(tài)和語法信息等。

方式1：查看當前數(shù)據(jù)庫的所有觸發(fā)器的定義

SHOW TRIGGERS\G

方式2：查看當前數(shù)據(jù)庫中某個觸發(fā)器的定義

SHOW CREATE TRIGGER 觸發(fā)器名

方式3：從系統(tǒng)庫information_schema的TRIGGERS表中查詢“salary_check_trigger”觸發(fā)器的信息。

SELECT * FROM information_schema.TRIGGERS;

3.2 刪除觸發(fā)器

觸發(fā)器也是數(shù)據(jù)庫對象，刪除觸發(fā)器也用DROP語句，語法格式如下：

DROP TRIGGER IF EXISTS 觸發(fā)器名稱;

4. 觸發(fā)器的優(yōu)缺點

4.1 優(yōu)點

1、觸發(fā)器可以確保數(shù)據(jù)的完整性。

假設我們用 進貨單頭表 （demo.importhead）來保存進貨單的總體信息，包括進貨單編號、供貨商編號、倉庫編號、總計進貨數(shù)量、總計進貨金額和驗收日期。

用進貨單明細表 （demo.importdetails）來保存進貨商品的明細，包括進貨單編號、商品編號、進貨數(shù) 量、進貨價格和進貨金額。

每當我們錄入、刪除和修改一條進貨單明細數(shù)據(jù)的時候，進貨單明細表里的數(shù)據(jù)就會發(fā)生變動。這個時候，在進貨單頭表中的總計數(shù)量和總計金額就必須重新計算，否則，進貨單頭表中的總計數(shù)量和總計金 額就不等于進貨單明細表中數(shù)量合計和金額合計了，這就是數(shù)據(jù)不一致。

為了解決這個問題，我們就可以使用觸發(fā)器，規(guī)定每當進貨單明細表有數(shù)據(jù)插入、修改和刪除的操作 時，自動觸發(fā) 2 步操作：

1）重新計算進貨單明細表中的數(shù)量合計和金額合計；

2）用第一步中計算出來的值更新進貨單頭表中的合計數(shù)量與合計金額。

這樣一來，進貨單頭表中的合計數(shù)量與合計金額的值，就始終與進貨單明細表中計算出來的合計數(shù)量與 合計金額的值相同，數(shù)據(jù)就是一致的，不會互相矛盾。

2、觸發(fā)器可以幫助我們記錄操作日志。

利用觸發(fā)器，可以具體記錄什么時間發(fā)生了什么。比如，記錄修改會員儲值金額的觸發(fā)器，就是一個很好的例子。這對我們還原操作執(zhí)行時的具體場景，更好地定位問題原因很有幫助。

3、觸發(fā)器還可以用在操作數(shù)據(jù)前，對數(shù)據(jù)進行合法性檢查。

比如，超市進貨的時候，需要庫管錄入進貨價格。但是，人為操作很容易犯錯誤，比如說在錄入數(shù)量的時候，把條形碼掃進去了；錄入金額的時候，看串了行，錄入的價格遠超售價，導致賬面上的巨虧…… 這些都可以通過觸發(fā)器，在實際插入或者更新操作之前，對相應的數(shù)據(jù)進行檢查，及時提示錯誤，防止錯誤數(shù)據(jù)進入系統(tǒng)。

4.2 缺點

1、觸發(fā)器最大的一個問題就是可讀性差。

因為觸發(fā)器存儲在數(shù)據(jù)庫中，并且由事件驅(qū)動，這就意味著觸發(fā)器有可能不受應用層的控制 。這對系統(tǒng)維護是非常有挑戰(zhàn)的。

2、相關(guān)數(shù)據(jù)的變更，可能會導致觸發(fā)器出錯。

特別是數(shù)據(jù)表結(jié)構(gòu)的變更，都可能會導致觸發(fā)器出錯，進而影響數(shù)據(jù)操作的正常運行。這些都會由于觸發(fā)器本身的隱蔽性，影響到應用中錯誤原因排查的效率。

4.3 注意點

注意，如果在子表中定義了外鍵約束，并且外鍵指定了ON UPDATE/DELETE CASCADE/SET NULL子句，此時修改父表被引用的鍵值或刪除父表被引用的記錄行時，也會引起子表的修改和刪除操作，此時基于子表的UPDATE和DELETE語句定義的觸發(fā)器并不會被激活。

例如：基于子表員工表（t_employee）的DELETE語句定義了觸發(fā)器t1，而子表的部門編號（did）字段定義了外鍵約束引用了父表部門表（t_department）的主鍵列部門編號（did），并且該外鍵加了“ON DELETE SET NULL”子句，那么如果此時刪除父表部門表（t_department）在子表員工表（t_employee） 有匹配記錄的部門記錄時，會引起子表員工表（t_employee）匹配記錄的部門編號（did）修改為NULL， mysql> update demo.membermaster set memberdeposit=20 where memberid = 2; ERROR 1054 (42S22): Unknown column ‘a(chǎn)a’ in ‘field list’ 但是此時不會激活觸發(fā)器t1。只有直接對子表員工表（t_employee）執(zhí)行DELETE語句時才會激活觸發(fā)器 t1。

?

??

??

??
文章來源地址http://www.zghlxwxcb.cn/news/detail-405937.html

到了這里，關(guān)于MySQL函數(shù)、視圖、存儲過程及觸發(fā)器的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！