Total number 150 piece / Zhang Junhong
Yes Sql, Or I know about it Sql People in the world , Should be able to write the following line of code ：
select * from t
The code above represents a query t All information in the table , yes Sql The most basic in query , The simplest line of code , You can think of it as being in other programming languages Hello World.
select * It's just that you step into Sql The first step of the gate , In real work , It must be more than that . Let's take an example .
Now we have the following table t
, The transaction details of each commodity category are stored , We need to get the order quantity more than 10 Corresponding category , And take out the order quantity 3 Category of products , There will be some testing orders (catid=c666 This is the test ), We need to filter it out .
catidorderid
c11
c12
c13
c24
c25
c36
……
c10010000
Do the above requirements , ours Sql You can write that ：
select catid, count(orderid) as sales from t where
catid <> "c666" group by catid having count(orderid) > 10
order by count(orderid) desc limit 3
above Sql What is involved in the code select,from,where,group by,having,order by,limit this 7 Key words , It basically includes
Sql
All query keywords in , The order above is this 7 The grammatical order of three key words , That's when you're writing code , It should be written in this order , Well, here 7 What is the execution order of each keyword ? That is to say, which should be executed first and then which ?
To be sure , Certainly not from top to bottom , If that's the case , There is no need to write this article .
One of the attitudes I've always insisted on is , Computers are no different from people when they do things , The basic logic and process are the same , After all, computers are designed by people
. In that case , Let's take a look , If we do the above requirement manually , What are we going to do .
First of all, do I need to know from which table I want to get what I want , that is from
; Now I know which table to get it from , But not all the information in this table is what I need , I need to get rid of something I don't need ( For example, test orders ), Or sift out what I need , This is it. where
; Now I'll filter out the order details I need , But I want the order quantity per category , Do you need to do a group aggregation at this time , that is group by
; The results of group aggregation are not all we need , We just need to be bigger than that 10 Category of products , So we need to put more than 10 We're going to screen them out , Not greater than 10 Filtered out by category , This is it. having
; Now most of the information we want has come out , We can use it select We got them ; Because we need to take the top three categories in the end , So we need to sort the results in descending order , Namely
order by; The last step is to show only the first three , Just make a limit , that is limit.
That's all Sql A basic execution order of a statement , To sum up ：
from-where-groupby-having-select-orderby-limit
Technology