Linux云计算 |【第四阶段】RDBMS1-DAY5

主要内容：

试图概述（创建视图VIEW、修改、查看、删除）、变量（全局变量、会话变量、用户变量、局部变量）、存储过程（创建、调用、删除存储过程）、流程控制结构（分支结构：if/case、循环结构while/loop/repeat）

一、视图概述

视图是由数据库中的一个表或多个表导出的虚拟表，是一种虚拟存在的表，方便用户对数据的操作，其内容由查询定义。 同真实表一样，视图包含一系列带有名称的列和行数据；数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。

使用试图的原因：（安全、数据独立）

① 安全原因，视图可以隐藏一些数据，例如员工信息表，可用视图只显示姓名、工龄、地址，而不显示社会保险号和工资数等。

② 另一个原因是可使复杂的查询易于理解和使用。

1、创建视图

• 语法格式：

CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]

VIEW 视图名[(属性清单)]

AS SELECT语句

[WITH [CASCADED|LOCAL] CHECK OPTION];

解释说明：

- REPLACE：替换现有视图

- ALGORITHM：可选项，表示视图选择的算法。

- 属性清单：可选项，指定视图中各个属性的名词，默认情况下与SELECT语句中的查询的属性相同。

- SELECT语句：表示一个完整的查询语句，将查询记录导入视图中。

- WITH CHECK OPTION：可选项，表示更新视图时要保证在该视图的权限范围之内。

补充：

- CREATE VIEW 视图名 (字段名列表) as SELECT语句

注意：在视图表中不定义字段名的话，默认使用表中的字段名，若定义字段名的话，视图表中的字段名个数必须和基本中的字段个数相等；

例如：创建包含员工名、email和部门名是运维部的视图

mysql> create view emp_view     //创建视图
    -> as
    ->   select name,email,dept_name
    ->   from employees as e
    ->   inner join departments as d
    ->   on e.dept_id = d.dept_id
    ->   where dept_name='运维部';
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-------------------+
| Tables_in_nsd2021 |
+-------------------+
| departments       |
| emp_view          |
| employees         |
| salary            |
| wage_grade        |
+-------------------+
5 rows in set (0.00 sec)
 
mysql> desc emp_view;     //查询视图的虚拟表结构
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| name      | varchar(10) | YES  |     | NULL    |       |
| email     | varchar(25) | YES  |     | NULL    |       |
| dept_name | varchar(10) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
 
mysql> select * from emp_view;     //查询视图中数据
+-----------+--------------------+-----------+
| name      | email              | dept_name |
+-----------+--------------------+-----------+
| 廖娜      | liaona@tarena.com  | 运维部    |
| 窦红梅    | douhongmei@tedu.cn | 运维部    |
| 聂想      | niexiang@tedu.cn   | 运维部    |
| 陈阳      | chenyang@tedu.cn   | 运维部    |
| 戴璐      | dailu@tedu.cn      | 运维部    |
| 陈斌      | chenbin@tarena.com | 运维部    |
+-----------+--------------------+-----------+
6 rows in set (0.00 sec)

例如：创建包含员工名、工资总额的视图

mysql> create view emp_sal_view
    -> as
    ->   select name,date,basic+bonus as total
    ->   from employees as e
    ->   inner join salary as s
    ->   on e.employee_id = s.employee_id;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from emp_sal_view;
+-----------+------------+-------+
| name      | date       | total |
+-----------+------------+-------+
| 梁伟      | 2018-07-10 | 27206 |
| 梁伟      | 2018-08-10 | 24206 |
| 梁伟      | 2018-09-10 | 24206 |
…
| 杨金凤    | 2020-10-10 | 13379 |
| 杨金凤    | 2020-11-10 | 17379 |
| 杨金凤    | 2020-12-10 | 10697 |
+-----------+------------+-------+
8055 rows in set 

2、修改视图

① 方法1（与创建视图完全一样）

• 语法格式：

CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]

VIEW 视图名[(属性清单)]

AS SELECT语句

[WITH [CASCADED|LOCAL] CHECK OPTION];

mysql> create or replace view emp_view
    -> as
    ->   select name,email,dept_name
    ->   from employees as e
    ->   inner join departments as d
    ->   on e.dept_id = d.dept_id;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from emp_view where dept_name='运维部';   //查询视图中数据，筛选条件
+-----------+--------------------+-----------+
| name      | email              | dept_name |
+-----------+--------------------+-----------+
| 廖娜      | liaona@tarena.com  | 运维部    |
| 窦红梅    | douhongmei@tedu.cn | 运维部    |
| 聂想      | niexiang@tedu.cn   | 运维部    |
| 陈阳      | chenyang@tedu.cn   | 运维部    |
| 戴璐      | dailu@tedu.cn      | 运维部    |
| 陈斌      | chenbin@tarena.com | 运维部    |
+-----------+--------------------+-----------+
6 rows in set (0.00 sec)

② 方法2

• 语法格式：ALTER VIEW 视图名 AS 查询语句;

例如：通过alter修改视图查询列表，并找到2018年12月，总工资大于35000的员工信息

mysql> alter view emp_sal_view
    -> as
    ->   select name,date,basic,bonus,basic+bonus as total
    ->   from employees as e
    ->   inner join salary s
    ->   on e.employee_id = s.employee_id;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from emp_sal_view 
    -> where year(date)=2018 and month(date)=12 and total > 35000;
+--------+------------+-------+-------+-------+
| name   | date       | basic | bonus | total |
+--------+------------+-------+-------+-------+
| 和林   | 2018-12-10 | 25524 | 11000 | 36524 |
+--------+------------+-------+-------+-------+
1 row in set (0.01 sec)

3、查看视图

• 语法格式：SHOW TABLES;    //查看所有表
• 语法格式：DESC 视图名;      //查看表结构

4、删除视图

• 语法格式：DROP VIEW 视图1,视图2, ...;

mysql> drop view emp_view,emp_sal_view;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_nsd2021 |
+-------------------+
| departments       |
| employees         |
| salary            |
| wage_grade        |
+-------------------+
4 rows in set (0.00 sec)

二、MySQL变量

mysql变量可分为两大类：

• ① 系统变量：由系统提供，不是由用户定义的。包括全局变量、会话变量
• ② 用户自定义变量：用户定义的变量。包括用户变量、局部变量

- 局部变量：

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

- 用户变量：

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

- 会话变量：

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

- 全局变量：

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

1、系统变量

全局变量global、会话变量session

① 查看所有系统变量

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

② 查看满足条件的部分变量（默认为会话变量）

• 格式：show global|session variables like '%变量名%';

mysql> show global variables like '%version%';   //不指定global的话，默认为会话变量
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.17                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.17                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
8 rows in set (0.00 sec)
 
mysql> show session variables like '%version%';   //同等于show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.17                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.17                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
8 rows in set (0.00 sec)
| Variable_name           | Value                        |
8 rows in set (0.00 sec)

③ 查看某个系统变量

变量结构为@@变量名、@@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)

例如：修改系统变量的会话变量中的isolation事务隔离级别，查看效果；

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@tx_isolation;   //当前会话变量已更改
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)
 
mysql> select @@session.tx_isolation;   //当前会话变量已更改
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+
1 row in set (0.00 sec)
 
mysql> select @@global.tx_isolation;     //全局变量未受影响
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)

# 打开另一个终端，再次查看变量信息（只影响终端的当前会话）

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

2、变量赋值

• 语法格式：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)

# 同等于

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

注意：为全局变量赋值后，会话变量是不会因全局变量的赋值而受到影响；

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

3、用户变量

作用域：仅对当前会话有效，同于会话变量作用域（临时）

1）声明并初始化

• 语法格式：

SET @用户变量=值;

或

SET @用户变量:=值;

或

SELECT @用户变量:=值;

2）赋值用户变量

• 语法格式：

SET @用户变量=值;

或

SET @用户变量:=值;

或

SELECT @用户变量:=值;

或

SELECT 字段 INTO @用户变量 FROM 表;

SELECT 字段 FROM 表 INTO @用户变量;

3）查看用户变量

