Oracle與MySQL的學習

---

前言

已經入職幾個月的時間了，不同於平常學習使用的 MySQL，公司內使用的是 Oracle 資料庫。針對以前 MySQL 的學習筆記，這裡主要總結下 Oracle 資料庫，以及其與 MySQL 的一些區別。

---

使用者與儲存區別

mysql：

• 對資料的管理可以有很多個使用者，登入使用者後可以看到該使用者許可權下的所有的資料庫
• 一般來講都是一個專案對應著一個資料庫，該資料庫下存放著所有該專案所要用到的表，檢視等東西
• 檢視所有的資料庫命令：show databases;
• 檢視某資料庫下的所有表命令：show tables;

oracle：

• 對資料的管理同樣有著很多個使用者，同樣的登入使用者後可以看到該使用者許可權下的所有東西
• 資料庫只有一個，但是可以有很多個表空間
  【表空間就像是mysql中的一個個的資料庫，用來區分專案與專案之間的所屬東西】
• oracle多了一個東西叫做資料字典，這其實就是系統字典表，我們只有檢視的許可權。系統字典表有很多張，需要細緻的去學習
• 檢視所有表空間命令：select tablespace_name from dba_tablespaces;
  【dba_tablespaces – 所有表空間資料字典，user_tablespaces --當前使用者許可權下的所有表空間】
• 檢視當前使用者下的所有表及對應的表空間命令：select table_name,tablespace_name from dba_tables;

總結
Oracle通過資料庫使用者，實現了物件級控制資料庫存取和使用的機制。而資料庫使用者是定義在資料庫中一個名稱，是Oracle資料庫的基本存取機制，且每一個資料庫使用者都擁有自己的資料庫，擁有不同的許可權，能夠根據許可權存取其他使用者的資料庫資訊。一個範例有多個使用者，每個使用者預設擁有一個表空間，也可以操作多個表空間，多個使用者可以同時用一個表空間。

---

欄位資料型別區別

mysql：

• 數值型：tinyint（超小整數）、smallint（小整數）、mediumint（中整數）、int（整數）、bigint（大整數）、float（單精度浮點型）、double（雙精度浮點型）
• 字元型：char（定長字串）、varchar（變長字串）、blob（二進位制長文字）、text（長文字）、longblob（二進位制超長文字）、longtext（超長文字）
• 日期型別：date（日期）、time（時間）、year（年份）、datetime（時期加時間）、timestamp（時間戳）

oracle：

• 數值型：number(m,n) 表示資料長度，n表示小數點位數；
• 字元型：char(n) 用於標識固定長度的字串；varchar2(n) 可變長度的字串型別，最長為4000，不可以儲存空字串""，沒有資料時為null；blob 相當於 mysql 的 longblob；clob 相當於mysql的 longtext
• 日期型別：date、timestamp

---

其他區別

• 提交方式：MySQL預設自動提交，Oracle則需要手動提交。
• 分頁查詢：不支援MySQL中的 limit 功能，通過 ROWNUM 實現。

  select * from (
      select temp.*,ROWNUM RN from (select * from WJYTEST) temp where ROWNUM <= (pageNum*PageSize)
  ) where RN > ((pageNum-1)*PageSize);
  

• 非空欄位：MySQL的非空欄位有空的內容（空字元），Orcale的非空欄位不允許有空的內容（空字元），
• 引號：MySQL中單引號與雙引號相同，Orcale 只能用單引號包起字串。
• 自增欄位：MySQL 可以直接給某個欄位定義自增規則，Orcale 需要先定義序列。
  • 序列

    create SEQUENCE myque increment by 1 minvalue 1 maxvalue 9999 start with 1;  
    

  • SQL呼叫

    insert into WJYTEST(ID,IMAGE,CONTENT,HEHE) VALUES (myque.NEXTVAL,NULL,NULL,NULL);  
    

  • sql要和mysql一樣，則需要定義觸發器

    create or replace trigger mytrig before insert on WJYTEST for each row
    begin
        select myque.NEXTVAL into :new.ID from dual;
    end mytrig;
    insert into WJYTEST(IMAGE,CONTENT,HEHE) VALUES (NULL,NULL,NULL);
    

• 函數呼叫：MySQL直接呼叫，Oracle需要寫 from dual

  	select concat('12','34') from dual;
  

• 儲存過程：
  • 建立：Oracle 使用 create or replace，不同於 MySQL 的先判斷是否存在再 Drop 刪除。
  • 引數： Oracle 引數型別 in/out/inout 寫在引數名後面，且引數可以定義為表的欄位型別。
• 索引：不同於 MySQL 的最左匹配原則，Oracle 支援索引跳躍式掃描。使那些在where條件中沒有對目標索引的前導列指定查詢條件，但同時又對該索引的非前導列指定了查詢條件的目標SQL依然可以用上該索引。跳躍式掃描，即就像在掃描該索引時跳過了它的前導列，直接從該索引的非前導列開始掃描（實際的執行過程並非如此）。

---

EXPLANIN 執行計劃

Oracle 每條SQL語句都會經過優化器的評估，而EXPLAIN執行計劃是一種用於優化應用的效能診斷工具。通過執行計劃能夠判斷SQL是否進行了全表掃描，是否使用上了相關索引。
兩種使用方式：

• sql 語句前加上 explain plan for，然後檢視 select * from table(dbms_xplan.display())
• sqldeveloper 中選中具體的 sql 塊，F10 檢視對應的執行計劃

檢視的執行計劃，通過 TABLE ACCESS 能夠知道該 sql 是全表掃描還是索引，其他搜尋路徑常用欄位：

• Rows：該操作返回的結果集行數
• Cost：該操作執行的成本（cpu耗費）
• Time：Oracle估計執行該操作所需的時間

---

分割區

Oracle 目前只能建表的時候定義分割區
範圍分割區： 通常以 Date 型別的列作為範圍分割區鍵

	partition by range (create_time)
	(partition part1 values less than (to_date('01-JUN-2020',''DD-MON-YYYY)) tablespace  ORD_TS01 ,
	 partition part2 values less than ( to_date('01-JUN-2020',''DD-MON-YYYY)) tablespace  ORD_TS02)

列表分割區： 對某一列使用列表值將表劃分成幾部分

	partition by list(sex)
	(partition part1 values('male') tablespace PROB_TS01,
	 partition part2 values('female') tablespace PROB_TS02
	)  

雜湊分割區： 對某一列（唯一/基本唯一）使用雜湊函數把表分成幾部分

	partition by hash(userid) partitions 4

複合分割區： 同時使用兩種分割區方法對錶進行分割區