home › Navigators › Mysql workbench is all about operators. Quick start: Visual database design in MySQL Workbench. Adding and editing data

Mysql workbench is all about operators. Quick start: Visual database design in MySQL Workbench. Adding and editing data

MySQL Workbench - software, created for database design. There is a catalog of tools for operating and modeling the database. The product is characterized by high performance.

The use of the software is recommended during a complex transition. The tables display saved processes and foreign keys. An integrated shell is supported that allows you to write scripts. First of all, the program is a design tool for visual graphic presentation. There is an editor that allows you to adjust requests and then send them through the server. Accepted answers are presented in the form of tables. When the view is rendered, the user still has the ability to make edits.

Download the full Russian version of MySQL Workbench for free from the official website without registration and SMS.

System requirements

Supported OS: Windows 10, Vista, 8.1, XP, 7, 8
Bit depth: 64 bit, 32 bit, x86

A web developer grows with the projects he creates and develops. As projects grow, the complexity of the software increases, the amount of data it processes inevitably increases, as well as data schema complexity. Communication with other web developers shows that MySQL databases are very popular among us, and to manage them - the well-known PHPMyAdmin. Moving from small projects to large ones, from cms to frameworks, many, like me, remain faithful to MySQL. However, to design a complex database with a large number of tables and relationships, the capabilities of PHPMyAdmin are sorely lacking. So I decided to write a review MySQL Workbench is a great free desktop program for working with MySQL.

In the first part of the review, I will talk about the very basics of working with the program, so you can use this article as beginner's guide. The second part will be devoted using Workbench in combat when working with a remote server. In it I will give the basic instructions and recommendations for setting up a server connection and synchronization with it.

MySQL Workbench- a tool for visual database design that integrates design, modeling, creation and operation of a database into a single seamless environment for the MySQL database system.

I must say that the program is really great. It allows you to throw quickly and with pleasure project data schemas, design entities and connections between them, painlessly implement changes into the scheme and just as quickly and painlessly synchronize it with a remote server. A graphics editor EER diagrams, reminiscent of funny cockroaches, allows you to see the overall picture of the data model and enjoy its lightness and elegance :) After the first try, this tool becomes an indispensable assistant in the combat arsenal of a web programmer.

Download MySQL Workbench

The MySQL Workbench distribution is available on this page. The latest version of the program at the time of writing is Version 6.1. Before downloading, you must select one of the following platforms:

Microsoft Windows (available MSI Installer and ZIP archive)
Ubuntu Linux
Fedora
Red Hat Enterprise Linux / Oracle Linux
Mac OS X

After choosing a platform, you are prompted to register or log in to Oracle. If you don't want to, there's a link below. "No thanks, just start my download"- click on it ;)

Beginning of work

The program's start screen reflects the main areas of its functionality - designing database models and their administration:

At the top of the screen there is a list of connections to the MySQL servers of your projects, and a list of the latest open data models is at the bottom of the screen. Work usually starts with creating a data schema or loading an existing structure into MySQL Workbench. Let's get to work!

Creating and editing a data model

To add a model, click the plus sign next to the "Models" heading or select "File → New Model" (Ctrl + N):

On this screen, enter the database name, select the default encoding and, if necessary, fill in the comment field. You can start creating tables.

Adding and editing a table

The list of project databases and the list of tables within the database will be located in the tab "Physical Schemes". To create a table, double click on "+Add Table":

A convenient interface will open for editing the list of fields and their properties. Here we can set the field name, data type, and also set various attributes for the fields: assign field primary key (PK), mark it Not Null (NN), binary (BIN), unique (UQ) and others, set for field auto-increment (AI) And default value.

Index management

You can add, delete and edit table indexes in the tab "Indexes" table management interface:

Enter the name of the index, select its type, then check the list of fields participating in this index in the required order. The order of the fields will correspond to the order in which the checkboxes were checked. In this example, I added a unique index to the field username.

Relationships between tables

Setting foreign keys and linking tables is only possible for tables InnoDB(this storage system is selected by default). To manage relationships, each table has a tab "Foreign Keys":

To add a connection, open the tab "Foreign Keys" child table, enter the name of the foreign key and select parent table. Further in the middle part of the tab in the column Column select the key field from the child table, and in the column Referenced Column- the corresponding field from the parent table (field types must match). When creating foreign keys corresponding indexes are automatically created in the child table.

In chapter "Foreign Key Options" configure the behavior of the foreign key when the corresponding field changes (ON UPDATE) and removal (ON DELETE) parent record:

RESTRICT- throw an error when changing/deleting a parent record
CASCADE- update foreign key when parent record changes, delete child record when parent is deleted
SET NULL- set the foreign key value NULL when changing/deleting parent (not acceptable for fields that have the flag set NOT NULL!)
NO ACTION- do nothing, but in fact the effect is similar to RESTRICT

In the example above, I added to the child table UserProfile foreign key to link to parent table User. When editing a field userId and deleting positions from the table User similar changes will be made automatically also occur with related records from the table UserProfile.

When creating a project, you often need to add startup data to the database. These could be root categories, administrative users, etc. In MySQL Workbench table management there is a tab for this "Inserts":

As can be seen from the example, if some MySQL function needs to be applied to the data before writing to the database, this is done using the syntax \func functionName("data"), For example, \func md5("password").

Creating an EER diagram (entity-relationship diagram)

To present the data schema, entities and their relationships in graphical form, MySQL Workbench has an EER diagram editor. To create a diagram at the top of the database management screen, double-click on the icon "+Add Diagram":

In its interface you can create and edit tables, add relationships of various types between them. To add a table that already exists in the diagram to the diagram, simply drag it from the panel "Catalog Tree".

To export a data schema to graphic file select "File → Export" and then one of the options (PNG, SVG, PDF, PostScript File).

Importing an existing data schema (from SQL dump)

If we already have a data schema, it can be easily imported into MySQL Workbench for further work. To import a model from an SQL file, select "File → Import → Reverse Engineer MySQL Create Script...", then select the required SQL file and click "Execute >"

MySQL Workbench also provides import and synchronization of the data model directly with a remote server. To do this you will need to create connection remote access to MySQL, which I will talk about in the continuation of this review.

The demo project from the article is available for download at this link. I wish you success and beautiful cockroach schemes!

With the advent of the MySQL Workbench program as part of MySQL, the process of creating databases (DBs) has been significantly simplified. After all, what previously had to be done manually using a SQL script and command line, can now be done “in visual mode” using a friendly GUI.

Let's look at the process of creating a database using MySQL Workbench in more detail.

The screenshot below shows a general view of the MySQL Workbench program window.

In order to create a database, you need to right-click in the left panel in the area with the database list (indicated as SCHEMAS) and in context menu select "Create Schema".

After this, a tab will appear in which you should specify the name of the new database and specify the sorting parameters. The database, for example, will be named mynewdatabase. You can either select sorting options from the drop-down list or leave those offered by default (in this example, the default options are left).

After this, to continue creating the database, click the “Apply” button. The dialog box that appears will display the database creation script generated by MySQL Workbench. If necessary, this script can be edited directly in this window.

At the top of the window there is an Online DDL area. It is intended to set script execution parameters. These parameters can be useful when manipulating an existing database. When creating a database, it is recommended to leave the default values (“Default”).

The purpose of this post is to help a novice developer quickly get used to and design a simple database using the visual database design tool MySQL Workbench from Oracle and obtain its ER model and SQL dump.

Well, less words and more meaning! Appearance program window, the “Data Modeling” section looks like this:

In order to open an existing model, click on the link: Open Existing EER Model, to create a new model – select the option: Create New EER Model To create an entity-relationship model from an existing database, click on the parameter: Create EER Model From Existing Database, and to create an EER model from a SQL script you need to select: Create EER Model From SQL Script.
To create a new model, use the Create New EER Model link; after clicking on it, a window with parameters will be displayed:

First you need to create tables, to do this, click on the button Add Table, the following form will appear:

First let's create a table users, which will store user data information system, in field table name enter the table name in the form section Columns Let's create table fields:
- First field id will contain a unique user number, set its properties: Auto Increment, Not Null, Primary key And Unique, In chapter Data type choose an integer type integer.
- Second field fio, where it will be stored FULL NAME. user, set the property field: Not Null, Primary key, In chapter Data type choose string type VARCHAR 255 .
- Third field login, will contain the user login, it must be unique, like the field id, so let’s set it the property Unique and set the number of characters in 255 .
- The following fields: password containing the password, e_mail containing the address Email and field type containing the user type will be without special properties, with a string type VARCHAR long in 255 characters except the last field type who has enough 45 characters.
After the completed manipulations, a form with the table name users will look like this:

A table will appear on the diagram users with fields and indexes:

Let's create a table in a similar way settings with access settings to the IS database containing fields id, host to specify the host name (server address), db– database name, user And password with a username and password to install the IS on a remote server.

Next, using the already known method, we will create a table shops that will store data about shops in the fields: id type integer– key, non-zero, unique with auto-increment field name storing the store name, field address– his physical address, field tel- store phone number, site– online store website and field email with the store's email address.

