Oracle使用學(xué)習(xí)總結(jié)

Oracle使用學(xué)習(xí)經(jīng)驗(yàn)總結(jié)

張浩博客

sql語(yǔ)句部分

1.新型企業(yè)級(jí)智能數(shù)據(jù)管理系統(tǒng)---SybaseASE12.5（AdaptiveServerEn

terprise）

2.**17260,"Can"trun%1!fromwithinatransaction."

3.在創(chuàng)建存儲(chǔ)過(guò)程CREAT之前編寫(xiě)存儲(chǔ)過(guò)程德錯(cuò)誤編號(hào)說(shuō)明4.charindex("sa_role",show_role())

5.begin

6.@@trancount

Oracle系統(tǒng)安裝配置

1.修改sybase數(shù)據(jù)庫(kù)sa用戶(hù)密碼

2.

3.env查看環(huán)境變量

4.linux中.bash_profile和.bashrc的什么區(qū)別5.數(shù)據(jù)庫(kù)中執(zhí)行“存儲(chǔ)”過(guò)程。

6.bcp導(dǎo)入、導(dǎo)出數(shù)據(jù),備份數(shù)據(jù)庫(kù)、備份表格數(shù)據(jù)、備份數(shù)據(jù)

●bcp

7.附錄1、英文錯(cuò)誤碼描述：（靈活運(yùn)用�。。。�

1．Auserwiththesamenamealreadyexistsinthedatabase在數(shù)據(jù)庫(kù)中已經(jīng)存在相同的一個(gè)用戶(hù)2．isnotavalidname不是一個(gè)可用的名字

3．Useralreadyhasaloginunderadifferentname.用戶(hù)已經(jīng)用一個(gè)不同的名字登錄了4．Nologinwiththespecifiednameexists沒(méi)有登錄

5．Timerangenamemustbenon-NULL時(shí)間范圍名字必須不為空6．Limittypemustbenon-NULL限制類(lèi)型必須不為空7．Unknownlimittype不可知的限制類(lèi)型8．Ge獲得開(kāi)始結(jié)束時(shí)間從當(dāng)前的范圍9．

8.附錄2、錯(cuò)誤語(yǔ)句：

9.附錄3、錯(cuò)誤語(yǔ)句：

10.附錄4、錯(cuò)誤語(yǔ)句：

11.附錄5、錯(cuò)誤語(yǔ)句：自我總結(jié)書(shū)籍和網(wǎng)站（建議經(jīng)常去看看，呵呵）

1.2.3.4.5.

擴(kuò)展閱讀：有關(guān)Oracle學(xué)習(xí)總結(jié)

表xyzabc1mhj1mhjk1njk2uwe2uwert3qs4ads4adsaa

怎么刪掉第1、5、8條記錄？（字段a、b相同的幾條記錄只留一條，留下字段C較短的一條）

DELETEFROMLIANXI

WHERELENGTH(C)NOTIN(SELECTMIN(LENGTH(C))FROMLIANXIGROUPBYA,B)

oracle數(shù)據(jù)庫(kù)常用的命令集錦

今日開(kāi)始研究oracle,搜索到了一個(gè)好東東，拿出來(lái)與大家一同分享。下面是摘抄的部分：[local]2[/local]

ORACLE相關(guān)語(yǔ)法及命令一、Oracle入門(mén)理論知識(shí)：

Oracle的物理組件有三個(gè)：

(1)數(shù)據(jù)文件數(shù)據(jù)文件是用于存儲(chǔ)數(shù)據(jù)庫(kù)數(shù)據(jù)的文件，如表、索引數(shù)據(jù)。每個(gè)Oracle數(shù)據(jù)庫(kù)有一個(gè)或多個(gè)物理數(shù)據(jù)文件，一個(gè)數(shù)據(jù)文件只能與一個(gè)數(shù)據(jù)庫(kù)關(guān)聯(lián)。(2)日志文件用于記錄對(duì)數(shù)據(jù)庫(kù)進(jìn)行的修改信息，日志文件主要用于在數(shù)據(jù)庫(kù)出現(xiàn)故障時(shí)實(shí)施數(shù)據(jù)庫(kù)恢復(fù)。

(3)控制文件控制文件是記錄數(shù)據(jù)庫(kù)物理結(jié)構(gòu)的二進(jìn)制文件，每個(gè)Oracle數(shù)據(jù)庫(kù)都含有一個(gè)控制文件。

Oracle的邏輯組件：

表空間(TableSpace)表空間是數(shù)據(jù)庫(kù)最大的邏輯單位，一個(gè)數(shù)據(jù)庫(kù)至少包含一個(gè)表空間，一個(gè)表空間包含一個(gè)或多個(gè)段等等。段(Segment)段存在于表空間中，分成4類(lèi)，數(shù)據(jù)段、索引段、回退段、臨時(shí)段。區(qū)(Extent)區(qū)是磁盤(pán)空間分配最小單位，由連續(xù)的數(shù)據(jù)塊組成，一個(gè)或多個(gè)區(qū)構(gòu)成段，區(qū)只能存在于一個(gè)數(shù)據(jù)文件中。

數(shù)據(jù)塊(DataBlock)數(shù)據(jù)塊是數(shù)據(jù)庫(kù)中最小的數(shù)據(jù)組織單位與管理單位，Oracle數(shù)據(jù)庫(kù)中的數(shù)據(jù)存儲(chǔ)于數(shù)據(jù)塊中，取值范圍2K-64K之間。

模式(schema)模式是對(duì)用戶(hù)所創(chuàng)建的數(shù)據(jù)庫(kù)對(duì)象的總稱(chēng)，又稱(chēng)為用戶(hù)模式。概念：

內(nèi)存Oracle內(nèi)存結(jié)構(gòu)包含以下兩個(gè)內(nèi)存區(qū)。

1、系統(tǒng)全局區(qū)(SGA)實(shí)例啟動(dòng)時(shí)分配該內(nèi)存區(qū)，是Oracle實(shí)例的一個(gè)基本組件。又稱(chēng)為共享全局區(qū)，它用來(lái)存儲(chǔ)數(shù)據(jù)庫(kù)信息，并由多個(gè)數(shù)據(jù)庫(kù)進(jìn)程共享。可分為共享池、數(shù)據(jù)緩沖區(qū)及日志緩沖區(qū)。

