[MySQL]单行函数

文章目录

• 资料
• 1 函数的理解
• • 1.1 什么是函数
  • 1.2 MySQL的内置函数及分类
• 2 数值函数
• • 2.1 基本函数
  • • ABS(x)
    • SIGN(X)
    • PI()
    • CEIL(x)，CEILING(x)
    • FLOOR(x)
    • LEAST(e1,e2,e3…)
    • GREATEST(e1,e2,e3…)
    • MOD(x,y)
    • RAND()
    • RAND(x)
    • ROUND(x)
    • ROUND(x,y)
    • TRUNCATE(x,y)
    • SQRT(x)
    • 代码示例
  • 2.2 角度与弧度互换函数
  • • RADIANS(x)
    • DEGREES(x)
    • 代码示例
  • 2.3 三角函数
  • • SIN(x)
    • ASIN(x)
    • COS(x)
    • ACOS(x)
    • TAN(x)
    • ATAN(x)
    • ATAN2(m,n)
    • COT(x)
    • 代码示例
  • 2.4 指数与对数
  • • POW(x,y)，POWER(X,Y)
    • EXP(X)
    • LN(X)，LOG(X)
    • LOG10(X)
    • LOG2(X)
    • 代码示例
  • 2.5 进制间的转换
  • • BIN(x)
    • HEX(x)
    • OCT(x)
    • CONV(x,f1,f2)
    • 代码示例
• 3 字符串函数
• • • ASCII(S)
    • CHAR_LENGTH(s)
    • LENGTH(s)
    • CONCAT(s1,s2,......,sn)
    • CONCAT_WS(x, s1,s2,......,sn)
    • INSERT(str, idx, len, replacestr)
    • REPLACE(str, a, b)
    • UPPER(s) 或 UCASE(s)
    • LOWER(s) 或LCASE(s)
    • LEFT(str,n)
    • RIGHT(str,n)
    • LPAD(str, len, pad)
    • RPAD(str ,len, pad)
    • LTRIM(s)
    • RTRIM(s)
    • TRIM(s)
    • TRIM(s1 FROM s)
    • TRIM(LEADING s1 FROM s)
    • TRIM(TRAILING s1 FROM s)
    • REPEAT(str, n)
    • SPACE(n)
    • STRCMP(s1,s2)
    • SUBSTR(s,index,len)
    • LOCATE(substr,str)
    • ELT(m,s1,s2,…,sn)
    • FIELD(s,s1,s2,…,sn)
    • FIND_IN_SET(s1,s2)
    • REVERSE(s)
    • NULLIF(value1,value2)
• 4. 日期和时间函数
• • 4.1 获取日期、时间
  • • CURDATE() ，CURRENT_DATE()
    • CURTIME() ， CURRENT_TIME()
    • NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()
    • UTC_DATE()
    • UTC_TIME()
    • 代码示例
  • 4.2 日期与时间戳的转换
  • • UNIX_TIMESTAMP()
    • UNIX_TIMESTAMP(date)
    • FROM_UNIXTIME(timestamp)
    • 代码示例
  • 4.3 获取月份、星期、星期数、天数等函数
  • • YEAR(date) / MONTH(date) / DAY(date)
    • HOUR(time) / MINUTE(time) / SECOND(time)
    • MONTHNAME(date)
    • DAYNAME(date)
    • WEEKDAY(date)
    • QUARTER(date)
    • WEEK(date) ， WEEKOFYEAR(date)
    • DAYOFYEAR(date)
    • DAYOFMONTH(date)
    • DAYOFWEEK(date)
    • 代码示例
  • 4.4 日期的操作函数
  • • EXTRACT(type FROM date)
  • 4.5 时间和秒钟转换的函数
  • • TIME_TO_SEC(time)
    • SEC_TO_TIME(seconds)
    • 代码示例
  • 4.6 计算日期和时间的函数
  • • DATE_ADD(datetime, INTERVAL expr type)， ADDDATE(date,INTERVAL expr type)
    • DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type)
    • ADDTIME(time1,time2)
    • SUBTIME(time1,time2)
    • DATEDIFF(date1,date2)
    • TIMEDIFF(time1, time2)
    • FROM_DAYS(N)
    • TO_DAYS(date)
    • LAST_DAY(date)
    • MAKEDATE(year,n)
    • MAKETIME(hour,minute,second)
    • PERIOD_ADD(time,n)
    • 代码示例
  • 4.7 日期的格式化与解析
  • • DATE_FORMAT(date,fmt)
    • TIME_FORMAT(time,fmt)
    • GET_FORMAT(date_type,format_type)
    • STR_TO_DATE(str, fmt)
    • 非GET_FORMAT 函数中fmt参数常用的格式符：
    • GET_FORMAT函数中date_type和format_type参数取值如下：
    • 代码示例
• 5. 流程控制函数
• • • IF(value,value1,value2)
    • IFNULL(value1, value2)
    • CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 .... [ELSE resultn] END
    • CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END
• 6. 加密与解密函数
• • • PASSWORD(str)
    • MD5(str)
    • SHA(str)
    • ENCODE(value,password_seed)
    • DECODE(value,password_seed)
• 7. MySQL信息函数
• • • VERSION()
    • CONNECTION_ID()
    • DATABASE()，SCHEMA()
    • USER()，CURRENT_USER()、SYSTEM_USER()， SESSION_USER()
    • CHARSET(value)
    • COLLATION(value)
    • 代码示例
• 8. 其他函数
• • • FORMAT(value,n)
    • CONV(value,from,to)
    • INET_ATON(ipvalue)
    • INET_NTOA(value)
    • BENCHMARK(n,expr)
    • CONVERT(value USING char_code)

---

资料

根据【MySQL数据库教程天花板，mysql安装到mysql高级，强！硬！】 整理
资料链接：
百度网盘：
链接：https://pan.baidu.com/s/1KboU_3EZJxrezMWZ2klP6g
提取码：1234
阿里云盘
【MySQL】

1 函数的理解

1.1 什么是函数

函数在计算机语言的使用中贯穿始终。

函数的作用：
可以把我们经常使用的代码封装起来，需要的时候直接调用即可。这样既提高了代码效率，又提高了可维护性。

在 SQL 中我们也可以使用函数对检索出来的数据进行函数操作。使用这些函数，可以极大地提高用户对数据库的管理效率。

从函数定义的角度出发，我们可以将函数分成 内置函数 和 自定义函数 。

在 SQL 语言中，同样也包括了内置函数和自定义函数。

内置函数是系统内置的通用函数，而自定义函数是我们根据自己的需要编写的。

1.2 MySQL的内置函数及分类

• MySQL提供的内置函数从实现的功能角度可以分为：

  • 数值函数
  • 字符串函数
  • 日期和时间函数
  • 流程控制函数
  • 加密与解密函数
  • 获取MySQL信息函数
  • 聚合函数
  • …

• 这里将这些丰富的内置函数再分为两类：

  • 单行函数
    • 输入一行数据，结果也为一行数据
    • 单行函数可以嵌套，单行函数的参数可以是一列或一个值
  • 聚合函数（或分组函数）
    • 输入多行数据，结果为一行数据

2 数值函数

2.1 基本函数

ABS(x)

返回x的绝对值

SIGN(X)

返回X的符号。正数返回1，负数返回-1，0返回0

PI()

返回圆周率的值

CEIL(x)，CEILING(x)

返回大于或等于某个值的最小整数

天花板函数，向上取整。

FLOOR(x)

返回小于或等于某个值的最大整数

地板函数，向下取整。

LEAST(e1,e2,e3…)

返回列表中的最小值

GREATEST(e1,e2,e3…)

返回列表中的最大值

MOD(x,y)

返回X除以Y后的余数

RAND()

返回0~1的随机值

RAND(x)

返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数

ROUND(x)

返回一个对x的值进行四舍五入后，最接近于X的整数

ROUND(x,y)

返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位

TRUNCATE(x,y)

返回数字x截断为y位小数的结果

截断后剩余的小数位数必须指定(y必须指定)。

SQRT(x)

返回x的平方根。当X的值为负数时，返回NULL

代码示例

SELECT
       ABS(-123),
       ABS(32),
       SIGN(-23),
       SIGN(43),
       PI(),
       CEIL(32.32),
       CEILING(-43.23),
       FLOOR(32.32),
       FLOOR(-43.23),
       MOD(12,5),
       RAND(),
       RAND(),
       RAND(10),
       RAND(10),
       RAND(-1),
       RAND(-1),
       ROUND(12.33),
       ROUND(12.343,2),
       ROUND(12.324,-1),
       TRUNCATE(12.66,1),
       TRUNCATE(12.66,-1)
