MySQL中函數(shù)列舉

一、ifNull(expr1,expr2)

ifNull(expr1,expr2)類似于 Oracle 中的 NVL 函數(shù)。含義是：如果第一個(gè)參數(shù)不為空，則返回第一個(gè)參數(shù)，否則返回第二個(gè)參數(shù)。

二、if(expr1,expr2,expr3)

if(expr1,expr2,expr3)類似于 Oracle 中的NVL2 函數(shù)。含義是：如果第一個(gè)表達(dá)式的值為 true(不為 0 或不為 null)，則返回第二個(gè)參數(shù)的值，否則返回第三個(gè)參數(shù)的值。

三、dateDiff函數(shù)(begin減去end)

dateDiff(begin,end);計(jì)算兩個(gè) date，dateTime 或 timeStamp 值之間的天數(shù)。該函數(shù)接受兩個(gè)任何有效日期或日期時(shí)間值的參數(shù)。如果傳遞dateTime或timeStamp值，則dateDiff函數(shù)僅將日期部分用于計(jì)算，并忽略時(shí)間部分。

SELECT 
    NOW() a,
    -- NOW() 函數(shù)返回當(dāng)前的日期和時(shí)間

    DATEDIFF(NOW(),'2008-08-08') b,
    -- begin>end，返回正數(shù)

    DATEDIFF('2008-08-08',NOW()) c,
    -- end<begin，返回負(fù)數(shù)

    DATEDIFF(NOW(),'2008-08-08') / 7  as weeks1,
    ROUND(DATEDIFF(NOW(),'2008-08-08') / 7, 2) as weeks2,
    DATEDIFF(NOW(),'2008-08-08') / 30 as months1,
    ROUND(DATEDIFF(NOW(),'2008-08-08') / 30,2) as months2
    -- 將dateDiff函數(shù)的返回值除以7或30，可以計(jì)算周或月作為間隔時(shí)間

四、 timeStampDiff函數(shù)

1??區(qū)別于dateDiff(begin,end);，timeStampDiff(unit,begin,end);返回end-begin的結(jié)果，其中begin和end是 date 或 dateTime 表達(dá)式。

2??該函數(shù)允許其參數(shù)具有混合類型，例如，begin是 date 值，end可以是 dateTime 值。 如果使用 date 值，則timeStampDiff函數(shù)將其視為時(shí)間部分為“00:00:00”的 dateTime 值。

3??unit參數(shù)是確定(end-begin)的結(jié)果的單位，表示為整數(shù)。 以下是有效單位：

  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
SELECT 
    TIMESTAMPDIFF(YEAR, '2010-01-01', '2000-12-31') YEAR,
    -- 不足10年，為9。而不是(2000-2010)

    TIMESTAMPDIFF(MONTH, '2000-01-31', '2000-08-08') MONTH,
    -- 不足7個(gè)月，為6。而不是(8-1)

    TIMESTAMPDIFF(DAY, '2010-01-01', '2000-01-01') DAY,

    TIMESTAMPDIFF(MINUTE, '2000-01-01 10:00:00', '2000-01-01 10:45:59') MINUTE,
    -- 差值應(yīng)該是45分59秒。 但是，將unit參數(shù)傳遞為MINUTE，此時(shí)，函數(shù)返回45分鐘

    TIMESTAMPDIFF(SECOND, '2000-01-01 00:00:00', '2000-01-01 00:45:59') SECOND
    -- 注：45分59秒= 45×60 + 59(秒)= 2759秒

五、截取字符串subString(str, pos)

subString(str, pos)等同于subStr(str, pos)

select 
    name a,
    SUBSTRING(name FROM 1 FOR 1) b,
    SUBSTRING(name ,1,1) c,
    SUBSTRING(name ,3,1) d,
    substring(name ,3,2) e
from tab;

select substr('abcde',2,3),substr('abcde',2),substr('abcde',-4,5) from dual;

六、trim( )、rTrim( )、lTrim( )

