This topic describes how to use DDL statements to create, delete, and rename a view.
Create a view
CREATE [OR REPLACE] VIEW [IF NOT EXISTS] view_name [(col_name [COMMENT col_comment], ...)] [COMMENT view_comment] [AS select_statement]
This syntax is used to create a view.
- To create a view, you must have read permissions on the table referenced by the view.
- A view can reference other views but cannot reference itself. Circular reference is not supported.
- Writing data into a view is not allowed. For example, you cannot manage a view by
- After a view is created, it may be inaccessible if the referenced table is altered. For example, a referenced table is deleted. You must maintain the mappings between referenced tables and views.
- view_name: the name of the created view.
- IF NOT EXISTS: If
IF NOT EXISTSis not specified and the view already exists, the execution of the
CREATE VIEWstatement causes exceptions. In this case, you can execute
CREATE OR REPLACE VIEWto recreate the view. The permissions on the view remain unchanged after the view is recreated.
- col_name: the name of the column contained in the view.
- view_comment: the comment of the view.
- select_statement: the query statement, which is the data source of the view. A view can contain only
-- Create a view named sale_detail_view. create view if not exists sale_detail_view (store_name, customer_id, price, sale_date, region) comment 'a view for table sale_detail' as select * from sale_detail;
Rename a view
ALTER VIEW view_name RENAME TO new_view_name;
- view_name: the name of the view you want to rename.
- new_view_name: the new name of the view. If a view with the same name already exists, an error is returned.
-- Rename sale_detail_view to market. alter view sale_detail_view rename to market;
Delete a view
DROP VIEW [IF EXISTS] view_name;
- view_name: the name of the view you want to delete.
- IF NOT EXISTS: If the view does not exist and
IF NOT EXISTSis not specified, an error is returned.
-- Delete sale_detail_view. DROP VIEW IF EXISTS sale_detail_view;