Detailed explanation of MySQL Workbench usage tutorial

MySQL Workbench is an integrated desktop software designed specifically for MySQL. It is also the next generation of visual database design and management tools. It has both open source and commercial versions. The software supports Windows and Linux systems and can be downloaded from https://dev.mysql.com/downloads/workbench/.

MySQL Workbench is a visual database design software that provides a complete visual database operating environment for database administrators and developers. Its main functions include database design and model building, SQL development (replaces MySQL Query Browser), and database management (replaces MySQL Administrator).

MySQL Workbench is available in two versions:

• MySQL Workbench Community Edition (also called MySQL Workbench OSS, Community Edition), MySQL Workbench OSS is an open source community version released under the GPL certificate.
• MySQL Workbench Standard Edition (also called MySQL Workbench SE, commercial version), MySQL Workbench SE is a commercial version charged annually.

The initial interface of MySQL Workbench is shown in the figure below.

(I) Using Workbench to operate the database

Open the MySQL Workbench software, as shown in the figure below. The part marked with a box is the list of databases that have been created in the current database server.
In MySQL, SCHEMAS is equivalent to a list of DATABASES. Right-click in a blank area of ​​the SCHEMAS list and select Refresh All to refresh the current database list.

① Create a database

Right-click in a blank area of ​​the SCHEMAS list and select "Create Schema..." to create a database, as shown in the following figure.

In the Create Database dialog box, enter the name of the database in the Name box and select the character set specified by the database in the Collation drop-down list. Click the Apply button to create it successfully, as shown in the figure below.

After completing the settings in the Create Database dialog box, you can preview the SQL script for the current operation, namely CREATE DATABASE test_db, and then click the Apply button. Finally, in the next pop-up dialog box, directly click the Finish button to complete the creation of the database test_db, as shown in the figure below.

Use the same method to create the database test_db_char and specify the character set as UTF-8 by default.

② Modify the database

After successfully creating the database, you can modify the character set of the database. Right-click on the database whose character set needs to be modified and select the "Alter Schema..." option to modify the character set specified by the database, as shown in the figure below.

In the dialog box for modifying the database, the name of the database cannot be modified. In the Collation drop-down list, select the character set to which the database needs to be modified. Click the Apply button to create it successfully, as shown in the figure below.

③ Delete the database

You can delete a database in the SCHEMAS list. Right-click the database to be deleted and select "Drop Schema...", as shown in the figure below.

In the pop-up dialog box, click the Drop Now button to directly delete the database, as shown in the figure below.

If you click the Review SQL button, the SQL statement corresponding to the delete operation will be displayed. Click the Execute button to perform the deletion operation, as shown in the figure below.

④ Set the default database

In the SCHEMAS list, you can select the default database, right-click on the database for which you want to specify the default, and select Set As Default Schema, as shown in the following figure. This operation is equivalent to the USE <database_name> command in the command line tool.

After the database test_db is set as the default database, the font of test_db in the SCHEMAS list will be displayed in bold, as shown in the following figure.

(II) Using Workbench to operate data tables ① Create a data table

Open MySQL Workbench software, expand the current default test_db database in the SCHEMAS list, right-click on the Tables menu, and select "Create Table..." to create a data table in the test_db database, as shown in the figure below.

In the dialog box for creating a data table, enter the name of the data table in the Table Name box, edit the column information of the data table in the box part in the figure, and after editing, click the Apply button to successfully create the data table, as shown in the figure below.

After the settings are completed, you can preview the SQL script of the current operation, then click the Apply button, and finally click the Finish button in the next pop-up dialog box to complete the creation of the data table tb_emp1, as shown in the figure below.

② View data sheet

After successfully creating a data table, you can view the structural information of the data table. Right-click the data table whose table structure you want to view and select the Table Inspector option to view the structure of the data table, as shown in the figure below.

