5.多表查詢(xún)
多表查詢(xún)
目的：從多張表獲取數(shù)據(jù)
前提：進(jìn)行連接的多張表中有共同的列
等連接
通過(guò)兩個(gè)表具有相同意義的列,建立連接條件.
查詢(xún)結(jié)果只顯示兩個(gè)列中的值是等值條件的行數(shù)據(jù)
表中同名列被選擇時(shí)必須添加表名前綴進(jìn)行修飾 否則無(wú)法確定這一列是屬于哪個(gè)表
不等連接
A表中的某列數(shù)據(jù)和B表中一列或多列的關(guān)系是非等值關(guān)系,大于,小于,不等于,等條件都屬于不等連接的范疇
自連接
數(shù)據(jù)都來(lái)自一張表,所以在from字句中需要對(duì)表添加別名,添加表別名后才能合法化的引用表中的列名.
本質(zhì)就是將一張表虛擬成了兩張表
外連接
即是選擇出滿(mǎn)足等連接條件及其以外的行
(+)修飾符號(hào)用法:放置在選出結(jié)果只包含等連接的列后,則另一列的結(jié)果就是等值行+非等值行
全連接
全鏈接是把做符合和不符合關(guān)聯(lián)條件的兩個(gè)表的信息都顯示出來(lái)
等鏈接
SQL> select *from emp,dept where emp.deptno=dept.deptno;
EMPNO ENAME? ? ? JOB ? MGR HIREDATE ? ? ? SAL COMM ? DEPTNO? ? DEPTNO DNAME ? LOC
---------- ---------- --------- ----- --------- ---------- ---------- ---------- ---------- -------------- ----------
7782 CLARK? ? ? MANAGER 7839 09-JUN-81? ? ? 2450 ? ? ? 10 10 ACCOUNTING ? NEW YORK
7839 KING? ? ? PRESIDENT? ? ? 17-NOV-81? ? ? 5000 ? ? ? 10 10 ACCOUNTING ? NEW YORK
7934 MILLER? ? CLERK 7782 23-JAN-82? ? ? 1300 ? ? ? 10 10 ACCOUNTING ? NEW YORK
7566 JONES? ? ? MANAGER 7839 02-APR-81? ? ? 2975 ? ? ? 20 20 RESEARCH ? DALLAS
7902 FORD? ? ? ANALYST 7566 03-DEC-81? ? ? 3000 ? ? ? 20 20 RESEARCH ? DALLAS
7876 ADAMS? ? ? CLERK 7788 23-MAY-87? ? ? 1100 ? ? ? 20 20 RESEARCH ? DALLAS
7369 SMITH? ? ? CLERK 7902 17-DEC-80? ? ? ? 800 ? ? ? 20 20 RESEARCH ? DALLAS
7788 SCOTT? ? ? ANALYST 7566 19-APR-87? ? ? 3000 ? ? ? 20 20 RESEARCH ? DALLAS
7521 WARD? ? ? SALESMAN 7698 22-FEB-81? ? ? 1250 ? 500 ? ? ? 30 30 SALES ? CHICAGO
7844 TURNER? ? SALESMAN 7698 08-SEP-81? ? ? 1500 ? ? 0 ? ? ? 30 30 SALES ? CHICAGO
7499 ALLEN? ? ? SALESMAN 7698 20-FEB-81? ? ? 1600 ? 300 ? ? ? 30 30 SALES ? CHICAGO
7900 JAMES? ? ? CLERK 7698 03-DEC-81? ? ? ? 950 ? ? ? 30 30 SALES ? CHICAGO
7698 BLAKE? ? ? MANAGER 7839 01-MAY-81? ? ? 2850 ? ? ? 30 30 SALES ? CHICAGO
7654 MARTIN? ? SALESMAN 7698 28-SEP-81? ? ? 1250 1400 ? ? ? 30 30 SALES ? CHICAGO
標(biāo)準(zhǔn)sql寫(xiě)法：
SQL> select empno,ename,loc from emp inner join dept on emp.deptno=dept.deptno;
idle>
這才是我們要的結(jié)果.笛卡爾積幾乎我們不會(huì)需要.多表查詢(xún)時(shí)基本都帶有where子句來(lái)描述多個(gè)表的關(guān)系 避免笛卡爾集
當(dāng)兩個(gè)表中有相同的列名時(shí),為了區(qū)分 要在列前加上表名作前綴.
在聯(lián)合的兩個(gè)表內(nèi)取數(shù)據(jù):描述scott在哪個(gè)部門(mén)
idle> select empno,ename,dname,sal from emp,dept where emp.deptno=dept.deptno and ename='SCOTT';
EMPNO ENAME? ? ? DNAME ? ? SAL
---------- ---------- -------------- ----------
7788 SCOTT? ? ? RESEARCH ? 3000
為了書(shū)寫(xiě)方便抄瓦，我們可以給表起別名
表的別名
格式:表名 別名
給表取別名是很有必要的,因?yàn)橛械谋砻荛L(zhǎng) 不便于引用時(shí)書(shū)寫(xiě).
SQL> select *from emp a,dept b where a.deptno=b.deptno and a.ename='SCOTT';
EMPNO ENAME? ? ? JOB ? MGR HIREDATE ? ? ? SAL COMM ? DEPTNO? ? DEPTNO DNAME ? LOC
---------- ---------- --------- ----- --------- ---------- ---------- ---------- ---------- -------------- ----------
7788 SCOTT? ? ? ANALYST 7566 19-APR-87? ? ? 3000 ? ? ? 20 20 RESEARCH ? DALLAS
不等連接
就是排除完全相等條件以外的 >,<,!=, <=, >=, between and
主要在于不同表之間顯示特定范圍的信息(也可以理解成包含關(guān)系)
求出每個(gè)員工的工資等級(jí)
SQL> SELECT *FROM SALGRADE;
GRADE LOSAL ? HISAL
---------- ---------- ----------
1 ? 700 ? ? 1200
2 1201 ? ? 1400
3 1401 ? ? 2000
4 2001 ? ? 3000
5 3001 ? ? 9999
SQL> select empno,ename,sal,grade from emp a,salgrade b where a.sal between b.losal and b.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
7788 SCOTT ? ? 3000 ? 4
7902 FORD ? ? 3000 ? 4
7839 KING ? ? 5000 ? 5
14 rows selected.
自連接
同一張表內(nèi)的連接查詢(xún) 即把一個(gè)表映射成兩個(gè)表
主要用于表的自參照關(guān)系 比如emp中的上下級(jí)或?qū)哟侮P(guān)系
因?yàn)樽赃B接是同一張表之間的鏈接查詢(xún) 所以必須定義表別名
SQL> select a.empno,a.ename,b.empno,b.ename from emp a,emp b where a.mgr=b.empno;
EMPNO ENAME ? EMPNO ENAME
---------- ---------- ---------- ----------
7902 FORD ? ? 7566 JONES
7788 SCOTT ? ? 7566 JONES
7844 TURNER ? ? 7698 BLAKE
7499 ALLEN ? ? 7698 BLAKE
7521 WARD ? ? 7698 BLAKE
7900 JAMES ? ? 7698 BLAKE
7654 MARTIN ? ? 7698 BLAKE
7934 MILLER ? ? 7782 CLARK
7876 ADAMS ? ? 7788 SCOTT
7698 BLAKE ? ? 7839 KING
7566 JONES ? ? 7839 KING
7782 CLARK ? ? 7839 KING
7369 SMITH ? ? 7902 FORD
13 rows selected.
外鏈接
不僅返回滿(mǎn)足連接的記錄 還會(huì)返回不滿(mǎn)足連接的記錄
外鏈接的運(yùn)算符(+)
該符號(hào)可以放在等的左邊或右邊,但一定要放在缺少信息(完全滿(mǎn)足條件的行)的一邊.
若加在多信息的一邊 和沒(méi)加一樣，可以理解為誰(shuí)需要空行來(lái)填補(bǔ)逆趣，就在誰(shuí)的后面加（+）
例如:emp中不包含40號(hào)部門(mén)的員工 而dept中有40號(hào)部門(mén)
SQL> select empno,ename,b.deptno,dname from emp a,dept b where a.deptno(+)=b.deptno;
EMPNO ENAME ? DEPTNO DNAME
---------- ---------- ---------- --------------
7782 CLARK ? ? ? 10 ACCOUNTING
7839 KING ? ? ? 10 ACCOUNTING
7934 MILLER ? ? ? 10 ACCOUNTING
7566 JONES ? ? ? 20 RESEARCH
7902 FORD ? ? ? 20 RESEARCH
7876 ADAMS ? ? ? 20 RESEARCH
7369 SMITH ? ? ? 20 RESEARCH
7788 SCOTT ? ? ? 20 RESEARCH
7521 WARD ? ? ? 30 SALES
7844 TURNER ? ? ? 30 SALES
7499 ALLEN ? ? ? 30 SALES
7900 JAMES ? ? ? 30 SALES
7698 BLAKE ? ? ? 30 SALES
7654 MARTIN ? ? ? 30 SALES
40 OPERATIONS
15 rows selected.
標(biāo)準(zhǔn)sql寫(xiě)法 left join on
查詢(xún)出所有部門(mén)名和10號(hào)部門(mén)內(nèi)的員工
因?yàn)閱T工信息顯示較少 所以(+)符號(hào)放在emp.deptno上
SQL> select b.deptno,b.dname,a.empno,a.ename from emp a,dept b where a.deptno(+)=b.deptno and a.deptno(+)=10;
DEPTNO DNAME ? ? ? EMPNO ENAME
---------- -------------- ---------- ----------
10 ACCOUNTING 7782 CLARK
10 ACCOUNTING 7934 MILLER
10 ACCOUNTING 7839 KING
20 RESEARCH
30 SALES
40 OPERATIONS
6 rows selected.
SQL>
SQL> 如果不在所有的條件上面寫(xiě)（+）就會(huì)產(chǎn)生這樣的結(jié)果
SQL> select b.deptno,b.dname,a.empno,a.ename from emp a,dept b where a.deptno(+)=b.deptno and a.deptno=10;
DEPTNO DNAME ? ? ? EMPNO ENAME
---------- -------------- ---------- ----------
10 ACCOUNTING 7782 CLARK
10 ACCOUNTING 7839 KING
10 ACCOUNTING 7934 MILLER
外連接的注意事項(xiàng)
1.where中有多個(gè)條件時(shí) 必須每個(gè)條件都需要使用(+)符號(hào)
2.(+)只使用于列 不能是表達(dá)式
3.(+)不能與in or一起操作
實(shí)際測(cè)試中，in是可以的，給or加上括號(hào)，提高運(yùn)算優(yōu)先級(jí)跑杭，也是可以的瞻想。
全連接
符合連接條件的，就連接成一行杆煞，不符合的魏宽，也全部顯示出來(lái)
full join on
SQL> select a.deptno,a.dname,b.empno,b.ename from dept a full join emp b on a.deptno=b.deptno and a.deptno=10;
DEPTNO DNAME ? ? ? EMPNO ENAME
---------- -------------- ---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
10 ACCOUNTING 7782 CLARK
7788 SCOTT
10 ACCOUNTING 7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
10 ACCOUNTING 7934 MILLER
30 SALES
40 OPERATIONS
20 RESEARCH
17 rows selected.
1.標(biāo)準(zhǔn)的sql寫(xiě)法
natual join
兩個(gè)表要有共有列,并且數(shù)據(jù)類(lèi)型一致
如果兩個(gè)表的共有列不只一個(gè) natural join會(huì)按所有共有列連接
SQL> select empno,ename,deptno from emp e natural join dept d;
join using 是 inner join using的縮寫(xiě)
USING 子句
注意事項(xiàng):
1.natural join 和 using子句互斥 不能同時(shí)使用
2.所引用的列不能使用表名前綴
3.被引用的列一定是兩個(gè)表的共有列
4.多列相同時(shí)只能選擇一列
SQL> select empno,ename,deptno from emp e join dept d using (deptno);
join on 是inner join on的簡(jiǎn)寫(xiě)形式
SQL> select empno,ename,dname,loc from emp e join dept d on e.deptno=d.deptno;
left join on 是left outer join on的簡(jiǎn)寫(xiě)形式
左外連接
滿(mǎn)足連接條件的數(shù)據(jù),以及不滿(mǎn)足連接條件的左邊表的其他數(shù)據(jù)
SQL> select d.deptno,d.dname,d.loc,e.empno,ename from dept d left join emp e on d.deptno=e.deptno and d.deptno=10;
DEPTNO DNAME ? LOC ? ? EMPNO ENAME
---------- -------------- ------------- ---------- ----------
10 ACCOUNTING ? NEW YORK ? ? ? 7782 CLARK
10 ACCOUNTING ? NEW YORK ? ? ? 7839 KING
10 ACCOUNTING ? NEW YORK ? ? ? 7934 MILLER
40 OPERATIONS ? BOSTON
30 SALES ? CHICAGO
20 RESEARCH ? DALLAS
right join on 是right outer join的簡(jiǎn)寫(xiě)形式
右外鏈接
滿(mǎn)足連接條件的數(shù)據(jù),以及不滿(mǎn)足連接條件的右邊表的其他數(shù)據(jù)
full join on 是full outer join on的簡(jiǎn)寫(xiě)形式
全外連接
滿(mǎn)足連接條件的數(shù)據(jù)腐泻，以及不滿(mǎn)足連接條件的兩邊的表的其他數(shù)據(jù)
連接條件和過(guò)濾條件的區(qū)別
連接條件出現(xiàn)在on子句中的條件，用于限制能夠進(jìn)行表連接的記錄
被連接的表只展示符合連接條件的數(shù)據(jù)
例如：這里的emp表是被連接的表
SQL> select d.deptno,d.dname,d.loc,e.empno,ename from dept d left join emp e on d.deptno=e.deptno and d.deptno=10;
DEPTNO DNAME ? LOC ? ? EMPNO ENAME
---------- -------------- ------------- ---------- ----------
10 ACCOUNTING ? NEW YORK ? ? ? 7782 CLARK
10 ACCOUNTING ? NEW YORK ? ? ? 7839 KING
10 ACCOUNTING ? NEW YORK ? ? ? 7934 MILLER
40 OPERATIONS ? BOSTON
30 SALES ? CHICAGO
20 RESEARCH ? DALLAS
6 rows selected.
過(guò)濾條件是用在where子句中的队询，用于對(duì)連接后的結(jié)果進(jìn)行過(guò)濾
2.集合操作
集合操作
用于多條select語(yǔ)句合并結(jié)果
union 并集 去重
union all 并集 不去重
intersect 交集
minus 差集
union
A集合和B集合的合并,但去掉兩集合重復(fù)的部分 會(huì)排序
輸出列名按照第一條sql的輸出方式展示派桩，
所有對(duì)應(yīng)的列必須具有相同的數(shù)據(jù)類(lèi)型，否則報(bào)錯(cuò)
集合的字段名使用第一個(gè)結(jié)果集的字段名稱(chēng)
SCOTT@ora11g> select deptno,ename from emp where deptno in (20,30)
2? ? ? ? ?? union
3? ? ? ? ?? select deptno,ename from emp where deptno in (20,10)
4? ;
DEPTNO ENAME
---------- ----------
10 CLARK
10 KING
10 MILLER
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH
30 ALLEN
30 BLAKE
30 JAMES
30 MARTIN
30 TURNER
30 WARD
14 rows selected.
SCOTT@ora11g>
union all
A集合和B集合的合并,不去重,不排序
SCOTT@ora10g> select deptno,ename from emp where deptno in (20,30)
2 ? ? ? union all
3 ? ? ? select deptno,ename from emp where deptno in (20,10)
4*
SCOTT@ora10g> /
DEPTNO ENAME
---------- ----------
20 SMITH
30 ALLEN
30 WARD
20 JONES
30 MARTIN
30 BLAKE
20 SCOTT
30 TURNER
20 ADAMS
30 JAMES
20 FORD
20 SMITH
20 JONES
10 CLARK
20 SCOTT
10 KING
20 ADAMS
20 FORD
10 MILLER
19 rows selected.
SCOTT@ora11g>
intersect
兩個(gè)集合的交集部分,排序并去重
SCOTT@ora11g> select deptno,ename from emp where deptno in (20,30)
2 ? ? ? intersect
3 ? ? ? select deptno,ename from emp where deptno in (20,10)
4*
SCOTT@ora10g> /
DEPTNO ENAME
---------- ----------
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH
SCOTT@ora11g>
minus
取兩個(gè)集合的差集,A集合中存在,B集合中不存在的數(shù)據(jù)(取A集合中B集合不存在的數(shù)據(jù)) 去重
SCOTT@ora10g>? select deptno,ename from emp where deptno in (20,30)
2 ? ? ? minus
3 ? ? ? select deptno,ename from emp where deptno in (20,10)
4*
SCOTT@ora10g>
DEPTNO ENAME
---------- ----------
30 ALLEN
30 BLAKE
30 JAMES
30 MARTIN
30 TURNER
30 WARD
6 rows selected.
3.子查詢(xún)
子查詢(xún)
子查詢(xún)指嵌入在其他SQL中的select語(yǔ)句,也稱(chēng)嵌套查詢(xún).
按照子查詢(xún)返回結(jié)果,可將子查詢(xún)分為:
單行單列? col = (sub_q)
單行多列? (col1,col2..)=(sub_q)
多行單列? col in (sub_q)
多行多列? (col1,col2...) in (sub_q)
數(shù)值 一對(duì)一? = > <
數(shù)據(jù) 一對(duì)多? > any? > all
按照子查詢(xún)where條件來(lái)分
標(biāo)量子查詢(xún)
關(guān)聯(lián)子查詢(xún)
特點(diǎn):
1.優(yōu)先執(zhí)行子查詢(xún),主查詢(xún)?cè)偈褂米硬樵?xún)的結(jié)果
2.子查詢(xún)返回的列數(shù)和類(lèi)型要匹配
3.子查詢(xún)要用括號(hào)括起來(lái)
4.子查詢(xún)返回多行要用多行關(guān)系運(yùn)算符
單列單行子查詢(xún)
子查詢(xún)返回一行記錄
查詢(xún)和scott在同一部門(mén)的員工
SCOTT@ora11g> select deptno,ename,sal from emp where deptno=(select deptno from emp where ename='SCOTT');
DEPTNO ENAME ? ?? SAL
---------- ---------- ----------
20 SMITH ? ?? 800
20 JONES ? ? 2975
20 SCOTT ? ? 3000
20 ADAMS ? ? 1100
20 FORD ? ? 3000
SCOTT@ora11g>
也可以把子查詢(xún)結(jié)果當(dāng)成一列
SCOTT@ora10g> select deptno,ename,(select deptno from emp where ename='SCOTT') AA from emp where deptno=10;
DEPTNO ENAME ? ? ? AA
---------- ---------- ----------
10 CLARK ? ? ? 20
10 KING ? ? ? 20
10 MILLER ? ? ? 20
多行子查詢(xún)
多行子查詢(xún)指返回多行數(shù)據(jù)的子查詢(xún)語(yǔ)句
當(dāng)在where中使用時(shí),必須使用多行比較符(in all any)
ALL和any操作符不能獨(dú)立使用 要與單行比較符(= > < >= <= <>)結(jié)合使用
in?? 匹配于子查詢(xún)結(jié)果的任一個(gè)值即可
ALL? 必須要符合子查詢(xún)結(jié)果的所有值
ANY? 只要符合子查詢(xún)結(jié)果的任意一個(gè)值即可
in 操作
SCOTT@ora10g> select empno from emp where deptno=10;
EMPNO
----------
7782
7839
7934
SCOTT@ora10g> select empno,ename,sal from emp where empno in (select empno from emp where deptno=10);
EMPNO ENAME ? ?? SAL
---------- ---------- ----------
7782 CLARK ? ? 2450
7839 KING ? ? 5000
7934 MILLER ? ? 1300
any 操作
小于最大的即可
SCOTT@ora10g> select deptno,ename,sal from emp where deptno < any (select distinct deptno from emp where deptno = 20 or deptno = 30);
DEPTNO ENAME ? ?? SAL
---------- ---------- ----------
10 CLARK ? ? 2450
10 KING ? ? 5000
10 MILLER ? ? 1300
20 JONES ? ? 2975
20 FORD ? ? 3000
20 ADAMS ? ? 1100
20 SMITH ? ?? 800
20 SCOTT ? ? 3000
8 rows selected.
SCOTT@ora10g>
ALL 操作
小于最小的即可
SCOTT@ora10g> select deptno,ename,sal from emp where deptno < all (select distinct deptno from emp where deptno = 20 or deptno = 30);
DEPTNO ENAME ? ?? SAL
---------- ---------- ----------
10 CLARK ? ? 2450
10 KING ? ? 5000
10 MILLER ? ? 1300
多列子查詢(xún)
指子查詢(xún)返回多個(gè)列的數(shù)據(jù)
當(dāng)多個(gè)列只有一行數(shù)據(jù)時(shí) 可以使用單行比較符
當(dāng)多個(gè)列有多行數(shù)據(jù)時(shí),還是需要 IN
多列單行
就是多條件比較
查詢(xún)和SMITH相同部門(mén) 相同崗位的人
SCOTT@ora11g> select deptno,ename,job,sal from emp where (deptno,job) = (select deptno,job from emp where ename='SMITH');
DEPTNO ENAME? ? ? JOB ? ? ?? SAL
---------- ---------- --------- ----------
20 SMITH? ? ? CLERK ? ? ?? 800
20 ADAMS? ? ? CLERK ? ? ? 1100
SCOTT@ora10g>
多列多行
IN
找出領(lǐng)導(dǎo)和工資與SCOTT和WARD一致的人
SCOTT@ora11g> select ename,mgr,sal from emp where ename in ('SCOTT','WARD');
ENAME ? MGR ? ?? SAL
---------- ---------- ----------
WARD 7698 ? ? 1250
SCOTT 7566 ? ? 3000
SCOTT@ora11g> select deptno,ename,mgr,sal from emp where (mgr,sal) in (select mgr,sal from emp where ename in ('SCOTT','WARD')) and ename not in ('SCOTT','WARD');
DEPTNO ENAME ? ?? MGR SAL
---------- ---------- ---------- ----------
30 MARTIN ? ? 7698? ? ?? 1250
20 FORD ? ? 7566? ? ?? 3000
查找每個(gè)部門(mén)的最高工資的員工姓名
SCOTT@ora10g> select deptno,max(sal) from emp group by deptno;
DEPTNO?? MAX(SAL)
---------- ----------
30 2850
20 3000
10 5000
SCOTT@ora10g> select deptno,ename,sal from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);
DEPTNO ENAME ? ?? SAL
---------- ---------- ----------
30 BLAKE ? ? 2850
20 SCOTT ? ? 3000
10 KING ? ? 5000
20 FORD ? ? 3000
關(guān)聯(lián)子查詢(xún)
將主查詢(xún)的內(nèi)容傳遞給子查詢(xún) 子查詢(xún)?cè)侔巡樵?xún)結(jié)構(gòu)反饋給主查詢(xún)
子查詢(xún)執(zhí)行的次數(shù)取決于主查詢(xún)傳遞值的次數(shù)
找出每個(gè)部門(mén)工資最高的人
不使用關(guān)聯(lián)子查詢(xún) 使用in分組方式實(shí)現(xiàn)
查找每個(gè)部門(mén)的最高工資的員工姓名
SCOTT@ora10g> select deptno,max(sal) from emp group by deptno;
DEPTNO?? MAX(SAL)
---------- ----------
30 2850
20 3000
10 5000
SCOTT@ora10g> select deptno,ename,sal from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);
DEPTNO ENAME ? ?? SAL
---------- ---------- ----------
30 BLAKE ? ? 2850
20 SCOTT ? ? 3000
10 KING ? ? 5000
20 FORD ? ? 3000
一一比較模式
SQL> select deptno,ename,sal from emp where sal = (select max(sal) from emp where deptno=10) and deptno=10;
DEPTNO ENAME ? ?? SAL
---------- ---------- ----------
10 KING ? ? 5000
SQL> select deptno,ename,sal from emp where sal = (select max(sal) from emp where deptno=20) and deptno=20;
DEPTNO ENAME ? ?? SAL
---------- ---------- ----------
20 SCOTT ? ? 3000
20 FORD ? ? 3000
SQL> select deptno,ename,sal from emp where sal = (select max(sal) from emp where deptno=30) and deptno=30;
DEPTNO ENAME ? ?? SAL
---------- ---------- ----------
30 BLAKE ? ? 2850
SQL>
使用關(guān)聯(lián)子查詢(xún)模式
SQL> select deptno,ename,sal from emp e where sal = (select max(sal) from emp where deptno=e.deptno);
DEPTNO ENAME ? ?? SAL
---------- ---------- ----------
30 BLAKE ? ? 2850
20 SCOTT ? ? 3000
10 KING ? ? 5000
20 FORD ? ? 3000
SQL>
關(guān)聯(lián)子查詢(xún)出現(xiàn)在select字段中
SQL> select empno,ename,deptno,(select loc from dept where deptno=e.deptno) loc from emp e;
EMPNO ENAME? ? ? ? ? DEPTNO LOC
---------- ---------- ---------- -------------
7369 SMITH? ? ? ? ? ? ? 20 DALLAS
7499 ALLEN? ? ? ? ? ? ? 30 CHICAGO
7521 WARD? ? ? ? ? ? ?? 30 CHICAGO
7566 JONES? ? ? ? ? ? ? 20 DALLAS
7654 MARTIN? ? ? ? ? ?? 30 CHICAGO
7698 BLAKE? ? ? ? ? ? ? 30 CHICAGO
7782 CLARK? ? ? ? ? ? ? 10 NEW YORK
7788 SCOTT? ? ? ? ? ? ? 20 DALLAS
7839 KING? ? ? ? ? ? ?? 10 NEW YORK
7844 TURNER? ? ? ? ? ?? 30 CHICAGO
7876 ADAMS? ? ? ? ? ? ? 20 DALLAS
7900 JAMES? ? ? ? ? ? ? 30 CHICAGO
7902 FORD? ? ? ? ? ? ?? 20 DALLAS
7934 MILLER? ? ? ? ? ?? 10 NEW YORK
已選擇14行蚌斩。
exists查詢(xún)
使用EXISTS語(yǔ)句可以測(cè)試集合是否為空铆惑，
EXISTS語(yǔ)句通常與子查詢(xún)結(jié)合在一起使用。
只要子查詢(xún)中至少返回一個(gè)值凳寺，則EXISTS語(yǔ)句的值就為T(mén)rue鸭津。\
查到就不再繼續(xù)查
找出領(lǐng)導(dǎo) 此員工的empno在mgr列存在即是領(lǐng)導(dǎo)
SCOTT@ora10g> select empno,ename,mgr from emp e where exists (select 1 from emp where mgr=e.empno);
EMPNO ENAME ? ?? MGR
---------- ---------- ----------
7902 FORD ? ? 7566
7698 BLAKE ? ? 7839
7839 KING
7566 JONES ? ? 7839
7788 SCOTT ? ? 7566
7782 CLARK ? ? 7839
6 rows selected.
in也能做到 只是in的效率不高 in會(huì)一直比下去 而exists比成功就不在比了.
SCOTT@ora10g> select empno,ename,mgr from emp where empno in (select mgr from emp);
EMPNO ENAME ? ?? MGR
---------- ---------- ----------
7902 FORD ? ? 7566
7698 BLAKE ? ? 7839
7839 KING
7566 JONES ? ? 7839
7788 SCOTT ? ? 7566
7782 CLARK ? ? 7839
6 rows selected.
SCOTT@ora10g>
取反
用in改寫(xiě)時(shí)并不一定得到和not exists一致的結(jié)果
即是查找普通員工 不是領(lǐng)導(dǎo)的人
SQL> select empno,ename from emp e where not exists (select 1 from emp where mgr=e.empno);
EMPNO ENAME
---------- ----------
7844 TURNER
7521 WARD
7654 MARTIN
7499 ALLEN
7934 MILLER
7369 SMITH
7876 ADAMS
7900 JAMES
8 rows selected.
SQL>
SQL> select empno,ename from emp e where empno in (select mgr from emp);
EMPNO ENAME
---------- ----------
7902 FORD
7698 BLAKE
7839 KING
7566 JONES
7788 SCOTT
7782 CLARK
6 rows selected.
SQL> select empno,ename from emp e where empno not in (select mgr from emp);
no rows selected
SQL> 原因是什么?
with 語(yǔ)句
當(dāng)查詢(xún)中多次用到某一部分時(shí)，可以用with語(yǔ)句創(chuàng)建一個(gè)公共臨時(shí)表肠缨。
因?yàn)樽硬樵?xún)?cè)趦?nèi)存臨時(shí)表中逆趋，避免了重復(fù)解析，所以執(zhí)行效率會(huì)提高不少晒奕。
臨時(shí)表在一次查詢(xún)結(jié)束自動(dòng)清除闻书。
語(yǔ)法: 一個(gè)select里的一個(gè)子查詢(xún)? 有效范圍在這個(gè)SQL執(zhí)行周期內(nèi)
第一次執(zhí)行（解析 執(zhí)行 得到結(jié)果)
第二次執(zhí)行（解析 執(zhí)行 得到結(jié)果）
第一次執(zhí)行（解析 執(zhí)行 得到結(jié)果) ==> 緩存到臨時(shí)表
第二次執(zhí)行 （直接取結(jié)果）
with
alias_name1 as? ? (subquery1),
alias_name2 as? ? (subQuery2),
……,
alias_nameN as? ? (subQueryN)
select col1,col2…… col3
from alias_name1,alias_name2……,alias_nameN
例子:
SQL> with
q1 as (select 3+5 s from dual),
q2 as (select 3*5 m from dual),
q3 as (select s,m,s+m,s*m from q1,q2)
select * from q3;
S ? ? M ? ?? S+M S*M
---------- ---------- ---------- ----------
8 ?? 15 ? ? ? 23 120
SQL>
查詢(xún)每個(gè)部門(mén)大于平均工資的員工，也可以這樣來(lái)實(shí)現(xiàn)
SQL> with
2? a as (select deptno,avg(sal) x from emp group by deptno)
3? select * from emp,a where emp.deptno=a.deptno and emp.sal>a.x;
EMPNO ENAME? ? ? JOB ? ? ?? MGR HIREDATE ? ? SAL? ? ?? COMM? ?? DEPTNO ? DEPTNO ? X
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7499 ALLEN? ? ? SALESMAN ? ? ? 7698 20-FEB-81 ?? 1600? ? ? ? 300 ?? 30 ? ? ? 30 1566.66667
7566 JONES? ? ? MANAGER ? ? ? 7839 02-APR-81 ?? 2975 ?? 20 ? ? ? 20? ? ?? 2175
7698 BLAKE? ? ? MANAGER ? ? ? 7839 01-MAY-81 ?? 2850 ?? 30 ? ? ? 30 1566.66667
7788 SCOTT? ? ? ANALYST ? ? ? 7566 19-APR-87 ?? 3000 ?? 20 ? ? ? 20? ? ?? 2175
7839 KING? ? ?? PRESIDENT ?? 17-NOV-81 ?? 5000 ?? 10 ? ? ? 10 2916.66667
7902 FORD? ? ?? ANALYST ? ? ? 7566 03-DEC-81 ?? 3000 ?? 20 ? ? ? 20? ? ?? 2175
with子句中的視圖叫做詢(xún)問(wèn)塊脑慧，詢(xún)問(wèn)塊的復(fù)雜查詢(xún)?cè)?/p>
with子句中只運(yùn)行一次魄眉，運(yùn)行成功后會(huì)將詢(xún)問(wèn)塊的結(jié)果集
保存到temp表空間，以后再次調(diào)用詢(xún)問(wèn)塊時(shí)會(huì)在后臺(tái)轉(zhuǎn)換
為對(duì)結(jié)果集的直接訪(fǎng)問(wèn)
4.DDL
DDL 語(yǔ)句 數(shù)據(jù)定義語(yǔ)言
CREATE 創(chuàng)建
ALTER 修改
DROP 刪除
TRUNCATE 截?cái)?/p>
COMMNET 注釋
RENAME 更名
修改表名
rename old_name to new_name
SQL> rename t5 to t6;
表已重命名闷袒。
增加列
alter table table_name add(column datatype [default expr] [,column datatype...])
SQL> alter table t6 add(id number default 1000);
修改列的定義
（修改后的數(shù)據(jù)類(lèi)型不能與列中當(dāng)前的數(shù)據(jù)沖突坑律，
否則列必須為空，default值的修改只對(duì)后續(xù)insert數(shù)據(jù)有效囊骤，之前的數(shù)據(jù)不受影響）
alter table table_name modify(column datatype [default expr] [,column datatype...)
刪除列晃择，不管有無(wú)數(shù)據(jù)均可以刪除，但是最后一列是不可以被刪除的也物。
alter table drop (column)
SQL> alter table t2 drop (id);
表已更改宫屠。
使用SET UNUSED 選項(xiàng)標(biāo)記一列或多列為unused
使用DROP UNUSED COLUMNS 選項(xiàng)刪除unused列。
這個(gè)功能可以在系統(tǒng)高峰期因刪除字段而帶來(lái)影響滑蚯，因?yàn)閯h除字段會(huì)記錄undo浪蹂，因此可以先把列標(biāo)記為unused等系統(tǒng)不繁忙的時(shí)候再drop掉。
語(yǔ)法：
ALTER TABLE? <table_name>SET?? UNUSED()(<column_name>);
ALTER TABLE? <table_name>SET?? UNUSED COLUMN(<column_name>);
ALTER TABLE? <table_name>DROP? UNUSED COLUMNS;
修改列名（列名稱(chēng)不可以與關(guān)鍵字沖突告材，不如類(lèi)型名稱(chēng)坤次，命令名稱(chēng)等）
alter table table_name rename column old_column_name to new_column_name
SQL> alter table t2 rename column sal to salary;
表已更改。
增加注釋
comment on table table_name is 'text'
comment on column table_name.columb is 'text'
user_tab_comments? 表的注釋信息创葡，在這里面可以查到
user_col_comments? 列的注釋信息浙踢，在這里可以查到
截?cái)啾?/p>
保留表結(jié)構(gòu)
清空表的數(shù)據(jù)
tuncate table table_name
屬于ddl語(yǔ)句，同樣都有刪除數(shù)據(jù)的功能灿渴，但是有以下不同
truncate 可以回收存儲(chǔ)空間洛波，delete不會(huì)
delete可以回退，truncate不可以
工作中骚露，慎重使用truncate
刪除表
drop table table_name [purge]
10G中增加了回收站功能,表沒(méi)有被完全刪 只是放到回收站里去了
SQL> drop table t2;
表已刪除蹬挤。
從回收站中恢復(fù)表
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE? DROP TIME
---------------- ------------------------------ ------------ -------------------
T4 BIN$M1uFYjFloJrgUAB/AQALVQ==$0 TABLE ? ?? 2016-05-21:21:52:41
或者
select *from recyclebin;
SQL> flashback table t4 to before drop;
Flashback complete.
例如:
SQL> flashback table t4 to before drop;
Flashback complete.
SQL> select *from tab;
TNAME ? ? ?? TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS ? ? ?? TABLE
DEPT ? ? ?? TABLE
EMP ? ? ?? TABLE
SALGRADE ? ? ?? TABLE
T4 ? ? ?? TABLE
SQL> drop table t4;
Table dropped.
SQL>
SQL>
SQL> show recycle
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE? DROP TIME
---------------- ------------------------------ ------------ -------------------
T4 BIN$M1uFYjFmoJrgUAB/AQALVQ==$0 TABLE ? ?? 2016-05-21:21:57:42
還原已刪除的表并改名
SQL> flashback table t4 to before drop rename to t7;
Flashback complete.
SQL> select *from tab;
TNAME ? ? ?? TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS ? ? ?? TABLE
DEPT ? ? ?? TABLE
EMP ? ? ?? TABLE
SALGRADE ? ? ?? TABLE
T7 ? ? ?? TABLE
如果回收站里的表名有重復(fù)，默認(rèn)恢復(fù)最近一次刪除的那張表
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE? DROP TIME
---------------- ------------------------------ ------------ -------------------
T4 BIN$OASw9TBNurHgUAB/AQBbYw==$0 TABLE ? ?? 2016-07-20:05:26:13
T4 BIN$N++H8pxalGbgUAB/AQAvxA==$0 TABLE ? ?? 2016-07-19:04:08:26
T4 BIN$N+52pkGdHVLgUAB/AQAgTQ==$0 TABLE ? ?? 2016-07-19:02:52:00
SQL> flashback table t4 to before drop;
Flashback complete.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE? DROP TIME
---------------- ------------------------------ ------------ -------------------
T4 BIN$N++H8pxalGbgUAB/AQAvxA==$0 TABLE ? ?? 2016-07-19:04:08:26
T4 BIN$N+52pkGdHVLgUAB/AQAgTQ==$0 TABLE ? ?? 2016-07-19:02:52:00
如果回收站里的表名有重復(fù)棘幸，指定要恢復(fù)的表焰扳，需要使用回收站里的表名
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE? DROP TIME
---------------- ------------------------------ ------------ -------------------
T4 BIN$OASw9TBOurHgUAB/AQBbYw==$0 TABLE ? ?? 2016-07-20:05:29:27
T4 BIN$N+52pkGdHVLgUAB/AQAgTQ==$0 TABLE ? ?? 2016-07-19:02:52:00
指定回收站里的名字需要加雙引號(hào)
SQL> flashback table "BIN$N+52pkGdHVLgUAB/AQAgTQ==$0" to before drop;
Flashback complete.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE? DROP TIME
---------------- ------------------------------ ------------ -------------------
T4 BIN$OASw9TBOurHgUAB/AQBbYw==$0 TABLE ? ?? 2016-07-20:05:29:27
清空回收站
SQL> show recycle;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE? DROP TIME
---------------- ------------------------------ ------------ -------------------
T7 BIN$M1uFYjFnoJrgUAB/AQALVQ==$0 TABLE ? ?? 2016-05-21:21:59:28
SQL> purge recyclebin;
Recyclebin purged.
SCOTT@ora10g> show recycle; 查看回收站.
SCOTT@ora10g> purge recyclebin; 清空回收站.
SCOTT@ora10g> purge table t2 ; 清空回收站中t2的表.
SQL>? purge dba_recyclebin; 清空所有用戶(hù)回收站里的內(nèi)容.
SQL> alter session set recyclebin=off; 關(guān)閉當(dāng)前會(huì)話(huà)的回收站;
SQL> alter system set recyclebin=off; 關(guān)閉系統(tǒng)的回收站;
每個(gè)用戶(hù)分配一個(gè)回收站。
轉(zhuǎn)至：↓
鏈接：http://www.reibang.com/p/420b1c14f1a1
來(lái)源：簡(jiǎn)書(shū)
著作權(quán)歸作者所有误续。商業(yè)轉(zhuǎn)載請(qǐng)聯(lián)系作者獲得授權(quán)吨悍，非商業(yè)轉(zhuǎn)載請(qǐng)注明出處。
