I.oracleiteast

本文檔對應程序在myeclipse的jdbc/src/下

分頁查詢中的rownum可以方便刪除重復記錄等各種方便查詢

Oracle安裝自動生成sys用戶和system用戶

sys超級用戶具有最高權限具有sysDBA角色，有createdatabase權限

該用尸默認密碼是change_in_install

system管理操作員權限也比較大，具有sysopcr角色，沒有createdatabase權限。

該用戶默認密碼是manager

這是通過sqlplus客戶端連接數據庫時有多個實例采用下面DOS命令：sqlplusscott/tiger@zhuiin

見2.13oracle創(chuàng)建數據庫實例

啟動sqlplus,然后登陸數據庫出現錯誤:TNS:協(xié)議適配器錯誤

原因有3個：

L監(jiān)聽服務沒有啟動：services.msc或開始一＞程序一＞管理工具一＞服務，打開服務面板：

啟動oraclehome92TNSlistener服務

2.databaseinstance沒有啟動：services.msc或開始一＞程序一＞管理工具一＞服務啟動oralceserviceXXX,XXX

就是你databasesID如zhulin

3.注冊表問題：

regedit.msc

進入HKEY_LOC/\L_MACHINE\SOFTWARE\ORACLE\KEY_OraI)bllg_homel

下的ORACLE_SID值修改為zhulin

你的全局數據庫名字你的數據庫SID即可。

ORACLE用SYS和SYSTEM默認密碼登錄提示ORA-01017:invalidusername/password;logonddenied該

怎么解決？

解決辦法：

有可能是你在建數據庫的時候，

Connectedto：

修改了默認的密碼OracleDatabase11gEnterpriseEditionReleas

而自己又忘記WiththePartitioning,OLAP,DataMiningand

你可再重新修改過來SQL>alteruserscottidentifiedbytiger

sqlplus/assysdba2；

alterusersystemidentifiedbymanager;Useraltered.

alterusersysidentifiedbymanager;

SQL>connscott/tiger

或者改成其他的你自己容易記住的Connected.

默認scott用戶密碼是tigerSQL>showuser

USERie"SCOTT”

,3QL>

l.oralce解鎖步驟

先使用system登錄

然后輸入alteruserscottaccountunlock；〃解鎖scott賬號

SQL語句必須帶分號!!!!!!!!!!!!!最好都分號結束

-v-IW--

i'D：\xiaoqiang\oracle\product\11.2.0\dbhome_l\BIN\sqlplus.exe

SQL*Plus：Release11.2.0.1.0Productionon星期三1月3009：30：522013

Copyright<c>19822010，Orac1c.Allrightsreserved.

請輸入用戶名：system

輸人口令：

連接到:

OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-64bitProduction

WiththePartitioning,OLfiP,DataMiningandRealApplicationTestingoption:

SQL>alteruseraccountunlock；

alteruseraccountunlock

第1行出現曾誤Z4二f

ORA-00922：選項缺失或尢效

SQL>alteruserscottaccountunlock；

用戶已更改。

SQL》.

Z.oralce開發(fā)工具

sqlpulsw和sqlus工具

在開始f程序foracleoradb_homelOgfapplicationdevelopment—sqlplus

或在運行欄輸入sqlplus

pl/sqldeveloper這款軟件用的很多第三方軟件需要單獨安裝

企業(yè)管理器(web)首先保證相關服務啟動即oracleDBconsole+實例名啟動

在瀏覽器中輸入htti)://ii):1158〃emip是指你的具體ip地址或者你的機器名1158是端口

一般情況下這個服務是不啟動很不安全

4.oracle用戶管理

⑴創(chuàng)建用戶只有具有DBA權限才能創(chuàng)建比如systemsys

基本用法：createuser用戶名identiHedby密碼

舉例:createuserxiaoqiangidentifiedbyhao200881037[oracle要求用戶卷碼不能用數字開頭]

SQL>createuserxiaoqiangidentifiedbyhao200881037

用戶已創(chuàng)建。

SQL>一

后面我將密碼修改為了200881037

?為什么創(chuàng)建的用戶無法登陸

這是因為oracle剛剛創(chuàng)建的用戶是沒有任何權限，需要管理員給用戶分配適應的權限，才能夠登陸