SELECT
  TRIM('   solos   ') a,
  TRIM(LEADING 's' FROM 'solos') b,
  TRIM(LEADING 'S' FROM 'solos') c,
  TRIM(TRAILING 's' FROM 'solos') d,
  TRIM(BOTH 's' FROM 'solos') e,
  LTRIM('   solos   ') f,
  RTRIM('   solos   ') g,
  TRIM('a' from 'abacde') h;

七、concat()

1??concat(str1, str2,...)將多個(gè)字符串連接成一個(gè)字符串。返回結(jié)果為連接參數(shù)產(chǎn)生的字符串，如果有任何一個(gè)參數(shù)為 null，則返回值為 null。

2??concat(str1, seperator,str2,seperator,...)返回結(jié)果為連接參數(shù)產(chǎn)生的字符串并且有分隔符，如果有任何一個(gè)參數(shù)為 null，則返回值為 null。

select * from ext_area;   -- 3193
select * from ext_area where area like concat('%');      --  3193
select * from ext_area where area like concat('%%');     --  3193
select * from ext_area where area like concat('%''%');   --  null
select * from ext_area where area like concat('%' '%');  --  3193

select concat('hello',',word!')  str; --  結(jié)果為：'hello,word!'

八、concat_ws()

區(qū)別于concat()，concat_ws(separator, str1, str2, ...)雖然也是將多個(gè)字符串連接成一個(gè)字符串，但是可以一次性指定分隔符【concat_ws 就是 concat with separator】

說明：第一個(gè)參數(shù)指定分隔符。需要注意的是分隔符不能為 null，如果為 null，則返回結(jié)果為 null。

九、group_concat()

以 deptId 分組，把 name 的[去重]值打印在一行，逗號(hào)分隔(默認(rèn))

select deptId,group_concat(distinct name) from tab group by deptId;

執(zhí)行 sql 之后：

注：group_concat 只有與 group by 同時(shí)使用才能產(chǎn)生效果。

十、length： 獲取字節(jié)個(gè)數(shù)(utf-8 一個(gè)漢字為3個(gè)字節(jié)，gbk為2個(gè)字節(jié))

SELECT
    LENGTH( 'crud' ) a,
    LENGTH( '哈crud' ) b,
    length( 'a bc' ) c 
FROM
    DUAL;

十一、大小寫轉(zhuǎn)換

SELECT
    lower( 'NAME' ) a,
    upper( 'NaMe' ) b,
    CONCAT( UCASE( LEFT ( 'name', 1 ) ), SUBSTRING( 'name', 2 ) ) c 
FROM
    DUAL;

十二、instr：返回子串第一次出現(xiàn)的索引，如果沒有則返回0

SELECT
    INSTR( '哈嘍啊', '哈' ) a, 
    INSTR( '哈嘍啊', '哦' ) b;
 -- a為1；b為0 （mysql是從1開始算位數(shù)）

十三、用指定字符實(shí)現(xiàn)左/右填充指定長(zhǎng)度

SELECT
    LPAD( 'crud', 6, '*' ) a,
    LPAD( 'crud', 2, '*' ) b,
    RPAD( 'crud', 7, '*' ) c,
    RPAD( 'crud', 2, '*' ) d

十四、替換函數(shù)replace() 

select 
  REPLACE('6166666','1','ac') a,
  REPLACE('666A666','A','ac') b
from dual;

/四舍五入/
select round(23.44),round(23.44,1),round(53.44,-2.2) from dual;

/向上取整，向下取整/
select ceil(23.44),floor(23.44) from dual;

/絕對(duì)值/
select abs(-33.42),abs(33.42),abs(0) from dual;

/求余函數(shù)/
select mod(5,2) from dual;

/m的n次方/
select power(2,3),power(null,2) from dual;

/取平方根/
select sqrt(16) from dual;

/數(shù)學(xué)函數(shù)/
select tan(6) from dual;

/拼接字符串/
select 'ab'||'cd',concat('ab','cd') from dual;

/獲取系統(tǒng)時(shí)間/
select sysdate from dual;

/當(dāng)前日期加整數(shù)月/
select add_months(sysdate,3),add_months(sysdate,-3) from dual;

