MySQL视图

视图是关系型数据库重要的组成部分之一,它可以限制数据访问,简化复杂查询,保持数据的独立性,以及基于相同的数据提供不同的视图等等。本文介绍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;

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏我的博客

ubuntu安装puref-ftpd

1.安装pureftpd sudo apt-get install pure-ftpd sudo apt-get install pure-ftpd-mysql...

2954
来自专栏cloudskyme

oracle修改表字段

增加字段     alter   table   docdsp     add   dspcode   char(200)     删除字段     AL...

36610
来自专栏Python、Flask、Django

自动创建权限表数据和超级管理员用户(第一次优化)

1122
来自专栏沃趣科技

Performance Schema使用简介(一)

Performance Schema简介 Oracle DBA都应该知道 Oracle中提供了大量的视图供DBA们排查问题使用,并且有等待事件帮助大家快速定位问...

44210
来自专栏zcqshine's blog

update in 的优化写法

1747
来自专栏跟着阿笨一起玩NET

SQL语句使用总结(二)

441
来自专栏别先生

mysql的内连接,外连接(左外连接,右外连接)巩固

1:mysql的内连接: 内连接(inner join):显示左表以及右表符合连接条件的记录; 1 select a.goods_id,a.goods_name...

2037
来自专栏Java帮帮-微信公众号-技术文章全总结

【数据库】MySQL进阶一、主外键讲解

MySQL进阶主外键讲解 1.什么是外键: 主键:是唯一标识一条记录,不能有重复的,不允许为空,用来保证数据完整性 外键:是另一表的主键, 外键可...

3547
来自专栏Netkiller

数据库安全·Token 认证

以下节选择《Netkiller Architect 手札》 作者:netkiller 地址 http://www.netkiller.cn/archit...

3854
来自专栏互联网杂技

修改数据表之添加主键约束

上一篇是对表中列的操作: 涉及到列的增删改查; ----- 而本篇是设置一个与另一个表的关系,还有列的默认值; ---- 1.一个表创建之后, 需要对这个表中的...

32111

扫码关注云+社区