www.av网站,一代女皇色情艳史片一级太平公主

主頁 > 知識庫 > MySQL系列之五視圖、存儲函數、存儲過程、觸發器

MySQL系列之五視圖、存儲函數、存儲過程、觸發器

系列教程

MySQL系列之開篇 MySQL關系型數據庫基礎概念
MySQL系列之一 MariaDB-server安裝
MySQL系列之二多實例配置
MySQL系列之三基礎篇
MySQL系列之四 SQL語法
MySQL系列之六用戶與授權
MySQL系列之七 MySQL存儲引擎
MySQL系列之八 MySQL服務器變量
MySQL系列之九 mysql查詢緩存及索引
MySQL系列之十 MySQL事務隔離實現并發控制
MySQL系列之十一日志記錄
MySQL系列之十二備份與恢復
MySQL系列之十三 MySQL的復制
MySQL系列之十四 MySQL的高可用實現
MySQL系列之十五 MySQL常用配置和性能壓力測試

一、視圖

視圖：VIEW,虛表，保存有實表的查詢結果，實際數據不保存在磁盤

物化視圖：實際數據在磁盤中有保存，加快訪問，MySQL不支持物化視圖

基表：視圖依賴的表

視圖中的數據事實上存儲于“基表”中，因此，其修改操作也會針對基表實現。其修改操作受基表限制。

注意：修改視圖時是修改的原表

1、視圖的創建

CREATE VIEW view_name AS select_statement

MariaDB [testdb]> CREATE VIEW v_students AS SELECT id,name,ages FROM students;
MariaDB [testdb]> SELECT * FROM v_students;
+----+---------------+------+
| id | name          | ages |
+----+---------------+------+
|  1 | tom           |   26 |
|  2 | jerry         |   19 |
|  3 | maria         |   19 |
|  4 | xiaolongnv    |   18 |
|  5 | dongfangbubai |   28 |
|  6 | ouyangfeng    |   56 |
+----+---------------+------+

2、查看視圖定義

SHOW CREATE VIEW view_name

