锐客网

  1. 首页 > 生活百科 > it技术 > >

oracle如何子查询 oracle 子查询和连接查询 效率

IT技术查询

oracle使用(五)_子查询_分页rownumsql允许多次嵌套oracle如何子查询，子查询即嵌套其oracle如何子查询他查询中得查询
可把子查询返回结果理解成一张表oracle如何子查询，外层查询会把子查询返回的结果当成一张表
子查询要用括号括起来
将子查询放在比较运算符的右边oracle如何子查询，增强可读性
子查询的分类oracle如何子查询：
单行子查询：子查询返回一行记录，可使用单行比较运算符
多行子查询：子查询返回多行记录
--有哪些雇员薪水在平均薪水之上
--1、先求平均薪水
select avg(nvl(e.sal,0)) from emp e;
--2.把所有人的薪水和平均薪水作比较，使用的单行比较运算符
select * from emp e where e.sal(select avg(nvl(e.sal,0)) from emp e);
-- 查询雇员表哪些人是经理人
-- 1.先查询所有经理人 distinct去重(多行子查询)
select distinct e.mgr from emp e;
--2.在雇员表中过滤是经理的
select * from emp where empno in (select distinct e.mgr from emp e);
-- 每个部门的平均薪水等级
--1.求出每个部门的平均薪水等级
select e.deptno,avg(nvl(e.sal,0)) from emp e group by e.deptno;
--2.根据部门平均薪水断出薪水等级
select g.deptno,sg.grade from salgrade sg join (select e.deptno,avg(nvl(e.sal,0)) avgsal from emp e group by e.deptno) g on g.avgsal between sg.losal and sg.hisal;
--1.求平均薪水最高的部门的部门编号
-- 求部门平均薪水
select e.deptno,avg(nvl(e.sal,0)) from emp e group by e.deptno;
-- 求最高的薪水
select max(t.vsal) from (select e.deptno,avg(nvl(e.sal,0)) vsal from emp e group by e.deptno) t;
-- 求部门编号（重复的sql可以抽取为视图）
select t.deptno,t.vsal from (select e.deptno,avg(nvl(e.sal,0)) vsal from emp e group by e.deptno) t where t.vsal = (select max(t.vsal) from (select e.deptno,avg(nvl(e.sal,0)) vsal from emp e group by e.deptno) t);
)
--2.求部门的平均薪水的等级
--3.求部门平均的薪水等级 (薪水等级的平均)
--求每个人的薪水等级
select e.deptno, sg.grade
from emp e
join salgrade sg
on e.sal between sg.losal and sg.hisal;
--求部门的平均薪水等级
select t.deptno, avg(t.grade)
from (select e.deptno, sg.grade
from emp e
join salgrade sg
on e.sal between sg.losal and sg.hisal) t
group by t.deptno;
--4.求薪水最高的前5名员工
-- 所有员工薪水降序
select * from emp e order by e.sal desc;
-- oracle中没有limit子句，rownum只存在于查询出来的虚拟表中
select * from (select * from emp e order by e.sal desc) t where rownum =5;
--5.求薪水最高的第6到10名
-- 默认对于基表 rownum，跟插入顺序有关
select rownum rn,t.* from emp t;
-- 对于子查询rownum跟内层查询的顺序有关
--rn顺序是根据内层查询记录进行分配的
select rownum rn,t.* from (select * from emp e order by e.sal desc) t;
-- 根据rn顺序来过滤第6到10条
select * from (select rownum rn,t.* from (select * from emp e order by e.sal desc) t) t1 where t1.rn 5 and t1.rn =10;
Oracle数据库中 查询高于自己部门平均工资的员工信息 用相关子查询怎么做?。?/h2>每个部门俯场碘渡鄢盗碉醛冬互大于所有人平均工资的员工oracle如何子查询：
Select BM,YG From TB WHERE Salary(Select Avg(Salary) From TB)
每个部门大于所在部门所有人平均工资的员工oracle如何子查询：
Select BM,YG From TB
Join (Select BM,Avg(Salary) as AvgSalary From TB
Group By BM) As Temp
On TB.BM=Temp.BM
where SalaryTemp.AvgSalary
SELECT DEPT.DNAME, EMP.ENAME, (EMP.SALNVL(EMP.COMM, 0)) AS SC
FROM EMP,
DEPT,
(SELECT DEPT.DEPTNO AS D, AVG(EMP.SALNVL(EMP.COMM, 0)) ESC
FROM DEPT, EMP
WHERE EMP.DEPTNO = DEPT.DEPTNO
GROUP BY DEPT.DEPTNO) T
WH俯场碘渡鄢盗碉醛冬互ERE EMP.DEPTNO = DEPT.DEPTNO
AND EMP.DEPTNO = T.D
AND (EMP.SALNVL(EMP.COMM, 0))T.ESC;
select a.empname, b.deptname ,a.salary
from emp as a,
( select d俯场碘渡鄢盗碉醛冬互eptid,deptname ,avg(salary) c from dept group by deptid,deptname ) b
where a.deptid=b.deptid
and a.salary b.c
一、单行子查询：
1、单行子查询指在子查询中只返回单行值oracle如何子查询，这种子查询需要使用单行比较运算符 ， 包括=、、=、、=、 。
2、单行子查询出现在WHERE子句中 ， 如下所示：
3、单行子查询中有分组函数，要求分组函数返回的是单行数据 。如下图为查询出薪水比本部门平均薪水高的员工信息：
4、单行子查询出现在HAVING子句中，如下图为查询出平均薪水高于部门30最高薪水的部门信息 。
二、多行子查询：
如果子查询返回oracle如何子查询了多行，则主查询中的比较操作符应该使用多行比较操作符 。Oracle数据库中的多行比较操作符包括IN、ALL、ANY，其中ALL和ANY不能单独使用，需要配合单行比较操作符、=、、=一起使用 。
1、多行子查询中使用IN：
IN后面是值的列表，只不过这里的值是子查询查出来的多个数据 。如下图：
三、多行子查询中使用ANY操作符：
ANY操作符不能单独使用，必须和、等比较操作符一起使用 。ANY表示大于列表中任何一个数据即为TRUE；ANY表示小于列表中的任何数据即为TRUE 。具体实现如下图：
Oracle通用函数，分组函数，子查询 注意 ）Oracle中oracle如何子查询的字符串是严格区分大小写oracle如何子查询的 日期和字符只能在单引号中出现
）把列与列 列与字符连接在一起用 || 符号
）列的别名 紧跟列名 也可以在列名和别名之间加入关键字 AS 以便在别名中包含空格或特殊的字符并区分大小写 使用双引号
例子
SELECTlast_name||is a||job_idASEmployee Details
FROMemployees
wherefirst_namelike%s_ ;
通用函数
空值是无效的 未指定的 未知的或不可预知的值 空值不是空格或者 包含空值的数学表达式的值都为空值
这些函数适用于任何数据类型 同时也适用于空值
NVL (expr expr )
NVL (expr expr expr )
NULLIF (expr expr )
COALESCE (expr expr exprn)
（ ）NVL (expr expr ) expr 为NULL 返回expr 不为NULL 返回expr 注意两者的类型要一致
（ ）NVL (expr expr expr ) expr 不为NULL 返回expr 为NULL 返回expr expr 和expr 类型不同的话 expr 会转换为expr 的类型
（ ）NULLIF (expr expr ) 相等返回NULL 不等返回expr
（ ）COALESCE(expr expr exprn) 列表中第一个非空的表达式是函数的返回值 如果所有的表达式都是空值 最终将返回一个空值
条件表达式在SQL语句中使用IF THEN ELSE 逻辑 可以使用两种方法:–CASE表达式CASEexprWHENparison_exprTHENreturn_expr[WHENparison_exprTHENreturn_exprWHENparison_exprnTHENreturn_exprnELSEelse_expr]END
–DECODE函数DECODE ( col | expressionsearchresult[ search result ][ default] )
分组函数
分组函数作用于一组数据 并对一组数据返回一个值
组函数语法
SELECT [column ] group_function(column)
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];
注意 在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中
(所用包含于SELECT列表中 而未包含于组函数中的列都必须包含于GROUPBY子句中)
但包含在GROUP BY 子句中的列不必包含在SELECT列表中
且可以在在GROUP BY子句中包含多个列
不能在WHERE子句中使用组函数 但可以在HAVING子句中使用组函数
使用HAVING过滤分组
行已经被分组
使用oracle如何子查询了组函数
满足HAVING子句中条件的分组将被显示
SELECT column group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
子查询
语法
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
注意 子查询(内查询) 在主查询之前一次执行完成
子查询的结果被主查询使用(外查询)
子查询要包含在括号内
将子查询放在比较条件的右侧
除非进行Top N 分析 否则不要在子查询中使用ORDER BY子句
单行操作符对应单行子查询 多行操作符对应多行子查询
单行操作符operator多行操作符operator
=等于IN等于列表中的任何一个
大于ANY和子查询返回的任意一个值比较
=大于等于ALL和子查询返回的所有值比较
小于EXISTS功能等价于IN
=小于等于
不等于
DECODE和CASE SELECT中除GROUP BY 子句以外的所有子句中 都可以使用单行子查询
在子查询中使用HAVING子句
SELECT select_list
FROM table
[GROUP BY group_by_expression]
[HAVING group_condition] expr operator
(SELECT select_list
FROM table);
在FROM子句中使用子查询 例子
SELECT a last_name a salary a department_id b salavg
FROM employees a (SELECT department_id AVG(salary) salavg
FROM employees
GROUP BY department_id) b
WHERE a department_id = b department_id
ANDa salaryb salavg;
有两个简单例子 以说明 exists 和 in 的效率问题
) select * from T where exists(select from T where T a=T a) ;
T 数据量小而T 数据量非常大时 T T 时 ) 的查询效率高
) select * from T where T a in (select T a from T ) ;
T 数据量非常大而T 数据量小时 T T 时 ) 的查询效率高
exists 用法
请注意 ）句中的有颜色字体的部分 理解其含义
其中 select from T where T a=T a 相当于一个关联表查询 相当于
select from T Twhere T a=T a
但是 如果oracle如何子查询你当当执行 ） 句括号里的语句 是会报语法错误的 这也是使用exists需要注意的地方
exists（xxx） 就表示括号里的语句能不能查出记录 它要查的记录是否存在
因此 select 这里的 其实是无关紧要的 换成 * 也没问题 它只在乎括号里的数据能不能查找出来 是否存在这样的记录 如果存在 这 ） 句的where 条件成立
in 的用法
继续引用上面的例子
) select * from T where T a in (select T a from T )
这里的 in 后面括号里的语句搜索出来的字段的内容一定要相对应 一般来说 T 和T 这两个表的a字段表达的意义应该是一样的 否则这样查没什么意义
打个比方 T T 表都有一个字段 表示工单号 但是T 表示工单号的字段名叫 ticketid T 则为 id 但是其表达的意义是一样的 而且数据格式也是一样的 这时 用 ）的写法就可以这样
select * from T where T ticketid in (select T id from T )
EXISTS操作符
EXISTS 操作符检查在子查询中是否存在满足条件的行
如果在子查询中存在满足条件的行
– 不在子查询中继续查找
– 条件返回TRUE
如果在子查询中不存在满足条件的行
– 条件返回FALSE
– 继续在子查询中查找
相关子查询
相关子查询按照一行接一行的顺序执行 主查询的每一行都执行一次子查询 子查询使用了主查询中的列
SELECTcolumn column
FROMtable outer
WHERE columnk operator (SELECTcolum colum
FROMtable
WHERE expr= outer expr);
相关更新
使用相关子查询依据一个表中的数据更新另一个表的数据
UPDATEtable alias
SETcolumn = (SELECTexpression
FROMtablealias
WHERElumn = lumn);
相关删除
使用相关子查询依据一个表中的数据删除另一个表的数据
DELETE FROMtable alias
WHEREcolumn operator (SELECTexpression
FROMtable alias
WHERE lumn = lumn);
WITH子句
使用WITH子句 可以避免在SELECT语句中重复书写相同的语句块
WITH子句将该子句中的语句块执行一次 并存储到用户的临时表空间中
使用WITH子句可以提高查询效率
oracle如何子查询我们可以使用WITHClause来定义一个queryblock 然后在SELECTstatement的其它地方使用这个queryblock 如果在一个很复杂的 Query 里 我们必须重复地使用某个 subquery 那么使用WITHClause可以降低Query的复杂度以及提高 performance 因为WITHClause 所读出的资料会暂存在User的temporarytablespace中
WITH子句应用举例
WITH
dept_costs AS (SELECTd department_name SUM(e salary) AS dept_total
FROMemployees e departments d
WHEREe department_id = d department_id
GROUP BYd department_name)
avg_costAS (SELECTSUM(dept_total)/COUNT(*) AS dept_avg
FROMdept_costs)
SELECT*
FROMdept_costs
WHEREdept_total(SELECTdept_avg
FROMavg_cost)
ORDER BYdepartment_name;
GROUP BY 扩展
带有ROLLUP和CUBE操作的GROUP BY子句
使用带有ROLLUP和CUBE操作的GROUP BY子句产生多种分组结果
ROLLUP产生n种分组结果 其是对GROUP BY子句的扩展
CUBE产生 的n次方种分组结果 其是对GROUP BY子句的扩展
注 其中的n指的是group_by_expression的数目
ROLLUP操作符 ROLLUP产生n种分组结果 顺序是从右向左
SELECT[column ] group_function(column)
FROMtable
[WHERE condition]
[GROUP BY [ROLLUP] group_by_expression]
[HAVING having_expression]
[ORDER BY column];
CUBE操作符 CUBE会产生类似于笛卡尔集的分组结果
SELECT[column ] group_function(column)
FROMtable
[WHERE condition]
[GROUP BY [CUBE] group_by_expression]
[HAVING having_expression]
[ORDER BY column];
GROUPING函数 能够实现更加直观的分组结果显示提示
SELECT[column ] group_function(column) [GROUPING(group_by_expression)]
FROMtable
[WHERE condition]
[GROUP BY[ROLLUP] [CUBE] group_by_expression]
[HAVING having_expression]
[ORDER BY column];
GROUPING函数可以和CUBE或ROLLUP结合使用
使用GROUPING函数 可以找到哪些列在该行中参加了分组
使用GROUPING函数 可以区分空值产生的原因
GROUPING函数返回 或
GROUPING SETS
GROUPING SETS是对GROUP BY子句的进一步扩充
使用GROUPING SETS可以实现在同一个查询中定义多个分组集
Oracle 对GROUPING SETS子句指定的分组集进行分组后用UNION ALL操作将各分组结果结合起来
Grouping set 的优点
– 只进行一次分组即可
– 不必书写复杂的UNION语句
– GROUPING SETS中包含的分组项越多性能越好
以下例子实现了对department_id job_id分组 对job_id manager_id分组 最终形成两个分组
SELECTdepartment_id job_id manager_id avg(salary)
FROMemployees
GROUP BY GROUPING SETS ((department_id job_id) (job_id manager_id));
复合列
复合列是被作为整体处理的一组列的集合
使用括号将若干列组成复合列在ROLLUP或CUBE中作为整体进行操作
在ROLLUP或CUBE中 复合列可以避免产生不必要的分组结果
例如 GROUP BY ROLLUP( department_id (job_id manager_id)); //小括号实现复合列
连接分组集
连接分组集可以产生有用的对分组项的结合
将各分组集 ROLLUP 和CUBE用逗号连接 Oracle自动在GROUP BY子句中将各分组集进行连接
连接的结果是对各分组生成笛卡尔集
例如 GROUP BY GROUPING SETS(a b) GROUPING SETS(c d);
lishixinzhi/Article/program/Oracle/201311/18505
Oracle数据库的子查询关键字该怎么使用？一、
伪列就像Oracle中oracle如何子查询的一个表列oracle如何子查询，但实际上它并未存储在表中 。伪列可以从表中查询oracle如何子查询，但是不能插入、更新或删除它们的值 。常用的伪列：rowid和rownum 。
Rowid：数据库中的每一行都有一个行地址，Rowid伪列返回该行地址 。可以使用Rowid值来定位表中的一行 。通常情况下，Rowid值可以唯一地标识数据库中的一行 。
Rowid伪列有以下重要用途：
1）能以最快的方式访问表中的一行；
2）能显示表的行是如何存储的 。
3）可以作为表中行的唯一标识 。
如：SQL
select
rowid,ename
from
emp;
Rownum：对于一个查询返回的每一行，Rownum伪列返回一个数值代表的次序 。返回的第一行的Rownum值为1 ， 第二行的Rownum值为2，依此类推 。通过使用Rownum伪列，用户可以限制查询返回的行数 。
如：SQLselect
*
from
emp
where
rownum11;
从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
【oracle如何子查询 oracle 子查询和连接查询 效率】关于oracle如何子查询和oracle 子查询和连接查询 效率的介绍到此就结束了 ， 不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息 ， 记得收藏关注本站 。

    推荐阅读


    上一篇：网上电商上货如何上，电商如何带货

    下一篇：阿里云服务器和价格，阿里云服务器价格是多少