Then let's create a table products storing data about store products in the fields: id type integer– key, non-zero, unique with auto-increment, name field storing the name of the store, key, non-zero field of integer type shop_id storing the store number, field type_id with information about the product number from the table of product types. Brand field – manufacturer’s brand, 255 characters long, field model– with product model, field data– with data and characteristics of the product type Tinytext, field img with the full address to the product image, 255 characters long, and the price field with the price of the product and warranty with information about the warranty period for the product, 45 characters long.

Tables we created settings, shops And products look like this:

Next we need a table storing the type of products product_type, it consists of a unique, non-null key field id with auto-increment of an integer type, and a unique name field 255 characters long, which contains the name of the product type.

The table looks like this:

The last two tables are orders And deliveries, the first contains information about customer orders, and the last contains information about product delivery.

Table fields orders: id key, non-zero, unique field of integer type with auto-increment, field shop_id containing the store number - a key, non-zero integer field product_id storing the product number - a key, non-zero integer field fio date with order date – type DATE, field quantity with the number of ordered goods – integer type, field tel with the customer's phone number - a string type 255 characters long and a confirm field containing information about order confirmation - a logical type.

Table fields deliveries: order_id with order number - key, non-zero, unique field of integer type with auto-increment, field field fio with the number of the user who made the order - a key, non-zero integer field address storing the delivery address of the goods specified by the client - a string type 255 characters long, field time storing the desired delivery time of the goods - a string type 255 characters long, field date with the date the customer placed the order - type DATE and a boolean field confirm storing information about the delivery of goods.

Tables orders And deliveries look like this:

Table relationships

We have created a database consisting of seven tables, now we need to link the tables, we have already created key fields of the integer type, they will become the basis for linking.
For example, to link two tables products And product_type, you need to double-click the left mouse button on the diagram with the products table and select the tab foreign keys(foreign keys), further into the field Foreign key name enter a unique name for the foreign key, double-click on the tab Referenced table and select the table product_type, then in the form located to the right select the referencing field type_id and select the field from the pop-up list id.

Thus, both fields of the table are connected, then you need to set the type of relationship between the tables, open a window by clicking on the relationship between the tables that appears, and select the tab Foreign Key and in the section Cardinality Select the one-to-many connection type and close the window. The diagram will display the relationship between the tables:

In a similar way, we link all the key fields in the tables so that they are logically interconnected, then we need to make sure that the designed database corresponds to the third normal form.

Normal form- a property of a relationship in a relational data model, characterizing it from the point of view of redundancy, which can potentially lead to logically erroneous results of sampling or changing data. Normal form is defined as a set of requirements that a relation must satisfy.

In the relational model, a relation is always in first normal form by definition of the concept of a relation. As for the various tables, they may not be correct representations of relationships and, accordingly, may not be in first normal form. A relation variable is in second normal form if and only if it is in first normal form and every non-key attribute is irreducibly (functionally complete) dependent on its candidate key. A database will be in third normal form if it is reduced to second normal form and each non-key column is independent of each other.

Thus, our base is in third normal form, because Each non-key column is independent of each other. This is clearly visible in our database diagram:

Most tables are in a one-to-many relationship, with the exception of tables deliveries And orders in a one-to-one relationship, because delivered, there can only be one order, i.e. One order has only one delivery. The remaining connections are clearly indicated above.

Now let's upload our database to the server. To do this, create a new connection to the database by clicking on the link New connection in the program start window:

Then fill in the fields in the window that opens:

Specify the connection name in the field Connection Name, select the connection method from the list Connection Method, set the host name and port in the tab Parameters, indicate the username and password if you have one and click on the OK button. Then open the tab EER Diagram, in the panel select the item Database and click on the parameter Forward Engineer:

After the window appears, click on the button "Next", select the parameter Export MySQL Table Objects and press the button "Next":

After clicking the button, a tab with SQL code will appear, you can save it by clicking the button “Save to file” if necessary and then press the button "Next". A window with connection parameters will appear:

We check if the connection parameters are correct and click on the button "Execute", if the SQL code does not contain errors, then after executing the code we will see a window with a list of tables, otherwise an error message will be displayed. Now our database is uploaded to the server.

Thank you for your attention, download the program itself.

UPD:

Some Khabra residents were interested in the possibility of displaying table connection lines in field-to-field mode

on the advice of one of the users, I will give a short explanation on how to change the appearance of relationships and tables, to do this you need to select the following option in the menu section Relationship Notation:

After this, the table relationships will take the form:

It is also possible to change the type of tables; to do this, you need to check the box in the above section of the menu and in the following Object Notation: