SQL从入门到出门第4篇查询条件

程序员文章站 2022-05-09 19:10:02

...

- 简单逻辑条件
- 复杂逻辑条件

本篇介绍SQL:2016（ISO/IEC 9075:2016）标准中定义的WHERE条件语句，以及六种主流数据库中的实现及差异：Oracle、MySQL、Microsoft SQL Server、PostgreSQL、Db2、SQLite。

在上篇的示例中，总是返回表中的所有数据。但是更多情况下，我们只需要查看满足一定条件的结果。SQL中的WHERE关键字用于指定查询的条件。

SELECT column1, column2, ... 
  FROM table 
 WHERE conditions;

WHERE子句位于FROM之后，用于指定一个或者多个逻辑条件，满足条件的行将会返回，否则将被忽略。在SQL中，WHERE子句也被称为谓词（predicate）。

简单逻辑条件

先来看一个简单的示例：

SELECT first_name,
       last_name 
  FROM employees
 WHERE last_name = 'King';

查询结果如下：
SQL从入门到出门第4篇查询条件
结果中只返回了 last_name 等于 “King” 的2条记录。

除了比较字符串的值之外，也可以与数字或日期进行比较：

SELECT first_name,
       last_name,
       salary
  FROM employees
 WHERE salary > 10000;

结果返回了月薪大于10000的所有员工：
SQL从入门到出门第4篇查询条件

在WHERE子句的条件中，允许使用各种比较运算符。

运算符	描述	示例
=	等于	manager_id = 100
!=	不等于	department_id != 50
<>	不等于	job_id <> ‘SA_REP’
>	大于	salary > 10000
>=	大于等于	hire_date >= ‘2007-01-01’
<	小于	salary < 15000
<=	小于等于	employee_id <= 123

在Oracle中还支持使用 ^= 运算符，表示不等于。

除了比较运算符之外，SQL还支持许多其他的条件运算符。

运算符	描述	示例
BETWEEN	位于范围之内	salary BETWEEN 10000 AND 15000
IN	属于列表之中	job_id IN (‘AC_MGR’, ‘HR_REP’, ‘IT_PROG’)
ANY	与子查询的任意结果比较为真时，返回真值；参考后续子查询相关章节	salary > ANY (subquery)
SOME	与子查询的任意结果比较为真时，返回真值；参考后续子查询相关章节	salary = SOME (subquery)
ALL	与子查询的所有结果比较为真时，返回真值；参考后续子查询相关章节	salary != ALL (subquery)
LIKE	匹配某个模式	first_name LIKE ‘Ja%’;
EXISTS	子查询至少返回一条结果时返回真值；参考后续子查询相关章节	EXISTS (subquery);
IS [ NOT ] NULL	测试是否为空值	manager_id IS NULL

SQLite 不支持 ANY、SOME、ALL 运算符

关于子查询的内容，我们将会在高级查询的相关篇章中进行介绍。现以ANY为例，进行简单说明。

-- Not for SQLite
SELECT first_name,
       last_name,
       job_id, 
       salary
  FROM employees
 WHERE salary > ANY (SELECT salary
                       FROM employees 
                      WHERE department_id = 30);

首先，ANY后面的子查询返回 department_id 等于 30 的所有员工的薪水列表，然后查询薪水大于这个列表中任何值的员工信息（也就是大于这个列表中的最小薪水）。
SQL从入门到出门第4篇查询条件

Oracle允许在ANY、SOME、ALL中直接使用列表：
salary = ANY (5000， 6000， 7000);

许多编程语言和工具都能够执行字符串模式匹配，SQL同样也提供了这一功能。

SELECT first_name,
       last_name,
       salary
  FROM employees
 WHERE first_name LIKE 'Ja%';

以上查询返回 first_name 以“Ja”开始的数据行。
SQL从入门到出门第4篇查询条件

在LIKE运算符中，可以使用以下两个通配符：

%，百分号可以匹配零个或者多个字符
_，下划线可以匹配一个字符

例如，“en%”匹配以“en”结束的字符串，“%en%”匹配包含“en”的字符串，“B_g”匹配“Big”、“Bug”等。

如果要匹配通配符（% 或 _）自身，可以在它们前面加上一个反斜杠（\）进行转义。

expression LIKE pattern [ ESCAPE 'escape_character' ];

例如，“25\%” 匹配 “25%”，“25#% ESCAPE ‘#’” 同样匹配 “25%”。

数据库	LIKE运算符	描述
Oracle	OK	Oracle提供了LIKEC、LIKE2、LIKE4用于不同字符集的字符匹配；同时还提供了更为强大的正则表达式函数`REGEXP_LIKE`
MySQL	OK	MySQL支持使用更为强大的正则表达式运算符`REGEXP`、`RLIKE`以及函数`REGEXP_LIKE`
SQL Server	OK	SQL Server支持使用 [ ] 匹配某个范围内的字符，[^ ] 匹配某个范围之外的字符
PostgreSQL	OK	PostgreSQL提供了另一个匹配运算符`SIMILAR TO`（SQL:2016 中已经弃用）；同时还提供了更为强大的正则表达式匹配运算符和函数
Db2	OK	Db2提供了更为强大的正则表达式函数`REGEXP_LIKE`
SQLite	OK	SQLite提供了其他的运算符，包括`GLOB`、`REGEXP`、`MATCH`

