MySQL学习日记4

矫情吗；* 2024-04-17 05:42 100阅读 0赞

# MySQL学习日记4 #

## 视图 ##

### 1. 视图的定义 ###

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

> 视图（view）是在基本表之上建立的表，是不存在的表。基本表是实表。  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d4d3h5aA_size_16_color_FFFFFF_t_70]

### 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. 触发器的创建 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d4d3h5aA_size_16_color_FFFFFF_t_70 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 ;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d4d3h5aA_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/fe91be10558f49208a07b6f664214f98.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d4d3h5aA_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/d7c0a167ef574132b272875dede1e893.png