The use of query statement based on Mysql _mysql

1> query data table except the first three data.

At first, I thought of this statement.

SELECT * FROM admin WHERE userid to (select UserID from admin order by UserID LIMIT 3) Order by UserID DESC

But the runtime will report this version of MySQL doesn ' t yet support ' LIMIT & in/all/any/some subquery

This means that when the subquery does not support limit, there is a point I am very second is the query with not in the efficiency is very low

The Final Solution is

CREATE VIEW view_top3_admin as SELECT * from admin order by UserID LIMIT 3;

Create a view to place the subquery criteria in the view

And then you 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

Let's explain this statement . SELECT 1 from view_top3_admin b WHERE B.userid=a.userid indicates that the values in the query table are displayed as long as the data is 1, and 1 does not read the data

This is to improve the performance of the query, of course, can also change the inside of the 1 to null performance is consistent. The whole statement means querying the Admin table value to determine if the value is not in the subquery.

2 > Union and UNION ALL use

First, explain the two keywords that provide the Union and UNION all keywords in the MySQL database, all of which combine the result set into one, but both are different in terms of usage and efficiency.

The Union filters out duplicate records after a table link, so the resulting result sets are sorted after the table is connected, the duplicate records are deleted, and the results are returned.

SELECT * FROM table Union SELECT * FROM TABL

UNION all simply merges two results and returns if there are duplicate data in the two result sets returned, the resulting set will contain duplicate data.

SELECT * FROM table UNION ALL SELECT * FROM Tabl

In terms of efficiency, union All is much faster than union, so if you can confirm that the merged two result sets do not contain duplicate data, then use the Union

These two keywords used as a report more