MySQL Query statement

In the MySQL database, the main words include a SELECT clause, a FROM clause, a WHERE clause, a GROUP BY clause, an ORDER BY clause, a HAVING clause. When the database management system executes an SQL statement, the above clauses are executed in 1, the FROM clause is executed, the table in the FROM clause is used as an intermediate table, and 2, if there is a WHERE clause, removes rows from the intermediate table that do not meet the filter criteria, based on the filter conditions therein; 3, according to group The grouped columns specified in the BY clause, grouping the data in the intermediate table, 4, calculating the value of the SELECT clause aggregate function for each group, and generating a row in the query result for each group; 5, if there is a HAVING clause, the result of the grouping calculation is filtered again according to the filter condition of the HAVING clause. 6. If there is an ORDER BY clause, the results are sorted according to the columns in the ORDER BY clause. In the above 6 statements, where and having are filter conditions, but you can see that they have the execution time of the different, while there is the result of the aggregation is filtered again, and where is the original data table filtering.
When we use Query statements in a table, there are often a variety of conditions. For example, the EMP employee table is known to "query the information for the person with the highest wage (SAL) in each department (DEPNO) in the table." Then we can write select * from emp where (sal,depno) in (select Max (SAL), depno from EMP Group by Depno). This whole sentence is a sub-query example, the outside of the SELECT statement is to query the content, the inside of the SELECT statement is the required conditions. Here is the use of where condition first execution, we look at an example to "query more than 20 department number of departments", SQL statement for select DEPTNO from EMP GROUP by DEPTNO have count (*) > (SELECT Count (*) from EMP where deptno=20); Why do we use having but not where, because there is a sequential problem of execution, where as the filter is performed before the grouping group by, but the aggregate function count (*) is executed after the group by, it is obvious that there is a contradiction, And having the appearance of the perfect solution to this problem.

MySQL Query statement