oralce 自學(xué)筆記記錄

一：使用where子句：

?如何顯示工資高于3000的員工：

selectename,salfromempwheresal>3000;

?如何查找1981.1.1后入職的員工：

SQL>selectename"姓名，hiredateas"入職時(shí)間"fromempwherehiredate>'1-1月"1982';

SQL>selectename"姓名，salas"工資",hiredatefromempwherehiredate>to_date('1981.01.01l,,yyyy.mm.dd')；

?如何顯示工資在2000到2500的員工的情況：

SQL>selectename"姓名",salas"工資"fromempwheresal>=2000andsal<=3000;

SQL>selectename,salfromempwheresalbetween2000and3000;

-：如何使用like操作符：

%：表示0到多個(gè)字符

_：表示單個(gè)字符

?如何顯示首字符為S的員工姓名和工資

SQL>selectename"姓名",salas"工資"fromempwhereenamelike'S%";

?如何顯示第三個(gè)字符為大寫O的所有員工的姓名和工資：

SQL>selectename"姓名，salas"工資"fromempwhereenamelike'_0%';〃'—'只有兩根?。?/p>

三：在where條件中使用in

?如何顯示EMPNO為123,345,800.......的員工的情況：

SQL>select*fromempwhereempnoin(123,234,456);

四:使用isnull操作符

?如何顯示沒有上級(jí)的雇員的情況：

SQL>select*fromempwheremgrisnull;

五：使用邏輯操作符號(hào)

?查詢員工工資高于500或是崗位為MANAGER的雇員，同時(shí)還要滿足他們姓名的首寫字母為大寫的J:

SQL>select*fromempwhere(sal>500orjob='MANAGER')andenamelike'J%';

六：使用orderby字句

?如何按照工資的從低到高的順序顯示雇員的信息：

SQL>select*fromemporderbysal;

?如何按照工資的從高到低的順序顯示雇員的信息：

SQL>select*fromemporderbysaldesc;

?按照部門號(hào)升序而雇員的工資降序排列：

SQL>select*fromemporderbyDEPTNOasc,saldesc;//orderby本身默認(rèn)的是asc,所以，asc可寫可不寫！先

保證部門的升序，再確定sal的降序！

?按照部門號(hào)升序而雇員入職時(shí)間降序排列

SQL>select*fromemporderbyDEPTNOasc,hiredatedesc;

七：使用列的別名排序

?selectename,sal*12“年薪"fromemporderby"年薪"asc;

SQL>selectename,(sal+nvl(comm,0))*13"年薪"fromemporderby"年薪"；〃如果別名是英文名，就不用加""

號(hào)了.別名可以用as,也可以不用as.

A：分頁(yè)查詢

按雇員的ID號(hào)升序取出：

Oracle表的復(fù)雜查詢

一：說明

在實(shí)際應(yīng)用中經(jīng)常需要執(zhí)行復(fù)雜的數(shù)據(jù)統(tǒng)計(jì)，經(jīng)常需要顯示多張表的數(shù)據(jù)，現(xiàn)在我們給大家介紹較為復(fù)雜的

select語(yǔ)句

二：數(shù)據(jù)分組-max,min,avg,sum,count

?如何顯示所有員工中最高工資和最低工資：

SQL>selectmax(sal),min(sal)fromemp;

?如何顯示所有員工中最高工資和對(duì)應(yīng)的員工名：

SQL>selectename,salfromempwheresal=(selectmax(sal)fromemp);〃運(yùn)用至！IT子查詢。

如何寫成:SQL>electename,max(sal)fromemp;,這樣是不行的，因?yàn)閑name和max(sal)不是同一類型名，ename

是列(表)名，而max(sal)是單組分組函數(shù)！

?顯示所有員工的平均工資和工資總和以及共有多少員工：

SQL>selectavg(sal),sum(sal),count(sal)fromemp;

擴(kuò)展要求：

?請(qǐng)顯示工資高于平均工資的員工信息：

SQL>selectename,salfromempwheresal>(selectavg(sal)fromemp);--分組函數(shù)是不允許直接用來進(jìn)行比較

和與列名一起顯示的。

三：groupby和having子句

Groupby用于對(duì)查詢的結(jié)果分組統(tǒng)計(jì)，having子句用于限制分組顯示結(jié)果。

?如何顯示每個(gè)部門的平均工資和最高工資：

SQL>selectavg(sal),max(sal),deptnofromempgroupbydeptno;--和分組函數(shù)在一起顯示時(shí),顯示的列名必

須為分組的對(duì)象。

