上一篇我们介绍了基本的查询语句,学习了如何使用 SELECT 和 FROM 查询表中的数据。
不过,在实际应用中通常并不需要返回表中的全部数据,而只需要找出满足某些条件的结果。比如,某个部门中的员工或者某个产品最近几天的销售情况。在 SQL 中,可以通过查询条件实现数据的过滤。
查询条件
在 SQL 语句中,使用关键字 WHERE 指定查询的过滤条件。以下语句只返回姓名为“刘备”的员工信息:
SELECT * FROM employee WHERE emp_name = '刘备';
其中,WHERE 位于 FROM 之后,用于指定一个或者多个过滤条件;只有满足条件的数据才会返回,其他数据将被忽略。该语句执行的结果如下:
在 SQL 中,WHERE 子句也被称为谓词(Predicate)。
这种通过查询条件过滤数据的操作在关系运算中被称为选择(Selection)。它是针对表进行的水平选择,保留满足条件的行用于生成新的表。以下是选择操作的示意图:
比较运算符
比较运算符可以比较两个数值的大小,包括字符、数字以及日期类型的数据。下表列出了 SQL 中的各种比较运算符:
运算符 | 描述 | 示例 |
---|---|---|
= | 等于 | WHERE emp_id = 1 |
!= 或者 <> | 不等于 | WHERE sex != ‘男’ |
> | 大于 | WHERE salary > 10000 |
>= | 大于等于 | WHERE hire_date >= DATE ‘2018-01-01’ |
< | 小于 | WHERE bonus < 15000 |
<= | 小于等于 | WHERE dept_id <= 2 |
BETWEEN | 位于范围之内 | WHERE salary BETWEEN 10000 AND 15000 |
IN | 属于列表之中 | WHERE emp_name IN (‘刘备’, ‘关羽’, ‘张飞’) |
Oracle 中 ^= 运算符也表示不等于。
这些运算符的作用都比较好理解。我们来看一个日期数据的比较操作,假设想要知道哪些员工在 2018 年 1 月 1 日之后入职,可以使用以下查询:
-- 适用于 Oracle、MySQL 以及 PostgreSQL
SELECT emp_name, hire_date FROM employee WHERE hire_date >= DATE '2018-01-01';
其中,DATE ‘2018-01-01’定义了一个日期类型的常量值。对于 SQL Server,指定日期时可以直接使用字符串字面值表示:
-- 适用于 SQL Server、MySQL 以及 PostgreSQL
SELECT emp_name, hire_date FROM employee WHERE hire_date >= '2018-01-01';
以上两个查询语句的结果如下:
除了我们常见的比较运算符之外,SQL 还提供了两个特殊的比较运算符:BETWEEN 和 IN。
BETWEEN 运算符
如果想要查找一个范围内的数据,可以使用 BETWEEN 运算符。以下示例查询月薪位于 10000 到 15000 之间的员工:
SELECT emp_name, salary FROM employee WHERE salary BETWEEN 10000 AND 15000;
该语句的结果如下:
需要注意的是,BETWEEN 包含了两端的值(10000 和 15000)。
IN 运算符
IN 运算符可以用于查找列表中的值。以下示例查询姓名为“刘备”、“关羽”或者“张飞”的员工:
SELECT emp_id, emp_name FROM employee WHERE emp_name IN ('刘备', '关羽', '张飞');
该查询的结果如下:
只要匹配列表中的任何一个值,都会返回结果。IN 运算符还有一个常见的用途就是子查询的结果匹配,我们将会在第 15 篇中进行介绍。
空值判断
空值(NULL)是 SQL 中的一个特殊值,代表了缺失或者未知的数据。与其他编程语言(例如 Java)不同,SQL 中判断一个值是否为空不能使用等于或者不等于。例如,以下查询尝试找出没有上级领导(manager 字段为空)的员工:
-- 空值判断的错误示例
SELECT emp_name, manager FROM employee WHERE manager = NULL;
该语句没有返回任何结果,但确实存在这样的数据。这个错误的原因在于将一个值与一个未知的值进行数学比较,结果仍然未知;即使是将两个空值进行比较,结果也是未知。以下运算的结果均为未知,用于查询条件的话不会返回任何结果:
NULL = 5;
NULL = NULL;
NULL != NULL;
那么,如何判断某个值是否为空值呢?在 SQL 中需要使用两个特殊的运算符:
expression IS NULL;
expression IS NOT NULL;
如果表达式 expression 的值为空,IS NULL 返回真,IS NOT NULL 返回假;如果表达式的值不为空,IS NULL 返回假,IS NOT NULL 返回真。因此,查找没有上级领导的员工应该使用以下语句:
SELECT emp_name, manager FROM employee WHERE manager IS NULL;
emp_name|manager|
--------|-------|
刘备 |[NULL] |
“刘备”是公司的最高领导,他没有上级领导。
如果仅仅能够指定单个过滤条件,就无法满足复杂的查询需求;为此,SQL 引入了用于构建复杂条件的逻辑运算符。
复合条件
借助于逻辑代数中的逻辑运算,SQL 提供了三个逻辑运算符:
- AND,逻辑与运算符。只有当两边的条件都为真时,结果才为真,返回数据;否则,不返回数据。
- OR,逻辑或运算符。只要有一个条件为真,结果就为真,返回数据;否则,不返回数据。
- NOT,逻辑非运算符。用于将判断结果取反,真变为假,假变为真;空值取反后仍然为空值。
AND 运算符
以下示例使用 AND 运算符查找性别为“女”,并且月薪超过 10000 的员工:
“孙尚香”是女性员工,并且月薪为 12000。SELECT emp_name, sex, salary FROM employee WHERE sex = '女' AND salary > 10000;
emp_name|sex|salary |
--------|---|--------|
孙尚香 |女 |12000.00|
OR 运算符
使用 OR 运算符查找姓名为“刘备”、“关羽”或者“张飞”的员工:
SELECT emp_name, sex, salary FROM employee WHERE emp_name = '刘备' OR emp_name = '关羽' OR emp_name = '张飞';
短路运算
对于逻辑运算符 AND 和 OR,SQL 使用短路运算(short-circuit evaluation)。也就是说,只要前面的表达式能够决定最终的结果,不执行后面的计算。这样能够提高运算效率。因此,以下语句不会产生除零错误:
SELECT 'AND' FROM employee WHERE 1 = 0 AND 1/0 = 1; SELECT 'OR' FROM employee WHERE 1 = 1 OR 1/0 = 1;
第一个查询由于 AND 左边的结果为假,肯定不会返回任何结果,因此也就不会计算 1/0;第二个查询由于 OR 左边的结果为真,一定会返回结果,同样不会产生除零错误。
NOT 运算符
NOT 运算符可以结合其他的运算符一起使用,用于对查询条件的结果取反:
- NOT BETWEEN,位于范围之外。
- NOT IN,不在列表之中。
- NOT LIKE,不匹配某个模式。LIKE 运算符用于字符串的模糊查找,将在下一篇中进行介绍。
- NOT EXISTS,子查询中不存在结果。关于子查询和 EXISTS 运算符,将在第 16 篇中进行介绍。
- NOT IS NULL,不为空。等价于 IS NOT NULL。
以下示例查找除了“刘备”、“关羽”以及“张飞”之外的其他员工:
SELECT emp_id, emp_name FROM employee WHERE emp_name NOT IN ('刘备', '关羽', '张飞');
该语句的结果如下(显示部分内容):
将多个逻辑运算符进行组合,可以构造任意复杂的查询条件。不过,需要注意不同的运算符之间的优先级问题。
运算符优先级
假如我们想要知道人力资源部(dept_id = 2)或者财务部门(dept_id = 3)中,哪些员工拥有奖金。如果使用以下查询:
SELECT emp_name, dept_id, bonus FROM employee WHERE dept_id = 2 OR dept_id = 3 AND bonus IS NOT NULL;
获得的结果如下:
其中有两条数据并不是我们期望的结果(“黄忠”和“魏延”并没有奖金)。那么问题出在哪里了呢?因为 AND 运算符比 OR 运算符的优先级高,所以该查询返回的是人力资源部(dept_id = 2)的员工,或者财务部(dept_id = 3)中拥有奖金的员工。
如果想要获得我们期望的结果,可以使用圆括号调整运算符的优先级:
SELECT emp_name, dept_id, bonus FROM employee WHERE (dept_id = 2 OR dept_id = 3) AND bonus IS NOT NULL;
正确的结果如下:
以下是 SQL 中各种条件运算符按照优先级从高到低进行的排列;必要时可以使用圆括号进行调整。
- =、!=、<>、<、<=、>、>=
- IS [NOT] NULL、[NOT] LIKE、[NOT] BETWEEN、[NOT] IN、[NOT] EXISTS
- NOT
- AND
- OR
除了使用查询条件过滤数据之外,SQL 还提供了一种特殊的数据选择操作:去除查询结果中的重复值。
去除重复值
SQL 使用 DISTINCT 关键字去除查询结果中的重复数据。例如,以下查询返回了员工表所有可能的性别:
SELECT DISTINCT sex FROM employee;
sex|
---|
男 |
女 |
首先,DISTINCT 位于 SELECT 之后而不是像其他过滤条件一样位于 WHERE 之后;其次,查询结果中重复的记录只会出现一次。
与 DISTINCT 相反的是 ALL,用于返回不去重的结果。我们通常不需要加上 ALL 关键字,因为它是默认的行为。另外,为了消除重复值,数据库系统需要对结果进行排序,然后扫描重复值;因此,大量数据的重复值处理可能会降低查询的速度。
Oracle 中的 UNIQUE 等价于 DISTINCT,MySQL 中的 DISTINCTROW 等价于 DISTINCT。
小结
在 SQL 中使用 WHERE 子句指定一个或者多个过滤条件,可以查找满足要求的数据。SQL 查询条件中支持各种比较运算符、逻辑运算符以及空值判断等。另外,DISITINCT 关键字可以去除查询结果中的重复记录。
思考题:查找 2018 年 1 月 1 日之后入职,月薪小于 5000,并且奖金小于 1000(包括没有奖金)的员工。