MySQL logical query
Query is the most frequent operation, he is also used to build DELETE, UPDATE, because you want to DELETE or UPDATE them, the first is to check these records, so SELECT is particularly important, for query processing, can be divided into logical query and physical query, Logical queries show what results should be produced when a SELECT statement is executed, while physical queries show how MySQL gets the results.
This chapter looks at logical queries.
In SQL statements, the FROM statement is processed first, and the LIMIT statement is executed last. If all statements are used, such as GROUP BY and ORDER BY, then there are roughly 10 steps, as shown below. Each operation produces a virtual table.
(7) select (8)distinct<select_list>
(1) from <left table>
(3) <join_type> join <right_table>
(2) on<conditions>
(4) where <conditions>
(5) group by<Field list>
(6) having<conditions>
(9) order by<field>
(10) limit
Copy the codeTo examine this with a practical example, start by creating two tables, users and orders.
mysql> create table user (userId int(11),userName varchar(255),city varchar(255), primary key (userId));
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> create table orders(orderId int(11) ,userId int(11),primary key (orderId));
Query OK, 0 rows affected, 2 warnings (0.05 sec)
Copy the codeInsert data.
insert user values(1," Zhang SAN "," Inner Mongolia ");insert user values(2," Li Si "," Inner Mongolia ");insert user values(3," Wang Wu "," Beijing ");insert user values(4," Dija "," Tibet ");insert user values(5," Golden Warrior "," Inner Mongolia ");insert orders values(10001.1);
insert orders values(10002.1);
insert orders values(10003.4);
insert orders values(10004.1);
insert orders values(10005.1);
insert orders values(10006.4);
insert orders values(10007.2);
Copy the codeOk, now let’s query users from Inner Mongolia, and the number of orders is less than 3, SQL as follows.
mysql> select userName,count(orders.orderId) as total from user
left join orders on user.userId = orders.userId
where city="Inner Mongolia"group by user.userId
having count(orders.orderId)<3
order by total desc;
+--------------+-------+
| userName | total |
+--------------+-------+
|Li si| 1 |
|Shining golden warrior| 0 |
+--------------+-------+
2 rows in set (0.00 sec)
Copy the codeThere are data and SQL, the following analysis of the specific process.
1. Cartesian product
The first thing we do is we take a Cartesian product of the two tables before and after the FROM statement, so what is a Cartesian product? For example, suppose that set A = {A, b}, set b = {0, 1, 2}, the two sets of cartesian product for {(A, 0), (A, 1), (A, 2), (b, 0), (b, 1), (b, 2)}.
Therefore, corresponding to the above data, a virtual table VT1 will be generated, which will contain 35 rows of data, as shown below.
| userId | userName | city | orderId | userId |
|---|---|---|---|---|
| 1 | Zhang SAN | Inner Mongolia | 10001 | 1 |
| 1 | Zhang SAN | Inner Mongolia | 10002 | 1 |
| 1 | Zhang SAN | Inner Mongolia | 10003 | 4 |
| 1 | Zhang SAN | Inner Mongolia | 10005 | 1 |
| 1 | Zhang SAN | Inner Mongolia | 10006 | 1 |
| 1 | Zhang SAN | Inner Mongolia | 10005 | 4 |
| 1 | Zhang SAN | Inner Mongolia | 10007 | 2 |
| . | ||||
| 5 | Shining golden warrior | Inner Mongolia | 10001 | 1 |
| 5 | Shining golden warrior | Inner Mongolia | 10002 | 1 |
2. ON filter
Select * from VT1 where user.userId = orders.userId; select * from VT2 where user.userId = orders.userId; select * from VT2 where user.userId = orders;
+--------+--------------+--------+---------+--------+
| userId | userName | city | orderId | userId |
+--------+--------------+--------+---------+--------+
| 1 |Zhang SAN|Inner Mongolia| 10005 | 1 |
| 1 |Zhang SAN|Inner Mongolia| 10004 | 1 |
| 1 |Zhang SAN|Inner Mongolia| 10002 | 1 |
| 1 |Zhang SAN|Inner Mongolia| 10001 | 1 |
| 2 |Li si|Inner Mongolia| 10007 | 2 |
| 3 |Cathy|Beijing| NULL | NULL |
| 4 |tiga|Tibet| 10006 | 4 |
| 4 |tiga|Tibet| 10003 | 4 |
| 5 |Shining golden warrior|Inner Mongolia| NULL | NULL |
+--------+--------------+--------+---------+--------+
Copy the code3. Add external rows
This step only happens if the JOIN type is OUTER JOIN.
Mysql > select * from LEFT OUTER JOIN; mysql > select * from RIGHT OUTER JOIN; mysql > select * from RIGHT OUTER JOIN; mysql > select * from LEFT OUTER JOIN; The data of the non-reserved table is assigned NULL.
Finally, the following result is generated, denoted as virtual table VT3.
+--------+--------------+--------+---------+--------+
| userId | userName | city | orderId | userId |
+--------+--------------+--------+---------+--------+
| 1 |Zhang SAN|Inner Mongolia| 10005 | 1 |
| 1 |Zhang SAN|Inner Mongolia| 10004 | 1 |
| 1 |Zhang SAN|Inner Mongolia| 10002 | 1 |
| 1 |Zhang SAN|Inner Mongolia| 10001 | 1 |
| 2 |Li si|Inner Mongolia| 10007 | 2 |
| 3 |Cathy|Beijing| NULL | NULL |
| 4 |tiga|Tibet| 10006 | 4 |
| 4 |tiga|Tibet| 10003 | 4 |
| 5 |Shining golden warrior|Inner Mongolia| NULL | NULL |
+--------+--------------+--------+---------+--------+
Copy the code4. WHERE filter
This step is very simple, the condition is city=” Inner Mongolia “, that is, only the lower city is Inner Mongolia column, and generate a new virtual table VT4. The end result is as follows.
+--------+--------------+--------+---------+--------+
| userId | userName | city | orderId | userId |
+--------+--------------+--------+---------+--------+
| 1 |Zhang SAN|Inner Mongolia| 10005 | 1 |
| 1 |Zhang SAN|Inner Mongolia| 10004 | 1 |
| 1 |Zhang SAN|Inner Mongolia| 10002 | 1 |
| 1 |Zhang SAN|Inner Mongolia| 10001 | 1 |
| 2 |Li si|Inner Mongolia| 10007 | 2 |
| 5 |Shining golden warrior|Inner Mongolia| NULL | NULL |
+--------+--------------+--------+---------+--------+
Copy the codeI’m going to GROUP BY GROUP
This step groups the previous step and generates a new virtual table VT5, resulting in the following.
+--------+--------------+--------+
| userId | userName | city |
+--------+--------------+--------+
| 1 |Zhang SAN|Inner Mongolia|
| 2 |Li si|Inner Mongolia|
| 5 |Shining golden warrior|Inner Mongolia|
+--------+--------------+--------+
Copy the code6. HAVING filtering
Orders.orderid = count(Orders.orderID)<3
| userId | userName | city | count(orders.orderId) |
+--------+--------------+--------+-----------------------+
| 2 |Li si|Inner Mongolia| 1 |
| 5 |Shining golden warrior|Inner Mongolia| 0 |
+--------+--------------+--------+-----------------------+
Copy the code7. Process the SELECT list
Although SELECT is the first part of the query to be specified, the actual processing takes place here, where the column specified in SELECT is selected from the virtual table generated in the previous step.
8. Application of DISTINCT
If a DISTINCT clause exists in the query statement, a memory temporary table is created. The table structure of the memory temporary table is the same as the virtual table generated in the previous step, except that a unique index is added to the columns with DISTINCT operations to remove duplicate data.
In addition, for queries that use the GROUP BY statement, the use of DISTINCT is redundant because the GROUP is already in place and no rows are removed.
Sort and LIMIT
Finally, sort, and return the new virtual table. The results are as follows.
+--------------+-------+
| userName | total |
+--------------+-------+
|Li si| 1 |
|Shining golden warrior| 0 |
+--------------+-------+
Copy the codeBut LIMIT is not used in this example, if it is, then the specified number of rows from the specified position is selected,