Edit and execute SQL statements - ApsaraDB for OceanBase

This topic introduces how to edit and execute SQL statements in the SQL window.

Background information

The SQL window is the user interface for handling data. This topic aims to explain how to edit and execute SQL statements in the SQL window.

Using the creation of a table as an example, this topic demonstrates how to create a table named employee in the odc_test database within the SQL window.

Note

The data used in this topic are examples. You can replace them with actual data as needed.

Edit SQL statements

Note

If you do not have permission to the table, you can query/export/update the table through Table Permissions Apply in Tickets.

In the odc_4.2.0 project, click Log On to Database to go to the SQL development window.

In the SQL window, edit an SQL statement to create a table namedemployee.

CREATE TABLE odc_test.employee (
emp_no int(120) COMMENT 'Employee ID' NOT NULL,
birthday date COMMENT 'Birthday' NULL,
name varchar(120) COMMENT 'Employee name' NULL,
CONSTRAINT cons_employee_empno PRIMARY KEY (emp_no)) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;

The syntax of this statement is as follows:

CREATE TABLE table_name (column_name column_type, column_name column_type,.......);

The parameters are described in the following table.

Parameter	Description
CREATE TABLE	The key word that indicates the creation of a table with a specified name. You must have the privileges to create tables.
table_name	The name of the table. The table name must conform to the identifier naming rules.
column_name column_type	The name and data type of the column in the table. To create multiple columns, separate the tuple with commas (,).

In the SQL window shown in the preceding figure, clickto switch to another database.

Execute SQL statements

After you complete writing SQL statements, click Press F8 in the toolbar to run all SQL statements in the current SQL window. The following table describes the icons related to SQL statement execution.

Icon	Note
Press F8	Click this icon to run all SQL statements in the current SQL window. You can also press F8 to run all SQL statements in the current SQL window.
Run Current Statement (F9)	Click this icon to run all selected SQL statements or the statement in the line where the pointer hovers. You can also press F9, or press Ctrl + Enter in Windows or Command + Enter in macOS to run the current statement.
Abort	Click this icon to abort the statements being executed.

On the Results tab, you can view the execution status and result.

View data

In the left-side navigation pane of the SQL window, clickand check whether the employee table is successfully created in the odc_test database in the odc_4.2.0 project.
Right-click the employee table or edit the SELECT statement in the SQL window to view the data in the table.
Click to manually synchronize metadata such as table columns in the current project or data source.
Right-click the name of a database and selectSync Metadatato manually synchronize metadata such as table columns in the current database.

SQL ascripts

In ODC, you can save edited scripts in the SQL window or anonymous block window and manage saved scripts on the Script tab.

Save a script

In the SQL window, save the script that creates the employee table.

Parameter	Example
Table Name	employee
Script Name	create_employee

In the SQL window, click the Save Script icon in the toolbar.
Specify the name of the script and click OK.

Manage scripts

In the left-side navigation pane of the SQL window, clickto go to the script management page.
On the Script tab, you can edit, download, delete, and refresh a saved script, copy the path of a script, and upload a script from your local disk.
Note
- You can click the name of a script to directly reference its content in the SQL window.
- You can also copy the path of a script to reference its content in the command-line window.

Snippets

ODC allows you to create snippets. You can query created snippets to learn the usage details about relevant statements during development.

Create a snippet

On the Snippets tab, you can save the snippet for creating the employee table.

Parameter	Example
Table Name	employee
Snippet Name	create_table

In the left-side navigation pane of the SQL window, click. On the Snippets tab, click the plus sign (+) to create a snippet.
On the Create Snippet page, specify the snippet information and click OK.
Note
In ODC V4.2.3 and later, the maximum snippet length allowed is changed to 64 KB.

Manage snippets

On the Snippets page, you can copy, edit, delete, and refresh a saved snippet.

ApsaraDB for OceanBase:Edit and execute SQL statements