派生表与视图

1. 派生表（Derived Table）

派生表是查询语句中嵌套的临时表，存在于单条 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）

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

创建视图

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

使用视图

SELECT * FROM customer_orders WHERE total_orders > 5;

更新视图

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

3. 优势

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

---

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