数据库笔记 day04

2019/8/23 周五
学习内容：视图，触发器，存储过程，变量，if else语句，范式
关键字：create view；describe；select；drop；delimiter ； create trigger；before；after；new ；old ；create procedure；in；out；call；set @；if then else;

视图

• What：试图（view）是一个虚拟表，可以从数据库中的一个或者多个表中导出来（也可以在已经存在的视图的基础上定义）。视图本身并不包含任何数据，只是一种映射，其本质是封装了一条查询语句。
• Why：视图可以用来封装复杂的sql语句，也可以对机密数据提供安全保护。
• How：如何使用视图？

1. 创建视图：create view

语法：CREATE VIEW 视图名 AS 查询语句
下段代码为制作一个给销售人员看的商品视图，连接了商品类型表和商品表，包含商品名称，出售价格，商品的类型，产地信息

create view view_sellset as
select c.c_name as 商品名称, c.c_madein as 商品产地, ct.ct_name as 商品类型, c.c_outprice as 商品售价
from commodity as c inner join commoditytype as ct on c.c_type=ct.ct_id
where c.c_outprice is not null; # 过滤掉没有售价的商品

创建视图时，可以给原来数据表中的字段设置显示新的名称，用as关键字

2. 查看视图信息

（1）DESCRIBE 视图名;

查看视图的基本信息

describe view_sellset;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| 商品名称 | varchar(50) | NO   |     | NULL    |       |
| 商品产地 | varchar(50) | NO   |     | NULL    |       |
| 商品类型 | varchar(50) | NO   |     | NULL    |       |
| 商品售价 | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.12 sec)

（2）SHOW TABLE STATUS LIKE ‘视图名’;
查看视图基本信息，但在这个案例中尝试，显示出来的信息除了视图名之外，其他属性全是null

（3）SHOW CREATE VIEW 视图名;
查看视图的详细信息，可以显示出创建视图时写的语句

3. 查看视图里的详细数据：select

查看视图信息与查看数据表中的信息一样，使用select关键字

select * from view_sellset;

输入上面的查询命令，得到的结果为MySQL拼接好的视图（后面省略）

+-----------------------+---------+----------+---------+
| 商品名称               | 商品产地 | 商品类型 | 商品售价 |
+-----------------------+---------+----------+---------+
| 变形金刚-擎天柱        | 中国     | 玩具     |      50 |
| 变形金刚-霸天虎        | 中国     | 玩具     |      45 |
·······

4. 更新视图

视图像表一样有插入、修改、删除表中数据的操作，用法与表的操作一样，使用insert into、update和delete关键字。下面试一下修改数据

update view_sellSet set 商品售价=20 where 商品名称='变形金刚-擎天柱';

因为视图是一个虚拟表，对视图的增删改操作，实际上是对其基本表的增删改，重新查询commodity表里的信息，发现1号商品的售价更新为20了

select * from commodity;
+------+-------------------+----------+--------+-----------+------------+-------+
| c_id | c_name            | c_madein | c_type | c_inprice | c_outprice | c_num |
+------+-------------------+----------+--------+-----------+------------+-------+
|    1 | 变形金刚-擎天柱    | 中国     |      1 |        20 |         20 |    60 |
|    2 | 变形金刚-霸天虎    | 中国     |      1 |        20 |         45 |    50 |
······

增加和删除操作也是同样的道理。但是需要注意的是，视图的数据更新有很多限制，详情可参考：视图更新的限制

如在这个案例中，视图是两张表连接起来的，增加和删除视图中一条数据时，都会涉及到原来的两张表，操作会报错；而修改操作，如果修改的字段只涉及到一张表的内容，是可以执行的，就像上面的例子，但如果同时改两张表的字段，如果下面代码第4行，也会报错，和增加时报错一样。

insert into view_sellSet (商品名称,商品产地,商品类型,商品售价) values (哈哈,哈哈,玩具,50);
ERROR 1393 (HY000): Can not modify more than one base table through a join view 'ishop.view_sellset'

update view_sellset set 商品类型='书籍',商品售价=10 where 商品名称='变形金刚-擎天柱';
ERROR 1393 (HY000): Can not modify more than one base table through a join view 'ishop.view_sellset'

