數(shù)據(jù)庫教學課件第7天

1、數(shù)據(jù)庫第7天課件關(guān)于PLSQL.DEV工具代碼編寫時的智能感應(yīng)寫sql代碼時，自動補全相關(guān)代碼設(shè)置如下多表聯(lián)合查詢要點多表聯(lián)合查詢要解決的問題通過相應(yīng)的外鍵，找到主表的需要的其他字段例如通過主表STUDENT的STUD_ID(學號),找到學生姓名、班級號、年齡、性別等，這些字段中的一個，或多個通過主表CLASS_REG表的CLASS_ID,找CLASS_NAME、START_TIME、END_TIME通過訂單明細表的產(chǎn)品ID，在主表產(chǎn)品表中，找零售價，可以通過零售價，及購物數(shù)量，計算本條目的購物金額小計通過訂單表示的客戶ID，在客戶表中找客戶的送貨地址，客戶電話等基本原則外鍵，本表一個字段的值

2、，能代表主表一行記錄的全部字段的值。因為這個字段在主鍵是主鍵，可以標識這一行數(shù)據(jù)多表關(guān)聯(lián)查詢的方法如果是N個表關(guān)聯(lián)，則必須要找到N-1外鍵這N1外鍵，可能分布在一個表中，也可能多個表中，只要能將這N表都關(guān)聯(lián)起來，就不會產(chǎn)生迪卡爾積。關(guān)于ORACLE常用系統(tǒng)函數(shù)的用法每個數(shù)據(jù)庫都有一些自己特殊的函數(shù)這些函數(shù)在其他數(shù)據(jù)庫不能運行這些函數(shù)的范圍有日期計算字符串的計算統(tǒng)計計算其他計算系統(tǒng)日期，sysdate-系統(tǒng)日期select sysdate from dual;-add_months(日期字段,增加月數(shù))select add_months(sysdate,3) from dual; -在現(xiàn)有日日期

