Example Analysis of logical query in MySQL

This article mainly introduces the example analysis of logical query in MySQL, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

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 out these records, so SELECT is particularly important. For query processing, it can be divided into logical query and physical query. Logical query indicates what kind of result should be produced when executing SELECT statement, and physical query indicates how MySQL gets this result.

This chapter talks about logical queries.

In the SQL statement, the first processing is the FROM statement, the last execution is the LIMIT statement, if all the statements are used, such as GROUP BY, ORDER BY, then it can be roughly divided into 10 steps, as shown below, each operation produces a virtual table.

(7) select (8) distinct (1) from (3) join (2) on (4) where (5) group by (6) having (9) order by (10) limit

Let's analyze it with a practical example, first creating two tables, the user and the order.

Mysql > create table user (userId int, userName varchar, city varchar, primary key); Query OK, 0 rows affected, 1 warning (0.05sec) mysql > create table orders (orderId int (11), userId int (11), primary key (orderId)); Query OK, 0 rows affected, 2 warnings (0.05sec)

Insert 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, "Dica", "Xizang"); insert user values (5, "Golden Armour Warrior", "Inner Mongolia"); insert orders values (10001prime1); insert orders values (10002prime1); insert orders values (10003Lint 4); insert orders values (10004MAL 1); insert orders values (10005L1); insert orders values (10006pr 4) Insert orders values (10007jue 2)

OK, now let's check out the 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= "Inner Mongolia" group by user.userId having count (orders.orderId)