变量、存储过程变量变量分类 mysql变量可分为两大类：系统变量：由系统提供，不是由用户定义的。包括全局变量、会话变

变量

变量分类

mysql变量可分为两大类：
- 系统变量：由系统提供，不是由用户定义的。包括全局变量、会话变量
- 用户自定义变量：用户定义的变量。包括用户变量、局部变量

局部变量

只能用在begin/end语句块中，比如存储过程中的begin/end语句块。

用户变量

用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以了。

会话变量

服务器为每个连接的客户端维护一系列会话变量
其作用域仅限于当前连接，即每个连接中的会话变量是独立的

全局变量

影响服务器整体操作，作用于所有会话
当服务启动时，它将所有全局变量初始化为默认值
更改全局变量，必须具有super权限
其作用域为server的整个生命周期，服务重启消失

使用系统变量

查看变量

查看所有系统变量

mysql> show global variables;   # 查看所有全局变量
mysql> show session variables;  # 查看当前会话变量

查看满足条件的部分变量

mysql> show global variables like '%char%';  # 不指定global的话，默认为会话变量

查看某个系统变量

# 变量结构为@@变量名、@@global.变量名、@@session.变量名
mysql> select @@tx_isolation;   # 默认为会话变量
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

mysql> select @@global.character_set_system;
+-------------------------------+
| @@global.character_set_system |
+-------------------------------+
| utf8                          |
+-------------------------------+
1 row in set (0.00 sec)


mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set (0.00 sec)

变量赋值

为系统变量赋值

set global|session 系统变量名=值
或
set @@global|session.系统变量名=值

示例：

mysql> set @@global.autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.autocommit;
+---------------------+
| @@global.autocommit |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)

使用用户变量

作用域

仅对当前会话有效，同于会话变量作用域

使用步骤

声明并初始化

SET @用户变量=值
或
SET @用户变量:=值
或
SELECT @用户变量:=值

赋值

SET @用户变量=值
或
SET @用户变量:=值
或
SELECT @用户变量:=值
或
SELECT 字段 INTO @用户变量 FROM 表

使用

SELECT @变量

示例：

mysql> set @user='tom';
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from employees into @count;
Query OK, 1 row affected (0.00 sec)

mysql> select @user;
+-------+
| @user |
+-------+
| tom   |
+-------+
1 row in set (0.00 sec)

mysql> select @count;
+--------+
| @count |
+--------+
|    133 |
+--------+
1 row in set (0.00 sec)

使用局部变量

作用域

仅在定义它的GEGIN/END中有效

使用步骤

声明

DECLARE 变量 类型
DECLARE 变量 类型 DEFAULT 值

赋值

SET 局部变量=值
或
SET 局部变量:=值
或
SELECT 局部变量:=值
或
SELECT 字段 INTO 局部变量 FROM 表

使用

SELECT 局部变量

存储过程

存储过程是可编程的函数，在数据库中创建并保存，可以由一组SQL语句和控制结构组成。
提高了代码的重用性
减少了编译次数并减少了和数据库的连接次数，提高了效率

使用存储过程

创建存储过程

语法：

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
    一组合法的sql语句;
END

存储过程前后最好使用DELIMITER //
参数列表包含三部分：
- 参数模式
  - IN：需要调用者传值，与Python函数的参数作用类似
  - OUT：该参数可以作为输入。与Python函数的返回值类似
  - INOUT：既可以作为输入又可以作为输出
- 参数名
- 参数类型

分隔符

MySQL默认以";"为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错
所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个"//"之间的内容当做存储过程的代码，不会执行这些代码
通过“DELIMITER ;”把分隔符还原。

调用存储过程

语法

CALL 存储过程(实参列表)

存储过程示例

空参列表

mysql> drop database if exists mydb;
mysql> create database if not exists mydb default charset utf8mb4;
mysql> use mydb;
mysql> create table departments like nsd2021.departments;

mysql> create procedure dep_pro()
    -> begin
    ->   insert into departments values
    ->   (1, '人事部'), (2, '财务部');
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> call dep_pro() //
Query OK, 2 rows affected (0.00 sec)

