MySQL学习笔记之变量、流程控制和游标

最新推荐文章于 2024-06-28 13:15:25 发布
最新推荐文章于 2024-06-28 13:15:25 发布
阅读量258 收藏
点赞数
分类专栏： 数据库 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_37475168/article/details/131018356
版权

文章目录

变量

系统变量

系统变量包括全局系统变量和会话系统变量，分别用global和session修饰。不加修饰符的话，默认是会话系统变量。

查看

查看全局系统变量：

# 全局系统变量
SHOW GLOBAL VARIABLES;

查看会话系统变量，有两种方式：

# 会话系统变量
SHOW SESSION VARIABLES;

# 会话系统变量
SHOW VARIABLES;
  • 全局系统变量和会话系统变量有交集；
  • 某个会话对会话系统变量的修改，不会同步到其他会话；
  • 但如果修改的是全局系统变量，此修改会同步到修改后开启的所有会话；
  • 系统变量针对的是当前MySQL服务实例，所以MySQL重启，系统变量会恢复到默认值。

查看指定系统变量：

SELECT
    @@GLOBAL .max_connections;

SELECT
    @@SESSION .character_set_client;

SELECT
    @@GLOBAL .character_set_client;

SELECT
    @@character_set_client;

如果某个系统变量既是系统又是会话，则默认显示会话值：

SELECT
    @@SESSION .pseudo_thread_id;

上例说明全局系统变量和会话系统变量有交集。

修改系统变量

有三种方式，任选其一即可：

  1. 修改配置文件my.ini,重启MySQL
  2. set @@global/@@session.变量名 = 变量值
  3. set global/session 变量名 = 变量值
SET @@GLOBAL .max_connections = 161;

SET GLOBAL max_connections = 171;

第一种方式永久有效，后两种方式重启或开启新会话后恢复默认值(mysql.ini中的值)。

用户变量

用户变量分为会话用户变量和局部用户变量。
会话用户变量只对当前连接会话有效，局部用户变量只在存储过程或函数中使用，且只在其中有效。

会话用户变量定义

用=和:=均可：

SET @var1 = 1;

SET @var2 = 'Abel';

SET @var3 := 3;

SET @sum = @var1 + @var3;

查看用户变量

SELECT @sum;

通过select语句创建会话用户变量

有两种方式，任选其一。方式1：

# 方式1
SELECT
    @count := count(*)
FROM
    employees;
SELECT
    @count;

方式2：

# 方式2
SELECT
    avg(salary) INTO @avg_salary
FROM
    employees;

SELECT
    @avg_salary;

局部用户变量

局部用户变量可以通过declare语句声明，但需要放到begin..end语句开头，赋值方式跟会话用户变量相同。
下面用一个例子加以说明，在存储过程中使用局部变量，注意看其中的注释：

CREATE PROCEDURE test_var_local ()
BEGIN
    # 局部用户变量声明
    DECLARE
        a INT DEFAULT 0;
DECLARE
    b,
    c INT;

DECLARE
    name_ VARCHAR (25);

# 局部用户变量赋值
SET a = 1;

SET b = 2;

SET c := 4;

SELECT
    last_name,
    salary INTO name_,
    c
FROM
    employees
WHERE
    employees.employee_id = 101;

# 局部用户变量读取
SELECT
    a,
    b,
    c,
    name_;

END;

CALL test_var_local ();

DROP PROCEDURE test_var_local;

DESC employees;

再看一个例子，创建存储过程，返回员工和他领导的薪资差距：

CREATE PROCEDURE diff_salary (
    IN emp_id INT,
    OUT dif_salary DOUBLE
)
BEGIN
    DECLARE # 局部用户变量声明
        worker_salary,
        manager_salary DOUBLE;

SELECT
    employees.salary,
    worker.worker_salary INTO manager_salary,
    worker_salary
FROM
    employees,
    (
        SELECT
            employees.manager_id manager_id,
            employees.salary worker_salary
        FROM
            employees
        WHERE
            employees.employee_id = emp_id
    ) worker
WHERE
    employees.employee_id = worker.manager_id;

SELECT
    manager_salary - worker_salary INTO dif_salary;

END;

测试：

SET @dif_salary = 0; # 会话变量定义

CALL diff_salary (103, @dif_salary);

SELECT
    @dif_salary;

DROP PROCEDURE diff_salary;

流程控制

