MySQL视图

Leshami

发布于 2018-08-06 10:07:21

2.8K0

发布于 2018-08-06 10:07:21

文章被收录于专栏：乐沙弥的世界乐沙弥的世界

视图是关系型数据库重要的组成部分之一，它可以限制数据访问，简化复杂查询，保持数据的独立性，以及基于相同的数据提供不同的视图等等。本文介绍MySQL数据库视图的一些用法，供大家参考。

一、视图的特点

视图与表类似，包含列和数据行可以对视图查询或特定情形下DML操作视图仅仅包含一些DDL定义语句视图不存储任何真实数据，数据来源于基表视图限制访问数据，简化复杂查询

创建视图语法

CREATE
    [OR REPLACE]
    [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]

重要部分说明扩展算法是MySQL在标准SQL之上的扩展视图名称后的列可自定义，可省略 with check option，该选项用于在配置可更新视图时，新增和更新后的数据应能满足视图定义的sql语句过滤条件，确保后续仍可查询到这些记录。

二、简单视图特点

单表查询不包含相关聚合函数不包含分组可通过DML语句更新视图

--1、 请创建一个视图返回更新日期为大于2016-02-15之后客户信息
-- 当前版本
mysql> show variables like 'version';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| version | 5.7.17 |
+---------------+--------+
CREATE VIEW vw_cust
AS
   SELECT first_name, last_name, email
   FROM customer
   WHERE last_update >= '2016-02-15';

--2、 请基于inventory创建一个视图，视图名为vw_inve，要求仅返回store_id值为2的数据行
-- 仅仅返回2列数据，一列inventory_id，一列为film_id，并且将返回的2列列名分别定义为inventory-id,film-id

CREATE OR REPLACE VIEW vw_inve
(
   inventoryid,
   filmid
)
AS
   SELECT inventory_id, film_id
   FROM inventory
   WHERE store_id = 2;

-- 3、请基于vw_inve创建另外一个视图，名称为vw_inventory
CREATE OR REPLACE VIEW vw_inventory
AS
   SELECT * FROM vw_inve;

三、复杂视图的特点

基于多个表连接查询或者子查询包括聚合函数，如max,min,sum,count,distinct等使用了分组group by ,having子句等使用了基于集合的运算如union,union all等

-- 创建一个基于连接查询的视图，要求返回员工的id号，姓名以及地址
CREATE OR REPLACE VIEW vw_list_staff
AS
   SELECT s.staff_id,
          s.first_name,
          s.last_name,
          a.address
   FROM staff AS s INNER JOIN address AS a ON s.address_id = a.address_id;

-- 创建包含聚会函数的视图

CREATE OR REPLACE VIEW vw_pay_ment_sum
AS
   SELECT staff_id, sum(amount)
   FROM payment
   GROUP BY staff_id;

-- 创建包含子查询的视图
-- 请创建一个视图，要求查询产品表当中购买价格高于同一产品平均价格的产品及其价格

CREATE OR REPLACE VIEW vw_higher_product
AS
   SELECT productname, buyprice
   FROM products p1
   WHERE buyprice > (SELECT AVG(buyprice)
                     FROM products
                     WHERE productline = p1.productline);

-- 请创建一个视图，要求按客户地区进行分组，统计各个地区用户总数

CREATE VIEW vw_cust_sun
AS
   SELECT d.district, sum(c.customer_id) sum_cust
   FROM customer c INNER JOIN address d ON c.address_id = d.address_id
   WHERE d.district <> ''
   GROUP BY d.district
   ORDER BY 2 DESC;

四、视图管理

-- 查看视图
mysql> show full tables
mysql> show full tables like '%vw%';

-- 提取视图DDL
mysql> show create view vw_items\G
*************************** 1. row ***************************
                View: vw_items
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` 
         SQL SECURITY DEFINER VIEW `vw_items` AS 
         select `items`.`id` AS `id`,`items`.`name` AS `name`,
         `items`.`price` AS `price` from `items` where (`items`.`price` > 700)
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

-- 查看视图是否可更新
SELECT table_name, is_updatable
FROM information_schema.views
WHERE table_schema = 'sakila';

-- 删除视图
DROP VIEW IF EXISTS vw_items;

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2018年07月24日，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server