?顯示每個(gè)部門的每種崗位的平均工資和最高工資：

SQL>selectavg(sal),max(sal),deptno,jobfromempgroupbydeptnojob;〃分組函數(shù)不能用括號(hào)括起來。deptno

和job的順序不能換！

?顯示平均工資低于2000的部門號(hào)和它的平均工資：

SQL>selectavg(sal),max(sal),deptnofromempgroupbydeptnohavingavg(sal)>2000;〃分組函數(shù)是不能

用來作查詢的。先要把員工按部門號(hào)選出來，算部門平均工資，再用having條件來控制顯示！

擴(kuò)展要求：

對(duì)數(shù)據(jù)分組的總結(jié)

1：分組函數(shù)只能出現(xiàn)在選擇列表、having、orderby子句中。

SQL>selectavg(sal),max(sal),deptnofromempgroupbydeptnohavingavg(sal)>2000;

2：如果在select語(yǔ)句中同時(shí)包含有g(shù)roupby、having,orderby那么他們的順序是groupby(分組),having

(執(zhí)行條件條件標(biāo)志)，orderby(條件)。

SQL>selectavg(sal),max(sal),deptnofromempgroupbydeptnohavingavg(sal)>2000orderbyavg(sal);

3：在選擇列中如果有列、表達(dá)式、和分組函數(shù)，那么這些列和表達(dá)式必須有一個(gè)出現(xiàn)在groupby子句中，

否則就會(huì)出錯(cuò)。

如selectdeptno,avg(sal),max(sal)fromempgroupbydeptnohavingavg(sal)<2000;

這里deptno就一定要出現(xiàn)在groupby中，呵呵

多表查詢：

一：說明：

多表查詢是指基于兩個(gè)或兩個(gè)以上的表或是視圖的查詢，在實(shí)際應(yīng)用中，查詢單個(gè)表不能滿足你的需求，(如

顯示sales部門位置和其員工的姓名)，這種情況下需要使用到(dept表和emp表)

?顯示雇員名，雇員工資及所在部門的名字：

SQL>selectaLename,al.sal,a2.dnamefromempal,depta2whereal.deptno=a2.deptno;//al,a2分別是emp

和dept的別名,這兩個(gè)表是以deptno聯(lián)系在一起的!如果去掉了whereal.deptno=a2.deptno,則查詢的結(jié)

果會(huì)出到4*14各數(shù)據(jù)！形成的結(jié)果是因?yàn)橐凰钠ヅ涞慕Y(jié)果，empal.depta2不能寫成emp.al,dept.a2!

規(guī)定：多表查詢的條件數(shù)>=n(表數(shù))-1;這樣才能消除【笛卡爾集，

?如何顯示部門號(hào)為10的部門名、員工名和工資:

SQL>selectal.dnameza2.ename,a2.salfromdeptal,empa2whereal.deptno=a2.deptnoandal.deptno=10;-

結(jié)果查詢的表是兩張表，不是一張表，只是在查詢的時(shí)候，遵守了where后面的條件。

?顯示各個(gè)員工的姓名，工資及其工資的級(jí)別：

SQL>selectal.ename,al.sal,a2.gradefromempal,salgradea2whereal.salbetweena2.losalanda2.hisal;

SQL>selectal.ename,al.sal,a2.gradefromempal.salgradea2wheresalbetweenlosalandhisal;〃也可以這

樣不用明確定義死，al表a2的對(duì)應(yīng)的列名不同！

SQL>selectename,sal,gradefromempal,salgradea2wheresalbetweenlosalandhisal;//這樣也是可以

的。

SQL>selectename,sal,gradefromemp,salgradewheresalbetweenlosalandhisal;--當(dāng)多表沒有同列名或者同

列名沒有用到時(shí)，可以不用給表其它表名，列名前面也不必要加表名。

擴(kuò)展要求：

?顯示雇員名，雇員工資，所在部門的名字及部門號(hào)，并按部門號(hào)排序：

SQL>selectal.ename,al,sal,a2.dname,al.deptnofromempal,depta2whereal.deptno=a2.deptnoorderby

al.deptno;

二：自連接

自連接是指在同一張表上的連接查詢。

?顯示所有員工和他們上級(jí)領(lǐng)導(dǎo)的姓名：

SQL>selectworker.ename,boss.enamefromempworker,empbosswhereworker.mgr=boss.empno;〃把一張

表分開成兩張表來看(對(duì)待)；

