1.1 下載Linux 安裝包

RUBY

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 啟動 MySQL 服務(wù)

SQL

service mysql start

service mysql stop

service mysql status

service mysql restart

1.4 登錄MySQL

mysql 安裝完成之后, 會自動生成一個隨機的密碼, 并且保存在一個密碼文件中 : /root/.mysql_secret

mysql -u root -p 

登錄之后, 修改密碼 :

set password = password('itcast');

授權(quán)遠程訪問 : 

grant all privileges on *.* to 'root' @'%' identified by 'itcast';
flush privileges;

2. 索引

2.1 索引概述

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

1555902055367

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

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

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

優(yōu)勢

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

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

劣勢

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

2） 雖然索引大大提高了查詢效率，同時卻也降低更新表的速度，如對表進行INSERT、UPDATE、DELETE。因為更新表時，MySQL 不僅要保存數(shù)據(jù)，還要保存一下索引文件每次更新添加了索引列的字段，都會調(diào)整因為更新所帶來的鍵值變化后的索引信息。

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

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

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

MyISAM、InnoDB、Memory三種存儲引擎對各種索引類型的支持

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

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

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

• 樹中每個節(jié)點最多包含m個孩子。
• 除根節(jié)點與葉子節(jié)點外，每個節(jié)點至少有[ceil(m/2)]個孩子。
• 若根節(jié)點不是葉子節(jié)點，則至少有兩個孩子。
• 所有的葉子節(jié)點都在同一層。
• 每個非葉子節(jié)點由n個key與n+1個指針組成，其中[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時，中間節(jié)點分裂到父節(jié)點，兩邊節(jié)點分裂。

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

演變過程如下：

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

1555944126588

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

1555944549825

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

1555944596893

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

1555944652560

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

image.png

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

image.png

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

image.png

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

image.png

到此，該BTREE樹就已經(jīng)構(gòu)建完成了， BTREE樹 和 二叉樹 相比， 查詢數(shù)據(jù)的效率更高， 因為對于相同的數(shù)據(jù)量來說，BTREE的層級結(jié)構(gòu)比二叉樹小，因此搜索速度快。

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

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

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

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

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

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

2.3.3 MySQL中的B+Tree

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

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

1555906287178

2.4 索引分類

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

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

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

2.5 索引語法

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

準(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)建索引

語法 ：

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 查看索引

語法：

`show index  from  table_name;`

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

image.png

image.png

2.5.3 刪除索引

語法 ：

SQL

DROP  INDEX  index_name  ON  tbl_name;

示例 ： 想要刪除city表上的索引idx_city_name，可以操作如下：

1551438238293

2.5.4 ALTER命令

SQL

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

    該語句添加一個主鍵，這意味著索引值必須是唯一的，且不能為NULL

2). alter  table  tb_name  add  unique index_name(column_list);

    這條語句創(chuàng)建索引的值必須是唯一的（除了NULL外，NULL可能會出現(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);

    該語句指定了索引為FULLTEXT， 用于全文索引

2.6 索引設(shè)計原則

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

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

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

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

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

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

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

 創(chuàng)建復(fù)合索引:

     CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);

 就相當(dāng)于
     對name 創(chuàng)建索引 ;
     對name , email 創(chuàng)建了索引 ;
     對name , email, status 創(chuàng)建了索引 ;

3. 視圖

3.1 視圖概述

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

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

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

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

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

SQL

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

修改視圖的語法為：

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

SQL

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

