作者：codejiwei 时间：2020-06-18

1变量

①系统变量

**说明：**变量由系统定义，不是用户定义，属于服务器层面。系统变量|会话变量

使用步骤：

查看所有系统变量 show global|【session】 variables; 查看满足条件的部分系统变量 show global|【session】 variables like '%char%'; # 查看所有带有char的系统变量 查看指定的系统变量的值 select @@global|【session】.系统变量名; 为某个系统变量赋值 # 方式一： set global | 【session】 系统变量名=值; # 方式二： set @@global|【session】 系统变量名=值;

全局变量

关键字：global

作用域：针对所有会话（连接）有效，但不能跨重启

实例：

# ①查看所有的全局变量 show global variables; # ②查看满足条件的部分系统变量 show global variables like '%char%'; #③查看指定的系统变量的值 select @@global.autocommit; #④为某个系统变量赋值 set @@global.autocommit=0; set global autocommit=0

会话变量

关键字：session，如果不写默认会话级别

作用域：针对当前会话（连接）有效

实例：

#①查看所有会话变量 show session variables; #②查看满足条件的部分会话变量 show session variables like '%char%'; #③查看指定的会话变量的值 select @@session.tx_isolation; select @@tx_isolation; #④为某个会话变量赋值 set @@session.tx_isolation='read-uncommitted'; set session tx_isolation='read-committed'

②自定义变量

**说明：**变量由用户自定义，而不是系统提供的。用户变量|局部变量

使用步骤：

​ 1 声明

​ 2 赋值

​ 3 使用（查看、比较、运算等）

用户变量

作用域：针对当前会话（连接）有效，作用域同会话变量

赋值操作符为 =或：=

1 声明并初始化

set @变量名=值; set @变量名：=值; select @变量名：=值;

2 赋值（更新变量的值）

#方式一： SET @变量名=值; SET @变量名:=值; SELECT @变量名:=值; #方式二： SELECT 字段 INTO @变量名 FROM 表;

3 使用（查看变量的值）

SELECT @变量名;

局部变量

作用域：仅仅在定义它的begin end块中有效

应用在：begin end中的第一句话

1 声明

DECLARE 变量名 类型; DECLARE 变量名 类型 【DEFAULT 值】;

2 赋值(更新变量的值)

#方式一： SET 局部变量名=值; SET 局部变量名:=值; SELECT 局部变量名:=值; #方式二： SELECT 字段 INTO 具备变量名 FROM 表;

3 使用（查看变量的值）

SELECT 局部变量名;

用户变量和局部变量的对比

作用域 定义位置 语法 用户变量 当前会话 会话的任何地方 加@符号，不用指定类型 局部变量 定义它的BEGIN END中 BEGIN END的第一句话 一般不用加@,需要指定类型

案例：

#案例：声明两个变量，求和并打印 #用户变量 SET @m=1; SET @n=1; SET @sum=@m+@n; SELECT @sum; #局部变量 DECLARE m INT DEFAULT 1; DECLARE n INT DEFAULT 1; DECLARE SUM INT; SET SUM=m+n; SELECT SUM;

2存储过程和函数

存储过程和函数：类似于java中的方法

好处：

提高代码的重用性简化操作

区别：

​ 存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新

​ 函数：有且仅有1个返回，适合做处理数据后返回一个结果

①存储过程

含义：一组预先编译好的SQL语句的集合，理解成批处理语句

优点：

提高代码的重用性简化操作减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

注意：

参数列表包含三部分：

参数模式 参数名 参数类型

参数模式有：

in：该参数可以作为输入，也就是该参数需要调用方传入值out：该参数可以作为输出，也就是该参数可以作为返回值inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

如果存储过程体仅仅只有一句话，begin end可以省略

存储过程体的每条sql语句的结尾必须加分号

存储过程的结尾可以使用delimiter重新设置

语法：delimiter 结束标记案例：delimiter $

创建语法：

create procedure 存储过程名称（参数列表） begin 存储过程体（一组合法的SQL语句） end

调用语法：

call 存储过程名（实参列表）;

---

无参案例演示：

# 无参案例：插入到admin表中五条记录 select * from admin; delimiter $ create procedure myp1() begin insert into admin(username, `password`) values('join', '0000'),('tom', '0000'),('rose', '0000'),('jack', '0000'),('lily', '0000') end $ # 调用 call myp1() $

创建带in模式参数的存储过程

#案例1：创建存储过程实现 根据女神名，查询对应的男神信息 create procedure myp2 (in beautyname varchar(20)) begin select bo.* # 查询对应的男神信息 from boys bo right join beauty b on bo.id = b.boyfriend_id where b.name = beautyname; # 形参等于b表中的name end $ # 调用 call myp2("柳岩") $ #实参 柳岩 #案例2：创建存储过程实现，用户是否登录成功 create procedure myp3(in username varchar(20), in `password` varchar(20)) begin declare result int default 0; # 声明并初始化赋值 select count(*) into result; # 赋值 from admin where admin.username = username and admin.password = `password` select if (result > 0, '成功', '失败') # 使用 end $ # 调用 call myp3("张飞", '8888') $

