@TOC

1.1 下載Linux 安裝包

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

image-20210630151551039

1.2 安裝MySQL

APACHE

1). 卸載 centos 中預(yù)安裝的 mysql

    rpm -qa | grep -i mysql

    rpm -e mysql-libs-5.1.71-1.el6.x86_64 --nodeps

2). 上傳 mysql 的安裝包

    alt + p -------> put  E:/test/MySQL-5.6.22-1.el6.i686.rpm-bundle.tar

3). 解壓 mysql 的安裝包 

    mkdir mysql

    tar -xvf MySQL-5.6.22-1.el6.i686.rpm-bundle.tar -C /root/mysql

4). 安裝依賴包 

    yum -y install libaio.so.1 libgcc_s.so.1 libstdc++.so.6 libncurses.so.5 --setopt=protected_multilib=false

    yum  update libstdc++-4.4.7-4.el6.x86_64

5). 安裝 mysql-client

    rpm -ivh MySQL-client-5.6.22-1.el6.i686.rpm

6). 安裝 mysql-server

    rpm -ivh MySQL-server-5.6.22-1.el6.i686.rpm

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

SQL

service mysql start

service mysql stop

service mysql status

service mysql restart

1.4 登錄MySQL

mysql 安裝完成之后, 會(huì)自動(dòng)生成一個(gè)隨機(jī)的密碼, 并且保存在一個(gè)密碼文件中 : /root/.mysql_secret

mysql -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)就是索引极谊。如下面的示意圖所示 :【白嫖資料】

1555902055367

左邊是數(shù)據(jù)表，一共有兩列七條記錄安岂，最左邊的是數(shù)據(jù)記錄的物理地址（注意邏輯上相鄰的記錄在磁盤上也并不是一定物理相鄰的）轻猖。為了加快Col2的查找，可以維護(hù)一個(gè)右邊所示的二叉查找樹(shù)域那，每個(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ū)籍的目錄索引檐束，提高數(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 樹(shù)索引馋嗜。
• HASH 索引：只有Memory引擎支持 齐板， 使用場(chǎng)景簡(jiǎn)單 。
• R-tree 索引（空間索引）：空間索引是MyISAM引擎的一個(gè)特殊索引類型嵌戈，主要用于地理空間數(shù)據(jù)類型覆积，通常使用較少听皿，不做特別介紹熟呛。
• Full-text （全文索引） ：全文索引也是MyISAM的一個(gè)特殊索引類型，主要用于全文索引尉姨，InnoDB從Mysql5.6版本開(kāi)始支持全文索引庵朝。

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

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

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

BTree又叫多路平衡搜索樹(shù)汰蜘，一顆m叉的BTree特性如下：

• 樹(shù)中每個(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

1555944126588

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

1555944549825

3). 插入E，K辜限，Q不需要分裂【白嫖資料】

[圖片上傳失敗...(image-184321-1625730257089)]

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

[圖片上傳失敗...(image-371b38-1625730257089)]

5). 插入F，W薄嫡，L氧急，T不需要分裂

[圖片上傳失敗...(image-72d3f2-1625730257089)]

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

[圖片上傳失敗...(image-8da2-1625730257089)]

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

[圖片上傳失敗...(image-821e58-1625730257089)]

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

[圖片上傳失敗...(image-595244-1625730257089)]

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

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

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

1). n叉B+Tree最多含有n個(gè)key汹押，而B(niǎo)Tree最多含有n-1個(gè)key。

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

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

[圖片上傳失敗...(image-ee5e29-1625730257089)]

由于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)示意圖:

1555906287178

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)建索引 砸彬；

[圖片上傳失敗...(image-80a471-1625730257089)]

2.5.2 查看索引

語(yǔ)法：

`show index  from  table_name;`

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

[圖片上傳失敗...(image-4bfd65-1625730257089)]

[圖片上傳失敗...(image-d8c9c0-1625730257089)]

2.5.3 刪除索引

語(yǔ)法 ：

SQL

DROP  INDEX  index_name  ON  tbl_name;

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

[圖片上傳失敗...(image-1d05a1-1625730257089)]

2.5.4 ALTER命令

SQL

1). alter  table  tb_name  add  primary  key(column_list); 

    該語(yǔ)句添加一個(gè)主鍵砂碉，這意味著索引值必須是唯一的，且不能為NULL

2). 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ǔ)法為：

SQL

CREATE [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;

查詢視圖 :

[圖片上傳失敗...(image-a1674-1625730257089)]

3.3 查看視圖

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

image

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

[圖片上傳失敗...(image-441e08-1625730257089)]

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

image

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)用開(kāi)發(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()
begin
    select '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ǔ)法如下：

NGINX

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

示例 :

 DELIMITER $

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

  DELIMITER ;

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

DELIMITER $

CREATE  PROCEDURE pro_test5()
BEGIN
    declare  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()
begin
  declare  height  int  default  175; 
  declare  description  varchar(50);

  if  height >= 180  then
    set description = '身材高挑';
  elseif height >= 170 and height < 180  then
    set description = '標(biāo)準(zhǔn)身材';
  else
    set description = '一般身材';
  end if;

  select description ;
end$

delimiter ;

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

1552057035580

4.6.3 傳遞參數(shù)

語(yǔ)法格式 :

DELPHI

create 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)前身高的所屬的身材類型
示例 :

```c

delimiter $

create procedure pro_test5(in height int)
begin
    declare description varchar(50) default '';
  if height >= 180 then
    set description='身材高挑';
  elseif height >= 170 and height < 180 then
    set description='標(biāo)準(zhǔn)身材';
  else
    set 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))
begin
  if height >= 180 then
    set description='身材高挑';
  elseif height >= 170 and height < 180 then
    set description='標(biāo)準(zhǔn)身材';
  else
    set 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_value

  WHEN when_value THEN statement_list

  [WHEN when_value THEN statement_list] ...

  [ELSE statement_list]

END CASE;

方式二 : 

CASE

  WHEN 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)
begin
  declare 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 do

    statement_list

end while;

需求:

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

示例 :

delimiter $

create procedure pro_test8(n int)
begin
  declare total int default 0;
  declare num int default 1;
  while num<=n do
    set 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) :

`REPEAT

  statement_list

  UNTIL search_condition

END REPEAT;

需求:

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

示例 :

delimiter $

create procedure pro_test10(n int)
begin
  declare 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:] LOOP

  statement_list

END 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)
BEGIN
  declare total int default 0;

  ins: LOOP

    IF n <= 0 then
      leave 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()
begin
  declare 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()
begin
  DECLARE 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;

  repeat
    fetch emp_result into id , name , age , salary;
    select concat('id為',id, ', name 為' ,name , ', age為 ' ,age , ', 薪水為: ', salary);
    until has_data = 0
  end 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
begin
  declare 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ā)措译。

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

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

create trigger trigger_name 

before/after insert/update/delete

on tbl_name 

[ for each row ]  -- 行級(jí)觸發(fā)器

begin

    trigger_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 
begin
  insert 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 
begin
  insert 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 
begin
  insert 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，快速升職加薪是晨，走上人生巔峰肚菠。

可以的話請(qǐng)給我一個(gè)三連支持一下我喲??????【白嫖資料】

[圖片上傳失敗...(image-ce1396-1625730257089)]