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
Copy after login
Let’s analyze it through a practical example. First, create two tables, users and orders.
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);
Copy after login
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)
Copy after login
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.
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!
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