(1)共享池是對(duì)SQL、PL\\SQL程序進(jìn)行語(yǔ)法分析、編譯、執(zhí)行的內(nèi)存區(qū)域。共享池由庫(kù)緩存和數(shù)據(jù)字典緩存組成。其中，庫(kù)緩存含有最近執(zhí)行的SQL、PL\\SQL語(yǔ)句的分析碼和執(zhí)行計(jì)劃；數(shù)據(jù)字典緩存含有從數(shù)據(jù)字典中得到的表、索引、列定義和權(quán)限等信息。(2)數(shù)據(jù)緩沖區(qū)數(shù)據(jù)緩沖區(qū)用于存儲(chǔ)從磁盤(pán)數(shù)據(jù)文件中讀入的數(shù)據(jù)，所有用戶(hù)共享。(3)日志緩沖區(qū)日志記錄數(shù)據(jù)庫(kù)的所有修改信息，主要用于恢復(fù)數(shù)據(jù)。

2、程序全局區(qū)(PGA)服務(wù)器進(jìn)程啟動(dòng)時(shí)分配該內(nèi)存區(qū)。PGA為非共享區(qū)，只能單個(gè)進(jìn)程使用，當(dāng)一個(gè)用戶(hù)會(huì)話結(jié)束后，PGA釋放。

用戶(hù)進(jìn)程(PGA)發(fā)送SQL語(yǔ)句到共享全局區(qū)(SGA)，先在共享池的庫(kù)緩存中查詢(xún)是否存在所需的數(shù)據(jù)塊，如果存在就在數(shù)據(jù)字典中讀取相應(yīng)的數(shù)據(jù)塊，如果不存在就由服務(wù)器進(jìn)程(DBWR)來(lái)IO數(shù)據(jù)庫(kù)

語(yǔ)法知識(shí)：

創(chuàng)建表空間的語(yǔ)法如下：

CREATETABLESPACEtablespacenameDATAFILE"d:\\filename.DBF"[SIZEint[KB|MB]][AUTOEXTEND[OFF|ON]];

tablespacename是需創(chuàng)建的表空間名稱(chēng)。

DATAFILE指定組成表空間的一個(gè)或多個(gè)數(shù)據(jù)文件，當(dāng)有多個(gè)數(shù)據(jù)文件時(shí)使用逗號(hào)分隔。filename是表空間中數(shù)據(jù)文件的路徑和名稱(chēng)。

SIZE指定文件的大小，用K指定千字節(jié)大小，用M指定兆字節(jié)大小。AUTOEXTEND子句用來(lái)啟用或禁用數(shù)據(jù)文件的自動(dòng)擴(kuò)展。Oracle默認(rèn)用戶(hù)：

用戶(hù)名：sys默認(rèn)密碼：chage_on_install用來(lái)管理?yè)碛蠴racle數(shù)據(jù)字典文件用戶(hù)名：system默認(rèn)密碼：manager用來(lái)管理?yè)碛袛?shù)據(jù)字典視圖對(duì)象用戶(hù)名：scott默認(rèn)密碼：tiger示例用戶(hù)，包括emp、dept等表連接Oracle:在控制臺(tái)下輸入sqlplus用戶(hù)名/密碼回車(chē)或sqlplusw回車(chē)相關(guān)命令：

disconn//退出當(dāng)前登錄conn用戶(hù)名/密碼//連接Oracle

alteruser用戶(hù)名identifiedby密碼//修改用戶(hù)口令dropuser用戶(hù)名cascade;//刪除用戶(hù)

alteruser用戶(hù)名accountlock;//給某個(gè)用戶(hù)加鎖alteruser用戶(hù)名accountunlock;//給某個(gè)用戶(hù)解鎖ed回車(chē)://打開(kāi)緩沖區(qū)

/回車(chē)://執(zhí)行緩沖區(qū)中的語(yǔ)句

createuser用戶(hù)名identifiedby密碼[passwordexpire]

[defaulttablespace表空間名][temporarytablespace臨時(shí)表空間名];//創(chuàng)建用戶(hù)相關(guān)權(quán)限：

grantconnecttoscott;//connect角色將允許用戶(hù)創(chuàng)建數(shù)據(jù)庫(kù)并在數(shù)據(jù)庫(kù)中創(chuàng)建表或其他對(duì)象grantresourcetoscott;//resource角色將允許用戶(hù)使用數(shù)據(jù)庫(kù)中的空間grantcreatesequencetoscott;//createsequence權(quán)限將允許用戶(hù)創(chuàng)建序列，此權(quán)限包含在connect連接角色中

grantselectonemptoscott;//將emp表的查詢(xún)權(quán)限授予用戶(hù)scott

grantupdate(vencode,venname)on表名toscott;//將特定列的更新權(quán)限授予用戶(hù)scottgrant權(quán)限on表名to用戶(hù)名withgrantoption;//接受該權(quán)限的用戶(hù)可以將此權(quán)限授予其他用戶(hù)

revokeselect,updateon表名from用戶(hù)名;//收回相應(yīng)的權(quán)限二、SQL查詢(xún)和SQL函數(shù)SQL支持如下類(lèi)別的命令：

數(shù)據(jù)定義語(yǔ)言：create(創(chuàng)建)、alter(更改)、drop(刪除)和truncate(截?cái)?命令。數(shù)據(jù)操縱語(yǔ)言：insert(插入)、select(選擇)、delete(刪除)和update(更新)命令。事務(wù)控制語(yǔ)言：commit(提交)、savepoint(保存點(diǎn))和rollback(回滾)命令。數(shù)據(jù)控制語(yǔ)言：grant(授予)和revoke(回收)命令。數(shù)據(jù)類(lèi)型：

char:長(zhǎng)度在1到201*個(gè)字節(jié)，聲明多少字節(jié)在內(nèi)存中就占用多少字節(jié)，輸入的值小于指定的長(zhǎng)度時(shí)用空格填充。

varchar2:長(zhǎng)度在1到4000個(gè)字節(jié)，輸入的值是多少字節(jié)，就占用多少字節(jié)。

long:長(zhǎng)度在2GB，設(shè)置為此類(lèi)型的列時(shí)，要注意：一個(gè)表中只有一列可以為long類(lèi)型，long類(lèi)型列不能定義為唯一約束或主鍵約束，不能建立索引，過(guò)程或存儲(chǔ)過(guò)程不能接受long類(lèi)型的參數(shù)。

