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ù)文件組成。默認表空間是“system”

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

tablespacedata01;表空間名稱表空間文件名表空間的大小擴展表空間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ù)據(jù)據(jù)類型字符類char定長最大2000個字符。varchar2/varchar變長最大4000個字符.注意：varchar2是oracle自己開發(fā)的，，想有向后兼兼容的能力，，建議使用用varchar2。clob(characterlargeobject)字符型大對象象最大4G注意：char查詢的速度極極快浪費空間間，查詢比較較多的數(shù)據(jù)用用。varchar2節(jié)省空間數(shù)字型number(p,s)范圍-10的38次方到10的38次方，可以表表示整數(shù)，也也可以表示小小數(shù)。p和s都為可選number(5,2)，表示一位小小數(shù)有5位有效數(shù)，2位小數(shù)。范圍圍：-999.99到999.99number(5)，表示一個5位整數(shù)。范圍圍99999到-99999日期類型date包含年月日和和時分秒oracle默認格式1-1月-1999timestamp這是oracle9i對date數(shù)據(jù)類型的擴擴展?？梢跃_到毫秒。。語法timestamp(n)，n指定秒的小數(shù)數(shù)位數(shù)，取值值范圍0～9。缺省是６。。圖片blob二進制數(shù)據(jù)可可以存放圖圖片/聲音4G注意：一般般來講，在真真實項目中是是不會把圖片片和聲音真的的往數(shù)據(jù)庫里里存放，一般般存放圖片、、視頻的路徑徑，如果安全全需要比較高高的話，則放放入數(shù)據(jù)庫。。創(chuàng)建表實際操作修改表添加一個字段段ALTERTABLEstudentadd(sexchar(2));修改一個字段段的長度ALTERTABLEstudentMODIFY(sexchar(5));刪除一個字段段不建議做做(刪了之后，順順序就變了。。加就沒問題題，應(yīng)為是加加在后面)ALTERTABLEstudentDROPCOLUMNsex;修改表的名字字很少有這這種需求RENAME原表名TO新表名;刪除表DROPTABLEstudent;數(shù)據(jù)完整性在oracle中，數(shù)據(jù)完整整性可以使用用約束、觸發(fā)器、應(yīng)用程序（過程、函數(shù)數(shù)）三種方法法來實現(xiàn)，在在這三種方法法中，因為約束易于維護，并并且具有最好好的性能，所所以作為維護護數(shù)據(jù)完整性性的首選。約束約束用于確保保數(shù)據(jù)庫數(shù)據(jù)據(jù)滿足特定的的商業(yè)規(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));表是默認建在在SYSTEM表空間的建表后添加約約束使用altertable命令為表增加加約束。但是是要注意：增增加notnull約束時，需要要使用modify選項，而增加加其它四種約約束使用add選項。1.增加商品名也也不能為空altertablestuInfomodifystuNamenotnull;2.增加身份證也也不能重復altertablestuInfoaddconstraint約束名unique(stuName);3.增加學生的住住址只能是’’海淀’,’朝陽’,’東城’,’西城’,’通州’,’崇文’,’昌平’；altertablestuInfoaddconstraint約束名check(addressin(’海淀’,’朝陽’,’東城’,’西城’,’通州’,’崇文’,’昌平’));刪除約束當不再需要某某個約束時，，可以刪除。。altertable表名dropconstraint約束名稱；注意：在刪除除主鍵約束的的時候，可能能會有錯誤altertable表名dropprimarykey；這是因為如果果在兩張表存存在主從關(guān)系系，那么在刪刪除主表的主主鍵約束時，，必須帶上cascade選項如像：：altertable表名dropprimarykeycascade;自動標識列oracle里面沒有標識識列！只能增加一個個自增的序列列，每當要用用的時候調(diào)用用這個序列！！創(chuàng)建序列createsequencetest--test為序列的名稱稱startwith1 --從1開始incrementby1--每次遞增1使用序列插入入數(shù)據(jù)insertintostuInfovalues(test.nextval,‘‘張三');向表中添加數(shù)數(shù)據(jù)oracle中默認的日期期格式‘dd-mon-yy’dd日子（天）mon月份yy2位的年‘09-6月-99’INSERTINTOstudentVALUES('A001','張三','男','01-5月-05',10);使用do_date函數(shù)insertintostudentvalues('mark',to_date('08-21-2003','MM-DD-YYYY'));修改日期的默默認格式（臨臨時修改，數(shù)數(shù)據(jù)庫重啟后后仍為默認；；如要修改需需要修改注冊冊表）ALTERSESSIONSETNLS_DATE_FORMAT='yyyy-mm-dd';修改表中的數(shù)數(shù)據(jù)UPDATEstudentSETsex='女'WHERExh='A001';UPDATEstudentSETsex='男',birthday='1984-04-01'WHERExh='A001';刪除表中的數(shù)數(shù)據(jù)DELETEFROMstudent;刪除所有記錄錄，表結(jié)構(gòu)還還在，寫日志志，可以恢復復的，速度慢慢。savepointa;--創(chuàng)建保存點DELETEFROMstudent;rollbacktoa;--恢復到保存點點一個有經(jīng)驗的的DBA，在確保完成成無誤的情況況下要定期創(chuàng)創(chuàng)建還原點。。DROPTABLEstudent;--刪除表的結(jié)構(gòu)構(gòu)和數(shù)據(jù)；deletefromstudentWHERExh='A001';--刪除一條記錄錄；truncateTABLEstudent;--刪除表中的所所有記錄，表表結(jié)構(gòu)還在，，不寫日志，，無法找回刪刪除的記錄，，速度快。表查詢使用scott用戶中的幾張張表作示例emp雇員表dept部門表salgrade工資級別salgrade工資級別表grade級別losal最低工資hisal最高工資dept部門表deptno部門編號號Dname部門名稱loc部門門所在地點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;預設(shè)的值問題：如何查查找1982.1.1后入職的員工工？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)簭碗s雜查詢數(shù)據(jù)分組——max，min，avg，sum，count問題：如何顯顯示所有員工工中最高工資資和最低工資資？SELECTMAX(sal),min(sal)FROMempe;最高工資那個個人是誰？selectename,salfromempwheresal=(selectmax(sal)fromemp);練習：問題：如何顯顯示所有員工工的平均工資資和工資總和和？問題：如何計計算總共有多多少員查詢最高工資資員工的名字字，工作崗位位顯示工資高于于平均工資的的員工信息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;擴展要求：問題：顯示雇雇員名，雇員員工資及所在在部門的名字字，并按部門門排序？SELECTe.ename,e.sal,d.dnameFROMempe,deptdWHEREe.deptno=d.deptnoORDERbye.deptno;自連接自連接是指在在同一張表的的連接查詢問題：顯示某某個員工的上上級領(lǐng)導的姓姓名？比如顯顯示員工‘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é)：：在這里需需要說明明的當在在from子句中使使用子查查詢時，，該子查查詢會被被作為一一個視圖圖來對待待，因此此叫做內(nèi)內(nèi)嵌視圖圖，當在在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ù)來分分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相當于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é)結(jié)果創(chuàng)建建新表CREATETABLEmytable(id,name,sal,job,deptno)asSELECTempno,ename,sal,job,deptnoFROMemp;創(chuàng)建好之之后，descmytable；；和select*frommytable合并查詢詢有時在實實際應(yīng)用用中，為為了合并并多個select語句的結(jié)結(jié)果，可可以使用用集合操操作符號號union，unionall，intersect，minus多用于數(shù)數(shù)據(jù)量比比較大的的數(shù)據(jù)局局庫，運運行速度度快。1).union該操作符符用于取取得兩個個結(jié)果集集的并集集。當使使用該操操作符時時，會自自動去掉掉結(jié)果集集中重復復行。SELECTename,sal,jobFROMempWHEREsal>2500UNIONSELECTename,sal,jobFROMempWHEREjob='MANAGER';2).unionall該操作符符與union相似，但但是它不不會取消消重復行行，而且且不會排排序。SELECTename,sal,jobFROMempWHEREsal>2500UNIONALLSELECTename,sal,jobFROMempWHEREjob='MANAGER';該操作符符用于取取得兩個個結(jié)果集集的并集集。當使使用該操操作符時時，會自自動去掉掉結(jié)果集集中重復行。。3).intersect使用該操操作符用用于取得得兩個結(jié)結(jié)果集的的交集。。SELECTename,sal,jobFROMempWHEREsal>2500INTERSECTSELECTename,sal,jobFROMempWHEREjob='MANAGER';4).minus使用改操操作符用用于取得得兩個結(jié)結(jié)果集的的差集，，他只會會顯示存存在第一一個集合合中，而而不存在第第二個個集合合中的的數(shù)據(jù)據(jù)。SELECTename,sal,jobFROMempWHEREsal>2500MINUSSELECTename,sal,jobFROMempWHEREjob='MANAGER';（MINUS就是減減法的的意思思）Oracle中中常用用函數(shù)數(shù)字符函函數(shù)upper(char)：將字字符串串轉(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ù)學函函數(shù)數(shù)學函函數(shù)的的輸入入?yún)?shù)數(shù)和返返回值值的數(shù)數(shù)據(jù)類類型都都是數(shù)數(shù)字類類型的的。數(shù)數(shù)學函函數(shù)包包括cos，cosh，，exp，，ln,log，，sin，，sinh，sqrt，，tan，，tanh，acos，，asin，atan，，round，，round(n,[m])該函數(shù)數(shù)用于于執(zhí)行行四舍舍五入入，如如果省省掉m，則四四舍五五入到到整數(shù)數(shù)，如果m是正數(shù)數(shù)，則則四舍舍五入入到小小數(shù)點點的m位后。。如果果m是負數(shù)數(shù)，則則四舍舍五入入到小小數(shù)點的的m位前。。trunc(n,[m])該函數(shù)數(shù)用于于截取取數(shù)字字。如如果省省掉m，就截截去小小數(shù)部部分，，如果果m是正數(shù)數(shù)就截截取到到小數(shù)數(shù)點的的m位后，，如果果m是負數(shù)數(shù)，則則截取取到小小數(shù)點點的前前m位。mod(m,n)floor(n)返回小小于或或是等等于n的最大大整數(shù)數(shù)ceil(n)返回大大于或或是等等于n的最小小整數(shù)數(shù)對數(shù)字字的處處理，，在財財務(wù)系系統(tǒng)或或銀行行系統(tǒng)統(tǒng)中用用的最最多，，不同同的處處理方方法，，對財財務(wù)報報表有不不同的的結(jié)果果。問題：：顯示示在一一個月月為30天的情情況下下，所所有員員工的的日薪薪金，，忽略略余數(shù)數(shù)。SQL>selecttrunc(sal/30),enamefromemp;orSQ