oracle學(xué)習(xí)筆記分析和總結(jié)

TOC\o"1-5"\h\z\o"CurrentDocument"第一章SQL更新數(shù)據(jù)2\o"CurrentDocument"第二章序列4\o"CurrentDocument"第三章SQL查詢6\o"CurrentDocument"第四章ORACLE數(shù)據(jù)表對象15Selectmanager_namename,statusFromt_managersWherestatus='ACT'UnionallSelectemployee_name,statusFromt_employeesWherestatus=,ACT'Intersect查詢Intersect查詢用于獲得兩個結(jié)果集的交集。例selectemployee_name,statusFromt_employeesWherestatus='ACT'Intersectselectmanager_name,statusFromt_managersWherestatus='ACT'Minus查詢Minus查詢可以看做集合間的減法運(yùn)算，該操作的第一個集合看做被減數(shù)，而第二個集合看作減數(shù)，那么minus操作將返回第一個結(jié)果集中存在，而第二個結(jié)果集中不存在的記錄例selectemployee_name,statusFromt_employeesWherestatus='ACT'MinusSelectmanager_name,statusFromt_managersWherestatus='ACT'IV.聯(lián)接聯(lián)接用于多數(shù)據(jù)源（表、視圖）之間如何組合，以形成最終的數(shù)據(jù)源。聯(lián)接對于查詢語句有著不可或缺的作用。如果未顯示指定聯(lián)接，那么將獲得多個數(shù)據(jù)源的笛卡爾積。Oracle中主要包括以下幾種聯(lián)接關(guān)系。自然聯(lián)接內(nèi)聯(lián)接外聯(lián)接：左聯(lián)接外聯(lián)接：右聯(lián)接外聯(lián)接：完全聯(lián)接自然聯(lián)接自然聯(lián)接將兩個數(shù)據(jù)源中具有相同名稱的列進(jìn)行聯(lián)接。用戶不必明確指定執(zhí)行聯(lián)接的列。自然聯(lián)接應(yīng)該使用naturaljoin關(guān)鍵字。例select*Fromt_employeesnaturaljoint_managers內(nèi)聯(lián)接內(nèi)聯(lián)接像自然聯(lián)接不同，需要在from子句中使用聯(lián)接條件。但是，用戶可以自行制定所要聯(lián)接的各數(shù)據(jù)源的列。這克服T自然聯(lián)接要求聯(lián)接必須同名的限制。例select*Fromt_employeeseinnerjoint_managersmOne.employee_name=m.manager_name內(nèi)連接可以用where子句來代替外聯(lián)接一左聯(lián)接外聯(lián)接與內(nèi)聯(lián)接不同的是，內(nèi)聯(lián)接中的兩個數(shù)據(jù)源是并列關(guān)系,二者具有平等的地位，而外聯(lián)接將其中一個數(shù)據(jù)源指定為基表（或者說為主表）另一個數(shù)據(jù)源可以看做附表。在最終的數(shù)據(jù)源中，一定含有基表中的數(shù)據(jù)，而附表中的數(shù)據(jù)是否出現(xiàn),那么依具體的聯(lián)接條件而定。左聯(lián)接例子selecte.employeejd,e.employee_name,s.salaryFromt_employeeseLeftouterjoint_salarysOne.employeejd=s.employee_idOrderbye.employee_id（當(dāng)左側(cè)數(shù)據(jù)表中的數(shù)據(jù)在右側(cè)表中沒有記錄與之對應(yīng)，將利用null來填補(bǔ)空缺的查詢結(jié)果）對于左聯(lián)接，oracle還提供了另外一種簡寫的方式一一在where子句條件中添加(+)來指定附表Selecte.employeejd,e.employee_name,salaryFromt_employeese,t_salarysWheree.employee_id=s.employee_id(+)Orderbye.employee_id外聯(lián)接一一右聯(lián)接右聯(lián)接與左聯(lián)接的執(zhí)行過程非常相似，二者的區(qū)別在于基表的選擇。右聯(lián)接應(yīng)該選擇使用right(outer)join關(guān)鍵字，而基表即處于該關(guān)鍵字右側(cè)的數(shù)據(jù)表selecte.employeejd,e.employee_name,s.salaryFromt_employeeserightouterjoint_salarysOne.employeeJd=s.employee_idOrderbye.employee_id外聯(lián)接一一完全聯(lián)接完全聯(lián)接實(shí)際是一個左聯(lián)接和右聯(lián)接的組合，即首先執(zhí)行一個左聯(lián)接，然后執(zhí)行一個右聯(lián)接，最后將兩個結(jié)果集執(zhí)行union操作，從而獲得最終的數(shù)據(jù)源。例selecte.employeejd,e.employee_name,s.salaryFromt_employeesefulljoint_salarysOne.employeeJd=s.employee_idOrderbye.employee_id缺點(diǎn)：完全聯(lián)接開銷很大V.層次化查詢利用connectby進(jìn)行層次化查詢例select*frommarketStartwithmarket_id=14Connectbypriorparent_market_id=market_idConnect指定的遞歸條件，可以像where子句中的條件一樣，使用and或者or等運(yùn)算符來指定多個條件例select*frommarketStartwithmarketjd=14Connectbypriorparent_market_id=marketjdandinstr(market_name,洲)=0演示用connectby獲得所有中國市場的客戶信息。(具體看書)Select*frommarket_customerWheremarketjdin(SelectmarketjdfrommarketStartwithmarket_name='中國，Connectbypriormarketjd=parent_market_id)Sys_connect_by_path()函數(shù)的使用(具體看書316頁)第四章ORACLE數(shù)據(jù)表對象與其他數(shù)據(jù)庫不同，oracle數(shù)據(jù)庫的下一層邏輯結(jié)構(gòu)并非數(shù)據(jù)表，而是表空間；每個數(shù)據(jù)表都屬于唯一的表空間。I.oracle表空間表空間是oracle的開創(chuàng)性理念，他是一個邏輯對象。使用表空間有以下好處：.防止磁盤空間突然耗竭的風(fēng)險.規(guī)劃數(shù)據(jù)更靈活（類似于操作系統(tǒng)的文件夾功能）.提高數(shù)據(jù)庫性能（訪問頻繁的數(shù)據(jù)表放入單獨(dú)的表空間）.提高數(shù)據(jù)庫的安全性II.創(chuàng)立oracle表空間.Oracle表空間是一個邏輯概念，創(chuàng)立是需要指定物理文件，即實(shí)際數(shù)據(jù)分配磁盤空間。表空間的物理文件被稱為數(shù)據(jù)文件（datafile）；與此同時，應(yīng)同時指定數(shù)據(jù)文件的初始大小。創(chuàng)立一個名為USER1的表空間，其SQL語句如下：Createtablespaceuserldatafile'f:\database\oracle\user1_data.dbfsize20M?指定數(shù)據(jù)文件的可擴(kuò)展性表空間設(shè)計(jì)理念靈活性的一個方面在于數(shù)據(jù)文件的可擴(kuò)展性。當(dāng)存儲在某個數(shù)據(jù)文件中的數(shù)據(jù)量超過了其初始大小時，數(shù)據(jù)文件可以進(jìn)行自動擴(kuò)展。要像實(shí)現(xiàn)該功能，在創(chuàng)立表空間時應(yīng)該使用autoextend選項(xiàng)Createtablespaceuser2datafile'f:\database\oracle\user2_data.dbfsize20Mautoextendon要關(guān)閉自動擴(kuò)展，可以將autoextend選項(xiàng)的值設(shè)為“off”?指定數(shù)據(jù)文件的增長幅度數(shù)據(jù)文件默認(rèn)每次增長64K,當(dāng)頻繁增長時，可以使用next選項(xiàng)設(shè)定數(shù)據(jù)文件自動增長的幅度例createtablespaceuser3datafile'f:\database\oracle\user3_data.dbfsize20MAutoextendonnext5M數(shù)據(jù)文件的大小和自動增長的幅度單位只有KB和MB兩種。.指定數(shù)據(jù)文件的最大尺寸數(shù)據(jù)文件可以自動增長，但是無限制的增長往往帶來風(fēng)險。很多情況下，某臺服務(wù)器上可能同時運(yùn)行著多個系統(tǒng)，如郵件服務(wù)器和數(shù)據(jù)庫服務(wù)器可能為同一臺機(jī)器。，，因此除非特殊需要，應(yīng)為每個表空間的數(shù)據(jù)文件設(shè)定最大尺寸。應(yīng)使用maxsize選項(xiàng)Createtablespaceuser4datafile'f:\datafile\oracle\user4_data.dbfsize20MAutoextendonnext5mmaxsizeunlimited這里unlimited作用是不限制最大尺寸。.查看表空間是否創(chuàng)立成功表空間成功創(chuàng)立后，會在數(shù)據(jù)庫系統(tǒng)表中添加相應(yīng)的記錄，并且創(chuàng)立相應(yīng)的物理文件?？梢酝ㄟ^查看視圖dba_data_files中的記錄和實(shí)際數(shù)據(jù)文件存在性，來判斷表空間是否創(chuàng)立成功Selectfile_name,tablespace_namefromdba_data_filesorderbyfile_name,為一個表空間創(chuàng)立多個數(shù)據(jù)文件.一個表空間可以有多個數(shù)據(jù)文件，為一個表空間創(chuàng)立多個數(shù)據(jù)文件需要指定多個數(shù)據(jù)文件的完整路徑和詳細(xì)的選項(xiàng)參數(shù)。各數(shù)據(jù)文件之間使用分隔。例selecttablespacemultiple_data_filedatafile'f:\database\tmp\data_1.dbfsizeIM,'f:\database\tmp\data_2.dbfsize5MIII.查看表空間Dba_tablespaces可以用來查看所有表空間的基本信息；而dba_data_files可以用來查看相關(guān)數(shù)據(jù)文件的信息。查看表空間信息，可以利用如下SQL語句：Selecttablespace_name,status,allocation_typefromdba_tablespacesW,修改數(shù)據(jù)庫默認(rèn)表空間查詢每個用戶的默認(rèn)表空間，可以使用如下SQL語句：Selectuser_id,username,default_tablespacefromdba_users修改數(shù)據(jù)庫默認(rèn)的表空間的SQL語句如下：Alterdatabasedefaulttablespaceuserl.修改表空間名稱修改表空間名稱使用rename命令A(yù)ltertablespaceuser2renametouser20表空間重命名并不對數(shù)據(jù)文件產(chǎn)生影響，需要注意的是，不能對數(shù)據(jù)的系統(tǒng)表空間進(jìn)行重命名，例如SYSTEM,SYSAUX等無法進(jìn)行重命名。.刪除表空間刪除表空間的命令為droptablespaceo刪除表空間有兩種方式，一種是僅僅刪除其在數(shù)據(jù)庫中的記錄，另一種是將記錄和數(shù)據(jù)文件一起刪除。僅刪除其在數(shù)據(jù)庫中的記錄的SQL語句如下：Droptablespaceuser20刪除表空間及其數(shù)據(jù)文件的SQL語句如下：Droptablespaceuser20includingcontentsanddatafiles.創(chuàng)立oracle數(shù)據(jù)表利用命令創(chuàng)立數(shù)據(jù)表例createtablet_user(user_idnumbernotnull,user_namevarchar2(20)notnull,user_emailvarchar2(30))利用命令查看表結(jié)構(gòu)Selecttable_name,tablespace_namefromuser_tableswheretable_name=,t_user'視圖user_tables可以用來查看所有用戶表的基本信息?？梢栽赟QLPlus或PL/SQLDeveloper的[CommandWindow]執(zhí)行如下SQL語句來查看數(shù)據(jù)表信息Describetuser;.修改oracle數(shù)據(jù)表結(jié)構(gòu)利用命令修改數(shù)據(jù)表結(jié)構(gòu)修改數(shù)據(jù)表結(jié)構(gòu)的SQL命令為altertable可以利用RENAME選項(xiàng)，對列名進(jìn)行修改例altertablet_userrenamecolumnuser_emailtoemail列更名時，不會影響數(shù)據(jù)庫中的記錄；但注意不能將列名更新為與其他已存在列名相同?？梢岳胢odify選項(xiàng)對列的屬性進(jìn)行修改，以修改USER_NAME的長度為例，相應(yīng)的SQL語句如下：Altertablet_usermodify(user_namevarchar2(15))Oracle允許一次修改多個列屬性。例altertablet_usermodify(user_namevarchar2(15),emailvarchar2(30))使用altertable命令同樣可以為表添加一列例altertablet_useradd(remarksvarchar2(50))刪除表中的操作使用drop選項(xiàng)第一章SQL更新數(shù)據(jù)I.插入數(shù)據(jù)一.Insert語句向表中插入數(shù)據(jù).為所有列顯示賦值Insertintopeoplevalues(people_seq.nextval,1,'ACT');.同時指定要插入的列名列表和列值列表Insertintotable_name(column1,column2,...)values(value1,value2二.利用子查詢批量插入數(shù)據(jù)此時的SQL語句除了包含insertinto命令之外，還應(yīng)包含一個查詢語句，其語法規(guī)那么如下：Insertintotable_nameselect...例：insertintopeopleselectemployeejd,employee_name,statusfromt_employeeswhereemployee_id>=3;n.修改數(shù)據(jù)Update修改數(shù)據(jù)一般有以下幾種情況：直接修改單列的值，直接修改多列的值、利用where子句限制修改范圍和利用視圖修改數(shù)據(jù)。L修改單列的值updatetable_namesetcolum=value例updatepeoplesetstatus='CXL';例altertablet_userdropcolumnremarkAvertable命令除了對列進(jìn)行修改外，還可以對表本身的屬性進(jìn)行修改。例如，利用rename選項(xiàng)修改表名Altertablet_userrenametot_users（注意，不要輕易修改表名）.刪除數(shù)據(jù)表刪除數(shù)據(jù)表的命令為droptable例droptablet_dropDroptable和delete命令的區(qū)別：droptable用于刪除數(shù)據(jù)表，一旦刪除，該數(shù)據(jù)表在數(shù)據(jù)庫中將不再存在，而delete那么用于刪除數(shù)據(jù)表的記錄，無論記錄有無，數(shù)據(jù)表仍然是存在的。有時，某些數(shù)據(jù)表的列被其他表引用，如外鍵引用，直接使用droptable將無法刪除該表。此時，需要使用cascadeconstraints選項(xiàng)。例droptablet_dropcascadeconstraintscascadeconstraints表示刪除表時，將一起作用于約束。,備份/恢復(fù)數(shù)據(jù)表數(shù)據(jù)表的備份和恢復(fù)是最常用的數(shù)據(jù)庫操作，數(shù)據(jù)表的備份主要用于以下場合修改數(shù)據(jù)表結(jié)構(gòu)之前修改數(shù)據(jù)表的數(shù)據(jù)之前刪除某個數(shù)據(jù)表之前利用命令備份/恢復(fù)數(shù)據(jù)表對于oracle數(shù)據(jù)表的備份/恢復(fù)操作，最常用的命令為exp和impoExp命令實(shí)現(xiàn)導(dǎo)出操作，imp實(shí)現(xiàn)導(dǎo)入操作。使用exp導(dǎo)出表T_USERS的步驟如下Expsystem/abc123@testtables=(T_USERS)file=D:\temp\command\T_USERS.dmp使用imp導(dǎo)入數(shù)據(jù)表的步驟如下所示：Impsystem/abc123@testfile=D:\temp\command\T_USERS.dmptables=T_USERS注意，tabi6s參數(shù)是必需的，而且要與導(dǎo)出時的表名保持一致.臨時表Oracle使用createtable命令創(chuàng)立的數(shù)據(jù)表稱為永久表或普通表。在oracle中還有另外一種特殊的數(shù)據(jù)表臨時表。臨時表指的是所存儲的數(shù)據(jù)的臨時性，臨時表雖然一直存在，但是其中的數(shù)據(jù)會在某種條件下被oracle數(shù)據(jù)庫自動清空。臨時表數(shù)據(jù)清空的條件有兩種，一種是事物提交或回滾；二是會話結(jié)束。創(chuàng)立臨時表應(yīng)該使用createglobaltemporarytable命令創(chuàng)立名為tmp_users_session的會話級臨時表。Createglobaltemporarytabletmp_users_session(userjdint,user_namevarchar2(20),user_emailvarchar2(30))oncommitpreserverows創(chuàng)立事務(wù)級臨時表例createglobaltemporarytabletmp_users_transaction(user_idint,user_namevarchar2(20),user_emailvarchar2(30))oncommitdeleterowsVII.特殊的表dual第五章約束—.主鍵約束主鍵約束可以保存數(shù)據(jù)完整性，即防止數(shù)據(jù)表中的兩條記錄完全相同，通過將主鍵納入查詢條件，可以到達(dá)查詢結(jié)果最多返回一條記錄的目的。.創(chuàng)立主鍵約束Createtablestudent(studentjdnumberprimarykey,student_namevarchar2(20),student_birthdaydate,student_addressvarchar2(50),student_phonevhar2(20))查看約束Selecttable_name,constraint_name,constraint_type,statusfromuser_constraintswheretable_name="STUDENT'演示獲得主鍵的作用列Selectconstraintname,tablename,columnnamefromuser_cons_columnswhereconstraint_name='SYS_C00512r創(chuàng)立表時顯示命名主鍵Createtablestudent(studentjdnumberconstraintpk_studentprimarykey,student_namevarchar2(20),student_brithdaydate,student_addressvarchar2(50),student_phonevarchar2(20))創(chuàng)立主鍵的另一種寫法Createtablestudent(studentjdnumber,student_namevarchar2(20),student_birthdaydate,student_addressvarchar2(50),student_phonevarchar2(20)constraintpk_studentprimarykey(student_id))在student_name,student_birthday,student_address三列上創(chuàng)立主鍵約束。Createtablestudent(student_idnumber,student_namevarchar2(20),student_birthdaydate,student_addressvarchar2(50),student_phonevarchar2(20),constraintpk_studentprimarykey(student_name,student_birthday,student_address)).修改表的主鍵約束為已存在的表創(chuàng)立主鍵Altertablestudentmodify(studentJdnumberprimarykey)當(dāng)要添加的主鍵作用于多個列時，可以通過添加主鍵命令，而不是通過修改列的屬性來添加主鍵。例altertablestudentaddconstraintpk_studentprimarykey(student_name,student_birthday,student_address)刪除主鍵Altertablestudentdropprimarykey利用刪除約束的語法刪除主鍵Altertablestudentdropconstraintpk_student列status標(biāo)識了約束的可用性演示禁用主鍵約束Altertablestudentdisableprimarykey啟用主鍵約束的SQL語句如下：Altertablestudentenableprimarykey重命名主鍵AltertablestudentrenameconstraintSYS_C005145toPK_STUDENT.利用update命令同時修改多列的值。Updatetable_namesetcolum1=value1,colum2=value2updatepeoplesetid=(20000+id),status='ACT";.利用where了,句限制修改范圍例updatepeoplesetstatus='CXL'whereid>20010;m.刪除數(shù)據(jù)Delete命令刪除表中的某些記錄，truncate命令的作用是刪除整個數(shù)據(jù)表。語法規(guī)那么deletefromtable_namewhere例deletefrompeoplepwhereexists(select1fromt_employeesewheree.employee_name=)Truncate命令的語法規(guī)那么Truncatetabletable_name例truncatetablepeopleDDL不能回滾，DML可以回滾回滾命令:rollback提交命令:commit第二章序列I.創(chuàng)立序歹|J使用createsequence命令例createsequenceemployee_seq序列有兩個重要屬性cuirval和nextvaLCuirval用于獲得序列的當(dāng)前值，nextval用于獲得序列的下一個值。每次調(diào)用nextval,都會使序列的當(dāng)前值增加單位步長（默認(rèn)步長為l）o序列的調(diào)用方法為seq.currval和seq.nextval。但是，需要注意的是，在序列創(chuàng)立之后，應(yīng)該首先使用seq.nextval,然后才能使用seq.currvalo使用方法：Selectemployee_seq.nextvalfromdualSelectemployee_seq.currvalfromdual向表中插入新的員工信息：Insertintot_employeesvalues（employee_seq.nextval；陸遜\O「ACT）;刪除序列：Dropsequenceemployee_seq創(chuàng)立序列，并指定startwith選項(xiàng)：Createsequenceemployee_seqstartwith12n.修改序列屬性修改minvalue和maxvalue的值A(chǔ)ltersequenceemployee_seqminvalue20;Altersequenceemployee_seqmaxvalue99999;設(shè)置maxvalue值為無限Altersequenceemployee_seqnomaxvalue；利用alter命令修改步長為5：Altersequencetest_seqincrementby5；III.修改cycleCycle選項(xiàng)用于指定序列在獲得最大值的下一個值時，從頭開始獲取，這里的頭即為minvahie指定的值。修改序列test_seq為可循環(huán)Altersequencetest_seqcycle；關(guān)閉可循環(huán)Altersequencetest_seqnocycle；Cache是序列緩存，其實(shí)際意義為，每次利用nextval,并非直接操作序列，而是一次性獲取多個值的列表到緩存。使用nextval獲得的值，實(shí)際是從緩存抓取。抓取的值依賴于序列的cuiral和步長incrementby。緩存的值（在這里，即容量20）必須小于一次循環(huán)所能獲得的數(shù)目。Cache的存在會對其他修改其他選項(xiàng)產(chǎn)生影響。第三章SQL查詢I.基本查詢例selectemployee_namefromt_employees;當(dāng)from子句中含有多個數(shù)據(jù)源時，這些數(shù)據(jù)源將實(shí)現(xiàn)笛卡爾積。Where子句Where子句用于過濾from子句所獲得的數(shù)據(jù)源。例selecte.employee_id,e.employ_name,s.salaryfromt_employeese,t_salarysWheree.employee_id=s.employee_id（當(dāng)from子句中含有多個數(shù)據(jù)表或其他數(shù)據(jù)源時，為各數(shù)據(jù)表指定別名是一個良好的習(xí)慣，即使select語句所要捕獲的列不會引起列名的混淆。）利用distinct獲得唯一性記錄，被distinct限制的既可以是單個列，也可以是多個列的組合。演示利用distinct關(guān)鍵字獲得工資表中的所有月份Selectdistinctmonthfromt_salary可以獲得員工的工資的唯一性記錄，以表現(xiàn)員工的工資變化：Selectdistincte.employee_name,s.salaryFromt_employeee,t_salarysWheree.employeejd=s.employee_id此語句捕獲唯一的employee_name和salary的組合。Orderby子句Orderby子句可以將查詢結(jié)果按照一定的順序進(jìn)行排序。Selectdistincte.employee_name,s.salaryFromt_employeee,t_salarysWheree.employee_id=s.employeeJdOrderbys.salary默認(rèn)進(jìn)行升序排列。升asc降desc例orderbys.salarydescGroupby子句Groupby子句用于對記錄集合進(jìn)行分組，一旦使用了分組之后，select語句的真實(shí)操作目標(biāo)即為各個分組數(shù)據(jù)，每次循環(huán)處理的也是各個分組，而不再是單條記錄。Selecte.employee_name,sum(s.salary)Fromt_employeee,t_salarysWheree