CREATE VIEW¶

CREATE OR ALTER VIEW¶

如果视图尚不存在，则创建新视图；或者更新现有视图的属性以匹配语句中定义的属性。CREATE OR ALTER VIEW 语句遵循 CREATE VIEW 语句的语法规则，并具有与 ALTER VIEW 语句相同的限制。

CREATE OR ALTER VIEW 命令不支持在视图创建后更改视图定义。此限制继承自 ALTER VIEW 命令。

支持以下修改：

• 转换为安全视图（或从安全视图恢复）。

• 添加、覆盖、移除视图或视图列的注释。

• 启用或禁用视图的变更跟踪。

有关更多信息，请参阅 CREATE OR ALTER VIEW 使用说明 和 CREATE OR ALTER <对象>。

CREATE OR ALTER [ SECURE ] [ { [ { LOCAL | GLOBAL } ] TEMP | TEMPORARY | VOLATILE } ] [ RECURSIVE ] VIEW <name>
  [ ( <column_list> ) ]
  [ CHANGE_TRACKING =  { TRUE | FALSE } ]
  [ COMMENT = '<string_literal>' ]
  AS <select_statement>

基本示例¶

在当前架构中创建一个带有注释的视图，该视图从表中选择所有行：

CREATE VIEW myview COMMENT='Test view' AS SELECT col1, col2 FROM mytable;

SHOW VIEWS;

+---------------------------------+-------------------+----------+---------------+-------------+----------+-----------+--------------------------------------------------------------------------+
| created_on                      | name              | reserved | database_name | schema_name | owner    | comment   | text                                                                     |
|---------------------------------+-------------------+----------+---------------+-------------+----------+-----------+--------------------------------------------------------------------------|
| Thu, 19 Jan 2017 15:00:37 -0800 | MYVIEW            |          | MYTEST1       | PUBLIC      | SYSADMIN | Test view | CREATE VIEW myview COMMENT='Test view' AS SELECT col1, col2 FROM mytable |
+---------------------------------+-------------------+----------+---------------+-------------+----------+-----------+--------------------------------------------------------------------------+

Copy

下一个示例与上一个示例相同，只是视图是安全的：

CREATE OR REPLACE SECURE VIEW myview COMMENT='Test secure view' AS SELECT col1, col2 FROM mytable;

SELECT is_secure FROM information_schema.views WHERE table_name = 'MYVIEW';

Copy

下面显示了创建递归视图的两种方法：

第一，创建并加载表：

CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);

Copy

INSERT INTO employees (title, employee_ID, manager_ID) VALUES
    ('President', 1, NULL),  -- The President has no manager.
        ('Vice President Engineering', 10, 1),
            ('Programmer', 100, 10),
            ('QA Engineer', 101, 10),
        ('Vice President HR', 20, 1),
            ('Health Insurance Analyst', 200, 20);

Copy

使用递归 CTE 创建视图，然后查询该视图。

CREATE VIEW employee_hierarchy (title, employee_ID, manager_ID, "MGR_EMP_ID (SHOULD BE SAME)", "MGR TITLE") AS (
   WITH RECURSIVE employee_hierarchy_cte (title, employee_ID, manager_ID, "MGR_EMP_ID (SHOULD BE SAME)", "MGR TITLE") AS (
      -- Start at the top of the hierarchy ...
      SELECT title, employee_ID, manager_ID, NULL AS "MGR_EMP_ID (SHOULD BE SAME)", 'President' AS "MGR TITLE"
        FROM employees
        WHERE title = 'President'
      UNION ALL
      -- ... and work our way down one level at a time.
      SELECT employees.title, 
             employees.employee_ID, 
             employees.manager_ID, 
             employee_hierarchy_cte.employee_id AS "MGR_EMP_ID (SHOULD BE SAME)", 
             employee_hierarchy_cte.title AS "MGR TITLE"
        FROM employees INNER JOIN employee_hierarchy_cte
       WHERE employee_hierarchy_cte.employee_ID = employees.manager_ID
   )
   SELECT * 
      FROM employee_hierarchy_cte
);

Copy

SELECT * 
    FROM employee_hierarchy 
    ORDER BY employee_ID;
+----------------------------+-------------+------------+-----------------------------+----------------------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MGR_EMP_ID (SHOULD BE SAME) | MGR TITLE                  |
|----------------------------+-------------+------------+-----------------------------+----------------------------|
| President                  |           1 |       NULL |                        NULL | President                  |
| Vice President Engineering |          10 |          1 |                           1 | President                  |
| Vice President HR          |          20 |          1 |                           1 | President                  |
| Programmer                 |         100 |         10 |                          10 | Vice President Engineering |
| QA Engineer                |         101 |         10 |                          10 | Vice President Engineering |
| Health Insurance Analyst   |         200 |         20 |                          20 | Vice President HR          |
+----------------------------+-------------+------------+-----------------------------+----------------------------+

Copy

使用关键字 RECURSIVE 创建视图，然后查询该视图。

CREATE RECURSIVE VIEW employee_hierarchy_02 (title, employee_ID, manager_ID, "MGR_EMP_ID (SHOULD BE SAME)", "MGR TITLE") AS (
      -- Start at the top of the hierarchy ...
      SELECT title, employee_ID, manager_ID, NULL AS "MGR_EMP_ID (SHOULD BE SAME)", 'President' AS "MGR TITLE"
        FROM employees
        WHERE title = 'President'
      UNION ALL
      -- ... and work our way down one level at a time.
      SELECT employees.title, 
             employees.employee_ID, 
             employees.manager_ID, 
             employee_hierarchy_02.employee_id AS "MGR_EMP_ID (SHOULD BE SAME)", 
             employee_hierarchy_02.title AS "MGR TITLE"
        FROM employees INNER JOIN employee_hierarchy_02
        WHERE employee_hierarchy_02.employee_ID = employees.manager_ID
);

Copy

SELECT * 
    FROM employee_hierarchy_02 
    ORDER BY employee_ID;
+----------------------------+-------------+------------+-----------------------------+----------------------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MGR_EMP_ID (SHOULD BE SAME) | MGR TITLE                  |
|----------------------------+-------------+------------+-----------------------------+----------------------------|
| President                  |           1 |       NULL |                        NULL | President                  |
| Vice President Engineering |          10 |          1 |                           1 | President                  |
| Vice President HR          |          20 |          1 |                           1 | President                  |
| Programmer                 |         100 |         10 |                          10 | Vice President Engineering |
| QA Engineer                |         101 |         10 |                          10 | Vice President Engineering |
| Health Insurance Analyst   |         200 |         20 |                          20 | Vice President HR          |
+----------------------------+-------------+------------+-----------------------------+----------------------------+

Copy

CREATE OR ALTER VIEW 示例¶

CREATE OR ALTER TABLE my_table(a INT);

CREATE OR ALTER VIEW v2(one)
  AS SELECT a FROM my_table;

CREATE OR ALTER VIEW v2(one)
  COMMENT = 'fff'
  CHANGE_TRACKING = true
  AS SELECT a FROM my_table;

CREATE OR ALTER VIEW v2(one COMMENT 'bar')
  COMMENT = 'foo'
  AS SELECT a FROM my_table;

CREATE OR ALTER VIEW v2(one COMMENT 'bar')
  CHANGE_TRACKING = true
  AS SELECT a FROM my_table;