视图、变量、存储过程、流程控制结构

视图

视图概述

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

使用视图的原因

安全原因，视图可以隐藏一些数据，例如，员工信息表，可以用视图只显示姓名、工龄、地址，而不显示社会保险号和工资数等
另一个原因是可使复杂的查询易于理解和使用。

创建视图

语法格式

CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
VIEW 视图名[(属性清单)]
AS SELECT语句
[WITH [CASCADED|LOCAL] CHECK OPTION];

REPLACE：替换现有视图
ALGORITHM：可选项，表示视图选择的算法。
属性清单：可选项，指定视图中各个属性的名词，默认情况下与SELECT语句中的查询的属性相同。
SELECT语句：表示一个完整的查询语句，将查询记录导入视图中。
WITH CHECK OPTION：可选项，表示更新视图时要保证在该视图的权限范围之内。

视图示例

创建包含员工名、email和部门名的视图

mysql> use nsd2021;
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;
Query OK, 0 rows affected (0.01 sec)

# 查询视图中数据
mysql> select * from emp_view;
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)

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

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 where year(date)=2020 and month(date)=12;

修改视图

语法格式

方式一：

与创建视图完全一样

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, d.dept_id, dept_name
    ->   from employees as e
    ->   inner join departments as d
    ->   on e.dept_id=d.dept_id;
    
mysql> select * from emp_view;

方式二

ALTER VIEW 视图名 AS 查询语句

示例：

mysql> alter view emp_sal_view
    -> as
    ->   select name, date, basic, bonus, 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.01 sec)

mysql> select * from emp_sal_view where year(date)=2020 and month(date)=12;

查看视图

语法：

SHOW TABLES

DESC 视图

删除视图

语法

DROP VIEW 视图1, 视图2, ...

示例：

mysql> drop view emp_view, emp_sal_view;
Query OK, 0 rows affected (0.00 sec)

变量

变量分类

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

最后编辑于：2021.08.09 22:07:40

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 216,496评论 6赞 501
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 92,407评论 3赞 392
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 162,632评论 0赞 353
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 58,180评论 1赞 292
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 67,198评论 6赞 388
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,165评论 1赞 299
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,052评论 3赞 418
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,910评论 0赞 274
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,324评论 1赞 310
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,542评论 2赞 332
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,711评论 1赞 348
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,424评论 5赞 343
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 41,017评论 3赞 326
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,668评论 0赞 22
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,823评论 1赞 269
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,722评论 2赞 368
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,611评论 2赞 353

视图、变量、存储过程、流程控制结构

视图

视图概述

使用视图的原因

创建视图

语法格式

视图示例

修改视图

语法格式

方式一：

方式二

查看视图

删除视图

变量

变量分类

局部变量

用户变量

会话变量

全局变量

使用系统变量

查看变量

变量赋值

使用用户变量

作用域

使用步骤

使用局部变量

作用域

使用步骤

存储过程

使用存储过程

创建存储过程

语法：

分隔符

调用存储过程

语法

存储过程示例

查看存储过程

删除存储过程

流程控制结构

分支结构

if语句

case语句

循环结构

while循环

loop循环

repeat循环

推荐阅读更多精彩内容