MySQL中也有对判断、循环这种基本流程控制的支持，其中：

  • 判断语句包括if、case……when；
  • 循环语句包括loop、while、repeat，退出循环可用leave，跳过当前迭代可用iterate。

IF语句

SELECT
    last_name,

IF (
    salary >= 6000,
    "high_salary",
    "low_salary"
) salary_level
FROM
    employees;

SELECT
    last_name, commission_pct, IF(commission_pct is not NULL, commission_pct, 0) "pct",
    salary * 15 + IF(commission_pct is not NULL, commission_pct * 1.2, 0) "annual"
FROM
    employees;

存储过程或函数中的if语句：

CREATE PROCEDURE test_if ()
BEGIN

# 局部变量声明
DECLARE
	age INT DEFAULT 20;

# if...elseif...else判断分支
IF age > 40 THEN
    SELECT
        'middle or old';

ELSEIF age > 18 THEN
    SELECT
        'young';

ELSEIF age > 12 THEN
    SELECT
        'youth';

ELSE
    SELECT
        'child';

END
IF;
# 判断分支结束

END;
# 存储过程结束

CALL test_if ();

再看一个例子，入职五年的员工涨薪500：

CREATE PROCEDURE update_salary_by_eid1 (IN emp_id INT)
BEGIN

# 局部变量声明
DECLARE
	hire_year DOUBLE;

DECLARE
    current_salary DOUBLE;

# 执行语句定义
SELECT
    salary,
    hire_date INTO current_salary,
    hire_year
FROM
    employees
WHERE
    employee_id = emp_id;

## if判断分支
IF DATEDIFF(CURDATE(), hire_year) / 365 > 5 THEN
    UPDATE employees
SET salary = current_salary + 500
WHERE
    employee_id = emp_id;

END
IF;
## 判断分支结束

END;
# 存储过程结束

# 存储过程调用
CALL update_salary_by_eid1 (103);

CALL update_salary_by_eid1 (104);

再看一个例子：

  • 工资< 9000者，更新工资为9000；
  • 9000 <= 工资 < 10000 且commission_pct为null者，更新commission_pct为0.01；
  • 其余者，加薪100：
CREATE PROCEDURE update_salary_by_eid2 (IN emp_id INT)
BEGIN

# 局部变量声明
DECLARE
	current_salary DOUBLE;

DECLARE
    comm_pct DOUBLE;

# 定义执行语句
SELECT
    salary,
    commission_pct INTO current_salary,
    comm_pct
FROM
    employees
WHERE
    employee_id = emp_id;

## if...elseif...else判断分支
IF current_salary < 9000 THEN
    UPDATE employees
SET salary = 9000
WHERE
    employee_id = emp_id;

ELSEIF current_salary < 10000
AND comm_pct IS NULL THEN
    UPDATE employees
SET commission_pct = 0.01
WHERE
    employee_id = emp_id;

ELSE
    UPDATE employees
SET salary = current_salary + 100
WHERE
    employee_id = emp_id;

END
IF;
## 判断分支结束

END;
# 存储过程结束

# 存储过程调用
CALL update_salary_by_eid2 (102);

CALL update_salary_by_eid2 (103);

CALL update_salary_by_eid2 (104);

CASE WHEN … THEN … ELSE … END语句

相当于java里的switch-case：

SELECT
    last_name,
    salary,
    CASE # case...when语句
WHEN salary >= 15000 THEN
    "A"
WHEN salary >= 10000 THEN
    "B"
WHEN salary >= 5000 THEN
    "C"
ELSE
    "D"
END "level" ## case...when默认值
FROM
    employees;

SELECT
    last_name,
    department_id,
    CASE  # case...when语句
WHEN department_id = 10 THEN
    salary * 1.1
WHEN department_id = 20 THEN
    salary * 1.2
WHEN department_id = 30 THEN
    salary * 1.3
END "salary" ## case...when默认值
FROM
    employees
WHERE
    department_id IN (10, 20, 30);

同样，该语句也可以用在存储过程或存储函数中：

# 流程控制之CASE语句
CREATE PROCEDURE test_case ()
BEGIN
    DECLARE
        v1 INT;

SET v1 = 20;

## case语句匹配具体值，相当于switch-case
CASE v1
WHEN 10 THEN
    SELECT
        "v1 = 10";
WHEN 20 THEN
    SELECT
        "v1 = 20";
WHEN 30 THEN
    SELECT
        "v1 = 30";
ELSE
    SELECT
        "v1 is other value";
END CASE;

