# 派生表与视图

URL: https://caijiao.org/mariadb/program/join/view
Source: docs/mariadb/program/join/view.md
Description: 本节介绍 MariaDB 中派生表和视图的概念与用法，帮助理解临时表和虚拟表的创建及其在复杂查询中的应用。

## 1. 派生表（Derived Table）

派生表是查询语句中嵌套的临时表，存在于单条 SQL 语句执行期间，常用于复杂查询的简化。

### 语法示例

```sql
SELECT dt.customer_id, dt.total_orders
FROM (
    SELECT customer_id, COUNT(*) AS total_orders
    FROM orders
    GROUP BY customer_id
) AS dt
WHERE dt.total_orders > 5;
````

## 2. 视图（View）

视图是数据库中保存的虚拟表，其内容由查询语句定义，便于复用复杂查询。

### 创建视图

```sql
CREATE VIEW customer_orders AS
SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id;
```

### 使用视图

```sql
SELECT * FROM customer_orders WHERE total_orders > 5;
```

### 更新视图

```sql
ALTER VIEW customer_orders AS
SELECT customer_id, COUNT(*) AS total_orders, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id;
```

## 3. 优势

* 派生表简化复杂查询，避免重复代码。
* 视图提高代码复用性，简化业务逻辑。
* 视图可控制数据访问权限。

---

掌握派生表与视图，有助于构建清晰高效的数据库查询结构。