Oracle學習筆記六 SQL常用函數，oracle學習筆記sql

Oracle學習筆記六 SQL常用函數，oracle學習筆記sql

函數的分類

Oracle 提供一系列用於執行特定操作的函數

SQL 函數帶有一個或多個參數並返回一個值 以下是SQL函數的分類：   單行函數對於從表中查詢的每一行只返回一個值 可以出現在 SELECT 子句中和 WHERE 子句中 單行函數可以大致劃分為： 　　1.日期函數 　　2.數字函數 　　3.字符函數 　　4.轉換函數 　　5.其他函數

單行函數

日期函數

日期函數對日期值進行運算，並生成日期數據類型或數值類型的結果 日期函數包括： 　　1.ADD_MONTHS 　　2.MONTHS_BETWEEN 　　3.LAST_DAY 　　4.ROUND 　　5.NEXT_DAY 　　6.TRUNC 　　7.EXTRACT

--Sysdate:獲取系統時間函數
Select sysdate from dual;

--Add_months:指定日期增加（減少）月份的時間
Select add_months('24-10月-15', 12) from dual;
Select add_months(sysdate, 10) from dual;

--Months_between:返回兩個時間之間的月份的差
Select months_between(sysdate, '24-12月-15') from dual;

--Last_day:返回本月最後一天
Select last_day(sysdate) from dual;

--Next_day:下一個日期是什麼時候
Select next_day(sysdate, '星期六') from dual;

--To_date(日期， 日期格式):將一個日期按照指定的格式轉成一個日期數據;
Select to_date('2015/10/24', 'yyyy-mm-dd') from dual;

字符函數

字符函數接受字符輸入並返回字符或數值    

   函數

   輸入

  輸出

說明

Initcap(char)

Select initcap(‘hello’) from dual;

Hello

將首字母大寫

Lower(char)

Select lower(‘FUN’) from dual;

fun

大寫轉換為小寫

Upper(char)

Select upper(‘sun’) from dual;

SUN

小寫轉換為大寫

Ltrim(char,set)

Select ltrim( ‘xyzadams’,’xyz’) from dual;

adams

從左邊開始找，刪除指定字符

Rtrim(char,set)

Select rtrim(‘xyzadams’,’ams’) from dual;

xyzad

從右邊開始找，刪除指定字符

Translate(char, from, to)

Select translate(‘jack’,’j’ ,’b’) from dual;

back

替換字符

Replace(char, searchstring,[rep string])

Select replace(‘jack and jue’ ,’j’,’bl’) from dual;

black and blue

替換所有指定字符

Instr (char, m, n)

Select instr (‘worldwide’,’d’) from dual;

5

找到指定字符的位置

Substr (char, m, n)

Select substr(‘abcdefg’,3,2) from dual;

cd

找到指定位置的字符

Concat (expr1, expr2)

Select concat (‘Hello’,’ world’) from dual;

Hello world

拼接字符

 

以下是一些其它的字符函數：

• CHR和ASCII   CHR:將ASCII碼轉換成char字符;ASCII:得到字符的ASCII碼
• LPAD和RPAD      將不夠的字符補齊:
  • 　　例:

  • select rpad('1234.5', 10, '0') from dual;--輸出'1234.50000'

  • select job, lpad(job, 10, ' ') from emp;

• TRIM    去掉兩邊的空格
• LENGTH  計算長度

• Select * from emp2 where length(ename) = 4; --名字為4個字符的數據

• DECODE

 

 數字函數

數字函數接受數字輸入並返回數值結果

   函數

   輸入

  輸出

說明

Abs(n)

Select abs(-15) from dual;

15

取絕對值

Ceil(n)

Select ceil(44.778) from dual;

45

上取整函數

Cos(n)

Select cos(180) from dual;

-.5984601

余弦函數

Sin(n)

Select sin(0) from dual;

0

正弦函數

Floor(n)

Select floor(100.2) from dual;

100

四捨五入

Power(m,n)

Select power(4,2) from dual;

16

冪函數

Mod(m,n)

Select mod(10,3) from dual;

1

求余函數

Round(m,n)

Select round(100.256,2) from dual;

100.26

向上把數值字段捨入為指定的小數位數

Trunc(m,n)

Select trunc(100.256,2) from dual;

100.25

向下把數值字段捨入為指定的小數位數

Sqrt(n)

Select sqrt(4) from dual;

2

平方根函數

Sign(n)

Select sign(-30) from dual;

-1

符號函數

當x<0時，sgn(x)=-1 當x=0時，sgn(x)=0 當x>0時，sgn(x)=1  

轉換函數

轉換函數將值從一種數據類型轉換為另一種數據類型 常用的轉換函數有： 　　TO_CHAR  --轉換為字符 　　TO_DATE  --轉換為時間類型 　　TO_NUMBER  --轉換為Number類型  

轉換空值函數:

以下是幾個用來轉換空值的函數： 　　NVL 　　NVL2 　　NULLIF

 

Select sal, comm, sal+nvl(comm, 0) from emp;  --如果comm為null，用0表示

select itemdesc, NVL(re_level,0) from itemfile;

select itemdesc, NVL2(re_level,re_level,max_level) from itemfile;

select itemdesc, NULLIF(re_level,max_level) from itemfile;

 

 

分組函數

分組函數基於一組行來返回結果 為每一組行返回一個值    

Avg:求整個列的平均值

Select avg(sal) from emp;  --平均工資

 

Min:最小值

Select min(sal) from emp;

 

Max:最大值

Sum:總和

Count:計算條數；注：如果寫的是列名，則不統計null行。

Select count(*) from emp where job='MANAGER'; --統計有多少個經理
Select sum(sal) from emp;  --統計工資的總和
Select count(*) from emp where deptno = 30;   --統計部門號為30的有多少個人
select count(distinct job) from emp;  --有多少個崗位，去除重復數據

 注：查詢出來的結果不能喝其他行列一同顯示：

select ename, min(sal) from emp;--錯誤寫法

 

GROUP BY子句

用於將信息劃分為更小的組 每一組行返回針對該組的單個結果

select p_category, MAX(itemrate) from itemfile group by p_category;

HAVING子句

用於指定 GROUP BY 子句檢索行的條件

select p_category, MAX(itemrate) from itemfile group by p_category having p_category not in ('accessories');

 

分析函數

分析函數根據一組行來計算聚合值 用於計算完成聚集的累計排名、移動平均數等 分析函數為每組記錄返回多個行       以下三個分析函數用於計算一個行在一組有序行中的排位，序號從1開始 1.ROW_NUMBER 返回連續的排位，不論值是否相等 2.RANK 具有相等值的行排位相同，序數隨後跳躍 3.DENSE_RANK 具有相等值的行排位相同，序號是連續的  

select d.dname, e.ename, e.sal, DENSE_RANK()
  OVER (partition by e.deptno order by e.sal desc)
  as denrank
from emp e, dept d WHERE e.deptno = d.deptno;