grantcreatesessiontoxiaoqiang〃會話權限

Enteruser-name：system

Enterpassword：

Connectedto：

OracleDatabase11gEnterpriseEditionRelease.0-64bitProduction

WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions

SQL>grantcreatesessiontoxiaoqiang

2；

Grantsucceeded.

SQL>connxiaoqiang/hao200881037

Connected.

⑴權限

系統(tǒng)權限：和數據庫管理相關的權限:

createsession:createtable;createindex;createview;createsequence;createtri

對象權限：和用戶操作數據對象相關的權限:

updalu;ii^vrl;dclvtv；KcIvcl

(2)角色

預定義角色：把常用的權限集中起來，形成角色(套餐)

比如dbaconnectresource三種角色

自定義角色：自己定義套餐

(3)方案(schema)

在一個數據庫實例下：

當一個用戶，創(chuàng)建好后，如果該用戶創(chuàng)建了任意一個數據對象(表或觸發(fā)器等)，這時我們的DBMS

就會創(chuàng)建一個對應的方案與該用戶對應，并且該方案名字和用戶名一致.

小技巧：如果希望看到某個用戶的方案的數據對象，可以使用PL/SQLdeveloper工具

案例1：完成一個功能；讓xiaoqiang用戶去查詢scott的emp表

步驟1：先用SCOH登錄

connscott/tiger

步驟2:在scott賬號上給xiaoqiang賦權限

grantselect[update|delete|insert|all]onemptoxiaoqiang

這里就可以看出來方案A和方案B可以有相同名的數據庫，但是方案A中不可以有相同名的數據庫

步驟3：登錄xiaoqiang用戶去查詢emp表

錯誤用法：select*fromemp原因是在xiaoqiang登錄狀態(tài)下需要制定emp表來自哪里？

正確用法：select*fromscott.emp;

查詢時如果查詢其他方案一定要用帶上其他方案名。

如果不帶，就默認是select*fromxiacqiang.cmp

案例2：完成一個功能想辦法將xiaoqiang擁有的對scott.emp的權限轉給stu用戶。

sco【t—Xiaoqiang—>stu[權限轉移]

connscott/tiger;

grantallonscott.emptostuwithgrantoption;

//withgrantoption對象權限表示得到權限的用戶可以把權限繼續(xù)分配

//withadminoption系統(tǒng)權限如果是系統(tǒng)權限，則帶withadminiption

創(chuàng)建了普通賬戶xiaoqiang密碼hao200881()37

修改密碼(前提是system或sys用戶)passw[ord]

基本用法password用戶名

如果給自己修改密碼則可以不帶用戶名

如果給別人修改密碼則需要帶用戶名

表空間：表存在的空間，一個表空間就是指向具體的數據文件

(4)用戶管理的綜合案例

創(chuàng)建的新用戶是沒有任何權限的，甚至連登錄(會話)的數據庫的權限都沒有，需要為其指定響應的權限，給

一個用戶賦權限使用命令grant,回收權限revoke

grant權限/角色to用戶

(I)使用system創(chuàng)建xiaoqiang

SQI.>rreatpuserxianqiangidfintififtdhyhan2PlRRRlA37

用戶已創(chuàng)建。

SQL>一

后面我將密碼修改為了2(X)881037

(2)使用system給小紅分配2個常用角色

grantconnecttoxiaoqiang

grantresourcetoxiaoqiang

disconn〃切斷連接

(3)讓xiaoqiang登錄

connxiaoqiang/200881037

(4)xiaoqiang修改密碼pasw[ord]xiacqiang即可然要求你輸入舊密碼當然超級管理員不需要輸入IH密碼

⑸xiaohong創(chuàng)建一張最簡單的表

SQL>connxiaoqiang/200881037

Connected.

SQL>createtablestudent<idnumb”)；

Tablecreated.

SQL>insertintostudentualues<l>；

1vowcheated.

SQL>select*fromstudent

2；

ID

1

(6)使用system登錄，然后回收角色。

revokeconnectfromxiaoqiang

revokeresourcefromxiaoqiang

⑺刪除xiaoqiang用戶：

dropuser用戶名[cascade]

