MySQL之视图详解

华子目录

• 什么是视图？
• 创建视图
• 查询
• • 查看创建视图语句
  • 查看视图数据
  • 修改视图结构
  • • 方式1
    • 方式2
    • 删除视图
    • 在视图中插入数据
    • 视图的==with检查选项==
    • 视图的更新
    • 视图的作用和优点
    • 视图的使用规则
    • 案例

      什么是视图？

      • 视图（view）是一种虚拟存在的表，视图中的数据并不在数据库中实际存在，视图中的数据来自定义视图时select查询的表，并且视图是动态生成的。
      • 通俗的讲：视图只保存了select的SQL逻辑，不保存查询结果。所以我们在创建视图时，主要工作就落在创建select查询语句上。
      • MySQL 视图是一个虚拟的表，它由一个 SQL 查询定义，并且不存储实际的数据。视图的数据来自于查询执行的结果，并且可以像表一样被查询、更新和删除。视图提供了一种简化复杂查询的方法，并且可以用来限制用户对数据库中特定数据的访问。
      • 视图的定义保存在数据字典内，创建视图所基于的表称为“基表”

        创建视图

        mysql> create [or replace] view 视图名[(字段列表)] as select语句 [with [cascaded/local] check option];

        注：视图的字段列表写不写都无所谓

        mysql> select * from stu;+-----+--------+-----+---------+| id  | name   | age | classid |+-----+--------+-----+---------+| 101 | 小天   |  18 |    1001 || 102 | 小明   |  20 |    1003 || 103 | 小红   |  13 |    1002 |+-----+--------+-----+---------+mysql> create or replace view view_stu as select id,name from stu where age>15;Query OK, 0 rows affected (0.02 sec)mysql> show tables;+-----------------+| Tables_in_huazi |+-----------------+| account         || class           || department      || employee        || mytable         || stu             || view_employee   || view_stu        |+-----------------+mysql> select * from view_stu;+-----+--------+| id  | name   |+-----+--------+| 101 | 小天   || 102 | 小明   |+-----+--------+

        mysql> select * from employee;+------+-----------+-----------------+--------+-------+--------+| id   | name      | job             | salary | bonus | job_id |+------+-----------+-----------------+--------+-------+--------+|  101 | 麦当      | 后端研发        |  25000 |  5000 |   1003 ||  102 | 咕咚      | 网络运维        |  15000 |  3000 |   1003 ||  103 | 迪亚      | 测试工程师      |  12000 |  2000 |   1003 ||  104 | 米龙      | 后端开发        |  20000 |  3500 |   1003 ||  105 | 极光      | 前端开发        |  15000 |  2500 |   1003 ||  106 | 村长      | 人力资源        |  10000 |   500 |   1001 ||  107 | 五条人    | 销售工程师      |  14000 |  7000 |   1002 ||  108 | 皇帝      | 董事长          |  30000 | 10000 |   1004 |+------+-----------+-----------------+--------+-------+--------+mysql> create or replace view view_employee(id,name,salary) as select * from employee where salary>15000;ERROR 1353 (HY000): In definition of view, derived table or common table expression, SELECT list and column names list have different column counts#报错原因：视图的字段列表和select的字段列表必须一致，否则就会报错mysql> create or replace view view_employee(id,name,salary) as select id,name,salary from employee where salary>15000;Query OK, 0 rows affected (0.00 sec)mysql> show tables;+-----------------+| Tables_in_huazi |+-----------------+| account         || class           || department      || employee        || mytable         || stu             || view_employee   |+-----------------+mysql> select * from view_employee;+------+--------+--------+| id   | name   | salary |+------+--------+--------+|  101 | 麦当   |  25000 ||  104 | 米龙   |  20000 ||  108 | 皇帝   |  30000 |+------+--------+--------+

        查询

        查看创建视图语句

        mysql> show create view 视图名\G;

        mysql> show create view view_employee;+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+| View          | Create View                                         | character_set_client | collation_connection |+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+| view_employee | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_employee` (`id`,`name`,`salary`) AS select `employee`.`id` AS `id`,`employee`.`name` AS `name`,`employee`.`salary` AS `salary` from `employee` where (`employee`.`salary` > 15000) | utf8mb4              | utf8mb4_0900_ai_ci   |+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+mysql> show create view view_employee\G;*************************** 1. row ***************************                View: view_employee         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_employee` (`id`,`name`,`salary`) AS select `employee`.`id` AS `id`,`employee`.`name` AS `name`,`employee`.`salary` AS `salary` from `employee` where (`employee`.`salary` > 15000)character_set_client: utf8mb4collation_connection: utf8mb4_0900_ai_ci

        查看视图数据

        mysql> select * from 视图名;

        mysql> select * from view_employee;+------+--------+--------+| id   | name   | salary |+------+--------+--------+|  101 | 麦当   |  25000 ||  104 | 米龙   |  20000 ||  108 | 皇帝   |  30000 |+------+--------+--------+

        修改视图结构

        方式1

        mysql> create [or replace] view 视图名[(字段列表)] as select语句 [with [cascaded/local] check option];

        mysql> select * from view_employee;+------+--------+--------+| id   | name   | salary |+------+--------+--------+|  101 | 麦当   |  25000 ||  104 | 米龙   |  20000 ||  108 | 皇帝   |  30000 |+------+--------+--------+mysql> create or replace view view_employee as select id,name from employee where salary>15000;Query OK, 0 rows affected (0.00 sec)mysql> select * from view_employee;+------+--------+| id   | name   |+------+--------+|  101 | 麦当   ||  104 | 米龙   ||  108 | 皇帝   |+------+--------+

        注：视图的字段列表写不写都无所谓

        方式2

        mysql> alter view 视图名[(字段列表)] as select语句 [with [cascaded/local] check option];

        mysql> select * from stu;+-----+--------+-----+---------+| id  | name   | age | classid |+-----+--------+-----+---------+| 101 | 小天   |  18 |    1001 || 102 | 小明   |  20 |    1003 || 103 | 小红   |  13 |    1002 |+-----+--------+-----+---------+mysql> select * from view_stu;+-----+--------+| id  | name   |+-----+--------+| 101 | 小天   || 102 | 小明   |+-----+--------+mysql> alter view view_stu as select id,name,age from stu where age>15;Query OK, 0 rows affected (0.03 sec)mysql> select * from view_stu;+-----+--------+-----+| id  | name   | age |+-----+--------+-----+| 101 | 小天   |  18 || 102 | 小明   |  20 |+-----+--------+-----+

        注：视图的字段列表写不写都无所谓

        删除视图

        mysql> drop view [if exists] 视图名;

        mysql> show tables;+-----------------+| Tables_in_huazi |+-----------------+| account         || class           || department      || employee        || mytable         || stu             || view_employee   || view_stu        |+-----------------+mysql> drop view if exists view_stu;Query OK, 0 rows affected (0.00 sec)mysql> show tables;+-----------------+| Tables_in_huazi |+-----------------+| account         || class           || department      || employee        || mytable         || stu             || view_employee   |+-----------------+

        在视图中插入数据

        注：在视图中插入数据，都是基于基表的插入条件

        mysql> create view view_account as select id,name from account where money>=2000;Query OK, 0 rows affected (0.01 sec)mysql> show tables;+-----------------+| Tables_in_huazi |+-----------------+| account         || class           || department      || employee        || mytable         || stu             || view_account    || view_employee   |+-----------------+mysql> select * from view_account;+----+--------+| id | name   |+----+--------+|  1 | 张三   ||  2 | 李四   |+----+--------+mysql> insert into view_account values    -> (3,'小明');Query OK, 1 row affected (0.01 sec)mysql> select * from view_account;+----+--------+| id | name   |+----+--------+|  1 | 张三   ||  2 | 李四   |+----+--------+mysql> select * from account;+----+--------+-------+| id | name   | money |+----+--------+-------+|  1 | 张三   |  2000 ||  2 | 李四   |  2000 ||  3 | 小明   |  NULL |+----+--------+-------+

        mysql> select * from account;+----+--------+-------+| id | name   | money |+----+--------+-------+|  1 | 张三   |  2000 ||  2 | 李四   |  2000 |+----+--------+-------+#将主表中的money字段修改约束条件为not nullmysql> alter table account modify money int not null;Query OK, 0 rows affected (0.06 sec)mysql> show create table account\G;*************************** 1. row ***************************       Table: accountCreate Table: CREATE TABLE `account` (  `id` int NOT NULL AUTO_INCREMENT,  `name` varchar(10) NOT NULL,  `money` int NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='账户表'mysql> select * from view_account;+----+--------+| id | name   |+----+--------+|  1 | 张三   ||  2 | 李四   |+----+--------+#插入失败的原因：主表中的money字段是not nullmysql> insert int view_account values    -> (3,'小明');ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int view_account values(3,'小明')' at line 1

        视图的with检查选项

        视图的更新

        • 要使视图可更新，视图数据的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新:

          视图的作用和优点

          作用：

          • 控制安全
          • 保存查询数据

            优点：

            • 提供了灵活一致级别安全性。
            • 隐藏了数据的复杂性
            • 简化了用户的SQL指令
            • 通过重命名列，从另一个角度提供数据

              视图的使用规则

              1. 视图必须有唯一命名
              2. 在mysql中视图的数量没有限制
              3. 创建视图必须从管理员那里获得必要的权限
              4. 视图支持嵌套，也就是说可以利用其他视图检索出来的数据创建新的视图
              5. 在视图中可以使用order by，但是如果视图内已经使用该排序子句，则视图的ORDER BY将覆盖前面的ORDER BY。
              6. 视图不能索引，也不能关联触发器或默认值
              7. 视图可以和表同时使用

              案例