oracle11g教程從入門到精通

一：Oracle認證，與其它數(shù)據(jù)庫比較，安裝Oracle安裝會自動的生成sys用戶和system用戶:sys用戶是超級用戶，具有最高權(quán)限，具有sysdba角色，有createdatabase的權(quán)限，該用戶默認的密碼是change_on_installsystem用戶是管理操作員，權(quán)限也很大。具有sysoper角色，沒有createdatabase的權(quán)限，默認的密碼是manager一般講，對數(shù)據(jù)庫維護，使用system用戶登錄就可以拉也就是說sys和system這兩個用戶最大的區(qū)別是在于有沒有createdatabase的權(quán)限。二：Oracle的基本使用--基本命令sql*plus的常用命令

連接命令1.conn[ect]

用法：conn用戶名/密碼@網(wǎng)絡(luò)服務(wù)名[assysdba/sysoper]當用特權(quán)用戶身份連接時，必須帶上assysdba或是assysoper

2.disc[onnect]

說明:該命令用來斷開與當前數(shù)據(jù)庫的連接

3.psssw[ord]

說明:該命令用于修改用戶的密碼，如果要想修改其它用戶的密碼，需要用sys/system登錄。

4.showuser

說明:顯示當前用戶名

說明:該命令會斷開與數(shù)據(jù)庫的連接，同時會退出sql*plus文件操作命令1.start和@

說明:運行sql腳本

案例:sql>@d:\a.sql或是sql>startd:\a.sql

,這樣會把d:\a.sql這個文件打開

說明:該命令可以將sql*plus屏幕上的內(nèi)容輸出到指定文件中去。

案例:sql>spoold:\b.sql并輸入sql>spooloff

交互式命令1.&說明：可以替代變量，而該變量在執(zhí)行時，需要用戶輸入。

select*fromempwherejob='&job'；

說明：該命令可以編輯指定的sql腳本

案例：SQL>editd:\a.sql

說明：該命令可以將sql*plus屏幕上的內(nèi)容輸出到指定文件中去。

spoold:\b.sql并輸入spooloff

顯示和設(shè)置環(huán)境變量概述：可以用來控制輸出的各種格式，setshow如果希望永久的保存相關(guān)的設(shè)置，可以去修改glogin.sql腳本說明：設(shè)置顯示行的寬度，默認是80個字符showlinesizesetlinesize90說明：設(shè)置每頁顯示的行數(shù)目，默認是14用法和linesize一樣至于其它環(huán)境參數(shù)的使用也是大同小異三：oracle用戶管理oracle用戶的管理

創(chuàng)建用戶

概述：在oracle中要創(chuàng)建一個新的用戶使用createuser語句，一般是具有dba(數(shù)據(jù)庫管理員)的權(quán)限才能使用。

createuser用戶名identifiedby密碼;(oracle有個毛病，密碼必須以字母開頭，如果以字母開頭，它不會創(chuàng)建用戶)

給用戶修改密碼

概述：如果給自己修改密碼可以直接使用

password用戶名

如果給別人修改密碼則需要具有dba的權(quán)限，或是擁有alteruser的系統(tǒng)權(quán)限

SQL>alteruser用戶名identifiedby新密碼

刪除用戶

概述：一般以dba的身份去刪除某個用戶，如果用其它用戶去刪除用戶則需要具有dropuser的權(quán)限。

比如dropuser用戶名【cascade】

在刪除用戶時，注意：

如果要刪除的用戶，已經(jīng)創(chuàng)建了表，那么就需要在刪除的時候帶一個參數(shù)cascade;

用戶管理的綜合案例

概述：創(chuàng)建的新用戶是沒有任何權(quán)限的，甚至連登陸的數(shù)據(jù)庫的權(quán)限都沒有，需要為其指定相應(yīng)的權(quán)限。給一個用戶賦權(quán)限使用命令grant，回收權(quán)限使用命令revoke。

為了給講清楚用戶的管理，這里我給大家舉一個案例。

SQL>connxiaoming/m12;

ERROR:

ORA-01045:userXIAOMINGlacksCREATESESSIONprivilege;logondenied

警告:您不再連接到ORACLE。

SQL>showuser;

USER為""

SQL>connsystem/p;

已連接。

SQL>grantconnecttoxiaoming;

授權(quán)成功。

SQL>connxiaoming/m12;//后面的為密碼分開來輸入。

已連接。

SQL>

注意：grantconnecttoxiaoming;在這里，準確的講，connect不是權(quán)限，而是角色。?？磮D：

現(xiàn)在說下對象權(quán)限，現(xiàn)在要做這么件事情：

*希望xiaoming用戶可以去查詢emp表

*希望xiaoming用戶可以去查詢scott的emp表

grantselectonemptoxiaoming

*希望xiaoming用戶可以去修改scott的emp表

grantupdateonemptoxiaoming

*希望xiaoming用戶可以去修改/刪除，查詢，添加scott的emp表

grantallonemptoxiaoming

*scott希望收回xiaoming對emp表的查詢權(quán)限

revokeselectonempfromxiaoming

//對權(quán)限的維護。

*希望xiaoming用戶可以去查詢scott的emp表/還希望xiaoming可以把這個權(quán)限繼續(xù)給別人。

--如果是對象權(quán)限，就加入withgrantoption

grantselectonemptoxiaomingwithgrantoption

我的操作過程：

SQL>connscott/tiger;

已連接。

SQL>grantselectonscott.emptoxiaomingwithgrantoption;

授權(quán)成功。

SQL>connsystem/p;

已連接。

SQL>createuserxiaohongidentifiedbym123;

用戶已創(chuàng)建。

SQL>grantconnecttoxiaohong;

授權(quán)成功。

SQL>connxiaoming/m12;

已連接。

SQL>grantselectonscott.emptoxiaohong;

授權(quán)成功。--如果是系統(tǒng)權(quán)限。

system給xiaoming權(quán)限時：

grantconnecttoxiaomingwithadminoption

問題：如果scott把xiaoming對emp表的查詢權(quán)限回收，那么xiaohong會怎樣？

答案：被回收。下面是我的操作過程：

