数据库学习笔记(十)-----视图与触发器

---

前言

MySQL视图与触发器学习笔记，仅供参考！

---

一、视图

1.什么是视图？

视图是从一个或多个表中导出来的表，是一种虚拟存在的表。视图就像一个窗口，通过这个窗口可以看到系统专门提供的数据，这样用户可以不看整个数据库表中的数据，而只关心对自己有用的数据。视图可以使用户的操作更方便，而且可以保障数据库系统的安全性。

为了提高复杂的SQL语句的复用性和表的操作的安全性，MySQL数据库管理系统提供了视图特性。所谓视图，本质上是一种虚拟表，其内容与真实的表相似，包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储数据值的形式存在，行和列数据来自定义视图的查询所引用的基本表，并且在具体引用视图时动态生成。

视图使程序员只关心感兴趣的某些特定数据和他们所负责的特定任务。这样程序员只能看到视图中所定义的数据，而不是视图所引用表中的数据，从而提高数据库中数据的安全性。

2.创建视图

虽然视图可以被看成是一种虚拟表，但是其物理上是不存在的，即MySQL并没有专门的位置为视图存储数据。根据视图的概念可以发现其数据来源于查询语句，因此创建视图的基本语法为：

 CREATE[OR REPLACE] VIEW viewname[columnlist]  AS SELECT statement 

其中，CREATE表示创建新的视图；REPLACE表示替换已经创建的视图；viewname为视图的名称；columnlist为属性列；SELECT statement表示SELECT语句；
注意! 创建视图需要登陆用户有相应的权限，查看权限方法：

use school;
select user, Select_priv, Create_view_priv FROM mysql.user;	#查询数据库用户创建和选择视图权限

1.单表创建视图

举例：

use school;
alter table student add privacy varchar(64);	#增加私隐列  
create view view_student as select id,belongclass,name from student;	#学生表创建视图
desc view_student;	#查看视图
select * from view_student;	#视图内容查询

2.多表创建视图

CREATE[OR REPLACE] VIEW viewname[columnlist] AS SELECT statement  

其中，CREATE表示创建新的视图；REPLACE表示替换已经创建的视图；viewname为视图的名称；columnlist为属性列；SELECT statement表示SELECT语句；与单表上创建视图不同的是，SELECT子句是涉及到多表的联合查询语句。
举例：

#student_name与class_name是给视图取其他列名
create view view_student_class as 
select student.id,student.name as student_name,class.name as class_name,class.teacher 
from class inner join student on class.id=student.belongclass;

desc view_student_class;
select * from view_student_class;

3.查看视图

创建完视图后，像表一样，我们经常需要查看视图信息。在MySQL中，有许多可以实现查看视图的语句，如DESCRIBE、SHOW TABLES、SHOW CREATE VIEW。如果要使用这些语句，首先要确保拥有SHOW VIEW的权限。

1.使用DESCRIBE | DESC语句查看视图基本信息

在之前的博客中我们已经大致讲解了使用DESCRIBE语句来查看表的基本定义。因为视图也是一张表，只是这张表比较特殊，是一张虚拟的表，所以同样可以使用DESCRIBE语句来查看视图的基本定义。DESCRIBE语句查看视图的语法如下：

 DESCRIBE | DESC viewname;

在上述语句中，参数viewname表示所要查看设计信息的视图名称。

2.使用SHOW TABLES语句查看视图基本信息

从MySQL 5.1版本开始，执行SHOW TABLES语句时不仅会显示表的名字，同时也会显示视图的名字。
下面演示通过SHOW TABLES语句查看数据库school中的视图和表的功能，具体SQL语句如下，执行结果如下图所示。

show tables;

注意：为了便于区分视图与表，我们通常给视图加一个“view”的前缀名，这样就能清楚将二者区分开！

3.使用 show create view/table 语句查看视图创建信息

基本语法：

SHOW CREATE TABLE或VIEW viewname;   

viewname即为视图名。

4.更新视图数据

更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的数据。因为视图实质是一个虚拟表，其中没有数据，通过视图更新时都是转换到基本表更新。更新视图时，只能更新权限范围内的数据，超出范围就不能更新了。
举例：

