sql视图学习-CSDN博客

本文链接：https://blog.csdn.net/q12ERTYU/article/details/151766973

SQL 视图（View）是数据库中一种虚拟表，它基于 SELECT 查询的结果动态生成。视图不存储实际数据，而是提供一种便捷的方式来访问和操作数据

视图是一个基于 SELECT 查询的虚拟表，存储的是查询逻辑，而不是数据本身。每次访问视图时，数据库会执行其定义的查询并返回结果。

特点：

动态生成：数据来自基础表，实时反映基础表的更新。
简化查询：封装复杂查询，方便复用。
安全性：通过视图限制用户访问特定列或行。
不存储数据

2. 创建视图

语法

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY ...]
[HAVING ...]
[ORDER BY ...];

操作示例

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE
);

INSERT INTO employees VALUES
(1, 'Alice', 'HR', 50000, '2023-01-10'),
(2, 'Bob', 'IT', 60000, '2022-06-15'),
(3, 'Charlie', 'HR', 45000, '2023-03-20');


CREATE VIEW hr_employees AS
SELECT id, name, salary
FROM employees
WHERE department = 'HR';

创建一个视图，只显示 HR 部门的员工信息：

SELECT * FROM hr_employees;

视图的类型和特性

简单视图

基于单一表或简单查询。
通常可更新（插入、更新、删除），但需满足条件（如不含聚合函数、DISTINCT、GROUP BY）。
示例：上面的 hr_employees 是简单视图。

复杂视图

包含多表 JOIN、聚合函数（如 COUNT、SUM）、GROUP BY 或子查询。
通常不可更新。
示例：

CREATE VIEW dept_salary AS
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

物化视图

某些数据库（如 PostgreSQL、Oracle）支持物化视图，实际存储数据，适合性能优化。
需要手动或定时刷新。

修改和删除视图

修改视图

使用 CREATE OR REPLACE VIEW：

CREATE OR REPLACE VIEW hr_employees AS

SELECT id, name, salary, hire_date -- 添加 hire_date

FROM employees

WHERE department = 'HR';

删除视图

sql

DROP VIEW IF EXISTS hr_employees;

视图的优缺点

优点

简化查询：封装复杂查询，方便用户调用。
安全性：限制访问特定列或行。例如，只允许查看 hr_employees 而非整个 employees。
逻辑抽象：屏蔽底层表结构变化。
一致性：基础表更新，视图自动同步（非物化视图）。

缺点

性能开销：复杂视图可能导致查询性能下降（非物化视图）。
不可更新性：复杂视图通常不能直接更新。
维护成本：视图依赖的基础表结构变化可能导致视图失效。
存储问题：物化视图占用额外存储空间。

索引视图（Indexed View，或称为物化视图在某些数据库中）是一种特殊的视图，数据库会为其创建索引以提升查询性能，并实际存储视图的数据（类似物化视图）。它在 SQL Server 中最为常见，其他数据库如 PostgreSQL（物化视图）或 Oracle（物化视图）也有类似功能，但实现和限制不同。以下是关于索引视图的详细学习指南，结合你提到的前后端分离项目背景，保持简洁并提供实用代码示例。

什么是索引视图？

定义：索引视图是基于 SELECT 查询的视图，数据库为其创建物理存储（通过唯一聚集索引），使其数据持久化，而不是动态计算。
特点：
- 数据存储：视图的结果集存储在磁盘上，类似表。
- 自动更新：基础表更新时，索引视图同步更新（由数据库引擎维护）。
- 性能提升：适合频繁查询的复杂聚合或 JOIN 操作。
- 限制严格：索引视图对查询和基础表有特定要求（见下文）。
适用场景：
- 报表生成：如汇总销售数据。
- 频繁查询的复杂计算：如多表 JOIN 或聚合。
- 数据仓库：优化只读查询。

