Write and execute SQL statements

Background information

The SQL window of OceanBase Developer Center (ODC) provides a GUI for data processing. You can sort, filter, add, edit, and delete data, and execute database objects in the SQL window.

This topic describes how to write and execute an SQL statement in the SQL window.

Write SQL statements

Example: In the SQL window, create a table named employee in the odc_test database in the odc_4.2.0 project.

1. In the odc_4.2.0 project, click Log on to Database to go to the SQL window.

   

2. In the SQL window, write an SQL statement to create a table named employee.

   

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

   The syntax is as follows:

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

   The following table describes the fields in the syntax.

   Field	Description
   CREATE TABLE	The keyword that indicates the creation of a table with a specified name. You must have the permission 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 each column in the table. Separate the tuples of multiple columns with commas (,).

   The following table describes the icons provided in the toolbar of the SQL window.

   Icon	Description
   SQL Check	Click this icon to check the syntax of the SQL statement.
   Plan	Click this icon to view the estimated execution plan (execution result of EXPLAIN) for the SQL statement that is selected or on which the pointer hovers. The displayed execution data may be slightly different from the data generated after statement execution. You can use this feature to evaluate an SQL statement. Click the Plan icon. On the Plan Details tab, click View Formatting Info to switch to the formatted view.
   Find and Replace	Click this icon and enter text in the search field to find the specific content and enter text in the replacement field to replace the content found.
   Undo	Click this icon to undo the previous operation.
   Redo	Click this icon to reverse an Undo operation.
   Format	Click this icon to apply formatting, such as indentation, line break, and keyword highlighting, to the selected SQL statements or all the SQL statements in the current SQL window.
   IN Value Conversion	Click this icon to convert the copied rows or columns into the specified format during queries. After you paste the copied data to the SQL editing area, select the copied data and click IN Value Conversion to convert it into the in('A','B') format. Column values are separated with line breaks. Row values are separated with spaces or tabs.
   Case Sensitivity	The system supports three capitalization formats: All Caps, All Lowercase, and Capitalize First Letter. Click this icon to convert the selected statements in the script to the corresponding capitalization format.
   Indent	You can add indents to or delete indents from the statements that you selected.
   Comment	You can click Add Comment to convert the statements that you select into comments or click Delete Comment to convert comments to SQL statements.
   Settings	Delimiter: Select the sign that you want to use as the delimiter from the drop-down list. Five types of delimiters are supported: semi-colons (;), slashes (/), double slashes (//), dollar signs ($), and double dollar signs ($$). Query Result Limit: Set the maximum number of lines that a query can return. Default value: 1000. Obtain Column Information of Result Set: Choose whether to obtain information about columns in the result set before SQL execution. If the table contains a large number of columns, we recommend that you disable this option. After you disable this option, column comments and editable columns are not queried, which shortens the SQL execution time. Session Variable: You can edit, refresh, and search for variables.

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

Execute SQL statements

1. 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	Description
   Press F8	Click this icon 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.
   Abort	Click this icon to abort the statements being executed.

   

2. On the result tab, view the execution status and result.

   

View data

1. In the left-side navigation pane of the SQL window, click and check whether the employee table is successfully created in the odc_test database in the odc_4.2.0 project.

   

2. Right-click the employee table or write a SELECT statement in the SQL window to view the data in the table.

   

SQL scripts

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 for creating the employee table.

1. In the SQL window, click the Save Script icon in the toolbar.

   

2. Specify the script name and click OK.

   

Manage scripts

1. In the left-side navigation pane of the SQL window, click to go to the script management page.

2. 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.

1. In the left-side navigation pane of the SQL window, click . On the Snippets tab, click the plus sign (+) to create a snippet.

   

2. On the Create Snippet page, specify the snippet information and click OK.

   

Manage snippets

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

References

• Perform PL compilation and debugging

• Use the command-line window

• Edit and export the result set of an SQL statement

• Analyze an SQL statement

• Generate test data

• Database change management

• Manage partitions

• Synchronization of shadow tables

• Lock-free schema changes