How do I manage a database on the SQLConsole tab? - Data Management

After you log on to a database in Data Management (DMS), you can manage the database on the SQLConsole tab in the DMS console, such as creating tables in the database and querying and modifying the table data. This topic describes how to manage a database on the SQLConsole tab. In this example, a database that runs on an ApsaraDB RDS for MySQL instance is used.

Go to the SQLConsole tab

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

Go to the SQLConsole tab from 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 console and choose All Features > SQL Console > SQL Console.
In the Select Database 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 from the database instance list

Log on to the DMS console V5.0.
In the Database Instances section on the left of the page, 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 use 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 in the top toolbar of the SQL editor.
After the SQL statement is executed, if the table name is not displayed in the left-side table list, click the icon.

Create a table in a visualized manner

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 a security rule associated with the database instance prohibits the execution of the CREATE TABLE statement, how do I resolve these issues?
A: This prompt appears only for database instances that are managed in Security Collaboration mode. 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, choose Security and Specifications > Security Rules.
  Note
  If you use the DMS console in simple mode, move the pointer over the icon in the upper-left corner 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. In the left-side navigation pane, click SQL Correct.
4. Set the Checkpoints parameter to SQL execution rules.
5. Select the All DDL can execute directly in SQLConsole rule and click Edit in the Actions column.
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 the All DDL can execute directly in SQLConsole rule 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.
Then, you can modify the result set on the Execution tab in the execution result section. For more information, see the Manage a result set section of this topic.

Modify data

Go to the SQLConsole tab.
On the SQLConsole tab, enter an UPDATE statement in the SQL editor and click Execute in the top toolbar 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 a frequently used SQL statement

On the SQLConsole tab, enter an SQL statement in the SQL editor and select the entire statement.
In the top toolbar of the SQL editor, 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 the added SQL statement

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

Edit the added SQL statement

In the top toolbar of the SQL editor, choose My SQL > Management.
In the dialog box that appears, Edit, delete, or use the added SQL statements.
Note
You can modify the Title, Scope, and SQL parameters of a SQL statement that is added.

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:Table name 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 result sets

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 set

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

References

After you perform data change operations on tables on the SQLConsole tab, you may need to perform the following operations:

If data changes fail to meet your expectations due to accidental operations such as accidental update, delete, or write operations, you can use the data tracking feature to efficiently restore data. For more information, see Data tracking.
You can export data to your local devices. For more information, see Export databases.