FROM DUAL;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

单行函数的嵌套：

SELECT truncate(round(123.456, 2), 1)
FROM dual;
1
2

先四舍五入保留两位小数后为123.46，然后截断保留一位小数后为123.4。

2.2 角度与弧度互换函数

RADIANS(x)

将角度转化为弧度，其中，参数x为角度值

DEGREES(x)

将弧度转化为角度，其中，参数x为弧度值

代码示例

SELECT
       RADIANS(30),
       RADIANS(60),
       RADIANS(90),
       DEGREES(2*PI()),
       DEGREES(RADIANS(90))
FROM DUAL;
1
2
3
4
5
6
7

2.3 三角函数

SIN(x)

返回x的正弦值，其中，参数x为弧度值

ASIN(x)

返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL

COS(x)

返回x的余弦值，其中，参数x为弧度值

ACOS(x)

返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL

TAN(x)

返回x的正切值，其中，参数x为弧度值

ATAN(x)

返回x的反正切值，即返回正切值为x的值

ATAN2(m,n)

返回两个参数的反正切值

COT(x)

返回x的余切值，其中，X为弧度值

代码示例

SELECT
       SIN(RADIANS(30)),
       DEGREES(ASIN(1)),
       TAN(RADIANS(45)),
       DEGREES(ATAN(1)),
       DEGREES(ATAN2(1,1) )
FROM DUAL;
1
2
3
4
5
6
7

ATAN2(M,N)函数返回两个参数的反正切值。 与ATAN(X)函数相比，ATAN2(M,N)需要两个参数，例如有两个点point(x1,y1)和point(x2,y2)，使用ATAN(X)函数计算反正切值为ATAN((y2-y1)/(x2-x1))，使用ATAN2(M,N)计算反正切值则为ATAN2(y2-y1,x2-x1)。由使用方式可以看出，当x2-x1等于0时，ATAN(X)函数会报错，而ATAN2(M,N)函数则仍然可以计算。

(y2-y1)/(x2-x1) 计算出来的为两个点间的正切值。

2.4 指数与对数

POW(x,y)，POWER(X,Y)

返回x的y次方

EXP(X)

返回e的X次方，其中e是一个常数，2.718281828459045

LN(X)，LOG(X)

返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL

LOG10(X)

返回以10为底的X的对数，当X <= 0 时，返回的结果为NULL

LOG2(X)

返回以2为底的X的对数，当X <= 0 时，返回NULL

代码示例

SELECT
       POW(2,5),
       POWER(2,4),
       EXP(2),
       LN(10),
       LOG10(10),
       LOG2(4)
FROM DUAL;
1
2
3
4
5
6
7
8

2.5 进制间的转换

BIN(x)

返回x的二进制编码

HEX(x)

返回x的十六进制编码

OCT(x)

返回x的八进制编码

CONV(x,f1,f2)

返回f1进制数的x变成f2进制数

代码示例

SELECT 
       BIN(10),
       HEX(10),
       OCT(10),
       CONV(10,2,8)
FROM DUAL;
1
2
3
4
5
6

3 字符串函数

注意：MySQL中，字符串的位置是从1开始的。

ASCII(S)

返回字符串S中的第一个字符的ASCII码值.

与第一个字符后面的其他字符无关。

SELECT
       ASCII('Abcdfsf')
FROM DUAL;
1
2
3

CHAR_LENGTH(s)

返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同。

SELECT
       CHAR_LENGTH('hello'),
       CHAR_LENGTH('我们')
FROM DUAL;
1
2
3
4

LENGTH(s)

返回字符串s的字节数，和字符集有关

utf-8 中每个中文字符占三个字节

SELECT
       LENGTH('hello'),
       LENGTH('我们')
FROM DUAL;
1
2
3
4

CONCAT(s1,s2,…,sn)

连接s1,s2,…,sn为一个字符串

SELECT
       CONCAT('zs',' worked for ','ls')
FROM DUAL;
1
2
3

CONCAT_WS(x, s1,s2,…,sn)

同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上x

SELECT
       concat_ws('---', 'zs', 'ls', 'ww')
FROM DUAL;
1
2
3

INSERT(str, idx, len, replacestr)

