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

2021-08-09 本文已影响0人秋天丢了李姑娘

视图

视图概述

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

使用视图的原因

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

创建视图

语法格式

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

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

视图

视图概述

使用视图的原因

创建视图

语法格式

视图示例

修改视图

语法格式

方式一：

方式二

查看视图

删除视图

变量

变量分类

局部变量

用户变量

会话变量

全局变量

使用系统变量

查看变量

变量赋值

使用用户变量

作用域

使用步骤

使用局部变量

作用域

使用步骤

存储过程

使用存储过程

创建存储过程

语法：

分隔符

调用存储过程

语法

存储过程示例

查看存储过程

删除存储过程

流程控制结构

分支结构

if语句

case语句

循环结构

while循环

loop循环

repeat循环

猜你喜欢

热点阅读