翼斯待兒。翼Style

日期與時間

1.取得系統日期
select sysdate from dual;
2008/1/20 下午 10:31:20

2.日期欄位轉字串格式
select to_char(crdate,'YYYY/MM/DD HH24:MI:SS') from table_a;
2008/01/20 22:31:20 

3. 取得英文格式之月份
select to_char(sysdate,'MON','nls_date_language=english) from dual;

APR

4.取星期幾 

SELECT TO_CHAR(SYSDATE,'DAY') FROM DUAL

星期六

5.日期為在星期中的第幾天

SELECT TO_CHAR(SYSDATE-1,'D') FROM DUAL

7

6.取得下個月的今天 

select ADD_MONTHS(sysdate,要加的月數) from dual

select sysdate "Today",add_months(sysdate,1) "Next Month" from dual;

Today                     Next Month

2009/4/26 下午 02:17:07   2009/5/26 下午 02:17:07

7.日期相減

select  ROUND((DATE1-DATE2),2)  from table_a

相減出來的結果單位是天

(A.DATE-B.DATE)*24*60*60 取得二個日期相差的秒數

資料處理

1.從其他(或目前)table新增資料到目前table的語法
insert into table_a(id, name) (select id,name from table_b where id=1);

2.建立新的table，但是schema要跟某個table一樣
create table table_b as (select * from table_a where 1=2);   //只copy schema
create table table_b as (select * from table_a);                  //copy schema及資料

3.新增欄位名稱 

alter table table_a add (column_b varchar2(1) default 'Y');

4.修改欄位名稱 

ALTER TABLE table_a RENAME COLUMN column_b TO column_c;

5.刪除NOT NULL 限制的語法
ALTER TABLE table_a MODIFY (column_b NULL);

6.利用修改的方式，建立主鍵
ALTER TABLE table_a ADD CONSTRAINT column_b_pk PRIMARY KEY (column_b);

字串處理

1.INSTR 找尋字串所在位置
INSTR(string1, string2,[, n[ ,m]])
select INSTR('true blood' , 'blood') from dual
6

2. LENGTH 取得字串長度 

LENGTH(string)

select length('true blood') from dual

10

3.REPLACE字串取代 

REPLACE(string, search_string, [,replacement_string])

select replace('Bill,I love you','Bill','Sookie') from dual

Sookie,I love you

4.LPAD字串左邊補上特定字元

LPAD(char1,n,char2)

select lpad('Bill', 8, '0') from dual;

0000Bill

RPAD ()在字串右邊補上特定字元

5.字串的截取(從後面或前面取)

substr (string string, int start [, int length])

select substr('This is Bill.', 9, 4) from dual 

Bill

6.只取欄位中長度為5碼之字串 

select column_b from table_a where column_b like '_____'

7.字串合併 

select concat(column_a,concat(column_b,column_c) ) from table_d

顯示資訊

1.顯示table的所有欄位資訊 

DESC table_a;

2.顯示目前user的所有的table名稱

 SELECT TABLE_NAME FROM USER_TABLES;

3.顯示目前user的所有的sequence名稱 

SELECT SEQUENCE_NAME FROM USER_SEQUENCES;

4.顯示目前user的所有的view名稱 

SELECT VIEW_NAME FROM USER_VIEWS;

5.查詢tablespace使用量

 select segment_name, bytes/1024/1024 M from user_segments order by 2 desc;

SEGMENT_NAME        M

table_a                   5

table_b                   3

select sum(m) from ( select segment_name, bytes/1024/1024 m from user_segments ) ;

SUM(M)

390.984375

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE" FROM
SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C                                                                                    
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; 

TABLESPACE_NAME   TOTAL             USED                FREE            % USED           % FREE

database_a                    20971520      10092544         10813440        48.125           51.5625

6.取得資料庫語系

7.查詢 db link
SELECT * FROM user_db_links;
DB_LINK                         USERNAME    PASSWORD                      HOST               CREATED 
database_a.US.ORACLE.COM  database_a  database_a_passwd       database_a    2010/12/19 下午 03:42:23