MySQL视图

一：视图概述

1：为什么使用视图

　　视图可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。比如，针对一个公司的销售人员，我们只想给他看部分数据，而某些特殊的数据，比如采购的价格，则不会提供给他。再比如，人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，其他人的查询视图中则不提供这个字段。

2：什么是视图

①：视图是一种虚拟表，本身是不具有数据的，占用很少的内存空间，它是SQL中的一个重要概念。
②：视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
③：视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化。
④：向视图提供数据内容的语句为SELECT语句, 可以将视图理解为存储起来的SELECT语句在数据库中，视图不会保存数据，数据真正保存在数据表
　　中。当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化。
⑤：视图是向用户提供基表数据的另一种表现形式。通常情况下，小型项目的数据库可以不使用视图，但是在大型项目中，以及数据表比较复杂的情况
　　下，视图的价值就凸显出来了，它可以帮助我们把经常查询的结果集放到虚拟表中，提升使用效率。理解和使用起来都非常方便。

二：视图操作

1：基本语法

CREATE [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW 视图名称 [(字段列表)]
    AS 查询语句
    [WITH [CASCADED|LOCAL] CHECK OPTION]

-- 创建数据库及使用表
CREATE DATABASE IF NOT EXISTS demo_view_school CHARACTER SET utf8 COLLATE utf8_general_ci;
USE demo_view_school;
-- 创建数据表
CREATE TABLE `student`(
    `sid`      int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学生ID',
    `sname`    varchar(5) NOT NULL COMMENT '学生姓名',
    `ssex`     enum('男','女') DEFAULT '男' COMMENT '性别',
    `sage`     tinyint(11) unsigned NOT NULL COMMENT '年龄',
    `saddress` mediumtext COMMENT '住址',
    `tid`      int(11) DEFAULT NULL COMMENT '引用老师ID'
) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8;
CREATE TABLE `teacher`(
    `tid`      int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '老师ID',
    `tname`    varchar(5) NOT NULL COMMENT '老师姓名',
    `tsex`     enum('男','女') DEFAULT '男' COMMENT '性别',
    `tage`     tinyint(3) unsigned DEFAULT NULL COMMENT '年龄',
    `taddress` varchar(10) DEFAULT NULL COMMENT '住址'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 填充数据
INSERT INTO `demo_view_school`.`student` (`sid`, `sname`, `ssex`, `sage`, `saddress`, `tid`) VALUES
(1, '王生安', '男', 21, '安徽合肥', 1),(2, '李鑫灏', '女', 21, '安徽合肥', 1),(3, '薛佛世', '男', 21, '安徽蚌埠', 2),
(4, '蔡壮保', '男', 21, '安徽安庆', 2),(5, '钱勤堃', '女', 23, '安徽合肥', 1),(6, '潘恩依', '女', 24, '安徽合肥', 1),
(7, '陈国柏', '女', 24, '安徽六安', 2),(8, '魏皑虎', '女', 24, '安徽六安', 2);
INSERT INTO `demo_view_school`.`teacher` (`tid`, `tname`, `tsex`, `tage`, `taddress`) VALUES
(1, '张老师', '女', 28,'江苏南京'),(2, '李老师', '男', 28,'江苏无锡');

2：视图的增删改查

#### 创建视图
-- 创建单表视图
CREATE VIEW t_view1(sid,sname,ssex,saddress) 
AS SELECT sid,sname,ssex,saddress FROM student;
-- 创建多表视图
CREATE VIEW t_view2(sid,sname,ssex,saddress,tname) 
AS SELECT s.sid,s.sname,s.ssex,s.saddress,t.tname FROM student s INNER JOIN teacher t USING(tid);
-- 注：创建完视图后，基表上的主键外键唯一键等等都会丢失
-- 注：创建完视图会在MySQL的data数据存储目录里存在t_view1.frm，t_view2.frm的文件

#### 查看视图
-- 查看数据库的表对象、视图对象
SHOW TABLES;
    -- 示例：
        +----------------------------+
        | Tables_in_demo_view_school |
        +----------------------------+
        | student                    |
        | t_view1                    |
        | t_view2                    |
        | teacher                    |
        +----------------------------+
-- 查询表结构
DESC t_view2;
    -- 示例：
        +----------+-------------------+------+-----+---------+-------+
        | Field    | Type              | Null | Key | Default | Extra |
        +----------+-------------------+------+-----+---------+-------+
        | sid      | int(11)           | NO   |     | 0       |       |
        | sname    | varchar(5)        | NO   |     | NULL    |       |
        | ssex     | enum('男','女')   | YES  |     | 男      |       |
        | saddress | mediumtext        | YES  |     | NULL    |       |
        | tname    | varchar(5)        | NO   |     | NULL    |       |
        +----------+-------------------+------+-----+---------+-------+
-- 查询视图状态（因为视图是基于基表创建的，所以视图就像一个被封装的SELECT）
SHOW TABLE STATUS LIKE 't_view2'\G
    -- 示例：（因为是视图所以就Comment为VIEW）
        *************************** 1. row ***************************
                   Name: t_view2
                 Engine: NULL
                Version: NULL
             Row_format: NULL
                   Rows: NULL
         Avg_row_length: NULL
            Data_length: NULL
        Max_data_length: NULL
           Index_length: NULL
              Data_free: NULL
         Auto_increment: NULL
            Create_time: NULL
            Update_time: NULL
             Check_time: NULL
              Collation: NULL
               Checksum: NULL
         Create_options: NULL
                Comment: VIEW

#### 使用视图
-- 视图主要就是为了查询，所以将视图当作表一样查询即可
-- 普通查询
SELECT * FROM t_view2;
    -- 示例：
        +-----+-----------+------+--------------+-----------+
        | sid | sname     | ssex | saddress     | tname     |
        +-----+-----------+------+--------------+-----------+
        |   1 | 王生安     | 男   | 安徽合肥      | 张老师      |
        |   2 | 李鑫灏     | 女   | 安徽合肥      | 张老师      |
        |   3 | 薛佛世     | 男   | 安徽蚌埠      | 李老师      |
        |   4 | 蔡壮保     | 男   | 安徽安庆      | 李老师      |
        |   5 | 钱勤堃     | 女   | 安徽合肥      | 张老师      |
        |   6 | 潘恩依     | 女   | 安徽合肥      | 张老师      |
        |   7 | 陈国柏     | 女   | 安徽六安      | 李老师      |
        |   8 | 魏皑虎     | 女   | 安徽六安      | 李老师      |
        +-----+-----------+------+--------------+-----------+

#### 修改视图
-- 视图本身不可修改，但是视图的来源是可以修改的
-- 修改视图其实就是修改视图本身的来源（SELECT语句）
-- 比如修改t_view1视图
ALTER VIEW t_view1(sid,sname) AS SELECT sid,sname FROM student;
    -- 示例：    
        select * from t_view1;
        +-----+-----------+
        | sid | sname     |
        +-----+-----------+
        |   1 | 王生安     |
        |   2 | 李鑫灏     |
-- 使用 OR REPLACE 修改视图（和ALTER VIEW一样）
CREATE OR REPLACE VIEW t_view3(sid,sname) AS SELECT sid,sname FROM student;

#### 删除视图
-- 单个删除、批量删除
DROP VIEW 视图名称；
DROP VIEW 视图名称,视图名称；
注：为什么删除视图不用DROP TABLE；因为TABLE表示存数据的，而VIEW则是没有具体数据的，
所以删视图它只是删结构(.frm的文件)，删TABLE则是删结构和数据(.frm和.ibd)

三：视图数据操作

视图数据新增：
    ①：多表视图不能新增数据
    ②：可以向单表视图插入数据，但是视图中包含的字段必须和基表中所有不能为空(或没有默认值)的字段对应

视图数据删除：
    ①：多表视图不能删除数据
    ②：单表视图可以删除数据

视图数据更新：
    理论上无论是单表视图还是多表视图都可以更新数据
    更新限制：WITH CHECK OPTION，如果对视图在新增的时候，限定了某个字段有限制，那么
    对视图进行数据更新操作时，系统会进行验证，要保证更新之后，数据依然可以按实体查询出来，否则不让更新。
    -- 示例
    -- 创建视图
    -- 单表视图（加上sage约束,表示视图来源数据都是21岁）
    -- WITH CHECK OPTION修改数据时，我们不能修改sage字段导致不符合条件
    CREATE VIEW t_view3(sid,sname,sage,ssex,saddress) 
    AS SELECT sid,sname,sage,ssex,saddress FROM student WHERE sage = 21 WITH CHECK OPTION;
    -- 更新示例
    UPDATE t_view3 SET sage = 66 WHERE sid = 1;
    ERROR 1369 (HY000): CHECK OPTION failed 'demo_view_school.t_view3'

关于视图更新总结一下：
    要使视图可更新，视图中的行和底层基本表中的行之间必须存在一对一的关系。
    另外当视图定义出现如下情况时，视图不支持更新操作：
        ①：在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，视图将不支持INSERT和DELETE操作；
        ②：视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作；
        ③：在定义视图的SELECT语句中使用了JOIN联合查询，视图将不支持INSERT和DELETE操作；
        ④：在定义视图的SELECT语句后的字段列表中使用了数学表达式或子查询，视图将不支持INSERT，
            也不支持UPDATE使用了数学表达式、子查询的字段值；
        ⑤：在定义视图的SELECT语句后的字段列表中使用DISTINCT、聚合函数、GROUP BY 、HAVING、UNION等，
            视图将不支持INSERT、UPDATE、DELETE；
        ⑥：在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持INSERT、UPDATE、DELETE；
        ⑦：视图定义基于一个不可更新视图；
        ⑧：常量视图。

四：视图算法

执行基础数据：
ALTER TABLE student ADD ssalary DECIMAL(7,2);
UPDATE student set ssalary = ceil(rand()*3000+8000);
基表数据信息：
+-----+-----------+------+------+--------------+------+----------+
| sid | sname     | ssex | sage | saddress     | tid  | ssalary  |
+-----+-----------+------+------+--------------+------+----------+
|   1 | 王生安     | 男   |   21 | 安徽合肥       |    1 |  9225.00 |
|   2 | 李鑫灏     | 女   |   21 | 安徽合肥       |    1 |  8542.00 |
|   3 | 薛佛世     | 男   |   21 | 安徽蚌埠       |    2 | 10035.00 |
|   4 | 蔡壮保     | 男   |   21 | 安徽安庆       |    2 | 10548.00 |
|   5 | 钱勤堃     | 女   |   23 | 安徽合肥       |    1 |  8633.00 |
|   6 | 潘恩依     | 女   |   24 | 安徽合肥       |    1 |  9522.00 |
|   7 | 陈国柏     | 女   |   24 | 安徽六安       |    2 | 10709.00 |
|   8 | 魏皑虎     | 女   |   24 | 安徽六安       |    2 | 10977.00 |
+-----+-----------+------+------+--------------+------+----------+
    ①：创建视图，使工资降序排列DESC
        CREATE VIEW t_view5 AS SELECT * FROM student ORDER BY ssalary DESC;
    ②：查询学生表并按照年龄分组
        SELECT * FROM t_view5 GROUP BY sage;
        -- 查询结果
            +-----+-----------+------+------+--------------+------+---------+
            | sid | sname     | ssex | sage | saddress     | tid  | ssalary |
            +-----+-----------+------+------+--------------+------+---------+
            |   1 | 王生安     | 男   |   21 | 安徽合肥       |    1 | 9225.00 |
            |   5 | 钱勤堃     | 女   |   23 | 安徽合肥       |    1 | 8633.00 |
            |   6 | 潘恩依     | 女   |   24 | 安徽合肥       |    1 | 9522.00 |
            +-----+-----------+------+------+--------------+------+---------+
        -- 说明：这个就是没有按照顺序执行，应该先DESC排序再分组才是正确的
通过上面的查询会发现查询的是错误的，因为DESC排列后已经是从高到底排列了，这时候按照年龄进行
分组，分组完应该每个年龄中都是最高的工资

视图算法：系统对视图以及外部查询视图的select语句的一种解析方式。
    视图：可以理解为子查询，2个select，第一个select：得到视图
视图算法分成3种：
    Undefined：未定义(默认)这不是一种算法，是一种推卸责任的算法告诉系统，视图没有定义算法，系统你自己看着办
    Temptable：临时表算法：先执行视图的select语句，然后在执行外部查询Select语句。
    Merge：合并算法，系统在执行select语句之前，会对视图的select和外部查询视图的select语句进行合并，然后执行
        -- Merge效率高，但是慢
算法指定：
    ①：创建视图（使用算法），使工资降序排列DESC
        CREATE ALGORITHM = TEMPTABLE VIEW t_view5 AS SELECT * FROM student ORDER BY ssalary DESC;
    ②：查询学生表并按照年龄分组
        SELECT * FROM t_view5 GROUP BY sage;
        +-----+-----------+------+------+--------------+------+----------+
        | sid | sname     | ssex | sage | saddress     | tid  | ssalary  |
        +-----+-----------+------+------+--------------+------+----------+
        |   4 | 蔡壮保     | 男   |   21 | 安徽安庆       |    2 | 10548.00 |
        |   5 | 钱勤堃     | 女   |   23 | 安徽合肥       |    1 |  8633.00 |
        |   8 | 魏皑虎     | 女   |   24 | 安徽六安       |    2 | 10977.00 |
        +-----+-----------+------+------+--------------+------+----------+
算法选择：
    如果视图的select语句中包含一个查询子句（order by,limit）,而且很可能该关键字执行顺序比外部查询语句关键字靠后
    一定要使用使用temptable算法，其他情况下不用指定，默认即可。

.