?顯示某個(gè)員工和他上級(jí)領(lǐng)導(dǎo)的姓名：(比如顯示‘FORD'的上級(jí))

SQL>selectworker.ename,boss.enamefromempworker,empbosswhereworker.mgr=boss.empnoand

worker.ename='FORD';

三：什么是子查詢

子查詢是指嵌入在其它sql語(yǔ)句中的select語(yǔ)句，也叫嵌套查詢。

子查詢的格式：selectfromtablelwhere**=(selectfrom.......=selectfrom........)；

1：?jiǎn)涡凶硬樵?/p>

單行子查詢是指返回一行數(shù)據(jù)的子查詢語(yǔ)句。

〃條件語(yǔ)句是從右到左掃描的。如:SQL>selectdeptnofromempwhereename='SMITH'andjob='CLERK';

這條查詢SMITH所在部門的語(yǔ)句，它查詢的順序是千先找到SMITH的工作CLERK,再找到SMITH這個(gè)人員所

在的部門的！

?如何顯示與SMITH同一部門的所有員工？

<1>：能不能查詢出SMITH所在的部門號(hào)：SQL>selectdeptnofromempwhereename='SMITH';

<2>:顯示所有：

SQL>select*fromempwheredeptno=(selectdeptnofromempwhereename='SMITH');

2：多行子查詢：

多行子查詢是指返回多行數(shù)據(jù)的子查詢。

?如何查詢和部門10的工作相同的雇員的名字、崗位、工作、部門號(hào)：

SQL>select*fromempwherejobin(selectdistinctjobfromempwheredeptno=10);

3：在多行子查詢中使用all操作符

?如何顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號(hào)：

SQL>selectename,sal,deptnofromempwheresal>all(selectsalfromempwheredeptno=30);

擴(kuò)展要求：大家想想還有沒有別的查詢方法：

SQL>selectename,sal,deptnofromempwheresal>(selectmax(sal)fromempwheredeptno=30);

?如何按工資的升序顯示工資比部門30中所有員工工資高的員工的姓名、工資和部門號(hào)：

SQL>selectename,sal,deptnofromempwheresal>(selectmax(sal)fromempwheredeptno=30)orderbysal;

4：在多行子查詢中使用any操作符

?如何顯示工資比部門30的任意一個(gè)員工的工資高的員工的姓名、工資和部門號(hào)：

SQL>selectename,sal,deptnofromempwheresal>any(selectsalfromempwheredeptno=30);

〃比任意一個(gè)員工工資高，就是說只要比30號(hào)部門中的最低的工資高就可以了！

擴(kuò)展要求：大家想想還有沒有別的查詢方法：

SQL>selectename,sal,deptnofromempwheresal>(selectmin(sal)fromempwheredeptno=30);

5：多列子查詢

單行子查詢是指子查詢只返回單列、單行數(shù)據(jù)，多行子查詢是指返回單列多行數(shù)據(jù)，都是針對(duì)單列而言的，

而多列子查詢則是查詢返回多個(gè)列數(shù)據(jù)的子查詢語(yǔ)句。

?如何查詢與SMITH的部門和崗位完全相同的所有雇員：

步驟：一：查詢出SMITH的部門號(hào)和崗位：

SQL>selectdeptno,jobfromempwhereename='SMITH';

二：查詢出結(jié)果：

Sql>select*fromempwhere(deptno,job)=(selectdeptno,jobfromempwhereename='SMITH');

//deptnojob要和后面的deptno,job順序---對(duì)應(yīng)的。又如:

Sql>select*fromempwhere(job,deptno)=(selectjob,deptnofromempwhereename='SMITH');這樣也是

可以的。

SQL>select*fromempwhere(deptnojob)in(selectdeptnojobfromempwhereename='SMITH');〃用

in也可以,IN表示和in后面的括號(hào)里查詢出來的行條件之一相同的所有數(shù)據(jù)。

SQL>select*fromempwheredeptno=(selectdeptnofromempwhereename='SMITH')andjob=(selectjob

fromempwhereename='SMITH')；〃用and也可以!

6：在from子句中使用子查詢

〃從分組得到的每組中再統(tǒng)計(jì)數(shù)據(jù)！

?如何顯示高于自己部門平均工資的員工姓名，工資和平均工資：

//I;查詢出各個(gè)部門的平均工資和部門號(hào)：

SQL>selectdeptno,avg(sal)mysalfromempgroupbydeptno;

2：把上面的查詢看作是一張子表al而存在：

