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. |
Example
View the structure of the
vview.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 setView the creation statement of the
vview.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 setView the structure of the
t3table.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 setChange the definition of the
vview to the definition of thet3table.obclient> ALTER VIEW v AS SELECT * FROM t3; Query OK, 0 rows affectedView the structure of the
vview 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