delete from view_sellSet where 商品名称='变形金刚-擎天柱';
ERROR 1395 (HY000): Can not delete from join view 'ishop.view_sellset'

既然update只能影响到一张表，试过修改commodity表对应的列后，再试一下对commoditytype表的列的修改，需要修改的是商品类型

update view_sellset set 商品类型='书籍' where 商品名称='变形金刚-擎天柱';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

select * from view_sellset;
+-----------------------+---------+----------+----------+
| 商品名称               | 商品产地 | 商品类型 | 商品售价 |
+-----------------------+---------+----------+----------+
| 变形金刚-擎天柱        | 中国     | 书籍     |       20 |	# 修改成功
| 变形金刚-霸天虎        | 中国     | 书籍     |       45 |
······
| 儿童彩色铅笔           | 中国     | 文具     |       80 |
| 快乐家族文具礼盒       | 中国     | 文具     |      460 |
| java入门到精通         | 中国     | 书籍     |       66 |
| .net入门到精通         | 中国     | 书籍     |       59 |
······

话说一开始还认为只是第一条商品的类型会修改为书籍类型，后面的如“变形金刚-霸天虎”应该还是玩具类型，毕竟条件是 where 商品名称=‘变形金刚-擎天柱’。但结果跟想象中不一样，所有玩具都变成了书籍，但其中也还是有文具类型，并没有整张表被改，不是条件忘写了。
然后再看一下commoditytype表，才意识到，忘记实际被修改的是commoditytype表了，原本为玩具类型的所有商品，自然都被改成书籍类型了。

select * from commoditytype;
+-------+---------+
| ct_id | ct_name |
+-------+---------+
|     1 | 书籍    |
|     2 | 文具    |
|     3 | 书籍    |
|     4 | 服装    |
+-------+---------+
4 rows in set (0.00 sec)

5. 修改和删除视图

（1）修改
ALTER VIEW 视图名 AS 创建视图的语句
AS后要加完整的查询语句，这基本上就相当于重新创建一个视图了。

（2）删除
DROP VIEW [IF EXISTS] 视图名[,视图名2]…
与删除database和删除table类似

drop view view_sellset;
Query OK, 0 rows affected (0.00 sec)

select * from view_sellset;
ERROR 1146 (42S02): Table 'ishop.view_sellset' doesn't exist

触发器

• What：触发器（trigger）是用来保证数据完整性的一种方法，它的执行不是由程序调用，也不是手工启动，而是由事件来触发。
• Why：触发器可以在修改了某张表里的数据后，把这张表使用到的基础表里的数据也同步更新成最新的信息。
• How：触发器创建语法四要素：
  1. 监视地点(table)
  2. 监视事件(insert/update/delete)
  3. 触发时间(after/before)
  4. 触发事件(insert/update/delete)

具体使用步骤为：（以监视新增订单为例）
（1）先修改命令结束标识符

delimiter $

在sql语句中，默认命令结束标识符是分号 “ ; ” ，除此之外一般用的就是“$”。
因为在写触发后需要执行的命令（触发事件）时，也会用到命令结束标识符，但在创建触发器的过程中显然不能让它执行，因此临时修改一下命令结束标识符。
（2）创建触发器

create trigger tri_1 
# tri_1 为触发器的名字，命名规则一般为tri_后数字依次增加
after insert on `order` 
# 格式：触发时间 监视事件 on 表名(监视地点)
# 这里就是指定监视order表中有新数据insert进来的事件
for each row 
# 固定语法，意思是监视每一行
begin # 触发事件开始
update commodity set c_num=c_num-new.c_num where c_id=new.o_cid; 
# 具体的触发事件，即要触发器自动做什么操作
# 这里是把商品表中的数量修改为新增的订单中的c_num，即购买的数量
end$ # 触发事件结束，触发器创建完成。这里要注意用现在的命令结束标识符

上面代码中有一个new关键字，它保存了新增到order表中的新数据的所有字段信息
与之对应的还有一个old关键字，在监视delete删除事件时，可以保存被删除了那条旧数据的所有字段信息
而当监视update事件时，old保存更新前的数据，new保存更新后的数据