对于LIKE后面的模式字符串，Oracle、PostgreSQL、Db2区分大小写：
‘abc’ LIKE ‘Abc’ – 结果为false

在SQL中，NULL是一个特殊值，表示未知数据。对于NULL值的比较，需要使用特殊的运算符：

expression IS NULL;
expression IS NOT NULL;

如果表达式或者列的值为空，IS NULL返回true，IS NOT NULL返回false；如果表达式或者列的值不为空，IS NULL返回false，IS NOT NULL返回true。

例如，查看所有佣金百分比不为空的员工（只有销售部门的员工拥有佣金部分）：

SELECT first_name,
       last_name,
       job_id,
       commission_pct
  FROM employees
 WHERE commission_pct IS NULL;

查询结果如下：
SQL从入门到出门第4篇查询条件

对NULL值执行常规的比较运算，结果总是返回空值：

NULL = 5; -- 结果为空值
NULL = NULL; -- 结果为空值
NULL ！= NULL; -- 结果为空值

复杂逻辑条件

以上的示例中只有一个查询条件，如果想要查看 job_id 等于 “IT_PROG”并且 salary 大于等于 5000 的员工信息，需要使用逻辑运算符指定多个条件：

SELECT first_name,
       last_name,
       salary
  FROM employees
 WHERE job_id = 'IT_PROG'
   AND salary > 5000;

AND运算符指定了两个条件，只有当它们都为真时，结果才为真；否则结果为假，不返回结果。

以上查询的结果如下：
SQL从入门到出门第4篇查询条件

AND运算符的逻辑真值表如下：

	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL

与AND类似的还有OR运算符，它也可以组合多个查询条件；但是OR只要有一个条件为真，结果就为真。

SELECT first_name,
       last_name,
       job_id,
       salary
  FROM employees
 WHERE job_id = 'AD_PRES'
    OR job_id = 'AD_VP';

结果返回了所有 job_id 等于“AD_PRES”或者“AD_VP”的员工。
SQL从入门到出门第4篇查询条件

OR运算符的逻辑真值表如下：

	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

对于逻辑运算符AND和OR，SQL使用短路运算，也就是说，只要前面的表达式能够决定最终的结果，不进行后面的计算。这样能够提高运算效率。因此，以下条件不会产生除零错误：

1 = 0 AND 1/0 = 1;
1 = 1 OR 1/0 = 1;

通过结合运算符AND和OR，可以构造更加复杂的查询条件。假设我们想要查询 job_id 等于 “IT_PROG” 或者 “SA_REP”，并且薪水大于 10000 的员工信息：

SELECT first_name,
       last_name,
       job_id,
       salary
  FROM employees
 WHERE job_id = 'IT_PROG'
    OR job_id = 'SA_REP'
   AND salary > 10000;

查询结果如下：
SQL从入门到出门第4篇查询条件

可以看到返回结果的前5行数据中，薪水都小于 10000，并不符合我们的要求。这是因为操作符的优先级别导致的问题。

SQL中的AND总是在OR之前执行，所以上面的示例实际上查询的是 job_id 等于 “IT_PROG” 的员工，或者job_id 等于 “SA_REP” 同时薪水大于 10000 的员工。

解决这个问题的方法很简单，就是使用圆括号来指定操作的顺序。以上示例的正确写法如下：

SELECT first_name,
       last_name,
       job_id,
       salary
  FROM employees
 WHERE (job_id = 'IT_PROG'
    OR job_id = 'SA_REP')
   AND salary > 10000;

除了AND和OR之外，NOT运算符用于取反操作，它的逻辑真值表如下：

	NOT
TRUE	FALSE
FALSE	TRUE
NULL	NULL

假如我们想要查看薪水大于 20000 的员工，可以直接使用大于号判断，也可以使用以下语句：

SELECT first_name,
       last_name,
       job_id,
       salary
  FROM employees
 WHERE NOT salary <= 20000;

结果如下：
SQL从入门到出门第4篇查询条件

除此之外，NOT还可以结合前面介绍的运算符一起使用：

NOT BETWEEN，位于范围之外
NOT IN，不在列表之中
NOT LIKE，不匹配模式
NOT EXISTS，子查询不存在结果
NOT IS NULL，不为空，等价于IS NOT NULL

最后，当查询条件包含复杂逻辑时，它们的运算优先级从高到低排列如下：

条件运算符	描述
=, !=, <>, <, <=, >, >=	比较运算
IS [NOT] NULL, [NOT] LIKE, [NOT] BETWEEN, [NOT] IN, [NOT] EXISTS	比较运算
NOT	逻辑否定
AND	逻辑与
OR	逻辑或

SQL从入门到出门第4篇查询条件

简单逻辑条件

复杂逻辑条件

SQL从入门到出门第7篇分组与汇总