Customers（客户表）：包含客户信息，如 CustomerID, CustomerName。
Orders（订单表）：包含订单信息，如 OrderID, CustomerID, OrderDate。
OrderDetails（订单详情表）：包含订单的详细信息，如 OrderID, ProductID, Quantity。
Products（产品表）：包含产品信息，如 ProductID, ProductName。

目标是创建一个视图，显示以下字段：

Customers.CustomerName
Orders.OrderDate
OrderDetails.Quantity
Products.ProductName

这些表通过主键和外键关联：

Customers.CustomerID = Orders.CustomerID
Orders.OrderID = OrderDetails.OrderID
OrderDetails.ProductID = Products.ProductID

sql左连接右连接内连接 sql

CREATE VIEW vw_CustomerOrderDetails
AS
SELECT
c.CustomerName,
o.OrderDate,
od.Quantity,
p.ProductName
FROM
Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID;

业务场景

一个简单的电商数据库，包含以下实体：

Customers（客户表）：存储客户信息。
Orders（订单表）：存储客户下的订单信息。
OrderDetails（订单详情表）：存储订单中的商品详情。
Products（产品表）：存储商品信息。

表之间的关系

Customers 和 Orders 通过 CustomerID 关联（一对多）。
Orders 和 OrderDetails 通过 OrderID 关联（一对多）。
OrderDetails 和 Products 通过 ProductID 关联（多对一）。

创建这四个表的SQL语句（包含主键、外键、常见字段和约束）。
编写视图，展示来自四个表的字段（基于前文要求）。
展示使用 INNER JOIN、LEFT JOIN 和 RIGHT JOIN 的查询示例。

-- 创建 Customers 表
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Phone VARCHAR(20),
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 创建 Products 表
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    UnitPrice DECIMAL(10, 2) NOT NULL CHECK (UnitPrice >= 0),
    StockQuantity INT NOT NULL CHECK (StockQuantity >= 0)
);

-- 创建 Orders 表
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    TotalAmount DECIMAL(10, 2) NOT NULL CHECK (TotalAmount >= 0),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- 创建 OrderDetails 表
CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT NOT NULL CHECK (Quantity > 0),
    UnitPrice DECIMAL(10, 2) NOT NULL CHECK (UnitPrice >= 0),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- 插入 Customers 数据
INSERT INTO Customers (CustomerID, CustomerName, Email, Phone) VALUES
(1, 'Alice Smith', 'alice@example.com', '123-456-7890'),
(2, 'Bob Johnson', 'bob@example.com', '234-567-8901'),
(3, 'Charlie Brown', 'charlie@example.com', '345-678-9012');

-- 插入 Products 数据
INSERT INTO Products (ProductID, ProductName, UnitPrice, StockQuantity) VALUES
(1, 'Laptop', 999.99, 50),
(2, 'Smartphone', 499.99, 100),
(3, 'Headphones', 79.99, 200);

-- 插入 Orders 数据
INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount) VALUES
(101, 1, '2025-09-01', 1079.98),
(102, 1, '2025-09-02', 499.99),
(103, 2, '2025-09-03', 79.99);

-- 插入 OrderDetails 数据
INSERT INTO OrderDetails (OrderDetailID, OrderID, ProductID, Quantity, UnitPrice) VALUES
(1, 101, 1, 1, 999.99),
(2, 101, 3, 1, 79.99),
(3, 102, 2, 1, 499.99),
(4, 103, 3, 1, 79.99);

CREATE VIEW vw_CustomerOrderDetails
AS
SELECT 
    c.CustomerName,
    o.OrderDate,
    od.Quantity,
    p.ProductName
FROM 
    Customers c
    INNER JOIN Orders o ON c.CustomerID = o.CustomerID
    INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
    INNER JOIN Products p ON od.ProductID = p.ProductID;

创建视图（显示四个表字段）

根据你的要求，创建一个视图，显示以下字段：