（3）触发器创建完成后，记得把命令结束标识符改回来

delimiter ; 

（4）以下是验证触发器的作用，以购买12号商品为例

# 先查看12号商品的库存
select c_name,c_num from commodity where c_id=12;
+---------------------+-------+
| c_name              | c_num |
+---------------------+-------+
| 乐高玩具-蝙蝠侠纪念版 |     6 |
+---------------------+-------+
1 row in set (0.01 sec)

# 新增一个订单到`order`表，购买数量为2个
insert into `order` (o_cuid,o_cid,o_num) values (1,12,2);

# 再查看商品表中，12号商品的库存就减少了2个
select c_name,c_num from commodity where c_id=12;
+---------------------+-------+
| c_name              | c_num |
+---------------------+-------+
| 乐高玩具-蝙蝠侠纪念版 |     4 |
+---------------------+-------+
1 row in set (0.00 sec)

（5）同样，删除和更新订单时的触发器类似

delimiter $
create trigger tri_2
before delete on `order` # 删除订单的触发器
for each row
begin
update commodity set c_num=c_num+old.o_num where c_id=old.o_cid;
end$

create trigger tri_3
after update on `order`  # 更新订单的触发器
for each row
begin
update commodity set c_num=c_num+old.o_num-new.o_num where c_id=old.o_cid;
end$
delimiter ;

其中，更新订单的触发器需要更新库存数量，其实就是把更新前的旧订单购买的数量还回去（+old.o_num），再加上更新后的新订单购买的数量（-new.o_num）。

（6）查看触发器

show triggers; 

可以使用show命令查看已有的触发器详细信息，记得要加s，跟show databases和show tables一样。在显示出来的列表中，能找到触发器的四要素、创建时间等信息。

存储过程（以及变量，if else语句）

• What：存储过程（procedure）是用来存储完成一定操作的一组SQL语句，相当于Java里面的方法，也和方法一样可以有参数和返回值
• Why：存储过程能大大提高效率（存储过程本身执行速度非常快，调用存储过程大大减少数据库交互次数）；提高代码重用性
• How：创建存储过程的步方法

1.带in参数创建，用create关键字

 # 创建存储过程，格式为 create procedure 存储过程的名称 （in 参数名称 参数类型）
create procedure pro_1(in cid int(11))
begin # 标记存储过程执行语句的开始
select * from commodity where c_id=cid;
end$ # 存储过程结束

2.存储过程的调用，用call关键字

call 存储过程的名称（参数），参数记得要一一对应

call pro_1(12);

3.带in和out参数创建

create procedure pro_2(in cid int(11),out cnum int(11))
begin
set cnum=(select c_num from commodity where c_id=cid);
end$

因为有一个out参数，即存储过程的返回值，调用时就需要有一个变量来接这个值，在sql中，变量用@表示（使用时也需要带上@符号），用set关键字来定义变量

set @num=-1; # 声明变量并给变量赋初始值
call pro_2(12,@num); # 调用pro_2存储过程，out参数传入这个变量
select @num; # 输出变量

在MySQL中，所有的变量都是字符串。但也可以进行运算，MySQL会尝试进行数值的转换，如果转换不成功则抛出异常。

4. 删除存储过程

drop procedure 存储过程名称

5. 案例

在这个案例中，可以使用存储过程和事务完成订单数据的新增操作。存储过程可以省略新增订单时需要写的insert语句，每次新增只需要用call来调用；事务可以使订单新增后，如果库存不足，即c_num<0，则回滚，撤销新增订单的操作。

# 先修改命令结束标识符
delimiter $ 
# 创建存储过程，参数就是新增一个订单时需要的参数
create procedure pro_add(in in_cuid int(11),in in_cid int(11),in in_num int(11))
begin
# 使用flag变量标记新增订单是否成功
set @flag=0;
set autocommit=0; # 关闭自动提交
start transaction; # 标记事务起始点
# 新增一条订单的语句
insert into `order` (o_cuid,o_cid,o_num) values (in_cuid,in_cid,in_num);
# 使用num变量获取新增订单后的库存
# 这里由于之前已经创建过触发器了，新增订单后库存会自动更新
set @num=(select c_num from commodity where c_id=in_cid);
# 用if语句判断库存值是否为正
if @num>=0
# 如果是，则提交，flag变量设为1
then commit;
set @flag=1;
# 否则回滚，flag变量设为0
else rollback;
set @flag=0;
end if; # 标记if语句的结束，重要！
# 使用if语句判断flag的值，显示新增结果，这里的语法相当于JAVA中的三目运算符
select if(@flag=0,'商品库存不够，新增订单失败','新增订单成功') as 新增结果;
set autocommit=1; # 重新开启自动提交
end$ # 存储过程结束
delimiter ; # 把命令结束标识符改回来

