久热草视频在线精品,亚洲伊人院线

首頁免費課 Oracle高級查詢筆記

Oracle高級查詢

                
                趙強老師
            數據庫工程師

                    查看講師更多課程 
                    趙強老師講師的其他課程
                
Oracle觸發器
初級·36934
免費課程
                                
Oracle存儲過程和自定義函數
初級·56374
免費課程
                                
Oracle數據庫開發必備利器之PL/SQL基礎
初級·75164
免費課程
                                
                難度初級
            
                時長 3小時25分
            
                學習人數
            
綜合評分9.63
                            123人評價
                        查看評價
                                9.8
                                內容實用
                            
                                9.6
                                簡潔易懂
                            
                                9.5
                                邏輯清晰

最熱最新

慕姐0267268 04:47

相關子查詢能否用部門分組平均薪水來替代？

查看全部

0 采集收起來源：[Oracle] 主查詢和子查詢的執行順序
2025-06-13
慕姐0267268

rownum在原表中是升序，對原表做降序數據處理后rownum順序沒發生變化，要做TOP-N分析可以做一個子查詢，在新的查詢結果中做rownum的篩選會更有效。如select rownum from（selece * from 表 order by 降序）where rownum<數字能取出rownum 準確的前幾行。

查看全部

0 采集收起來源：[Oracle] 子查詢的排序問題
2025-06-12
慕姐0267268 01:22

分組函數可以嵌套，比如有多個部門，先求出每個部門的平均工資，得到的結果值可以同時嵌套一個max在后邊，得到的結果就是每個部門的平均工資最大值。

查看全部

0 采集收起來源：[Oracle] 分組函數的嵌套
2025-06-12
慕姐0267268 02:17

order by 后面的字段可以用select 中對應字段數，比如（第一列、第二列等來表示）

查看全部

0 采集收起來源：[Oracle] 在分組函數中使用Order
2025-06-12
舊蒔0 04:38
```
select?rownum,empno,ename,sal
from?(select?*?from?emp?order?by?sal?desc)
where?rownum?<=?3;
```
查看全部

0 采集收起來源：[Oracle] 主查詢和子查詢的執行順序
2023-10-25
斯巴達漢子 02:26

create table pm_ci
(ci_id varchar2(20),
stu_ids? varchar2(100));

insert into pm_ci values('1','1,2,3,4');
insert into pm_ci values('2','1,4');

create table pm_stu
(stu_id varchar2(20),
stu_name varchar2(20));

insert into pm_stu values('1','張三');
insert into pm_stu values('2','李四');
insert into pm_stu values('3','王五');
insert into pm_stu values('4','趙六');

col stu_name for a20;

select c.ci_id,wm_concat(s.stu_name) stu_name
from pm_ci c,pm_stu s
where instr(c.stu_ids,s.stu_id)>0
group by c.ci_id;

查看全部

0 采集收起來源：練習
2022-02-01
斯巴達漢子 10:21

按照部門統計工人數，按照如下格式輸出
方式一：函數的方式
select count(*) Total,
sum(decode(to_char(hiredate,'YYYY'),'1980',1,0)) "1980",
sum(decode(to_char(hiredate,'YYYY'),'1981',1,0)) "1981",
sum(decode(to_char(hiredate,'YYYY'),'1982',1,0)) "1982",
sum(decode(to_char(hiredate,'YYYY'),'1987',1,0)) "1987"
from emp;

