SQL基础介绍-函数使用

MySQL
函数使用

这里介绍下，MySQL中常用的函数，函数有太多太多，不一定都需要记住，只需要有个印象，需要的时候去文档中找一下，记住一些常用的就好了

数学函数

abs(x)

返回x的绝对值

select abs(-10),abs(10)

abs

ceil(x)、ceiling(x)

向上取整,比该值大的第一个整数

select CEIL(9.3),CEIL(9.5),CEIL(9.6),CEIL(-9.5),CEIL(-9.1)

ceil

floor(x)

向下取整，比该值小的第一个整数

select FLOOR(9.3),FLOOR(9.5),FLOOR(9.6),FLOOR(-9.5),FLOOR(-9.1)

floor

round(x),round(x,y)

四舍五入，round(x)，最近的一个整数，round(x,y)，这个y可以指定精度

select ROUND(9.3),ROUND(9.6),ROUND(9.378,2),ROUND(9.455,2)

round

rand(),rand(x)

rand() 返回0~1之间的随机数
rand(x) 返回0~1之间的随机数，如果x值相等，则返回值相等

select RAND(),RAND(),RAND(10),RAND(10)

rand

字符串函数

获取字符串长度

length(str)，返回str的长度,这里要注意下中文，占3个长度,这里的长度单位是bytes

mysql> select length('abc'),length('中国'),length('hi中国');
+---------------+----------------+------------------+
| length('abc') | length('中国') | length('hi中国') |
+---------------+----------------+------------------+
|             3 |              6 |                8 |
+---------------+----------------+------------------+
1 row in set

CHAR_LENGTH(str)，返回str的长度，中文和英文一样，占1个字符，这里长度单位是字符

mysql> select char_length('abc'),char_length('中国'),char_length('hi中国');
+--------------------+---------------------+-----------------------+
| char_length('abc') | char_length('中国') | char_length('hi中国') |
+--------------------+---------------------+-----------------------+
|                  3 |                   2 |                     4 |
+--------------------+---------------------+-----------------------+
1 row in set

字符串拼接

CONCAT(str1,str2,...)，将str1，str2拼接在一起


mysql> select concat('h','e','gogo','中国');
+-------------------------------+
| concat('h','e','gogo','中国') |
+-------------------------------+
| hegogo中国                    |
+-------------------------------+
1 row in set

这里要注意下NULL，如果其中有参数为NULL，则结果为NULL

mysql> select concat('h','e','gogo',NULL,'中国');
+------------------------------------+
| concat('h','e','gogo',NULL,'中国') |
+------------------------------------+
| NULL                               |
+------------------------------------+
1 row in set

CONCAT_WS(separator,str1,str2,...)，使用指定的separator进行拼接

mysql> select concat_ws('^','e','gogo',NULL,'中国');
+---------------------------------------+
| concat_ws('^','e','gogo',NULL,'中国') |
+---------------------------------------+
| e^gogo^中国                           |
+---------------------------------------+
1 row in set

这里是如果有NULL，对结果是没有影响的，会直接忽略NULL值

剔除空格或指定字符

剔除字符串左右的空格
ltrim(str),剔除左侧空格
rtrim(str),剔除右侧空格
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)
trim可以使用参数来控制剔除空格或者是指定的remstr，默认是空格

mysql> select concat(ltrim('   hi   '),'oo'),concat(rtrim('   hi   '),'oo'),concat(trim('   hi   '),'oo');
+--------------------------------+--------------------------------+-------------------------------+
| concat(ltrim('   hi   '),'oo') | concat(rtrim('   hi   '),'oo') | concat(trim('   hi   '),'oo') |
+--------------------------------+--------------------------------+-------------------------------+
| hi   oo                        |    hioo                        | hioo                          |
+--------------------------------+--------------------------------+-------------------------------+
1 row in set

mysql> select trim('a' from 'aabbbccaa'),trim(leading 'a' from 'aabbbccaa'),trim(trailing 'a' from 'aabbbccaa');
+----------------------------+------------------------------------+-------------------------------------+
| trim('a' from 'aabbbccaa') | trim(leading 'a' from 'aabbbccaa') | trim(trailing 'a' from 'aabbbccaa') |
+----------------------------+------------------------------------+-------------------------------------+
| bbbcc                      | bbbccaa                            | aabbbcc                             |
+----------------------------+------------------------------------+-------------------------------------+
1 row in set

字符串填充

LPAD(str,len,padstr)，左侧填充
RPAD(str,len,padstr)，右侧填充
len是指定str的长度，如果不够，则使用padstr填充，如果超了，则进行截取

