Oracle数据库的子查询关键字该怎么使用?
发布网友
共2个回答
懂视网
为何要用子查询
SQL> --查询工资比SCOTT高的员工信息
SQL> --1. 知道SCOTT的工资
SQL> select sal from emp where ename=‘SCOTT‘;
SAL
----------
3000
SQL> --2. 查询比3000高的员工
SQL> set linesize 120
SQL> col sal for 9999
SQL> select * from emp where sal>3000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
SQL> --子查询解决问题：不能一步求解时，考虑使用子查询
SQL> select *
2 from emp
3 where sal> (select sal
4 from emp
5 where ename=‘SCOTT‘);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
子查询注意的问题
SQL> /*SQL> 注意的问题：
SQL> 1. 子查询应该在括号中
SQL> 2. 子查询相对主查询采用缩进
SQL> 3. 可以在主查询的where from select having后，都可以放子查询
SQL> 4. 在主查询的group by后面，不能放置子查询
SQL> 5. 强调在from后面放置子查询
SQL> 6. 主查询和子查询可以不是同一张表，只要子查询返回的结果，主查询可以使用，即可
SQL> 7. 一般来讲，不在子查询中使用 order by;但在Top-N分析中，必须使用order by
SQL> 8. 单行子查询必须使用单行操作符；多行子查询必须使用多行操作符
SQL> 9. 注意：子查询中的空值
SQL> */
子查询要包含在括号内。 将子查询放在比较条件的右侧。 单行操作符对应单行子查询，多行操作符对应多行子查询。
子查询放置的位置
SQL> -- 3. 可以在主查询的where from select having后，都可以放子查询SQL> select *
2 from (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
ENAME SAL
---------- -----
JAMES 950
FORD 3000
MILLER 1300
已选择14行。
SQL> ed
已写入 file afiedt.buf
1 select e.*
2* from (select ename,sal from emp) e
SQL> /
ENAME SAL
---------- -----
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
ENAME SAL
---------- -----
JAMES 950
FORD 3000
MILLER 1300
已选择14行。
SQL> ---select后放子查询:必须是单行子查询
SQL> select ename,sal,(select job from emp where empno=7839)
2 from emp;
ENAME SAL (SELECTJO
---------- ----- ---------
SMITH 800 PRESIDENT
ALLEN 1600 PRESIDENT
WARD 1250 PRESIDENT
JONES 2975 PRESIDENT
MARTIN 1250 PRESIDENT
BLAKE 2850 PRESIDENT
CLARK 2450 PRESIDENT
SCOTT 3000 PRESIDENT
KING 5000 PRESIDENT
TURNER 1500 PRESIDENT
ADAMS 1100 PRESIDENT
ENAME SAL (SELECTJO
---------- ----- ---------
JAMES 950 PRESIDENT
FORD 3000 PRESIDENT
MILLER 1300 PRESIDENT
已选择14行。
主查询和子查询是否同表
SQL> --6. 主查询和子查询可以不是同一张表，只要子查询返回的结果，主查询可以使用，即可SQL> select ename,sal,(select dname from dept where deptno=10)
2 from emp;
ENAME SAL (SELECTDNAMEFR
---------- ----- --------------
SMITH 800 ACCOUNTING
ALLEN 1600 ACCOUNTING
WARD 1250 ACCOUNTING
JONES 2975 ACCOUNTING
MARTIN 1250 ACCOUNTING
BLAKE 2850 ACCOUNTING
CLARK 2450 ACCOUNTING
SCOTT 3000 ACCOUNTING
KING 5000 ACCOUNTING
TURNER 1500 ACCOUNTING
ADAMS 1100 ACCOUNTING
ENAME SAL (SELECTDNAMEFR
---------- ----- --------------
JAMES 950 ACCOUNTING
FORD 3000 ACCOUNTING
MILLER 1300 ACCOUNTING
已选择14行。
子查询与多表查询对比
SQL> host clsSQL> --查询部门名称为SALES的员工信息
SQL> --子查询
1 select *
2 from emp
3 where deptno=(select deptno
4 from dept
5* where dname=‘SALES‘)
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
已选择6行。
SQL> --多表查询
SQL> select e.*
2 from dept d,emp e
3 where d.dname=‘SALES‘ and d.deptno=e.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
已选择6行。
一个数据库优化问题（子查询与多表查询）
SQL> --如果子查询和多表查询均可，一般采用多表查询 （子查询要查询两次数据库，多表查询查询一次）
单行子查询
子查询结果只返回一行使用单行比较操作符
查询示例：
SELECTlast_name,job_id,salary
FROM employees
WHERE job_id=
(SELECT job_id
FROM employees
WHERE employee_id= 141)
AND salary >
(SELECT salary
FROM employees
WHERE employee_id= 143);
多行子查询
in与not in
SQL> --多行子查询SQL> --in: 在集合中
SQL> --查询部门名称为SALES和ACCOUNTING的员工信息
SQL> --练习：多表查询
SQL> select *
2 from emp
3 where deptno in (select deptno from dept where dname=‘SALES‘ or dname=‘ACCOUNTING‘);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择9行。
SQL> ed
已写入 file afiedt.buf
1 select *
2 from emp
3* where deptno not in (select deptno from dept where dname=‘SALES‘ or dname=‘ACCOUNTING‘)
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
7902 FORD ANALYST 7566 03-12月-81 3000 20
SQL> host cls
any与all查询
any
SQL> --any: 集合中的任意一个SQL> --查询工资比20号部门任意员工工资高的员工信息
SQL> select *
2 from emp
3 where sal > any (select sal from emp where deptno=20);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
已选择13行。
SQL> ed
已写入 file afiedt.buf
1 select *
2 from emp
3* where sal > any (select sal from emp where deptno=10)
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
已选择8行。
all
SQL> --all:和集合的所有值比
SQL> --查询工资比20号部门所有员工工资高的员工信息
SQL> select *
2 from emp
3 ;
from emp
*
第 2 行出现错误:
ORA-00911: 无效字符
SQL> select *
2 from emp
3 where sal > all (select sal from emp where deptno=20);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
SQL> select *
2 from emp
3 where sal > (select min(sal) from emp where deptno=10);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
已选择8行。
SQL> ed
已写入 file afiedt.buf
1 select *
2 from emp
3* where sal > (select max(sal) from emp where deptno=20)
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
SQL> host cls
SQL> --查询不是经理的员工
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO &
热心网友
一、
伪列就像Oracle中的一个表列，但实际上它并未存储在表中。伪列可以从表中查询，但是不能插入、更新或删除它们的值。常用的伪列：rowid和rownum。
Rowid：数据库中的每一行都有一个行地址，Rowid伪列返回该行地址。可以使用Rowid值来定位表中的一行。通常情况下，Rowid值可以唯一地标识数据库中的一行。
Rowid伪列有以下重要用途：
1）能以最快的方式访问表中的一行；
2）能显示表的行是如何存储的。
3）可以作为表中行的唯一标识。
如：SQL>
select
rowid,ename
from
emp;
Rownum：对于一个查询返回的每一行，Rownum伪列返回一个数值代表的次序。返回的第一行的Rownum值为1，第二行的Rownum值为2，依此类推。通过使用Rownum伪列，用户可以*查询返回的行数。
如：SQL>select
*
from
emp
where
rownum<11;
从EMP表中提取10条记录
二、
oracle中不支持select
top
n
from
tablename
查询，但是通过
order
by
和
rownum
组合可以实现此功能。例如：SELECT 列名1．．．列名n FROM
(SELECT 列名1．．．列名n FROM
表名
ORDER
BY
列名1．．．列名n)
WHERE
ROWNUM
<=
N（抽出记录数）
ORDER
BY
ROWNUM
ASC
