Modify a view|V4.0.0| docs|Distributed Database

Modify a view

Last Updated：2023-07-21 09:11:01 Updated

This topic describes how to modify a normal view.

Syntax

ALTER VIEW view_name [(column_name_list)] AS select_stmt;

column_name_list:
    column_name [, column_name ...]

Parameters

Parameter Description

select_stmt A SELECT statement that defines a view. You can use this statement to query base tables or other views.

column_name_list The column names in the view, which must be unique, like those in a base table. By default, the column names retrieved by the SELECT statement are used as the column names in the view. You can also use the optional column_name_list clause to define column names for a view. Separate multiple column names with commas (,). The number of column names in column_name_list must be equal to the number of columns retrieved by the SELECT statement. The SELECT statement can directly reference the columns of a table. You can also use functions, constants, and operators in the statement to retrieve columns.

Parameter	Description
select_stmt	A `SELECT` statement that defines a view. You can use this statement to query base tables or other views.
column_name_list	The column names in the view, which must be unique, like those in a base table. By default, the column names retrieved by the `SELECT` statement are used as the column names in the view. You can also use the optional `column_name_list` clause to define column names for a view. Separate multiple column names with commas (,). The number of column names in `column_name_list` must be equal to the number of columns retrieved by the `SELECT` statement. The `SELECT` statement can directly reference the columns of a table. You can also use functions, constants, and operators in the statement to retrieve columns.

Examples

View the structure of the v view.

obclient> DESC v;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(256) | YES  |     | NULL    |       |
| date  | date         | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
| sex   | varchar(256) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set

View the creation statement of the v view.

obclient> show create table v\G
*************************** 1. row ***************************
                View: v
         Create View: CREATE VIEW `v` AS select `a`.`id1` AS `id`,`a`.`name` AS `name`,`b`.`date` AS `date`,`b`.`age` AS `age`,`a`.`sex` AS `sex` from `ny`.`t1` `a`,`ny`.`t2` `b` where (`a`.`id1` = `b`.`id2`)
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
1 row in set

View the structure of the t3 table.

obclient> DESC t3;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | YES  |     | NULL    |       |
| name  | varchar(256) | YES  |     | NULL    |       |
| date  | date         | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
| sex   | varchar(128) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set

Change the definition of the v view to the definition of the t3 table.

obclient> ALTER VIEW v AS SELECT * FROM t3;
Query OK, 0 rows affected

View the structure of the v view again.

obclient> desc v;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | YES  |     | NULL    |       |
| name  | varchar(256) | YES  |     | NULL    |       |
| date  | date         | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
| sex   | varchar(128) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set