Oracle表管理相關(guān)知識

Oracle表管理主要內(nèi)容Oracle表空間常用的數(shù)據(jù)類型表的創(chuàng)建和刪除數(shù)據(jù)完整性（約束）對數(shù)據(jù)的CRUD操作表空間表空間是數(shù)據(jù)庫的邏輯組成部分。從物理上講，數(shù)據(jù)庫數(shù)據(jù)存放在數(shù)據(jù)文件中；從邏輯上講，數(shù)據(jù)庫則是存放在表空間中，表空間由一個或多個數(shù)據(jù)文件組成。默認(rèn)表空間是“system”

通過表空間可以達(dá)到以下作用：1.控制數(shù)據(jù)庫占用的磁盤空間2.dba可以將不同數(shù)據(jù)類型部署到不同的位置，這樣有利于提高i/o性能，同時利于備份和恢復(fù)等管理操作。建立和使用表空間建立：createtablespacedata01datafile'd:\test\dada01.dbf'size20m使用：createtablestudent(snonumber(4),snamevarchar2(14))

tablespacedata01;表空間名稱表空間文件名表空間的大小擴(kuò)展表空間1.增加數(shù)據(jù)文件altertablespace

表空間名adddatafile‘d:\test\sp01.dbf’size20m;2.修改數(shù)據(jù)文件的大小alterdatabase

datafile‘d:\test\sp01.dbf’resize50m;注意：數(shù)據(jù)文件的大小不要超過500m。3.設(shè)置文件的自動增長。SQL>alterdatabase

datafile‘d:\test\sp01.dbf’autoextendonnext10mmaxsize500m;刪除表空間droptablespace

表空間名

