MySQL学习日记4

MySQL学习日记4

视图

1. 视图的定义

视图是一个虚表，是从数据库中一个或者多个表中导出来的表。视图还可以从已经存在的视图的基础上定义。

视图（view）是在基本表之上建立的表，是不存在的表。基本表是实表。

2.视图的作用

• 简单性
• 安全性
• 逻辑数据独立性

3. 视图的使用场景

• 对复杂的sql语句进行封装
• 对于数据库敏感信息的保护（如上图中的视图，没有显示出C列与A1列的信息）

视图的创建

语法：
CREATE VIEW 视图名
AS
<select statement>
示例：
#制作一个视图提供给销售人员 他们需要知道商品名称 出售价格 商品的类型 产地等信息

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;

查看视图的基本信息

• DESCRIBE语句查看视图基本信息
  语法：DESCRIBE 视图名;
• SHOW TABLE STATUS语句查看视图基本信息
  语法：SHOW TABLE STATUS LIKE ‘视图名’;
• SHOW CREATE VIEW语句查看视图详细信息
  语法：SHOW CREATE VIEW 视图名;

视图的删，改，查

1.查看视图

使用select语句可以查看数据库中所有视图的详细信息。

 select * from view_sellset;

提问：视图是否能够进行增删改？是否会影响实表数据？
答：视图可以进行增删改，会直接影响实表的数据。
更新视图是指通过插入、更新、删除表中的数据，因为视图是一个虚拟表，其中没有数据。通过视图更新的时候都是转到基本表进行更新的，如果对视图增加或者删除记录，实际上是对其基本表增加或者删除记录

修改视图

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

删除视图

删除视图时必须拥有DROP权限
语法：
DROP VIEW [IF EXISTS]
视图名[,视图名2]…

---

触发器（trigger）

触发器的含义

触发器是一种特殊类型的存储过程，它在指定的表中的数据发生变化时自动生效。

触发器的作用

1.安全性。可以基于数据库的值使用户具有操作数据库的某种权利。
2.审计。可以跟踪用户对数据库的操作。
3.实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的 事务。当插入一个与其主健不匹配的外部键时，这种触发器会起作用。
4.同步实时地复制表中的数据。
5.自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。

触发器的使用

1. 触发器的创建

在触发器中触发事件，由于在触发事件以分号（命令结束标识符）结尾，所以在触发事件提交后，整个命令就执行了，这时还没运行到下面的end，整个触发器还没封装好。因此在这里需要修改命令结束标识符。当然在触发器使用结束后要记得改回来。

触发器new old

• insert语句，只有new是合法的。
• delete语句，只有old是合法的。
• update语句，new和old可以同时使用

例题1： 写一个触发器，当新增一条订单的时候，自动从商品表的库存中扣去相应的商品订单数量

delimiter $ #将命令提示符修改为$
create trigger tri_1
after insert on `order`
for each row #行级触发器
begin
update commodity set c_num=c_num-new.o_num where c_id=new.o_cid;
end$
Query OK, 0 rows affected (0.08 sec)#sql的提示

delimiter ;	#将命令提示符修改回来
select c_name,c_num from commodity where c_id=12;
+-----------------------+-------+
| c_name                | c_num |
+-----------------------+-------+
| 乐高玩具-蝙蝠侠纪念版 |     6 |
+-----------------------+-------+
1 row in set (0.00 sec)

例题2： 写一个触发器，当客户不想买了退货，自动从商品表的库存中增加相应的商品订单数量

delimiter $
create trigger tri_2
after delete on `order`
for each row
begin
update commodity set c_num=c_num+old.o_num where c_id=old.o_cid;
end$
Query OK, 0 rows affected (0.02 sec)

delimiter ;

例题3： 写一个触发器，当客户买多了，修改订单，自动从商品表的库存中修改成相应的商品订单数量

delimiter $
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$
Query OK, 0 rows affected (0.04 sec)

delimiter ;

---

存储过程(不常用)

概念

一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效。

作用

大大提高效率（存储过程本身执行速度非常快，调用存
储过程大大减少数据库交互次数）；提高代码重用性

存储过程的使用

创建存储过程

语法：
create procedure sp_name(proc_parameter[…])
begin
[characteristic …] routine_body
end