In the dialog box for viewing the data table, the Info tab displays information such as the table name, storage engine, number of columns, table space size, creation time, update time, character set collation rules, etc., as shown in the following figure.

The Columns tab displays the information of the table data columns, including column name, data type, default value, non-null flag, character set, collation rules, and usage permissions, as shown in the following figure.

③ Modify the data table

In the test_db database of the SCHEMAS interface, right-click on the data table whose table structure needs to be modified and select the "Alter Table..." option to modify the basic information and structure of the data table, as shown in the figure below.

In the dialog box for modifying the data table, as shown in the figure below, you can modify the name of the data table in the Table Name box, and edit the column information of the data table in the box part in the figure, including editing the column name, editing the data type, creating a new column, and deleting a column. You can adjust the order of the columns by dragging up and down, and right-click on the data column to delete the column. After editing is completed, click the Apply button to successfully modify the data table.

After the settings are completed, you can preview the SQL script of the current operation, then click the Apply button, and finally click the Finish button in the next pop-up dialog box to complete the modification of the data table tb_emp1, as shown in the figure below.

④ Delete the data table

You can delete a table in the Tables list of the test_db database in SCHEMAS. Right-click the table to be deleted and select "Drop Table...", as shown in the figure below.

In the pop-up dialog box, click the Drop Now button to directly delete the data table, as shown in the figure below.

If you click the Review SQL button in the pop-up dialog box, the SQL statement corresponding to the delete operation will be displayed. Click the Execute button to execute the delete operation, as shown in the figure below.

(III) Primary key constraint

In MySQL Workbench, in the dialog box for modifying a table, you can manipulate the primary key constraint of the data table by controlling the check status of the PK column. When the PK checkbox is checked, the column becomes the primary key of the data table; when the PK checkbox is unchecked, the primary key constraint of the column is canceled. Click the Apply button to complete the table modification, as shown in the figure below.

(IV) Foreign key constraints

In MySQL Workbench, open the dialog box for modifying the data table and enter the Foreign Keys tab. First, fill in the foreign key name in Foreign Key Name. Next, select the data table in the current database in Referenced Table. Then select the field to set the foreign key. Next, select the associated field of the primary table. Finally, click the Apply button to complete the foreign key addition operation, as shown in the figure below.

After the settings are completed, you can preview the SQL script of the current operation, then click the Apply button, and finally click the Finish button in the next pop-up dialog box to complete the creation of the foreign key in the data table tb_emp2, as shown in the figure below.

In the list of foreign key constraints, right-click the foreign key that needs to be deleted, select the Delete selected option, delete the corresponding foreign key, and click the Apply button to complete the deletion, as shown in the following figure.

After the settings are completed, you can preview the SQL script of the current operation, then click the Apply button, and finally click the Finish button in the next pop-up dialog box to complete the deletion of the foreign key in the data table tb_emp2, as shown in the figure below.

(V) Unique constraint

In MySQL Workbench, in the dialog box for modifying a data table, you can manipulate the unique constraint of the data table by controlling the check status of the UQ column. When the UQ checkbox is checked, the column becomes the unique constraint index of the data table; when the UQ checkbox is unchecked, the unique constraint index of the column is unchecked. Click the Apply button to complete the table modification, as shown in the figure below.

(VI) Default value constraints

In MySQL Workbench, in the Modify Table dialog box, edit the default value of the field in the Default/Expression column. Click the Apply button to complete the table modification, as shown in the figure below.

(VII) Non-null constraints

In MySQL Workbench, in the dialog box for modifying a data table, you can manipulate the not-null constraint of the data table by controlling the check status of the NN column. When the NN checkbox is checked, the column is a non-null constraint for the data table; when the NN checkbox is unchecked, the non-null constraint for the column is canceled. Click the Apply button to complete the table modification, as shown in the figure below.

8. Editing data in a table