SQL>connscott/tiger;

已連接。

SQL>revokeselectonempfromxiaoming;

撤銷成功。

SQL>connxiaohong/m123;

已連接。

SQL>select*fromscott.emp;

select*fromscott.emp

第1行出現(xiàn)錯誤:

ORA-00942:表或視圖不存在

結(jié)果顯示：小紅受到誅連了。使用profile管理用戶口令

概述：profile是口令限制，資源限制的命令集合，當建立數(shù)據(jù)庫的，oracle會自動建立名稱為default的profile。當建立用戶沒有指定profile選項，那么oracle就會將default分配給用戶。

概述：指定該賬戶(用戶)登陸時最多可以輸入密碼的次數(shù)，也可以指定用戶鎖定的時間(天)一般用dba的身份去執(zhí)行該命令。

例子：指定scott這個用戶最多只能嘗試3次登陸，鎖定時間為2天，讓我們看看怎么實現(xiàn)。

創(chuàng)建profile文件

SQL>createprofilelock_accountlimitfailed_login_attempts3password_lock_time2;

SQL>alteruserscottprofilelock_account;

2.給賬戶(用戶)解鎖

SQL>alteruserteaaccountunlock;

為了讓用戶定期修改密碼可以使用終止口令的指令來完成，同樣這個命令也需要dba的身份來操作。

例子：給前面創(chuàng)建的用戶tea創(chuàng)建一個profile文件，要求該用戶每隔10天要修改自己的登陸密碼，寬限期為2天。看看怎么做。

SQL>createprofilemyprofilelimitpassword_life_time10password_grace_time2;

SQL>alteruserteaprofilemyprofile;口令歷史

概述：如果希望用戶在修改密碼時，不能使用以前使用過的密碼，可使用口令歷史，這樣oracle就會將口令修改的信息存放到數(shù)據(jù)字典中，這樣當用戶修改密碼時，oracle就會對新舊密碼進行比較，當發(fā)現(xiàn)新舊密碼一樣時，就提示用戶重新輸入密碼。

例子：

1）建立profile

SQL>createprofilepassword_historylimitpassword_life_time10password_grace_time2password_reuse_time10

password_reuse_time//指定口令可重用時間即10天后就可以重用

2）分配給某個用戶

刪除profile

概述：當不需要某個profile文件時，可以刪除該文件。

SQL>dropprofilepassword_history【casade】

注意：文件刪除后，用這個文件去約束的那些用戶通通也都被釋放了。加了casade，就會把級聯(lián)的相關(guān)東西也給刪除掉四：oracle表的管理(數(shù)據(jù)類型，表創(chuàng)建刪除，數(shù)據(jù)CRUD操作)oracle的表的管理表名和列的命名規(guī)則必須以字母開頭長度不能超過30個字符不能使用oracle的保留字只能使用如下字符A-Z，a-z，0-9，$,#等oracle支持的數(shù)據(jù)類型字符類char

定長最大2000個字符。

例子：char(10)

‘小韓’前四個字符放‘小韓’，后添6個空格補全如‘小韓’

varchar2(20)

變長

最大4000個字符。

例子：varchar2（10）‘小韓’oracle分配四個字符。這樣可以節(jié)省空間。

clob(characterlargeobject)字符型大對象最大4G

char查詢的速度極快浪費空間，查詢比較多的數(shù)據(jù)用。varchar節(jié)省空間

數(shù)字型

number范圍-10的38次方到10的38次方

可以表示整數(shù)，也可以表示小數(shù)

number(5,2)

表示一位小數(shù)有5位有效數(shù)，2位小數(shù)

范圍：-999.99到999.99

number(5)

表示一個5位整數(shù)

范圍99999到-99999

日期類型

date包含年月日和時分秒

oracle默認格式

1-1月-1999

timestamp這是oracle9i對date數(shù)據(jù)類型的擴展?？梢跃_到毫秒。

圖片

blob二進制數(shù)據(jù)可以存放圖片/聲音

4G

一般來講，在真實項目中是不會把圖片和聲音真的往數(shù)據(jù)庫里存放，一般存放圖片、視頻的路徑，如果安全需要比較高的話，則放入數(shù)據(jù)庫。

怎樣創(chuàng)建表

建表

--學(xué)生表

createtablestudent(

表名

xh

number(4),

--學(xué)號

xm

varchar2(20),

--姓名

sex

char(2),

--性別

birthdaydate,

--出生日期

sal

number(7,2)

--獎學(xué)金

);--班級表

CREATETABLEclass(

classIdNUMBER(2),

cNameVARCHAR2(40));修改表

添加一個字段SQL>ALTERTABLEstudentadd(classIdNUMBER(2));

修改一個字段的長度

SQL>ALTERTABLEstudentMODIFY(xmVARCHAR2(30));

修改字段的類型/或是名字（不能有數(shù)據(jù)）不建議做

SQL>ALTERTABLEstudentmodify(xmCHAR(30));

刪除一個字段

不建議做(刪了之后，順序就變了。加就沒問題，應(yīng)為是加在后面)

SQL>ALTERTABLEstudentDROPCOLUMNsal;

修改表的名字

很少有這種需求

SQL>RENAMEstudentTOstu;

刪除表

SQL>DROPTABLEstudent;

添加數(shù)據(jù)

所有字段都插入數(shù)據(jù)

INSERTINTOstudentVALUES('A001','張三','男','01-5月-05',10);

oracle中默認的日期格式‘dd-mon-yy’dd日子（天）mon月份

yy

2位的年

‘09-6月-99’1999年6月9日

修改日期的默認格式（臨時修改，數(shù)據(jù)庫重啟后仍為默認；如要修改需要修改注冊表）

ALTERSESSIONSETNLS_DATE_FORMAT='yyyy-mm-dd';

修改后，可以用我們熟悉的格式添加日期類型：

INSERTINTOstudentVALUES('A002','MIKE','男','1905-05-06',10);

插入部分字段

INSERTINTOstudent(xh,xm,sex)VALUES('A003','JOHN','女');

插入空值

INSERTINTOstudent(xh,xm,sex,birthday)VALUES('A004','MARTIN','男',null);

