从头开始学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;