Oracle資料庫

Ⅰ 資料庫

　　①層次型資料庫
　　②網狀型資料庫
　　③關係型資料庫（主要介紹）

E-R圖：屬性（橢圓形），實體（矩形），聯繫（菱形-一對一、一對多、多對多）
註：有的聯繫也有屬性

關係型資料庫的設計範式：

Oracle資料庫：
　　三大概念：實例、資料庫、資料庫伺服器
　　實例：指一組Oracle後臺進程以及在伺服器中分配的共用記憶體區域；
　　資料庫：由基於磁碟的數據文件、控制文件、日誌文件、參數文件和歸檔日誌文件等組成的物理文件集合；
　　資料庫伺服器：指管理資料庫的各種軟體工具（比如，sqlplus、OEM等）和實例及資料庫三個部分。
　　實例與資料庫兩者聯繫：實例用於管理和控制資料庫，而資料庫為實例提供數據；一個資料庫可以被多個實例裝載和打開，而一個實例在其生存期內只能裝載和打開一個資料庫。

Oracle資料庫的邏輯存儲結構　　

Ⅰ表空間管理
　　一）創建表空間
　　　語法：

　　create [smallfile小文件 | bigfile大文件表空間] tablespace 表空間名 datafile 存放路徑 size ***k/m reuse -【options】-如下

　　本地化管理方式：extent management local [autoallocal自棟擴展 |uniform size **k/m 等同大小進行]
　　自動擴展：autoextent [on | off] next **k/m  ##若自動，則設置next的值
　　線上/離線：online | offline
　　永久/臨時表空間：permanent | temporary
　　是否產生日誌：logging | nologging  ##預設產生

　　//代碼示例//

　　--查詢表空間

　　select tablespace_name,file_name,bytes from dba_data_files order by tablespace_name; 

　　--創建表空間
　　create tablespace tbs_test1 datafile 'D:\oracle_service\oracle_tablespace\tbs_test1.DBF' size 10m reuse
　　extent management local autoallocate; 

　　select * from v$datafile;

　　--重命名
　　alter tablespace tbs_test rename to tbs_test1; 

　　--查詢數據文件
　　select tablespace_name,file_name,bytes,autoextensible from DBA_DATA_FILES; 

　　--添加數據文件
　　alter tablespace tbs_test1 add datafile 'D:\oracle_service\oracle_tablespace\tbs_test2.DBF' size 10M reuse
　　autoextend on next 2m maxsize unlimited; 

　　--刪除數據文件
　　alter tablespace tbs_test1 drop datafile 'D:\oracle_service\oracle_tablespace\tbs_test2.DBF'; 

　　--修改數據文件為自動擴展
　　alter database datafile 'D:\oracle_service\oracle_tablespace\tbs_test1.DBF' autoextend on next 2m maxsize unlimited; 

　　二）撤銷表空間

　　create undo tablespace avatar_undo datafile ..
　　alter system set undo_tablespace=avatar_undo

　　作用：使讀寫一致、可回退事務、事務恢復、閃回操作

　　參數：
　　　　undo_tablespace
　　　　undo_management
　　　　undo_retention

　　三）臨時表空間：（磁碟空間）

　　create temporary tablespace tablespace_name datafile ..

Ⅰ Oracle用戶管理
　一）【用戶】

　　查詢用戶

　　select * from dba_users;
　　select * from all_users;
　　select * from user_users;

　　創建一個用戶peppa,密碼peppa，預設表空間

　　create user peppa identified by peppa;

　　創建用戶yclhj,密碼yclhj,表空間為users表空間

　　create user yclhj identified by yclhj default tablespace users;

　　查詢系統許可權和對象許可權【許可權】

　　select * from system_privilege_map;
　　select * from table_privilege_map;

　　查看角色【角色】

　　select * from dba_roles;

　　直接給peppa用戶授予登錄許可權create session

　　grant create session,alter session to peppa with admin option;

　　通過角色來添加用戶許可權

　　grant connect,resource to peppa;
　　grant connect,resource to yclhj;　

　二）【用戶與角色】
　　用戶擁有的角色

　　select * from dba_role_privs;
　　select * from user_role_privs;
　　select * from role_role_privs;

　三）【用戶與許可權】
　　用戶擁有的系統許可權：

　　select * from dba_sys_privs;
　　select * from user_sys_privs;

　　用戶擁有的對象許可權：

　　select * from dba_tab_privs;
　　select * from all_tab_privs;
　　select * from user_tab_privs;
　　select * from dba_col_privs;
　　select * from all_col_privs;
　　select * from user_col_privs;

Ⅰ SQL語句

　　1）DML語句（數據操作語言）

　　　　Insert / Update / Delete/merge

　　2）DDL語句（數據定義語言）

