ORACLE學習總結(jié).docx

ORACLE學習總結(jié)默認分類 2008-06-08 02:42:25 閱讀62 評論0 字號：大中小訂閱 一.文件系統(tǒng)和作用 執(zhí)行文件:Oracle系統(tǒng)的核心,數(shù)據(jù)文件只能被Oracle處理程序所識別,離開了可執(zhí)行文件系統(tǒng)就無意義.存放可執(zhí)行文件的空間一定要安全可靠. 參數(shù)文件:啟動實例時候配置Oracle數(shù)據(jù)庫的一些基本信息,確定運行環(huán)境.參數(shù)文件應數(shù)據(jù)庫不同而不同,命名方式為init.ora. 控制文件:在Oracle服務器啟動期間來標識物理文件和數(shù)據(jù)庫結(jié)構(gòu)的二進制文件.控制文件提供了建立新實例時候所需要的文件目錄,可以在Oracle操作其間更新控制文件,可以建立多個控制文件副本,以便進行數(shù)據(jù)庫恢復. 數(shù)據(jù)文件:數(shù)據(jù)文件用來存放用戶的數(shù)據(jù),它的穩(wěn)定性和完整性都十分重要.每個數(shù)據(jù)庫都至少一個相關數(shù)據(jù)文件. 聯(lián)機重做日志文件:是數(shù)據(jù)庫的事物日志,它用于恢復數(shù)據(jù)庫. 輸出文件:它影響數(shù)據(jù)庫的可恢復性,它由Oracle自帶的輸出程序生成,它在指定時間點上對數(shù)據(jù)庫的數(shù)據(jù)和結(jié)構(gòu)進行備份.它是數(shù)據(jù)庫邏輯副本,而不是物理副本. 臨時文件:用以在內(nèi)存不足以保存中間結(jié)果集的時候保存結(jié)果集.它不生成重做,即不生成聯(lián)機重做文件. 二:后臺進程和作用 后臺進程的主要作用是為了提高數(shù)據(jù)庫的性能和可靠性,沒個進程都有自己的職責. pmon:監(jiān)控服務器進程,確保能夠銷毀發(fā)生損壞或出現(xiàn)故障的進程,并釋放資源. smon:負責重新啟動系統(tǒng),清楚臨時段,執(zhí)行盤區(qū)結(jié)合任務.確保系統(tǒng)達到一致性. dbwr:負責將緩存中的數(shù)據(jù)塊寫入到磁盤中. lgwr:日志寫入器,負責向聯(lián)機重做日志文件中寫入已提交的事物處理. ckpt:檢查點進程,負責使用最新的檢查點信息更新所有控制文件和數(shù)據(jù)文件的標題.數(shù)據(jù)庫寫入器會周期性的將其緩寸寫入到磁盤中,存儲檢查點. cjq0:負責管理和協(xié)調(diào)作業(yè). arc0:歸檔器:主要負責備份聯(lián)機重做日志中的事物,以防止他們被覆蓋. 三.系統(tǒng)表,視圖和作用 查看有關用戶的信息:dba_users 查看有關角色的信息:dba_roles,dba_role_privs,role_sys_privs 查看有關系統(tǒng)權限的信息:dba_sys_privs 查看當前數(shù)據(jù)庫表空間狀況:dba_tablespaces 查看用戶的系統(tǒng)權限:user_sys_privs 查看某個用戶對另外一個用戶授予的權限:user_tab_privs_made 查看某個用戶對另外一個用戶授予的列級權限:user_col_privs_made 查看某個用戶接受的權限:user_tab_privs_recd 查看某個用戶接受的列級權限:user_col_privs_recd 查看有關用戶的角色信息:user_role_privs 查看有關授予某個角色的系統(tǒng)權限信息:role_sys_privs 查看有關授予某個角色的對象權限信息:role_tab_privs 查看當前用戶所擁有的表信息:user_tables 查看當前用戶有權限訪問的表信息:all_tables 查看當前用戶所擁有的所有表的列信息:user_tab_columns 查看當前用戶可以訪問的表中的列信息:all_tab_columns 查看當前用戶所擁有的所有約束信息:user_constraint 查看當前用戶所擁有的所有約束和列的關系:user_cons_constraint 查看表中注釋內(nèi)容:user_tab_comments 查看表中列注釋內(nèi)容：user_col_comments 提供練習的表:dual 查看相關時區(qū)的名稱和簡稱:v$timezone_names 四.表空間和作用 system表空間:記錄運行信息. sysaux表空間:存儲支持Oracle系統(tǒng)活動的許多工具和選項. temp表空間:存儲中間結(jié)果. undo表空間:存儲事物提交或撤消的改變數(shù)據(jù). 五.默認用戶角色 DBA:執(zhí)行數(shù)據(jù)庫中所有的操作 connect:可以執(zhí)行數(shù)據(jù)庫連接等操作 resource:可以執(zhí)行使用數(shù)據(jù)庫資源等操作 select_catalog_role:可以執(zhí)行select操作 OLAP_DBA:可以執(zhí)行與表,視圖有關的操作 scheduler_admin:可以執(zhí)行作業(yè)管理等操作 exp_full_database:可以執(zhí)行引出數(shù)據(jù)庫等操作 imp_full_database:可以執(zhí)行引入數(shù)據(jù)庫等操作 六.權限 系統(tǒng)權限:可以執(zhí)行數(shù)據(jù)庫中某些操作的權限稱為系統(tǒng)權限.如:create table create user系統(tǒng)權限:擁有此權限才可以創(chuàng)建用戶 create session系統(tǒng)權限:擁有此權限的用戶可以連接數(shù)據(jù)庫 create any table系統(tǒng)權限:在任意模式中創(chuàng)建表 create synonym系統(tǒng)權限:可以創(chuàng)建同意詞 execute any procedure系統(tǒng)權限:執(zhí)行任何過程 create role系統(tǒng)權限:可以創(chuàng)建角色 drop any table系統(tǒng)權限:可以刪除任何表 創(chuàng)建用戶:create user 用戶名identified by 口令default tablespance 默認表空間 temporary tablespace 臨時表空間 修改用戶口令:alter user 用戶名 identified by 新密碼 鎖定用戶和解鎖用戶:alter user 用戶名 account lock|unlick 修改用戶默認表空間:alter user 用戶名 default tablespace 新表空間名 temporary tablespace 新臨時表空間名 刪除用戶:drop user 用戶名 cascade 如果當前用戶擁有對象則必須加上 cascade 為用戶授以系統(tǒng)權限:grant 系統(tǒng)權限1,系統(tǒng)權限2,系統(tǒng)權限3. to 用戶名 with admin option 如果允許用戶把權限轉(zhuǎn)受于其它用戶則要加上with admin option 收回授予的系統(tǒng)權限:revoke 系統(tǒng)權限1,系統(tǒng)權限2,系統(tǒng)權限3. from 用戶名 在收回權限時,被當前用戶所轉(zhuǎn)授的權限不會被收回 對象權限:針對數(shù)據(jù)庫中的表,視圖等對象的權限為對象權限.如:select insert update delete execute 為用戶授予對象權限:grant 對象權限(列名稱) on 對象名 to 用戶名 with grant option 如果允許用戶把權限轉(zhuǎn)受于其它用戶則要加上with grant option 收回授予的對象權限:revoke 對象權限 on 對象名 from 用戶名 收回權限只能從表上收回,不能從列上收回,在權限被收回時,被該用戶轉(zhuǎn)授的權限也會被收回 創(chuàng)建同義詞:create public synonym 同義詞名 for 對象名 public代表所有用戶都可以使用公共同義詞 角色:角色是一組可以授予用戶和其它角色的權限,一個用戶可以擁有多種角色 創(chuàng)建角色:create role 角色名 identified by 角色口令 授予角色權限:類似授予用戶權限 收回角色:drop role 角色名 七.表 建表:create global temporary table 表名 (列名 列類型其它信息) on commit (delete|preserve) row tablespace 表空間名 on commit指定表中數(shù)據(jù)持續(xù)時間,delete在事物執(zhí)行完畢后刪除,preserve表示在會話結(jié)束后刪除 查看表的系統(tǒng)信息:desc|describe 對象名 刪除表：drop table 表名 添加列:alter table 表名 add 列名 列類型 更新列:alter table 表名 modify 列名 列類型 刪除列:alter table 表名 drop column 列名 禁止約束:alter table 表名 discount contraint 約束名 禁止約束:alter table 表名 enable novalidate contraint 約束名 如果有novalidate則表示在激活時不驗證數(shù)據(jù)內(nèi)容是否滿足約束 刪除約束:alter table 表名 drop constraint 約束名 修改表名稱:rename 舊表名 to 新表名 注釋表:注釋表就是為表和表中列添加文字說明以讓其它人可以看懂表結(jié)構(gòu) 注釋表語法:comment to table 表名 is 注釋信息 comment to column 表名.列名 is 注釋信息 注釋內(nèi)容用單引號 八.操作數(shù)據(jù) 插入數(shù)據(jù):insert into 表名(列名列表) values(列值列表) 插入列時用default關鍵字代表值則是插入列的默認值,如果用null則代表插入一個空值,插入列的時候必須滿足列的約束 復制數(shù)據(jù):insert into 表名(列列表) 和列對應的查詢語句 修改數(shù)據(jù):update 表名 set 列名=列值,列名1=列值1. where 限制條件列表 刪除數(shù)據(jù):delete from 表名 where 限制條件列表 刪除表中所有數(shù)據(jù)的兩種方法:delete from 表名 ; truncate table 表名;truncate table效率高些,因為它是重新設置表的存儲空間 合并數(shù)據(jù)操作:把兩個表合并成一個表,并且以某一字段作為判斷是否相同的機準.如表A和表B 用id來判斷是否相同,那么把表A合并到B中,則以A中的數(shù)據(jù)為基準,如果a.id=b.id成立,那么合并后的其它字段將采用a表的 合并數(shù)據(jù):merge into 表名1 using 表名2 on (比較表達式) when matched then 更新語句 when no matched then 插入語句 閃回查詢:有兩種類型的閃回查詢1.基于時間的閃回查詢,把系統(tǒng)回復到之前的某一時間;2.是基于版本號的閃回查詢,把系統(tǒng)回復到之前一個版本號.使用閃回查詢,則必須擁有dbms_flashback包的execute權限. 授予用戶閃回查詢的對象權限:grant execute on dbms_flashback to 用戶名; 執(zhí)行基于時間的閃回查詢:execute dbms_flashback.enable_at_time(時間); 時間一般算法:sysdate-分鐘數(shù)/1440 執(zhí)行基于系統(tǒng)版本號的閃回查詢:execute dbms_flashback.enable_at_system_change_number(系統(tǒng)改變號); 改變號可以通過;dbms_flashback.get_system_change_number();獲得 關閉閃回查詢:執(zhí)行完閃回查詢功能后應該關閉,關閉的方法為:dbms_flashback.disable(); 事務:事務是一個單元操作,這些操作要么全部成功,要么全部失敗. 九.檢索 算數(shù)運算:可以在查詢中使用算數(shù)運算(+,-,*,/) 別名:在Select語句中為了便于查看為列臨時定義的名稱.定義方式: 列名 as 別名 合并輸出結(jié)果:使用|符號連接.例:結(jié)果1|結(jié)果2|結(jié)果3. 空值處理:nvl(空值列,顯示值) 如果列的值是空則顯示顯示值 distinct關鍵字:限定在檢索結(jié)果中不出現(xiàn)重復值;select distinct 列列表 from 表名 where:where后面跟運算表達式;例:select 列列名 from 表名 where 限制條件 運算符: =等于 或!=不等于 大于 =大于或等于 any(值列表):比值列表中的最小值大 all(值列表):比值列表中的最大值大 all(值列表):比值列表中的最小值小 like:按照指定的模式匹配 like _A% _代表任意一個字符,%代表任意多個字符 如果在匹配的字符串中包含_或%那么則使用escape來說明是實際數(shù)據(jù)而不是匹配表達式 in:匹配值清單 如: in(select a from TA) between:匹配范圍內(nèi)值 如:between(1,20) is null:與空值匹配 is nan:與非數(shù)字值匹配 is infinite:與無窮的binary_float和binary_double匹配 not:用于在各種運算符中取反 or:或,當兩個條件中有一個為真時,就匹配 and:與,必須兩個條件全部是真才匹配 排序數(shù)據(jù):order by 排序表達式 asc|desc,排序表達式1 asc|desc asc和desc代表排列順序,asc是正序,desc是倒序,默認asc 檢索多個表的數(shù)據(jù)1:select 列列表 from 表名1 as 別名1 , 表名2 as 別名2 where 限制條件 笛卡爾集:兩個集合的乘集,如:集合A中有2個記錄,集合B中有20個,則他們的笛卡爾集就有40條記錄. 內(nèi)連接:內(nèi)連查詢出來的所有數(shù)據(jù)都是滿足查詢條件的.方式:select 列列表 表名1 別名1 inner join 表名2 別名2 on 內(nèi)連接條件 外連接:外連接可以把不符合條件的表的數(shù)據(jù)顯示在結(jié)果中.分為左外連接,右外連接,全外連接.左外連接包含左表中不滿足條件的數(shù)據(jù),右外連接包含右表中不滿足條件的數(shù)據(jù).全外連接則包含左右兩表中不滿足條件的數(shù)據(jù).join關鍵字左邊的稱為左表,右邊的是右表 外連接方式:select 列列表 from 表名1 別名1 left|right|full outer join 表名2 別名2 on 連接條件 自連接:是一個表連接自身的一個連接.可以采用別名的方式查詢,自連常用內(nèi)連接方式 自連接方式:select 列列表 from 表名 別名1 inner join 表名 別名2 on 連接條件 交叉連接:交叉連接沒有連接條件,結(jié)果就是笛卡爾集. 交叉連接方式:select 列列表 from 表名1 cross join 表名2 十.程序包 dbms_flushback:用于執(zhí)行閃回查詢 十一.函數(shù) 字符串函數(shù): ascii(x):返回字符x的ascii碼; char(x):返回asscii值的字符; concat(x,y):把y附加到x上并返回; initcap(x):將字符x中的每一個英文單詞設置為首字母大寫的形式; instr(x,find_string,start,occurrence:在字符串x中搜索find_string,返回find_string出現(xiàn)的位置,可以選擇開始搜索的位置start,還可以限制它是在第幾次出現(xiàn)的occurrence length(x):返回字符串X的長度 lower(x):把x轉(zhuǎn)換為小寫,并返回 lpad(x,width,pad_string):使用空格補齊x的左邊使其長度為width,如果提供了pad_string,則使用pad_string補充 ltrim(x,trim_string):刪除x左邊的字符,默認是刪除空格,如果指定了trim_string則刪除trim_string nanvl(x,value):如果x不是數(shù)字則返回value否則返回x nvl(x,value):如果X是空,則返回value否則返回x nvl2(x,value1,value2):如果X不是空,返回value1否則返回value2 replace(x,search_string,replace_string):在X字符串中搜索search_string并替換為replace_string rpad(x,width,pad_string):和lpad一樣但是是從右邊 rtrim(x,trim_string):和ltrim一樣但是是從右邊 soundex(x):返回包含字符串的音標 substr(x,start,length):返回x的子字符串,開始位置是start長度為length,默認為全部返回 trim(char fromx):刪除x左右兩邊的字符串,如果有char from 則刪除左右兩邊的char upper(x):把字符串x中的字母轉(zhuǎn)變?yōu)榇髮懽帜?數(shù)學函數(shù): abs(x):返回X絕對值 acos(x):返回X的反余弦值 asin(x):返回X的反正弦值 atan(x):返回X的反正切值 atans(x,y):返回X和Y的反正切值 bitand(x,y):返回X和Y的二進制與的結(jié)果 cos(x):返回X的余弦值其中X以弧度表示 cosh(x):返回X的雙曲弦值 ccil(x):返回大于或者等于X的最小整數(shù) exp(x):返回e的X冪,其中e等于2.71828183 floor(x):返回小于或者等于x的最大整數(shù) log(x,y):返回底為x的外的對數(shù)值 ln(x):返回x的自然對數(shù)值 mod(x,y):返回X除以Y的余數(shù) power(x,y):返回X的Y次方 round(x,y):圓整X值.如果不指定Y,則圓整到整數(shù);如果指定Y,則圓整到Y(jié)指定的小數(shù)點位數(shù);如果Y是負數(shù),則圓整到小數(shù)點左端的位數(shù) sign(x):符號函數(shù),如果X是負數(shù),則返回-1;如果X是正數(shù),則返回1;如果X是0則返回0 sin(x):返回X的正弦值 sinh(x):返回X的雙曲正弦值 sqrt(x):返回X的平方根 tan(x):返回X的正切值 tanh(x):返回X的雙曲正切值 trunk(x,y):截斷X值,如果不值定Y,則截斷到整數(shù);如果指定Y則截斷到Y(jié)值定的小數(shù)點位數(shù);如果Y是負數(shù),則截斷到小數(shù)點左端的位數(shù) 轉(zhuǎn)換函數(shù): asciistr(x):把X轉(zhuǎn)換為ascii字符串,其中x可以是任意字符集中的字符串 bin_to_num(x):把X轉(zhuǎn)變成一個二進制數(shù),返回數(shù)字 cast(x as type_name):把X從一種數(shù)據(jù)類型轉(zhuǎn)換成type_name數(shù)據(jù)類型 chartorowid(x):把X轉(zhuǎn)變?yōu)閞owid值 compose(x):把X轉(zhuǎn)換成unicode字符串,unicode使用2字節(jié)字符集,并且可以表示出65000多個字符,可以用于表示非英語字符. convert(x,source_char_set,dest_char_set):把X從source_char_set字符集轉(zhuǎn)換為dest_char_set字符集 decode(x,search,result,default):比較X和search如果相等就返回result不等就返回default decompose(x):把X轉(zhuǎn)換為UNICODE字符串. hextoraw(x):把包含了十六進制的X字符轉(zhuǎn)變?yōu)槎M制數(shù)字,返回結(jié)果是raw數(shù)字 numtodsinterval(x):把數(shù)字x轉(zhuǎn)換為interval_day_to_second類型 numtoyminterval(x):把數(shù)字X轉(zhuǎn)換為interval_year_to_money類型 rowtohex(x):把二進制數(shù)據(jù)X轉(zhuǎn)變成包含了等價十六進制代碼的varchar2字符 rowidtochar(x):把rowid類型的X轉(zhuǎn)換為varchar2類型 rowidtonchar(x):把rowid類型餓x轉(zhuǎn)變成nvarchar2類型 to_binary_double(x):把X轉(zhuǎn)變成binary_double to_binary_float(x):把x轉(zhuǎn)變成binary_float to_char(x,format):把x轉(zhuǎn)換為varchar2字符串,可以提供一個格式化表達式format to_clob(x):把X轉(zhuǎn)換為一個CLOB to_date(x,format):把X轉(zhuǎn)換為日期 to_dsinterval(x):把字符穿X轉(zhuǎn)變成interval_day_to_second類型 to_multi_byte(x):把X中的單字節(jié)字符轉(zhuǎn)變?yōu)槎嘧止?jié)字符 to_nchar(x):把x轉(zhuǎn)變成nvarchar2類型 to_nclob(x):把X轉(zhuǎn)變成nclob類型 to_number(x,format):把X轉(zhuǎn)變成number to_single_byte(x):把X中的多字節(jié)字符轉(zhuǎn)變單字節(jié)字符 to_timestamp(x):把字符串X轉(zhuǎn)變?yōu)橐粋€時間戳 to_timestamp_tz(x):把字符串X轉(zhuǎn)變成timestamp with time zone to_yminterval(x):把字符串X轉(zhuǎn)變成interval_year_to_moth translate(x,from_string,to_string):把出現(xiàn)在X中的所有from_string替換為to_string unistr(x):把X中的字符轉(zhuǎn)變?yōu)閚char字符集 格式化參數(shù): 9:返回數(shù),如果數(shù)字是負數(shù)則有負號 0:0999表示數(shù)字前面有0,9990表示數(shù)字后面有0 .:表示小數(shù)點的位置 ,:指定位置顯示逗號 $:S99表示數(shù)字前面是貨幣號 B:如果整數(shù)部分為0,則使用空格表示 C:在指定的位置使用ISO標準貨幣符號 D:在指定的位置返回小數(shù)點位置 EEEE:用科學計算法記載 FM:刪除數(shù)字前后空格 G:在指定的位置顯示分組符號 L:在指定的位置顯示本地貨幣符號 PR:負數(shù)的尾部有三角擴號,正數(shù)的頭部和尾部有空格 MI:負數(shù)的尾部有負號,正數(shù)的尾部有空格 RN/m:返回羅馬數(shù)字,RN表示大寫,m小寫,數(shù)字必須是1-3999之間 S:S999表示負數(shù)前面有負號,正數(shù)前面有正號,999S表示符號在后面 TM:使用最小的字符數(shù)返回數(shù)字 U:在指定位置返回雙貨幣號 V:返回一個數(shù)字乘以10的x的次方,x為指定數(shù)字 X:返回十六進制數(shù)字 正則表達式: *:匹配0個或多個任意字符 ?:匹配1個任意字符 :表示字符串的開始 $:表示字符串結(jié)束 x|y:匹配x或者y :匹配方擴號中任意一個字符 m:允許匹配m次 m,n:至少匹配M次,最多匹配N次 n:表示前一個表達式重復N次 :字符類:脂定一個字符類,匹配該類中的任意一個字符 regexp_like(search_string,pattern,match_option):類似like運算符,可以使用正則表達式執(zhí)行模式匹配操作 option有四個值C匹配時,大小寫敏感,I匹配時大小寫不敏感,N允許使用.匹配任意新增字符, regexp_instr(search_string,pattern,position,occurrence,return_opotion,match_option):instr運算符擴展,它可以用來搜索匹配正則表達式模式的輸入字符 regexp_replace(seach_string,pattern,replacestr,positionoccurrence,match_option):replace函數(shù)擴展,它允許用戶搜索符合正則表達式的輸入字符傳,并且用替換字符串替換 regexp_substr(search_string,pattern,positioin,occurrence,match_option):substr函數(shù)擴展 合計函數(shù): avg(x):平均值 count(x):統(tǒng)計數(shù)量 max(x):最大值 median(x):中間值 min(x):最小值 stddev(x):標準差 sum(x):匯總值 variance(x):方差 分組技術:select 列列表 from 表名 where 限制條件 group by 分組列 having 分組后的數(shù)據(jù)應該滿足的條件 時間函數(shù): add_months(x,y):在x上增加y個月,若Y為負數(shù)則減少 last_day(x),返回包含在X中的月份的最后一天 months_between(x,y):返回X和Y之間的月數(shù),如果Y在X前返回負數(shù) next_day(x,day);返回僅接著X的下一天,DAY是一個字串 sysdate():返回當前系統(tǒng)的日期 trunk(x,unit):截斷x round(x,unit):圓整X current_date():返回本地區(qū)的當前日期 dbtimezone():返回當前數(shù)據(jù)庫的時區(qū) new_time(x,time_zone1,time_zone2):把x從時區(qū)1轉(zhuǎn)換為時區(qū)2 sessiontimezone();返回當前數(shù)據(jù)庫對話的時區(qū) tz_offset(time_zone):按照最小時哈分鐘返回time_zone的偏置時間 時間戳函數(shù): current_timestamp():返回包含了當前會話時間,時區(qū)信息的timestamp with time zone類型 extact(year|month|day|hour|minute|second|timezone_hour|timezone_minute|timezone_region|timezone_abbr from x):從表達式X中提取參數(shù)信息 from_tz(x,time_zone):把指定的時間戳和日期轉(zhuǎn)換為timestamp with time zone類型 localtimestamp():返回會話的timestamp with time zone類型數(shù)據(jù) systimestamp():返回當前數(shù)據(jù)庫的timestamp with time zone類型數(shù)據(jù) sys_extact_utc(x):把類型為tmestamp with time zone 的表達式轉(zhuǎn)換為timestamp類型 to_timestamp(x,format):把字串表達式X轉(zhuǎn)換成timestamp類型可以使用format指定格式 to_timestamp_tz(x,format):把字串表達式X轉(zhuǎn)換成timestamp with time zone類型可以使用format指定格式 十二.日期和時間 設置默認時間格式:默認情況下是:dd-mon-yy;alter session nls_date_format 日期格式;則可以改變 轉(zhuǎn)換日期數(shù)據(jù): cc:兩位數(shù)字的世紀 scc:有負號的兩位數(shù)字的世紀,表示世紀前 q:表示一位數(shù)季度 yyyy:表示4位數(shù)年 iyyy:表示4位數(shù)