This topic describes how to create a normal view.
Syntax
You can use the CREATE VIEW statement to create a view.
create_view_stmt:
CREATE [OR REPLACE] VIEW view_name [(column_name_list)] AS select_stmt;
column_name_list:
column_name [, column_name ...]
Parameters
| Parameter | Description |
|---|---|
| OR REPLACE | Specifies to re-create a view that already exists by using a new definition. |
| view_name | Specifies the name of the view. The name must be unique in the database and cannot be the same as that of another table or view. |
| select_stmt | Specifies the SELECT statement used to create the view, which defines the view. The statement can query multiple base tables or source 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
Create an associated query view named v based on the t1 and t2 tables.
Create a table named
t1.obclient> CREATE TABLE t1 (id1 int,name varchar(256),sex varchar(256)); Query OK, 0 rows affectedCreate a LIST COLUMNS-partitioned table named
t2.obclient> CREATE TABLE t2 (id2 int,age int,date date); Query OK, 0 rows affectedCreate a view named
v.obclient> CREATE VIEW v AS SELECT id1 as id,name, date, age, sex FROM t1 a, t2 b WHERE a.id1 = b.id2; Query OK, 0 rows affectedShow the description 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 set