《Oracle 入门教程》第 05 篇 数据过滤
作者: 不剪发的Tony老师
毕业于北京航空航天大学,十多年数据库管理与开发经验,目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址:https://tonydong.blog.csdn.net
文章目录
5.1 过滤条件
5.2 模式匹配
5.3 空值判断
5.4 复合条件
5.5 去除重复数据
上一篇介绍了如何使用 SELECT 语句查询表中的数据,查询返回了全部的数据行。本篇我们讨论如何利用WHERE子句返回满足条件的数据,以及使用DISTINCT去除查询结果中的重复值。
5.1 过滤条件
在实际应用中,我们通常不需要查看表中的全部数据,而是返回满足指定条件的数据;例如找出某天入职的员工,某个用户未完成的订单等。此时,我们就可以利用WHERE子句来实现数据的过滤。
SELECT column1, column2, ...
FROM table_name
WHERE conditions;
其中,WHERE 子句位于 FROM 子句之后,conditions 用于指定一个或者多个逻辑条件。对于表中的数据行,如果满足该条件就会返回,否则将被忽略。例如:
SELECT emp_name, hire_date, salary
FROM employee
WHERE hire_date = DATE '2018-11-11';
EMP_NAME|HIRE_DATE |SALARY|
--------|-------------------|------|
邓芝 |2018-11-11 00:00:00| 4000|
其中,WHERE 子句中的等于号(=)是一个比较运算符,只有比较的结果为真(True)时才返回对应的数据行。因此,以上语句返回了 2018 年 11 月 11 日入职的员工。
除了等号运算符之外,Oracle 还提供了很多其他的比较运算符,如下表所示:
以上这些运算符的作用都比较容易理解,需要注意BETWEEN包含了两端的值,等价于>=加上<=。例如:
SELECT emp_name,
salary
FROM employee
WHERE salary BETWEEN 11000 AND 12000;
EMP_NAME|SALARY|
--------|------|
孙尚香 | 12000|
运算符IN可以实现一次和多个数据的等于比较,例如:
SELECT emp_name,
salary
FROM employee
WHERE salary IN (10000 ,12000, 15000);
EMP_NAME|SALARY|
--------|------|
孙尚香 | 12000|
赵云 | 15000|
法正 | 10000|
以上查询返回了月薪为 10000、12000 或者 15000 的员工。该查询也可以使用ANY或者SOME运算符实现:
SELECT emp_name,
salary
FROM employee
WHERE salary =ANY (10000 ,12000, 15000);
EMP_NAME|SALARY|
--------|------|
孙尚香 | 12000|
赵云 | 15000|
法正 | 10000|
📝SOME 和 ANY 是同义词,可以互相替换。
运算符ALL表示和列表中的所有数据比较,例如:
SELECT emp_name,
salary
FROM employee
WHERE salary >ALL (10000 ,12000, 15000);
EMP_NAME|SALARY|
--------|------|
刘备 | 30000|
关羽 | 26000|
张飞 | 24000|
诸葛亮 | 24000|
查询返回的是月薪大于列表中所有数据的员工,也就是大于 15000 的员工。
📝ANY/SOME 以及 ALL 通常和 =、!=、>、>=、 <、<= 等一起使用。同时,IN、ANY/SOME 以及 ALL 中还可以使用子查询语句,我们在后续文涨中进行介绍。
5.2 模式匹配
Oracle 提供了字符串的模式匹配功能,也就是LIKE运算符。例如查找名字中包含“飞”字的员工:
SELECT emp_name,
salary
FROM employee
WHERE emp_name LIKE '%飞%';
其中,百分号(%)是一个通配符,表示匹配零个或者多个任意字符。另外,也可以使用下划线(_)匹配一个任意字符。例如:
“%飞”匹配以“飞”字结束的字符串;
“诸_亮”匹配“诸葛亮”、“诸云亮”等;
“SQL”匹配“SQL”,但是不能匹配“sql”,因为 LIKE 运算符区分大小写。
如果查找的模式自身包含这两个通配符(% 或 _),可以通过ESCAPE子句指定一个的转义字符。例如:
SELECT 'OK'
FROM dual
WHERE '完成进度 25%。' LIKE '%25\%。' ESCAPE '\';
'OK'|
----|
OK |
语句中的反斜线被定义为转义字符,\% 表示匹配百分号,而不是任意字符。
另外,NOT LIKE运算符执行 LIKE 相反的操作。例如:
SELECT emp_name,
salary
FROM employee
WHERE emp_name NOT LIKE '%飞%';
EMP_NAME|SALARY|
--------|------|
刘备 | 30000|
关羽 | 26000|
诸葛亮 | 24000|
...
以上语句返回了名字中不包含“飞”字的员工。
5.3 空值判断
在数据库中,空值(NULL)是一个特殊值,代表了未知数据或者不适用的场景。Oracle 中的 NULL 不等于数字 0,也不等于空字符串。如果使用常规的比较运算符进行 NULL 值进行比较,结果总是未知。
NULL = 0; -- 结果未知
NULL = NULL; -- 结果未知
NULL != NULL; -- 结果未知
我们既不确定 NULL 等于 NULL,也不确定 NULL 不等于 NULL,因为任何值和未知数据比较的结果总是未知。如果在 WHERE 子句中使用这种查询条件,将不会返回任何结果。
对于 NULL 值的比较,需要使用特殊的运算符:IS NULL。例如:
SELECT emp_name, manager
FROM employee
WHERE manager IS NULL;
EMP_NAME|MANAGER|
--------|-------|
刘备 | |
以上查询返回了没有上级领导的员工,也就是公司的最高领导。
另外,IS NOT NULL运算符执行与IS NULL相反的操作,返回取值不为空的数据。例如:
SELECT emp_name, manager
FROM employee
WHERE manager IS NOT NULL;
EMP_NAME|MANAGER|
--------|-------|
关羽 | 1|
张飞 | 1|
诸葛亮 | 1|
...
5.4 复合条件
Oracle 中的复合条件是指通过逻辑运算符(AND、OR、NOT)将多个条件进行组合的结果。
AND表示逻辑与运算符,它的逻辑真值表如下:
对于AND运算符,只有当它两边的结果都为真时,最终结果才为真;否则最终结果为假,不返回结果。以下查询返回薪水为 4000,并且有奖金的员工:
SELECT emp_name, salary, bonus
FROM employee
WHERE salary = 4000
AND bonus IS NOT NULL;
EMP_NAME|SALARY|BONUS|
--------|------|-----|
蒋琬 | 4000| 1500|
OR表示逻辑或运算符,它的逻辑真值表如下:
OR运算符只要有一个条件为真,结果就为真。以下查询返回薪水为 4000,或者有奖金的员工:
SELECT emp_name, salary, bonus
FROM employee
WHERE salary = 4000
OR bonus IS NOT NULL;
EMP_NAME|SALARY|BONUS|
--------|------|-----|
刘备 | 30000|10000|
...
蒋琬 | 4000| 1500|
邓芝 | 4000| |
逻辑运算符AND和OR使用短路运算;也就是说,只要前面的表达式能够决定最终的结果,省略后面的计算。这样能够提高运算的效率。因此,以下语句不会产生除零错误:
SELECT 1 FROM dual WHERE 1 = 0 AND 1/0 = 1;
SELECT 1 FROM dual WHERE 1 = 1 OR 1/0 = 1;
另外,当我们组合使用AND和OR运算符时,AND运算符优先级更高,先执行。例如:
SELECT emp_name, salary, bonus
FROM employee
WHERE salary = 4000
OR salary = 6000
AND bonus IS NOT NULL;
EMP_NAME|SALARY|BONUS|
--------|------|-----|
蒋琬 | 4000| 1500|
邓芝 | 4000| |
由于 AND 优先级高,以上查询返回的是薪水为 6000 并且有奖金的员工,或者月薪为 4000 的员工。如果相要返回月薪为 4000 或 6000,并且有奖金的员工,可以使用括号:
SELECT emp_name, salary, bonus
FROM employee
WHERE (salary = 4000 OR salary = 6000)
AND bonus IS NOT NULL;
EMP_NAME|SALARY|BONUS|
--------|------|-----|
蒋琬 | 4000| 1500|
NOT表示逻辑取反运算符,它的逻辑真值表如下:
注意,对于未知的NULL值,经过NOT处理之后仍然是未知值。
NOT经常和前面介绍的运算符一起使用:
NOT BETWEEN,位于指定范围之外;
NOT IN,不在指定列表之中;
NOT LIKE,不匹配某个模式。
当查询条件包含复合逻辑时,它们的运算优先级从高到低排列如下:
如有必要,可以使用括号调整多个运算符之间的优先级。
5.5 去除重复数据
DISTINCT是一种特殊的运算符,可以去除查询结果中的重复记录。例如:
SELECT DISTINCT sex
FROM employee;
SEX|
---|
男 |
女 |
其中,DISTINCT 位于 SELECT 之后,而不是像其他过滤条件一样位于 WHERE 之后。如果 DISTINCT 之后指定了多个字段或表达式,表示返回它们组合之后的唯一值:
SELECT DISTINCT dept_id, sex
FROM employee;
DEPT_ID|SEX|
-------|---|
3|女 |
1|男 |
2|男 |
5|男 |
4|男 |
4|女 |
📝Oracle 中的UNIQUE等价于 DISTINCT,但是不属于 SQL 标准。
与 DISTINCT 相反的关键字是ALL,表示返回全部结果。我们通常不需要加上 ALL 关键字,因为它是默认值。