sql語句

SQL> create table t as select * from dba_objects;  資料字典

SQL> desc t;

SQL> select owner from t where rownum <=10; 僞列  rowid

SQL> select count(*) from t;

僞列 rowid,rownum

SQL> delete from t where rownum<=10000;（删除t表10000行）

SQL> select OBJECT_NAME,OBJECT_ID  from t order by OBJECT_ID desc(倒序)

SQL> select * from t order by OBJECT_ID asc (正序)

SQL> select count(*),owner from t group by owner;(分組)

 where   group by    having 

SQL> update t set owner='abc' where OBJECT_ID=51073;

運算符:

等于 = 

大于 >

小于 <

大于等于 >=

小于等于 <=

不等于 !=

不等于 ^=

不等于 <>

--------------------- 

模糊查詢like

( _代表一個字元，%代表所有字元)

SQL> select OBJECT_NAME,owner from t where owner like '___TT';

SQL> select * from t where owner like '%TT%';

-----------------------

is null（空）   is not null （非空）

SQL> select * from t where owner is null;

------------------------

SQL> select * from t where OBJECT_ID between 51070 and 51080;

（OBJECT_ID值在10000到20000之間）

--------------------------vfast_jiaoxue_database_10

where 子查詢

SQL> select * from t where owner =(select owner from t where OBJECT_ID=51079);

組合表

 create table t1 (id int,xingming varchar2(10));

insert into t1 values(1,'zs');

insert into t1 values(2,'ls');

insert into t1 values(3,'ww');

SQL> commit;

create table t2 (id int,gongzi int);

insert into t2 values(1,10000);

insert into t2 values(2,20000);

commit;

SQL> select T1.xingming,T2.gongzi from t1,t2 where T1.id=T2.id;

-------------------------

建立視圖

SQL> create view v_t2

    as select * from t2 where gongzi<=10000;

SQL> select * from v_t2;

---------------------------

oracle 串函數  ************

|| 連接配接符

SQL> select id||'        '||xingming from t1;

LTRIM,RTRIM,TRIM

LTRIM:左删除

RTRIM:右删除

TRIM:删除串兩邊的字元

SQL> select RTRIM(gongzi,'0') from t2;

SQL> select rtrim(' aaa ') from dual;

（發現aaa的右面空格沒有了）

SQL> select trim(' aaa ') from dual;

(發現aaa兩端的空格沒了)

---------------------------------

length求得是字元長度

SQL> select length('haha.hehe') from dual;

lengthb求得是位元組長度(如有中文時使用)

SQL> select lengthb('haha.hehe') from dual;

-----------------------------------

TO_CHAR 是把日期或數字轉換為字元串 ********************

SQL> select to_char(123,'9999.00') from dual 

SQL> select to_char(123,'$99,999.99') from dual;

檢視時間

SQL> select sysdate from dual;

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

SQL> create table t12 (id int,t date);  

SQL>  insert into t12 values(1,to_date('2012-12-12 12:12:12','yyyy-mm-dd hh24:mi:ss'));

--------------------------------------

substr 截取函數  ********************

SQL> select substr('abcdef',2,3) from dual;

(截取從第二個字元開始的3個字元)

SQL> select substr('abcdef',-5) from dual;

(截取後5位)

to_char(sj,'yyyy-mm-dd')  t3    sj index

-------------------------------------

文法:

instr(string,substring,position,occurrence)

string:代表源字元串

substring:代表想從源字元串中查找的子串

position:代表查找開始的位置,預設為1

occurrence:代表查找值弟幾次出現,結果為字元串的位置

SQL> select instr('hello word','o',-1,1) from dual;

ascii函數将字元轉換成其對應的ascii碼，而chr函數将數字轉換成對應acscii碼的字元

SQL> select ascii('a') from dual;

SQL> select chr(97) from dual;

---------------------------------------

四個字元組成的代碼 (SOUNDEX) 以評估兩個字元串的相似性