MariaDB [testdb]> SHOW CREATE VIEW v_students\G
         View: v_students
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_students` AS select `students`.`id` AS `id`,`students`.`name` AS `name`,`students`.`ages` AS `ages` from `students`

MariaDB [testdb]> SHOW TABLE STATUS LIKE 'v_students'\G
        Name: v_students
        Comment: VIEW  #判斷一個表是否是視圖

3、刪除視圖

DROP VIEW [IF EXISTS] view_name [, view_name] ...

MariaDB [testdb]> DROP VIEW v_students;

二、存儲函數

說明：參數可以有多個,也可以沒有參數，必須有且只有一個返回值。

1、系統函數

參考官方文檔：https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

2、自定義函數(user-defined function：UDF)

自定義函數保存在mysql.proc表中

查看UDF列表 SHOW FUNCTIOIN STATUS;
查看UDF定義 SHOW CREATE FUNCTION function_name
刪除UDF DROP FUNCTION function_name
調用自定義函數語法 SELECT function_name(parameter_value,...)
為變量賦值 SET parameter_name = value[,parameter_name = value...]
查看變量 SELECT INTO parameter_name

MariaDB [testdb]> DELIMITER //  #修改結束符為//
MariaDB [testdb]> CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, Y SMALLINT UNSIGNED)
    -> RETURNS SMALLINT
    -> BEGIN
    -> DECLARE a, b SMALLINT UNSIGNED DEFAULT 10;
    -> SET  a = x, b = y;
    -> RETURN a+b;
    -> END//
Query OK, 0 rows affected (0.01 sec)
MariaDB [testdb]> DELIMITER ;  #定義完函數后再修改回來
MariaDB [testdb]> SELECT addTwoNumber(8,9);  #調用UDF求和
+-------------------+
| addTwoNumber(8,9) |
+-------------------+
|                17 |
+-------------------+

三、存儲過程

存儲過程把經常使用的SQL語句或業務邏輯封裝起來,預編譯保存在數據庫中,當需要時從數據庫中直接調用,省去了編譯的過程。提高了運行速度同時降低網絡數據傳輸量

存儲過程：存儲過程保存在mysql.proc表中

創建存儲過程
- CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter ...]]) routime_body
- proc_parameter : [IN|OUT|INOUT] parameter_name type
- 其中IN表示輸入參數，OUT表示輸出參數，INOUT表示既可以輸入也可以輸出；param_name表示參數名稱；type表示參數的類型
查看存儲過程列表 SHOW PROCEDURE STATUS
查看存儲過程定義 SHOW CREATE PROCEDURE sp_name
調用存儲過程: CALL sp_name ([ proc_parameter [,proc_parameter ...]])
- 說明:當無參時,可以省略"()",當有參數時,不可省略"()”
刪除存儲過程: DROP PROCEDURE [IF EXISTS] sp_name

ALTER語句修改存儲過程只能修改存儲過程的注釋等無關緊要的東西,不能修改存儲過程體,所以要修改存儲過程,方法就是刪除重建

流程控制

存儲過程和函數中可以使用流程控制來控制語句的執行

IF：用來進行條件判斷。根據是否滿足條件，執行不同語句
CASE：用來進行條件判斷，可實現比IF語句更復雜的條件判斷
LOOP：重復執行特定的語句，實現一個簡單的循環
LEAVE：用于跳出循環控制
ITERATE：跳出本次循環，然后直接進入下一次循環
REPEAT：有條件控制的循環語句。當滿足特定條件時，就會跳出循環語句
WHILE：有條件控制的循環語句

四、觸發器

觸發器的執行不是由程序調用，也不是由手工啟動，而是由事件來觸發、激活從而實現執行。

創建觸發器

CREATE 
    [DEFINER = { user | CURRENT_USER }] 
    TRIGGER trigger_name 
    trigger_time trigger_event 
    ON tbl_name FOR EACH ROW 
    trigger_body

trigger_name：觸發器的名稱

trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后觸發

trigger_event:：{ INSERT |UPDATE | DELETE }，觸發的具體事件

tbl_name：該觸發器作用在表名

查看觸發器 SHOW TRIGGERS;
刪除觸發器 DROP TRIGGER trigger_name;

示例：創建觸發器，在向學生表INSERT數據時，學生數增加，刪除學生信息時，學生數減少。

MariaDB [testdb]> CREATE TABLE students_info (id TINYINT(2) NOT NULL AUTO_INCREMENT,name VARCHAR(30) DEFAULT NULL,PRIMARY KEY(id));  #創建一張學生信息表
MariaDB [testdb]> CREATE TABLE students_count (stu_count TINYINT(2) DEFAULT 0);  #創建一張學生數量表
MariaDB [testdb]> INSERT INTO students_count VALUES(0);  #給個初識值0

MariaDB [testdb]> CREATE TRIGGER trigger_students_count_insert
    -> AFTER INSERT
    -> ON students_info FOR EACH ROW
    -> UPDATE students_count SET stu_count=stu_count+1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> CREATE TRIGGER trigger_students_count_delete
    -> AFTER DELETE
    -> ON students_info FOR EACH ROW
    -> UPDATE students_count SET stu_count=stu_count-1;
Query OK, 0 rows affected (0.01 sec)
MariaDB [testdb]> INSERT students_info(id,name) VALUES (1,'Tom'),(2,'Maria');
MariaDB [testdb]> SELECT * FROM students_info;
+----+-------+
| id | name  |
+----+-------+
|  1 | Tom   |
|  2 | Maria |
+----+-------+
MariaDB [testdb]> SELECT * FROM students_count;  #插入記錄，觸發事件，數量增加為2
+-----------+
| stu_count |
+-----------+
|         2 |
+-----------+
MariaDB [testdb]> DELETE FROM students_info WHERE id=1;
MariaDB [testdb]> SELECT * FROM students_info;
+----+-------+
| id | name  |
+----+-------+
|  2 | Maria |
+----+-------+
MariaDB [testdb]> SELECT * FROM students_count;  #刪除記錄，數量減1
+-----------+
| stu_count |
+-----------+
|         1 |
+-----------+