[Oracle数据库学习]二、过滤和排序数据

D1

使用WHERE语句在查询时过滤行

SELECT *|{[DISTINCT] column|expression[alias],...}
FROM table_name
[WHERE condition(s)];

WHERE语句紧跟着FROM语句

SELECT *
FROM employees;

EMPLOYEE_ID	EMPLOYEE_NAME	DEPARTMENT_ID	JOB_ID	SALARY	COMMISSION_PCT
1	Alice	1	PM	10000	(null)
2	Bob	1	DEV	5000	0.12
3	Cindy	2	QA	6000	0.1
4	Donald	2	AM	30000	0.3

 

过滤数字

SELECT *
FROM employees
WHERE department_id = 2；

EMPLOYEE_ID	EMPLOYEE_NAME	DEPARTMENT_ID	JOB_ID	SALARY	COMMISSION_PCT
3	Cindy	2	QA	6000	0.1
4	Donald	2	AM	30000	0.3

 

过滤字符和日期

1）字符和日期要包含在单引号中；
2）字符大小写敏感；
3）日期格式敏感，默认的日期格式是DD-MON-RR。

SELECT *
FROM employees
WHERE employee_name = 'Bob';

EMPLOYEE_ID	EMPLOYEE_NAME	DEPARTMENT_ID	JOB_ID	SALARY	COMMISSION_PCT
2	Bob	1	DEV	5000	0.12

 

比较运算

操作符	含义
=	等于
>	大于
>=	大于等于
<	小于
<=	小于等于
<>	不等于

SELECT employee_name, salary
FROM employees
WHERE salary < 10000 ;

EMPLOYEE_NAME	SALARY
Bob	5000
Cindy	6000

 

其他比较运算

操作符	含义
BETWEEN...AND...	在两个值之间（包含边界）
IN(set)	等于值列表中的一个
LIKE	模糊查询
IS NULL	空值

使用BETWEEN运算显示一个区间中的值。

SELECT employee_name, salary
FROM employees
WHERE salary BETWEEN 5000 AND 10000 ;

EMPLOYEE_NAME	SALARY
Alice	10000
Bob	5000
Cindy	6000

使用IN运算显示列表中的值。

SELECT employee_name, job_id
FROM employees
WHERE job_id IN ('PM', 'AM') ;

EMPLOYEE_NAME	JOB_ID
Alice	PM
Donald	AM

使用LIKE运算显示类似的值。

条件可以包含字符或数字：
1）%（百分号）代表一个或多个字符。
2）_（下划线）代表一个字符。

SELECT employee_name
FROM employees
WHERE employee_name LIKE 'C%' ;

EMPLOYEE_NAME

Cindy

可以同时使用%和_。

SELECT employee_name
FROM employees
WHERE employee_name LIKE '_o%' ;

EMPLOYEE_NAME

Bob

Donald

使用ESCAPE选择%或_符号（将%或_作为普通字符）。

SELECT employee_name, description
FROM employees
WHERE description LIKE '%c\%%' ESCAPE '\';

EMPLOYEE_NAME	DESCRIPTION
Bob	abc%%
Donald	ac%d

 

NULL

使用NULL判断空值。

SELECT employee_name, commission_pct
FROM employees
WHERE commission_pct is NULL;

EMPLOYEE_NAME	COMMISSION_PCT
Alice	(null)

 

逻辑运算

操作符	含义
AND	逻辑并/逻辑与
OR	逻辑或
NOT	逻辑否

 

AND：要求与关系为真。

SELECT employee_name, job_id, salary
FROM employees
WHERE job_id like '%M%' 
AND salary > 8000;

EMPLOYEE_NAME	JOB_ID	SALARY
Alice	PM	10000
Donald	AM	30000

 

OR：要求或关系为真。

SELECT employee_name, job_id, salary
FROM employees
WHERE job_id LIKE '%M%' 
OR salary > 5000;

EMPLOYEE_NAME	JOB_ID	SALARY
Alice	PM	10000
Cindy	QA	6000
Donald	AM	4000

 

