MYSQL常见概念三：视图

定义：   利用一条sql实现复杂的查询功能并对外封装成一张简单的二维表(虚拟表)

特性：   1. 视图只是逻辑上对一张或若干表做了关联关系，其删除与创建对实表无影响

               2. 视图也可进行正常的增删改查，并且会影响对应实表，但当视图来自多个实表时，不允许增删数据

应用场景：  1.  当查询比较复杂时，每次编写sql太麻烦，利用视图构建这样一个虚表，在该表上进行查询，数据库会将该查询

                        作用到具体实表上

                   2.  可将数据库用户权限加到行列级别，利用视图可轻松对某个用户隐藏某行某列，或着“增加"某列

  优点：    1.   安全

                2.  方便

                3. 多维度展示数据，在一定程度上有点类似 MapReduce

   缺点：   1. 性能差

                 2. 修改麻烦

   创建视图语法： 

CREATE                          -- 创建
OR REPLACE                      -- 如果已经存在就替
ALGORITHM = UNDEFINED           -- 视图使用的算法  有 UNDEFINED , MERGE , TEMPTABLE
                                -- UNDEFINED 表示mysql自动选择算法
                                -- MERGE     将视图查询语句转换成对实表查询的sql，再查询对应的数据
                                -- TEMPTABLE 将视图结果集存入临时表，查询查临时表
VIEW view_test(id,name)         -- 创建一个名为view_test的视图，包含id,name 两列
AS 
SELECT id,name FROM users;      -- 创建视图用到的查询语句(从users表选出两列id,name)
-- WITH CHECK OPTION            -- 保持视图一致性，视图相关操作只影响视图涉及到的数据或者权限范围内的数据
-- WITH CASCADED CHECK OPTION   -- 1.操作视图时要检查所有实体表的条件是否满足
-- WITH LOCAL CHECK OPTION      -- 2.操作视图时只要满足视图本身的条件即可

示例： 实现视图， institution_users_info ,里面包含机构对应的用户信息，但是不会暴露用户密码信息

首先，创建两张表，users表，institution_users表，并插入数据

DROP TABLE IF EXISTS users ;
CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mobile` BIGINT(11) COLLATE utf8_unicode_ci DEFAULT NULL,
  `pass` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '2018-01-01 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


DROP TABLE IF EXISTS institution_users;
CREATE TABLE `institution_users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `institution_id` int(11) unsigned,
  `user_id` int(11) unsigned,
  `created_at` timestamp NOT NULL DEFAULT '2018-01-01 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO users(id,name,mobile,pass,created_at) 
VALUES (10000,'test_view1',18370337022,'123456',NOW()),
       (10001,'test_view2',18370337023,'123456',NOW()),
       (10002,'test_view3',18370337024,'123456',NOW()),
       (10004,'test_view4',18370337025,'123456',NOW()),
       (10005,'test_view5',18370337026,'123456',NOW());

INSERT INTO institution_users(institution_id,user_id)
VALUES (13,10000),
       (14,10000),
       (15,10000),
       (14,10001),
       (15,10001),
       (14,10002),
       (15,10003),
       (14,10004),
       (15,10004),
       (15,10005),
       (14,10005),
       (16,10005);

建立视图：

CREATE                              
OR REPLACE                      
ALGORITHM = UNDEFINED          
VIEW institution_users_info(institution_id,user_id,user_name,user_mobile)    
AS 
SELECT institution_users.institution_id  as  institution_id ,
       institution_users.user_id         as  user_id,
       users.name                        as  user_name,
       users.mobile                      as  user_mobile
FROM institution_users 
LEFT JOIN users
ON institution_users.user_id = users.id  ; 

执行视图查询：

SELECT * from institution_users_info;

返回结果如下：