爱生活,乐分享,大家好才是真的好!
返回顶部

mysql函数大全

mysql函数大全

字符串函数

对于针对字符串位置的操作,第一个位置被标记为1。

ASCII(str) 与ORD(str)

ASCII():返回字符串str的最左面字符的ASCII代码值。如果str是空字符串,返回0。如果str是NULL,返回NULL。

ORD():返回字符串第一个字符的ASCII 值。如果最左面的字符不是一个多字节字符。返回与ASCII()函数返回的相同值。如果字符串str最左面字符是一个多字节字符,通过以格式((first byte ASCII code)256+(second byte ASCII code))[256+third byte ASCII code...]返回字符的ASCII代码值来返回多字节字符代码。

 
 
 
 
 
 
 
 
select ascii('0'),ascii('1'),ASCII('A'),ASCII('Z'),ASCII('a'),ASCII('z'),ASCII(NULL),ASCII('ab');
select ORD('0'),ORD('1'),ORD('A'),ORD('Z'),ORD('a'),ORD('z'),ORD(NULL),ORD('ab');  -- 对单字符,是一样的
 

 

字符串拼接函数

CONCAT(str1,str2,...)

返回来自于参数连接的字符串。

注意:1,如果任何参数是NULL,返回NULL。

2,可以有1到多个的参数。

3,一个数字参数被变换为等价的字符串形式。

 
 
 
xxxxxxxxxx
 
 
 
 
select CONCAT('My', 'S', 'QL'),CONCAT('My', NULL, 'QL'); -- MySQLnull
select CONCAT(14.3),CONCAT(14.3,'a');   -- 14.3  14.3a
 
 
 
 
xxxxxxxxxx
 
 
 
 
补充用法:mysql向表中某字段后追加一段字符串:
update table_name set field=CONCAT(field,'',str);
 

 

CONCAT_WS(separator,str1,str2,...)

CONCAT_WS() 代表 CONCAT With Separator ,是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。即以指定连接符连接字符串。 注意:

1,分隔符可以是一个字符串,也可以是其它参数。,

2,如果分隔符为 NULL,则结果为 NULL。

3,函数会忽略任何分隔符参数后的 NULL 值。

 
 
 
xxxxxxxxxx
 
 
 
 
select concat_ws('--','我','love',1314);--     我--love--1314
select CONCAT_WS('我','love',null,'love','my dog');  -- love我love我my dog
select CONCAT_WS(null,'love','me','love','my dog'); -- null
 

group_concat 函数

完整的语法如下: group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

与 group by 联合使用,把分组后的组内数据按一定方式连接起来。

 
 
 
xxxxxxxxxx
 
 
 
 
select * from student;
 

 
 
 
 
 
 
 
 
select s.stu_id,GROUP_CONCAT(s.class_name),GROUP_CONCAT(s.main_class_flag)from student s group by s.stu_id;
select s.stu_id,GROUP_CONCAT(s.class_name order by s.class_id desc Separator '-*-'),GROUP_CONCAT(DISTINCT s.main_class_flag)
from student s group by s.stu_id;
 

repeat()函数

 
 
 
 
 
 
 
 
repeat(str,count)用来复制字符串,如下'ab'表示要复制的字符串,2表示复制的份数
select REPEAT('ab',2);-- abab
 

字符串的长度函数

LENGTH(str) ,返回字符串的字节数,同OCTET_LENGTH(str) ;

CHAR_LENGTH(str) ,返回字符串的字符数量,同 CHARACTER_LENGTH(str) .

bit_length:返回字符串的二进制位(bit)。

 
 
 
x
 
 
 
 
SELECT length('aaa'),OCTET_LENGTH('aaa');           # 3
SELECT length('ɠɠɠ'),octet_length('ɠɠɠ');           # 6
SELECT length('我我我'),octet_length('我我我');         # 9
SELECT char_length('aaa'),CHARACTER_LENGTH('aaa');      # 3
SELECT char_length('ɠɠɠ'),character_length('ɠɠɠ');      # 3
SELECT char_length('我我我'),character_length('我我我');    # 3
SELECT bit_length('a'),bit_length(1),bit_length('ɠ'),bit_length('我');    # 8 8 16  24
 

 

字符串的位置函数

LOCATE(substr,str)、POSITION(substrIN str)、INSTR(str,substr)函数

三个函数作用相同,返回子字符串substr在字符串str中的开始位置(从第几个字符开始)

注意:1,如果substr不在str中,则返回0。

2,,如果substr或str为NULL,则返回NULL。

3,loction() 与instr() 参数位置相反;

4,LOCATE(substr,str,pos) 返回字符串substr中第一个出现子 字符串的 str位置,从位置开始 pos。

 
 
 
xxxxxxxxxx
 
 
 
 
SELECT position('a' IN 'banana');   # 2
SELECT locate('a', 'banana');       # 2
SELECT locate('a', 'banana', 3);    # 4
SELECT locate('x', 'banana');       # 0
SELECT locate(1, 'banana');         # 0
SELECT locate(NULL , 'banana');     # null
SELECT locate('a' , NULL );         # null
SELECT instr('banana', 'a');        # 2
SELECT instr('banana', 'e');        # 0
 

