MySQLWorkbench tutorial (Beginner Edition) _ MySQL

MySQL Workbench is a visual database design tool recently released by MySQL AB. This tool is a dedicated tool for designing MySQL databases. MySQL Workbench has many functions and features. this article, written by Djoni Darmawikarta, 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 use forward-engineer (forward engine) generate our model into a MySQL database. MySQL Workbench is 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 MySQL Workbench 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. MySQL Workbench has many functions and features. this article, written by Djoni Darmawikarta, 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 use forward-engineer (forward engine) generate our model into a MySQL database. In our example, the physical model created using MySQL Workbench looks like this: how to use Workbench (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF1293K0-19D8.jpg "alt =" "/> create ORDER Schema first let's create a solution that saves the ORDER physical model. Click the + button (marked in red) to use Workbench. (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF1350-2aa.jpg "alt =" "/> Change the default name of the new scheme 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 (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF13Z620-3PB.jpg "alt =" "/> rename the scheme and close the schema window. Workbench tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF142A60-4S08.jpg "alt =" "/> create order table we now 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 Entity Relationship ). Therefore, double-click Add digoal. Workbench tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF1460940-55356.jpg "alt =" "/> click the Table icon, move the mouse to the EER digoal area, click the mouse at the position where you want to place the first table. Workbench tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF14Z620-C3P.jpg "alt =" "/> Workbench tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF1521X0-KQ9.jpg "alt =" "/> for the other two tables, repeat the above operation. You can drag a table to move its position. Workbench tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF1556250-Y3O.jpg "alt =" "/> Next, we will do some operations on table1 through the table editor of Workbench. To open the Table editor, right-click table1 and select the Edit Table menu. Workbench tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF1593K0-a110.jpg "alt =" "/> type the table name ORDER for table1. Workbench tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF1629E0-1062I.jpg "alt =" "/> Next, add a column (field ). Select the Columns label. How to change the column name (field name) idORDER to ORDER_NO. Workbench (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF1B4060-11cR.jpg "alt =" "/> select the data type INT from the drop-down list box. Workbench tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF1E5310-12L39.jpg "alt =" "/> we want the value of the ORDER_NO column to be automatically controlled by the MySQL database, so, we select the Auto Increment column (Auto Increment ). AI is a feature of MySQL databases. Workbench tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF1IK0-134L0.jpg "alt =" "/> You can also specify other physical properties of the table, such as its Collation attribute; of course, you can specify other advanced options for the table, such as trigger and portioning (corresponding to the Trigger and Partioning labels respectively ). Workbench tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF1M3440-142459.jpg "alt =" "/> note that 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 (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF1P3120-1541U.jpg "alt =" "/> Go back to the table designer and add the other two columns (field): 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 (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF1S2Q0-1620V.jpg "alt =" "/> use the same method to create the SALES_ORDER table and its columns (fields ). Workbench tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF1V5620-1I432.jpg "alt =" "/> Finally, create the PURCHASE_ORDER table and its columns (fields ). Workbench tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF1910940-1T248.jpg "alt =" "/> create a 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 identifying relationship icon, click in the SALES_ORDER table, and then click in the ORDER table. Workbench tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF1951560-19C91.jpg "alt =" "/> note that when you click a table, the icon changes to a hand shape with a relationship. Workbench tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF1c0620-205534.jpg "alt =" "/> Workbench tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF202Q20-213151.jpg "alt =" "/> The 1:1 relationship is set like this; the ORDER_NO primary key is moved to the SALES_ORDER table and serves as its primary key. Workbench tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF20593P-22N30.jpg "alt =" "/> Next, create the relationship between PURCHASE_ORDER and ORDER, it is still a relationship. Workbench tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF209DP-2325K.jpg "alt =" "/> we have now completed the design of the relationship between the table and the table; save our model as ORDER. mwb. workbench tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF2135940-2421Q.jpg "alt =" "/> Workbench tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF21GX0-251T1.jpg "alt =" "/> generate DDL and database the ultimate purpose of designing the data model in this article is to build a MySQL database. We will first generate DDL (SQL CREATE script) and then execute this script. From the File | Export menu, select Forward Engineer SQL CREATE Script. Workbench usage tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF2214060-2B3O.jpg "alt =" "/> Workbench tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF2254E0-2N3Q.jpg "alt =" "/> Workbench tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF22Y060-2YO2.jpg "alt =" "/> Finally, run 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 (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF232Q20-291523.jpg "alt =" "/> Workbench tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF23F310-30W02.jpg "alt =" "/> You can also check whether the table has been created. Workbench tutorial (Beginner Edition) "src =" http://img.bitscn.com/upimg/allimg/c140719/1405KF24093P-31S05.jpg "alt ="/> 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.