number(p,s):其中p為精度，表示數(shù)字的總位數(shù)，在1至38之間。s為范圍，表示小數(shù)點(diǎn)右邊數(shù)字的位數(shù)，在-84至127之間。

date:日期類(lèi)型，sysdate為當(dāng)前系統(tǒng)時(shí)間。格式為08-9月-07。

timestamp:用于存儲(chǔ)日期的年、月、日以及時(shí)間的時(shí)、分和秒。其中秒精確到小數(shù)點(diǎn)后6位，

systimestamp返回當(dāng)前日期、時(shí)間。格式為08-9月-0704.08.30.000000下午。

raw:此數(shù)據(jù)類(lèi)型用于存儲(chǔ)基于字節(jié)的數(shù)據(jù)，如二進(jìn)制數(shù)據(jù)或字節(jié)串，該類(lèi)型最多能存儲(chǔ)201*個(gè)字節(jié)，可以建立索引。

longraw:此數(shù)據(jù)類(lèi)型用于可變長(zhǎng)度的二進(jìn)制數(shù)據(jù)，最多能存儲(chǔ)2GB。long數(shù)據(jù)類(lèi)型的所有限制對(duì)longraw數(shù)據(jù)類(lèi)型也同樣有效。

lob又稱(chēng)為"大對(duì)象"數(shù)據(jù)類(lèi)型，最多能存儲(chǔ)4GB的非結(jié)構(gòu)化信息。包括：

clob:clob代表CharacterLOB(字符LOB)，它能存儲(chǔ)大量字符數(shù)據(jù)。如XML文檔。blob:blob代表BinaryLOB(二進(jìn)制LOB)，它能存儲(chǔ)較大的二進(jìn)制對(duì)象，如圖形、視頻剪輯和聲音剪輯。

bfile:bfile代表BinaryFile(二進(jìn)制文件)，它能夠?qū)⒍�進(jìn)制文件存儲(chǔ)在數(shù)據(jù)庫(kù)外部的操作系統(tǒng)文件中。偽列：

rowid:selectrowid,ename,fromscott.empwhereempno="7900";

rownum:select*fromscott.empwhererownumaltertable表名dropcolumn列名;//刪除列

truncatetable表名;//中刪除記錄而不刪除結(jié)構(gòu)，不使用事務(wù)處理，因此無(wú)法回滾droptable表名;//刪除表及其全部數(shù)據(jù)

createtable新表名asselect*from表名where1=2;//用現(xiàn)有的表創(chuàng)建一個(gè)新表selectdeptno*2"NewNo",dname,locfromdept;//指定一個(gè)含有特殊字符(如空格)的列標(biāo)題

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

savepoint標(biāo)記名;//標(biāo)記事務(wù)點(diǎn)rollback;//回滾整個(gè)事務(wù)處理

rollbackto[savepoint]標(biāo)記名;//回滾到事務(wù)中某個(gè)特定的保存點(diǎn)集合操作符：

union(聯(lián)合):此操作符返回兩個(gè)查詢(xún)選定的所有不重復(fù)的行。

語(yǔ)法selectordernofromorder_masterUNIONselectordernofromorder_detail;unionall(聯(lián)合所有):此操作符合并兩個(gè)查詢(xún)選定的所有行，包括重復(fù)的行。

語(yǔ)法：selectorderno,enamefromorder_masterUNIONALLselectorderno,pronamefromorder_detailorderby2;

注意：在兩個(gè)select語(yǔ)句中指定的列名不必相同，但數(shù)據(jù)類(lèi)型必須匹配。也可以對(duì)聯(lián)合查詢(xún)的結(jié)果進(jìn)行排序，使用OrderBy子句時(shí)，它必須放在最后

一個(gè)select語(yǔ)句之后，而且必須指定列索引來(lái)排序，而不是指定列名，列索引是從1開(kāi)始的整數(shù)。上述語(yǔ)法便是以proname的索引排序

intersect(交集):此操作符只返回兩個(gè)查詢(xún)都有的行。

語(yǔ)法：selectordernofromorder_masterINTERSECTselectordernofromorder_detail;

minus(減集):此操作符中返回由第一個(gè)查詢(xún)選定但是第二個(gè)查詢(xún)中沒(méi)有選定的行，也就是在第一個(gè)查詢(xún)結(jié)果中排除第二個(gè)查詢(xún)結(jié)果中出現(xiàn)的行。

語(yǔ)法：selectordernofromorder_masterMINUSselectordernofromorder_detail;查詢(xún)尚未交付的訂單

連接(||)操作符：

語(yǔ)法：select("供應(yīng)商"||venname||"的地址是"||venadd1||""||venadd2||""||venadd3)地址fromvendor_masterwherevencode="V002";將多個(gè)字符串合并為一個(gè)字符串。SQL函數(shù)：

1、日期函數(shù)：

add_months:此函數(shù)返回給指定的日期加上指定的月數(shù)后的日期值。語(yǔ)法為add_months(d,n),其中d是日期，n表示月數(shù)。

示例：selectadd_months(sysdate,2)fromdual;將當(dāng)前時(shí)間加上2個(gè)月后的日期值。months_between:此函數(shù)返回兩個(gè)日期之間的月數(shù)。語(yǔ)法為months_between(d1,d2),其中d1和d2是日期，如果d1大于d2，則結(jié)果為正數(shù)；否則為負(fù)數(shù)。

last_day:此函數(shù)返回指定日期當(dāng)月的最后一天的日期值，語(yǔ)法為last_day(d),其中d表示日期。

示例：selectlast_day(sysdate)fromdual;返回當(dāng)前日期的月的最后一天，如果是9月就返回30-09月-07

round:此函數(shù)返回日期值，將日期四舍五入為格式模型指定的單位。語(yǔ)法為round(d,[fmt])。其中d是日期，fmt是格式模型。fmt是一個(gè)可選項(xiàng)，日期默認(rèn)舍入為最靠近的那一天。如果指定格式為年"Year"，則舍入到年的開(kāi)始，即1月1日;如果格式為月"Month"，則舍入到月的第一日;如果格式為周"Day"，則舍入到最靠近的星期日。示例：selectround(sysdate,"month")fromdual;返回最接近的一個(gè)月。

next_day:此函數(shù)返回指定的下一個(gè)星期幾的日期。語(yǔ)法為next_day(d,day)。其中d表示日期，而day指周內(nèi)任何一天。

示例：selectnext_day(sysdate,"星期日")fromdual;返回下一個(gè)星期日的日期，也可以用1表示，以此類(lèi)推，星期一以2表示。

trunc:此函數(shù)將指定日期截?cái)酁橛筛袷侥Ｐ椭付ǖ膯挝蝗掌�，與Round函數(shù)不同的是它只舍不入，語(yǔ)法為trunc(d,[fmt])，與round格式相同。

示例：selecttrunc(sysdate,"year")fromdual;返回當(dāng)前年的第一天，也就是1月1日。示例：selecttrunc(sysdate,"day")fromdual;返回緊靠前面的星期日。如果為"201*年1月27日"就會(huì)返回"201*年1月23日"。

extract:此函數(shù)提取日期時(shí)間類(lèi)型中的特定部分。語(yǔ)法為extract(fmtfromd)，其中d是日期時(shí)間表達(dá)式，fmt是要提取的部分的格式。格式的取值可以是year,month,day,hour,minute,second,注意此處的格式不使用單引號(hào)。示例：selectextract(yearfromsysdate)fromdual;返回當(dāng)前的年份。2、字符函數(shù)：

initcap(char):首字母大寫(xiě)，示例：selectinitcap("hello")fromdual;輸出結(jié)果:Hello。lower(char):轉(zhuǎn)換為小寫(xiě)，示例：selectlower("FUN")fromdual;輸出結(jié)果:fun。upper(char):轉(zhuǎn)換為大寫(xiě)，示例：selectupper("sun")fromdual;輸出結(jié)果:SUN。

ltrim(char,set):左剪裁，示例：selectltrim("xyzadams","xyz")fromdual;輸出結(jié)果:adams。rtrim(char,set):右剪裁，示例：selectrtrim("xyzadams","ams")fromdual;輸出結(jié)果:xyzad。translate(char,from,to):按字符翻譯，示例：selecttranslate("jack","abcd","1234")fromdual;輸出結(jié)果:j13k。

replace(char,search_str,replace_str):字符串替換，示例：selectreplace("jackandjue","j","bl")fromdual;輸出結(jié)果:blackandblue。

instr(char,substr[,pos1,pos2]):查找子字串位置。

示例：selectinstr("vorldwide","d")fromdual;輸出結(jié)果:5。pos1為可選，表示從第幾個(gè)位置查找。pos2為可選，表示從第幾次出現(xiàn)的位置找。substr(char,pos,len):取子字符串，示例：selectsubstr("abcdefg",3,2)fromdual;輸出結(jié)果:cd。concat(char1,char2):連接字符串，示例：selectconcat("Hello","world")fromdual;輸出結(jié)果:Helloworld。

chr:此函數(shù)根據(jù)Ascii碼返回對(duì)應(yīng)的字符，示例：selectchr(45788),chr(53671),chr(50167),chr(65)fromdual;輸出結(jié)果:曹學(xué)明A。

ascii:此函數(shù)返回GBK編碼值，示例：selectascii("曹")cao,ascii("學(xué)")xue,ascii("明")Mingfromdual;輸出結(jié)果:457885367150167。

lpad和rpad：示例：selectlpad("function",15,"=")fromdual;輸出結(jié)果:=======function。而rpad則相反，字符串填充在右邊。

trim:此函數(shù)從字符串的開(kāi)頭或結(jié)尾(或開(kāi)頭和結(jié)尾)剪裁特定的字符，默認(rèn)剪裁空格。如果加上leading選項(xiàng)時(shí)與ltrim函數(shù)相似。指定trailing時(shí)和rtrim函數(shù)相似。示例：selecttrim(9from999992598899)fromdual;輸出結(jié)果:25988。

示例：selecttrim(leading9from999992598899)fromdual;輸出結(jié)果:2598899。示例：selecttrim(trailing9from999992598899)fromdual;輸出結(jié)果:9999925988。

length:此函數(shù)返回字符串的長(zhǎng)度，示例：selectlength("frances")fromdual;輸出結(jié)果:7。decode:示例：selectdeptno,dname,decode(loc,"NEWYORK","紐約","BOSTON","波士頓")fromscott.dept;此示例將替換顯示loc列的結(jié)果,結(jié)果為"NEWYORK"的替換為"紐約"，"BOSTON"的替換為"波士頓"。

GREATEST/least:返回一組表達(dá)式中的最大值/最小值,即比較字符的編碼大小.示例：selectgreatest("AA","AB","AC")fromdual;輸出結(jié)果:AC。selectleast("AA","AB","AC")fromdual;輸出結(jié)果:AA。selectgreatest("啊","安","天")fromdual;輸出結(jié)果:天。selectleast("啊","安","天")fromdual;輸出結(jié)果:啊。3、數(shù)字函數(shù)：

abs(n):取絕對(duì)值，示例：selectabs(-15)fromdual;輸出結(jié)果:15。ceil(n):向上取整，示例：selectceil(44.778)fromdual;輸出結(jié)果:45。sign(n):取符號(hào)，示例：selectsign(-2)fromdual;輸出結(jié)果:-1。

floor(n):向下取整，示例：selectfloor(200.88)fromdual;輸出結(jié)果:200。power(m,n):m的n次冪，示例：selectpower(5,3)fromdual;輸出結(jié)果:125。mod(m,n):取余數(shù)，示例：selectmod(10,3)fromdual;輸出結(jié)果:1。

round(m,n):四舍五入，示例：selectround(100.256,2)fromdual;輸出結(jié)果:100.26。trunc(m,n):截?cái)�，示例：selecttrunc(100.256,2)fromdual;輸出結(jié)果:100.25。sqrt(n):平方根，示例：selectsqrt(4)fromdual;輸出結(jié)果:2。4、轉(zhuǎn)換函數(shù)：

to_char(d|n[,fmt]):其中d是日期，n是數(shù)字，fmt指定日期或數(shù)字的格式。

示例：selectto_char(sysdate,"yyyy"年"fmmm"月"fmdd"日"hh24:mi:ss")fromdual;輸出結(jié)果:201*年9月09日20:44:27。

