创建视图

描述

视图基于 SQL 查询的结果集。 CREATE VIEW 构造了一个没有物理数据的虚拟表,因此其他操作如 ALTER VIEW DROP VIEW 仅仅改变元数据。

语法

CREATE [ OR REPLACE ] [ [ GLOBAL ] TEMPORARY ] VIEW [ IF NOT EXISTS ] view_identifier
create_view_clauses AS query

参数

示例

-- 创建或替换带注释的 `experienced_employee` 视图。
CREATE OR REPLACE VIEW experienced_employee
(ID COMMENT '唯一识别号', Name)
COMMENT '经验丰富员工的视图'
AS SELECT id, name FROM all_employee
WHERE working_years > 5;
-- 如果不存在则创建全局临时视图 `subscribed_movies`。
CREATE GLOBAL TEMPORARY VIEW IF NOT EXISTS subscribed_movies
AS SELECT mo.member_id, mb.full_name, mo.movie_title
FROM movies AS mo INNER JOIN members AS mb
ON mo.member_id = mb.id;