mysql> select * from departments //
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 人事部    |
|       2 | 财务部    |
+---------+-----------+
2 rows in set (0.00 sec)

mysql> delimiter ;
mysql> select * from departments;

使用IN参数

mysql> use nsd2021;

mysql> delimiter //
mysql> create procedure empcount_pro(IN dept_no int)
    -> begin
    ->   select dept_id, count(*) from employees
    ->   where dept_id=dept_no
    ->   group by dept_id;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> call empcount_pro(1)//
+---------+----------+
| dept_id | count(*) |
+---------+----------+
|       1 |        8 |
+---------+----------+
1 row in set (0.00 sec)

mysql> delimiter ;

使用OUT参数

mysql> use nsd2021;
mysql> delimiter //

mysql> create procedure empemail_pro(IN emp_name varchar(10), OUT mail varchar(25))
    -> begin
    ->   select email into mail
    ->   from employees
    ->   where name=emp_name;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> call empemail_pro('刘倩', @m)//
Query OK, 1 row affected (0.00 sec)

mysql> select @m//
+--------------------+
| @m                 |
+--------------------+
| liuqian@tarena.com |
+--------------------+
1 row in set (0.00 sec)

mysql> delimiter ;

使用INOUT参数

mysql> delimiter //
mysql> create procedure myadd(INOUT i int)
    -> begin
    ->   set i=i+100;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> set @n=8;
Query OK, 0 rows affected (0.00 sec)

mysql> call myadd(@n);
Query OK, 0 rows affected (0.00 sec)

mysql> select @n;
+------+
| @n   |
+------+
|  108 |
+------+
1 row in set (0.00 sec)

查看存储过程

mysql> select name from mysql.proc where db='nsd2021';
+--------------+
| name         |
+--------------+
| empcount_pro |
| empemail_pro |
| myadd        |
+--------------+
3 rows in set (0.00 sec)

mysql> show create procedure empemail_pro \G

删除存储过程

mysql> drop procedure myadd;
Query OK, 0 rows affected (0.00 sec)

流程控制结构

顺序结构：自上向下执行
分支结构：从多条路径中选择一条路径执行
循环结构：满足某种条件，反复执行一段代码

分支结构

if语句

语法：

IF 条件 THEN
  语句;
END IF;

IF 条件 THEN
  语句1;
ELSE
  语句2;
END IF;

IF 条件1 THEN
  语句1;
ELSEIF 条件2 THEN
  语句2;
ELSE
  语句3;
END IF;

示例：

mysql> use nsd2021;
mysql> delimiter //

mysql> create procedure deptype_pro(IN no int, OUT dept_type varchar(5))
    -> begin
    ->   declare name varchar(5);
    ->   select dept_name into name from departments
    ->   where dept_id=no;
    ->   if name='运维部' then
    ->     set dept_type='技术部';
    ->   elseif name='开发部' then
    ->     set dept_type='技术部';
    ->   elseif name='测试部' then
    ->     set dept_type='技术部';
    ->   else
    ->     set dept_type='非技术部';
    ->   end if;
    -> end//
Query OK, 0 rows affected (0.00 sec)
    
mysql> call deptype_pro(1, @t)//
Query OK, 1 row affected (0.00 sec)

mysql> select @t//
+--------------+
| @t           |
+--------------+
| 非技术部     |
+--------------+
1 row in set (0.00 sec)

mysql> call deptype_pro(3, @t1)//
Query OK, 1 row affected (0.00 sec)

mysql> select @t1//
+-----------+
| @t1       |
+-----------+
| 技术部    |
+-----------+
1 row in set (0.00 sec)

mysql> delimiter ;

case语句

语法：

CASE 变量|表达式|字段
WHEN 判断的值1 THEN 返回值1;
WHEN 判断的值2 THEN 返回值2;
... ...
ELSE 返回值n;
END CASE;

示例

mysql> delimiter //

