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 mode.
Syntax
ALTER VIEW view_name [(column_name_list)]
AS select_stmt;
column_name_list:
column_name [, column_name ...]
Parameters
| Parameter | Description |
|---|---|
| view_name | Specifies the name of the view. |
| column_name_list | Optional. Specifies the list of column names for the view. If no column names are specified, 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 involving functions, constants, operators, etc. The following restrictions apply to view column names:
|
| column_name | The name of the view column. |
| select_stmt | The query (SELECT) statement used to define the view. It provides the definition of the view, which can select data from base tables or other views. For more information about the structure and options of query statements, see SELECT statement. |
Examples
Modify the definition of the view v1 to select data from test_tbl2.
Create the
test_tbl1table.CREATE TABLE test_tbl1 (col1 INT, col2 INT);Create the view
v1based 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 view
v1to select data fromtest_tbl2.ALTER VIEW v1 AS SELECT * FROM test_tbl2;View the definition of the view
v1again.SHOW CREATE VIEW v1;The returned 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