Features and usage of mysql workbench. Quick Start: Visual Database Design in MySQL Workbench

All webmasters have to delve into programming and database technologies after developing their resources.

How to create a MySQL database? You can create tables manually and establish relationships between them, but this is not so convenient.

The free Workbench program was created specifically for this. With it, you can visually create MySQL databases. Modeling them with the help of software is easier, more convenient and many times faster.

Workbench will help you create a MySQL database

With one simple tool, you no longer have to describe the structure. The program generates code automatically. Download the utility from this site, it is suitable for any operating system.

After the normal installation of the program, To create a MySQL database, follow these steps:

1. First you need to open a new model, this is done through the menu or by pressing Ctrl+N:




2. The first step in creating a database is adding a table, so we select the appropriate function:




3. Next, the table is populated. Specify the name and attributes, keep in mind that one of the attributes is the flagged master key. Think in advance how the tables will then be linked to each other:




4. After filling in the required data, create a diagram to determine the relationships between subjects:



5. You will see the table in the workspace. For convenience, you can expand the table structures:




6. Now you need to set the links between the elements, this is done with a special tool on the working panel:
7. As a result, you should have tables in the workspace and links between them:




8. Double-clicking on a connection opens a window in which additional parameters are set:

Attention, since WorkBench has been updated, I wrote this article, which consists of the theory and practice of building a database from WorkBench.

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

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

Saving from model to real/physical database

“File → Export → Forward Engineer MySQL Create Script…”

We mark the necessary checkboxes, I needed only one Generate INSERT Statements for Tables. If you need to save the script to a file, enter the directory in the field above.

In the next window, you can configure which objects we will export. If you look closely, we have only 2 tables created.

Executing a script - creating a database and tables

Click on the "house" in the upper left corner of the program ...

Then double click on MyConnection….

We have a tab like this...

This is our connection to the server, this is where we will execute our script. Please note, on the left, the databases that were created in the WorkBench program ....

Now, you need to give the command to execute this script, for this, click in the top menu, Query Execute (All or Selection)

So, if everything is fine, then in the lower output window, you will see all the “green checkmarks”. And when you press Refresh in context menu in the list of databases, you will see the newly created database mydatabase1.

Finally, let's build an ER diagram. ER stands for Entity Relation - a successful Entity-Relationship model, which, in particular, was developed by Peter Chen. So, back to the model tab and click on Add Diagramm…

We have created a one-to-many relationship. Several students can study at the same faculty. Note that the link next to the Students table is split - this means "to many".

So, we have created a model, from it, through the execution of a script, a real database with tables. And also created an ER diagram.

Whatever the database developer is: a beginner (in particular) or a bearded professional, it is always easier and more visual for him to imagine what he is working on, what he is developing. Personally, I put myself in the first category and in order to understand the material, I would like to see visually what I design / develop.

To date, there are various programs and tools that cope with a similar task: some better, some worse. But today I would like to talk a little about MySQL WorkBench, a visual database design tool that integrates database design, modeling, creation and operation into a single seamless environment for the MySQL database system, which is the successor to DBDesigner 4 from FabForce.(c) Wikipedia. MySQL WorkBench is distributed in two flavors: OSS-Community Edition(distributed under the LGPL) and SE - Standard Edition- version for which developers ask for money. But I think that for many it will be enough and OSS version (especially for beginners and those who do not want or consider it inappropriate to pay for software, as well as adherents of open source programs), Especially since the OSS version has rich functionality.

So, as the name suggests, this tool designed to work with MySQL databases and supports a large number of various types MySQL models (see screenshot below) and will become an indispensable tool for a better understanding and study of relational databases (in particular MySQL) for beginners:

Thus, any MySQL developer will find what he needs. Moreover MySQL Workbench allows you to connect an existing database, perform SQL queries and SQL scripts, edit and manage database objects. But for those who are just starting to master relational databases, the most interesting, in my opinion, is the ability to create EER Models Database. In other words, this is a visual representation of all relationships between tables in your database, which, if necessary, can be easily presented as an SQL script, edited or created a new view. But more on that later. First, let's see what the main eye looks like. MySQL Workbench(5.2.33 rev 7508):
In order to create an EER-model of your database, select " Create New EER Model". As a result, we will have a tab in which we can add / create diagrams, tables, views, procedures; set various access rights for users; create a model using SQL scripts. This tab looks like this:
We will not consider the process of creating tables and databases, because everything is simple here. I will give only the final version of the finished model (see the screenshots below). Moreover, if you hover over the link line (dashed line) of the tables, then the "link", the primary key, and the foreign key will be highlighted in a different color. If you hover over a table, the table itself will be highlighted, as well as all links belonging to the selected table.

In order to edit the table, just right-click on the table we need and select " Edit Table..." As a result, an additional table editing area will appear at the bottom of the window, in which you can change the table name, columns, foreign keys, and much more. In order to export a table to an SQL script, just right-click on the table we need and select " Copy SQL to Clipboard", and then paste from the clipboard to the desired location / program / file.

And now directly about installation MySQL Workbench. Naturally, first you need to download MySQL WorkBench. To do this, go to the MySQL WorkBench download page, at the bottom of the page in the drop-down list, select the one we need operating system. As a result, we will be offered several download options:

• for OS Windows you can download the MSI installer, zip archive of the program, as well as the archive with the source code. For this OS MySQL Workbench can only be downloaded for 32-bit Windows;
• for users ubuntu the choice is a little richer than for Windows users - we are offered to download MySQL Workbench for Ubuntu versions 10.04, 10.10 (at the time of writing) and 32- or 64-bit versions of deb packages;
• for rpm-based distributions, and in this case it is Fedora, Suse Linux and RedHat/Oracle Linux, MySQL Workbench assemblies for 32-bit and 64-bit OS are presented;
• Macintosh users have not been forgotten either - for them there is an assembly for a 32-bit OS only;
• and of course you can download source programs;

So, select the desired download option and click DownLoad. Then we will be kindly asked to introduce ourselves: for registered users - to enter a login and password, for beginners - to register. If you do not want to introduce yourself, then select the option below " "No thanks, just take me to the downloads!" and choose the nearest mirror for downloading. In addition, before installing, make sure that you have installed mysqlclient,.otherwise MySQL WorkBench will refuse to install.

Things to remember and know for Linux users:

Naturally, as in the case with Windows, do not forget about the MySQL Client. For Ubuntu users - you need to download the version of the program, in accordance with the version of your Ubuntu. During installation, carefully look at the error messages, if any, which will most likely tell you which packages are missing in your OS. Read about it below.

How things are with rmp-base distributions, I unfortunately do not know, because. I have never used such distributions, but I think that it is about the same as with debian-based.

You may have noticed that there is no assembly MySQL Workbench for OS Debian GNU/Linux. But, as practice has shown, it's okay. For installation MySQL Workbench in Debian 6.0 (Squeeze) we will use deb- package for Ubuntu 10.04(do not forget about the bitness of your OS: x86 or x64). Let me remind you that in order to install the downloaded deb package, you can use the utility gdebi or enter the following command in the console as root:

# dpkg -i mysql-workbench-gpl-5.2.33b-1ubu1004-amd64.deb For example, during my MySQL installations WorkBench encountered the following error:
dpkg: package dependencies prevent customization of mysql-workbench-gpl package:
mysql-workbench-gpl depends on libcairomm-1.0-1 (>= 1.6.4), however:
Package libcairomm-1.0-1 is not installed.
mysql-workbench-gpl depends on libctemplate0, however:
The libctemplate0 package is not installed.
mysql-workbench-gpl depends on libgtkmm-2.4-1c2a (>= 1:2.20.0), however:
Package libgtkmm-2.4-1c2a is not installed.
mysql-workbench-gpl depends on libpangomm-1.4-1 (>= 2.26.0), however:
Package libpangomm-1.4-1 is not installed.
mysql-workbench-gpl depends on libzip1 (>= 0.9), however:
The libzip1 package is not installed.
mysql-workbench-gpl depends on python-paramiko, however:
The python-paramiko package is not installed.
mysql-workbench-gpl depends on python-pysqlite2, however:
The python-pysqlite2 package is not installed.
dpkg: failed to process mysql-workbench-gpl option (--install):
dependency issues -- leave unconfigured
Errors occurred while processing the following packages:
mysql-workbench-gpl

To fix this error, I just had to type a command in the console to install some packages:

# aptitude install libzip1 libcairomm-1.0-dev libctemplate0 libgtkmm-2.4-1c2a

To install the above packages, you will also need additional packages who manager apt kindly offer to download. After installing all the necessary packages, MySQL WorkBench installs without problems.

Everything: MySQL WorkBench is successfully installed and ready to learn how to work.

upd:
If I'm not mistaken, since Ubuntu 12.04 MySQL WorkBench can be found in the distribution's repositories. Therefore, the installation process is much easier and without any crutches.
To install MySQL WorkBench, just enter the following command in the terminal:
sudo aptitude install mysql-workbench

A web developer grows with the projects he creates and develops. With the growth of projects, the complexity of the software part increases, the amount of data processed by it inevitably increases, as well as data schema complexity. Communication with other web developers shows that MySQL databases are very popular among us, and 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 large quantity tables and relationships, the possibilities of PHPMyAdmin are sorely lacking. So I decided to write a review MySQL Workbench- a wonderful free desktop program for working with MySQL.

In the first part of the review, I will cover the very basics of working with the program, so you can use this article as novice user guide. The second part will be devoted to 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 visual database design tool that integrates the 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 quickly and with pleasure throw project data schemas, design entities and relationships 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, resembling funny cockroaches, allows you to see the big picture of the data model and enjoy its lightness and elegance :) After the first try, this tool becomes 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 this 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
• MacOS X

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

Beginning of work

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

At the top of the screen is a list of connections to your projects' MySQL servers, and a list of recently opened data models is at the bottom of the screen. Work usually begins with creating a data schema or loading an existing structure in 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 name of the database, 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 Schemas". To create a table, double click on "+Add Table":

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

Index management

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

We enter the name of the index, select its type, then tick 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 ticked. In this example, I have added 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 in each table there is a tab "Foreign Keys":

To add a link, 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 entry:

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

In the above example, I added to the child table UserProfile foreign key to link to parent table user. When editing a field userId and removing positions from the table user similar changes will automatically happen to related records from the table UserProfile.

When creating a project, it is often necessary to add start data to the database. These can be root categories, administrator users, and so on. There is a tab for this in MySQL Workbench table management "Inserts":

As you can see from the example, if you need to apply some MySQL function 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 represent the data schema, entities and their relationships in a 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 "+AddDiagram":

In its interface, you can create and edit tables, add links 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).

Import 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 the model from the SQL file, select "File → Import → Reverse Engineer MySQL Create Script...", then select the desired SQL file and click "Execute >"

MySQL Workbench also provides for importing and synchronizing the data model directly with a remote server. For this you 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!