首页 > 数据库 >MYSQL常用函数总结

MYSQL常用函数总结

时间：2023-06-09 18:14:34浏览次数：60  
标签：总结 13 09 06 函数 22 MYSQL 2023 SELECT

目录

一、数学函数

数学函数主要用于处理数字，包括整型、浮点数等。

计算绝对值

SELECT ABS(-1) AS '求绝对值';

小数取整

SELECT
	CEIL( 1.5 ) AS '向上取整1',
	CEILING( 1.5 ) AS '向上取整2',
	FLOOR( 1.5 ) AS '向下取整';

数字精度处理

SELECT
    ROUND(1.23456) AS '最近整数',
	TRUNCATE(1.23456, 3) AS '保留3位不舍入',
    ROUND(1.23456, 3) AS '保留3位舍入',
    FORMAT(1.23456,3) AS '保留3位舍入'

随机数(0~1)

SELECT
    RAND() ,
    RAND() AS '每次随机',
    RAND(10),
    RAND(10) AS '根据随机种子随机'

计算数字符号

SELECT
    SIGN(0) AS '零值',
    SIGN(-10) AS '负数',
    SIGN(10) AS '正数';

获取圆周率

SELECT PI()

计算次方

SELECT
	POW( 2, 2 ),
	POWER( 2, 3 ),
	EXP(4) as '指数e的4次方'

计算开平方

SELECT SQRT(25)

计算除法取余

SELECT MOD(5,2);

计算对数

SELECT
    LOG(20.085536923188) AS '自然指数e为底的对数',
    LOG10(100) AS '10为底的对数'

角度<=>弧度

SELECT
	RADIANS( 180 ) AS '角度转弧度',
	DEGREES( 3.1415926535898 ) AS '弧度转角度'

三角函数计算

SELECT
    SIN(-3.15) AS '求正弦(以下参数均是弧度)',
    ASIN(0.15) AS '求反正弦',
    COS(30) AS '求余弦',
    ACOS(0.15) AS '求反余弦',
    TAN(- 15) AS '求正切',
    ATAN(30) AS '求反正切',
    ATAN(30, 15) AS '求反正切',
    ATAN2(30) AS '求反正切',
    ATAN2(30, 15) AS '求反正切',
    COT(15) AS '求余切值'

进制转换

SELECT
    ASCII('abc') AS '求第一个字符的ASCII码',
    BIN(2) AS '10进制转换为二进制',
    OCT(7) AS '10进制转换为八进制',
    HEX(15) AS '10进制转换为十六进制',
    CONV(10, 8, 10) AS '指定进制转换'

二、字符串函数

字符串长度

SELECT
	CHAR_LENGTH( '你好123' ) AS '字符数',
	LENGTH( '你好123' ) AS '字符长度'

字符拼接

SELECT
    CONCAT('12', '34') AS '字符串普通拼接',
    CONCAT_WS('连接符', 'a', 'b', 'c') AS '用连接符拼接'

字符串大小写转换

SELECT
	UPPER( 'abc' ) AS '小转大',
	UCASE( 'abc' ) AS '小转大',
	LOWER( 'A' ) AS '大转小',
	LCASE( 'A' ) AS '大转小'

字符串截取

SELECT
    LEFT('123456789', 2) AS '截取从左边开始到第2位',
    RIGHT('123456789', 2) AS '截取从右边开始到第2位',
    SUBSTRING('123456789',3,6) AS '截取从左边开始第3-6位',
    MID('123456789',3,6) AS '截取从左边开始第3-6位'

复杂截取

SUBSTRING_INDEX(str,x,index)：用x截取str，并在截取的数组中取第index个

SELECT SUBSTRING_INDEX('a*b','*',1); -- a
SELECT SUBSTRING_INDEX('a*b','*',-1); -- b
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1); -- c

指定位置与长度的字符替换

INSERT(str,pos,len,newstr)

str：字符串
pos：str开始被替换的位置
len：被替换的字符串长度
newstr：新字符串

SELECT INSERT('12345',2,1,'a')