問題來了，如果你要查詢student表里birthday為null的記錄，怎么寫sql呢？

錯誤寫法：select*fromstudentwherebirthday=null;

正確寫法：select*fromstudentwherebirthdayisnull;

如果要查詢birthday不為null,則應(yīng)該這樣寫：

select*fromstudentwherebirthdayisnotnull;

修改數(shù)據(jù)

修改一個字段UPDATEstudentSETsex='女'WHERExh='A001';

修改多個字段

UPDATEstudentSETsex='男',birthday='1984-04-01'WHERExh='A001';

修改含有null值的數(shù)據(jù)

不要用=null而是用isnull；

SELECT*FROMstudentWHEREbirthdayISnull;

刪除數(shù)據(jù)

DELETEFROMstudent;

刪除所有記錄，表結(jié)構(gòu)還在，寫日志，可以恢復(fù)的，速度慢。

Delete的數(shù)據(jù)可以恢復(fù)。

savepointa;--創(chuàng)建保存點

DELETEFROMstudent;

rollbacktoa;

--恢復(fù)到保存點

一個有經(jīng)驗的DBA，在確保完成無誤的情況下要定期創(chuàng)建還原點。

DROPTABLEstudent;--刪除表的結(jié)構(gòu)和數(shù)據(jù)；

deletefromstudentWHERExh='A001';--刪除一條記錄；

truncateTABLEstudent;--刪除表中的所有記錄，表結(jié)構(gòu)還在，不寫日志，無法找回刪除的記錄，速度快。五：oracle表查詢(1)oracle表基本查詢

介紹在我們講解的過程中我們利用scott用戶存在的幾張表（emp，dept）為大家演示如何使用select語句，select語句在軟件編程中非常有用，希望大家好好的掌握。

emp雇員表

clerk

普員工

salesman銷售

manager

經(jīng)理

analyst分析師

president

總裁

mgr

上級的編號

hiredate入職時間

sal月工資

comm獎金

deptno部門

dept部門表

deptno部門編號

accounting財務(wù)部

research

研發(fā)部

operations業(yè)務(wù)部

loc部門所在地點

salgrade

工資級別

grade

級別

losal

最低工資

hisal

最高工資

簡單的查詢語句

查看表結(jié)構(gòu)DESCemp;

查詢所有列

SELECT*FROMdept;

切忌動不動就用select*

SETTIMINGON;打開顯示操作時間的開關(guān)，在下面顯示查詢時間。

CREATETABLEusers(userIdVARCHAR2(10),uNameVARCHAR2(20),uPasswVARCHAR2(30));

INSERTINTOusersVALUES('a0001','啊啊啊啊','aaaaaaaaaaaaaaaaaaaaaaa');

--從自己復(fù)制，加大數(shù)據(jù)量大概幾萬行就可以了

可以用來測試sql語句執(zhí)行效率

INSERTINTOusers(userId,UNAME,UPASSW)SELECT*FROMusers;

SELECTCOUNT(*)FROMusers;統(tǒng)計行數(shù)

查詢指定列

SELECTename,sal,job,deptnoFROMemp;

如何取消重復(fù)行DISTINCT

SELECTDISTINCTdeptno,jobFROMemp;

查詢SMITH所在部門，工作，薪水

SELECTdeptno,job,salFROMempWHEREename='SMITH';

注意：oracle對內(nèi)容的大小寫是區(qū)分的，所以ename='SMITH'和ename='smith'是不同的

使用算術(shù)表達式

nvl

null

問題：如何顯示每個雇員的年工資？

SELECTsal*13+nvl(comm,0)*13"年薪",ename,commFROMemp;

使用列的別名

SELECTename"姓名",sal*12AS"年收入"FROMemp;

如何處理null值

使用nvl函數(shù)來處理

如何連接字符串（||）

SELECTename

||'isa'||jobFROMemp;

使用where子句

問題：如何顯示工資高于3000的員工？

SELECT*FROMempWHEREsal>3000;

問題：如何查找1982.1.1后入職的員工？SELECTename,hiredateFROMempWHEREhiredate>'1-1月-1982';

問題：如何顯示工資在2000到3000的員工？

SELECTename,salFROMempWHEREsal>=2000ANDsal<=3000;

如何使用like操作符

%：表示0到多個字符

_：表示任意單個字符

問題：如何顯示首字符為S的員工姓名和工資？

SELECTename,salFROMempWHEREenamelike'S%';

如何顯示第三個字符為大寫O的所有員工的姓名和工資？

SELECTename,salFROMempWHEREenamelike'__O%';

在where條件中使用in

問題：如何顯示empno為7844,7839,123,456的雇員情況？

SELECT*FROMempWHEREempnoin(7844,7839,123,456);

使用isnull的操作符

問題：如何顯示沒有上級的雇員的情況？

錯誤寫法：select*fromempwheremgr='';

正確寫法：SELECT*FROMempWHEREmgrisnull;六：oracle表查詢(2)使用邏輯操作符號

問題：查詢工資高于500或者是崗位為MANAGER的雇員，同時還要滿足他們的姓名首字母為大寫的J？

SELECT*FROMempWHERE(sal>500orjob='MANAGER')andenameLIKE'J%';

使用orderby字句

默認asc

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

SELECT*FROMempORDERbysal;

問題：按照部門號升序而雇員的工資降序排列

SELECT*FROMempORDERbydeptno,salDESC;

使用列的別名排序問題：按年薪排序

selectename,(sal+nvl(comm,0))*12"年薪"fromemporderby"年薪"asc;

別名需要使用“”號圈中,英文不需要“”號

分頁查詢

等學(xué)了子查詢再說吧。。。。。。。。

Clear清屏命令

oracle表復(fù)雜查詢

說明

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

數(shù)據(jù)分組——max，min，avg，sum，count

問題：如何顯示所有員工中最高工資和最低工資？

SELECTMAX(sal),min(sal)FROMempe;

最高工資那個人是誰？

錯誤寫法：selectename,salfromempwheresal=max(sal);

正確寫法：selectename,salfromempwheresal=(selectmax(sal)fromemp);

