Oracle資料庫基礎知識點匯總

標籤：知識點   pre   插入資料   檔案   地址   null   取資料   logs   enc   

1.資料庫 database

2.主鍵 ，表的唯一性標識的一列，主鍵的值永遠不能重。primary key

主鍵的類型：單一主鍵：一列構成的；複合主鍵：多個列組合起來充當主鍵。多採用流水號，單一列當做主鍵

3.三大規則 ：實體完整性約束：主鍵  ：PK

　　　　　　參照完整性條件約束：外鍵 ：A表的主鍵出現在B表，在B中就被成為外鍵，簡稱FK，此時A為主表，B從表

從表外鍵的值需要參照主表主鍵進行填充，或者從表外鍵的值為空白，如果外鍵的值在主建中不存在，禁止填充

　　　　　　自訂約束：唯一性限制式 unique（對NULL無效，可以有多個NULL），

　　　　　　　　　　　　空值約束 not null

　　　　　　　　　　　　預設值約束 default（對NULL無效，可以錄入NULL）

　　　　　　　　　　　　檢查約束 check（列名=‘‘or列名=‘‘）（對null無效）

4.　儲存字串：　Varchar2（長度 char）長度個漢字的長度

　　   　　　　　　Varcher2（長度）

　　儲存數字 NUMBER(n) 

　　儲存日期 DATE  

　　　　　　DATE(N)錯誤書寫

5.建立一個表的文法

　　　　　　create table 表名（列名1 類型 primary key not null，

　　　　　　　　 　　　　　　 列名2 類型 not null，

　　　　　　　　 　　　　　　 列名3 類型 not null） ； （注意最後一個沒有逗號）

插入資料到表中：

　　　　　　insert into 表名（列名1，列名2，列名3）

　　　　　　values（列值1，列值2，列值3）列值的類型必須是列名的類型

查詢語句的書寫

　　　　　　select 列名1，列名2，列名3 B（可以給列名起別名用於顯示：

　　　　　　　　　　　　　　　　　　　　　　列別名需要加雙引號的三種情況：1）區分大小寫

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　2）有空格

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　3）特殊字元），B.列名

　　　　　　from 表名 A（給表起別名A，然後在select中可以寫A.列名），

　　　　　　　　表名B（可以多表一起查詢）（做多表串連的時候表的書寫順序，從表在前，主表在後，）

　　　　　　where 條件陳述式 （先寫篩選條件，後寫串連）

　　　　　　（where 可以做巢狀查詢把另一個查詢出來的結果作為篩選條件，但是不建議用，效能太差，一般把　　select查詢出來的作為from的一個虛擬表進行處理）

　　　　　　　order by asc 列名（正序）or desc 列名（逆序） 排序輸出結果（order by 中 null最大）

改寫語句

　　　　　　update 表名 set 列值 （新）where 條件  滿足什麼條件就修改

刪除語句 

　　　　　　delete from 表名  整張表的資料刪除  

　　　　　　delete from 表名 where 條件陳述式  有條件的刪除資料

在資料表空間：

1.資料表空間與分使用者管理

  分使用者管理在實現上有兩種方式

  方式1:物理隔離

    每個使用者的所有資料都儲存到獨立的資料表空間中,該資料表空間只裝載該使用者的所有表,這樣一個使用者的資料表空間發生問題,對其他使用者沒有影響

  方式2:邏輯隔離

     表歸屬使用者所有,但是所有使用者的表共用一個資料表空間

2.資料表空間(TABLESPACE)

   就是表的容器,實際上就是一堆資料檔案的集合,使用者表中的資料,實際上儲存到資料表空間的指定檔案中.

   資料表空間的分類:

       1.暫存資料表空間-----做交換分區

       2.永久資料表空間------用以儲存資料  

刪除表有兩個：

　　 　drop：可恢複

　 　　ttruncate（資料截斷）    ：不可恢複  在oracle中有一個資料復原，truncate就是繞過這個資料復原，而drop可以復原就恢複剛剛 刪除的資料。

oracle資料庫中的時間date：sysdate 就可以從表中擷取 ：select sysdate from dual；

字串與日期之間的轉換：to_date（‘1997-08-08‘,‘YYYY-MM-DD‘）

　　　　　　　　　　　　to_char(sysdate,‘YYYY-MM-DD HH25:MI:SS‘ )

時間的運算按天

多表關聯查詢中，表之間的構型

   1.一主多從

   2.一從多主

   3.單線連珠

自串連 ： 一個表和自己建立串連，把自己虛擬成另一張表就可以串連啦

比如查詢scott 下 emp 中 每個職員的管理者

SELECT A.EMPNO,A.ENAME,A.JOB,A.MGR,B.ENAME  FROM EMP A,EMP B WHERE A.MGR=B.EMPNO

