2.4 创建视图

原创于 2025-06-12 20:53:53 发布 · 523 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

MySQL 专栏收录该内容

19 篇文章

订阅专栏

1. 创建视图

因为越来越多的表连接+子查询会十分复杂，所以可以将这些查询or子查询存在视图里，下一次还可以继续使用这些视图，而不需要再写一次查询。

如，在数据库【sql_invoicing】中写一段查询，得到每位客户的总销售额:

use sql_invoicing;

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
order by client_id

这样的查询十分频繁，所以可以直接保存为视图，在其他地方进行使用。

创建视图：

use sql_invoicing;

-- 创建视图
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
order by client_id

views中将会显示我们创建的视图

我们可以将视图视为一个表去进行操作，比如对总的销售进行倒序排序：

select *
from sales_by_client
order by client_id desc;

注意：⚠️视图中不存储数据，数据只储存于表中

【练习】创建一个视图，显示每一个顾客的结余，命名为“clients_balance”，视图中包含client_id、name、balance

create view clients_balance as 
select 
	c.client_id,
    c.name,
    sum(invoice_total - payment_total) as balance
	from clients c
    join invoices i using (client_id)
group by client_id, name
order by client_id;

2. 更改或删除视图

方法一：删除视图重新创建

drop view sales_by_client

方法二：使用REPLACE关键字（推荐）

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
order by client_id;

为了防止视图丢掉，一般情况下创建一个views的文件夹，将创建视图的源代码进行存储（团队），或者按如下图所示的小按钮，视图会以编辑模式打开

3. 可更新视图

可更新视图不包含以下语句（可以在视图中更新数据）：

distinct
aggregate functions（min，max，sum）
group by、having
union

可更新视图可以使用于insert、update、delete语句中

创建一个有结余的视图，其中结余=支票总计-支付总计

use sql_invoicing;

create or replace view invoices_with_balace as 
select 
	invoice_id,
    number,
    client_id,
    invoice_total,
    payment_total,
    invoice_total-payment_total as balance,
    invoice_date,
    due_date,
    payment_date
from invoices 
where invoice_total-payment_total > 0 -- 无法使用balanace，系统会告诉你没有这个列

这是一个可更细视图，所以可以进行修改:

delete from invoices_with_balace
where invoice_id = 1

4. WITH OPTION CHECK字句

修改上个视图中的2号，将他的payment_total改成invoice_total

update invoices_with_balace
set payment_total = invoice_total
where invoice_id = 2;

可以看到2消失了，因为2计算出来的invoice_total-payment_total=0，不在视图范围内，因此不会显示出来。

如果不想update或delete语句将行从视图中删除，可以在视图代码中进行以下操作：

use sql_invoicing;

create or replace view invoices_with_balace as 
select 
	invoice_id,
    number,
    client_id,
    invoice_total,
    payment_total,
    invoice_total-payment_total as balance,
    invoice_date,
    due_date,
    payment_date
from invoices 
where invoice_total-payment_total > 0 -- 无法使用balanace，系统会告诉你没有这个列
with check option

在执行后可以发现报错，也就是说如果在修改某一行时，其结果可能会导致该行从视图里被删除，就会有报错