数据库MySQL-函数

-- 获取随机数
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)
注意： 截取数据直接截取，不四舍五入

-- 大小写转换
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)

-- 时间戳
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)

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)