Working with MySQL using dbForge Studio

KV.by

More than 20 thousand vacancies opened for IT specialists in the last quarter of 2012 require knowledge of the SQL query language from candidates.
Knowledge of SQL is the most common requirement for IT professionals, despite the fact that in Q4 2012 the need for database specialists decreased by 1.4% compared to Q3 2012. However, there were 20,660 job openings in Knowledge of SQL is listed among the employee skill requirements.
British recruitment company CW Jobs

You can design a database using SQL commands using MySQL tools directly, but this option is quite painstaking and time-consuming, so there are many software products that greatly facilitate this difficult work.

One such flexible, professional tool for MySQL developers and users is dbForge Studio for SQL from Devart. With its help, routine tasks of MySQL development and administration are automated: designing a database, entering and editing table data, creating and executing SQL scripts, queries and triggers, etc. The program is free for private non-commercial use and for educational institutions.

The program has Russian localization, but its reference manual (by the way, quite good) is only available in English. Once on the program forum I read an explanation from the program developers about the lack of a Russian version of the help. Its essence is this: we already provide the opportunity to use the program for free, so to demand that we also localize (and support) the help, to put it mildly, is not correct, which, in my opinion, is completely fair. On the other hand, it would be nice to have some idea about the product in Russian, especially since I have not seen any literature on it (although I admit that it exists somewhere). Therefore, I hope that this article will help someone understand this quite good software product.

The article discusses the latest version of the program at the time of writing, 6.0.265.

Program interface

The main program window is divided into several areas (panels):

Database Explorer. A list of database objects, structured in the form of a tree, grouped into functional groups.
Properties. Displays the properties of the selected object.
Main window. The working window in which all actions with the database are carried out.
Project.
Output window. Display system messages.

Please note that the panel information is not always automatically updated. Therefore, if you do not observe any changes made, then try calling up the context menu on the connection name in the program explorer panel and selecting the “Update” item from it, or pressing the function key, or clicking the “Update circuit information” button on the toolbar of the explorer window.

Connecting to MySQL server

After downloading the program, you should connect to the server. If this is the first time, then on the toolbar of the Explorer window you need to click the “New connection” button, after which the “Database connection properties” window will appear, in which you specify the parameters for connecting to the server:

IP address of the host where the database is located.
Port (default "3306").
Username.
His password.
You can also specify a database to automatically work with after establishing a connection.

To check the correctness of the entered parameters, click the “Check” button. If the connection to the server is successful, the following message will appear: “Connection established.”

After successfully connecting to the server, a list of databases on the server will appear in the program explorer panel, with the name of the connection at the root level. At the same time, a message about establishing a connection to the server appears in the output window.

The next time you boot dbForge Studio for SQL, you do not need to create a new connection, you just need to reconnect to the server. This can be done in different ways, for example, by calling the context menu on the connection name in the program explorer panel and selecting “Open (connection)” from it, or simply double-clicking on the connection name.

Database creation

To create a new database, execute the command Database > New Database..., after which the “Create a new database” window will appear.
In the “Name” field, enter a custom name.
In the “Encoding” list you can set the data encoding. Naturally, when working with Cyrillic, you should select the Russian encoding for Windows “cp 1251”.
In the “Matching” list, you set a rule for working with table data. For example, to work with data in Russian, the set for “cp 1251_general_ci” is selected. At the same time, the comparison allows you to “detail” the selected encoding, choosing, for example, the rules for the Bulgarian or Ukrainian language.

Let me digress a little from the main topic. The (default) settings in the window are not random; they are determined when configuring MySQL. It is there that if you plan to work with data in Russian, you should set the following setting:

character-set-server=cp1251

After creating a new database in dbForge Studio for SQL, its name should appear in the Explorer window.

Database properties, in particular the set of characters for working with the database and the set of rules for working, are displayed in the properties panel when you place the cursor on the database name in the Explorer window.

Working with tables

Database tables are accessed in the “Tables” node of the corresponding database in the “Explorer” panel.

Creating a table

Creating a new table is done like this:

We expand the node with the name of the desired database (expanding and collapsing a node is carried out in the same way as in the “regular” Windows Explorer, that is, by clicking on the “+” or “-” symbol in front of the database name.
Call the context menu on the “Tables” item and select “New Table” from it.
A table tab window will appear in the working window, which in turn contains several tabs. On the “General” tab, you set the general attributes of the table: its name, data type, and here you can, if necessary, change the database for which the table is created. The table data type, again, is set automatically according to the MySQL settings.
On the same “General” tab, you enter data about the structure of the table, that is. about all its fields. The new field is created automatically, or you can add it “explicitly” by running the Table > New Column command. In general, the main menu item of the Table program is intended specifically for working with a table, including actions that allow you to change the structure of the table: add, delete, insert, edit table fields.

Key field properties:

Primary key. Defining a field as a key.
Empty values. Allow or prohibit the presence of empty values in a field.
No sign. Prohibition of entering negative numbers into the field.
Binary. Setting this property specifies that the values in this field will be case sensitive.
Autoincrement. Automatically increases the value in the field by “1” when adding a new entry. Applies to integer data type (INT) fields.

To save the table structure, use the File > Save command or click the corresponding icon on the program’s standard toolbar.

Below the table structure there is a “SQL” window, which displays the SQL command automatically generated by the program when creating the table based on the data we entered. Please note that the text of the SQL command is not editable.

Further access to the table is carried out by double-clicking on its name in Explorer, after which its structure is displayed in the main window.

Changing the structure of the table (the composition of fields or their properties) is also carried out on the “General” tab.

A summary of a table can be displayed in the properties panel by double-clicking the table name in the Explorer panel.

Data input

To enter data, select the desired table in Explorer, and in the tab window with the table, go to the “Data” tab.

A new line (record) is added automatically when you press the <down> function key with the cursor positioned on the last record. The transition from field to field is carried out using the navigation keys, or . If the data is not completely visible visually in a field, you can enlarge it by dragging its right border or double-clicking it in the field header row.

You can move through the table data using the navigation keys. The lower part of the table window also contains a navigation bar, which is convenient to use when moving around a table with a large number of records.

I note that after entering data into the table, you need to save it, that is, run the File > Save command or click the corresponding icon on the program’s standard toolbar. However, if you forget about this, the program itself will remind you of the presence of unsaved data when you end your session with it.

Working with requests

Building a Query

Run the command File > New > Query or on the standard toolbar of the program click the create new query button, after which the “Query.sql” tab will appear in the main window.
Drag the tables from which you want to select information from the Explorer window to the “Query.sql” tab.
Generate request parameters, which will be discussed below.
If necessary, enter a meaningful name for the field in the “Nickname” column. In this case, this is what will be displayed in the output (otherwise the field name is displayed).
Save the query by executing the File > Save Query.sql command or by clicking the corresponding icon on the toolbar. The “Save File As” window will appear, in which you should specify the name of the request, which can also be in Russian. The query file has a SQL extension.

Create a select query

The simplest type of query is a select query. Displaying and generating a list of fields included in the selection is carried out on the “Selection” tab. To include field values in a query, simply check the label field with the field name in the area of the window displaying the structure of the selected tables. Another option for generating a query is to select fields from the drop-down list in the “Column” column. This is how compound expressions are added to the query, containing, for example, functions or representing an expression from several fields.

The query results will be displayed in the exact order they appear in the Column column. If there is a need to display certain information only once, then you need to check the box for the “Unique Rows” label field. To view the results of the query while it is being generated (without remembering), you need to click the “Run” button.

Selection by criterion

Imposing a condition on the values of a field or several fields is done on the “Conditions” tab.

To add a condition, click the button. This will produce text that contains the phrase "Enter a value" twice, separated by an "=" sign.

The first text "Enter a value" is the left side of the condition. This is a field (column name) of some table.
The "=" sign is a conditional operator. It can be replaced with another one. To do this, click on it and select something else from the list that appears, for example, “>”.
The text "Enter a value" to the right of the "=" sign is the value of the condition. Clicking on the “Enter a value” text displays a list of table fields, various expressions, etc., which can be thought of as a condition.

For example, to select students who have a grade of “5”, the condition can be written as “Grade”=5.

There may be several conditions. To add a new condition, you also need to click the button and create a new condition according to the above rules.

By default, conditions are connected together using the logical operator AND, however, this can also be changed by clicking on it and selecting another from the list.

Creating relationships (merging tables)

In a MySQL database, you cannot hardcode relationships between tables one time. The user must create them in each query using SELECT and JOIN. The program allows you to create five types of connections, including internal, left-sided and right-sided joins.

Merging two tables is done by merging the required table fields with the mouse. The created connection is displayed graphically. The same actions can be performed on the “Connections” tab. To do this, click the button, after which an empty JOIN statement will appear. Click on the text “Specify a column name” and add the fields of the tables to be joined to the corresponding fields.

The SELECT command code that displays the creation of connections also appears on the Connections tab.

To change the type of connection, you should call the context menu on the type (name) of the connection, for example, “Inner Join” and select the desired one from it.

You can delete a connection in different ways, for example, by calling up the context menu on the graphic connection line and selecting “Delete from diagram” from it.

Computational fields

Quite often, the output of query results should contain not just the values of one of the table fields, but a combination of field values from one or more tables or a mathematical operation on them. For example, the query should calculate the total cost of an item, which is defined as the product of the total quantity of the item and the price per unit of the item. To do this, you need to create a calculation field. For example, creating the product of the “Price” and “Quantity” fields is done like this.

Go to the "Sampling" tab.
Click on an empty line in the “Column” field.
Select the “Price” field.
At the bottom of the selection window, click the “*” operator icon.
From the corresponding table, select the “Quantity” field, after which an expression like Price*Quantity will appear in the “Column” field.
To create a meaningful signature for a computational field, replace the expression “Exp” with “Total” in the “Alias” field.

Arranging (sorting) records

If it is necessary to organize the selected data by a specific field, then, as is known, sorting is used. Defining sort fields is done on the “Sorting” tab. Here, the Columns list contains a list of all the fields included in the query. Double-clicking any of them or clicking the Add Column button moves that field to the Sort list, which determines the sort fields and the order in which the records are arranged. The default sort method is Ascending, but you can easily change it to Descending by clicking the Sort by... button.

The number of sorting fields is not limited, which allows for multi-level sorting of the output results, arranging it by several fields at once. In this case, the fields for which sorting is performed should be placed in the list in the exact order in which it will be performed.

Grouping records and aggregate functions

The grouping action makes it possible to combine table records that are identical in some way for a specific field into groups and apply calculations to them using different functions.

Grouping of records is specified on the “Grouping” tab.

Adding a function for a field is done on the “Selection” tab like this:

Click the new field button in the Column column.
In the “Function” list, select the desired function, after which its name will appear in the “Column” column.
The desired field from the “Table” list is selected as the function argument.

Let me remind you that aggregate functions include the following:

The Sum function provides the ability to calculate the sum for a group of records;
function Avg - calculate the average value;
Count — count the total number of records;
Max and Min—define the maximum and minimum values in the field.

So, if you group students by their last name, grade book number, etc., then in a table that contains data with student grades, using the Sum function you can calculate the total amount of grades for each student, and using the Avg function you can calculate it GPA.

Using Aggregate Functions in Conditions

In the conditions in the HAVING clause, you can use aggregate functions that operate within the groups being created. This is done on the “Enable” tab.

For example, to select the grades of students in a group who have grades above the average score, you can use two options:

Rating > AVG(Rating)

Score - AVG(Score) > 0

At the same time, on the “Grouping” tab, grouping by students must be specified.

Executing the request

To execute a request, you must first open it. To do this, click the “Open” button on the program’s standard toolbar. To run a query, click the button in the Query Panel.

Formation of triggers

As you know, a trigger is a procedure (a sequence of SQL statements) that is activated during data manipulation operations (adding, replacing, and deleting) on tables. Triggers are one of the mechanisms for maintaining database integrity.

dbForge Studio for SQL provides automated tools for creating triggers.

A trigger is created using the following algorithm:

In the Explorer panel, call up the context menu for the “Triggers” item and select “New Trigger” from it. The trigger generation window will appear.
On the “General” tab, the general details of the trigger are defined: name, owner and table, which are the database and table, respectively, for which the trigger is created.
In the “Trigger body” field on the same tab, the developer independently creates a sequence of operators that will be executed during the data manipulation operation. They are located between the BEGIN and END statements.
The “Event” tab determines what data manipulation operation the trigger is intended for, as well as the moment it fires: before or after the event.

The “SQL” tab displays the script for the trigger automatically generated by the system.

Backup/Restore

When working with a computer, you must always remember the possibility of emergency situations: power outages, device failures, etc. If the database contains a significant amount of information, then the loss or destruction of data can be perceived as the “end of the world.” Therefore, the first rule when working with any system is to have a backup copy of the data, which allows you to restore the data if necessary. A copy can also be useful if you need to return to previous data.

A backup copy is created using the command Database > Backup > Create DB backup. The archive is created with the SQL extension, and its name contains the name of the database for which it is created and the time of its creation, for example: “enterprise 20101117 1559”.

To restore the database, it is desirable to have it on the server. However, it can be created automatically during the recovery process. After this, you should call the context menu on the name of the database and sequentially select the items Backup, Restore DB from backup.

Actions to create and restore a backup copy are carried out under the control of a wizard program and do not cause any difficulties. The only thing I would like to note is that when creating a backup copy, make sure that the desired database is selected in the “Database” field.

And one more note. It should be borne in mind that if the database is transferred from computer to computer, then objects such as tables with all their contents and triggers will be restored correctly and without problems. But for each request, the first time you run it on another computer, you will need to provide confirmation about transferring it to the new server.

And one last thing.

When working with dbForge Studio for SQL, users often note the presence of bugs in it. In this regard, I would like to note the following: firstly, the task itself set by the program developers, namely, the automatic generation of SQL commands when visually designing a database, is quite difficult to implement (despite the formality of the generation algorithms), secondly , the bugs themselves are constantly being fixed, and, thirdly, the program is free, and, as you know, you don’t look a gift horse in the mouth...

And the very last thing. I prefaced the article with an excerpt from the British recruiting company CW Jobs, which noted the high demand for IT specialists who know the SQL language. It follows from the article that the program automatically generates SQL commands based on the entered data, and this will allow novice developers to track the correct code required to create database objects.

Valery FETISOV

What is MySql?

MySql is a free database management system (DBMS). The company that developed this DBMS developed it purely for its own needs, this was in the early 90s, but subsequently this software product went beyond the scope of this company and gained enormous popularity due to its simplicity and compactness, as far as I know , the first distributions of this server were approximately 4 megabytes!

The note! What is a DBMS.

MySql can be run on different platforms, for example: Windows, Linux, Mac OS X, FreeBSD, HP-UX, Solaris and others . And this also arouses great interest among everyone in this DBMS.

MySql has gained wide popularity on the Internet as a database server. Of course, compared to other paid DBMSs, such as Oracle or Microsoft SQL Server, MySql loses a little, but in most (and for web masters in almost all) cases it fully satisfies all needs. By the way, MySql today belongs to Oracle, so we can conclude that MySql will live for a long time!

Let's go further, I recommend everyone to use versions of MySql no lower than 5.1 , since versions below are simply an order of magnitude inferior to versions such as 5.5. You can simply forget about versions 3 and 4, this is already the last century, and if you are offered to use these versions (for example, by a hosting provider), then refuse and look for another hosting.

Now a couple of tips on how you can and should use MySql when developing a website. By the way, MySql is supported by almost all popular programming languages, for example: Delphi, C, C++, Java, Perl, PHP, Python, Ruby and others.

Some people think that MySql is purely a database and only data is stored in it and that’s it, it can’t do anything else. In fact, this is not so, MySql has almost all the capabilities that a real DBMS should have: the ability to write your own procedures, functions and much more. And you should use this, as it will simply have a great effect on the operation of the site (for example, in the speed of its operation). So, my advice to you is that when writing a web application, use only calling procedures and functions in the code, and do not use entire sql queries, because, firstly, it is much safer, and secondly, it is much faster. For example, when you send an sql request to the server, the server first checks it for syntax, then executes the request, then transmits the data back, and when you call the procedure, everything is already compiled, so to speak, and executed purely on the server, and the server returns the result, the result is much faster than with a regular request.

If you are interested in this DBMS and want to install it on your computer, this is done very simply, now I will tell you a little about it. But from the beginning, I’ll tell you where you can download it, there is only one answer, only on the official MySql website, for example, for Windows you can download it here, by the way, the advice applies not only to MySql, but to all software.