注意：selectename,max(sal)fromemp;這語句執(zhí)行的時候會報錯，說ORA-00937：非單組分組函數(shù)。因為max是分組函數(shù)，而ename不是分組函數(shù)

但是selectmin(sal),max(sal)fromemp;這句是可以執(zhí)行的。因為min和max都是分組函數(shù)，就是說：如果列里面有一個分組函數(shù)，其它的都必須是分組函數(shù)，否則就出錯。這是語法規(guī)定的問題：如何顯示所有員工的平均工資和工資總和？

問題：如何計算總共有多少員工問題：如何擴展要求：

查詢最高工資員工的名字，工作崗位

SELECTename,job,salFROMempewheresal=(SELECTMAX(sal)FROMemp);

顯示工資高于平均工資的員工信息

SELECT*FROMempewheresal>(SELECTAVG(sal)FROMemp);

groupby和having子句

groupby用于對查詢的結(jié)果分組統(tǒng)計，

having子句用于限制分組顯示結(jié)果。

問題：如何顯示每個部門的平均工資和最高工資？

SELECTAVG(sal),MAX(sal),deptnoFROMempGROUPbydeptno;

（注意：這里暗藏了一點，如果你要分組查詢的話，分組的字段deptno一定要出現(xiàn)在查詢的列表里面，否則會報錯。因為分組的字段都不出現(xiàn)的話，就沒辦法分組了）

問題：顯示每個部門的每種崗位的平均工資和最低工資？

SELECTmin(sal),AVG(sal),deptno,jobFROMempGROUPbydeptno,job;

問題：顯示平均工資低于2000的部門號和它的平均工資？

SELECTAVG(sal),MAX(sal),deptnoFROMempGROUPbydeptnohavingAVG(sal)<2000;

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

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

2如果在select語句中同時包含有g(shù)roupby,having,orderby那么它們的順序是groupby,having,orderby

3在選擇列中如果有列、表達式和分組函數(shù)，那么這些列和表達式必須有一個出現(xiàn)在groupby子句中，否則就會出錯。

如SELECTdeptno,AVG(sal),,,d.dnameFROMempe,,,e.salFROMempe,,,s.gradeFROMempe,,,d.dnameFROMempe,,boss.enameFROMempworker,empbossWHEREworker.mgr=boss.empnoANDworker.ename='FORD';

子查詢

什么是子查詢

子查詢是指嵌入在其他sql語句中的select語句，也叫嵌套查詢。

單行子查詢

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

請思考：顯示與SMITH同部門的所有員工？

思路：

1查詢出SMITH的部門號

selectdeptnofromempWHEREename='SMITH';

2顯示

SELECT*FROMempWHEREdeptno=(selectdeptnofromempWHEREename='SMITH');

數(shù)據(jù)庫在執(zhí)行sql是從左到右掃描的，如果有括號的話，括號里面的先被優(yōu)先執(zhí)行。

多行子查詢

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

請思考：如何查詢和部門10的工作相同的雇員的名字、崗位、工資、部門號

SELECTDISTINCTjobFROMempWHEREdeptno=10;

SELECT*FROMempWHEREjobIN(SELECTDISTINCTjobFROMempWHEREdeptno=10);

（注意：不能用job=..，因為等號=是一對一的）

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

問題：如何顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號？

SELECTename,sal,deptnoFROMempWHEREsal>all(SELECTsalFROMempWHEREdeptno=30);

擴展要求：

大家想想還有沒有別的查詢方法。

SELECTename,sal,deptnoFROMempWHEREsal>(SELECTMAX(sal)FROMempWHEREdeptno=30);

執(zhí)行效率上，函數(shù)高得多

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

問題：如何顯示工資比部門30的任意一個員工的工資高的員工姓名、工資和部門號？

SELECTename,sal,deptnoFROMempWHEREsal>ANY(SELECTsalFROMempWHEREdeptno=30);

擴展要求：

大家想想還有沒有別的查詢方法。

SELECTename,sal,deptnoFROMempWHEREsal>(SELECTmin(sal)FROMempWHEREdeptno=30);

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

請思考如何查詢與SMITH的部門和崗位完全相同的所有雇員。

SELECTdeptno,jobFROMempWHEREename='SMITH';

SELECT*FROMempWHERE(deptno,job)=(SELECTdeptno,jobFROMempWHEREename='SMITH');

在from子句中使用子查詢

請思考：如何顯示高于自己部門平均工資的員工的信息

思路：

1.查出各個部門的平均工資和部門號

SELECTdeptno,,,,ds.mysalFROMempe,(SELECTdeptno,,,,ds.mysalFROMempe,(SELECTdeptno,AVG(sal)mysalFROMempGROUPbydeptno)asdsWHEREe.deptno=ds.deptnoANDe.sal>ds.mysal;

在ds前不能加as，否則會報錯

（給表取別名的時候，不能加as；但是給列取別名，是可以加as的）

分頁查詢

按雇員的id號升序取出

oracle的分頁一共有三種方式

1.根據(jù)rowid來分

select*fromt_xiaoxiwhererowidin(selectridfrom(selectrownumrn,ridfrom(selectrowidrid,cidfromt_xiaoxiorderbyciddesc)whererownum<10000)wherern>9980)orderbyciddesc;

執(zhí)行時間0.03秒

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

select*from(selectt.*,row_number()over(orderbyciddesc)rkfromt_xiaoxit)whererk<10000andrk>9980;

執(zhí)行時間1.01秒

3.按rownum來分

select*from(selectt.*,rownumrnfrom(select*fromt_xiaoxiorderbyciddesc)twhererownum<10000)wherern>9980;

執(zhí)行時間0.1秒

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

個人感覺1的效率最好，3次之，2最差。

//測試通過的分頁查詢okokok

select*from(selecta1.*,rownumrnfrom(selectename,jobfromemp)a1whererownum<=10)wherern>=5;

下面最主要介紹第三種：按rownum來分

1.rownum分頁

SELECT*FROMemp;

2.顯示rownum[oracle分配的]

SELECTe.*,ROWNUMrnFROM(SELECT*FROMemp)e;

rn相當于Oracle分配的行的ID號

3.挑選出6—10條記錄

先查出1-10條記錄

SELECTe.*,ROWNUMrnFROM(SELECT*FROMemp)eWHEREROWNUM<=10;

如果后面加上rownum>=6是不行的，

4.然后查出6-10條記錄

SELECT*FROM(SELECTe.*,ROWNUMrnFROM(SELECT*FROMemp)eWHEREROWNUM<=10)WHERErn>=6;

5.幾個查詢變化

a.指定查詢列，只需要修改最里層的子查詢

只查詢雇員的編號和工資

SELECT*FROM(SELECTe.*,ROWNUMrnFROM(SELECTename,salFROMemp)eWHEREROWNUM<=10)WHERErn>=6;

b.排序查詢，只需要修改最里層的子查詢

工資排序后查詢6-10條數(shù)據(jù)

SELECT*FROM(SELECTe.*,ROWNUMrnFROM(SELECTename,salFROMempORDERbysal)eWHEREROWNUM<=10)WHERErn>=6;

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

這個命令是一種快捷的建表方式

CREATETABLEmytable(id,name,sal,job,deptno)asSELECTempno,ename,sal,job,deptnoFROMemp;

創(chuàng)建好之后，descmytable；和select*frommytable;看看結(jié)果如何？

合并查詢

合并查詢

有時在實際應(yīng)用中，為了合并多個select語句的結(jié)果，可以使用集合操作符號union，unionall，intersect，minus

多用于數(shù)據(jù)量比較大的數(shù)據(jù)局庫，運行速度快。

1).union

