oracle之过滤和排序数据

oracle 过滤和排序数据

需求：返回在 90号部门工作的所有员工的信息

1.1 sql语句(where子句)

SELECT employee_id, last_name, job_id, department_id
FROM   employees
WHERE  department_id = 90 ;

1.2 sql（字符串）

SQL> SELECT last_name, job_id, department_id
  2  FROM   employees
  3  WHERE  last_name = 'Whalen';

LAST_NAME                 JOB_ID     DEPARTMENT_ID
------------------------- ---------- -------------
Whalen                    AD_ASST               10

1.3 sql（日期）

SELECT last_name, hire_date, department_id
FROM   employees
WHERE  hire_date = '7-6月-1994'

注意
字符和日期要包含在单引号中。
字符大小写敏感，日期格式敏感。
默认的日期格式是 DD-MON月-RR。

1.4 比较运算

操作符	含义
=	等于(不是==)
>	大于
>=	大于等于
<	小于
<=	小于等于
<>或!=	不等于
:=	赋值
BETWEEN…AND…	在两个值之间
IN(set)	等于值列表种的一个
LIKE	模糊查询
IS NULL	空值

举例BETWEEN…AND…

SQL> SELECT last_name, salary
  2  FROM   employees
  3  WHERE  salary BETWEEN 2500 AND 3500;

LAST_NAME                     SALARY
------------------------- ----------
Rajs                         3500.00
Davies                       3100.00
Matos                        2600.00

举例IN

SQL> SELECT employee_id, last_name, salary, manager_id
  2  FROM   employees
  3  WHERE  manager_id IN (100, 101, 201);
EMPLOYEE_ID LAST_NAME                     SALARY MANAGER_ID
----------- ------------------------- ---------- ----------
        101 Kochhar                     17000.00        100
        200 Whalen                       4400.00        101
        202 Fay                          6000.00        201

举例LIKE

% 代表零个或多个字符(任意个字符)。
_ 代表一个字符
first_name以S开头

SQL> SELECT	first_name
  2  FROM 	employees
  3  WHERE	first_name LIKE 'S%';
FIRST_NAME
--------------------
Steven
Shelley
Shelli

last_name第二个字母为o

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

回避特殊符号：使用转义符。例如：将[%]转为[%]、[_]转为[_]，然后再加上[ESCAPE ‘\’] 即可。

SELECT job_id
FROM   jobs
WHERE  job_id LIKE 'IT\_%' escape '\';

使用 IS (NOT) NULL 判断MANAGER_ID空值。

SQL> SELECT last_name, manager_id
  2  FROM   employees
  3  WHERE  manager_id IS NULL;
LAST_NAME                 MANAGER_ID
------------------------- ----------
King                      
1 row selected

1.5 逻辑运算

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

举例AND

SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary >=10000
AND  job_id LIKE '%MAN%';

举例OR

SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary >= 10000
OR  job_id LIKE '%MAN%';

举例NOT

SELECT last_name, job_id
FROM   employees
WHERE  job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');

优先级（可以使用括号改变优先级）

优先级
1	算数运算符
2	连接符
3	比较符
4	IS [NOT] NULL,LIKE,[NOT] IN
5	[NOT] BETWEEN
6	NOT
7	AND
8	OR

1.6 ORDER BY子句

注意
使用 ORDER BY 子句排序
ASC（ascend）: 升序（小到大）
DESC（descend）: 降序（大到小）
ORDER BY 子句在SELECT语句的结尾。

举例按 hire_date 日期排序升序（默认是ASC）

SQL> SELECT last_name, job_id, department_id, hire_date
  2  FROM  employees
  3  ORDER BY hire_date ;
LAST_NAME                 JOB_ID     DEPARTMENT_ID HIRE_DATE
------------------------- ---------- ------------- -----------
King                      AD_PRES               90 1987/6/17
Whalen                    AD_ASST               10 1987/9/17
Kochhar                   AD_VP                 90 1989/9/21

举例按 hire_date 日期排序倒序（DESC）

SQL> SELECT last_name, job_id, department_id, hire_date
  2  FROM employees
  3  ORDER BY hire_date DESC ;

LAST_NAME                 JOB_ID     DEPARTMENT_ID HIRE_DATE
------------------------- ---------- ------------- -----------
Kumar                     SA_REP                80 2000/4/21
Banda                     SA_REP                80 2000/4/21
Ande                      SA_REP                80 2000/3/24

举例按别名排序

SQL> SELECT employee_id, last_name, salary*12 annsal
  2  FROM   employees
  3  ORDER BY annsal;

EMPLOYEE_ID LAST_NAME                     ANNSAL
----------- ------------------------- ----------
        132 Olson                          25200
        128 Markle                         26400
        135 Gee                            28800

举例先按department_id升序排序（如果department_id相同），再按salary倒序排序

SELECT last_name, department_id, salary
FROM   employees
ORDER BY department_id, salary DESC;

总结

使用WHERE 子句过滤数据
使用比较运算
使用 BETWEEN AND, IN, LIKE和 NULL运算
使用逻辑运算符 AND, OR和NOT
使用 ORDER BY 子句进行排序。

SELECT     *|{[DISTINCT] column|expression [alias],...}
FROM       table
[WHERE     condition(s)]
[ORDER BY  {column, expr, alias} [ASC|DESC]];