MySQL系列之五视图、存储函数、存储过程、触发器

原作者: [db:作者] 来自: [db:来源] 收藏邀请

系列教程

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 |
+-----------+