該操作符用于取得兩個結(jié)果集的并集。當使用該操作符時，會自動去掉結(jié)果集中重復(fù)行。

SELECTename,sal,jobFROMempWHEREsal>2500

UNION

SELECTename,sal,jobFROMempWHEREjob='MANAGER';

2).unionall

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

SELECTename,sal,jobFROMempWHEREsal>2500

UNIONALL

SELECTename,sal,jobFROMempWHEREjob='MANAGER';

該操作符用于取得兩個結(jié)果集的并集。當使用該操作符時，會自動去掉結(jié)果集中重復(fù)行。

3).intersect

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

SELECTename,sal,jobFROMempWHEREsal>2500

INTERSECT

SELECTename,sal,jobFROMempWHEREjob='MANAGER';

4).minus

使用改操作符用于取得兩個結(jié)果集的差集，他只會顯示存在第一個集合中，而不存在第二個集合中的數(shù)據(jù)。

SELECTename,sal,jobFROMempWHEREsal>2500

MINUS

SELECTename,sal,jobFROMempWHEREjob='MANAGER';

（MINUS就是減法的意思）

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

1).通過oracle提供的向?qū)Чぞ摺！?/p>

databaseConfigurationAssistant

【數(shù)據(jù)庫配置助手】

2).我們可以用手工步驟直接創(chuàng)建。七：java操作oraclejava連接oracle

介紹：前面我們一直在plsql中操作oracle，那么如何在java程序中操作數(shù)據(jù)庫呢？下面我們舉例說明，寫一個java，分頁顯示emp表的用戶信息。Java代碼package

com.sp;

import

java.sql.Connection;

import

java.sql.DriverManager;

import

java.sql.ResultSet;

import

java.sql.Statement;

//演示

如何使用

jdbc_odbc橋連接方式

public

class

TestOracle

{

public

static

void

main(String[]

args)

{

try

{

//

1.加載驅(qū)動

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

//

2.得到連接

Connection

ct

=

DriverManager.getConnection(

"jdbc.odbc:testConnectOracle",

"scott",

"tiger");

//

從下面開始，和SQL

Server一模一樣

Statement

sm

=

ct.createStatement();

ResultSet

rs

=

sm.executeQuery("select

*

from

emp");

while

(rs.next())

{

//用戶名

System.out.println("用戶名：

"+rs.getString(2));

//默認是從1開始編號的

}

}

catch

(Exception

e)

{

e.printStackTrace();

}

}

}

在得到連接那里，要去配置數(shù)據(jù)源，點擊控制面板-->系統(tǒng)和安全-->管理工具-->數(shù)據(jù)源(ODBC),打開后點添加,如圖：可以看到，有個OracleinOraDb10g_home1的驅(qū)動，它是Oracle安裝完后自動加上去的。選中后，點完成，再填如下信息,如圖：

這樣配好后基本就可以了，但為了安全起見，建議大家測試一下，點擊TestConnection按鈕，測試通過后點ok,然后數(shù)據(jù)源就生成了，如圖：

然后把數(shù)據(jù)源名稱寫進jdbc.odbc:里。這里要注意：jdbcodbc能不能遠程連接呢？不能遠程連接，也就是你這樣寫的話就意味著java程序和oracle數(shù)據(jù)庫應(yīng)該是在同一臺機器上，因為這里沒有指定IP地址，肯定默認就是本地。如果要遠程連，就用jdbc,jdbc是可以遠程連的。,控制臺輸出可惜我沒運行成功，說java.sql.SQLException:Nosuitabledriverfoundforjdbc.odbc:testConnectOracle

atjava.sql.DriverManager.getConnection(UnknownSource)

atjava.sql.DriverManager.getConnection(UnknownSource)

atcom.sp.TestOracle.main(TestOracle.java:18)

不知道為什么。。。接下來講解用JDBC的方式連接OracleJava代碼package

com.sp;

import

java.sql.Connection;

import

java.sql.DriverManager;

import

java.sql.ResultSet;

import

java.sql.Statement;

//使用

jdbc連接oracle

public

class

TestOracle2

{

public

static

void

main(String[]

args)

{

try

{

//

1.加載驅(qū)動

Class.forName("oracle.jdbc.driver.OracleDriver");

//

2.得到連接

Connection

ct

=

DriverManager.getConnection

("jdbc:oracle:thin:@:1521:orcl",

"scott",

"tiger");

//

從下面開始，和SQL

Server一模一樣

Statement

sm

=

ct.createStatement();

ResultSet

rs

=

sm.executeQuery("select

*

from

emp");

while

(rs.next())

{

//用戶名

System.out.println("用戶名：

"+rs.getString(2));

//默認是從1開始編號的

}

}

catch

(Exception

e)

{

e.printStackTrace();

}

}

}

記得要把驅(qū)動包引入，classes12.jar

運行，。。。。再次可惜，我還是沒運行成功，錯誤是：

java.sql.SQLException:Io異常:TheNetworkAdaptercouldnotestablishthe

connection

atoracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)

atoracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)

atoracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:334)

atoracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:418)

