MySQL函式大全(字串函式，數學函式，日期函式，系統級函式，聚合函式)

目錄

字串函式

數學函式

日期函式

系統級函式

聚合函式

字串函式

1.char_length(' '):計算字串字元的長度

select char_length('abcd我');

執行結果如下：

2.length(' ')：計算字串的長度。這裡需要注意的是，一個漢字長度是3。

select length('abcd我');

執行結果如下：

3.concat(' ',' '...):字串拼接

select concat('我','ni');

執行結果如下：

4.concat_WS('分隔符',' ',' '...):用指定的符號拼接

select concat_WS('-','ni','我')

執行結果如下：

5.group_concat(distinct FIELD_NAME order by FIELD_NAME[DESC]) :配合分組查詢，進行行轉列

舉例說明：比如在一張成績表中，查詢學生學了哪些課程。

select student_id，course_id from score;

這句話會將所有學生所有學的課程列出來，導致會有很多行。

如果使用group_concat，按照student_id分組,將每個學生的課程行轉列，變成一行來顯示，就會看得一目瞭然。

6.insert(s1,x,len,s2):從字串s1的x位置開始，被字串s2取代len個字元。

select insert('我愛你',2,2,'哈哈哈');

執行結果如下：

7.lower(' '):轉小寫

select lower('A我A');

執行結果如下：

8.upper(''):轉大寫

select upper('a我a');

 執行結果如下：

9.left('',n):字串從最左邊開始的n個字元

select left('abcd',3);

執行結果如下：

10.right('',n):字串從最右邊開始的n個字元

select right('efgh',3);

執行結果如下：

11.mid('',n,len):從字串的第n個位置開始，取len個字元

select mid('efgh',2,2);

執行結果如下：

12.lpad(s1,len,s2):左填充

select lpad('abc',5,'cd');

執行結果如下：

13.rpad(s1,len,s2):右填充

select rpad('abc',5,'cd');

執行結果如下：

14.ltrim(''):左去空

select ltrim('   abc');

執行結果如下：

15.rtrim(''):右去空

select rtrim('abc   ');

執行結果如下：

16.trim(''):兩端去空

select trim('  abc   ');

執行結果如下：

17.trim(s1 from s):刪除s兩端的所有s1

select trim('ab' from 'bcdabefhab');

執行結果如下：

18.repeat('',n):讓字串重複n次

select repeat('abc',3);

 執行結果如下：

19.space(n):返回由n個空格組成的字串

select concat('我愛你',space(4),'他愛她');

  執行結果如下：

20.replace('1','2','3'):將字串1包含字串2的部分由引數3替代

select replace('abcd','bc','-');

   執行結果如下：

21.strcmp(s1,s2):比大小，返回0,1,-1。若為-1，則s1<s2;若為1，則s1>s2;若為0，則s1=s2

select strcmp('ab','ad');

 執行結果如下：

select strcmp('afce','adcd');

執行結果如下：

select strcmp('ad','ad');

 執行結果如下：

22.locate('1','2'):判斷2中是否有1，有則返回其第一次出現的位置

select locate('ab','bcdabefgab');

  執行結果如下：

23.reverse(''):反轉字串

select reverse('abc');

   執行結果如下：

24.elt(N,'',''...):返回第N個字串。根據引數1指定的位置，提取以引數2為1起，之後的對應位置的值，若位置不存在返回null

select elt(2,'a','v','c','d');

    執行結果如下：

25.field('1','2','3',...):返回2,3之後的所有字串中第一次出現與1匹配的位置,沒有返回0。

select field('ab','cd','ab');

     執行結果如下：

26.find_in_set('1',',,,...'):返回1在後面很多字串(以逗號分割的字串)中能匹配上的位置，匹配不上，返回0

select find_in_set('b','abbcd,abc,b');

      執行結果如下：

27.make_set(x,'1','2'...):根據引數1轉換成二進位制後的結果，保留和集合元素數量位低位值，然後逆序匹配提取子集合，1就提取，0不提取

select make_set(25,'aa','bb','cc','dd','ee');

   執行結果如下：

因為25轉成二進位制為11001,將二進位制轉一下為10011，所以結果為aa,dd,ee。

28.substring_index('str','分隔符','擷取位置')：擷取字串

從左邊開始擷取到第三個逗號

select substring_index('我,你,他,她,它',',',3);

    執行結果如下：

數學函式

