MySQL Workbench - software product, 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

Open the Start menu and type 'Workbench'. Select MySQL Workbench from the list.

A window called the home screen will open.

Home screen

At the top you can see a list of connections to the DBMS. These controls are used to connect to the DBMS and provide direct control.

At the bottom there is a list models. Models are used to visually design databases.

Connections

“Tile” connections

When you hover the mouse over a connection, the corner of the “tile” bends. If you click on this “corner”, information about the connection will be displayed: DBMS version, date of last use, DBMS server address, login, etc.

Connection information

In the lower right corner there is a ‘Connect’ button that will open this connection.

The connection can also be opened by clicking on the “tile” itself.

Open the first connection in the list.

SQL Query Editor

After opening the connection, the SQL Query Editor window opens.

SQL Query Editor Window

In the center we see the query editing window.

You can return to the home screen by clicking on the icon in the upper left corner of the window.

On the left is a navigator displaying the main tasks and a list of database objects.

In the lower left corner there is an information window about the selected object.

There is a help window on the right.

Below is the request history window.

In the top right corner are controls that allow you to hide or show sidebars.

Executing queries

SQL Editor - SQL Query Panel

Let's run a query against the world database. First, let's select a base world data as active.

To do this, find the world object in the navigator and select it by double-clicking, or by right-clicking and context menu by selecting Set as Default Schema.

Now you can query the world database.

Comment

You can also select the active database by running the request

USE schema_name;

For example,

USE world;

First, let's request a list of tables in the selected database. To do this, in the SQL query editing window, we write

SHOW TABLES ;

When you enter a query, the help window automatically displays hints on the syntax of the query you are entering, if this feature is enabled. To enable automatic help, click on the third panel button from the left in the help window.

Then click on the query execution button (yellow lightning), or select Query → Execute (All or Selection) from the menu. Keyboard shortcut– ctrl + shift + enter

Other SQL Query Window Panel Buttons
SQL Editor - Toolbar
From left to right, buttons:

Open an SQL Script File: Loads the contents of a previously saved request from a file.

Save SQL Script to File: Saves the request to a file

Execute SQL Script: Executes the selected part of the request, or the entire request if nothing is selected.

Execute Current SQL script : Executes the expression where the text cursor is located.

- **Explain (All or Selection)**: Displays information about the operation of the optimizer for the selected part of the query or the entire query.
Stop the query being executed: Aborts the current request.

Toggle whether execution of SQL script should continue after failed statements: Toggles the behavior for errors in expressions. If a red circle is displayed on the button, then the execution of the request is interrupted if the execution of one of the expressions fails.

Otherwise, if a green arrow is displayed, expressions with errors are skipped and the query continues.

Commit: Confirms the current transaction

Rollback: Cancels the current transaction

Toggle Auto-Commit Mode: If enabled, each expression will be automatically validated.

Set Limit for Executed Queries: Limit the number of query results.

Beautify SQL: Format the request text.

Find panel: Display the search bar in the query body.

Invisible characters: Display “invisible” characters.

Wrapping: Enable word wrapping.

The query result window will open.

In the center of the result window is the result of the query - in our case, a list of table names. On the right are buttons for selecting a display style:

Result Grid– in table form (default)
Form Editor- in the form of a form. Each entry opens on a separate page, with arrows at the top of the window to navigate through the entries.
Field Types– displays the result attribute types.
Query Stats– displays various request statistics
Execution Path– shows the algorithm of the optimizer

Let's see how the Country table is structured. To do this, run the command

SELECT * FROM Country;

and select the result display style Field Types.

Comment

Running an execution request starts All expressions that are written in the query window. If several expressions are written in the query window, separated by semicolons, they will be executed sequentially. Therefore, delete or comment on already completed requests.

Comments in SQL begin with a double hyphen, for example:

Comment -- SHOW TABLES; SELECT * FROM Country;

IN at the moment we are interested in the attributes (columns) Name - the name of the country and LifeExpectancy - the average life expectancy of citizens.

Let us display a list of countries with an average life expectancy of citizens of more than 80 years. To do this, let's run the request

Another query you can run on the Country table is the number of countries by form of government.

SELECT GovernmentForm, COUNT (Name) FROM Country GROUP BY GovernmentForm;

Creating a Database

To create a database you can run a query

CREATE DATABASE db_name;

Create a database called myFirstDatabase:

CREATE DATABASE myFirstDatabase;

To display the created database in the list of objects, right-click on an empty space in the list and select Refresh All.

Select myFirstDatabase as active.

Creating a table

Let's create a table People, consisting of columns id, name, birthday:

CREATE TABLE People ( id BIGINT AUTO_INCREMENT PRIMARY KEY , name TINYTEXT NOT NULL birthday DATE );

Note There is graphic tool creating tables. We will analyze it when we work with the graphical database design tool.

Let's check that the table has been created:

SHOW TABLES ;

* FROM People;

Adding and editing data

By selecting all columns of a table, Workbench allows you to edit records directly through the query result interface. Select the Form Editor result display style, and enter the values for the name and birthday attributes. The latter is entered in the format YYYY-MM-DD, for example for September 1, 2015, enter 2015-09-01.

Leave the id field empty.

In the lower right corner of the result window you can see the Apply and Revert buttons. The first will generate and execute the SQL INSERT query, and the second will discard the changes.

Click on Apply, view the generated request, and apply it. If everything is done correctly, then the table is inserted new entry. Complete your request

SELECT * FROM People;

again to make sure of this.

