MySQL函数

最新推荐文章于 2022-07-31 15:40:15 发布

Sudley.su

最新推荐文章于 2022-07-31 15:40:15 发布

阅读量192

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/Sudley/article/details/99481380

版权

MySQL 专栏收录该内容

13 篇文章 2 订阅

订阅专栏

文章目录

数学函数
聚合函数
字符串函数
日期时间函数
控制流函数
系统调试函数

数学函数

函数名称	函数用法
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内的随机值
round(x,y)	返回参数x的四舍五入的有y位小数的值
sign(x)	返回代表数字x的符号的值
sqrt(x)	返回一个数的平方根
truncate(x,y)	返回数字x截短为y位小数的结果

示例：
选择性的演示几个函数

mysql> select bin(7);
+--------+
| bin(7) |
+--------+
| 111    |
+--------+
1 row in set (0.00 sec)

mysql> select ceiling(7.5);
+--------------+
| ceiling(7.5) |
+--------------+
|            8 |
+--------------+
1 row in set (0.00 sec)

mysql> select floor(7.5);
+------------+
| floor(7.5) |
+------------+
|          7 |
+------------+
1 row in set (0.00 sec)

mysql> select greatest(1,3,9,5);
+-------------------+
| greatest(1,3,9,5) |
+-------------------+
|                 9 |
+-------------------+
1 row in set (0.00 sec)

mysql> select round(pi(),2);
+---------------+
| round(pi(),2) |
+---------------+
|          3.14 |
+---------------+
1 row in set (0.00 sec)

mysql> select ceiling(rand()*10+5);
+----------------------+
| ceiling(rand()*10+5) |
+----------------------+
|                    8 |
+----------------------+
1 row in set (0.00 sec)

mysql> select truncate(pi(),6);
+------------------+
| truncate(pi(),6) |
+------------------+
|         3.141592 |
+------------------+
1 row in set (0.00 sec)

mysql> select truncate(pi(),4);
+------------------+
| truncate(pi(),4) |
+------------------+
|           3.1415 |
+------------------+
1 row in set (0.00 sec)

mysql> select round(pi(),4);
+---------------+
| round(pi(),4) |
+---------------+
|        3.1416 |
+---------------+
1 row in set (0.00 sec)

聚合函数

函数名称	函数用法
avg(col)	返回指定列的平均值
count(col)	返回指定列中非NULL值的个数
min(col)	返回指定列最小值
max(col)	返回指定列最大值
sum(col)	返回指定列所有值之和
group_concat(col)	返回由属于一组的列值连接组合而成的结果

示例：
选择性的演示几个函数

mysql> select * from mt;
+--------+--------+---------+
| cat_id | name   | high_cm |
+--------+--------+---------+
|      1 | 张三   |     173 |
|      2 | 张三   |     155 |
+--------+--------+---------+
2 rows in set (0.04 sec)

mysql> select group_concat(high_cm,cat_id) from mt group by name;
+------------------------------+
| group_concat(high_cm,cat_id) |
+------------------------------+
| 1731,1552                    |
+------------------------------+
1 row in set (0.00 sec)

字符串函数

函数名称	函数用法
ascii(x)	返回字符的ASCII码值
length(str)	返回字符串的字节数
bit_length(str)	返回字符串的比特长度
char_length(str)	返回字符串的字节数
concat(s1,s2,…,sn)	将s1s2连接成字符串
concat_as(sep,s1,s2,…,sn)	将s1s2连接成字符串并用sep字符间隔
insert(str,x,y,instr)	将字符串str从x~y位置长度的子串替换为字符串instr，返回结果
find_in_set(str,list)	分析都好相隔的list列表就，如果发现str，返回str在list中的位置
lcase(str)/lower(str)	返回将字符串所有字符改变为小写的结果
left(str,x)	返回字符串最左边的x个字符
ltrim(str)	去除字符串str开头的空格
position(substr in str)	返回子串substr在字符串str中第一次出现的位置
quote(str)	用反斜杠转义str中的单引号
repeat(str,srchstr,rplcstr)	返回字符串str重复x次的结果
reverse(str)	返回颠倒字符串的结果
right(str,x)	返回字符串str中最右边的x个字符
rtrim(str)	返回字符串str中最右边的空格
strcmp(s1,s2)	比较字符串
trim(str)	去除字符串首尾的空格
ucase(str)/upper(str)	返回将字符串所有字符转变为大写的结果

示例：
选择性的演示几个函数

mysql> select ascii('a');
+------------+
| ascii('a') |
+------------+
|         97 |
+------------+
1 row in set (0.00 sec)

mysql> select ascii('b');
+------------+
| ascii('b') |
+------------+
|         98 |
+------------+
1 row in set (0.00 sec)

mysql> select length('Sudley');
+------------------+
| length('Sudley') |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)

mysql> select bit_length('Sudley');
+----------------------+
| bit_length('Sudley') |
+----------------------+
|                   48 |
+----------------------+
1 row in set (0.00 sec)

mysql> select char_length('Sudley');
+-----------------------+
| char_length('Sudley') |
+-----------------------+
|                     6 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select reverse('Sudley');
+-------------------+
| reverse('Sudley') |
+-------------------+
| yelduS            |
+-------------------+
1 row in set (0.00 sec)

mysql> select position('u' in 'Sudley');
+---------------------------+
| position('u' in 'Sudley') |
+---------------------------+
|                         2 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select left('Sudley',2),right('Sudley',3);
+------------------+-------------------+
| left('Sudley',2) | right('Sudley',3) |
+------------------+-------------------+
| Su               | ley               |
+------------------+-------------------+
1 row in set (0.01 sec)