1.abs(v NUMBER):絕對值

select abs(-1);

     執行結果如下：

 

2.floor(v DECIMAL):去掉小數（不四捨五入）

select floor(3.456);

 執行結果如下：

3.ceil(v DECIMAL):有小數就進1

select ceil(0.000001);

 執行結果如下：

4.truncate(v DECIMAL,n INT):截斷(不四捨五入)

select truncate(3.35567,1);

  執行結果如下：

5.round(v DECIMAL)：返回離引數最近的整數，四捨五入

select round(3.56);

  執行結果如下：

6.round(v DECIMAL,n INT)：保留幾位小數，四捨五入

select round(3.564,1);

  執行結果如下：

7.rand():返回0~1之間的隨機小數

select rand();

   執行結果如下：

 

8.sign(v NUMBER):返回數字是否為正數還是負數。正數為1，負數為-1

select sign(3.56);

 執行結果如下：

9.pi()：返回圓周率的值(共7位)

select pi();

執行結果如下： 

10.pow(b NUMBER,p NUMBER):冪運算

select pow(2,3);

執行結果如下： 

11.sqrt(b NUMBER):開平方根

select sqrt(4);

執行結果如下： 

12.exp(n NUMBER):返回e的x乘方後的值

select exp(2);

執行結果如下： 

13.mod(n NUMBER,m NUMBER):n%m取餘

select mod(3,5);

執行結果如下： 

14.log2(x):返回x相對於基數2的對數

select log2(16);

執行結果如下： 

15.log10(x):返回x相對於基數10的對數

select log10(10);

執行結果如下： 

16.radians(d NUMBER):角度轉弧度

select radians(30);

執行結果如下： 

17.degree(r NUMBER)：弧度轉角度

select degrees(0.5235987755982988);

執行結果如下： 

日期函式

1.now():返回當前日期時間

select now();

執行結果如下：  

2.curdate():返回當前日期

select curdate();

執行結果如下：  

3.curtime():返回當前時間

select curtime();

執行結果如下：  

4.unix_timestamp():獲取到當前日期的時間的秒數

select unix_timestamp();

執行結果如下：  

5.unix_timestamp(['yyyy-MM-dd hh:mm:ss sss']):獲取到指定日期的時間的秒數

select unix_timestamp('2020-2-13');

執行結果如下：  

select UTC_DATE();

執行結果如下：  

6.from_unixtime(v BIGINT):返回秒數對應的日期時間

select from_unixtime(1581523200);

執行結果如下：  

7.UTC_DATE():返回格式為"YYYY-MM-DD"的當前UTC日期值

select UTC_DATE();

執行結果如下：  

8.UTC_TIME():返回格式為"hh:mm:ss"的當前UTC時間值

select UTC_TIME();

執行結果如下：  

9.year('日期'):返回日期中的年份值，範圍是1~12

select year('2020-10-3');

執行結果如下：  

10.month('日期')：返回日期中的月份值，範圍是1~12

select month('2020-10-3');

執行結果如下：  

11.week('日期')：計算日期是本年的第幾個星期，範圍是0~53

select week('2020-10-3');

執行結果如下：  

12.weekofyear('日期')：計算日期是本年的第幾個星期,1~53

select weekofyear('2020-10-3');

執行結果如下：  

13.day('日期')：返回日期中的天數

select day('2020-10-3');

執行結果如下：  

14.monthname('日期'):返回日期中的月份名稱

select monthname('2020-10-3');

執行結果如下：  

15.dayname('日期'):返回日期是星期幾

select dayname('2020-10-3');

執行結果如下：  

16.quarter('日期'):日期是第幾季度

select quarter('2020-10-3');

執行結果如下：  

17.hour('時間')：時間的小時值

select hour(curtime());

執行結果如下：  

18.minute('時間')：時間的分鐘值

select minute(curtime());

執行結果如下：  

19.second('時間')：時間的秒鐘值

select second(curtime());

執行結果如下：  

20.extract(type from date):從日期中提取一部分，type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND

select extract(year from curtime());

執行結果如下：  

21.time_to_sec('時間'):將時間轉換為秒

select time_to_sec(curtime());

執行結果如下：  

22.sec_to_time(秒數)：將以秒為單位的時間轉換為時分秒的格式

select sec_to_time(27524);

執行結果如下：  

23.to_days('日期'):計算日期d至0000年1月1日的天數