selectto_char(sysdate,"yyyy/mm/ddhh24:mi:ss")fromdual;selectto_char(sysdate,"yyyy-mm-ddhh24:mi:ss")fromdual;

selectto_char(sysdate,"yyyy"年"mm"月"dd"日"hh24"時(shí)"mi"分"ss"秒"")fromdual;示例：selectto_char(sal,"$99999")fromemp;輸出結(jié)果:$1600。

to_date(char[,fmt]):此函數(shù)將char或varchar2數(shù)據(jù)類(lèi)型轉(zhuǎn)換為日期數(shù)據(jù)類(lèi)型。示例：selectto_date("201*-12-06","yyyy-mm-dd")fromdual;輸出結(jié)果:06-12月-05。selectto_date("201*/08/1020:08:08","yyyy/mm/ddhh24:mi:ss")fromdual;selectto_date("201*-08-1020:08:08","yyyy/mm/ddhh24:mi:ss")fromdual;

selectto_date("201*年08月10日20時(shí)08分08秒","yyyy"年"mm"月"dd"日"hh24"時(shí)"mi"分"ss"秒"")fromdual;

to_number(char):此函數(shù)將包含數(shù)字的字符串轉(zhuǎn)換為number數(shù)據(jù)類(lèi)型，通常不用這么做，因?yàn)镺racle可以對(duì)數(shù)字字符串進(jìn)行隱式轉(zhuǎn)換。

示例：selectsqrt(to_number("100"))fromdual;輸出結(jié)果:10。5、其它函數(shù)：

nvl(expression1,expression2):如果expression1為NULL，則nvl返回expression2。

nvl2(expression1,expression2,expression3):如果expression1不是NULL，則nvl2返回expression2，如果expression1是NULL，則返回expression3。nullif(expr1,expr2):此函數(shù)比較兩個(gè)表達(dá)式，如果它們相等，則返回空值，否則返回expr1。nullif函數(shù)等價(jià)于以下的case表達(dá)式：

casewhenexpr1=expr2thennullelseexpr1end6、分組函數(shù)：

avg:此函數(shù)返回指定列值的平均值，示例：selectavg(sal)fromemp;輸出結(jié)果:2073.21429。min:此函數(shù)返回指定列值的最小值，示例：selectmin(sal)fromemp;輸出結(jié)果:800。max:此函數(shù)返回指定列值的最大值，示例：selectmax(sal)fromemp;輸出結(jié)果:5000。sum:此函數(shù)返回指定列值的總和，示例：selectsum(sal)fromemp;輸出結(jié)果:29025。count:此函數(shù)是為了計(jì)算行數(shù)，它可以接受3種不同的參數(shù)示例：selectcount(*)fromemp;輸出結(jié)果:14。示例：selectcount(列名)from表名;

示例：selectcount(distinct列名)from表名;

groupby:此子句用于將信息表劃分為組，按組進(jìn)行聚合運(yùn)算。select后面跟的列名只能是分組函數(shù)、groupby子句中出現(xiàn)的列或表達(dá)式。

示例：selectdeptno,max(sal)fromempgroupby(deptno);查出每個(gè)部門(mén)的最高工資。示例：selectdeptno,count(*),sum(sal)fromscott.empgroupbydeptno;//groupby主要用來(lái)對(duì)一組數(shù)進(jìn)行統(tǒng)計(jì)

having:此子句用來(lái)指定groupby子句的檢索條件。

示例：selectdeptno,count(*),sum(sal)fromscott.empgroupbydeptnohavingcount(*)>=5;//having對(duì)分組統(tǒng)計(jì)再加限制條件

7、分析函數(shù)：只能出現(xiàn)在select列表或orderby子句中。

row_number:下面對(duì)所有員工的工資進(jìn)行排名，即使工資相同，其排名也不能相同。示例：selectename,job,deptno,sal,row_number()over(orderbysaldesc)as排名fromscott.emp;

下面對(duì)所有員工的工資按部門(mén)進(jìn)行排名，即使工資相同，排名也不同。

示例：selectename,job,deptno,sal,row_number()over(partitionbydeptnoorderbysaldesc)as排名fromscott.emp;

rank:此函數(shù)計(jì)算一個(gè)值在一組值中的排位，排位是以1開(kāi)頭的連續(xù)整數(shù)，如果兩行的序數(shù)為1，則沒(méi)有序數(shù)2，下行的序數(shù)為3。

下面根據(jù)員工的工資和傭金對(duì)員工在每個(gè)部門(mén)中進(jìn)行排位。相同的工資排位相同，并且排位不連續(xù)。

示例：selectename,sal,comm,deptno,rank()over(partitionbydeptnoorderbysaldesc,comm)排名fromscott.emp;

dense_rank:此函數(shù)計(jì)算一個(gè)行在一組有序行中的排位，排位是以1開(kāi)頭的連續(xù)整數(shù)，具有相同值的排位相同，并且排位是連續(xù)的。

下面首先選擇所有在accounting或research部門(mén)中工作的員工的部門(mén)名稱(chēng)、員工姓名和工資，

然后分別計(jì)算每個(gè)員工的工資在部門(mén)中的排位，相等的工次排位相同示例：selectd.dname,e.ename,e.sal,dense_rank()over(partitionbye.deptnoorderbye.saldesc)排名

fromempe,deptdwheree.deptno=d.deptno;

三、鎖和表分區(qū)

鎖定是數(shù)據(jù)庫(kù)用來(lái)控制共享資源并發(fā)訪問(wèn)的機(jī)制。

Oracle提供以確保在多用戶(hù)環(huán)境下數(shù)據(jù)的完整性和一致性。鎖的兩種級(jí)別：(只有在提交或回滾后才能釋放鎖定)

(1)行級(jí)鎖：是一種排他鎖，防止其他事務(wù)修改此行，但是不會(huì)阻止讀取此行的操作。在使用Insert、Update、Delete和Select...ForUpdate等語(yǔ)句時(shí)，Oracle會(huì)自動(dòng)應(yīng)用行級(jí)鎖定。Select...ForUpdate語(yǔ)法為：

Select...ForUpdate[OFcolumn_list][WAITn|NOWAIT]

