超詳細(xì)圖解！【MySQL進(jìn)階篇】存儲(chǔ)過(guò)程,視圖,索引,函數(shù),觸發(fā)器

• • • • 1.1 下載Linux 安裝包
      • 1.2 安裝MySQL
      • 1.3 啟動(dòng) MySQL 服務(wù)
      • 1.4 登錄MySQL
    • 2\. 索引
    • • 2.1 索引概述
      • 2.2 索引優(yōu)勢(shì)劣勢(shì)
      • 2.3 索引結(jié)構(gòu)
      • • 2.3.1 BTREE 結(jié)構(gòu)
        • 2.3.3 B+TREE 結(jié)構(gòu)
        • 2.3.3 MySQL中的B+Tree
      • 2.4 索引分類
      • 2.5 索引語(yǔ)法
      • • 2.5.1 創(chuàng)建索引
        • 2.5.2 查看索引
        • 2.5.3 刪除索引
        • 2.5.4 ALTER命令
      • 2.6 索引設(shè)計(jì)原則
    • 3\. 視圖
    • • 3.1 視圖概述
      • 3.2 創(chuàng)建或者修改視圖
      • 3.3 查看視圖
      • 3.4 刪除視圖
    • 4\. 存儲(chǔ)過(guò)程和函數(shù)
    • • 4.1 存儲(chǔ)過(guò)程和函數(shù)概述
      • 4.2 創(chuàng)建存儲(chǔ)過(guò)程
      • 4.3 調(diào)用存儲(chǔ)過(guò)程
      • 4.4 查看存儲(chǔ)過(guò)程
      • 4.5 刪除存儲(chǔ)過(guò)程
      • 4.6 語(yǔ)法
      • • 4.6.1 變量
        • 4.6.2 if條件判斷
        • 4.6.3 傳遞參數(shù)
        • 4.6.4 case結(jié)構(gòu)
        • 4.6.5 while循環(huán)
        • 4.6.6 repeat結(jié)構(gòu)
        • 4.6.7 loop語(yǔ)句
        • 4.6.8 leave語(yǔ)句
        • 4.6.9 游標(biāo)/光標(biāo)
      • 4.7 存儲(chǔ)函數(shù)
    • 5\. 觸發(fā)器
    • • 5.1 介紹
      • 5.2 創(chuàng)建觸發(fā)器
      • 5.3 刪除觸發(fā)器
      • 5.4 查看觸發(fā)器

1.1 下載Linux 安裝包

下載地址：https://dev.mysql.com/downloads/mysql/5.7.html#downloads

1.2 安裝MySQL

APACHE1). 卸載 centos 中預(yù)安裝的 mysqlrpm -qa | grep -i mysqlrpm -e mysql-libs-5.1.71-1.el6.x86_64 --nodeps2). 上傳 mysql 的安裝包alt + p -------> put E:/test/MySQL-5.6.22-1.el6.i686.rpm-bundle.tar3). 解壓 mysql 的安裝包 mkdir mysqltar -xvf MySQL-5.6.22-1.el6.i686.rpm-bundle.tar -C /root/mysql4). 安裝依賴包 yum -y install libaio.so.1 libgcc_s.so.1 libstdc++.so.6 libncurses.so.5 --setopt=protected_multilib=falseyum update libstdc++-4.4.7-4.el6.x86_645). 安裝 mysql-clientrpm -ivh MySQL-client-5.6.22-1.el6.i686.rpm6). 安裝 mysql-serverrpm -ivh MySQL-server-5.6.22-1.el6.i686.rpm

1.3 啟動(dòng) MySQL 服務(wù)

SQLservice mysql startservice mysql stopservice mysql statusservice mysql restart

1.4 登錄MySQL

mysql 安裝完成之后, 會(huì)自動(dòng)生成一個(gè)隨機(jī)的密碼, 并且保存在一個(gè)密碼文件中 : /root/.mysql_secretmysql -u root -p 登錄之后, 修改密碼 :set password = password('itcast');授權(quán)遠(yuǎn)程訪問(wèn) : grant all privileges on *.* to 'root' @'%' identified by 'itcast'; flush privileges;

2. 索引

2.1 索引概述

MySQL官方對(duì)索引的定義為：索引（index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)（有序）。在數(shù)據(jù)之外，數(shù)據(jù)庫(kù)系統(tǒng)還維護(hù)者滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)， 這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高級(jí)查找算法，這種數(shù)據(jù)結(jié)構(gòu)就是索引。如下面的示意圖所示

