PAGE韓順平—玩轉oracle視頻教程筆記一：Oracle認證，與其它數據庫比較，安裝

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

連接命令1.conn[ect]

用法：conn用戶名/密碼@網絡服務名[assysdba/sysoper]當用特權用戶身份連接時，必須帶上assysdba或是assysoper

2.disc[onnect]

說明:該命令用來斷開與當前數據庫的連接

3.psssw[ord]

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

4.showuser

說明:顯示當前用戶名

5.exit

說明:該命令會斷開與數據庫的連接，同時會退出sql*plus

文件操作命令

1.start和@

說明:運行sql腳本

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

2.edit

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

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

3.spool

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

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

交互式命令

1.&說明：可以替代變量，而該變量在執行時，需要用戶輸入。

select*fromempwherejob='&job'；

2.edit

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

案例：SQL>editd:\a.sql

3.spool

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

spoold:\b.sql并輸入spooloff

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

創建用戶

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

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

給用戶修改密碼

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

password用戶名

如果給別人修改密碼則需要具有dba的權限，或是擁有alteruser的系統權限

SQL>alteruser用戶名identifiedby新密碼

刪除用戶

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

比如dropuser用戶名【cascade】

在刪除用戶時，注意：

如果要刪除的用戶，已經創建了表，那么就需要在刪除的時候帶一個參數cascade;

用戶管理的綜合案例

概述：創建的新用戶是沒有任何權限的，甚至連登陸的數據庫的權限都沒有，需要為其指定相應的權限。給一個用戶賦權限使用命令grant，回收權限使用命令revoke。

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

SQL>connxiaoming/m12;

ERROR:

ORA-01045:userXIAOMINGlacksCREATESESSIONprivilege;logondenied

警告:您不再連接到ORACLE。

SQL>showuser;

USER為""

SQL>connsystem/p;

已連接。

SQL>grantconnecttoxiaoming;

授權成功。

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

已連接。

SQL>

注意：grantconnecttoxiaoming;在這里，準確的講，connect不是權限，而是角色。。看圖：

現在說下對象權限，現在要做這么件事情：

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

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

grantselectonemptoxiaoming

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

grantupdateonemptoxiaoming

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

grantallonemptoxiaoming

*scott希望收回xiaoming對emp表的查詢權限

revokeselectonempfromxiaoming

//對權限的維護。

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

--如果是對象權限，就加入withgrantoption

grantselectonemptoxiaomingwithgrantoption

我的操作過程：

SQL>connscott/tiger;

已連接。

SQL>grantselectonscott.emptoxiaomingwithgrantoption;

授權成功。

SQL>connsystem/p;

已連接。

SQL>createuserxiaohongidentifiedbym123;

用戶已創建。

SQL>grantconnecttoxiaohong;

授權成功。

SQL>connxiaoming/m12;

已連接。

SQL>grantselectonscott.emptoxiaohong;

授權成功。--如果是系統權限。

system給xiaoming權限時：

grantconnecttoxiaomingwithadminoption

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

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

SQL>connscott/tiger;

已連接。

SQL>revokeselectonempfromxiaoming;

撤銷成功。

SQL>connxiaohong/m123;

已連接。

SQL>select*fromscott.emp;

select*fromscott.emp

第1行出現錯誤:

ORA-00942:表或視圖不存在

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

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

1.賬戶鎖定

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

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

創建profile文件

SQL>createprofilelock_accountlimitfailed_login_attempts3password_lock_time2;

SQL>alteruserscottprofilelock_account;

2.給賬戶(用戶)解鎖

SQL>alteruserteaaccountunlock;

3.終止口令

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

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

SQL>createprofilemyprofilelimitpassword_life_time10password_grace_time2;

SQL>alteruserteaprofilemyprofile;口令歷史

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

例子：

1）建立profile

SQL>createprofilepassword_historylimitpassword_life_time10password_grace_time2password_reuse_time10

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

2）分配給某個用戶

刪除profile

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

SQL>dropprofilepassword_history【casade】

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

字符類

char

定長最大2000個字符。

例子：char(10)

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

varchar2(20)

變長

最大4000個字符。

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

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

char查詢的速度極快浪費空間，查詢比較多的數據用。

varchar節省空間

數字型

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

可以表示整數，也可以表示小數

number(5,2)

表示一位小數有5位有效數，2位小數

范圍：-999.99到999.99