其中：OF子句用于指定即將更新的列，即鎖定行上的特定列。WAIT子句指定等待其他用戶(hù)釋放鎖的秒數(shù)，防止無(wú)限期的等待。NOWAIT為不等待。示例1：演示如何鎖定deptno值為10的所有行。

select*fromdeptwheredeptno=10forupdateofdname,loc;

(2)表級(jí)鎖：將保護(hù)表數(shù)據(jù)，在事務(wù)處理過(guò)程中，表級(jí)鎖會(huì)限制對(duì)整個(gè)表的訪問(wèn)。表級(jí)鎖用來(lái)限制對(duì)表執(zhí)行添加、更新和刪除等修改操作。語(yǔ)法：

LOCKTABLEINMODE[NOWAIT];其中：table_name是要被鎖定的表的名稱(chēng)。lock_mode是鎖定的模式。表級(jí)鎖的模式：

1、行共享(ROWSHARE,RS)：允許其他用戶(hù)訪問(wèn)和鎖定該表，但是禁止排他鎖鎖定整個(gè)表。

2、行排他(ROWEXCLUSIVE,RX)：與行共享模式相同，同時(shí)禁止其他用戶(hù)在此表上使用共享鎖。使用Select...Forupdate語(yǔ)句會(huì)自動(dòng)應(yīng)用行排他。

3、共享(SHARE,S)：共享鎖將鎖定表，僅允許其他用戶(hù)查詢(xún)表中的行，但不允許插入、更新或刪除行。多個(gè)用戶(hù)可以同時(shí)在同一張表中放置共享鎖即允許資源共享。但是這樣極容易造成死鎖。

4、共享行排他(SHAREROWEXCLUSIVE,SRX)：執(zhí)行比共享表鎖更多的限制。防止其他事務(wù)在表上應(yīng)用共享鎖、共享行排他鎖以及排他鎖。

5、排他(EXCLUSIVE,X)：對(duì)表執(zhí)行最大限制。除了允許其他用戶(hù)查詢(xún)?cè)摫淼挠涗�，排他鎖防止其他事務(wù)對(duì)表做任何更改或在表上應(yīng)用任何類(lèi)型的鎖。示例2：演示如何以共享模式鎖定表。locktabledeptinsharemodenowait;表分區(qū)的優(yōu)點(diǎn)：

改善表的查詢(xún)性能;表更容易管理;便于備份和恢復(fù);提高數(shù)據(jù)安全性。注意：要分區(qū)的表不能具有Long和LongRaw數(shù)據(jù)類(lèi)型的列。四種分區(qū)方法：

1、范圍分區(qū)：根據(jù)表的某個(gè)列或一組列的值范圍，決定將該數(shù)據(jù)存儲(chǔ)在哪個(gè)分區(qū)上。語(yǔ)法如下：

在CreateTable語(yǔ)句后增加

PARTITIONBYRANGE(column_name)(

PARTITIONpart1VALUELESSTHAN(range1)[TABLESPACEtbs1],PARTITIONpart2VALUELESSTHAN(range2)[TABLESPACEtbs2],....

PARTITIONpartNVALUELESSTHAN(MAXVALUE)[TABLESPACEtbsN]);

其中：column_name是以其為基礎(chǔ)創(chuàng)建范圍分區(qū)的列，特定行的該列值稱(chēng)為分區(qū)鍵。part1...partN是分區(qū)的名稱(chēng)。

range1...MAXVALUE是分區(qū)的邊界值。

tbs1...tbsN是分區(qū)所在的表空間，TABLESPACE子句是可選項(xiàng)。示例3：

createtablet_emp(

empnonumber(4),enamevarchar2(30),salnumber)

partitionbyrange(empno)(

partitione1valueslessthan(1000)tablespaceemp1,也可以than(to_date("201*-01-01","yyyy-mm-dd"))

partitione2valueslessthan(201*)tablespaceemp2,也可以than(to_date("201*-01-01","yyyy-mm-dd"))

partitione3valueslessthan(maxvalue)tablespaceemp3);

2、散列分區(qū)：語(yǔ)法有兩種如下

PARTITIONBYHASH(column_name)

PARTITIONSnumber_of_partitions[STOREIN(tablespace_list)];或

PARTITIONBYHASH(column_name)(

PARTITIONpart1[TABLESPACEtbs1],PARTITIONpart2[TABLESPACEtbs2],...

PARTITIONpartN[TABLESPACEtbsN]);

其中：column_name是以其為基礎(chǔ)創(chuàng)建散列分區(qū)的列。

number_of_partitions是散列分區(qū)的數(shù)目，使用這種方法系統(tǒng)會(huì)自動(dòng)生成分區(qū)的名稱(chēng)。tablespace_list指定分區(qū)使用的表空間，如果分區(qū)數(shù)目比表空間的數(shù)目多，分區(qū)將會(huì)以循環(huán)的方式分配到表空間中。part1...partN是分區(qū)的名稱(chēng)。

tbs1...tbsN是分區(qū)所在的表空間，TABLESPACE子句是可選項(xiàng)。示例4：自動(dòng)分配4個(gè)散列分區(qū)，

可以使用selectpartition_name,HIGH_VALUEfromuser_tab_partitionswheretable_name=upper("t_emp")查詢(xún)分區(qū)名createtablet_emp(

empnonumber(4),enamevarchar2(30),salnumber)

partitionbyhash(empno)partitions4;

3、復(fù)合分區(qū)：是范圍分區(qū)和散列分區(qū)的結(jié)合。在創(chuàng)建復(fù)合分區(qū)時(shí)，先根據(jù)范圍對(duì)數(shù)據(jù)進(jìn)行分區(qū)，然后在這些分區(qū)內(nèi)創(chuàng)建散列子分區(qū)。語(yǔ)法如下：

PARTITIONBYRANGE(column_name1)SUBPARTITIONBYHASH(column_name2)

SUBPARTITIONSnumber_of_partitions[STOREIN(tablespace_list)](

PARTITIONpart1VALUELESSTHAN(range1)[TABLESPACEtbs1],PARTITIONpart2VALUELESSTHAN(range2)[TABLESPACEtbs2],....

PARTITIONpartNVALUELESSTHAN(MAXVALUE)[TABLESPACEtbsN]);