左邊是數(shù)據(jù)表，一共有兩列七條記錄，最左邊的是數(shù)據(jù)記錄的物理地址（注意邏輯上相鄰的記錄在磁盤上也并不是一定物理相鄰的）。為了加快Col2的查找，可以維護(hù)一個(gè)右邊所示的二叉查找樹，每個(gè)節(jié)點(diǎn)分別包含索引鍵值和一個(gè)指向?qū)?yīng)數(shù)據(jù)記錄物理地址的指針，這樣就可以運(yùn)用二叉查找快速獲取到相應(yīng)數(shù)據(jù)。

一般來(lái)說(shuō)索引本身也很大，不可能全部存儲(chǔ)在內(nèi)存中，因此索引往往以索引文件的形式存儲(chǔ)在磁盤上。索引是數(shù)據(jù)庫(kù)中用來(lái)提高性能的最常用的工具。

2.2 索引優(yōu)勢(shì)劣勢(shì)

優(yōu)勢(shì)

1） 類似于書籍的目錄索引，提高數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫(kù)的IO成本。

2） 通過(guò)索引列對(duì)數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本，降低CPU的消耗。

劣勢(shì)

1） 實(shí)際上索引也是一張表，該表中保存了主鍵與索引字段，并指向?qū)嶓w類的記錄，所以索引列也是要占用空間的。

2） 雖然索引大大提高了查詢效率，同時(shí)卻也降低更新表的速度，如對(duì)表進(jìn)行INSERT、UPDATE、DELETE。因?yàn)楦卤頃r(shí)，MySQL 不僅要保存數(shù)據(jù)，還要保存一下索引文件每次更新添加了索引列的字段，都會(huì)調(diào)整因?yàn)楦滤鶐?lái)的鍵值變化后的索引信息。

2.3 索引結(jié)構(gòu)

索引是在MySQL的存儲(chǔ)引擎層中實(shí)現(xiàn)的，而不是在服務(wù)器層實(shí)現(xiàn)的。所以每種存儲(chǔ)引擎的索引都不一定完全相同，也不是所有的存儲(chǔ)引擎都支持所有的索引類型的。MySQL目前提供了以下4種索引：

• BTREE 索引 ： 最常見(jiàn)的索引類型，大部分索引都支持 B 樹索引。
• HASH 索引：只有Memory引擎支持 ， 使用場(chǎng)景簡(jiǎn)單 。
• R-tree 索引（空間索引）：空間索引是MyISAM引擎的一個(gè)特殊索引類型，主要用于地理空間數(shù)據(jù)類型，通常使用較少，不做特別介紹。
• Full-text （全文索引） ：全文索引也是MyISAM的一個(gè)特殊索引類型，主要用于全文索引，InnoDB從Mysql5.6版本開始支持全文索引。

MyISAM、InnoDB、Memory三種存儲(chǔ)引擎對(duì)各種索引類型的支持

索引InnoDB引擎MyISAM引擎Memory引擎

BTREE索引	支持	支持	支持
HASH 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

我們平常所說(shuō)的索引，如果沒(méi)有特別指明，都是指B+樹（多路搜索樹，并不一定是二叉的）結(jié)構(gòu)組織的索引。其中聚集索引、復(fù)合索引、前綴索引、唯一索引默認(rèn)都是使用 B+tree 索引，統(tǒng)稱為 索引。

2.3.1 BTREE 結(jié)構(gòu)

BTree又叫多路平衡搜索樹，一顆m叉的BTree特性如下：

• 樹中每個(gè)節(jié)點(diǎn)最多包含m個(gè)孩子。
• 除根節(jié)點(diǎn)與葉子節(jié)點(diǎn)外，每個(gè)節(jié)點(diǎn)至少有[ceil(m/2)]個(gè)孩子。
• 若根節(jié)點(diǎn)不是葉子節(jié)點(diǎn)，則至少有兩個(gè)孩子。
• 所有的葉子節(jié)點(diǎn)都在同一層。
• 每個(gè)非葉子節(jié)點(diǎn)由n個(gè)key與n+1個(gè)指針組成，其中[ceil(m/2)-1] <= n <= m-1

以5叉BTree為例，key的數(shù)量：公式推導(dǎo)[ceil(m/2)-1] <= n <= m-1。所以 2 <= n <=4 。當(dāng)n>4時(shí)，中間節(jié)點(diǎn)分裂到父節(jié)點(diǎn)，兩邊節(jié)點(diǎn)分裂。

插入 C N G A H E K Q M F W L T Z D P R X Y S 數(shù)據(jù)為例。

