1. 变量

1.1 系统变量

1.1.1 全局变量

  • 作用域:服务器每次启动时将为所有的全局变量赋初始值,针对于所有的会话有效,但是不能跨重启
  • 查看所有全局变量
# 查看全局
SHOW GLOBAL VARIABLES;
  • 查看部分的全局变量的值
# 查看部分全局变量
SHOW VARIABLES LIKE '%chara%';
  • 查看指定的全局变量的值
SELECT @@global.autocommit;
SELECT @@tx_isolation;
  • 为某个指定的全局变量赋值
SET @@global.autocommit = 0;
SET GLOBAL AUTOCOMMIT = 0;

1.1.2 会话变量

  • 作用域:仅仅针对于当前会话(连接)有效
  • 查看所有的会话变量
SHOW SESSION VARIABLES;
  • 查看部分的会话变量
SHOW VARIABLES LIKE '%char%';
SHOW SESSION VARIABLES LIKE '%char%';
  • 查看指定的某个会话变量的值
SELECT @@tx_isolation;
SELECT @@session.tx_isolation;
  • 为某个会话变量赋值
SET @@session.tx_isolation = 'read-uncommitted';
SET SESSION TX_ISOLATION = 'read-committed';

1.2 自定义变量

1.2.1 说明

变量由用户定义,不是由系统生成的

使用步骤:

  • 声明
  • 赋值
  • 使用(查看、比较、运算)

1.2.2 用户变量

  • 作用域:针对于当前会话或连接有效,同与绘画变量的作用域,可以用在任何地方
  • 声明并初始化
set @用户变量名 = 变量值;
set @用户变量名:=值;
select @用户变量名:=值;
  • 赋值方式一
# 方式一:通过set或select,语法与声明和初始化相同
SET @name = 'john';
SET @name = 100;
  • 赋值方式二
select 字段 into 变量名 from 表;
  • 使用
select @变量名

1.2.3 局部变量

  • 作用域:仅仅在定义它的begin、end语句块中有效,应用在语句块的第一条语句
  • 声明:
declare 变量名 类型;
declare 变量名 类型 default 值;
  • 赋值方式一:
set 用户变量名 = 变量值;
set 用户变量名:=值;
select @用户变量名:=值;
  • 赋值方式二
select 字段 into 变量名 from 表;
  • 使用
select 局部变量名;

1.2.4 局部变量与用户变量对比

作用域定义和使用的位置语法
用户变量当前会话会话中的任何位置必须加@,不用限定类型
局部变量begin end只能在begin end的第一句一般不用加@,除非是select,需要限定类型

2. 存储过程

2.1 简介

  • 含义:一组预先编译好的SQL语句的集合,可以理解成批处理语句
  • 好处:

    • 提高代码的重用性
    • 简化操作
    • 减少了编译次数和数据库的连接次数,提高了效率

2.2 创建

  • 语法:
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
   存储过程体;
END

注意:

  • 参数列表包含三部分:

    • 参数模式

      • IN:该参数可以作为输入,需要调用者传入
      • OUT:该参数作为输出,作为返回值
      • INOUT:该参数既可以作为输入,也可以作为输出
    • 参数名
    • 参数类型
  • 如果存储过程仅有一句话,BEGIN END通常可以省略,存储过程体中的每条语句结尾要加分号,存储过程的结尾可以使用DELIMITER 重新设置结尾符
  • 调用:CALL 存储过程名(参数列表)

2.2.1 空参存储过程

  • 示例:插入到admin表中5条记录
# 插入到admin表中五条记录
SELECT *
FROM
    admin;

DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
    INSERT INTO admin(username, password)
    VALUES
        ('john1', '0000'),
        ('john2', '0000'),
        ('john3', '0000'),
        ('john4', '0000'),
        ('john5', '0000');
END $
# 调用
CALL myp1()$

2.2.2 IN模式参数

  • 示例一:创建存储过程,根据员工名查询对应部门的信息
CREATE PROCEDURE myp2(IN last_name VARCHAR(20))
BEGIN
    SELECT
        d.*
    FROM
        departments d
            RIGHT JOIN employees e ON d.department_id = e.department_id
    WHERE
        e.last_name = last_name;
END $

CALL myp2('john')$
  • 示例二:创建存储过程,判断登录是否成功
CREATE PROCEDURE myp4(IN username VARCHAR(20), IN password VARCHAR(20))
BEGIN
    DECLARE result VARCHAR(20);

    SELECT
        count(*)
    INTO result
    FROM
        admin
    WHERE
          admin.username = username
      AND admin.password = password;

    SELECT if(result > 0, '成功', '失败') result;
