Learn more about logical queries in MySQL

Query is the most frequent operation in MySQL, and it is also the basis for building DELETE and UPDATE; query processing can be divided into logical queries and physical queries. Today we will introduce logical query to you in detail. I hope it will be helpful to you!

In MySQL, query is the basis for building DELETE and UPDATE. Because when you want to delete or update them, you first need to find these records, so SELECT displays is particularly important. For query processing, it can be divided into logical queries and physical queries. Logical queries indicate what results should be produced when executing the SELECT statement, while physical queries indicate how MySQL obtains this result. [Related recommendations: mysql video tutorial]

This chapter will talk about logical queries.

In the SQL statement, 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, it can be roughly divided into 10 steps. , as shown below, each operation will generate a virtual table.

(7) select (8)distinct<select_list>

(1) from <left table>
(3) <join_type> join <right_table>
(2)    on<条件>
(4) where <条件>
(5) group by<字段list>
(6) having<条件>
(9) order by<字段>
(10) limit

Let’s analyze it through a practical example. First, create 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)

Insert data.

insert user values(1,"张三","内蒙");
insert user values(2,"李四","内蒙");
insert user values(3,"王五","北京");
insert user values(4,"迪迦","西藏");
insert user values(5,"金甲战士","内蒙");

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);

Okay, now let’s query users from Inner Mongolia whose order quantity is less than 3. The SQL is as follows.

mysql> select userName,count(orders.orderId) as total from user 
left join orders on user.userId = orders.userId 
where city="内蒙" 
group by user.userId 
having count(orders.orderId)<3 
order by total desc;
+--------------+-------+
| userName     | total |
+--------------+-------+
| 李四         |     1 |
| 金甲战士     |     0 |
+--------------+-------+
2 rows in set (0.00 sec)

There is data and SQL. Let’s analyze the specific process.

1. Cartesian product

#The first thing to do is to perform the Cartesian product of the two tables before and after the FROM statement, so what is Cartesian product? For example, assuming that the set A={a, b} and the set B={0, 1, 2}, the Cartesian product of the two sets is {(a, 0), (a, 1), ( a, 2), (b, 0), (b, 1), (b, 2)}.

So, corresponding to the above data, a virtual table VT1 will eventually be generated, which will contain 35 rows of data. The specific data is as follows.

##1张三Inner Mongolia1000341张三Inner Mongolia100051##11##1张三Inner Mongolia100072............Golden Armor Warrior##5Golden Armor WarriorInner Mongolia100021

2. ON过滤器

下一步，通过ON后面的添加过滤掉不需要的数据，在上述SQL中，条件是user.userId = orders.userId ，所以通过上面生成的虚拟表VT1，除去不相关的数据，生成新的虚拟表VT2，最终的结果如下。

+--------+--------------+--------+---------+--------+
| userId | userName     | city   | orderId | userId |
+--------+--------------+--------+---------+--------+
|      1 | 张三         | 内蒙   |   10005 |      1 |
|      1 | 张三         | 内蒙   |   10004 |      1 |
|      1 | 张三         | 内蒙   |   10002 |      1 |
|      1 | 张三         | 内蒙   |   10001 |      1 |
|      2 | 李四         | 内蒙   |   10007 |      2 |
|      3 | 王五         | 北京   |    NULL |   NULL |
|      4 | 迪迦         | 西藏   |   10006 |      4 |
|      4 | 迪迦         | 西藏   |   10003 |      4 |
|      5 | 金甲战士     | 内蒙   |    NULL |   NULL |
+--------+--------------+--------+---------+--------+

3.添加外部行

这一步只有在连接类型为OUTER JOIN才发生。

LEFT OUTER JOIN把左表记为保留表，RIGHT OUTER JOIN把右表作为保留表，FULL OUTER JOIN表示都作为保留表，添加外部行的工作就是在上一步的基础上添加保留表中被过滤条件过滤掉的数据，非保留表的数据被赋值NULL。