select * from view_student;

#更新
update view_student set name='Change甲' where name='甲';
select * from view_student;

select * from student;	#查看更新后student表的内容

注意：当我们更新视图数据后，原表内的数据也会进行更改，之前就已经提到了，视图知识在原表的基础上新开一个“窗口”，让我们能看到“窗口”内的内容罢了。
视图中也存在一些不能更新的情况：
1.视图中包含SUM()、COUNT()、MAX()和MIN()等函数
2.视图中包含UNION、UNION ALL、DISTINCT、GROUP BY和HAVING等关键字
3.视图对应的表存在没有默认值的列，而且该列没有包含在视图里
4.包含子查询的视图
5.其他特殊情况

5.修改视图

修改视图是指修改数据库中存在的视图，当基本表的某些字段发生变化的时候，可以通过修改视图来保持与基本表的一致性。ALTER语句来修改视图。
使用ALTER语句修改视图

ALTER VIEW viewname[columnlist]   

AS SELECT statement
这个语法中的所有关键字和参数除了alter 外，其他都和创建视图是一样的，因此不再赘述。
举例：

#修改前
desc view_student_class;
select * from view_student_class;

#修改视图语句
alter view view_student_class as select student.id as student_id,student.name as student_name,class.name as class_name,class.id as class_id,class.teacher from class inner join student on class.id=student.belongclass;

#修改后
desc view_student_class;
select * from view_student_class;

6.删除视图

删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据。
在MySQL中，可使用DROP VIEW语句来删除视图，但是用户必须拥有DROP权限。删除视图的语法如下：

DROP VIEW viewname [,viewnamen]；

在上述语句中，参数viewname表示所要删除视图的名称，可同时指定删除多个视图。
例如我们要删除视图“view_student”：

drop view view_student;

二、触发器

1.什么是触发器？

在实际开发中往往会碰到这样的情况：
当我们对一个表进行数据操作时，需要同步对其它的表执行相应的操作，正常情况下，如果我们使用sql语句进行更新，将需要执行多条操作语句！

比如，在某些棋牌游戏中，当玩家充值金币后，玩家表数据库中金币增加的同时，玩家所属的代理会得到相应的提成并计入代理的收益中，即代理数据库表提成记录字段也要同步更新。而以上的场景，我们可以轻松使用触发器来实现！

触发器（TRIGGER）是由事件来触发某个操作。这些事件包括INSERT语句、UPDATE语句和DELETE语句。当数据库系统执行这些事件时，就会激活触发器执行相应的操作。MySQL从5.0.2版本开始支持触发器。

2.创建触发器

在MySQL中创建触发器通过SQL语句CREATE TRIGGER来实现，其语法形式如下：

CREATE trigger trigger_name BEFORE|AFTER trigger_EVENT ON TABLE_NAME FOR EACH ROW trigger_STMT

在上述语句中，参数trigger_name表示要创建的触发器名；
参数BEFORE和AFTER指定了触发器执行的时间，前者在触发器事件之前执行触发器语句，后者在触发器事件之后执行触发器语句；

参数trigger_EVENT表示触发事件，即触发器执行条件，包含DELETE、INSERT和UPDATE语句；参数TABLE_NAME表示触发事件的操作表名；参数FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器；

参数trigger_STMT表示激活触发器后被执行的语句。执行语句中如果要引用更新记录中的字段，对于INSERT语句,只有NEW是合法的，表示当前已插入的记录；对于DELETE语句，只有OLD才合法，表示当前删除的记录；而UPDATE语句可以和NEW（更新后）以及OLD（更新前）同时使用。

注意：不能创建具有相同名字的触发器。另外，对于具有相同触发程序动作时间和事件的给定表，不能有两个触发器。因此，对于有经验的用户，在创建触发器之前，需要查看MySQL中是否已经存在该标识符的触发器和触发器的相关事件。
举例：

