Shulou(Shulou.com)05/31 Report--

Editor to share with you what is the underlying operation principle of the query in SQ, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Data preparation

The purpose of this article is to explain the execution process of SQL query, which does not involve too complex SQL operations, but mainly involves two tables: citizen and city. The specific data are as follows:

CREATE TABLE citizen (name CHAR (20), city_id INT (10)); CREATE TABLE city (city_id INT (10), city_name CHAR (20)); INSERT INTO cityVALUES (1, "Shanghai"), (2, "Beijing"), (3, "Hangzhou") INSERT INTO citizenVALUES ("tom" 3), ("jack", 2), ("robin", 1), ("jasper", 3), ("kevin", 1), ("rachel", 2), ("trump", 3), ("lilei", 1), ("hanmeiei", 1)

Query execution order

The query sentences involved in this article are as follows: join the citizen table and the city table, then filter out the data of city_name! = "Shanghai", and then group according to city_name to count the cities with a total number of people greater than 2 in each city, as shown below:

Query statement

SELECT city.city_name AS "City", COUNT (*) AS "citizen_cnt" FROM citizen JOIN city ON citizen.city_id = city.city_id WHERE city.city_name! = 'Shanghai' GROUP BY city.city_nameHAVING COUNT (*) > = 2ORDER BY city.city_name ASCLIMIT 2

Execution steps

The writing order of the above SQL query statement is:

SELECT... FROM... WHERE... GROUP BY... HAVING... ORDER BY...

However, the execution order is not like this, and the specific execution order is shown in the following steps:

1. Get data (From, Join)

two。 Filter data (Where)

3. Grouping (Group by)

4. Packet filtering (Having)

5. Returns the query field (Select)

6. Sorting and paging (Order by & Limit / Offset)

Scream Tip: the purpose of this article is to illustrate the general underlying principles of SQL execution without considering its optimization techniques, such as predicate push-down, projection push-down, and so on.

The underlying principle of execution

In fact, the SQL execution order mentioned above is the so-called underlying principle. When we execute the SELECT statement, each step will produce a virtual table (virtual table), which will be used as input when the next step is executed. Note that these processes are transparent to the user.

You will notice that SELECT starts with the FROM step. At this stage, if you are doing JOIN with multiple tables, you will also go through the following steps:

Get data (From, Join)

First, we will calculate the Cartesian product through CROSS JOIN, which is equivalent to getting the virtual table vt1-1.

Then filter through the ON condition, virtual table vt1-1 as input, output virtual table vt1-2

Add an external line. If we use left join, right link or full join, we will involve external rows, that is, add external rows to the virtual table vt1-2 to get the virtual table vt1-3.

Filter data (Where)

After the above steps, we get a final virtual table vt1, on which we use where filtering to filter out the data that does not meet the conditions, so as to get the virtual table vt2.

Grouping (Group by)

After the where filtering operation, you get the vt2. Next, do the GROUP BY operation to get the intermediate virtual table vt3.

Packet filtering (Having)

Based on the virtual table vt3, having is used to filter out the aggregate data that does not meet the conditions, and the vt4 is obtained.

Returns the query field (Select)

When we have completed the conditional filtering section, we can filter the fields extracted from the table, that is, enter the SELECT and DISTINCT phases. First, the target field is extracted in the SELECT phase, and then the duplicate rows are filtered out in the DISTINCT phase to get the intermediate virtual tables vt5-1 and vt5-2, respectively.

Sorting and paging (Order by & Limit / Offset)

When we have extracted the desired field data, we can sort by the specified field, that is, the ORDER BY stage, to get the virtual table vt6. Finally, on the basis of vt6, take out the record of the specified row, that is, the LIMIT stage, and get the final result, corresponding to the virtual table vt7.

Perform step analysis in detail

Step 1: get data (From, Join)

FROM citizenJOIN city

The first step in this process is to execute the statement in the From clause, and then execute the Join clause. The result of these operations is the Cartesian product of the two tables.

Namecity_idcity_idcity_nametom31 Shanghai tom32 Beijing tom33 Hangzhou jack21 Shanghai jack22 Beijing jack23 Hangzhou robin11 Shanghai robin12 robin13 Hangzhou jasper31 Shanghai jasper32 Beijing jasper33 Hangzhou kevin11 Shanghai kevin12 Beijing kevin13 Hangzhou rachel21 Shanghai rachel22 Beijing rachel23 Hangzhou trump31 trump32 Shanghai lilei11 Shanghai lilei12 Beijing lilei13 hanmeiei11 Shanghai hanmeiei12 Beijing hanmeiei13 Hangzhou

After the execution of FROM and JOIN is finished, the required rows are filtered according to the ON condition of JOIN

ON citizen.city_id = city.city_idnamecity_idcity_idcity_nametom33 Hangzhou jack22 Beijing robin11 Shanghai jasper33 Hangzhou kevin11 Shanghai rachel22 Beijing trump33 Hangzhou lilei11 Shanghai hanmeiei11 Shanghai

Step 2: filter data (Where)

When a row that meets the condition is obtained, it is passed to the where clause. This evaluates each line using a conditional expression. If the row does not evaluate to true, it is removed from the collection.

WHERE city.city_name! = 'Shanghai' namecity_idcity_idcity_nametom33 Hangzhou jack22 Beijing jasper33 Hangzhou rachel22 Beijing trump33 Hangzhou

Step 3: grouping (Group by)

The next step is to execute the Group by clause, which groups rows with the same value into groups. After that, all Select expressions are evaluated by group, not by line.

GROUP BY city.city_nameGROUP_CONCAT (citizen. Name) city_idcity_namejack,rachel2 Beijing tom,jasper,trump3 Hangzhou

Step 4: packet filtering (Having)

Filter the grouped data using the predicates contained in the Having clause

HAVING COUNT (*) > = 2

Step 5: returns the query field (Select)

In this step, the processor evaluates what the query results will print and whether there are any functions to run on the data, such as Distinct,Max,Sqrt,Date,Lower, and so on. In this case, the SELECT clause prints only the count (*) value of the city name and its corresponding grouping, and uses the identifier "City" as the alias for the city_name column.

SELECT city.city_name AS "City", COUNT (*) AS "citizen_cnt" citycitizen_cnt Beijing 2 Hangzhou 3

Step 6: sorting and paging (Order by & Limit / Offset)

The final processing step of the query involves the sorting and output size of the result set. In our example, we sort it in ascending alphabetical order and output two pieces of data results.

ORDER BY city.city_name ASCLIMIT 2citycitizen_cnt Beijing 2, Hangzhou 3 and above are all the contents of this article entitled "what is the underlying operation principle of querying in SQ". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.