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、系统函数

参考官方文档：

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

总结

到此这篇关于mysql视图、存储函数、存储过程、触发器的文章就介绍到这了,更多相关mysql视图、存储函数、存储过程、触发器内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！

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

目录