演變過(guò)程如下：

1). 插入前4個(gè)字母 C N G A

2). 插入H，n>4，中間元素G字母向上分裂到新的節(jié)點(diǎn)

3). 插入E，K，Q不需要分裂

4). 插入M，中間元素M字母向上分裂到父節(jié)點(diǎn)G

5). 插入F，W，L，T不需要分裂

6). 插入Z，中間元素T向上分裂到父節(jié)點(diǎn)中

7). 插入D，中間元素D向上分裂到父節(jié)點(diǎn)中。然后插入P，R，X，Y不需要分裂

8). 最后插入S，NPQR節(jié)點(diǎn)n>5，中間節(jié)點(diǎn)Q向上分裂，但分裂后父節(jié)點(diǎn)DGMT的n>5，中間節(jié)點(diǎn)M向上分裂

到此，該BTREE樹就已經(jīng)構(gòu)建完成了， BTREE樹 和 二叉樹 相比， 查詢數(shù)據(jù)的效率更高， 因?yàn)閷?duì)于相同的數(shù)據(jù)量來(lái)說(shuō)，BTREE的層級(jí)結(jié)構(gòu)比二叉樹小，因此搜索速度快。

2.3.3 B+TREE 結(jié)構(gòu)

B+Tree為BTree的變種，B+Tree與BTree的區(qū)別為：

1). n叉B+Tree最多含有n個(gè)key，而BTree最多含有n-1個(gè)key。

2). B+Tree的葉子節(jié)點(diǎn)保存所有的key信息，依key大小順序排列。

3). 所有的非葉子節(jié)點(diǎn)都可以看作是key的索引部分。

由于B+Tree只有葉子節(jié)點(diǎn)保存key信息，查詢?nèi)魏蝛ey都要從root走到葉子。所以B+Tree的查詢效率更加穩(wěn)定。

2.3.3 MySQL中的B+Tree

MySql索引數(shù)據(jù)結(jié)構(gòu)對(duì)經(jīng)典的B+Tree進(jìn)行了優(yōu)化。在原B+Tree的基礎(chǔ)上，增加一個(gè)指向相鄰葉子節(jié)點(diǎn)的鏈表指針，就形成了帶有順序指針的B+Tree，提高區(qū)間訪問(wèn)的性能。

MySQL中的 B+Tree 索引結(jié)構(gòu)示意圖:

2.4 索引分類

1） 單值索引 ：即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引

2） 唯一索引 ：索引列的值必須唯一，但允許有空值

3） 復(fù)合索引 ：即一個(gè)索引包含多個(gè)列

2.5 索引語(yǔ)法

索引在創(chuàng)建表的時(shí)候，可以同時(shí)創(chuàng)建， 也可以隨時(shí)增加新的索引。

準(zhǔn)備環(huán)境:

create database demo_01 default charset=utf8mb4;use demo_01;CREATE TABLE `city` (`city_id` int(11) NOT NULL AUTO_INCREMENT,`city_name` varchar(50) NOT NULL,`country_id` int(11) NOT NULL,PRIMARY KEY (`city_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `country` (`country_id` int(11) NOT NULL AUTO_INCREMENT,`country_name` varchar(100) NOT NULL,PRIMARY KEY (`country_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into `city` (`city_id`, `city_name`, `country_id`) values(1,'西安',1); insert into `city` (`city_id`, `city_name`, `country_id`) values(2,'NewYork',2); insert into `city` (`city_id`, `city_name`, `country_id`) values(3,'北京',1); insert into `city` (`city_id`, `city_name`, `country_id`) values(4,'上海',1);insert into `country` (`country_id`, `country_name`) values(1,'China'); insert into `country` (`country_id`, `country_name`) values(2,'America'); insert into `country` (`country_id`, `country_name`) values(3,'Japan'); insert into `country` (`country_id`, `country_name`) values(4,'UK');

2.5.1 創(chuàng)建索引

語(yǔ)法 ：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON tbl_name(index_col_name,...)index_col_name : column_name[(length)][ASC | DESC]

示例 ： 為city表中的city_name字段創(chuàng)建索引 ；

?

?

2.5.2 查看索引

語(yǔ)法：

`show index from table_name;`

示例：查看city表中的索引信息；

2.5.3 刪除索引

語(yǔ)法 ：

SQLDROP INDEX index_name ON tbl_name;

示例 ： 想要?jiǎng)h除city表上的索引idx_city_name，可以操作如下：

2.5.4 ALTER命令

SQL1). alter table tb_name add primary key(column_list); 該語(yǔ)句添加一個(gè)主鍵，這意味著索引值必須是唯一的，且不能為NULL2). alter table tb_name add unique index_name(column_list);這條語(yǔ)句創(chuàng)建索引的值必須是唯一的（除了NULL外，NULL可能會(huì)出現(xiàn)多次）3). alter table tb_name add index index_name(column_list);添加普通索引， 索引值可以出現(xiàn)多次。4). alter table tb_name add fulltext index_name(column_list);該語(yǔ)句指定了索引為FULLTEXT， 用于全文索引

