第十一节：视图和临时表

视图

视图的简介

  1. 视图是一个虚拟表，其内容由查询语句定义
  2. 视图同真实的表一样，包含一系列带有名称的列和行数据。但是，视图在数据库中并不是以存储数据的数据集的形式存在。行和列数据来自于视图定义时查询语句所引用的表，并且在引用视图时动态生成
  3. 视图是在基本表之上建立的虚拟表，它的结构和内容都来自基本表，它依据基本表存在而存在。一个视图可以对应或者多个基本表
  4. mysql中视图和表的区别

mysql中视图和表的区别

  1. 本质:视图是数据的窗口，而表是内容。视图是已经编译好的sql语句，是基于sql语句的结果集的可视化的表，而表是数据库中存储数据的数据集
  2. 实与虚:表属于全局模式中的表，是实表；视图不是数据库中真实存在的表，而是有SQL查询语句查询出的可视化表
  3. 物理记录:视图没有实际的物理记录，不是以数据集的形式存储在数据库中的
  4. 物理空间:表占用物理空间，而视图不占用。视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能用创建的语句来修改
  5. 影响:视图的建立（create）和删除（drop）只影响视图本身，不影响对应的基本表
  6. 安全因素:视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些sql语句的集合。从安全的角度来说，使用视图的用户不接触数据表，不知道表结构

视图的优点

  1. 定制用户数据，聚焦特定的数据，为不同的用户提供基础表中的不同数据
  2. 简化数据操作：在使用查询时，若果语句比较长，而且经常使用。可以创建视图来简化操作
  3. 提高基表数据的安全性：视图是虚拟的，物理上面是不存在的，对视图结构的修改不影响表的结构
  4. 共享所需数据：通过使用视图，每个用户不必都定义和存储自己所需的数据，可以共享数据库中的数据，同样的数据只需要存储一次
  5. 更改数据格式：通过使用视图，可以重新格式化检索出的数据，并组织输出到其他应用程序中

使用视图的注意点

  1. 创建视图需要足够的访问权限
  2. 创建视图的数目没有限制
  3. 视图可以嵌套，即查询语句的数据可以来自其他视图
  4. 视图不能索引，也不能有关联的触发器、默认值或规则
  5. 视图可以和表一起使用
  6. ORDER BY 子句可以用在视图中，但若该视图检索数据的 SELECT 语句中也含有 ORDER BY 子句，则该视图中的 ORDER BY 子句将被覆盖
  7. 视图定义中不能引用 TEMPORARY 表（临时表）

创建视图

  1. CHECK TABLE 视图名称 可以用来查看视图对应的基本表是否存在
  2. 语法格式：CREATE VIEW 视图名 AS SELECT语句
  3. 语法说明：
    1. 视图名：指定视图的名称。该名称在数据库中必须是唯一的
    2. SELECT语句：指定创建视图的SELECT语句，可用于查询多个基础表或视图
    3. SELECT语句存在以下限制：
      1. 用户要拥有 CREATE VIEW 权限和select语句中涉及的基础表和视图的相关权限
      2. SELECT 语句不能引用系统或用户变量
      3. SELECT 语句不能引用预处理语句参数
  4. 创建基于单表的视图： 
    create view view_Student as select * from Student; #创建的视图和基本表中的数据和字段是一样的
create view view_Student1(Vid,vName) as select id,name from Student; #可以自定义视图中字段的名称和个数
# view_Student 和 view_Student1 两个视图中的字段不同，但对应字段的数据却相同。因此，在使用视图时，可能用户不需要了解基本表的结构，更接触不到实际表中的数据，从而保证了数据库的安全。
  5. 创建基于多表的视图： 
    create view view_Student2(id,name,age,teacher_name) as select s.id,s.name,s.age,t.name from Student as s,teacher as t where s.id = t.id;

修改视图

修改视图名称

修改视图的名称可以先将视图删除，然后按照相同的定义语句进行视图的创建，并命名为新的视图名称

修改视图的结构

  1. 语法格式：alter view 视图名 as select语句
  2. 语法说明如下：
    1. 视图名：指定修改视图的名称，在数据库中该名称必须唯一
    2. select语句：指定创建视图的select语句，可以用于查询多个基础表或者视图
    3. select语句存在以下限制：
      1. 用户要拥有 create view 权限和drop 权限以及select语句中涉及的基础表和视图的相关权限
      2. select 语句不能引用系统或用户变量
      3. select 语句不能引用预处理语句参数
  3. 注意：修改视图除了使用alter view，还可以先将视图删除，在使用create view 语句来重新创建视图
  4. 修改视图结构的实例： 
    alter view view_student(s_id,s_name) as select id,name from student; #修改视图只是修改视图中的字段（字段名称和字段数目）
