CREATE VIEWCREATE VIEW

根據 SQL 查詢的結果集，構造沒有實體資料的虛擬資料表。Constructs a virtual table that has no physical data based on the result-set of a SQL query. ALTER VIEW 而且 DROP VIEW 只會變更中繼資料。ALTER VIEW and DROP VIEW only change metadata.

語法Syntax

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

參數Parameters

  • 或取代OR REPLACE

    如果已經存在相同名稱的視圖，則會予以取代。If a view of same name already exists, it is replaced.

  • GLOBAL.ASAX臨時[ GLOBAL ] TEMPORARY

    暫時視圖會以會話為範圍，並在會話結束時捨棄，因為它會略過保存基礎中繼存放區中的定義（如果有的話）。TEMPORARY views are session-scoped and is dropped when session ends because it skips persisting the definition in the underlying metastore, if any. 全域臨時視圖系結至系統保留的暫存資料庫 global_temp 。GLOBAL TEMPORARY views are tied to a system preserved temporary database global_temp.

  • 如果不存在IF NOT EXISTS

    如果不存在，則建立一個視圖。Creates a view if it does not exist.

  • view_identifierview_identifier

    可選擇性地使用資料庫名稱限定的視圖名稱。A view name, optionally qualified with a database name.

    語法：[database_name.] view_nameSyntax: [database_name.] view_name

  • create_view_clausescreate_view_clauses

    這些子句是選擇性的，且不區分順序。These clauses are optional and order insensitive. 它可以是下列格式。It can be of following formats.

    • [ ( column_name [ COMMENT column_comment ], ... ) ] 指定資料行層級批註。[ ( column_name [ COMMENT column_comment ], ... ) ] to specify column-level comments.
    • [ COMMENT view_comment ] 指定視圖層級批註。[ COMMENT view_comment ] to specify view-level comments.
    • [ TBLPROPERTIES ( property_name = property_value [ , ... ] ) ] 加入中繼資料索引鍵/值組。[ TBLPROPERTIES ( property_name = property_value [ , ... ] ) ] to add metadata key-value pairs.

  • 查詢 從基表或其他視圖中建立視圖的 SELECT 語句。query A SELECT statement that constructs the view from base tables or other views.

範例Examples

-- Create or replace view for `experienced_employee` with comments.
CREATE OR REPLACE VIEW experienced_employee
    (ID COMMENT 'Unique identification number', Name)
    COMMENT 'View for experienced employees'
    AS SELECT id, name FROM all_employee
        WHERE working_years > 5;

-- Create a global temporary view `subscribed_movies` if it does not exist.
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;