Create a view

OceanBase Developer Center (ODC) provides a visual interface for creating views. This topic describes how to create a view using ODC.

Overview

A view is a virtual table. Its structure and content are derived from a SELECT query on one or more tables. The view itself does not store data.

Benefits of views

• Simplify data queries. A view combines data from different tables into a single virtual table. This simplifies complex multi-table queries into simple queries on the view.

• Restrict data access. You can use a view to expose only specific data to users. Users can only see the data that the view is configured to display.

As shown in the preceding figure, creating a view involves the following five steps:

1. Specify basic information.

2. Select base tables.

3. Select fields.

4. Confirm the SQL statement.

5. Finish creating the view.

This topic provides an example of how to create an employee salary view named `salary` in ODC. The view combines the `name` field from the `employee` table with the `budget` and `dept_name` fields from the `consumer` table.

Procedure

Step 1: Specify basic information

In OceanBase Developer Center (ODC), click a connection name to open the connection. In the navigation pane on the left, click the Views tab to view the list of views. In the upper-right corner of the list, click + to create a view. You can also click Create in the top navigation bar to create an object.

In the Basic Information section, enter a View Name and select a Check Option.

• View Name: Specify a name for the view.

• Check Option: Specifies the constraint check on written data. In Oracle mode of OceanBase Database, only the read-only option is supported. The default value is None.

After you specify the basic information, click OK in the lower-left corner of the tab to complete this step.

After you set the basic information, you can click Next: Confirm SQL at the bottom of the page to proceed to Step 4. You can also proceed to Step 2 to select base tables if needed.

Step 2: Select base tables

After specifying the basic information, you can proceed to select base tables. This step is optional.

On the Select Base Tables tab, the list on the left side displays the available base tables in a hierarchical structure of Schema > Database > Table/View. You can search the list. In the base table list, select the target table or view. Then, click the > button next to the list to add the selected table or view to the table operations area on the right.

In the table operations area, you can perform the following operations on the selected base tables:

Item	Description
Set alias	Set an alias for the selected base table. This is optional. In the table operations area, click the <Alias> tag after the table name and enter an alias in the text box.
Set join relationship	If there are two or more tables in the table operations area, you can select the join relationship between them from a drop-down list. The default value is JOIN. The last table does not require a join relationship. Click the drop-down list tag after the alias tag to make a selection. Options include 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 join order	Drag the selected base tables in the table operations area to change their order. Each time you change the order, the join relationship of the new last table is cleared.
Delete a base table	Click the delete button after a table in the table operations area to remove it. Each time you delete a table, the join relationship of the new last table is cleared.

After you select and set the base tables, click OK in the lower-left corner of the tab to complete this step.

Step 3: Select fields

After you select the base tables, you can proceed to select fields. If you skipped the previous step, you cannot select fields.

On the Select Fields tab, the list on the left side displays the fields from the base tables that you selected in Step 2. The fields are shown in a hierarchical structure of Schema > Database > Table/View. You can search the list for fields. In the field list, select the target fields. Then, click the > button next to the list to add the selected fields to the field operations area on the right.

In the field operations area, you can perform the following operations on the selected fields:

Item	Description
Set alias	Set an alias for the selected field. This is optional. In the field operations area, click the <Alias> tag after the field name and enter an alias in the text box.
Adjust order	Drag the selected fields in the field operations area to change their order.
Delete a field	Click the delete button after a field in the field operations area to remove it.
Custom field	Click the + Custom tag in the upper-right corner of the field operations area to add a field. Specify a name and an alias for the new field.

After you select and set the fields, click OK in the lower-left corner of the tab to complete this step.

Step 4: Confirm SQL

After you specify the preceding information, click Next: Confirm SQL to go to the SQL statement editing page.

The SQL statement editing page displays a view definition statement that is generated based on the information that you specified on the Basic Information, Select Base Tables, and Select Fields tabs. If the view includes two or more tables, you must complete the statement by adding join relationships and logical conditions. After you complete the statement, click Create in the upper-right corner to create the view.

You can edit the view creation statement on the SQL Confirmation page. The syntax is as follows:

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

The toolbar on the editing page provides the following functions:

Function	Description
Format	Formats the selected SQL statement or all statements in the current SQL window. This includes actions such as indenting, adding line breaks, and highlighting keywords.
Find/Replace	Enter text in the find box to search the script. Then, enter text in the replace box to replace the search results.
Undo	Reverts the script to the result of the previous operation.
Redo	After performing an Undo operation, re-executes the operation that was undone.
Change Case	Changes the case of the selected text. Options include ALL UPPERCASE, all lowercase, and Capitalize First Letter.
Indent	Adds or removes indents for the selected text. Options are Add Indent and Remove Indent.
Comment	Adds or removes comments for the selected text. Options are Add Comment and Remove Comment.
IN Value Conversion	Converts text such as `A B` to the `('A','B')` format.
Previous	Returns to the Create View page. You can then edit the values on the Basic Information, Select Base Tables, and Select Fields tabs.

Step 5: Finish creating the view

Click Create. After the view is created, you can query it using a `SELECT` statement, just as you would query a table.

Syntax:

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

Example:

SELECT `name`, `budget`, `department_name` FROM `salary`;

Related topics

• Create a table

• Create a function

• Create a stored procedure

• Create a sequence

• Create a package

• Create a trigger

• Create a type

• Create a synonym