drop view  view_student;
create view view_studnet1 as select * from student; # 要修改视图名称需要先将视图删除，然后按照相同的定义语句进行新视图的创建

更新视图中的数据

  1. 可以使用insert、update和delete来更新视图中的数据，因为视图是一张虚表，本身并不存储数据，实际的数据来自于基础标，所以对视图中数据的更新，实际是更新视图所引用基本表中的数据
  2. 注意：对视图的修改就是对基本表的修改，因此在修改时，要满足基本表的数据定义
  3. 如果视图中包含下面的结构则视图中的数据不可更新：
    1. 聚合函数 sum()、min()、max()、count() 等
    2. distinct 关键字
    3. group by 子句
    4. having 子句
    5. union 或 union all 运算符
    6. 位于选择列表中的子查询
    7. from 子句中的不可更新视图或包含多个表
    8. where 子句中的子查询，引用 from 子句中的表
  4. 实例： 
    delete from view_studnet1 where id = 1; # 将视图中的数据删除
select * from student;  # 视图所依赖的基本表中的数据也被删除，依赖该基本表中的所有的视图的该行数据也被删除

create view view_student3 as select max(age) from student;   # 视图中包含max函数
delete from view_student3 where name = 26;  #在对view_student3进行更新时候回报错

删除视图

  1. 语法格式： DROP VIEW 视图名1,…, 视图名n
  2. 语法解释如下：
    1. 视图名：指定要删除的视图名称
    2. DROP VIEW 语句可以一次删除多个视图
  3. 删除视图实例：drop view if exists view_Studnet1;

临时表

  1. 临时表：临时存在的表，只在当前连接中可见。当前数据库连接关闭，mysql会自动删除临时表并且释放所有的空间
  2. 临时表的作用：只是用来临时保存一些数据
  3. 注意：通常情况临时表默认指外部临时表
  4. 临时表(外部)的特点：
    1. 临时表会在当前会话结束后被系统删除。也可以使用drop（temporary） table_name 来显式删除临时表
    2. 一个会话内，临时表可以和普通表名称相同，同名后普通表则不可见(所有的操作都是在临时表上面进行的），直到临时表被删除
    3. show tables命令不显示临时表，可通过information_schema.temporary_tables系统表可以查看外部临时表的相关信息
    4. 因为临时表只对当前会话可见，所以不同的会话可以创建同名的临时表
  5. MySQL临时表类型：
    1. 外部临时表：通过CREATE TEMPORARY TABLE 创建的临时表，这种临时表称为外部临时表
    2. 内部临时表，内部临时表是一种特殊轻量级的临时表，用来进行性能优化。比如执行group by, order by, distinct, union等等，执行计划中如果包含Using temporary，这种临时表会被MySQL自动创建并用来存储这些操作的中间结果。。这些操作可能包括在优化阶段或者执行阶段。这种内部表对用户来说是不可见的，但是通过EXPLAIN或者SHOW STATUS可以查看MYSQL是否使用了内部临时表用来帮助完成某个操作。
  6. 临时表的应用场景：
    1. 一些操作的结果集只需要在当前连接保存使用，不需要永久存在时。比如京东的购物车表，将商品放入购物车（insert），变更数量（update），删除商品(delete)，结算金钱后，这些数据就要清掉，这时需要用临时表
    2. 在导出数据时，你可能不想导完整的数据库，或者表，你可能只想要导出符合某些条件的数据，那么你可以创建临时表，把select语句插入到临时表，接着导出这个临时表，导完以后通过结束session或者事务的方式，让这些没用的数据自动清理掉
    3. 如果需要连接多张表才能得到结果，同时做连接消耗太大，可以先将A,B,C连接的结果，放在临时表，接着再把这张临时表，跟D,E,F连接，最后得的数据插入普通表
  7. 实例： 
    create temporary table tmp_customer(
id int(11) primary key,
name varchar(25) not null,
age int(11) not null
);
insert into tmp_customer values(1,'Tom',25),(2,'Jack',26),(3,'Weiking',27);
select * from tmp_customer;
show tables;  # Show tables 命令无法看到临时表
drop （temporary）table tmp_customer;  # 可以使用drop table 语句来删除临时表,但是最好加上temporary，防止删除普通的表（比如当前会话突然断了有自动连接上了，你不知道，使用drop table就会导致普通的表被删除）
select * from tmp_customer;  # 断开当前连接，重新连接mysql，执行该查询，会报表不存在错误。因为断开连接时临时表就已经被删除了
posted @ 2020-01-02 15:07  WeiKing  阅读(1693)  评论(0)  编辑  收藏  举报