(selectdeptno,avg(sal)mysalfromempgroupbydeptnoal;

則正確的語(yǔ)句應(yīng)為：

SQL>selecta2.ename,a2,sal,al.mysalfromempa2,(selectdeptno,avg(sal)mysalfromempgroupbydeptno)al

wherea2.deptno=al.deptnoanda2.sal>al.mysal;//al.deptno=a2.deptno決定了是員工和同部門的平均工次

相比。并且avg(sal)必須用別名,否則會(huì)提示:a2,avg標(biāo)識(shí)符無效。

DEPTNOENAHESALMYSAL

30BLAKE2850.001347.76285

10KING5000.002916.66666

30TUPNER1500.001347.7628S

20FORD3000.001735

30ALLEN1600.001347.76285

20JONES2975.001735

總結(jié)：這里需要說明的當(dāng)在from子句中使用子查詢時(shí)，該子查詢會(huì)被作為一個(gè)視圖來對(duì)待，因此頁(yè)叫做內(nèi)嵌

視圖，當(dāng)在from子句中使用子查詢時(shí)，必須給子查詢指定別名！給表取別名的時(shí)候，不能加as,給列取別名

的時(shí)候，可以加as。

重點(diǎn)：7：分頁(yè)查詢

一：以rownum為例來講解：

<1>：rownum分頁(yè)：

(select*fromemp)

<2>：顯示rownumrn:

SQL>selectal.*,rownumrnfrom(select*fromemp)al;//rownumrn是給表的每一行分配的行號(hào)，但行

號(hào)是變化的，它不是固定的：*表示全部列。也可以這樣就只能分列查詢了，rn是列號(hào)。

SQL>selectal.ename,al.deptno,rownumrnfrom(select*fromemporderbyempno)al;

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNORN

7369SMITHCI￡RK79021980-12-17800.0020

7499ALLENSALESMAN76981981-2-201600.00300.00302

7521WARDSALESMAN76981981-2-221250.00500.00303

7566JONESMANAGER78391981-4-22975.0020I

7654MARTINSALESMAN76981981-9-281250.001400.00305

7698BLAKEMANAGER78391981-5-12850.00306

7782CLARKMANAGER78391981-6-92450.00107

7788SCOTTANALYST75661987-4-193000.00208

7839KINGPRESIDENT1981-11-175000.00103

7844TURMERSALESMAN76981981-9-81500.000.003010

7876ADAMSCI￡RK77881987-5-231100.002011

7900JAMESCLERK76981981-12-3950.003012

7902FORDA.NKLYST75661981-12-33000.002013

7934MILLERCI￡RK77821982-1-231300.001014

9999shunpingCLERK77821988-5-52456.3455.661015

SQL>selectal.*,rownumrnfrom(select*fromemp)alorderbysal;!=SQL>selectal.*,rownumrnfrom

(select*fromemporderbysal)al;〃前是先排序排號(hào)再按orderbysal排列，序號(hào)

是混亂的。而后面序號(hào)是每個(gè)增加1的。

<3>：

?如查詢出6<=id<=10的員工的信息：

SQL>selectal.*,rownumrnfrom(select*fromemp)alwhererownum<=10;

AMEJOBMGRHIREDATESALCOMMDEPTHORN

ITHCLERK79021980-12-17800.00201

LENSALESMAN76981981-2-201600.00300.00302

RDSALESMAN76981981-2-221250.00500.00303

NESMANAGER78391981-4-22975.00204

RTIKSALESMAN76981981-9-281250.001400.00305

AKEMANAGER78391981-5-12850.00306

ARKMANAGER78391981-6-92450.00107

OTTANALYST75661987-4-193000.00208

HGPRESIDENT1981-11-175000.00109

RRERSALESMAN76981981-9-81500.000.003010

SQL>selectal.*,rownumrnfrom(select*fromemp)alwhererownum<=10andrownum>=6;

\MEJOBMGRHIREDATESALCOMMDEPTHORN

//可以看出，什么都查不出來，其實(shí)。racle有規(guī)定，rownum一次只能用一次，它不能用兩次或兩次以上,

這樣用是沒有用的！

SQL>select*from(selectal.*,rownumrnfrom(select*fromemp)alwhererownum<=10)wherern>=6;

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNORN

7698BLAKEONAGER78391981-5-12850.00306

7782CLARKMANAGER78391981-6-92450.00107

7788SCOTTANALYST75661987-4-193000.00208

7839KINGPRESIDENT1981-11-175000.00109

7844TURNERSALESMAN76981981-9-81500.000.003010

〃注：oracle的分頁(yè)查詢是以二分機(jī)制來實(shí)現(xiàn)的，它的優(yōu)點(diǎn)是查詢的速度非?？?，也就是說，我們要查詢出

6<=rn<=10的數(shù)據(jù)表信息，那么我們必須先查詢出rownum<=10的表，再在rn<=10的表上查出rn>=6的表！

rownum和rn是不能混寫的。

按雇員的ID號(hào)升序取出：［如查詢出6<=id<=10的員工的信息］

〃查詢顯示結(jié)果的所有的改動(dòng)只需改最里層的select查詢語(yǔ)句就行了。

?如要如查詢出6<=id<=10的員工的姓名，工資，部門信息：

SQL>select*from(selectal.*zrownumrnfrom(selectename,sal,deptnofromemp)alwhererownum<=10)where

rn>=6;

三NAMESALDEPTNORN

TURNER1500.00306

ADAMS1100.00207

JAMES950.00308

FORD3000.00209

MILLER1300.001010

?按照其工資從低到高排序：

SQL>select*from(selectal.*,rownumrnfrom(selectename,sa^deptnofromemporderbysal)alwhere

rownum<=10)wherern>=6;

ENAMESALDEPTNORN

MILLZR1300.00106

TURNZR1500.00307

ALLEN1600.00308

CLARK2450.00109

BLAKE2850.003010

//rn是自動(dòng)顯示的！

〃降序是：

SQL>select*from(selectal.*,rownumrnfrom(selectename,sal,deptnofromemporderbysaldescjalwhere

rownum<=10)wherern>=6;

ZNAMZSALDEPTNORN

CLARK2450.00106

ALLEN1600.00307

TURNZR1500.00308

MILLZR1300.00109

MARTIN1250.003010

法二：

SQL>selectename,deptno,salfrom(selectt.*,row_number()over(orderbysaldesc)rnfromempt)wherern<10

andrn>6;

ENAMEDEPTNOSAL

TUPNKR301500.00

HILLER101300.00

MARTIN301250.00

SQL>selectename,deptno,sal,rnfrom(selectt.*,rrow_number()over(orderbydeptnodesc)rnfromempt)where

rn<10andrn>6;

ENAMEDEPTNOSALPN

BLAKE302850.007I

SCOTT20800.008

SMITH20800.009

〃從法二可以看出：rn是選擇顯示的；over。是用來排序的，它相對(duì)來說較簡(jiǎn)單，它內(nèi)部自動(dòng)生成了子表，省

去了法三的建立子表過程！

//oracle分頁(yè)一共有三種方式:

1.根據(jù)ROWID來分

select?frooit.xiaoxiwhererowidin(selectridfrom(selectrownumrn,rid

from(selectrowidrid,cidfrom

t.xiaoxiorderbyciddesc)whererownum<l0000)wherern>9980)orderbycid

dose*

執(zhí)行每問0.03秒

2.按分析函數(shù)來分

select?from(selectt.row.number()over(orderbyciddesc)rkfrom

t.xiaoxit)whererk<l0000andrk>9980;

執(zhí)行時(shí)間I.01秒

from(se1ectt.rownum

whererownum〈l0000)wherji

其中t_xiaoxi為表名稱，cid為表的關(guān)鍵字段，取按CID降序排序后的第9981-9999條記錄,

St.xiaoxi表有70000多條記錄

個(gè)人感覺1的效率最好，3次之，2最差

〃測(cè)試通過的分頁(yè)查詢okokook

select?from(selectal.?,rownumrnfrom(selectename,jobfromemp)al

whererownum<-l0)wherern>-5;

8：用查詢結(jié)果創(chuàng)建新表：

這個(gè)命令是一種快捷的建表方法：

SQL>createtablemyemp2(id,ename,sal)asselectEMPNO,ename,salfromemp;

9：合并查詢

有時(shí)在實(shí)際應(yīng)用中，為了合并多個(gè)select語(yǔ)句的結(jié)果，可以使用集合操作符號(hào)union,unionall,intersect,

minus

l):union

該操作符用于取得兩個(gè)結(jié)果集的并集，相當(dāng)于‘或者'。當(dāng)使用該操作符時(shí)，會(huì)自動(dòng)去掉結(jié)果集中重復(fù)

行，也就是兩個(gè)集合交集的重復(fù)的部分。

SQL>selectename,sal,jobfromempwheresal>=2500unionselectename,saljobfromempwherejob='MANAGER';

三NAMESALJOB

BLAKE2850.00MANAGER

CLARK2450.00MANAGER

FORD3000.00ANALYST

JONES2975.00MANAGER

KING5000.00PRESIDZNT

SCOTT3000.00ANALYST

SQL>selectename,saljobfromempwheresal>=2500andjob='MANAGER';

ENAMESALJOB

BLAKE2850.00MANAGER

JONES2975.00MANAGER

〃可以看出，and是既是...又是....。

//SQL>selectename,saljobfromempwheresal>=2500;

ENAMESALJOB

BLAKE2850.00MANAGER

SCOTT3000.00ANALYST

KINGS000.00PRESIDENT

FORD3000.00ANALYST

JONHS2975.00MANAGER

//SQL>selectename,saljobfromempwherejob='MANAGER,;

HNAMZSALJOB

BLAKZ2850.00MANAGER

CLARK2450.00MANAGER

JONZS2975.00MANAGER

從上兩表可以看出，有兩條是重復(fù)的，但是，用了Union后，它們之間重復(fù)的部分自動(dòng)刪除一次！

2)：unionall

