11.限制和排序语句
1.限制行使用
--where子句
--比较条件用 = ， <= ，between， in ， like 和 null
--逻辑条件用and， or， not 操作符
--1.1限制行的查询使用where子句
--1.2where自居放在from子句后面
hr@ORCLPDB01 2023-02-19 09:50:14> r
1 select employee_id, last_name,job_id,department_id
2 from employees
3* where department_id = 90
EMPLOYEE_ID LAST_NAME JOB_ID DEPARTMENT_ID
----------- ------------------------- ---------- -------------
100 King AD_PRES 90
101 Kochhar AD_VP 90
102 De Haan AD_VP 90
Elapsed: 00:00:00.00
--1.3字符与日期
--字符，日期使用单引号引起来
--字符区分大小写，日期格式敏感
--默认日期格式显示：DD-MON-RR
hr@ORCLPDB01 2023-02-19 09:53:57> r
1 select employee_id, last_name,job_id,department_id
2 from employees
3* where last_name = 'Whalen'
EMPLOYEE_ID LAST_NAME JOB_ID DEPARTMENT_ID
----------- ------------------------- ---------- -------------
200 Whalen AD_ASST 10
Elapsed: 00:00:00.02
hr@ORCLPDB01 2023-02-19 09:55:11> r
1 select employee_id, last_name,job_id,department_id
2 from employees
3* where hire_date = '2002-06-07'
EMPLOYEE_ID LAST_NAME JOB_ID DEPARTMENT_ID
----------- ------------------------- ---------- -------------
203 Mavris HR_REP 40
204 Baer PR_REP 70
205 Higgins AC_MGR 110
206 Gietz AC_ACCOUNT 110
Elapsed: 00:00:00.002.比较操作符
--使用比较操作符
hr@ORCLPDB01 2023-02-19 09:57:43> select last_name, salary
2 from employees
3 where salary <= 3000 ;
LAST_NAME SALARY
------------------------- ----------
Baida 2900
Tobias 2800
Himuro 2600
Colmenares 2500
Mikkilineni 2700
Landry 2400
Markle 2200
Atkinson 2800
Marlow 2500
Olson 2100
Rogers 2900
Gee 2400
Philtanker 2200
Seo 2700
Patel 2500
Matos 2600
Vargas 2500
Sullivan 2500
Geoni 2800
Cabrio 3000
Gates 2900
Perkins 2500
Jones 2800
Feeney 3000
OConnell 2600
Grant 2600
26 rows selected.
Elapsed: 00:00:00.00
--限制数据在某个范围用between
hr@ORCLPDB01 2023-02-19 09:59:56> r
1 select last_name, salary
2 from employees
3* where salary between 2500 and 3000
LAST_NAME SALARY
------------------------- ----------
Baida 2900
Tobias 2800
Himuro 2600
Colmenares 2500
Mikkilineni 2700
Atkinson 2800
Marlow 2500
Rogers 2900
Seo 2700
Patel 2500
Matos 2600
Vargas 2500
Sullivan 2500
Geoni 2800
Cabrio 3000
Gates 2900
Perkins 2500
Jones 2800
Feeney 3000
OConnell 2600
Grant 2600
21 rows selected.
Elapsed: 00:00:00.01
--使用in关系条件显示数据一半是可以被穷举的值
hr@ORCLPDB01 2023-02-19 10:01:53> 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 100
102 De Haan 17000 100
114 Raphaely 11000 100
120 Weiss 8000 100
121 Fripp 8200 100
122 Kaufling 7900 100
123 Vollman 6500 100
124 Mourgos 5800 100
145 Russell 14000 100
146 Partners 13500 100
147 Errazuriz 12000 100
148 Cambrault 11000 100
149 Zlotkey 10500 100
201 Hartstein 13000 100
108 Greenberg 12008 101
200 Whalen 4400 101
203 Mavris 6500 101
204 Baer 10000 101
205 Higgins 12008 101
202 Fay 6000 201
20 rows selected.
Elapsed: 00:00:00.00
--使用like条件来执行通配符搜索有效的字符串值
--搜索条件可以包含文字字符串或数字
-- % 表示零或者多个字符
-- _ 表示一个字符
hr@ORCLPDB01 2023-02-19 10:06:22> select first_name
2 from employees
3 where first_name like 'S%' ;
FIRST_NAME
--------------------
Sundar
Shelli
Sarah
Shelley
Steven
Sundita
Steven
Susan
Samuel
Sarath
Stephen
Sigal
Shanta
13 rows selected.
Elapsed: 00:00:00.01
hr@ORCLPDB01 2023-02-19 10:07:36> r
1 select first_name
2 from employees
3* where first_name like '_o%'
FIRST_NAME
--------------------
Mozhe
John
Louise
Douglas
Donald
Joshua
John
John
Jonathon
Jose Manuel
10 rows selected.
Elapsed: 00:00:00.00
-- 通配符
--模式匹配字符你可能使用的有%和_
--使用escape转义%与_匹配字符为本来含义
--使用null条件
--nulls 使用is null或者is not null 进行比较操作
hr@ORCLPDB01 2023-02-19 10:12:19> select last_name,manager_id
2 from employees
3 where manager_id is null;
LAST_NAME MANAGER_ID
------------------------- ----------
King
Elapsed: 00:00:00.003.逻辑条件
-- and要求所有条件都是true
hr@ORCLPDB01 2023-02-19 10:14:32> select employee_id,last_name,job_id,salary
2 from employees
3 where salary >= 10000
4 and job_id like '%MAN%';
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
114 Raphaely PU_MAN 11000
145 Russell SA_MAN 14000
146 Partners SA_MAN 13500
147 Errazuriz SA_MAN 12000
148 Cambrault SA_MAN 11000
149 Zlotkey SA_MAN 10500
201 Hartstein MK_MAN 13000
7 rows selected.
Elapsed: 00:00:00.00
-- or要求任意一个条件是true
hr@ORCLPDB01 2023-02-19 10:16:51> r
1 select employee_id,last_name,job_id,salary
2 from employees
3 where salary >= 10000
4* or job_id like '%MAN%'
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
100 King AD_PRES 24000
101 Kochhar AD_VP 17000
102 De Haan AD_VP 17000
108 Greenberg FI_MGR 12008
114 Raphaely PU_MAN 11000
120 Weiss ST_MAN 8000
121 Fripp ST_MAN 8200
122 Kaufling ST_MAN 7900
123 Vollman ST_MAN 6500
124 Mourgos ST_MAN 5800
145 Russell SA_MAN 14000
146 Partners SA_MAN 13500
147 Errazuriz SA_MAN 12000
148 Cambrault SA_MAN 11000
149 Zlotkey SA_MAN 10500
150 Tucker SA_REP 10000
156 King SA_REP 10000
162 Vishney SA_REP 10500
168 Ozer SA_REP 11500
169 Bloom SA_REP 10000
174 Abel SA_REP 11000
201 Hartstein MK_MAN 13000
204 Baer PR_REP 10000
205 Higgins AC_MGR 12008
24 rows selected.
Elapsed: 00:00:00.00
-- not操作符
hr@ORCLPDB01 2023-02-19 10:17:49> select last_name, job_id
2 from employees
3 where job_id not in ('IT_PROG','ST_CLERK','SA_REP');
LAST_NAME JOB_ID
------------------------- ----------
Baer PR_REP
Baida PU_CLERK
Bell SH_CLERK
Bull SH_CLERK
Cabrio SH_CLERK
Cambrault SA_MAN
Chen FI_ACCOUNT
Chung SH_CLERK
Colmenares PU_CLERK
De Haan AD_VP
Dellinger SH_CLERK
Dilly SH_CLERK
Errazuriz SA_MAN
Everett SH_CLERK
Faviet FI_ACCOUNT
Fay MK_REP
Feeney SH_CLERK
Fleaur SH_CLERK
Fripp ST_MAN
Gates SH_CLERK
Geoni SH_CLERK
Gietz AC_ACCOUNT
Grant SH_CLERK
Greenberg FI_MGR
Hartstein MK_MAN
Higgins AC_MGR
Himuro PU_CLERK
Jones SH_CLERK
Kaufling ST_MAN
Khoo PU_CLERK
King AD_PRES
Kochhar AD_VP
Mavris HR_REP
McCain SH_CLERK
Mourgos ST_MAN
OConnell SH_CLERK
Partners SA_MAN
Perkins SH_CLERK
Popp FI_ACCOUNT
Raphaely PU_MAN
Russell SA_MAN
Sarchand SH_CLERK
Sciarra FI_ACCOUNT
Sullivan SH_CLERK
Taylor SH_CLERK
Tobias PU_CLERK
Urman FI_ACCOUNT
Vollman ST_MAN
Walsh SH_CLERK
Weiss ST_MAN
Whalen AD_ASST
Zlotkey SA_MAN
52 rows selected.
Elapsed: 00:00:00.004.规则有限原则
注意：可以使用括号强制改变规则优先顺序
hr@ORCLPDB01 2023-02-19 10:21:09> select last_name, job_id,salary
2 from employees
3 where job_id = 'SA_REP'
4 or job_id = 'AD_PRES'
5 and salary > 15000;
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------
King AD_PRES 24000
Tucker SA_REP 10000
Bernstein SA_REP 9500
Hall SA_REP 9000
Olsen SA_REP 8000
Cambrault SA_REP 7500
Tuvault SA_REP 7000
King SA_REP 10000
Sully SA_REP 9500
McEwen SA_REP 9000
Smith SA_REP 8000
Doran SA_REP 7500
Sewall SA_REP 7000
Vishney SA_REP 10500
Greene SA_REP 9500
Marvins SA_REP 7200
Lee SA_REP 6800
Ande SA_REP 6400
Banda SA_REP 6200
Ozer SA_REP 11500
Bloom SA_REP 10000
Fox SA_REP 9600
Smith SA_REP 7400
Bates SA_REP 7300
Kumar SA_REP 6100
Abel SA_REP 11000
Hutton SA_REP 8800
Taylor SA_REP 8600
Livingston SA_REP 8400
Grant SA_REP 7000
Johnson SA_REP 6200
31 rows selected.
Elapsed: 00:00:00.00
hr@ORCLPDB01 2023-02-19 10:22:03> select last_name, job_id,salary
2 from employees
3 where (job_id = 'SA_REP'
4 or job_id = 'AD_PRES')
5 and salary > 15000;
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------
King AD_PRES 24000
Elapsed: 00:00:00.015.使用order by子句
--排序检索行的order by子句
--ASC 升序排列 默认
--DESC 降序排列
-- order by 子句放在select语句末尾
hr@ORCLPDB01 2023-02-19 10:22:44> 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
------------------------- ---------- ------------- -------------------
De Haan AD_VP 90 2001-01-13 00:00:00
Gietz AC_ACCOUNT 110 2002-06-07 00:00:00
Baer PR_REP 70 2002-06-07 00:00:00
Mavris HR_REP 40 2002-06-07 00:00:00
Higgins AC_MGR 110 2002-06-07 00:00:00
--降序排列
hr@ORCLPDB01 2023-02-19 10:26:53> r
1 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 2008-04-21 00:00:00
Banda SA_REP 80 2008-04-21 00:00:00
Ande SA_REP 80 2008-03-24 00:00:00
Markle ST_CLERK 50 2008-03-08 00:00:00
Lee SA_REP 80 2008-02-23 00:00:00
--排序用列别名
hr@ORCLPDB01 2023-02-19 10:26:55> 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
136 Philtanker 26400
135 Gee 28800
127 Landry 28800
--排序用列所在数字位
hr@ORCLPDB01 2023-02-19 10:29:07> r
1 select employee_id,last_name,salary*12 annsal
2 from employees
3* order by 3
EMPLOYEE_ID LAST_NAME ANNSAL
----------- ------------------------- ----------
132 Olson 25200
128 Markle 26400
136 Philtanker 26400
135 Gee 28800
127 Landry 28800
--多列排序
hr@ORCLPDB01 2023-02-19 10:29:57> r
1 select employee_id,last_name,salary*12 annsal
2 from employees
3* order by 2,3
EMPLOYEE_ID LAST_NAME ANNSAL
----------- ------------------------- ----------
174 Abel 132000
166 Ande 76800
130 Atkinson 33600
105 Austin 57600
204 Baer 120000
注意：
数字按照数字的大小排序，日期按照时间顺序排列，字符按照字母顺序排列，排序认为NULL是最大值
6.替代变量
--使用替代变量
----临时变量使用&，调用替代变量用&&
--替代变量可以使用在：
---- where子句
---- order by子句
---- 列表达式
---- 表名
---- 整个语句
--字符和日期使用变量要用单引号引起来
hr@ORCLPDB01 2023-02-19 10:35:16> select last_name,department_id,salary*12
2 from employees
3 where job_id = '&job_title'
4 ;
Enter value for job_title: IT_PROG
old 3: where job_id = '&job_title'
new 3: where job_id = 'IT_PROG'
LAST_NAME DEPARTMENT_ID SALARY*12
------------------------- ------------- ----------
Hunold 60 108000
Ernst 60 72000
Austin 60 57600
Pataballa 60 57600
Lorentz 60 50400
Elapsed: 00:00:00.00
--define命令
--define命令创建和分配一个值到一个变量
--undefine取消已定义的变量
hr@ORCLPDB01 2023-02-19 10:38:09> define employee_num = 200
hr@ORCLPDB01 2023-02-19 10:38:23> select employee_id, last_name,salary,department_id
2 from employees
3 where employee_id = &employee_num;
old 3: where employee_id = &employee_num
new 3: where employee_id = 200
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
200 Whalen 4400 10
Elapsed: 00:00:00.00
hr@ORCLPDB01 2023-02-19 10:39:13> undefine employee_num