Customers.CustomerName
Orders.OrderDate
OrderDetails.Quantity
Products.ProductName

CREATE VIEW vw_CustomerOrderDetails

AS

SELECT

c.CustomerName,

o.OrderDate,

od.Quantity,

p.ProductName

FROM

Customers c

INNER JOIN Orders o ON c.CustomerID = o.CustomerID

INNER JOIN OrderDetails od ON o.OrderID = od.OrderID

INNER JOIN Products p ON od.ProductID = p.ProductID;

查询视图

SELECT * FROM vw_CustomerOrderDetails;

视图结果

CustomerName	OrderDate	Quantity	ProductName
Alice Smith	2025-09-01 00:00:00	1	Laptop
Alice Smith	2025-09-01 00:00:00	1	Headphones
Alice Smith	2025-09-02 00:00:00	1	Smartphone
Bob Johnson	2025-09-03 00:00:00	1	Headphones

3. 联接查询示例

以下展示使用 INNER JOIN、LEFT JOIN 和 RIGHT JOIN 的查询，基于上述表结构和数据。

(1) 内联接（INNER JOIN）

只返回所有表中匹配的记录。

SELECT

c.CustomerName,

o.OrderDate,

od.Quantity,

p.ProductName

FROM

Customers c

INNER JOIN Orders o ON c.CustomerID = o.CustomerID

INNER JOIN OrderDetails od ON o.OrderID = o.OrderID

INNER JOIN Products p ON od.ProductID = p.ProductID;

结果：与视图结果相同，仅包含匹配的记录（Charlie 没有订单，因此不显示）。

(2) 左连接（LEFT JOIN）

返回 Customers 表的所有记录，以及匹配的 Orders、OrderDetails 和 Products 记录。如果没有匹配，相关字段显示 NULL。

SELECT

c.CustomerName,

o.OrderDate,

od.Quantity,

p.ProductName

FROM

Customers c

LEFT JOIN Orders o ON c.CustomerID = o.CustomerID

LEFT JOIN OrderDetails od ON o.OrderID = od.OrderID

LEFT JOIN Products p ON od.ProductID = p.ProductID;

结果：

CustomerName	OrderDate	Quantity	ProductName
Alice Smith	2025-09-01 00:00:00	1	Laptop
Alice Smith	2025-09-01 00:00:00	1	Headphones
Alice Smith	2025-09-02 00:00:00	1	Smartphone
Bob Johnson	2025-09-03 00:00:00	1	Headphones
Charlie Brown	NULL	NULL	NULL

说明：Charlie 没有订单，因此 Orders、OrderDetails 和 Products 的字段显示 NULL。

(3) 右连接（RIGHT JOIN）

返回 OrderDetails 表的所有记录，以及匹配的 Orders、Customers 和 Products 记录。如果没有匹配，相关字段显示 NULL。

为了让 RIGHT JOIN 有意义，假设 OrderDetails 表新增一条记录，引用不存在的 OrderID=104 和 ProductID=4：

sql

INSERT INTO OrderDetails (OrderDetailID, OrderID, ProductID, Quantity, UnitPrice)

VALUES (5, 104, 4, 1, 29.99);

RIGHT JOIN 查询：

sql

SELECT

c.CustomerName,

o.OrderDate,

od.Quantity,

p.ProductName

FROM

Customers c

RIGHT JOIN Orders o ON c.CustomerID = o.CustomerID

RIGHT JOIN OrderDetails od ON o.OrderID = od.OrderID

LEFT JOIN Products p ON od.ProductID = p.ProductID;

结果：

CustomerName	OrderDate	Quantity	ProductName
Alice Smith	2025-09-01 00:00:00	1	Laptop
Alice Smith	2025-09-01 00:00:00	1	Headphones
Alice Smith	2025-09-02 00:00:00	1	Smartphone
Bob Johnson	2025-09-03 00:00:00	1	Headphones
NULL	NULL	1	NULL