END$

CALL myp4('123', '123');

2.2.3 OUT模式参数

  • 示例:根据员工名,返回对应的部门名
CREATE PROCEDURE myp5(IN last_name VARCHAR(20), OUT department_name VARCHAR(20))
BEGIN
    SELECT
        d.department_name
    INTO
        department_name
    FROM
        departments d
            INNER JOIN employees e
                       ON d.department_id = e.department_id
    WHERE
        e.last_name = last_name;
END $
# 调用

CALL myp5('Lex', @dname)$
SELECT @bname$

2.2.4 INOU模式参数

  • 示例:传入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$

2.3 删除

  • 语法:
DROP PROCEDURE myp1;

2.4 查看信息

  • 语法:
SHOW CREATE PROCEDURE myp5;

3. 函数

与存储过程的区别:

  • 存储过程:可以有0个返回值,适合做批量插入、更新等
  • 函数:有且仅有一个返回值,适合处理数据并返回结果

3.1 创建和调用

  • 语法:
CREATE FUNCTION 函数名(参数名) RETURNS 返回类型
BEGIN
    函数体
END
  • 注意:

    • 参数列表分为两部分:参数名和返回类型
    • 函数体肯定会有return语句,如果return语句没有放在最后,也不会报错,但是不推荐
    • 需要使用DELIMITER $更改结尾符
  • 调用语法
SELECT 函数名(参数列表);

3.1.1 无参有返回

  • 示例:返回公司的员工个数
# 案例:返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
    DECLARE c INT DEFAULT 0;
    SELECT
        count(*)
    INTO c
    FROM
        employees;

    RETURN c;
END$

SELECT myf1()$

3.1.2 有参又返回

  • 示例:根据员工名返回工资
# 根据员工名返回工资
CREATE FUNCTION myf2(empname VARCHAR(20)) RETURNS DOUBLE
BEGIN
    SET @sal = 0;
    SELECT
        salary
    INTO @sal
    FROM
        employees
    WHERE
        last_name = empname;
END $

SELECT myf2('k_ing')$

3.2 查看和删除

  • 查看:
SHOW CREATE FUNCTION myf3;
  • 删除
DROP FUNCTION myf3;

4. 流程控制

4.1 分支结构

4.1.1 IF函数

  • 功能:实现简单分支
  • 语法:IF(表达式1, 表达式2, 表达式3);
  • 执行顺序:如果表达式1成立,返回表达式2的值,否则返回表达式3的值**
  • 应用:可以用在任何地方

4.1.2 CASE结构

  • 情况一:类似于java中的switch语句,一般用于实现等值判断
  • 语法:
CASE 变量|表达式|字段
WHEN 要判断的值 THEN 返回值1或语句1;
WHEN 要判断的值 THEN 返回值2或语句2;
......
ELSE 要返回的值n或语句;
END CASE;
  • 情况二:类似于java中的多重if,用于区间判断
  • 语法:
CASE
WHEN 要判断的条件 THEN 返回值1或语句;
WHEN 要判断的条件 THEN 返回值2或语句;
...
END 要返回的值n或语句;
END CASE;
  • 特点:

    • 可以作为表达式,嵌套在其他语句中使用,可以放在任何地方,也可以作为独立语句,只能放在BEGIN END
    • 如果WHEN中的值满足或条件成立,则执行对应的THEN后面的语句,并且结束CASE,如果都不满足,则执行ELSE中的语句,ELSE省略并且所有条件都不满足时,返回NULL
  • 示例:创建存储过程,根据传入的成绩,显示对应的等级
CREATE PROCEDURE test_case(IN score INT)
BEGIN
    CASE
        WHEN score >= 90 AND score <= 100 THEN SELECT 'A';
        WHEN score >= 80 THEN SELECT 'B';
        WHEN score >= 60 THEN SELECT 'C';
        ELSE SELECT 'D';
        END CASE;
END$

CALL test_case(95)$

4.1.2 IF结构

  • 功能:实现多重分支
  • 语法:
if 条件1 then 语句1:
elseif 条件2
...
[else 语句n]
  • 示例:创建存储过程,根据传入的成绩,显示对应的等级
CREATE FUNCTION test_if(score INT) RETURNS CHAR
BEGIN
    IF score >= 90 AND score <= 100 THEN
        RETURN 'A';
    ELSEIF score >= 80 THEN
        RETURN 'B';
    ELSEIF score >= 60 THEN
        RETURN 'C';
    ELSE
        RETURN 'D';
    END IF;
END $

SELECT test_if(86)$
最后修改:2023 年 11 月 14 日
如果觉得我的文章对你有用,请随意赞赏