MySQL之视图详解

华子目录

什么是视图？

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

创建视图

mysql>create[orreplace]view 视图名[(字段列表)]asselect语句 [with[cascaded/local]checkoption];

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

mysql>select*from stu;+-----+--------+-----+---------+| id  | name   | age | classid |+-----+--------+-----+---------+|101| 小天   |18|1001||102| 小明   |20|1003||103| 小红   |13|1002|+-----+--------+-----+---------+

mysql>createorreplaceview view_stu asselect id,name from stu where age>15;
Query OK,0rows affected (0.02 sec)

mysql>showtables;+-----------------+| 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>createorreplaceview view_employee(id,name,salary)asselect*from employee where salary>15000;
ERROR 1353(HY000): In definition ofview, derived tableor common table expression,SELECT list andcolumn names list have different column counts
#报错原因：视图的字段列表和select的字段列表必须一致，否则就会报错

mysql>createorreplaceview view_employee(id,name,salary)asselect id,name,salary from employee where salary>15000;
Query OK,0rows affected (0.00 sec)

mysql>showtables;+-----------------+| 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>showcreateview 视图名\G;

mysql>showcreateview view_employee;+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+|View|CreateView| character_set_client | collation_connection |+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+| view_employee |CREATEALGORITHM=UNDEFINEDDEFINER=`root`@`localhost`SQL SECURITY DEFINERVIEW`view_employee`(`id`,`name`,`salary`)ASselect`employee`.`id`AS`id`,`employee`.`name`AS`name`,`employee`.`salary`AS`salary`from`employee`where(`employee`.`salary`>15000)| utf8mb4              | utf8mb4_0900_ai_ci   |+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

mysql>showcreateview view_employee\G;***************************1.row***************************View: view_employee
         CreateView: CREATEALGORITHM=UNDEFINEDDEFINER=`root`@`localhost`SQL SECURITY DEFINERVIEW`view_employee`(`id`,`name`,`salary`)ASselect`employee`.`id`AS`id`,`employee`.`name`AS`name`,`employee`.`salary`AS`salary`from`employee`where(`employee`.`salary`>15000)
character_set_client: utf8mb4
collation_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[orreplace]view 视图名[(字段列表)]asselect语句 [with[cascaded/local]checkoption];

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

mysql>createorreplaceview view_employee asselect id,name from employee where salary>15000;
Query OK,0rows affected (0.00 sec)

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

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

方式2

mysql>alterview 视图名[(字段列表)]asselect语句 [with[cascaded/local]checkoption];

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>alterview view_stu asselect id,name,age from stu where age>15;
Query OK,0rows affected (0.03 sec)

mysql>select*from view_stu;+-----+--------+-----+| id  | name   | age |+-----+--------+-----+|101| 小天   |18||102| 小明   |20|+-----+--------+-----+

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

删除视图

mysql>dropview[ifexists] 视图名;

mysql>showtables;+-----------------+| Tables_in_huazi |+-----------------+| account         || class           || department      || employee        || mytable         || stu             || view_employee   || view_stu        |+-----------------+

mysql>dropviewifexists view_stu;
Query OK,0rows affected (0.00 sec)

mysql>showtables;+-----------------+| Tables_in_huazi |+-----------------+| account         || class           || department      || employee        || mytable         || stu             || view_employee   |+-----------------+

在视图中插入数据

在这里插入图片描述
注：在视图中插入数据，都是基于基表的插入条件

mysql>createview view_account asselect id,name from account where money>=2000;
Query OK,0rows affected (0.01 sec)

mysql>showtables;+-----------------+| Tables_in_huazi |+-----------------+| account         || class           || department      || employee        || mytable         || stu             || view_account    || view_employee   |+-----------------+

mysql>select*from view_account;+----+--------+| id | name   |+----+--------+|1| 张三   ||2| 李四   |+----+--------+

mysql>insertinto view_account values->(3,'小明');
Query OK,1row 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 null
mysql>altertable account modify money intnotnull;
Query OK,0rows affected (0.06 sec)

mysql>showcreatetable account\G;***************************1.row***************************Table: account
CreateTable: CREATETABLE`account`(`id`intNOTNULLAUTO_INCREMENT,`name`varchar(10)NOTNULL,`money`intNOTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=4DEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='账户表'

mysql>select*from view_account;+----+--------+| id | name   |+----+--------+|1| 张三   ||2| 李四   |+----+--------+#插入失败的原因：主表中的money字段是not null
mysql>insertint 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 touse near 'int view_account values
(3,'小明')' at line 1

视图的with检查选项

在这里插入图片描述

视图的更新

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

视图的作用和优点

在这里插入图片描述
作用：

控制安全
保存查询数据

优点：

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

视图的使用规则

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

案例

在这里插入图片描述

标签： mysql 数据库运维

本文转载自: https://blog.csdn.net/huaz_md/article/details/136443735
版权归原作者 ^～^前行者～～～ 所有，如有侵权，请联系我们删除。

MySQL之视图详解

华子目录

什么是视图？

创建视图

查询

查看创建视图语句

查看视图数据

修改视图结构

方式1

方式2

删除视图

在视图中插入数据

视图的with检查选项

视图的更新

视图的作用和优点

视图的使用规则

案例

发表评论

“MySQL之视图详解”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航