sql 限定查询
在之前的简单查询之中，是将所有的记录进行显示,但是现在可以对显示的记录进行过滤的操作，而这就是属于限定查询的工作了，限定查询就是在之前语法的基础上增加了一个where子句，用于指定限定条件， 限定如下
SELECT [DISTINCT] *| 字段 [别名] [字段[别名]]
FROM 表名称 [别名]
[WHERE 条件(s)];
在WHERE 子句之后可以增加多个条件，最常见的条件就是基本的关系运算：>、>=、<、<=、<> 、BETWEEN...AND、LIKE、IN、IS NULL、AND、OR、NOT;
例子：要求查询出基本工资高于1500的所有雇员信息。
SQL> SELECT first_name,job_id,salary FROM employees WHERE salary>1500;
FIRST_NAME JOB_ID SALARY
-------------------- ---------- ----------
Donald SH_CLERK 2600
Douglas SH_CLERK 2600
Jennifer AD_ASST 4400
Michael MK_MAN 13000
Pat MK_REP 6000
Susan HR_REP 6500
Hermann PR_REP 10000
Shelley AC_MGR 12000
William AC_ACCOUNT 8300
Steven AD_PRES 24000
Neena AD_VP 17000
例子：查办事员的雇员信息询出所有职务是办事员的雇员信息
SQL> SELECT first_name,job_id,salary FROM employees WHERE job_id='sh_clerk';
no rows selected
这个时候没有返回相应的查询结果，大家知道是怎么回事吗？原因是在oracle数据库中，所有的数据都是区分大小写的。修改如下：
SQL> SELECT first_name,job_id,salary FROM employees WHERE job_id='SH_CLERK';
FIRST_NAME JOB_ID SALARY
-------------------- ---------- ----------
Donald SH_CLERK 2600
Douglas SH_CLERK 2600
Winston SH_CLERK 3200
Jean SH_CLERK 3100
Martha SH_CLERK 2500
Girard SH_CLERK 2800
Nandita SH_CLERK 4200
Alexis SH_CLERK 4100
Julia SH_CLERK 3400
Anthony SH_CLERK 3000
Kelly SH_CLERK 3800
FIRST_NAME JOB_ID SALARY
-------------------- ---------- ----------
Jennifer SH_CLERK 3600
Timothy SH_CLERK 2900
Randall SH_CLERK 2500
Sarah SH_CLERK 4000
Britney SH_CLERK 3900
Samuel SH_CLERK 3200
Vance SH_CLERK 2800
Alana SH_CLERK 3100
Kevin SH_CLERK 3000
20 rows selected.
在以上只是查询一个条件，我们也可以查询多个条件，而这多个条件我们可以用AND或OR进行连接操作。
例子：查询工资在1500-3000之间的全部雇员信息。
SQL> SELECT first_name,job_id,salary FROM employees WHERE salary>=1500 AND salary<=3000;
FIRST_NAME JOB_ID SALARY
-------------------- ---------- ----------
Donald SH_CLERK 2600
Douglas SH_CLERK 2600
Shelli PU_CLERK 2900
Sigal PU_CLERK 2800
Guy PU_CLERK 2600
Karen PU_CLERK 2500
Irene ST_CLERK 2700
James ST_CLERK 2400
Steven ST_CLERK 2200
Mozhe ST_CLERK 2800
James ST_CLERK 2500
FIRST_NAME JOB_ID SALARY
-------------------- ---------- ----------
TJ ST_CLERK 2100
Michael ST_CLERK 2900
Ki ST_CLERK 2400
Hazel ST_CLERK 2200
John ST_CLERK 2700
Joshua ST_CLERK 2500
Randall ST_CLERK 2600
Peter ST_CLERK 2500
Martha SH_CLERK 2500
Girard SH_CLERK 2800
Anthony SH_CLERK 3000
FIRST_NAME JOB_ID SALARY
-------------------- ---------- ----------
Timothy SH_CLERK 2900
Randall SH_CLERK 2500
Vance SH_CLERK 2800
Kevin SH_CLERK 3000
26 rows selected.
例子: 查询出职位是办事员，或者是销售人员的全部信息
SQL> SELECT first_name,job_id,salary FROM employees WHERE job_id='SH_CLERK' OR job_id='SA_MAN';
FIRST_NAME JOB_ID SALARY
-------------------- ---------- ----------
John SA_MAN 14000
Karen SA_MAN 13500
Alberto SA_MAN 12000
Gerald SA_MAN 11000
Eleni SA_MAN 10500
Donald SH_CLERK 2600
Douglas SH_CLERK 2600
Winston SH_CLERK 3200
Jean SH_CLERK 3100
Martha SH_CLERK 2500
Girard SH_CLERK 2800
FIRST_NAME JOB_ID SALARY
-------------------- ---------- ----------
Nandita SH_CLERK 4200
Alexis SH_CLERK 4100
Julia SH_CLERK 3400
Anthony SH_CLERK 3000
Kelly SH_CLERK 3800
Jennifer SH_CLERK 3600
Timothy SH_CLERK 2900
Randall SH_CLERK 2500
Sarah SH_CLERK 4000
Britney SH_CLERK 3900
Samuel SH_CLERK 3200
FIRST_NAME JOB_ID SALARY
-------------------- ---------- ----------
Vance SH_CLERK 2800
Alana SH_CLERK 3100
Kevin SH_CLERK 3000
25 rows selected.
例子：查询出职位是办事员，或者是销售人员的全部信息，且要求这些雇员的工资大于1200
SQL> SELECT first_name,job_id,salary FROM employees WHERE (job_id='SH_CLERK' OR job_id='SA_MAN') and salary>1200;
FIRST_NAME JOB_ID SALARY
-------------------- ---------- ----------
John SA_MAN 14000
Karen SA_MAN 13500
Alberto SA_MAN 12000
Gerald SA_MAN 11000
Eleni SA_MAN 10500
Donald SH_CLERK 2600
Douglas SH_CLERK 2600
Winston SH_CLERK 3200
Jean SH_CLERK 3100
Martha SH_CLERK 2500
Girard SH_CLERK 2800
FIRST_NAME JOB_ID SALARY
-------------------- ---------- ----------
Nandita SH_CLERK 4200
Alexis SH_CLERK 4100
Julia SH_CLERK 3400
Anthony SH_CLERK 3000
Kelly SH_CLERK 3800
Jennifer SH_CLERK 3600
Timothy SH_CLERK 2900
Randall SH_CLERK 2500
Sarah SH_CLERK 4000
Britney SH_CLERK 3900
Samuel SH_CLERK 3200
FIRST_NAME JOB_ID SALARY
-------------------- ---------- ----------
Vance SH_CLERK 2800
Alana SH_CLERK 3100
Kevin SH_CLERK 3000
25 rows selected.
例子：查询所有不是办事员的信息
SQL> SELECT first_name,job_id,salary FROM employees WHERE job_id<>'SH_CLERK';
FIRST_NAME JOB_ID SALARY
-------------------- ---------- ----------
Jennifer AD_ASST 4400
Michael MK_MAN 13000
Pat MK_REP 6000
Susan HR_REP 6500
Hermann PR_REP 10000
Shelley AC_MGR 12000
William AC_ACCOUNT 8300
Steven AD_PRES 24000
Neena AD_VP 17000
Lex AD_VP 17000
Alexander IT_PROG 9000
2.范围判断：BETWEEN....AND
"BETWEEN 最小值 AND 最大值",表示是一个范围间的判断过程。
例子：要求查询出基本工资在1500-3000的雇员信息
SQL> SELECT first_name,job_id,salary FROM employees WHERE salary BETWEEN 1500 AND 3000;
FIRST_NAME JOB_ID SALARY
-------------------- ---------- ----------
Donald SH_CLERK 2600
Douglas SH_CLERK 2600
Shelli PU_CLERK 2900
Sigal PU_CLERK 2800
Guy PU_CLERK 2600
Karen PU_CLERK 2500
Irene ST_CLERK 2700
James ST_CLERK 2400
Steven ST_CLERK 2200
Mozhe ST_CLERK 2800
James ST_CLERK 2500
FIRST_NAME JOB_ID SALARY
-------------------- ---------- ----------
TJ ST_CLERK 2100
Michael ST_CLERK 2900
Ki ST_CLERK 2400
Hazel ST_CLERK 2200
John ST_CLERK 2700
Joshua ST_CLERK 2500
Randall ST_CLERK 2600
Peter ST_CLERK 2500
Martha SH_CLERK 2500
Girard SH_CLERK 2800
Anthony SH_CLERK 3000
FIRST_NAME JOB_ID SALARY
-------------------- ---------- ----------
Timothy SH_CLERK 2900
Randall SH_CLERK 2500
Vance SH_CLERK 2800
Kevin SH_CLERK 3000
26 rows selected.
例子：也可以对BETWEEN .... AND 操作求相反的
SQL> SELECT first_name,job_id,salary FROM employees WHERE NOT salary BETWEEN 1500 AND 3000;
FIRST_NAME JOB_ID SALARY
-------------------- ---------- ----------
Jennifer AD_ASST 4400
Michael MK_MAN 13000
Pat MK_REP 6000
Susan HR_REP 6500
Hermann PR_REP 10000
Shelley AC_MGR 12000
William AC_ACCOUNT 8300
Steven AD_PRES 24000
Neena AD_VP 17000
Lex AD_VP 17000
Alexander IT_PROG 9000
.
.
.
.
.
.
FIRST_NAME JOB_ID SALARY
-------------------- ---------- ----------
Sarah SH_CLERK 4000
Britney SH_CLERK 3900
Samuel SH_CLERK 3200
Alana SH_CLERK 3100
81 rows selected.
以上这个例子只针对数字有效，也对日期也有用。
例子：要求查询在1981年雇佣的全部雇员信息
时间范围：17-JUN-87，21-JUN-99，使用hiredate字段表示雇佣日期；
hiredate 字段上的内容可以使用字符串表示:'17-JUN-87’- '21-JUN-99‘
SQL> SELECT first_name,job_id,salary,hire_date FROM employees WHERE hire_date BETWEEN '17-JUN-87' AND '21-JUN-99';
FIRST_NAME JOB_ID SALARY HIRE_DATE
-------------------- ---------- ---------- ------------------
Donald SH_CLERK 2600 21-JUN-99
Jennifer AD_ASST 4400 17-SEP-87
Michael MK_MAN 13000 17-FEB-96
Pat MK_REP 6000 17-AUG-97
Susan HR_REP 6500 07-JUN-94
Hermann PR_REP 10000 07-JUN-94
Shelley AC_MGR 12000 07-JUN-94
William AC_ACCOUNT 8300 07-JUN-94
Steven AD_PRES 24000 17-JUN-87
Neena AD_VP 17000 21-SEP-89
Lex AD_VP 17000 13-JAN-93
FIRST_NAME JOB_ID SALARY HIRE_DATE
-------------------- ---------- ---------- ------------------
Alexander IT_PROG 9000 03-JAN-90
Bruce IT_PROG 6000 21-MAY-91
David IT_PROG 4800 25-JUN-97
Valli IT_PROG 4800 05-FEB-98
Diana IT_PROG 4200 07-FEB-99
Nancy FI_MGR 12000 17-AUG-94
Daniel FI_ACCOUNT 9000 16-AUG-94
John FI_ACCOUNT 8200 28-SEP-97
Ismael FI_ACCOUNT 7700 30-SEP-97
Jose Manuel FI_ACCOUNT 7800 07-MAR-98
Den PU_MAN 11000 07-DEC-94
FIRST_NAME JOB_ID SALARY HIRE_DATE
-------------------- ---------- ---------- ------------------
Alexander PU_CLERK 3100 18-MAY-95
Shelli PU_CLERK 2900 24-DEC-97
Sigal PU_CLERK 2800 24-JUL-97
Guy PU_CLERK 2600 15-NOV-98
Matthew ST_MAN 8000 18-JUL-96
Adam ST_MAN 8200 10-APR-97
Payam ST_MAN 7900 01-MAY-95
Shanta ST_MAN 6500 10-OCT-97
Julia ST_CLERK 3200 16-JUL-97
Irene ST_CLERK 2700 28-SEP-98
James ST_CLERK 2400 14-JAN-99
FIRST_NAME JOB_ID SALARY HIRE_DATE
-------------------- ---------- ---------- ------------------
Laura ST_CLERK 3300 20-AUG-97
Mozhe ST_CLERK 2800 30-OCT-97
James ST_CLERK 2500 16-FEB-97
TJ ST_CLERK 2100 10-APR-99
Jason ST_CLERK 3300 14-JUN-96
Michael ST_CLERK 2900 26-AUG-98
Renske ST_CLERK 3600 14-JUL-95
Stephen ST_CLERK 3200 26-OCT-97
John ST_CLERK 2700 12-FEB-98
Joshua ST_CLERK 2500 06-APR-98
Trenna ST_CLERK 3500 17-OCT-95
FIRST_NAME JOB_ID SALARY HIRE_DATE
-------------------- ---------- ---------- ------------------
Curtis ST_CLERK 3100 29-JAN-97
Randall ST_CLERK 2600 15-MAR-98
Peter ST_CLERK 2500 09-JUL-98
John SA_MAN 14000 01-OCT-96
Karen SA_MAN 13500 05-JAN-97
Alberto SA_MAN 12000 10-MAR-97
Peter SA_REP 10000 30-JAN-97
David SA_REP 9500 24-MAR-97
Peter SA_REP 9000 20-AUG-97
Christopher SA_REP 8000 30-MAR-98
Nanette SA_REP 7500 09-DEC-98
FIRST_NAME JOB_ID SALARY HIRE_DATE
-------------------- ---------- ---------- ------------------
Janette SA_REP 10000 30-JAN-96
Patrick SA_REP 9500 04-MAR-96
Allan SA_REP 9000 01-AUG-96
Lindsey SA_REP 8000 10-MAR-97
Louise SA_REP 7500 15-DEC-97
Sarath SA_REP 7000 03-NOV-98
Clara SA_REP 10500 11-NOV-97
Danielle SA_REP 9500 19-MAR-99
Lisa SA_REP 11500 11-MAR-97
Harrison SA_REP 10000 23-MAR-98
Tayler SA_REP 9600 24-JAN-98
FIRST_NAME JOB_ID SALARY HIRE_DATE
-------------------- ---------- ---------- ------------------
William SA_REP 7400 23-FEB-99
Elizabeth SA_REP 7300 24-MAR-99
Ellen SA_REP 11000 11-MAY-96
Alyssa SA_REP 8800 19-MAR-97
Jonathon SA_REP 8600 24-MAR-98
Jack SA_REP 8400 23-APR-98
Kimberely SA_REP 7000 24-MAY-99
Winston SH_CLERK 3200 24-JAN-98
Jean SH_CLERK 3100 23-FEB-98
Martha SH_CLERK 2500 21-JUN-99
Nandita SH_CLERK 4200 27-JAN-96
FIRST_NAME JOB_ID SALARY HIRE_DATE
-------------------- ---------- ---------- ------------------
Alexis SH_CLERK 4100 20-FEB-97
Julia SH_CLERK 3400 24-JUN-98
Anthony SH_CLERK 3000 07-FEB-99
Kelly SH_CLERK 3800 14-JUN-97
Jennifer SH_CLERK 3600 13-AUG-97
Timothy SH_CLERK 2900 11-JUL-98
Sarah SH_CLERK 4000 04-FEB-96
Britney SH_CLERK 3900 03-MAR-97
Samuel SH_CLERK 3200 01-JUL-98
Vance SH_CLERK 2800 17-MAR-99
Alana SH_CLERK 3100 24-APR-98
FIRST_NAME JOB_ID SALARY HIRE_DATE
-------------------- ---------- ---------- ------------------
Kevin SH_CLERK 3000 23-MAY-98
89 rows selected.
3.判断是否为空：IS(NOT)NULL
使用此语法以判断某一个字段上的内容是否“null”，但是null和数字0以空字符串是两个概念。
SQL> SELECT first_name,job_id,salary,manager_id FROM employees WHERE manager_id IS NOT NULL;
FIRST_NAME JOB_ID SALARY MANAGER_ID
-------------------- ---------- ---------- ----------
Donald SH_CLERK 2600 124
Douglas SH_CLERK 2600 124
Jennifer AD_ASST 4400 101
Michael MK_MAN 13000 100
Pat MK_REP 6000 201
Susan HR_REP 6500 101
Hermann PR_REP 10000 101
Shelley AC_MGR 12000 101
William AC_ACCOUNT 8300 205
Neena AD_VP 17000 100
Lex AD_VP 17000 100
FIRST_NAME JOB_ID SALARY MANAGER_ID
-------------------- ---------- ---------- ----------
Alexander IT_PROG 9000 102
Bruce IT_PROG 6000 103
David IT_PROG 4800 103
Valli IT_PROG 4800 103
Diana IT_PROG 4200 103
Nancy FI_MGR 12000 101
Daniel FI_ACCOUNT 9000 108
John FI_ACCOUNT 8200 108
Ismael FI_ACCOUNT 7700 108
Jose Manuel FI_ACCOUNT 7800 108
Luis FI_ACCOUNT 6900 108
FIRST_NAME JOB_ID SALARY MANAGER_ID
-------------------- ---------- ---------- ----------
Den PU_MAN 11000 100
Alexander PU_CLERK 3100 114
Shelli PU_CLERK 2900 114
Sigal PU_CLERK 2800 114
Guy PU_CLERK 2600 114
Karen PU_CLERK 2500 114
Matthew ST_MAN 8000 100
Adam ST_MAN 8200 100
Payam ST_MAN 7900 100
Shanta ST_MAN 6500 100
Kevin ST_MAN 5800 100
FIRST_NAME JOB_ID SALARY MANAGER_ID
-------------------- ---------- ---------- ----------
Julia ST_CLERK 3200 120
Irene ST_CLERK 2700 120
James ST_CLERK 2400 120
Steven ST_CLERK 2200 120
Laura ST_CLERK 3300 121
Mozhe ST_CLERK 2800 121
James ST_CLERK 2500 121
TJ ST_CLERK 2100 121
Jason ST_CLERK 3300 122
Michael ST_CLERK 2900 122
Ki ST_CLERK 2400 122
FIRST_NAME JOB_ID SALARY MANAGER_ID
-------------------- ---------- ---------- ----------
Hazel ST_CLERK 2200 122
Renske ST_CLERK 3600 123
Stephen ST_CLERK 3200 123
John ST_CLERK 2700 123
Joshua ST_CLERK 2500 123
Trenna ST_CLERK 3500 124
Curtis ST_CLERK 3100 124
Randall ST_CLERK 2600 124
Peter ST_CLERK 2500 124
John SA_MAN 14000 100
Karen SA_MAN 13500 100
FIRST_NAME JOB_ID SALARY MANAGER_ID
-------------------- ---------- ---------- ----------
Alberto SA_MAN 12000 100
Gerald SA_MAN 11000 100
Eleni SA_MAN 10500 100
Peter SA_REP 10000 145
David SA_REP 9500 145
Peter SA_REP 9000 145
Christopher SA_REP 8000 145
Nanette SA_REP 7500 145
Oliver SA_REP 7000 145
Janette SA_REP 10000 146
Patrick SA_REP 9500 146
FIRST_NAME JOB_ID SALARY MANAGER_ID
-------------------- ---------- ---------- ----------
Allan SA_REP 9000 146
Lindsey SA_REP 8000 146
Louise SA_REP 7500 146
Sarath SA_REP 7000 146
Clara SA_REP 10500 147
Danielle SA_REP 9500 147
Mattea SA_REP 7200 147
David SA_REP 6800 147
Sundar SA_REP 6400 147
Amit SA_REP 6200 147
Lisa SA_REP 11500 148
FIRST_NAME JOB_ID SALARY MANAGER_ID
-------------------- ---------- ---------- ----------
Harrison SA_REP 10000 148
Tayler SA_REP 9600 148
William SA_REP 7400 148
Elizabeth SA_REP 7300 148
Sundita SA_REP 6100 148
Ellen SA_REP 11000 149
Alyssa SA_REP 8800 149
Jonathon SA_REP 8600 149
Jack SA_REP 8400 149
Kimberely SA_REP 7000 149
Charles SA_REP 6200 149
FIRST_NAME JOB_ID SALARY MANAGER_ID
-------------------- ---------- ---------- ----------
Winston SH_CLERK 3200 120
Jean SH_CLERK 3100 120
Martha SH_CLERK 2500 120
Girard SH_CLERK 2800 120
Nandita SH_CLERK 4200 121
Alexis SH_CLERK 4100 121
Julia SH_CLERK 3400 121
Anthony SH_CLERK 3000 121
Kelly SH_CLERK 3800 122
Jennifer SH_CLERK 3600 122
Timothy SH_CLERK 2900 122
FIRST_NAME JOB_ID SALARY MANAGER_ID
-------------------- ---------- ---------- ----------
Randall SH_CLERK 2500 122
Sarah SH_CLERK 4000 123
Britney SH_CLERK 3900 123
Samuel SH_CLERK 3200 123
Vance SH_CLERK 2800 123
Alana SH_CLERK 3100 124
Kevin SH_CLERK 3000 124
106 rows selected.
注意：是106行，少一行。
还可以这样查询：
SQL> SELECT first_name,job_id,salary,manager_id FROM employees WHERE NOT manager_id IS NULL;
FIRST_NAME JOB_ID SALARY MANAGER_ID
-------------------- ---------- ---------- ----------
Donald SH_CLERK 2600 124
Douglas SH_CLERK 2600 124
Jennifer AD_ASST 4400 101
Michael MK_MAN 13000 100
Pat MK_REP 6000 201
Susan HR_REP 6500 101
Hermann PR_REP 10000 101
Shelley AC_MGR 12000 101
William AC_ACCOUNT 8300 205
Neena AD_VP 17000 100
Lex AD_VP 17000 100
FIRST_NAME JOB_ID SALARY MANAGER_ID
-------------------- ---------- ---------- ----------
Alexander IT_PROG 9000 102
Bruce IT_PROG 6000 103
David IT_PROG 4800 103
Valli IT_PROG 4800 103
Diana IT_PROG 4200 103
Nancy FI_MGR 12000 101
Daniel FI_ACCOUNT 9000 108
John FI_ACCOUNT 8200 108
Ismael FI_ACCOUNT 7700 108
Jose Manuel FI_ACCOUNT 7800 108
Luis FI_ACCOUNT 6900 108
FIRST_NAME JOB_ID SALARY MANAGER_ID
-------------------- ---------- ---------- ----------
Den PU_MAN 11000 100
Alexander PU_CLERK 3100 114
Shelli PU_CLERK 2900 114
Sigal PU_CLERK 2800 114
Guy PU_CLERK 2600 114
Karen PU_CLERK 2500 114
Matthew ST_MAN 8000 100
Adam ST_MAN 8200 100
Payam ST_MAN 7900 100
Shanta ST_MAN 6500 100
Kevin ST_MAN 5800 100
FIRST_NAME JOB_ID SALARY MANAGER_ID
-------------------- ---------- ---------- ----------
Julia ST_CLERK 3200 120
Irene ST_CLERK 2700 120
James ST_CLERK 2400 120
Steven ST_CLERK 2200 120
Laura ST_CLERK 3300 121
Mozhe ST_CLERK 2800 121
James ST_CLERK 2500 121
TJ ST_CLERK 2100 121
Jason ST_CLERK 3300 122
Michael ST_CLERK 2900 122
Ki ST_CLERK 2400 122
FIRST_NAME JOB_ID SALARY MANAGER_ID
-------------------- ---------- ---------- ----------
Hazel ST_CLERK 2200 122
Renske ST_CLERK 3600 123
Stephen ST_CLERK 3200 123
John ST_CLERK 2700 123
Joshua ST_CLERK 2500 123
Trenna ST_CLERK 3500 124
Curtis ST_CLERK 3100 124
Randall ST_CLERK 2600 124
Peter ST_CLERK 2500 124
John SA_MAN 14000 100
Karen SA_MAN 13500 100
FIRST_NAME JOB_ID SALARY MANAGER_ID
-------------------- ---------- ---------- ----------
Alberto SA_MAN 12000 100
Gerald SA_MAN 11000 100
Eleni SA_MAN 10500 100
Peter SA_REP 10000 145
David SA_REP 9500 145
Peter SA_REP 9000 145
Christopher SA_REP 8000 145
Nanette SA_REP 7500 145
Oliver SA_REP 7000 145
Janette SA_REP 10000 146
Patrick SA_REP 9500 146
FIRST_NAME JOB_ID SALARY MANAGER_ID
-------------------- ---------- ---------- ----------
Allan SA_REP 9000 146
Lindsey SA_REP 8000 146
Louise SA_REP 7500 146
Sarath SA_REP 7000 146
Clara SA_REP 10500 147
Danielle SA_REP 9500 147
Mattea SA_REP 7200 147
David SA_REP 6800 147
Sundar SA_REP 6400 147
Amit SA_REP 6200 147
Lisa SA_REP 11500 148
FIRST_NAME JOB_ID SALARY MANAGER_ID
-------------------- ---------- ---------- ----------
Harrison SA_REP 10000 148
Tayler SA_REP 9600 148
William SA_REP 7400 148
Elizabeth SA_REP 7300 148
Sundita SA_REP 6100 148
Ellen SA_REP 11000 149
Alyssa SA_REP 8800 149
Jonathon SA_REP 8600 149
Jack SA_REP 8400 149
Kimberely SA_REP 7000 149
Charles SA_REP 6200 149
FIRST_NAME JOB_ID SALARY MANAGER_ID
-------------------- ---------- ---------- ----------
Winston SH_CLERK 3200 120
Jean SH_CLERK 3100 120
Martha SH_CLERK 2500 120
Girard SH_CLERK 2800 120
Nandita SH_CLERK 4200 121
Alexis SH_CLERK 4100 121
Julia SH_CLERK 3400 121
Anthony SH_CLERK 3000 121
Kelly SH_CLERK 3800 122
Jennifer SH_CLERK 3600 122
Timothy SH_CLERK 2900 122
FIRST_NAME JOB_ID SALARY MANAGER_ID
-------------------- ---------- ---------- ----------
Randall SH_CLERK 2500 122
Sarah SH_CLERK 4000 123
Britney SH_CLERK 3900 123
Samuel SH_CLERK 3200 123
Vance SH_CLERK 2800 123
Alana SH_CLERK 3100 124
Kevin SH_CLERK 3000 124
106 rows selected.
例子：查询出所有不属于雇员的信息
SQL> SELECT first_name,job_id,salary,manager_id FROM employees WHERE manager_id IS NULL;
FIRST_NAME JOB_ID SALARY MANAGER_ID
-------------------- ---------- ---------- ----------
Steven AD_PRES 24000
4.指定范围的判断：IN操作符
这个操作符表示是指定一个查询的范围，如下查询：
例子：查询出雇员编号为204、103的雇员信息
SQL> SELECT employee_id,first_name,job_id,salary FROM employees WHERE employee_id=204 OR employee_id=103;
EMPLOYEE_ID FIRST_NAME JOB_ID SALARY
----------- -------------------- ---------- ----------
103 Alexander IT_PROG 9000
204 Hermann PR_REP 10000
如果现在使用了IN的话，则代码简单了
SQL> SELECT employee_id,first_name,last_name,salary FROM employees WHERE employee_id IN (204,103);
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
103 Alexander Hunold 9000
204 Hermann Baer 10000
在如果现在使用的是NOT IN 呢？则表示不在指定的范围之中。
SQL> SELECT employee_id,first_name,last_name,salary FROM employees WHERE employee_id NOT IN (204,103);
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
198 Donald OConnell 2600
199 Douglas Grant 2600
200 Jennifer Whalen 4400
201 Michael Hartstein 13000
202 Pat Fay 6000
203 Susan Mavris 6500
205 Shelley Higgins 12000
206 William Gietz 8300
100 Steven King 24000
101 Neena Kochhar 17000
102 Lex De Haan 17000
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
104 Bruce Ernst 6000
105 David Austin 4800
106 Valli Pataballa 4800
107 Diana Lorentz 4200
108 Nancy Greenberg 12000
109 Daniel Faviet 9000
110 John Chen 8200
111 Ismael Sciarra 7700
112 Jose Manuel Urman 7800
113 Luis Popp 6900
114 Den Raphaely 11000
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
115 Alexander Khoo 3100
116 Shelli Baida 2900
117 Sigal Tobias 2800
118 Guy Himuro 2600
119 Karen Colmenares 2500
120 Matthew Weiss 8000
121 Adam Fripp 8200
122 Payam Kaufling 7900
123 Shanta Vollman 6500
124 Kevin Mourgos 5800
125 Julia Nayer 3200
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
126 Irene Mikkilineni 2700
127 James Landry 2400
128 Steven Markle 2200
129 Laura Bissot 3300
130 Mozhe Atkinson 2800
131 James Marlow 2500
132 TJ Olson 2100
133 Jason Mallin 3300
134 Michael Rogers 2900
135 Ki Gee 2400
136 Hazel Philtanker 2200
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
137 Renske Ladwig 3600
138 Stephen Stiles 3200
139 John Seo 2700
140 Joshua Patel 2500
141 Trenna Rajs 3500
142 Curtis Davies 3100
143 Randall Matos 2600
144 Peter Vargas 2500
145 John Russell 14000
146 Karen Partners 13500
147 Alberto Errazuriz 12000
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
148 Gerald Cambrault 11000
149 Eleni Zlotkey 10500
150 Peter Tucker 10000
151 David Bernstein 9500
152 Peter Hall 9000
153 Christopher Olsen 8000
154 Nanette Cambrault 7500
155 Oliver Tuvault 7000
156 Janette King 10000
157 Patrick Sully 9500
158 Allan McEwen 9000
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
159 Lindsey Smith 8000
160 Louise Doran 7500
161 Sarath Sewall 7000
162 Clara Vishney 10500
163 Danielle Greene 9500
164 Mattea Marvins 7200
165 David Lee 6800
166 Sundar Ande 6400
167 Amit Banda 6200
168 Lisa Ozer 11500
169 Harrison Bloom 10000
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
170 Tayler Fox 9600
171 William Smith 7400
172 Elizabeth Bates 7300
173 Sundita Kumar 6100
174 Ellen Abel 11000
175 Alyssa Hutton 8800
176 Jonathon Taylor 8600
177 Jack Livingston 8400
178 Kimberely Grant 7000
179 Charles Johnson 6200
180 Winston Taylor 3200
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
181 Jean Fleaur 3100
182 Martha Sullivan 2500
183 Girard Geoni 2800
184 Nandita Sarchand 4200
185 Alexis Bull 4100
186 Julia Dellinger 3400
187 Anthony Cabrio 3000
188 Kelly Chung 3800
189 Jennifer Dilly 3600
190 Timothy Gates 2900
191 Randall Perkins 2500
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
192 Sarah Bell 4000
193 Britney Everett 3900
194 Samuel McCain 3200
195 Vance Jones 2800
196 Alana Walsh 3100
197 Kevin Feeney 3000
105 rows selected.
注意：关于NOT IN的问题，如果现在使用IN操作符，查询的范围内有存在null，是不影响查询结果的
SQL> SELECT employee_id,first_name,last_name,salary FROM employees WHERE employee_id IN (204,103,NULL);
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
103 Alexander Hunold 9000
204 Hermann Baer 10000
如果我们现在使用的是NOT IN操作符，如果查询范围之中有NULL，则不会再任何的查询结果返回；
SQL> SELECT employee_id,first_name,last_name,salary FROM employees WHERE employee_id NOT IN (204,103,NULL);
no rows selected
这个是限制语句的特点要记住，后面会讲到NOT IN之中不能出现NULL，要记住如果NOT IN 出现 NULL则表示就是查询全部数据。
5.模糊查询：LIKE子句
LIKE 子句的功能是提供了模糊查找的操作，但是要想使用LIKE子句则必须认识两个匹配符号：
1)匹配单个字符：“_” ->1个
2)匹配任意多个字符：“%” ->0个、1个、多个
例子：要求查询雇员姓名中以字母A开头的全部雇员信息
SQL> SELECT employee_id,first_name,last_name,salary FROM employees WHERE first_name LIKE 'A%';
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
103 Alexander Hunold 9000
115 Alexander Khoo 3100
121 Adam Fripp 8200
147 Alberto Errazuriz 12000
158 Allan McEwen 9000
167 Amit Banda 6200
175 Alyssa Hutton 8800
185 Alexis Bull 4100
187 Anthony Cabrio 3000
196 Alana Walsh 3100
10 rows selected.
例子：要求查询出雇员姓名中第2个字母是A的全部雇员信息
SQL> SELECT employee_id,first_name,last_name,salary FROM employees WHERE first_name LIKE '_a%';
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
202 Pat Fay 6000
105 David Austin 4800
106 Valli Pataballa 4800
108 Nancy Greenberg 12000
109 Daniel Faviet 9000
119 Karen Colmenares 2500
120 Matthew Weiss 8000
122 Payam Kaufling 7900
127 James Landry 2400
129 Laura Bissot 3300
131 James Marlow 2500
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
133 Jason Mallin 3300
136 Hazel Philtanker 2200
143 Randall Matos 2600
146 Karen Partners 13500
151 David Bernstein 9500
154 Nanette Cambrault 7500
156 Janette King 10000
157 Patrick Sully 9500
161 Sarath Sewall 7000
163 Danielle Greene 9500
164 Mattea Marvins 7200
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
165 David Lee 6800
169 Harrison Bloom 10000
170 Tayler Fox 9600
177 Jack Livingston 8400
182 Martha Sullivan 2500
184 Nandita Sarchand 4200
191 Randall Perkins 2500
192 Sarah Bell 4000
194 Samuel McCain 3200
195 Vance Jones 2800
32 rows selected.
例子：要求查询出雇员姓名带有字母A的雇员
SQL> SELECT employee_id,first_name,last_name,salary FROM employees WHERE first_name LIKE '%A%';
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
103 Alexander Hunold 9000
115 Alexander Khoo 3100
121 Adam Fripp 8200
147 Alberto Errazuriz 12000
158 Allan McEwen 9000
167 Amit Banda 6200
175 Alyssa Hutton 8800
185 Alexis Bull 4100
187 Anthony Cabrio 3000
196 Alana Walsh 3100
10 rows selected.
我们可以使用NOT操作，对操作进行求反的功能；
SQL> SELECT employee_id,first_name,last_name,salary FROM employees WHERE first_name NOT LIKE '%A%';
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
198 Donald OConnell 2600
199 Douglas Grant 2600
200 Jennifer Whalen 4400
201 Michael Hartstein 13000
202 Pat Fay 6000
203 Susan Mavris 6500
204 Hermann Baer 10000
205 Shelley Higgins 12000
206 William Gietz 8300
100 Steven King 24000
101 Neena Kochhar 17000
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
102 Lex De Haan 17000
104 Bruce Ernst 6000
105 David Austin 4800
106 Valli Pataballa 4800
107 Diana Lorentz 4200
108 Nancy Greenberg 12000
109 Daniel Faviet 9000
110 John Chen 8200
111 Ismael Sciarra 7700
112 Jose Manuel Urman 7800
113 Luis Popp 6900
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
114 Den Raphaely 11000
116 Shelli Baida 2900
117 Sigal Tobias 2800
118 Guy Himuro 2600
119 Karen Colmenares 2500
120 Matthew Weiss 8000
122 Payam Kaufling 7900
123 Shanta Vollman 6500
124 Kevin Mourgos 5800
125 Julia Nayer 3200
126 Irene Mikkilineni 2700
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
127 James Landry 2400
128 Steven Markle 2200
129 Laura Bissot 3300
130 Mozhe Atkinson 2800
131 James Marlow 2500
132 TJ Olson 2100
133 Jason Mallin 3300
134 Michael Rogers 2900
135 Ki Gee 2400
136 Hazel Philtanker 2200
137 Renske Ladwig 3600
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
138 Stephen Stiles 3200
139 John Seo 2700
140 Joshua Patel 2500
141 Trenna Rajs 3500
142 Curtis Davies 3100
143 Randall Matos 2600
144 Peter Vargas 2500
145 John Russell 14000
146 Karen Partners 13500
148 Gerald Cambrault 11000
149 Eleni Zlotkey 10500
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
150 Peter Tucker 10000
151 David Bernstein 9500
152 Peter Hall 9000
153 Christopher Olsen 8000
154 Nanette Cambrault 7500
155 Oliver Tuvault 7000
156 Janette King 10000
157 Patrick Sully 9500
159 Lindsey Smith 8000
160 Louise Doran 7500
161 Sarath Sewall 7000
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
162 Clara Vishney 10500
163 Danielle Greene 9500
164 Mattea Marvins 7200
165 David Lee 6800
166 Sundar Ande 6400
168 Lisa Ozer 11500
169 Harrison Bloom 10000
170 Tayler Fox 9600
171 William Smith 7400
172 Elizabeth Bates 7300
173 Sundita Kumar 6100
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
174 Ellen Abel 11000
176 Jonathon Taylor 8600
177 Jack Livingston 8400
178 Kimberely Grant 7000
179 Charles Johnson 6200
180 Winston Taylor 3200
181 Jean Fleaur 3100
182 Martha Sullivan 2500
183 Girard Geoni 2800
184 Nandita Sarchand 4200
186 Julia Dellinger 3400
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
188 Kelly Chung 3800
189 Jennifer Dilly 3600
190 Timothy Gates 2900
191 Randall Perkins 2500
192 Sarah Bell 4000
193 Britney Everett 3900
194 Samuel McCain 3200
195 Vance Jones 2800
197 Kevin Feeney 3000
97 rows selected.
对于LIKE子句，不一定只能在字符串上使用，也可以再任意的数据上使用。
SQL> SELECT employee_id,first_name,salary,hire_date FROM employees WHERE employee_id LIKE '2%' OR first_name LIKE 'A%' OR hire_date LIKE '01%';
EMPLOYEE_ID FIRST_NAME SALARY HIRE_DATE
----------- -------------------- ---------- ------------------
200 Jennifer 4400 17-SEP-87
201 Michael 13000 17-FEB-96
202 Pat 6000 17-AUG-97
203 Susan 6500 07-JUN-94
204 Hermann 10000 07-JUN-94
205 Shelley 12000 07-JUN-94
206 William 8300 07-JUN-94
103 Alexander 9000 03-JAN-90
115 Alexander 3100 18-MAY-95
121 Adam 8200 10-APR-97
122 Payam 7900 01-MAY-95
EMPLOYEE_ID FIRST_NAME SALARY HIRE_DATE
----------- -------------------- ---------- ------------------
145 John 14000 01-OCT-96
147 Alberto 12000 10-MAR-97
158 Allan 9000 01-AUG-96
167 Amit 6200 21-APR-00
175 Alyssa 8800 19-MAR-97
185 Alexis 4100 20-FEB-97
187 Anthony 3000 07-FEB-99
194 Samuel 3200 01-JUL-98
196 Alana 3100 24-APR-98
20 rows selected.
注意:如果在模糊查询上不设置任何的查询关键字的话['%%'] 则表示查询全部记录。
SQL> SELECT employee_id,first_name,salary,hire_date FROM employees WHERE employee_id LIKE '%%' OR first_name LIKE '%%' OR hire_date LIKE '%%';
EMPLOYEE_ID FIRST_NAME SALARY HIRE_DATE
----------- -------------------- ---------- ------------------
198 Donald 2600 21-JUN-99
199 Douglas 2600 13-JAN-00
200 Jennifer 4400 17-SEP-87
201 Michael 13000 17-FEB-96
202 Pat 6000 17-AUG-97
203 Susan 6500 07-JUN-94
204 Hermann 10000 07-JUN-94
205 Shelley 12000 07-JUN-94
206 William 8300 07-JUN-94
100 Steven 24000 17-JUN-87
101 Neena 17000 21-SEP-89
EMPLOYEE_ID FIRST_NAME SALARY HIRE_DATE
----------- -------------------- ---------- ------------------
102 Lex 17000 13-JAN-93
103 Alexander 9000 03-JAN-90
104 Bruce 6000 21-MAY-91
105 David 4800 25-JUN-97
106 Valli 4800 05-FEB-98
107 Diana 4200 07-FEB-99
108 Nancy 12000 17-AUG-94
109 Daniel 9000 16-AUG-94
110 John 8200 28-SEP-97
111 Ismael 7700 30-SEP-97
112 Jose Manuel 7800 07-MAR-98
EMPLOYEE_ID FIRST_NAME SALARY HIRE_DATE
----------- -------------------- ---------- ------------------
113 Luis 6900 07-DEC-99
114 Den 11000 07-DEC-94
115 Alexander 3100 18-MAY-95
116 Shelli 2900 24-DEC-97
117 Sigal 2800 24-JUL-97
118 Guy 2600 15-NOV-98
119 Karen 2500 10-AUG-99
120 Matthew 8000 18-JUL-96
121 Adam 8200 10-APR-97
122 Payam 7900 01-MAY-95
123 Shanta 6500 10-OCT-97
EMPLOYEE_ID FIRST_NAME SALARY HIRE_DATE
----------- -------------------- ---------- ------------------
124 Kevin 5800 16-NOV-99
125 Julia 3200 16-JUL-97
126 Irene 2700 28-SEP-98
127 James 2400 14-JAN-99
128 Steven 2200 08-MAR-00
129 Laura 3300 20-AUG-97
130 Mozhe 2800 30-OCT-97
131 James 2500 16-FEB-97
132 TJ 2100 10-APR-99
133 Jason 3300 14-JUN-96
134 Michael 2900 26-AUG-98
EMPLOYEE_ID FIRST_NAME SALARY HIRE_DATE
----------- -------------------- ---------- ------------------
135 Ki 2400 12-DEC-99
136 Hazel 2200 06-FEB-00
137 Renske 3600 14-JUL-95
138 Stephen 3200 26-OCT-97
139 John 2700 12-FEB-98
140 Joshua 2500 06-APR-98
141 Trenna 3500 17-OCT-95
142 Curtis 3100 29-JAN-97
143 Randall 2600 15-MAR-98
144 Peter 2500 09-JUL-98
145 John 14000 01-OCT-96
EMPLOYEE_ID FIRST_NAME SALARY HIRE_DATE
----------- -------------------- ---------- ------------------
146 Karen 13500 05-JAN-97
147 Alberto 12000 10-MAR-97
148 Gerald 11000 15-OCT-99
149 Eleni 10500 29-JAN-00
150 Peter 10000 30-JAN-97
151 David 9500 24-MAR-97
152 Peter 9000 20-AUG-97
153 Christopher 8000 30-MAR-98
154 Nanette 7500 09-DEC-98
155 Oliver 7000 23-NOV-99
156 Janette 10000 30-JAN-96
EMPLOYEE_ID FIRST_NAME SALARY HIRE_DATE
----------- -------------------- ---------- ------------------
157 Patrick 9500 04-MAR-96
158 Allan 9000 01-AUG-96
159 Lindsey 8000 10-MAR-97
160 Louise 7500 15-DEC-97
161 Sarath 7000 03-NOV-98
162 Clara 10500 11-NOV-97
163 Danielle 9500 19-MAR-99
164 Mattea 7200 24-JAN-00
165 David 6800 23-FEB-00
166 Sundar 6400 24-MAR-00
167 Amit 6200 21-APR-00
EMPLOYEE_ID FIRST_NAME SALARY HIRE_DATE
----------- -------------------- ---------- ------------------
168 Lisa 11500 11-MAR-97
169 Harrison 10000 23-MAR-98
170 Tayler 9600 24-JAN-98
171 William 7400 23-FEB-99
172 Elizabeth 7300 24-MAR-99
173 Sundita 6100 21-APR-00
174 Ellen 11000 11-MAY-96
175 Alyssa 8800 19-MAR-97
176 Jonathon 8600 24-MAR-98
177 Jack 8400 23-APR-98
178 Kimberely 7000 24-MAY-99
EMPLOYEE_ID FIRST_NAME SALARY HIRE_DATE
----------- -------------------- ---------- ------------------
179 Charles 6200 04-JAN-00
180 Winston 3200 24-JAN-98
181 Jean 3100 23-FEB-98
182 Martha 2500 21-JUN-99
183 Girard 2800 03-FEB-00
184 Nandita 4200 27-JAN-96
185 Alexis 4100 20-FEB-97
186 Julia 3400 24-JUN-98
187 Anthony 3000 07-FEB-99
188 Kelly 3800 14-JUN-97
189 Jennifer 3600 13-AUG-97
EMPLOYEE_ID FIRST_NAME SALARY HIRE_DATE
----------- -------------------- ---------- ------------------
190 Timothy 2900 11-JUL-98
191 Randall 2500 19-DEC-99
192 Sarah 4000 04-FEB-96
193 Britney 3900 03-MAR-97
194 Samuel 3200 01-JUL-98
195 Vance 2800 17-MAR-99
196 Alana 3100 24-APR-98
197 Kevin 3000 23-MAY-98
107 rows selected.
注意：这个可以帮助用户节约很多的代码，所以一定要记住。
转载于:https://blog.51cto.com/atxstrom/888823
扫一扫
2678
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
