变量
- 系统变量
- 自定义变量
  - 用户变量
第二种, 这种方式要求查询出来的结果集只能是一行一列，这样才能赋值给变量
- 局部变量
第二种, 这种方式要求查询出来的结果集只能是一行一列，这样才能赋值给变量
- 比较
存储过程
- 存储过程的创建
  - 参数列表
  - 注意点
但是在 GUI 管理工具中，虽然依然要声明结束标记，但是依然可以使用分号来表示存储过程的结束（很奇怪，类似于语法糖？）
函数
流程控制结构
经典案例

变量

在 MySQL 中，变量可以分为以下两类

系统变量
- 全局变量
- 会话变量
自定义变量
- 用户变量
- 局部变量

系统变量

系统提供的变量，属于服务器层面

查看系统变量

show variables;
#查看系统变量中的全局变量
SHOW GLOBAL VARIABLES;
#查看系统变量中的会话变量
show session variables;
show global|[session] variables;

查看满足条件的部分系统变量

show global|[session] variables like "xxx";

查看指定的某个系统变量的值

select @@global|[session]系统变量名;

设置系统变量

set  global|[session] 系统变量名=值;

或者

set @@global|[session] 系统变量名=值;

并不是所有的系统变量都可以修改，有些是只读的

上述使用中如果不指定为 global 的话，默认就是会话变量，因此 session 关键字可以不加 ( session可以替换为 local )

全局变量

作用域：MySQL 服务器每次启动将为所有的全局变量赋初始值，对全局变量的修改会影响整个 MySQL 服务器 (所有的会话)。每次重启后上次修改的值都会恢复，想要永久生效需要更改 my.ini 配置文件

#查看全局变量
show global variables;

#查看部分全局变量
show global variables like "xx";

#查看指定的全局变量的值
select @@global.autocommit;

#为指定全局变量赋值
set @@global.autocommit=false;

显式声明 global 来进行变量的更改可以跨连接 (会话) 生效 ( 感觉类似于作用域提升 )

会话变量

作用域：针对当前 MySQL 会话 (连接) 有效，针对会话变量的修改只对当前会话有效

如何查看设置会话变量这里就不再赘述了

自定义变量

变量由用户自定义

步骤和其它语言中一样：声明->赋值 -> 使用

自定义变量分为两类

用户变量

作用域为对当前会话 ( 连接 ) 有效，类似于全局变量。用户变量的定义可以在任何地方

使用步骤

声明并初始化 (不能只声明)，赋值符号为 = 或 :=

#必须加一个 @ 符号
set @用户变量名 = 值;
#第二种
set @用户变量名 := 值;
#第三种
select @用户变量名 := 值;

更改值 ```mysql set @用户变量名 = 值; set @用户变量名 := 值; select @用户变量名 := 值;

第二种, 这种方式要求查询出来的结果集只能是一行一列，这样才能赋值给变量

select 字段 into 变量名 from 表;



3. 
查看用户变量的值
```mysql
select @用户变量名;

局部变量

作用域：只在定义局部变量的 begin end 中有效

对局部变量的所有操作只能在 begin end 中进行。并且局部变量的声明必须是 begin end 中的第一句

声明和初始化

declare 变量名 类型;
#初始化
declare 变量名 类型 default 值;

赋值 ```mysql set 变量名[:]=值;

select @变量名 := 值;

第二种, 这种方式要求查询出来的结果集只能是一行一列，这样才能赋值给变量

select 字段 into 变量名 from 表;



3. 
查看
```mysql
select 局部变量名;

begin end 将在下面介绍

比较

	作用域	定义和使用位置	语法
用户变量	当前会话	一个会话中的任何地方	必须添加 @ 符号，不用声明类型
局部变量	begin end 中	begin end 中，且必须在第一句	一般不用加 @ (使用 select 修改值时需要加)，需要限定类型

案例

#声明两个用户变量并赋值，求和并打印
SET @x := 1;
SET @y := 2;
SET @z = @x + @y;
SELECT @z;

存储过程

类似于 Java 中的方法

好处

提高重用性
简化操作
封装细节
减少编译次数和数据库服务器的了连接次数

一组预先编译好的 SQL 语句的集合，即批处理语句

存储过程在预编译时会先检查是否编译过，如果编译过则不再进行编译，因此封装在存储过程中的 SQL 只需要一次预编译 ( 一次连接 ) 就可以将其中的语句编译完成，而不会像 SQL 语句拆开时那样执行语句连接一次，提高了效率