其中：column_name1是以其為基礎(chǔ)創(chuàng)建范圍分區(qū)的列。column_name2是以其為基礎(chǔ)創(chuàng)建散列分區(qū)的列。number_of_partitions是要?jiǎng)?chuàng)建的子分區(qū)的數(shù)目。part1...partN是分區(qū)的名稱(chēng)。

range1...MAXVALUE是范圍分區(qū)的邊界值

示例5：將雇員表先按照雇傭時(shí)間hiredate進(jìn)行了范圍分區(qū)，然后再把每個(gè)分區(qū)分為2個(gè)子hash分區(qū)，此表一共是6個(gè)分區(qū)。createtablet_emp(

empnonumber(4),enamevarchar2(30),hiredatedate)

partitionbyrange(hiredate)subpartitionbyhash(empno)subpartitions2(

partitione1valueslessthan(to_date("201*0501","YYYYMMDD")),partitione2valueslessthan(to_date("201*1001","YYYYMMDD")),partitione3valueslessthan(maxvalue));

4、列表分區(qū)：此分區(qū)允許用戶(hù)明確地控制行到分區(qū)的映射。語(yǔ)法如下：

PARTITIONBYLIST(column_name)(

PARTITIONpart1VALUES(values_list1),PARTITIONpart2VALUES(values_list2),....

PARTITIONpartNVALUES(DEFAULT));

其中：column_name是以其為基礎(chǔ)創(chuàng)建列表分區(qū)的列。part1...partN是分區(qū)的名稱(chēng)。

values_list是對(duì)應(yīng)分區(qū)的分區(qū)鍵值的列表。DEFAULT關(guān)鍵字允許存儲(chǔ)前面的分區(qū)不能存儲(chǔ)的記錄。示例6：

createtablet_emp1(

empnonumber(4),enamevarchar2(30),locationvarchar2(30))

partitionbylist(location)(

partitione1values("北京"),

partitione2values("上海","天津","重慶"),partitione3values("廣東","福建"),);

要查詢(xún)表分區(qū)中的數(shù)據(jù)行：select*fromtable_namePARTITION(p1);注：p1是分區(qū)名。分區(qū)維護(hù)操作：

1、添加分區(qū)：ALTER...ADDPARTITION語(yǔ)句用于在現(xiàn)有的最后一個(gè)分區(qū)之后添加新的分區(qū)。

示例7：演示如何將名為E4的新分區(qū)添加到示例3中創(chuàng)建的t_emp表。ALTERTABLEt_empADDPARTITIONE4VALUESLESSTHAN(3000);在此請(qǐng)注意：上例公適用于已使用特定的鍵值定義了最后一個(gè)分區(qū)的表。如果要在表的開(kāi)始或中間位置添加分區(qū)，或者最高分區(qū)的分區(qū)

邊界是MAXVALUE，則應(yīng)使用SPLITPARTITION語(yǔ)句。

2、刪除分區(qū)：使用ALTERTABLE...DROPPARTITION語(yǔ)句。

示例8：演示了如何刪除t_emp表的E4分區(qū)。刪除分區(qū)時(shí)，分區(qū)中的數(shù)據(jù)也隨之刪除。ALTERTABLEt_empDROPPARTITIONE4;

3、截?cái)喾謪^(qū)：使用ALTERTABLE...TRUNCATEPARTITION語(yǔ)句來(lái)截?cái)喾謪^(qū)，只刪除表分區(qū)中的所有記錄。

示例9：演示了如何刪除t_emp表中e3的分區(qū)的所有記錄。ALTERTABLEt_empTRUNCATEPARTITIONe3;

4、合并分區(qū)：可以將范圍分區(qū)或復(fù)合分區(qū)表的兩個(gè)相鄰分區(qū)連接起來(lái)。結(jié)果分區(qū)將繼承被合并的兩個(gè)分區(qū)的較高上界。語(yǔ)法如下：

ALTERTABLEtable_nameMERGEPARTITIONSpartitions_name,partitions_nameINTOPARTITIONpartition_name;

示例10：演示了如何將e1和e2合并成一個(gè)e2分區(qū)。

ALTERTABLEt_empMERGEPARTITIONSe1,e2INTOPARTITIONe2;

5、拆分分區(qū)：使用SPLITPARTITION語(yǔ)句在表的開(kāi)頭或中間添加分區(qū)。拆分分區(qū)允許用戶(hù)將一個(gè)分區(qū)拆分為兩個(gè)分區(qū)。語(yǔ)法如下：ALTERTABLEtable_nameSPLIYPARTITIONpartition_nameAT(value)INTO(PARTITIONpartition1,PARTITIONpartition2);

示例11：演示了如何將t_emp表中的e3分區(qū)拆分為e31和e32兩個(gè)分區(qū)。

ALTERTABLEt_empSPLITPARTITIONe3AT(Date"201*-01-01")INTO(PARTITIONe31,PARTITIONe32);

6、重新命名拆分后的分區(qū):

ALTERTABLEt_empRENAMEPARTITIONe31TOP3;ALTERTABLEt_empRENAMEPARTITIONe32TOP4;

可以查詢(xún)字典視圖user_tab_partitions來(lái)查看用戶(hù)所創(chuàng)建的分區(qū)的詳細(xì)信息示例12：selecttable_name,partition_name,high_valuefromuser_tab_partitions;

可以查詢(xún)字典視圖dba_tab_subpartitions來(lái)查看用戶(hù)所創(chuàng)建的子分區(qū)的詳細(xì)信息

示例13：selecttable_name,partition_name,subpartition_namefromdba_tab_subpartitions;

四、數(shù)據(jù)庫(kù)對(duì)象

表、視圖、序列、過(guò)程、函數(shù)、程序包，甚至其它同義詞都可以創(chuàng)建同義詞。

1、同義詞：私有同義詞、公有同義詞。私有同義詞只能被當(dāng)前模式的用戶(hù)訪問(wèn)。私有同義詞名稱(chēng)不可與當(dāng)前模式的對(duì)象名稱(chēng)相同。要在自身的模式創(chuàng)建私有同義詞，

用戶(hù)必須擁有CreateSynonym系統(tǒng)權(quán)限。要在其它用戶(hù)模式創(chuàng)建私有同義詞，用戶(hù)必須擁有CreateAnySynonym系統(tǒng)權(quán)限。