将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr

注意：MySQL中，字符串的位置是从1开始的。

SELECT 
       INSERT('helloworld',2,3,'aaaaa')
FROM DUAL;
1
2
3

REPLACE(str, a, b)

用字符串b替换字符串str中所有出现的字符串a

字符串str中不存在子字符串a，不进行替换。

SELECT
       replace('hello', 'll', 'mm'),
       REPLACE('hello','lol','mmm')
FROM DUAL;
1
2
3
4

UPPER(s) 或 UCASE(s)

将字符串s的所有字母转成大写字母

SELECT UPPER('HelLo'),UCASE('HelLo')
FROM DUAL;
1
2

LOWER(s) 或LCASE(s)

将字符串s的所有字母转成小写字母

SELECT LOWER('HelLo'),LCASE('HelLo')
FROM DUAL;
1
2

LEFT(str,n)

返回字符串str最左边的n个字符

RIGHT(str,n)

返回字符串str最右边的n个字符

SELECT LEFT('hello',2),RIGHT('hello',3),RIGHT('hello',13)
FROM DUAL;
1
2

LPAD(str, len, pad)

用字符串pad对str最左边进行填充，直到str的长度为len个字符

实现右对齐效果

RPAD(str ,len, pad)

用字符串pad对str最右边进行填充，直到str的长度为len个字符

实现左对齐效果

SELECT LPAD('hello', 7,' '), LPAD('world', 7,' '), LPAD('aa', 7,' '),
       RPAD('hello', 7,' '), RPAD('world', 7,' '), RPAD('aa', 7,' ')
FROM dual;
1
2
3

LTRIM(s)

去掉字符串s左侧的空格

RTRIM(s)

去掉字符串s右侧的空格

TRIM(s)

去掉字符串s开始与结尾的空格

SELECT
       '         he l lo         ',
       CONCAT('*', LTRIM('         he l lo         '), '*'),
       CONCAT('*', RTRIM('         he l lo         '), '*'),
       CONCAT('*', TRIM('         he l lo         '), '*')
FROM dual;
1
2
3
4
5
6

TRIM(s1 FROM s)

去掉字符串s开始与结尾的s1

TRIM(LEADING s1 FROM s)

去掉字符串s开始处的s1

TRIM(TRAILING s1 FROM s)

去掉字符串s结尾处的s1

SELECT
       TRIM('oo' FROM 'ooheollo'),
       TRIM(LEADING 'o' FROM  'ooheollo'),
       TRIM(TRAILING 'o' FROM  'ooheollo')
FROM DUAL;
1
2
3
4
5

REPEAT(str, n)

返回str重复n次的结果

SELECT REPEAT('hello',4)
FROM DUAL;
1
2

SPACE(n)

返回n个空格

SELECT CONCAT(SPACE(5), '*')
FROM DUAL;
1
2

STRCMP(s1,s2)

比较字符串s1,s2的ASCII码值的大小

SELECT
       STRCMP('abc','abe'),
       STRCMP('abe','abc'),
       STRCMP('abc','abc')
FROM DUAL;
1
2
3
4
5

SUBSTR(s,index,len)

返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、 MID(s,n,len)相同

SELECT SUBSTR('hello',2,2)
FROM DUAL;
1
2

LOCATE(substr,str)

返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0

SELECT
       LOCATE('lll','hello'),
       LOCATE('ll','hello'),
       LOCATE('l','hello')
FROM DUAL;
1
2
3
4
5

ELT(m,s1,s2,…,sn)

返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn

FIELD(s,s1,s2,…,sn)

返回字符串s在字符串列表中第一次出现的位置

FIND_IN_SET(s1,s2)

返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分
隔的字符串

SELECT 
       ELT(2,'a','b','c','d'),
       FIELD('mm','gg','jj','mm','dd','mm'),
       FIND_IN_SET('mm','gg,mm,jj,dd,mm,gg')
FROM DUAL;
1
2
3
4
5

REVERSE(s)

返回s反转后的字符串

SELECT
       REVERSE('abcdefg')
FROM DUAL;
1
2
3

NULLIF(value1,value2)

比较两个字符串，如果value1与value2相等，则返回NULL，否则返回
value1

SELECT NULLIF('mysql','mysql'),NULLIF('mysql', '') FROM dual;
1

4. 日期和时间函数