NOT：要求非为真。

SELECT employee_name, job_id, salary
FROM employees
WHERE job_id NOT LIKE '%M%';

EMPLOYEE_NAME	JOB_ID	SALARY
Bob	DEV	5000
Cindy	QA	6000

 

优先级

优先级	运算符	示例
1	算术运算符	*
2	连接符	||
3	比较符	>
4	逻辑运算中NULL/LIKE/IN	IS [NOT] NULL LIKE [NOT] IN
5	逻辑运算中BETWEEN	[NOT] BETWEEN
6	逻辑运算中NOT	NOT
7	逻辑运算中AND	AND
8	逻辑运算中OR	OR

 

SELECT employee_name, job_id, salary
FROM employees
WHERE job_id LIKE '%M%' OR job_id = 'DEV' AND salary > 6000;

EMPLOYEE_NAME	JOB_ID	SALARY
Alice	PM	10000
Donald	AM	4000
Elsa	DEV	8000

注意：AND比OR优先级高，查询结果为job_id包含M（Alice和Donald）和salary>6000的DEV（Elsa）。

加括号提高优先级。

SELECT employee_name, job_id, salary
FROM employees
WHERE ( job_id LIKE '%M%' OR job_id = 'DEV' ) AND salary > 6000;

EMPLOYEE_NAME	JOB_ID	SALARY
Alice	PM	10000
Elsa	DEV	8000

注意：此时查的是job_id包含M或为DEV，同时salary需大于6000，因此过滤掉了Donald。

 

ORDER_BY子句

使用ORDER_BY子句进行排序：ASC表示升序，DESC表示降序。

ORDER_BY子句需要放在SELECT语句的末尾。

SELECT employee_name, salary, hire_date
FROM employees
ORDER BY hire_date;

EMPLOYEE_NAME	SALARY	HIRE_DATE
Elsa	8000	2007-11-13T00:00:00Z
Alice	10000	2007-11-15T00:00:00Z
Cindy	6000	2010-04-15T00:00:00Z
Bob	5000	2017-03-10T00:00:00Z
Donald	4000	2019-01-05T00:00:00Z

 

降序

SELECT employee_name, salary, hire_date
FROM employees
ORDER BY hire_date DESC;

EMPLOYEE_NAME	SALARY	HIRE_DATE
Donald	4000	2019-01-05T00:00:00Z
Bob	5000	2017-03-10T00:00:00Z
Cindy	6000	2010-04-15T00:00:00Z
Alice	10000	2007-11-15T00:00:00Z
Elsa	8000	2007-11-13T00:00:00Z

注意：笔者采用的环境非SQL*Plus，因此日期显示非默认格式。

 

按别名排序

SELECT employee_name, salary*12 annsal
FROM employees
ORDER BY annsal;

EMPLOYEE_NAME	ANNSAL
Donald	48000
Bob	60000
Cindy	72000
Elsa	96000
Alice	120000

 

多个列排序

SELECT employee_name, department_id, salary*12 annsal
FROM employees
ORDER BY department_id, annsal;

EMPLOYEE_NAME	DEPARTMENT_ID	ANNSAL
Bob	1	60000
Alice	1	120000
Donald	2	48000
Cindy	2	72000
Elsa	2	96000

 

可以使用不在SELECT列表中的列排序。

SELECT employee_name, department_id, salary*12 annsal
FROM employees
ORDER BY hire_date;

EMPLOYEE_NAME	DEPARTMENT_ID	ANNSAL
Elsa	2	96000
Alice	1	120000
Cindy	2	72000
Bob	1	60000
Donald	2	48000

注：可以参考ORDER_BY子句处的按hire_date排序的查询结果。

 

总结：

1）使用WHERE子句过滤数据：使用比较运算、BETWEEN...AND...、IN、LIKE和NULL、逻辑运算AND、OR、NOT；

2）使用ORDER BY子句进行排序：升序/降序；使用多个列；使用没有SELECT的列。