decode(條件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

方式二：子查詢的方式
select
(select count(*) from emp) Total,
(select count(*) from emp where to_char(hiredate,'yyyy')='1980') "1980",
(select count(*) from emp where to_char(hiredate,'yyyy')='1981') "1981",
(select count(*) from emp where to_char(hiredate,'yyyy')='1982') "1982",
(select count(*) from emp where to_char(hiredate,'yyyy')='1987') "1987"
from dual;
dual 偽表

查看全部

0 采集收起來源：案例3
2022-02-01
斯巴達漢子 07:07

找到員工表中薪水大于本部門平均薪水的員工

select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
from emp e
where sal>(select avg(sal) from emp where deptno=e.deptno);

-- 用多表查詢實現
select e.empno,e.ename,e.sal,d.avgsal
from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d
where e.deptno=d.deptno and e.sal > d.avgsal;

--- 查詢sql語句運算效率
explain plan for
select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
from emp e
where sal>(select avg(sal) from emp where deptno=e.deptno);

select * from table (dbms_xplan.display);

explain plan for
select e.empno,e.ename,e.sal,d.avgsal
from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d
where e.deptno=d.deptno and e.sal > d.avgsal;

select * from table (dbms_xplan.display);

************************************************************************
SQL> select rownum,r,empno,ename,sal
? 2? from (select rownum r,empno,ename,sal
? 3? ? ?from (select rownum,empno,ename,sal from emp order by sal desc) e1
? 4? ? ?where rownum<=8) e2
? 5? where r>=5;

? ? ROWNUM? ? ? ? ? R? ? ? EMPNO ENAME? ? ? ? ? ? ?SAL
---------- ---------- ---------- ---------- ----------
? ? ? ? ?1? ? ? ? ? 5? ? ? ?7698 BLAKE? ? ? ? ? ? 2850
? ? ? ? ?2? ? ? ? ? 6? ? ? ?7782 CLARK? ? ? ? ? ? 2450
? ? ? ? ?3? ? ? ? ? 7? ? ? ?7499 ALLEN? ? ? ? ? ? 1600
? ? ? ? ?4? ? ? ? ? 8? ? ? ?7844 TURNER? ? ? ? ? ?1500

SQL> select e.empno,e.ename,e.sal,d.avgsal
? 2? from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d
? 3? where e.deptno=d.deptno and e.sal > d.avgsal;

? ? ?EMPNO ENAME? ? ? ? ? ? ?SAL? ? ?AVGSAL
---------- ---------- ---------- ----------
? ? ? 7499 ALLEN? ? ? ? ? ? 1600 1566.66667
? ? ? 7566 JONES? ? ? ? ? ? 2975? ? ? ?2175
? ? ? 7698 BLAKE? ? ? ? ? ? 2850 1566.66667
? ? ? 7788 SCOTT? ? ? ? ? ? 3000? ? ? ?2175
? ? ? 7839 KING? ? ? ? ? ? ?5000 2916.66667
? ? ? 7902 FORD? ? ? ? ? ? ?3000? ? ? ?2175

已選擇6行。

SQL> select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
? 2? from emp e
? 3? where sal>(select avg(sal) from emp where deptno=e.deptno);

? ? ?EMPNO ENAME? ? ? ? ? ? ?SAL? ? ?AVGSAL
---------- ---------- ---------- ----------
? ? ? 7499 ALLEN? ? ? ? ? ? 1600 1566.66667
? ? ? 7566 JONES? ? ? ? ? ? 2975? ? ? ?2175
? ? ? 7698 BLAKE? ? ? ? ? ? 2850 1566.66667
? ? ? 7788 SCOTT? ? ? ? ? ? 3000? ? ? ?2175
? ? ? 7839 KING? ? ? ? ? ? ?5000 2916.66667
? ? ? 7902 FORD? ? ? ? ? ? ?3000? ? ? ?2175

已選擇6行。

SQL> explain plan for
? 2? select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
? 3? from emp e
? 4? where sal>(select avg(sal) from emp where deptno=e.deptno);

已解釋。

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
------------------------------
Plan hash value: 2385781174

--------------------------------------------------------------------------------
| Id? | Operation? ? ? ? ? ? | Name? ? | Rows? | Bytes | Cost (%CPU)| Time? ? ?|
--------------------------------------------------------------------------------
|? ?0 | SELECT STATEMENT? ? ?|? ? ? ? ?|? ? ?1 |? ? 43 |? ? ?8? (25)| 00:00:01 |
|? ?1 |? SORT AGGREGATE? ? ? |? ? ? ? ?|? ? ?1 |? ? ?7 |? ? ? ? ? ? |? ? ? ? ? |
|*? 2 |? ?TABLE ACCESS FULL? | EMP? ? ?|? ? ?5 |? ? 35 |? ? ?3? ?(0)| 00:00:01 |
|*? 3 |? HASH JOIN? ? ? ? ? ?|? ? ? ? ?|? ? ?1 |? ? 43 |? ? ?8? (25)| 00:00:01 |
|? ?4 |? ?VIEW? ? ? ? ? ? ? ?| VW_SQ_1 |? ? ?3 |? ? 78 |? ? ?4? (25)| 00:00:01 |
|? ?5 |? ? HASH GROUP BY? ? ?|? ? ? ? ?|? ? ?3 |? ? 21 |? ? ?4? (25)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
------------------------------
|? ?6 |? ? ?TABLE ACCESS FULL| EMP? ? ?|? ? 14 |? ? 98 |? ? ?3? ?(0)| 00:00:01 |
|? ?7 |? ?TABLE ACCESS FULL? | EMP? ? ?|? ? 14 |? ?238 |? ? ?3? ?(0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

? ?2 - filter("DEPTNO"=:B1)
? ?3 - access("ITEM_1"="E"."DEPTNO")
? ? ? ?filter("SAL">"AVG(SAL)")

已選擇21行。

SQL> explain plan for
? 2? select e.empno,e.ename,e.sal,d.avgsal
? 3? from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d
? 4? where e.deptno=d.deptno and e.sal > d.avgsal;

已解釋。

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
------------------------------
Plan hash value: 269884559

-----------------------------------------------------------------------------
| Id? | Operation? ? ? ? ? ? | Name | Rows? | Bytes | Cost (%CPU)| Time? ? ?|
-----------------------------------------------------------------------------
|? ?0 | SELECT STATEMENT? ? ?|? ? ? |? ? ?1 |? ? 43 |? ? ?8? (25)| 00:00:01 |
|*? 1 |? HASH JOIN? ? ? ? ? ?|? ? ? |? ? ?1 |? ? 43 |? ? ?8? (25)| 00:00:01 |
|? ?2 |? ?VIEW? ? ? ? ? ? ? ?|? ? ? |? ? ?3 |? ? 78 |? ? ?4? (25)| 00:00:01 |
|? ?3 |? ? HASH GROUP BY? ? ?|? ? ? |? ? ?3 |? ? 21 |? ? ?4? (25)| 00:00:01 |
|? ?4 |? ? ?TABLE ACCESS FULL| EMP? |? ? 14 |? ? 98 |? ? ?3? ?(0)| 00:00:01 |
|? ?5 |? ?TABLE ACCESS FULL? | EMP? |? ? 14 |? ?238 |? ? ?3? ?(0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
------------------------------
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

? ?1 - access("E"."DEPTNO"="D"."DEPTNO")
? ? ? ?filter("E"."SAL">"D"."AVGSAL")

已選擇18行。

查看全部

0 采集收起來源：案例2
2022-01-31
斯巴達漢子 01:33

行號rownum是個偽列

查看全部

0 采集收起來源：案例1
2022-01-31
斯巴達漢子 00:39

子查詢注意的10個問題

1.子查詢語法中的小括號
2.子查詢的書寫風格
3.可以使用子查詢的位置：where，select，having，from后面使用子查詢
4.不可以使用子查詢的位置：group by后面不可使用子查詢
5.強調:from后面的子查詢---
6.主查詢和子查詢可以不是同一張表
7.一般不在子查詢中使用排序；但在Top-N分析問題中，必須對子查詢排序
8.一般先執行子查詢，再執行主查詢；但相關子查詢例外
9.單行子查詢只能使用單行操作符；多行子查詢只能使用多行操作符；
10.注意：子查詢中是null值問題

1---如果沒有小括號，語法是錯誤的
2----書寫風格好，方便閱讀
3----
單行子查詢---僅僅返回一條記錄
多行子查詢---返回多行記錄，2行及其以上
select empno,ename,sal,(select job from emp where empno=7839) 第四列
from emp;

select deptno,avg(sal)
from emp
group by deptno
having avg(sal) > (select max(sal)
? ?from emp
? ?where deptno=30);
-- 這having不能用where替代，有avg

select *
from(select empno,ename,sal from emp);
-- from 中加子查詢的，用的很多
-- 括號里面，相當于一個新的結果集

4 -- group by 后不能使用子查詢
-- 錯誤示例
select avg(sal)
from emp
group by (select deptno from emp);

5.from后面的子查詢
select *
from (select empno,ename,sal,sal*12 from emp);
--通過12*sal計算出年薪

6.主查詢和子查詢不是一張表
-- 查詢銷售部員工
select *?
from emp
where deptno=(select deptno
from dept
where dname='SALES');

-- 多表查詢的方式解決同樣問題,相對上面，這里數據庫訪問一次，效率得根據實際數據量模式
select e.*
from emp e,dept d
where e.deptno=d.deptno and d.dname='SALES';

7.一般不在子查詢中使用排序；但在Top-N分析問題中，必須對子查詢排序
select rownum,empno,ename,sal
from emp
where rownum<3
order by sal desc;

select rownum,empno,ename,sal from emp order by sal desc;

行號永遠按照默認的順序生成
行號只能使用< <=不能使用> >=?
select rownum,empno,ename,sal
from (select * from emp order by sal desc)
where rownum<=3;

8.一般先執行子查詢，再執行主查詢；但相關子查詢例外

-- 查詢在所在部門，工資高于平均工資的員工
select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
from emp e
where sal > (select avg(sal) from emp where deptno=e.deptno);

9.單行子查詢只能使用單行操作符
? 多行子查詢只能使用多行操作符

返回一條記錄---單行子查詢
返回2條及其以上----多行子查詢

單行子查詢示例1：
查詢員工信息：
1.職位與7566員工一樣
2.薪水大于7782的薪水
select *
from emp
where job = (select job from emp where empno=7566) and
? ? ? sal > (select sal from emp where empno=7782);

單行子查詢示例2：
查詢工資最低的員工信息
select *
from emp
where sal = (select min(sal) from emp);

單行查詢示例-3：
查詢最低工資大于20號部門最低工資的部門號和部門的最低工資
select deptno,min(sal)
from emp
group by deptno
having min(sal) > (select min(sal)
? ?from emp
where deptno=20);

多行操作符-
in--- 等于列表中的任何一個
any--- 和子查詢返回的任意一個值比較
all--- 和子查詢返回的所有值比較

多行操作符in：
查詢部門名稱是SALES和ACCOUNTING的員工信息
select *
from emp
where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING');

多表查詢實現：
select e.*
from emp e,dept d
where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING');
tips:如若不加括號，按照and，or從前到后次序執行；

多行操作符any
示例：查詢工資比30號部門任意一個員工高的員工信息
select *
from emp
where sal > any (select sal from emp where deptno=30);

select *
from emp
where sal >? (select min(sal) from emp where deptno=30);

多行操作符：all
示例：查詢工資比30號部門所有員工高的員工信息
select *
from emp
where sal > all (select sal from emp where deptno=30);

select *
from emp
where sal >? (select max(sal) from emp where deptno=30);

10.子查詢中的null問題
單行子查詢中的null問題----結果是空值
多行子查詢中的null問題

示例：查詢不是老板的員工
select *
from emp
where empno not in (select mgr from emp);
-- 因為子查詢中有null，導致返回空數據

select *
from emp
where empno not in (select mgr from emp where mgr is not null);
*******************************************************

查看全部

0 采集收起來源：Oracle子查詢概述
2022-01-31
斯巴達漢子 05:23

10.子查詢中的null問題
單行子查詢中的null問題----結果是空值
多行子查詢中的null問題

示例：查詢不是老板的員工
select *
from emp
where empno not in (select mgr from emp);
-- 因為子查詢中有null，導致返回空數據

select *
from emp
where empno not in (select mgr from emp where mgr is not null);

查看全部

0 采集收起來源：[Oracle] 子查詢中的空值問題
2022-01-31
斯巴達漢子 00:55

9.單行子查詢只能使用單行操作符
? 多行子查詢只能使用多行操作符

返回一條記錄---單行子查詢
返回2條及其以上----多行子查詢

單行子查詢示例1：
查詢員工信息：
1.職位與7566員工一樣
2.薪水大于7782的薪水
select *
from emp
where job = (select job from emp where empno=7566) and
? ? ? sal > (select sal from emp where empno=7782);

單行子查詢示例2：
查詢工資最低的員工信息
select *
from emp
where sal = (select min(sal) from emp);

單行查詢示例-3：
查詢最低工資大于20號部門最低工資的部門號和部門的最低工資
select deptno,min(sal)
from emp
group by deptno
having min(sal) > (select min(sal)
? ?from emp
where deptno=20);

多行操作符-
in--- 等于列表中的任何一個
any--- 和子查詢返回的任意一個值比較
all--- 和子查詢返回的所有值比較

多行操作符in：
查詢部門名稱是SALES和ACCOUNTING的員工信息
select *
from emp
where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING');

多表查詢實現：
select e.*
from emp e,dept d
where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING');
tips:如若不加括號，按照and，or從前到后次序執行；

多行操作符any
示例：查詢工資比30號部門任意一個員工高的員工信息
select *
from emp
where sal > any (select sal from emp where deptno=30);

select *
from emp
where sal >? (select min(sal) from emp where deptno=30);

多行操作符：all
示例：查詢工資比30號部門所有員工高的員工信息
select *
from emp
where sal > all (select sal from emp where deptno=30);

select *
from emp
where sal >? (select max(sal) from emp where deptno=30);

查看全部

0 采集收起來源：[Oracle] 單行子查詢和多行子查詢
2022-01-31
斯巴達漢子 07:58

單行子查詢錯誤示例提示
subquery 子查詢

ORA-01427 single-row subquery returns more than one row

查看全部

0 采集收起來源：[Oracle] 單行子查詢和多行子查詢
2022-01-31
斯巴達漢子 04:55

8.一般先執行子查詢，再執行主查詢；但相關子查詢例外

-- 查詢在所在部門，工資高于平均工資的員工
select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
from emp e
where sal > (select avg(sal) from emp where deptno=e.deptno);
*******************************************************************
SQL> select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
? 2? from emp e
? 3? where sal > (select avg(sal) from emp where deptno=e.deptno);

? ? ?EMPNO ENAME? ? ? ? ? ? ?SAL? ? ?AVGSAL
---------- ---------- ---------- ----------
? ? ? 7499 ALLEN? ? ? ? ? ? 1600 1566.66667
? ? ? 7566 JONES? ? ? ? ? ? 2975? ? ? ?2175
? ? ? 7698 BLAKE? ? ? ? ? ? 2850 1566.66667
? ? ? 7788 SCOTT? ? ? ? ? ? 3000? ? ? ?2175
? ? ? 7839 KING? ? ? ? ? ? ?5000 2916.66667
? ? ? 7902 FORD? ? ? ? ? ? ?3000? ? ? ?2175

已選擇6行。

查看全部

0 采集收起來源：[Oracle] 主查詢和子查詢的執行順序
2022-01-31
斯巴達漢子 05:33

7.一般不在子查詢中使用排序；但在Top-N分析問題中，必須對子查詢排序
select rownum,empno,ename,sal
from emp
where rownum<3
order by sal desc;

select rownum,empno,ename,sal from emp order by sal desc;

行號永遠按照默認的順序生成
行號只能使用< <=不能使用> >=?
select rownum,empno,ename,sal
from (select * from emp order by sal desc)
where rownum<=3;
*******************************************************
SQL> select rownum,empno,ename,sal
? 2? from emp
? 3? where rownum<3
? 4? order by sal desc;

? ? ROWNUM? ? ? EMPNO ENAME? ? ? ? ? ? ?SAL
---------- ---------- ---------- ----------
? ? ? ? ?2? ? ? ?7499 ALLEN? ? ? ? ? ? 1600
? ? ? ? ?1? ? ? ?7369 SMITH? ? ? ? ? ? ?800

SQL> select rownum,empno,ename,sal
? 2? from (select * from emp order by sal desc)
? 3? where rownum<=3;

? ? ROWNUM? ? ? EMPNO ENAME? ? ? ? ? ? ?SAL
---------- ---------- ---------- ----------
? ? ? ? ?1? ? ? ?7839 KING? ? ? ? ? ? ?5000
? ? ? ? ?2? ? ? ?7788 SCOTT? ? ? ? ? ? 3000
? ? ? ? ?3? ? ? ?7902 FORD? ? ? ? ? ? ?3000

SQL> select rownum,empno,ename,sal from emp order by sal desc;

? ? ROWNUM? ? ? EMPNO ENAME? ? ? ? ? ? ?SAL
---------- ---------- ---------- ----------
? ? ? ? ?9? ? ? ?7839 KING? ? ? ? ? ? ?5000
? ? ? ? 13? ? ? ?7902 FORD? ? ? ? ? ? ?3000
? ? ? ? ?8? ? ? ?7788 SCOTT? ? ? ? ? ? 3000
? ? ? ? ?4? ? ? ?7566 JONES? ? ? ? ? ? 2975
? ? ? ? ?6? ? ? ?7698 BLAKE? ? ? ? ? ? 2850
? ? ? ? ?7? ? ? ?7782 CLARK? ? ? ? ? ? 2450
? ? ? ? ?2? ? ? ?7499 ALLEN? ? ? ? ? ? 1600
? ? ? ? 10? ? ? ?7844 TURNER? ? ? ? ? ?1500
? ? ? ? 14? ? ? ?7934 MILLER? ? ? ? ? ?1300
? ? ? ? ?3? ? ? ?7521 WARD? ? ? ? ? ? ?1250
? ? ? ? ?5? ? ? ?7654 MARTIN? ? ? ? ? ?1250

? ? ROWNUM? ? ? EMPNO ENAME? ? ? ? ? ? ?SAL
---------- ---------- ---------- ----------
? ? ? ? 11? ? ? ?7876 ADAMS? ? ? ? ? ? 1100
? ? ? ? 12? ? ? ?7900 JAMES? ? ? ? ? ? ?950
? ? ? ? ?1? ? ? ?7369 SMITH? ? ? ? ? ? ?800

已選擇14行。

查看全部

0 采集收起來源：[Oracle] 子查詢的排序問題
2022-01-31
斯巴達漢子 00:09

6.主查詢和子查詢不是一張表
-- 查詢銷售部員工
select *?
from emp
where deptno=(select deptno
from dept
where dname='SALES');

-- 多表查詢的方式解決同樣問題,相對上面，這里數據庫訪問一次，效率得根據實際數據量模式
select e.*
from emp e,dept d
where e.deptno=d.deptno and d.dname='SALES';

查看全部

0 采集收起來源：[Oracle] 主查詢和子查詢不是同一張表
2022-01-31
斯巴達漢子 01:15

-- group by 后不能使用子查詢
-- 錯誤示例
select avg(sal)
from emp
group by (select deptno from emp);

查看全部

0 采集收起來源：[Oracle] 不可以使用子查詢的位置
2022-01-31
斯巴達漢子 05:01

3----
單行子查詢---僅僅返回一條記錄
多行子查詢---返回多行記錄，2行及其以上
select empno,ename,sal,(select job from emp where empno=7839) 第四列
from emp;

select deptno,avg(sal)
from emp
group by deptno
having avg(sal) > (select max(sal)
? ?from emp
? ?where deptno=30);
-- 這having不能用where替代，有avg

select *
from(select empno,ename,sal from emp);
-- from 中加子查詢的，用的很多
-- 括號里面，相當于一個新的結果集

*******************************************************
SQL> select empno,ename,sal,(select job from emp where empno=7839) 第四列
? 2? from emp;

? ? ?EMPNO ENAME? ? ? ? ? ? ?SAL 第四列
---------- ---------- ---------- ---------
? ? ? 7369 SMITH? ? ? ? ? ? ?800 PRESIDENT
? ? ? 7499 ALLEN? ? ? ? ? ? 1600 PRESIDENT
? ? ? 7521 WARD? ? ? ? ? ? ?1250 PRESIDENT
? ? ? 7566 JONES? ? ? ? ? ? 2975 PRESIDENT
? ? ? 7654 MARTIN? ? ? ? ? ?1250 PRESIDENT
? ? ? 7698 BLAKE? ? ? ? ? ? 2850 PRESIDENT
? ? ? 7782 CLARK? ? ? ? ? ? 2450 PRESIDENT
? ? ? 7788 SCOTT? ? ? ? ? ? 3000 PRESIDENT
? ? ? 7839 KING? ? ? ? ? ? ?5000 PRESIDENT
? ? ? 7844 TURNER? ? ? ? ? ?1500 PRESIDENT
? ? ? 7876 ADAMS? ? ? ? ? ? 1100 PRESIDENT

? ? ?EMPNO ENAME? ? ? ? ? ? ?SAL 第四列
---------- ---------- ---------- ---------
? ? ? 7900 JAMES? ? ? ? ? ? ?950 PRESIDENT
? ? ? 7902 FORD? ? ? ? ? ? ?3000 PRESIDENT
? ? ? 7934 MILLER? ? ? ? ? ?1300 PRESIDENT

已選擇14行。

SQL> select deptno,avg(sal)
? 2? from emp
? 3? group by deptno
? 4? having avg(sal) > (select max(sal)
? 5? ? ? ? ? ? ? ? from emp
? 6? ? ? ? ? ? ? ? where deptno=30);

? ? DEPTNO? ?AVG(SAL)
---------- ----------
? ? ? ? 10 2916.66667
SQL> select *
? 2? from(select empno,ename,sal from emp);

? ? ?EMPNO ENAME? ? ? ? ? ? ?SAL
---------- ---------- ----------
? ? ? 7369 SMITH? ? ? ? ? ? ?800
? ? ? 7499 ALLEN? ? ? ? ? ? 1600
? ? ? 7521 WARD? ? ? ? ? ? ?1250
? ? ? 7566 JONES? ? ? ? ? ? 2975
? ? ? 7654 MARTIN? ? ? ? ? ?1250
? ? ? 7698 BLAKE? ? ? ? ? ? 2850
? ? ? 7782 CLARK? ? ? ? ? ? 2450
? ? ? 7788 SCOTT? ? ? ? ? ? 3000
? ? ? 7839 KING? ? ? ? ? ? ?5000
? ? ? 7844 TURNER? ? ? ? ? ?1500
? ? ? 7876 ADAMS? ? ? ? ? ? 1100

? ? ?EMPNO ENAME? ? ? ? ? ? ?SAL
---------- ---------- ----------
? ? ? 7900 JAMES? ? ? ? ? ? ?950
? ? ? 7902 FORD? ? ? ? ? ? ?3000
? ? ? 7934 MILLER? ? ? ? ? ?1300

已選擇14行。

SQL> select empno,ename,sal from emp;

? ? ?EMPNO ENAME? ? ? ? ? ? ?SAL
---------- ---------- ----------
? ? ? 7369 SMITH? ? ? ? ? ? ?800
? ? ? 7499 ALLEN? ? ? ? ? ? 1600
? ? ? 7521 WARD? ? ? ? ? ? ?1250
? ? ? 7566 JONES? ? ? ? ? ? 2975
? ? ? 7654 MARTIN? ? ? ? ? ?1250
? ? ? 7698 BLAKE? ? ? ? ? ? 2850
? ? ? 7782 CLARK? ? ? ? ? ? 2450
? ? ? 7788 SCOTT? ? ? ? ? ? 3000
? ? ? 7839 KING? ? ? ? ? ? ?5000
? ? ? 7844 TURNER? ? ? ? ? ?1500
? ? ? 7876 ADAMS? ? ? ? ? ? 1100

? ? ?EMPNO ENAME? ? ? ? ? ? ?SAL
---------- ---------- ----------
? ? ? 7900 JAMES? ? ? ? ? ? ?950
? ? ? 7902 FORD? ? ? ? ? ? ?3000
? ? ? 7934 MILLER? ? ? ? ? ?1300

已選擇14行。

SQL>

查看全部

0 采集收起來源：Oracle可以使用子查詢的位置
2022-01-31
斯巴達漢子 05:03

子查詢注意的10個問題

1.子查詢語法中的小括號
2.子查詢的書寫風格----書寫風格好，方便閱讀
3.可以使用子查詢的位置：where，select，having，from后面使用子查詢
4.不可以使用子查詢的位置：group by后面不可使用子查詢
5.強調:from后面的子查詢---
6.主查詢和子查詢可以不是同一張表
7.一般不在子查詢中使用排序；但在Top-N分析問題中，必須對子查詢排序
8.一般先執行子查詢，再執行主查詢；但相關子查詢例外
9.單行子查詢只能使用單行操作符；多行子查詢只能使用多行操作符；
10.注意：子查詢中是null值問題

*******************************************************

查看全部

0 采集收起來源：Oracle子查詢需要注意的問題
2022-01-31
斯巴達漢子 05:14

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

查看全部

0 采集收起來源：Oracle子查詢概述
2022-01-31
斯巴達漢子 12:37

自連接存在的問題
1.不適合操作大表---數據運算太大，產生的笛卡爾積的大小數據的平方

解決辦法：層次查詢----一張表查詢，本質是單表查詢

select level,empno,ename,sal,mgr
from emp
connect by prior empno=mgr
start with mgr is null
order by 1;

connect by 上一層的員工號=老板號
start with mgr is null 一定要從頂層開始查
必須在查詢語句中把查詢的偽列給顯示出來level
*******************************************************
SQL> select count(*) from emp e,emp b;

? COUNT(*)
----------
? ? ? ?196

SQL> select level,empno,ename,sal,mgr
? 2? from emp
? 3? connect by prior empno=mgr
? 4? start with mgr is null
? 5? order by 1;

? ? ?LEVEL? ? ? EMPNO ENAME? ? ? ? ? ? ?SAL? ? ? ? MGR
---------- ---------- ---------- ---------- ----------
? ? ? ? ?1? ? ? ?7839 KING? ? ? ? ? ? ?5000
? ? ? ? ?2? ? ? ?7566 JONES? ? ? ? ? ? 2975? ? ? ?7839
? ? ? ? ?2? ? ? ?7698 BLAKE? ? ? ? ? ? 2850? ? ? ?7839
? ? ? ? ?2? ? ? ?7782 CLARK? ? ? ? ? ? 2450? ? ? ?7839
? ? ? ? ?3? ? ? ?7902 FORD? ? ? ? ? ? ?3000? ? ? ?7566
? ? ? ? ?3? ? ? ?7521 WARD? ? ? ? ? ? ?1250? ? ? ?7698
? ? ? ? ?3? ? ? ?7900 JAMES? ? ? ? ? ? ?950? ? ? ?7698
? ? ? ? ?3? ? ? ?7934 MILLER? ? ? ? ? ?1300? ? ? ?7782
? ? ? ? ?3? ? ? ?7499 ALLEN? ? ? ? ? ? 1600? ? ? ?7698
? ? ? ? ?3? ? ? ?7788 SCOTT? ? ? ? ? ? 3000? ? ? ?7566
? ? ? ? ?3? ? ? ?7654 MARTIN? ? ? ? ? ?1250? ? ? ?7698

? ? ?LEVEL? ? ? EMPNO ENAME? ? ? ? ? ? ?SAL? ? ? ? MGR
---------- ---------- ---------- ---------- ----------
? ? ? ? ?3? ? ? ?7844 TURNER? ? ? ? ? ?1500? ? ? ?7698
? ? ? ? ?4? ? ? ?7876 ADAMS? ? ? ? ? ? 1100? ? ? ?7788
? ? ? ? ?4? ? ? ?7369 SMITH? ? ? ? ? ? ?800? ? ? ?7902

已選擇14行。

查看全部

0 采集收起來源：[Oracle] 自連接存在的問題和解決辦法
2022-01-30
斯巴達漢子 03:53

自連接

核心：通過別名，將同一張表視為多張表
select e.ename 員工姓名,b.ename 老板姓名,e.empno,e.mgr
from emp e,emp b
where e.mgr=b.empno;

*******************************************************
SQL> select e.ename 員工姓名,b.ename 老板姓名,e.empno,e.mgr
? 2? from emp e,emp b
? 3? where e.mgr=b.empno;

員工姓名? ?老板姓名? ? ? ? EMPNO? ? ? ? MGR
---------- ---------- ---------- ----------
FORD? ? ? ?JONES? ? ? ? ? ? 7902? ? ? ?7566
SCOTT? ? ? JONES? ? ? ? ? ? 7788? ? ? ?7566
TURNER? ? ?BLAKE? ? ? ? ? ? 7844? ? ? ?7698
ALLEN? ? ? BLAKE? ? ? ? ? ? 7499? ? ? ?7698
WARD? ? ? ?BLAKE? ? ? ? ? ? 7521? ? ? ?7698
JAMES? ? ? BLAKE? ? ? ? ? ? 7900? ? ? ?7698
MARTIN? ? ?BLAKE? ? ? ? ? ? 7654? ? ? ?7698
MILLER? ? ?CLARK? ? ? ? ? ? 7934? ? ? ?7782
ADAMS? ? ? SCOTT? ? ? ? ? ? 7876? ? ? ?7788
BLAKE? ? ? KING? ? ? ? ? ? ?7698? ? ? ?7839
JONES? ? ? KING? ? ? ? ? ? ?7566? ? ? ?7839

員工姓名? ?老板姓名? ? ? ? EMPNO? ? ? ? MGR
---------- ---------- ---------- ----------
CLARK? ? ? KING? ? ? ? ? ? ?7782? ? ? ?7839
SMITH? ? ? FORD? ? ? ? ? ? ?7369? ? ? ?7902

已選擇13行。

查看全部

0 采集收起來源：[Oracle] 自連接
2022-01-30
斯巴達漢子 07:58

按照部門統計員工數，要求顯示：部門號，部門名稱，人數

select d.deptno 部門號,d.dname 部門名稱,count(e.empno) 人數
from emp e,dept d
where e.deptno=d.deptno
group by d.deptno,d.dname;

select d.deptno 部門號,d.dname 部門名稱,count(e.empno) 人數
from emp e,dept d
where e.deptno=d.deptno
group by d.deptno;

核心:通過外鏈接，把對于連接條件不成立的記錄，仍然包含在最后的結果中

左外連接：當連接條件不成立的時候，等號左邊的表仍然被包含
右外連接: 當連接條件不成立的時候，等號右邊的表仍然被包含

---- 右外連接示例：叫法和寫法左右是反著的
select d.deptno 部門號,d.dname 部門名稱,count(e.empno) 人數
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname;

select d.deptno 部門號,d.dname 部門名稱,count(e.empno) 人數
from emp e,dept d
where e.deptno=d.deptno(+)
group by d.deptno,d.dname;

*******************************************************
SQL> select * from dept;

? ? DEPTNO DNAME? ? ? ? ? LOC
---------- -------------- -------------
? ? ? ? 10 ACCOUNTING? ? ?NEW YORK
? ? ? ? 20 RESEARCH? ? ? ?DALLAS
? ? ? ? 30 SALES? ? ? ? ? CHICAGO
? ? ? ? 40 OPERATIONS? ? ?BOSTON

SQL> select d.deptno 部門號,d.dname 部門名稱,count(e.empno) 人數
? 2? from emp e,dept d
? 3? where e.deptno=d.deptno
? 4? group by d.deptno,d.dname;

? ? 部門號部門名稱? ? ? ? ? ? ?人數
---------- -------------- ----------
? ? ? ? 10 ACCOUNTING? ? ? ? ? ? ? 3
? ? ? ? 20 RESEARCH? ? ? ? ? ? ? ? 5
? ? ? ? 30 SALES? ? ? ? ? ? ? ? ? ?6

SQL> select d.deptno 部門號,d.dname 部門名稱,count(e.empno) 人數
? 2? from emp e,dept d
? 3? where e.deptno=d.deptno
? 4? group by d.deptno;
select d.deptno 部門號,d.dname 部門名稱,count(e.empno) 人數
? ? ? ? ? ? ? ? ? ? ? ?*
第 1 行出現錯誤:
ORA-00979: 不是 GROUP BY 表達式

SQL> select count(*) from emp
? 2? ;

? COUNT(*)
----------
? ? ? ? 14

SQL> select count(*) from dept;

? COUNT(*)
----------
? ? ? ? ?4

SQL> select d.deptno 部門號,d.dname 部門名稱,count(e.empno) 人數
? 2? from emp e,dept d
? 3? where e.deptno(+)=d.deptno
? 4? group by d.deptno;
select d.deptno 部門號,d.dname 部門名稱,count(e.empno) 人數
? ? ? ? ? ? ? ? ? ? ? ?*
第 1 行出現錯誤:
ORA-00979: 不是 GROUP BY 表達式

SQL> select d.deptno 部門號,d.dname 部門名稱,count(e.empno) 人數
? 2? from emp e,dept d
? 3? where e.deptno(+)=d.deptno
? 4? group by d.deptno,d.dname;

? ? 部門號部門名稱? ? ? ? ? ? ?人數
---------- -------------- ----------
? ? ? ? 10 ACCOUNTING? ? ? ? ? ? ? 3
? ? ? ? 40 OPERATIONS? ? ? ? ? ? ? 0
? ? ? ? 20 RESEARCH? ? ? ? ? ? ? ? 5
? ? ? ? 30 SALES? ? ? ? ? ? ? ? ? ?6

SQL> select d.deptno 部門號,d.dname 部門名稱,count(e.empno) 人數
? 2? from emp e,dept d
? 3? where e.deptno=d.deptno(+)
? 4? group by d.deptno,d.dname;

? ? 部門號部門名稱? ? ? ? ? ? ?人數
---------- -------------- ----------
? ? ? ? 10 ACCOUNTING? ? ? ? ? ? ? 3
? ? ? ? 20 RESEARCH? ? ? ? ? ? ? ? 5
? ? ? ? 30 SALES? ? ? ? ? ? ? ? ? ?6

查看全部

0 采集收起來源：[Oracle] 外連接
2022-01-30
斯巴達漢子 01:15

鏈接條件是不等號，不等值連接

select e.empno,e.ename,e.sal,s.grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal;

select e.empno,e.ename,e.sal,s.grade
from emp e,salgrade s
where e.sal between s.hisal and s.losal;

*******************************************************
SQL> select * from salgrade;

? ? ?GRADE? ? ? LOSAL? ? ? HISAL
---------- ---------- ----------
? ? ? ? ?1? ? ? ? 700? ? ? ?1200
? ? ? ? ?2? ? ? ?1201? ? ? ?1400
? ? ? ? ?3? ? ? ?1401? ? ? ?2000
? ? ? ? ?4? ? ? ?2001? ? ? ?3000
? ? ? ? ?5? ? ? ?3001? ? ? ?9999

SQL> select e.empno,e.ename,e.sal,s.grade
? 2? from emp e,salgrade s
? 3? where e.sal between s.losal and s.hisal;

? ? ?EMPNO ENAME? ? ? ? ? ? ?SAL? ? ? GRADE
---------- ---------- ---------- ----------
? ? ? 7369 SMITH? ? ? ? ? ? ?800? ? ? ? ? 1
? ? ? 7900 JAMES? ? ? ? ? ? ?950? ? ? ? ? 1
? ? ? 7876 ADAMS? ? ? ? ? ? 1100? ? ? ? ? 1
? ? ? 7521 WARD? ? ? ? ? ? ?1250? ? ? ? ? 2
? ? ? 7654 MARTIN? ? ? ? ? ?1250? ? ? ? ? 2
? ? ? 7934 MILLER? ? ? ? ? ?1300? ? ? ? ? 2
? ? ? 7844 TURNER? ? ? ? ? ?1500? ? ? ? ? 3
? ? ? 7499 ALLEN? ? ? ? ? ? 1600? ? ? ? ? 3
? ? ? 7782 CLARK? ? ? ? ? ? 2450? ? ? ? ? 4
? ? ? 7698 BLAKE? ? ? ? ? ? 2850? ? ? ? ? 4
? ? ? 7566 JONES? ? ? ? ? ? 2975? ? ? ? ? 4

? ? ?EMPNO ENAME? ? ? ? ? ? ?SAL? ? ? GRADE
---------- ---------- ---------- ----------
? ? ? 7788 SCOTT? ? ? ? ? ? 3000? ? ? ? ? 4
? ? ? 7902 FORD? ? ? ? ? ? ?3000? ? ? ? ? 4
? ? ? 7839 KING? ? ? ? ? ? ?5000? ? ? ? ? 5

已選擇14行。

SQL> select e.empno,e.ename,e.sal,s.grade
? 2? from emp e,salgrade s
? 3? where e.sal between s.highsal and s.losal;
where e.sal between s.highsal and s.losal
? ? ? ? ? ? ? ? ? ? *
第 3 行出現錯誤:
ORA-00904: "S"."HIGHSAL": 標識符無效

SQL> select e.empno,e.ename,e.sal,s.grade
? 2? from emp e,salgrade s
? 3? where e.sal between s.hisal and s.losal;

未選定行

查看全部

0 采集收起來源：[Oracle] 不等值連接
2022-01-30