number(5)

表示一個5位整數

范圍99999到-99999

日期類型

date包含年月日和時分秒

oracle默認格式

1-1月-1999

timestamp這是oracle9i對date數據類型的擴展。可以精確到毫秒。

圖片

blob二進制數據可以存放圖片/聲音

4G

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

怎樣創建表

建表

--學生表

createtablestudent(

表名

xh

number(4),

--學號

xm

varchar2(20),

--姓名

sex

char(2),

--性別

birthdaydate,

--出生日期

sal

number(7,2)

--獎學金

);--班級表

CREATETABLEclass(

classIdNUMBER(2),

cNameVARCHAR2(40));修改表

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

修改一個字段的長度

SQL>ALTERTABLEstudentMODIFY(xmVARCHAR2(30));

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

SQL>ALTERTABLEstudentmodify(xmCHAR(30));

刪除一個字段

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

SQL>ALTERTABLEstudentDROPCOLUMNsal;

修改表的名字

很少有這種需求

SQL>RENAMEstudentTOstu;

刪除表

SQL>DROPTABLEstudent;

添加數據

所有字段都插入數據

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

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

yy

2位的年

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

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

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,則應該這樣寫：

select*fromstudentwherebirthdayisnotnull;

修改數據

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

修改多個字段

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

修改含有null值的數據

不要用=null而是用isnull；

SELECT*FROMstudentWHEREbirthdayISnull;

刪除數據

DELETEFROMstudent;

刪除所有記錄，表結構還在，寫日志，可以恢復的，速度慢。

Delete的數據可以恢復。

savepointa;--創建保存點

DELETEFROMstudent;

rollbacktoa;

--恢復到保存點

一個有經驗的DBA，在確保完成無誤的情況下要定期創建還原點。

DROPTABLEstudent;--刪除表的結構和數據；

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

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

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

emp雇員表

clerk

普員工

salesman銷售

manager

經理

analyst分析師

president

總裁

mgr

上級的編號

hiredate入職時間

sal月工資

comm獎金

deptno部門

dept部門表

deptno部門編號

accounting財務部

research

研發部

operations業務部

loc部門所在地點

salgrade

工資級別

grade

級別

losal

最低工資

hisal

最高工資

簡單的查詢語句

查看表結構DESCemp;

查詢所有列

SELECT*FROMdept;

切忌動不動就用select*

SETTIMINGON;打開顯示操作時間的開關，在下面顯示查詢時間。

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

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

--從自己復制，加大數據量大概幾萬行就可以了

可以用來測試sql語句執行效率

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

SELECTCOUNT(*)FROMusers;統計行數

查詢指定列

SELECTename,sal,job,deptnoFROMemp;

如何取消重復行DISTINCT

SELECTDISTINCTdeptno,jobFROMemp;

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

SELECTdeptno,job,salFROMempWHEREename='SMITH';

注意：oracle對內容的大小寫是區分的，所以ename='SMITH'和ename='smith'是不同的

使用算術表達式

nvl

null

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

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

使用列的別名

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

如何處理null值

使用nvl函數來處理

如何連接字符串（||）

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;

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

分頁查詢

等學了子查詢再說吧。。。。。。。。

Clear清屏命令

oracle表復雜查詢

說明

在實際應用中經常需要執行復雜的數據統計，經常需要顯示多張表的數據，現在我們給大家介紹較為復雜的select語句

數據分組——max，min，avg，sum，count

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

SELECTMAX(sal),min(sal)FROMempe;

最高工資那個人是誰？

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

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

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

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

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

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

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

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

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

groupby和having子句

groupby用于對查詢的結果分組統計，

having子句用于限制分組顯示結果。

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

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

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

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

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

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

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

對數據分組的總結

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

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

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

如SELECTdeptno,AVG(sal),MAX(sal)FROMempGROUPbydeptnoHAVINGAVG(sal)<2000;

這里deptno就一定要出現在groupby中

多表查詢

說明

多表查詢是指基于兩個和兩個以上的表或是視圖的查詢。在實際應用中，查詢單個表可能不能滿足你的需求，（如顯示sales部門位置和其員工的姓名），這種情況下需要使用到（dept表和emp表）

問題：顯示雇員名，雇員工資及所在部門的名字【笛卡爾集】？

規定：多表查詢的條件是至少不能少于表的個數-1才能排除笛卡爾集

