变量
变量分为系统变量
和用户自定义变量
系统变量
由系统定义,属于服务器层面。这些系统变量定义了当前Mysql服务实例的属性、特征。系统变量分为全局系统变量(需要加global关键字)
和会话级系统变量(需要加session关键字)
,如果不写,默认会话级别。
查看系统变量
#查看所有全局变量
SHOW GLOBAL VARIABLES [LIKE '%标识符%'];
#查看所有会话变量
SHOW SESSION VARIABLES [LIKE '%标识符%']; 或 SHOW VARIABLES [LIKE '%标识符%'];
查看指定系统变量
#全局
SELECT @@global.变量名;
#会话
SELECT @@session.变量名; 或者 SELECT @@变量名;
修改系统变量
- 方式一:修改Mysql的配置文件(windows是my.ini,linux是my.cnf),重启服务
- 方式二:在服务运行期间,使用set命令重新设置系统变量。语法:
SET @@global.变量名=变量值;
SET GLOBAL 变量名=变量值;
SET @@session.变量名=变量值;
SET SESSION 变量名=变量值;
用户变量
用户变量是用户自己定义的,分为会话用户变量和局部用户变量。
- 会话级用户变量:在当前会话生效
- 局部变量:只有BEGIN和END语块中生效
会话用户变量定义赋值
SET @变量名 = 变量值;
SET @变量名 := 变量值;
SELECT @变量名 := 表达式;如:(select @var := count(*) from t1)
SELECT 表达式 into @变量名 [FROM 等子句];
局部用户变量定义赋值
BEGIN
#声明局部变量(必须是BEGIN下的第一句)
DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值];
DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];
#为局部变量赋值
SET 变量名1 = 值;
SELECT 值 INTO 变量名2 [FROM 子句];
#查看局部变量的值
SELECT 变量1,变量2,变量3;
END
定义条件与处理程序
定义条件就是给错误设置个别名,处理程序就是定义在遇到错误时的处理方式。
定义条件
DECLARE 错误名称 CONDITION FOR 错误码(或者错误条件)
错误码说明:
MySQL_error_code
和sqlstate_value
都可以表示mysql的错误- MySQL_error_code是数值错误类型代码
- sqlstate_value是长度为5的字符串错误代码
- 例如,在ERROR 1418 (HY000)中,1418是MySQL_error_code,'HY000’是sqlstate_value。
举例:
定义“Field_Not_Be_NULL”错误名与MySQL中违反非空约束的错误类型是“ERROR 1048 (23000)”对应。
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;
或者
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE ‘23000’;
处理程序
可以为SQL执行过程中发生的某种类型的vu哦呜定义特殊的处理程序。语法如下:
DECLARE 处理方式 HANDLER FOR 错误类型 处理语句
- 处理方式:处理方式有三个取值:CONTINUE、EXIT、UNDO。
- CONTINUE:遇到错误不处理,继续执行.
- EXIT:遇到错误推出执行。
- UNDO:遇到错误撤销执之前的操作。
- 错误类型:
- SQLSTATE ‘字符串错误码’: 表示长度为5的sqlstate_value类型的错误代码
- MySQL_error_code :匹配数值类型错误代码
- 错误名称 :上面说的定义条件
- SQLWARNING :匹配所有以01开头的SQLSTATE错误代码
- NOT FOUND :匹配所有以02开头的SQLSTATE错误代码
- SQLEXCEPTION :匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码
- 处理语句:如果出现上述条件之一,则采用对应的处理方式,并执行指定的处理语句。语句可以是像“ SET 变量 = 值 ”这样的简单语句,也可以是使用 BEGIN … END 编写的复合语句。
流程控制
作用就是控制sql语句的执行顺序。有以下三类:
- 条件判断:IF语句和CASE语句
- 循环语句:LOOP、WHILE和REPEAT语句
- 跳转语句:ITERATE和LEAVE语句
分支结构
IF
IF 返回布尔值的表达式 THEN 操作1
ELSEIF 返回布尔值的表达式 THEN 操作2
ELSE 操作3
END IF
CASE
#情况一,类似switch
CASE 表达式
WHEN 值1 THEN 操作一
WHEN 值2 THEN 操作二
END CASE
循环结构
LOOP
loop_label:LOOP
#循环体[
SET id = id + 1;
IF id >= 10 THEN LEAVE loop_label;
END IF;
#循环体]
END LOOP loop_label;
WHILE
WHILE 循环条件 DO
循环体
END WHILE;
REPEAT
REPEAT
循环体
UNITL 结束循环的条件
END REPEAT;
跳转语句
LEAVE
用在循环语句内(loop语法有写),或者以BEGIN和END包裹的程序体内。可以理解为java的break
LEAVE 标记名;
ITERATE
只能用在循环语句,类似java的continue;
游标
是一种临时的数据库对象,可以指向存储在数据库表中的数据行指针。
游标的使用
- 第一步,声明游标
DECLARE 游标名称(cursor_name) CURSOR FOR 查询语句(select * from test)
- 第二步,打开游标
OPEN cursor_name;
- 第三步,使用游标(从游标中获取数据)
FETCH cursor_name INTO 变量一(var_name1) ...
- 第四步,关闭游标
CLOSE cursor_name
举例
创建存储过程“get_count_by_limit_total_salary()”,声明IN参数 limit_total_salary,DOUBLE类型;声明OUT参数total_count,INT类型。函数的功能可以实现累加薪资最高的几个员工的薪资值,直到薪资总和达到limit_total_salary参数的值,返回累加的人数给total_count。
DELIMITER //
CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT total_count INT)
BEGIN
DECLARE sum_salary DOUBLE DEFAULT 0; #记录累加的总工资
DECLARE cursor_salary DOUBLE DEFAULT 0; #记录某一个工资值
DECLARE emp_count INT DEFAULT 0; #记录循环个数
#定义游标
DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
#打开游标
OPEN emp_cursor;
REPEAT
#使用游标(从游标中获取数据)
FETCH emp_cursor INTO cursor_salary;
SET sum_salary = sum_salary + cursor_salary;
SET emp_count = emp_count + 1;
UNTIL sum_salary >= limit_total_salary
END REPEAT;
SET total_count = emp_count;
#关闭游标
CLOSE emp_cursor;
END //
DELIMITER ;
小结
游标是 MySQL 的一个重要的功能,为 逐条读取 结果集中的数据,提供了完美的解决方案。跟在应用层面实现相同的功能相比,游标可以在存储程序中使用,效率高,程序也更加简洁。
但同时也会带来一些性能问题,比如在使用游标的过程中,会对数据行进行 加锁 ,这样在业务并发量大的时候,不仅会影响业务之间的效率,还会 消耗系统资源 ,造成内存不足,这是因为游标是在内存中进行的处理。