字符串替换

REPLACE(s,x,y)：在字符串s中将x替换成y

SELECT REPLACE('abaca','a','0')

字符串填充

  • LPAD(s,len,x)：从左边开始用x填充s到长度为len
  • RPAD(s,len,x)：规则同上
SELECT
	LPAD( 'abc', 5, 'xz' ) AS '左填充',
	RPAD( 'abc', 5, 'xx' ) AS '右填充';

去除空格(首尾)

SELECT
	LTRIM( '  123456  ' ) AS '去除左边空白字符',
	RTRIM( '  123456  ' ) AS '去除右边空白字符',
	TRIM( '  123456  ' ) AS '去除两边空白字符'

去除指定字符(首尾)

SELECT TRIM('@' FROM '@@abc@@') AS '剔除除指定字符'

字符串重复

SELECT REPEAT('123',3)

获得n个空格字符

SELECT SPACE(100) AS '获得n个空格字符'

翻转字符串

SELECT REVERSE('abc')

比较两个字符串(ASCII码值)大小

SELECT
    ASCII('2'),
    ASCII('1'),
    STRCMP('2', '1')

找字符在字符串中的位置

LOCATE(x,s)、POSITION(x IN s)：x在s中的位置

SELECT LOCATE('4', '123456789'),POSITION('5' IN '123456789')

INSTR(s,y)：y在s中的位置

SELECT INSTR('123456789','2')

返回字符串第n项

ELT(n,s1,s2,...sn)

SELECT ELT(2,'a','b','c')

找位置

FIELD(x,s1,s2...,sn)：找到x在s1-sn字符串列表中匹配的位置

SELECT FIELD('3','1','2','3','4','5','6','7','8','9')

FIND_IN_SET(x,strlist) ：在strlist字符串列表(注意：必须是"1,2,3,4"这种以逗号分隔的字符串)中找到x的位置

SELECT FIND_IN_SET('4','1,2,3,4,5,6,7,8,9')

计算二进制数真假位

EXPORT_SET(二进制数, 真位, 假位, 连接符, 二进制数中需要的位数)：https://vimsky.com/examples/usage/export_set-function-in-mysql.html

SELECT BIN(5),EXPORT_SET(5,'真位也即二进制1','假位也即二进制0','-连接符-',5)

SELECT BIN(5),EXPORT_SET(5,'y','n','——',5)

计算二进制数真假位返回对应结果

MAKE_SET(x,str1,str2,…)：将x转换为二进制数，根据位置一一对应，返回真位对应的字符串,https://blog.csdn.net/qq_41725312/article/details/83039525

SELECT
    BIN(2 | 1),
    MAKE_SET(2 | 1, 'a', 'b', 'c');

三、日期时间函数

返回当前时间相关

SELECT
	NOW() AS '当前日期时间1',
	CURRENT_TIMESTAMP () AS '当前日期时间2',
	LOCALTIME () AS '当前日期时间3',
	SYSDATE() AS '当前日期时间4',
	LOCALTIMESTAMP () AS '当前日期时间5',
	CURDATE() AS '当前日期1',
	CURRENT_DATE () AS '当前日期2',
	CURTIME() AS '当前时间1',
	CURRENT_TIME () AS '当前时间2'

UNIX时间戳相关

SELECT
	UNIX_TIMESTAMP() AS 'UNIX时间戳1',
	UNIX_TIMESTAMP( '2023-06-09 13:16:58' ) AS '日期时间转UNIX时间戳',
	FROM_UNIXTIME(1686287851) AS 'UNIX时间戳转日期时间'

返回UTC格式的日期时间

SELECT UTC_DATE(),UTC_TIME()

在日期时间中取月值

SELECT
    MONTH('2023-06-09 13:22:44') AS '取月值',
    MONTHNAME('2023-06-09 13:22:44') AS '取月名'

在日期时间中取日值