3、基礎(chǔ)，增加N個月select * from class_regselect t.*,add_months(start_time,2) starTime加2個月 from class_reg t-last_day,返回本月最后一天的日期與時間select last_day(sysdate) from dual;select t.*,last_day(start_time) starTime當月最后天 from class_reg t-MONTHS_BETWEEN(日期1，日期2) 兩個日期之間的月個數(shù)，小數(shù)點表示select MONTHS_BETWEEN(sysdate,to_date(2013

4、-5-1,yyyy-mm-dd) from dual;-相關(guān)日期計算，求2個日期之間天數(shù)，月數(shù)，年數(shù)，均有計算公式字符串計算-字符串計算select abc|處理 from dual;-求字符串長度 ,length(字符串變量)select length(abc|處理) from dual;select * from studentselect length(name)姓名長度,t.* from student t-求子字符串,substr(字符串變量,starIndex,字符個數(shù)) startIndex從1開始,字符串個數(shù)省略，表示求余下全部子字符串select substr(abc|處理,

5、3) from dual;select substr(name,2),t.* from student tselect length(abc|處理) 長度 from dual; -長度以字符為最小單位select length( abc|處理 ) 長度 from dual; -長度以字符為最小單位select ltrim( abc|處理 ) 清除左空格 , abc|處理 字符原樣 from dual;select rtrim( abc|處理 ) 清除右空格 , abc|處理 字符原樣 from dual;select trim( abc|處理 ) 清除左右空格 ,length(trim( ab

6、c|處理 ) 長度, abc|處理 字符原樣 from dual;-upper 轉(zhuǎn)大寫select upper(rtrim( abc|處理 ) 清除右空格 , abc|處理 字符原樣 from dual;-lower 轉(zhuǎn)不寫select lower(rtrim( aBC|處理 ) 清除右空格 , abc|處理 字符原樣 from dual;CHR 按數(shù)據(jù)庫的字符集由數(shù)字返回字符 CONCAT(c1,c2) 把兩個字符c1,c2組合成一個字符, 和 | 相同 REPLACE(c,s,r) 把字符c里出現(xiàn)s的字符替換成r, 返回新字符 SUBSTR(c,m,n) m大于0,字符c從前面m處開始取n

7、位字符,m等于0和1一樣, m小與0,字符c從后面m處開始取n位字符 TRANSLATE(c,f1,t1) 字符c按f1到t1的規(guī)則轉(zhuǎn)換成新的字符串 INITCAP 字符首字母大寫,其它字符小寫 LOWER 字符全部小寫 UPPER 字符全部大寫 LTRIM(c1,c2) 去掉字符c1左邊出現(xiàn)的字符c2 RTRIM(c1,c2) TRIM(c1,c2) 去掉字符c1左右兩邊的字符c2 LPAD(c1,n,c2) 字符c1按制定的位數(shù)n顯示不足的位數(shù)用c2字符串替換左邊的空位 RPAD(c1,n,c2)邏輯比較函數(shù)有邏輯比較的函數(shù)NVL(EXPR1, EXPR2)函數(shù) 解釋: IF EXPR1=

8、NULL RETURN EXPR2 ELSE RETURN EXPR1 DECODE(AA0V10R10V20R2.)函數(shù) 解釋: IF AA=V1 THEN RETURN R1 IF AA=V2 THEN RETURN R2 . ELSE RETURN NULL 舉例: decode(id,1,dept sale,2,dept tech)-求一位同學多門課考試成績中的最高分與最低分數(shù)據(jù)庫的SQL語言分類DML語言sql語句的增、刪、改、查DDL語言數(shù)據(jù)定義語言，通常有關(guān)鍵詞create /drop table之類alter table column add/modify/drop name

9、varchar2(20)DCL語言數(shù)據(jù)庫系統(tǒng)控制，包括權(quán)限控制create user grant dba,connect,resource to studsysdrop useralter user studsys account 。關(guān)于多個查詢的結(jié)果集處理并集UNION(去掉重復(fù)記錄) 并集UNION ALL(不去掉重復(fù)記錄) 差集MINUS, 交集INTERSECT 關(guān)于ER圖采用可視化方式，表示一個數(shù)據(jù)庫的設(shè)計ER 圖=Entity Relationship 圖 實體 關(guān)系 圖(的形式，表示數(shù)據(jù)庫設(shè)計)采用PowerDesigner15進行的數(shù)據(jù)表設(shè)計這是sybase數(shù)據(jù)庫提供的一個設(shè)計

10、工具，主要設(shè)計CDM，Concept Design Model這個設(shè)計適用于任何數(shù)據(jù)庫PDM,Physical Design Model綁定了某個具體的數(shù)據(jù)庫這個ER圖設(shè)計完成后，可以直接生成數(shù)據(jù)庫.sql文件數(shù)據(jù)庫流字段類型了解一個字段的值特別大時，興許有幾百兆，或更大，此時采用流作為數(shù)據(jù)類型流作為數(shù)據(jù)類型BLOB二進制流，在所有數(shù)據(jù)庫中均采用blob用java的流對數(shù)據(jù)作讀、寫操作，字節(jié)流CLOB字符流在mssql中有text表示用java的流對數(shù)據(jù)作讀、寫操作，字符流圖片bfile-oracleimage-mssqlORACLE存儲過程設(shè)計數(shù)據(jù)訓(xùn)的存儲過程只適用每個數(shù)據(jù)庫自身過程的定義，

11、procedure類比java類中的方法，在此稱為過程最大的特點不通用也就是Oracle的存儲過程，不能在MSSQL中運行存儲過程的作用在數(shù)據(jù)庫后臺，用一個己編繹好的過程，或函數(shù)，對數(shù)據(jù)表作增、刪、改、查操作運行的效率比較高前臺只需要調(diào)用這個過程即可過程的工作框架在過程中寫一個過程，可以向?qū)г诙x過程時，varchar2(nn),只需要寫為varchar2,即可變量賦值時，要用name:=名稱要在建立過程如果這個變量有返回值，則必須先聲明變量名稱過程沒有參數(shù)時，無括號一個參數(shù)，既可能是輸入，也可以是輸出示例如下create or replace procedure MyProc2(userId

12、 in out varchar2, name in out varchar2) isbegin dbms_output.put_line(userId=| userId|,name=| name); userId:=new UserId; name:=張小紅; end MyProc2;create or replace procedure TestMyProc is 無參數(shù)時，無括號begin dbms_output.put_line(這是一個簡單的存儲過程，沒有任何參數(shù),是我嗎-888899); -等效于java中的System.out.println(.) end TestMyProc;運

13、行方法如下，可以在3個窗口中運行sql窗口，必須有程序體declare userid varchar2(20); name varchar2(20); begin userId:=lifangId; name:=李芳; MyProc2(userid,name); dbms_output.put_line(-userId=| userId|,-name=| name); end;在sqlwindow中運行begin TestMyProc();end;-關(guān)于過程運行begin dbms_output.put_line(這是一個簡單的存儲過程，沒有任何參數(shù)-);end; 在命令窗口中運行需要設(shè)置輸出

14、set serverout onexe 過程名稱(參數(shù)1，參數(shù)2，參數(shù)n);-SQL set serverout on;SQL exec TestMyProc(); 這是一個簡單的存儲過程，沒有任何參數(shù) PL/SQL procedure successfully completedSQL declare 2 userid varchar2(20); 3 name varchar2(20); 4 5 begin 6 userId:=lifangId; 7 name:=李芳; 8 MyProc2(userid,name); 9 dbms_output.put_line(-userId=| user

15、Id|,-name=| name); 10 end; 11 / userId=lifangId,name=李芳-userId=new UserId,-name=張小紅 PL/SQL procedure successfully completed SQL存儲過程的調(diào)試,第3種方式運行在調(diào)試窗口中進行在此窗口輸入過程代碼與sql窗口運行的代碼完全相同，但可以單步調(diào)試操作如下建一個test窗口編寫調(diào)試代碼單步運行操作示例事務(wù)處理概念日常生活的事務(wù)日常生活的事務(wù)，事務(wù)繁忙定義是執(zhí)行某個任務(wù)，或業(yè)務(wù)，要涉及到數(shù)據(jù)庫多個相互關(guān)聯(lián)的操作，這些操作要么全部成功，要么全部失敗，如果失敗，則回到原始狀態(tài)。解釋一

16、個事務(wù)，是一個任務(wù)，或一個業(yè)務(wù)不是一個項目，更不是一個工程興許在一個同類的數(shù)據(jù)庫中，也興許不在同一個類數(shù)據(jù)庫中事務(wù)的屬性，或性質(zhì)，4個，ACID原子性，A,Atomicty，多個操作合起來是一個最小單位表示事務(wù)的多個操作是一個整體項，不能再細化，是一個最小單位一致性，C,Consistency，按計劃，無錯誤表示事務(wù)的多個操作沒有錯誤發(fā)生，全部按計劃進行隔離性,I,Isolation，并發(fā)多個事務(wù)同時進行時，本事務(wù)不受到影響持久性，D，Durability表示事務(wù)一旦完成，不能撤消，或反悔，不能再回到原始狀態(tài)關(guān)于事務(wù)的編程Oracle必須在一個procedure中進行每一段必須一程序體begi

17、n -在這個之間寫plsql的語言代碼end;異常處理代碼exception when othersbegin rollback; 提示輸出end;必須涉及數(shù)據(jù)表的2個或以上的新增、修改、刪除項操作這個數(shù)據(jù)表可以相同，或不相同示例如下create or replace procedure MyTransection isbegin begin update study set score=146 where stud_id=10000 and course_id=1; insert into study values(10000,5,145.678); commit; -表示要么全部成功 db

18、ms_output.put_line(事務(wù)運行成功！); end; exception when others then begin rollback;-表示要么全部失敗 dbms_output.put_line(事務(wù)運行失敗,執(zhí)行了回滾操作！); end;end MyTransection;運行command windowSQL exec MyTransection; 事務(wù)運行成功！ PL/SQL procedure successfully completed SQL在sql window，或test window代碼相同begin MyTransection;end;在output窗口觀察輸出ORACLE數(shù)據(jù)備分與恢復(fù)備分的工具是exp.exe文件恢復(fù)的工具是imp.exe文件需要寫腳本運行操作流程新建一個用戶對新用戶授權(quán)導(dǎo)出原有用戶數(shù)據(jù)將導(dǎo)出的數(shù)據(jù)，導(dǎo)入新用戶中示例代碼如下新建用戶SQL SQL create user testnew identified by orcl; User created SQL grant dba,connect,resource to testnew; Grant succeeded導(dǎo)出數(shù)據(jù)pauseexp studsys/o