　　　　Create / Alter / Drop / Truncate(刪除數據立即生效)
　　3）DCL語句（數據控制語言）
　　　　Grant（賦於許可權 ） / Revoke（回收許可權 ）
　　4）事務控制語句
　　　　Commit / Rollback / Savepoint
　　5）查詢語句
　　　　簡單查詢 / 條件查詢 / 連接查詢
　　6）約束
　　　　NOT NULL 約束：確保某列不能有 NULL 值。
　　　　DEFAULT 約束：當某列沒有指定值時，為該列提供預設值。
　　　　UNIQUE 約束：確保某列中的所有值是不同的。
　　　　PRIMARY Key 約束：唯一標識資料庫表中的各行/記錄。
　　　　CHECK 約束：CHECK 約束確保某列中的所有值滿足一定條件。
　　7）單行函數
　　　　使用語法：
　　　　　　函數名稱(操作的列 | 具體的數值 [,若幹參數])
　　　　1.字元串函數

　　大小寫轉換：UPPER()、LOWER()
　　首字母大寫：INITCAP()
　　替換：REPLACE()
　　長度：LENGTH()、
　　截取：①SUBSTR(str,index)     ②SUBSTR(str，begin，size)
　　連接：CONTACT()

　　　　2.數值函數

　　round()函數: 表示的是四捨五入，而且是帶小數點的
　　trunc()函數: 直接截取，不再進行四捨五入了（'+'是指小數點後，'-'是指小數點前，表示一個方向）
　　mod()取餘

　　　　3.轉換函數

　　TO_CHAR() #轉換為字元串
　　TO_DATE() #轉換為日期形式
　　TO_NUMBER() #轉換為數字型

　　　　4.日期函數
　　　　當前系統時間：【sysdate】

　　SELECT SYSDATE FROM dual;

　　　　三大計算公式：
　　　　日期 – 數字 = 日期；
　　　　日期 + 數字 = 日期；
　　　　日期 – 日期 = 數字（天數）

　　MONTHS_BETWEEN(日期1，日期2)：求出兩個日期之間的月數；
　　ADD_MONTHS()：求出幾個月之後的日期；
　　NEXT_DAY()：求出下一個的今天；
　　LAST_DAY()：求出給定日期的最後一天。

　　　　5.聚合函數

　　COUNT()：統計數據量；
　　SUM()：求和；
　　AVG()：求平均；
　　MAX()：最大值；
　　MIN()：最小值；

　　　　6.通用函數

　　①nvl（）: 如果第一個參數為空，就取第二個參數的值，如果不為空就去第一個參數的值
　　②decode() : 多條件判斷
　　　　語法：
　　　　DECODE(列 | 值，判斷條件1，輸出結果1，判斷條件2，輸出結果2，.....)

　　8）子查詢

　　SQL執行的順序

　　　　1.FROM先確定從哪個表中獲取數據
　　　　2.WHERE對條件加以限定
　　　　3.GROUP BY ...HAVING
　　　　4.聚合函數
　　　　5.SELECT欄位篩選
　　　　6.ORDERBY

　　語法：

　　SELECT 查詢的欄位,(
　　　　SELECT 查詢的欄位
　　　　FROM 表名稱1 別名，表名稱2 別名，....
　　　　[WHERE 條件(s)]
　　　　[GROUP BY 分組欄位]
　　　　[ORDER BY 排序欄位 ASC | DESC，排序欄位 ASC | DESC] ;
　　)
　　FROM 表名稱1 別名，表名稱2 別名，.... (
　　　　SELECT 查詢的欄位
　　　　FROM 表名稱1 別名，表名稱2 別名，....
　　　　[WHERE 條件(s)]
　　　　[GROUP BY 分組欄位]
　　　　[ORDER BY 排序欄位 ASC | DESC，排序欄位 ASC | DESC] ;
　　)
　　[WHERE 條件(s) (
　　　　SELECT 查詢的欄位
　　　　FROM 表名稱1 別名，表名稱2 別名，....
　　　　[WHERE 條件(s)]
　　　　[GROUP BY 分組欄位]
　　　　[ORDER BY 排序欄位 ASC | DESC，排序欄位 ASC | DESC] ;
　　　)]
　　[GROUP BY 分組欄位]
　　[ORDER BY 排序欄位 ASC | DESC，排序欄位 ASC | DESC] ;

　　9）分頁查詢

　　rowid：代表該條記錄的物理地址
　　rownum ：為生成的數據的編號，使用分頁需要使用到rownum

　　select * from emp order by sal;
　　select rowid,rownum,emp.* from emp;

　　示例：
　　--每頁顯示3條記錄，查詢第一頁
　　select * from emp where rownum<=3;
　　--查詢第二頁記錄
　　select * from emp where rownum>3 and rownum <=6; --rownum>*不存在，條件滿足不了
　　--解決問題：使用子查詢先生成rownum,然後再基於生成的rownum進行分頁
　　select empno,ename,sal,comm,job
　　from (select rownum a,empno,ename,sal,comm,job from emp)r
　　where r.a>3 and r.a<=6;

　　--按照sal排序
　　--正確順序：先排序，再生成rownum
　　select rownum,empno,ename,sal,comm,job
　　from (select empno,ename,sal,comm,job from emp order by sal);

　　--完整語句
　　select empno,ename,job,sal,comm 
　　from (select rownum r,empno,ename,job,sal,comm 
　　　　　　from (select empno,ename,job,sal,comm from emp order by sal))
　　where r>3 and r<=6;