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

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

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

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

CHAR_LENGTH    CONCAT    CONCAT_WS    GROUP_CONCAT    FORMAT    INSERT    LOCATE    LCASE    LEFT    REPLACE  REVERSE    SUBSTRINGCEIL    FLOOR    MAX    MIN    COUNT    AVG    ABS    SUMnow()     current_time    current_dateDATE  YEAR  MONTH  DAY  TIMEADDDATE(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 tWHERE 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;

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。