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 局部变量与用户变量对比

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)$