DB筆記(Oracle),db筆記oracle
DB筆記(Oracle)
一.Oracle的安裝,服務的啟動,資料庫的建立,使用者的建立(略過)。
二.Select語句:
(一)、單行函數:
1. 運算式和null計算,結果為null,即,不作顯示(也就是資料失蹤了!)。所以一般用函數NVL(列名,替換值),如NVL(sal,0)。
2. 字串拼接:|| 例如:select ID||’---’||uname from emp;
3. 排序 和過濾(order by 和 where):
a) select* from emp order by sal asc,comm desc; 此運算式為按薪水升序排,薪水相同的按獎金倒序排。
b) select* from emp order by ( sal + NVL(comm,0) ) desc;此運算式是:按照薪水和獎金之和來排序,注意:如果不用NVL直接用comm,則會丟失一些值。
c) select* from emp where uname > ‘CBA’; 字串的過濾, 這個具體還沒搞懂。
d) select* from emp where sal between 100 and 200;
4. 條件選擇:and,or,in(a1,a2,a3)。注意:其中in(a1,a2,a3)表示選擇a1,a2,a3三個中的一個,而不是選擇三個範圍中的一個。
5. Not關鍵字:not in(a,b,c); is not null; 等等。
6. Like關鍵字:萬用字元有兩個,如下
a) %: 任一字元 出現 任意次數。
b) _: 任一字元 出現 一次。
注意:查詢萬用字元需要轉義,如下:select * from emp where ename like‘%\%%’ escape ‘\’; select * from emp where ename like ‘_H@_%’ escape ‘@’;
7. 事件處理函數: upper(), Lower(), substr(ename,1,4), length(), trim(), ltrim(), rtrim()。
8. 時間格式函數:to_char(sysdate,’yyyy-mm-dd’); to_date(string,’yyyy-mm-dd’)。
兩個日期間的天數 select floor( sysdate – to_date(‘2014/12/27’ , ‘yyyy/mm/dd’) );
9. rownum: 行號,表的原本資料排序,只能用於<或<=運算子,不能用>或>=運算子,如果要取後幾行資料,需要一個where子查詢先倒敘排列。
10. Regex:sql語句支援Regex,例子如下:
select* from emp where regexp_like(列名,’Regex’);
(二)、組函數:
1. avg() ,min(), max(), sum() 略過不講。
2.count():計數函數。返回查詢結果的行數,經常配合distinct,去除重複行。
例如: select count(eno) from emp whereeno>200; 或者 select count(distinct eno) from emp;
3. group by : 分組函數。
a). 查詢單位不再以行為單位,所有操作均以組為單位.
b). 何為一組?值相同則為一組.
c). having為過濾分組,
c). group by子句 須在where子句之後,order by子句之前。
d). select eno from emp group byeno; ----eno相同的分為一組,查詢每組的eno
select depno,sal(sal) from dept group by depno; ----以部門號分組,查詢。
select max( avg(sal) ) from dept group by deptno; ----先按部門編號分組,查出每組平均值的最大值。
select * from dept group by sal,comm; ----工資和獎金相同的分為一組,查詢*。
三.Insert,update,delete語句(略過)。
四.其他動作:
1. 索引index:可以給某個列增加index,提高其select的效率。建立方法超級簡單:create index index_name on tablename(cols); 然後不用管,我們索引時oracle會自動調用index來檢索。刪除方法也超簡單:drop index index_name; 注意: index缺點也不少,Index不適合:資料唯一性不好的列+增刪改頻繁的列。所以不到必要,不用建立。
2. 視圖view:使用方法和表的一模一樣,好處:sql重用+安全
create view v$_vname as (select子句); drop view V$_vname;
3. 序列sequence:用法:建立,刪除,引用。略過。
五.DB設計範式: (重要)
1. DB範式目前有六種,1NF,2NF,3NF,BCNF,4NF,5NF(完美範式)。依次越最佳化。
2. 企業級開發,最低滿足3NF。
3. 滿足範式越高,DB冗餘越低,DB越最佳化。
4. 詳解:
a) 1NF:表的每個列都是原子的。
錯誤示範 -->
b) 2NF:實體屬性完全依賴主關鍵字。即不能只依賴主鍵的一部分。
錯誤示範 à (略過)
常見錯誤 à 聯合主鍵的表。 某些屬性依賴主鍵的一部分。
解決方案 à 拆分主鍵拆分表,外部索引鍵關聯。
c) 3NF:
錯誤示範 -->
常見錯誤 à 表中屬性A依賴於B,B依賴於C。資料量大時會大量冗餘。
解決方案 à 拆表,外部索引鍵關聯。
d) BCNF:允許出現有主鍵的一部分被主鍵另一部分或者其他部分決定。即聯合主鍵相互依賴。
錯誤示範 à倉庫表storehouse(倉庫ID,管理員ID,物品ID,數量),一個倉庫一個管理員,一個倉庫多個物品,則依賴關係如下:
出現了倉庫ID和管理員ID相互依賴,符合3NF但不符合BCNF。
解決方案 -->拆分。
5. 表的關聯關係 和解決方案:
a) 一對一:可外鍵放到任意一張表中。
b) 一對多:外鍵放在多方。例如:班級表(一)--學生表(多),學生表加一個外鍵。
c) 多對一:一對多反過來,(略過)。
d) 多對多:拆分出一個中間表來關聯,把多對多編程兩個一對多,這種結構也叫:實體表—映射表關連接構。
e) 注意:映射表中兩個外鍵需形成聯合主鍵。
備忘:
1. 別名可以用中文,但要加雙引號;
2. oracle的某些用戶端,如PLsql等,有sql window和command window視窗,用來跑不同的語句,例如desc User; 指令只能在command window中跑;
3. SQL語句最佳化:Select * from emp; *是萬用字元,效能較低,程式中盡量不要出現*,最好能具體到列名。
4. SQL不區分大小寫,但是script執行的時候會統一轉化為大寫。 所以:大寫執行效率高但不利於閱讀,小寫指令碼執行效率高但不利於閱讀,所以一般關鍵字大寫,另外需要注意:建議程式裡關鍵字也大寫,有利於增加效率。
5. SQL語句的最佳化: 檢索結果少and 檢索結果多, 檢索結果多 and 檢索結果少。因為sql語句是順序執行。
6. SQL語句最佳化:select * from emp where eno=100 or eno=200 oreno=300; 此語句和select * from emp where eno in (100,200,300); 等價,但是用in的文法清晰+效率高,因為in函數內部做了最佳化。
7.SQL語句最佳化:用like關鍵字和萬用字元來檢索資料萬不得已再用,它的操作慢的不得了。
8. 組函數的嵌套最多兩層,例如 max( avg(sal) )等,不能出現max( min( avg(sal) ) ).
9. Oracle內部執行順序:where行級過濾 à group by分組 à having組級過濾 à order by排序。所以:group by子句 須在where子句之後,order by子句之前,即where…group by…order by…
10. Transaction事務機制要點:只操作insert,update,delete行級語句。如果系統正常關閉,則事務自動commit;如果執行create,drop等表級操作,事務自動commit;如果非正常關閉如斷電等,事務自動rollback。