各種各樣的函數(一些自帶的便利工具)

分類：算術，字符串，日期，轉換，聚合。

1.ABS絕對值

ABS(數值)?:select?m,ABS(m)?as?abs_col?from?samplemath;

2.求余MOD

MOD(被除數，除數)?:select?n,p,MOD(n,p)?as?mod_col?from?samplemath;

3.round函數

ROUND函數(對象數值，保留小數的位數)

-----對m列的數值進行n列位數的四舍五入處理

Select?m,n,ROUND(m,n)?as?round_col?FROM?Samplamath;

4.||?拼接

Select?str1,str2,str1||str2?as?str_concat?from?samplestr;

5.拼接三個字符串(str1+str2+str3)

Select?str1,str2,str3,str1||str2||str3?as?str_concat?from?samplestr?where?str1?=?‘lala’;

//mysql?select?str1,str2,str3,concat(str1,str2,str3)?as?str_concat??from?samplestr;

6.計算字符串長度LENGTH(字串)

Select?str1,length(str1)?as?len_str?from?samplestr;//注意length函數不能再sql?server中用

7.lower---小寫轉換

Lower(字符串)?select?str1?lower(str1)?as?low_str

From?samplestr?where?str?in(‘ABC’,’aBC’,’abc’,’三本’);

8.replace--字符串替換

Replace(字符串對象，替換前字符串對象，替換后字符串對象);

Select?str1,str2,str3,replace(str1,str2,str3)?as?rep_str?from?samplestr;

9.SUBSTRING(object_str?from?截取位置FOR截取的字符數)

---截取出字符串中第三和第四位的字符

Select?str1?substring(str1?from?3?for?2)?as?sub_str

From?samplestr;

10.?Upper--大寫轉換

Select?str1?upper(str1)?as?up_str?from?samplestr?where?str?in?(‘abc’,’aBc’,’ABC’,’三本’);

----將小寫換成大寫

11.CURRENT_DATE---日期函數

Select?CURRENT_DATE;

----截取當前日期

CURRENT_TIME---獲取當前時間

Select?CURRENT_TIME;

CURRENT_TIMESTAMP?---當前日期和時間

Select?CURRENT_TIMESTAMP;

---截取日期元素EXTRACT(日期元素FROM日期);

Select?CURRENT_TIMESTAMP,EXTRACT(YEAR?FROM?CURRENT_TIMESTAMP)?AS?year,

EXTRACT(MONTH?FROM?CURRENT_TIMESTAMP)?AS?month,

EXTRACT(DAY?FROM?CURRENT_TIMESTAMP)?AS?day,

EXTRACT(HOUR?FROM?CURRENT_TIMESTAMP)?AS?hour,

EXTRACT(MINUTE?FROM?CURRENT_TIMESTAMP)?AS?minute,

EXTRACT(SECOND?FROM?CURRENT_TIMESTAMP)?AS?second;

12.類型轉換---CAST

Select?CAST(‘0001’?AS?INTEGER)?AS?int_col;

---將字符串型轉換為數值型

Select?CAST(‘2009-12-14’?AS?DATE)?AS?date_col;

---將字符串類型轉換為日期類型

COALESCE---將NULL轉換為其他值

COALESCE(數據1，數據2，。。。，);

Select?COALESCE(NULL,1)?AS?col_1,COALESCE(NULL,’test’,NULL)?AS?col_2,

Coalesce(NULL,NULL,’2009-11-01’);

6-2謂詞(predicate)

LIKE ,BETWEEN,IS?NULL、IS?NOT?NULLINEXISTS

---前方一致查詢

Select?*?from?samplelike

Where?strcol?LIKE?‘ddd%’;

---中間一致查詢

Select?*?from?samplelike?where?strcol?LIKE?‘%ddd%’;

---后方一致查詢

Select?*form?samplelike?where?strcol?LIKE?‘%ddd’;

------使用LIKE和_(下劃線)進行后方查詢一致

Select?*

From?sample

Where?strcol?LIKE?‘abc__’;

BETWEEN范圍查詢

Select?shohin_mei,hanbai_tanka

From?shohin

Where?hanbai_tanka?BETWEEN?100?AND?1000;特點在于結果中會包含100和1000這兩個值

Select?shohin_mei,hanbai_tanka

From?shohin

Where?hanbai_tanka?>=?100?AND?hanbai_tanka?<=?1000;

----上下兩個是等效的

IS?NULL?和IS?NOT?NULL---判斷是否為NULL

Select?shohin_mei,shiire_tanka

From?shohin

Where?shiire_tanka?IS?NULL;

Select?shohin_mei,shiire_tanka?From?shohin

Where?shiire_tanka?IS?NOT?NULL;

IN謂詞--OR的簡便用法

Select?shohin_mei,shiire_tanka

From?shohin

Where?shiire_tanka?=?320?OR

Shiire_tanka?=?500?OR

Shiire_tanka?=?5000;

Select?shohin_mei,shiire_tanka

From?shohin

Where?shiire_tanka?IN?(320,500,5000);

EXIST謂詞

作用：“判斷是否存在滿足某條件的記錄”；

Select?shohin_mei,hanbai_tanka

From?shohin?AS?S

Where?EXISTS(select?*?from?TenpoShohin?AS?TS

Where?TS.tenpo_id?=?‘00C’?AND?TS,shohin_id?=?S.shohin_id);

6-3CAST表達式

CAST?WHEN??THEN?

.

.

.

WHEN??THEN

ELSE?

END

---通過CASE表達式將A-C的字符串加入到商品分類當中去

Select?shohin_mei,

CASE?WHEN?shohin_bunrui?=?‘衣服’

THEN?‘A:’?||shohin_bunrui

WHEN?shohin_bunrui?=?‘辦公用品’

THEN?‘B:’?||?shohin_bunrui

WHEN?shohin_bunrui?=?‘廚房用具’

ELSE?NULL

END?NULL

END?AS?abc_shohin_bunrui

FROM?shohin;

Select?shohin_bunrui,SUM(hanbai_tanka)?AS?sum_tanak

From?shohin?group?by?shohin_bunrui;

總結

以上是生活随笔為你收集整理的mysql 函数rep_Mysql之各种各样的函数啦的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。