SQL> select soundex('two'),soundex('too'),soundex('to') from dual;

SQL> select soundex('abc'),soundex('cba') from dual;

-----------------------------------------

聚集函數  *************************

avg(平均值)

SQL> select avg(gongzi) from t2;

sum(求和)

SQL> select sum(gongzi) from t2;

min(最小值)

SQL> select min(gongzi) from t2;

max(最大值)

SQL> select max(gongzi) from t2;

count(行計數)

SQL> select count(*) from t2; 

--------------------------------------------

nvl(傳回一個非空值) ******************

nvl(e1,e2)功能為: 如果e1值為空,結果就顯示為 e2設定的值

SQL>  select nvl(name,0) from aa;

NVL2(E1, E2, E3)的功能為：如果E1為NULL，則函數傳回E3，若E1不為null，則傳回E2

SQL> select nvl2(name,1,0) from aa;

abs ceil 傳回絕對值 

SQL> select abs(-5) from dual; (傳回值為5)

SQL> select abs(5.5)  from dual;(傳回值為5.5)

SQL> select ceil(5.4) from dual;(傳回值為6)

---------------------------------------------

vm_concat合并列

SQL> select * from tt;

      BMID NAME               GZ

---------- ---------- ----------

         1 zs              10000

         1 ls              12000

         1 ww              15000

         2 haha            30000

         3 hehe            50000

顯示每部門人名

SQL> select id,wm_concat(xingming) from t5 group by id;

顯示每部門人名和工資

SQL> select id,wm_concat(xingming||'('||gongzi||')') from t5 group by id;

------------------------------------------

sqrt 求平方根

SQL> select sqrt(64) from dual;

LPAD

lpad函數将左邊的字元串填充一些特定的字元，其文法格式如下： lpad( String, 截取長度, 填充字元串 ) 如果截取長度比原字元串的長度要短，lpad函數将會把字元串截取成截取長度; 填充字元串是要添加到String的左邊，如果這個參數未寫，lpad函數将會在string1的左邊補齊空格。

 例如： lpad('tech', 7); 将傳回' tech'

lpad('tech', 2); 将傳回'te'

lpad('tech', 8, '0'); 将傳回'0000tech'

lpad('tech on the net', 15, 'z'); 将傳回 'tech on the net'

lpad('tech on the net', 16, 'z'); 将傳回 'ztech on the net'

Lpad（str1,number,str2），這個函數的意思是，如果str1不足number那麼多位，則使用str2去補齊左邊的空

SELECT lpad('!!',5,'aaaa') FROM dual

--結果

aaa!!

rpad函數與lpad函數正好相反，rpad函數将左邊的字元串填充一些特定的字元，其文法格式如下： rpad( String, 截取長度, 填充字元串 )

SELECT rpad('!!',5,'aaaa') FROM dual

!!aaa

Round 函數 (四舍五入) ********************

SQL> select round(123.123) from dual;

SQL> select round(123.8) from dual;

trunc 截掉小數點後值

SQL> select trunc(123.8) from dual;

-----------------------------------------------

sign取數字n的符号,大于0傳回1,小于0傳回-1,等于0傳回0

SQL> select sign(100),sign(-100),sign(0) from dual;

-------------------------------------------------

删除重複行  ******************

SQL> select distinct(xingming) from t11;

-----------------

時間格式

SQL> select to_char(CREATED,'yyyy-mm-dd hh24:mi:ss') from t where rownum<10;

SQL> create table tt2(id int,time date);

SQL> insert into tt2 values (1,to_date('2011-11-11 11:11:11' 'YYYY-MM-DD HH24:MI:SS'));

SQL> select to_char(TIME,'YYYY-MM-DD HH24:MI:SS') from tt2;

     本文轉自陳繼松 51CTO部落格，原文連結：http://blog.51cto.com/chenjisong/1737377，如需轉載請自行聯系原作者