2.6 索引設(shè)計(jì)原則

? 索引的設(shè)計(jì)可以遵循一些已有的原則，創(chuàng)建索引的時(shí)候請(qǐng)盡量考慮符合這些原則，便于提升索引的使用效率，更高效的使用索引。

• 對(duì)查詢頻次較高，且數(shù)據(jù)量比較大的表建立索引。

• 索引字段的選擇，最佳候選列應(yīng)當(dāng)從where子句的條件中提取，如果where子句中的組合比較多，那么應(yīng)當(dāng)挑選最常用、過(guò)濾效果最好的列的組合。

• 使用唯一索引，區(qū)分度越高，使用索引的效率越高。

• 索引可以有效的提升查詢數(shù)據(jù)的效率，但索引數(shù)量不是多多益善，索引越多，維護(hù)索引的代價(jià)自然也就水漲船高。對(duì)于插入、更新、刪除等DML操作比較頻繁的表來(lái)說(shuō)，索引過(guò)多，會(huì)引入相當(dāng)高的維護(hù)代價(jià)，降低DML操作的效率，增加相應(yīng)操作的時(shí)間消耗。另外索引過(guò)多的話，MySQL也會(huì)犯選擇困難病，雖然最終仍然會(huì)找到一個(gè)可用的索引，但無(wú)疑提高了選擇的代價(jià)。

• 使用短索引，索引創(chuàng)建之后也是使用硬盤來(lái)存儲(chǔ)的，因此提升索引訪問(wèn)的I/O效率，也可以提升總體的訪問(wèn)效率。假如構(gòu)成索引的字段總長(zhǎng)度比較短，那么在給定大小的存儲(chǔ)塊內(nèi)可以存儲(chǔ)更多的索引值，相應(yīng)的可以有效的提升MySQL訪問(wèn)索引的I/O效率。

• 利用最左前綴，N個(gè)列組合而成的組合索引，那么相當(dāng)于是創(chuàng)建了N個(gè)索引，如果查詢時(shí)where子句中使用了組成該索引的前幾個(gè)字段，那么這條查詢SQL可以利用組合索引來(lái)提升查詢效率。

創(chuàng)建復(fù)合索引:CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);就相當(dāng)于對(duì)name 創(chuàng)建索引 ;對(duì)name , email 創(chuàng)建了索引 ;對(duì)name , email, status 創(chuàng)建了索引 ;

3. 視圖

3.1 視圖概述

? 視圖（View）是一種虛擬存在的表。視圖并不在數(shù)據(jù)庫(kù)中實(shí)際存在，行和列數(shù)據(jù)來(lái)自定義視圖的查詢中使用的表，并且是在使用視圖時(shí)動(dòng)態(tài)生成的。通俗的講，視圖就是一條SELECT語(yǔ)句執(zhí)行后返回的結(jié)果集。所以我們?cè)趧?chuàng)建視圖的時(shí)候，主要的工作就落在創(chuàng)建這條SQL查詢語(yǔ)句上。

視圖相對(duì)于普通的表的優(yōu)勢(shì)主要包括以下幾項(xiàng)。

• 簡(jiǎn)單：使用視圖的用戶完全不需要關(guān)心后面對(duì)應(yīng)的表的結(jié)構(gòu)、關(guān)聯(lián)條件和篩選條件，對(duì)用戶來(lái)說(shuō)已經(jīng)是過(guò)濾好的復(fù)合條件的結(jié)果集。
• 安全：使用視圖的用戶只能訪問(wèn)他們被允許查詢的結(jié)果集，對(duì)表的權(quán)限管理并不能限制到某個(gè)行某個(gè)列，但是通過(guò)視圖就可以簡(jiǎn)單的實(shí)現(xiàn)。
• 數(shù)據(jù)獨(dú)立：一旦視圖的結(jié)構(gòu)確定了，可以屏蔽表結(jié)構(gòu)變化對(duì)用戶的影響，源表增加列對(duì)視圖沒(méi)有影響；源表修改列名，則可以通過(guò)修改視圖來(lái)解決，不會(huì)造成對(duì)訪問(wèn)者的影響。