（如果有N張表聯合查詢，必須得有N-1個條件，才能避免笛卡爾集合）

SELECTe.ename,e.sal,d.dnameFROMempe,deptdWHEREe.deptno=d.deptno;

問題：顯示部門號為10的部門名、員工名和工資？

SELECTd.dname,e.ename,e.salFROMempe,deptdWHEREe.deptno=d.deptnoande.deptno=10;

問題：顯示各個員工的姓名，工資及工資的級別？

先看salgrade的表結構和記錄

SQL>select*fromsalgrade;

GRADE

LOSAL

HISAL

1

700

1200

2

1201

1400

3

1401

2000

4

2001

3000

5

3001

9999

SELECTe.ename,e.sal,s.gradeFROMempe,salgradesWHEREe.salBETWEENs.losalANDs.hisal;

擴展要求：

問題：顯示雇員名，雇員工資及所在部門的名字，并按部門排序？

SELECTe.ename,e.sal,d.dnameFROMempe,deptdWHEREe.deptno=d.deptnoORDERbye.deptno;

（注意：如果用groupby，一定要把e.deptno放到查詢列里面）

自連接

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

問題：顯示某個員工的上級領導的姓名？

比如顯示員工‘FORD’的上級

SELECTworker.ename,boss.enameFROMempworker,empbossWHEREworker.mgr=boss.empnoANDworker.ename='FORD';

子查詢

什么是子查詢

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

單行子查詢

單行子查詢是指只返回一行數據的子查詢語句

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

思路：

1查詢出SMITH的部門號

selectdeptnofromempWHEREename='SMITH';

2顯示

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

數據庫在執行sql是從左到右掃描的，如果有括號的話，括號里面的先被優先執行。

多行子查詢

多行子查詢指返回多行數據的子查詢

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

SELECTDISTINCTjobFROMempWHEREdeptno=10;

SELECT*FROMempWHEREjobIN(SELECTDISTINCTjobFROMempWHEREdeptno=10);

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

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

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

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

擴展要求：

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

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

執行效率上，函數高得多

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

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

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

擴展要求：

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

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

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

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

SELECTdeptno,jobFROMempWHEREename='SMITH';

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

在from子句中使用子查詢

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

思路：

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

SELECTdeptno,AVG(sal)mysalFROMempGROUPbydeptno;

2.把上面的查詢結果看做是一張子表

SELECTe.ename,e.deptno,e.sal,ds.mysalFROMempe,(SELECTdeptno,AVG(sal)mysalFROMempGROUPbydeptno)dsWHEREe.deptno=ds.deptnoANDe.sal>ds.mysal;

如何衡量一個程序員的水平？

網絡處理能力，數據庫，程序代碼的優化程序的效率要很高

小總結：

在這里需要說明的當在from子句中使用子查詢時，該子查詢會被作為一個視圖來對待，因此叫做內嵌視圖，當在from子句中使用子查詢時，必須給子查詢指定別名。

注意：別名不能用as，如：SELECTe.ename,e.deptno,e.sal,ds.mysalFROMempe,(SELECTdeptno,AVG(sal)mysalFROMempGROUPbydeptno)asdsWHEREe.deptno=ds.deptnoANDe.sal>ds.mysal;

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

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

分頁查詢

按雇員的id號升序取出

oracle的分頁一共有三種方式

1.根據rowid來分

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

執行時間0.03秒

2.按分析函數來分

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

執行時間1.01秒

3.按rownum來分

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

執行時間0.1秒

其中t_xiaoxi為表名稱，cid為表的關鍵字段，取按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條數據

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

用查詢結果創建新表

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

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

創建好之后，descmytable；和select*frommytable;看看結果如何？

合并查詢

合并查詢

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

多用于數據量比較大的數據局庫，運行速度快。

1).union

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

SELECTename,sal,jobFROMempWHEREsal>2500

UNION

SELECTename,sal,jobFROMempWHEREjob='MANAGER';

2).unionall

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

SELECTename,sal,jobFROMempWHEREsal>2500

UNIONALL

SELECTename,sal,jobFROMempWHEREjob='MANAGER';

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

3).intersect

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

SELECTename,sal,jobFROMempWHEREsal>2500

INTERSECT

SELECTename,sal,jobFROMempWHEREjob='MANAGER';

4).minus

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

SELECTename,sal,jobFROMempWHEREsal>2500

MINUS

