數(shù)據(jù)庫教學課件第7天oracle函數(shù)總結(jié)

OraclesqlOracle常用sqlOraclesqlOracle常用sql操作整理總一、選1.簡單的SELECT語SELECT1AS字段名1解釋FROM2.處理NVL函數(shù)可把NULL轉(zhuǎn)換成其它類型的編程技巧:NVL函數(shù)在多條件模糊查詢的時候比較有NVL函數(shù)可返回多種數(shù)據(jù)類型返回日期NVL(start_date,'2002-02-返回字NVL(title,'no返回數(shù)字3.SQLdesctable;顯示表結(jié)SQLselect*fromtab;查看用戶下所有的SQLsetpauseon;可以使大量結(jié)果集在用戶按“Enter”(回車)SQLsetpagesize100;設定SQL語句返回結(jié)果集一頁的100,默認14SQL>setlinesize100;SQL語句返回結(jié)果集一行的寬度100,默認值是804.SQL*PLUS里規(guī)定字段的顯示格式SQL>column字段名format99999999999;SQL>column字段名format999,999,999,999;SQL>column字段名formata[word_wrapped];規(guī)定long字符的顯示寬度SQL>setlong規(guī)定字段名的顯示內(nèi)SQLcolumn字段名heading字段名顯示內(nèi)容';SQL>setheadingoff;查詢時不顯示字段名稱SQLcolumn字段名justify[left|right|清除字段的格SQLcolumn字段名5.SQL*PLUS里規(guī)定字段的顯示格式例SQLcolumn字段名5.SQL*PLUS里規(guī)定字段的顯示格式例SQL>columnlast_nameheading'Employee|Name'formata15;SQL>columnsalaryjustifyrightformat$99,999.99;SQL>columnstart_dateformata10null'Not說明：如果start_datenull,顯示字符串'Not6.判斷題(T/F)(1).SQL(2).darealwaysheldinsqlbuffer.dassitwithquerydata.7、SQL*PLUS命令只控制SELECT結(jié)果集的顯示格式及控制文件.只有SQL數(shù)據(jù)庫二、限制選擇1.按指定的規(guī)則SELECTexprFROMtable[ORDERBY{column,expr}[ASC|DESC]默認的排序是ASC升序(由小到大還可以ORDERBY字段名的位置[1]|2]ASC|2.用WHERE限制選擇行比較操^=與NULL比較不能用上面的比較操作符ANYSOMESQL操作符N…AND…INLIKEISNOTN…AND…NOTINNOTLIKEISNOT邏輯操ANDOR3.用WHERE限制選擇行(1).=<>>=<=inlikeisnullbet(2).and(3).n4.LIKE操%零到任意多個字符_一個例如:字段名%零到任意多個字符_一個例如:字段名likeM%'字段名likem%'like如果要找含下劃線的字符,要加反斜線如:字段名like%X/_Y5.日期字段的比舉例日期字段nto_date('2001-12-12','YYYY-MM-DD')andto_date('2002-02-01','YYYY-MM-日期字段>to_date('2001-12-12','YYYY-MM-DD')and日期字段to_date('2002-02-01','YYYY-MM-6.不能用到索引的比較操作ISNULLISNOTNULLLIKE三、單行函1.數(shù)字函ABS取絕對ERLN10為底SQRT平方根EXPen次乘方LOG(m,n)m為底數(shù)n 算函數(shù):ACOSATANATAN2COSCOSHSIGNSINSINHTANCEIL大于或等于取整數(shù)FLOOR小于或等于取整數(shù)MOD取余數(shù)ROUND(n,m)m的位數(shù)取四舍五入值如果round(日期):12以后將是明天的日期.TRUNC(n,m)按m的位數(shù)取小數(shù)點后的trunc(日期),確省的是去掉2.字符函CHR按數(shù)據(jù)庫的字符集由數(shù)字返回CONCAT(c1,c2)把兩個字符c1,c2一個字符,||相REPLACE(c,s,r)c里出現(xiàn)s的字符替換成r,返回新字SUBSTR(c,m,n)m0,字符c從前面m處開始取n位字符,m01一樣,m小與0,c從后面m處開始取n位字符TRANSLATE(c,f1,t1)字符c按f1t1的規(guī)則轉(zhuǎn)換成新的字符INITCAP字符首字母大寫,其它字符小LOWER字符全部小UPPER字符全部LTRIM(c1,c2)去掉字符c1左邊出現(xiàn)的字符TRIM(c1,c2)去掉字符c1左右兩邊的字符c2LPAD(c1,n,c2)字符c1按制定的n顯示的位數(shù)用c2字TRIM(c1,c2)去掉字符c1左右兩邊的字符c2LPAD(c1,n,c2)字符c1按制定的n顯示的位數(shù)用c2字符串替換左邊的空3.日期函ADD_MONTHS(d,n)日期值加nLAST_DAY返回當月的最后一天的N(d1,d2)兩個日期值間的月份,d1<d2返回NEXT_DAY返回日期值下一天的日SYSDATE當前的系統(tǒng)時DUAL是SYS用戶下一個空表，它只有一個字段4.轉(zhuǎn)換函數(shù)(1)TO_CHAR(number)用于顯示或報表的格式TO_LOB把long字段轉(zhuǎn)換成lob字段TO_NUMBER(char)用于計算或者比較大4.轉(zhuǎn)換函數(shù)to_date里日期顯示YYYYYEARYYYYYQ季MMMONTHW(說明 kofmonth)WW,IWkof是按ISO標準,11日數(shù)到后面七天為一周,不一定是從周一到周日DDDAYHH24HH12MI分SS如果想固定日期的顯示格式可以在數(shù)據(jù)庫的參數(shù)文件initorasid.ora里新寫一行參NLS_DATE_FORMAT=yyyy-mm-hh24:mi:ss可以在UNIX環(huán)境變量或者NT表里的設置NLS_DATE_FORMAT=yyyy-mm-dd4.轉(zhuǎn)換函數(shù)如果想固定日期的顯示格式可以用alter命令SQL>alter它的作用順序如下setnls_date_format='yyyy-mm-ddinitializationALTERd4.轉(zhuǎn)換函數(shù)ALTERd4.轉(zhuǎn)換函數(shù)(4)9數(shù)字位0數(shù)字前0to_char(-.小數(shù)點的,標記位置的逗號數(shù)字顯示格式的左L根據(jù)數(shù)據(jù)庫字符集加貨幣to_char(-1200,'L9999.99')B0顯示為空格,用在數(shù)字顯示格式的右邊MI右邊的負數(shù)標記to_char(-1200,'9999.99MI')PR括起來的負數(shù)to_char(-1200,'9999.99PR')EEEE用指數(shù)方式to_char(-輸入字符,返回數(shù)字的instr(c1,c2)字符c2出現(xiàn)在c1的位置,不出現(xiàn),返回0,常用于模糊查length按數(shù)據(jù)庫的字符集,算出字符c的長度,跟數(shù)據(jù)庫的字符集有關,一個漢字長度為有邏輯比較的函數(shù)NVL(EXPR1,EXPR2)函數(shù)解釋:IFEXPR1=NULLRETURNEXPR2ELSERETURNEXPR1 )解釋IFAA=V1THENRETURNIFAA=V2THENRETURNRETURN舉例:decode(id,1,'deptsale',2,'dept四、從多個表里選取1.數(shù)據(jù)表間的連接SELECT1,字段名2FROM1,表名2WHERE表名1.字段名=2.字段名[AND……];SELECT1,字段名2FROM1,表名2WHERE表名1.字段名=2.字段名(+)[AND……];等于的不等于的連接NANDIN注意INOR不能一起外連接個字段名(+),沒有滿足的條件補空自連接個表自己跟自己連接如找2.數(shù)據(jù)表間的連接例自連接個表自己跟自己連接如找2.數(shù)據(jù)表間的連接例刪除table_name表里字段 重復 SQL>deletefromtable_namet1wheret1.rowid>(selectmin(rowid)fromtable_namet2wheret1. =t2.grouphaving)>找 用戶的服務區(qū)域SQL>selecta.handphoneno,nvl(,'null'),a.totalscorefromtopscorea,chargeoperatorcc,chargeoperatorinfocwheresubstr(a.handphoneno,1,7)=cc.hpnohead(+)andorderbya.totalscore3.數(shù)據(jù)表間的連接技連接N個表,需要N-1個連接操被連接的表最好建一個單字符的別名,字段名前加上這個單字符的NAND..比用AND<=要連接操作的字段名上最好要有索連接操作的字段最好用整數(shù)數(shù)字類有外連接時,OR或IN的比較4.如何分析和執(zhí)行SQL語寫多表SQL語句時要知道它的分析執(zhí)行計劃的情況.Sys用戶下運行@/ORACLE_HOME/sqlplus/admin/plustrql產(chǎn)生plustrace角Sys用戶下把此角色賦予一般SQLgrantplustraceto一般用戶下運行@/RACE_OE/rms/ami/tlxla.sl產(chǎn)生l_tleSQLsettimeon;說明：打開時間顯SQL>setautotraceon;說明：打開自動分析統(tǒng)計，并顯示SQL語句的運行結(jié)果SQL>setautotracetraceonly;說明：打開自動分析統(tǒng)計，不顯示SQL語句的運行結(jié)果 試SQL語句，看到其分析統(tǒng)計結(jié)果了。一般來講 的SQL語句應該避免大表的全表掃描SQLsetautotraceoff;說明：關閉自動分析統(tǒng)五、集合函經(jīng)常和groupby一起使1.集合函數(shù)列AVGDISTINCT1.集合函數(shù)列AVGDISTINCT|ALL|N)取平COUNTDISTINCT|ALL|N|expr|*)統(tǒng)計數(shù)MAXDISTINCT|ALL|N)取最大值MINDISTINCT|ALL|N)取最小值SUMDISTINCT|ALL|N)取合STDDEV(DISTINCT|ALL|N)取偏差值,如果組里選擇的內(nèi)容都相同,VARIANCE(DISTINCT|ALL|N)取平方2.使用集合函數(shù)的語SELECTcolumn,group_functionFROMtableWHEREconditionGROUPBYgroup_by_expresHAVINGgroup_conditionORDERBY3.使用count時的注意事項SELECTCOUNT(*)FROMtable;SELECTCOUNT(常量FROMtable;都是統(tǒng)計表 數(shù)量,如果沒有PK后者要好一SELECTCOUNT(all字段名FROMtable;SELECTCOUNT(字段名)FROMtable;不會統(tǒng)計為NULL的字段的SUM,AVG時都會忽略為NULL4.用groupby時的限制條SELECT字段名不能隨意,要包含在GROUPBY的字GROUPBYORDERBY時不能用位置符號和別名限制GROUPBY的顯示結(jié)果,HAVING條件5.例SQL>selecttitle,sum(salary)payrollfroms_empwheretitlelike'VP%'groupbytitlehavingsum(salary)>5000orderbysum(salary)找出某表里字段重復 數(shù),并顯SQL>select(duplicatefieldnames)fromtable_namegroupby(listoutfields)havingcount(*)>1;6.判斷題GroupfunctionsincludenullsincalculationsUsingthehavingGroupfunctionsincludenullsincalculationsUsingthehavingclausetoexcluderowsfromagroupcalculation解釋Groupfunction都是忽略NULL值的您要計算NULL值,用NVL函Where語句在GroupBy前把結(jié)果集排除在外Having語句在GroupBy后把結(jié)果集排除在六、子1.查詢語句可以例如SELECT……FROMSELECTFROM1,2,……]WHERE條件WHERE條件2.何處可用子查詢當查詢條件是不確定的條件DML(insert,update,delete)語句里也可用子查詢HAVING里也可用子查詢3.兩個查詢語句的結(jié)果可以做集合操例如并集UNION(去掉重)并集UNIONALL(不去掉重復差集MINUS,交 )4.子查詢的注意先執(zhí)行括號里SQL語句，一層層到外面如果里層的結(jié)果集返回多個，不能用等比較符要用5.子查詢的例子SQL>selecttitle,avg(salary)froms_empgroupbytitleHavingavg(salary)=(selectmin(avg(salary))froms_empgroupbytitle);找到最低平均工資的職位名稱和工5.子查詢的例子子查詢可以用父查詢里的表這條SQL語句是這條SQL語句是對的SQL>selectcty_namefromcitywherest_code(selectst_codefromsewherest_name='TENNESSEE'說明：父查詢調(diào)用子查詢只執(zhí)行一6.取出80100的SQL語RACE處理每個結(jié)果集只有一個RWNUM字段標明它的邏輯位置,并且只能用RWNU<100,不能用RWNU>80。以下是經(jīng)過分析后較ORACLE取得結(jié)果集80100間的SQL語ID是唯一關鍵字的字語句寫SQL>select*from(selectrownumasnumrow,c.*fromselectfield_namefromtable_namewhere1orderby2)c)wherenumrow>80andnumrow<=100)orderby條件七、在執(zhí)行SQL語句時綁定變1.接收和定義變量的SQL*PLUS命&2.綁定變量SQL語句的例子SQL>selectid,last_name,salaryfroms_empwheredept_id=Entervaluefordepartment_number:old1:selectid,last_name,salaryfroms_empwheredept_id=&department_number;new1:selectid,last_name,salaryfroms_empwheredept_id=10SQLSETVERIFYOFF|ON;可以關閉和打開提示確認信息old1new1的顯示3.綁定變量SQL語句的例子(2)SQLselectidlast_namesalaryfroms_empwheretitle=Entervalueforjob_title:StockClerkSQL>selectid,last_name,salaryfroms_empwherehiredate>to_date('&start_hire_date','YYYY-MM-Entervalueforstart_hire_date:2001-01-把綁定字符串和日期類型變量時，變量把綁定字符串和日期類型變量時，變量外面要加單引也可綁定變量來查詢不同的字段輸入變量值的時候不要加;等其它符4.ACCEPT的語法和SQL>ACCEPTvariable[daype][FORMAT][PROMPTtext]說明variable指變量名daype指變量類型，如number,charformat指變量顯示格式prompttext可自定義彈出提示符的內(nèi)容texthide隱藏用戶的輸入符號使用ACCEPT的例子ACCEPTp_dnamePROMPT'Providethedepartmentname:ACCEPTp_salaryROMPT'Salaryamount:ACCEPTpswdCHARPROMPT'Password:'ACCEPTlow_datedateformat'YYYY-MM-DD'PROMPT“Enterthelowdaterange('YYYY-MM-4.DEFINE的語法和例SQL>DEFINEvariable=說明variable指變量名value指變量定義好了變良值后,執(zhí)行綁定變SQL語句時不再提示輸入變量使用DEFINE的例子：SQL>DEFINEdname=salesSQL>DEFINEdnameDEFINEdname=“sales”SQL>selectnamefromdeptwherelower(name)='&dname';SQL>UNDEFINESQL>DEFINESymboldnameis5.SQL*PLUS里傳遞參數(shù)到保存好的*.sql文件SQL>@/路徑名/1[,2,….]SQLstart路徑名/文件名1[,參數(shù)名2一次最多只能9個&變量,變量名稱只能是從&1,&2到&9如果在SQL*PLUS里要把&符號保存在ORACLE數(shù)據(jù)庫里,要修改sql*plus環(huán)境變量SQL>setdefine八、概述數(shù)據(jù)模型和數(shù)據(jù)庫1.系統(tǒng)開發(fā)的階段StrategyandBuildand2.數(shù)據(jù)八、概述數(shù)據(jù)模型和數(shù)據(jù)庫1.系統(tǒng)開發(fā)的階段StrategyandBuildand2.數(shù)據(jù)模Mofsystemincnt'sEntityTableofcnt'sofentityTableson3.實體關系模型(ERM)概ERM(entityrelationship實體有特定信息的目標和事件:客戶,屬性述實體的如,關系個實體間的關系如:訂單和產(chǎn)品等Dashedline(虛線)可選參數(shù)“maybe”Solidline(實線)必選參數(shù)“mustbe”Crow'sfoot多線)程度“oneormore”Singleline單線)程度“oneandonly4.實體關系模型每個訂單都必須有一個或幾個客每個客戶可能是一個或幾個訂單的申請5.實體關系的類1:1如:M:1多對一:乘客和1:M一對多:員工和6.校正實體關系的原屬性是單一值的,不會有屬性必須依存于實體,要有唯一沒有非唯一屬性依賴于另一個非唯一的屬7.定義結(jié)構時的注意事減少數(shù)據(jù)冗沒有非唯一屬性依賴于另一個非唯一的屬7.定義結(jié)構時的注意事減少數(shù)據(jù)冗減少完整性約束產(chǎn)確認省略的實體,關系和屬8.完整性約束的Primarykey主關鍵字唯一非Foreignkey外鍵于另一個Primarykey,可能為NULLColumn字段名符合定義的類型和長度約束條件用戶自定義的約束條件,要符合工作流要例如:一個銷的提 過它的基本工Candidatekey候選主關個字段名可組成候選主關鍵字,其組合是唯一和非NULL9.把實體關系到關系數(shù)據(jù)庫對象的方把簡單到數(shù)據(jù)庫里把屬 到數(shù)據(jù)庫里的表的字段,標明類型和注把唯一到數(shù)據(jù)庫里的唯一關鍵把實體間的關到數(shù)據(jù)庫里的其它的考慮設計索引,使查詢更建立視圖,使信息有不同的呈現(xiàn)面,減少復雜的SQL語計空間的分重新定義完整性約束10.實體關系圖里符號的PK唯一關鍵字的字FK外鍵的字FK1,FK2同一個表的兩個不同的外鍵FK1,FK1兩個字段共同組成一個外鍵NN非null字段U唯一字U1,U1兩個字段共同組成一個唯一字九、創(chuàng)1.ORACLE常用的字段類ORACLE常用的字段類VARCHAR2size)可變長度的字符串,必須規(guī)定CHAR(size)固定長度的字符串,不規(guī)定長度默認值為,s)數(shù)字型p是CHAR(size)固定長度的字符串,不規(guī)定長度默認值為,s)數(shù)字型p是位數(shù)總長度s是小數(shù)的長度,可存負最長38位.不夠位時會四舍五入.DATE日期和時間類型LOB超長字符,最大可達4GCLOB超長文本字符串BLOB超長二進制字符串BFILE超長二進制字符串,保存在數(shù)據(jù)庫外的文件里是只讀的.原始數(shù)字字段類型位 的NumberNumber12345678Number錯Number(9,1)1234567.9Number(9,3)錯Number(7,2)錯Number(5,-2)Number(5,-4)Number(*,1)2.創(chuàng)建表時給 默認值和約束條創(chuàng)建表時可以上默例如:日期字段DEFAULT這樣每 和修改時,不用程序操作這個字段都能得到動作的時創(chuàng)建表時可以例如:NOTNULL不允許重復UNIQUE關鍵字PRIMARYKEY上約束條按條件CHECK條件外鍵表名(字段名3.創(chuàng)建表的例CREATETABLEEPTNONUMBER(2)CONSTRADNAMEVARCHAR2(14),LOCVARCHAR2(13))CREATETABLEPK_DEPTPRIMARYIDnumber(2)NOTNULLPRIMARYtcodenumberdefault'0'NOTNULL,areanamevarchar2(30)default''NOTNULL);4.創(chuàng)建表 名規(guī)則和注意事表名和字段 名規(guī)則：必須以字母開頭，可以含符號A-Z,a-z,0-大小寫不區(qū)不用SQL里的保留字,一定要4.創(chuàng)建表 名規(guī)則和注意事表名和字段 名規(guī)則：必須以字母開頭，可以含符號A-Z,a-z,0-大小寫不區(qū)不用SQL里的保留字,一定要用時引號把字符串括起來注意事項:建表時可以用中文的字段名,但最好還是用英文的字 面,可能為空的字段放在后面一個表的最多字段個數(shù)也是有限制的，254個5.約束 名規(guī)則和語(n是數(shù)字約束名規(guī)則約束名如果在建表的時候沒有指明，系統(tǒng)命約束名字符名規(guī)則同于表和字段6.使用約束時的注意事約束里不能用系統(tǒng)函數(shù),SYSDATE和別的表的字段比較想在事務處理后,做約束的檢SQL>altersetconstras7.由實體關系圖到創(chuàng)建表的例子前提條件:已有region表且含唯一關鍵字的字段idSQL>CREATETABLEs_dept(ids_dept_id_pkPRIMARYnames_dept_name_nnNOTregion_ids_dept_region_id_fkregions_dept_name_region_id_ukUNIQUE(name,8.較復雜的創(chuàng)建表例SQL>CREATETABLEs_empidNUMBER(7) s_emp_id_pkPRIMARYlast_nameVARCHAR2(25)s_emp_last_name_nnNOTuseridVARCHAR2s_emp_userid_nnNOTs_emp_userid_ukstart_dateDATEDEFAULTmanager_idNUMBER(7),titleVARCHAR2(25),dept_idNUMBER(7)salarys_emp_dept_id_fk _pctNUMBER(4,2)_pct_ck_pct9.通過子查詢建manager_idNUMBER(7),titleVARCHAR2(25),dept_idNUMBER(7)salarys_emp_dept_id_fk _pctNUMBER(4,2)_pct_ck_pct9.通過子查詢建通過子查詢建表的例SQL>CREATETABLEemp_41FROMs_empWHEREdept_id=ECTid,last_name,userid,SQL>CREATETABLEA只要表的結(jié)構ect*fromBwhere10.用子查詢建表的注意可以關連多個表及用集合函數(shù)生成新表,注意選擇出來的字段必須有合法的字段名稱,且不能重復。用子查詢方式建立的表，只有非空NOTNULL的約束條件能繼承過來,其它的約束條件和默認值都沒有繼承過來根據(jù)需要，可以用altertableadd ……再建立其它的約束條件，如primarykey等11.ForeignKey的可選ONDELETE在創(chuàng)建ForeignKey時可以加可選參數(shù)ONDELETECASCADE它的含義是如果刪除外鍵主表里的內(nèi)容，子表里相關的內(nèi)容將一起被刪除如果沒有ONDELETECASCADE參數(shù)，子表里有內(nèi)容，父表里的主關鍵不能被刪除掉12.如果數(shù)據(jù)庫表里有不滿足 存在，建立約束條件將不會成功13.給表創(chuàng)建和刪除同義詞的例子SQL>CREATESYNONYMd_sum2FORdept_sum_vu;SQL>CREATEPUBLICSYNONYM2FORSQL>DROPSYNONYM十、ORACLE里的數(shù)據(jù)數(shù)據(jù)字典?ORACLE的數(shù)據(jù)字典是數(shù)據(jù)庫的重要組成部分之一，它隨著數(shù)據(jù)的產(chǎn)生而產(chǎn)生,隨著數(shù)據(jù)庫的變化而變化,體現(xiàn)為sys用戶下所有的一些表和視圖2.數(shù)據(jù)字的產(chǎn)生而產(chǎn)生,隨著數(shù)據(jù)庫的變化而變化,體現(xiàn)為sys用戶下所有的一些表和視圖2.數(shù)據(jù)字典里存了以下內(nèi)容用戶信用戶的權限信所有數(shù)據(jù)對象信息表的約束條件統(tǒng)計分析數(shù)據(jù)庫的視圖不能手工修改數(shù)據(jù)字典里的信息3.常用的數(shù)據(jù)字Dictionary存放所有數(shù)據(jù)表，視圖，同義詞名稱和解Dict_columns數(shù)據(jù)字典里字段名稱的和解Dba_users用戶Dba_data_files數(shù)據(jù)庫的文件Dba_free_space空閑表Dba_rollback_segs回滾User_objects數(shù)據(jù)對象User_constras約束條序列號User_views視User_indexes索引User_synonyms同義_roles用戶的User_role_privs用戶的角色權User_sys_privs用戶的系統(tǒng)權限User_tab_privs用戶的表級權實時用戶情況實時用戶實時系統(tǒng)實時SGA使V$locked_objectV$controlfile控制V$logfile日志V$parameter參數(shù)文4.數(shù)據(jù)字典的分類數(shù)據(jù)字典四大User_用戶下所有數(shù)據(jù)庫對All_用戶權限范圍內(nèi)所有的數(shù)據(jù)庫對Dba_所有的數(shù)據(jù)庫對;統(tǒng)計分析數(shù)據(jù)庫的視圖賦于oem_monitor權限非DBA用戶也可查詢V$*視5.查詢數(shù)據(jù)字SQL>select*fromdictionarywhereinstr(comments,'index')>0;SQL>selectconstra_name,constra_type,2search_condition,r_constra_name3fromuser_constras4wheretable_name=十一.控制1、INSERT(往數(shù)據(jù)的語句SQL o表名(字段名1,2,……)values(1,2SQL o表名(字1、INSERT(往數(shù)據(jù)的語句SQL o表名(字段名1,2,……)values(1,2SQL o表名(字段名1,2,select(1,字段名2,from另外的表名where條件;快 數(shù)據(jù)的方法,一般用于大于128M的數(shù)據(jù)轉(zhuǎn)SQLinsert*+append o表select*from另外的用戶名.另外的表名WHERE條件;SQL>commit;注意事用INSERT*+APPEND*/的 _tablename產(chǎn)生級別為6的獨占鎖如果運行此命令時還有 _tablename的DML操作會排隊在它后面對OLTP系統(tǒng)在用的表操作是不合適字符串類型的字段的注意事項字符串類型的字段值必須用單引號括起來,例如:’GOOD如果字段值里包含單引號’需要進行字符串轉(zhuǎn)換字符串類型的字段值超過定義的長度會出錯,最好‘’標記是NULLuser標明當前用把它替換成兩個前進行長度校日期字段的字段值可以用當前數(shù)據(jù)庫的系統(tǒng)時間SYSDATE,精確到秒用字符串轉(zhuǎn)換成日期型函數(shù)TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)TO_DATE()還有很多種日期格式,可以參看ORACLEDOC.年-月-日小時:分鐘:秒的格式Y(jié)YYY-MM-DDHH24:MI:SSNSERT時最大可操作的字符串長度小于等于4000個單字節(jié), 更長的字符串,請考慮字段用CLOB類型,方法借用ORACLE里自帶的DBMS_LOB程序包3、UPDATE(修改數(shù)據(jù)的語句SQL>UPDATE表名SET字段名1=值1,字段名2=值2,……WHERE條件;如果修改的值N沒有賦值或定義時,將把原來的 內(nèi)容清為NULL,最好在修改前進行非空校驗值N超過定義的長度會出錯,最好在 例子：SQLupdateselect*froms_dept)setid=50where4、DELETE(刪除數(shù)據(jù)表的語句SQLDELETEFROM表名WHERE條件并不 ORACLE里被占用的數(shù)據(jù)塊表空間注意它只把那些被刪除的數(shù)據(jù)塊標成如果確實要刪除一個大表里的全 ,可以用TRUNCATE命令,它可SQLTRUNCATETABLE如果確實要刪除一個大表里的全 ,可以用TRUNCATE命令,它可SQLTRUNCATETABLE表名此操作不可回退占用的數(shù)據(jù)塊表空5SQL語句的分數(shù)據(jù)定義語言(DDL)：create、alter、drop（創(chuàng)建、修改結(jié)構、刪除）（其他數(shù)據(jù) 語言(DM)：isrt、dlt、slet、pt（增、刪、查、改）（其他：trnt）數(shù)據(jù)控制語言(C)：rat、roe（ 、回收）、setrole事務控制：commit、rollback、savepo（其他：locktable、setconstra、set審計控制：audit、系統(tǒng)控制：altersystem會話控制：alter其他語句：comment（添加注釋）、explainplan yze、validate、6、ORACLE里事務控Commit提交事Rollback回退事設置斷點,在事務中標記位置,事務結(jié)束,斷事務結(jié)束的情況遇到commit或者rollback遇到DDL和DCL語句發(fā)現(xiàn)錯誤，如死鎖用戶退SQL*PLUS系統(tǒng)重啟7.DML操作的注意事以上SQL語句對表都加上了行級鎖,確認完成后必須加上事物處理結(jié) 令COMMIT才能正式生效否則改變不一定寫入數(shù)據(jù)庫里.行級鎖也未能得如果想撤回這些操作,可以用命令ROLLBACK復原.在運行INSERTDELETEUPDATE語句前最好估算一下可能操范圍應該把它限定在較小(一萬條 )范圍內(nèi),.否則ORACLE處理這個事物用到很大的回退段.程序響應慢甚至失去響應.如果 可以把SQL語句分段分次完成,其間加上COMMIT確認事物處理太過頻繁的commit不十二、改變表和約束1.改變表的幾種情況(1)運行時會加表級鎖SQLRENAME1TO表名2;SQLALTERTABLE1RENAMETO表名在表的后面增加一個SQLALTERTABLE表名ADD字段名段名DEFAULTexprNOTNULL2修改表里字段的定義SQLALTERTABLE表名MODIFY字段名11[DEFAULTexprNOTNULL2修改表里字段的定義SQLALTERTABLE表名MODIFY字段名11[DEFAULTexprNOTNULL2為空時，可以減少字段長改變字段類型修改DEFAULT值只作用于修改后的INSERT和UPDATE修改NOT約束只對現(xiàn)存含非的字段起1.改變表的幾種情況(2)運行時會加表級鎖SQLALTERTABLE表名DROP字段名給表里的 上/啟用約束SQLALTERTABLE表名ADD|DISABLE|ENABLEPRIMARYKEY字段名1[,2SQLALTERTABLE表名ADD|DISABLE|ENABLEUNIQUE1[,2加唯一關鍵字或者唯一約束條件時自動建立索說明 唯一關鍵字和唯一約束時索引仍然存在，可以被使用1.改變表的幾種情況(3)運行時會加表級鎖約束約束SQLALTERTABLE表名DROPS約束名會把約束相關的索引一起刪除.CACAE能同時刪去外鍵的約束條件.把表放在或取出數(shù)據(jù)庫的內(nèi)存區(qū)SQLALTERTABLE表名SQLALTERTABLE表名改變的表空SQLALTERTABLE表名MOVETABLESPACE表空注意:如果被轉(zhuǎn)移表空間的表含有索引,表轉(zhuǎn)移后索引變得不可用.2.刪除表及表里的數(shù)據(jù)SQLDROPTABLE[CASCADE清空表SQLTRUNCATETABLE表名按時間清空日志表里，使用重新命名的方(應用程序可能有短暫出錯,可以選擇在不繁忙的時間執(zhí)行按原來表A的建表語句創(chuàng)建新表A1,把表A重命名為A2（如果表A上有較頻繁的DML操作,會對表加上行級鎖，重命名過程用遞歸的方式循環(huán)做DML操作結(jié)束，命名成功).把創(chuàng)建新表A1重命名為歷 表A2備份或刪3.刪除表后應該刪除表后把表里的索引一起刪去刪除表后會結(jié)束基于它的懸而未決的事刪除表后根據(jù)表創(chuàng)建的views,synonym,storedprocedure,storedfunction依然存但views,synonym變的刪除表后根據(jù)表創(chuàng)建的views,synonym,storedprocedure,storedfunction依然存但views,synonym變的.需要手工找出它們并如果用了CASCADE此操作不可回S會把與它相關的約束一起4.給表加注釋SQLCOMMENTONTABLE表名|COLUMN表名.字段名IS加注釋的例SQL>commentontables_empis‘Enployeeinformation‘;SQL>commentoncolumns_emp.last_nameis‘‘;十三、創(chuàng)建序1.創(chuàng)建序列號里各參數(shù)的解SQL>CREATESEQUENCEname[INCREMENTBYn][STARTWITHn][{MAXVALUEn|NOMAXVALUE}][{MINVALUEn|NOMINVALUE}][{CYCLE|NOCYCLE}][{CACHEn|NOCACHE}]INCREMENTBYn一次增長n個數(shù)字NOMAXVALUE10E+27NOMINVALUE缺省值1NOCYCLE不循環(huán),常用于唯一關鍵CACHEn在內(nèi)存里緩存n個序列,出錯回退時會oracle8i里默n序列號的名稱一般可以采用“表名_字段名名規(guī)自動增長序列號字段的INSERT時如果要用到從1開始自動增長的數(shù)字做唯一關鍵字,應該先建立一個序列號CREATESEQUENCE序列號的名稱(最好是表名+序列號標記INCREMENTBY1STARTWITH1MAXVALUE99999NOCYCLENOCACHE;其中最大的值按字段的長度來定,比如定義的自動增長的序列NUMBER,最大值為999999INSERT語句 這個字段值為:序列號的名稱.NEXTVAL例子:SQL>insert os_dept(id,name,region_id)values(s_dept_id.nextval,'finance',2);1rowcreated.只有運行了序列號的名稱nextval后序列號的名稱currval才有效才有值3.查詢序列號的SQL>selectsequence_name,min_value,max_value,increment_by,last_numberfrom;其中l(wèi)ast_number指的是序列號的下一個值4.改變序SQL>ALTERequence[INCREMENTBYn]其中l(wèi)ast_number指的是序列號的下一個值4.改變序SQL>ALTERequence[INCREMENTBYn][{MAXVALUEn|[{MINVALUEn|[{CYCLE|NOCYCLE}][{CACHEn|注意:不能改變它的起始如果要改變序列的起始值,先把序列號刪除掉,再新建一個5.刪除序SQL>DROP6.不能用序列號的nextval和currval的地方有distinct的查有groupby,having,orderby的查詢表里的缺省十四、創(chuàng)建視1.視圖的概念和視圖是基于一個或多個表及視圖的一些查詢語句,它象顯示數(shù)據(jù)的視窗,它本身是不 ,更好的控制權限使用戶使用簡單的查詢語數(shù)據(jù)的數(shù)據(jù)的非依賴同一數(shù)據(jù)的不同表現(xiàn)2.創(chuàng)建視圖的語SQL>CREATE[ORREPLACE][FORCE|NOFORCE]VIEWview[(alias[,A[WITHCHECKOPTION[CONSTRA[WITHREADONLY]參數(shù)解釋constraFORCE表不存在時,視圖仍然可以創(chuàng)建成功WITHCHECKOPTION只有符合視圖定義的WITHREADONLY不允許DML操作Oracle8i以后創(chuàng)建視圖可以用order才入或3.創(chuàng)建修改視圖的例SQL>CREATEORREPLACEVIEWsalvu41last_nameLAST,salaryMONTHLY_SALARYFROMs_empWHEREdept_id=41;ECT3.創(chuàng)建修改視圖的例SQL>CREATEORREPLACEVIEWsalvu41last_nameLAST,salaryMONTHLY_SALARYFROMs_empWHEREdept_id=41;ECT,SQL>CREATEVIEWdept_sum_vu(name,minsal,maxsal,avgsal)ECT,AVG(e.salary)FROMs_empe,s_deptdWHEREe.dept_id=d.idGROUPBY注意:如果用select*fromtable_name創(chuàng)建的table_name的結(jié)構改變后view要重建或compile后才能顯示新的字段內(nèi)4.查詢視圖的數(shù)據(jù)字SQL>setlongSQL>selectview_name,textfrom說明:可以根據(jù)視圖text_length來設置setlong數(shù)字;User_updatable_columns視圖能查詢視圖里能被修改的字段5.簡單和復雜的視圖對單視圖雜視表的數(shù)量有函數(shù)嗎?沒有有有分組操作嗎?沒有有基于視圖的DML操作嗎?6.在視圖上可以用DML命令嗎?可以,但有一定的限制條件沒有下面的情況,可以刪除view里.groupfunction,groupby,沒有上面和下面的情況,可以修改view里例如salary*12含rownum的.字段表達式?jīng)]有上面兩種情況,view里含基表里所有非空字段的情況,view.7.在視圖里使用WITHCHECKOPTION約束條SQL>createorreplaceviewaect*froms_empwheredept_id=withcheckoption 如果運行下面命令會出錯ora-SQL>updateempvu41setdept_id=42where原因:empvu41里規(guī)定只能看41的修改后會 排除在視圖empvu41以8.刪除視SQL>DROPVIEW十五、創(chuàng)建索1.索引的8.刪除視SQL>DROPVIEW十五、創(chuàng)建索1.索引的概索引是數(shù)據(jù)庫里的一種數(shù)據(jù)它利用B*樹,hash,bitmap結(jié)構直接快速地 索引創(chuàng)建好了后,由系統(tǒng)自動調(diào)用和管2.什么時候創(chuàng)建索引自動創(chuàng)建的索引:唯一關鍵字,唯一的約束手工需要創(chuàng)建的索引:大表查詢時,sql語句where后經(jīng)常用到的字段或字段組字段內(nèi)容差別很大有NULL值表很大,返數(shù)較3.B*樹索引的結(jié)構每個索引由字段值和指針或ROWID組4.創(chuàng)建索引的CREATEINDEX索引名ON(1,字段2TABLESPACE表空間名5.創(chuàng)建索引的注意事創(chuàng)建索引時會加行級獨占一個表的索引最好不要超過三個(特殊的大表除外最好用單字段索引最好和表分不同的表空間存結(jié)合SQL語句的分析執(zhí)行情況,也可以建立多字段的組合索引和基于函數(shù)的大表的索引會占用很空不要建唯一的索引,而應該加唯一的約束條6.查詢索引的查詢數(shù)據(jù)字典user_indexes和例子SQL>SELECTic.index_name,2itionFROMuser_indexesix,user_ind_columnsWHEREic.index_name=5ANDic.table_name=注意:數(shù)據(jù)字典里存放的字符都是大寫的7.5ANDic.table_name=注意:數(shù)據(jù)字典里存放的字符都是大寫的7.不用索引的地方where后不經(jīng)常使用的比較字表被頻繁修返數(shù)很where后含ISNULLISNOTNULLlike輸入符%’等條8.重建索引的語ALTERINDEXREBUILDTABLESPACE原來表空間名定期重建索引可以減少索引的碎片,更有效地使用表空間9.刪除索SQLdropindex索引名SQLaltertable表名drop約束名十六、控制用1.權限的類系統(tǒng)級權限:針對整個系統(tǒng)操作的如:用戶名,使用表空間的限額對象級權限:針對某個具體object操作的權如:針對某個表,視圖,表的某個字段的select,updatedelete權2.查看當前數(shù)據(jù)庫的用戶信SQL>selectusername,default_tablespace,temporary_tablespacefrom查用戶SQL>selectcount“number”,username“currentusername”fromgroupby用戶查看自己的缺省表空間SQL>selectusername,default_tablespacefrom3.創(chuàng)建新SQL>createuserusernameidentifiedbydefaulttablespaceta