current position:Home>Execute select first for SQL query statement, Vue project actual combat Baidu cloud disk

Execute select first for SQL query statement, Vue project actual combat Baidu cloud disk

2021-08-26 14:52:49 Programmer tiger

  1. GROUP BY <group_by_list>

  2. HAVING <having_condition>

  3. ORDER BY <order_by_condition>

10.LIMIT <limit_number>




 However, the order of execution is :



> **FROM**  

> < Table name > #  The cartesian product   

> **ON**  

> < filter > #  Filter the virtual table of Cartesian product   

> **JOIN** <join, left join, right join...>?  

> <join surface > #  Appoint join, Used to add data to on In the virtual table after , for example left join The remaining data from the left table will be added to the virtual table   

> **WHERE**  

> <where Conditions > #  Filter the above virtual table   

> **GROUP BY**  

> < Grouping conditions > #  grouping   

> <SUM() Wait for the aggregate function > #  be used for having Clause to judge , In writing, this kind of aggregate function is written in having Judge what's inside   

> **HAVING**  

> < Group screening > #  Aggregate and filter the results after grouping   

> **SELECT**  

> < Back to the data list > #  The returned single column must be in group by clause , Except aggregate functions   

> **DISTINCT**  

> \#  Data De duplication   

> **ORDER BY**  

> < Sorting conditions > #  Sort   

> **LIMIT**  

> < Row limit >



*    Actually ,** When the engine performs each of these steps , Will form a virtual table in memory , And then do the following operation on the virtual table **, And free the memory of the unused virtual table , And so on .



 Specific explanation :( notes : below “VT” Express  →  Virtual table  virtual )



1.  ?from:**select \* from table\_1, table\_2;  And  select \* from table\_1 join table\_2;  The result is the same , They all mean Cartesian product ;** It is used to directly calculate the Cartesian product of two tables , Get the virtual table VT1, This is all. select Statement is the first operation to be performed , Other operations are performed on this table , That is to say from What the operation accomplished 

2.  on:  from VT1 Filter the data that meets the criteria in the table , formation VT2 surface ;

3.  join:  Will be  join  Type of data added to VT2 In the table ,** for example  left join  The remaining data from the left table will be added to the virtual table VT2 in , formation VT3 surface ; If the number of tables is greater than 2, It will repeat 1-3 Step **;

4.  where:  Perform screening ,( You can't use aggregate functions ) obtain VT4 surface ;

5.  group by:  Yes VT4 Tables are grouped , obtain VT5 surface ; After processing the statement , Such as select,having, The columns used must be included in group by In the condition , Those that don't appear need aggregate functions ;

6.  having:  Filter grouped data , obtain VT6 surface ;

7.  select:  Return the column to get VT7 surface ;

8.  distinct:  Used to get rid of duplication VT8 surface ;

9.  order by:  Used to sort to get VT9 surface ;

10.  limit:  Returns the number of rows required , obtain VT10;



>  It should be noted that :

> 

> *   group by In the condition , Each column must be a valid column , It can't be an aggregate function ;

> *   null Values are also returned as a group ;

> *    Except for aggregate functions ,select Columns in clause must be in group by In the condition ;



  

** The above shows us what a query will return , meanwhile , Also answer the following questions :**



*    Can be in  GRROUP BY  Then use  WHERE  Do you ?( no way ,GROUP BY  Is in  WHERE  after !)

*    Can I filter the results returned by window functions ?( no way , The window function is  SELECT  In the sentence , and  SELECT  Is in  WHERE  and  GROUP BY  after )

*    Can be based on  GROUP BY  What's going on in  ORDER BY  Do you ?( Sure ,ORDER BY  Basically at the end of the execution , So it can be based on anything  ORDER BY)

*   LIMIT  When is the execution ?( In the end !)



 however , The database engine does not have to be executed strictly in this order  SQL  Inquire about , Because in order to execute queries faster , They will make some optimizations , These questions are explained below ↓↓↓.



  

SQL The alias in will affect SQL Execution order ?

======================



 The following parties SQL Shown :




     
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.
  • 45.
  • 46.
  • 47.
  • 48.
  • 49.
  • 50.
  • 51.
  • 52.
  • 53.
  • 54.
  • 55.
  • 56.
  • 57.
  • 58.
  • 59.
  • 60.
  • 61.
  • 62.
  • 63.
  • 64.
  • 65.
  • 66.
  • 67.
  • 68.
  • 69.
  • 70.
  • 71.
  • 72.
  • 73.
  • 74.
  • 75.
  • 76.
  • 77.
  • 78.
  • 79.
  • 80.
  • 81.
  • 82.
  • 83.
  • 84.
  • 85.
  • 86.
  • 87.
  • 88.
  • 89.
  • 90.
  • 91.
  • 92.
  • 93.
  • 94.
  • 95.
  • 96.
  • 97.
  • 98.
  • 99.
  • 100.
  • 101.
  • 102.
  • 103.
  • 104.
  • 105.
  • 106.
  • 107.
  • 108.
  • 109.
  • 110.
  • 111.
  • 112.
  • 113.
  • 114.
  • 115.
  • 116.
  • 117.
  • 118.
  • 119.
  • 120.
  • 121.
  • 122.
  • 123.
  • 124.
  • 125.

SELECT CONCAT(first_name, ’ ', last_name) AS full_name, count(*)

FROM table

GROUP BY full_name




 From this statement , As if  GROUP BY  Is in  SELECT  After that , Because it quotes  SELECT  One of the aliases in . But it doesn't have to be , The database engine will rewrite the query like this ↓↓↓:




     
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.

SELECT CONCAT(first_name, ’ ', last_name) AS full_name, count(*)

FROM table

GROUP BY CONCAT(first_name, ’ ', last_name)




 therefore , such  GROUP BY  Still execute first .



 in addition , The database engine will also do a series of checks , Make sure  SELECT  and  GROUP BY  The things in are effective , So we will check the query before generating the execution plan .



 The database is likely to perform queries out of the normal order ( Optimize )

====================



  


#  summary 

 Opportunity is for the prepared mind , Everyone should be clear about their attitude before applying for a job , Familiar with the job search process , Be prepared , Do something predictable .

 For new graduates , School recruitment is more suitable for you , Because most of them don't have work experience , Enterprises will not have the need for work experience . meanwhile , You don't need to fake your actual combat experience , So that your resume can stand out , On the contrary, it will make the interviewer doubt .

 You should be clear about your development direction in College , If you're a freshman, make sure you want to be Java The engineer , Then don't spend too much time learning other technical languages , High numbers or something , Why don't you think about how to tamp Java Basics . The following figure covers what new students and even Xiao Bai who has changed careers need to learn Java Content :

** You need to get this learning plan route and the... Mentioned in the article Java Inside Ali Java Students of this year's employment Dictionary , Please forward this article for support , Pay attention to me ,[ Click here for free ](https://gitee.com/vip204888/java-p7)**

![](https://s2.51cto.com/images/20210823/1629678736371113.jpg)

![](https://s2.51cto.com/images/20210823/1629678736815370.jpg)
     
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.

copyright notice
author[Programmer tiger],Please bring the original link to reprint, thank you.
https://en.qdmana.com/2021/08/20210826145247677r.html

Random recommended