Manage a database on the SQLConsole tab - Data Management

This topic describes how to manage a database, such as creating tables in the database and querying and updating data of tables, on the SQLConsole tab in the Data Management (DMS) console.

Prerequisites

You have the permissions to manage a database. For more information, see Manage permissions.

Go to the SQLConsole tab

You can choose one of the following methods to go to the SQLConsole tab.

Go to the SQLConsole tab in the top navigation bar

Log on to the DMS console V5.0.
In the top navigation bar, choose SQL Console > SQL Console.
Note
If you use the DMS console in simple mode, move the pointer over the icon in the upper-left corner of the DMS console and choose All functions > SQL Console > SQL Console.
In the Please select the database first dialog box, enter a keyword to search for a database, select the database instance from the search results, and then click Confirm.

Go to the SQLConsole tab in the database instance list

Log on to the DMS console V5.0.
In the left-side Database instance list, find the database that you want to manage.
Double-click the name of the database to go to the SQLConsole tab.

Create a table

In this example, a table named test_table is created in the MySQL database that is managed in Security Collaboration mode. You can choose one of the following methods to create a table.

Create a table by executing SQL statements

Go to the SQLConsole tab.

Enter the following SQL statement to create a table.

  CREATE TABLE test_table (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    PRIMARY KEY (id)
  );

Click Execute.
After the SQL statement is executed, if the target table name is not shown in the left-side table list, click the icon.

Create a table without executing an SQL statement

Go to the SQLConsole tab.
Right-click the blank area in the visual operation section and select Create Table. For more information, see the GUI of the SQL Console page section of the "Overview" topic.
Configure the parameters on the Basic Info, Column Info, Index Info, and Foreign Key Info tabs based on your business requirements.
Click Modify in the lower part of the page. Then, click Execute.

FAQ about creating a table

Q: If the system fails to execute the CREATE TABLE statement and prompts that the matching security rule prohibits the execution of the CREATE TABLE statement, how can I resolve these issues?
A: This prompt appears only for databases for which Security Collaboration is enabled. Contact the administrator or database administrator (DBA) to add the CREATE TABLE command to the security rules associated with the instance.
1. In the top navigation bar, click Security and Specifications. In the left-side navigation pane, click Security Rules.
  Note
  If you use the DMS console in simple mode, move the pointer over the icon in the upper-left corner of the DMS console and choose All functions > Security and Specifications > Security Rules.
2. Find the rule set that you want to manage and click Edit in the Actions column.
3. Click SQL Correct in the left-side pane.
4. Select SQL execution rules from Checkpoints.
5. Select All DDL can execute directly in SQLConsole and click Edit.
6. Add the prompted SQL type (such as CREATE TABLE) to the domain-specific language (DSL) statement for the security rule, and then click Submit.
7. After you submit the SQL type, enable All DDL can execute directly in SQLConsole and disable the rule that requires DDL operations to be executed by submitting tickets.
For more information, see Data change.

Query data

Go to the SQLConsole tab.
On the SQLConsole tab, enter an SQL query statement in the SQL editor or double-click a table in the table list to generate an SQL query statement, and then click Execute in the top toolbar of the SQL editor.
After you query data, you can modify the result set on the Execution tab in the execution result section. For more information, see Manage a database on the SQLConsole tab.

Modify data

Go to the SQLConsole tab.
On the SQLConsole tab, enter an UPDATE statement in the SQL editor and click Execute on the top of the SQL editor.
In the Execution Confirmation message, click OK.

Manage frequently used SQL statements

After you go to the SQLConsole tab, you can add, use and manage your frequently used SQL statements.

Add frequently used SQL statements

On the SQLConsole tab, enter an SQL statement in the SQL editor and select the entire statement.
Choose My SQL > Add.
In the Add to Saved SQL dialog box, enter a name for the SQL statement in the Title field, configure the Scope parameter, and then click OK.

Use added SQL statements

Choose My SQL > Select in the top toolbar of the SQL editor, and click the title of the SQL statement that you want to use. The SQL statement is automatically inserted into the SQL editor.

Edit added SQL statements

Choose My SQL > Management.
Edit, delete and use the added SQL statements.
Note
You can configure the Title, Scope and SQL parameters.