SELECT
	DAYOFYEAR( '2023-06-09 13:22:44' ) AS '年第几天',
	DAY ( '2023-06-09 13:22:44' ) AS '月第几天',
	DAYOFMONTH( '2023-06-09 13:22:44' ) AS '月第几天',
	DAYNAME( '2023-06-09 13:22:44' ) AS '星期几';

在日期时间中取周值

SELECT
	DAYOFWEEK( '2023-06-09 13:22:44' ) AS '周内的第几天',
	WEEKDAY( '2023-06-09 13:22:44' ) AS '星期几',
	WEEK ( '2023-06-09 13:22:44' ) AS '今年的第几周',
	WEEKOFYEAR( '2023-06-09 13:22:44' ) AS '今年的第几周';

在日期时间中取季度值

SELECT QUARTER('2023-06-09 13:22:44') AS '取季度'

在日期时间中取时间分部

SELECT
    HOUR('2023-06-09 13:22:44') AS '取小时',
    MINUTE('2023-06-09 13:22:44') AS '取分钟',
    SECOND('2023-06-09 13:22:44') AS '取秒'

在日期时间中提取

SELECT EXTRACT(MICROSECOND FROM '2023-06-09 13:22:44') AS '取微妙';
SELECT EXTRACT(SECOND FROM '2023-06-09 13:22:44') AS '取秒';
SELECT EXTRACT(MINUTE FROM '2023-06-09 13:22:44') AS '取分钟';
SELECT EXTRACT(HOUR FROM '2023-06-09 13:22:44') AS '取小时';
SELECT EXTRACT(DAY FROM '2023-06-09 13:22:44') AS '取月中第几天';
SELECT EXTRACT(WEEK FROM '2023-06-09 13:22:44') AS '取年中第几周';
SELECT EXTRACT(MONTH FROM '2023-06-09 13:22:44') AS '取月';
SELECT EXTRACT(QUARTER FROM '2023-06-09 13:22:44') AS '取季度';
SELECT EXTRACT(YEAR FROM '2023-06-09 13:22:44') AS '取年';
SELECT EXTRACT(SECOND_MICROSECOND FROM '2023-06-09 13:22:44') AS '取秒微妙';
SELECT EXTRACT(MINUTE_MICROSECOND FROM '2023-06-09 13:22:44') AS '取分钟秒微妙';
SELECT EXTRACT(MINUTE_SECOND FROM '2023-06-09 13:22:44') AS '取分钟秒';
SELECT EXTRACT(HOUR_MICROSECOND FROM '2023-06-09 13:22:44') AS '取小时分钟秒微妙';
SELECT EXTRACT(HOUR_SECOND FROM '2023-06-09 13:22:44') AS '取小时分钟秒';
SELECT EXTRACT(HOUR_MINUTE FROM '2023-06-09 13:22:44') AS '取小时分钟';
SELECT EXTRACT(DAY_MICROSECOND FROM '2023-06-09 13:22:44') AS '取日小时分钟秒微妙';
SELECT EXTRACT(DAY_SECOND FROM '2023-06-09 13:22:44') AS '取日小时分钟秒';
SELECT EXTRACT(DAY_MINUTE FROM '2023-06-09 13:22:44') AS '取日小时分钟';
SELECT EXTRACT(DAY_HOUR FROM '2023-06-09 13:22:44') AS '取日小时';
SELECT EXTRACT(YEAR_MONTH FROM '2023-06-09 13:22:44') AS '取年月';

将时间与秒数互转

SELECT
	TIME_TO_SEC( '2023-06-09 00:01:00' ) AS '时间转秒数1',
	TIME_TO_SEC( '00:01:00' ) AS '时间转秒数2',
	SEC_TO_TIME(120) AS '秒数转时间'

计算0000年1月1日相关

SELECT
	TO_DAYS( '0000-01-01' ) AS '计算到0000-01-01的天数1',
	TO_DAYS( '2023-06-09 13:58:50') AS '计算到0000-01-01的天数2',
	FROM_DAYS(739045) AS '计算0000-01-01往后偏移的日期'

计算天数差值(忽略时间分部)

