SQL 变量、流程控制、函数/存储过程、事件

变量

局部变量

位于 begin … end 中，语句块执行完后即结束生命周期

  • 声明局部变量 declare var [, …] varType [defualt initVal];
    默认初始值为null，后续通过set赋值
    必须放于begin…end函数体最前面的位置
用户变量

定义在当前客户端的连接下的变量，连接断开后结束生命周期。

  • 无需声明，直接通过set赋值使用即可。
    用户变量的变量名必须以 @ 开头
    查看一个不存在的用户变量时返回null
变量赋值
  1. set赋值
SET @a = 100, @b = 200;
SET @c :=300;
SELECT @a , @b , @c;
  1. select 赋值
SELECT @c :=300;
  1. select into 赋值
    通过select语句将所查询出的字段数据依次赋值到 into 后的变量中
select num, price into  data1, data2 from test2 where num = idx;

临时表

临时表只在当前连接可见（类似用户变量），连接断开后生命周期结束

创建临时表
  • 通过CREATE TEMPORARY TABLE创建的临时表
    同一连接中重复创建，会导致报错（Table '临时表名' already exists），因此需配合if not exist或DROP TEMPORARY TABLE IF Exists使用
drop TEMPORARY Table IF Exists temp_table_name;
CREATE TEMPORARY TABLE IF NOT EXISTS temp_table_name AS
(
    SELECT *  FROM admit_record
);
select * from temp_table_name;

  • 通过select into创建的临时表
    MySQL不支持该语法，仅能用于变量赋值

  • 派生表
    出现在from子句的独立子查询，称为派生表。
    派生表必须具有别名，否则报错：Every derived table must have its own alias

SELECT
    COUNT(*) 
FROM
    ( SELECT DISTINCT gender FROM users ) AS user_gender;
  • 物化表
    出现在where子句的独立子查询，称为物化表。
SELECT
    * 
FROM
    users 
WHERE
    gender IN ( SELECT gender FROM users2 WHERE gender = 1 );
删除临时表

以下两种都可以删除临时表，但推荐使用后者，防止误删永久表。

DROP TABLE 临时表名;
DROP TEMPORARY TABLE 临时表名;

流程控制

if
 if condition then
        statements
    [ elseif condition then
        statements ]
    [ else
        statements ]
    end if;
case
CASE 
WHEN condition THEN statements; 
ELSE statements;
while
[label:] while condition do
    statments
end while [label]
repeat
[label:] repeat
    statments
until condition
end repeat [label]
loop

死循环，需在过程中使用leave退出

[label:] LOOP
   statements
END LOOP [label]
leave 和 iterate

类似 break 和 continue，用于跳出循环

create
    function testLeave()
    returns varchar(255)
begin
    declare i,j int default 0;
    loop1: while (i<=5) do 
        set j = 0;
        while (j<=i) do
            if(j = 3) then
                leave loop1;
            end if;
            set j = j + 1;
        end while; 
        set i = i + 1;
    end while loop1;
    return concat('i: ', i, ' j:', j);
end;

函数与存储过程

模块化，方便复用，可在内部通过局部变量和流程控制语句实现复杂逻辑
避免了处理复杂逻辑时，数据从数据库和服务器的多次传输，所有操作都在数据库完成，提升了性能。
但因不方便调试，通常只用于数据逻辑。业务逻辑应在服务器端处理。

内置函数

SQL 拥有很多可用于计数和计算的内建函数。

  • AVG
    返回数值列的平均值。NULL 值不包括在计算中。
  • COUNT
    返回匹配指定条件的行数。通常写作count(*)或count(1)。如填具体列，则返回该字段不为null的行数
  • FIRST
    返回指定的字段中第一个记录的值。
  • LAST
    返回指定的字段中最后一个记录的值。
  • MAX
    返回一列中的最大值。NULL 值不包括在计算中。
  • MIN
    返回一列中的最小值。NULL 值不包括在计算中。
  • SUM
    返回数值列的总数。
  • CONCAT 拼接
SELECT concat(admit_name,'-',admit_type) as "名称-类型",count(*) from admit_record
  • UCASE
    把字段的值转换为大写。
  • LCASE
    把字段的值转换为小写。
  • MID （LEFT，RIGHT）
    用于从文本字段中提取字符。
MID(字符串,开始位置[,截取长度,不填则到底])
LEFT(字符串,截取长度)
RIGHT(字符串,截取长度)
  • LEN
    返回文本值的长度。
  • ROUND
    用于把数值字段舍入为指定的小数位数。
SELECT ROUND(列名,小数位数) FROM table_name
  • NOW / GETDATE()
    返回当前的日期和时间。
  • FORMAT
    对字段的显示进行格式化。
