12-MySQL--视图（创建视图+查看视图+使用视图+修改视图+删除视图）+视图的意义+视图数据操作（新增数据+删除数据+更新数据）+视图算法

一、视图

1、视图：view。视图是一种有结构（有行有列的二维表结构）但没结果（结构中不真实存放数据）的虚拟表。虚拟表的结构来源不是自己定义的，而是从对应的基表中产生（视图的数据来源）

注：虚拟表的数据也不是从自己来的

二、创建视图

1、基本语法

    -- 创建视图
    -- select语句：可以是 普通查询|连接查询|联合查询|子查询
    create view 视图名字 as select 语句;

（1）单表视图：基表只有一个

（2）多表视图：基表来源至少是两个

注：多表视图（基表有多张）的字段名不能重复。可以进行别名命名

    -- 创建视图：单表视图
    create view my_v1 as
    select * from my_student;

    -- 创建视图：多表视图
    create view my_v2 as
    select * from my_student as s left join my_class c on c.id = s.c_id;
    -- 报错：Duplicate column name 'id'，即 id重复（表中不允许出现同名字段）

    -- 多表视图（基表有多张）的字段名不能重复（可以进行别名命名）
    
    create view my_v2 as
    select s.*, c.name as c_name, c.room from my_student as s
    left join my_class c on c.id = s.c_id;

2、视图一旦创建，系统会在视图对应的数据库文件夹下创建一个对应的结构文件xxx.frm（视图不影响基表的存在，视图只是基于基表出现的东西）

注：视图名和表名不能重复。因为产生的都是xxx.frm结构文件

三、查看视图

1、视图是一张虚拟表，表的所有查看方式都适用于视图

    -- 查看所有视图/表
    show tables;
    show tables like '视图名的模糊查询';
    show views;    -- 报错
    
    -- 查看视图结构
    desc 视图名;
    
    -- 查看视图创建语句
    show create table 视图名 [\G];
    show create view 视图名 [\G];

四、使用视图

1、使用视图主要是为了查询数据，将视图当做表一样查询即可（视图不保存结果，但可以从基表拿到数据）

    -- 使用视图，查询数据
    -- 视图不保存结果，但可以从基表拿到数据
    select * from my_v1;

2、视图的执行，其本质就是执行封装的select语句

五、修改视图

1、视图本身不可修改，但视图的来源是可以修改的。修改视图，就是修改视图本身的来源语句（select语句）

2、基本语法

    -- 修改视图
    alter view 视图名 as 新的select语句;

    -- 创建视图
    create view my_v3 as
    select * from my_student;

    -- 查看视图结构
    desc my_v3;

    -- 修改视图
    alter view my_v3 as
    select * from my_class;

    -- 查看视图结构
    desc my_v3; 

六、删除视图

1、基本语法

    -- 删除视图
    drop view 视图名字;

    -- 删除视图
    drop view my_v3;
    drop table my_v3;    -- 报错
    
    -- 表可以存数据，视图不存数据
    -- 所以，删除视图是只删除结构，而删除表table会找到table删除表中的数据
    -- 但实际上没有my_v3这张表来存储数据

2、视图可以随意删除，因为视图没有数据。但建议不要这样做，因为创建视图的sql语句可能没有保存

七、视图的意义

1、视图可以节省sql语句。将一条复杂的查询语句使用视图进行保存，以后可以直接对视图进行操作

2、数据安全。视图操作主要是针对查询的，如果对视图结构进行处理（删除），不会影响基表数据，相对安全

3、视图往往是在大项目中使用，而且是多系统使用。可以对外提供有用的数据，但隐藏了关键（无用）的数据，数据安全

4、视图可以对外提供友好型。不同的视图提供不同的数据，对外好像专门设计

5、视图可以更好（容易）的进行权限控制

注：基表的数据往往要极力隐藏，甚至都不让别人知道表的名字和结构

八、视图数据操作

1、视图可以进行数据写操作，但是有很多限制。一般只会给查看视图的权限，不会给增删改视图的权限

2、对视图的操作会影响基表，基表的数据会发生改变

3、新增数据

（1）新增数据就是直接对视图进行数据新增

注：给视图新增数据意味着不知道基表，不知道基表就没办法操作基表。所以，所有的操作都是对视图进行的操作

（2）多表视图不能新增数据（不能给一个有连接的视图(多表视图)插入数据）

（3）可以向单表视图插入数据。但是视图中包含的字段必须有基表中所有不能为空（或者没有默认值）的字段

（4）视图是可以向基表插入数据的（对视图的操作会影响基表，基表的数据会发生改变）

    -- 单表视图插入数据
    -- 对视图的操作会影响基表，基表的数据会发生改变
    insert into 视图名 values ('值1', '值2', ... );

    -- 创建视图：单表视图
    create view my_v4 as
    select * from my_class;

    -- 查看数据
    select * from my_class;
    select * from my_v4;

    -- 单表视图插入数据
    -- 视图中包含的字段必须有基表中所有不能为空（或者没有默认值）的字段
    -- 对视图的操作影响了基表，基表数据发生了改变
    insert into my_v4 values (666, '6班', 'B606');

    -- 查看数据
    select * from my_v4;
    select * from my_class;

4、删除数据

（1）多表视图不能删除数据（此处的多表视图指的是连接） -- 试一下多表数据源（笛卡尔积）、联合 可不可以删除???