LOCAL ： 只要滿足本視圖的條件就可以更新。
CASCADED ： 必須滿足所有針對該視圖的所有視圖的條件才可以更新。 默認(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;

查詢視圖 :

1551503428635

3.3 查看視圖

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

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

如果需要查詢某個視圖的定義，可以使用 SHOW CREATE VIEW 命令進行查看 ：

3.4 刪除視圖

語法 :

SQL

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

示例 , 刪除視圖city_country_view :

SQL

`DROP VIEW city_country_view ;

4. 存儲過程和函數(shù)

4.1 存儲過程和函數(shù)概述

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

存儲過程和函數(shù)的區(qū)別在于函數(shù)必須有返回值，而存儲過程沒有。

函數(shù) ： 是一個有返回值的過程 ；

過程 ： 是一個沒有返回值的函數(shù) ；

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

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

示例 ：

delimiter $

create procedure pro_test1()
begin
    select 'Hello Mysql' ;
end$

delimiter ;

知識小貼士

DELIMITER

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

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

call procedure_name() ;

4.4 查看存儲過程

-- 查詢db_name數(shù)據(jù)庫中的所有的存儲過程
select name from mysql.proc where db='db_name';

-- 查詢存儲過程的狀態(tài)信息
show procedure status;

-- 查詢某個存儲過程的定義
show create procedure test.pro_test1 \G;

4.5 刪除存儲過程

`DROP PROCEDURE  [IF EXISTS] sp_name ；

4.6 語法

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

4.6.1 變量

• DECLARE

  通過 DECLARE 可以定義一個局部變量，該變量的作用范圍只能在 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，可以賦常量或者賦表達式，具體語法如下：

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 ;

也可以通過select ... into 方式進行賦值操作 :

DELIMITER $

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

DELIMITER ;

4.6.2 if條件判斷

語法結(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ù)

語法格式 :

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 , '對應(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

小知識

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

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

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

語法結(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

`給定一個月份, 然后計算出所在的季度` 

示例 :

`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)

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

while search_condition do

    statement_list

end while;

需求:

`計算從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)控制語句, 當(dāng)滿足條件的時候退出循環(huán) 。while 是滿足條件才執(zhí)行，repeat 是滿足條件就退出循環(huán)。

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

`REPEAT

  statement_list

  UNTIL search_condition

END REPEAT;

需求:

`計算從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語句

LOOP 實現(xiàn)簡單的循環(huán)，退出循環(huán)的條件需要使用其他的語句定義，通?？梢允褂?LEAVE 語句實現(xiàn)，具體語法如下：

`[begin_label:] LOOP

  statement_list

END LOOP [end_label]`

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

4.6.8 leave語句

用來從標(biāo)注的流程構(gòu)造中退出，通常和 BEGIN ... END 或者循環(huán)一起使用。下面是一個使用 LOOP 和 LEAVE 的簡單例子 , 退出循環(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)是用來存儲查詢結(jié)果集的數(shù)據(jù)類型 , 在存儲過程和函數(shù)中可以使用光標(biāo)對結(jié)果集進行循環(huán)的處理。光標(biāo)的使用包括光標(biāo)的聲明、OPEN、FETCH 和 CLOSE，其語法分別如下。

聲明光標(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ù), 并逐行獲取進行展示
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

通過循環(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 存儲函數(shù)

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

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

案例 :

定義一個存儲過程, 請求滿足條件的總記錄數(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ù)庫對象，指在 insert/update/delete 之前或之后，觸發(fā)并執(zhí)行觸發(fā)器中定義的SQL語句集合。觸發(fā)器的這種特性可以協(xié)助應(yīng)用在數(shù)據(jù)庫端確保數(shù)據(jù)的完整性 , 日志記錄 , 數(shù)據(jù)校驗等操作 。

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

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

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

create trigger trigger_name 

before/after insert/update/delete

on tbl_name 

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

begin

    trigger_stmt ;

end;

示例

需求

通過觸發(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 '操作時間',
  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ù)時的日志記錄 :

`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ù)時的日志記錄 :

`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ù)時的日志記錄 :

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 ;

測試：

`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ā)器

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

drop trigger [schema_name.]trigger_name

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

5.4 查看觸發(fā)器

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

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

SQL

`show triggers ；

最后，祝大家早日學(xué)有所成，拿到滿意offer，快速升職加薪，走上人生巔峰。

可以的話請給我一個三連支持一下我喲??????【白嫖資料】

[圖片上傳失敗...(image-f24e4d-1625392196348)]