一．基礎知識為顯示方便，可以直接在欄位後加上標籤(別名)sum(decode())用來統計order by desc/asc 排序distinct，不顯示重複的。 分組：group by 欄位 ，與select前邊的欄位匹配。聚集合函式(max,min,sum,avg)不能出現在where中，這時用having 模糊查詢like a%，以a開頭的。 表的串連from * join * on內串連左，右外串連(非完全符合)(+) 無關子查詢 IN NOT IN相互關聯的子查詢子查詢中不能用* EXISTS ,可以用* 表合并UNION，只是顯示合并 INTERSECT返回兩個表中都出現的行 插入多條記錄：INSERT INTO * SELECT結果集INSERT INTO VALUES,只能插入一條記錄根據已有的表來建立表。CREATE TABLE ttt AS (SELECT * FROM *)
二．
PL/SQL補充：前後台參數傳遞。PL/SQL: DECLARE...BEGIN....EXCEPTION...END 輸出：DBMS_OUTPUT.PUT_LINE('');|| 串連字元，與其他語言的 + 類似，不需要轉換類型 SET SERVEROUTPUT ON SIZE 10000設定緩衝區大小用於輸出到螢幕--行注釋/**/塊注釋 IF分支IF...THEN...ELSIF...THEN...ELSE...END IF CASE分支WHEN ..THEN..WHEN ..THEN..ELSEENDCASE LOOP迴圈LOOP...END LOOP WIHLE迴圈WHILE expression LOOP...END LOOP FOR迴圈 GOTO迴圈
記錄:TYPE * IS RECORD，定義記錄，(表的一行)DELCARETYPE myrecord IS RECORD(id varchar2(10),name varchar2(10)),real_record myrecord; 與表欄位的類型，結構相同DECLAREmyrec 表A%ROWTYPE 如果只有其中一個欄位相同，跟上面的一樣，改一處，id A.eid%TYPEBEGINSELECT * INTO myrec FROM A WHERE...
遊標遊標CURSOR：一種PL/SQL控制結構，可以對SQL語句的處理進行顯示控制，便於對錶的行資料逐條進行處理。 遊標的屬性：%FOUND，%ISOPEN,%NOTFOUND,%ROWCOUNT 顯式DELCARECURSOR mycur ISSELECT * FROM books;myrecord books%ROWTYPE;BEGINOPEN mycur;FETCH mycur INTO myrecord;WHILE mycur%FOUND LOOPDBMS_OUT.PUT_LINE(myrecord.books_id);FETCH mycur INTO myrecord;END LOOPCLOSE mycur;END;/遊標參數1DELCARECURSOR mycur_para(id varchar2) ISSELECT books_name FROM books WHERE books_id=id;t_name books.books_name%TYPE;BEGIN OPEN mycur_para('001');LOOPFETCH mycur_para INTO t_name;EXIT WHEN mycur_para%NOTFOUND;DBMS_OUTPUT.PUT_LINE(t_name);END LOOP CLOSE mycur_para;END;/遊標參數2DELCARECURSOR mycur_para(id varchar2) ISSELECT books_name FROM books WHERE books_id=id;BEGINDBMS_OUTPUT.PUT_LINE('****結果集是：****');FOR cur IN mycur_para('001') LOOP ——for迴圈不需要開啟關閉DBMS_OUTPUT.PUT_LINE(cur.books_name);END LOOP;END;/遊標 ISOPENDECLAREt_name books.books_name%TYPE;CURSOR mycur(id varchar2) ISSELECT books_name FROM books WHERE books_id=id;BEGINIF mycur%ISOPEN THEDBMS_OUTPUT.PUT_LINE('遊標已經被開啟');ELSEOPEN mycur('003')END IFFETCH mycur INTO t_name;CLOSE mycur;DBMS_OUTPUT.PUT_LINE(t_name);END;/ 利用遊標修改資料 必須在SELECT語句後加上 FOR UPDATEUPDATE deptment SET name=name||'_fujia' WHERE CURRENT OF mycur 隱式:沒有聲明，開啟，關閉BEGINFOR mycur IN (SECLECT name FROM *) LOOP列印輸出END LOOP;END;
三．預存程序定義： 將常用的或很複雜的工作，預先用SQL語句寫好並用一個指定的名稱儲存起來, 那麼以後要叫資料庫提供與已定義好的預存程序的功能相同的服務時,只需調用execute,即可自動完成命令。 那麼預存程序與一般的SQL語句有什麼區別呢? 預存程序的優點： 1.預存程序只在創造時進行編譯，以後每次執行預存程序都不需再重新編譯，而一般SQL語句每執行一次就編譯一次,所以使用預存程序可提高資料庫執行速度。 2.當對資料庫進行複雜操作時(如對多個表進行Update,Insert,Query,Delete時），可將此複雜操作用預存程序封裝起來與資料庫提供的交易處理結合一起使用。 3.預存程序可以重複使用,可減少資料庫開發人員的工作量 4.安全性高,可設定只有某此使用者才具有對指定預存程序的使用權 預存程序的種類： 1.系統預存程序：以sp_開頭,用來進行系統的各項設定.取得資訊.相關管理工作, 如 sp_help就是取得指定對象的相關資訊 2.擴充預存程序 以XP_開頭,用來叫用作業系統提供的功能 exec master..xp_cmdshell 'ping 10.8.16.1' 3.使用者自訂的預存程序,這是我們所指的預存程序 常用格式 Create procedure procedue_name [@parameter data_type][output] [with]{recompile|encryption} as sql_statement 解釋: output：表示此參數是可傳回的 with {recompile|encryption} recompile:表示每次執行此預存程序時都重新編譯一次 encryption:所建立的預存程序的內容會被加密 預存程序的3種傳回值: 1.以Return傳回整數 2.以output格式傳回參數 3.Recordset傳回值的區別: output和return都可在批次程式中用變數接收,而recordset則傳回到執行批次的用戶端中CREATE OR REPLACE PROCEDURE myproc(id IN varchar2(10))ISname varchar2(10);BEGIN
四．視圖、同義字、序列
視圖(安全，方便，一致性，虛表)：實際上是一條查詢語句，是資料的顯現方式CREATE OR REPLACE VIEW myviewASSELECT * FROM books; 通過視圖更新兩個基表，不可行。（觸發器）帶group by,sum avg等彙總函式，或者distinct，視圖不可更新。WITH READ ONLY
同義字：縮寫代碼。可以方便地操縱不同使用者模式下的對象CREATE SYNONYM dept FOR scott.dept;目前使用者的專有的私人的。DROP SYNONYM dept刪除CREATE PUBLIC SYNONYM dept FOR scott.dept;建立公用的。
序列：??CREATE SEQUENCE myseqSTART WITH 1INCREMENT BY 1ORDERNOCYCLE;
五．觸發器、資料表空間、索引觸發器：自動執行，不接受參數。事務：用於確保資料完整性和並發處理的能力，將一條、一組SQL語句當作一個邏輯上的單元，用於保證這些語句都成功/失敗事務的特性：A(Atomicity):原子性，不可分割C(Consistency):一致性，操作前後一致。I(Isolation):隔離性，與並發性緊密結合，不會自己提交(如果沒提交，在目前使用者下好像更新，其實沒有更新，必須提交)，COMMIT;ROLLBACK;設定使用者FOR UPDATE，別的使用者只能等，不能操作，只有等那個使用者提交後才能操作。(類似“鎖”)D(Duralibility):永久性，COMMIT和ROLLBACK，不能一起操作
行級觸發器：實現多表的完整性，實現原子性CREATE OR REPLACE TRIGGER del_deptidAFTER DELETE ON deptmentFOR EACH ROWBEGINDELETE FROM emp WHERE id=:old.id;END del_deptid;/在觸發器中不允許的操作： IF:old.books_id='0001' THEN RAISE_APPLICATION_ERROR(-20000,'不允許刪除')
語句級觸發器：不涉及資料完整性CREATE OR REPLACE TRIGGER dml_aaAFTER INSERT OR DELETE OR UPDATE ON mytableBEGINIF INSERTING THENINSERT INTO mytable VALUES('''');ELSIF DELETING THEN...ELSE...END IF;END;
替換觸發器(此觸發器只能建立在視圖上，可用於視圖多表更新)
資料表空間建立資料表空間CREATE TABLESPACE tabsDATAFILE '執行個體的路徑/tabs.dbf' SIZE 10M 授權GRANT UNLIMITED TABLESPACE
索引：提高速度位元影像索引(bitmap INDEX)，(資料多，比如性別，只有很少的幾種) SQL*Loader事先有資料檔案和控制檔案控制檔案可能是這樣的形式：load datainfile 'c:/loader.txt'appendinto table 表名(m1 position(1:3) char, --長度知道，並相同時，否則可以用 terminated by "," --以逗號分割m2 position(5:8) char) 》sqlldr control 控制檔案路徑 data 資料檔案路徑
OEM：企業管理工具，在瀏覽器中
六．資料庫的備份和恢複 邏輯備份exp test/test123@test尾碼名.dmp邏輯恢複imp test/test123 冷(離線)、熱(聯機)物理備份冷：shutdown immediate之後，把相關內容拷走。 熱：日誌歸檔方式查看 archive log list;alter system set log_archive start=true scope=spfile; shutdown immediate;startup mount;alter database archivelog;alter database open; 例子：alter tablespace 表 begin backup;把東西拷走alter tablespace 表 end backup;alter system archive log current;alter system switch logfile;shutdown immediate; 重新開啟後查看錯誤select * from v$recover_file;alter database datafile ? offline drop;alter database open; 最後 recover datafile ?; alter database datafile ? online;恢複完畢 備份控制檔案alter database backup controlfile to trace; --在admin下的執行個體下的udump下 記錄檔丟失重新生產記錄檔alter database open resetlogs;