eg：学生表和班级表连成一个视图，如果删除学生时把班级也删除了，就会造成所有的学生都没有匹配的班级了 -- 数据不可控

    -- 创建多表视图（连接）
    create view my_v5 as
    select s.*, c.name as c_name, c.room from my_student as s
    left join my_class c on c.id = s.c_id;

    -- 查看视图数据
    select * from my_v5;

    -- 多表视图删除数据
    delete from my_v5 where id = 1;
    -- 报错：Can not delete from join view 'mydatabase.my_v5'

（2）单表视图可以删除数据

    -- 单表视图删除数据
    -- 对视图的操作会影响基表，基表的数据会发生改变
    delete from 视图名 [where条件];

    -- 创建视图：单表视图
    create view my_v6 as
    select * from my_class;

    -- 查看数据
    select * from my_class;
    select * from my_v6;

    -- 单表视图删除数据
    -- 对视图的操作影响了基表，基表数据发生了改变
    delete from my_v6 where id = 1;

    -- 查看数据
    select * from my_v6;
    select * from my_class;

5、更新数据

（1）理论上，不论单表视图还是多表视图，都可以更新数据

    -- 单表视图|多表视图 更新数据
    -- 对视图的操作会影响基表，基表的数据会发生改变
    update 视图名 set 字段名 = 值 [where条件]

    -- 创建多表视图
    create view my_v7 as
    select s.*, c.name as c_name, c.room from my_student as s
    left join my_class c on c.id = s.c_id

    -- 查看数据
    select * from my_v7;

    -- 多表视图更新数据
    -- 对视图的操作影响了基表，基表数据发生了改变
    update my_v7 set c_id = 333 where id = 3;

    -- 查看数据
    select * from my_v7;
    select * from my_student;
    select * from my_class;

（2）更新限制：with check option。在创建视图的时候，如果对某个字段有限制，那么在对视图进行数据更新操作时，系统会进行验证。要保证更新之后，数据依然可以被视图查询出来（更新后的数据值依旧满足创建视图时对字段的限制），否则不让更新

    -- 更新限制（with check option）
    -- 创建视图，使用更新限制
    create view 视图名 as
    select * / 字段列表 from 表名 where条件1 with check option;

    -- 更新数据
    -- 更新的字段值必须满足创建视图时设定是where条件1，否则报错
    update 视图名 set 字段名 = 值 [where条件];

    -- 更新限制（with check option）
    -- 创建视图，使用更新限制
    -- where age>30：视图的数据来源都是age>30的数据
    -- with check option：视图更新的时候，不能将已经得到的age>30的数据改成age<=30的数据
    create view my_v8 as
    select * from my_student where age > 30 with check option;

    -- 查看数据
    select * from my_v8;

    -- 将视图可以查到的数据改成age<30的数据
    update my_v8 set age = 29 where id = 1;
    -- 报错：CHECK OPTION failed 'mydatabase.my_v8'

    -- 对视图的操作影响了基表，基表数据发生了改变
    update my_v8 set age = 36 where id = 1;    -- 修改成功
    -- 针对视图中的数据进行更新。视图中没有id=111的数据，所以修改无效
    update my_v8 set age = 36 where id = 111;

    -- 查看数据
    select * from my_v8;

6、单表视图和多表视图的操作区别

（1）单表视图：可以插入数据，可以删除数据，可以更新数据

（2）多表视图：不能插入数据，不能删除数据，可以更新数据

九、视图算法

1、视图算法：系统对视图以及外部查询视图的select语句的一种解析方式

2、视图算法分为三种

（1）undefined：未定义（默认）。这不是一种实际使用的算法，是一种推卸责任的算法。告诉系统，视图没有定义算法，系统自己看着办

（2）temptable：临时表算法。系统应该先执行视图的select语句，后执行外部查询语句（将第一次查询到的数据拿到内存中，再对内存进行操作，操作了两次。因为拿到内存的数据也不一定全部都是需要的数据）

（3）merge：合并算法。系统应该先将视图对应的select语句与外部查询视图的select语句进行合并，然后执行 -- 效率高（常态）

注：

a). 执行一次查询的效率比执行两次查询的效率高

b). 系统经常选择merge，undefined时也会经常选择merge

3、算法指定：在创建视图的时候

    -- 算法指定：在创建视图的时候
    create algorithm = 指定算法 view 视图名 as select 语句;

    -- 获取每个班级中最高的一个学生
    create view my_v9 as 
    select * from my_student order by height desc;

    -- 查询数据
    -- 下面两句sql效果一样，结果都是错误的
    select * from my_v9 group by c_id;
    select * from my_student group by c_id order by height desc;

    -- 指定算法为临时表temptable
    create algorithm = temptable view my_v10 as
    select * from my_student order by height desc;

    -- 查看视图创建语句
    show create view my_v10\G;
    
    -- 查看数据
    -- group by c_id：group by本身也带有排序效果，默认是升序asc。此处默认按照c_id升序排序
    select * from my_v10 group by c_id;    -- 结果正确
    -- 等价于
    select * from (select * from my_student order by height desc) as a group by a.c_id;

4、视图算法选择：如果视图的select语句中会包含一个查询子句（5子句），而且很有可能顺序比外部的查询语句要靠后，此时一定要使用临时表算法temptable。其他情况可以不用指定，默认即可