存储过程的创建

创建语法

create procedure 存储过程名(参数列表)
begin
存储过程体，一组SQL语句
end

参数列表

存储过程中的参数列表由三部分组成

参数模式参数名参数类型

eg：in stuname varchar(20)

MySQL 中包含三种参数模式

IN：使用 IN 修饰的参数可以作为输入 ( 就是作为形参，接收调用方实参的 )
OUT：使用 OUT 修饰的参数可以作为输出 ( 就是该参数可以作为返回值 )
INOUT：既可以作为形参，又可以作为返回值

注意点

如果存储过程体中只有一句 SQL，则 begin end 可以省略
存储过程体中的每一条 SQL 语句结尾都必须加分号
存储过程的结尾必须使用 DELIMITER 重新设置 (用来和存储过程体中的 ; 结尾进行区别) ```mysql DELIMITER 结束标记 create procedure 存储过程名(参数列表) begin 存储过程体，一组SQL语句 end 结束标记

但是在 GUI 管理工具中，虽然依然要声明结束标记，但是依然可以使用分号来表示存储过程的结束（很奇怪，类似于语法糖？）

DELIMITER 结束标记; create procedure 存储过程名(参数列表) begin 存储过程体，一组SQL语句 end ;




<a name="d5994f2c"></a>
### 存储过程的使用

语法

```mysql
call 存储过程名(实参列表); #GUI 管理器中可以这样使用，但是 cmd 中不能这样用，因为使用了 DELIMITER 相当于把一条语句的结尾标记直接替换了

DELIMITER 结束标记
call myp2()结束标记

空参列表的存储过程

案例

创建

#向 girls 库中的 amdin 表中插入五条记录
delimiter $
create procedure myp()
begin
insert into admin set username="xxx", `password`="12345";
insert into admin set username="yyy", `password`="1";
insert into admin set username="zzz", `password`="1245";
insert into admin set username="aaa", `password`="125";
insert into admin set username="ccc", `password`="777";
end $  #GUI 中可以用; 也可以用 $

调用

#调用时，在 GUI 中必须使用 ; 结尾
call myp();  
#调用时，在 cmd 中必须使用声明的结束标记结尾

带 in 模式的存储过程

#根据女神名查询对应的男神信息
delimiter $
create procedure myp6(in beautyName varchar(50))
begin
    select bo.* from boys bo 
    inner join beauty be
    on bo.id = be.boyfriend_id
    where be.name = beautyName;
end ;

call myp6("柳岩");

#使用存储过程，传入用户名和密码，最后根据数据库中是否有该用户来输出对应语句
delimiter $
create procedure adminp1 (
  in username varchar (10), in password varchar (10)
) 
begin
  #定义局部变量
  declare result int default 0;
  #记录返回条数，并赋值给局部变量 result
  select
    count(*) INTO result
  from
    admin a 
  where a.username = username 
    and a.password = password ;
  select if(result > 0, "登陆成功", "登录失败");
end ;

call adminp2("xxx", "12345");

带 out 模式的存储过程

带一个返回值的 out 模式

#根据女神名，返回男神名
delimiter $
create procedure girls1(in girlName varchar(50), out boyName varchar(50))
begin
    #查询并赋值
    select bo.boyName into boyName
    from beauty be inner join boys bo
    on be.boyfriend_id = bo.id
    where be.name = girlName;
end ;

#定义用户变量
set @boyName = '';
#传入变量，返回值会自动赋值给这个变量
call girls1("柳岩", @boyName);
#查询该变量
select @boyName;

存储过程和函数 - 图1

带多个返回值的 out 模式

#根据女神名，返回男神名和 userCP 值
delimiter $
create procedure girls2(in girlName varchar(50), out boyName varchar(20), out userCP int)
begin

    select bo.boyName, bo.userCP into boyName, userCP
    from beauty be inner join boys bo
    on be.boyfriend_id = bo.id
    where be.name = girlName;

end ;

set @boyName = '';
set @userCP = 0;
call girls2("柳岩", @boyName, @userCP);
select @boyName, @userCP;

存储过程和函数 - 图2

带 inout 模式的存储过程

#传入 a 和 b 两个值，将其都翻倍并且返回
delimiter $
create procedure pp1(inout a int, inout b int)
begin 
    set a = a*2;
    set b = b*2;
