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 .
NoteIf 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 .
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
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
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.
Query data
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
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 .
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 The SQL statement is automatically inserted into the SQL editor.
in the top toolbar of the SQL editor, and click the title of the SQL statement that you want to use.Edit added SQL statements
Choose
.Edit, delete and use the added SQL statements.
NoteYou can configure the Title, Scope and SQL parameters.
View data in the form of a visual table
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.
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 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.
NoteThe 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, pressCtrl+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, pressCtrl+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, pressCtrl+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, pressCtrl+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, pressCtrl+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, pressCtrl+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, pressCtrl+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, pressCtrl+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, pressCtrl+C
to copy data.