sp_name：存储过程名称
proc_parameter：存储过程的参数列表
characteristic：存储过程的特性
routine_body：SQL语句的内容，可以用begin…end来标志SQL语
句的开始和结束

1. 创建不带参数的存储过程
最简单的存储过程的案例 查询商品表

delimiter $	#将命令提示符修改为$
create procedure pro_queryall()
begin
select * from commodity;
end$
Query OK, 0 rows affected (0.03 sec)

delimiter ;	#将命令提示符修改回来
call  pro_queryall();	#调用存储过程

2.带参存储过程

• in参数的使用
  具有传入参数的存储过程 动态查询客户购 买的商品信息

delimiter $
create procedure pro_ask(in cu_id int(11))
begin
select c_name from commodity where c_id in (select o_cid from `order` where o_cuid=cu_id);
end$
Query OK, 0 rows affected (0.03 sec)

delimiter ;
call pro_ask(1);

• out参数的使用 具有传出参数的存储过程，动态查询某商品的库存

 delimiter $
 create procedure pro_askcnum(in cid int(11),out cnum int(11))
begin
select c_num into cnum from commodity where c_id=cid;
end$
Query OK, 0 rows affected (0.00 sec)

delimiter ;
 set @cn=-1;#定义一个变量用来接收存储过程传出的数据。
Query OK, 0 rows affected (0.00 sec)
#MySQL所有的变量都是字符串 当你进行运算的时候 MySQL会尝试进行数值的转换 如果转换不成功则抛出异常
call pro_askcnum(12,@cn);
Query OK, 1 row affected (0.00 sec)

select @cn;
+------+
| @cn  |
+------+
|    6 |
+------+
1 row in set (0.00 sec)

• INNOUT参数示例
  具有传入与传出参数的存储过程

CREATE PROCEDURE pro4(INOUT p_inout int)
BEGIN
select p_inout;
set p_inout=2;
select p_inout;
END

- 扩展：使用存储过程完成订单数据的新增，如果订单新增后，如果库存小于0 ，回滚
在做题之前，首先了解一下 if 语句

if(sva=1,“男”,“女”)，如果sva=1，则为男，否则为女;

select cu_name,cu_gender,if(cu_gender=1,'男','女')
from customer;
+---------+-----------+---------------------------+
| cu_name | cu_gender | if(cu_gender=1,'男','女') |
+---------+-----------+---------------------------+
| 刘德华  |         1 | 男                        |
| 张学友  |         1 | 男                        |
| 林志玲  |         0 | 女                        |
+---------+-----------+---------------------------+
3 rows in set (0.01 sec)

以下当c_num<=10 显示库存少 else 库存充足

select c_name,c_num,case when c_num<=10
then '库存少' else '库存充足' end
from commodity limit 20;

现在开始做题，使用存储过程完成订单数据的新增，如果订单新增后，如果库存小于0 ，回滚

DELIMITER ;;	#将命令提示符修改为;;
CREATE PROCEDURE `insertOrder`(in _o_cuid VARCHAR(50),in _o_cid VARCHAR(50),in _o_num INT,out flag INT(11))
BEGIN
	set @_c_num=0;
	set autocommit=0;	#关闭自动提交
	START TRANSACTION;	#事务开始标记
	INSERT INTO `order` (o_cuid,o_cid,o_num) VALUES (_o_cuid,_o_cid,_o_num);
	set @_c_num=(select c_num from commodity where c_id=_o_cid);
		IF @_c_num>=0 
			THEN COMMIT;	#事务提交
			SET flag = 1;
		else ROLLBACK;
			SET flag = 0;	#事务回滚
		END IF;
	set autocommit=1;	#开启自动提交
END
;;
DELIMITER ;	#将命令结束标识符改回来

DELIMITER ;;
CREATE PROCEDURE `updateOrder`(in _o_id INT,in _o_num INT,out flag INT(11))
BEGIN
	set @_c_num=0;
	set autocommit=0;
	START TRANSACTION;
	UPDATE `order` SET o_num=_o_num WHERE o_id=_o_id;
	set @_c_num=(select c_num from commodity where c_id=(SELECT o_cid FROM `order` WHERE o_id=_o_id));
		IF @_c_num>0 
			THEN COMMIT;
			SET flag = 1;
		else ROLLBACK;
			SET flag = 0;
		END IF;
	set autocommit=1;
END
;;
DELIMITER ;