MySQL is the query in the query. MySQL: query examples. MySQL Nested Queries

Currently, everyone can observe the rapid growth of digital information. And since most of this information is important, it becomes necessary to store it on digital media for future use. In this situation, modern technologies such as databases can be applied. They provide reliable storage of any digital information, and access to data can be carried out anywhere in the world. One of the technologies under consideration is the MySQL database management system.

MySQL DBMS - what is it?

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

Therefore, many projects where speed is important and it is necessary to ensure the storage of information, as well as to perform complex data sampling, are developed on the basis of the MySQL DBMS. Most of these developments are Internet sites. At the same time, MySQL is actively introducing itself in the implementation of both small (blogs, business card sites, 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 that the database management system provides.

How should data storage be organized?

For convenient storage and subsequent processing, the data must be ordered. The data structure allows you to determine how the tables used to store information will look like. Database tables are a set of fields (columns) that are responsible for each specific property of a data object.

For example, if a table of employees of a certain company is compiled, then its simplest structure will be as follows. 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 into the table. It can be anything: F. I. O., the number of the department to which it is assigned, telephone, address, etc. According to the requirements of normalization (6 normal forms of databases), as well as for MySQL queries to be structured in a structured manner, the fields of the table must be atomic, that is, without enumerations or lists. Therefore, as a rule, in the table there are separate fields for the last name, first name, etc.

The above is a trivial example of a 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 above table instead of the words in the column "Department" should contain the numbers of departments.

How data is sampled

To obtain data from tables in the DBMS, a special MySQL command is used - the Select query . In order for the database server to correctly respond to the request, the request must be correctly formed. The request structure is formed as follows. Any call to the database server begins with the select keyword. From him all are built in MySQL queries. Examples can have varying complexity, but the construction principle is very similar.

Then you need to specify from which fields you want to select the information of interest. Enumeration of the fields occurs comma after the select clause. After all the required fields have been listed, the query indicates the table object from which the selection will occur, using the from clause and specifying the table name.

To limit the selection, special statements provided by the DBMS are added to MySQL queries. The distinct clause is used to select non-repeating (unique) data, and the where clause is used to set conditions. As an example, applicable to the above table, you can consider a request that requires information about the full name employees working in the Sales department. The request structure will take the form as in the table below.

The concept of a subquery

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

Building Nested Queries

When analyzing the read information, a number of questions arise about where the nested queries can be used and whether it is possible to solve the problem by breaking them down into simple ones without complicating the structure. In practice, nested queries are used to solve complex problems. This type of tasks includes situations where the condition under which the restriction of a further selection of values ​​will occur is not known in advance. It is impossible to solve such problems if you simply use a regular MySQL query. A query consisting of hierarchies will search for constraints that may change over time or may not be known in advance.

If we consider the table above, then the following example can be cited as a difficult task. Suppose we need to find out basic information about employees subordinate to Grishin Grigory Grigoryevich, who is the head of the sales department. When forming a request, we do not know its identification number. Therefore, initially we need to know him. To do this, use a simple query that will allow you to find a solution to the main condition and complement the main MySQL query. The request clearly shows that the subquery receives the employee identification number, which further determines the restriction of the main request:

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

Summary

Summing up, it should be noted that there are many other additional features that greatly facilitate the construction of queries, since MySQL is a powerful tool with a rich arsenal of tools for storing and processing data.