MySQL的视图

一、什么是数据视图

视图(View)是从一个表或多个表中导出的一种虚拟存在的表,对于使用视图的用户来跟使用正常的数据表一样操作。视图并不在数据库中实际存在,行和列数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的

使用视图的优势:

  • 简化查询:使用视图的用户不需要关心视图对应的表的结构、关联条件和筛选条件,只管使用已经过滤好的复合条件的结果集就好。
  • 安全作用:数据库中的权限设计,并不能限制访问某一行、某一列,通过使用视图,我们能做到让用户仅能访问他们被允许查询的记录数据
  • 逻辑独立性:视图的来源表添加新列对视图没有影响;假如必须修改来源表的列名,我们可以修改视图来解决。这样我们的应用程序代码可以不做处理,正常运行

二、视图的使用场景

经常查询的一部分数据,抽象了有时非常复杂的查询细节

  1. 运营人员需要经常关注最新的推广数据,而这部分数据的获取需要关联很多张表
  2. 计算列的需要,数据库设计范式要求我们减少冗余字段,因此现在很多数据表都没有计算列字段,如采购单:有价格、数量、税率、含税金额,多半没有不含税金额、税额,而这些字段在很多报表中有都会用到,所以我们可以创建一个含有计算列字段的视图来解决这个问题
  3. 公司的人事系统给外包公司新增功能,但是不能暴露敏感数据,比如薪资、人事家庭地址

三、具体实例

数据表设计

SELECT trp.prodtypeid,tap.prodname,tab.`brdname`,tam.`mdlname`,taft.`typename`,taf.`faultsubject`,tar.`repair`,trp.`costlabour`,trp.`costmaterial` FROM `tbl_repair_prices` trp 
LEFT JOIN `tbl_attr_product` tap ON tap.prodtypeid=trp.prodtypeid 
LEFT JOIN `tbl_attr_brand` tab ON tab.brdid=trp.brdid 
LEFT JOIN `tbl_attr_model` tam ON tam.mdlid=trp.mdlid
LEFT JOIN `tbl_attr_faults_type` taft ON taft.`typeid`=trp.`faulttypeid`
LEFT JOIN `tbl_attr_faults` taf ON taf.`faultid`=trp.`faultid`
LEFT JOIN `tbl_attr_repair` tar ON tar.`rid`=trp.`rid`

职工信息表

CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50),
age TINYINT,
sex TINYINT,
salary DECIMAL(7,2),
department VARCHAR(50)
)

视图操作

1、创建视图的语法

CREATE  [OR REPLACE][ALGORITHM = {MERGE  | TEMPTABLE | UNDEFINED}]
VIEW [database_name].[view_name] [(column_list)]
AS
[SELECT  statement]
  • CREATE 创建关键字
  • OR REPLACE 替换已有视图
  • ALGORITHM 表示视图算法,会影响查询语句的解析方式,UNDEFINED 由自动选择算法 其他不学习
  • database_name.view_name 视图名
  • column_list 用于指定视图中的各个列的名称
  • AS 视图要执行的操作
  • SELECT statement 一个完整的查询语句

创建和执行视图

报价表视图

CREATE VIEW tbl_repair_prices_view AS SELECT trp.prodtypeid,tap.prodname,tab.`brdname`,tam.`mdlname`,taft.`typename`,taf.`faultsubject`,tar.`repair`,trp.`costlabour`,trp.`costmaterial` FROM `tbl_repair_prices` trp 
LEFT JOIN `tbl_attr_product` tap ON tap.prodtypeid=trp.prodtypeid 
LEFT JOIN `tbl_attr_brand` tab ON tab.brdid=trp.brdid 
LEFT JOIN `tbl_attr_model` tam ON tam.mdlid=trp.mdlid
LEFT JOIN `tbl_attr_faults_type` taft ON taft.`typeid`=trp.`faulttypeid`
LEFT JOIN `tbl_attr_faults` taf ON taf.`faultid`=trp.`faultid`
LEFT JOIN `tbl_attr_repair` tar ON tar.`rid`=trp.`rid`

DESC `tbl_repair_prices_view`

使用可选参数 column_list传入列名

CREATE VIEW tbl_repair_prices_view (`产品名`,`品牌名`,`机型名`,`故障类别`,`故障信息`,`维修方案`,`a`,`b`) AS SELECT tap.prodname,tab.`brdname`,tam.`mdlname`,taft.`typename`,taf.`faultsubject`,tar.`repair`,trp.`costlabour`,trp.`costmaterial` FROM `tbl_repair_prices` trp 
LEFT JOIN `tbl_attr_product` tap ON tap.prodtypeid=trp.prodtypeid 
LEFT JOIN `tbl_attr_brand` tab ON tab.brdid=trp.brdid 
LEFT JOIN `tbl_attr_model` tam ON tam.mdlid=trp.mdlid
LEFT JOIN `tbl_attr_faults_type` taft ON taft.`typeid`=trp.`faulttypeid`
LEFT JOIN `tbl_attr_faults` taf ON taf.`faultid`=trp.`faultid`
LEFT JOIN `tbl_attr_repair` tar ON tar.`rid`=trp.`rid`

修改视图

ALTER VIEW tbl_repair_prices_view (`产品名`,`品牌名`,`机型名`,`故障类别`,`故障信息`,`repair`,`a`,`b`) AS SELECT tap.prodname,tab.`brdname`,tam.`mdlname`,taft.`typename`,taf.`faultsubject`,tar.`repair`,trp.`costlabour`,trp.`costmaterial` FROM `tbl_repair_prices` trp 
LEFT JOIN `tbl_attr_product` tap ON tap.prodtypeid=trp.prodtypeid 
LEFT JOIN `tbl_attr_brand` tab ON tab.brdid=trp.brdid 
LEFT JOIN `tbl_attr_model` tam ON tam.mdlid=trp.mdlid
LEFT JOIN `tbl_attr_faults_type` taft ON taft.`typeid`=trp.`faulttypeid`
LEFT JOIN `tbl_attr_faults` taf ON taf.`faultid`=trp.`faultid`
LEFT JOIN `tbl_attr_repair` tar ON tar.`rid`=trp.`rid`

删除视图

drop view `tbl_repair_prices_view`