MySQL快查-常用函数

最新推荐文章于 2024-09-11 10:18:51 发布

Cracal

最新推荐文章于 2024-09-11 10:18:51 发布

阅读量347

点赞数

分类专栏：编程基础 MySQL基础文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/weixin_45345384/article/details/115581250

版权

编程基础同时被 2 个专栏收录

15 篇文章 0 订阅

订阅专栏

MySQL基础

15 篇文章 0 订阅

订阅专栏

MySQL快查

因为在日常工作学习中经常忘记mysql的一些语句、关键字、操作等内容，所以最近抽取时间写了以下关于mysql相关内容。相当于一本字典吧

数据类型
 运算符
本文
数据完整性
 数据库的基本操作
 对表本身的操作
 对表中数据的操作
 子查询
 多表连接
 索引
 视图
 预处理SQL语句
 自定义函数与存储过程
 在MySQL中编程

常用函数

MySQL快查
字符串函数
日期和时间函数
- date_add()和date_sub()函数unit参数
- 时间和日期的格式化代码
数学函数
获取系统信息函数
加密函数
格式化函数
聚合函数

本文记录了mysql常用的函数。其中包括了字符串、日期时间、数学、获取系统信息、加密、格式化及聚合相关的函数。

字符串函数

函数	作用	例子
char_length(str)	返回参数(str)中的字符个数	char_length(‘abc’) #3
concat(str1,…)	返回连接参数而成的字符串	concat(“hello”, " world") # hello world
left(str, len)	返回str左边开始到第len个字符的字符串	left(‘abcd’,2) # ab
length(str)	返回str的长度（字节）	length(‘abcd’) # 4
lower(str)	返回将str中的大写字母变小写字母后的字符串	lower(‘aBcD’) # abcd
ltrim(str)	返回去除str起始位置空格后的字符串	ltrim(‘00a0b0’) # a0b0 注：为了方便观察这里用0表示空格
repeat(str, count)	返回将str重复count次后形成的字符串	repeat(‘abc’,2) # abcabc
replace(str, from_str, to_str)	返回将str中的from_str替换成to_str字串后的字符串	replace(‘l love u’, ‘love’,‘hate’) # l hate u
reverse(str)	返回反转的str	reverse(‘abc’) # cba
right(str, len)	返回从str右边开始到倒数第len个字符的字符串	right(‘abcdefg’, 3) # efg
rtrim(str)	返回去除str结尾位置的空格后的字符串	rtrim(‘00a0b0’) # 00a0b 注：为了方便观察这里用0表示空格
space(N)	返回由重复N次的空格组成的字符串	space(3) # 000 注：为了方便观察这里用0表示空格
substring	求字串	例子在该表格下面
upper(str)	返回将str中的小写字母变大写字母后的字符串	upper(‘aBcd’) # ABCD

# substring的例子
# substr类似于substring 以下用substr举例
substr(str, pos) # 截取str中从pos开始的字符串
mysql> select substr('hello world', 3);
+--------------------------+
| substr('hello world', 3) |
+--------------------------+
| llo world                |
+--------------------------+
1 row in set (0.00 sec)

substr(str FROM pos) # 同substr(str, pos)
mysql> select substr('hello world' from 3);
+------------------------------+
| substr('hello world' from 3) |
+------------------------------+
| llo world                    |
+------------------------------+
1 row in set (0.00 sec)

substr(str, pos, len) # 截取str中从pos开始,长度为len的字符串
mysql> select substr('hello world', 3, 6);
+-----------------------------+
| substr('hello world', 3, 6) |
+-----------------------------+
| llo wo                      |
+-----------------------------+
1 row in set (0.01 sec)

substr(str FROM pos FOR len) # 同substr(str, pos, len)
mysql> select substr('hello world' from 3 for 6);
+------------------------------------+
| substr('hello world' from 3 for 6) |
+------------------------------------+
| llo wo                             |
+------------------------------------+
1 row in set (0.00 sec)

日期和时间函数

函数	功能	例子
curdate()	返回当前日期 YYYY-MM-DD or YYYYMMDD	curdate() # 2021-04-10 curdate()+0 # 20210410
curtime()	获取当前时间 HH:MM:SS or HHMMSS	类似curdate()
date_add(date,INTERVAL expr unit)	对日期进行加法运算	date_add(‘2021-04-10’, interval 3 day) # 2021-04-13 除了day之外还有year mouth，second等, 详见下表
date_sub(date,INTERVAL expr unit)	对日期进行减法运算	类似date_add
datediff(expr1, expr2)	计算两个日期相隔天数(expr1-expr2)	datediff(now(), now()) # 0
now() / sysdate()	返回系统当前时间 YYYY-MM-DD HH:MM:SS	now() # 2021-04-10 19:05:56
day(date)	获取指定日期的日期整数(获取在某月中的天数, 类似dayofmonth)	day(now()) # 10,今天是10号 dayofmonth(‘2020-10-02’) # 2
dayname(date)	返回星期几	dayname(‘2021-04-10’) # Saturday
hour(time)	返回小时	hour(now()) # 19 ，现在是晚上7点
minute(time)	返回分钟	类似hour
month(date)	返回月份	类似hour
quarter(date)	返回季度	quarter(‘2021-10-1’) # 4
date_format(date, format)	格式化日期	date_format(now(), ‘%m, %H’) # 04, 19 格式化代码见下方
time_format(time, format)	格式化时间	类似date_format

