MySQL的视图

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`

来自为知笔记(Wiz)