MySql高级—视图、函数、存储过程、触发器
目录
一、视图
1、视图的定义
视图的定义:
视图是由查询结果形成的一张虚拟表,是表通过某种运算得到的一个投影。
同一张表可以创建多个视图
创建视图的语法:
create view view_name as select 语句
说明:
(1)视图名跟表名是一个级别的名字,隶属于数据库;
(2)该语句的含义可以理解为:就是将该select命名为该名字(视图名);
(3)视图也可以设定自己的字段名,而不是select语句本身的字段名——通常不设置。
(4)视图的使用,几乎跟表一样!
2、视图的作用
(1)可以简化查询。
案例1:查询平均价格前3高的栏目。
传统的sql语句写法
select cat_id,avg(shop_price) as pj from ecs_goods group by cat_id order by pj desc limit 3;
创建一个视图,简化查询。
语法:
create view ecs_goods_v1 as select cat_id,avg(shop_price) pj from ecs_goods group by cat_id;
查询平均价格前3高的栏目,我们只需查询视图即可。
案例2:查询出商品表,以及所在的栏目名称;
传统写法:
select goods_id,goods_name,b.cat_name,shop_price from ecs_goods a left join ecs_category b on a.cat_id=b.cat_id;
创建一个视图
create view ecs_goods_v2 as select goods_id,goods_name,b.cat_name,shop_price from ecs_goods a left join ecs_category b on a.cat_id=b.cat_id;
查询视图;
select * from ecs_goods_v2;
(2)可以进行权限控制
把表的权限封闭,但是开放相应的视图权限,视图里只开放部分数据,比如某张表,用户表为例,2个网站搞合作,可以查询对方网站的用户,需要向对方开放用户表的权限,但是呢,又不想开放用户表中的密码字段。
再比如一个goods表,两个网站搞合作,可以相互查询对方的商品表,比如进货价格字段不能让对方查看。
第一步:创建一个视图,视图中不能包含in_price字段。
语法: create view goods_v1 as select id,goods_name,shop_price from goods;
第二步:创建一个用户,授予查询权限,只能操作goods_v1表(视图)
语法:grant select on php.goods_v1 to 'xiaotian'@'%' identified by '1234456';
第三步:我们就测试一下,
3、查询视图
语法:select * from 视图名 [where 条件]
视图和表一样,可以添加where 条件
4、修改视图
alter view view_name as select XXXX
5、删除视图
drop view 视图名称
6、查看视图结构
和表一样的,语法,desc 视图名称
7、查看所有视图
和表一样,语法:show tables;
注意:没有show views语句;
8、视图与表的关系
视图是表的查询结果,自然表的数据改变了,影响视图的结果。
(1)视图的数据与表的数据一一对应时,可以修改。
对视图的修改,也会影响到数据库表的修改。
(2)视图增删改也会影响表,但是视图并不是总是能增删改的。
create view lmj as select cat_id,max(shop_price) as lmj from goods group by cat_id;
mysql> update lmj set lmj=1000 where cat_id=4;
ERROR 1288 (HY000): The target table lmj of the UPDATE is not updatable
注意:视图的数据与表的数据一一对应时,可以修改,
(3)对于视图insert还应注意,视图必须包含表中没有默认值的列。
以上对user_v1的操作,就类似于,如下sql语句的操作,原因就是age字段没有默认值,我们里面,没有包含age字段,
create table user(
id int not null,
name varchar(32) not null,
age tinyint not null
)engine myisam charset utf8;
insert into user values(12,'xiaofan');
注意:一般来说,视图只是用来查询的,不应该执行增删改的操作。
9、视图算法
algorithm= merge/temptable/undefined
merge:当引用视图时,引用视图的语句与定义视图的语句合并(默认)。
temptableÿ