CREATE TABLE class 
(	#创建班级表
`id` int NOT NULL AUTO_INCREMENT, 
`name` varchar(128) DEFAULT NULL,
`teacher` varchar(64) DEFAULT NULL,
`count`  int DEFAULT 0,	#学生人数，默认值是0
UNIQUE KEY `id` (`id`)
);
#班级表插入数据
insert into class values(101, '物联网工程一班', '玛卡巴卡', 0),(102, '物联网工程二班', '伊古比古', 0),(103, '物联网工程三班', '唔西迪西', 0);

CREATE TABLE student 
(	#创建学生表
`id` int NOT NULL AUTO_INCREMENT UNIQUE,
`name` varchar(64) DEFAULT NULL,
`class_id` int DEFAULT NULL,
`sex` enum('F','M') DEFAULT NULL
);

#创建触发器，当新增学员时，对应班级人数+1
create trigger tri_insert_student after insert on student for each row update class set count=count+1 where class.id = NEW.class_id;

#学生表插入数据
insert into student values(1,'甲',101,'M'),(2,'乙',102, 'F'),(3,'丙',102,'F'),(4,'丁',101,'F');
select * from class;

#创建触发器，当删除学员时，对应班级人数-1
create trigger tri_delete_student after delete on student for each row update class set count=count-1 where id = OLD.class_id;
delete from student where id=1;	#删除id为1的学员（该学院对应班级为物联网工程一班）
select * from class;

3.触发器包含多条执行语句

CREATE   trigger trigger_name BEFORE|AFTER trigger_EVENT     
	ON TABLE_NAME FOR EACH ROW                                 
	BEGIN                                                        
	trigger_STMT                                                 
    END

在上述语句中，比“只有一条执行语句的触发器”语法多出来两个关键字BEGIN和END，在这两个关键字之间是所要执行的多个执行语句的内容，执行语句之间用分号隔开。
在MySQL中，一般情况下用“;”符号作为语句的结束符号，可是在创建触发器时，需要用到“;”符号作为执行语句的结束符号。为了解决该问题，可以使用关键字DELIMITER语句。

DELIMITER $$	#将结束符号设置为$$

举例：

create table grade(id int UNIQUE AUTO_INCREMENT,  math tinyint unsigned, chinese tinyint unsigned, english tinyint unsigned);	#创建成绩表grade
insert into grade values(2, 72, 64, 89),(3, 54, 69, 87),(4, 78, 79, 89);

create trigger tri_delete_student_grade after delete on student for each row 
	BEGIN                                    
    Delete from grade where id = OLD.id;  #删除学生时，也删除成绩表中对应学生的成绩                                                        
    update class set count=count-1 where id = OLD.class_id; #更新班级表中的记录   
    END;                                    
    $$                                       
   	DELIMITER ;

delete from student where id=2;	#删除id为2的学生(对应学生乙);

select * from class;
select * from grade;

4.查看触发器

那么如何查看MySQL软件中已经存在的触发器呢？在MySQL软件中查看已经存在的触发器，通过SQL语句SHOW TRIGGERS来实现，其语法形式如下:

SHOW TRIGGERS ;  

在该列表中会显示出所有触发器的信息。其中，参数Trigger表示触发器的名称；参数Event表示触发器的激发事件；参数Table表示触发器对象触发事件所操作的表；参数Statement表示触发器激活时所执行的语句；参数Timing表示触发器所执行的时间。（由于窗口大小原因，显示的表格有异常。）

5.查看系统表triggers实现查看触发器

在MySQL中，在系统数据库information_schema中存在一个存储所有触发器信息的系统表triggers，因此查询该表格的记录也可以实现查看触发器功能。系统表triggers的表结构

use information_schema;   #选择数据库information_schema                  
select * from triggers;                                                        
select * from triggers where trigger_name=’tri_delete_student’; #查询系统表triggers中的触发器

6.触发器的删除

在MySQL软件中，可以通过DROP TRIGGER语句或通过工具来删除触发器。
在MySQL中，删除触发器可以通过SQL语句DROP TRIGGER来实现，其语法形式如下：

DROP TRIGGER trigger_name; 

在上述语句中，参数trigger_name表示所要删除的触发器名称。

---

总结

从视图与触发器开始，后面内容（存储过程、光标等等）的SQL语句愈加复杂，难度有所上升，仍需努力学习！！！