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

Create a view

Last Updated：2023-07-24 09:52:12 Updated

You can use the CREATE VIEW statement to create a view. 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] [[NO] FORCE] 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.
[NO] FORCE	If you want to create a view regardless of whether the base tables of the view or the referenced object types exist, or the owner of the schema containing the view has privileges on them, specify `FORCE`. If you want to create a view only when the view has base tables and the owner of the schema containing the view has privileges on them, specify `NOFORCE`. The default value is `NOFORCE`.
view_name	The name of the view.
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

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,c_date date);
Query OK, 0 rows affected

Create a view named v to retrieve information from the NAME, C_DATE, AGE, and SEX fields in t1 that match the ID field in t2.

obclient> CREATE VIEW v
                  AS
                  SELECT
                  id1 as id,name, c_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     | NUMBER(38)    | YES  | NULL | NULL    | NULL  |
| NAME   | VARCHAR2(256) | YES  | NULL | NULL    | NULL  |
| C_DATE | DATE          | YES  | NULL | NULL    | NULL  |
| AGE    | NUMBER(38)    | YES  | NULL | NULL    | NULL  |
| SEX    | VARCHAR2(256) | YES  | NULL | NULL    | NULL  |
+--------+---------------+------+-----+---------+-------+
5 rows in set