SET v1 = v1 + 10;
## case语句匹配范围，相当于if-else if-else
CASE
WHEN v1 < 20 THEN
    SELECT
        "v1 < 20";
WHEN v1 < 40 THEN
    SELECT
        "20 <= v1 < 40";
WHEN v1 < 60 THEN
    SELECT
        "40 <= v1 < 60";
ELSE
    SELECT
        "v1 is in other range";
END CASE;

END;

CALL test_case ();

循环结构

循环结构包括loop、while和repeat三种循环。
其中while循环和repeat循环自带循环控制，而loop循环的循环控制需要用循环标签和leave或iterate语句。

LOOP

CREATE PROCEDURE test_loop ()
BEGIN
    DECLARE
        num INT DEFAULT 1;

loop_label : # 循环标签
LOOP
# 循环体
SET num = num + 1;

IF num > 10 THEN
    LEAVE loop_label; # 退出循环，指定要退出的循环标签

END
IF;

END
LOOP
    loop_label; # 循环体结束

SELECT
    num;

END;

CALL test_loop ();

看一个案例：全体加薪10%，直到平均工资>=20000，并输出加薪次数：

CREATE PROCEDURE add_avg()
BEGIN
# 局部变量定义
DECLARE loop_count INT DEFAULT 0;
DECLARE avg_sal DOUBLE DEFAULT 0;

# 循环标签定义
add_salary:
    LOOP # 循环体
        SELECT AVG(salary) INTO avg_sal from employees;

        IF avg_sal >= 20000 THEN
            LEAVE add_salary;
        END IF;

        UPDATE employees SET salary = salary * 1.1;

        set loop_count = loop_count + 1;

    END LOOP; # 循环体结束

select loop_count;

# 存储过程结束
END;

# 存储过程调用
call add_avg();

# 存储过程执行效果查看
SELECT AVG(salary) from employees;

# 存储过程删除
drop procedure add_avg;

WHILE

create PROCEDURE test_while()
BEGIN
DECLARE num INT DEFAULT 1;

WHILE num <= 10 DO # 循环条件
    set num = num + 1; # 循环体
END WHILE; # 循环体结束

select num;

END;

call test_while();

看一个案例：全体降薪至平均工资小于10000，输出平均工资和降薪次数：

CREATE PROCEDURE reduce_salary()
BEGIN
# 局部变量定义
DECLARE avg_sal double DEFAULT 0;
DECLARE reduce_count INT DEFAULT 0;

SELECT AVG(salary) INTO avg_sal from employees;

# 循环体
WHILE avg_sal > 10000 DO
    update employees set salary = salary *0.9;
    SELECT AVG(salary) INTO avg_sal from employees;
  set reduce_count = reduce_count + 1;
END WHILE;
# 循环体结束

select avg_sal, reduce_count;

# 存储过程结束
END;

#　存储过程调用
call reduce_salary();

# 存储过程删除
DROP procedure reduce_salary;

REPEAT

CREATE PROCEDURE test_repeat()
BEGIN
# 局部变量定义
DECLARE num INT DEFAULT 1;

REPEAT # 循环开始
    SET num = num + 1; # 循环体
    UNTIL num >= 10 # 循环结束条件
END REPEAT; # 循环结束

select num;

# 存储过程结束
END;

#　存储过程调用
CALL test_repeat();

对比三种循环

REPEAT是先执行后判断，类似于java中的do-while;
WHILE则是先判断后执行，类似于java中的for和while；
LOOP配合LEAVE可以实现先执行后判断或先判断后执行。

LEAVE

CREATE PROCEDURE leave_begin(IN num INT)
begin_label: BEGIN
IF num <= 0 THEN LEAVE begin_label; # 退出某个标签
ELSEIF num = 1 THEN select avg(salary) from employees;
ELSEIF num = 2 THEN select min(salary) from employees;
ELSEIF num = 3 THEN select max(salary) from employees;
END IF;

select count(*) from employees;

# 存储过程结束
END;

# 存储过程调用
CALL leave_begin(0);
CALL leave_begin(1);
CALL leave_begin(2);
CALL leave_begin(3);
CALL leave_begin(4);

2.5、ITERATE
ITERATE用于结束循环的当前迭代，类似于java里的continue：

CREATE PROCEDURE test_iterate()
BEGIN
# 局部变量定义
DECLARE num INT DEFAULT 0;

