How to use MySQLWorkbench _ MySQL

This article mainly introduces how to use MySQLWorkbench (text). For more information, see MySQLWorkbench. It is a visual database design tool recently released by MySQL AB. This tool is a dedicated tool for designing MySQL databases.

: Http://www.bitsCN.com/database/29494.html

MySQLWorkbench has many functions and features. in this article, DjoniDarmawikartaThe written articles show some of them through an example. We will create a physical data model for an order system, where the order system can be a sales order or an order, and useForward-engineer (forward engine)Generate our model into a MySQL database.

MySQLWorkbenchIs a visual database design tool recently released by MySQL. This tool is a dedicated tool for designing MySQL databases.

The physical data model you created in MySQLWorkbench is called. A physical data model is a data model for a specific RDBMS product. The model in this article will have some unique MySQL specifications. We can use it to generate (forward-engineer) database objects. in addition to tables and columns (fields), we can also contain views.

MySQLWorkbench has many functions and features. this article, written by DjoniDarmawikarta, shows some of them through an example. We will create a physical data model for an order system, where the order system can be a sales order or an order, and useForward-engineer (forward engine)Generate our model into a MySQL database.

In our example, the physical model created using MySQL Workbench looks like this:

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG621X0-14J8.jpg "/>

ORDER Schema)

First, let's create a solution to save the physical model of the order. Click the + button (marked in red)

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG6250-22a9.jpg "/>

Change the default name of the new solution to order. Note that when you type a solution name, the label name on Physical Schemata also changes-this is a good feature.

The order scheme is added to the Catalog (red circled in the figure ).

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG62A60-3W94.jpg "/>

Close the schema window after renaming the solution.

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG62Q20-4JQ.jpg "/>

Create order table

Now we create three tables in the ORDER model: the ORDER table and its two sub-tables SALES_ORDER and PURCHASE_ORDER. First, make sure that you have selected the label of the ORDER scheme, so that the table we created will be included in this scheme.

The table we want to create is displayed as an EER chart (EER = Enhanced EntityRelationship ). Therefore, double-click Add digoal.

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG629E0-54A9.jpg "/>

Click the Table icon, move the mouse to the EER digoal area, and click the cursor at the position where you want to place the first Table.

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG629E0-61V8.jpg "/>

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG631250-G140.jpg "/>

For the other two tables, repeat the preceding operation. You can drag a table to move its position.

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG632Q0-U609.jpg "/>

In the next step, we need to perform some operations on Table 1. these operations are completed through the table editor of Workbench. To open the Table editor, right-click table1 and select the Edit Table menu.

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG6343P-95364.jpg "/>

Enter the table name ORDER in Table 1.

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG635940-104122.jpg "/>

Next, add columns (fields ). Select the Columns label. Change the column name (field name) idORDERORDER_NO.

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG63K0-113407.jpg "/>

Select the data type INT in the drop-down list box.

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG63K0-12Ua.jpg "/>

We want the value of the ORDER_NO column to be automatically controlled by the MySQL database. Therefore, we select the AI column (AutoIncrement -- auto increment ).

AI is a feature of MySQL databases.

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG63Z60-139546.jpg "/>

You can also specify other physical attributes of a table, such as its Collation attribute. you can also specify other advanced options of the table, such as trigger and portioning (corresponding to the Trigger and Partioning labels respectively ).

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG63Z60-142612.jpg "/>

Note: in digoal, table 1 has changed to ORDER, and it has a column (field) ORDER_NO. You can also see three tables in the directory.

Black spots on the right of the table indicate that they are included in a chart.

If you expand ORDER, you can see the ORDER_NO column. Because we define it as the primary key, there is a key icon on the left side of it.

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG640620-154200.jpg "/>

Return to the table designer and add the other two columns (fields): ORDER_DATE and ORDER_TYPE. ORDER_TYPE can have two values: S indicates the sales order, and P indicates the purchase order. Because sales orders are more commonly used, the default value of columns (fields) is S.

You can double-click the white area below the last column to add the next field.

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG640620-1D252.jpg "/>

Use the same method to create the SALES_ORDER table and its columns (fields ).

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG640620-1KO7.jpg "/>

Finally, create the PURCHASE_ORDER table and its columns (fields ).

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG6421Z-1Y594.jpg "/>

Create link

We have created three tables. This is not the end; we still need to create their relationships.

SALES_ORDER is the subtable of ORDER, which means they are, SALES_ORDER is the subtable, ORDER is the parent table, and the ORDER key is moved to SALES_ORDER. Therefore, select (click) 1:1 identifyingrelationship icon, click in the SALES_ORDER table, and then click in the ORDER table.

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG643K0-1952T.jpg "/>

Note: When you click a table, the icon changes to a hand shape with a relationship.

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG643K0-2015X.jpg "/>

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG645310-211Q0.jpg "/>

The relationship is set in this way. The ORDER_NO primary key is moved to the SALES_ORDER table and serves as its primary key.

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG645310-2291B.jpg "/>

Next, create the relationship between PURCHASE_ORDER and ORDER. The relationship is still.

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG64DP-234S5.jpg "/>

We have now completed the design of the relationship between the table and the table; save our model as ORDER. mwb.

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcG64T40-24EN.jpg "/>

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcGA1560-25Fc.jpg "/>

Generate DDL and database

The data model is designed in this article to create a MySQL database. We will first generate the DDL (SQLCREATE script) and then execute this script.

SlaveFile | ExportIn the menu, select Forward Engineer SQL CREATEScript.

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcGA3120-2E102.jpg "/>

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcGA3120-2H544.jpg "/>

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcGA3120-2U2H.jpg "/>

Finally, execute the saved SQL CREATE script. MySQL Workbench itself does not have the ability to execute this script; we can execute it in the MySQL command console.

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcGA6250-292507.jpg "/>

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcGA6250-30QK.jpg "/>

You can also check whether the table has been created.

Workbench tutorial "Alt =" "src =" http://img.bitscn.com/upimg/allimg/c160324/145WcGAN10-311402.jpg "/>

Summary

This article shows you how to visually create a MySQL physical data model in MySQL Workbench and use it to create a MySQL database.