end ;

set @a = 2;
set @b = 3;
call pp1(@a, @b);
select @a, @b;

练习

#创建存储过程实现传入用户名和密码，插入到 admin 表中
DELIMITER $
CREATE PROCEDURE PP2(IN userName VARCHAR(10), IN `password` VARCHAR(10))
BEGIN
    INSERT INTO admin(username, PASSWORD) VALUES(userName, `password`);
END ;

SET @username = "ddd";
SET @password = "12345";
CALL pp2(@username, @password);

SELECT * FROM admin WHERE username = "ddd";

存储过程和函数 - 图3

#创建存储过程实现传入女神编号，返回女神名称和女神电话
DELIMITER $
CREATE PROCEDURE pp4(IN id INT(11), OUT `name` VARCHAR(50), OUT phone VARCHAR(11))
BEGIN
    SELECT be.`name`, be.phone INTO `name`, phone
    FROM beauty be
    WHERE be.id = id;
END ;

CALL pp4(2, @n, @p);
SELECT @name, @phone;

存储过程和函数 - 图4

#创建存储过程实现传入两个女神生日，返回大小
DELIMITER $
CREATE PROCEDURE pp5(IN birth1 DATETIME, IN birth2 DATETIME, OUT isBigger INT)
BEGIN
    SELECT DATEDIFF(birth1, birth2) INTO isBigger;
END ;

SELECT borndate INTO @birth1
FROM beauty
WHERE id = 1;
SELECT borndate INTO @birth2
FROM beauty
WHERE id = 2;

CALL pp5(@birth2, @birth1, @isBigger);
SELECT @isBigger;

存储过程和函数 - 图5

#创建存储过程实现传入一个日期，格式化成 xx 年 xx 月 xx 日并返回
DELIMITER $
CREATE PROCEDURE pp6(IN myDate DATETIME, OUT formatDate VARCHAR(20))
BEGIN
    SELECT DATE_FORMAT(myDate, "%Y年%m月%d日") INTO formatDate;
END ;

CALL pp6(NOW(), @formatDate);
SELECT @formatDate;

存储过程和函数 - 图6

#创建存储过程，实现传入女神名称，返回格式为 女神名 and 对应男神名的字符串
DELIMITER $
CREATE PROCEDURE pp8(IN girlName VARCHAR(50), OUT formatStr VARCHAR(50))
BEGIN
    DECLARE girlsName VARCHAR(50) DEFAULT "";
    DECLARE boysName VARCHAR(50) DEFAULT "";

    SELECT be.name, bo.boyName INTO girlsName, boysName
    FROM beauty be INNER JOIN boys bo
    ON be.boyfriend_id = bo.id
    WHERE girlName = be.name;

    SET formatStr = CONCAT(girlsName, "and", boysName);

    #或者
    SELECT CONCAT(girlsName, "and", boysName) into formatStr
    FROM beauty be INNER JOIN boys bo
    ON be.boyfriend_id = bo.id
    WHERE girlName = be.name;

END ;

SELECT NAME INTO @girlName
FROM beauty
WHERE id = 1;

CALL pp8(@girlName, @result);
SELECT @result;

存储过程和函数 - 图7

#创建存储过程，根据传入的条目数和起始索引查询 beauty 表的记录
delimiter $
create procedure pp9(in startIndex int, in len int)
begin 
    select * 
    from beauty
    limit startIndex, len;
end ;

call pp9(1, 3);

存储过程和函数 - 图8

存储过程的删除

语法

drop procedure 存储过程名;

查看存储过程的信息

show create procedure 存储过程名;

函数

函数和存储过程的作用差不多，区别在于二者的返回值

存储过程可以有 0 个返回，也可以有 n 个返回
函数有且只有 1 个返回

因此存储过程适合做批量插入，批量更新等操作；而函数适合在处理数据后返回仅有 1 个的结果

创建

create function 函数名(参数列表) returns 返回类型
begin
    函数体
    return 值;
end;

注意点：

参数列表包含两部分：参数名和参数类型（参数名在前面）；和存储过程不一样，不包含参数模式
函数体中的最后一句一般为 return 语句 ( 没有 return 会报错，return 没放在结尾不叫错 )
函数体中只有一句话时，可以省略 begin … end
需要使用 delimiter 设置结束标记

调用语法

select 函数名(参数列表)

执行函数中所有语句

无参有返回值

