1. 視圖

視圖是一個虛擬表，其內(nèi)容由查詢定義。與實(shí)際的物理表類似，視圖也包含一系列具有名稱的列和行數(shù)據(jù)。視圖的數(shù)據(jù)變化會影響基表，反之，基表的數(shù)據(jù)變化也會影響視圖。

1.1 基本使用

創(chuàng)建視圖

創(chuàng)建視圖的基本語法如下：

CREATE VIEW 視圖名 AS SELECT 查詢語句;

示例：查看學(xué)生的學(xué)號、姓名、成績和課程號：

SELECT s1.sno, snme, sdept, grade, cno 
FROM student s1 
JOIN score s2 ON s1.sno = s2.sno;

創(chuàng)建視圖 v_s_s，該視圖包含學(xué)生學(xué)號、姓名、成績和課程號：

CREATE VIEW v_s_s AS 
SELECT s1.sno, snme, sdept, grade, cno 
FROM student s1 
JOIN score s2 ON s1.sno = s2.sno;

修改視圖中的數(shù)據(jù)：假設(shè)我們希望修改馬小燕課程號 001 的成績?yōu)?100，若視圖支持更新操作，可以修改原表數(shù)據(jù)：

UPDATE v_s_s 
SET grade = 100 
WHERE sno = '馬小燕' AND cno = '001';

刪除視圖

刪除視圖的語法如下：

DROP VIEW 視圖名;

1.2 視圖的規(guī)則與限制

視圖與基表之間存在緊密的關(guān)系，視圖數(shù)據(jù)的修改會影響基表的數(shù)據(jù)，反之亦然。為了確保系統(tǒng)的穩(wěn)定性，使用視圖時需要特別注意以下限制：

• 數(shù)據(jù)更新限制：并非所有視圖都支持?jǐn)?shù)據(jù)更新操作。特別是當(dāng)視圖涉及多個表的連接、聚合函數(shù)、分組（GROUP BY）等操作時，修改視圖中的數(shù)據(jù)可能會受到限制。例如，包含聚合函數(shù)或聯(lián)合查詢的視圖通常不支持更新。
• 性能考慮：雖然視圖可以簡化查詢，但如果查詢的視圖非常復(fù)雜且涉及大量數(shù)據(jù)，可能會導(dǎo)致性能問題。因此，在設(shè)計(jì)視圖時應(yīng)避免過于復(fù)雜的查詢，特別是涉及大量數(shù)據(jù)的視圖。
• 不支持索引：視圖本身不支持索引，因此在使用視圖時，查詢性能可能不如直接查詢基表。如果視圖查詢包含復(fù)雜的計(jì)算或連接操作，可能會對查詢性能產(chǎn)生影響。
• 只讀視圖：一些視圖被設(shè)計(jì)為只讀的，無法修改其中的數(shù)據(jù)。這通常發(fā)生在視圖涉及多表連接、聚合操作或復(fù)雜計(jì)算時。對于這種只讀視圖，修改視圖中的數(shù)據(jù)將會失敗。

1.3 視圖與查找數(shù)據(jù)創(chuàng)建表的比較

視圖和基于查詢結(jié)果創(chuàng)建的表在以下方面有所不同：

視圖：
視圖是動態(tài)的，它始終基于最新的查詢結(jié)果。當(dāng)視圖中的數(shù)據(jù)發(fā)生變化時，實(shí)際的數(shù)據(jù)表也會發(fā)生變化。視圖不存儲數(shù)據(jù)本身，而是存儲查詢邏輯。當(dāng)查詢視圖時，實(shí)際上是執(zhí)行視圖定義中的查詢語句。

語法示例：

CREATE VIEW t_name AS SELECT 查詢數(shù)據(jù);

創(chuàng)建表：

使用 CREATE TABLE 可以將查詢結(jié)果保存為一個物理表。與視圖不同，創(chuàng)建的表會將數(shù)據(jù)永久存儲在數(shù)據(jù)庫中，數(shù)據(jù)修改不會影響原始數(shù)據(jù)表。創(chuàng)建的表可以具有索引等性能優(yōu)化。

語法示例：

CREATE TABLE t_name AS SELECT 查詢數(shù)據(jù);

1.4 視圖添加限制

