Postgresql 中文操作指南

2.5. Querying a Table #

要从表中检索数据，表为 queried 。为此，使用 SQL SELECT 语句。该语句分为选择列表（列出要返回的列的部分）、表列表（列出要从中检索数据的表的的部分）和可选限定（指定任何限制的部分）。例如，要检索表 weather 的所有行，请键入：

To retrieve data from a table, the table is queried. An SQL SELECT statement is used to do this. The statement is divided into a select list (the part that lists the columns to be returned), a table list (the part that lists the tables from which to retrieve the data), and an optional qualification (the part that specifies any restrictions). For example, to retrieve all the rows of table weather, type:

SELECT * FROM weather;

这里的 * 是“所有列”的缩写。 [2 ] 因此，以下命令将产生相同的结果：

Here * is a shorthand for “all columns”. [2] So the same result would be had with:

SELECT city, temp_lo, temp_hi, prcp, date FROM weather;

输出应为：

The output should be:

     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      43 |      57 |    0 | 1994-11-29
 Hayward       |      37 |      54 |      | 1994-11-29
(3 rows)

你可以在列表中编写表达式，而不仅仅是简单的列引用。例如，你可以执行：

You can write expressions, not just simple column references, in the select list. For example, you can do:

SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

这应给出：

This should give:

     city      | temp_avg |    date
---------------+----------+------------
 San Francisco |       48 | 1994-11-27
 San Francisco |       50 | 1994-11-29
 Hayward       |       45 | 1994-11-29
(3 rows)

注意 AS 子句是如何用于重新标记输出列的。（AS 子句是可选的。）

Notice how the AS clause is used to relabel the output column. (The AS clause is optional.)

可以通过添加 WHERE 子句（指定所需的行），来“限制”查询。WHERE 子句包含布尔（真值）表达式，并且仅返回布尔表达式为真行的结果。限制中允许使用常规布尔运算符 (AND、OR 和 NOT）。例如，以下内容检索下雨天的旧金山的天气情况：

A query can be “qualified” by adding a WHERE clause that specifies which rows are wanted. The WHERE clause contains a Boolean (truth value) expression, and only rows for which the Boolean expression is true are returned. The usual Boolean operators (AND, OR, and NOT) are allowed in the qualification. For example, the following retrieves the weather of San Francisco on rainy days:

SELECT * FROM weather
    WHERE city = 'San Francisco' AND prcp > 0.0;

结果：

Result:

     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
(1 row)

您可以请求以排序顺序返回查询的结果：

You can request that the results of a query be returned in sorted order:

SELECT * FROM weather
    ORDER BY city;

     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
 Hayward       |      37 |      54 |      | 1994-11-29
 San Francisco |      43 |      57 |    0 | 1994-11-29
 San Francisco |      46 |      50 | 0.25 | 1994-11-27

在此示例中，排序顺序未完全指定，所以你可能会以任何顺序获得旧金山行。但是，如果你执行以下操作，你将始终获得上述结果：

In this example, the sort order isn’t fully specified, and so you might get the San Francisco rows in either order. But you’d always get the results shown above if you do:

SELECT * FROM weather
    ORDER BY city, temp_lo;

您可以请求从查询结果中删除重复行：

You can request that duplicate rows be removed from the result of a query:

SELECT DISTINCT city
    FROM weather;

     city
---------------
 Hayward
 San Francisco
(2 rows)

再次，结果行排序可能有所不同。您可以通过同时使用 DISTINCT 和 ORDER BY 来确保获得一致的结果： [3 ]

Here again, the result row ordering might vary. You can ensure consistent results by using DISTINCT and ORDER BY together: [3]

SELECT DISTINCT city
    FROM weather
    ORDER BY city;

[2 ] 虽然 SELECT * 对于即兴查询很有用，但它普遍被认为是生产代码中的糟糕风格，因为向表中添加列会改变结果。

[2] While SELECT * is useful for off-the-cuff queries, it is widely considered bad style in production code, since adding a column to the table would change the results.

[3 ] 在某些数据库系统中（包括旧版本的 PostgreSQL）， DISTINCT 的实现会自动对行进行排序，因此 ORDER BY 是不必要的。但这不是 SQL 标准所要求的，而且当前的 PostgreSQL 并没有保证 DISTINCT 会对行进行排序。

[3] In some database systems, including older versions of PostgreSQL, the implementation of DISTINCT automatically orders the rows and so ORDER BY is unnecessary. But this is not required by the SQL standard, and current PostgreSQL does not guarantee that DISTINCT causes the rows to be ordered.