，當我們刪除一個用戶的時候，若這個用戶自己已經創(chuàng)建了數據對象(表、觸發(fā)器等)，需要加選項

cascade表示把這個用戶刪除同時，把該用戶創(chuàng)建的數據對象一并刪除。

否則無法刪除該用戶，oracle用戶認為刪除了該用戶就徹底拋棄了

⑸賬號鎖定

使用profile管理用戶口令，賬號鎖定指用戶登錄時最多可以輸入密碼的次數，也可以指定用戶鎖定的時間

(天)一般用DBA的身份去執(zhí)行該命令。profile文件［規(guī)則］

eg:

createprofilelock_accountlimitfailcd_login_attempts3paswword_lock_time2;

alteruserteaprofilelock_account;〃其中k)ck_account是文件名

(6)賬號解鎖

alteruser用戶名accountunlock;

(7)終止口令

eg:給tea創(chuàng)建一個profm文件，要求該用戶每隔10天必須修改自家的登錄密碼，款限期為2天。

createprofilemyprofilelimitpassword_life_time10password_grace_time2;

〃可以繼續(xù)加限制條件

alteruserteaprofilemyprofile;

(8)刪除profile文件

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

dropprofileprofile文件名。

5.oracle數據庫啟動流程

oracle可以通過命令行的方式啟動，我們看看具體如何操作：

windows下:

(I)lsnrcllstart(啟動監(jiān)聽)

(2)oradim-startup-sid數據庫實例名

linux下：

(1)IsnrctIstart(啟動監(jiān)聽)

(2)sqlplussys/chang_on_installassysdba(以sysdba身份登錄)

sqlplus/nolog

connsys/chang_on_installassysdba

(3)siartup

6.oracle登錄認證方式

oracle在windows和linux下是不完全相同的：

windows下:

如果當前用戶屬于本地操作系統(tǒng)的ora_dba組（對于windows操作系統(tǒng)而言），即可通過操作系統(tǒng)認證。

普通用戶:默認是以數據庫方式認證，比如connscott/tiger;

特權用戶：默認是以操作系統(tǒng)認證（即:只要當前用戶是在ora_dba組中則可以通過認證），比如conn

system/managerassysdba;DBMS一看到assysdba則認為要以特權用戶登錄，前面的用戶名和密碼不看，

登錄后自動切換成sys用戶<=>connsys/managero

如果當前用戶（win7系統(tǒng)賬號）不在ora_dba組中，connsys/manager輸對了密碼還是可以登錄進去的（這

時是采用了數據庫方式驗證）

sqlnet.ora文件在D:\xiaoqiang\oracle'.product\11.2.0\dbhome_l\NET\VORK\ADMIN目錄下：

同時如果你安裝第三方工具PL/SQLDeveloper,同時也需要修改

D:\xiaoqiang\oracle\product\instantclientl12目錄下的sqlnet.ora文件

通過配置sqlnet.ora文件,可以修改oracle登錄認證方式：

SQLNET.AUTHENTICATIONSERVICES=（NTS）是基于操作系統(tǒng)驗證

SQLNET.AUTHENTICATIONSERYICES=（NONE）是基于Oracle驗證

SQLNET.AUTHENTICATIONSERVICES=（NONE,NTS）是二者共存

linux下：

默認情況下linux下的oracle數據庫sqlnet.ora文件沒有SQLNET.AUTHENTICATION_SERVICES

參數，此時是基于操作系統(tǒng)認真和oracle密碼驗證共存，力口上SQLNET.AUTHENTICATION_SERVICE5參

數后，不管SQLNET.AUTHENTICATION_SERVICES設置為NONE還是NTS都是基于oracle密碼驗證。

7.oracle丟失管理員密碼怎么辦

數據庫實例名是根據實際情況命名的C

恢復辦法：把原有密碼文件刪除，生成一個新的密碼文件

恢復步驟如下：(I)搜索名為PWD數據庫實例名.ora文件

(2)刪除該文件，為以防萬一，建議備份

⑶生成新的密碼文件，在DOS控制臺下輸入命令

orapwd行B=原來密碼文件的全路徑'密碼文件名.orapassword=新密碼entries=10;

