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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.