Oracle 學習筆記(一)

標籤：search   lengthb   val   還原   cas   prim   new   處理   tcap   

一、字串函數

lower(字串)  小寫

upper(字串)   大寫

initcap(字串)  首字母大寫

concat(列1，列2)  兩列合并

length(字串)  按照字

lengthb(字串) 按照位元組

lengthc(字串)  unicode 的長度

substr(列名，m，n )    m: 開始索引號，n:截取長度

instr(字串，字元)    字元在字串中的位置

leading ‘a‘ from  ‘aaaaaabababaaaaaaaa‘   截取連續的前置a

trailing ‘a‘ from  ‘aaaaaaababaaaaaaaa‘    截取連續的後置a

both ‘a‘ from  ‘aaaaaaababaaaaaaaa‘    截取連續的前置和後置a

  lpad(字串，n，字元)  在字串的左側添加n個字元

rpad(字串，n，字元) 在字串的右側添加n個字元

replace(字串，字元1，字元2)  將字元1 替換成字元2

traim（字串）　去掉字串兩邊的空格

ltraim(字串) 去掉字串左邊的空格

rtraim(字串) 去掉字串右邊的空格 

 二、數字操作函數

ROUND(數字，位元)   四捨五入
TRUNC(數字，位元)   小數全部捨棄，取整
CEIL(數字)    小數全部捨棄，取整（上進一位）
ABS(數字)  數位絕對值
ACOS(數字) 反餘弦
COS(數字)  餘弦
FOOR(x) 小於或等於x的最大數
LOG(數字)  對數
MOD(x,y) x/y 的餘數
POWER(x,y) x的y次冪
SＱRT(數字)　　平方根

三、 日期操作函數

sysdate  資料庫的目前時間

nls_date_format 重新定義日期的顯示格式

alter session set nls_date_format=‘yyyy/mm/dd:hh24:mi:ss‘

round(日期1-日期2) 兩個日期之間的差，單位天，帶小數

months_between(日期1，日期2) 兩個日期間的月數

add_months(日期1，月份)

next_day(日期1，“friday”)  從日期1算起，下一個星期5是哪一天

last_day(日期1)  日期1月底是哪一天

extract(year from 日期1) 日期1中特定的部分 , year,month,day,hour,minute,second

日期進位和截取

round(date,‘mm‘) 

round(date,‘month‘)

round(date,‘yyyy‘) 

round(date,‘year‘)

trunc(date,‘mm‘) 

trunc(date,‘month‘)

trunc(date,‘yyyy‘) 

trunc(date,‘year‘)

to_char  轉換成char 類型

to_date  轉換成日期類型

to_number 轉換成數字

四、 NULL 操作

NULL 值指，空值，末賦值或不可知的值

NVL(expr1,epxr2)  如果 expr1 為非空，就返回 expr1,否則返回 epxr2, 資料類型要一致
NVL2(expr1,epxr2,epxr3) 如果 expr1 為非空，就返回 expr2, 否則返回 epxr3
NULLIF(expr1,epxr2) 如果 expr1和 epxr2 相同就返回空，否則返回 expr1
COALESCE(expr1,epxr2,...,exprn) 返回第一個非空的值 

五、 分支語句

 1. CASE 語句

 select  列1，列2，列3，

 CASE 列4     

when ‘值1‘   then ‘傳回值1‘  

when ‘值2‘   then ‘傳回值2‘  

when ‘值3‘   then ‘傳回值3‘  

 ELSE       

    ‘值0‘ 

    END  ‘新列1‘    

  2. Decode 語句  

     Decode 語句和 CASE 語句一樣，都是分支語句，Decode 是 Oracle 自訂的，其它資料庫不支援  

     Decode(coll|expression,search1,result1,search2,result2,...,defaul

 六、 表的串連

笛卡兒串連：  cross join   

如果是兩個表，第一張表的所有行與第二個表的所有行進行矩陣相乘，得到 n*m 行的結果。   避免笛卡兒串連： 給定一個正確的串連條件      表的別名一旦定義，表的本名就無效    

外串連：   left/right outer join

外串連分為： 左外串連 left outer join，右外串連 right outer join， outer 可省略  

使用 where A.列1 = B.列1，   

where A.列1 (+)= B.列1， （等價於 右外串連）   將第一張表，第二張表沒有的資料也顯示,+號不能同時出現在等號 的兩邊,只能出現在一邊    +號是oracle 特有的，出現在左邊指右外串連，在右邊指左外串連     

自串連：  self join

     即自身串連。

 自然串連：    natural join  

  所有同名的列，都作為等值條件，且資料類型相同，不能加表的首碼    using(列名) 指定列的串連，當有多列同名時，使用其中某一列作為串連條件     

七、 DDL 和 DML語句

 DDL:  資料定義語言 (Data Definition Language)，包括表的建立，修改，刪除，驗證   

DML:  資料修改語言，包括資料行的修改，刪除       表是存放資料的最基本對象。   

我們將一般的表叫做 heap table (堆表)，其含義為雜亂無章的存放資料，堆表是資料庫的重要組織形式，它表別於索引表和 cluster 表。      

表的名稱規則：    

標準 ASCII  碼可以描述  

字母開頭  30個字母內  

不能是保留字  

可以包含大小寫字母 ，數字，下畫線，$,#  千萬不要使用漢字作為表的名稱或列的名稱，因為漢字是ASCII碼所不能描述的，  ORACLE 的核心是 ASCII 編寫的    

在現有表的基礎上建立新表：    create table t2 as select * from emp;  這個新表會包括資料      

如果不想包含資料，加一個false 的條件選擇    create table t2 as select * from emp where 1=0;

修改表結構：    

如果列為null,可以隨意修改列的類型和寬度。  如果有資料，修改會受到限制，但不會破壞資料。    

Alter table t2 modify(name varchar(20));  Alter table t2 add(name varchar(50));  

修改表的名稱：  rename t2 to newTableName

修改列的名稱：  Alter table t2 rename column c1 to newColumnName

表注釋：  comment on table t2 is ‘注釋內容‘

列注釋：  comment on column  c1 is ‘列注釋‘  

刪除表：  drop table t2, 在commit 前，表末真正刪除

顯示資源回收筒的資訊：  show recyclebin

將資源回收筒的表還原：  flashback table t2 to before drop;     flashback table t2 to before drop rename to  tt2;  

清空資源回收筒內指定的表：  purge table t2;  

清空目前使用者的資源回收筒，不影響其它使用者的資源回收筒：  purge recyclebin;   DML 語句

 DML，資料操作語言，insert, update, delete, merge

插入資料：  insert into t2(c1,c2,c3) values(v1,v2,v3);  在插入時，沒有列出的列，就會插入null,如果該列有defalult 值 ，就會使用預設值  當插入日期的時候， 最好強制轉換成日期類型，使用to_date 函數

修改資料：  update t2 set c1=‘1‘ where 子句  

刪除行：  delete t2  where 子句  

事務：  提交事務： commit;  復原事務： rollback;  

約束：  添加約束： alter table t2 add  constraint  約束名稱  unique(列名)      

alter table t2 add  constraint  約束名稱  primary key(列名)      

alter table t2 add  constraint  約束名稱  check(length(列)>10)          

主鍵不是必需的，可以沒有，主鍵是通過索引實現的，索引的名稱和主鍵的名稱相同  外鍵：     

alter table t2 add constraint 名稱 foreign key(列名) references 表名(列名)     

  級連的外鍵，主表行被刪除，子表的相關列自動被賦予 NULL 值   

  alter table t2 add constraint 名稱 foreign key(列名) references 表名(列名) on delete set null          

alter table t2 add constraint 名稱 foreign key(列名) references 表名(列名) on delete cascade; 子表相關的列的自動刪除   

  序列：   

CREATE SEQUENCE  序列名稱    

MINVALUE 1  

 MAXVALUE 999  

 INCREMENT BY 1   

START WITH 1   

CACHE 20    --NOCACHE  

 NOORDER    

NOCYCLE ;        

序列名稱.nextval  序列的下一個值   序列名稱.currval  序列的當前值    當序列沒有值在記憶體中時，currval 值無效，先 nextval 後，才會有效   序列的不連續問題：  交易回復，序號不回退     其它語句引用了該序列    Cache n 序列的值到記憶體中，一次存放n個值， 預設是20個，如果連續使用，可將n 設定大些  NOCACHE 每次取值都要計算  停止資料庫後，記憶體中存放的序列會丟失    order  在單一實例中沒有差別，它體現在群集 rac 環境中，它的含義是，  為叢集中的每個節點相要獲得的序列的值都要重新從序列的定義中計算獲得，  order 和 CACHE 是相互排斥的，一旦使用的 order 項， CACHE 項會無效。    如果使用序列作主鍵，請設定 NOCYCLE, 不迴圈  如果要快速發生序列值，將 CACHE 設定大一些  如果序列要很長時間才能用完， 可以考慮使用迴圈的序列

八、View 視圖   

  視圖的定義存在於資料字典中，Oracle 的所有字典都是視圖，  視圖只是一個定義，沒有獨立的資料，資料還是存放在表中      為使用提供了方便  增加了資料的負擔  降低了資料庫的效能 （如果用不當）    限制對資料庫的訪問  將複雜的查詢包起來，化繁為簡  提供給使用者獨立的資料  在同一個表上建立不同的視圖，減少視圖的個數    可以從 user_views 中查看所有的視圖， user_object 查看所有對象   建立視圖：  create or replace view 視圖名稱 as  select * from t2 WITH 約束項  Read only  唯讀  check option constraint 約束名稱

刪除視圖  drop view 視圖名稱  

視圖上運行 DML  Delete 限制   

1.有組的函數        

2.有group by 子句   

3.用了distinct 關鍵字  

 4.有rownum 列  

 Update 限制  

 1.有組的函數        

2.有group by 子句   

3.用了distinct 關鍵字   

4.有rownum 列   

 5.有運算式的列     

Insert 限制   

1.有組的函數        

2.有group by 子句  

 3.用了distinct 關鍵字   

4.有rownum 列    

5.有運算式的列  

 6.基表中有not null 的列，但該列沒有出現在視圖的定義裡     

Top-n 查詢       使用 where rownum <=5   -- 前5條     

查詢結果的集合操作    

並集 union / union all      

union all 不進行排序，也不去掉重複的行   

uion  進行排序，去掉重複的行  

交集 intersect       相同的部分，進行排序，去掉重複的行  

補集 minus         第一個表有，第二個表沒有的行去掉重複的行

九、 分組

 group by  分組，在有 group by  的 select 語句中，不是組的列一定要放在 group by  中
對 group by 結果篩選，使用 having  過濾

    rollup
         rollup分組，一次全表掃描， group by rollup(列1，列2，列3...,列n),結果集共有 n+1 個
         Grouping(列名) 表示該列是否參加了分組操作，0：參加; 1： 末參加
 
   cube 
        group by cube(列1，列2，列3...,列n),結果集共有 2^n 個

樹結構查詢
    start with 子句，處理階層的資料
     start with(列1=‘val1‘)  connect by prior id=父級id
 
 查詢時，刪除節點，下級保留
        select name from t2
        where name<> ‘will‘
        start with(id=‘004‘)
        connect by prior  id=fid
 
  刪除節點及其子節點
         select name from t2
         start with(id=‘004‘)
        connect by prior  id=fid
        and name<> ‘will‘

 

Oracle 學習筆記(一)