SQL Console quick start - Data Management - Alibaba Cloud Documentation Center

After you log on to a database in Data Management (DMS), you can use the sql console to perform operations such as creating databases, creating tables, querying table data, and modifying table data. This topic uses an ApsaraDB RDS for MySQL database as an example.

Access the SQL console

You can use one of the following methods to open the sql console.

From the top navigation bar

Log in to DMS 5.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, search for and select a database, and then click Confirm.

From the instance list

Log in to DMS 5.0.
In the Database Instances list on the left side of the page, find the database that you want to manage.
Double-click the database name to go to the sql console.

Create a table

This section provides an example of how to create a table named test_table in a MySQL database that is in Security Collaboration mode.

SQL statements

open the sql console.

Enter the SQL statements 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 statements execute, if the table name is not displayed in the table list on the left, click the icon.

GUI

open the sql console.
Right-click a blank area and select Create Table. For an overview of the SQL Window layout, see SQL Window interface.
Configure the Basic Info and Column Info for the table. You can configure the Index Info and Foreign Key Info tabs based on your business requirements.
After you complete the configuration, click Save Changes at the bottom of the page, and then click Execute Directly.

FAQ about creating tables

Q: What do I do if the system fails to execute the CREATE TABLE statement and a message indicates that a security rule prohibits the execution of CREATE_TABLE?
A: This issue occurs only for databases that are managed in Security Collaboration mode. Contact your administrator or DBA to add the CREATE_TABLE command to the security rule that is associated with the instance.
1. In the top navigation bar, choose Security and disaster recovery (DBS) > 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 Features > Security and disaster recovery (DBS) > Security Rules.
2. Find the target rule set and click Edit in the Actions column.
3. In the navigation pane on the left, click SQL Correct.
4. Set Checkpoints to SQL execution rules.
5. Find the rule named Allow all DDL statements to be directly executed in sql console and click Edit in the Actions column.
6. Add the SQL type from the error message, such as CREATE_TABLE, to the rule DSL of the security rule, and click Submit.
7. After you submit the change, enable the Allow all DDL statements to be directly executed in sql console rule, and disable the rule that requires you to submit a ticket to execute DDL statements.
For more information, see Data change.

Query data using SQL

open the sql console.
In the sql console, enter a query statement or double-click a table name to automatically generate a query statement, and then click Execute.

After you query the data, you can edit the result set in the Execution Result section. For more information, see Manage result sets.

Update data using SQL

open the sql console.
In the SQL Window window, enter an update statement and click Execute.
In the Execution Confirmation dialog box, click OK.

Manage saved SQL queries

After you open the sql console, you can add, use, and manage your saved SQL queries.

Add a saved SQL query

In the sql console window, enter and select an SQL statement.
Click Saved SQL > Add.
In the Add to Saved SQL dialog box, enter a Title, select a Scope, and click OK to add the statement to Saved SQL.

Use a saved SQL query

Click Saved SQL > Select, and then select the title of the target query to load the corresponding SQL statement.

Edit a saved SQL query

Click Saved SQL > Management.
Edit, delete, or use the saved SQL queries.

Note
You can edit the Title, Scope, and SQL of a saved query.

View data as a table

open the sql console.
In the sql console window, right-click the target table and select Open Table.
On the Table tab that appears, you can view the data in a table format.
In the upper-right corner of the page, click the icon, select a Column Name, and then click OK to modify the columns returned by the query.

Manage result sets

Search a result set

In the search box in the upper-right corner of the result set, enter the content that you want to search for. Data that matches the entered content is highlighted. You can also select Filter to display only the rows that match your search.

Export a result set

Click Export File. You can export files of various types, such as All Result Sets (all result sets in a single sql console, each in a separate Excel file), Export as excel, and Export as text.

Edit a result set

In the result set area, click Enable Edit to perform operations such as Insert and Delete. After editing, click Submit Changes.

Note

You cannot edit a result set if it does not have a 'NOT NULL' primary key or a unique key. If editing is unavailable, move the pointer over the edit-lock icon to view the reason and solution.