select to_days('0001-1-1');

執行結果如下：  

24.from_days(n):計算從0000年1月1日開始n天后的日期

select from_days(729669);

執行結果如下：  

25.dayofyear('日期'):本年的第幾天

select dayofyear('2020-10-3');

執行結果如下：  

26.dayofmonth('日期'):計算日期是本月的第幾天

select dayofmonth('2020-10-3');

執行結果如下：  

27.dayofweek('日期'):本週的第幾天。1為星期日

select dayofweek('2020-10-3');

執行結果如下：  

28.weekday('日期')：日期是星期幾。0為星期一

select weekday('2020-10-3');

執行結果如下：  

29.datediff(d1,d2):計算d1和d2間隔的天數

select datediff('2020-10-3','2020-10-20');

執行結果如下：  

30.adddate(date,'正負值' YEAR|QUARTER|YEAR_MONTH|MONTH|DAY|...)

select adddate('2020-10-3',interval 1 year);

執行結果如下：  

31.adddate('日期',n)：在日期後加n天后的日期

select adddate('2020-10-3',5);

 執行結果如下：  

32.subtime('時間',n);

select subtime('12:03:45',10);

 執行結果如下：   

33.date_format(now(),'%Y-%m-%d %H:%i:%s'):格式化日期時間

select date_format('2020-10-03','%Y/%m/%d');

 執行結果如下：   

34.time_format('時間','格式')：格式化時間

select time_format(curtime(),'%H:%i:%s');

 執行結果如下：   

35.計算自己的週歲

select floor(datediff(now(),'1995-10-20')/365);

 執行結果如下：   

36.計算本週第一天：select adddate(date,interval concat('-',dayofweek(date))+2 day);

select adddate(curdate(),interval concat('-',dayofweek(curdate()))+2 day);

 執行結果如下：   

37.指定日期所在季度的第一天

select concat_WS('-',year(now()),(quarter(now())-1)*3+1,'1');

 執行結果如下：   

系統級函式

1.if(expr,v1,v2):如果表示式expr成立，執行v1，否則執行v2。

select if(2>1,1,2);

 執行結果如下：    

2.ifnull(v1,v2)：如果v1不為空，則顯示v1的值；否則顯示v2的值

select ifnull(null,3);

 執行結果如下：    

select ifnull(2,3);

 執行結果如下：    

3.case when expr1 then v1 [when expr2 then v2 …][else vn] end:case表示函式開始，end表示函式結束。

如果表示式expr1成立，則返回v1的值；如果表示式expr2成立，則返回v2的值。依次類推，最後遇到else時，返回vn的值。

舉例說明：按各科平均成績從低到高和及格率的百分數從高到低順序

select
course_id,avg(score) avg,sum(case when score>60 then 1 else 0 end)/count(1)*100 lv
from
score
group by
course_id
order by
avg,lv DESC;

執行結果如下：

4.case expr when e1 then v1 [when e2 then v2 …][else vn] end:case表示函式開始，end表示函式結束。

如果表示式expr取值為e1，則返回v1的值；如果表示式expr取值為e2，則返回v2的值，依次類推，最後遇到else，則返回vn的值

舉例說明：學生成績表中，按照學生ID，列出其語文數學英語成績。

select
SC.student_id 學生ID,
max(case C.cname when '語文' then SC.score else 0 end) '語文',
max(case C.cname when '數學' then SC.score else 0 end) '數學',
max(case C.cname when '英語' then SC.score else 0 end) '英語'
from
score SC
left join
course C
on
SC.course_id=C.cid
group by SC.student_id;

執行結果如下：

5.database():查詢當前所使用的資料庫

select database();

執行結果如下： 

6.password(src_str)：加密

select password('abc');

 執行結果如下：

7.md5(src_str)：單向加密

select md5('abc');

執行結果如下：

8.encode(src_str,pass_str)：用引數2對引數1進行加密

select encode('abc','def');

執行結果如下：

9.decode(crypt_str,pass_str):使用引數2來為引數1解密

select decode(encode('abc','def'),'def');

 執行結果如下：

聚合函式

1.min(NUMBER_FIELD_NAME)：最小值

2.max(NUMBER_FIELD_NAME)：最大值

3.sum(NUMBER_FIELD_NAME)：求和

4.avg(NUMBER_FIELD_NAME)：求平均值

5.count(1/distinct FIELD_NAME)：計數