创建视图
一、在单表上创建视图
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结束符号)