9)mysql常见函数

常用聚合函数

COUNT(*)/COUNT(col)：计算符合条件的数据行数
SUM(col_name)：      计算符合条件的数值列合计值
AVG(col_name)：      计算符合条件的数值列平均值
MAX(col_name)：      计算符合条件的任意列中数据的最大值
MIN(col_name)：      计算符合条件的任意列中数据的最小值

常用时间函数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)：    把秒数转换为(小时：分：秒)
TIME_TO_SEC(time)：       把时间（小时：分：秒）转换为秒数

示例
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

更多时间函数如下

DATEDIFF函数示例 DATE_ADD函数示例 EXTRACT函数示例

常用字符串函数

示例

创建test表格
CREATE TABLE test(
    title VARCHAR(20) NOT NULL,
    class_name VARCHAR(10) NOT NULL
)
插入数据
title                   class_name       
----------------------  -----------------
MySQL课程-79889           大数据        
MySQL课程-69546           Hbase            
PostgreSQL课程-43917      大数据        
MySQL课程-88343           MySQL            

SELECT *,CONCAT(title,class_name)
FROM test;
输出
title                   class_name       concat(title,class_name)               
----------------------  ---------------  ---------------------------------------
MySQL课程-79889           大数据              MySQL课程-79889大数据             
MySQL课程-69546           Hbase            MySQL课程-69546Hbase                 
PostgreSQL课程-43917      大数据              PostgreSQL课程-43917大数据        
MySQL课程-88343           MySQL            MySQL课程-88343MySQL  


SELECT *,CONCAT_WS('||',title,class_name)
FROM test;
输出
title                   class_name       CONCAT_ws('||',title,class_name)         
----------------------  ---------------  -----------------------------------------
MySQL课程-79889           大数据              MySQL课程-79889||大数据             
MySQL课程-69546           Hbase            MySQL课程-69546||Hbase                 
PostgreSQL课程-43917      大数据              PostgreSQL课程-43917||大数据        
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)  
---------------  -----------------------  --------------------
大数据                                 3                     9
Hbase                                 5                     5
大数据                                 3                     9
MySQL                                 5                     5


格式化为 ##.###.###，保留小数点后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


创建测试表格 test_user
CREATE TABLE test_user(
    sex TINYINT NOT NULL
)
插入数据
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  未知