includingcontentsanddatafiles;說明：includingcontents表示刪除表空間時，刪除該空間的所有數(shù)據(jù)庫對象，datafiles表示將數(shù)據(jù)庫文件也刪除。1.知道表空間名，顯示該表空間包括的所有表select*fromall_tableswheretablespace_name=’表空間名’;2.知道表名，查看該表屬于那個表空間selecttablespace_name,table_namefromuser_tableswheretable_name=’emp’;此處查的是scott這個用戶表空間下的所有表名selecttable_namefromall_tableswhereowner=upper('scott')表名和列的命名規(guī)則必須以字母開頭長度不能超過30個字符不能使用oracle的保留字只能使用如下字符A-Z，a-z，0-9，$,#等Oracle常常用用的的數(shù)數(shù)據(jù)據(jù)類類型型字符符類類char定長長最最大大2000個字字符符。。varchar2/varchar變長長最最大大4000個字字符符.注意意：：varchar2是oracle自己己開開發(fā)發(fā)的的，，想想有有向向后后兼兼容容的的能能力力，，建建議議使使用用varchar2。clob(characterlargeobject)字符符型型大大對對象象最最大大4G注意意：：char查詢詢的的速速度度極極快快浪浪費(fèi)費(fèi)空空間間，，查查詢詢比比較較多多的的數(shù)數(shù)據(jù)據(jù)用用。。varchar2節(jié)省省空空間間數(shù)字字型型number(p,s)范圍圍-10的38次方方到到10的38次方方，，可可以以表表示示整整數(shù)數(shù)，，也也可可以以表表示示小小數(shù)數(shù)。。p和s都為為可可選選number(5,2)，表表示示一一位位小小數(shù)數(shù)有有5位有效效數(shù)，，2位小數(shù)數(shù)。范范圍：：-999.99到999.99number(5)，表示示一個個5位整數(shù)數(shù)。范范圍99999到-99999日期類類型date包含年年月日日和時時分秒秒oracle默認(rèn)格格式1-1月-1999timestamp這是oracle9i對date數(shù)據(jù)類類型的的擴(kuò)展展。可可以精精確到到毫秒秒。語法timestamp(n)，n指定秒秒的小小數(shù)位位數(shù)，，取值值范圍圍0～9。缺省省是６６。圖片blob二進(jìn)制制數(shù)據(jù)據(jù)可可以存存放圖圖片/聲音4G注意：：一一般來來講，，在真真實項項目中中是不不會把把圖片片和聲聲音真真的往往數(shù)據(jù)據(jù)庫里里存放放，一一般存存放圖圖片、、視頻頻的路路徑，，如果果安全全需要要比較較高的的話，，則放放入數(shù)數(shù)據(jù)庫庫。創(chuàng)建表實際操作修改表添加一個字字段ALTERTABLEstudentadd(sexchar(2));修改一個字字段的長度度ALTERTABLEstudentMODIFY(sexchar(5));刪除一個字字段不建建議做(刪了之后，，順序就變變了。加就就沒問題，，應(yīng)為是加加在后面)ALTERTABLEstudentDROPCOLUMNsex;修改表的名名字很少少有這種需需求RENAME原表名TO新表名;刪除表DROPTABLEstudent;數(shù)據(jù)完整整性在oracle中，數(shù)據(jù)據(jù)完整性性可以使使用約束、觸發(fā)器、應(yīng)用程序序（過程、、函數(shù)））三種方方法來實實現(xiàn)，在在這三種種方法中中，因為為約束易于維護(hù)護(hù)，并且且具有最最好的性性能，所所以作為為維護(hù)數(shù)數(shù)據(jù)完整整性的首首選。約束約束用于于確保數(shù)數(shù)據(jù)庫數(shù)數(shù)據(jù)滿足足特定的的商業(yè)規(guī)規(guī)則。在oracle中，約束束包括：：notnull、unique、primarykey、foreignkey和check五種。建表時添添加約束束createtablecustomer(customerIdchar(8)primarykey,--主鍵namevarchar2(50)notnull,--不為空addressvarchar2(50),emailvarchar2(50)unique,sexchar(2)default‘‘男’check(sexin(‘男’，‘女’))，cardIdchar(18));表是默認(rèn)建在在SYSTEM表空間的建表后添加約約束使用altertable命令為表增加加約束。但是是要注意：增增加notnull約束時，需要要使用modify選項，而增加加其它四種約約束使用add選項。1.增加商品名也也不能為空altertablestuInfomodifystuNamenotnull;2.增加身份證也也不能重復(fù)altertablestuInfoaddconstraint約束名unique(stuName);3.增加學(xué)生的住住址只能是’’海淀’,’朝陽’,’東城’,’西城’,’通州’,’崇文’,’昌平’；altertablestuInfoaddconstraint約束名check(addressin(’海淀’,’朝陽’,’東城’,’西城’,’通州’,’崇文’,’昌平’));刪除約束當(dāng)不再需要某某個約束時，，可以刪除。。altertable表名dropconstraint約束名稱；注意：在刪除除主鍵約束的的時候，可能能會有錯誤altertable表名dropprimarykey；這是因為如果果在兩張表存存在主從關(guān)系系，那么在刪刪除主表的主主鍵約束時，，必須帶上cascade選項如像：：altertable表名dropprimarykeycascade;自動標(biāo)識列oracle里面沒有標(biāo)識識列！只能增加一個個自增的序列列，每當(dāng)要用用的時候調(diào)用用這個序列?。?chuàng)建序列createsequencetest--test為序列的名稱稱startwith1--從1開始incrementby1--每次遞遞增1使用序序列插插入數(shù)數(shù)據(jù)insertintostuInfovalues(test.nextval,‘‘張三');向表中中添加加數(shù)據(jù)據(jù)oracle中默認(rèn)認(rèn)的日日期格格式‘‘dd-mon-yy’dd日子（（天））mon月份yy2位的年年‘‘09-6月-99’INSERTINTOstudentVALUES('A001','張三','男','01-5月-05',10);使用用do_date函數(shù)數(shù)insertintostudentvalues('mark',to_date('08-21-2003','MM-DD-YYYY'));修改改日日期期的的默默認(rèn)認(rèn)格格式式（（臨臨時時修修改改，，數(shù)數(shù)據(jù)據(jù)庫庫重重啟啟后后仍仍為為默默認(rèn)認(rèn)；；如如要要修修改改需需要要修修改改注注冊冊表表））ALTERSESSIONSETNLS_DATE_FORMAT='yyyy-mm-dd';修改改表表中中的的數(shù)數(shù)據(jù)據(jù)UPDATEstudentSETsex='女女'WHERExh='A001';UPDATEstudentSETsex='男男',birthday='1984-04-01'WHERExh='A001';刪除除表表中中的的數(shù)數(shù)據(jù)據(jù)DELETEFROMstudent;刪除除所所有有記記錄錄，，表表結(jié)結(jié)構(gòu)構(gòu)還還在在，，寫寫日日志志，，可可以以恢恢復(fù)復(fù)的的，，速速度度慢慢。。savepointa;--創(chuàng)建建保保存存點(diǎn)點(diǎn)DELETEFROMstudent;rollbacktoa;--恢復(fù)復(fù)到到保保存存點(diǎn)點(diǎn)一個個有有經(jīng)經(jīng)驗驗的的DBA，在在確確保保完完成成無無誤誤的的情情況況下下要要定定期期創(chuàng)創(chuàng)建建還還原原點(diǎn)點(diǎn)。。DROPTABLEstudent;--刪除除表表的的結(jié)結(jié)構(gòu)構(gòu)和和數(shù)數(shù)據(jù)據(jù)；；deletefromstudentWHERExh='A001';--刪除一條條記錄；；truncateTABLEstudent;--刪除表中中的所有有記錄，，表結(jié)構(gòu)構(gòu)還在，，不寫日日志，無無法找回回刪除的的記錄，，速度快快。表查詢使用scott用戶中的的幾張表表作示例例emp雇員表dept部門表salgrade工資級別別salgrade工資級別別表grade級別losal最低工資資hisal最高工資資dept部門表deptno部部門編編號Dname部門名稱稱loc部部門所所在地點(diǎn)點(diǎn)emp雇員表Empno員工編號號Ename員工姓名名Job工作mgr上上級的的編號號hiredate入入職職時間間sal月月工資資comm獎獎金金deptno部部門門查詢一一：SELECTename,sal,job,deptnoFROMemp;SELECTDISTINCTdeptno,jobFROMemp;SELECTdeptno,job,salFROMempWHEREename='SMITH';注意：：oracle對內(nèi)容的的大小小寫是是區(qū)分分的，所以以ename='SMITH'和ename='smith'是不同同的如何處處理null值使用nvl函數(shù)來來處理理SELECTsal*13+nvl(comm,0)*13"年薪",ename,commFROMemp;SELECTename"姓名",sal*12AS"年收入"FROMemp;如何連接字字符串（||）SELECTename||'isa'||jobFROMemp;預(yù)設(shè)的值問題：如何何查找后入職的員員工？SELECTename,hiredateFROMempWHEREhiredate>'1-1月-1982';使用like%：表示0到多個字符符_：表示任意意單個字符符問題：如何何顯示首字字符為S的員工姓名名和工資？？SELECTename,salFROMempWHEREenamelike'S%';如何顯示第第三個字符符為大寫O的所有員工工的姓名和和工資？SELECTename,salFROMempWHEREenamelike'__O%';問題：如何何顯示empno為7844,7839,123,456的雇員情況況？SELECT*FROMempWHEREempnoin(7844,7839,123,456);問題：如何何顯示沒有有上級的雇雇員的情況況？SELECT*FROMempWHEREmgrisnull;查詢二：使使用邏輯操操作符號問題：查詢詢工資高于于500或者是崗位位為MANAGER的雇員，同同時還要滿滿足他們的的姓名首字字母為大寫寫的J？SELECT*FROMempWHERE(sal>500orjob='MANAGER')andenameLIKE'J%';問題：如何何按照工資資的從低到到高的順序序顯示雇員員的信息？？SELECT*FROMempORDERbysal;問題：按照照部門號升升序而雇員員的工資降降序排列SELECT*FROMempORDERbydeptno,salDESC;問題：按年年薪排序selectename,(sal+nvl(comm,0))*12"年薪"fromemporderby"年薪"asc;查詢?nèi)簭?fù)復(fù)雜查詢數(shù)據(jù)分組——max，min，avg，sum，count問題：如何何顯示所有有員工中最最高工資和和最低工資資？SELECTMAX(sal),min(sal)FROMempe;最高工資那那個人是誰誰？selectename,salfromempwheresal=(selectmax(sal)fromemp);練習(xí)：問題：如何何顯示所有有員工的平平均工資和和工資總和和？問題：如何何計算總共共有多少員員查詢最高工工資員工的的名字，工工作崗位顯示工資高高于平均工工資的員工工信息groupby和和having子句問題：如何何顯示每個個部門的平平均工資和和最高工資資？SELECTAVG(sal),MAX(sal),deptnoFROMempGROUPbydeptno;問題：顯示示每個部門門的每種崗崗位的平均均工資和最最低工資？？SELECTmin(sal),AVG(sal),deptno,jobFROMempGROUPbydeptno,job;問題：顯示示平均工資資低于2000的部門號和和它的平均均工資？SELECTAVG(sal),MAX(sal),deptnoFROMempGROUPbydeptnohavingAVG(sal)<2000;查詢四：多多表查詢問題：顯示示雇員名，，雇員工資資及所在部部門的名字字SELECTe.ename,e.sal,d.dnameFROMempe,deptdWHEREe.deptno=d.deptno;問題：顯示示部門號為為10的部門名、、員工名和和工資？SELECTd.dname,e.ename,e.salFROMempe,deptdWHEREe.deptno=d.deptnoande.deptno=10;問題：顯示示各個員工工的姓名，，工資及工工資的級別別？SELECTe.ename,e.sal,s.gradeFROMempe,salgradesWHEREe.salBETWEENs.losalANDs.hisal;擴(kuò)展要求：：問題：顯示示雇員名，，雇員工資資及所在部部門的名字字，并按部部門排序？？SELECTe.ename,e.sal,d.dnameFROMempe,deptdWHEREe.deptno=d.deptnoORDERbye.deptno;自連接自連接是指指在同一張張表的連接接查詢問題：顯示示某個員工工的上級領(lǐng)領(lǐng)導(dǎo)的姓名名？比如顯顯示員工‘‘FORD’’的上級SELECTworker.ename,boss.enameFROMempworker,empbossWHEREworker.mgr=boss.empnoANDworker.ename='FORD';子查查詢詢1查詢詢出出SMITH的部部門門號號selectdeptnofromempWHEREename='SMITH';2顯示示SELECT*FROMempWHEREdeptno=(selectdeptnofromempWHEREename='SMITH');請思思考考：：如如何何查查詢詢和和部部門門10的工工作作相相同同的的雇雇員員的的名名字字、、崗崗位位、、工工資資、、部部門門號號SELECT*FROMempWHEREjobIN(SELECTDISTINCTjobFROMempWHEREdeptno=10);在多多行行子子查查詢詢中中使使用用all操作作符符問題題：：如如何何顯顯示示工工資資比比部部門門30的所所有有員員工工的的工工資資高高的的員員工工的的姓姓名名、、工工資資和和部部門門號號？？SELECTename,sal,deptnoFROMempWHEREsal>all(SELECTsalFROMempWHEREdeptno=30);大家想想還有有沒有別的查查詢方法。SELECTename,sal,deptnoFROMempWHEREsal>(SELECTMAX(sal)FROMempWHEREdeptno=30);執(zhí)行效率上，，函數(shù)高得得多All等價于N個And語句在多行子查詢詢中使用any操作符符問題：如何顯顯示工資比部部門30的任意一個員員工的工資高高的員工姓名名、工資和部部門號？SELECTename,sal,deptnoFROMempWHEREsal>ANY(SELECTsalFROMempWHEREdeptno=30);大家想想還有有沒有別的查查詢方法。SELECTename,sal,deptnoFROMempWHEREsal>(SELECTmin(sal)FROMempWHEREdeptno=30);Any等價于于N個or語句多列子子查詢詢查詢與與SMITH的部門門和崗崗位完完全相相同的的所有有雇員員。a)SELECTdeptno,jobFROMempWHEREename='SMITH';b)SELECT*FROMempWHERE(deptno,job)=(SELECTdeptno,jobFROMempWHEREename='SMITH');1.查出各各個部部門的的平均均工資資和部部門號號SELECTdeptno,AVG(sal)mysalFROMempGROUPbydeptno;2.把上面面的查查詢結(jié)結(jié)果看看做是是一張張子表表SELECTe.ename,e.deptno,e.sal,ds.mysalFROMempe,(SELECTdeptno,AVG(sal)mysalFROMempGROUPbydeptno)dsWHEREe.deptno=ds.deptnoANDe.sal>ds.mysal;小總結(jié)結(jié)：在這里里需要要說明明的當(dāng)當(dāng)在from子句中中使用用子查查詢時時，該該子查查詢會會被作作為一一個視視圖來來對待待，因因此叫叫做內(nèi)內(nèi)嵌視視圖，，當(dāng)在在from子句中使用用子查詢時時，必須給給子查詢指指定別名。。注意：給表表取別名的的時候，不不能加as；但是給列列取別名，，是可以加加as的查詢五：分分頁查詢oracle的分頁一共共有三種方方式1.根據(jù)rowid來分select*fromt_xiaoxiwhererowidin(selectridfrom(selectrownumrn,ridfrom(selectrowidrid,cidfromt_xiaoxiorderbyciddesc)whererownum<10000)wherern>9980)orderbyciddesc;執(zhí)行時間0.03秒2.按分析函數(shù)數(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秒1的效率率最好，3次之，，2最差差。select*from(selecta1.*,rownumrnfrom(selectename,jobfromemp)a1whererownum<=10)wherern>=5;下面最主要要介紹第三三種：按rownum來分1.rownum分頁SELECT*FROMemp;2.顯示rownum[oracle分配的]SELECTe.*,ROWNUMrnFROM(SELECT*FROMemp)e;注：rn相當(dāng)于Oracle分配的行的的ID號3.挑選出6—10條記錄,先查出1-10條記錄SELECTe.*,ROWNUMrnFROM(SELECT*FROMemp)eWHEREROWNUM<=10;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合并查詢有時在實際際應(yīng)用中，，為了合并并多個select語句的結(jié)果果，可以使使用集合操操作符號union，unionall，intersect，minus多用于數(shù)據(jù)據(jù)量比較大大的數(shù)據(jù)局局庫，運(yùn)行行速度快。。1).union該操作符用用于取得兩兩個結(jié)果集集的并集。。當(dāng)使用該該操作符時時，會自動動去掉結(jié)果果集中重復(fù)復(fù)行。SELECTename,sal,jobFROMempWHEREsal>2500UNIONSELECTename,sal,jobFROMempWHEREjob='MANAGER';2).unionall該操作符與與union相似，但是是它不會取取消重復(fù)行行，而且不不會排序。。SELECTename,sal,jobFROMempWHEREsal>2500UNIONALLSELECTename,sal,jobFROMempWHEREjob='MANAGER';該操作符符用于取取得兩個個結(jié)果集集的并集集。當(dāng)使使用該操操作符時時，會自自動去掉掉結(jié)果集集中重復(fù)行。。3).intersect使用該操操作符用用于取得得兩個結(jié)結(jié)果集的的交集。。SELECTename,sal,jobFROMempWHEREsal>2500INTERSECTSELECTename,sal,jobFROMempWHEREjob='MANAGER';4).minus使用改操操作符用用于取得得兩個結(jié)結(jié)果集的的差集，，他只會會顯示存存在第一一個集合合中，而而不存在第二二個集合合中的數(shù)數(shù)據(jù)。SELECTename,sal,jobFROMempWHEREsal>2500MINUSSELECTename,sal,jobFROMempWHEREjob='MANAGER';（MINUS就是減法的意意思）Oracle中常用函數(shù)數(shù)字符函數(shù)upper(char)：將字符串轉(zhuǎn)轉(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ù)和返回值值的數(shù)據(jù)類類型都是數(shù)數(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ù)數(shù)，如果m是正數(shù)，則則四舍五入入到小數(shù)點(diǎn)點(diǎn)的m位后。如果m是負(fù)數(shù)，則四四舍五入到小小數(shù)點(diǎn)的m位前。trunc(n,[m])該函數(shù)用于截截取數(shù)字。如如果省掉m，就截去小數(shù)數(shù)部分，如果果m是正數(shù)就截取取到小數(shù)點(diǎn)的的m位后，如果m是負(fù)數(shù)，則截截取到小數(shù)點(diǎn)點(diǎn)的前m位。mod(m,n)floor(n)返回小于或是是等于n的最大整數(shù)ceil(n)返回大于或是是等于n的最小小整數(shù)數(shù)對數(shù)字字的處處理，，在財財務(wù)系系統(tǒng)或或銀行行系統(tǒng)統(tǒng)中用用的最最多，，不同同的處處理方方法，，對財財務(wù)報報表有不不同的的結(jié)果果。問題：：顯示示在一一個月月為30天的情情況下下，所所有員員工的的日薪薪金，，忽略略余數(shù)數(shù)。SQ