过滤和排序数据
一、WHERE子句:限制行
WHERE 子句格式:
select [列名]
from [表名]
where [判断条件];
以上目标是将不满足条件的行过滤掉,其中判断条件中需要注意以下问题:
- 字符和日期包含在单引号中
- 字符大小写敏感,日期格式敏感
- 默认日期格式是DD-MON-RR
1.更改日期的显示格式:
alter session set nls_date_format='yyyy-mm-dd hh24-mi-ss';
将日期格式设置为类似于“2019-07-22 20:00:00”的样式。
2.比较运算符
运算符 | 含义 |
---|---|
= | Equal to |
> | Greater than |
>= | Greater than or equal to |
< | Less than |
<= | Less than or equal to |
<> | Not equal to |
BETWEEN …AND… | Between two values |
IN | Match any of a list of values |
LIKE | Match a character patterm |
IS NULL | Is a null value |
2.1 between…and…范围之间查询
select *
from emp
where sal
between low_val and high_val;
从emp表中列出sal大小在low_val和high_val之间的行的所有列信息。
2.2 in多行查询
select *
from emp
where ename
in ('KING','SCOTT','SMITH');
从emp表中列出ename为KING,SCOTT或SMITH的行的所有列信息。
2.3 like模糊搜索
select *
from emp
where comm is NULL;
从emp表中列出comm是空集的行的所有列信息。
3.逻辑运算符
3.1 and
select *
from emp
where deptno=10
and sal>300;
列出emp表中deptno为10并且sal大于300的行的所有列信息。
3.2 or
select *
from emp
where deptno=10
or deptno=20;
列出emp表中deptno为10或者deptno为20的行的所有列信息。
3.3 not
select *
from emp
where ename
not in ('KING','SCOTT','SMITH');
列出emp表中ename不是KING,SCOTT,SMITH的行的所有列信息。
4.运算优先级
优先级 | 运算符 |
---|---|
1 | 算数运算符( ±*/) |
2 | 连接符 |
3 | 比较符 |
4 | IS [NOT] NULL,LIKE,[NOT] IN |
5 | [NOT] BETWEEN…AND… |
6 | <>,!= |
7 | NOT |
8 | AND |
9 | OR |
二、ORDER BY子句:排序
- ASC(默认):升序
- DESC:降序
select ename,sal*12+nvl(comm,0) as year_sal
from emp
where deptno=20
order by year_sal;
列出emp表中部门编号为20的员工姓名和年薪,并且根据年薪升序排列。
select *
from emp
order by deptno desc,sal asc;
列出emp表中所有列信息,并且首先按照deptno降序,其次按照sal升序排列。
三、绑定变量
使用&和&&临时存储值:&每次都要输入,&&输入一次后会储存。
使用绑定变量,可以替代以下内容:
- WHERE条件
- ORDER BY子句
- 列表达式
- 表名
- 整个SELECT语句
1.替代where条件
实例
SQL> select *
2 from emp
3 where &sal;
Enter value for sal: sal>3000
old 3: where &sal
new 3: where sal>3000
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
2.替代order by子句
实例
SQL> select *
2 from emp
3 where sal>1000
4 order by &sal;
Enter value for sal: sal
old 4: order by &sal
new 4: order by sal
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
3.替代列表达式
实例
SQL> select &name
2 from emp;
Enter value for name: ename,sal
old 1: select &name
new 1: select ename,sal
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
4.替代表名
实例
SQL> select ename,sal
2 from &name;
Enter value for name: emp
old 2: from &name
new 2: from emp
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
5.替代整个select语句
实例
SQL> select &all;
Enter value for all: ename,sal from emp
old 1: select &all
new 1: select ename,sal from emp
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
6.&&变量使用
在第一次定义过&&变量后,后面使用时就会默认为第一次输入的结果,可以减少多次输入。
实例
=================定义员工信息变量
SQL> select &&empinfo
2 from emp;
Enter value for empinfo: empno,ename,sal,deptno,comm,hiredate
old 1: select &&empinfo
new 1: select empno,ename,sal,deptno,comm,hiredate
EMPNO ENAME SAL DEPTNO COMM HIREDATE
---------- ---------- ---------- ---------- ---------- -------------------
7369 SMITH 800 20 1980-12-17 00:00:00
7499 ALLEN 1600 30 300 1981-02-20 00:00:00
7521 WARD 1250 30 500 1981-02-22 00:00:00
7566 JONES 2975 20 1981-04-02 00:00:00
7654 MARTIN 1250 30 1400 1981-09-28 00:00:00
7698 BLAKE 2850 30 1981-05-01 00:00:00
7782 CLARK 2450 10 1981-06-09 00:00:00
7788 SCOTT 3000 20 1987-04-19 00:00:00
7839 KING 5000 10 1981-11-17 00:00:00
7844 TURNER 1500 30 0 1981-09-08 00:00:00
7876 ADAMS 1100 20 1987-05-23 00:00:00
7900 JAMES 950 30 1981-12-03 00:00:00
7902 FORD 3000 20 1981-12-03 00:00:00
7934 MILLER 1300 10 1982-01-23 00:00:00
14 rows selected.
SQL>
=================查看月薪超过2000的员工信息
SQL> select &&empinfo
2 from emp
3 where sal>2000;
old 1: select &&empinfo
new 1: select empno,ename,sal,deptno,comm,hiredate
EMPNO ENAME SAL DEPTNO COMM HIREDATE
---------- ---------- ---------- ---------- ---------- -------------------
7566 JONES 2975 20 1981-04-02 00:00:00
7698 BLAKE 2850 30 1981-05-01 00:00:00
7782 CLARK 2450 10 1981-06-09 00:00:00
7788 SCOTT 3000 20 1987-04-19 00:00:00
7839 KING 5000 10 1981-11-17 00:00:00
7902 FORD 3000 20 1981-12-03 00:00:00
6 rows selected.