mySQL(5)-视图-触发器

1.视图
   1.1是什么：临时表
   1.2 语法： 创建，修改，删除
   1.3 特点：动态生成
2.触发器
   2.1是什么
   2.2 语法
   

---

1.视图
   1.1是什么：临时表
       临时表 :  select* from student  的返回值 
       子查询：select * from student
   1.2 语法： 创建，修改，删除    

  create view v1 as   select * from student where sid>10 -- 创建
  alter  view  v1 as   select * from student where sid>10 -- 修改
  drop    view v1                                           -- 删除

   1.3 特点：动态生成
              我在原始表 t1中更改了数据，那么视图的结果也是会被修改的。
2.触发器
   2.1是什么：表操作(增删改) sql1 前后 ，自动运行sql2(增删改)
   2.2 语法：
 

CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW # 插入前
BEGIN
...
END
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW # 插入后
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW # 删除前
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW # 删除后
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW # 更新前
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW # 更新后

  例子：   

  -- 视图  
			
  create view v1 as   select * from student where sid>10 -- 创建
  alter  view  v1 as   select * from student where sid>10 -- 修改
  drop    view v1  -- 删除
	
	-- 触发器
	CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW # 插入前
BEGIN
...
END
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW # 插入后
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW # 删除前
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW # 删除后
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW # 更新前
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW # 更新后

-- 例子
-- 1.创建一个触发器
 delimiter //
create trigger t1 before insert on student for each row
begin
Insert into teacher (tname) values('sdfdfd');--sql1
end //
delimiter;

-- 2.在终端调用这个触发器
 -- 2.1 添加一条记录
Insert into student(gender,class_id,sname) values('女'，1，‘女1’);-- sql2
-- 2.2 添加2条记录
Insert into student(gender,class_id,sname) values('女'，2，‘女2’),('女'，2，‘女3’);-- sql3

-- 2.3 去掉这个触发器
drop trigger t1;

-- 3. new old  
-- New  代指新数据  Insert
-- Old  代指旧数据  delete  NEW OLD update

Insert into teacher (tname) values(NEW.sname);
-- NEW.sname =下句中 student.sname
Insert into student(gender,class_id,sname) values('女'，2，‘女2’),('女'，2，‘女3’)

   其实一般公司里，DBA才会用视图和触发器，而作为开发人员，大家还是用程序的多。