10 变量
- 系统变量:全局变量、会话变量
- 自定义变量:用户变量、局部变量
10.1 系统变量
- 说明:变量由系统提供,不是用户定义,属于服务器层面
1、查看所有的系统变量
SHOW GLOBAL | [SESSION] VARIABLES;
2、查看所有满足条件的部分系统变量
SHOW GLOBAL | [SESSION] VARIABLES LIKE '%char%';
3、查看指定的某个系统变量的值
SELECT @@global | [SESSION] .系统变量名;
4、为某个系统变量赋值
方式一:
SET GLOBAL | [SESSION] 系统变量名 = 值;
方式二:
SET @@global | [SESSION] .系统变量名 = 值;
注意:如果是全局级别,则需要加GLOBAL,如果是会话级别,则需要加SESSION,如果不写,则默认sesion
10.2 自定义变量
- 说明:变量是用户自定义的,不是由系统的
- 使用步骤:声明、赋值、使用(查看,比较,运算等)
10.2.1 用户变量
作用域:针对于当前会话(连续)有效,同于会话变量的作用域;应用在任何地方,也就是BEGIN END里面或者外面
赋值的操作符: =或:=
①声明并初始化
SET @用户变量名=值;或 SET @用户变量名:=值;或 SELECT @用户变量名:=值;
②赋值(更新用户变量的值)
方式一;通过SET或SELECTSET @用户变量名=值;或 SET @用户变量名:=值;或 SELECT @用户变量名:=值;
方式二:通过SELECT INTO
SELECT 字段 INTO 变量名 FROM 表;
③使用(查看用户变量的值)
SELECT @用户变量名;
10.2.2 局部变量
- 作用域:仅仅在定义它的BEGIN END中有效
①声明
DECLARE 变量名 类型; DECLARE 变量名 类型 DEFAULT 值;
②赋值
方式一;通过SET或SELECTSET 局部变量名=值;或 SET 局部变量名:=值;或 SELECT @局部变量名:=值;
方式二:通过SELECT INTO
SELECT 字段 INTO 局部变量名 FROM 表;
③使用
SELECT 局部变量名;
11 存储过程和函数
存储过程和函数:类似于面向对象中的方法
好处:
1、提高代码的重用性
2、简化操作
11.1 存储过程
- 含义:一组预先编译好的SQL语句的集合,理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率
11.1.1 创建语法
CREATE PROCEDURE 存储过程名(参数列表) BEGIN 存储过程体(一组合法的SQL语句) END
注意:
1、参数列表包含三部分
参数模式 参数名 参数类型 举例: IN stuname VARCHAR(20) 参数模式: IN:该参数可以作为输入,也就是改参数需要方传入值 OUT:该参数可以作为输出,也就是该参数可以作为返回值 INOUT:该参数既可以作为输入又可以作为输出,也就是该参数既需要传入值,又可以返回值
2、如果存储过程体仅仅只有一句话,BEGIN END 可以省略
存储过程体中的每条SQL语句的结尾要求必须加分号。 存储过程的结尾可以使用DELIMITER重新设置 语法: DELIMITER 结束标记 案例: DELIMITER $
11.1.2 调用语法
#1、空参列表
#案例:插入到admin表中3条记录
SELECT * FROM admin;
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO amdin(username,'password')
VALUES('john','00000'),('lily','00000')('jack','00000');
END $
#调用
CALL myp1()$
#2、创建带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;
END $
#调用
CALL myp2('张飞')$
#3、创建带out模式的存储过程
#案例:根据女神名,返回对应的男神名
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyName INTO boyName
FROM boys bo
INNER JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name = beautyName;
END $
#调用
CALL myp5('小昭',@bname)$
SELECT @bname$
#4、创建带inout模式的存储过程
#案例:传入a和b两个值,最终a和b都翻倍并返回
CREATE PROCEDURE myp6(INOUT a INT,INOUT b INT)
BEGIN
SET a=a*2;
SET b=b*2;
END
#调用
SET @m=10$
SET @n=20$
CALL myp6(@m,@n)$
SELECT @m,@n$
11.1.3 删除存储过程
语法: DROP PROCEDURE 存储过程名
DROP PROCEDURE p1;
DROP PROCEDURE p2,p3;
11.1.4 查看存储过程的信息
DESC myp2;
SHOW CREATE PROCEDURE myp2;
11.2 函数
- 含义:一组预先编译好的SQL语句的集合,理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率
- 区别:
存储过程:可以有0个返回,也可以有多个返回,适合做批量插入、批量更新
函数:有且仅有有个返回,适合做处理数据后返回一个结果
11.2.1 创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
- 注意:
1、参数列表 包含两部分:
参数名 参数类型2、函数体:肯定会有RETURN语句,如果没有会报错
如果RETURN语句没有放在函数体的最后也不会报错,但不建议3、函数体中仅有一句话,则可以省略BEGIN END
4、使用DELIMITER语句设置结束标记
11.2.2 调用语法
SELECT 函数名(参数列表)
#-------------案列演示-----------------
#1、无参数返回
#案例:返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;#定义变量
SELECT COUNT(*) INTO c#赋值
FROM employees;
RETURN c;
END $
SELECT myf1()$
#2、有参数返回
#案例:根据部门名,返回该部门的平均工资
CREATE FUNCTION myf2(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE sal DOUBLE;
SELECT AVG(salary) INTO sal
FROM employees e
JOIN department d ON e.department_id = d.department_id
WHERE d.department_name=deptName;
RETURN sal;
END $
SELECT myf2('IT')$
11.2.3 查看函数
SHOW CREATE FUNCTION myf2;
11.2.4 删除函数
DROP FUNCTION myf2;
#案例:创建函数,实现传入两个float,返回二者之和
CREATE FUNCTION test_fun1(num1 FLOAT,nums FLOAT) RETURN FLOAT
BEGIN
DECLARE SUM FLOAT DEFAULT 0;
SET SUM = num1+nums;
RETURN SUM;
END $
SELECT test_funs(1,2)$