函数
数字类
获取随机数 rand()
MariaDB [sel]> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.3974576679966018 |
+--------------------+
# `1 row in set (0.000 sec)`
随机排序
MariaDB [sel]> select * from grades order by rand();
+-------+------+---------+------+
| name | sex | chinese | math |
+-------+------+---------+------+
| Tommy | boy | 98 | 94 |
| Sunny | boy | 93 | 96 |
| Jerry | boy | 97 | 91 |
| Marry | girl | 95 | 94 |
+-------+------+---------+------+
# `4 rows in set (0.000 sec)`
随机获取一条记录
MariaDB [sel]> select * from grades order by rand() limit 1;
+-------+------+---------+------+
| name | sex | chinese | math |
+-------+------+---------+------+
| Tommy | boy | 98 | 94 |
+-------+------+---------+------+
# `1 row in set (0.000 sec)`
四舍五入 round()
MariaDB [sel]> select round(3.1415926,3) '四舍五入';
+----------+
| 四舍五入 |
+----------+
| 3.142 |
+----------+
# `1 row in set (0.008 sec)`
截取数据 truncate()
MariaDB [sel]> select truncate(3.1415926,3) '截取数据';
+----------+
| 截取数据 |
+----------+
| 3.141 |
+----------+
# `1 row in set (0.007 sec)`
向上取整 ceil()
MariaDB [sel]> select ceil(3.1415926) '向上取整';
+----------+
| 向上取整 |
+----------+
| 4 |
+----------+
# `1 row in set (0.007 sec)`
向下取整 floor()
MariaDB [sel]> select floor(3.1415926) '向下取整';
+----------+
| 向下取整 |
+----------+
| 3 |
+----------+
# `1 row in set (0.000 sec)`
字符串类
大小写转换 ucase() lcase()
MariaDB [sel]> select ucase('i name is tom') '转成大写',lcase('My Name IS TOM') '转成小写';
+---------------+----------------+
| 转成大写 | 转成小写 |
+---------------+----------------+
| I NAME IS TOM | my name is tom |
+---------------+----------------+
# `1 row in set (0.008 sec)`
截取字符串 left() right() substring()
MariaDB [sel]> select left('sunflower',3) '从左截取',right('sunflower',6) '从右截取',substring('sunflower',4,9) '位置截取';
+----------+----------+----------+
| 从左截取 | 从右截取 | 位置截取 |
+----------+----------+----------+
| sun | flower | flower |
+----------+----------+----------+
# `1 row in set (0.007 sec)`
字符串相接 concat()
mysql> select concat('中国','北京','顺义') '地址';
+--------------+
| 地址 |
+--------------+
| 中国北京顺义 |
+--------------+
# `1 row in set (0.00 sec)`
mysql> select concat(stuname,'-',stusex) 信息 from stuinfo;
+-------------+
| 信息 |
+-------------+
| 张秋丽-男 |
| 李文才-男 |
| 李斯文-女 |
| 欧阳俊雄-男 |
| 诸葛丽丽-女 |
| 争青小子-男 |
| 梅超风-女 |
+-------------+
# `7 rows in set (0.00 sec)`
数据判断 coalesce(str1,str2)
- 说明
- 如果str1有值，显示str1的值，如果str1没有值，显示str2的值
-- 将成绩为空的显示为缺考
mysql> select stuname,coalesce(writtenexam,'缺考'),coalesce(labexam,'缺考') from stuinfo natural left join stumarks;
+----------+------------------------------+--------------------------+
| stuname | coalesce(writtenexam,'缺考') | coalesce(labexam,'缺考') |
+----------+------------------------------+--------------------------+
| 张秋丽 | 77 | 82 |
| 李文才 | 50 | 90 |
| 李斯文 | 80 | 58 |
| 欧阳俊雄 | 65 | 50 |
| 诸葛丽丽 | 缺考 | 缺考 |
| 争青小子 | 56 | 48 |
| 梅超风 | 缺考 | 缺考 |
+----------+------------------------------+--------------------------+
# `7 rows in set (0.02 sec)`
字节长度 length()
MariaDB [sel]> select length('我爱北京天安门') 字节长度;
+----------+
| 字节长度 |
+----------+
| 14 |
+----------+
# `1 row in set (0.000 sec)`
字符长度 char_length()
MariaDB [sel]> select char_length('我爱北京天安门') 字符长度;
+----------+
| 字符长度 |
+----------+
| 7 |
+----------+
# `1 row in set (0.000 sec)`
时间类
时间戳 unix_timestamp()
MariaDB [sel]> select unix_timestamp() '时间戳';
+------------+
| 时间戳 |
+------------+
| 1606984955 |
+------------+
# `1 row in set (0.000 sec)`
格式化时间戳 from_unixtime(unix_timestamp())
MariaDB [sel]> select from_unixtime(unix_timestamp()) '格式化时间戳';
+---------------------+
| 格式化时间戳 |
+---------------------+
| 2020-12-03 16:43:50 |
+---------------------+
# `1 row in set (0.000 sec)`
获取当前格式化时间 now()
MariaDB [sel]> select now() '当前时间';
+---------------------+
| 当前时间 |
+---------------------+
| 2020-12-03 16:49:05 |
+---------------------+
# `1 row in set (0.000 sec)`
获取年月日时分秒 year(now()) month(now()) day(now()) hour(now()) minute(now()) second(now())
MariaDB [sel]> select year(now()) 年,month(now()) 月,day(now()) 日,hour(now()) 时,minute(now()) 分,second(now())秒;
+------+------+------+------+------+------+
| 年 | 月 | 日 | 时 | 分 | 秒 |
+------+------+------+------+------+------+
| 2020 | 12 | 3 | 16 | 53 | 6 |
+------+------+------+------+------+------+
# `1 row in set (0.000 sec)`
获取星期 dayname(now())
MariaDB [sel]> select dayname(now()) '星期';
+----------+
| 星期 |
+----------+
| Thursday |
+----------+
# `1 row in set (0.000 sec)`
获取本年第几天 dayofyear(now())
MariaDB [sel]> select dayofyear(now()) '本年第几天';
+------------+
| 本年第几天 |
+------------+
| 338 |
+------------+
# `1 row in set (0.000 sec)`
日期相减 datediff(now(),'2021-01-01')
MariaDB [sel]> select datediff('2021-01-01',now()) '2020年剩余天数';
+----------------+
| 2020年剩余天数 |
+----------------+
| 29 |
+----------------+
# `1 row in set (0.000 sec)`
加密函数
md5() 加密
MariaDB [sel]> select md5('hello') 'MD5加密';
+----------------------------------+
| MD5加密 |
+----------------------------------+
| 5d41402abc4b2a76b9719d911017c592 |
+----------------------------------+
# `1 row in set (0.000 sec)`
sha() 加密
MariaDB [sel]> select sha('hello') 'sha加密';
+------------------------------------------+
| sha加密 |
+------------------------------------------+
| aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d |
+------------------------------------------+
1 row in set (0.000 sec)