內串連（等值串連）：

　　只顯示滿足串連條件的資料，不滿足的不顯示；應用語境：應用於過去和現在，對既成事實的查詢

外串連：以某一張表為基礎，該表中的所有資料都顯示，對應的從表，如果存在資料，則顯示資料，如果不存在資料就顯示NULL：對將來，全部的概念。

在Oracle中的文法：從表外鍵加（+）；

比如查詢scott下表emp所有人的管理者；

SELECT A.EMPNO,A.ENAME,A.MGR,B.EMPNO,B.ENAME  FROM EMP A,EMP B WHERE A.MGR=B.EMPNO(+)

對未來尚未實現的夢想進行查詢外串連去反就可以實現：就是取從表相應列為null，該部分資料表位將來時

查詢將來可能被管理的人 

SELECT A.EMPNO,A.ENAME,A.MGR,B.EMPNO,B.ENAME  FROM EMP A,EMP B WHERE A.MGR=B.EMPNO(+) AND B.EMPNO IS NULL

這個例子可能不恰當，但是用法就這樣

前面提到的排序 order by asc 正序 desc 倒敘也叫降序

多欄位排序：依據欄位分組，組內一欄位一排序

分組統計函數 ：

　　min 求最小值

　　max  求最大值（max min count可以對任意資料類型列取值）

　　avg 求平均值（avg 與 sum 只能對數實值型別列取值）

　　sum 求和

　　count 計算滿足條件的記錄數（自動忽略null值）

　　　　count（列）只統計非空值，此列上所有不為null的參與計數

　　　　count（*） 只要有一列不為空白即可

　　　　count五大統計函數中 具有篩選功能，

　　　　用了彙總函式count，寫在select中還有其他列名要查，就得進行彙總分組group by ，select出現的都得寫在group by中。group by 子句中不能使用別的列別名進行分組，也不可以有子查詢，可以有having子句。

sql語句的執行順序

　　1 from 擷取資料

　　2 where 篩選資料

　　3.group by 彙總分組

　　4.彙總函式運算

　　5.having條件陳述式

　　6.select 查詢需要的列

　　7 distinct 重複資料刪除資料

　　8　.order by 排序想要的資料

decode函數

　　 就是給字元表示的漢字意思顯示出來。比如存入資料庫 狀態一欄資訊，1代表活著.2代表死了

查詢出來 1 或者2 別人看不懂我們就可以用 decode函數實現 decode（表名.列名,‘1‘,‘活著‘）這樣如果是1就顯示活著、

substr 函數

　　用來 截取字串 substr（‘abcdefg’，2,3）從第二個開始截取三個字元‘bcd’，如果2改成-2則順序顛倒，如果3改成100截取到最後一個位置‘bcdefg’。

rowid：

　　這是兩個偽列；行的物理地址，以最快的速度，檢索或者刪除滿足條件的記錄

這個用到巢狀查詢 我們查出要刪除的重複資料的最大rowid然後把前邊的我們不需要的全部刪除

rownum：

　　查詢結果集中，每行的記錄的行號。一般用於top-n分析；在select中直接加上rownum就會給查詢出來的資料每行加上一個序號；然後可以在where條件中寫 rownum<3  就可以擷取前兩行的資料。< 與!=一個意思，<=小於等於

大多數人說：rownum用來做分頁查詢：

在這我瞭解到rownum用來做top-n分析。就是對前幾名的資料的擷取，先是查詢出來用某一列用order by排序作為虛擬表用rownum選擇我們要的前幾名然後在可以用一個order by 語句來選擇我們想要的順序輸出，注意用什麼來排序必須在上述的select 虛擬表中查出來的。

序列  

create sequence 序列名  ：用來產生一個遞增的值，但不保證連續 

刪除 drop sequence 序列名

索引

作用：有助於提高查詢效率

建立時機：一般在系統上上線運行半年，基於最佳化效能角度考量，將使用者經常使用的查詢條件列建立索引。

自動索引，oracle中存在主鍵索引和唯一約束索引

建立索引的語句create index 索引名 on 表名（列名，列名）可以一列 為單一索引可以多列複合索引

刪除索引 是drop index 索引名

視圖

view 建立視圖 語句 create view 視圖名字 as select 查詢出來的資料 相當於一個虛擬表 真正項目中不會用到視圖的 

刪除語句 drop view 視圖名字

視圖與原始表的關係：

視圖只是原始表資料的引用，視圖中，沒有任何資料

當查詢檢視的時候，相當於將建立視圖時select語句再執行一次，

視圖的作用：混淆未經處理資料，重構資料，簡化查詢。

 

Oracle資料庫基礎知識點匯總