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

復合磁區： 同時使用兩種磁區方法對表進行磁區