atoracle.jdbc.driver.OracleDriver.getConnectionInstance

(OracleDriver.java:521)

atoracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:325)

atjava.sql.DriverManager.getConnection(UnknownSource)

atjava.sql.DriverManager.getConnection(UnknownSource)

atcom.sp.TestOracle2.main(TestOracle2.java:18)

我也不知道為什么。。。幽怨了。。

接下來建個webproject，來測試oracle的分頁，挺麻煩，不記錄了。。

在oracle中操作數(shù)據(jù)-使用特定格式插入日期值

使用to_date函數(shù)

請大家思考：如何插入列帶有日期的表，并按照年-月-日的格式插入？

insertintoempvalues

(9998,'xiaohong',

'MANAGER',7782,to_date('1988-12-12','yyyy-mm-dd'),,,

10);

注意：

insertintoempvalues

(9998,'xiaohong','MANAGER',

7782,'12-12月-1988',,,10);

這句語句是可以成功運行的

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

介紹

當使用valus子句時，一次只能插入一行數(shù)據(jù)，當使用子查詢插入數(shù)據(jù)時，一條inset語句可以插入大量的數(shù)據(jù)。當處理行遷移或者裝載外部表的數(shù)據(jù)到數(shù)據(jù)庫時，可以使用子查詢來插入數(shù)據(jù)。

把emp表中10號部門的數(shù)據(jù)導(dǎo)入到新表中

createtablekkk(myIdnumber(4),myNamevarchar2(50),myDeptnumber(5));

insertintokkk(myId,myName,myDept)

selectempno,ename,deptnofromempwheredeptno=10;

介紹

使用update語句更新數(shù)據(jù)時，既可以使用表達式或者數(shù)值直接修改數(shù)據(jù)，也可以使用子查詢修改數(shù)據(jù)。

問題：希望員工SCOTT的崗位、工資、補助與SMITH員工一樣。

updateempset(job,sal,comm)=(selectjob,sal,commfromempwhereename='SMITH')whereename='SCOTT';八：oracle中事務(wù)處理什么是事務(wù)

事務(wù)用于保證數(shù)據(jù)的一致性，它由一組相關(guān)的dml語句組成，該組的dml(數(shù)據(jù)操作語言，增刪改，沒有查詢)語句要么全部成功，要么全部失敗。

如：網(wǎng)上轉(zhuǎn)賬就是典型的要用事務(wù)來處理，用于保證數(shù)據(jù)的一致性。

dml數(shù)據(jù)操作語言

銀行轉(zhuǎn)賬、QQ申請、車票購買

事務(wù)和鎖

當執(zhí)行事務(wù)操作時（dml語句），oracle會在被作用的表上加鎖，防止其它用戶修改表的結(jié)構(gòu)。這里對我們的用戶來來講是非常重要的。

其它進程排序，知道1號進程完成，鎖打開，2號進程進入。依次進行，如果有進程級別較高的，可以插隊。

提交事務(wù)

當執(zhí)行用commit語句可以提交事務(wù)。當執(zhí)行了commit語句之后，會確認事務(wù)的變化、結(jié)束事務(wù)。刪除保存點、釋放鎖，當使用commit語句結(jié)束事務(wù)之后，其它會話將可以查看到事務(wù)變化后的新數(shù)據(jù)。

保存點就是為回退做的。保存點的個數(shù)沒有限制

回退事務(wù)

在介紹回退事務(wù)前，我們先介紹一下保存點（savepoint）的概念和作用。保存點是事務(wù)中的一點。用于取消部分事務(wù)，當結(jié)束事務(wù)時，會自動的刪除該事務(wù)所定義的所有保存點。當執(zhí)行rollback時，通過指定保存點可以回退到指定的點，這里我們作圖說明。

事務(wù)的幾個重要操作

1.設(shè)置保存點savepointa

2.取消部分事務(wù)rollbacktoa

3.取消全部事務(wù)rollback

注意：這個回退事務(wù)，必須是沒有commit前使用的；如果事務(wù)提交了，那么無論你剛才做了多少個保存點，都統(tǒng)統(tǒng)沒有。

如果沒有手動執(zhí)行commit,而是exit了，那么會自動提交java程序中如何使用事務(wù)在java操作數(shù)據(jù)庫時，為了保證數(shù)據(jù)的一致性，比如賬戶操作（1）從一個賬戶中減掉10$（2）在另一個賬戶上加入10$,我們看看如何使用事務(wù)？Java代碼package

com.sp;

import

java.sql.Connection;

import

java.sql.DriverManager;

import

java.sql.ResultSet;

import

java.sql.Statement;

public

class

TestTrans

