数据库MySQL-函数---cwl_java

1.7 函数1.7.1 数字类-- 获取随机数 mysql> select rand(); +------------------+ | rand() | +------------------+ | 0.25443412666622 | +------------------+ 1 row in set (0.00 sec) -- 随机排序 mysql> select * from stuinfo order by rand(); -- 随机获取一条记录 mysql> select * from stuinfo order by rand() limit 1; -- 四舍五入，向上取整，向下取整 mysql> select round(3.1415926,3) '四舍五入',truncate(3.14159,3) '截取数据',ceil(3.1) '向上取整',floor(3.9) '向下取整'; +----------+----------+----------+----------+ | 四舍五入 | 截取数据 | 向上取整 | 向下取整 | +----------+----------+----------+----------+ | 3.142 | 3.141 | 4 | 3 | +----------+----------+----------+----------+ 1 row in set (0.04 sec) 注意： 截取数据直接截取，不四舍五入1.7.2 字符串类-- 大小写转换 mysql> select ucase('i name is tom') '转成大写',lcase('My Name IS TOM') '转成小写'; +---------------+----------------+ | 转成大写 | 转成小写 | +---------------+----------------+ | I NAME IS TOM | my name is tom | +---------------+----------------+ 1 row in set (0.00 sec) -- 截取字符串 mysql> select left('abcdef',3) '从左边截取',right('abcdef',3) '从右边截取',substring('abcdef',2,3) '字符串'; +------------+------------+--------+ | 从左边截取 | 从右边截取 | 字符串 | +------------+------------+--------+ | abc | def | bcd | +------------+------------+--------+ 1 row in set (0.00 sec) -- 字符串相连 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（）:字节长度，char_length()：字符长度 mysql> select length('锄禾日当午') 字节,char_length('锄禾日当午') 字符; +------+------+ | 字节 | 字符 | +------+------+ | 10 | 5 | +------+------+ 1 row in set (0.00 sec)1.7.3 时间类-- 时间戳 mysql> select unix_timestamp(); +------------------+ | unix_timestamp() | +------------------+ | 1560330458 | +------------------+ 1 row in set (0.00 sec) -- 格式化时间戳 mysql> select from_unixtime(unix_timestamp()); +---------------------------------+ | from_unixtime(unix_timestamp()) | +---------------------------------+ | 2019-06-12 17:08:18 | +---------------------------------+ 1 row in set (0.05 sec) -- 获取当前格式化时间 mysql> select now(); +---------------------+ | now() | +---------------------+ | 2019-06-12 17:08:50 | +---------------------+ 1 row in set (0.00 sec) -- 获取年，月，日，小时，分钟，秒 mysql> select year(now()) 年,month(now()) 月,day(now()) 日,hour(now()) 小时,minute(now()) 分钟,second(now())秒; +------+------+------+------+------+------+ | 年 | 月 | 日 | 小时 | 分钟 | 秒 | +------+------+------+------+------+------+ | 2019 | 6 | 12 | 17 | 10 | 48 | +------+------+------+------+------+------+ 1 row in set (0.00 sec) -- 星期，本年第几天; mysql> select dayname(now()) 星期,dayofyear(now()) 本年第几天; +-----------+------------+ | 星期 | 本年第几天 | +-----------+------------+ | Wednesday | 163 | +-----------+------------+ 1 row in set (0.00 sec) -- 日期相减 mysql> select datediff(now(),'2010-08-08') 相距天数; +----------+ | 相距天数 | +----------+ | 3230 | +----------+ 1 row in set (0.00 sec)1.7.4 加密函数

1、md5()

2、sha()

mysql> select md5('aa'); +----------------------------------+ | md5('aa') | +----------------------------------+ | 4124bc0a9335c27f086f24ba207a4912 | +----------------------------------+ 1 row in set (0.00 sec) mysql> select sha('aa'); +------------------------------------------+ | sha('aa') | +------------------------------------------+ | e0c9035898dd52fc65c41454cec9c4d2611bfb37 | +------------------------------------------+ 1 row in set (0.00 sec) ---来自腾讯云社区的---cwl_java