- 一、变量
- 1、系统变量
- (1)全局变量和会话变量作用域
- (2)系统变量创建、赋值、使用语法
- 2、自定义变量
- (1)用户变量
- (2)局部变量
- (3)用户变量和局部变量的比较：
- 二、存储过程
- 1、存储过程
- 2、存储过程的好处
- 3、存储过程创建语法：
- 4、存储过程调用语法
- 5、使用案例
- (1)创建空参列表的存储过程
- (2)创建带in模式参数的存储过程。
- (3)创建带out模式的存储过程
- (4)创建带inout模式参数的存储过程
- 6、删除和查看存储过程
- 三、函数
- 1、函数和存储过程的区别
- 2、创建语法
- 3、调用语法
- 4、案例演示
- (1)无参有返回
- (2)有参有返回
- 5、函数的查看和删除
- (1)函数的查看
- (2)函数的删除
- 四、流程控制
- 1、分支结构
- (1)IF函数
- (2)case结构
- (3)IF结构
- 2、循环结构
- (1)分类
- (2)语法
- (3)案例：
所有数据库学习总结文章均是根据B站尚硅谷视频总结而来，视频链接如下：
[https://www.bilibili.com/video/BV12b411K7Zu]
系统变量可分为全局变量和会话变量。使用时全局级别需要加GLOBAL，会话级别加SESSION,如果不写默认SESSION。
(1)全局变量和会话变量作用域全局变量服务器每次启动将为所有的全局变量赋予初始值，对所有的会话连接有效；会话变量仅仅针对于当前会话有效。
(2)系统变量创建、赋值、使用语法①查看所有的全局变量/会话变量 语法； SHOW GLOBAL/【SESSION可省略】 VARIABLES; 例如： SHOW GLOBAL VARIABLES; #查看全局变量 SHOW VARIABLES; #查看会话变量 SHOW SESSION VARIABLES; #查看会话变量 ②查看满足条件的部分系统变量 SHOW GLOBAL/【SESSION可省略】 VARIABLES LIKE '%ASA%'; #查看包含ASA的变量 ③查看指定的某个系统变量 语法： SELECt @@GLOBAL/【SESSION可选】.系统变量名; 举例： SELECT @@global.autocommit; #查看指定全局变量的值 SELECT @@tx_isolation; #查看指定会话变量的值 SELECT @@session.tx_isolation; #查看指定会话变量的值 ④为某个具体的系统变量赋值 语法： 方式一：SET GLOBAL/【SESSION可选】 系统变量名=值; 方式二：SET @@GLOBAL/【SESSION可选】.系统变量名=值; 举例： SET @@global.autocommit=0; #为全局变量赋值 SET @@session.autocommit=0; #为会话变量赋值 SET SESSION autocommit=0; #为会话变量赋值2、自定义变量
自定义变量分为：用户变量和局部变量。
(1)用户变量①作用域：针对于当前会话（连接）有效，用于会话变量作用域。
②创建、赋值和使用语法：
声明并初始化
SET @用户变量名=值;
SET @用户变量名 :=值;
或 SELECT @用户变量名:=值;
赋值
方式一：初始化赋值;
方式二：通过 SELECT INTO
SELECT 字段 INTO 变量名 FROM 表;
使用（查看、比较、运算等）
SELECt @用户变量名;
(2)局部变量
①作用域：仅仅在定义它的begin and中有效，并且应放在begin and里边第一句。
②创建、赋值和使用语法：
声明
DECLARE 局部变量名 类型;
DECLARE 局部变量名 类型 DEFAULT 值;
赋值
方式一：通过SET或SELECT
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT @局部变量名:=值;
方式二：通过 SELECT INTO
SELECT 字段 INTO 局部变量名 FROM 表;
使用
SELECt 局部变量名;
(3)用户变量和局部变量的比较：
| 比较特点 | 用户变量 | 局部变量 |
|---|---|---|
| 作用域 | 当前会话 | BEGIN AND 中 |
| 定义和使用位置 | 当前会话中的任意位置 | BEGIN AND 中且为第一句 |
| 语法 | 必须加@符号，不用限定类型 | 除SELECT外都不加@符号，需要限定类型 |
存储过程是一组编译好的SQL语句的集合。
2、存储过程的好处提高代码的重用率；简化操作；减少编译次数和连接次数。
3、存储过程创建语法：CREATE PROCEDURE 存储过程名（参数列表） BEGIN 一条或多条SQL语句; END;
注意：
①参数列表包含三部分：参数模式、参数名、参数类型。
参数模式： =IN:该参数可以作为输入,需要输入方传入值。 OUT:该参数可以作为输出，可以作为返回值。 INOUT:该参数既可以作为输入也可以作为输出。
②如果存储过程体只有一句话,BEGIN AND 可以省略。
③存储过程体中的每条SQL语句的结尾要求必须加分号，
④存储过程的结尾可以使用 DELIMITER 重新设置，语法如下： DELIMITER 结束标记。例如:DELIMITER $;即将结束标记设置为 $。
CALL 存储过程名(实参列表);5、使用案例
以下代码需要的命令行窗口操作。
(1)创建空参列表的存储过程#案例1：插入到admim表中5条记录。
DELIMITER $
CREATE PROCEDURE myp3()
BEGIN
INSERT INTO admin(username,PASSWORD)
VALUES('join','000'),('ffs','001'),('jo','002'),('joi','004'),('r1','003');
END $
CALL myp1()$ #调用
(2)创建带in模式参数的存储过程。
#案例1：创建存储过程实现，根据女神名查询对应的男神信息。
DELIMITER $ #设置结束符为$
CREATE PROCEDURE myp2(IN beautyName VARCHAR(10))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON bo.id=b.boyfriend_id
WHERe b.name=beautyName;
END $
CALL myp2('柳岩')$ #调用
#案例2：创建存储过程实现，用户是否登录成功。
DELIMITER $ #设置结束符为$
CREATE PROCEDURE
myp4(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 myp4('张飞','hshs')$ #调用
(3)创建带out模式的存储过程
#案例1：根据女生名，返回对应男生名。
DELIMITER $ #设置结束符为$
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyname INTO boyName
FROM boys bo
JOIN beauty b ON bo.id=b.boyfriend_id
WHERe b.name=beautyName;
END $
CALL myp5('小昭',@bName)$ #调用
SELECt @bName$ #查询
#案例2:根据女神名,返回对应的男神名和男神魅力值
DELIMITER $ #设置结束符为$
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN
SELECT bo.boyname,bo.userCP INTO boyName,userCP
FROM boys bo
JOIN beauty b ON bo.id=b.boyfriend_id
WHERe b.name=beautyName;
END $
CALL myp6('小昭',@bName,@usercp)$ #调用
SELECt @bName,@usercp$ #查询
(4)创建带inout模式参数的存储过程
#案例1：定义两数a和b,求两数的2倍的结果为。
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;
6、删除和查看存储过程
#删除存储过程 DROP PROCEDURE 存储过程名; #查看存储过程 SHOW CREATE PROCEDURE 存储过程名;三、函数
函数和存储过程类似，都可以提高代码的重用率，简化操作，减少编译次数和连接次数。
1、函数和存储过程的区别存储过程：可以有0个返回，也可以有多个返回，适合做批量的插入、更新。
函数：有且仅有一个返回，适合做数据处理返回一个结果。
CREATE FUNCTION 函数名(参数列表) RETURNS 返回值的类型
BEGIN
函数体;
RETURN 返回值;
END
注意：
①参数列表包含两部分：参数名 参数类型。
②函数体：里边要有return语句，如果没有会报错，一般return放在最后。
③函数体中仅有一句话，则可以省略begin end。
④使用delimiter语句设置结束标记。
SELECT 函数名(参数列表);4、案例演示
以下代码需要的命令行窗口操作。
(1)无参有返回#案例1：返回公司的员工个数 SET GLOBAL log_bin_trust_function_creators = 1;#先设置二进制文件开启 DELIMITER $ 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 @a=0; #定义用户变量
SELECT salary INTO @a #为变量赋值
FROM employees
WHERe last_name=empName;
RETURN @a;
END $
SELECt myf2('Chen')$
#案例2：根据部门名，返回该部门平均工资。
CREATE FUNCTION myf3( dName 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=dName;
RETURN sal;
END $
SELECT myf3('IT')$
#案例3：创建函数，实现传入两个float，返回二者之和
CREATE FUNCTION myf4(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
SET @sum=num1+num2;
RETURN @sum;
END $
5、函数的查看和删除
(1)函数的查看
SHOW CREATE FUNCTION 函数名;(2)函数的删除
DROP FUNCTION 函数名;四、流程控制 1、分支结构 (1)IF函数
①语法: IF(表达式1,表达式2,表达式3)
②执行顺序：如果表达式1成立，则返回表达式2的值，否则返回表达式3的值。
①类似于Java中的switch 语句，一般用于实现等值判断。
CASE 变量|表达式|字段 WHEN 要判断的值 THEN 返回的值1或【语句;】 WHEN 要判断的值 THEN 返回的值2或【语句;】 ... ELSE 要返回的值n或【语句 ;】 #else可省略 END 【CASE】;
②类似于Java中的多重if语句，一般用于实现区间判断。
CASE WHEN 要判断的条件1 THEN 返回的值1或【语句;】 WHEN 要判断的条件2 THEN 返回的值2或【语句;】 ... ELSE 要返回的值n或【语句;】 #else可省略 END 【CASE】;
③特点
可以作为表达式，嵌套在其他语句中使用，可以放在任何地方，每个 THEN 语句结尾没有分号；
可以作为独立的语句去使用，只能放在 BEGIN AND 中，每个 THEN 语句结尾都有分号。
如果满足判断条件，则执行对应的 THEN 后面的语句，并且结束 CASE， 如果都不满足则执行 ELSE ， ELSE 可以省略，若 ELSE 省略，则返回 NULL 。
④案例：创建存储过程，根据传入的成绩来显示等级，比如传入的成绩90-100 显示A，80-90显示B，70-80显示C，其余全为D。
DELIMITER $ CREATE PROCEDURE gra(IN score INT) BEGIN CASE WHEN score>90 AND score<=100 THEN SELECT 'A'; WHEN score>80 AND score<=90 THEN SELECT 'B'; WHEN score>70 AND score<=80 THEN SELECT 'C'; ELSE SELECT 'D'; END CASE; END $ CALL gra(90)$(3)IF结构
①功能和Java中的if elseif类似， 只能应用在 begin and 中。
② 语法：
BEGIN IF 条件1 THEN 语句; ELSEIF 条件2 THEN 语句2; ··· 【 ELSE 语句n;】 END IF; END
③案例：创建存储过程，根据传入的成绩来返回等级，比如传入的成绩90-100 返回A，80-90返回B，70-80返回C，其余全返回D。
CREATE FUNCTION myf5(score INT) RETURNS CHAR BEGIN IF score>90 AND score<=100 THEN RETURN 'A'; ELSEIF score>80 AND score<=90 THEN RETURN 'B'; ELSEIF score>70 AND score<=80 THEN RETURN 'C'; ELSE RETURN 'D'; END IF; END$ SELECT myf5(89) 等级;2、循环结构
循环结构只能放在 begin and里边。
(1)分类while----先判断后执行；
loop----可用来模拟简单死循环,要跳出要搭配循环控制；
repeat----先执行后判断；
循环控制：
iterate类似于continue，结束本次循环；
leave类似于break，结束当前所在的循环。
#1、while---先判断后执行 【标签：】 while 循环条件 do 循环体; end while 【标签】; #2、loop---死循环 可用来模拟简单死循环,要跳出要搭配循环控制 【标签:】 loop 循环体; end loop 【标签】; #3、repeat --先执行后判断 至少执行一次，类似do while 【标签：】 repeat 循环体; until 结束循环的条件 end repeat 【标签】;(3)案例：
①案例1(没有添加循环控制语句)：批量插入，根据次数插入到admin表中多条记录。
DROP PROCEDURE IF EXISTS test_while$
CREATE PROCEDURE test_while(IN insertcount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<= insertcount DO
INSERT INTO admin(username,PASSWORD)
VALUES(CONCAT('shi',i),'8888');
SET i=i+1;
END WHILE a;
END$
CALL test_while(100)$
②案例2(添加leave语句)：批量插入，根据次数插入到admin表中多条记录，如果次数大于20则停止。
TRUNCATE TABLE admin$
DROP PROCEDURE test_while$
CREATE PROCEDURE test_while(IN insertcount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<= insertcount DO
INSERT INTO admin(username,PASSWORD)
VALUES(CONCAT('xia',i),'8888');
IF i>=20 THEN LEAVE a;
END IF;
SET i=i+1;
END WHILE a;
END$
DELIMITER $
CALL test_while(100)$
③案例3：批量插入,根据次数插入到admin表中多条记录，只插入偶数次数，且大于20停止。
TRUNCATE TABLE admin$ #删除表里的所有数据
DROP PROCEDURE test_while$ #删除存储过程
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('xia',i),'8888');
END WHILE a;
END$
CALL test_while1(100)$
