数据库笔记 day04

/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 asselect 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_idwhere 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: 0select * 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：触发器创建语法四要素： 监视地点(table)监视事件(insert/update/delete)触发时间(after/before)触发事件(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_2before delete on `order` # 删除订单的触发器for each rowbeginupdate commodity set c_num=c_num+old.o_num where c_id=old.o_cid;end$create trigger tri_3after update on `order` # 更新订单的触发器for each rowbeginupdate 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))beginset 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变量设为1then commit;set @flag=1;# 否则回滚，flag变量设为0else 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))beginset @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>=0then commit;set @flag=1;else rollback;set @flag=0;end if;select if(@flag=0,'商品库存不够，修改订单失败','修改订单成功') as 修改结果;set autocommit=1;end$delimiter ;

范式

范式其实就是设计数据库时需要遵循的一种规范，使数据库结构更合理。数据库设计有三大范式：

第一范式(确保每列保持原子性)

第二范式(确保表中的每列都和主键相关)

第三范式(确保每列都和主键列直接相关,而不是间接相关)

具体可见解释：数据库设计三大范式满足后一个范式的前提是已经满足前面所有的范式第二范式和第三范式更通俗易懂的理解，第二范式就是一张数据表至少有一个主键，第三范式就是一张数据表有且只有一个主键