Oracle使用學(xué)習(xí)總結(jié)
Oracle使用學(xué)習(xí)經(jīng)驗(yàn)總結(jié)
張浩博客
sql語句部分
1.新型企業(yè)級智能數(shù)據(jù)管理系統(tǒng)---SybaseASE12.5(AdaptiveServerEn
terprise)
2.**17260,"Can"trun%1!fromwithinatransaction."
3.在創(chuàng)建存儲(chǔ)過程CREAT之前編寫存儲(chǔ)過程德錯(cuò)誤編號說明4.charindex("sa_role",show_role())
5.begin
6.@@trancount
Oracle系統(tǒng)安裝配置
1.修改sybase數(shù)據(jù)庫sa用戶密碼
2.3.env查看環(huán)境變量
4.linux中.bash_profile和.bashrc的什么區(qū)別5.數(shù)據(jù)庫中執(zhí)行“存儲(chǔ)”過程。
6.bcp導(dǎo)入、導(dǎo)出數(shù)據(jù),備份數(shù)據(jù)庫、備份表格數(shù)據(jù)、備份數(shù)據(jù)
●bcp
7.附錄1、英文錯(cuò)誤碼描述:(靈活運(yùn)用。。。
1.Auserwiththesamenamealreadyexistsinthedatabase在數(shù)據(jù)庫中已經(jīng)存在相同的一個(gè)用戶2.isnotavalidname不是一個(gè)可用的名字
3.Useralreadyhasaloginunderadifferentname.用戶已經(jīng)用一個(gè)不同的名字登錄了4.Nologinwiththespecifiednameexists沒有登錄
5.Timerangenamemustbenon-NULL時(shí)間范圍名字必須不為空6.Limittypemustbenon-NULL限制類型必須不為空7.Unknownlimittype不可知的限制類型8.Ge獲得開始結(jié)束時(shí)間從當(dāng)前的范圍9.
8.附錄2、錯(cuò)誤語句:
9.附錄3、錯(cuò)誤語句:
10.附錄4、錯(cuò)誤語句:
11.附錄5、錯(cuò)誤語句:自我總結(jié)書籍和網(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ù)庫常用的命令集錦
今日開始研究oracle,搜索到了一個(gè)好東東,拿出來與大家一同分享。下面是摘抄的部分:[local]2[/local]
ORACLE相關(guān)語法及命令一、Oracle入門理論知識:
Oracle的物理組件有三個(gè):
(1)數(shù)據(jù)文件數(shù)據(jù)文件是用于存儲(chǔ)數(shù)據(jù)庫數(shù)據(jù)的文件,如表、索引數(shù)據(jù)。每個(gè)Oracle數(shù)據(jù)庫有一個(gè)或多個(gè)物理數(shù)據(jù)文件,一個(gè)數(shù)據(jù)文件只能與一個(gè)數(shù)據(jù)庫關(guān)聯(lián)。(2)日志文件用于記錄對數(shù)據(jù)庫進(jìn)行的修改信息,日志文件主要用于在數(shù)據(jù)庫出現(xiàn)故障時(shí)實(shí)施數(shù)據(jù)庫恢復(fù)。
(3)控制文件控制文件是記錄數(shù)據(jù)庫物理結(jié)構(gòu)的二進(jìn)制文件,每個(gè)Oracle數(shù)據(jù)庫都含有一個(gè)控制文件。
Oracle的邏輯組件:
表空間(TableSpace)表空間是數(shù)據(jù)庫最大的邏輯單位,一個(gè)數(shù)據(jù)庫至少包含一個(gè)表空間,一個(gè)表空間包含一個(gè)或多個(gè)段等等。段(Segment)段存在于表空間中,分成4類,數(shù)據(jù)段、索引段、回退段、臨時(shí)段。區(qū)(Extent)區(qū)是磁盤空間分配最小單位,由連續(xù)的數(shù)據(jù)塊組成,一個(gè)或多個(gè)區(qū)構(gòu)成段,區(qū)只能存在于一個(gè)數(shù)據(jù)文件中。
數(shù)據(jù)塊(DataBlock)數(shù)據(jù)塊是數(shù)據(jù)庫中最小的數(shù)據(jù)組織單位與管理單位,Oracle數(shù)據(jù)庫中的數(shù)據(jù)存儲(chǔ)于數(shù)據(jù)塊中,取值范圍2K-64K之間。
模式(schema)模式是對用戶所創(chuàng)建的數(shù)據(jù)庫對象的總稱,又稱為用戶模式。概念:
內(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è)基本組件。又稱為共享全局區(qū),它用來存儲(chǔ)數(shù)據(jù)庫信息,并由多個(gè)數(shù)據(jù)庫進(jìn)程共享?煞譃楣蚕沓、數(shù)據(jù)緩沖區(qū)及日志緩沖區(qū)。
(1)共享池是對SQL、PL\\SQL程序進(jìn)行語法分析、編譯、執(zhí)行的內(nèi)存區(qū)域。共享池由庫緩存和數(shù)據(jù)字典緩存組成。其中,庫緩存含有最近執(zhí)行的SQL、PL\\SQL語句的分析碼和執(zhí)行計(jì)劃;數(shù)據(jù)字典緩存含有從數(shù)據(jù)字典中得到的表、索引、列定義和權(quán)限等信息。(2)數(shù)據(jù)緩沖區(qū)數(shù)據(jù)緩沖區(qū)用于存儲(chǔ)從磁盤數(shù)據(jù)文件中讀入的數(shù)據(jù),所有用戶共享。(3)日志緩沖區(qū)日志記錄數(shù)據(jù)庫的所有修改信息,主要用于恢復(fù)數(shù)據(jù)。
2、程序全局區(qū)(PGA)服務(wù)器進(jìn)程啟動(dòng)時(shí)分配該內(nèi)存區(qū)。PGA為非共享區(qū),只能單個(gè)進(jìn)程使用,當(dāng)一個(gè)用戶會(huì)話結(jié)束后,PGA釋放。
用戶進(jìn)程(PGA)發(fā)送SQL語句到共享全局區(qū)(SGA),先在共享池的庫緩存中查詢是否存在所需的數(shù)據(jù)塊,如果存在就在數(shù)據(jù)字典中讀取相應(yīng)的數(shù)據(jù)塊,如果不存在就由服務(wù)器進(jìn)程(DBWR)來IO數(shù)據(jù)庫
語法知識:
創(chuàng)建表空間的語法如下:
CREATETABLESPACEtablespacenameDATAFILE"d:\\filename.DBF"[SIZEint[KB|MB]][AUTOEXTEND[OFF|ON]];
tablespacename是需創(chuàng)建的表空間名稱。
DATAFILE指定組成表空間的一個(gè)或多個(gè)數(shù)據(jù)文件,當(dāng)有多個(gè)數(shù)據(jù)文件時(shí)使用逗號分隔。filename是表空間中數(shù)據(jù)文件的路徑和名稱。
SIZE指定文件的大小,用K指定千字節(jié)大小,用M指定兆字節(jié)大小。AUTOEXTEND子句用來啟用或禁用數(shù)據(jù)文件的自動(dòng)擴(kuò)展。Oracle默認(rèn)用戶:
用戶名:sys默認(rèn)密碼:chage_on_install用來管理擁有Oracle數(shù)據(jù)字典文件用戶名:system默認(rèn)密碼:manager用來管理擁有數(shù)據(jù)字典視圖對象用戶名:scott默認(rèn)密碼:tiger示例用戶,包括emp、dept等表連接Oracle:在控制臺(tái)下輸入sqlplus用戶名/密碼回車或sqlplusw回車相關(guān)命令:
disconn//退出當(dāng)前登錄conn用戶名/密碼//連接Oracle
alteruser用戶名identifiedby密碼//修改用戶口令dropuser用戶名cascade;//刪除用戶
alteruser用戶名accountlock;//給某個(gè)用戶加鎖alteruser用戶名accountunlock;//給某個(gè)用戶解鎖ed回車://打開緩沖區(qū)
/回車://執(zhí)行緩沖區(qū)中的語句
createuser用戶名identifiedby密碼[passwordexpire]
[defaulttablespace表空間名][temporarytablespace臨時(shí)表空間名];//創(chuàng)建用戶相關(guān)權(quán)限:
grantconnecttoscott;//connect角色將允許用戶創(chuàng)建數(shù)據(jù)庫并在數(shù)據(jù)庫中創(chuàng)建表或其他對象grantresourcetoscott;//resource角色將允許用戶使用數(shù)據(jù)庫中的空間grantcreatesequencetoscott;//createsequence權(quán)限將允許用戶創(chuàng)建序列,此權(quán)限包含在connect連接角色中
grantselectonemptoscott;//將emp表的查詢權(quán)限授予用戶scott
grantupdate(vencode,venname)on表名toscott;//將特定列的更新權(quán)限授予用戶scottgrant權(quán)限on表名to用戶名withgrantoption;//接受該權(quán)限的用戶可以將此權(quán)限授予其他用戶
revokeselect,updateon表名from用戶名;//收回相應(yīng)的權(quán)限二、SQL查詢和SQL函數(shù)SQL支持如下類別的命令:
數(shù)據(jù)定義語言:create(創(chuàng)建)、alter(更改)、drop(刪除)和truncate(截?cái)?命令。數(shù)據(jù)操縱語言:insert(插入)、select(選擇)、delete(刪除)和update(更新)命令。事務(wù)控制語言:commit(提交)、savepoint(保存點(diǎn))和rollback(回滾)命令。數(shù)據(jù)控制語言:grant(授予)和revoke(回收)命令。數(shù)據(jù)類型:
char:長度在1到201*個(gè)字節(jié),聲明多少字節(jié)在內(nèi)存中就占用多少字節(jié),輸入的值小于指定的長度時(shí)用空格填充。
varchar2:長度在1到4000個(gè)字節(jié),輸入的值是多少字節(jié),就占用多少字節(jié)。
long:長度在2GB,設(shè)置為此類型的列時(shí),要注意:一個(gè)表中只有一列可以為long類型,long類型列不能定義為唯一約束或主鍵約束,不能建立索引,過程或存儲(chǔ)過程不能接受long類型的參數(shù)。
number(p,s):其中p為精度,表示數(shù)字的總位數(shù),在1至38之間。s為范圍,表示小數(shù)點(diǎn)右邊數(shù)字的位數(shù),在-84至127之間。
date:日期類型,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ù)類型用于存儲(chǔ)基于字節(jié)的數(shù)據(jù),如二進(jìn)制數(shù)據(jù)或字節(jié)串,該類型最多能存儲(chǔ)201*個(gè)字節(jié),可以建立索引。
longraw:此數(shù)據(jù)類型用于可變長度的二進(jìn)制數(shù)據(jù),最多能存儲(chǔ)2GB。long數(shù)據(jù)類型的所有限制對longraw數(shù)據(jù)類型也同樣有效。
lob又稱為"大對象"數(shù)據(jù)類型,最多能存儲(chǔ)4GB的非結(jié)構(gòu)化信息。包括:
clob:clob代表CharacterLOB(字符LOB),它能存儲(chǔ)大量字符數(shù)據(jù)。如XML文檔。blob:blob代表BinaryLOB(二進(jìn)制LOB),它能存儲(chǔ)較大的二進(jìn)制對象,如圖形、視頻剪輯和聲音剪輯。
bfile:bfile代表BinaryFile(二進(jìn)制文件),它能夠?qū)⒍M(jìn)制文件存儲(chǔ)在數(shù)據(jù)庫外部的操作系統(tǒng)文件中。偽列:
rowid:selectrowid,ename,fromscott.empwhereempno="7900";
rownum:select*fromscott.empwhererownumaltertable表名dropcolumn列名;//刪除列
truncatetable表名;//中刪除記錄而不刪除結(jié)構(gòu),不使用事務(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è)查詢選定的所有不重復(fù)的行。
語法selectordernofromorder_masterUNIONselectordernofromorder_detail;unionall(聯(lián)合所有):此操作符合并兩個(gè)查詢選定的所有行,包括重復(fù)的行。
語法:selectorderno,enamefromorder_masterUNIONALLselectorderno,pronamefromorder_detailorderby2;
注意:在兩個(gè)select語句中指定的列名不必相同,但數(shù)據(jù)類型必須匹配。也可以對聯(lián)合查詢的結(jié)果進(jìn)行排序,使用OrderBy子句時(shí),它必須放在最后
一個(gè)select語句之后,而且必須指定列索引來排序,而不是指定列名,列索引是從1開始的整數(shù)。上述語法便是以proname的索引排序
intersect(交集):此操作符只返回兩個(gè)查詢都有的行。
語法:selectordernofromorder_masterINTERSECTselectordernofromorder_detail;
minus(減集):此操作符中返回由第一個(gè)查詢選定但是第二個(gè)查詢中沒有選定的行,也就是在第一個(gè)查詢結(jié)果中排除第二個(gè)查詢結(jié)果中出現(xiàn)的行。
語法:selectordernofromorder_masterMINUSselectordernofromorder_detail;查詢尚未交付的訂單
連接(||)操作符:
語法:select("供應(yīng)商"||venname||"的地址是"||venadd1||""||venadd2||""||venadd3)地址fromvendor_masterwherevencode="V002";將多個(gè)字符串合并為一個(gè)字符串。SQL函數(shù):
1、日期函數(shù):
add_months:此函數(shù)返回給指定的日期加上指定的月數(shù)后的日期值。語法為add_months(d,n),其中d是日期,n表示月數(shù)。
示例:selectadd_months(sysdate,2)fromdual;將當(dāng)前時(shí)間加上2個(gè)月后的日期值。months_between:此函數(shù)返回兩個(gè)日期之間的月數(shù)。語法為months_between(d1,d2),其中d1和d2是日期,如果d1大于d2,則結(jié)果為正數(shù);否則為負(fù)數(shù)。
last_day:此函數(shù)返回指定日期當(dāng)月的最后一天的日期值,語法為last_day(d),其中d表示日期。
示例:selectlast_day(sysdate)fromdual;返回當(dāng)前日期的月的最后一天,如果是9月就返回30-09月-07
round:此函數(shù)返回日期值,將日期四舍五入為格式模型指定的單位。語法為round(d,[fmt])。其中d是日期,fmt是格式模型。fmt是一個(gè)可選項(xiàng),日期默認(rèn)舍入為最靠近的那一天。如果指定格式為年"Year",則舍入到年的開始,即1月1日;如果格式為月"Month",則舍入到月的第一日;如果格式為周"Day",則舍入到最靠近的星期日。示例:selectround(sysdate,"month")fromdual;返回最接近的一個(gè)月。
next_day:此函數(shù)返回指定的下一個(gè)星期幾的日期。語法為next_day(d,day)。其中d表示日期,而day指周內(nèi)任何一天。
示例:selectnext_day(sysdate,"星期日")fromdual;返回下一個(gè)星期日的日期,也可以用1表示,以此類推,星期一以2表示。
trunc:此函數(shù)將指定日期截?cái)酁橛筛袷侥P椭付ǖ膯挝蝗掌冢cRound函數(shù)不同的是它只舍不入,語法為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í)間類型中的特定部分。語法為extract(fmtfromd),其中d是日期時(shí)間表達(dá)式,fmt是要提取的部分的格式。格式的取值可以是year,month,day,hour,minute,second,注意此處的格式不使用單引號。示例:selectextract(yearfromsysdate)fromdual;返回當(dāng)前的年份。2、字符函數(shù):
initcap(char):首字母大寫,示例:selectinitcap("hello")fromdual;輸出結(jié)果:Hello。lower(char):轉(zhuǎn)換為小寫,示例:selectlower("FUN")fromdual;輸出結(jié)果:fun。upper(char):轉(zhuǎn)換為大寫,示例: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碼返回對應(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ù)從字符串的開頭或結(jié)尾(或開頭和結(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ù)返回字符串的長度,示例: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):取絕對值,示例:selectabs(-15)fromdual;輸出結(jié)果:15。ceil(n):向上取整,示例:selectceil(44.778)fromdual;輸出結(jié)果:45。sign(n):取符號,示例: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ù)類型轉(zhuǎn)換為日期數(shù)據(jù)類型。示例: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ù)類型,通常不用這么做,因?yàn)镺racle可以對數(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è)部門的最高工資。示例:selectdeptno,count(*),sum(sal)fromscott.empgroupbydeptno;//groupby主要用來對一組數(shù)進(jìn)行統(tǒng)計(jì)
having:此子句用來指定groupby子句的檢索條件。
示例:selectdeptno,count(*),sum(sal)fromscott.empgroupbydeptnohavingcount(*)>=5;//having對分組統(tǒng)計(jì)再加限制條件
7、分析函數(shù):只能出現(xiàn)在select列表或orderby子句中。
row_number:下面對所有員工的工資進(jìn)行排名,即使工資相同,其排名也不能相同。示例:selectename,job,deptno,sal,row_number()over(orderbysaldesc)as排名fromscott.emp;
下面對所有員工的工資按部門進(jìn)行排名,即使工資相同,排名也不同。
示例:selectename,job,deptno,sal,row_number()over(partitionbydeptnoorderbysaldesc)as排名fromscott.emp;
rank:此函數(shù)計(jì)算一個(gè)值在一組值中的排位,排位是以1開頭的連續(xù)整數(shù),如果兩行的序數(shù)為1,則沒有序數(shù)2,下行的序數(shù)為3。
下面根據(jù)員工的工資和傭金對員工在每個(gè)部門中進(jìn)行排位。相同的工資排位相同,并且排位不連續(xù)。
示例:selectename,sal,comm,deptno,rank()over(partitionbydeptnoorderbysaldesc,comm)排名fromscott.emp;
dense_rank:此函數(shù)計(jì)算一個(gè)行在一組有序行中的排位,排位是以1開頭的連續(xù)整數(shù),具有相同值的排位相同,并且排位是連續(xù)的。
下面首先選擇所有在accounting或research部門中工作的員工的部門名稱、員工姓名和工資,
然后分別計(jì)算每個(gè)員工的工資在部門中的排位,相等的工次排位相同示例:selectd.dname,e.ename,e.sal,dense_rank()over(partitionbye.deptnoorderbye.saldesc)排名
fromempe,deptdwheree.deptno=d.deptno;
三、鎖和表分區(qū)
鎖定是數(shù)據(jù)庫用來控制共享資源并發(fā)訪問的機(jī)制。
Oracle提供以確保在多用戶環(huán)境下數(shù)據(jù)的完整性和一致性。鎖的兩種級別:(只有在提交或回滾后才能釋放鎖定)
(1)行級鎖:是一種排他鎖,防止其他事務(wù)修改此行,但是不會(huì)阻止讀取此行的操作。在使用Insert、Update、Delete和Select...ForUpdate等語句時(shí),Oracle會(huì)自動(dòng)應(yīng)用行級鎖定。Select...ForUpdate語法為:
Select...ForUpdate[OFcolumn_list][WAITn|NOWAIT]
其中:OF子句用于指定即將更新的列,即鎖定行上的特定列。WAIT子句指定等待其他用戶釋放鎖的秒數(shù),防止無限期的等待。NOWAIT為不等待。示例1:演示如何鎖定deptno值為10的所有行。
select*fromdeptwheredeptno=10forupdateofdname,loc;
(2)表級鎖:將保護(hù)表數(shù)據(jù),在事務(wù)處理過程中,表級鎖會(huì)限制對整個(gè)表的訪問。表級鎖用來限制對表執(zhí)行添加、更新和刪除等修改操作。語法:
LOCKTABLEINMODE[NOWAIT];其中:table_name是要被鎖定的表的名稱。lock_mode是鎖定的模式。表級鎖的模式:
1、行共享(ROWSHARE,RS):允許其他用戶訪問和鎖定該表,但是禁止排他鎖鎖定整個(gè)表。
2、行排他(ROWEXCLUSIVE,RX):與行共享模式相同,同時(shí)禁止其他用戶在此表上使用共享鎖。使用Select...Forupdate語句會(huì)自動(dòng)應(yīng)用行排他。
3、共享(SHARE,S):共享鎖將鎖定表,僅允許其他用戶查詢表中的行,但不允許插入、更新或刪除行。多個(gè)用戶可以同時(shí)在同一張表中放置共享鎖即允許資源共享。但是這樣極容易造成死鎖。
4、共享行排他(SHAREROWEXCLUSIVE,SRX):執(zhí)行比共享表鎖更多的限制。防止其他事務(wù)在表上應(yīng)用共享鎖、共享行排他鎖以及排他鎖。
5、排他(EXCLUSIVE,X):對表執(zhí)行最大限制。除了允許其他用戶查詢該表的記錄,排他鎖防止其他事務(wù)對表做任何更改或在表上應(yīng)用任何類型的鎖。示例2:演示如何以共享模式鎖定表。locktabledeptinsharemodenowait;表分區(qū)的優(yōu)點(diǎn):
改善表的查詢性能;表更容易管理;便于備份和恢復(fù);提高數(shù)據(jù)安全性。注意:要分區(qū)的表不能具有Long和LongRaw數(shù)據(jù)類型的列。四種分區(qū)方法:
1、范圍分區(qū):根據(jù)表的某個(gè)列或一組列的值范圍,決定將該數(shù)據(jù)存儲(chǔ)在哪個(gè)分區(qū)上。語法如下:
在CreateTable語句后增加
PARTITIONBYRANGE(column_name)(
PARTITIONpart1VALUELESSTHAN(range1)[TABLESPACEtbs1],PARTITIONpart2VALUELESSTHAN(range2)[TABLESPACEtbs2],....
PARTITIONpartNVALUELESSTHAN(MAXVALUE)[TABLESPACEtbsN]);
其中:column_name是以其為基礎(chǔ)創(chuàng)建范圍分區(qū)的列,特定行的該列值稱為分區(qū)鍵。part1...partN是分區(qū)的名稱。
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ū):語法有兩種如下
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ū)的名稱。tablespace_list指定分區(qū)使用的表空間,如果分區(qū)數(shù)目比表空間的數(shù)目多,分區(qū)將會(huì)以循環(huán)的方式分配到表空間中。part1...partN是分區(qū)的名稱。
tbs1...tbsN是分區(qū)所在的表空間,TABLESPACE子句是可選項(xiàng)。示例4:自動(dòng)分配4個(gè)散列分區(qū),
可以使用selectpartition_name,HIGH_VALUEfromuser_tab_partitionswheretable_name=upper("t_emp")查詢分區(qū)名createtablet_emp(
empnonumber(4),enamevarchar2(30),salnumber)
partitionbyhash(empno)partitions4;
3、復(fù)合分區(qū):是范圍分區(qū)和散列分區(qū)的結(jié)合。在創(chuàng)建復(fù)合分區(qū)時(shí),先根據(jù)范圍對數(shù)據(jù)進(jìn)行分區(qū),然后在這些分區(qū)內(nèi)創(chuàng)建散列子分區(qū)。語法如下:
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ū)的名稱。
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ū)允許用戶明確地控制行到分區(qū)的映射。語法如下:
PARTITIONBYLIST(column_name)(
PARTITIONpart1VALUES(values_list1),PARTITIONpart2VALUES(values_list2),....
PARTITIONpartNVALUES(DEFAULT));
其中:column_name是以其為基礎(chǔ)創(chuàng)建列表分區(qū)的列。part1...partN是分區(qū)的名稱。
values_list是對應(yīng)分區(qū)的分區(qū)鍵值的列表。DEFAULT關(guān)鍵字允許存儲(chǔ)前面的分區(qū)不能存儲(chǔ)的記錄。示例6:
createtablet_emp1(
empnonumber(4),enamevarchar2(30),locationvarchar2(30))
partitionbylist(location)(
partitione1values("北京"),
partitione2values("上海","天津","重慶"),partitione3values("廣東","福建"),);
要查詢表分區(qū)中的數(shù)據(jù)行:select*fromtable_namePARTITION(p1);注:p1是分區(qū)名。分區(qū)維護(hù)操作:
1、添加分區(qū):ALTER...ADDPARTITION語句用于在現(xiàn)有的最后一個(gè)分區(qū)之后添加新的分區(qū)。
示例7:演示如何將名為E4的新分區(qū)添加到示例3中創(chuàng)建的t_emp表。ALTERTABLEt_empADDPARTITIONE4VALUESLESSTHAN(3000);在此請注意:上例公適用于已使用特定的鍵值定義了最后一個(gè)分區(qū)的表。如果要在表的開始或中間位置添加分區(qū),或者最高分區(qū)的分區(qū)
邊界是MAXVALUE,則應(yīng)使用SPLITPARTITION語句。
2、刪除分區(qū):使用ALTERTABLE...DROPPARTITION語句。
示例8:演示了如何刪除t_emp表的E4分區(qū)。刪除分區(qū)時(shí),分區(qū)中的數(shù)據(jù)也隨之刪除。ALTERTABLEt_empDROPPARTITIONE4;
3、截?cái)喾謪^(qū):使用ALTERTABLE...TRUNCATEPARTITION語句來截?cái)喾謪^(qū),只刪除表分區(qū)中的所有記錄。
示例9:演示了如何刪除t_emp表中e3的分區(qū)的所有記錄。ALTERTABLEt_empTRUNCATEPARTITIONe3;
4、合并分區(qū):可以將范圍分區(qū)或復(fù)合分區(qū)表的兩個(gè)相鄰分區(qū)連接起來。結(jié)果分區(qū)將繼承被合并的兩個(gè)分區(qū)的較高上界。語法如下:
ALTERTABLEtable_nameMERGEPARTITIONSpartitions_name,partitions_nameINTOPARTITIONpartition_name;
示例10:演示了如何將e1和e2合并成一個(gè)e2分區(qū)。
ALTERTABLEt_empMERGEPARTITIONSe1,e2INTOPARTITIONe2;
5、拆分分區(qū):使用SPLITPARTITION語句在表的開頭或中間添加分區(qū)。拆分分區(qū)允許用戶將一個(gè)分區(qū)拆分為兩個(gè)分區(qū)。語法如下: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;
可以查詢字典視圖user_tab_partitions來查看用戶所創(chuàng)建的分區(qū)的詳細(xì)信息示例12:selecttable_name,partition_name,high_valuefromuser_tab_partitions;
可以查詢字典視圖dba_tab_subpartitions來查看用戶所創(chuàng)建的子分區(qū)的詳細(xì)信息
示例13:selecttable_name,partition_name,subpartition_namefromdba_tab_subpartitions;
四、數(shù)據(jù)庫對象
表、視圖、序列、過程、函數(shù)、程序包,甚至其它同義詞都可以創(chuàng)建同義詞。
1、同義詞:私有同義詞、公有同義詞。私有同義詞只能被當(dāng)前模式的用戶訪問。私有同義詞名稱不可與當(dāng)前模式的對象名稱相同。要在自身的模式創(chuàng)建私有同義詞,
用戶必須擁有CreateSynonym系統(tǒng)權(quán)限。要在其它用戶模式創(chuàng)建私有同義詞,用戶必須擁有CreateAnySynonym系統(tǒng)權(quán)限。
公有同義詞可被所有的數(shù)據(jù)庫用戶訪問。要?jiǎng)?chuàng)建公有同義詞,用戶必須擁有CreatePublicSynonym系統(tǒng)權(quán)限。
創(chuàng)建私有同義詞語法:
Create[ORREPLACE]SYNONYM[schema.]synonym_nameFOR[schema.]object_name;其中:ORREPLACE表示在同義詞存在的情況下替換該同義詞。synonym_name表示要?jiǎng)?chuàng)建的同義詞的名稱。
object_name指定要為之創(chuàng)建同義詞的對象的名稱。示例1:createsynonyms_empforscott.emp;
創(chuàng)建公有同義詞語法:
CreatePUBLICSYNONYMsynonym_nameFOR[schema.]object_name;示例2:createpublicsynonymemp_synfromscott.emp;
可以查詢字典視圖User_Synonyms來查看用戶所創(chuàng)建的同義詞的詳細(xì)信息
刪除同義詞語法:DropSynonymssynonym_name;刪除公有同義詞加上一個(gè)Public此命令只刪除同義詞,不會(huì)刪除對應(yīng)的表。
2、序列:是用來生成唯一、連續(xù)的整數(shù)的數(shù)據(jù)庫對象。序列通常用來自動(dòng)生成主鍵或唯一鍵的值。
創(chuàng)建序列語法如下:
CreateSEQUENCEsequence_name[STARTWITHinteger][INCREMENTBYinteger]
[MAXVALUEinteger|NOMAXVALUE][MINVALUEinteger|NOMINVALUE][CYCLE|NOCYCLE]
[CACHEinterger|NOCACHE];
其中:STARTWITH是指定要生成的第一個(gè)序列號。對于升序序列,其默認(rèn)值為序列的最小值。對于降序序列,其默認(rèn)值為序列的最大值。
INCREMENTBY是用于指定序列號之間的間隔。其默認(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ù)從頭開始生成值。
NOCYCLE這是默認(rèn)選項(xiàng)。指定序列在達(dá)到最大值或最小值后,將不能再繼續(xù)生成值。CACHE使用CACHE選項(xiàng)可以預(yù)先分配一組序列號,并將其保留在內(nèi)存中,這樣可以更快的訪問序列號。
NOCACHE此項(xiàng)則不會(huì)為加快速度而預(yù)先分配序列號。如果在創(chuàng)建序列時(shí)忽略了CACHE和NOCACHE選項(xiàng),Oracle將默認(rèn)緩存20個(gè)序列號。示例3:CreateSEQUENCEtoys_seqSTARTWITH10INCREMENTBY2MAXVALUE201*MINVALUE10NOCYCLECACHE30;
訪問序列:可以通過CURRVAL和NEXTVAL偽列來訪問該序列的值。示例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、修改增量值、修改緩存中的序列號的數(shù)目。
修改序列語法如下:注意,不能修改序列的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序列打開了CYCLE。ALTERSEQUENCEtoys_seqMAXVALUE5000CYCLE;
可以查詢字典視圖User_Sequences來查看用戶所創(chuàng)建的序列的詳細(xì)信息
刪除序列語法:DropSEQUENCEtoys_seq;
[local]2[/local]
友情提示:本文中關(guān)于《Oracle使用學(xué)習(xí)總結(jié)》給出的范例僅供您參考拓展思維使用,Oracle使用學(xué)習(xí)總結(jié):該篇文章建議您自主創(chuàng)作。
來源:網(wǎng)絡(luò)整理 免責(zé)聲明:本文僅限學(xué)習(xí)分享,如產(chǎn)生版權(quán)問題,請聯(lián)系我們及時(shí)刪除。