在 MySQL 中，視圖雖然提供了極大的便利，但在某些情況下需要對其進(jìn)行適當(dāng)?shù)南拗?，以確保數(shù)據(jù)的一致性和完整性。以下是常見的視圖限制及其應(yīng)用：

視圖的修改限制

• 當(dāng)視圖涉及多個表、聚合函數(shù)、分組等操作時，視圖通常為只讀，無法直接修改。只有在視圖基于單一表且沒有涉及復(fù)雜計(jì)算時，視圖才通常支持?jǐn)?shù)據(jù)更新操作。
• 若需要限制視圖中數(shù)據(jù)的修改，可以使用 WITH CHECK OPTION，該選項(xiàng)確保通過視圖進(jìn)行的更新操作符合視圖中的條件，否則修改會被拒絕。

例如，創(chuàng)建一個只允許修改 grade >= 60 的視圖：

CREATE VIEW v_students AS 
SELECT sno, snme, grade 
FROM student 
WHERE grade >= 60
WITH CHECK OPTION;

視圖查詢限制

視圖能夠簡化復(fù)雜的查詢，但也需要根據(jù)實(shí)際需求進(jìn)行適當(dāng)限制。為了確保不暴露敏感數(shù)據(jù)，可以設(shè)計(jì)只包含非敏感字段或經(jīng)過加密/脫敏處理的視圖。

權(quán)限控制與安全性

權(quán)限設(shè)置示例：

GRANT SELECT ON v_employee_view TO 'user1';

使用視圖時，應(yīng)當(dāng)考慮權(quán)限控制，通過為不同用戶分配不同的視圖訪問權(quán)限，可以確保數(shù)據(jù)安全。視圖可以提供一個中介層，使得用戶僅能訪問特定數(shù)據(jù)，而不暴露整個表的數(shù)據(jù)。

對于敏感數(shù)據(jù)，視圖的設(shè)計(jì)應(yīng)遵循最小權(quán)限原則，避免直接暴露敏感信息。

2. 存儲過程的基本語法

存儲過程是一組 SQL 語句的集合，它被存儲在數(shù)據(jù)庫中，并可根據(jù)需要執(zhí)行,可以接收輸入?yún)?shù)并返回結(jié)果。

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

存儲過程的創(chuàng)建需要修改語句分隔符，以避免與 SQL 語句的結(jié)束符（;）發(fā)生沖突：

DELIMITER $$  -- 修改分隔符以避免與語句結(jié)束符沖突
CREATE PROCEDURE procedure_name (parameters)
BEGIN
   -- SQL 語句
END$$
DELIMITER ;  -- 恢復(fù)分隔符

存儲過程的參數(shù)包括：

• IN：輸入?yún)?shù)，用于向存儲過程傳遞值。
• OUT：輸出參數(shù)，用于存儲過程返回?cái)?shù)據(jù)。
• INOUT：輸入輸出參數(shù)，既可以接收輸入數(shù)據(jù)，又可以返回結(jié)果。

不改變分隔符會出現(xiàn)報錯：

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

調(diào)用存儲過程的語法如下：

CALL procedure_name(parameters);

2.3 查看存儲過程信息

查看所有數(shù)據(jù)庫的存儲過程：

SHOW PROCEDURE STATUS;

查看當(dāng)前數(shù)據(jù)庫的存儲過程：

SHOW PROCEDURE STATUS WHERE db = 'db_name';

Db：存儲過程所在的數(shù)據(jù)庫

Name：存儲過程的名稱

Type：存儲過程類型（例如 PROCEDURE）

Definer：存儲過程的定義者

Modified：最后修改時間

Created：創(chuàng)建時間

Security_type：安全類型

Comment：存儲過程的注釋

2.4 查看存儲過程定義

查看存儲過程定義的語法：

SHOW CREATE PROCEDURE procedure_name;

2.5 刪除存儲過程

刪除存儲過程的語法如下：

DROP PROCEDURE procedure_name;

3. 變量

3.1 查看系統(tǒng)變量

 3.1.1查看所有系統(tǒng)變量

 查看當(dāng)前會話的系統(tǒng)變量：

SHOW SESSION VARIABLES;

 查看全局系統(tǒng)變量：

