1. 常用增删查改等操作
| 類型 | 方式 |
| 資料庫 | 建立資料庫： create database database_name 删除資料庫： drop database database_name |
| 表 | 建立新表： create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根據已有表建立新表： Create table table_new like table_old; Create table table_new as select col1, col2 ... from table_old definition only; 注：definition only表示隻定義無資料 删除表： drop table table_name; |
| 列 | 增加列： alter table table_name add column col type; 注：列增加後将不能删除。DB2中列加上後資料類型也不能改變，唯一能改變的是增加varchar類型的長度。 改變列屬性： alter table table_name modify col type; |
| 主鍵 | 添加主鍵： Alter table table_name add primary key(col); 删除主鍵： Alter table table_name drop primary key(col); |
| 索引 | 建立索引： Create [unique] index index_name on table_name[col1, col2 ...]; 删除索引： Drop index index_name; 注： 索引的優點：加快資料檢索速度；“唯一性索引”可保證資料記錄唯一性；加快表的連接配接速度。 索引的缺點：本身占用空間；增删改資料時需要動态維護索引。 |
| 視圖 | 建立視圖： Create view view_name as select statement; 删除視圖： Drop view view_name; |
| 基本sql | 選擇： select * from table1 where 範圍 插入： insert into table1(field1,field2) values(value1,value2) 删除： delete from table1 where 範圍 更新： update table1 set field1=value1 where 範圍 查找： select * from table1 where field1 like ’%value1%’--like的文法很精妙，查資料! 排序： select * from table1 order by field1,field2 [desc] 總數： select count(field1) as totalcount from table1 求和： select sum(field1) as sumvalue from table1 平均： select avg(field1) as avgvalue from table1 最大： select max(field1) as maxvalue from table1 最小： select min(field1) as minvalue from table1 |
| 外連接配接 | Left (outer) join: Select table_a.*, table_b.* from table_a left join table_b on table_a.col=table_b.col; Right (outer) join: Select table_a.*, table_b.* from table_a right join table_b on table_a.col=table_b.col; Full (outer) join: Select table_a.*, table_b.* from table_a full join table_b on table_a.col=table_b.col; Inner join: Select table_a.*, table_b.* from table_a inner join table_b on table_a.col=table_b.col; 注： 1. 外連接配接是相對内連接配接，其outer可以省略； 2. 内連接配接的inner可以省略用逗号表示，這時就是普通的雙表聯合查詢； |
| 幾個進階查詢運算詞 | UNION: Select col1, col2 ... from table1 union [all] Select col1, col2 ... from table2; INTERSECT: Select col1, col2 ... from table1 intersect Select col1, col2 ... from table2; EXCEPT: (oracle中用minus) Select col1, col2 ... from table1 except [all] Select col1, col2 ... from table2; 共同要求： 連接配接的兩個結果集必須有相同的列數和列順序，同時列的資料類型必須相容。 |
| Group by | SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name |
| Between | Select * from table1 where col [not] between value1 and value2; |
| In | Select * from table1 where col [not] in (value1, value2 ...); |
| 分頁查詢 | SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM <= 40 ) WHERE RN >= 21 注意，oracle的select語句中不能設定ROWNUM下限，即>=，因為oracle查詢是從第一條開始根據結果遞增，如果下限大于1則無法繼續 |
2. oracle對誤删表的恢複
1、從FLASHBACK TABLE裡查詢被删除的表
SELECT * FROM RECYCLEBIN ORDER BY DROPTIME DESC;
2.執行表的恢複
FLASHBACK TABLE TABLE_NAME TO BEFORE DROP;
或者FLASHBACK TABLE SCOTT.TEST TO TIMESTAMP TO_TIMESTAMP('2009-12-11 20:47:30','yyyy-mm-dd hh24:mi:ss');
看已删除表的曆史情況：
SELECT * FROM FLASHBACK_TRANSACTION_QUERY WHERE TABLE_NAME='TEST';
看是否有資料：
SELECT * FROM SCOTT.TEST AS OF TIMESTAMP TO_TIMESTAMP('2009-12-11 20:53:57','yyyy-mm-dd hh24:mi:ss');
3. oracle死鎖
| 類型 | 方法 | 說明 |
| 發現死鎖 | select username,lockwait,status,machine,program from v$session where sid in (select session_id from v$locked_object) | 如果有輸出的結果，則說明有死鎖，且能看到死鎖的機器是哪一台。字段說明： Username：死鎖語句所用的資料庫使用者； Lockwait：死鎖的狀态，如果有内容表示被死鎖。 Status： 狀态，active表示被死鎖 Machine： 死鎖語句所在的機器。 Program： 産生死鎖的語句主要來自哪個應用程式。 |
| 檢視死鎖 | select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select session_id from v$locked_object)) | |
| 查找死鎖的程序 | SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#, l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID; | |
| kill掉這個死鎖的程序 | alter system kill session ‘sid,serial#’; | 其中sid為上面的session_id |
| 如果還不能解決 | select pro.spid from v$session ses,v$process pro where ses.sid=XX and ses.paddr=pro.addr; | 其中sid用死鎖的sid替換: exit； ps -ef|grep spid 其中spid是這個程序的程序号，kill掉這個Oracle程序 |
4. 執行個體、使用者、表空間、資料檔案、臨時資料檔案、表
安裝Oracle後會有預設的執行個體ORCL，可以不用再建立新的執行個體；
使用者、表空間屬于執行個體的邏輯概念，一個執行個體下的使用者和表空間獨立于其它執行個體。使用者主要是權限上的考慮，表空間主要是資料檔案存儲上的考慮。
使用者被授權于表空間，表空間在邏輯上整理了資料檔案，資料檔案隻能屬于某個表空間。
表可能分布在不同的資料檔案中，表邏輯上歸屬于使用者（同一表空間下不同使用者可以有相同的表名），與表空間無關，是以當我們登入資料庫的時候需要指定使用者而不是表空間。
5. Oracle資料庫優化
實體優化：
1). Oracle的運作環境（網絡，硬體等）
2). 使用合适的優化器
3). 合理配置oracle執行個體參數
4). 建立合适的索引（減少IO）
根據記錄量，索引也是很占空間的；删除标的部分記錄時不會删除索引，隻有記錄全部删除才會删除索引。
5). 将索引資料和表資料分開在不同的表空間上（降低IO沖突）
6). 建立表分區，将資料分别存儲在不同的分區上（以空間換取時間，減少IO）。表分區方式：記錄字段的範圍、時間、枚舉取值、散列等方式分區。表分區應用場景：表大小超過2G；表中含曆史資料、新資料增加到新的分區中。
邏輯優化：
1). 表拆分。可以對表進行邏輯分割，如中國移動使用者表，可以根據手機尾數分成10個表，這樣對性能會有一定的作用。
2). 綁定變量。Sql語句使用占位符語句，并且開發時候必須按照規定編寫sql語句（如全部大寫，全部小寫等）oracle解析語句後會放置到共享池中。
如： select * from Emp where name=? 這個語句隻會在共享池中有一條，而如果是字元串的話，那就根據不同名字存在不同的語句，是以占位符效率較好。
3). 存儲過程。資料庫不僅僅是一個存儲資料的地方，同樣是一個程式設計的地方，一些耗時的操作，可以通過存儲過程等在使用者較少的情況下執行，進而錯開系統使用的高峰時間，提高資料庫性能。
4). sql寫法。盡量不使用*号，如select * from Emp，因為要轉化為具體的列名是要查資料字典，比較耗時；使用Exits Not Exits 替代 In Not in ；
6). 事務處理。合理使用事務，合理設定事務隔離性。資料庫的資料操作比較消耗資料庫資源的，盡量使用批量處理，以降低事務操作次數。
重要說明：
資料庫的垂直拆分和水準拆分。垂直拆分、是解決表之間IO的問題，将資料量太大的表放到單獨的server上；水準拆分、是解決單表IO的問題，對資料量太大的單表根據字段特征進行拆分成多個表。
綁定變量的必要性：oracle的sql執行過程為：1.文法檢查，判斷sql拼寫是否符合文法；2. 語義檢查，判斷合法權限下相關對象是否存在；3.語句解析，利用内部算法生成解析樹及執行計劃；4.執行sql，傳回結果。其中第三步比較耗時，是根據内部算法看目前sql是否已在library cache中，如果在的話則省略了優化器的相關工作，這個在大量查詢時很重要。
6. oracle幾個自定義結構變量
| 類型 | 文法 | 描述 |
| record | Type recordName is record( V1 data_ype [not null] [:=defaultValue]; V2 data_ype [not null] [:=defaultValue];) eg: declare type recordTest is record( var1 tableTest.volumn1%type, var2 tableTest.volumn2%type ); recordResult recordTest; begin select vol1, vol2 into recordResult from tableTest2; dbms_output.put_line(recordResult.var1); end; | 類似python中的元組，可以用來存儲資料表的一行記錄； |
| varray | type varrayName is varray(size) of data_ype [not null] eg: declare type varrayTest is varray(5) of varchar2(64); varrayResult varrayTest; begin varrayResult := varrayTest('1', '2', '3', '4', '5'); for i in varrayResult.first .. varrayResult.last loop DBMS_OUTPUT.PUT_LINE(varrayResult(i)); end loop; end; | 類似c中的數組，成員支援下标取值，靈活易用； |
| table | type tableName is table of data_ype [not null] index by [BINARY_INTEGER|PLS_INTEGER|VARRAY2] eg: declare type recordTest is record( var1 tableTest.volumn1%type, var2 tableTest.volumn2%type ); type tableTest is table of recordTest; tableResult tableTest; begin select vol1, vol2 into bulk collect tableResult from tableTest2; for i in tableResult.first .. tableResult.last loop dbms_output.put_line(tableResult.var1); end loop; end | 對record類型的擴充，支援多行資料，可以用來存儲資料表的查詢結果。 存儲單列多行： type tableTest is table of varchar2(64); 存儲多列多行和ROWTYPE結合使用： type tableTest is table of tableTest%rowtype; 存儲多列多行和RECORD結合使用： Type tableTest is table of recordTest; |
7. 存儲過程、函數、作業
存儲過程：
create or replace procedure 存儲過程名 (p1 in|out type, p2 in|out type) as|is
var1 type;
begin
null;
end;
函數：
create or replace function 函數名 (p1 type, p2 type) return type is
var1 type;
begin
null;
end;
二者的差別：
函數一定要有return傳回值，存儲過程沒有；
函數可用在sql語句調用但不能單獨執行，存儲過程可以單獨執行但不能用于sql調用；
此外二者在文法等方面，都基本相似。
作業：
variable test_job number;
begin
dbms_job.submit(:test_job,'test_procedure;',sysdate,'sysdate+1/1440');
end;
這裡參數的解釋：
job OUT binary_ineger：出參，唯一辨別一個工作
What IN varchar2：表示被執行的代碼塊對象名
next_date IN date：初次運作此job的時間
interval IN varchar2：何時此job被重新運作
8.其它
oracle的導入導出非常耗記憶體；
oracle的排序對性能影響很大，如非必要就别排序；
表設計時，根據具體業務場景，不一定死扣範式；
外鍵的好處是提高資料品質，但壞處是增加了其它負擔，而且需要注意一些問題，比如外鍵如果不加索引會有嚴重性能問題；
oracle字段在設計時順序就确定了，對于通路頻繁的字段最好放前面，對性能有好處；
資料類型在設計是一定要确定好，另外不要用char, long這些資料類型；
varchar2類型的長度，雖然不會影響實際記錄資料的大小，但對于外部應用來說，是有影響的，比如weblogic等程序會根據類型長度預配置設定記憶體，這樣可能導緻這些程序的記憶體消耗過大；
表大分表、業務大分庫，不過這些一般在設計階段會容易些，因為沒有資料負擔；
主鍵不要有業務規則，主鍵僅标志記錄唯一性即可，否則随業務膨脹會有影響；
轉載于:https://www.cnblogs.com/PattonCCNU/p/6347645.html
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)