4.1 获取日期、时间

CURDATE() ，CURRENT_DATE()

返回当前日期，只包含年、月、日

CURTIME() ， CURRENT_TIME()

返回当前时间，只包含时、分、秒

NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()

返回当前系统日期和时间

UTC_DATE()

返回UTC（世界标准时间）日期

UTC_TIME()

返回UTC（世界标准时间）时间

代码示例

SELECT
       CURDATE(),
       CURRENT_DATE(),
       CURTIME(),
       NOW(),
       SYSDATE(),
       UTC_DATE(),
       UTC_TIME()
FROM DUAL;
1
2
3
4
5
6
7
8
9

4.2 日期与时间戳的转换

UNIX_TIMESTAMP()

以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() - >1634348884

UNIX_TIMESTAMP(date)

将时间date以UNIX时间戳的形式返回。

FROM_UNIXTIME(timestamp)

将UNIX时间戳的时间转换为普通格式的时间

代码示例

SELECT
       UNIX_TIMESTAMP(),
       UNIX_TIMESTAMP('2021-10-01 12:12:32'),
       FROM_UNIXTIME(1635173853),
       FROM_UNIXTIME(1633061552)
FROM DUAL;
1
2
3
4
5
6

4.3 获取月份、星期、星期数、天数等函数

YEAR(date) / MONTH(date) / DAY(date)

返回具体的日期值

HOUR(time) / MINUTE(time) / SECOND(time)

返回具体的时间值

MONTHNAME(date)

返回月份：January，…

DAYNAME(date)

返回星期几：MONDAY，TUESDAY…SUNDAY

WEEKDAY(date)

返回周几，注意，周1是0，周2是1，…，周日是6

QUARTER(date)

返回日期对应的季度，范围为1～4

WEEK(date) ， WEEKOFYEAR(date)

返回一年中的第几周

DAYOFYEAR(date)

返回日期是一年中的第几天

DAYOFMONTH(date)

返回日期位于所在月份的第几天

DAYOFWEEK(date)

返回周几，注意：周日是1，周一是2，…，周六是7

代码示例

SELECT
       YEAR(CURDATE()),
       MONTH(CURDATE()),
       DAY(CURDATE()),
       HOUR(CURTIME()),
       MINUTE(NOW()),
       SECOND(SYSDATE()),
       MONTHNAME('2021-10-26'),
       DAYNAME('2021-10-26'),
       WEEKDAY('2021-10-26'),
       QUARTER(CURDATE()),
       WEEK(CURDATE()),
       DAYOFYEAR(NOW()),
       DAYOFMONTH(NOW()),
       DAYOFWEEK(NOW())
FROM DUAL;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

4.4 日期的操作函数

EXTRACT(type FROM date)

返回指定日期中特定的部分，type指定返回的值

SELECT
       EXTRACT(SECOND FROM NOW()),
       EXTRACT(DAY FROM NOW()),
       EXTRACT(HOUR_MINUTE FROM NOW()),
       EXTRACT(QUARTER FROM '2021-05-12')
FROM DUAL;
1
2
3
4
5
6

4.5 时间和秒钟转换的函数

TIME_TO_SEC(time)

将 time 转化为秒并返回结果值。转化的公式为： 小时*3600+分钟 *60+秒

SEC_TO_TIME(seconds)

将 seconds 描述转化为包含小时、分钟和秒的时间

代码示例

SELECT
       TIME_TO_SEC(CURTIME()),
       SEC_TO_TIME(53515)
FROM DUAL;
1
2
3
4

4.6 计算日期和时间的函数

DATE_ADD(datetime, INTERVAL expr type)， ADDDATE(date,INTERVAL expr type)

返回与给定日期时间相差INTERVAL时间段的日期时间

type的取值：

SELECT
       NOW(),
       DATE_ADD(NOW(),INTERVAL 1 YEAR),
       DATE_ADD(NOW(),INTERVAL -1 YEAR)
FROM DUAL;
1
2
3
4
5

SELECT
       DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,
       DATE_ADD('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col2,
       ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
       DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
       DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
       DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号
FROM DUAL;
1
2
3
4
5
6
7
8

DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type)

返回与date相差INTERVAL时间间隔的日期

type的取值：

SELECT
       NOW(),
       DATE_SUB(NOW(),INTERVAL 1 YEAR)