SHOW GLOBAL VARIABLES;

3.1.2系統(tǒng)變量的模糊匹配

show session variables like '...';
?show global variables like '...';

 3.1.3查看指定變量

select @@global.tname;----查看指定全局環(huán)境變量
select @@session.tname;----查看當(dāng)前會話環(huán)境變量

3.2 設(shè)置全局變量與會話變量

3.2.1全局變量設(shè)置

SET GLOBAL transaction_isolation_level = 'READ COMMITTED';

 重新啟動一個新的會話：

3.2.2當(dāng)前會話變量設(shè)置

set session transaction isolation level read committed;

重新啟動一個會話：

3.3 用戶定義變量

用戶定義變量是會話級別的臨時變量，用戶可以在 SQL 語句中使用它們來存儲數(shù)據(jù)或進(jìn)行計(jì)算。變量名以 @ 開頭。例如：

 使用 SET 語句定義變量：

SET @variable_name = value;-----方法一
SET @variable_name := value;----方法二

例如，定義一個名為 @age 的變量并賦值為 25：

SET @age = 25;

也可以直接在查詢語句中進(jìn)行賦值：

SELECT @variable_name := expression;

例如，將查詢結(jié)果賦值給變量：

SELECT @age := age FROM users WHERE name = 'John';-----方法一
SELECT age into @age FROM users WHERE name = 'John';---方法二

3.4 局部變量

局部變量是在存儲過程、函數(shù)或觸發(fā)器內(nèi)部定義的變量，作用范圍僅限于該存儲過程、函數(shù)或觸發(fā)器的執(zhí)行期間。它們通常用于臨時存儲數(shù)據(jù)、進(jìn)行計(jì)算或傳遞信息。

3.4.1 局部變量的聲明

在 MySQL 中，局部變量通過 DECLARE 語句在存儲過程、函數(shù)或觸發(fā)器中聲明。局部變量的作用范圍僅限于聲明它們的存儲過程、函數(shù)或觸發(fā)器內(nèi)部，并且不能在 SQL 查詢的其他地方使用。

局部變量的特點(diǎn)：

• 局部性：局部變量僅在存儲過程、函數(shù)或觸發(fā)器的執(zhí)行期間有效。當(dāng)存儲過程或函數(shù)執(zhí)行完畢后，局部變量會被自動銷毀。
• 無法在查詢外部使用：局部變量只能在其所在的存儲過程、函數(shù)或觸發(fā)器內(nèi)使用，不能在 SQL 查詢的其他部分引用。
• 生命周期：當(dāng)存儲過程或函數(shù)執(zhí)行結(jié)束時，局部變量的值會丟失。每次執(zhí)行存儲過程或函數(shù)時，局部變量會重新創(chuàng)建，并可以為其賦予初始值（如果指定了初始值）。

3.4.2 局部變量的使用

局部變量常用于存儲中間計(jì)算結(jié)果、執(zhí)行邏輯運(yùn)算或在存儲過程/函數(shù)中臨時存儲查詢結(jié)果。它們的使用受到以下限制：

• 聲明位置：DECLARE 語句必須在存儲過程、函數(shù)或觸發(fā)器的開頭部分，也就是在 BEGIN 語句之前聲明。
• 命名規(guī)則：局部變量不能使用以 @ 開頭的命名方式。@ 是用于用戶定義會話變量的前綴，局部變量不允許使用此命名規(guī)則。
• 初始值：如果沒有為局部變量指定初始值，則其默認(rèn)值為 NULL。因此，在使用局部變量時，開發(fā)者需要考慮 NULL 的處理，確保程序的邏輯正確。

 語法：

• DECLARE variable_name data_type [DEFAULT value];

variable_name：變量的名稱。

data_type：變量的數(shù)據(jù)類型（如 INT, VARCHAR, DATE 等）。

[DEFAULT value]：可選，設(shè)置默認(rèn)值。如果不指定，則默認(rèn)值為 NULL。

例子：

DECLARE @user_id INT DEFAULT 100;
DECLARE @user_name VARCHAR(255) DEFAULT 'John';

到此這篇關(guān)于Mysql之存儲過程的文章就介紹到這了,更多相關(guān)mysql存儲過程內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論