从头开始学MySQL-------函数深入探究

        前言：虽然常见的Mysql函数，在平时的开发里面已经用的很多。但是还是会多多少少遇到一些不会的，或者好想会了，也好想不会的函数。

        经常用函数，总会有书到用时方恨少的感觉。比如给我一个需求，用户传递一个时间，查询这个时间前后八小时内的订单。呃呃呃，(┬＿┬)，好像会写，好像也写不出来。。

        因此，最近要恶补一下Mysql的函数。需要掌握的函数

CHAR_LENGTH    CONCAT    CONCAT_WS    GROUP_CONCAT    FORMAT    INSERT    LOCATE    LCASE    LEFT    REPLACE  REVERSE    SUBSTRING

CEIL    FLOOR    MAX    MIN    COUNT    AVG    ABS    SUM

now()     current_time    current_date

DATE  YEAR  MONTH  DAY  TIME

ADDDATE(now() , INTERVAL 1 Hour)

SUBDATE(now() , INTERVAL 2 Day)

DATE_FORMAT(now() ， '%Y-%m-%d-%r')

DATE_FORMAT(now(),    '%Y-%m-%d %H:%i:%s') '年月日 时分秒'

IF    ISNULL   IFNULL  CASE

        千里之行，始于足下。让我们开始吧。

一、字符串函数

（1）返回字符串长度。从结果来看，空格也算长度。如果字符串是null，则返回null。

SELECT CHAR_LENGTH('xiao da yu') AS length;

    

（2）连接字符串。如果有任意字符串为null，那么返回结果为null。

SELECT CONCAT('xiao ','da ','yu') AS myName;

    

（3）使用分隔符连接字符串。如果有任意字符串为null，则忽略掉这个null。

SELECT CONCAT_WS("-", 'xiao', NULL, 'da', 'yu') AS myName;

    

（4）国际化金额函数。把数字或者数字字符串，转为国际化的格式，并且还能保留小数位。

         我一口老血吐出来，我曾经用Java强行实现过这个功能。早知道这个，就不那么头疼了。

SELECT FORMAT("123456.789",2);    
SELECT FORMAT(123456.789,2); 

            

（5）字符串替换。把给定字符串从【start，end】替换为给定字符串。

         比如下面的例子中，从1到4，就是"XIAO"替换为"xiao"。

SELECT INSERT("XIAO da yu",1,4,"xiao") AS myName

    

（6）字符串定位。某个字符串首次出现的位置。有null运算返回null。

SELECT LOCATE("da",'xiao da yu') AS startIndex;

        INSERT函数与LOCATE函数，类似于Java中的String的 subString函数与 indexOf函数。

        好，让我们来玩一下游戏。

        有个字符串叫："从头开始学mysql"，需求是这个字符串改为"从头开始学MSQL"。

        首先要定位到"mysql"的开始位置，然后计算出"mysql"的长度。最后用"MySQL"替换"mysql"。

SELECT INSERT ('从头开始学mysql',LOCATE('mysql','从头开始学mysql'),CHAR_LENGTH('mysql'),'MySQL')
AS blogName

    

（7）大小写。

SELECT LCASE('MYSQL') AS '小写' , UCASE('mysql') AS '大写'

  

（8）返回前X位，后X位。

SELECT LEFT('mysql',2) AS '前两位' , RIGHT('mysql',2) AS '后两位'

    

（9）去掉首位空格。LTRIM去掉左边空格，RTRIM去掉右边空格，TRIM去掉两边空格。

SELECT 
LTRIM('   mysql'),RTRIM('mysql    '), TRIM('  mysql  ')

（10）替换

SELECT REPLACE('Mysql','sql','SQL') AS result

    

（11）反转

SELECT REVERSE('LQSyM') AS result

  

（12）截取

SELECT SUBSTRING("MySQL", 1, 3) AS result

（13）连接分组下所有属性值

        有如下一张数据库表，现在根据classId分组。

SELECT classId, GROUP_CONCAT(name) FROM t_student 
GROUP BY classId

  

二、数字函数

         为了便于操作，先执行以下SQL。创建一张t_grade表，并初始化一些数据。

DROP TABLE IF EXISTS t_grade;

CREATE TABLE t_grade
(
    id BIGINT(11) PRIMARY KEY AUTO_INCREMENT,
    grade decimal(5,2) NOT NULL 
);

INSERT INTO t_grade(grade) VALUES(88.5),(-71.5),(-96),(58.5),(80);

（1）获取大于它的最小整数，获取小于它的最大整数

SELECT 
grade,
CEIL(grade)  AS '大于它的最小整数',
FLOOR(grade) AS '小于它的最大整数'
FROM t_grade

（2）获取绝对值。

SELECT 
id,
grade,
ABS(grade) AS '绝对值'
FROM t_grade

    

（3）获取最大值、最小值、平均值

SELECT max(grade) , min(grade) ,avg(grade) FROM t_grade

   

（4）个数，总和

SELECT sum(grade),count(grade) FROM t_grade

    

三、时间函数

（1）当前时间。 now()        current_time      current_date  

SELECT 
now() AS '当前时间',
current_date  AS '年月日',
current_time  AS '时分秒'

（2）提取日期时间表达式中

SELECT 
now(),
DATE(now()),
YEAR(now()),
MONTH(now()),
DAY(now()),
TIME(now())

（3）以当前时间为基准，增加时间，减少时间

SELECT 
now(),
ADDDATE(now(), INTERVAL 1 second) '1秒后',
ADDDATE(now(), INTERVAL 1 minute) '1分后',
ADDDATE(now(), INTERVAL 1 hour)   '1小时后',
ADDDATE(now(), INTERVAL 1 day)    '1天后',

SUBDATE(now(), INTERVAL 2 day)    '2天前'

自测小练习

        基于上述，来弄一个基本需求。获取前三个小时到当前时间的所有记录。

DROP TABLE IF EXISTS T_TIME_TEST;

CREATE TABLE T_TIME_TEST
(
    TIME_STR varchar(20) NOT NULL 
);

INSERT INTO T_TIME_TEST
 VALUES('2019-08-29 10:00'),('2019-08-29 11:00'),('2019-08-29 12:00'),('2019-08-29 13:00'),('2019-08-29 14:00');

        当前时间大约是15：52，往前推算3小时，是12：52。3个小时内的预期结果是 13：00、14：00的两条记录。

SELECT  t.* , now() AS '当前时间' FROM T_TIME_TEST t
WHERE t.TIME_STR BETWEEN SUBDATE(now(), INTERVAL 3 HOUR) AND now()

（4）时间格式化

SELECT 
DATE_FORMAT(now(),'%Y-%m-%d-%r') AS '全',
DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s') '年月日 时分秒'

四、条件函数

（1）最想用的三目运算函数IF

SELECT IF(1>0,'对的哦','错的哦'),IF(1<0,'对的哦','错的哦')

（2）ISNULL

SELECT ISNULL(NULL),ISNULL(1)

（3）IFNULL

SELECT IFNULL(NULL,4),IFNULL(5,4);

（4）CASE   WHEN

     SELECT 
        CASE 1>0
			WHEN 1 THEN '对的'
			WHEN 0 THEN '错的'
			ELSE        '算个锤子'
        END AS result;