# 循环体
loop_label: LOOP
    SET num = num + 1;
    IF num > 0 and num % 3 > 0 THEN ITERATE loop_label; # 结束当前迭代，指定循环标签名
    ELSE SELECT num;
    END IF;

    IF num > 20 THEN LEAVE loop_label;
    END IF;
END LOOP;
# 循环体结束

# 存储过程结束
END;

# 存储过程调用
call test_iterate();

# 存储过程删除
DROP PROCEDURE test_iterate;

游标

游标用于记录定位，充当了表的行指针，但需要放到变量定义之后定义。游标使用时会锁定对应的表数据，导致其他对表的查询会失败，因此必须及时关闭游标。

案例：累加工资最高的员工的工资，直到超过阈值，最后返回累加次数：

CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE
                , OUT total_count INT)
BEGIN
DECLARE sum_sal DOUBLE DEFAULT 0;
DECLARE cur_sal DOUBLE DEFAULT 0; # 保存游标所在行中的数据
DECLARE iter_count INT DEFAULT 0;

DECLARE emp_cursor CURSOR FOR # 创建游标：declare 游标名 cursor for 查询语句;
    SELECT salary FROM employees ORDER BY salary DESC;

OPEN emp_cursor; # 打开游标

REPEAT
    FETCH emp_cursor INTO cur_sal; # 使用游标，注意游标中的字段要和into后的字段一一对应
    set sum_sal = sum_sal + cur_sal;
    set iter_count = iter_count + 1;
    UNTIL sum_sal > limit_total_salary
END REPEAT;

CLOSE emp_cursor; # 关闭游标

SELECT iter_count into total_count;

END; # 存储过程结束

# 存储过程调用，查看执行效果
set @total_count = 0;
call get_count_by_limit_total_salary(200000, @total_count);
SELECT @total_count;

# 删除存储过程
drop PROCEDURE get_count_by_limit_total_salary;

综上，可以总结书游标的使用四步骤：创建、打开、使用、关闭。