mysql> select lpad('hi',6,'@'),lpad('higogo',4,'@'),rpad('hi',6,'@'),rpad('higogo',4,'@');
+------------------+----------------------+------------------+----------------------+
| lpad('hi',6,'@') | lpad('higogo',4,'@') | rpad('hi',6,'@') | rpad('higogo',4,'@') |
+------------------+----------------------+------------------+----------------------+
| @@@@hi           | higo                 | hi@@@@           | higo                 |
+------------------+----------------------+------------------+----------------------+
1 row in set

字符串截取

LEFT(str,len)，从左侧开始截取len个字符
RIGHT(str,len)，从右侧截取len个字符
SUBSTR(str,pos), SUBSTR(str FROM pos), SUBSTR(str,pos,len), SUBSTR(str FROM pos FOR len)，从指定pos开始截取len个字符

mysql> select left('hello',1),left('hello',3),right('hello',3);
+-----------------+-----------------+------------------+
| left('hello',1) | left('hello',3) | right('hello',3) |
+-----------------+-----------------+------------------+
| h               | hel             | llo              |
+-----------------+-----------------+------------------+
1 row in set

mysql> select substr('hello',1),substr('hello',2),substr('hello' from 2);
+-------------------+-------------------+------------------------+
| substr('hello',1) | substr('hello',2) | substr('hello' from 2) |
+-------------------+-------------------+------------------------+
| hello             | ello              | ello                   |
+-------------------+-------------------+------------------------+
1 row in set

mysql> select substr('hello',1,3),substr('hello',2,3),substr('hello' from 2 for 2);
+---------------------+---------------------+------------------------------+
| substr('hello',1,3) | substr('hello',2,3) | substr('hello' from 2 for 2) |
+---------------------+---------------------+------------------------------+
| hel                 | ell                 | el                           |
+---------------------+---------------------+------------------------------+
1 row in set

大小写转换

LOWER(str) LCASE(str)，将str转为小写
UPPER(str) UCASE(str)，将str转为大写

mysql> select lower('AppLE'),lcase('AppLE'),upper('AppLE'),ucase('AppLE');
+----------------+----------------+----------------+----------------+
| lower('AppLE') | lcase('AppLE') | upper('AppLE') | ucase('AppLE') |
+----------------+----------------+----------------+----------------+
| apple          | apple          | APPLE          | APPLE          |
+----------------+----------------+----------------+----------------+
1 row in set

日期和函数

获取当前日期、时间

SELECT CURRENT_DATE,CURRENT_DATE(),CURRENT_TIME,CURRENT_TIME(),CURRENT_TIMESTAMP(),NOW()

当前日期、时间

时间戳相关函数

UNIX_TIMESTAMP() 返回当前时间的时间戳，
UNIX_TIMESTAMP(x) 返回指定日期的时间戳
FROM_UNIXTIME(x) 将时间戳转为日期
FROM_UNIXTIME(x,y) 将时间戳转为指定格式的日期

mysql> select UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2017-09-10');
+------------------+------------------------------+
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP('2017-09-10') |
+------------------+------------------------------+
|       1505096065 |                   1504972800 |
+------------------+------------------------------+
1 row in set

mysql> select FROM_UNIXTIME(1505096033),FROM_UNIXTIME(1504972800),FROM_UNIXTIME(1505096033,'%Y-%m-%d');
+---------------------------+---------------------------+--------------------------------------+
| FROM_UNIXTIME(1505096033) | FROM_UNIXTIME(1504972800) | FROM_UNIXTIME(1505096033,'%Y-%m-%d') |
+---------------------------+---------------------------+--------------------------------------+
| 2017-09-11 10:13:53       | 2017-09-10 00:00:00       | 2017-09-11                           |
+---------------------------+---------------------------+--------------------------------------+
1 row in set

mysql>

extract

EXTRACT(unit FROM date)
返回日期/时间的单独部分，比如年、月、日、小时、分钟等等
date 参数是合法的日期表达式。unit 参数可以是下列的值：

extract

mysql> select extract(YEAR FROM NOW()),extract(MONTH from now()),extract(HOUR from now());
+--------------------------+---------------------------+--------------------------+
| extract(YEAR FROM NOW()) | extract(MONTH from now()) | extract(HOUR from now()) |
+--------------------------+---------------------------+--------------------------+
|                     2017 |                         9 |                       10 |
+--------------------------+---------------------------+--------------------------+
1 row in set

mysql>

datediff、timediff、timestampdiff

datediff(expr1,expr2),获取2个日期相差的天数

mysql> select datediff('2017-09-11 10:00:00','2017-09-08 00:00:00');
+-------------------------------------------------------+
| datediff('2017-09-11 10:00:00','2017-09-08 00:00:00') |
+-------------------------------------------------------+
|                                                     3 |
+-------------------------------------------------------+
1 row in set