创建带out 模式参数的存储过程

#案例1：根据输入的女神名，返回对应的男神名 create procedure myp6(in beautyname varchar(20), out boyname varchar(20)) begin select bo.boyname into boyname from boys bo right join beauty b on bo.id = b.boyfriend_id where b.name = beautyname end $ # 调用 call myp7("小昭", @name, @cp) $ # 定义用户变量@name @cp select @name, @cp $ # 调用用户变量 #案例2：根据输入的女神名，返回对应的男神名和魅力值 create procedure myp7(in beautyname varchar(20), out boyname varchar(20), out usercp int) begin select bo.boyname, bo.usercp into boyname, usercp from boys bo right join beauty b on b.boyfriend_id = bo.id where b.name = beautyname; end $ # 调用 call myp7 ('小昭', @name,@cp) $ select @name, @cp $

创建带input模式参数的存储过程

#案例1：传入a和b两个值，最终a和b都翻倍并返回 create procedure myp8(inout a int, inout b int) begin set a = a*2 set b = b*2 end $ # 调用 set @m=10 $ set @n=20 $ call myp8(@m, @n) $ select @m, @n $

删除存储过程：

语法：drop procedure 存储过程名 drop procedure p1; drop procedure p2, p3 # × 存储过程的删除不能删除多个！

查看存储过程的信息：

desc myp2; # × 不能这样查看 show create procedure myp2; # 可以这样查看

②函数

含义：一组预先编译好的SQL语句的集合，理解成批处理语句

优点：

提高代码的重用性简化操作减少了编译次数并且减少了和数据库服务器的连接次数，提高效率

创建语法：

create function 函数名（参数列表） returns 返回类型 begin 函数体 end

调用语法：

select 函数名（参数列表）

注意：

参数列表包含两个部分：参数名 参数类型

函数体：肯定会有return语句，如果没有会报错

​ 如果return语句没有放在函数体的最后也不会报错，但是不建议

函数体中仅有一句话，则可以省略begin end

使用delimiter语句设置结束标记

---

案例演示： #1.无参有返回 #案例：返回公司的员工个数 create function myf1() returns int begin declare c int default 0; # 定义局部变量 select count(*) into c # 赋值 from employees; return c; end $ # 调用 select myf1() $ #2.有参有返回 #案例1：根据员工名，返回它的工资 create function myf2(empName varchar(20)) returns double begin set @sal = 0 # 定义用户变量 select salary into @sal # 赋值 from employees where last_name = empName; return @sal; end # 调用 select myf2('k_ing') $ #案例2：根据部门名，返回该部门的平均工资 create function myf3(deptname varchar(20)) returns double begin declare sal double; select avg(salary) into sal from employees e join departments d on e.department_id=d.department_id where d.department_name = deptname; return sal; end # 调用 select myf3('IT') $

查看函数

show create function myf3;

删除函数

drop function myf3;

③案例讲解：

#一、创建函数，实现传入两个float，返回二者之和 create function test_fun1(num1 float , num2 float) returns float begin declare sum float default 0; set sum = num1 + num2; return sum; end $ select test_fun1(1, 2) $ #一、创建存储过程实现传入用户名和密码，插入到admin表中 create procedure test_pro1(in username varchar(20), in loginpwd varchar(20)) begin insert into admin(admin.username, `password`) values(username, loginpwd); end $ call test_pro1('zhangfei', '100101') $ #二、创建存储过程实现传入女神编号，返回女神名称和女神电话 create procedure test_pro2(in beauty_id int , out beauty_name varchar(20), out beauty_phone varchar(20)) begin select b.name, b.phone into beauty_name, beauty_phone from beauty b where b.id = beauty_id; end $ call test_pro2(1, @name, @number) $ select @name, @number $ #三、创建存储存储过程或函数实现传入两个女神生日，返回大小 create procedure test_pro3(in birth1 datetime, in birth2 datetime, out result int) begin select datediff(birth1, birth2) into result; end $ call test_pro3('1996-1-1', '1993-1-1', @result) $ select @result $ #四、创建存储过程或函数实现传入一个日期，格式化成xx年xx月xx日并返回 create procedure test_pro4(in mydate datetime, out strDate varchar(50)) begin select date_format(mydate, '%y年%m月%d日') into strDate; end $ call test_pro4(now(), @str) $ select @str $ #五、创建存储过程或函数实现传入女神名称，返回：女神 and 男神 格式的字符串 如 传入 ：小昭 返回： 小昭 AND 张无忌 drop procedure test_pro5 $ create procedure test_pro5 (in beautyname varchar(20), out str varchar(50)) begin select concat(beautyname, " and ", ifnull(boyName, "null")) into str from beauty b left join boys bo on b.boyfriend_id = bo.id where b.name = beautyname; end $ CALL test_pro5('柳岩',@str)$ SELECT @str $ #六、创建存储过程或函数，根据传入的条目数和起始索引，查询beauty表的记录 DROP PROCEDURE test_pro6$ CREATE PROCEDURE test_pro6(IN startIndex INT,IN size INT) BEGIN SELECT * FROM beauty LIMIT startIndex,size; END $ CALL test_pro6(3,5)$