FROM DUAL;
1
2
3
4

ADDTIME(time1,time2)

返回time1加上time2的时间。当time2为一个数字时，代表的是秒 ，可以为负数

SUBTIME(time1,time2)

返回time1减去time2后的时间。当time2为一个数字时，代表的是 秒 ，可以为负数

DATEDIFF(date1,date2)

返回date1 - date2的日期间隔天数

TIMEDIFF(time1, time2)

返回time1 - time2的时间间隔

FROM_DAYS(N)

返回从0000年1月1日起，N天以后的日期

TO_DAYS(date)

返回日期date距离0000年1月1日的天数

LAST_DAY(date)

返回date所在月份的最后一天的日期

MAKEDATE(year,n)

针对给定年份与所在年份中的天数返回一个日期，即指定年份的第几天

MAKETIME(hour,minute,second)

将给定的小时、分钟和秒组合成时间并返回

PERIOD_ADD(time,n)

返回time加上n后的时间

代码示例

SELECT
       ADDTIME(NOW(),20),
       SUBTIME(NOW(),30),
       SUBTIME(NOW(),'1:1:3'),
       DATEDIFF(NOW(),'2021-10-01'),
       TIMEDIFF(NOW(),'2021-10-25 22:10:10'),
       FROM_DAYS(366),
       TO_DAYS('0000-12-25'),
       LAST_DAY(NOW()),
       MAKEDATE(YEAR(NOW()),32),
       MAKETIME(10,21,23),
       PERIOD_ADD(20200101010101,10)
FROM DUAL;`在这里插入代码片`
1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT
       # 日期转化为数值
       CURDATE(),
       CURDATE() + 0,
       CURTIME() + 0,
       NOW() + 0
FROM DUAL;
1
2
3
4
5
6
7

4.7 日期的格式化与解析

格式化：日期 —> 字符串
解析： 字符串 —> 日期
这里指的是日期的显式格式化和解析

DATE_FORMAT(date,fmt)

按照字符串fmt格式化日期date值

TIME_FORMAT(time,fmt)

按照字符串fmt格式化时间time值

GET_FORMAT(date_type,format_type)

返回日期字符串的显示格式

既不是格式化也不是解析，用于得到日期的格式，如：'%Y-%M-%D'

STR_TO_DATE(str, fmt)

按照字符串fmt对str进行解析，解析为一个日期

非GET_FORMAT 函数中fmt参数常用的格式符：

GET_FORMAT函数中date_type和format_type参数取值如下：

代码示例

#格式化：
SELECT
       DATE_FORMAT(CURDATE(),'%Y-%M-%D'),
       DATE_FORMAT(NOW(),'%Y-%m-%d'),
       TIME_FORMAT(CURTIME(),'%h:%i:%S'),
       DATE_FORMAT(NOW(),'%Y-%M-%D %h:%i:%S %W %w %T %r')
FROM DUAL;
1
2
3
4
5
6
7

#解析：格式化的逆过程
SELECT
       STR_TO_DATE('2021-October-25th 11:37:30 Monday 1','%Y-%M-%D %h:%i:%S %W %w'),
       GET_FORMAT(DATE,'USA'),
       DATE_FORMAT(CURDATE(),GET_FORMAT(DATE,'USA'))
FROM DUAL;
1
2
3
4
5
6

5. 流程控制函数

流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。

MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

IF(value,value1,value2)

如果value的值为TRUE，返回value1，否则返回value2

SELECT last_name,salary,IF(salary >= 6000,'高工资','低工资') "details"
FROM employees;
1
2

SELECT last_name,commission_pct,IF(commission_pct IS NOT NULL,commission_pct,0) "details",
salary * 12 * (1 + IF(commission_pct IS NOT NULL,commission_pct,0)) "annual_sal"
FROM employees;
1
2
3

IFNULL(value1, value2)

如果value1不为NULL，返回value1，否则返回value2

SELECT last_name,commission_pct,IFNULL(commission_pct,0) "details"
FROM employees;
1
2

CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 … [ELSE resultn] END

相当于Java的if…else if…else…

SELECT last_name,salary,CASE WHEN salary >= 15000 THEN '白骨精'
			     WHEN salary >= 10000 THEN '潜力股'
			     WHEN salary >= 8000 THEN '小屌丝'
			     ELSE '草根' END "details",department_id
