MySQL数据库编程（1）

在 MySQL 数据库中，变量有两种，分别为： 系统变量 和 自定义变量 。

系统变量，顾名思义，是系统设置好的变量（皆为全局级别变量），也是用来控制服务器表 现的，如autocommit 、 wait_timeout 等。

查看系统变量，语法为：

基本语法 ： show variables;

查看具体的系统变量的值，语法为：

基本语法 ： select + @@变量名 + [, @@变量名, ... , @@变量名];

一、用户自定义变量

概念：用户自定义变量用于存储 MySQL 存储程序运行期间所产生的临时变量，它分为用户

会话变量和局部变量。（what）

1.用户会话变量

(1)概念： 是在每次 MySQL 客户与 MySQL 服务器建立一个新的连接后，由MySQL 客户

进行定义的一种变量，该变量与“当前会话”有密切关系。 只要是当前用户当次连接，

都会受到影响，不区分数据库 。

(2)定义用户自定义变量

--set命令

MySQL 用户会话变量以一个“@”开头，并且大小写不敏感。

基本语法 ： set @变量名 = 值;

值可以是 可以是 常量、变量和表达式

然后使用 select 语句查看变量的值：

select @user_name, @age;

--select

第 1 种 ：边赋值，边查看结果。语法为

基本语法 ： select @变量名 := 字段名 from 表名;

赋值的同时展示结果集，变量的值是结果集中最后一行的值。

第 2 种 ：只赋值，不查看结果。语法为

基本语法 ：

select + 字段列表 + from + 表名 + into + 变量列表;

或者“ select expression1 into @user_variable1 , expression2 into

@user_variable2, ...”

只定义变量并赋值，不展示结果集，如果结果集返回多行，运行该语句报错，但会取第 一条赋值给变量。

2.用户会话变量赋值

给定义的变量赋值时，通常需要对查询语句做限定，使得结果集中有单 个值时可以赋值给变量。

3.重置命令结束标记

(1)begin-end语句块

通常begin-end用于定义一组语句块，在各大数据库中的客户端工具中可直接调

用，但在mysql中不可。

begin-end、流程控制语句、局部变量只能用于函数、存储过程内部、游标、触

发器的定义内部。

（2）结束标记

在MySQL中默认的结束符DELIMITER是 ; ， begin-end 语句块中通常存在多 条 MySQL 表达式，每条 MySQL 表达式都使用“;”作为结束标记。 由于 begin-end 语句块中的多条 MySQL 表达式密不可分，为了避免这些 MySQL 表达式被拆开，需要重置 MySQL 客户机的命令结束标记，亦称命令分隔符 （delimiter）。（why）

在使用习惯中，我们经常将结束符更改为 ;; 、 // 、 $$ 等。

语法：DELIMITER 符号

1 mysql > DELIMITER // # 更改结束符为 //

2 mysql > CREATE FUNCTION delUser ( userid INT UNSIGNED )

3 ‐> RETURNS VARCHAR ( 30 ) # 设定返回类型

4 ‐> BEGIN

5 ‐> DELETE FROM user_info WHERE id = userid ; # 删除指定 id 用户

6 ‐> RETURN NOW (); # 返回操作时间

7 ‐> END

8 ‐> //

9 Query OK , 0 rows affected ( 0.00 sec )

10 mysql > DELIMITER ;

二、存储过程

1.局部变量

(1)概念：局部变量（local variable） 必须定义在 存储程序中，如函数、存储过程、触发器

以及事件中，而且局部变量的作用范围仅局限于存储程序中。（what）

(2)用法 局部变量 定义语法 形式：declare 局部变量 数据类型 ;

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

局部变量 赋值语法 ：

set var_name=expr [,

var_name=expr]...;

s et 语句既可以用于局部变量的赋值，也可以用于用户变量的申明并 赋值。

1 declare c int default 0 ;

2 set c = a + b ;

3 select c as C ;

select …. into… 形式赋值

1 declare v_employee_name varchar ( 100 );

2 declare v_employee_salary decimal ( 8 , 4 );

3

4 select employee_name , employee_salary

5 into v_employee_name , v_employee_salary

6 from employees

7 where employee_id = 1 ;

(3)使用场合

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

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

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

2.存储过程介绍

（1）概念：

存储过程是一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中。 用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执 行它。一个存储过程其实就是一个可编程的函数（函数有返回值，存储过程没有返回 值）。（what）

（2）存储过程优点：

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

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

（1）定义存储过程的语法结构：

1 create procedure 存储过程名字 (

2 [ in| out | inout ] 参数 1 数据类型 1 ,

3 [ in| out | inout ] 参数 2 数据类型 2 , ……

4 )

5 [ no sql | reads sql data | modifies sql data ]

6 begin