3.2 創(chuàng)建或者修改視圖

創(chuàng)建視圖的語(yǔ)法為：

SQLCREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]VIEW view_name [(column_list)]AS select_statement[WITH [CASCADED | LOCAL] CHECK OPTION]

修改視圖的語(yǔ)法為：

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]VIEW view_name [(column_list)]AS select_statement[WITH [CASCADED | LOCAL] CHECK OPTION]

SQL

選項(xiàng) :WITH [CASCADED | LOCAL] CHECK OPTION `決定了是否允許更新數(shù)據(jù)使記錄不再滿足視圖的條件。

LOCAL ： 只要滿足本視圖的條件就可以更新。 CASCADED ： 必須滿足所有針對(duì)該視圖的所有視圖的條件才可以更新。 默認(rèn)值.

示例 , 創(chuàng)建city_country_view視圖 , 執(zhí)行如下SQL :

`create or replace view city_country_view as select t.*,c.country_name from country c , city t where c.country_id = t.country_id;

查詢視圖 :

3.3 查看視圖

? 從 MySQL 5.1 版本開始，使用 SHOW TABLES 命令的時(shí)候不僅顯示表的名字，同時(shí)也會(huì)顯示視圖的名字，而不存在單獨(dú)顯示視圖的 SHOW VIEWS 命令。

同樣，在使用 SHOW TABLE STATUS 命令的時(shí)候，不但可以顯示表的信息，同時(shí)也可以顯示視圖的信息。

如果需要查詢某個(gè)視圖的定義，可以使用 SHOW CREATE VIEW 命令進(jìn)行查看 ：

3.4 刪除視圖

語(yǔ)法 :

SQL`DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]

示例 , 刪除視圖city_country_view :

SQL`DROP VIEW city_country_view ;

4. 存儲(chǔ)過(guò)程和函數(shù)

4.1 存儲(chǔ)過(guò)程和函數(shù)概述

? 存儲(chǔ)過(guò)程和函數(shù)是 事先經(jīng)過(guò)編譯并存儲(chǔ)在數(shù)據(jù)庫(kù)中的一段 SQL 語(yǔ)句的集合，調(diào)用存儲(chǔ)過(guò)程和函數(shù)可以簡(jiǎn)化應(yīng)用開發(fā)人員的很多工作，減少數(shù)據(jù)在數(shù)據(jù)庫(kù)和應(yīng)用服務(wù)器之間的傳輸，對(duì)于提高數(shù)據(jù)處理的效率是有好處的。

? 存儲(chǔ)過(guò)程和函數(shù)的區(qū)別在于函數(shù)必須有返回值，而存儲(chǔ)過(guò)程沒(méi)有。

? 函數(shù) ： 是一個(gè)有返回值的過(guò)程 ；

? 過(guò)程 ： 是一個(gè)沒(méi)有返回值的函數(shù) ；

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

`CREATE PROCEDURE procedure_name ([proc_parameter[,...]]) begin-- SQL語(yǔ)句 end ;

示例 ：

delimiter $create procedure pro_test1() beginselect 'Hello Mysql' ; end$delimiter ;

知識(shí)小貼士

DELIMITER

? 該關(guān)鍵字用來(lái)聲明SQL語(yǔ)句的分隔符 , 告訴 MySQL 解釋器，該段命令是否已經(jīng)結(jié)束了，mysql是否可以執(zhí)行了。默認(rèn)情況下，delimiter是分號(hào);。在命令行客戶端中，如果有一行命令以分號(hào)結(jié)束，那么回車后，mysql將會(huì)執(zhí)行該命令。

4.3 調(diào)用存儲(chǔ)過(guò)程

call procedure_name() ;

4.4 查看存儲(chǔ)過(guò)程

-- 查詢db_name數(shù)據(jù)庫(kù)中的所有的存儲(chǔ)過(guò)程 select name from mysql.proc where db='db_name';-- 查詢存儲(chǔ)過(guò)程的狀態(tài)信息 show procedure status;-- 查詢某個(gè)存儲(chǔ)過(guò)程的定義 show create procedure test.pro_test1 \G;

4.5 刪除存儲(chǔ)過(guò)程

`DROP PROCEDURE [IF EXISTS] sp_name ；

4.6 語(yǔ)法

存儲(chǔ)過(guò)程是可以編程的，意味著可以使用變量，表達(dá)式，控制結(jié)構(gòu) ， 來(lái)完成比較復(fù)雜的功能。