3流程控制结构

顺序、分支、循环

①分支结构

（1）if函数

语法：if(条件,值1，值2)

功能：实现双分支

应用：在begin end中或外面

（2）case结构

语法： 情况1：类似于switch case 变量或表达式 when 值1 then 语句1; when 值2 then 语句2; ... else 语句n; end 情况2： case when 条件1 then 语句1; when 条件2 then 语句2; ... else 语句n; end 应用：在begin end 中或外面

（3）if结构

语句： if 条件1 then 语句1； elseif 条件2 then 语句2； ... else 语句n； end if； 功能：类似于多重if应用：只能应用在begin end中

案例讲解

#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D create function test_if(score float) returns char begin declare ch char default 'A'; if score > 90 then set ch = 'A'; elseif score >80 then set ch = 'B'; elseif score >60 then set ch = 'C'; else set ch = 'D'; end if ; return ch; end $ SELECT test_if(87)$ #案例2：创建存储过程，如果工资<2000,则删除，如果5000>工资>2000,则涨工资1000，否则涨工资500 create procedure test_if_pro(in sal double) begin if sal <= 2000 then delete from employees where employees.salary = sal; elseif sal >=2000 and sal <5000 then update employees set salary =salary+1000 where employees.salary = sal; else update employees set salary =salary+500 where employees.salary = sal; end if ; end $ call test_if_pro(2100) $ #案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR BEGIN DECLARE ch CHAR DEFAULT 'A'; CASE WHEN score>90 THEN SET ch='A'; WHEN score>80 THEN SET ch='B'; WHEN score>60 THEN SET ch='C'; ELSE SET ch='D'; END CASE; RETURN ch; END $ SELECT test_case(56)$

②循环结构

分类： while、loop、repeat循环控制： iterate类似于 continue ，继续，结束本次循环，继续下一次leave类似于 break ，跳出，结束当前所在的循环

（1）while★

语法：【标签：】while 循环条件 do

​ 循环体

​ end while 【标签】；

#1.没有添加循环控制语句 #案例：批量插入，根据次数插入到admin表中多条记录 drop procedure pro_while1$ create procedure pro_while1(in insertCount int) begin declare i int default 1; where i < insertCount do insert into admin(username, `password`) values (concat('rose', i), '123456') set i = i+1 end where ; end $ call pro_while1(100) $ #2.添加leave语句 #案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止 TRUNCATE TABLE admin$ DROP PROCEDURE test_while1$ CREATE PROCEDURE test_while1(IN insertCount INT) BEGIN DECLARE i INT DEFAULT 1; a:WHILE i<=insertCount DO INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000'); IF i>=20 THEN LEAVE a; END IF; SET i=i+1; END WHILE a; END $ CALL test_while1(100)$ #3.添加iterate语句 #案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次 TRUNCATE TABLE admin$ DROP PROCEDURE test_while1$ CREATE PROCEDURE test_while1(IN insertCount INT) BEGIN DECLARE i INT DEFAULT 0; a:WHILE i<=insertCount DO SET i=i+1; IF MOD(i,2)!=0 THEN ITERATE a; END IF; INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000'); END WHILE a; END $ CALL test_while1(100)$

（2）loop

语法： 【标签：】 loop

​ 循环体

​ end loop 【标签】；

可以用来模拟简单的死循环

（3）repeat

语法：【标签：】 repeat

​ 循环体

​ until 结束循环的条件

​ end repeat 【标签】；

经典案例讲解

/*一、已知表stringcontent 其中字段： id 自增长 content varchar(20) 向该表插入指定个数的，随机的字符串 */ DROP TABLE IF EXISTS stringcontent; CREATE TABLE stringcontent( id INT PRIMARY KEY AUTO_INCREMENT, content VARCHAR(20) ); DELIMITER $ CREATE PROCEDURE test_randstr_insert(IN insertCount INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz'; DECLARE startIndex INT;#代表初始索引 DECLARE len INT;#代表截取的字符长度 WHILE i<=insertcount DO SET startIndex=FLOOR(RAND()*26+1);#代表初始索引，随机范围1-26 SET len=FLOOR(RAND()*(20-startIndex+1)+1);#代表截取长度，随机范围1-（20-startIndex+1） INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startIndex,len)); SET i=i+1; END WHILE; END $ CALL test_randstr_insert(10)$

案例解析

1、创建存储过程或函数实现传入用户名和密码，插入到 admin 表中 delimiter $ create procedure myp1() begin insert into admin(username, password) values('Tom', '1001'), ('Jerry', '1001'), ('Jiwer', '1001'); end $ # 创建完成后调用存储过程 2、创建存储过程或函数实现传入女神编号，返回女神名称和女神电话 3、创建存储存储过程或函数实现歘人两个女神生日，返回大小 4、创建存储过程或函数实现传入一个日期，格式化成 xx 年 xx 月 xx 日并返回 5、创建存储过程或函数实现传入女神名称，返回：女神 and 男神 格式的字符串 如 传入 ：小昭 返回： 小昭 and 张无忌 6、创建存储过程或函数，根据传入的条目数和起始索引，查询 beauty 表的记录