Purpose
This statement is used to modify the definition of a view.
Privilege requirements
To execute the ALTER VIEW statement, the current user must have the DROP and CREATE VIEW privileges. For more information about OceanBase Database privileges, see Privilege classification in MySQL-compatible mode.
Syntax
ALTER VIEW view_name [(column_name_list)]
AS select_stmt;
column_name_list:
column_name [, column_name ...]
Parameters
| Parameter | Description |
|---|---|
| view_name | The name of the view. |
| column_name_list | Optional. The list of column names of the view. If you do not specify the column names, the column names retrieved by the SELECT statement will be used as the view column names. The columns retrieved by the SELECT statement can be simple references to table columns, or expressions that use functions, constants, operators, and so on. The view column names are subject to the following restrictions:
|
| column_name | The name of a view column. |
| select_stmt | The query (SELECT) statement used to define the view. It specifies the definition of the view, which can select data from base tables or other views. For more information about the specific structure and options of query statements, see SELECT statement. |
Examples
Modify the definition of the v1 view to select data from the test_tbl2 table.
Create the
test_tbl1table.CREATE TABLE test_tbl1 (col1 INT, col2 INT);Create the
v1view based on thetest_tbl1table.CREATE VIEW v1 AS SELECT * FROM test_tbl1;Create the
test_tbl2table.CREATE TABLE test_tbl2 (col1 INT, col2 INT, col3 INT);Modify the definition of the
v1view to select data from thetest_tbl2table.ALTER VIEW v1 AS SELECT * FROM test_tbl2;View the definition of the
v1view again.SHOW CREATE VIEW v1;The return result is as follows:
+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | View | Create View | character_set_client | collation_connection | +------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | v1 | CREATE VIEW `v1` AS select `db_test`.`test_tbl2`.`col1` AS `col1`,`db_test`.`test_tbl2`.`col2` AS `col2`,`db_test`.`test_tbl2`.`col3` AS `col3` from `db_test`.`test_tbl2` | utf8mb4 | utf8mb4_general_ci | +------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ 1 row in set