SQL query of the first 10 records (sqlserver/MySQL/Oracle/Sybase) [syntax analysis]

 

This articleArticleIt mainly analyzes the methods in which four databases (sqlserver/Oracle/MySQL/Sybase) query the first n data records or query the intermediate N data records. Perform a simple analysis on the syntax of the four databases. This is a preliminary tutorial. Skip this tutorial automatically. In the future, we will also write something more and more coherent. For example, the next article will be related to this article. I will write some common paging SQL statements, which will use the methods written today to query the first N or the middle n. Then go deep into the paging storage. Next I will also write a series of syntax analysis series for heterogeneous databases, which will be used to sort out some frequently asked questions from many friends, colleagues, and customers, on the one hand, it provides more friends with opportunities for communication and learning.

 

Go to the topic. The first 10 methods of MSSQL query are as follows:

 

Select   Top X *   From Table_name -- You can change the first X records to the expected number.
Select   Top N *   From ( Select   Top M *   From Table_name Order   By Column_name) Order   By Column_name Desc -- Query the N to M records. This is also the common paging method.
For example, common paging methods:
Declare   @ Page   Int
Declare   @ Row   Int
Set   @ Page = 2   -- Page number
Set   @ Row = 3 -- Number of lines per page
Select   Top ( @ Row ) *   From ( Select   Top ( @ Row * @ Page ) *   From Table_name Order   By ID) Order   By ID Desc -- The most basic paging method: Change @ row and @ page to achieve paging effect.

 

 

The first 10 methods of MySQL query:

Select   *   From Table_name limit 0 , 10 -- Generally, 0 can be omitted and directly written as limit 10. 0 indicates starting after 0th records, that is, starting from the first record.
Select   *   From Table_name limit 1 , 10 -- It is displayed starting from the record after the first entry, that is, starting from the second entry.

 

The top 10 methods for Oracle query:

Select   *   From Table_name Where Rownum < X -- X indicates the first number of records.
Select   *   From ( Select A. * , A. rownum Rn From ( Select   * From Table_name) Where A. rownum < M) Where Rn > N -- This is the data from N to m, which is divided into three layers.