关于SELECT的疑惑

以mysql为例进行分页，如：

• SELECT * FROM table WHERE age < 30 LIMIT 0, 10
• SELECT * FROM table WHERE age < 30 LIMIT 10, 20
• ...
• SELECT * FROM table WHERE age < 30 LIMIT 1000, 1010

数据库如何直接从第1000条开始？自动把"SELECT * FROM table WHERE age < 30"的结果缓存起来？View?

从14亿行中选age < 30的，比如结果有2亿行。在PHP里：

$result = mysqli_query($connection, $sql); $row_count = mysqli_num_rows($result);

数据库是不是要用很长时间才能返回row_count？用程序打开文本文件逐行读并输出满足条件的行，可能马上就看到第一行，而总数要最后才知道。

虽然这里讲了不少SQL，我还是不明白。

A SELECT statement retrieves zero or more rows from one or more database tables or database views. The database translates the query into a "query plan" which may vary between executions, database versions and database software. This functionality is called the "query optimizer" as it is responsible for finding the best possible execution plan for the query, within applicable constraints.

In a database, a view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object. This pre-established query command is kept in the database dictionary. Unlike ordinary base tables in a relational database, a view does not form part of the physical schema: as a result set, it is a virtual table computed or collated dynamically from data in the database when access to that view is requested. Changes applied to the data in a relevant underlying table are reflected in the data shown in subsequent invocations of the view.

A query plan (or query execution plan) is a sequence of steps used to access data in a SQL relational database management system. Since SQL is declarative, there are typically many alternative ways to execute a given query, with widely varying performance. When a query is submitted to the database, the query optimizer evaluates some of the different, correct possible plans for executing the query and returns what it considers the best option. Because query optimizers are imperfect, database users and administrators sometimes need to manually examine and tune the plans produced by the optimizer to get better performance.

The query optimizer attempts to determine the most efficient way to execute a given query by considering the possible query plans. Generally, the query optimizer cannot be accessed directly by users: once queries are submitted to the database server, and parsed by the parser, they are then passed to the query optimizer where optimization occurs. However, some database engines allow guiding the query optimizer with hints.

Queries can be nested so that the results of one query can be used in another query via a relational operator or aggregation function. A nested query is also known as a subquery. While joins and other table operations provide computationally superior (i.e. faster) alternatives in many cases, the use of subqueries introduces a hierarchy in execution that can be useful or necessary. In the following example, the aggregation function AVG receives as input the result of a subquery:
SELECT isbn, title, price FROM Book WHERE price < (SELECT AVG(price) FROM Book) ORDER BY title;

An Inline view is the use of referencing an SQL subquery in a FROM clause. Essentially, the inline view is a subquery that can be selected from or joined to. Inline View functionality allows the user to reference the subquery as a table. The inline view also is referred to as a derived table or a subselect. In the following example, the SQL statement involves a join from the initial Books table to the Inline view "Sales". This inline view captures associated book sales information using the ISBN to join to the Books table. As a result, the inline view provides the result set with additional columns (the number of items sold and the company that sold the books):

SELECT b.isbn, b.title, b.price, sales.items_sold, sales.company_nm FROM
Book b
JOIN (SELECT SUM(Items_Sold) Items_Sold, Company_Nm, ISBN FROM Book_Sales GROUP BY Company_Nm, ISBN) sales ON sales.isbn = b.isbn

Limiting result rows. Often it is convenient to indicate a maximum number of rows that are returned. This can be used for testing or to prevent consuming excessive resources if the query returns more information than expected. The approach to do this often varies per vendor. In ISO SQL:2003, result sets may be limited by using cursors, or by adding a SQL window function to the SELECT-statement. ISO SQL:2008 introduced the FETCH FIRST clause.

Some DBMSes offer non-standard syntax either instead of or in addition to SQL standard syntax. Below, variants of the simple limit query for different DBMSes are listed:

• SET ROWCOUNT 10 SELECT * FROM T
• SELECT * FROM T LIMIT 10 OFFSET 20
• SELECT * from T WHERE ROWNUM <= 10
• SELECT FIRST 10 * from T
• SELECT FIRST 10 * FROM T order by a
• SELECT SKIP 20 FIRST 10 * FROM T order by c, d
• SELECT TOP 10 * FROM T
• SELECT * FROM T SAMPLE 10
• SELECT TOP 20, 10 * FROM T
• SELECT TOP 10 START AT 20 * FROM T
• SELECT FIRST 10 SKIP 20 * FROM T
• SELECT * FROM T ROWS 20 TO 30
• SELECT * FROM T WHERE ID_T > 10 FETCH FIRST 10 ROWS ONLY
• SELECT * FROM T WHERE ID_T > 20 FETCH FIRST 10 ROWS ONLY

Rows Pagination is an approach used to limit and display only a part of the total data of a query in the database. Instead of showing hundreds or thousands of rows at the same time, the server is requested only one page (a limited set of rows, per example only 10 rows), and the user starts navigating by requesting the next page, and then the next one, and so on. It is very useful, specially in web systems, where there is no dedicated connection between the client and the server, so the client does not have to wait to read and display all the rows of the server.

sql查询（三）之分页查询 | PHP mysqli_num_rows()

六级/考研单词: retrieve, nil, data, translate, query, hardware, execute, persist, dictionary, physics, compute, dynamic, underlie, alternate, evaluate, manual, tune, hint, nest, aggregate, superior, hierarchy, necessity, derive, seldom, consume, excess, vendor, offset, skip, navigate, web, dedicate