MySQL高级查询与编程笔记 • 【第4章 MySQL编程】
全部章节 >>>>
本章目录
4.1 用户自定义变量
4.1.1 用户会话变量
用户自定义变量用于存储 MySQL 存储程序运行期间所产生的临时变量,它分为用户会话变量和局部变量
用户会话变量(User Session Variable)是在每次 MySQL 客户与 MySQL 服务器建立一个新的连接后,由MySQL 客户进行定义的一种变量,该变量与“当前会话”有密切关系
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;
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 创建和执行存储过程的语法
创建存储过程的语法格式如下
语法:
create procedure 存储过程名字 (
[in|out|inout] 参数 1 数据类型 1,
[in|out|inout] 参数 2 数据类型 2, ……
)
[no sql | reads sql data | modifies sql data]
begin
存储过程语句块 ;
end;
语法说明如下:
存储过程的参数是局部变量。
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
在 Navicat For MySQL 的“函数”处可见 proc_product_info,表明 proc_product_info 已创建成功。单击需要操作的存储过程,如 proc_product_info,点击“设计函数”即可对该存储过程的代码进行编辑
运行存储过程的两种方式如下
- 在存储过程的编辑窗口,点击“运行”
- 在 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 个步骤
本文来自博客园,作者:明金同学,转载请注明原文链接:https://www.cnblogs.com/vmuu/p/15663358.html
公众号:【明金同学】