菜鸟教程
教程 mariadb tutorial

派生表与视图

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. 优势

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

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

相关内容