SELECTename,sal,jobFROMempWHEREjob='MANAGER';

（MINUS就是減法的意思）

創建數據庫有兩種方法：

1).通過oracle提供的向導工具。√

databaseConfigurationAssistant

【數據庫配置助手】

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

介紹：前面我們一直在plsql中操作oracle，那么如何在java程序中操作數據庫呢？下面我們舉例說明，寫一個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.加載驅動

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();

}

}

}

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

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

然后把數據源名稱寫進jdbc.odbc:里。這里要注意：jdbcodbc能不能遠程連接呢？不能遠程連接，也就是你這樣寫的話就意味著java程序和oracle數據庫應該是在同一臺機器上，因為這里沒有指定IP地址，肯定默認就是本地。如果要遠程連，就用jdbc,jdbc是可以遠程連的。運行TestOracle.java,控制臺輸出可惜我沒運行成功，說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.加載驅動

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();

}

}

}

記得要把驅動包引入，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中操作數據-使用特定格式插入日期值

使用to_date函數

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

insertintoempvalues

(9998,'xiaohong',

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

78.9,

55.33,

10);

注意：

insertintoempvalues

(9998,'xiaohong','MANAGER',

7782,'12-12月-1988',78.9,

55.33,10);

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

使用子查詢插入數據

介紹

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

把emp表中10號部門的數據導入到新表中

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

insertintokkk(myId,myName,myDept)

selectempno,ename,deptnofromempwheredeptno=10;

介紹

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

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

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

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

如：網上轉賬就是典型的要用事務來處理，用于保證數據的一致性。

dml數據操作語言

銀行轉賬、QQ申請、車票購買

事務和鎖

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

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

提交事務

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

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

回退事務

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

事務的幾個重要操作

1.設置保存點savepointa

2.取消部分事務rollbacktoa

3.取消全部事務rollback

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

如果沒有手動執行commit,而是exit了，那么會自動提交java程序中如何使用事務在java操作數據庫時，為了保證數據的一致性，比如賬戶操作（1）從一個賬戶中減掉10$（2）在另一個賬戶上加入10$,我們看看如何使用事務？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.加載驅動

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'");

//

關閉打開的資源

sm.close();

ct.close();

}

catch

(Exception

e)

{

e.printStackTrace();

}

}

}

運行，會出現異常，查看數據庫，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.加載驅動

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

//

2.得到連接

ct

=

DriverManager.getConnection(

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

"scott",

"tiger");

//

加入事務處理

ct.setAutoCommit(false);//

設置不能默認提交

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'");

//

提交事務

mit();

//

關閉打開的資源

sm.close();

ct.close();

}

catch

(Exception

e)

{

//

如果發生異常，就回滾

try

{

ct.rollback();

}

catch

(SQLException

e1)

{

e1.printStackTrace();

}

e.printStackTrace();

}

}

}

再運行一下，會出現異常，查看數據庫，數據沒變化。。

只讀事務

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

設置只讀事務

settransactionreadonly;九：oracle的函數sql函數的使用

字符函數

介紹

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

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

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

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;

數學函數介紹

數學函數的輸入參數和返回值的數據類型都是數字類型的。數學函數包括cos，cosh，exp，ln,log，sin，sinh，sqrt，tan，tanh，acos，asin，atan，round，我們講最常用的：

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

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

mod(m,n)

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

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

對數字的處理，在財務系統或銀行系統中用的最多，不同的處理方法，對財務報表有不同的結果。

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

SQL>selecttrunc(sal/30),enamefromemp;

or

SQL>selectfloor(sal/30),enamefromemp;

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

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

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

其它的數學函數，有興趣的同學可以自己去看看：

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

selectabs(-13)fromdual;

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

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

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

cos(n)：

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

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

power(m,n)：

返回m的n次冪

日期函數

介紹

日期函數用于處理date類型的數據。

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

(1)sysdate:該函數返回系統時間

(2)add_months(d,n)

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

問題：查找已經入職8個月多的員工

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

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

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

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

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

or

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

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

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

轉換函數

介紹√

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

比如：

createtablet1(idint);

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

createtablet2(idvarchar2(10));

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

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

to_char

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

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

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

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

SQL>

yy：兩位數字的年份2004-->04

yyyy：四位數字的年份

2004年

mm：兩位數字的月份8月-->08

dd：兩位數字的天30號-->30

hh24：8點-