公有同義詞可被所有的數(shù)據(jù)庫(kù)用戶(hù)訪問(wèn)。要?jiǎng)?chuàng)建公有同義詞，用戶(hù)必須擁有CreatePublicSynonym系統(tǒng)權(quán)限。

創(chuàng)建私有同義詞語(yǔ)法：

Create[ORREPLACE]SYNONYM[schema.]synonym_nameFOR[schema.]object_name;其中：ORREPLACE表示在同義詞存在的情況下替換該同義詞。synonym_name表示要?jiǎng)?chuàng)建的同義詞的名稱(chēng)。

object_name指定要為之創(chuàng)建同義詞的對(duì)象的名稱(chēng)。示例1：createsynonyms_empforscott.emp;

創(chuàng)建公有同義詞語(yǔ)法：

CreatePUBLICSYNONYMsynonym_nameFOR[schema.]object_name;示例2：createpublicsynonymemp_synfromscott.emp;

可以查詢(xún)字典視圖User_Synonyms來(lái)查看用戶(hù)所創(chuàng)建的同義詞的詳細(xì)信息

刪除同義詞語(yǔ)法：DropSynonymssynonym_name;刪除公有同義詞加上一個(gè)Public此命令只刪除同義詞，不會(huì)刪除對(duì)應(yīng)的表。

2、序列：是用來(lái)生成唯一、連續(xù)的整數(shù)的數(shù)據(jù)庫(kù)對(duì)象。序列通常用來(lái)自動(dòng)生成主鍵或唯一鍵的值。

創(chuàng)建序列語(yǔ)法如下：

CreateSEQUENCEsequence_name[STARTWITHinteger][INCREMENTBYinteger]

[MAXVALUEinteger|NOMAXVALUE][MINVALUEinteger|NOMINVALUE][CYCLE|NOCYCLE]

[CACHEinterger|NOCACHE];

其中：STARTWITH是指定要生成的第一個(gè)序列號(hào)。對(duì)于升序序列，其默認(rèn)值為序列的最小值。對(duì)于降序序列，其默認(rèn)值為序列的最大值。

INCREMENTBY是用于指定序列號(hào)之間的間隔。其默認(rèn)值為1。如果integer為正值，則生成的序列將按升序排列，否則按降序排列。MAXVALUE指定序列可以生成的最大值。

NOMAXVALUE這是默認(rèn)選項(xiàng)，將升序序列的最大值設(shè)為10的27次冪，將降序序列的最大值設(shè)為-1。

MINVALUE指定序列的最小值。MINVALUE必須小于或等于STARTWITH的值，并且必須小于MAXVALUE。

NOMINVALUE這是默認(rèn)選項(xiàng)，將升序序列的最小值設(shè)為1，將降序序列的最小值設(shè)為-10的26次冪。

CYCLE指定序列在達(dá)到最大值或最小值后，將繼續(xù)從頭開(kāi)始生成值。

NOCYCLE這是默認(rèn)選項(xiàng)。指定序列在達(dá)到最大值或最小值后，將不能再繼續(xù)生成值。CACHE使用CACHE選項(xiàng)可以預(yù)先分配一組序列號(hào)，并將其保留在內(nèi)存中，這樣可以更快的訪問(wèn)序列號(hào)。

NOCACHE此項(xiàng)則不會(huì)為加快速度而預(yù)先分配序列號(hào)。如果在創(chuàng)建序列時(shí)忽略了CACHE和NOCACHE選項(xiàng)，Oracle將默認(rèn)緩存20個(gè)序列號(hào)。示例3：CreateSEQUENCEtoys_seqSTARTWITH10INCREMENTBY2MAXVALUE201*MINVALUE10NOCYCLECACHE30;

訪問(wèn)序列：可以通過(guò)CURRVAL和NEXTVAL偽列來(lái)訪問(wèn)該序列的值。示例4：演示從序列toys_seq中選擇值插入toys表中的toyid列。執(zhí)行成功將會(huì)在該表的toyid列插入值"P10"和"P12"。

INSERTINTOtoys(toyid,toyname,toyprice)values("p"||toys_seq.NEXTVAL,"TWENTY",25);INSERTINTOtoys(toyid,toyname,toyprice)values("p"||toys_seq.NEXTVAL,"MAGICPENCIL",75);

示例5：演示如何查看序列當(dāng)前值

Selecttoys_seq.CURRVALfromdual;

更改序列：ALTERSEQUENCE命令用于設(shè)置或刪除MINVALUE或MAXVALUE、修改增量值、修改緩存中的序列號(hào)的數(shù)目。

修改序列語(yǔ)法如下：注意，不能修改序列的STARTWITH參數(shù)。在修改序列時(shí)，應(yīng)注意升序序列的最小值應(yīng)小于最大值。

ALTERSEQUENCE[schema.]sequence_name[INCREMENTBYinteger]

[MAXVALUEinteger|NOMAXVALUE][MINVALUEinteger|NOMINVALUE][CYCLE|NOCYCLE]

[CACHEinterger|NOCACHE];

示例6：演示如何設(shè)置一個(gè)新的MAXVALUE，并為toys_seq序列打開(kāi)了CYCLE。ALTERSEQUENCEtoys_seqMAXVALUE5000CYCLE;

可以查詢(xún)字典視圖User_Sequences來(lái)查看用戶(hù)所創(chuàng)建的序列的詳細(xì)信息

刪除序列語(yǔ)法：DropSEQUENCEtoys_seq;

[local]2[/local]

友情提示：本文中關(guān)于《Oracle使用學(xué)習(xí)總結(jié)》給出的范例僅供您參考拓展思維使用，Oracle使用學(xué)習(xí)總結(jié)：該篇文章建議您自主創(chuàng)作。

來(lái)源：網(wǎng)絡(luò)整理 免責(zé)聲明：本文僅限學(xué)習(xí)分享，如產(chǎn)生版權(quán)問(wèn)題，請(qǐng)聯(lián)系我們及時(shí)刪除。

《Oracle使用學(xué)習(xí)總結(jié)》由互聯(lián)網(wǎng)用戶(hù)整理提供,轉(zhuǎn)載分享請(qǐng)保留原作者信息,謝謝!
鏈接地址：http://m.hmlawpc.com/gongwen/712073.html

• 上一篇：oracle總結(jié)
• 下一篇：oracle實(shí)訓(xùn)總結(jié)