Create a view|V4.0.0|OceanBase Database| docs|Distributed Database

Create a view

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

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 affected

Create a LIST COLUMNS-partitioned table named t2.

obclient> CREATE TABLE t2 (id2 int,age int,date date);
Query OK, 0 rows affected

Create 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 affected

Show the description 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