mysql> create procedure deptype_pro2(IN no int, OUT dept_type varchar(5))
    -> begin
    ->   declare name varchar(5);
    ->   select dept_name into name from departments
    ->   where dept_id=no;
    ->   case name
    ->   when '运维部' then set dept_type='技术部';
    ->   when '开发部' then set dept_type='技术部';
    ->   when '测试部' then set dept_type='技术部';
    ->   else set dept_type='非技术部';
    ->   end case;
    -> end//

mysql> call deptype_pro2(1, @tt)//
Query OK, 1 row affected (0.00 sec)

mysql> select @tt//
+--------------+
| @tt          |
+--------------+
| 非技术部     |
+--------------+
1 row in set (0.00 sec)

mysql> call deptype_pro2(3, @tt2)//
Query OK, 1 row affected (0.00 sec)

mysql> select @tt2//
+-----------+
| @tt2      |
+-----------+
| 技术部    |
+-----------+
1 row in set (0.00 sec)

mysql> delimiter ;

循环结构

while循环

可能一次不执行
语法：

[标签:]WHILE 循环条件 DO
  循环体;
END WHILE [标签];

示例：

mysql> use nsd2021;
mysql> delimiter //

mysql> create procedure while_pro(IN i int)
    -> begin
    ->   declare j int default 1;
    ->   while j<i do
    ->     insert into departments(dept_name) values('hr');
    ->     set j=j+1;
    ->   end while;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> call while_pro(3)//
Query OK, 1 row affected (0.00 sec)

mysql> delimiter ;

使用LEAVE结束循环。此处LEAVE相当于其他语言的break

mysql> delimiter //

mysql> create procedure while_pro2(IN i int)
    -> begin
    ->   declare j int default 1;
    ->   a:while j<i do
    ->     insert into departments(dept_name) values('hr');
    ->     if j>=2 then
    ->		leave a;
    ->     end if;
    ->     set j=j+1;
    ->   end while a;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> call while_pro2(10)//
Query OK, 1 row affected (0.00 sec)

mysql> delimiter ;

使用ITERATE跳过本次循环。此处的ITERATE相当于其他整语言的continue

mysql> delimiter //

mysql> create procedure while_pro3(IN i int)
    -> begin
    ->   declare j int default 0;
    ->   a:while j<i do
    ->     set j=j+1;
    ->     if mod(j, 2)=0 then
    ->		iterate a;
    ->     end if;
    ->     insert into departments(dept_name) values(concat('hr', j));
    ->   end while a;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> call while_pro3(10)//
Query OK, 1 row affected (0.00 sec)

mysql> delimiter ;

loop循环

没有条件的死循环
语法：

[标签:]LOOP
  循环体;
END LOOP [标签]

示例：

mysql> delimiter //

mysql> create procedure loop_pro()
    -> begin
    ->   declare i int default 0;
    ->   a:loop
    ->     set i=i+1;
    ->     if i>5 then leave a;
    ->     end if;
    ->     insert into departments(dept_name) values(concat('hr1', i));
    ->   end loop a;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> call loop_pro()//
Query OK, 1 row affected (0.00 sec)

mysql> delimiter ;

repeat循环

至少循环一次
语法：

[标签:]REPEAT
  循环体;
UNTIL 循环结束条件
END REPEAT [标签]

示例：

mysql> delimiter //

mysql> create procedure repeat_pro(IN i int)
    -> begin
    ->   declare j int default 1;
    ->   a:repeat
    ->     set j=j+1;
    ->     insert into departments(dept_name) values('sales');
    ->   until j>i;
    ->   end repeat a;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> call repeat_pro(1)//
Query OK, 1 row affected (0.00 sec)

mysql> delimiter ;

附：通过python3的pymysql模块操作数据库

import pymysql

conn = pymysql.connect(
    host='192.168.1.11',
    user='root',
    password='NSD2021@tedu.cn',
    db='mydb',
    charset='utf8mb4'
)

cur = conn.cursor()

insert1 = 'insert into departments(dept_name) values(%s)'

for dep in ('da', 'db', 'dc', 'dd'):
    cur.execute(insert1, (dep,))

conn.commit()

cur.close()
conn.close()