{

public

static

void

main(String[]

args)

{

try

{

//

1.加載驅(qū)動

Class.forName("oracle.jdbc.driver.OracleDriver");

//

2.得到連接

Connection

ct

=

DriverManager.getConnection(

"jdbc:oracle:thin:@:1521:orcl",

"scott",

"tiger");

Statement

sm

=

ct.createStatement();

//

從scott的sal中減去100

sm.executeUpdate("update

emp

set

sal=sal-100

where

ename='SCOTT'");

int

i

=

7

/

0;

//

給smith的sal加上100

sm.executeUpdate("update

emp

set

sal=sal+100

where

ename='SMITH'");

//

關(guān)閉打開的資源

sm.close();

ct.close();

}

catch

(Exception

e)

{

e.printStackTrace();

}

}

}

運行，會出現(xiàn)異常，查看數(shù)據(jù)庫，SCOTT的sal減了100，但是SMITH的sal卻不變，很可怕。。。

我們怎樣才能保證，這兩個操作要么同時成功，要么同時失敗呢？Java代碼package

com.sp;

import

java.sql.Connection;

import

java.sql.DriverManager;

import

java.sql.SQLException;

import

java.sql.Statement;

public

class

TestTrans

{

public

static

void

main(String[]

args)

{

Connection

ct

=

null;

try

{

//

1.加載驅(qū)動

Class.forName("oracle.jdbc.driver.OracleDriver");

//

2.得到連接

ct

=

DriverManager.getConnection(

"jdbc:oracle:thin:@:1521:orcl",

"scott",

"tiger");

//

加入事務(wù)處理

ct.setAutoCommit(false);//

設(shè)置不能默認提交

Statement

sm

=

ct.createStatement();

//

從scott的sal中減去100

sm.executeUpdate("update

emp

set

sal=sal-100

where

ename='SCOTT'");

int

i

=

7

/

0;

//

給smith的sal加上100

sm.executeUpdate("update

emp

set

sal=sal+100

where

ename='SMITH'");

//

提交事務(wù)

mit();

//

關(guān)閉打開的資源

sm.close();

ct.close();

}

catch

(Exception

e)

{

//

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

try

{

ct.rollback();

}

catch

(SQLException

e1)

{

e1.printStackTrace();

}

e.printStackTrace();

}

}

}

再運行一下，會出現(xiàn)異常，查看數(shù)據(jù)庫，數(shù)據(jù)沒變化。。

只讀事務(wù)

只讀事務(wù)是指只允許執(zhí)行查詢的操作，而不允許執(zhí)行任何其它dml操作的事務(wù)，使用只讀事務(wù)可以確保用戶只能取得某時間點的數(shù)據(jù)。假定機票代售點每天18點開始統(tǒng)計今天的銷售情況，這時可以使用只讀事務(wù)。在設(shè)置了只讀事務(wù)后，盡管其它會話可能會提交新的事務(wù)，但是只讀事務(wù)將不會取得最新數(shù)據(jù)的變化，從而可以保證取得特定時間點的數(shù)據(jù)信息。

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

settransactionreadonly;九：oracle的函數(shù)sql函數(shù)的使用

字符函數(shù)

介紹

字符函數(shù)是oracle中最常用的函數(shù)，我們來看看有哪些字符函數(shù)：

lower(char)：將字符串轉(zhuǎn)化為小寫的格式。

upper(char)：將字符串轉(zhuǎn)化為大寫的格式。

length(char)：返回字符串的長度。

substr(char,m,n)：取字符串的子串n代表取n個的意思，不是代表取到第n個replace(char1,search_string,replace_string)

instr(char1,char2,[,n[,m]])取子串在字符串的位置

問題：將所有員工的名字按小寫的方式顯示

SQL>selectlower(ename)fromemp;

問題：將所有員工的名字按大寫的方式顯示。

SQL>selectupper(ename)fromemp;

問題：顯示正好為5個字符的員工的姓名。

SQL>select*fromempwherelength(ename)=5;

問題：顯示所有員工姓名的前三個字符。

SQL>selectsubstr(ename,1,3)fromemp;

問題：以首字母大寫,后面小寫的方式顯示所有員工的姓名。

SQL>selectupper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1))

fromemp;

問題：以首字母小寫,后面大寫的方式顯示所有員工的姓名。

SQL>selectlower(substr(ename,1,1))||upper(substr(ename,2,length(ename)-1))

fromemp;

問題：顯示所有員工的姓名，用“我是老虎”替換所有“A”

SQL>selectreplace(ename,'A','我是老虎')fromemp;

數(shù)學(xué)函數(shù)介紹

數(shù)學(xué)函數(shù)的輸入?yún)?shù)和返回值的數(shù)據(jù)類型都是數(shù)字類型的。數(shù)學(xué)函數(shù)包括cos，cosh，exp，ln,log，sin，sinh，sqrt，tan，tanh，acos，asin，atan，round，我們講最常用的：

round(n,[m])該函數(shù)用于執(zhí)行四舍五入，如果省掉m，則四舍五入到整數(shù)，如果m是正數(shù)，則四舍五入到小數(shù)點的m位后。如果m是負數(shù)，則四舍五入到小數(shù)點的m位前。

trunc(n,[m])該函數(shù)用于截取數(shù)字。如果省掉m，就截去小數(shù)部分，如果m是正數(shù)就截取到小數(shù)點的m位后，如果m是負數(shù)，則截取到小數(shù)點的前m位。

mod(m,n)

floor(n)返回小于或是等于n的最大整數(shù)

ceil(n)返回大于或是等于n的最小整數(shù)

對數(shù)字的處理，在財務(wù)系統(tǒng)或銀行系統(tǒng)中用的最多，不同的處理方法，對財務(wù)報表有不同的結(jié)果。

問題：顯示在一個月為30天的情況下，所有員工的日薪金，忽略余數(shù)。

SQL>selecttrunc(sal/30),enamefromemp;

or

SQL>selectfloor(sal/30),enamefromemp;

在做oracle測試的時候，可以使用dual表

selectmod(10,2)fromdual;結(jié)果是0

selectmod(10,3)fromdual;結(jié)果是1

其它的數(shù)學(xué)函數(shù)，有興趣的同學(xué)可以自己去看看：

abs(n)：返回數(shù)字n的絕對值

selectabs(-13)fromdual;

acos(n)：返回數(shù)字的反余弦值

asin(n)：返回數(shù)字的反正弦值

atan(n)：返回數(shù)字的反正切值

cos(n)：

exp(n)：返回e的n次冪

log(m,n)：返回對數(shù)值

power(m,n)：

返回m的n次冪

日期函數(shù)

介紹

日期函數(shù)用于處理date類型的數(shù)據(jù)。

默認情況下日期格式是dd-mon-yy即12-7月-78

(1)sysdate:該函數(shù)返回系統(tǒng)時間

(2)add_months(d,n)

(3)last_day(d)：返回指定日期所在月份的最后一天

問題：查找已經(jīng)入職8個月多的員工

SQL>select*fromempwheresysdate>=add_months(hiredate,8);