該操作賦于union相似，但是它不會(huì)取消重復(fù)行，而且不會(huì)排序。

SQL>selectename,sal,jobfromempwheresal>=2500unionallselectename,saljobfromempwhere

job='MANAGER';

ENAMESALJOB

BLAKE2850.00MANAGER

SCOTT3000.00ANALYST

KING5000.00PRESIDENT

FORD3000.00ANALYST

JONZS2975.00MANAGER

BLAKE2850.00MANAGER

CLARK2450.00MANAGER

JONZS297S.00MANAGER

該操作符用于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí)，不會(huì)自動(dòng)去掉結(jié)果集中重復(fù)行。

3)intersect

使用該操作符用于取兩個(gè)結(jié)果集的交集。

SQL>selectename,sal,jobfromempwheresal>2500intersectselectename,sal,jobfromempwhere

job='MANAGER';

ZNAMHSALJOB

BLAKE2850.00MANAGER

JONES2975.00MANAGER

4):minus

使用該操作符用于取得兩個(gè)結(jié)果集的差集，它只會(huì)顯示存在第一個(gè)集合中，而不存在第二個(gè)集合中數(shù)據(jù),

所以，第一個(gè)集一定要比第二個(gè)集大。

SQL>selectename,sal,jobfromempwheresal>2500minusselectename,saljobfromempwherejob='MANAGE';

