MySQL基础知识—视图

最新推荐文章于 2024-07-24 05:15:00 发布

kuangd_1992

最新推荐文章于 2024-07-24 05:15:00 发布

阅读量1.3k

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/kuangd_1992/article/details/121720446

版权

MySQL 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

学习目的

1、了解视图的作用

2、学习视图创建的过程，以及视图的用法

基本概念

视图是虚拟的表，只包含使用时动态检索数据的查询。

示例

用下面的select语句从3个表中检索数据：

mysql> select cust_name,cust_contact from customers,orders,orderitems where customers.cust_id=orders.cust_id and orderitems.order_num=orders.order_num and prod_id='RGAN01';

这个查询语句用来检索订购了某个特定产品的客户。

为了检索其他产品的数据，需要修改最后的where子句。

如果可以将整个查询包装成一个名为productcustomers的虚拟表，则可以轻松的检索出相同的数据：

mysql> select cust_name,cust_contact
		from productcustomers
		where prod_id='RGAN01';

以上就是视图的简单作用。视图不包含任何列或数据，包含的是一个查询。

通过视图，可以展现基表的部分数据；视图数据来自定义视图的查询中使用的表，使用视图动态生成。

常见应用

重用SQL语句
简化复杂的SQL操作，在编写查询后，可以方便的重用它而不必知道它的基本查询细节。
使用表的组成部分而不是整张表
保护数据。可以给用户授予表特定部分的访问权限而不是整个表的访问权限。
更改数据格式和表示。视图可以返回与基表的表示和格式不同的数据。

视图仅仅是用来查看存储在别处的数据的一种设施，视图本身不包含数据，它们返回的数据是从其他表中检索出来的。

在添加或更改这些表中的数据时，视图将返回改变过的数据。

规则和限制

视图必须唯一命名—不能取与别的视图或表相同的名字
创建的视图数目没有限制
为了创建视图，必须有足够的访问权限
视图可以嵌套。可以利用其他视图中检索数据的查询来构造一个视图。
许多DBMS禁止在视图查询中使用order by子句
视图不能索引，也不能有关联的触发器或默认值

创建视图

语法

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
   [WITH [CASCADED | LOCAL] CHECK OPTION]

1）OR REPLACE：表示替换已有视图

2）ALGORITHM：表示视图选择算法，默认算法是UNDEFINED(未定义的)：MySQL自动选择要使用的算法；merge合并；temptable临时表

3）select_statement：表示select语句

4）[WITH [CASCADED | LOCAL] CHECK OPTION]：表示视图在更新时保证在视图的权限范围之内

　　cascade是默认值，表示更新视图的时候，要满足视图和表的相关条件

　　local表示更新视图的时候，要满足该视图定义的一个条件即可

TIPS：推荐使用WHIT [CASCADED|LOCAL] CHECK OPTION选项，可以保证数据的安全性

示例

将上面的例子中复杂的查询SQL隐藏起来，做成一个视图。

mysql> create view productcustomers as select cust_name,cust_contact,prod_id from customers,orders,orderitems where customers.cust_id=orders.cust_id and orderitems.order_num = orders.order_num;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from productcustomers;
+----------------+--------------+---------+
| cust_name      | cust_contact | prod_id |
+----------------+--------------+---------+
| Coyote Inc.    | Y Lee        | ANV01   |
| Coyote Inc.    | Y Lee        | ANV02   |
| Coyote Inc.    | Y Lee        | TNT2    |
| Coyote Inc.    | Y Lee        | FB      |
| Coyote Inc.    | Y Lee        | FB      |
| Coyote Inc.    | Y Lee        | OL1     |
| Coyote Inc.    | Y Lee        | SLING   |
| Coyote Inc.    | Y Lee        | ANV03   |
| Wascals        | Jim Jones    | JP2000  |
| Yosemite Place | Y Sam        | TNT2    |
| E Fudd         | E Fudd       | FC      |
+----------------+--------------+---------+
11 rows in set (0.03 sec)

上面语句创建了一个名为productcustomers的视图，联结三个表，返回已订购了任意产品的所有客户的列表。

查看视图

1、使用show create view语句来查看视图信息。

mysql> show create view productcustomers;
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View             | Create View                                                                                                                                                                                                                                                                                                                                                                                          | character_set_client | collation_connection |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| productcustomers | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `productcustomers` AS select `customers`.`cust_name` AS `cust_name`,`customers`.`cust_contact` AS `cust_contact`,`orderitems`.`prod_id` AS `prod_id` from ((`customers` join `orders`) join `orderitems`) where ((`customers`.`cust_id` = `orders`.`cust_id`) and (`orderitems`.`order_num` = `orders`.`order_num`)) | utf8mb4              | utf8mb4_0900_ai_ci   |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.03 sec)

2、创建完成视图后，使用查询语句查询

3、有关视图的信息记录在infor_schema数据库的views表中。

mysql> select * from information_schema.views where table_name='productcustomers';
+---------------+--------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME       | VIEW_DEFINITION                                                                                                                                                                                                                                                                                                                                                            | CHECK_OPTION | IS_UPDATABLE | DEFINER        | SECURITY_TYPE | CHARACTER_SET_CLIENT | COLLATION_CONNECTION |
+---------------+--------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
| def           | mysql        | productcustomers | select `mysql`.`customers`.`cust_name` AS `cust_name`,`mysql`.`customers`.`cust_contact` AS `cust_contact`,`mysql`.`orderitems`.`prod_id` AS `prod_id` from `mysql`.`customers` join `mysql`.`orders` join `mysql`.`orderitems` where ((`mysql`.`customers`.`cust_id` = `mysql`.`orders`.`cust_id`) and (`mysql`.`orderitems`.`order_num` = `mysql`.`orders`.`order_num`)) | NONE         | YES          | root@localhost | DEFINER       | utf8mb4              | utf8mb4_0900_ai_ci   |
+---------------+--------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
1 row in set (0.03 sec)

使用视图过滤不想要的数据

视图对于普通的where子句也很有用。

示例

定义customeremaillist视图，过滤没有电子邮件地址的客户。

mysql> create view customeremaillist as select cust_id,cust_name,cust_email from customers where cust_email is not null;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from customeremaillist;
+---------+----------------+---------------------+
| cust_id | cust_name      | cust_email          |
+---------+----------------+---------------------+
|   10001 | Coyote Inc.    | ylee@coyote.com     |
|   10003 | Wascals        | rabbit@wascally.com |
|   10004 | Yosemite Place | sam@yosemite.com    |
+---------+----------------+---------------------+
3 rows in set (0.02 sec)

视图的更改

create or replace view语句修改视图

基本格式：

create or replace view view_name as select语句;

在视图存在的情况下对视图进行修改。

视图不存在的情况下创建视图。

alter语句修改视图

ALTER
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

注意：修改视图是指修改数据库中已存在的表的定义，当基表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致。