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 |
+-----------+--------------------+-----------+
| 廖娜      | [email protected]  | 运维部    |
| 窦红梅    | [email protected] | 运维部    |
| 聂想      | [email protected]   | 运维部    |
| 陈阳      | [email protected]   | 运维部    |
| 戴璐      | [email protected]      | 运维部    |
| 陈斌      | [email protected] | 运维部    |
+-----------+--------------------+-----------+
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 |
+-----------+--------------------+-----------+
| 廖娜      | [email protected]  | 运维部    |
| 窦红梅    | [email protected] | 运维部    |
| 聂想      | [email protected]   | 运维部    |
| 陈阳      | [email protected]   | 运维部    |
| 戴璐      | [email protected]      | 运维部    |
| 陈斌      | [email protected] | 运维部    |
+-----------+--------------------+-----------+
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)