SQL语言笔记 第七章 视图 VIEW

本文详细介绍了在MySQL数据库中如何使用视图进行数据操作,包括创建、更改和删除视图,以及如何利用视图进行数据的插入、更新和删除。视图作为虚拟表简化了查询,减少了数据库设计改动的影响,并提供了数据访问的安全性。通过WITH CHECK OPTION子句,可以限制对视图的更新和删除操作,确保数据完整性。视图在保护敏感信息和提供数据访问控制方面具有显著优点。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

使用的是MySQL数据库

很多时候没有直接修改表的权限,因此需要借助视图来修改表

1. 创建视图

  • 视图可以看作是一张虚拟表,但视图不存储数据,可以认为视图是存储了选择语句,基础表的内容改变,相关的视图内容也会变。
-- 创建视图
CREATE VIEW sales_by_client AS
-- 未来可能多次使用下面查询的信息,因此可以创建视图保存这段查询
SELECT
c.client_id,
c.name,
SUM(invoice_total) AS total_sales
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id,name;
 
-- 视图可以像表一样查询
SELECT *
FROM sales_by_client
-- 查询sales_by_client视图,结果按照total_sales降序排列
ORDER BY total_sales DESC;
-- 关联其他表查询
-- JOIN clients c USING (client_id)
-- 查询金额大于500的结果
-- WHERE total_sales > 500;

在这里插入图片描述

2. 更改或删除视图

-- 更改视图的两种方法
-- 第一种:删除视图并重新创建
-- 删除视图写法 DROP VIEW
-- DROP VIEW sales_by_client;
-- 再创建视图
 
-- 第二种:CREATE OR REPLACE VIEW
-- 推荐下面这种写法,这样不需要先删除视图
CREATE OR REPLACE VIEW sales_by_client AS
SELECT
c.client_id,
c.name,
SUM(invoice_total) AS total_sales
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id,name;
  • 如何拿到创建视图的查询?
    – 第一种:将视图另存在sql文件中;(推荐)
    – 第二种:打开视图的编辑模式
    在这里插入图片描述
  • MySQL会给表和列名称打上反引号,这是为了防止名称与MySQL关键字发生冲突;
    在这里插入图片描述
  • 修改好查询语句,点击右下角的Apply,即可更新视图;
    在这里插入图片描述

3. 可更新视图 - 可以使用INSERT\UPDATE\DELETE语句更新 可更新视图

-- 如果查询语句中没有出现以下这些关键字或函数,则称这个视图为可更新视图
-- DISTINCT
-- 聚合函数
-- GROUP BY / HAVING
-- UNION
 
-- 创建可更新视图
-- 给invoices表添加结余列,且结余>0,并保存为视图
CREATE OR REPLACE VIEW invoices_with_balance AS
SELECT
	invoice_id,
	number,
	client_id,
	invoice_total,
	payment_total,
   -- 结余列balance
	(invoice_total - payment_total) AS balance,
	invoice_date,
	due_date,
	payment_date
FROM invoices
-- WHERE子句不可以使用别名或聚合函数
WHERE (invoice_total - payment_total) > 0;

-- 删除视图中invoice_id为1的发票
DELETE FROM invoice_with_balance
WHERE invoice_id = 1;
 
-- 更新视图中invoice_id为2的发票,到期日期改为后天
UPDATE invoice_with_balance
SET due_date = DATE_ADD(NOW(), INTERVAL 2 DAY)
WHERE invoice_id = 2;
 
-- 视图中插入数据
-- 在视图中插入的记录也会被插入到基础表中,所以视图要与单表的列一致,不多不少,否则插入不成功(试过视图中缺少列,或者有单个基础表的所有列并且还多了其他列,都插入不成功);
-- 我的理解:将单表所有列都复制给视图,并且视图中只有这一个单表列,可以通过视图向表中插入数据;
-- 插入成功后,其他视图中的结果也会随着基础表的变化而变化;

4. WITH CHECK OPTION 子句

  • 添加了WITH CHECK OPTION子句可以限制使视图中记录消失的更新和删除操作;
  • 但如果更新视图的操作不会让视图中的这条记录消失,则会更新视图中的这个列,且对应基础表中的列数据也会随之变化。
-- 前提:使用invoices_with_balance视图
UPDATE invoices_with_balance
SET payment_total = invoice_total
WHERE invoice_id = 2
-- 更新付款金额等于发票金额,则此时结余为0;若直接执行上面代码,则视图中将不显示发票id为2的记录;
-- 若此时不希望更新或删除的动作,导致发票不在视图中显示,就在WHERE子句后面加上WITH CHECK OPTION子句并执行;
-- 再执行上面的更新语句,就会得到错误提示“检查视图失败”;
-- *注意:需要在添加了WITH CHECK OPTION子句后,视图才会在执行了可能将行从视图中删除的更新、删除语句后报错;
-- *但如果更新视图的操作不会让视图中的这条记录消失,则会更新视图中的这个列,且对应基础表中的列数据也会随之变化。

5. 视图的优点

1. 简化查询;(简单)
2. 减少数据库设计改动的影响;(复用)
如:发票表中的一些列移到其他表里,其他表的列移到发票表中,此时希望能看到一开始发票中的记录,则此时使用视图就可以在不变动表结构的情况下查询原有表数据;
3. 限制基础表访问;(安全)
如:取消用户直接访问基础表的权限,可以通过使用WHERE子句筛选显示部分数据,保护机密数据的安全;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值