4.6.1 變量

• DECLARE

  通過(guò) DECLARE 可以定義一個(gè)局部變量，該變量的作用范圍只能在 BEGIN…END 塊中。

SQL`DECLARE var_name[,...] type [DEFAULT value]`

示例 :

delimiter $create procedure pro_test2() begin declare num int default 5;select num+ 10; end$delimiter ;

• SET

直接賦值使用 SET，可以賦常量或者賦表達(dá)式，具體語(yǔ)法如下：

NGINXSET var_name = expr [, var_name = expr] ...`

示例 :

DELIMITER $CREATE PROCEDURE pro_test3()BEGINDECLARE NAME VARCHAR(20);SET NAME = 'MYSQL';SELECT NAME ;END$DELIMITER ;

也可以通過(guò)select … into 方式進(jìn)行賦值操作 :

DELIMITER $CREATE PROCEDURE pro_test5() BEGINdeclare countnum int;select count(*) into countnum from city;select countnum; END$DELIMITER ;

4.6.2 if條件判斷

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

if search_condition then statement_list[elseif search_condition then statement_list] ...[else statement_list]end if;

需求：

YAML根據(jù)定義的身高變量，判定當(dāng)前身高的所屬的身材類型 180 及以上 ----------> 身材高挑170 - 180 ---------> 標(biāo)準(zhǔn)身材170 以下 ----------> 一般身材

示例 :

delimiter $create procedure pro_test6() begindeclare height int default 175; declare description varchar(50);if height >= 180 thenset description = '身材高挑';elseif height >= 170 and height < 180 thenset description = '標(biāo)準(zhǔn)身材';elseset description = '一般身材';end if;select description ; end$delimiter ;

調(diào)用結(jié)果為 :

4.6.3 傳遞參數(shù)

語(yǔ)法格式 :

DELPHIcreate procedure procedure_name([in/out/inout] 參數(shù)名 參數(shù)類型) ...IN : 該參數(shù)可以作為輸入，也就是需要調(diào)用方傳入值 , 默認(rèn) OUT: 該參數(shù)作為輸出，也就是該參數(shù)可以作為返回值 INOUT: 既可以作為輸入?yún)?shù)，也可以作為輸出參數(shù)

IN - 輸入

需求 :

`根據(jù)定義的身高變量，判定當(dāng)前身高的所屬的身材類型 示例 :```cdelimiter $create procedure pro_test5(in height int) begindeclare description varchar(50) default '';if height >= 180 thenset description='身材高挑';elseif height >= 170 and height < 180 thenset description='標(biāo)準(zhǔn)身材';elseset description='一般身材';end if;select concat('身高 ', height , '對(duì)應(yīng)的身材類型為:',description); end$delimiter ;

OUT-輸出

需求 :

根據(jù)傳入的身高變量，獲取當(dāng)前身高的所屬的身材類型`

示例:

create procedure pro_test5(in height int , out description varchar(100)) beginif height >= 180 thenset description='身材高挑';elseif height >= 170 and height < 180 thenset description='標(biāo)準(zhǔn)身材';elseset description='一般身材';end if; end

調(diào)用:

NGINX`call pro_test5(168, @description)$select @description

小知識(shí)

@description : 這種變量要在變量名稱前面加上“@”符號(hào)，叫做用戶會(huì)話變量，代表整個(gè)會(huì)話過(guò)程他都是有作用的，這個(gè)類似于全局變量一樣。

@@global.sort_buffer_size : 這種在變量前加上 “@@” 符號(hào), 叫做 系統(tǒng)變量

4.6.4 case結(jié)構(gòu)

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

`方式一 : CASE case_valueWHEN when_value THEN statement_list[WHEN when_value THEN statement_list] ...[ELSE statement_list]END CASE;方式二 : CASEWHEN search_condition THEN statement_list[WHEN search_condition THEN statement_list] ...[ELSE statement_list]END CASE;`

需求:

* 1`給定一個(gè)月份, 然后計(jì)算出所在的季度`

示例 :

`delimiter $create procedure pro_test9(month int) begindeclare result varchar(20);case when month >= 1 and month <=3 then set result = '第一季度';when month >= 4 and month <=6 then set result = '第二季度';when month >= 7 and month <=9 then set result = '第三季度';when month >= 10 and month <=12 then set result = '第四季度';end case;select concat('您輸入的月份為 :', month , ' , 該月份為 : ' , result) as content ;end$delimiter ;`

4.6.5 while循環(huán)

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

while search_condition dostatement_listend while;

需求:

`計(jì)算從1加到n的值`

示例 :

delimiter $create procedure pro_test8(n int) begindeclare total int default 0;declare num int default 1;while num<=n doset total = total + num;set num = num + 1;end while;select total; end$delimiter ;

4.6.6 repeat結(jié)構(gòu)

有條件的循環(huán)控制語(yǔ)句, 當(dāng)滿足條件的時(shí)候退出循環(huán) 。while 是滿足條件才執(zhí)行，repeat 是滿足條件就退出循環(huán)。

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

`REPEATstatement_listUNTIL search_conditionEND REPEAT;

