视图_存储过程_游标的简单使用

数据库-必知必会 专栏收录该内容
11 篇文章 0 订阅

前言

1️⃣ :mysql环境准备

2️⃣ :简单的表查询

3️⃣ :通配符+正则表达式

4️⃣ :mysql函数与分组

5️⃣ :子查询_联结查询_组合查询

6️⃣ :mysql全文本搜索

7️⃣ :表的增删改查

第一部分:我们准备环境:安装数据库+创建数据库+创建用户+授权。

第二部分:环境准备好后,进行简单的单表查询:导入表+表查询。

第三部分:通配符和正则表达式,增强where的筛选功能。

第四部分:对列进行计算处理称为字段。复杂的字段用函数处理。按照不同组汇总,进行分组操作。

第五部分:在多表上进行查询:子查询+联结查询+组合查询。

第六部分:mysql的全文本搜索。

第七部分:表的删改查。

前七部分组成表的增删改查。 之后都是一个一个小模块的介绍。

现在是第八部分:参考《mysql必知必会》chapter 22~24,视图+存储过程+游标


摘要与总结

视图是虚拟的表;存储过程是一条或多条MySQL语句的集合;对于填有数据的游标,根据需要取出(检索)各行;

视图

推荐:深入解析MySQL视图VIEW

Q-A,来自上面链接。

Q:什么是视图?视图是干什么用的?

A:

  1. 视图(view)是一种虚拟存在的表,是一个逻辑表,本身并不包含数据。作为一个select语句保存在数据字典中的。

  2. 通过视图,可以展现基表的部分数据;视图数据来自定义视图的查询中使用的表,使用视图动态生成。

基表:用来创建视图的表叫做基表base table

Q:为什么要使用视图?

A:因为视图的诸多优点,如下

  1. 简单:使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件,对用户来说已经是过滤好的复合条件的结果集。

  2. 安全:使用视图的用户只能访问他们被允许查询的结果集,对表的权限管理并不能限制到某个行某个列,但是通过视图就可以简单的实现。

  3. 数据独立:一旦视图的结构确定了,可以屏蔽表结构变化对用户的影响,源表增加列对视图没有影响;源表修改列名,则可以通过修改视图来解决,不会造成对访问者的影响。

总而言之,使用视图的大部分情况是为了保障数据安全性,提高查询效率。

# 视图的创建
CREATE VIEW productcustomers AS
SELECT cust_name,cust_contact,prod_id 
FROM customers,orders,orderitems
WHERE customers.cust_id=orders.cust_id AND orders.order_num=orderitems.order_num
with check option; -- 对视图所做的DML操作的结果,不能违反视图的WHERE条件的限制。这里没啥用

# 使用视图和表类似
SELECT * FROM productcustomers
WHERE prod_id='TNT2';

需要注意的是:因为视图本身没有数据,因此对视图进行的dml操作最终都体现在基表中

对于视图的修改,必须正确的确定被更新的基数据,才能通过视图更新作用到基表上。

所以对于计算列之类,无法确定更新到基数据上的dml是无法(无法?)操作的。



存储过程

推荐:MySQL存储过程的创建及调用

参考:MySQL存储过程与函数

Q:什么是存储过程?
存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件,虽然它们的作用不仅限于批处理。

Q:存储过程的作用

  1. 通过把处理封装在容易使用的单元中,简化复杂的操作
  2. 统一接口,确保数据的安全

Q:相关的操作(基本都是那一套,创建/查看/使用/修改/删除)

创建存储过程、查看当前所用于的存储过程、执行存储过程、删除存储过程

-- 存储过程
-- name:ordertotal
-- parameters: ordernum=order_num
--             taxable = 0 or 1 
--             ototal = order total varibale
DELIMITER $$
drop procedure if exists ordertotal$$

create procedure ordertotal(
	in ordernum int,
    in taxable boolean,
    out ototal decimal(8,2)
)comment 'get total variable with tax'
begin
	-- declare varibale for total
    declare total decimal(8,2);
    -- declare tax precent
    declare tax_prec int default 6;
    
    -- get total variable without tax
	select sum(quantity*item_price)
    from orderitems
    where order_num=ordernum
    into total;
    
    -- is taxable
    if taxable then
		select total+(total/100*tax_prec) into total;
	end if;
	
    -- return total with tax
    select total into ototal;
end$$
DELIMITER ;

call ordertotal(20005,1,@total);
select @total;


游标

推荐:MySQL游标的简单实践
参考:mysql游标最后一行重复的问题

Q:为什么需要游标

MySQL检索操作返回一组称为结果集的行。这组返回的行都是与SQL语句相匹配的行(零行或多行)。使用简单的SELECT语句,例如,没有办法得到第一行、下一行或前10行,也不存在每次一行地处理所有行的简单方法(相对于成批地处理它们)。有时,对于填有数据的游标,根据需要取出(检索)各行。。这就是使用游标的原因。

我想了下,一行一行的检索交给where来处理,检索出来的操作可以给列来处理,相对而言就可以替代游标的效果了。不知道是不是这样。

Q:什么是游标

游标( cursor) 是一个存储在MySQL服务器上的数据库查询,它不是一条SELECT语句,而是被该语句检索出来的结果集。 (这个恰好和视图是相反的)

Q:游标的操作

创建,打开,使用,关闭

-- 游标
-- 将total with tax单独放入一个表中
delimiter $$
drop procedure if exists prosessorders$$

create procedure prosessorders()
begin
	
    -- 声明一个boolen变量,用于游标终止循环
    declare done boolean default 0;
	-- 声明一个变量存储total
    declare total decimal(8,2);
    -- 声明一个变量从游标每次取值
    declare ordernum int;
    
	-- 声明个游标
    declare ordernums cursor
    for
    select order_num from orders;
    -- declare continue handler
    -- declare continue handler for sqlstate '02000' set done=1;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; 
    
    -- 创建一个表,保存totals
    drop table if exists ordertotals;
    create table if not exists ordertotals
    (order_num int,total decimal(8,2));
    
    -- 打开游标
    open ordernums;

    -- loop through all the row
    repeat
		fetch ordernums into ordernum;
        
        if !done then
			-- 根据order_num,从orderitems中获取totals
			call ordertotal(ordernum,1,total);
			
			-- 把计算出来的结果,插入表中
			insert into ordertotals(order_num,total)
			values(ordernum,total);
		end if;
	until done end repeat;

    -- 关闭游标
    close ordernums;
end$$
delimiter ;

call prosessorders();
select * from ordertotals;--  order by order_num;


参考文章

深入解析MySQL视图VIEW

MySQL存储过程的创建及调用

MySQL存储过程与函数

MySQL游标的简单实践

mysql游标最后一行重复的问题

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 书香水墨 设计师:CSDN官方博客 返回首页

打赏作者

大1234草

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值