ENAMESALJOB

BLAKZ2850.00MANAGER

FORD3000.00ANALYST

JONES2975.00MANAGER

KING5000.00PRHSIDENT

SCOTT3000.00ANALYST

〃集合操作遠(yuǎn)遠(yuǎn)比and,or速度快得多。

創(chuàng)建新的數(shù)據(jù)庫(kù)

1:創(chuàng)建數(shù)據(jù)庫(kù)有兩種方法：

1)通過。racle提供的向?qū)Чぞ?/p>

創(chuàng)建數(shù)據(jù)庫(kù)使用工具：databaseconfigurationassistant簡(jiǎn)稱dbca［數(shù)據(jù)庫(kù)配置助手］

2)我們可以用手工步驟直接創(chuàng)建

第三天

期望目標(biāo)：

1.掌握oracle表對(duì)數(shù)據(jù)操作技巧

2.掌握在java程序中操作oracle

3.理解。racle事物概念

4.掌握oracle各種sql函數(shù)

1：java連接oracle

介紹：前面我們一直在plsql中操作oracle,那么如何在java程序中操作數(shù)據(jù)庫(kù)呢？

下面我們舉例說明，寫一個(gè)showEmp.java,分頁(yè)顯示emp表的用戶信息。

參照視頻，是一個(gè)重點(diǎn)內(nèi)容！

2:使用特定格式插入日期值

使用to_date函數(shù)(可以由你任意指定格式)

?如何插入帶有日期的表，并且按照年-月-日的格式插入：

小紅

SQL>insertintoempvalues(9998,'2','MANAGER','7782',to_date('1988-12-12'；yyyy-mm-dd'),78.9,55.33,10);

HMPNOENAMEJOBMGRHIREDATHSA1|COMMDEPTNO

9998小紅2MANAGER77821988-12-1278.9055.3310

3：使用子查詢插入數(shù)據(jù)

介紹：當(dāng)使用values子名時(shí)，一次只能插入一行數(shù)據(jù)，當(dāng)使用子查詢插入數(shù)據(jù)時(shí)，一條insert語(yǔ)句可以插入

大量的數(shù)據(jù)。當(dāng)處理行遷移或者裝載外部表的數(shù)據(jù)到數(shù)據(jù)庫(kù)時(shí)，可以使用子查詢來插入數(shù)據(jù)。

1)、創(chuàng)建表kkk:

SQL>createtablekkk(myldnumber(4),mynamevarchar2(50),myDeptnumber(5));

MYIDMYNAMHMYDEPT

//CREATETABLE.......asselect........；創(chuàng)建表時(shí)插入數(shù)據(jù)。

2)、用into語(yǔ)句把表emp中deptno為10的員工的基本信息插入到kkk表中：