需求:

`計(jì)算從1加到n的值

示例 :

delimiter $create procedure pro_test10(n int) begindeclare total int default 0;repeat set total = total + n;set n = n - 1;until n=0 end repeat;select total ;end$delimiter ;

4.6.7 loop語(yǔ)句

LOOP 實(shí)現(xiàn)簡(jiǎn)單的循環(huán)，退出循環(huán)的條件需要使用其他的語(yǔ)句定義，通常可以使用 LEAVE 語(yǔ)句實(shí)現(xiàn)，具體語(yǔ)法如下：

`[begin_label:] LOOPstatement_listEND LOOP [end_label]`

如果不在 statement_list 中增加退出循環(huán)的語(yǔ)句，那么 LOOP 語(yǔ)句可以用來(lái)實(shí)現(xiàn)簡(jiǎn)單的死循環(huán)。

4.6.8 leave語(yǔ)句

用來(lái)從標(biāo)注的流程構(gòu)造中退出，通常和 BEGIN … END 或者循環(huán)一起使用。下面是一個(gè)使用 LOOP 和 LEAVE 的簡(jiǎn)單例子 , 退出循環(huán)：

delimiter $CREATE PROCEDURE pro_test11(n int) BEGINdeclare total int default 0;ins: LOOPIF n <= 0 thenleave ins;END IF;set total = total + n;set n = n - 1;END LOOP ins;select total; END$delimiter ;

4.6.9 游標(biāo)/光標(biāo)

游標(biāo)是用來(lái)存儲(chǔ)查詢結(jié)果集的數(shù)據(jù)類型 , 在存儲(chǔ)過(guò)程和函數(shù)中可以使用光標(biāo)對(duì)結(jié)果集進(jìn)行循環(huán)的處理。光標(biāo)的使用包括光標(biāo)的聲明、OPEN、FETCH 和 CLOSE，其語(yǔ)法分別如下。

聲明光標(biāo)：

SQL`DECLARE cursor_name CURSOR FOR select_statement

OPEN 光標(biāo)：

OPEN cursor_name ;

FETCH 光標(biāo)：

FETCH cursor_name INTO var_name [, var_name] ...

CLOSE 光標(biāo)：

CLOSE cursor_name ;

示例 :

初始化腳本:

create table emp(id int(11) not null auto_increment ,name varchar(50) not null comment '姓名',age int(11) comment '年齡',salary int(11) comment '薪水',primary key(`id`) )engine=innodb default charset=utf8 ;insert into emp(id,name,age,salary) values(null,'金毛獅王',55,3800),(null,'白眉鷹王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龍王',42,1800); -- 查詢emp表中數(shù)據(jù), 并逐行獲取進(jìn)行展示 create procedure pro_test11() begindeclare e_id int(11);declare e_name varchar(50);declare e_age int(11);declare e_salary int(11);declare emp_result cursor for select * from emp;open emp_result;fetch emp_result into e_id,e_name,e_age,e_salary;select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為: ',e_salary);fetch emp_result into e_id,e_name,e_age,e_salary;select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為: ',e_salary);fetch emp_result into e_id,e_name,e_age,e_salary;select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為: ',e_salary);fetch emp_result into e_id,e_name,e_age,e_salary;select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為: ',e_salary);fetch emp_result into e_id,e_name,e_age,e_salary;select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪資為: ',e_salary);close emp_result; end

通過(guò)循環(huán)結(jié)構(gòu) , 獲取游標(biāo)中的數(shù)據(jù) :