這里密碼文件名是原來的密碼文件g=PWD數據庫實例名

entries表示登錄sys的最多用戶(特權用戶)

如果希望新的密網生效，則需要重新啟動數據庫實例服務.dosbservices.exe

還有出現以下情況：

ORACLE用SYS和SYSTEM默認密碼登錄提示ORA-Ol()17:invalidusernanie/password;logoiiddenied該

怎么解決？

解決辦法：

有可能是你在建數據庫的時候，

修改了默認的密碼

而自己又忘記

你可再重新修改過來

sqlplus/assysdba

alterusersystemidentifiedbymanager;

alterusersysidentifiedbymanager;

或者改成其他的你自己容易記住的

默認scott用戶密碼是tiger

8.oracle表管理

類(對象)和表(記錄)之間的關系

?創(chuàng)建表

基本語法

createtabictablc_namc(SQL>createtableusers(

列名列類型，2idnumber,

3namevarchar2(32),

4passwordvarchar2(32),

)5birthdaydate);

Tablecreated

?數據類型

①char(sizc)存放字符串最大2000個字符，是定長

eg：char(32)最多只能放入32個字符如果超過就報錯，如果不夠'abc，則用空格補全

②varchar2(size)變長最大可以存放4000個字符

③nchar(size)定長編碼方式unicode最大字符數是2000個

一個漢字占用nchar的一個字符空間，一個漢字，占用char的兩個字符空間

④nvarchar2(size)變長編碼方式unicode最大字符數是4000個

⑤dob字符型大對象變長最大8TB

⑥blob變長

說明:我們在實際開發(fā)中很少把文件存放在數據庫中(效率問題)，實際上我們一般記錄文件的一個路徑(URL

或本地路徑)，然后通過IO或網絡來操作。

如果我們要求對文件安全性比較高，可以考慮放入數據庫。

⑦number(p,s)p為整數位，s為小數位，范圍是l<=p<=38,-84<=s<=-127變長

保存數據范圍：■L0e-130<=numbervalue<=1.0e+126保存機器位數I-22byte

e.gnumber(5,2)表示一個小數有5位有效位，2位小數，范圍-999,99-999,99

比如你輸入573.316則真正保存是573.32,無法保存數據1000

number(5)等價于number(5.0),表示一個5位整數，范圍-99999-99999,輸入57523.316則保存57523

原則：如果在做實際開發(fā)中，我們沒有指定數據小數位，則直接使用number

⑧date日期類型

包含年月日，時分秒

插入數據時要使用默認格式是:'dd.mm.yyy，;當然如果用自己格式需要借用to_date函數

SQL>insertintotestlvalues(to_date(,2005-ll-ir；YYYY-MM-DD,));

1rowinserted

to_char

你可以使用selectename,hiredate,salfromempwheredeptno=10;顯示信息，可是,

在某些情況下，這個并不能滿足你的需求。

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

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

9.oracle基本查詢

oracle的crud操作(createretrieve/readupdatedelete)

oracle表的管理一修改表

使用ALTERTABLE語句添加,修改,或刪除列的語法.

ALTERTABLEtablename

ADD(colunmnamedatatype);

ALTERTABLEtable

MODIFY(columnnamedatatype);

飛

ALTERTABLEtable

DROPcolumn(column);

修改表的名稱：rename表名to新表名

添加一個字段

SQL>ALTERTABLEstudentadd(classic!NUMBER(2));

修改一個字段的長度

SQDALTERTABLEstudentmodify(xmVARCHAR2(30));

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

SQL)ALTERTABLEstudentmodify(xmCHAR(30));

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

SQL)ALTERTABLEstudentDROPCOLUMNsal;

修改表的名字很少有這種需求

SQDRENAMEstudentTOstu;

刪除表

SQL>DROPTABLEstudent;

添加數據

所有字段都插入數據

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

oracle中默認的日期格式'dd-mon-yy'dd日子(天)mon月份yy2位的年'09-6月-99'

199g年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=J1984-04-0TWHERExh='A001’;

修改含有null值的數據

不要用=null而是用isnull；

SELECT*FROMstudentWHEREbirthdayISnull;

刪除數據

DELETEFROMstudent;

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

Delete的數據可以恢復.

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