创建好存储过程后，用call调用测试，已知12号商品当前库存为2，如果购买数量大于库存

call pro_add(1,12,3);
+--------------------------+
| 新增结果                 |
+--------------------------+
| 商品库存不够，新增订单失败 |
+--------------------------+
1 row in set (0.17 sec)

select * from `order`;
+------+--------+-------+-------+
| o_id | o_cuid | o_cid | o_num |
+------+--------+-------+-------+
|    1 |      1 |    23 |     3 |
|    2 |      1 |    32 |     2 |
|    3 |      2 |    23 |     2 |
|    4 |      3 |    23 |     2 |
|    5 |      3 |    32 |     2 |
|    9 |      1 |    12 |     3 |
|   11 |      1 |    12 |     1 |
+------+--------+-------+-------+
7 rows in set (0.00 sec)

如果购买数量不超过库存

call pro_add(1,12,2);
+-------------+
| 新增结果     |
+-------------+
| 新增订单成功 |
+-------------+
1 row in set (0.12 sec)

select * from `order`;
+------+--------+-------+-------+
| o_id | o_cuid | o_cid | o_num |
+------+--------+-------+-------+
|    1 |      1 |    23 |     3 |
|    2 |      1 |    32 |     2 |
|    3 |      2 |    23 |     2 |
|    4 |      3 |    23 |     2 |
|    5 |      3 |    32 |     2 |
|    9 |      1 |    12 |     3 |
|   11 |      1 |    12 |     1 |
|   14 |      1 |    12 |     2 |  # 新增了一条订单
+------+--------+-------+-------+
8 rows in set (0.00 sec)

select c_name,c_num from commodity where c_id=12;
+---------------------+-------+
| c_name              | c_num |
+---------------------+-------+
| 乐高玩具-蝙蝠侠纪念版 |     0 |	# 商品库存减少了
+---------------------+-------+
1 row in set (0.00 sec)

测试通过了。
另外，这里也证实了另一点， order中的o_id有个自增约束，这里测试过如果在事务中增加订单，之后回滚了，相当于没有增加订单，但自增的o_id的空缺不会补上。

修改订单同样也可以使用事务和存储过程来实现

delimiter $
create procedure pro_update(in in_oid int(11),in in_num int(11))
begin
	set @flag=0;
	set autocommit=0;
	start transaction;
		update `order` set o_num=in_num where o_id=in_oid;
		set @num=(select c_num from commodity where c_id=(select o_cid from `order` where o_id=in_oid));
		if @num>=0
			then commit;
			set @flag=1;
		else rollback;
			set @flag=0;
		end if;
		select if(@flag=0,'商品库存不够，修改订单失败','修改订单成功') as 修改结果;
		set autocommit=1;
end$
delimiter ;

范式

• 范式其实就是设计数据库时需要遵循的一种规范，使数据库结构更合理。
• 数据库设计有三大范式：
  第一范式(确保每列保持原子性)
  第二范式(确保表中的每列都和主键相关)
  第三范式(确保每列都和主键列直接相关,而不是间接相关)
  具体可见解释：数据库设计三大范式
• 满足后一个范式的前提是已经满足前面所有的范式
• 第二范式和第三范式更通俗易懂的理解，第二范式就是一张数据表至少有一个主键，第三范式就是一张数据表有且只有一个主键

最后

以上就是怕孤单摩托为你收集整理的【数据库】视图，触发器，存储过程数据库笔记 day04的全部内容，希望文章能够帮你解决【数据库】视图，触发器，存储过程数据库笔记 day04所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。