Open MySQL Workbench software, expand the current default test_db database in the SCHEMAS list, expand the Tables menu, right-click on the tb_courses table, and select Select Rows–Limit 1000 to edit the data in the tb_courses table, as shown in the following figure.

In the pop-up dialog box, the Edit menu bar contains three buttons, namely "Modify", "Insert" and "Delete". Click the Apply button to save the changes, as shown in the figure below.

After completing the settings in the Edit Data Table dialog box, you can preview the SQL script for the current operation, then click the Apply button, and finally click the Finish button in the next pop-up dialog box to complete the modification of the data in the data table tb_courses, as shown in the figure below.

9. View

① Create a view

Open MySQL Workbench software, expand the current default test_db database in the SCHEMAS list, right-click on the Views menu, and select "Create View..." to create a view, as shown in the figure below.

In the Edit View dialog box, as shown below, set the view name and view definition. After editing is completed, click the Apply button to successfully create the view.

After the settings are completed, you can preview the SQL script of the current operation, then click the Apply button, and finally click the Finish button in the next pop-up dialog box to complete the creation of the view view_courses, as shown in the figure below.

② View view information

At this point, you can find the newly created view view_courses in the Views directory, right-click view_courses, and select Select Rows–Limit 1000 to view the view content, as shown in the following figure.

In the dialog box for viewing view contents, the view contents are read-only and cannot be modified, as shown in the following figure.

③ Delete view

You can delete views in the Views list of SCHEMAS. Right-click on the view to be deleted and select "Drop View...", as shown in the figure below.

In the pop-up dialog box, click the Drop Now button to directly delete the view, as shown in the following figure.

If you click Review SQL, the SQL statement corresponding to the delete operation will be displayed. Click the Execute button to perform the delete operation, as shown in the figure below.

10. Stored Procedures

Open the MySQL Workbench software and click the "New Stored Function" button in the menu bar to create a new stored procedure, as shown in the figure below.

In the Create Stored Procedure dialog box, set the name and definition of the stored procedure, and click the Apply button to successfully create the custom function, as shown in the following figure

After the settings are completed, you can preview the SQL script of the current operation, then click the Apply button, and finally click the Finish button in the next pop-up dialog box to complete the creation of the stored procedure new_procedure, as shown in the figure below.

In the SCHEMAS interface, expand the Stored Procedures directory in the test_db database, and right-click the newly created stored procedure new_procedure to create, modify, and delete stored procedures, as shown in the following figure.

11. Trigger

Open MySQL Workbench software, in the SCHEMAS interface, open the test_db database directory, expand the Triggers directory of tb_emp8, and you can view the triggers related to the data table, as shown in the following figure.

In the SHEMAS interface, click the "Information" button on the right side of the test_db database, as shown in the figure below.

In the database information list that pops up, select the Triggers tab to view the detailed information of the trigger, including the trigger name, event type, associated data table, and trigger condition, as shown in the following figure.

12. Users and permissions

① Create a user

Open the MySQL Workbench software, select the Server menu in the menu bar, select the Users and Privileges option in the expanded list, and enter the user and privilege management interface, as shown in the figure below.

Enter the user and permission management interface. The upper left box displays a list of users in the current database, including the default users mysql.session, mysql.sys, and root of the database system, as well as custom users. The list also displays the user's host name, such as localhost. In the lower left corner of the management interface, you can click the Add Account button to create a new user, as shown in the figure below.

In the creation user interface, you can set the user name, authentication type, host name, user password and confirm password. Click the Apply button to complete the user creation, as shown in the figure below.

In the user list on the left, select a user to view the user's user name, authentication type, host name, user password and other information. You can also modify the user information. After the modification is completed, click the Apply button to complete the modification of the user information, as shown in the figure below.

② Delete user

Below the user list, you can click the Delete button to delete a user, and click the Refresh button to refresh the user list, as shown in the following figure.

This is the end of this article about the detailed tutorial on how to use MySQL Workbench. For more relevant MySQL Workbench content, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!