7 存储过程语句块 ;

8 end ;

9 ‐‐ 语法说明：

10 in 代表输入参数（默认为 in 参数），表示该参数的值必须由调用程序指定。

11 out 代表输出参数，表示经过存储过程的计算后，将 out 参数的计算结果返回给调用程

序。

12 inout 代表既是输入参数又是输出参数，表示该参数的值既可以由调用程序指定，

13 又可以将该参数的计算结果返回给调用程序。

mysql创建存储过程的四个数据存取限制的参数：

CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句；

NO SQL表示子程序不包含SQL语句。

READS SQL DATA表示子程序包含读数据的语句，但不包含写数据的语句。

MODIFIES SQL DATA表示子程序包含写数据的语句。

如果这些特征没有明确给定，默认的是CONTAINS SQL。

这些特征值目前只提供给服务器，并没有根据这些特征值来约束过程实际的使用数据情况.

（2）调用存储过程的语法结构：

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

4.不带参数存储过程

（1）如何操作创建存储过程： 在 Navicat For MySQL 单击“工具” “命令行界面”，出现 MySQL 命令行界面，在 该界面输入存储过程的创建代码，按回车键即成功创建了存储过程。 也可以将如下编写的代 码拷贝到查询窗口运行。

（2）编辑存储过程：

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

（3）运行存储过程两种方式：

在存储过程的编辑窗口，点击“运行”；

或在 MySQL 命令行输入“call 存储过程名”；

5.带输入参数存储过程

（1）执行带参数的存储过程时，传入值的类型、个数和顺序都需要与存储过程中定义的参

数逐一对应。

在创建存储过程的时候， 设计中文的参数的数据类型需为Nvarchar

（2）用例：

1 ‐‐ 定义存储过程，查某人（如张三）下过的所有订单的金额

2 delimiter $$

3

4 mysql > create procedure sel_orders_byName ( in _userName nvarchar ( 50 ))

5 ‐> begin

6 ‐> select MONEY from t_order o where o . cashier_id in

7 ( select worker_id from t_worker w where w . name = _userName );

8 ‐> end

9 ‐> $$

10 Query OK , 0 rows affected

11 mysql > delimiter ;

12 ‐‐ 命令行下调用

13 mysql > call sel_orders_byName ( ' 张三 ' );

14 ‐‐ 查询窗口下调用存储过程 15 set @userName = ' 李四 ' ;

16 call sel_orders_byName ( @userName );

17

6.带输出参数存储过程

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

（2）带 输入和输出 参数的例子：

1 ‐‐ 查某人（如李四）下的订单中的最大金额

2 mysql > delimiter $$

3 mysql > create procedure sel_maxMoney_byName

4 ( in _userName nvarchar ( 20 ), out _money decimal ( 8 , 2 ))

5 ‐> begin

6 ‐> select max ( MONEY ) into _money from t_order o where o . cashier_id

7 in ( select worker_id from t_worker w where w . name = _userName );

8 ‐> end

9 ‐> $$

10 Query OK , 0 rows affected

11 mysql > delimiter ;

12

13 ‐‐ 查询窗口调用执行

14 set @userName = ' 李四 ' ;

15 set @maxMoney = 0 ;

16 call sel_maxMoney_byName ( @userName , @maxMoney );

17 SELECT @maxMoney ;

三、条件控制语句

MySQL 提供了简单的流程控制语句，其中包括条件控制语句以及循 环语句。

这些流程控制语句通常放在 begin-end 语句块中使用 。

1.if 语句

(1)语法：

1 if 条件表达式 1 then 语句块 1 ;

2 [ elseif 条件表达式 2 then 语句块 2 ] …

3 [ else 语句块 n ]

4 end if ; 5 ‐‐ 如果在 select 语句里用可以用 if 函数，如下：

6 ‐‐ 可以用该语句替换 select *,if(sex=1,” 男 ”,” 女 ”) as sex from user

（2）存储过程的begin-end中使用：

1 ‐‐ 定义存储过程：查询工资最高的员工是什么层次的员工

2 mysql > delimiter $$

3 mysql > create procedure sel_salary ( out _msg nvarchar ( 50 ))

4 ‐> begin

5 ‐> declare _salary decimal ( 8 , 2 );

6 ‐> select max ( w . salary ) into _salary from t_worker w ;

7 ‐> if _salary > 3000 then set _msg = ' 管理层 ' ;

8 ‐> elseif _salary > 2500 then set _msg = ' 中层管理 ' ;

9 ‐> else set _msg = ' 普通员工 ' ;

10 ‐> end if ;

11 ‐> end

12 ‐> $$

13 Query OK , 0 rows affected

14 ‐‐ 调用存储过程

15 set @msg = '' ;

16 call sel_salary ( @msg );

17 select @msg ;

2.case 语句

(1)语法：

1 case

2 when 表达式 1 then 语句块 1

3 when 表达式 2 then 语句块 2

4 …

5 else 语句块 n

6 end ;

（2）select中使用：

1 ‐‐ 查询所有员工的薪资情况（姓名，工资，等级），要求：

2 ‐‐ 如果工资 >3000 ，显示等级为 “ 高级员工 ” ；

3 ‐‐ 如果 3000> 工资 >2000 ，显示等级为 “ 中级员工 ” ；

4 ‐‐ 如果工资 <2000 ，显示等级为 “ 初级员工 ” ；

5 SELECT NAME as ' 姓名 ' , SALARY as ' 工资 ' ,

6 CASE

7 WHEN SALARY > 3000 THEN ' 高级员工 '

8 WHEN SALARY <= 3000 AND SALARY > 2000 THEN ' 中级员工 '

9 ELSE ' 初级员工 ' 10 END

11 as ' 等级 '

12 FROM t_worker

3.while 语句

（1）语法：

1 [ 循环标签 : ] while 条件表达式 do

2 循环体 ;

3 end while [ 循环标签 ];

（2）使用：

1 ‐‐ 实现 1‐50 的累加

2 declare total int default 0 ;

3 declare num int default 0 ;

4 while num <= 50 do

5 set total = total + num ;

6 set num = num + 1 ;

7 end while ;

8 select total ;

4.leave 语句

(1)概念：nleave 语句用于跳出当前的循环语句，如 while 语句，它的作用等同于高级编程 语言中的 break 语句。

（2）语法：

1 leave 循环标签 ;

（3）用法：

1 declare total int default 0 ;

2 declare num int default 0 ;

3 add_num: while true do

4 if ( num > 50 ) then

5 leave add_num ;

6 end if ;

7 set total = total + num ; set num = num + 1 ;

8 end while add_num ;

5.iterate 语句

（1）概念：niterate 语句用于跳出本次循环，进而进行下次循环，它的作用等同于高级编 程语言中的 continue 语句

（2）语法：

1 iterate 循环标签 ; （3）用法：

1 declare sum int default 0 ;

2 declare num int default 0 ;

3 add_num: while true do

4 if ( num % 2 = 0 ) then

5 set sum = sum + num ;

6 else

7 set num = num + 1 ;

8 iterate add_num ;

9 end if ;

四、游标

1、游标介绍

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

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

（3）类似生活案例：

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

2、游标使用步骤

• 声明游标、
• 打开游标
• 从游标中提取数据
• 关闭游标

（1）声明游标

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

（2）打开游标

1 语法：

2 open 游标名 ;

使用 open 语句打开游标后，与游标对应的 select 语句将被执行， MySQL 服

务器内存中将存放与 select 语句对应的结果集。

（3）从游标中提取数据需要使用 fetch 语句

1 语法：

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

注意：

变量名的个数和类型，必须与声明游标时使用的 select 语句结果集中的字段个 数和类型保持一致。

第一次执行 fetch 语句时，将从结果集中提取第 1 条记录，再次执行 fetch 语 句时，将从结果集中提取第 2条记录……以此类推，fetch 语句每次从结果集中仅仅 提取一条记录，因此 fetch 语句需要循环语句的配合才能实现整个结果集的遍历。

当使用 fetch 语句从游标中提取最后一条记录后，再次执行 fetch 语句时，将产生“error 1329(0200):no data to fetch”的错误信息。数据库开发人员可以针对 MySQL 错误代码 1329 自定义错误处理程序，以便结束结果集的遍历。

（4）关闭游标

1 语法结构：

2 close 游标名 ;

说明：

关闭游标的作用在于释放游标打开时产生的结果集，从而节省 MySQL 服务

器的内存空间。游标如果没有被显式关闭，那么它将在被打开的 begin-end 语

句块的末尾处关闭。

3、游标的使用

1 ‐‐ 查所有员工的工资总和

2 delimiter $$

3 create procedure sel_al ()

4 begin ;

5 declare allSalary decimal ( 8 , 2 );

6 declare sal decimal ( 8 , 2 );

7 ‐‐ 1 、声明游标

8 declare cur cursor for select salary from t_worker ;

9 declare continue handler for 1329 set state = 'error' ;

10 set allSalary = 0 ;

11 ‐‐2 、打开游标

12 open cur ;

13 upt: while true do

14 ‐‐ 3 、从游标中提取数据

15 fetch cur into sal ;

16 if ( state = 'error' ) then

17 leave upt ;

18 end if ; 19 allSalary = allSalary + sal ;

20 end while upt ;

21 ‐‐ 4 、关闭游标

22 close cur ;

23 select allSalary ;

24 end ;

25 $$