/返回下一個(gè)指定天的日期/
select next_day(sysdate,'星期一') from dual;

/返回date所在月的最后一天/
select last_day(sysdate) from dual;

/計(jì)算兩個(gè)日期之間間隔的月數(shù)/
select months_between('18-12月-15','18-1月-15') from dual;

/獲取年月日時(shí)分秒/
select extract(year from sysdate) YEAR,extract(month from sysdate) month,extract(day from sysdate) DAY from dual;

FIND_IN_SET(str,strlist)

  1. 假如字符串str在由N子鏈組成的字符串列表strlist中，則返回值的范圍在1到N之間。
  2. 一個(gè)字符串列表就是一個(gè)由一些被‘,’符號(hào)分開的自鏈組成的字符串。
  3. 如果第一個(gè)參數(shù)是一個(gè)常數(shù)字符串，而第二個(gè)是typeSET列，則FIND_IN_SET()函數(shù)被優(yōu)化，使用比特計(jì)算。
  4. 如果str不在strlist或strlist為空字符串，則返回值為0。
  5. 如任意一個(gè)參數(shù)為NULL，則返回值為NULL。這個(gè)函數(shù)在第一個(gè)參數(shù)包含一個(gè)逗號(hào)(‘,’)時(shí)將無法正常運(yùn)行。

strlist：一個(gè)由英文逗號(hào)“,”鏈接的字符串，例如："a,b,c,d"，該字符串形式上類似于SET類型的值被逗號(hào)給鏈接起來。

示例：SELECT FIND_IN_SET('b','a,b,c,d') //返回值為2，即第2個(gè)值。

MySQL中的 IN和FIND_IN_SET的查詢問題

原來以為mysql可以進(jìn)行這樣的查詢
1??select id, list, name from table where 'Uzi' IN (list)
注：1. table含有三個(gè)字段id:int, list:varchar(255), name:varchar(255)。實(shí)際上這樣是不行的。

測(cè)試代碼：

CREATE TABLE test (
  id int(8) NOT NULL auto_increment,
  name varchar(255) NOT NULL,
  list varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
)
insert into test values (1, 'name', 'Uzi,xiaohu,letme');
insert into test values (2, 'name2', 'xiaohu,Uzi,letme');
insert into test values (3, 'name3', 'letme,Uzi,xiaohu');

test1:sql =select * from test where 'Uzi' IN ('list');得到結(jié)果空值。
test2:sql =select * from test where FIND_IN_SET('Uzi','list');得到三條數(shù)據(jù)：

1   name    Uzi,xiaohu,letme
2   name2   xiaohu,Uzi,letme
3   name3   letme,Uzi,xiaohu

修改表數(shù)據(jù)：
update test set list='Uzi' where id='1';
然后執(zhí)行test1的sql，可以返回一條結(jié)果。

再來看看這個(gè)：
2??select id, list, name from table where 'Uzi' IN ('libk', 'zyfon', 'Uzi');

1??2??到底有什么區(qū)別呢？為什么第一條不能取得正確的結(jié)果，而第二條卻能取得結(jié)果？

原因其實(shí)是1??中(list)，list是變量， 而2??中('libk', 'zyfon', 'Uzi')是常量。所以如果要讓1??能正確工作，需要用find_in_set()：
select id, list, name from table where FIND_IN_SET( 'Uzi' , list)1??的改進(jìn)版。

總結(jié)：所以如果list是常量，則可以直接用IN，否則要用FIND_IN_SET()函數(shù)。

十、CAST()函數(shù)和CONVERT()函數(shù)

MySQL 的CAST()和CONVERT()函數(shù)可用來獲取一個(gè)類型的值，并產(chǎn)生另一個(gè)類型的值。兩者具體的語法如下：

CAST(value as type);
CONVERT(value, type);

