Postgresql 简明教程

PostgreSQL - WHERE Clause

PostgreSQL WHERE 子句用于在从单个表中获取数据或与多个表联接时指定一个条件。

The PostgreSQL WHERE clause is used to specify a condition while fetching the data from single table or joining with multiple tables.

如果满足给定条件，则它只会从表中返回特定值。您可以使用 WHERE 子句过滤掉您不希望包含在结果集中的行。

If the given condition is satisfied, only then it returns specific value from the table. You can filter out rows that you do not want included in the result-set by using the WHERE clause.

WHERE 子句不仅在 SELECT 语句中使用，而且在 UPDATE、DELETE 语句等中也使用，我们将在后续章节中探讨这些语句。

The WHERE clause not only is used in SELECT statement, but it is also used in UPDATE, DELETE statement, etc., which we would examine in subsequent chapters.

Syntax

带有 WHERE 子句的 SELECT 语句的基本语法如下 -

The basic syntax of SELECT statement with WHERE clause is as follows −

SELECT column1, column2, columnN
FROM table_name
WHERE [search_condition]

您可以使用 comparison or logical operators. like >、<、=、LIKE、NOT 等指定 search_condition。以下示例将使这个概念变得清晰。

You can specify a search_condition using comparison or logical operators. like >, <, =, LIKE, NOT, etc. The following examples would make this concept clear.

Example

考虑 COMPANY 表具有以下记录：

Consider the table COMPANY having records as follows −

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

以下是一些简单的示例，展示了 PostgreSQL 逻辑运算符的用法。Following SELECT 语句将列出所有 AGE 大于或等于 25 AND 薪水大于或等于 65000.00 的记录 -

Here are simple examples showing usage of PostgreSQL Logical Operators. Following SELECT statement will list down all the records where AGE is greater than or equal to 25 AND salary is greater than or equal to 65000.00 −

testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

以上给出的 PostgreSQL 语句将产生以下结果 -

The above given PostgreSQL statement will produce the following result −

 id | name  | age |  address   | salary
----+-------+-----+------------+--------
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(2 rows)

以下 SELECT 语句列出所有 AGE 大于或等于 25 OR 薪水大于或等于 65000.00 的记录 -

The following SELECT statement lists down all the records where AGE is greater than or equal to 25 OR salary is greater than or equal to 65000.00 −

testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

以上给出的 PostgreSQL 语句将产生以下结果 -

The above given PostgreSQL statement will produce the following result −

 id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
(4 rows)

以下 SELECT 语句列出所有 AGE 不为 NULL 的记录，这意味着所有记录，因为没有一个记录的 AGE 等于 NULL -

The following SELECT statement lists down all the records where AGE is not NULL which means all the records, because none of the record has AGE equal to NULL −

testdb=#  SELECT * FROM COMPANY WHERE AGE IS NOT NULL;

以上给出的 PostgreSQL 语句将产生以下结果 -

The above given PostgreSQL statement will produce the following result −

  id | name  | age | address    | salary
 ----+-------+-----+------------+--------
   1 | Paul  |  32 | California |  20000
   2 | Allen |  25 | Texas      |  15000
   3 | Teddy |  23 | Norway     |  20000
   4 | Mark  |  25 | Rich-Mond  |  65000
   5 | David |  27 | Texas      |  85000
   6 | Kim   |  22 | South-Hall |  45000
   7 | James |  24 | Houston    |  10000
(7 rows)

以下 SELECT 语句列出所有 NAME 以“Pa”开头的记录，无论“Pa”后面是什么。

The following SELECT statement lists down all the records where NAME starts with 'Pa', does not matter what comes after 'Pa'.

testdb=# SELECT * FROM COMPANY WHERE NAME LIKE 'Pa%';

以上给出的 PostgreSQL 语句将产生以下结果 -

The above given PostgreSQL statement will produce the following result −

 id | name | age |address    | salary
----+------+-----+-----------+--------
  1 | Paul |  32 | California|  20000

以下 SELECT 语句列出所有 AGE 值为 25 或 27 的记录 -

The following SELECT statement lists down all the records where AGE value is either 25 or 27 −

testdb=# SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );

以上给出的 PostgreSQL 语句将产生以下结果 -

The above given PostgreSQL statement will produce the following result −

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(3 rows)

以下 SELECT 语句列出所有 AGE 值既不是 25 也不是 27 的记录 -

The following SELECT statement lists down all the records where AGE value is neither 25 nor 27 −

testdb=# SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );

以上给出的 PostgreSQL 语句将产生以下结果 -

The above given PostgreSQL statement will produce the following result −

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  3 | Teddy |  23 | Norway     |  20000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
(4 rows)

以下 SELECT 语句列出所有 AGE 值在 25 到 27 之间的记录 -

The following SELECT statement lists down all the records where AGE value is in BETWEEN 25 AND 27 −

testdb=# SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

以上给出的 PostgreSQL 语句将产生以下结果 -

The above given PostgreSQL statement will produce the following result −

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(3 rows)

以下 SELECT 语句使用 SQL 子查询，其中子查询查找所有 AGE 字段的 SALARY > 65000 的记录，然后 WHERE 子句与 EXISTS 运算符一起使用，列出所有 AGE 从外部查询存在于子查询返回的结果中的记录 -

The following SELECT statement makes use of SQL subquery where subquery finds all the records with AGE field having SALARY > 65000 and later WHERE clause is being used along with EXISTS operator to list down all the records where AGE from the outside query exists in the result returned by sub-query −

testdb=# SELECT AGE FROM COMPANY
        WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

以上给出的 PostgreSQL 语句将产生以下结果 -

The above given PostgreSQL statement will produce the following result −

 age
-----
  32
  25
  23
  25
  27
  22
  24
(7 rows)

以下 SELECT 语句使用 SQL 子查询，其中子查询查找所有具有 SALARY > 65000 的 AGE 域的记录，然后， WHERE 子句与 > 运算符一起使用，列出所有记录 AGE 从外部查询大于通过子查询返回的结果中的年龄 −

The following SELECT statement makes use of SQL subquery where subquery finds all the records with AGE field having SALARY > 65000 and later WHERE clause is being used along with > operator to list down all the records where AGE from outside query is greater than the age in the result returned by sub-query −

testdb=# SELECT * FROM COMPANY
        WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

以上给出的 PostgreSQL 语句将产生以下结果 -

The above given PostgreSQL statement will produce the following result −

 id | name | age | address    | salary
----+------+-----+------------+--------
  1 | Paul |  32 | California |  20000