• 语法格式：SELECT @变量

例如：

mysql> set @user='tom';
Query OK, 0 rows affected (0.00 sec)
mysql> select @user;
+-------+
| @user |
+-------+
| tom   |
+-------+
1 row in set (0.00 sec)
 
mysql> set @user:='tom';
Query OK, 0 rows affected (0.00 sec)
mysql> select @user;
+-------+
| @user |
+-------+
| tom   |
+-------+
1 row in set (0.00 sec)

例如：

mysql> select @user:='jerry';    //同一变量名会覆盖旧变量的值
+----------------+
| @user:='jerry' |
+----------------+
| jerry          |
+----------------+
1 row in set (0.00 sec)
mysql> select @user;
+-------+
| @user |
+-------+
| jerry |
+-------+
1 row in set (0.00 sec)

例如：

mysql> select count(*) into @count from employees;
Query OK, 1 row affected (0.00 sec)
mysql> select @count;
+--------+
| @count |
+--------+
|    133 |
+--------+
1 row in set (0.00 sec)

mysql> select count(*) from employees into @count;
Query OK, 1 row affected (0.00 sec)
mysql> select @count;
+--------+
| @count |
+--------+
|    133 |
+--------+
1 row in set (0.00 sec)

4、局部变量

作用域：仅在定义它的BEGIN/END中有效（在存储过程中有效）

1）声明

DECLARE 变量 类型;

DECLARE 变量 类型 DEFAULT 值;

2）赋值局部变量（与用户变量不同，定义局部变量没有@）

• 语法格式：

SET 局部变量=值;

或

SET 局部变量:=值;

或

SELECT 局部变量:=值;

或

SELECT 字段 INTO 局部变量 FROM 表;

3）使用局部变量

• 语法格式：SELECT 局部变量;

三、存储过程

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

MySQL中的脚本，保存一系列SQL命令的集合，重复的数据处理，可以通过存储过程完成；

优点：

① 提高性能；

② 可减轻网络负担；

③ 可防止对表的直接访问；

④ 避免重复的SQL操作；

1、创建存储过程

• 语法格式：

DELIMITER //

CREATE PROCEDURE 存储过程名(参数列表)

BEGIN

    一组合法的sql语句;

END //

DELIMITER ;

解释说明：

- 存储过程前后最好使用DELIMITER //，声明当前段的分隔符是【//】

- 参数列表包含三部分：

① 参数模式

  - IN：需要调用者传值（与Python函数的参数作用类似）

  - OUT：该参数可以作为输入（与Python函数的return返回值类似）

  - INOUT：既可以作为输入又可以作为输出

② 参数名

③ 参数类型

2、调用存储过程

• 语法格式：CALL 存储过程名(实参列表);

补充：分隔符

MySQL默认以分号【;】作为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错；所以要事先用【DELIMITER //】声明当前段分隔符，让编译器把BEGIN和END之间的内容当做存储过程的代码，即使结尾有【;】不会执行这些代码，只有在END的后面加【//】，才算完整的结束；最后需要通过【DELIMITER ;】把分隔符还原。

例如：声明当前段分隔符

mysql> delimiter //
mysql> select * from departments where dept_id=10//
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|      10 | 行政部    |
+---------+-----------+
1 row in set (0.00 sec)
mysql> delimiter ;    //还原分隔符

存储过程示例：

实验准备，还原mydb库

mysql> drop database if exists mydb;    //删除库
Query OK, 5 rows affected (0.01 sec)
mysql> create database if not exists mydb default charset utf8mb4;   //创建库
Query OK, 1 row affected (0.00 sec)
mysql> use mydb;   //切换数据库
mysql> create table departments like nsd2021.departments;    //复制表结构
Query OK, 0 rows affected (0.00 sec)
mysql> desc departments;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| dept_id   | int(4)      | NO   | PRI | NULL    | auto_increment |
| dept_name | varchar(10) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

① 空参列表

例如：通过存储过程，插入语句

mysql> delimiter //   //声明分隔符为【//】
mysql> create procedure dep_pro()     //创建存储过程（空参列表）
    -> begin
    ->   insert into departments values(1,'人事部'),(2,'财务部');
    -> end  //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;    //还原分隔符
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)

② 使用IN参数

例如：通过存储过程，查询并统计部门id为1的人数

mysql> use nsd2021;
mysql> delimiter //
mysql> create procedure empcount_pro(IN dept_num int)  //创建存储过程，使用IN函数
    -> begin
    ->   select dept_id,count(*) from employees
    ->   where dept_id=dept_num
    ->   group by dept_id;
    -> end  //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

mysql> call empcount_pro(1);    //调用存储过程，并传参数
+---------+----------+
| dept_id | count(*) |
+---------+----------+
|       1 |        8 |
+---------+----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

③ 使用OUT参数

例如：通过存储过程，查询是刘倩的email

mysql> use nsd2021;
mysql> delimiter //
mysql> create procedure empemail_pro(IN emp_name varchar(10),OUT mail varchar(25))
    -> begin
    ->   select email into mail    //创建局部函数，OUT已定义参数及类型，无需再声明mail
    ->   from employees
    ->   where name=emp_name;
    -> end //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

mysql> call empemail_pro('刘倩',@m);   //调用存储过程
Query OK, 1 row affected (0.00 sec)
mysql> select @m;   //传一个“@变量”去接受OUT参数的值
+--------------------+
| @m                 |
+--------------------+
| liuqian@tarena.com |
+--------------------+
1 row in set (0.00 sec)

④ 使用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 ;

注意：由于INOUT参数中，IN是把参数传进去，OUT是返回参数值，所以需要通过定义变量传参数进去，再将返回的参数值作为变量返回来；

mysql> set @n=10;
mysql> call myadd(@n);
Query OK, 0 rows affected (0.00 sec)
mysql> select @n;
+------+
| @n   |
+------+
|  110 |
+------+
1 row in set (0.00 sec)

3、查看存储过程

• 格式：select name,db from mysql.proc [where 筛选条件];
• 格式：show create procedure 存储过程名 \G

补充：存储过程存储在MySQL的内部数据库mysql的proc表(procedure)里，name表示存储名称，db表示在哪个数据库里创建的；

mysql> select name,db from mysql.proc;    //查看所有的存储过程
+-------------------------------------+---------+
| name                                | db      |
+-------------------------------------+---------+
| dep_pro                             | mydb    |
| empcount_pro                        | nsd2021 |
| empemail_pro                        | nsd2021 |
| myadd                               | nsd2021 |
| create_synonym_db                   | sys     |
| diagnostics                         | sys     |
+-------------------------------------+---------+
52 rows in set (0.00 sec)
 
mysql> select name,db from mysql.proc where db='nsd2021';
+--------------+---------+
| name         | db      |
+--------------+---------+
| empcount_pro | nsd2021 |
| empemail_pro | nsd2021 |
| myadd        | nsd2021 |
+--------------+---------+
3 rows in set (0.00 sec)
 
mysql> show create procedure empemail_pro \G
*************************** 1. row ***************************
           Procedure: empemail_pro
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `empemail_pro`(IN emp_name varchar(10),OUT mail varchar(25))
begin
  select email into mail
  from employees
  where name=emp_name;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)

4、删除存储过程

• 语法格式：drop procedure 存储过程名;

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

四、流程控制结构

顺序结构：自上向下执行

分支结构：从多条路径中选择一条路径执行

循环结构：满足某种条件，反复执行一段代码

1、分支结构- if语句

• 语法格式：

IF 条件 TEHN

  语句;

END IF;

• 语法格式：

IF 条件 TEHN

  语句1;

ELSE

  语句2;

END IF;

• 语法格式：

IF 条件1 TEHN

  语句1;

ELSEIF 条件2 TEHN

  语句2;

ELSE

  语句3;

END IF;

IF分支示例：

mysql> delimiter //
mysql> create procedure deptype_pro(IN num int,OUT dept_type varchar(5))
    -> begin
    ->   declare name varchar(5);     //声明局部函数
    ->   select dept_name into name from departments   //创建局部函数
    ->   where dept_id=num;
    ->   if name='运维部' or name='开发部' or name='测试部' then
    ->      set dept_type='技术部';
    ->   else
    ->      set dept_type='非技术部';
    ->   end if;
    -> end  //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

mysql> call deptype_pro(1,@t);   //传一个“@变量”去接受OUT参数的值
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> create procedure deptype_pro(IN num int, OUT dept_type varchar(5))
    -> begin
    ->   declare name varchar(5);
    ->   select dept_name into name from departments
    ->   where dept_id=num;
    ->   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)

2、分支结构-case语句

• 语法格式：

CASE 变量|表达式|字段

WHEN 判断的值1 THEN 返回值1;

WHEN 判断的值2 THEN 返回值2;

... ...

ELSE 返回值n;

END CASE;

CASE分支示例：

mysql> delimiter //
mysql> create procedure deptype_pro2(IN num int,OUT dept_type varchar(5))
    -> begin
    ->   declare name varchar(5);
    ->   select dept_name into name from departments
    ->   where dept_id=num;
    ->   case name
    ->   when '运维部' then set dept_type='技术部';
    ->   when '开发部' then set dept_type='技术部';
    ->   when '测试部' then set dept_type='技术部';
    ->   else set dept_type='非技术部';
    ->   end case;
    -> end //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
 
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,@tt1);
Query OK, 1 row affected (0.00 sec)
mysql> select @tt1;
+-----------+
| @tt1      |
+-----------+
| 技术部    |
+-----------+
1 row in set (0.00 sec)

3、循环结构-while循环

当条件为真，则循环执行语句，否则可能一次不执行；

• 语法格式：

[标签:]WHILE 循环条件 DO

  循环体;

END WHILE [标签];

While循环示例：

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.01 sec)
mysql> delimiter ;

mysql> call while_pro(3);
Query OK, 1 row affected (0.00 sec)
mysql> select * from departments;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
...
|      18 | hr        |
|      19 | hr        |
+---------+-----------+
15 rows in set (0.00 sec)

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

mysql> delimiter //
mysql> create procedure while_pro2(IN i int)
    -> begin
    ->   declare j int default 1;
    ->   a:while j<i do      //设置标签a
    ->     insert into departments(dept_name) values('hr2');
    ->     if j>=2 then    //嵌套if分支做判断
    ->       leave a;     //leave就结束while循环
    ->     end if;
    ->     set j=j+1;
    ->   end while a;
    -> end //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

mysql> call while_pro2(8);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from departments;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
...
|      20 | hr2       |
|      21 | hr2       |
+---------+-----------+
17 rows in set (0.00 sec)

使用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> delimiter ;

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

mysql> select * from departments;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
...
|      22 | hr1       |
|      23 | hr3       |
|      24 | hr5       |
|      25 | hr7       |
|      26 | hr9       |
+---------+-----------+
22 rows in set (0.00 sec)

4、循环结构-loop循环

 没有条件的死循环

• 语法格式：

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

loop循环示例：

mysql> delimiter //
mysql> create procedure loop_pro()
    -> begin
    ->   declare i int default 0;   //声明局部变量，默认值为0
    ->   a:loop
    ->     set i=i+1;
    ->     if i>4 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> delimiter ;

mysql> call loop_pro();
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from departments;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
...
|      27 | hr11      |
|      28 | hr12      |
|      29 | hr13      |
|      30 | hr14      |
+---------+-----------+
26 rows in set (0.00 sec)

5、循环结构-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> delimiter ;

mysql> call repeat_pro(3);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from departments;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
...
|      31 | sales     |
|      32 | sales     |
|      33 | sales     |
+---------+-----------+
29 rows in set (0.00 sec)

附：通过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()

思维导图：

小结：

本篇章节为【第四阶段】RDBMS1-DAY5 的学习笔记，这篇笔记可以初步了解到 视图概述、变量、存储过程、流程控制结构。

Tip：毕竟两个人的智慧大于一个人的智慧，如果你不理解本章节的内容或需要相关笔记、视频，可私信小安，请不要害羞和回避，可以向他人请教，花点时间直到你真正的理解。