SELECT FORMAT(column_name,format) FROM table_name
SELECT ProductName, UnitPrice, FORMAT(GETDATE(),'yyyy-MM-dd hh:mm:ss') as PerDate
FROM Products
  • VERSION
    当前MySQL版本
select version();
窗口函数

MySQL8开始支持窗口函数函数，只能写在 select 子句中。
函数位置支持row_number、first_value等专用窗口函数，以及sum、count、max等聚合函数：<函数>() over (partition by <用于分组的列名> order by <用于排序的列名>)
示例，输出结果按type分类并排序，且额外输出一列每个type内部按value排序的序号：

SELECT *, max(value) over ( PARTITION BY `type` ORDER BY `value` ASC ) FROM `test`;
自定义函数

通过function声明
函数体中必须通过return返回一个值。当函数体为复合结构时，需使用begin/end 语句。
和内置函数一样，可以直接在SQL语句中通过函数名()使用

 CREATE  
        [DEFINER = { user | CURRENT_USER }]
        FUNCTION functionName ( varName varType [, ... ] )
        RETURNS returnVarType
        [characteristic ...] 
        routine_body

CREATE FUNCTION hello(param int) RETURNS int
    DETERMINISTIC
BEGIN
    RETURN (select sum(advert_banner.id) from advert_banner) + param;
END;


select hello(9999)
  • characteristic 配置
    MySQL强制要求：开启bin-log后，在主服务器上，除非子程序（函数、存储过程、触发器）被声明为确定性的或者不更改数据，否则创建或者替换子程序将报错（因不确定的数据改变会导致主从服务器执行结果不同）。
    因此创建子程序时应指定特性为DETERMINISTIC或NO SQL或READS SQL DATA：
    • NOT DETERMINISTIC 默认值，表示不确定
    • DETERMINISTIC 确定
    • NO SQL 没有SQL语句
    • CONTAINS SQL 有SQL语句
    • READS SQL DATA 只读
    • MODIFIES SQL 会改变数据
存储过程

通过procedure声明
通过call 存储过程名调用
通过IN、OUT、INOUT定义入参和返回值

CREATE PROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类型...])`
begin
  sql语句
end;

call 存储过程名;
  • IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
  • OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
  • INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
一些注意点

  1. 替换结束符
    命令行中;作为结束符会执行语句。为了防止函数/存储过程在定义时，其过程体中的;执行语句，应使用delimiter暂时修改结束符。

  2. 删除函数/存储过程
    函数/存储过程在声明后会永久存在于数据库中，无需重复使用的函数/存储过程应在调用后通过drop删除。
    此外为防止重名问题，可在声明前先进行删除操作。此时需添加if exists避免因不存在而报错。

DELIMITER $$
DROP PROCEDURE IF EXISTS test_two $$
CREATE PROCEDURE test_two()
BEGIN
  DECLARE i INT DEFAULT 2;
  WHILE i < 17 DO
    INSERT INTO `role_permission_group`  (`role_id`, `pergroup_id`) VALUES ('1',i);
  SET i = i+1;
  end WHILE;

END$$
DELIMITER ;
CALL  test_two();
DROP PROCEDURE test_two;

事件

事件是MySQL自带的功能，用于执行定时任务。

  1. 查看已开启事件功能
SHOW VARIABLES LIKE 'event_scheduler';
SELECT @@event_scheduler;
  1. 启用事件功能
SET GLOBAL event_scheduler = ON;

并在配置文件中添加event_scheduler=ON并重启，否则mysql重启后事件重置。

  1. 编写事件
CREATE EVENT IF NOT EXISTS test123 ON SCHEDULE EVERY 3 SECOND
ON COMPLETION PRESERVE
DO INSERT INTO ceshisy(lpname) VALUES(NOW());
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容

  • SQL存储过程和函数（1）
    定义： 存储过程和函数是在数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL...
    一枚豪迈的胡萝卜阅读 3,762评论 1赞 0
  • SQL存储过程相关
    首先，什么是存储过程？存储过程是为了完成某个数据库中的特定个功能而完成的语句集。该语句集包括SQL语句（对数据的增...
    你的操作666阅读 2,828评论 0赞 1
  • SQL存储过程
    存储过程 存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，...
    Rannio阅读 3,582评论 0赞 0
  • MySQL一一sql的存储过程、触发器
    当前数据库中有一张acount表 一、存储过程的创建： （1）语法：mysql> CREATE PROCEDURE...
    Help_II阅读 1,859评论 0赞 0
  • 扯下窗帘，阳光倾泻而下。
    渐变的面目拼图要我怎么拼？ 我是疲乏了还是投降了？ 不是不允许自己坠落， 我没有滴水不进的保护膜。 就是害怕变得面...
    闷热当乘凉阅读 9,787评论 0赞 13