View data in the form of a visual table

Go to the SQLConsole tab.
In the visual operation section of the SQLConsole tab, right-click the table that you want to view and select Open Table.
On the Table tab, view the data of the table in the form of a visual table.
In the upper-right corner of the tab, click the icon, select one or more columns that you want to view, and then click OK. The Table:Table name tab displays only the selected columns.

Manage a result set

Query a result set

After an SQL statement is executed, enter a keyword in the search box in the upper-right corner of the Execution tab. In this case, the result set is queried in fuzzy match mode, and the results that contain the keyword are highlighted. You can also select Filter to display only the results that contain the keyword.

Export one or more result sets

On the Execution tab, move the pointer over Export File and select an option. You can export the current result set in multiple types of files, such as Excel and TXT. If you export all result sets, each result set is exported in an Excel file.

Modify a result set

On the Execution tab, click Enable editing. Then, you can insert data into or delete data from the current result set. After you modify the result set, click Submit Change.

Note

If NOT NULL primary keys or unique keys do not exist in the result set, you cannot modify the result set. In this case, move the pointer over the edit-lock icon to view the cause and solution.

Insert data: You can add an empty row to the last row of the result set and enter the data to be inserted in the empty row.
Modify data: You can click a cell to modify data in the result set. However, you cannot modify sensitive fields that are masked. After the data in a cell is modified, a red mark appears in the upper-left corner of the cell.
Delete data: Select a row of data and click Delete. After the row of data is deleted, the background of the row is dimmed.
Note
The delete operation can be revoked.
Submit a change: After you insert, modify, or delete data, you can submit the change. The data change takes effect only after you click Submit Change and successfully execute the SQL statement that is used to generate the current result set in the SQL editor. Execute the SQL statement again and view the modified result set.

Copy data from a result set

This section describes how to copy data from a result set on the Windows operating system.

To copy data from a single cell, select the cell and click the icon in the upper-right corner of the cell or press Ctrl+C.
To copy data from a section, perform the following operations: Select the upper-left cell of the section to be copied, press the Shift key, and then click the lower-right cell of the section to be copied. In this case, the section to be copied is highlighted. This indicates that all cells in the section are selected. Then, press Ctrl+C.
To copy multiple columns of data from the result set, perform the following operations:
- To copy data in consecutive columns, click the name of the first column in the section to be copied, press the Shift key, and then click the name of the last column in the section to be copied. Then, press Ctrl+C.
  For example, if you want to copy data from the first to fifth columns of the test_table table, click the name of the first column, press the Shift key, and then click the name of the fifth column. In this case, the data in the first to fifth columns is highlighted. This indicates that the data in these columns is selected. Then, press Ctrl+C to copy data.
- To copy data in non-consecutive columns, click the name of a column to be copied, press the Ctrl key, and then click the names of one or more columns to be copied. Then, press Ctrl+C.
  For example, if you want to copy data from the first, third, and fifth columns of the test_table table, click the name of a column, press the Ctrl key, and then click the names of the other two columns. In this case, the data in the first, third, and fifth columns is highlighted. This indicates that the data in these columns is selected. Then, press Ctrl+C to copy data.
To copy multiple rows of data from the result set, perform the following operations:
- To copy data in consecutive rows, click the number of the first row in the section to be copied, press the Shift key, and then click the number of the last row in the section to be copied. Then, press Ctrl+C.
  For example, if you want to copy data from the first to fifth rows of the test_table table, click the number of the first row, press the Shift key, and then click the number of the fifth row. In this case, the data in the first to fifth rows is highlighted. This indicates that the data in these rows is selected. Then, press Ctrl+C to copy data.
- To copy data in non-consecutive rows, click the number of a row to be copied, press the Ctrl key, and then click the numbers of one or more rows to be copied. Then, press Ctrl+C.
  For example, if you want to copy data from the first, third, and fifth rows of the test_table table, click the number of a row, press the Ctrl key, and then click the numbers of the other two rows. In this case, the data in the first, third, and fifth rows is highlighted. This indicates that the data in these rows is selected. Then, press Ctrl+C to copy data.