How mysql workbench 6.3 works 7. MySQL Workbench basics: quick start, data schema management

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 bases are very popular among us. MySQL data, and to manage them - the notorious 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 is 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 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, reminiscent of 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. V 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 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 entry
• 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 schema of data, 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 "+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 remote access connection 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 (DB) was greatly simplified. After all, what previously had to be done manually using a SQL script and command line, can now be done "in visual mode" with a friendly GUI.

Consider the process of creating a database using MySQL Workbench in more detail.

The screenshot below shows general form MySQL Workbench window.

In order to create a database, in the left panel in the area with the list of databases (marked as SCHEMAS) click right click mice and context menu select "Create Schema".

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

After that, to continue creating the database, you should click the "Apply" button. The dialog box that appears will show the generated MySQL Workbench SQL database creation script. If necessary, this script can be edited directly in this window.

There is an Online DDL area at the top of the window. It is intended for setting script execution parameters. These options can be useful when manipulating an already existing database. When creating a database, it is recommended to leave the default values ​​(“Default”).

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 the help of one a 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 new model, this is done through the menu or the key combination 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:

How to create a MySQL database structure? How to create MySQL tables? Program to create database MySQL Workbench!

How to create a MySQL database structure using MySQL Workbench

Do you want to create your own database, but are you tired of creating tables and relationships between them using SQL? Use your free software MySQL Workbench, which was created to visually create databases.

MySQL Workbench allows you to model a MySQL database using a visual representation of tables. This eliminates the need to painstakingly describe the structure of the database, in SQL, the MySQL Workbench program will generate the code for you! You can download the program for free on the website: http://www.mysql.com/downloads/workbench, you can download it as installation version, and the one that requires only unpacking ( available systems include: Windows, Ubuntu Linux, Fedora, Mac OS X).

How to use a program to create a MySQL database?

Open MySQL Workbench, select File -> New Model, or press CTRL + N. The database modeling area is shown in the image below:

The first thing you should do is to create a table with attributes - so click on the "Add Table" button.

Fill in the appropriate fields: table name, attributes (remember that one of these must be the master key - indicated by the checkbox, PK "primary key".).

When you create tables, you must think about how they will be related to each other.

If you have filled in all the tables, click on the "Add diagram" button to define the relationship between the subjects.

You will see a window similar to the one below showing the created table in the Chart Stage.

My database structure will not be correct as I am only showing here how to model a database structure. Therefore, you can expand the tables in the work area.

Now join the table in order to form a relationship.

Suppose they look like this:

Book, can belong to one reader

The reader can borrow several books

Typically, there are three options that allow you to create a log (1:1, 1 for many, and many to many):

Thus, we create connections, as shown in the figure:

If you double click on the relationship, you will be able to set additional options.

When you are done creating the structure, you can create the base SQL data just by importing it. To do this, select the menu File -> Export -> and select the desired option, the data is mainly tables, and users (if any). The file I created is shown below.