#返回公司的员工个数
DELIMITER $
CREATE FUNCTION f1() RETURNS INT
BEGIN
    DECLARE re INT DEFAULT 0;
    SELECT COUNT(*) INTO re FROM employees;
    RETURN re;
END $

SELECT f1();

存储过程和函数 - 图9

有参有返回值

#根据员工名返回其工资
DELIMITER $
CREATE FUNCTION f2(empName VARCHAR(50) ) RETURNS DOUBLE
BEGIN
    DECLARE money DOUBLE DEFAULT 0;

    SELECT e.salary INTO money
    FROM employees e
    WHERE e.last_name = empName;

    RETURN money;
END $ 

SELECT f2("Feeney");

存储过程和函数 - 图10

#根据部门名，返回该部门的平均工资
delimiter $
create function f3(depName varchar(20)) returns double
begin
    declare re double default 0.0;

    select avg(salary) into re
    from employees e inner join departments d
    on e.department_id = d.department_id
    where d.department_name = depName;

    return re;
end $

select f3("Fin");

存储过程和函数 - 图11

查看

show create function 函数名;

删除

drop function 函数名;

流程控制结构

顺序结构：从上至下依次执行

分支结构：从多条执行路径中选择一条去执行

循环结构：在满足条件的基础上重复执行一段代码

分支结构

IF 函数

IF 函数可以实现简单的双分支

IF(表达式1, 真, 假);

delimiter $
create function f5(num1 int) returns int
begin
    declare n int default 0;
    select if(num1 > 0, 1, 0) into n;

    return n;
end $ 

select f5(2);

case 结构

场景1：类似于 switch 语句，用于等值判断

语法

case 变量|表达式|字段
when 要判断的值 then 返回的值1或[语句1;]
when 要判断的值 then 返回的值2或[语句2;]
...
else 要返回的值n或语句;
end [case;]

场景2：类似于 if…else if…else，一般用于区间判断

case 
when 要判断的条件1 then 返回的值1或[语句1;]
when 要判断的条件2 then 返回的值2或[语句2;]
...
else 返回的值n或语句;
end [case;]

PS：上面用 [ ] 的表示是将 case 作为独立的语句去使用时的语法

case 的特点

可以作为表达式嵌套在其他语句中使用，可以放在任何地方
可以作为独立的语句去使用，但只能放在 begin end 中
如果省略了缺省条件 else，并且所有 when 都不满足，则返回 null

实践

#创建存储过程和函数，通过传入的成绩来显示对应等级

#函数
DELIMITER $
CREATE FUNCTION f6(num INT) RETURNS CHAR(3)
BEGIN
    CASE
    WHEN num >= 90 AND num < 100 THEN RETURN "A";
    WHEN num >= 80 AND num < 90 THEN RETURN "B";
    WHEN num >=70 AND num < 80 THEN RETURN "C";
    WHEN num >=60 AND num <  70 THEN RETURN "D";
    ELSE RETURN "不及格";
    END CASE;
END ;

SELECT f6(65);

#存储过程
delimiter $
create procedure pc1(in num int)
begin
    case
    when num >= 90 and num < 100 then select "A";
    when num >= 80 and num < 90 then select "B";
    when num >=70 and num < 80 then select "C";
    when num >=60 and num <  70 then select "D";
    else select "不及格";
    end case;
end $

call pc1(6);

IF 结构

语法

if 条件1 then 语句1;
elseif 条件2 then 语句2：
...
[else 语句n;]
end if;

IF 结构只能在 begin end 中使用

案例

#创建存储过程和函数，通过传入的成绩来显示对应等级 (使用 if 结构)
DELIMITER $
CREATE FUNCTION fc1(num INT) RETURNS CHAR(3)
BEGIN
    IF num >= 90 AND num < 100 THEN RETURN "A";
    ELSEIF num >= 80 AND num < 90 THEN RETURN "B";
    ELSEIF num >=70 AND num < 80 THEN RETURN "C";
    ELSEIF num >=60 AND num < 70 THEN RETURN "D";
    ELSE RETURN "不及格";
    END IF;
END $

SELECT fc1(78);

循环结构

MySQL 中的循坏有：

while
loop
repeat

而循环控制语句则有：

iterate ( 代替 continue )：结束本次循环，继续下次循环
leave ( 代替 break )：跳出，结束当前所在的循环

while

语法

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

标签: 代表的是为循环命名，只有命了名才能搭配循环控制语句

loop

语法

[标签:]loop
    循环体;
end loop [标签];

loop 没有循环条件，因此原始的 loop 是死循环，如果搭配 leave 使用则可以按照条件结束循环

repeat

[标签:]repeat
    循环体;
until 结束循环的条件
end repeat [标签];

repeat 类似于 do while，无论如何都会先执行一次

存储过程和函数 - 图14

案例

while

#批量插入，根据传入的整型参数向 admin 表中插入对应条记录
DELIMITER $
CREATE PROCEDURE pc3(IN num INT)
BEGIN
    DECLARE n INT DEFAULT 0;

    w1:WHILE n<num
    DO
       INSERT INTO admin(username, PASSWORD)
       VALUES ("dwdw", "ddddd");
       SET n = n+1;
    END WHILE w1;
END $

CALL pc3(4);

存储过程和函数 - 图15

#批量插入，根据传入的整型参数向 admin 表中插入对应条记录，如果插入的条数大于 20 了，则停止
DELIMITER $
CREATE PROCEDURE pc4(IN num INT)
BEGIN
    DECLARE i INT DEFAULT 0;

    w1:WHILE i<num
    DO 
       IF i>20 THEN LEAVE w1;
       END IF;
       INSERT INTO admin(username, PASSWORD)
       VALUES(CONCAT("csrf", i), "111");
       SET i = i+1;
    END WHILE w1;
END $

CALL pc4(30);

存储过程和函数 - 图16

#只插入偶数次的记录
DELIMITER $
CREATE PROCEDURE pc5(IN num INT)
BEGIN
    DECLARE i INT DEFAULT 0;

    w1:WHILE i<num
    DO
        SET i = i+1; 
        IF i%2!=0 THEN ITERATE w1; 
        ELSE INSERT INTO admin(username, PASSWORD)
        VALUES(CONCAT("grgr",i), "121343");
        END IF;
    END WHILE w1;
END $

CALL pc5(20);

经典案例

#已知表 stringcontent，其中字段有：id 自增长，content varchar(20)
#要求向该表插入指定个数的，随机的字符串
create table stringcontent(
    id int auto_increment PRIMARY key,
    content varchar(20)
);

delimiter $
create procedure pc7(in strLen int)
begin
    declare i int default 0;

    w1:while i<strLen 
    do
       insert into stringcontent 
       set content = substring(md5(rand()), 1, 20);
       set i = i+1;
    end while w1;
end $

call pc7(20);

#或者使用原始方法
DELIMITER $
CREATE PROCEDURE pc8(IN strLen INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE startIndex INT DEFAULT 0;
    DECLARE len INT DEFAULT 0;
    DECLARE randomStr VARCHAR(50) DEFAULT "abcdefghijklmnopqrstuvwxyz";

    w1:WHILE i<strLen
    DO
       #通过让起始索引和长度随机来造成随机字符串的假象
       SET startIndex = FLOOR(RAND()*26+1);
       #可截取长度就是总长度-起始索引+1
       SET len = FLOOR(RAND()*(26-startIndex+1)+1);

       INSERT INTO stringcontent
       SET content = SUBSTRING(randomStr, startIndex, len);

       SET i = i+1;
    END WHILE w1;

END $

CALL pc8(10);

存储过程和函数

变量

系统变量

查看系统变量

设置系统变量

全局变量

会话变量

自定义变量

用户变量

第二种, 这种方式要求查询出来的结果集只能是一行一列，这样才能赋值给变量

局部变量

第二种, 这种方式要求查询出来的结果集只能是一行一列，这样才能赋值给变量

比较

存储过程

存储过程的创建

参数列表

注意点

但是在 GUI 管理工具中，虽然依然要声明结束标记，但是依然可以使用分号来表示存储过程的结束 （很奇怪，类似于语法糖？）

空参列表的存储过程

带 in 模式的存储过程

带 out 模式的存储过程

带 inout 模式的存储过程

练习

存储过程的删除

查看存储过程的信息

函数

创建

无参有返回值

有参有返回值

查看

删除

流程控制结构

分支结构

IF 函数

case 结构

实践

IF 结构

循环结构

while

loop

repeat

案例

while

经典案例

但是在 GUI 管理工具中，虽然依然要声明结束标记，但是依然可以使用分号来表示存储过程的结束（很奇怪，类似于语法糖？）