mysql>

TIMEDIFF(expr1,expr2)，返回expr1-expr2的时间差

mysql> select timediff('2017-09-11 10:00:00','2017-09-08 00:00:00');
+-------------------------------------------------------+
| timediff('2017-09-11 10:00:00','2017-09-08 00:00:00') |
+-------------------------------------------------------+
| 82:00:00                                              |
+-------------------------------------------------------+
1 row in set

mysql>

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
返回指定unit的datetime_expr2 − datetime_expr1时间差
unit可以是MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

mysql> select timestampdiff(DAY,'2017-09-11 10:00:00','2017-09-08 00:00:00');
+----------------------------------------------------------------+
| timestampdiff(DAY,'2017-09-11 10:00:00','2017-09-08 00:00:00') |
+----------------------------------------------------------------+
|                                                             -3 |
+----------------------------------------------------------------+
1 row in set

mysql> select timestampdiff(HOUR,'2017-09-11 10:00:00','2017-09-08 00:00:00');
+-----------------------------------------------------------------+
| timestampdiff(HOUR,'2017-09-11 10:00:00','2017-09-08 00:00:00') |
+-----------------------------------------------------------------+
|                                                             -82 |
+-----------------------------------------------------------------+
1 row in set

时间加减函数

对日期进行加减操作，有很多方法可以使用，最简单的是直接使用interval

mysql> select now(),now() + interval 3 DAY,now()+interval 1 Hour;
+---------------------+------------------------+-----------------------+
| now()               | now() + interval 3 DAY | now()+interval 1 Hour |
+---------------------+------------------------+-----------------------+
| 2017-09-11 10:57:18 | 2017-09-14 10:57:18    | 2017-09-11 11:57:18   |
+---------------------+------------------------+-----------------------+
1 row in set

当然也可使用提供的函数

ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)
DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)

日期加减

mysql> select now(),date_add(now(),interval 1 Day),adddate(now(),interval 3 Hour);
+---------------------+--------------------------------+--------------------------------+
| now()               | date_add(now(),interval 1 Day) | adddate(now(),interval 3 Hour) |
+---------------------+--------------------------------+--------------------------------+
| 2017-09-11 10:59:09 | 2017-09-12 10:59:09            | 2017-09-11 13:59:09            |
+---------------------+--------------------------------+--------------------------------+
1 row in set

条件判断函数

if

if(expr,v1,v2)
如果expr为真，则返回v1，为假，则返回v2

mysql> select if(1>0,'ok','no'),if(1=0,'ok','no');
+-------------------+-------------------+
| if(1>0,'ok','no') | if(1=0,'ok','no') |
+-------------------+-------------------+
| ok                | no                |
+-------------------+-------------------+
1 row in set

mysql>

ifnull

ifnull(v1,v2)
如果v1的值为null，则返回v2，如果v1不为null，则返回v1

mysql> select ifnull(99,20),ifnull(NULL,99);
+---------------+-----------------+
| ifnull(99,20) | ifnull(NULL,99) |
+---------------+-----------------+
|            99 |              99 |
+---------------+-----------------+
1 row in set

mysql>

case when

这里可以根据多个条件来判断，在不同的情况下，返回不同的值

select 
    s_id,s_name,s_gender,
    case 
        when s_gender=0 then '男' 
        when s_gender=1 then '女' 
    end gender,
    s_birthday,
    s_hobby,
    c_id
from 
    t_student
;

case when

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 217,277评论 6赞 503
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 92,689评论 3赞 393
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 163,624评论 0赞 353
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 58,356评论 1赞 293
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 67,402评论 6赞 392
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,292评论 1赞 301
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,135评论 3赞 418
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,992评论 0赞 275
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,429评论 1赞 314
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,636评论 3赞 334
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,785评论 1赞 348
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,492评论 5赞 345
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 41,092评论 3赞 328
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,723评论 0赞 22
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,858评论 1赞 269
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,891评论 2赞 370
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,713评论 2赞 354

SQL基础介绍-函数使用

SQL基础介绍-函数使用

数学函数

abs(x)

ceil(x)、ceiling(x)

floor(x)

round(x),round(x,y)

rand(),rand(x)

字符串函数

获取字符串长度

字符串拼接

剔除空格或指定字符

字符串填充

字符串截取

大小写转换

更多字符串函数

日期和函数

获取当前日期、时间

时间戳相关函数

extract

datediff、timediff、timestampdiff

时间加减函数

更多日期、时间函数

条件判断函数

if

ifnull

case when

推荐阅读更多精彩内容