DELETEFROMstudent;

rollbacktoa;一恢復到保存點

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

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

deletefromstudentWHERExh=*AOOT;一刪除一條記錄：

truncateTABLEsludenl;一刪除表中的所有記錄，表結構還在，不寫日志，無法找回刪除的記錄,

速度快。

Ie基本所有查詢案例

在我們講解的過程中我們利用scott用戶存在的幾張表(emp,dept)為大家演示如何使用select語句，select

語句在軟件編程中非常有用，希望大家好好的掌握。

emp雇員表hiredate入職時間research研發(fā)部

clerk普員工sal月工資operations業(yè)務部

salesman銷售comm獎金loc部門所在地點

manager經理deptno部門salgrade工資級別

analyst分析師dept部門表grade級別

president總裁deptno部門編號losal最低工資

mgr上級的編號accounting財務部hisal最高工資

DESCemp;

查詢所有列

SELECT*FROMdept;

切忌動不動就用select*

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

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

INSERTINTOusersVALUES('aOOOI','啊啊啊啊'，'aaaaaaaaaaaaaaaaaaaaaaa');

--從自己復制，加大數據量大概幾萬行就可以了可以用來測試sql語句執(zhí)行效率

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

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

查詢指定列

SELECTename,sal,job,dcptnoFROMemp;

如何取消重復行DISTINCT

SELECTDISTINCTdeptnojobFROMenip;

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

SELEC1depmo,job,salFROMempWHEREename='SMITH';

注意：oracle對內容的大小寫是區(qū)分的，所以ename=SMITH，和enamc=〈mith，是不同的

使月算術表達式nv】null

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

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

使月列的別名

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

如何處理null值使用nv］函數來處理

如何連接字符串(||)

SELECTename||'isa|||jobFROMemp;

使月where子句

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

SELECT*FROMempWHEREsal>3000;

問題:如何查找1982.1.1后入職的員工?

SELECTename,hiredateFROMempWHEREhiredate>'i-l月-1982';

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

SELECTcname.salFROMcmpWHEREsal>=2000ANDsal<=3000:

如何使用like操作符

%：表示。到多個字符_：表示任意單個字符

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

SELECTename,salFROMempWHEREenamelike'S%';

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

SELECTename,salFROMempWHEREenamelike

在where條件中使用in

問題：如何顯示empn。為7844.7839,123,456的雇員情況？

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

使月isnull的操作符

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

錯誤寫法:select*fromempwheremgr=

正確寫法：SELECT*FROMcmpWHEREmgrisnull;

使用邏輯操作符號

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

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

使月orderby字句默認asc

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

SELECT*FROMempORDERbysal;

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

SELECT*FROMempORDERbydeplno,salDESC;

使用列的別名排序

問題：按年薪排序

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

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

Cicar清屏命令

數據分組----max,min,avg,sum,count

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

SELECTMAX(sal),min(sal)FROMempe;

最高工資那個人是誰？

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

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

注意：selccienamc,max(sal)fromcmp;這語句執(zhí)行的時候會報錯，說ORA-00937：非單組分組函數。因為

max是分組函數，而ename不是分組函數.......

但是selectmin(sal),niax(sal)fromemp;這句是可以執(zhí)行的。因為min和max都是分組函數，就是說：如果列

里面有一個分組函數，其它的都必須是分組函數，否則就出錯。這是語法規(guī)定的問題：如何顯示所有員工

的平均工資和工資總和？

問題：如何計算總共有多少員工問題：如何

擴展要求：

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

SELECTename,job.salFROMenipewheresal=(SELECTMAX(sal)FROMemp);

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

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

groupby和having子句

groupby用于對查詢的結果分組統(tǒng)計having子句用于限制分組顯示結果

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

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

(注意：這里暗藏了?點，如果你要分組查詢的話，分組的字段deptno?定要出現在查詢的列表里面，否

則會報錯。因為分組的字段都不出現的話，就沒辦法分組了)

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

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

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

SELECTAVG(sal),MAX(sal),depmoFROMcmpGROUPbydeptnohavingAVG(sal)<2000:

對數據分組的總結

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

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

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

則就會出錯。

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

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

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

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