Insert data: This operation adds an empty row at the end of the result set. You can enter the data that you want to add.
Edit data: Click a cell to edit its content. However, you cannot edit masked sensitive fields. A red marker appears in the upper-left corner of an edited cell.
Delete data: Select a row and click Delete. The deleted row is highlighted in gray.

Note
You can undo this deletion.
Submit changes: After you insert, edit, or delete data, click Submit Changes to apply your modifications. Rerun the query to view the updated data.

Copy data from a result set

The following examples describe how to perform copy operations on a Windows operating system.

To copy data from a single cell, select it and click or press Ctrl+C.
To copy data from a range of cells, click the first cell, hold down the Shift key, and then click the last cell. Then, press Ctrl+C.
Copy data from multiple columns:
- To copy data from consecutive columns, click the header of the first column, hold down the Shift key, and then click the header of the last column. Then, press Ctrl+C.
  
  For example, to copy data from columns 1 to 5 of the test_table table, click the header of column 1. Hold down the Shift key and click the header of column 5. The data in columns 1 to 5 is highlighted. Then, press Ctrl+C.
- To copy data from non-consecutive columns, hold down the Ctrl key and click the header of each desired column. Then, press Ctrl+C.
  
  For example, to copy data from columns 1, 3, and 5 of the test_table table, click the header of any one of these columns. Hold down the Ctrl key and click the headers of the other two columns. The data in columns 1, 3, and 5 is highlighted. Then, press Ctrl+C.
Copy data from multiple rows:
- To copy data from consecutive rows, click the sequence number of the first row, hold down the Shift key, and then click the sequence number of the last row. Then, press Ctrl+C.
  
  For example, to copy data from rows 1 to 5 of the test_table table, click the sequence number of row 1. Hold down the Shift key and click the sequence number of row 5. The data in rows 1 to 5 is highlighted. Then, press Ctrl+C.
- To copy data from non-consecutive rows, hold down the Ctrl key and click the sequence number of each desired row. Then, press Ctrl+C.
  
  For example, to copy data from rows 1, 3, and 5 of the test_table table, click the sequence number of any one of these rows. Hold down the Ctrl key and click the sequence numbers of the other two rows. The data in rows 1, 3, and 5 is highlighted. Then, press Ctrl+C.

FAQ

Q: When I execute an SQL statement in DMS, the error message "Current database is busy, task execution canceled! Please try again when the database is idle" is reported. How do I fix this?

A: This error occurs because the "Maximum number of running threads" parameter in DMS is set too low. You can perform the following steps to view and adjust this parameter:
1. In the top navigation bar, click Security and Disaster Recovery (DBS) > Security Rules to go to the Security Specifications page.
2. For your target database engine, click the Edit button for the corresponding rule set (such as mysql default) to open the Details page.
3. In the navigation pane on the left, select SQL Execution Control.
4. Find the rule named Pre-SQL execution database load check, click its Edit button to view and modify the Maximum number of running threads parameter.
5. Adjust this value based on your actual business workload to ensure database stability. For information about other related settings, see Configure SQL Execution Control.
Q: When I run a ticket type check in DMS, the error message "SQL type check failed. Submission is not allowed according to security rule settings" is reported. How do I resolve this?

A: This error means your SQL statement violates a security rule in DMS. DMS uses security rule sets to validate SQL operations and ensure database security.

You can perform the following steps to view and adjust the relevant rule configurations:
1. In the top navigation bar, click Security and Disaster Recovery (DBS) > Security Rules to go to the Security Specifications page.
2. Depending on the target database engine, click the Edit button for the corresponding rule set (such as mysql default) to open the Details page.
3. In the navigation pane on the left, select SQL Correct.
4. Based on the specific information in the error message, locate the corresponding Configuration Name/Rule Name and click its Edit button to view the current rule configuration.
5. For a detailed explanation of the rule syntax, see Security Rule DSL Syntax.

Data Management:SQL Console quick start