問題：顯示滿10年服務(wù)年限的員工的姓名和受雇日期。

SQL>selectename,hiredatefromempwheresysdate>=add_months(hiredate,12*10);

問題：對于每個員工，顯示其加入公司的天數(shù)。

SQL>selectfloor(sysdate-hiredate)"入職天數(shù)",enamefromemp;

or

SQL>selecttrunc(sysdate-hiredate)"入職天數(shù)",enamefromemp;

問題：找出各月倒數(shù)第3天受雇的所有員工。

SQL>selecthiredate,enamefromempwherelast_day(hiredate)-2=hiredate;

轉(zhuǎn)換函數(shù)

介紹√

轉(zhuǎn)換函數(shù)用于將數(shù)據(jù)類型從一種轉(zhuǎn)為另外一種。在某些情況下，oracleserver允許值的數(shù)據(jù)類型和實際的不一樣，這時oracleserver會隱含的轉(zhuǎn)化數(shù)據(jù)類型

比如：

createtablet1(idint);

insertintot1values('10');-->這樣oracle會自動的將'10'-->10

createtablet2(idvarchar2(10));

insertintot2values(1);-->這樣oracle就會自動的將1-->'1'；

我們要說的是盡管oracle可以進行隱含的數(shù)據(jù)類型的轉(zhuǎn)換，但是它并不適應(yīng)所有的情況，為了提高程序的可靠性，我們應(yīng)該使用轉(zhuǎn)換函數(shù)進行轉(zhuǎn)換。

to_char

你可以使用selectename,hiredate,salfromempwheredeptno=10;顯示信息，可是，在某些情況下，這個并不能滿足你的需求。

問題：日期是否可以顯示時/分/秒

SQL>selectename,to_char(hiredate,'yyyy-mm-ddhh24:mi:ss')fromemp;

問題：薪水是否可以顯示指定的貨幣符號

SQL>

yy：兩位數(shù)字的年份2004-->04

yyyy：四位數(shù)字的年份

2004年

mm：兩位數(shù)字的月份8月-->08

dd：兩位數(shù)字的天30號-->30

hh24：8點-->20

hh12：8點-->08

mi、ss-->顯示分鐘\秒

9：顯示數(shù)字，并忽略前面0

0：顯示數(shù)字，如位數(shù)不足，則用0補齊

.：在指定位置顯示小數(shù)點

,：在指定位置顯示逗號

$：在數(shù)字前加美元

L：在數(shù)字前面加本地貨幣符號

C：在數(shù)字前面加國際貨幣符號

G：在指定位置顯示組分隔符、

D：在指定位置顯示小數(shù)點符號(.)

問題：顯示薪水的時候，把本地貨幣單位加在前面

SQL>selectename,to_char(hiredate,'yyyy-mm-ddhh24:mi:ss'),to_char(sal,'')fromemp;

問題：顯示1980年入職的所有員工

SQL>select*fromempwhereto_char(hiredate,'yyyy')=1980;

問題：顯示所有12月份入職的員工

SQL>select*fromempwhereto_char(hiredate,'mm')=12;

to_date

函數(shù)to_date用于將字符串轉(zhuǎn)換成date類型的數(shù)據(jù)。

問題：能否按照中國人習慣的方式年—月—日添加日期。

系統(tǒng)函數(shù)

sys_context

1）terminal：當前會話客戶所對應(yīng)的終端的標示符

2）lanuage:語言

3）db_name：當前數(shù)據(jù)庫名稱

4）nls_date_format：當前會話客戶所對應(yīng)的日期格式

5）session_user：當前會話客戶所對應(yīng)的數(shù)據(jù)庫用戶名

6）current_schema：當前會話客戶所對應(yīng)的默認方案名

7）host：返回數(shù)據(jù)庫所在主機的名稱

通過該函數(shù)，可以查詢一些重要信息，比如你正在使用哪個數(shù)據(jù)庫？

selectsys_context('USERENV','db_name')fromdual;

注意：USERENV是固定的，不能改的，db_name可以換成其它,比如selectsys_context('USERENV','lanuage')fromdual;又比如selectsys_context('USERENV','current_schema')fromdual;十：數(shù)據(jù)庫管理，表的邏輯備份與恢復(fù)數(shù)據(jù)庫管理員

介紹

每個oracle數(shù)據(jù)庫應(yīng)該至少有一個數(shù)據(jù)庫管理員（dba），對于一個小的數(shù)據(jù)庫，一個dba就夠了，但是對于一個大的數(shù)據(jù)庫可能需要多個dba分擔不同的管理職責。那么一個數(shù)據(jù)庫管理員的主要工作是什么呢：

職責

1.安裝和升級oracle數(shù)據(jù)庫

2.建庫，表空間，表，視圖，索引…

3.制定并實施備份和恢復(fù)計劃

4.數(shù)據(jù)庫權(quán)限管理，調(diào)優(yōu)，故障排除

5.對于高級dba，要求能參與項目開發(fā)，會編寫sql語句、存儲過程、觸發(fā)器、規(guī)則、約束、包

管理數(shù)據(jù)庫的用戶主要是sys和system

（sys好像是董事長，system好像是總經(jīng)理，董事長比總經(jīng)理大，但是通常是總經(jīng)理干事）

在前面我們已經(jīng)提到這兩個用戶，區(qū)別主要是：

1.最重要的區(qū)別，存儲的數(shù)據(jù)的重要性不同

sys：所有oracle的數(shù)據(jù)字典的基表和視圖都存放在sys用戶中，這些基表和視圖對于oracle的運行是至關(guān)重要的，由數(shù)據(jù)庫自己維護，任何用戶都不能手動更改。sys用戶擁有dba，sysdba，sysoper角色或權(quán)限，是oracle權(quán)限最高的用戶。

system：用于存放次一級的內(nèi)部數(shù)據(jù)，如oracle的一些特性或工具的管理信息。system用戶擁有dba，sysdba角色或系統(tǒng)權(quán)限。

看圖：

sysdba可以建數(shù)據(jù)庫，sysoper不能建數(shù)據(jù)庫

2