FROM employees;
1
2
3
4
5

CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END

相当于Java的switch…case…

# 查询部门号为 10,20, 30 的员工信息,
# 若部门号为 10, 则打印其工资的 1.1 倍,
# 20 号部门, 则打印其工资的 1.2 倍,
# 30 号部门,打印其工资的 1.3 倍数,
# 其他部门,打印其工资的 1.4 倍数
SELECT employee_id,last_name,department_id,salary,CASE department_id WHEN 10 THEN salary * 1.1
								     WHEN 20 THEN salary * 1.2
								     WHEN 30 THEN salary * 1.3
								     ELSE salary * 1.4 END "details"
FROM employees;
1
2
3
4
5
6
7
8
9
10

6. 加密与解密函数

加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。

这些函数在保证数据库安全时非常有用。

加密的操作可以在客户端实现，避免在信息传递给服务端的过程中被他人获取。

PASSWORD(str)

返回字符串str的加密版本，41位长的字符串。加密结果 不可逆 ，常用于用户的密码加密

MySQL8.0中弃用。

对于不可逆的加密，进行验证前后是否一致，可以匹配暗文。多次加密的结果一致。

MD5(str)

返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL，则会返回NULL 。加密结果 不可逆。

对于不可逆的加密，进行验证前后是否一致，可以匹配暗文。多次加密的结果一致。

SHA(str)

从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。 加密结果 不可逆。SHA加密算法比MD5更加安全 。

对于不可逆的加密，进行验证前后是否一致，可以匹配暗文。多次加密的结果一致。

ENCODE(value,password_seed)

返回使用password_seed作为加密密码，加密value

MySQL8.0中弃用。

DECODE(value,password_seed)

返回使用password_seed作为加密密码，解密value

ENCODE(value,password_seed)函数与DECODE(value,password_seed)函数互为反函数。

MySQL8.0中弃用。

7. MySQL信息函数

MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。

VERSION()

返回当前MySQL的版本号

CONNECTION_ID()

返回当前MySQL服务器的连接数

DATABASE()，SCHEMA()

返回MySQL命令行当前所在的数据库

USER()，CURRENT_USER()、SYSTEM_USER()， SESSION_USER()

返回当前连接MySQL的用户名，返回结果格式为 “主机名@用户名”

CHARSET(value)

返回字符串value自变量的字符集

COLLATION(value)

返回字符串value的比较规则

代码示例

SELECT
       VERSION(),
       CONNECTION_ID(),
       DATABASE(),
       SCHEMA(),
       USER(),
       CURRENT_USER(),
       CHARSET('尚硅谷'),
       COLLATION('尚硅谷')
FROM DUAL;
1
2
3
4
5
6
7
8
9
10

8. 其他函数

FORMAT(value,n)

返回对数字value进行格式化后的结果数据。n表示 四舍五入 后保留到小数点后n位

如果n的值小于或者等于0，则只保留整数部分

SELECT
       FORMAT(123.125,2),
       FORMAT(123.125,0),
       FORMAT(123.125,-2)
FROM DUAL;
1
2
3
4
5

CONV(value,from,to)

将value的值进行不同进制之间的转换，value为null返回结果为null。

SELECT
       CONV(16, 10, 2),
       CONV(8888,10,16),
       CONV(NULL, 10, 2)
FROM DUAL;
1
2
3
4
5

INET_ATON(ipvalue)

将以点分隔的IP地址转化为一个数字

计算方法：以“192.168.1.100”为例，计算方式为192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上100。

INET_NTOA(value)

将数字形式的IP地址转化为以点分隔的IP地址

INET_NTOA(value) 与INET_ATON(ipvalue) 实现的功能相反

SELECT
       INET_ATON('192.168.1.100'),
       INET_NTOA(3232235876)
FROM DUAL;
1
2
3
4

BENCHMARK(n,expr)

将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间

#BENCHMARK()用于测试表达式的执行效率
SELECT
       BENCHMARK(10000000,MD5('mysql'))
FROM DUAL;
1
2
3
4

CONVERT(value USING char_code)

将value所使用的字符编码修改为char_code

可以实现字符集的转换

SELECT
       CHARSET('atguigu'),
       CHARSET(CONVERT('atguigu' USING 'gbk'))
FROM DUAL;
1
2
3
4