Usage of mysql-Based Query statements

1> query data in addition to the first three data tables.

At first, I thought of this statement.

SELECT * FROM admin WHERE userid not in (SELECT userid FROM admin order by userid LIMIT 3) order by userid DESC

But it will reportThis version of MySQL doesn't yet support 'limit & IN/ALL/ANY/SOME subquery

This means that the subquery does not support limit. Another point is that the not in efficiency is very low during the query.

The final solution is:

Create view view_top3_admin as select * FROM admin order by userid LIMIT 3;

Create a view and place the subquery conditions in the view.

Use this statement

SELECT * FROM admin a where not exists (SELECT 1 FROM view_top3_admin B WHERE B. userid = a. userid) ORDER BY a. userid DESC

First, explain this statement.SELECT 1 FROM view_top3_admin B WHERE B. userid = a. useridIndicates that the value in the query table is displayed as 1, 1 as long as there is data. This indicates that no data is read.

This improves the query performance.The performance of replace 1 with null is consistent. The entire statement indicates querying the admin Table value. The judgment condition is that the value is not in the subquery table.

2> Use of union and union all

The two keywords provide the UNION and union all keywords in the mysql database. These two keywords combine the results into one, however, the two are different in terms of usage and efficiency.

UNION filters out duplicate records after table link. Therefore, after table link, it sorts the generated result sets and deletes duplicate records before returning results.

Select * from table union select * from tabl

Union all simply merges the two results and returns the results. If the two returned results have duplicate data, the returned results will contain duplicate data.

Select * from table union all select * from tabl

In terms of efficiency, union all is much faster than UNION. Therefore, if you can confirm that the two results of the merge do not contain duplicate data, use UNION

These two keywords are used for reports.