就是CAST(xxx AS 類型) CONVERT(xxx,類型)?？梢赞D(zhuǎn)換的類型是有限制的。這個(gè)類型可以是以下值其中的一個(gè)：

  • 二進(jìn)制，同帶binary前綴的效果 : BINARY
  • 字符型，可帶參數(shù) : CHAR()
  • 日期 : DATE
  • 時(shí)間: TIME
  • 日期時(shí)間型 : DATETIME
  • 浮點(diǎn)數(shù) : DECIMAL
  • 整數(shù) : SIGNED(字段取值范圍是-128 - 127)
  • 無符號(hào)整數(shù) : UNSIGNED(字段取值范圍是0-255)
    例：
    SELECT CONVERT('23',SIGNED) a,CAST('125e342.83' AS signed) b,CAST('3.35' AS signed) c;
    結(jié)果如下：

應(yīng)用

在MySQL中，進(jìn)行中文排序和查找的時(shí)候，對(duì)漢字的排序和查找結(jié)果往往都是錯(cuò)誤的。 這種情況在MySQL的很多版本中都存在。之所以這樣，是因?yàn)镸ySQL在查詢字符串時(shí)是大小寫不敏感的，在編繹MySQL時(shí)一般以ISO-8859字符集作為默認(rèn)的字符集，因此在比較過程中中文編碼字符大小寫轉(zhuǎn)換造成了這種現(xiàn)象。

解決方法：
1??對(duì)于包含中文的字段加上”binary”屬性，使之作為二進(jìn)制比較，例如將name char(10)改成name char(10) binary。
2??如果使用源碼編譯MySQL，可以編譯MySQL時(shí)使用 –with–charset=gbk參數(shù)，這樣MySQL就會(huì)直接支持中文查找和排序了（默認(rèn)的是latin1）。也可以用extra-charsets=gb2312,gbk來加入多個(gè)字符集。
3??如果不想對(duì)表結(jié)構(gòu)進(jìn)行修改或者重新編譯MySQL，也可以在查詢語句的 order by 部分使用 CONVERT 函數(shù)。例如：

select * from tab order by CONVERT(ChineseColumnName USING gbk);

UTF8 默認(rèn)校對(duì)集是utf8_general_ci，它不是按照中文來的。需要強(qiáng)制讓MySQL按中文來排序。

十一、DATE_FORMAT(date,format)

DATE_FORMAT() 函數(shù)用于以不同的格式顯示日期/時(shí)間數(shù)據(jù)。date 參數(shù)是合法的日期。format 規(guī)定日期/時(shí)間的輸出格式。

可以使用的格式有：
格式 描述
%a 縮寫星期名
%b 縮寫月名
%c 月，數(shù)值
%D 帶有英文前綴的月中的天
%d 月的天，數(shù)值(00-31)
%e 月的天，數(shù)值(0-31)
%f 微秒
%H 小時(shí) (00-23)
%h 小時(shí) (01-12)
%I 小時(shí) (01-12)
%i 分鐘，數(shù)值(00-59)
%j 年的天 (001-366)
%k 小時(shí) (0-23)
%l 小時(shí) (1-12)
%M 月名
%m 月，數(shù)值(00-12)
%p AM 或 PM
%r 時(shí)間，12-小時(shí)（hh:mm:ss AM 或 PM）
%S 秒(00-59)
%s 秒(00-59)
%T 時(shí)間, 24-小時(shí) (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天，與 %X 使用
%v 周 (01-53) 星期一是一周的第一天，與 %x 使用
%W 星期名
%w 周的天 （0=星期日, 6=星期六）
%X 年，其中的星期日是周的第一天，4 位，與 %V 使用
%x 年，其中的星期一是周的第一天，4 位，與 %v 使用
%Y 年，4 位
%y 年，2 位

應(yīng)用

select DATE_FORMAT(NOW(),'%b %d %Y %h %i %p');
select DATE_FORMAT(NOW(),'%m-%d-%Y');
select DATE_FORMAT(NOW(),'%d %b %y');
select DATE_FORMAT(NOW(),'%d %b %Y %T:%f');

結(jié)果如下：

Jan 11 2020 01 17 PM
01-11-2020
11 Jan 20
11 Jan 2020 13:17:40:000000
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

友情鏈接更多精彩內(nèi)容