FIELD(s,s1,s2,...)函数

返回第一个与字符串s匹配的字符串的位置。

 
 
 
xxxxxxxxxx
 
 
 
 
SELECT FIELD('love','love me','lover','my_lover','love');  -- 输出结果:4
 

自定义排序,

 
 
 
xxxxxxxxxx
 
 
 
 
select * from v_education order by field(schoolRecord,'小学','初中','高中','专科','本科','硕士','博士'); -- 按照学历的从低到高排序
 

FIND_IN_SET(str,strlist)

返回待查字符串str在字符串列表strlist中的位置。

注意:1,正常返回1到N之间,如果str不在strlist 或strlist 为空字符串,则返回值为 0 。

2,如任意一个参数为NULL,则返回值为 NULL。

3,strlist必须是逗号分隔的字符串列表,可有group_concat()默认生成。

 
 
 
xxxxxxxxxx
 
 
 
 
SELECT FIND_IN_SET('b','a,b,c,d');-- 2
select FIND_IN_SET('2019','1,2,3,2019,5,4090');-- 4
select FIND_IN_SET(2019,'1,2,3,2019,5,4090');-- 4
select FIND_IN_SET('11','1,2,3,2019,5,4090');-- 0
select FIND_IN_SET(null,'1,2,3,2019,5,4090');-- null
select FIND_IN_SET('1',null);-- null
 

 

字符串的截取函数

截取特定长度的字符串

SUBSTR(str,pos,len),SUBSTRING(str,pos,len)、MID(str,pos,len)函数

两个函数作用相同,从字符串str中返回一个第pos个字符开始、长度为len的字符串。

注意:1,len 参数可以省略,即默认截取到字符串末尾;

2,SUBSTR(str,pos,len)等同 SUBSTR(str FROM pos FOR len),三个都一样;

3,pos可以为负数,最后一位为-1,以此类推。

 
 
 
x
 
 
 
 
select SUBSTR('您好,欢迎访问pan_junbiao的博客',8,3);     -- 输出结果:pan
SELECT SUBSTRING('您好,欢迎访问pan_junbiao的博客',8,3);  -- 输出结果:pan
SELECT MID('您好,欢迎访问pan_junbiao的博客',8,3);        -- 输出结果:pan
select substr('abcde',3),SUBSTRING('abcde'from 3),MID('abcde',3),MID('abcde'from 3);-- 都返回 cde
select substr('abcde',-2),SUBSTRING('abcde'from -3 for 1);-- de  c
select MID('abcde',-2),MID('abcde'from -3 for 1);    -- de  c
 

从首尾开始截取字符串

LEFT(str,len)、RIGHT(str,len)函数

前者返回字符串str从最左边开始的len个字符,后者返回字符串str从最右边开始的len个字符。

 
 
 
xxxxxxxxxx
 
 
 
 
SELECT LEFT('您好,欢迎访问pan_junbiao的博客',7);   -- 输出结果:您好,欢迎访问
SELECT RIGHT('您好,欢迎访问pan_junbiao的博客',14); -- 输出结果:pan_junbiao的博客
 

关键字进行截取字符串

substring_index(str, delim, count),即:substring_index(被截取字符串,关键字,关键字出现的次数)

 
 
 
xxxxxxxxxx
 
 
 
 
--  1.截取第二个“.”之前的所有字符
 SELECT SUBSTRING_INDEX('www.baidu.com', '.', 2);-- www.baidu
--  2.截取倒数第二个“.”之后的所有字符
 SELECT SUBSTRING_INDEX('www.baidu.com', '.', -2);-- baidu.com
--  3.如果关键字不存在,则返回整个字符串
 SELECT SUBSTRING_INDEX('www.baidu.com', 'sprite', 1);-- www.baidu.com
 

 

字符串的替换函数

**INSERT(str,pos,len,newstr)

返回一个替换后的字符串,为字符串str从pos开始len个字符,被替换newstr。

**REPLACE(str,from_str,to_str)

返回一个替换后的字符串,把字符串str所有的from_str替代to_str;  

 
 
 
xxxxxxxxxx
 
 
 
 
SELECT insert('abcdefg',2,3,'#'); -- a#efg
SELECT replace('01234567890123','123','#');-- 0#4567890#
 

 

删除空格

LTRIM(str)

删除字符串str的前置空格字符。

 
 
 
xxxxxxxxxx
 
 
 
 
select ltrim(' as  b '),ltrim('as  b ');#'as b '#(注意b后有空格)
 

RTRIM(str)