date_add()和date_sub()函数unit参数

unit	格式(expr)	描述
day	日期	date_add(‘2021-04-10’, interval 3 day) # 2021-04-13
day_hour	日期:小时	date_add(‘2021-04-10 01:20:30’, interval ‘3:1’ day_hour) # 2021-04-13 02:20:30
day_minute	日期:小时:分钟	date_add(‘2021-04-10 01:20:30’, interval ‘3:1:20’ day_minute) # 2021-04-13 02:40:30
day_second	日期:小时:分钟:秒	date_add(‘2021-04-10 01:20:30’, interval ‘3:1:20’ day_second) # 2021-04-13 02:40:32
hour	小时	date_sub(‘2021-04-10 01:20:30’, interval 2 hour) # 2021-04-09 23:20:30
hour_minute	小时:分钟	类似上面
hour_second	小时:分钟:秒	类似上面
minute	分钟	date_sub(‘2021-04-10 01:20:30’, interval 2 minute) # 2021-04-10 01:18:30
minute_second	分钟:秒	类似上面
month	月	类似
year	年	类似上面
year_month	年-月	date_sub(‘2021-04-10 01:20:30’, interval ‘2-3’ year_month) # 2019-01-10 01:20:30
second	秒	类似上面

时间和日期的格式化代码

代码	描述
%a	缩写的星期 sun,…
%b	缩写的月份 jan,…
%d	月份中的天数
%H	小时 01,02,…
%I	分钟 00,01,…
%j	一年中的天数 001,002,…
%m	月份 00,01,…
%M	月份 january,…
%p	AM（上午）或PM（下午）
%r	时间，12小时格式
%S	秒 00,01,…
%T	时间，24小时格式
%w	一周中的天数 0,1,…
%W	星期 sunday,monday,…
%Y	年份,2000,2021,…

数学函数

函数	功能	例子
abs(X)	返回X的绝对值	abs(-8) # 8
ceiling(X) / ceil(X)	返回大于或等于X的最小整数	ceil(1.1) # 2
pi()	返回兀（3.1415926…）	pi() # 3.141593
cos(X)	返回余弦值	cos()
sin(X)	返回正弦值	类似上面
tan(X)	返回正切值	类似上面
degrees(X)	弧度转角度	degrees(PI()) # 180
radians(X)	角度转弧度	radians(180) # 3.141592653589793（PI()）
exp(X)	返回X为指数的e的值（e^X）	exp(2) # 7.3890560989307
floor(X)	返回小于或等于X的最大整数	floor(4.9) # 4
greatest(value1, value2,…)	返回各个alue中最大的数	greatest(3,1,2) # 3
least(value1, value2,…)	返回各个value中最小的数	least(3,1,2) # 1
log(b, x)	返回以b为底x的对数(log_bx)	–
log2(x) / log10(x)	返回以2/10为底x的对数	–
pow(X, Y)	返回x的y次方	pow(2,2) # 4
rand([N])	返回0～1之间的随机浮点数	rand() # 0.8214324673837353(随机数)
round(X[, D])	返回浮点数X精确到小数点后D位，D不写默认是0	round(3.4578) # 3 round(3.4578, 1) # 3.5
sqrt(X)	返回X的平方根	sqrt(4) # 2

获取系统信息函数

函数	功能
database()	返回当前数据库名
charset(str)	返回字符串str的字符集
user() or system_user()	返回当前登录用户
version()	获取mysql版本

加密函数

函数	描述
md5(str)	md5加密str为128位的密文，返回32位16进制字符串
sha(str) or sha1(str)	相对于md5较安全些，sha-1加密成160位，返回40位16进制的字符串
sha2(str, hash_length)	较md5和sha1较安全, has_length 取值224、256、384和512 分别对应 SHA-224, SHA-256, SHA-384, and SHA-512

一般不会使用这些函数

格式化函数

format(x, d,[locale])
将x（浮点数）格式化成保留d位小数点的数
可选的第三个参数locale允许指定区域,设置要用于结果的数字的小数点、千位分隔符和分组分隔符，默认使用en_US
如:
mysql> select format(2987.187, 0);
+---------------------+
| format(2987.187, 0) |
+---------------------+
| 2,987               |
+---------------------+
1 row in set (0.00 sec)

mysql> select format(2987.187, 2);
+---------------------+
| format(2987.187, 2) |
+---------------------+
| 2,987.19            |
+---------------------+
1 row in set (0.00 sec)

mysql> select format(2987.187, 5);
+---------------------+
| format(2987.187, 5) |
+---------------------+
| 2,987.18700         |
+---------------------+
1 row in set (0.00 sec)

聚合函数

聚合函数也成分组统计函数，常用于聚合在组内的数据表行进行计算。

下面的sql使用下面的数据表:

Field	Type	Null	Key	Default
student_id	int	NO	PRI	NULL
student_name	char(3)	NO		NULL
test_score	float	YES		0

avg([distinct] expr) 
# 统计expr组中数据的平均值
# 可以使用distinct去重
mysql> SELECT student_name, AVG(test_score)
       FROM student
       GROUP BY student_name;

count(expr)
# 返回组中数据数目
mysql> SELECT student.student_name,COUNT(*)
       FROM student
       GROUP BY student_name;

max([DISTINCT] expr)
# 取最大值

min([distinct] expr) 
# 取最小值

sum([distinct] expr)
# 求和