9)mysql常見函數(shù)

常用聚合函數(shù)

COUNT(*)/COUNT(col)：計算符合條件的數(shù)據(jù)行數(shù)
SUM(col_name)：      計算符合條件的數(shù)值列合計值
AVG(col_name)：      計算符合條件的數(shù)值列平均值
MAX(col_name)：      計算符合條件的任意列中數(shù)據(jù)的最大值
MIN(col_name)：      計算符合條件的任意列中數(shù)據(jù)的最小值

常用時間函數(shù)1

CURDATE() / CURTIME()：   返回當前日期 / 返回當前時間
NOW()：                   返回當前的日期和時間

DATE_FORMAT(date, fmt)：  按照fmt的格式，對日期date進行格式化
fmt格式為：
%Y:四位的年 %m:月份(00..12) %d:天(00..31)
%H:小時(00..24) %i分鐘(00..59) %s:秒(00..59)

SEC_TO_TIME(seconds)：    把秒數(shù)轉(zhuǎn)換為(小時：分：秒)
TIME_TO_SEC(time)：       把時間（小時：分：秒）轉(zhuǎn)換為秒數(shù)

示例
SELECT CURDATE(),CURTIME(),NOW();
輸出
curdate()   curtime()  now()                
----------  ---------  ---------------------
2019-08-21  08:49:50   2019-08-21 08:49:50  

示例
SELECT DATE_FORMAT(NOW(),'%Y--%m--%d  %H:%i:%s')
輸出
DATE_FORMAT(NOW(),'%Y--%m--%d  %H:%i:%s')  
-------------------------------------------
2019--08--21  09:04:16     
 
示例       
SELECT DATE_FORMAT(NOW(),'%Y--%m--%d')
輸出
DATE_FORMAT(NOW(),'%Y--%m--%d')  
---------------------------------
2019--08--21

示例          
SELECT DATE_FORMAT(NOW(),'%H:%i:%s')
輸出
DATE_FORMAT(NOW(),'%H:%i:%s')  
-------------------------------
09:06:10                       

示例
SELECT DATE_FORMAT(NOW(),'2010-01-01')
輸出
DATE_FORMAT(NOW(),'2010-01-01')  
---------------------------------
2010-01-01

示例
SELECT SEC_TO_TIME(3750)
輸出
sec_to_time(3750)  
-------------------
01:02:30          
 
示例
SELECT TIME_TO_SEC('1:2:30')
輸出
time_to_sec('1:2:30')  
-----------------------
                   3750

更多時間函數(shù)如下

DATEDIFF函數(shù)示例

DATE_ADD函數(shù)示例

EXTRACT函數(shù)示例

常用字符串函數(shù)

示例

創(chuàng)建test表格
CREATE TABLE test(
    title VARCHAR(20) NOT NULL,
    class_name VARCHAR(10) NOT NULL
)
插入數(shù)據(jù)
title                   class_name       
----------------------  -----------------
MySQL課程-79889           大數(shù)據(jù)        
MySQL課程-69546           Hbase            
PostgreSQL課程-43917      大數(shù)據(jù)        
MySQL課程-88343           MySQL            

SELECT *,CONCAT(title,class_name)
FROM test;
輸出
title                   class_name       concat(title,class_name)               
----------------------  ---------------  ---------------------------------------
MySQL課程-79889           大數(shù)據(jù)              MySQL課程-79889大數(shù)據(jù)             
MySQL課程-69546           Hbase            MySQL課程-69546Hbase                 
PostgreSQL課程-43917      大數(shù)據(jù)              PostgreSQL課程-43917大數(shù)據(jù)        
MySQL課程-88343           MySQL            MySQL課程-88343MySQL  


SELECT *,CONCAT_WS('||',title,class_name)
FROM test;
輸出
title                   class_name       CONCAT_ws('||',title,class_name)         
----------------------  ---------------  -----------------------------------------
MySQL課程-79889           大數(shù)據(jù)              MySQL課程-79889||大數(shù)據(jù)             
MySQL課程-69546           Hbase            MySQL課程-69546||Hbase                 
PostgreSQL課程-43917      大數(shù)據(jù)              PostgreSQL課程-43917||大數(shù)據(jù)        
MySQL課程-88343           MySQL            MySQL課程-88343||MySQL      


SELECT class_name,CHAR_LENGTH(class_name),LENGTH(class_name)
FROM test;
輸出
class_name       char_length(class_name)  length(class_name)  
---------------  -----------------------  --------------------
大數(shù)據(jù)                                 3                     9
Hbase                                 5                     5
大數(shù)據(jù)                                 3                     9
MySQL                                 5                     5


格式化為 ##.###.###，保留小數(shù)點后5位
SELECT FORMAT(12345.6789,5)
輸出
format(12345.6789,5)  
----------------------
12,345.67890       


SELECT LEFT('www.helloChina.com',3) AS a -- 從左邊起截取3位字符
    ,RIGHT('www.helloChina.com',3) AS b -- 從右邊起截取3位字符
    ,LEFT('今天是個好日子',3) AS c
    -- 如果沒有指定長度，則返回從從指定位置之后所有字符
    ,SUBSTRING('今天是個好日子',3) AS d 
    -- 指定長度4是偷，返回4個字符
    ,SUBSTRING('今天是個好日子',3,4) AS e
輸出
a       b       c          d                e             
------  ------  ---------  ---------------  --------------
www     com     今天是        是個好日子            是個好日                          


-- 截取課程標題里橫線之前的部分
SELECT title
    ,LOCATE('-',title) 
    ,SUBSTRING(title,1,LOCATE('-',title))   
    ,SUBSTRING(title,1,LOCATE('-',title)-1)
    ,SUBSTRING_INDEX(title,'-',1)
FROM test;
輸出
title                   LOCATE('-',title)  SUBSTRING(title,1,LOCATE('-',title))  SUBSTRING(title,1,LOCATE('-',title)-1)  SUBSTRING_INDEX(title,'-',1)  
----------------------  -----------------  ------------------------------------  --------------------------------------  ------------------------------
MySQL課程-79889                           8  MySQL課程-                              MySQL課程                                 MySQL課程                   
MySQL課程-69546                           8  MySQL課程-                              MySQL課程                                 MySQL課程                   
PostgreSQL課程-43917                     13  PostgreSQL課程-                         PostgreSQL課程                            PostgreSQL課程              
MySQL課程-88343                           8  MySQL課程-                              MySQL課程                                 MySQL課程                 


-- TRIM( [remstr FROM] str)從字符串str兩端刪除不必要的字符
SELECT TRIM('   hello  ')
    ,TRIM('x' FROM 'xxxhelloxxxxxxxxxxx')
輸出  
TRIM('   hello  ')  TRIM('x' FROM 'xxxhelloxxxxxxxxxxx')  
------------------  --------------------------------------
hello               hello

示例
SELECT ROUND(123.456,2),RAND();
輸出第一次：
round(123.456,2)              rand()  
----------------  --------------------
123.46              0.8179619683120045
輸出第二次：
round(123.456,2)               rand()  
----------------  ---------------------
123.46              0.17371040785099418


創(chuàng)建測試表格 test_user
CREATE TABLE test_user(
    sex TINYINT NOT NULL
)
插入數(shù)據(jù)
INSERT test_user VALUES(0),(1),(0),(1),(2);

SELECT sex,
    CASE WHEN sex=1 THEN '男'
         WHEN sex=0 THEN '女'
         ELSE '未知'
    END AS '性別'
FROM test_user;
輸出
   sex  性別  
------  --------
     0  女     
     1  男     
     0  女     
     1  男     
     2  未知