Create a view

This topic describes how to create a view in the SQL window.

Overview

A view is a virtual table that consists of data in one or more tables. You can use the SELECT statement to query a view. A view itself does not contain data.

You can use views to combine data from different tables in a database into a single virtual table, and thereby convert multi-table queries into a single-table query. You can also use views to present certain data to specific users. These users can view only the data in a specific view.

You can create a view in the following 5 steps:

1. Specify the basic information.

2. Select base tables.

3. Select fields.

4. Verify the SQL statement.

5. Complete the view creation.

In the following example, an employee salary view is created in the SQL window. The salary view consists of the name field in the employee table and the budget and dept_name fields in the consumer table.

Prerequisites

• A super account has been created in the OceanBase Cloud console for connecting to the analytical database.

• You have permission to manage accounts under the analytical cluster instance.

• You have created or joined a workspace.

Procedure

Step 1: Specify the basic information

1. Log in to the OceanBase Cloud console and enter the workspace. Click + next to View to create a view.

2. In the Basic Info section, specify View Name and Check Item.

   • View Name: specifies the name of the view.

   • Check Item: specifies the constraints on the input data. OceanBase Database in the Oracle compatible mode supports only the read-only mode. Default value: None.

   

3. After you specify all the basic information, click OK.

4. After the basic information is specified, you can either click Next: Verify SQL Statement to go to Step 4 and verify the SQL statement, or proceed to Step 2 and select base tables.

Step 2: Select base tables

The list on the left displays available base tables in the hierarchy of schema > database > table/view. You can search for base tables in this list. Select tables or views in this base table list, and click the add icon (>) next to the list to add the selected tables or views to the table operation area on the right.

In the table operation area, you can perform the following operations on a selected base table.

Action	Description
Set an alias	You can set an alias for a selected base table. Click <Alias> next to the table name in the table operation area and enter the alias. This operation is optional.
Set association relationships	When two or more tables exist in the table operation area, you can define an association relationship between the tables. The default association is JOIN. You do not need to set an association relationship for the last table. Click the icon next to Alias and select an association relationship. Supported options: JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN, FULL JOIN, UNION, UNION ALL, INTERSECT, MINUS, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.
Adjust the association order	You can drag the base tables in the table operation area to reorder them. If you drag a table to the bottom of the list, the association relationships of this table are removed.
Delete a base table	You can click the delete icon next to a table in the table operation area to delete it from the table operation area. After you delete a table, its associations with the next table are removed.

After you select and configure the base tables, click OK.

Step 3: Select fields

In the Select Fields section, the list on the left displays the fields of the base tables that you selected in Step 2 in the hierarchy of schema > database > table/view. You can search for fields in this list. Select fields in the field list, and click the add icon (>) next to the list to add the selected fields to the field operation area on the right.

After you select and configure the fields, click OK.

Step 4: Verify the SQL statement

After you complete all the previous steps, click Next: Verify SQL Statement to go to the statement editing page.

On the statement editing page, the view definition statements are generated based on the information specified in the Basic Info, Select Base Tables, and Select Fields sections. When two or more tables are included, you must complete the statement according to the relationship between the tables and logical conditions. After you complete the statements, click Create in the upper-right corner of the page to create the view.

You can edit the SQL statements of the created view on the Create View page. Syntax:

CREATE VIEW view_name AS
SELECT
  column1,
  column2.....
FROM
  table_name
WHERE
  [condition];

Step 5: Complete the view creation

Click Create in the upper-right corner to create the view. After a view is created, you can use the SELECT statement to query it, just like how you query a table.

Syntax:

SELECT
column1,
column2.....
FROM
table_name;

Example:

SELECT * FROM `salary`;

To manage a view, right-click the view name in the left-side navigation pane, and select the required operation from the context menu, which provides the following options: View, Create, Delete, Download, and Copy. For more information, see Functional keys in SQL Console.