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 database MySQL data? 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:

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




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




3. 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:




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



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




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




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

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 the world database as the active one.

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

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 there is an error in the execution of one of the expressions.

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:

A comment -- SHOW TABLES; SELECT * FROM Country;

IN this 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;

Database creation

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 a graphical table creation tool. 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, a new record is inserted into the table. 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 you to UPDATE requests and DELETE without specifying the 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;

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 a similar 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 a 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 Database. 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 communication line ( dotted line) tables, the “relationship”, primary key, and 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 we 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 of the program, 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 forgotten either - for them there is an assembly only for the 32-bit OS;
• Well, of course you can download source 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. For installation 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, during my MySQL installations WorkBench encountered the following error:
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 option mysql-workbench-gpl (--install):
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 also need additional packages who are 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

How to create a MySQL database structure? How to create MySQL tables? MySQL Workbench database creation program!

How to create a MySQL database structure using MySQL Workbench

Want to create your own database, but are you tired of creating tables and relationships between them using SQL? Use 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 database structure in SQL; MySQL Workbench will generate the code for you! You can download the program for free from the website: http://www.mysql.com/downloads/workbench, you can download both the installation version and the one that only requires 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 create a table with attributes - so click on the "Add Table" button.

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

When you create tables, you have to think about how they will relate to each other.

If you have completed all the tables, click on the "Add Diagram" button to define the relationships between subjects.

You will see a window similar to the one below, which shows the created table in the chart workspace.

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

Now join the table to form relationships.

Let's say they look like this:

Book may belong to one reader

The reader may occupy several books

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

So 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 right option, the data is mainly tables, and users (if such are created). The file I created is shown below.

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 big amount tables and relationships, PHPMyAdmin capabilities 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 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 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":

Will open user-friendly interface to edit 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 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 represent the schema of data, entities and their relationships in graphical form There is an EER diagram editor in MySQL Workbench. 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 SQL file and choose "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!