#获取某个字符后缀，可用于或取邮箱后缀
mysql> select right('Sudley',length('Sudley')-position('d' in 'Sudley'));       +------------------------------------------------------------+
| right('Sudley',length('Sudley')-position('d' in 'Sudley')) |
+------------------------------------------------------------+
| ley                                                        |
+------------------------------------------------------------+
1 row in set (0.00 sec)

日期时间函数

函数名称	函数用法
curdate()/current_date()	返回当前日期
curtime()	返回当前时间
date_add(date,interval int keyword)	返回日期date加上间隔时间int的结果
date_format(date,fmt)	依照指定的格式fmt格式化日期date
date_sub(date,interval int keyword)	返回
dayofweek(date)	返回1~7
dayofmonth(date)	返回1~31
dayofyear(date)	返回1~366
dayname(date)	返回date的星期名
from_unixtime(ts,fmt)	根据指定的格式fmt格式化unix时间戳ts
hour(time)	返回time的小时值0~23
minute(time)	返回分钟值0~59
month(date)	返回date的月份值
monthname(date)	返回月份名称
now()	返回当前日期和时间
quarter(date)	返回date在一年中的季度
week(date)	返回date属于一年中的第几周
year(date)	返回date中的年份信息

示例：
选择性的演示几个函数

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2019-08-17 |
+------------+
1 row in set (0.04 sec)

mysql> select dayname(curdate());
+--------------------+
| dayname(curdate()) |
+--------------------+
| Saturday           |
+--------------------+
1 row in set (0.01 sec)

mysql> select dayofyear(curdate());
+----------------------+
| dayofyear(curdate()) |
+----------------------+
|                  229 |
+----------------------+
1 row in set (0.00 sec)

mysql> select dayofyear(now());
+------------------+
| dayofyear(now()) |
+------------------+
|              229 |
+------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-08-17 01:35:06 |
+---------------------+
1 row in set (0.00 sec)

#周日是第一天
mysql> select dayname(now()),dayofweek(now());
+----------------+------------------+
| dayname(now()) | dayofweek(now()) |
+----------------+------------------+
| Saturday       |                7 |
+----------------+------------------+
1 row in set (0.00 sec)

控制流函数

case 列
when ‘a’ then ‘1’
else ‘2’
end

mysql> select * from result;
+--------+--------+-------+
| name   | subect | score |
+--------+--------+-------+
| 张三   | 数学   |   120 |
| 张三   | 语文   |    12 |
| 张三   | 地理   |    20 |
| 李四   | 数学   |    44 |
| 李四   | 语文   |    33 |
| 王五   | 数学   |     5 |
+--------+--------+-------+
6 rows in set (0.04 sec)

mysql> select score,case subect when '数学' then '1' when '语文' then '2' else '3' end from result;
+-------+------------------------------------------------------------------------+
| score | case subect when '数学' then '1' when '语文' then '2' else '3' end     |
+-------+------------------------------------------------------------------------+
|   120 | 1                                                                      |
|    12 | 2                                                                      |
|    20 | 3                                                                      |
|    44 | 1                                                                      |
|    33 | 2                                                                      |
|     5 | 1                                                                      |
+-------+------------------------------------------------------------------------+
6 rows in set (0.00 sec)

mysql> select score,case subect when '数学' then '1' when '语文' then '2' else '3' end as subjectnum from result;
+-------+------------+
| score | subjectnum |
+-------+------------+
|   120 | 1          |
|    12 | 2          |
|    20 | 3          |
|    44 | 1          |
|    33 | 2          |
|     5 | 1          |
+-------+------------+
6 rows in set (0.00 sec)

mysql> select score,case subect when '数学' then '1' when '语文' then '2' end as subjectnum from result;
+-------+------------+
| score | subjectnum |
+-------+------------+
|   120 | 1          |
|    12 | 2          |
|    20 | NULL       |
|    44 | 1          |
|    33 | 2          |
|     5 | 1          |
+-------+------------+
6 rows in set (0.00 sec)

fi(条件判断,‘true’,‘false’)

mysql> select score,if(score=120,'true','false') from result;
+-------+------------------------------+
| score | if(score=120,'true','false') |
+-------+------------------------------+
|   120 | true                         |
|    12 | false                        |
|    20 | false                        |
|    44 | false                        |
|    33 | false                        |
|     5 | false                        |
+-------+------------------------------+
6 rows in set (0.00 sec)

mysql> select score,if(score>60,'perfect','a little sad') from result;
+-------+---------------------------------------+
| score | if(score>60,'perfect','a little sad') |
+-------+---------------------------------------+
|   120 | perfect                               |
|    12 | a little sad                          |
|    20 | a little sad                          |
|    44 | a little sad                          |
|    33 | a little sad                          |
|     5 | a little sad                          |
+-------+---------------------------------------+
6 rows in set (0.00 sec)

ifnull(expr1,expr2)
ifnull：判断第一个表达式是否为null，如果为null返回第二个表达式，如果为null返回第一个表达式（自己）

mysql> select ifnull(null,0);
+----------------+
| ifnull(null,0) |
+----------------+
|              0 |
+----------------+
1 row in set (0.00 sec)

mysql> select ifnull('null',0);
+------------------+
| ifnull('null',0) |
+------------------+
| null             |
+------------------+
1 row in set (0.00 sec)

nullif(expr1,expr2)
如果expr1=expr2成立，则返回null，否则返回expr1

系统调试函数

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> select database();
+------------+
| database() |
+------------+
| Sudley     |
+------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.62    |
+-----------+
1 row in set (0.00 sec)