主页 系列 标签 关于 简体中文

存储过程，触发器，视图三者使用场景

 收录于  类别 数据库 和 系列 mysql数据库
     约 2282 字  预计阅读 5 分钟 
系列 - mysql数据库

视图是从一个或多个表中导出来的表，它是一种虚拟存在的表，表的结构和数据都依赖于基本表。通过视图不仅可以看到存放在基本表中的数据，还可以像操作基本表一样，对数据进行查询、添加、修改和删除。

  1. 一种逻辑对象，不占物理存储空间
  2. 来自一个或多个表，是由查询结果形成的一张虚拟表
  3. 同一张表可以创建多个视图
  1. 能够简化用户的操作
  2. 使用视图，表中的数据就有了一定的安全性
  3. 可以定制用户数据，聚焦于特定的数据

  1. 性能差 MySQL必须把视图查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，MySQL也要把它变成一个复杂的结合体，需要花费一定的时间。

  2. 修改限制 当用户试图修改试图的某些信息时，数据库必须把它转化为对基本表的某些信息的修改，对于简单的试图来说，这是很方便的，但是，对于比较复杂的试图，可能是不可修改的。

1
2
3
4
5
6
7

CREATE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition;

查看语法：show create view viewname

删除语法：drop view 视图名

  • 在默认情况下，新创建的视图保存在当前选择的数据库中。若要明确指定在某个数据库中创建视图，在创建时应将名称指定为“数据库名.视图名”。
  • 在 SHOW TBLES的查询结果中会包含已经创建的视图。
  • 创建视图要求用户具有 CREATE VIEW权限，以及查询涉及的列的 SELECT权限。如果还有 OR REPLACE子向，必须具有视图的DROP权限。)在同一个数据库中，视图名称和已经存在的表名称不能相同，为了区分，建议在命名时添加“view”。
  • 视图创建后， MySQL就会在数据库目录中创建一个“视图名.frm”文件。

例. 创建视图view_goods_promotion，里面保存打88折的商品的相关信息

1
2
3

create view view_goods_promotion (goods_id, goods_name, promotion_price) as

select id, name, price*0.88 from goods;

我们先创建并使用本章的数据库p10。

现在我们有两个表，一个是商品goods表，一个是订单order表。每生成一个订单orders，商品goods表就要跟着减少。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

create table goods(
id int primary key auto_increment,
name varchar(20) not null,
price decimal(10,2) default 1,
inv int comment '库存数量'
)ENGINE=InnoDB  DEFAULT CHARSET=utf8;

insert into goods values(null,'红米',1999,100),
(null,'小米',2999,50),
(null,'华为',2999,150),
(null,'iphonese',2999,300),
(null,'iphone12',5999,200);

create table orders(
id int primary key auto_increment,
goods_id int not null comment '商品id',
goods_number int comment '商品数量'
);

image.png

image.png

现在我们知道某商品的id，需要查询该商品的价格。每次都使用sql语句查询的话，效率就相对比较低，于是我们可以创建一个存储过程price，只要我们输入商品的id，我们就可以得到id对应的商品的价格price。即输入参数为id，输出参数为pri，表示价格。

创建存储过程price，输入参数为商品id，输出参数为该商品的价格。其创建语句格式如下：

1
2
3
4
5
6

delimiter //
create procedure price(in goodsId int,out pri varchar(10))
    begin
    select price into pri from goods where id=goodsId;
    select pri;
    end //

需要注意的是，在使用SELECT …INTO语句时，变量名不能和数据表中的字段名不能相同，否则虽然不会报错，但是在查询变量的时候会发现变量还是NULL，根本没发生变化。

调用存储过程price，查看商品id为1的商品的价格是多少，我们先查一下goods表里面的数据，方便等下验证。

image.png

生成订单的时候，订单对应的商品库存应该随之减少，于是我们可以创建一个触发器after_order，在往order表中插入数据，也就是订单生成的时候，goods商品表中的inv库存字段，就会随之减少。

创建触发器after_order，实现订单生成时，商品goods表的库存随之减少。其创建语句格式如下：

1
2
3
4
5

create trigger after_order
after insert on orders for each row
begin
update goods set inv=inv-new.goods_number where id=new.id;
end//

往订单orders表插入数据，模拟订单生成，验证触发器是否正常工作。

1

insert into orders values(null,1,2);

image.png

我们可以看到，生成了一个购买了2个id为1的商品的订单，再次查询goods表时，id为1的商品的库存即inv字段已减少2。

image.png

多测试几个数据，可以发现触发器工作正常。

创建视图view_goods_promotion，里面保存打88折的商品的相关信息。以后我们想查询打88折之后的商品信息的时候，直接查询该视图即可。

其语法格式如下：

1
2

create view view_goods_promotion (goods_id, goods_name, promotion_price) as
select id, name, price*0.88 from goods;

(goods_id, goods_name, promotion_price)分别为goods表中id, name, price*0.88的别名。

1
2
3

desc view_goods_promotion; /**查看视图view_goods_promotion的字段信息**/

show create view view_goods_promotion\G; /**查看视图view_goods_promotion的创建语句**/

image.png

  • 查询视图
1

select * from view_goods_promotion;
  • 修改视图
1

alter view view_goods_promotion as select id from goods;
  • 再次查看视图
1

desc view_goods_promotion;
  • 删除视图
1

drop view_goods_promotion;
  • 再次查看视图
1

desc view_goods_promotion;

image.png

可以看到视图view_goods_promotion已被修改

image.png

可以看到视图view_goods_promotion已不存在

现在我们要往视图view_goods插入一条数据，id为8，商品名称为“一加”的商品。

可采用如下语法：

1
2
3
4
5
6

create view view_goods as
select id,name from goods;

insert into view_goods values(8,"一加");;

select * from view_goods;

image.png

从上图可看出，通过视图添加的数据实际保存在基本表goods中。

现在我们要更新视图view_goods的数据，将id为8的商品名称改为“一加2”。

可采用如下语法：

1

update view_goods set name="一加2" where id=8;

image.png

可以看到，基本表goods也是有修改的。

现在我们要将视图view_goods中能够id为8的商品删除掉。

可采用如下语法：

1

delete from view_goods where id=8;

image.png

基本表goods中的相应数据也被删掉了。

更新于 2023-05-26
 mysql
返回 | 主页