删除字符串str的后置空格字符。

 
 
 
xxxxxxxxxx
 
 
 
 
select rtrim(' as  b '),ltrim('as  b ');#' as b'#(注意as前有空格) 
 

 

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

以上为完整格式,简化格式:TRIM([remstr FROM] str)

返回处理后的字符串str,其所有remstr前缀或后缀被删除了。如果没有修饰符BOTH、LEADING或TRAILING给出,BOTH被假定。如果remstr没被指定,空格被删除。

注意:1,函数本身没有逗号,from关键字前后为参数;

2,{BOTH | LEADING | TRAILING},分别表示前后都处理、头部、尾部处理。任选一个,默认为both。

3, [remstr],为要删除的字符,默认为空格。

 
 
 
x
 
 
 
 
 -- 默认删除前后空格 
 SELECT TRIM(' bar '); -- 'bar'
-- 删除指定首字符 如','  (','为中文逗号)
 SELECT TRIM(LEADING ',' FROM ',,bar,,'); -- 'bar,,' 
  -- 删除指定尾字符 如',' 
 SELECT TRIM(TRAILING ',' FROM ',,bar,,'); -- ',,bar'
 -- 删除指定首尾字符 
 SELECT TRIM(BOTH 'xyz' FROM 'xyzbarxyzxyz'); -- 'bar' 
 

注意:trim/ltrim/rtrim,处理的空格为" ",\t、\n、enter键,需要特殊处理。

