MYSQL视图和mysql触发器（学会并使用day6）

MYSQL视图

视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

视图是一个虚拟表，本身并不包含数据。作为一个select语句保存在数据字典中的。它是sql的查询结果，其内容由查询定义。是从另外一个角度看待原表。同真实的表一样，视图包含一系列带有名称的列和行数据，在使用视图时动态生成。视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ] ;

使用视图的原因

使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。

使用视图的用户只能访问他们被允许· 查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。

一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

使用视图的大部分情况是为了保障数据安全性，提高查询效率。

视图作用

• 重复利用SQL语句
• 简化SQL查询，快速取数据
• 只用知道表的部分结构
• 保护数据，根据特定授权
• 更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据
  视图只是用来查看存储在别处的数据的设施，本身不包含数据，返回的数据也是从其他表检索出来的。

视图规则和限制

• 与表一样，命名必须是唯一的(不能出现同名视图或表名)。
• 创建视图数目无限制，但是要考虑复杂查询创建为视图之后的性能影响。
• 视图不能添加索引，也不能有关联的触发器或者默认值。
• 视图可以提高安全性，必须具有足够的访问权限。
• order by可用在视图中，但是如果从该视图检索数据select中含有order by ，那么该视图中的order by将被覆盖。
• 视图可以和表一起使用。

视图的应用

1. 权限控制时使用

   如某几个列，允许用户查询，其他列不允许查询

   可以通过视图，开放其中几列查询，起到权限控制作用

2. 简化复杂查询时使用

   查询每个栏目下商品的平均价格，并按平均价格排序，查询出平均价格前3的栏目等使用。

3. 视图能不能更新，删除，添加

   如果视图的每一行，是与物理表一 一对应的则可以

   视图的行是由物理表多行经过计算得到的结果，视图不可以更新的。

实际操作

创建表并查看

CREATE TABLE IF NOT EXISTS employee(
id int(11) auto_increment PRIMARY key,
name VARCHAR(22),
age int(4),
money int(20)
);

INSERT into employee VALUES(null,"张三",20,3650);
INSERT into employee VALUES(null,"李四",30,4502);
INSERT into employee VALUES(null,"王二",30,3650);
INSERT into employee VALUES(null,"麻子",50,8885);
INSERT into employee VALUES(null,"小红",20,4445);

SELECT * from employee;

创建视图

创建语法

-- CREATE VIEW <视图名> AS (SELECT语句)

<视图名>：指定视图的名称。该名称在数据库中必须是唯一的，
不能与其他表或视图同名。
<SELECT 语句>：指定创建视图的 SELECT 语句，
可用于查询多个基础表或源视图。


-- 查看视图
select * from 视图名

生成视图并查看视图

CREATE view emp_view as(SELECT * from employee where money>4000);
SELECT * from emp_view ;

视图记录修改

修改语法

-- update 数据库表名 set 字段名1=字段值1,字段名2=字段值2,
...字段名n=字段值n where 条件表达式; #和表的修改一样
update ss set username='cl' where userId=2;

-- 注意:修改了视图的数据记录，对基表数据也有影响

修改视图，并查看基表和视图

UPDATE emp_view set money=9000 WHERE money>8000;
SELECT * from emp_view ;
SELECT * from employee;

视图发生变化

基表也发生变化

修改视图

格式：
alter view 视图名称 as select 语句;
或
alter view 视图名称 as select 视图;
或
create or replace view  视图名  as  select 字段名 from 表名;

视图重命名:
rename table 视图名 to 新视图名;

第一种

alter view emp_view as (SELECT * from employee where id>3);
SELECT * from emp_view;

第二种

CREATE VIEW emp_view02 AS SELECT * FROM employee WHERE id<3;
rename table emp_view02 to emp_view03;
SELECT * from emp_view03;

mysql触发器

触发器（TRIGGER）是由事件来触发某个操作。这些事件包括insert语句、update语句和delete语句。当数据库系统执行这些事件时，就会激活触发器执行相应的操作。

触发器是由insert、update和delete等事件来触发某种特定操作。满足触发器的触发条件时，数据库系统就会执行触发器中定义的程序语句。这样做可以保证某些操作之间的一致性。

创建触发器

语法

CREATE TRIGGER  -- 触发器名称
BEFORE/AFTER  -- (二选一,表示在事件之前执行还是事件之后执行)
UPDATE/INSERT/DELETE -- (三选一,指定在什么事件触发触发器,即增,删,改)
ON 表名称 
FOR EACH ROW  -- (影响所有行) 
#触发器主体
sql语句;

employee表

CREATE TABLE IF NOT EXISTS employee(
id int(11) auto_increment PRIMARY key,
name VARCHAR(22),
age int(4),
money int(20)
);

INSERT into employee VALUES(null,"张三",20,3650);
INSERT into employee VALUES(null,"李四",30,4502);
INSERT into employee VALUES(null,"王二",30,3850);
INSERT into employee VALUES(null,"麻子",50,8885);
INSERT into employee VALUES(null,"小红",20,4445);

employee02表

CREATE TABLE IF NOT EXISTS employee02(
id int(11) auto_increment PRIMARY key,
name VARCHAR(22),
age int(4),
money int(20)
);

创建一个触发器t1

CREATE TRIGGER t1 before update on 
employee for each row insert into 
employee02 VALUES(null,"鲍勃",20,4445);

每当执行一次更新employee表的操作时，在更新之前都会向employee02表插入一条姓名为"鲍勃"，年龄为20，ID为4445的固定记录。

更新语句并查看employee02表

UPDATE  employee set money=500 where money=4502;
SELECT * from employee02;

删除触发器

#删除触发器T1
DROP TRIGGER IF EXISTS T1;

查询触发器

#在已知数据库内，查询触发器
SHOW TRIGGERS;

触发器类型OLD和NEW

在触发器中，OLD和NEW是用来引用行数据的特殊关键字。
OLD关键字表示触发器中被修改的行的原有数据。当在触发器中使用OLD关键字时，可以引用被修改行的旧值。
NEW关键字表示触发器中被修改的行的新数据。当在触发器中使用NEW关键字时，可以引用被修改行的新值。
这些关键字通常在触发器中与BEFORE或AFTER结合使用，用于引用触发器所针对的行的旧值和新值。

CREATE TRIGGER t4 after update on employee for each row 
insert into employee03 VALUES(NEW.id,NEW.name,NEW.age,NEW.money);

这是一个"after update"触发器，表示在执行更新操作之后触发。每次更新"employee"表中的一行数据时，该触发器将会执行。

触发器中的代码将使用关键字NEW来引用被更新的行的新值，并将这些新值插入到"employee03"表中的一条新行中。

CREATE TRIGGER t3 before update on employee for each row 
insert into employee03 VALUES(OLD.id,OLD.name,OLD.age,OLD.money);

这是一个"before update"触发器，表示在执行更新操作之前触发。每次更新"employee"表中的一行数据时，该触发器将会执行。

触发器中的代码将使用关键字OLD来引用被更新的行的旧值，并将这些旧值插入到"employee03"表中的一条新行中。