SQL>insertintokkk(myld,mynamezmydept)selectempno,ename,deptnofromempwheredeptno='10';

MYIDMYNAMEMYDEPT

9998小紅10

9998小紅210

9998小紅210

7782CLARK10

7839KING10

7934MILLER10

4：使用子查詢更新數(shù)據(jù)

介紹：使用update語(yǔ)句更新數(shù)據(jù)時(shí)，既可以使用表達(dá)式或者數(shù)值直接修改數(shù)據(jù)，也可以使用子查詢查詢修改

數(shù)據(jù)。

?希望員工SCOTT的崗位、工資、補(bǔ)助與SMITH員工一樣：

//同時(shí)查詢符合幾個(gè)條件的語(yǔ)句的條件間用or連接或者直接用any或者直接用in：

SQL>select*fromempwhereename='SCOTT'ORENAME='SMITH';

SQL>select*fromempwhereename=ANY(,SMITH,,'SCOTT,);

SQL>SELECT*FROMEMPWHEREENAMEINCSMITH'/SCOTT');

HMPNOENAMEJOBMGRHIRSDATHSALCOMMDEPTNO

7788SCOTTCLERK7S661981-4-19800.0020

7369SMITHCLZRK79021982-12-17800.0020

方法一：

SQL>updateempsetjob=(selectjobfromempwhereename='SMITH'),sal=(selectsalfromempwhere

ename='SMITH'),comm=(selectcommfromempwhereename='SMITH')whereename='SCOTT';〃這樣一個(gè)個(gè)

導(dǎo)入很麻煩！

方法二：SQL>updateempset(job,sal,comm)=(selectjob,sal,commfromempwhereename='SMITH')where

ename='SCOTT';

5：oracle中事務(wù)處理

1)：什么是事務(wù)

事務(wù)用于保證數(shù)據(jù)的一致性，它由一組相關(guān)的dml語(yǔ)句組成，該組的dml語(yǔ)句(增、刪、改)要么全部成功，

要么全部失敗。

如：網(wǎng)上轉(zhuǎn)賬就是典型的要用事務(wù)來處理，用以保證數(shù)據(jù)的一致性。你把錢從工行轉(zhuǎn)到你農(nóng)行的卡里去，你

工行里減錢的事件a和你農(nóng)行里增錢的事件b,它們其實(shí)就可以叫做是一個(gè)事務(wù)！

2)：事務(wù)和鎖

當(dāng)執(zhí)行事務(wù)操作時(shí)(dml語(yǔ)句)，oracle會(huì)在被作用的表上加鎖，防止其它用戶改表的結(jié)構(gòu)。這果對(duì)我們用戶

來講是非常重要的。

//鎖就是訪問的權(quán)限機(jī)制。第一個(gè)人去訪問某個(gè)文件，都在先看鎖的狀態(tài)。如果這時(shí)，鎖沒有

人使用，是處于開放使用權(quán)限狀態(tài)，那么此人就直接可以拿到鎖的使用權(quán)進(jìn)入訪問此文件狀態(tài)；如果此時(shí)鎖

正在處于被使用(關(guān)閉)狀態(tài)，那么此人就會(huì)形成等待狀態(tài)，形成等待隊(duì)列。每個(gè)人訪問的步驟是先看有沒

有人在等待，再看鎖的狀態(tài)情況！

3):提交事務(wù)

當(dāng)執(zhí)行使用commit語(yǔ)句可以提交事務(wù)。當(dāng)執(zhí)行了commit語(yǔ)句過后，會(huì)確認(rèn)事務(wù)的變化、結(jié)束事務(wù)、刪除保

存點(diǎn)、釋放鎖，當(dāng)使用commit語(yǔ)句結(jié)束事務(wù)過后，其它會(huì)話將可以查看到事務(wù)變化后的新數(shù)據(jù)。

如果不做commit提交事務(wù)，那么關(guān)閉或者退出(exit)，會(huì)自動(dòng)提交！

直接寫rollback,則是取消全部保存點(diǎn)，不是提交事務(wù)！

4)：回退事務(wù)

在介紹回退事務(wù)前，我們先介紹一下保存點(diǎn)(savepoint)的概念和作用：保存點(diǎn)是事務(wù)中的一點(diǎn)，用于

