5.多表查詢
多表查詢
目的：從多張表獲取數(shù)據(jù)
前提：進(jìn)行連接的多張表中有共同的列
等連接
通過(guò)兩個(gè)表具有相同意義的列,建立連接條件.
查詢結(jié)果只顯示兩個(gè)列中的值是等值條件的行數(shù)據(jù)
表中同名列被選擇時(shí)必須添加表名前綴進(jìn)行修飾 否則無(wú)法確定這一列是屬于哪個(gè)表
不等連接
A表中的某列數(shù)據(jù)和B表中一列或多列的關(guān)系是非等值關(guān)系,大于,小于,不等于,等條件都屬于不等連接的范疇
自連接
數(shù)據(jù)都來(lái)自一張表,所以在from字句中需要對(duì)表添加別名,添加表別名后才能合法化的引用表中的列名.
本質(zhì)就是將一張表虛擬成了兩張表
外連接
即是選擇出滿足等連接條件及其以外的行
(+)修飾符號(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寫法：
SQL> select empno,ename,loc from emp inner join dept on emp.deptno=dept.deptno;
idle>
這才是我們要的結(jié)果.笛卡爾積幾乎我們不會(huì)需要.多表查詢時(shí)基本都帶有where子句來(lái)描述多個(gè)表的關(guān)系 避免笛卡爾集
當(dāng)兩個(gè)表中有相同的列名時(shí),為了區(qū)分 要在列前加上表名作前綴.
在聯(lián)合的兩個(gè)表內(nèi)取數(shù)據(jù):描述scott在哪個(gè)部門
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
為了書寫方便，我們可以給表起別名
表的別名
格式:表名 別名
給表取別名是很有必要的,因?yàn)橛械谋砻荛L(zhǎng) 不便于引用時(shí)書寫.
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)的連接查詢 即把一個(gè)表映射成兩個(gè)表
主要用于表的自參照關(guān)系 比如emp中的上下級(jí)或?qū)哟侮P(guān)系
因?yàn)樽赃B接是同一張表之間的鏈接查詢 所以必須定義表別名
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.
外鏈接
不僅返回滿足連接的記錄 還會(huì)返回不滿足連接的記錄
外鏈接的運(yùn)算符(+)
該符號(hào)可以放在等的左邊或右邊,但一定要放在缺少信息(完全滿足條件的行)的一邊.
若加在多信息的一邊 和沒(méi)加一樣砚嘴，可以理解為誰(shuí)需要空行來(lái)填補(bǔ)饲做，就在誰(shuí)的后面加（+）
例如:emp中不包含40號(hào)部門的員工 而dept中有40號(hào)部門
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寫法 left join on
查詢出所有部門名和10號(hào)部門內(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> 如果不在所有的條件上面寫（+）就會(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寫法
natual join
兩個(gè)表要有共有列,并且數(shù)據(jù)類型一致
如果兩個(gè)表的共有列不只一個(gè) natural join會(huì)按所有共有列連接
SQL> select empno,ename,deptno from emp e natural join dept d;
join using 是 inner join using的縮寫
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)寫形式
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)寫形式
左外連接
滿足連接條件的數(shù)據(jù),以及不滿足連接條件的左邊表的其他數(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)寫形式
右外鏈接
滿足連接條件的數(shù)據(jù),以及不滿足連接條件的右邊表的其他數(shù)據(jù)
full join on 是full outer join on的簡(jiǎn)寫形式
全外連接
滿足連接條件的數(shù)據(jù)，以及不滿足連接條件的兩邊的表的其他數(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ù)類型踪央，否則報(bào)錯(cuò)
集合的字段名使用第一個(gè)結(jié)果集的字段名稱
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.子查詢
子查詢
子查詢指嵌入在其他SQL中的select語(yǔ)句,也稱嵌套查詢.
按照子查詢返回結(jié)果,可將子查詢分為:
單行單列? col = (sub_q)
單行多列? (col1,col2..)=(sub_q)
多行單列? col in (sub_q)
多行多列? (col1,col2...) in (sub_q)
數(shù)值 一對(duì)一? = > <
數(shù)據(jù) 一對(duì)多? > any? > all
按照子查詢where條件來(lái)分
標(biāo)量子查詢
關(guān)聯(lián)子查詢
特點(diǎn):
1.優(yōu)先執(zhí)行子查詢,主查詢?cè)偈褂米硬樵兊慕Y(jié)果
2.子查詢返回的列數(shù)和類型要匹配
3.子查詢要用括號(hào)括起來(lái)
4.子查詢返回多行要用多行關(guān)系運(yùn)算符
單列單行子查詢
子查詢返回一行記錄
查詢和scott在同一部門的員工
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>
也可以把子查詢結(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
多行子查詢
多行子查詢指返回多行數(shù)據(jù)的子查詢語(yǔ)句
當(dāng)在where中使用時(shí),必須使用多行比較符(in all any)
ALL和any操作符不能獨(dú)立使用 要與單行比較符(= > < >= <= <>)結(jié)合使用
in? 匹配于子查詢結(jié)果的任一個(gè)值即可
ALL? 必須要符合子查詢結(jié)果的所有值
ANY? 只要符合子查詢結(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
多列子查詢
指子查詢返回多個(gè)列的數(shù)據(jù)
當(dāng)多個(gè)列只有一行數(shù)據(jù)時(shí) 可以使用單行比較符
當(dāng)多個(gè)列有多行數(shù)據(jù)時(shí),還是需要 IN
多列單行
就是多條件比較
查詢和SMITH相同部門 相同崗位的人
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è)部門的最高工資的員工姓名
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)子查詢
將主查詢的內(nèi)容傳遞給子查詢 子查詢?cè)侔巡樵兘Y(jié)構(gòu)反饋給主查詢
子查詢執(zhí)行的次數(shù)取決于主查詢傳遞值的次數(shù)
找出每個(gè)部門工資最高的人
不使用關(guān)聯(lián)子查詢 使用in分組方式實(shí)現(xiàn)
查找每個(gè)部門的最高工資的員工姓名
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)子查詢模式
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)子查詢出現(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查詢
使用EXISTS語(yǔ)句可以測(cè)試集合是否為空，
EXISTS語(yǔ)句通常與子查詢結(jié)合在一起使用畅蹂。
只要子查詢中至少返回一個(gè)值健无，則EXISTS語(yǔ)句的值就為True。\
查到就不再繼續(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改寫時(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)查詢中多次用到某一部分時(shí)液斜，可以用with語(yǔ)句創(chuàng)建一個(gè)公共臨時(shí)表累贤。
因?yàn)樽硬樵冊(cè)趦?nèi)存臨時(shí)表中，避免了重復(fù)解析少漆，所以執(zhí)行效率會(huì)提高不少臼膏。
臨時(shí)表在一次查詢結(jié)束自動(dòng)清除舔亭。
語(yǔ)法: 一個(gè)select里的一個(gè)子查詢? 有效范圍在這個(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>
查詢每個(gè)部門大于平均工資的員工抬旺，也可以這樣來(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子句中的視圖叫做詢問(wèn)塊，詢問(wèn)塊的復(fù)雜查詢?cè)?/p>
with子句中只運(yùn)行一次按灶，運(yùn)行成功后會(huì)將詢問(wèn)塊的結(jié)果集
保存到temp表空間，以后再次調(diào)用詢問(wèn)塊時(shí)會(huì)在后臺(tái)轉(zhuǎn)換
為對(duì)結(jié)果集的直接訪問(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ù)類型不能與列中當(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;
修改列名（列名稱不可以與關(guān)鍵字沖突葫盼，不如類型名稱残腌，命令名稱等）
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)啾?br>
保留表結(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; 清空所有用戶回收站里的內(nèi)容.
SQL> alter session set recyclebin=off; 關(guān)閉當(dāng)前會(huì)話的回收站;
SQL> alter system set recyclebin=off; 關(guān)閉系統(tǒng)的回收站;
每個(gè)用戶分配一個(gè)回收站。