(如果有N張表聯(liá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;

GRADELOSALHISAL

17001200

212011400

3140120(X)

420013000

530019999

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

擴展要求：

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

SELECTarnc.e.sal,d.dnamcFROMcmpc.deptdWHEREc.dcptno=d.dcptnoORDERbyc.dcptno;

(注意:如果用groupby,一定要把e.deptno放到查詢列里面)

自

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

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

比如顯示員工下ORD，的上級

SELECTworker.ename,boss.enameFROMempworkcr,empbossWHEREworker.mgr=boss.empnoAND

amc='FORD';

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

思路：

1查詢出SMITH的部門號

selectdeptnofromempWHEREename='SMITH,;

2顯示

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

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

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

SELECTDISTINCTjobFROMcmpWHEREdeptno=10;

SELECT*FROMempWHEREjobIN(SELECTDISTINCTjobFROMempWHEREdeptno=10);

(注意：不能用job=..,因為等號=是一對一的)

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

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

SELECTename.sal.deptnoFROMempWHEREsal>all(SELECTsalFROMempWHEREdeptno=30):

擴展要求：

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

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

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

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

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

SELECTcnamc,sal,dcpmoFROMcmpWHEREsal>ANY(SELECTsalFROMcmpWHEREdcptno=30);

擴展要求：

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

SELECTcname,sal,dcptnoFROMcmpWHEREsal>(SELECTmin(sal)FROMempWHEREdcptno=30);

多列子查詢

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

而多列子查詢是指查詢返回多個列數據的子查詢語句。

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

SELECTdcptno,jobFROMempWHEREcnamc='SMITH,;

SELECT*FROMempWHERE(deplno,job)=(SELECTdeptnoJobFROMempWHEREename='SMITH');

在from了?句中使用子查詢

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

思路：

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

SELECTdcptno,AVG(sal)mysalFROMcmpGROUPbydcptno;

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

SELECTamc,c.dcptno,c.sal,ds.mysalFROMcmpc,(SELECTdcptno,AVG(sal)mysalPROMcmpGROUP

bydcptno)dsWHEREc.dcptno=ds.deplnoANDc.sal>ds.mysal;

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

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

小總結：

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

當在from了句中使用了查詢時，必須給了?查詢指定別名。

注意:別名不能用as,如:SELECTamc,c.dcptno,c.sal,ds.mysalFROMcmpc.(SELECTdcptno.AVG(sal)

mysalFROMempGROUPbydeptno)asdsWHEREe.deptno=ds.deptnoANDe.sal>ds.mysal;

在擊前不能加as,否則會報錯(給表取別名的時候，不能加as；但是給列取別名，是可以加as的)

lO.oracle分次查詢

mysql:select*from表名where條件limit從第幾條取，取幾條見

mysql分頁查詢

sqlserver:selecttop取幾條*from表名whereidnotin（selecttop4idfrom

表名where條件）也可以使用行集函數見3.sqlserver分頁查由

排除前4條，再取4條，這個案例實際上是取5-8條

oracle：

SQL>selectemp.*,rownxunfromemp；Oracle將每個查詢當做臨時表，并且底層進行優(yōu)化

添加rownum字段一一

EMPNOENAMEJODMGRIIIREDATE5ALCOMMDEPTNO^ROWNUbT

7369SMITHCLERK79021980/12/17800.00201

7499ALLENSALESMAN76981981/2/201600.00300.00302

7521WARDSALESMAN76981981/2/221250.00500.00303

7566JONESMANAGER78391981/4/22975.00204

7654MARTINSALESMAN76981981/9/281250.001400.00305

7698BLAKEMANAGER78391981/5/12850.00306

7782CLARKMANAGER78391981/6/92450.00107

7788SCOTTANALYST75661987/4/193000.00208

7839KINGPRESIDENT1981/11/175000.00109

7844TURNERSALESMAN76981981/9/81500.000.003010

7876ADAMSCLERK77881987/5/231100.002011

7Q00JAMESCT.FRK76QE1QS0.00ac1?

7902FORDANALYST75661981/12/33000.002013

7934MILLERCLERK77821982/1/231300.001014

Oracle在每個查詢中會自動添加一個字段rownun（oracle底層進行了優(yōu)化）

14rowsselected

以scott/tiger賬號登陸進行查詢：［分頁查詢模板］

selectt2.*from

(selecttl.*,rownumrnfrom

(select*fromemp)tl

whererownum<=f|)t2wherern>=1;

先找到小于6的然后找到大于4的

【順序可以反】

selectt2.*from

(selecttl.*,rownumrnfrom

(select*fromemp)tl

whererownum>=()t2wherern<=6;

oracle使用三層過濾：

第一層:select*fromemp

第二層：selecttl.*,rownumrnfrom(select*fromemp)tl

whererownum<=6

第二層：selectt2.*from

(selecttl.*,rownumrnfrom

(select*fromemp)tl

whererownum<=()t2wherern>=4；

上面是一個分頁模板，■表示取到第幾條，■表示從第幾條取

(1)刪除重復記錄

在幾千條記錄里，存在著些相同的記錄，請用sql語句刪除。

【注意】1.表中肯定是沒有主健的，這才叫記錄相同

2.若有主鍵(主鍵肯定不同)，那請你把其他字段變成一個臨時表，再使用下而方法

準備：

--創(chuàng)建表

createtablepeople(

peopleldnumber,

peopleNamevarchar(50),

peoplcAgcnumber);

--插入數據

insertintopeoplevalues",'haozl',22);

insertintopeoplevalues(2,'wangx\23);

insertintopeoplevalues(3,'liwr',24);

insertintopeoplevalucs(4,'zhanggh',25);

insertintopeoplevalues(5,'cheng',26);

--自我復制

insertintopcople(pcopleld,peopleName,peopleAge)(selectpeopleld,peopleName,peopleAge

frompeople);

insertintopeoplevalues(6,'hancr,27);

insertintopeoplevalucs(7,'yangqp',22);

insertintopeoplevalues(8/wangt',23);

insertintopeoplevalues(9,'nieyp',18);

insertintopeoplevalucs(10/tianx',19);

insertintopeople(peopleld,peopleName,peopleAge)(selectpeopleld,peopleName,peopleAge

frompeople);

iiibcilinlupeoplevdlucb(ll/haii5iii',41);

insertintopeoplevalues(12；haog',31);

insertintopeoplevalues(13/chengyy',51);

insertintopeoplevalucs(14/chenmm',61);

insertintopeoplevalues(15/xujf,ll);

insertintopeople(peopleld,peopleName,peopleAge)(selectpeopleld,peopleName.peopleAge

frompeople);

insertintopeoplevalues(16；wanggl',23);

insertintopeoplevalues(17/dujl',32);

insertintopeoplevalues(18/gaozg',28);

insertintopeoplevalues(19/haow',27);

insertintopeoplevalues(20,'lizy',25);

--統(tǒng)計重復個數

selectpeopleld,COUNT(peopleld)frompeoplegroupbypeopleldhavingCOUNT(peopleld)>l;

-查詢具體重復記錄(單個字段］

selectdistinct*frompeoplewherepeopleldin

(selectpeopleldfrompeoplegroupbypeopleldhavingCOUNT(peopleId)>l);

一查詢具體重復記錄(多個字段)

selectdistinct*frorrpeopleawhere(peopleld,a.peoplename)in

(selectpeopleld,peoplenamefrompeoplegroupbypeopleld,peoplename

havingCOUNT(*)>1);

savepointsavepointl;一使用事務

一刪除衣中多余記錄(多個字段)

deletefrompeopleawhere(a.peopleld,a.peoplename)in

(selectpeopleld,peoplenamefrompeoplegroupbypeopleld,peoplename

havingCOUNT(*)>1)

and

rowidnotin

(selectmin(rowid)frempeoplegroupbypeopleld,peoplenamehaving

COUNT(*)>1);

rollbacktosavepointl;一還原

ll.oracle合并查詢

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

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

1).union

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

SELECTename,sal.jobFROMempWHEREsal>2500

UNION

SELECTename,sal,jobFROMempWHEREjob='MANAGER,;

2).unionall

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

SELECTcname,sal,jobFROMempWHEREsal>250()

UNIONALL

SELECTename,sal,jobFROMempWHEREjob='MANAGER'：

該操作符用于取得兩個