mysql视图和触发器

创建视图

一、在单表上创建视图

Create view 视图表名 as select * from 表名；

例mysql> create view aaa as select * frommember;

mysql> create view bbb(id,name,sex) as select id,name,sex from member;

Desc aaa；    查看视图表结构

二、在多表上创建视图

Create algorithm=merge view 视图表名 (字段1，字段2，字段3)

As select字段1，字段2，字段3       /这些字段是表下的字段

From 表1，表2 where 表1字段1=表2字段1

With local check option；

例mysql> create algorithm=merge view

   -> aaa (name,sex,age)

   -> as select member.name,famliy.sex,member.age

   -> from member,famliy where member.id=famliy.id

    -> with local check option;

修改视图（alter view修改）

Alteralgorithm=undefined|merge|tempbable view 视图名（属性字段）

As select语句

With cascaded|local check potion;

 例mysql> alter view aaa (dname,dsex,dage)

-> as select member.name,famliy.sex,famliy.age

-> from member,famliy where member.id=famliy.id

   -> with check option;

修改视图（create or replace view修改）

Create or replace algorithm=undefined|merge|tempbable view 视图名（属性字段）

As select语句

With cascaded|local check potion;

 例mysql> create or replace algorithm=temptable

    -> view aaa (name,sex,age)

    -> as select member.name,famliy.sex,famliy.age

-> from member,famliy;

 

查看视图

1 mysql> show table status like 'aaa' \G;

2 mysql> show create view aaa \G;

3 mysql> select * from information_schema.views \G;

 

删除视图

1 mysql> drop view if existsaaa;

2 mysql> drop view if existsaaa,bbb;

 

查看mysql是否有drop权限

Select drop_priv from mysql.user where user=’root’;

mysql触发器

一、创建只有一个执行语句的触发器

Create trigger 触发器名 before|after 触发事件

On 表明 for each row

执行语句

例mysql> create trigger aaa before insert

    -> on member for each row

    -> insert into triggername values(now());

 

二、创建有多个执行语句的触发器

Create trigger 触发器名 before|after 触发事件

On 表明 for each row

Begin

执行语句列表       （不用的执行语句之间用；号隔开）

End

例mysql> create trigger aaa after delete

    -> on member for each row

    -> begin

    -> insert into triggertime values('21:03:01');

    -> insert into triggertime values('12:23:12');

    -> end

    -> &&

 

查看触发器  

mysql>show triggers \G

mysql> select *from information_schema.triggers  \G

mysql> select *from information_schema.triggers where trigger_name='aaa'  \G

 

mysql中触发器执行的顺序是before、表操作（insert、update、delete）、after触发器。

 

删除触发器

mysql> drop trigger aaa;

 

mysql>delimiter ;     （更改mysql结束符号）