Postgresql - Where

使用WHERE过滤SELECT返回行。

语法

SELECT select_list
FROM table_name
WHERE condition
ORDER BY sort_expression

如果在语句中使用了列别名，就不能使用WHERE

Operator	Description
=	等于
>	大于
<	小于
>=	大于等于
<=	小于等于
<> or !=	不等于
AND	逻辑与
OR	逻辑或
IN	包含
BETWEEN	区间
LIKE	相似匹配
IS NULL	非空
NOT	不是

示例

从示例数据库中查询

=

查询first_name等于Jamie

SELECT
	last_name,
	first_name
FROM
	customer
WHERE
	first_name = 'Jamie';

AND

查询first_name等于Jamie和last_name等于Rice

SELECT
	last_name,
	first_name
FROM
	customer
WHERE
	first_name = 'Jamie' AND
        last_name = 'Rice';

OR

查询last_name等于Rodriguez或first_name等于Adam

SELECT
	first_name,
	last_name
FROM
	customer
WHERE
	last_name = 'Rodriguez' OR
	first_name = 'Adam';

IN

查询包含Ann、Anne、Annie任何一个

SELECT
	first_name,
	last_name
FROM
	customer
WHERE
	first_name IN ('Ann','Anne','Annie');

LIKE

查询以Ann开头的last_name

SELECT
	first_name,
	last_name
FROM
	customer
WHERE
	first_name LIKE 'Ann%'

BETWEEN

查询first_name的长度大于等于 3 小于等于 5

SELECT
	first_name,
	LENGTH(first_name) name_length
FROM
	customer
WHERE
	first_name LIKE 'A%' AND
	LENGTH(first_name) BETWEEN 3 AND 5
ORDER BY
	name_length;

<>

查询first_name以Bra开头，而且last_name不等于Motley

SELECT
	first_name,
	last_name
FROM
	customer
WHERE
	first_name LIKE 'Bra%' AND
	last_name <> 'Motley';

<>和!=是相同的作用。