取消部分事務(wù)，當(dāng)結(jié)束事務(wù)時(shí)，會(huì)自動(dòng)的刪除該事務(wù)所定義的所有保存點(diǎn)。當(dāng)執(zhí)行rollback時(shí)，通過指定保存

點(diǎn)可以回退到指定的點(diǎn)。這果我們作圖說明：

5)java程序中如何使用事務(wù)

在java操作數(shù)據(jù)庫(kù)時(shí)，為了保證數(shù)據(jù)的一致性，比如轉(zhuǎn)帳操作：

<1>:從帳戶SCOTT的sal中減掉10￥

<2>：在另一個(gè)帳戶SMITH上加上10￥,讓我們來看看如何使用事務(wù)？：

程序如下：

importjava.beans.Statement;

importerfaces.RSAKey;

importjava.sql.Connection;

importjava.sql.DriverManager;

importorg.omg.CORBA.CTX_RESTRICT_SCOPE;

publicclassTestTrans{

publicstaticvoidmain(String[]args){

//TODOAuto-generatedmethodstub

//事務(wù)案例

try(

//I.加載驅(qū)動(dòng)

Class.forName(Horacle.jdbc.driver.oracleDivern);

//2.得到連接

Connectionct=DriverManager.getConnection

nnnnHn

(jdbc:oracle:thin:0:1521:orclzxiaomint,ml23);

//加入事務(wù)處理

ct.setAutoCommit(false);//設(shè)置它不能默認(rèn)提交！

Statementsm=ct.createStatement();

sm.executeUpdate("updateempsetsal=sal-100whereename=1SCOTT*n);

//從SCOTT賬戶工資里-100￥；

inti=7/0;//拋異常,執(zhí)行了上面一句跳到這里來，不能再往下執(zhí)行直接拋異常，但是，SCOTT

的工資減了！

//如果把inti=7/0；注釋掉，那萬一后面的語(yǔ)句本身出了異常，怎么辦呢，它還是執(zhí)行了SCOTT減去100$了，

但是，SMITH還是沒有加上100￥??！所以，要想：不管是出現(xiàn)什么原因，都要同時(shí)成功和同時(shí)失敗的話，那么就

得加入事務(wù)語(yǔ)句：

sm?executeUpdate("updateempsetsal=sal+100whereename=**SMITH0);

//給SM工TH賬戶工資+100￥；

ct.commit();//提交事務(wù)

//關(guān)閉資源

sm.close();

ct.close();

}catch(Exceptione){

//如果發(fā)生異常，就回滾

try(

ct.rollback();//rollback本身可能發(fā)生異常,所有得try一下！

}catch(Exceptionex){

ex.printStackTrace();

//TODO:handleexception

)

e.printStackTrace();

//TODO:handleexception

)

)

)

6):只讀事務(wù)

只讀事務(wù)是指只允許執(zhí)行查詢的操作，而不允許執(zhí)行任何其它dml操作事務(wù)，使用只讀事務(wù)可以確保用戶

只能取得某時(shí)間點(diǎn)的數(shù)據(jù)。假定機(jī)票代售點(diǎn)每天18點(diǎn)開始統(tǒng)計(jì)今天的售票情況，這時(shí)可以使用只讀事務(wù)。在

設(shè)置了只讀事務(wù)后，盡管其它會(huì)話可能會(huì)提交新的事務(wù)，但是只讀事務(wù)將不會(huì)取得最新數(shù)據(jù)的變化，從而可

以保證取得特定時(shí)間點(diǎn)的數(shù)據(jù)信息。

7)設(shè)置只讀事務(wù)

Settransactionreadonly;

《事例說明:》

Sql>connsystem/123456;

Sql>settransactionreadonly;

SQL>select*fromxiaoming.emp;

SQL>insertintoxiaoming.emp

values(8888,'ok','MANAGER',7782,to_date('1988/12/：12','yyyy/mm/dd'),34.34,39.34,30);

報(bào)錯(cuò)：ORA-01456:不能在READONLY事務(wù)處理中執(zhí)行插入/刪除/更新操作

SQL>connxiaoming/ml23;

,,,

SQL>insertintoempvalues(8888,'ok；MANAGER/7782/to_date('1988-12-127yyyy-mm-dd)z34.34/39.34/30);

SQL>select*fromempwhereename='ok';

EMPNOENAMZJOBMGRHIREDATHSALCOMMDHPTNO

8888okMANAGER77821988-12-1234.3439.3430

Sql>connsystem/123456;

Sql>select*fromxia