CREATE VIEW

This command is used to create a new view in the current or specified SCHEMA based on the query results of one or more existing tables. A view is a virtual table that provides access to the query results without directly manipulating the underlying data tables.

Syntax


CREATE [ OR REPLACE ] VIEW [ IF NOT EXISTS ] [schema_name.]view_name
    [ (column_name conmmet'' [, ...]) ]
    [ COMMENT 'comment' ]
    AS query;

Parameter Description

OR REPLACE: This option is used to replace any existing view with the same name (if it exists). Note that it cannot be used simultaneously with the IF NOT EXISTS option. Using this option is equivalent to performing a DROP VIEW operation on the existing view and then creating a new view with the same name.
column_name: Specifies the column names in the new view. If you need to assign aliases or add comments to the columns in the new view, you can use this parameter.
COMMENT: Adds comment information to the newly created view. This helps other users understand the purpose and structure of the view.
query: The SQL query statement used to generate the content of the view.

Usage Example

Create a view named myview that includes columns col1 and col2 from the mytable table:


CREATE VIEW myview AS
SELECT col1, col2 FROM mytable;

Create a view named myview and specify column aliases and comments for it:


CREATE VIEW myview (col1_alias comment 'col1_alias', col2_alias comment 'col2_alias')
COMMENT 'This is my view with aliased columns'
AS
SELECT col1 AS col1_alias, col2 AS col2_alias FROM mytable;

Create a view named myview that only includes records that meet specific conditions:


CREATE VIEW myview AS
SELECT col1, col2, col3
FROM mytable
WHERE col1 > 100;

Use the OR REPLACE option to replace an existing view with the same name:


   ```SQL
   CREATE OR REPLACE VIEW myview AS
   SELECT col1, col2, col4
   FROM mytable;

Create a view in the specified SCHEMA:


   ```SQL
   CREATE VIEW schema_name.myview AS
   SELECT col1, col2
   FROM schema_name.mytable;