SELECT DATEDIFF('2023-06-09 00:00:00','2023-06-08 23:59:59')

加减指定天数(忽略时间分部)

SELECT
    '2023-06-09 14:05:30' AS '原始值',
    ADDDATE('2023-06-09 14:05:30', 1) AS '加1天',
    SUBDATE('2023-06-09 14:05:30', 1) AS '减1天',
    ADDTIME('2023-06-09 14:05:30', 1) AS '加1秒',
    SUBTIME('2023-06-09 14:05:30', 1) AS '减1秒'

日期时间通用偏移

正值向未来偏移，负值向过去偏移

  • ADDDATE(d,INTERVAL 数值 type)
  • DATE_ADD(d,INTERVAL 数值 type)

负值向过去偏移，正值向未来偏移

  • SUBDATE(d,INTERVAL 数值 type)

type可选为以下之一：

  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH

时间格式化

1、常用格式

SELECT 
DATE_FORMAT('2011-09-20 08:30:45', '%Y-%m-%d %H:%i:%S') AS '指定时间',
DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S') AS '现在'

2、日期格式化函数

DATE_FORMAT(d,f)

TIME_FORMAT(t,f)

3、获取格式化字符串函数

GET_FORMAT({DATE|TIME|DATETIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})

SELECT 
GET_FORMAT(DATETIME,'EUR') AS '欧盟标准',
GET_FORMAT(DATETIME,'USA') AS '美国标准',
GET_FORMAT(DATETIME,'JIS') AS '日本工业标准',
GET_FORMAT(DATETIME,'ISO') AS '国际标准化组织制订的标准',
GET_FORMAT(DATETIME,'INTERNAL') AS '国际标准',
GET_FORMAT(DATE,'EUR'),
GET_FORMAT(DATE,'USA'),
GET_FORMAT(DATE,'JIS'),
GET_FORMAT(DATE,'ISO'),
GET_FORMAT(DATE,'INTERNAL'),
GET_FORMAT(TIME,'EUR'),
GET_FORMAT(TIME,'USA'),
GET_FORMAT(TIME,'JIS'),
GET_FORMAT(TIME,'ISO'),
GET_FORMAT(TIME,'INTERNAL')

4、格式字符串表示