最终生成下面的结果，记为虚拟表VT3。

+--------+--------------+--------+---------+--------+
| userId | userName     | city   | orderId | userId |
+--------+--------------+--------+---------+--------+
|      1 | 张三         | 内蒙   |   10005 |      1 |
|      1 | 张三         | 内蒙   |   10004 |      1 |
|      1 | 张三         | 内蒙   |   10002 |      1 |
|      1 | 张三         | 内蒙   |   10001 |      1 |
|      2 | 李四         | 内蒙   |   10007 |      2 |
|      3 | 王五         | 北京   |    NULL |   NULL |
|      4 | 迪迦         | 西藏   |   10006 |      4 |
|      4 | 迪迦         | 西藏   |   10003 |      4 |
|      5 | 金甲战士     | 内蒙   |    NULL |   NULL |
+--------+--------------+--------+---------+--------+

4. WHERE过滤器

这一步很简单，条件为city="内蒙" ，即只保留下city为内蒙的列，并生成新的虚拟表VT4。最终结果如下。

+--------+--------------+--------+---------+--------+
| userId | userName     | city   | orderId | userId |
+--------+--------------+--------+---------+--------+
|      1 | 张三         | 内蒙   |   10005 |      1 |
|      1 | 张三         | 内蒙   |   10004 |      1 |
|      1 | 张三         | 内蒙   |   10002 |      1 |
|      1 | 张三         | 内蒙   |   10001 |      1 |
|      2 | 李四         | 内蒙   |   10007 |      2 |
|      5 | 金甲战士     | 内蒙   |    NULL |   NULL |
+--------+--------------+--------+---------+--------+

5. GROUP BY 分组

这步将上一个步骤进行分组，并生成新的虚拟表VT5，结果如下。

+--------+--------------+--------+
| userId | userName     | city   |
+--------+--------------+--------+
|      1 | 张三         | 内蒙   |
|      2 | 李四         | 内蒙   |
|      5 | 金甲战士     | 内蒙   |
+--------+--------------+--------+

6.HAVING筛选

分完组，我们就可以筛选了，选出count(orders.orderId)<3 的数据即可，最终结果如下。

| userId | userName     | city   | count(orders.orderId) |
+--------+--------------+--------+-----------------------+
|      2 | 李四         | 内蒙   |                     1 |
|      5 | 金甲战士     | 内蒙   |                     0 |
+--------+--------------+--------+-----------------------+

7.处理SELECT列表

虽然SELECT是查询中最先被指定的部分，但是直到这里才真正进行处理，在这一步，将SELECT中指定的列从上一步产生的虚拟表中选出。

8.应用DISTINCT

如果查询语句中存在DISTINCT子句，则会创建一张内存临时表，这张内存临时表的表结构和上一步产生的虚拟表一样，不同的是对进行DISTINCT操作的列增加了一个唯一索引，以此来去除重复数据。

另外对使用了GROUP BY语句的查询，再使用DISTINCT是多余的，因为已经进行了分组，不会移除任何行。

9.排序和LIMIT

最后就是排序，返回新的虚拟表。结果如下。

+--------------+-------+
| userName     | total |
+--------------+-------+
| 李四         |     1 |
| 金甲战士     |     0 |
+--------------+-------+

但是在本例子中没有使用到LIMIT，如果使用到了，那么则从选出指定位置开始的指定行数，

原文地址：https://juejin.cn/post/7000739902937628679

作者：i听风逝夜

更多编程相关知识，请访问：编程视频！！

userId	userName	city	orderId	userId
1	张三	Inner Mongolia	10001	1
1	张三	内Mongolia	10002	1
张三	Inner Mongolia	10006	1
张三	Inner Mongolia	10005	4
				##5
Inner Mongolia	10001	1

The above is the detailed content of Learn more about logical queries in MySQL. For more information, please follow other related articles on the PHP Chinese website!