`DELIMITER $create procedure pro_test12() beginDECLARE id int(11);DECLARE name varchar(50);DECLARE age int(11);DECLARE salary int(11);DECLARE has_data int default 1;DECLARE emp_result CURSOR FOR select * from emp;DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;open emp_result;repeatfetch emp_result into id , name , age , salary;select concat('id為',id, ', name 為' ,name , ', age為 ' ,age , ', 薪水為: ', salary);until has_data = 0end repeat;close emp_result; end$DELIMITER ;

4.7 存儲(chǔ)函數(shù)

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

CREATE FUNCTION function_name([param type ... ]) RETURNS type BEGIN... END;

案例 :

定義一個(gè)存儲(chǔ)過(guò)程, 請(qǐng)求滿足條件的總記錄數(shù) ;

delimiter $create function count_city(countryId int) returns int begindeclare cnum int ;select count(*) into cnum from city where country_id = countryId;return cnum; end$delimiter ;

調(diào)用:

CSHARP`select count_city(1);select count_city(2);

5. 觸發(fā)器

5.1 介紹

觸發(fā)器是與表有關(guān)的數(shù)據(jù)庫(kù)對(duì)象，指在 insert/update/delete 之前或之后，觸發(fā)并執(zhí)行觸發(fā)器中定義的SQL語(yǔ)句集合。觸發(fā)器的這種特性可以協(xié)助應(yīng)用在數(shù)據(jù)庫(kù)端確保數(shù)據(jù)的完整性 , 日志記錄 , 數(shù)據(jù)校驗(yàn)等操作 。

使用別名 OLD 和 NEW 來(lái)引用觸發(fā)器中發(fā)生變化的記錄內(nèi)容，這與其他的數(shù)據(jù)庫(kù)是相似的。現(xiàn)在觸發(fā)器還只支持行級(jí)觸發(fā)，不支持語(yǔ)句級(jí)觸發(fā)。

觸發(fā)器類型NEW 和 OLD的使用

INSERT 型觸發(fā)器	NEW 表示將要或者已經(jīng)新增的數(shù)據(jù)
UPDATE 型觸發(fā)器	OLD 表示修改之前的數(shù)據(jù) , NEW 表示將要或已經(jīng)修改后的數(shù)據(jù)
DELETE 型觸發(fā)器	OLD 表示將要或者已經(jīng)刪除的數(shù)據(jù)

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

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

create trigger trigger_name before/after insert/update/deleteon tbl_name [ for each row ] -- 行級(jí)觸發(fā)器begintrigger_stmt ;end;

示例

需求

通過(guò)觸發(fā)器記錄 emp 表的數(shù)據(jù)變更日志 , 包含增加, 修改 , 刪除 ;

首先創(chuàng)建一張日志表 :

create table emp_logs(id int(11) not null auto_increment,operation varchar(20) not null comment '操作類型, insert/update/delete',operate_time datetime not null comment '操作時(shí)間',operate_id int(11) not null comment '操作表的ID',operate_params varchar(500) comment '操作參數(shù)',primary key(`id`) )engine=innodb default charset=utf8;

創(chuàng)建 insert 型觸發(fā)器，完成插入數(shù)據(jù)時(shí)的日志記錄 :

`DELIMITER $create trigger emp_logs_insert_trigger after insert on emp for each row begininsert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'insert',now(),new.id,concat('插入后(id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')')); end $DELIMITER ;

創(chuàng)建 update 型觸發(fā)器，完成更新數(shù)據(jù)時(shí)的日志記錄 :

`DELIMITER $create trigger emp_logs_update_trigger after update on emp for each row begininsert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'update',now(),new.id,concat('修改前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,') , 修改后(id',new.id, 'name:',new.name,', age:',new.age,', salary:',new.salary,')')); end $DELIMITER

創(chuàng)建delete 行的觸發(fā)器 , 完成刪除數(shù)據(jù)時(shí)的日志記錄 :

DELIMITER $create trigger emp_logs_delete_trigger after delete on emp for each row begininsert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'delete',now(),old.id,concat('刪除前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,')')); end $DELIMITER ;

測(cè)試：

`insert into emp(id,name,age,salary) values(null, '光明左使',30,3500); insert into emp(id,name,age,salary) values(null, '光明右使',33,3200);update emp set age = 39 where id = 3;delete from emp where id = 5;

5.3 刪除觸發(fā)器

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

drop trigger [schema_name.]trigger_name

如果沒(méi)有指定 schema_name，默認(rèn)為當(dāng)前數(shù)據(jù)庫(kù) 。

5.4 查看觸發(fā)器

可以通過(guò)執(zhí)行 SHOW TRIGGERS 命令查看觸發(fā)器的狀態(tài)、語(yǔ)法等信息。

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

SQL`show triggers ；

最后，祝大家早日學(xué)有所成，拿到滿意offer

總結(jié)

以上是生活随笔為你收集整理的超详细图解！【MySQL进阶篇】存储过程,视图,索引,函数,触发器的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。