coder_szc
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySql学习笔记
weixin_45906989的博客
07-27 1322
MySql学习笔记
第16章_变量、流程控制与游标.docx
01-17
【第16章_变量、流程控制与游标】章节主要介绍了MySQL数据库中关于变量的使用，特别是系统变量和用户变量的分类、查看与修改。MySQL中的变量在存储过程和函数中发挥着重要作用，用于存储中间结果或最终输出。 1. **...
MySQL学习笔记8——游标
qq_44778824的博客
04-24 1436
本节学习了游标的使用方法，包括在存储过程中使用游标的4个步骤，分别是定义游标、打开游标、读取游标数据和关闭游标。除此之外，还介绍了经常与游标结合使用的流程控制语句，包括循环语句LOOP、WHILE和REPEAT；条件判断语句IF和CASE；还有跳转语句LEAVE和ITERATE。
MySQL之流程控制与游标
soberw的日常分享博客
02-20 674
MySQL之流程控制与游标 文章目录MySQL之流程控制与游标前言概念引入分支结构IF语句CASE语句循环结构LOOP语句WHILE语句REPEAT语句跳转语句LEAVE语句ITERATE语句游标 前言 本文是在看了康师傅的b站的尚硅谷视频 总结出来的笔记以及见解 点击跳转 概念引入 学习或者了解过编程语言的都知道，任何一门编程语言都不可能只通过一两句语句代码完成。 流程控制语句的作用就是在程序运行过程中控制语句的执行顺序，是我们完成复杂操作必不可少的一部分。 只要是执行的程序，流程就分为三大类： 顺序结
MySQL-变量、流程控制与游标：变量、定义条件与处理程序、流程控制
小孔靠得住的博客
04-13 1006
变量 在MySQL数据库的存储过程和函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据。 定义条件是事先定义程序执行过程中可能遇到的问题，处理程序定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行。 流程就分为三大类： 顺序结构：程序从上往下依次执行 分支结构：程序按条件进行选择执行，从两条或多条路径中选择一条执行 循环结构：程序满足一定条件下，重复执行一组语句
MySQL（16）：变量、流程控制与游标
学习记录
11-14 393
MySQL 学习笔记
【MySQL必知必会】第24章 使用游标(学习笔记)
Language_Participant的博客
07-13 329
其中fetch用来检索当前行的order列，将自动从第一行开始，放到名为o的局部声明的变量中，对检索出的数据不做处理。
mysql学习笔记
最新发布
m0_63086381的博客
06-28 996
主从复制允许将来自一个MySQL数据库服务器（主服务器）的数据复制到一个或多个MySQL数据库服务器（从服务器）。以上已经完成了主从复制（一主一从）配置，然而这只是一个基本配置，加上一个proxysql实现MySQL读写分离，proxysql支持MySQL协议的的数据库代理，程序访问的不再是具体的数据库，而是proxysql这个代理程序，用户请求发向proxysql，如果是写请求发往主节点，读请求发下从节点组中，以此实现读写分离一定程序上减轻了主数据库的io压力。
MySQL学习笔记-进阶篇-视图和存储过程
qingwufeiyang_530的专栏
06-22 520
用户自定义变量是用户根据需求自己定义的变量，用户变量不用提前声明，在用的时候，直接使用@变量名使用就可以。其作用域为当前连接。
MySQL学习笔记（自用）
xinshou_zhu的博客
05-28 1289
这篇文章记录的是MySQL进阶相关的一些知识点索引是帮助MySQL高效获取数据的一种数据结构这里主要记录两种索引：B+树索引和hash索引。在MySQL中，支持hash索引的是Memory存储引擎。而InnoDB中具有自适应hash功能，hash索引是InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。为什么InnoDB存储引擎选择使用B+tree索引结构?A. 相对于二叉树，层级更少，搜索效率高；
第16章_变量、流程控制与游标1
08-03
在本章中，我们主要探讨了SQL中的变量、流程控制以及游标的使用，这些都是数据库编程中的核心概念。首先，我们从变量开始。 1. 变量 在MySQL中，我们可以创建并使用变量来存储数据。这里展示了如何声明和使用局部...
第16章_变量、流程控制与游标3
08-03
它们在MySQL服务启动时初始化，并且根据其类型，系统变量可以分为全局系统变量和会话系统变量。全局变量对所有会话都有效，但其值的修改不会跨重启保留。会话变量仅对当前会话有效，其值可以在会话期间修改而不影响...
#第16章_变量、流程控制与游标.sql
01-07
#第16章_变量、流程控制与游标.sql
win10下使用hive时遇到的错误集锦
四国棋客
06-14 6654
背景 老子不知道hive在win10下怎么就这么多错.....不想用虚拟机，想换成mac，但是mac上没有实况19...真的是，我去年买了个表。 hive建表报错Column length too big for column 'PARAM_VALUE' (max = 21845); use BLOB or TEXT instead 解决方法：用navicat创建hive数据库，...
解决python查询neo4j时报错'Graph' object has no attribute 'data'
四国棋客
08-26 4502
背景 今天运行前些日子可以正常运行的demo时，报错 'Graph' object has no attribute 'data' 忘了以前的py2neo是什么版本，于是直接适配了最新的4.3.0 解决方法 把原来的查询语句 ret = self.graph.data("查询语句") 改成 ret = self.graph.run(cypher="查询语句").data()...
数据库CDC中间件学习之Maxwell
四国棋客
03-26 3337
文章目录介绍工作原理Maxwell与Canal的对比安装及配置前提条件在MySQL中打开binlog创建maxwell数据库操作Maxwell命令行测试插入数据更新数据删除数据Maxwell连接Kafka普通测试topic分区数据过滤数据表的全量输出 介绍 Maxwell是Zendesk开源的用java编写的MySQL实时抓取(CDC，Change Data Capture，变更数据读取)软件，通过实时读取MySQL的二进制日志Binlog生成json信息，再作为生产者将信息发给Kafka、控制台、redi
ClickHouse学习笔记之备份和恢复
四国棋客
11-19 2511
ClickHouse允许使用查询已创建表分区的本地副本，这是利用硬链接到目录中实现的，所以它通常不会因为旧数据而占用额外的磁盘空间。创建的文件副本不由ClickHouse服务器处理，所以不需要任何的外部系统就可以有一个简单的备份。为了防止硬件问题，最好将这些备份复制到另一台主机上，再删除本地副本。
MySQL学习笔记之SQL语句执行过程查看
四国棋客
07-29 2178
本文记录了用profile语句查看SQL语句执行过程的方法，包括查看性能数据
MySQL变量、流程控制与游标详解：系统变量与会话管理
本章节深入探讨MySQL数据库中的变量、流程控制和游标概念，这些都是高级SQL编程的重要组成部分。在MySQL环境中，变量被用来存储临时的数据，无论是查询结果的中间计算值还是最终输出，都依赖于变量的灵活运用。 ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值