MySQL is a query in the query. MySQL: examples of queries. Nested MySQL queries

At present, every person can observe a rapid growth in the volume of digital information. And since most of this information is important, there is a need to save it on digital media for later use. In this situation, modern technologies such as databases can be used. They provide reliable storage of any digital information, and access to data can be carried out anywhere in the world. One of the technologies considered is the MySQL database management system.

DBMS MySQL - what is it?

The relational database management system MySQL is one of the most popular and often used technologies of information storage. Its functionality is superior in many respects to existing DBMS. In particular, one of the main features is the ability to use nested MySQL queries.

Therefore, many projects where time is important and it is necessary to ensure the storage of information, as well as carry out complex data samples, are developed on the basis of the MySQL database. Most of these developments are Internet sites. At the same time, MySQL is actively implementing when implementing both small (blogs, site-business cards, etc.), and fairly large tasks (online stores, data storage , etc.). In both cases, a MySQL query is used to display information on the site page. In the request, the developers try to make the most of the available opportunities provided by the database management system.

How data storage should be organized

For convenient storage and subsequent processing, the data is necessarily arranged. The data structure allows you to determine how the tables used to store information will look. The database tables are a set of fields (columns) that are responsible for each particular property of the data object.

For example, if a table of employees of a certain company is being compiled, then its simplest structure will have the following form. Each employee is assigned a unique number, which, as a rule, is used as the primary key to the table. Then the employee's personal data is entered in the table. It can be anything: the name, the number of the department for which it is fixed, telephone, address and so on. According to the normalization requirements (6 normal database forms), and also for MySQL queries structured, the table fields must be atomic, that is, do not have enumerations or lists. Therefore, as a rule, in the table there are separate fields for a surname, a name, etc.

The above is a trivial example of the database table structure. However, it still does not fully meet the basic requirements of normalization. In real systems, an additional department table is created. Therefore, the table above instead of words in the column "Department" should contain the numbers of departments.

How the data is sampled

To get data from tables in the DBMS, use a special MySQL command - the Select query . In order for the database server to correctly respond to the request, the request must be correctly formed. The query structure is formed as follows. Any access to the database server begins with the keyword select . It was from him All are built in MySQL queries. Examples can have different complexity, but the principle of construction is very similar.

Then you need to specify which fields you want to select the information of interest. The fields are enumerated after comma after the select clause. After all the necessary fields have been listed, the query specifies the object of the table from which the selection will be made, using the from clause and specifying the table name.

To restrict the sample, special operators are added to the MySQL queries provided by the DBMS. The sample unique (unique) data is used distinct , and to set the conditions - where . As an example, applicable to the above table, you can consider a request that requires information about the name. Employees working in the sales department. The structure of the query will look like the table below.

The concept of a nested query

But the main feature of the DBMS, as mentioned above, is the ability to process nested MySQL queries. What should it look like? From the name it is logically clear that this is a query formed in a specific hierarchy of two or more queries. In theory to study the features of the DBMS it is said that MySQL does not impose restrictions on the number of MySQL queries that can be nested in the main query. However, you can experiment in practice and make sure that after the second ten nested queries the response time will seriously increase. In any case, in practice there are no tasks that require an extremely complex MySQL query. The query may require up to 3-5 nested hierarchies.

Building nested queries

When analyzing the information read, a number of questions arise about where the nested queries can be used and whether it is impossible to solve the problem by splitting them into simple ones without complicating the structure. In practice, nested queries are used to solve complex problems. This type of problem includes situations in which the condition is unknown beforehand, according to which the restriction of further sampling of values will occur. It is impossible to solve such problems if you just use the usual MySQL query. In a query consisting of hierarchies, there will be a search for constraints that can change over time or in advance can not be known.

If we consider the table given above, then the following example can be cited as a complicated problem. Let's say that we need to find out the basic information about the employees who are subordinated to Grigory Grigoryevich Grishin, who is the head of the sales department. When creating a request, we do not know its identification number. Therefore, initially we need to know it. To do this, a simple query is used that will help you find the solution to the main condition and complement the main MySQL query. The query clearly shows that the subquery receives the employee's identification number, which then determines the restriction of the main request:

In this case, the any clause is used to exclude the occurrence of errors if there are several employees with such initials.

Results

To summarize, it should be noted that there are many other additional features that make it much easier to build queries, since MySQL is a powerful tool with a rich arsenal of tools for storing and processing data.