Oracle筆記_多表查詢

1 執行sql文件

@文件地址名        --執行某個sql文件；

2 多表查詢

• 想要的數據不在同一張表，就需要多個表進行聯查。
• 多表查詢也叫做表連接查詢，其中的where條件就是連接條件。

• 可以使用join來進行表連接，from 中的逗號換成join ，where換成on即可，join後面還可以另外跟where條件。

  select ...
  from 表1,表2...
  where...
  ...

3 外連接

• 保留沒有通過連接條件篩選的數據的時候使用外連接。

• oracle用法

  • 想保留哪一張表的數據，應該在連接條件等號的另一邊使用(+)；

  select d.deptno,d.dname,count(empno)
  from dept d, emp e
  where d.deptno = e.deptno(+)
  group by d.deptno,d.dname

• sql用法

  select d.deptno,d.dname,count(empno)
  from dept d join emp e
  on d.deptno = e.deptno
  group by d.deptno,d.dname

4 自連接

• 表自己跟自己做多表連接查詢,要查詢的數據在同一張表但是不在同一行，就要使用自連接

  select a.ename||'''s manager is '||nvl(b.ename,' his wife')
  from emp a, emp b
  where a.mgr = b.empno(+)

5 子查詢

• 個查詢的select語句裡邊嵌套了另一個select語句；

  select * from emp 
  where sal > (select sal from emp where ename = 'SCOTT')

• 註意事項

  • 父查詢和子查詢可以是不同的表，子查詢返回的結果父查詢可以使用即可；

  • 父查詢的select、from、where、having都可以嵌套子查詢；

  • 單行子查詢只能使用單行操作符，多行子查詢只能使用多行操作符，單行操作符，就是對某個單一數據的比較操作 =|>|>=|<|<=等；

  • 多行操作符，就是對一個集合的比較操作(IN、ANY、ALL)

  select * from emp 
  where deptno in (select deptno from dept
  where dname ='SALES' or dname = 'ACCOUNTING')

  • 子查詢中返回的null值影響最終計算結果;

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

6 集合運算

並集 union
全並集 union all
交集 intersect
差集 minus

select * from emp where deptno = 10
union
select * from emp where deptno = 20

• 用命令break on deptno skip 2來去掉分組重覆的deptno，使用break on null恢復；
• 註意事項
  • 參與運算的各個集合必須列數相同，且類型一致；
  • 採用第一個集合的表頭作為最終使用的表頭，（別名也只能在第一個集合上起）；
  • 可以使用括弧修改各個sql執行的優先順序；

7 新增數據

insert into 表名 values(val1,val2,.....) val1 val2 這些值要對應表的每列順序
insert into 表名(列名,列名2.....) values(val1,val2,.....)

• 字元串和日期加單引號；

• "&"符號，可簡化增刪查改操作

  • 例：select * from &t； 再次輸入/時，可快速查找；

• 批處理

  --一次性將emp表中所有10號部門的員工，放到新表emp10中來。
  insert into emp10 select * from emp where deptno=10 ;    
  --一次性將 emp表中的指定列插入到表emp10中。       
  --註意：insert的列名，要和select的列名一致
  insert into emp10(empno, ename, sal, deptno)
  select empno, ename, sal, deptno   from emp
  where deptno=10;          
  --註意沒有values關鍵字了。且列名必須一一對應    

8 修改數據

• update 表名 set 列名1=值1,列名2=值2..... [where cond]
  • 例：update dept set dname='SUPPORT1',loc=null where deptno=50;

9 刪除數據

• delete from 表名 [where cond]
  • 例：delete from dept where deptno=50

10 rownum

• rownum 是行號，偽列，並不是真實存在於表中的列，最好在排序前生成；
• rownum只能使用<, <=符號，不能使用>，>=符號，除了>=1
  • select rownum,emp.* from emp order by sal desc;