trim是mysql内置函数,可以去除字段首尾空格 replace 内置函数,去除指定字符 char(9):水平制表符 (tab键 或者 \t) char(10):换行键 (\n) char(13):回车键 (Enter键)

 
 
 
xxxxxxxxxx
 
 
 
 
select replace(replace(replace('\tabc\ncde
acdv\n',char(9),''),char(10),''),char(13),'');-- abccdeacdv
 

 

mysql 时间函数用法集合

日期(date)相关

CURDATE()、CURRENT_DATE()、CURRENT_DATE

同义,返回当前日期。

 
 
 
xxxxxxxxxx
 
 
 
 
select CURRENT_DATE(),CURDATE(),CURRENT_DATE;-- 都返回 2020-01-09
 

DATE(date|datetime):

提取date或datetime 的日期部分。

 
 
 
xxxxxxxxxx
 
 
 
 
select date('2020-01-05'),date('20200105'),date('2020/01/05'),date('200105'),date('2020-01-05 12:34:56'); -- 都返回 2020-01-05
 

 

DATE_ADD(date,INTERVAL exp unit),DATE_SUB(date,INTERVAL exp unit):

在日期(也可以包含时间部分)加|减"时间",

注意:1,INTERVAL为关键字,unit 为说明符,exp为表达式,可以为正整数、负整数、字符串。

2,INTERVAL exp unit,为一个整体,不需要逗号,可以作为参数,也可以作为表达式加减;

 
 
 
xxxxxxxxxx
 
 
 
 
select date_add('2017-11-13 20:20:20',interval 1 day);     -- 2017-11-14 20:20:20
select date_add('2017-11-13 20:20:20',interval '1' day);    -- 2017-11-14 20:20:20
select date_add('2017-11-13',interval '1:2' minute_second);-- 2017-11-13 00:01:02
select date_add('2017-11-13 20:58:58',interval '1:2' minute_second);-- 2017-11-13 21:00:00
select '2017-11-13 20:20:20' + interval '1:2' minute_second; -- 2017-11-13 20:21:22
select date_add('2017-11-13 20:20:20',interval -1 day);   -- 2017-11-12 20:20:20
select date_sub('2017-11-13 20:20:20',interval 1 day);    -- 2017-11-12 20:20:20
select date_sub('2017-11-13 20:58:58',interval '1:2' day_hour);-- 2017-11-12 18:58:58
select date_sub('2017-11-13 20:58:58',interval '1 2' day_hour);-- 2017-11-12 18:58:58
 

ADDDATE()和SUBDATE()

形式一:ADDDATE(date,INTERVAL expr unit),SUBDATE(date,INTERVAL expr unit)同date_add()、date_sub()。

形式二:ADDDATE(expr,days),SUBDATE(expr,days),只对天操作,相当于形式一中 unit=day。

 
 
 
xxxxxxxxxx
 
 
 
 
select ADDDATE('2020-1-8',3),ADDDATE('2020-1-8','3'),SUBDATE('2020/1/8',-3);-- 都是'2020-01-11'
 

DATEDIFF(date1,date2):

两个日期相减,date1 与date2 都可以是单独的日期 或日期与时间,但只有日期部分参与运算。(date1 -date2 天数差)

注意:DATEDIFF(date1,date2),只算date1 -date2 天数差,忽略time时间部分。

 
 
 
xxxxxxxxxx
 
 
 
 
select DATEDIFF('2020-1-8','2020-1-7 13:59:59');-- 1
select DATEDIFF('2020/1/6','2020-1-7 13:59:59');-- -1
 select DATEDIFF('2020-01-10 00:00:00','2020-01-09 23:59:59');-- 1 忽略time时间部分
 

last_day() 函数:

返回月份中的最后一天。 参数可为date或datetime。

 
 
 
xxxxxxxxxx
 
 
 
 
select now(),LAST_DAY(now()),day(LAST_DAY(now()));-- 2020-01-10 16:16:322020-01-3131
select LAST_DAY('1999-02-01'),LAST_DAY('2000-02-01 12:34:56');-- 1999-02-282000-02-29
select DATE_ADD('1999-03-01',INTERVAL -1 day),DATE_ADD('2000-03-01 12:34:56',INTERVAL -1 day);-- 1999-02-282000-02-29 12:34:56
 

 

时间(time)相关

返回当前时间

CURTIME()、CURRENT_TIME()、CURRENT_TIME:

同义,返回当前时间,如:21:35:20。返回值以当前时区表达。

 
 
 
xxxxxxxxxx
 
 
 
 
select CURRENT_TIME,CURRENT_TIME(),CURTIME();-- 都是 18:08:51
 

subtime(time1,time2)

返回time1与time2时间差。

注意:1,subtime() 基本只计算time 时间的差值。(适用范围小,基本不使用)

2,返回值为时间,可用TIME_TO_SEC() 转化成秒数

 
 
 
xxxxxxxxxx
 
 
 
 
select SUBTIME('12:34:56','11:33:50'); -- 01:01:06
select SUBTIME('2020-1-8 12:34:56','2020-1-7 11:33:50'),
SUBTIME('2020-1-8 12:34:56','11:33:50'),
SUBTIME('12:34:56','2020-1-7 11:33:50');
-- null 2020-01-08 01:01:06  null 
select TIME_TO_SEC(SUBTIME('12:34:56','11:33:50')); -- 3666 01:01:06的秒数
 

TIMEDIFF(datetime1,datetime2)

返回两个TIME或DATETIME值之间的时间差值。

注意:1,与datediff() 不同,datediff() 返回天数差值,timediff()返回相差的时间,用time_to_sec()化成秒数。

2, TIMEDIFF函数接受两个必须为相同类型的参数,即TIME或DATETIME,否则返回null.

3, 因为TIMEDIFF函数返回TIME值,所以其结果被限制在从-838:59:59到838:59:59的TIME值范围内,否则MySQL会截断结果。。(即小于天数 839/24=34.958天)

4, 较好解决时间差,可以考虑datediff()与TIMEDIFF()或subtime()合用。完美解决可用timestampdiff().

 
 
 
xxxxxxxxxx
 
 
 
 
select TIMEDIFF('12:34:56','11:33:50'),
   TIMEDIFF('2020-1-8 12:34:56','2020-1-7 11:33:50'),
       TIMEDIFF('2020-1-8 12:34:56','11:33:50'),
       TIMEDIFF('12:34:56','2020-1-7 11:33:50');
       -- 01:01:06  25:01:06  null  null
select TIMEDIFF('2020-1-8 12:34:56','2019-12-7 11:33:50'),
  TIMEDIFF('2020-1-8 12:34:56','2019-11-7 11:33:50'),
  TIMEDIFF('2020-1-8 12:34:56','2019-10-7 11:33:50');
    -- 769:01:06838:59:59838:59:59
 

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

按指定时间维度unit来计算两个DATE或DATETIME值之间的差值。

注意:1, 特殊的地方,返回datetime_expr2-datetime_expr1的指定时间维度的数值;

2,TIMESTAMPDIFF()计算两个时间戳差值,再转化成时间,最后按时间分量单位unit取出。简记为会往下关注一个时间维度。datediff() 只关注date部分。

3, 缺少time ,补充为“00:00:00”的DATETIME值,需要date,却没有提供,无法计算时 结果为null.

 
 
 
xxxxxxxxxx
 
 
 
 
select 
TIMESTAMPDIFF(year,'2020-01-10 00:00:00','2010-01-09 23:59:59') y --  -10
,TIMESTAMPDIFF(year,'2020-01-10 00:00:00','2010-01-09 ') y --  -10
,TIMESTAMPDIFF(year,'2020-01-10','2010-01-09 23:59:59') y --  -10
,TIMESTAMPDIFF(year,'2020-01-10','2010-01-09') y --    -10
,TIMESTAMPDIFF(year,'2010-01-09 23:59:59','2020-01-10 00:00:00') y1 -- 10
,TIMESTAMPDIFF(month,'2010-01-09 23:59:59','2020-01-10 00:00:00') m  -- 120 
,TIMESTAMPDIFF(month,'2010-01-11 23:59:59','2020-01-10 00:00:00') m1  -- 119  相差10年少(2天少1秒)10年-2天+1秒
,TIMESTAMPDIFF(day,'2020-01-09 00:00:00','2020-01-10 00:00:00') d -- 1 =1day一天整
,TIMESTAMPDIFF(day,'2020-01-09 00:00:00','2020-01-10 00:00:01') d1-- 1 >1day  1天多1s 所以为1天
,TIMESTAMPDIFF(day,'2020-01-09 00:00:01','2020-01-10 00:00:00') d2-- 0  <1day 1天少1s 所以为0天
,datediff('2020-01-09 00:00:01','2020-01-10 00:00:00') df-- -1  关注date 前者减后者
,datediff('2020-01-10 00:00:00','2020-01-09 00:00:01') df1-- 1  
,TIMESTAMPDIFF(second,'2020-01-09 00:00:01','2020-01-10 00:00:00') s-- 86399 
,(unix_timestamp('2020-01-10 00:00:00')-unix_timestamp('2020-01-09 00:00:01'))s0-- 86399
,(TIME_TO_SEC('2020-01-10 00:00:00')-TIME_TO_SEC('2020-01-09 00:00:01')) s1 -- -1 关注time
,(unix_timestamp('00:00:05')-unix_timestamp('1970-01-01 08:00:01')) s2-- -1 =0-1
,TIMESTAMPDIFF(second,'1970-01-01 80:00:01','00:00:05') s3-- null 需要date 无法计算
 

 

日期和时间

返回当前日期和时间

NOW()、CURRENT_TIMESTAMP()、CURRENT_TIMESTAMP,LOCALTIME()、LOCALTIME、LOCALTIMESTAMP()、LOCALTIMESTAMP:

同义,都返回当前日期和时间。

SYSDATE():

系统时间,真正的系统时间,不受mysql 的SLEEP()等函数的影响。

 
 
 
xxxxxxxxxx
 
 
 
 
select now(),sleep(2),now();        -- 2020-01-09 18:13:1402020-01-09 18:13:14
select sysdate(),sleep(2),SYSDATE();-- 2020-01-09 18:13:1602020-01-09 18:13:18
 

 

日期与时间比较大小

注意:1,两个日期/时间可以比较大小,但不能直接相减,可用DATEDIFF返回的天数差与subtime(time1,time2)返回的时间差和0比大小。

2,日期比较函数、时间比较函数,运算时都只是相应的部门加入运算,未提供时默认为0。

 
 
 
xxxxxxxxxx
 
 
 
 
select '2020-1-10'>'2020-1-8','2020-01-10'>'2020-01-08',DATEDIFF('2020-01-10','2020-01-08 12:12:12'),DATEDIFF('2020-01-10','2020-1-8 12:12:12')>0,'2020-01-10'-'2020-01-08';-- 01210  时间相减是错误的,时间比较(<、>)要写完全的格式(如:2020-01-08)
select CURRENT_DATE,'2020-1-10'>CURRENT_DATE,DATEDIFF('2020-1-10',CURRENT_DATE()),'2020-1-10'- CURRENT_DATE;-- 2020-01-09 11-20198089
 
 
 
 
xxxxxxxxxx
 
 
 
 
select now(),now()>'18:50:00',SUBTIME(now(),'18:50:00'),SUBTIME(now(),'18:50:00')>0,now()-'18:50:00';
-- 2020-01-09 18:57:0112020-01-09 00:07:01120200109185683
select '12:24:56'-'18:50:00','12:24:56'-'12:24:55','12:24:56'-'12:23"20';-- -600 貌似之比较了小时数,所以不准
 

DATE_FORMAT(date,format):

用format 格式化date,format 为格式化字符串,常用的部分格式化标识符为:

常用年月日时分秒格式为 “%Y-%m-%d %H:%i:%s”

 
 
 
xxxxxxxxxx
 
 
 
 
select now(),DATE_FORMAT(now(),'%Y/%m/%d %H:%i:%s'),DATE_FORMAT(now(),'%Y%m%d%H%i%s');
-- 2020-01-09 19:16:102020/01/09 19:16:10        20200109191610
 

 

显示:2017/11/14

具体时间差

单纯的得出大小,用 > 或 < 运算即可,要得出具体相差的时间是多少,似乎没有一个函数可直接做到,则需要分别计算日期与时间差,最后换算为相应时间单位后相加得到最终结果。

需用到的函数:

TIME_TO_SEC(datetime):将时间部分转化为秒数。

UNIX_TIMESTAMP(date):返回值自’1970-01-01 00:00:00’与指定时间的秒数差

 
 
 
xxxxxxxxxx
 
 
 
 
-- 相差一年一天一分
select SUBTIME('2020-1-8 12:34:56','2019-1-7 12:33:56'),DATEDIFF('2020-1-8 12:34:56','2019-1-7 12:33:56');-- null  366
select TIME_TO_SEC('2020-1-8 12:34:56')-TIME_TO_SEC('2019-1-7 12:33:56');-- 60 只计算时间部分的秒差,需要加上天数差
select UNIX_TIMESTAMP('2020-1-8 12:34:56')-UNIX_TIMESTAMP('2019-1-7 12:33:56');-- 31622460 全部秒差,再换算
 

 

取时间分量

YEAR(date) MONTH(date)、day(),hour()、minute()、second()

 
 
 
xxxxxxxxxx
 
 
 
 
select now(),year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now());-- 2020-01-09 19:28:36202019192836
 

日期时间函数技巧

1,日期时间函数返回一般为字符串,+0可以把数字字符串变成数字。

 
 
 
xxxxxxxxxx
 
 
 
 
select CURDATE(),CURDATE() + 0; -- 2020-01-0920200109
select CURTIME(),CURTIME() + 0; -- 19:36:08193608
select now(),now() + 0;        -- 2020-01-09 19:36:3220200109193632
select SEC_TO_TIME(2378),SEC_TO_TIME(2378) + 0; -- 00:39:383938
 

2, 计算日期差,常用datediff(), 计算时间差,常用timestampdiff()。(案例见 1.2.2.4 timestampdiff()案例)

 

控制流程函数

 

CASE 函数

 

语法: CASE value WHEN [compare-value] THEN result

[WHEN [compare-value] THEN result ……]

[ELSE result ]

END

CASE WHEN [condition] THEN result

[WHEN[condition] THEN result ……]

[ELSE result]

END ;

函数用法说明:在第一个方案的返回结果中, value =compare-value 。而第二个方案的返回结果是第一种情况的真实结果。如果没有匹配的结果值,则返回结果为 ELSE 后的结果,如果没有 ELSE 部分,则返回值为 NULL

 

IF 函数用法

语法: IF(expr1,expr2,expr3)

函数用法说明:如果 expr1 是 TRUE (expr1 <> 0 and expr1 <> NULL) ,则 IF() 的返回值为 expr2 ; 否则返回值则为 expr3 。 IF() 的返回值为数字值或字符串值,具体情况视其所在语境而定

 

IFNULL 函数

语法: IFNULL(expr1,expr2)

函数用法说明:假如 expr1 不为 NULL ,则 IFNULL() 的返回值为 expr1 ; 否则其返回值为 expr2 。 IFNULL() 的返回值是数字或是字符串,具体情况取决于其所使用的语境。

NULLIF(arg1,arg2)

如果arg1=arg2返回NULL;否则返回arg1。

 
 
 
xxxxxxxxxx
 
 
 
 
select nullif(1,2),nullif(1,'1'),nullif('a','a'),nullif('a','b');-- 1null nulla
 

coalesce()

解释:返回参数中的第一个非空表达式(从左向右依次类推);

 
 
 
xxxxxxxxxx
 
 
 
 
select coalesce(null,2,3); --  Return 2
select coalesce(null,null,3); -- Return 3
select coalesce(1,2,3); -- Return 1
SELECT COALESCE(NULL, NULL, NULL, 1); -- Return 1 
SELECT COALESCE(NULL, NULL, NULL, NULL);-- Return NULL 
 

 

数学函数

 
 
 
xxxxxxxxxx
 
 
 
 
ABS(x)   返回x的绝对值
BIN(x)   返回x的二进制(OCT返回八进制,HEX返回十六进制)
CEILING(x)   返回大于x的最小整数值
EXP(x)   返回值e(自然对数的底)的x次方
FLOOR(x)   返回小于x的最大整数值
GREATEST(x1,x2,...,xn)返回集合中最大的值
LEAST(x1,x2,...,xn)      返回集合中最小的值
LN(x)                    返回x的自然对数
LOG(x,y)返回x的以y为底的对数
MOD(x,y) 返回x/y的模(余数)
PI()返回pi的值(圆周率)
RAND()返回0到1内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
ROUND(x,y)返回参数x的四舍五入的有y位小数的值,y默认为0,可省略
SIGN(x) 返回代表数字x的符号的值
SQRT(x) 返回一个数的平方根
TRUNCATE(x,y)返回数字x截短为y位小数的结果,精确数据,y为尾数。小数点为0位,往后y正,往前y负
least(x,y,...)  返回全部参数最小值
greatest(x,y,...) 返回全部参数最大值
 

案例:

 
 
 
xxxxxxxxxx
 
 
 
 
-- 举例最常用的
select round(3.14),round(3.8965,3);-- 33.897  四舍五入 ROUND(X,D),D默认为0
select TRUNCATE(3.8965,3),TRUNCATE(14335.3489385,0) --  3.89614335
,TRUNCATE(14335.3489385,-1),TRUNCATE(14335.3489385,-3);-- 1433014000
#rand()的取值范围为[0,1),在i ≤ R ≤ j 这个范围得到一个随机整数R ,需要用到表达式 FLOOR(i + RAND() * (j – i + 1))
select rand();-- 每次不一样 
select rand(10);-- 每次一样。10为随机数发生器种子值。每次在给定值种子生成,RAND()会产生一个可重复的一系列数字。
select floor(1+rand()*100);-- [1,100]随机整数。
select floor(6+rand()*(66-6+1));-- [6;66]
-- MOD(x,y): 求余函数,返回x被y除后的余数;对于带有小数部分的数据值也起作用,它返回除法运算后的精确余数。
select MOD(31,10), MOD(22,-7), MOD(-7,2), MOD(-7,-2), MOD(45.5,6);-- 11-1-13.5
              
select LEAST(12.9,123,789,'999'),least("b","a","c");-- 12.9a
select GREATEST(12.9,456,789,'999'),greatest("b","a","c"); -- 999c  
 

 

加密函数

函数 MD5(str )

函数使用说明:为字符串算出一个 MD5 128 比特检查和。该值以 32 位十六进制数字的二进制字符串的形式返回 , 若参数为 NULL 则会返回 NULL 。不可逆。例如,返回值可被用作散列关键字。

函数PASSWORD(str )

函数使用说明:从原文密码str 计算并返回密码字符串,当参数为 NULL 时返回 NULL 。这个函数用于用户授权表的Password 列中的加密MySQL 密码存储,

 
 
 
xxxxxxxxxx
 
 
 
 
select md5('admin'),md5('admin'),MD5('123456'); -- 前两个一样,可用于复杂str是否相等
select password('admin'),password('admin'),password('123456');-- 前两个一样,一般用数据库的管理的
 

decode()与encode()

DECODE(str,key) 使用key作为密钥解密加密字符串str ENCODE(str,key) 使用key作为密钥加密字符串str,调用ENCODE()的结果是一个二进制字符串,它以BLOB类型存储.

 
 
 
xxxxxxxxxx
 
 
 
 
SELECT ENCODE('xufeng','key');
SELECT DECODE('��R�','key');# 不能直接解
SELECT DECODE(ENCODE('xufeng','key'),'key');#加解密放在一起
 

格式化函数

 
 
 
xxxxxxxxxx
 
 
 
 
DATE_FORMAT(date,fmt)  依照字符串fmt格式化日期date值
FORMAT(x,y)   把x格式化为以逗号隔开的数字序列,y是结果的小数位数。把大的数值格式化为以逗号间隔的易读的序列。
INET_ATON(ip)   返回IP地址的数字表示
INET_NTOA(num)   返回数字所代表的IP地址
TIME_FORMAT(time,fmt)  依照字符串fmt格式化时间time值
 
 
 
 
xxxxxxxxxx
 
 
 
 
SELECT FORMAT(34234.34323432,3);-- 34,234.343
SELECT FORMAT(34234.34323432,4);-- 34,234.3432
SELECT FORMAT(34534234.456,0);-- 34,534,234
SELECT DATE_FORMAT(NOW(),'%W,%D %M %Y %r');-- Friday,10th January 2020 05:35:32 PM
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');-- 2020-01-10
SELECT DATE_FORMAT(19990330,'%Y-%m-%d');-- 1999-03-30
SELECT DATE_FORMAT(NOW(),'%h:%i %p');-- 05:35 PM
SELECT TIME_FORMAT(NOW(),'%h:%i %p');-- 05:35 PM
SELECT INET_ATON('10.122.89.47');-- 175790383
SELECT INET_NTOA(175790383);  -- 10.122.89.47
 

 

转化函数

CAST()类型转化函数

为了进行数据类型转化,MySQL提供了CAST()函数,它可以把一个值转化为指定的数据类型。

 
 
 
xxxxxxxxxx
 
 
 
 
可以转换的类型是有限制的。这个类型可以是以下值其中的一个:
    二进制,同带binary前缀的效果 : BINARY    
    字符型,可带参数 : CHAR()     
    日期 : DATE     
    时间: TIME     
    日期时间型 : DATETIME     
    浮点数 : DECIMAL      
    整数 : SIGNED     
    无符号整数 : UNSIGNED 
 
 
 
 
xxxxxxxxxx
 
 
 
 
SELECT CAST(NOW() AS SIGNED INTEGER),now()+0;-- 2020011018003720200110180037
SELECT bin(3)=BINARY 11,bin(3)=b'11','f'=BINARY 'F','f'=CAST('f' AS BINARY);-- 1 0 0 1
SELECT 0x41,CAST(0x41 AS UNSIGNED),CAST(0x41 AS CHAR);-- A65A
 

CONVERT() 转换

类型转换:CONVERT(expr,type) 等同 CAST(expr AS type);

字符集转换:CONVERT(expr USING transcoding_name) ,需要切换字符集时

 
 
 
xxxxxxxxxx
 
 
 
 
SELECT CAST('125e342.83' AS signed),CONVERT('125e342.83',signed)
  ,CAST('3.35' AS signed),CAST('3.3567' AS DECIMAL(4,2));     -- 125  12533.36
-- 中文姓名排序
create table id_names(id int(5) not null PRIMARY KEY auto_increment,name VARCHAR(10));
insert into id_names(name) values('马云'),('柳传志'),('李彦宏'),('毕福剑'),('赵薇');
select * from id_names order by CONVERT(name using gbk) ;
 

CONV(x,f1,f2)

返回 f1 进制数变成 f2 进制数。

 
 
 
xxxxxxxxxx
 
 
 
 
select CONV(13,10,2),CONV(16,10,2),CONV(160,10,16);-- 110110000A0
 

 

信息函数

  • DATABASE() 返回当前数据库名
  • BENCHMARK(count,expr) 将表达式expr重复运行count次
  • CONNECTION_ID() 返回当前客户的连接ID
  • FOUND_ROWS() 返回最后一个SELECT查询进行检索的总行数
  • USER()或SYSTEM_USER() 返回当前登陆用户名
  • VERSION() 返回MySQL服务器的版本
 
 
 
xxxxxxxxxx
 
 
 
 
SELECT DATABASE(),VERSION(),USER();
select BENCHMARK(1000000,@a:=@a+1) from (select @a:=0) a;
 

其他函数

UUID()

UUID是指在一台机器上生成的数字,它保证对在同一时空中的所有机器都是唯一的。在UUID的算法中,可能会用到诸如网卡MAC地址,IP,主机名,进程ID等信息以保证其独立性. 和INT类型相比,UUID才是最自然的主键选择.

 
 
 
xxxxxxxxxx
 
 
 
 
SELECT UUID();-- 31f50542-339e-11ea-8a1e-00155dc00356
SELECT REPLACE(UUID(), '-', '');  #将'-'符号替换掉  3e6d2ba7339e11ea8a1e00155dc00356
使用UUID,基本上不可能出现相同的UUID值
1. 前三组值是时间戳换算过来的;
2. 第四组值是暂时性保持时间戳的唯一性。例如,使用夏令时;
3. 第五组值是一个IEE 802的节点标识值,它是空间上唯一的。若后者不可用,则用一个随机数字替换。假如主机没有网卡,或者我们不知道如何在某系统下获得机器地址,则空间唯一性就不能得到保证,即使这杨,出现重复值的机率还是非常小的。
 

MySQL中可以有二类用于生成唯一值性质的工具:UUID()函数和自增序列,那么二者有何区别呢?我们就此对比下各自的特性及异同点:

1,都可以实现生成唯一值的功能;

2,UUID是可以生成时间、空间上都独一无二的值;自增序列只能生成基于表内的唯一值,且需要搭配使其为唯一的主键或唯一索引;

3,实现方式不一样,UUID是随机+规则组合而成的,而自增序列是控制一个值逐步增长的;

4,UUID产生的是字符串类型值,固定长度为:36个字符,而自增序列产生的是整数类型值,长度由字段定义属性决定.

在MySQL 5.1.*及更高版本有一个变种的UUID()函数,名称:UUID_SHORT(),可以生成一个17-64位无符号的整数.

 
 
 
xxxxxxxxxx
 
 
 
 
SELECT UUID_SHORT(),UUID_SHORT(),CHAR_LENGTH(UUID_SHORT()),length(UUID_SHORT());-- 98542418080563219985424180805632201717
 

 

sql性能分析方法

explain这个命令来查看一个这些SQL语句的执行计划,查看该SQL语句有没有使用上了索引,有没有做全表扫描。expain出来的信息有10列,分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra。主要关注type列。

 
 
 
xxxxxxxxxx
 
 
 
 
对表访问方式,表示MySQL在表中找到所需行的方式,又称“访问类型”。
常用的类型有: ALL、index、range、 ref、eq_ref、const、system、NULL(从左到右,性能从差到好)
ALL:Full Table Scan, MySQL将遍历全表以找到匹配的行
index: Full Index Scan,index与ALL区别为index类型只遍历索引树
range:只检索给定范围的行,使用一个索引来选择行
ref: 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
eq_ref: 类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量,system是const类型的特例,当查询的表只有一行的情况下,使用system
NULL: MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。
 

profiles 用来显示sql执行过程中各个环节的消耗情况,例如cpu使用情况,打开表、检查权限、执行优化器、返回数据等分别用了多长时间,可以分析语句执行慢的瓶颈在哪。

如果要使用这个命令首先要设置profiling为on,mysql默认设置为off;

 
 
 
xxxxxxxxxx
 
 
 
 
# 0,-- 查看当前profiling的值
select @@profiling;
-- 1,设置打开profiling
set profiling =1;-- set profiling=on;
-- 2,SQL语句的执行时间
show profiles;
-- 查看一条SQL语句的详细执行时间:
-- show profile for query query_id;
show profile for query 275;
 

 

posted @ 2020-09-03 03:29  博观约取&厚积薄发  阅读(203)  评论(0编辑  收藏  举报
回到顶部
返回顶部