MySQL高级查询与编程笔记 • 【第4章 MySQL编程】

古城微笑少年丶 2022-12-28 01:31 123阅读 0赞

# [全部章节   >>>>][Link 1] #

--------------------

**本章目录**

4.1 用户自定义变量

4.1.1 用户会话变量

4.1.2 用户会话变量赋值

4.1.3 重置命令结束标记

4.1.4 实践练习

4.2 存储过程

4.2.1 局部变量

4.2.2 存储过程介绍

4.2.3 创建和执行存储过程的语法

4.2.4 不带参数存储过程

4.2.5 带输入参数存储过程

4.2.7 实践练习

4.3 条件控制语句

4.3.1 if 语句

4.3.2 case 语句

4.3.3 exists 子查询的用法

4.3.3 while 语句

4.3.4 leave 语句

4.3.5 iterate 语句

4.3.6 实践练习

4.4 游标

4.4.1 游标介绍

4.4.2 MySQL 中使用游标的步骤

4.4.3 游标的使用

4.4.4 实践练习

总结

--------------------

# 4.1 用户自定义变量 #

## 4.1.1 用户会话变量 ##

用户自定义变量用于存储 MySQL 存储程序运行期间所产生的临时变量，它分为用户会话变量和局部变量

用户会话变量（User Session Variable）是在每次 MySQL 客户与 MySQL 服务器建立一个新的连接后，由MySQL 客户进行定义的一种变量，该变量与“当前会话”有密切关系

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70][]

MySQL 用户会话变量以一个“@”开头，并且大小写不敏感。一般情况下，用户会话变量的定义和赋值会同时进行

使用 set 命令和 select 语句，可以对用户会话变量进行定义和赋值 使用 set 命令定义用户会话变量的语法格式如下

**语法：**

set @user_variable1=expression1[,@user_variable2=expression2, …]

> user\_variable1、user\_variable2 为用户会话变量名；expression1、expression2 可以是常量、变量和表达式

**示例：**

使用 set 命令创建 MySQL 用户会话变量 @user\_name 以及 @age，并为其赋值，然后使用 select 语句输出上述变量的值

set @user_name= ' 张三 ';
    set @age=18;
    select @user_name, @age;
    set @age=@age+1;
    select @user_name, @age;

使用 select 语句定义用户会话变量的语法格式如下

**语法：**

第一种：“select @user\_variable1:=expression1\[,@user\_variable2:=expression2, ... \]”

第二种：“select expression1 into @user\_variable1, expression2 into @user\_variable2, ...”

> 第一种语法格式与第二种语法格式的区别在于：
> 
> 第一种语法格式中的 select 语句会产生结果集，第二种语法格式中的select 语句仅用于会话变量的定义及赋值，但不会产生结果集。

**示例：**

使用select 语句创建 MySQL 用户会话变量 @user\_name，并为其赋值，然后使用 select 语句输出该变量的值

select @user_name:='zhangsan';
    select @user_name;
    select'zhangsan'into @user_name;
    select @user_name;

## 4.1.2 用户会话变量赋值 ##

检索数据时，如果 select 语句的结果集是单个值，可以将 select 语句的返回结果赋予用户会话变量

**示例：**

统计“零聚网”商品和服务的数量，并将数量赋给用户会话变量 @productNum，并输出该变量值

方法 1：set+ 子查询

set @productNum=(select count(*) from product)
    select @productNum

方法 2：select+ 子查询

select @productNum:=(select count(*) from product)

方法 3：去掉子查询的简化形式（最常见）

select @productNum:=count(*) from product （简化形式 1）
    select count(*) into @productNum from product（简化形式 2）
    select count(*) from product into @productNum（简化形式 3）

## 4.1.3 重置命令结束标记 ##

begin-end 语句块中通常存在多条 MySQL 表达式，每条 MySQL 表达式都使用“;”作为结束标记

在 MySQL客户机上输入 MySQL 命令或 SQL 语句时，默认情况下 MySQL 客户机也是使用“;”作为 MySQL 命令的结束标记

由于 begin-end 语句块中的多条 MySQL 表达式密不可分，为了避免这些 MySQL 表达式被拆开，需要重置 MySQL 客户机的命令结束标记，亦称命令分隔符（delimiter）

通过重置命令结束标记，显示商品和服务的大类类型信息，大类的p\_categoryID 为空

**示例：**

delimiter $$
    select * from category where p_categoryID is null $$
    delimiter ;
    select * from category where p_categoryID is null;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 1][]

## 4.1.4 实践练习 ##

# 4.2 存储过程 #

## 4.2.1 局部变量 ##

局部变量（local variable）必须定义在存储程序中，如函数、存储过程、触发器以及事件中，而且局部变量的作用范围仅局限于存储程序中。如果脱离存储程序，局部变量将没有丝毫意义

**语法：**

declare 局部变量 数据类型 ;

declare price decimal(8,2); declare address varchar(20);

局部变量主要应用于以下 3 种场合

场合 1：局部变量定义在存储程序的 begin-end 语句块之间时，局部变量必须先进行 declare 命令定义，并且必须指定其数据类型。只有定义局部变量后，才可以使用 set 命令或 select 语句为其赋值。

场合 2：局部变量作为存储过程或函数的参数使用时，虽然不需要使用 declare 命令定义，但需要指定参数的数据类型。

场合 3：局部变量也可以用于存储程序的 SQL 语句中。数据检索时，如果 select 语句的结果集是单个值，则可以将 select 语句的返回结果赋予局部变量。局部变量也可以直接嵌入到 select 语句、insert 语句、update语句以及 delete 语句的表达式中

## 4.2.2 存储过程介绍 ##

存储过程是一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中

用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

一个存储过程其实就是一个可编程的函数（函数有返回值，存储过程没有返回值），它在数据库中创建并保存，并由 SQL 语句和一些特殊的控制结构所组成。

当希望在不同的应用程序或平台上执行相同的功能，或者封装特定的功能时，使用存储过程是非常实用的解决之道

**存储过程的优点主要包括以下 5 点**

*  增强了 SQL 语言的功能性和灵活性
 *  存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的
 *  SQL 语句 能实现较快的执行速度
 *  能减少网络流量 还可被作为一种安全机制来充分利用

## 4.2.3 创建和执行存储过程的语法 ##

创建存储过程的语法格式如下

**语法：**

> 语法说明如下：
> 
> 存储过程的参数是局部变量。
> 
> in 代表输入参数（默认为 in 参数），表示该参数的值必须由调用程序指定。
> 
> out 代表输出参数，表示经过存储过程的计算后，将 out 参数的计算结果返回给调用程序。
> 
> inout 代表既是输入参数又是输出参数，表示该参数的值既可以由调用程序指定，又可以将该参数的计算结果返回给调用程序。

执行存储过程的语法格式如下

**语法：**

call  存储过程名 ( 参数列表 )

> 如果存储过程有参数，则需向存储过程传递 in 参数、out 参数或 inout 参数

## 4.2.4 不带参数存储过程 ##

**示例：**

创建一个名为 proc\_product\_info 的存储过程，其将获取所有商品的标题、类型名、团购价、地区名和商店名，并按照类型和团购价升序显示

begin      
    select title, categoryName, currentPrice, areaName, shopName from product p,
    category c, Area a, Shop s where p.categoryID=c.categoryID and p.areaID=a.areaID and p.shopID=s.shopID order by categoryName, currentPrice;
    end
    $$
    delimiter ;

在 Navicat For MySQL 单击“工具” “命令行界面”，出现 MySQL 命令行界面，在该界面输入上述存储过程的创建代码，按回车键即成功创建了存储过程 proc\_product\_info

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 2][]

在 Navicat For MySQL 的“函数”处可见 proc\_product\_info，表明 proc\_product\_info 已创建成功。单击需要操作的存储过程，如 proc\_product\_info，点击“设计函数”即可对该存储过程的代码进行编辑

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 3][]

**运行存储过程的两种方式如下**

*  在存储过程的编辑窗口，点击“运行”
 *  在 MySQL 命令行输入“call 存储过程名”，本例即为“call proc\_product\_info;”

**示例：**

创建一个名为 proc\_ProductStatistics 的存储过程，将获取不同类型的商品服务的个数和平均团购价

begin
    select categoryName 商品类型名 , count(p.productID) 商品数量 , avg(currentPrice)
    平均团购价 from product p, category c where p.categoryID=c.categoryID
    group by categoryName order by 平均团购价 ;
    end
    $$
    delimiter ;

## 4.2.5 带输入参数存储过程 ##

**示例：**

创建一个名为 proc\_OrdersGivenCustomer 的存储过程，将获取指定客户在指定日期之后的订购信息，要求输出客户姓名、订单编号、下单日期、商品标题和团购价，按照订单编号和团购价升序显示

delimiter $$
    create procedure proc_OrdersGivenCustomer(
    in _customerName varchar(20),
    in _ordersDate date
    )

> 需要两个输入参数：一个用于接收客户信息（本题为客户姓名）；另一个用于接收指定日期

reads sql data
    begin
    select customerName 客户姓名 , o.ordersID 订单号 , ordersDate 下单日期 ,
    title 商品标题 , currentPrice 团购价
    from customer c, orders o, ordersDetail od, product p
    where c.customerID=o.customerID and o.ordersID=od.ordersID
    and od.productID=p.productID and c.customerName=_customerName
    and ordersDate>_ordersDate order by o.ordersID, currentPrice;
    end
    $$
    delimiter ;

执行该存储过程，用于获取姓名为“雷亚波”的客户于 2019 年 3 月 31 日之后的订购信息，执行存储过程 proc\_OrdersGivenCustomer

set @customerName= ' 雷亚波 ';
    set @ordersDate= '2019-3-31 ';
    call proc_OrdersGivenCustomer(@customerName, @ordersDate);

> 执行带参数的存储过程时，传入值的类型、个数和顺序都需要与存储过程中定义的参数逐一对应。

如果需要存储过程返回一个或多个值，则可通过使用输出参数来实现。输出参数必须在创建存储过程时，使用out 关键字进行声明

**示例：**

创建一个名为 proc\_MaxPriceGivenCategory 的存储过程，其将获取指定类型的商品的最高团购价

delimiter $$
    create procedure proc_MaxPriceGivenCategory(
    _categoryName varchar(20),
    out _maxPrice decimal   )

> 需定义两个参数：一个为输入参数，用于接收指定商品类型信息（本题为类型名）；另一个为输出参数，用于输出指定类型的商品的最高团购价

reads sql data
    begin
    select max(currentPrice) into _maxPrice from product p, category c
    where p.categoryID=c.categoryID and categoryName=_categoryName;
    end
    $$
    delimiter ;

执行该存储过程

set @categoryName=' 火锅 ';
    call proc_MaxPriceGivenCategory(@categoryName, @maxPrice);
    select concat(@categoryName,' 火锅类商品的最高团购价是 ',@maxPrice,' 元 ') 显示结果 ;

## 4.2.7 实践练习 ##

# 4.3 条件控制语句 #

## 4.3.1 if 语句 ##

MySQL 提供了简单的流程控制语句，其中包括条件控制语句以及循环语句。这些流程控制语句通常放在 begin-end 语句块中使用

条件控制语句分为两种：一种是 if 语句，另一种是 case 语句

if 语句根据条件表达式的值确定执行不同的语句块

**语法：**

if 条件表达式 1 then 语句块 1;
    [elseif 条件表达式 2 then 语句块 2]…
    [else 语句块 n]
    end if;

创建一个 proc\_MaxPriceGivenCategory2 的存储过程，其将获取指定类型的商品的最高团购价，并依据最高团购价的不同范围显示相关信息

*  价格大于等于 100，则显示“价格高昂”
 *  价格大于等于 50，并小于 100，则显示“价格适中”
 *  其他价格，则显示“价格低廉”

delimiter $$
    create procedure proc_MaxPriceGivenCategory2(
    _categoryName varchar(20),
    out _maxPrice decimal,
    out _message varchar(20) -- 信息显示
    )
    reads sql data

begin
    select max(currentPrice) into _maxPrice from product p, category c
    where p.categoryID=c.categoryID and categoryName=_categoryName;
    if	_maxPrice>=100	then	set _message=' 价格高昂 ';
    elseif	_maxPrice>=50 and _maxPrice<100	then	set _message=' 价格适中 ';
    else	set _message=' 价格低廉 ';		
    end if;
    end
    $$
    delimiter ;

执行该存储过程

set @categoryName = ' 火锅 ';
    call proc_MaxPriceGivenCategory2(@categoryName, @maxPrice, @message);
    select concat(@categoryName, ' 火锅类商品的最高团购价是 ',@maxPrice, ' 元，',@message) 结果显示 ;

## 4.3.2 case 语句 ##

case 语句用于实现比 if 语句分支更为复杂的条件判断

**语法：**

case
      when  表达式 1  then 语句块 1
      when  表达式 2  then 语句块 2
      …
      else 语句块 n
    end;

获取每种类型商品的平均团购价，并依据最高团购价的不同范围显示相关信息

*  价格大于等于 100，则显示“价格高昂”
 *  价格大于等于 50，并小于 100，则显示“价格适中”
 *  其他价格，则显示“价格低廉”

## 4.3.3 exists 子查询的用法 ##

select categoryName 类型 , avg(currentPrice) 平均团购价 ,
    case
    when avg(currentPrice)>=100 then ' 价格高昂 '
    when avg(currentPrice)>=50 and avg(currentPrice)<100 then ' 价格适中 '
    else ' 价格低廉 '
    end ' 价格范围 '
    from product p, category c
    where p.categoryID=c.categoryID group by categoryName

## 4.3.3 while 语句 ##

当条件表达式的值为 true 时，反复执行循环体，直到条件表达式的值为 false

**语法：**

[ 循环标签 :]while 条件表达式 do
      循环体 ;
    end while[ 循环标签 ];

实现从 1 ～ 50 的累加

declare total int default 0;
    declare num int default 0;
    while num<=50 do
    set total=total +num;
    set num=num+1;
    end while;
    select total;

## 4.3.4 leave 语句 ##

**leave** 语句用于跳出当前的循环语句，如 while 语句，它的作用等同于高级编程语言中的 break 语句

**语法：**

leave 循环标签 ;

实现从 1 ～ 50 的累加

declare total int default 0;
    declare num int default 0;
    add_num: while true do
    if(num>50) then
    leave add_num;
    end if;
    set total=total+num;    set num=num+1;
    end while add_num;

## 4.3.5 iterate 语句 ##

**iterate** 语句用于跳出本次循环，进而进行下次循环，它的作用等同于高级编程语言中的 continue 语句

**语法：**

iterate 循环标签 ;

实现从 1 ～ 50 的累加

declare sum int default 0;
    declare num int default 0;
    add_num: while true do
    if(num%2=0) then
    set sum=sum+num;
    else
    set num=num+1;
    iterate add_num;
    end if;

set num=num+1;
    if(num>50) then
    leave add_num;
    end if;
    end while add_num;

## 4.3.6 实践练习 ##

# 4.4 游标 #

## 4.4.1 游标介绍 ##

数据库开发人员在编写存储过程等存储程序时，有时需要使用存储程序中的 SQL 代码扫描 select 结果集中的数据，并要求对该结果集中的每条记录进行一些简单的处理。此类问题完全可以通过数据库的游标机制加以解决

游标本质上是一种能从 select 结果集中每次提取一条记录的机制，因此游标与 select 语句息息相关

现实生活中，在电话簿中寻找某个人的电话号码时，可能会用“手”每条逐行扫过，以帮助我们找到所需的号码。此情形与游标的模型非常类似，即“电话簿”如同查询结果集，“手”类似于游标

## 4.4.2 MySQL 中使用游标的步骤 ##

游标的使用可以概括为声明游标、打开游标、从游标中提取数据和关闭游标 4 个步骤

**声明游标需要使用 declare 语句**

**语法：**

open 游标名 ;

> 使用 open 语句打开游标后，与游标对应的 select 语句将被执行，MySQL 服务器内存中将存放与 select 语句对应的结果集。

**从游标中提取数据需要使用 fetch 语句**

**语法：**

fetch 游标名 into 变量名 1, 变量名 2 , … ;

> 变量名的个数和类型，必须与声明游标时使用的 select 语句结果集中的字段个数和类型保持一致。
> 
> 第一次执行 fetch 语句时，将从结果集中提取第 1 条记录，再次执行 fetch 语句时，将从结果集中提取第 2条记录……以此类推，fetch 语句每次从结果集中仅仅提取一条记录，因此 fetch 语句需要循环语句的配合才能实现整个结果集的遍历。
> 
> 当使用 fetch 语句从游标中提取最后一条记录后，再次执行 fetch 语句时，将产生“error 1329(0200):no data to fetch”的错误信息。数据库开发人员可以针对 MySQL 错误代码 1329 自定义错误处理程序，以便结束结果集的遍历。

**关闭游标需要使用 close 语句**

**语法：**

close 游标名 ;

> 关闭游标的作用在于释放游标打开时产生的结果集，从而节省 MySQL 服务器的内存空间。游标如果没有被显式关闭，那么它将在被打开的 begin-end 语句块的末尾处关闭。

## 4.4.3 游标的使用 ##

**示例：**

在“优乐网”营销的商家为了促进消费，拟将单次消费金额在 100 元以上（含 100 元）的订单打 95 折

（1）连接订单明细表和商品表，计算出每个订单的金额

select ordersID, sum(currentPrice*quantity) 金额 from ordersdetail od, product p
    where od.productID=p.productID group by ordersID

（2）使用子查询在订单表中生成每个订单的金额

update orders o join
    (select ordersID, sum(currentPrice*quantity) 金额 from ordersdetail od, product p
    where od.productID=p.productID group by ordersID) A
    on o.ordersID=A.ordersID set amount=A. 金额

（3）编写存储过程，在其中生成一个查询结果集为所有订单金额的游标，通过遍历游标结果集中的每一条订单，可对满足条件的（金额不小于 100）订单金额进行更新，即可实现需求

delimiter $$
    create procedure proc_AmountDiscount()
    modifies sql data
    begin
    declare _ordersID int;
    declare _amount decimal(10,2);
    declare state varchar(20);
    declare amount_cursor cursor for select ordersID, amount from orders;
    -- 错误处理程序的定义必须放在所有变量及游标定义之后，并且放在其他所有 MySQL 表达式之前

declare continue handler for 1329 set state='error';
    open amount_cursor;
    discount:while true do
    fetch amount_cursor into _ordersID, _amount;
    if(state='error') then
    leave discount;
    end if;

（4）执行存储过程“call AmountDiscount;”，则将单次消费金额在 100 元以上（含 100 元）的订单金额调整为原价的 95%。

if(_amount>=100) then
    update orders set amount=amount*0.95 where ordersID=_ordersID;
    end if;
    end while discount;
    close amount_cursor;
    end
    $$
    delimiter ;

## 4.4.4 实践练习 ##

# 总结 #

*  使用 set 命令和 select 语句，可以对用户会话变量进行定义和赋值
 *  创建存储过程时，数据库开发人员需提供存储过程名、存储过程的参数以及存储过程语句块（一系列 SQL 语句）等信息
 *  使用“ call  存储过程名 ( 参数列表 )“执行存储过程
 *  MySQL 提供了简单的流程控制语句，其中包括条件控制语句以及循环语句。这些流程控制语句通常放在 begin-end 语句块中使用
 *  游标的使用可以概括为声明游标、打开游标、从游标中提取数据和关闭游标 4 个步骤

[Link 1]: https://blog.csdn.net/weixin_44893902/article/details/110877635
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70]: /images/20221120/419ec67a7834413e979c1941b260e608.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201213141946822.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20201213142524305.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20201213142545329.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg5MzkwMg==,size_16,color_FFFFFF,t_70