Differences between select statements for extracting 10 data records from Oracle databases and SQL Server and MySQL

As long as interaction is involved, it will certainly deal with data. Maybe there is not much such content on the Internet, no matter how many, each summary is experience-based, and is carefully prepared, it may be for your future use without being forgotten, or for sharing resources, learning together, and making progress together. The following is a summary of the query statements used to extract 10 data records from common databases such as SQL Server, Oracle, and MYSQL:

1. SQL Server

This type of database is no longer familiar. I want to ask what is the best course I learned during my college years. It is definitely SQL Server.

For SQL Server, if 10 records are extracted from the database table, you must use the top keyword. If the SELECT statement contains both top and order by statements, it is selected from the sorted result set. For example:

Select top 10 * from Table Name

The data retrieval method described today allows you to retrieve records from rows m to N in a table at will, and implement paging using SQL statements. If you need to try it, I will not explain it here...

2. Oracle

I just got in touch with it. I don't know much about it, but the language has the same points. I just want to learn something different.

For Oracle, The rownum keyword is used to extract 10 records from the database table.

For example, to retrieve 10 records from Table A (whose primary key is ID), the statement is as follows:

Select * from a where rownum <= 10

The book warns that you cannot use ">" for rownum, which means that if you want to use

Select * from EMP where rownum> 5

It is a failure. To understand why rownum fails, you need to understand the mechanism behind rownum.

If rownum is used separately with =, it is only useful when rownum is = 1.

3. MySQL

Currently, this company is engaged in the use of this version of the database is still very frequent, basically using it, so it is a better understanding.

The implementation of MySQL database data retrieval is relatively simple. It provides a limit function, which is generally written directly after the SQL statement.

The limit substatement can be used to limit the number of data returned by the SELECT statement. It has one or two parameters. If two parameters are provided, the first parameter specifies the position of the first row returned in all data, starting from 0 (note not 1). The second parameter specifies the maximum number of returned rows. For example:

Select * from table name where condition limit 10; # Return the first 10 rows

Select * from table name where condition limit; # Return the first 10 rows

Select * from table name where condition: limit 10, 20; # Return Row 10-20 Data