%M      月名字(January……December)
%W      星期名字(Sunday……Saturday)
%D      有英语前缀的月份的日期(1st, 2nd, 3rd, 等等）
%Y      年, 数字, 4 位
%y      年, 数字, 2 位
%a      缩写的星期名字(Sun……Sat)
%d      月份中的天数, 数字(00……31)
%e      月份中的天数, 数字(0……31)
%m      月, 数字(01……12)
%c      月, 数字(1……12)
%b      缩写的月份名字(Jan……Dec)
%j      一年中的天数(001……366)
%H      小时(00……23)
%k      小时(0……23)
%h      小时(01……12)
%I      小时(01……12)
%l      小时(1……12)
%i      分钟, 数字(00……59)       		//这个最TM坑人，以前一直记得是m，错了半天才弄明白咋回事
%r      时间,12 小时(hh:mm:ss [AP]M)
%T      时间,24 小时(hh:mm:ss)
%S      秒(00……59)
%s      秒(00……59)
%p      AM或PM
%w      一个星期中的天数(0=Sunday ……6=Saturday）
%U      星期(0……52), 这里星期天是星期的第一天
%u      星期(0……52), 这里星期一是星期的第一天
%%      一个文字"%"

四、条件判断函数

IF

IF(expr,v1,v2)：如果表达式expr成立，返回结果v1；否则，返回结果v2

SELECT IF('1>2','正确','错误')

IFNULL

IFNULL(v1,v2)：如果v1的值不为NULL，则返回v1，否则返回v2

SELECT IFNULL(NULL,'Hello Word')

CASE

CASE表示函数开始，END表示函数结束。如果e1成立，则返回v1,如果e2成立，则返回v2，当全部不成立则返回vn，而当有一个成立之后，后面的就不执行了。

  • 语法1
CASE 
　　WHEN e1
　　THEN v1
　　WHEN e2
　　THEN e2
　　...
　　ELSE vn
END

SELECT
    CASE
        WHEN 1 > 0 THEN '1 > 0'
        WHEN 2 > 0 THEN '2 > 0'
        ELSE '3 > 0'
    END
  • 语法2
CASE expr 
　　WHEN e1 THEN v1
　　WHEN e1 THEN v1
　　...
　　ELSE vn
END

SELECT
    CASE 1
        WHEN 1 THEN 'A'
        WHEN 2 THEN 'B'
        ELSE 'C'
    END

五、系统信息函数

系统相关

SELECT
    VERSION() AS 'mysql版本',
    CONNECTION_ID() AS 'mysql当前链接数',
    DATABASE() AS '当前使用的数据库1',
    SCHEMA() AS '当前使用的数据库2'

用户相关

SELECT
    USER(),
    SYSTEM_USER(),
    SESSION_USER(),
    CURRENT_USER(),
    CURRENT_USER

字符集相关

SELECT
	CHARSET( "你好" ) AS '返回字符串str的字符集',
	COLLATION ( "你好" ) AS '返回字符串str的字符排列方式',
	LAST_INSERT_ID() AS '返回最近生成的AUTO_INCREMENT(自增)值'

六、加密函数

SELECT MD5('计算摘要')

8.0.16废弃：PASSWORD(str)、ENCODE(str,pswd_str)与DECODE(crypt_str,pswd_str)

七、其他函数

mysql中的get_lock锁机制解析

https://blog.csdn.net/tangtong1/article/details/51792617/

IP地址与数字相互转换的函数

SELECT
	INET_ATON( '192.168.0.1' ) AS 'IP转数字',
	INET_NTOA( 3232235521 ) AS '数字转IP'

加锁函数和解锁函数

  • GET_LOCK(name,time)函数定义一个名称为name、持续时间长度为time秒的锁。如果锁定成功，则返回1；如果尝试超时，则返回0；如果遇到错误，返回NULL。
  • IS_USED_LOCK(name)函数判断名称为name的锁定是否存在，存在返回1，不存在返回NULL
  • IS_FREE_LOCK(name)函数判断是否已使用名为name的锁定。如果使用，返回0，否则，返回1；
  • RELEASE_LOCK(name)函数解除名称为name的锁。如果解锁成功，则返回1；如果尝试超时，返回0了如果解锁失败，返回NULL；
SELECT GET_LOCK('MySQL1',10);
SELECT IS_USED_LOCK('MySQL1');

SELECT IS_FREE_LOCK('MySQL1');
SELECT RELEASE_LOCK('MySQL1');

重复执行指定操作的函数

BENCHMARK(count,expr)函数将表达式expr重复执行count次，然后返回执行时间。该函数可以用来判断MySQL处理表达式的速度。

SELECT BENCHMARK(10000,NOW())

改变字符集的函数

CONVERT(s USING cs)函数将字符串s的字符集变成cs。

SELECT
    CHARSET('ABC') AS '查看ABC的字符集',
    CONVERT('ABC' USING gbk) AS 'ABC使用gbk字符集',
    CHARSET(CONVERT('ABC' USING gbk)) AS 'ABC从原来字符集转换为gbk字符集'

转换数据类型

  • CAST(x AS type)
  • CONVERT(x,type)

　　这两个函数只对BINARY、CHAR、DATE、DATETIME、TIME、SIGNED INTEGER、UNSIGNED INTEGER有效

-- 字符串 转 整数
SELECT CAST('123' AS UNSIGNED INTEGER) + 1
-- mysql的隐式转换
SELECT '123' + 1
-- DATETIME 转换为 DATE
SELECT CAST(NOW() AS DATE)

参考：

https://vimsky.com/examples/usage/export_set-function-in-mysql.html

https://www.cnblogs.com/kissdodog/p/4168721.html

标签：总结,13,09,06,函数,22,MYSQL,2023,SELECT
From： https://www.cnblogs.com/hhddd-1024/p/17469920.html

相关文章

  • 配置SQLDeveloper连接mysql时报错Message from server:”host ‘****’ is not allowe
    问题描述：配置SQLDeveloper连接mysql时报错Messagefromserver:”host‘****’isnotallowedtoconnecttothismysqlserver”,如下所示：数据库：mysql8.0.27SQLDeveloper版本：23.1.0.097连接mysql使用的驱动文件：mysql-connector-j-8.0.33.jar1、异常重现2、解决过程[root......
  • 算法题总结-找零钱
    原题给定数组arr，arr中所有的值都为正整数且不重复。每个值代表一种面值的货币，每种面值的货币可以使用任意张，再给定一个aim，代表要找的钱数，求组成aim的最少货币数。如果无解，请返回-1.数据范围：数组大小满足0\len\le100000≤n≤10000，数组中每个数字都满足0<val\le10000......
  • mysql 8.0.26 my.cnf 配置文件模板
    ##############[mysqld]basedir=/home/work/mysql_3306datadir=/home/work/mysql_3306/datatmpdir=/home/work/mysql_3306/tmppid_file=/home/work/mysql_3306/tmp/mysqld.pidsocket=/home/work/mysql_3306/tmp/mysql.sockmysqlx_socket=/home/work/mysql......
  • Linux 命令总结
    实用Linux命令总结Linux关机,重启# 关机shutdown -h now# 重启shutdown -r now查看系统,CPU信息# 查看系统内核信息uname -a# 查看系统内核版本cat /proc/version# 查看当前用户环境变量envcat /proc/cpuinfo# 查看有几个逻辑cpu, 包括cpu型号cat /proc/cpu......
  • CVS 用法总结(zz)
    这里有份CVS中文手册http://man.chinaunix.net/develop/cvsdoc_zh/index.html#Topcvs用法总结(1)--cvs命令格式，标志字符和环境cvs用法总结(1)--cvs命令格式读书笔记，中文名"版本控制之道-使用cvs"，英文名"PragmaticVersionControl-UsingCVS"。以下内容......
  • 总结整理大全，69个后端技术头大问题
    总结到位：https://blog.csdn.net/JavaShark/article/details/125912023 前言：工欲善其事，必先利其器；士欲宣其义，必先读其书。后台开发作为互联网技术领域的掌上明珠，一直都是开发者们的追逐的高峰。本文将从后台开发所涉及到的技术术语出发，基于系统开发、架构设计、网络通信等几个方......
  • （转）七年老运维实战中的 Shell 开发经验总结
    原文：https://mp.weixin.qq.com/s/0VmbKcttZ0aKpVRb65ycew无论是系统运维，还是应用运维，均可分为“纯手工”—>“脚本化”—>“自动化”—>“智能化”几个阶段，其中自动化阶段，主要是将一些重复性人工操作和运维经验封装为程序或脚本，一方面避免重复性操作及风险，另一方面提高执行效率......
  • Mysql必知必会教程--数据过滤
    关键字：where这里只说明不匹配的用法，其他的较为简单，不予叙述。selectvend_id,prod_namefromproductswherevend_id<>1003;输出结果：+---------+--------------+|vend_id|prod_name|+---------+--------------+|1001|.5tonanvil||1001|1tonan......
  • mysql管理多服务器ftp虚拟用户
    前言1.FTP虚拟用户：FTP虚拟用户由ftp服务器提供，依赖于一个服务器本地账号，可以同时设置多个虚拟账号。vsftpd分辨虚拟用户，由可插入认证模块（PAM）认证，该模块的账号信息可用文件或者数据库存储。FTP虚拟用户是FTP服务器的专有用户，使用虚拟用户账号可以提供集中管理的FTP根目录，方便......
  • MySql必知必会教程--排序检索数据
    重点关键字：orderby排序单列数据：selectprod_namefromproductsorderbyprod_name;输出结果：+----------------+|prod_name|+----------------+|.5tonanvil||1tonanvil||2tonanvil||Birdseed||Carrots||Detonator......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99