Note You can also add and edit records in the table display of the result.

Deleting data

Data can be deleted from the results window using the Delete selected rows panel button.

Another way is to run an SQL query

For example, let’s delete a record with some id value from the People table:

Substitute one of the existing values instead of 1.

Note By default, Workbench runs queries with the SQL_SAFE_UPDATES option. This option does not allow UPDATE and DELETE queries to be made without specifying a WHERE clause with the primary key (in this case, id).

Import and export

Export

In the navigator, select Data Export. Select the databases and tables you want to export. If necessary, check other objects that you want to export: Dump Stored Procedures and Functions, Dump Events, Dump Triggers.

Select Export to Self-Contained File and the file where the export will be saved.

Do not check Include Create Schema: this option will include the CREATE DATABASE statement in the export.

Click on the Start Export button.

Import

In the navigator, select Data Import/Restore. Select Import from Self-Contained File.

Select the database to import into from the Default Target Schema drop-down list. You can also create a new database by clicking on the New... button.

Click Start Import.

Removing tables

A query is used to delete tables

DROP TABLE tbl_name;

Let's delete the People table

DROP TABLE People;

Deleting a database

To delete a database, use a query

DROP DATABASE tbl_name;

Let's delete the table myFirstDatabase

DROP DATABASE myFirstDatabase;

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

Gradually you begin to delve into every detail, but training material is not always easy to find. Plus, not everyone knows about the existence useful programs.

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

It was created especially for this free program Workbench. With its help you can visually create MySQL databases. Modeling them using software is easier, more convenient and much faster.

Workbench will help you create a MySQL database

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

After normal installation of the program, to create a MySQL database you need to do the following:

First you need to open a new model, this is done through the menu or using the Ctrl+N key combination:

The first step when creating a database is to add a table, so we select the appropriate function:

Next, the table is filled in. Specify the name and attributes, keep in mind that one of the attributes is the main key, marked with a checkbox. Think in advance about how the tables will then be connected to each other:

After filling in the required data, create a diagram to identify relationships between subjects:

You will see a table in the work area. For convenience, you can expand the table structures:

Now you need to set connections between elements; this is done with a special tool on working panel:
As a result, you should have tables presented in the workspace and relationships established between them:

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

Whatever the database developer is: a beginner (especially) or a bearded professional, it is always easier and more visual for him to present what he is working on and developing. Personally, I consider myself to be in the first category and to understand the material I would like to see visually what I am designing/developing.

Today, there are various programs and tools that cope with this task: some are better, some are 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 LGPL license) and S.E. - Standard Edition- the version for which the developers ask for money. But I think that for many it will be enough O.S.S. version (especially for beginners and those who do not want or consider it inappropriate to pay for software, as well as supporters of open source programs), Moreover, the OSS version has rich functionality.

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

Thus, any MySQL developer will find what he needs. Besides 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 databases. In other words, this is a visual representation of all the relationships between the tables of your database, which, if necessary, can easily be presented in the form of an SQL script, edited or created a new view. But more on that a little 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 charts, 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 the cursor over the connection line (dashed line) of the tables, then the “relationship”, the primary key, as well as the foreign key will be highlighted in a different color. If you hover the cursor over a table, the table itself will be highlighted, as well as all the relationships belonging to the selected table.

In order to edit a 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 a SQL script, just right-click on the table you need and choose " Copy SQL to Clipboard", and then paste from the clipboard into 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 programs, as well as an archive with source code. For this OS MySQL WorkBench can only be downloaded for the 32-bit version of Windows;
for users Ubuntu the choice is a little richer than for Windows OS 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 these are Fedora, Suse Linux and RedHat/Oracle Linux, MySQL WorkBench assemblies for 32- and 64-bit OS are presented;
Macintosh users have not been forgotten either - for them there is an assembly only for the 32-bit OS;
Well, of course you can download source code programs;

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

What Linux users need to remember and know:

Naturally, as in the case of Windows OS, we do not forget about 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 probably tell you which packages are missing in your OS. Read about this below.

Unfortunately, I don’t know how things are with rmp-base distributions, because... I’ve never used such distributions, but I think it’s about the same as with debian-based ones.

You may have noticed that the assembly is missing MySQL WorkBench for OS Debian GNU/Linux. But, as practice has shown, it’s okay. To install MySQL WorkBench in Debian 6.0 (Squeeze) we will use deb- package for Ubuntu 10.04(don’t forget about the bit depth 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 command in the console as root:

# dpkg -i mysql-workbench-gpl-5.2.33b-1ubu1004-amd64.deb For example, I got the following error while installing MySQL WorkBench:
dpkg: package dependencies prevent the mysql-workbench-gpl package from being configured:
mysql-workbench-gpl depends on libcairomm-1.0-1 (>= 1.6.4), however:
The libcairomm-1.0-1 package 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:
The libgtkmm-2.4-1c2a package is not installed.
mysql-workbench-gpl depends on libpangomm-1.4-1 (>= 2.26.0), however:
The libpangomm-1.4-1 package 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 the mysql-workbench-gpl (--install) option:
dependency problems - leave unconfigured
Errors occurred while processing the following packages:
mysql-workbench-gpl

To resolve this error, all I had to do was type the 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 need additional packages, which the manager apt will kindly offer to download. After installing all the necessary packages, MySQL WorkBench installs without problems.

That's it: MySQL WorkBench is safely installed and ready to learn.

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

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 the 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 section 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 section 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 email address store.

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 complies with 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 Khabranovites 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: