MySQL数据库函数
2018-09-18 本文已影响16人
泡泡龙吐泡泡
1. 数字类
| 函数 | 作用 |
|---|---|
| rand() | 生成随机数 |
| order by rand() | 随机排序 |
| round(小数) | 四舍五入 |
| ceil(小数) | 向上取整 |
| floor(小数) | 向下取整 |
| truncate(小数,小数位数) | 截取数字 |
例题：
mysql> select rand(); # 生成随机数
+---------------------+
| rand() |
+---------------------+
| 0.18474003969201822 |
+---------------------+
1 row in set (0.00 sec)
mysql> select * from stuinfo order by rand(); # 随机排序
mysql> select * from stuinfo order by rand() limit 2; # 随机抽两个学生
+--------+----------+--------+--------+---------+------------+
| stuNo | stuName | stuSex | stuAge | stuSeat | stuAddress |
+--------+----------+--------+--------+---------+------------+
| s25305 | 诸葛丽丽 | 女 | 23 | 7 | 河南 |
| s25304 | 欧阳俊雄 | 男 | 28 | 4 | 天津 |
+--------+----------+--------+--------+---------+------------+
2 rows in set (0.00 sec)
mysql> select round(3.5); #四舍五入
+------------+
| round(3.5) |
+------------+
| 4 |
+------------+
1 row in set (0.00 sec)
mysql> select ceil(3.1); # 向上取整
+-----------+
| ceil(3.1) |
+-----------+
| 4 |
+-----------+
1 row in set (0.00 sec)
mysql> select floor(3.9); # 向下取整
+------------+
| floor(3.9) |
+------------+
| 3 |
+------------+
1 row in set (0.00 sec)
mysql> select truncate(3.1415926,3); # 截取数字
+-----------------------+
| truncate(3.1415926,3) |
+-----------------------+
| 3.141 |
+-----------------------+
1 row in set (0.00 sec)
2. 字符串类
| 函数 | 作用 |
|---|---|
| ucase(字符串) | 转成大写 |
| lcase(字符串) | 转成小写 |
| left(字符串, 个数) | 从左边截取 |
| right(字符串,个数) | 从右边截取 |
| substring(字符串, 开始位置,个数) | 截取子字符串 |
| concat(字符串,字符串...) | 字符串相连 |
| coalesce(字段1，字段2) | 如果字段1不为空就显示字段1，否则，显示字段2 |
| length(字符串) | 获取字节长度 |
| char_length | 获取字符个数 |
例题：
mysql> select ucase('i am a boy!'); # 转成大写
+----------------------+
| ucase('i am a boy!') |
+----------------------+
| I AM A BOY! |
+----------------------+
1 row in set (0.00 sec)
mysql> select lcase('I Am A Boy!'); #转成小写
+----------------------+
| lcase('I Am A Boy!') |
+----------------------+
| i am a boy! |
+----------------------+
1 row in set (0.00 sec)
mysql> select left('abcde',3); # 从左边开始截取，截取3个
+-----------------+
| left('abcde',3) |
+-----------------+
| abc |
+-----------------+
1 row in set (0.00 sec)
mysql> select right('abcde',3); # 从右边开始截取，截取3个
+------------------+
| right('abcde',3) |
+------------------+
| cde |
+------------------+
1 row in set (0.00 sec)
mysql> select substring('abcde',2,3); #从第2个位置开始截取，截取3个【位置从1开始】
+------------------------+
| substring('abcde',2,3) |
+------------------------+
| bcd |
+------------------------+
1 row in set (0.00 sec)
mysql> select concat('中国','上海'); # 字符串相连
+-----------------------+
| concat('中国','上海') |
+-----------------------+
| 中国上海 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select concat(stuname,'-',stusex) from stuinfo; # 将表中的姓名和性别连接起来
+----------------------------+
| concat(stuname,'-',stusex) |
+----------------------------+
| 张秋丽-男 |
| 李文才-男 |
| 李斯文-女 |
| 欧阳俊雄-男 |
| 诸葛丽丽-女 |
| 争青小子-男 |
| 梅超风-女 |
+----------------------------+
7 rows in set (0.00 sec)
# coalesce(字段1，字段2) 如果字段1不为空就显示字段1，否则，显示字段2
mysql> select stuname,coalesce(writtenexam,'缺考'),coalesce(labexam,'缺考') from stuinfo natural left join stumarks; # 将考试成绩为空的显示为缺考
+----------+------------------------------+--------------------------+
| stuname | coalesce(writtenexam,'缺考') | coalesce(labexam,'缺考') |
+----------+------------------------------+--------------------------+
| 张秋丽 | 77 | 82 |
| 李文才 | 50 | 90 |
| 李斯文 | 88 | 58 |
| 欧阳俊雄 | 65 | 50 |
| 诸葛丽丽 | 缺考 | 缺考 |
| 争青小子 | 56 | 48 |
| 梅超风 | 缺考 | 缺考 |
+----------+------------------------------+--------------------------+
mysql> select length('锄禾日当午'); # 字节长度
+----------------------+
| length('锄禾日当午') |
+----------------------+
| 10 |
+----------------------+
1 row in set (0.00 sec)
mysql> select char_length('锄禾日当午'); # 字符个数
+---------------------------+
| char_length('锄禾日当午') |
+---------------------------+
| 5 |
+---------------------------+
1 row in set (0.00 sec)
3. 时间类
| 函数 | 作用 |
|---|---|
| unix_timestamp() | 获取当前时间戳 |
| unix_timestamp() | 获取时间戳 |
| from_unixtime(时间戳) | 将时间戳转成年-月-日 小时:分钟:秒的格式 |
| now() | 获取当前日期时间 |
| year(now()) | 获取当前年 |
| month(now()) | 获取当前月 |
| day(now()) | 获取当前日 |
| hour(now()) | 获取当前时 |
| minute(now()) | 获取当前分 |
| second(now()) | 获取当前秒 |
| dayname(now()) | 获取当前星期 |
| monthname(now()) | 获取当前月（英文） |
| dayofyear(now()) | 获取本年的第几天 |
| datediff(date1，date2) | 日期相减 |
| convert(now(),date/time) | 将now()转成日期/时间 |
| cast(now() as date/time) | 将now()转成日期/时间 |
mysql> select unix_timestamp(); #获取时间戳
+------------------+
| unix_timestamp() |
+------------------+
| 1537084508 |
+------------------+
1 row in set (0.00 sec)
mysql> select from_unixtime(unix_timestamp()); # 将时间戳转成年-月-日 小时:分钟:秒的格式
+---------------------------------+
| from_unixtime(unix_timestamp()) |
+---------------------------------+
| 2018-09-16 15:55:56 |
+---------------------------------+
1 row in set (0.00 sec)
mysql> select now(); # 获取当前日期时间
+---------------------+
| now() |
+---------------------+
| 2018-09-16 15:57:04 |
+---------------------+
1 row in set (0.00 sec)
mysql> select year(now()) 年,month(now()) 月, day(now()) 日,hour(now()) 小,minute(now()) 分钟,second(now()) 秒;
+------+------+------+------+------+------+
| 年 | 月 | 日 | 小时 | 分钟 | 秒 |
+------+------+------+------+------+------+
| 2018 | 9 | 16 | 15 | 59 | 14 |
+------+------+------+------+------+------+
1 row in set (0.00 sec)
mysql> select dayname(now()) 星期,monthname(now()),dayofyear(now()) 本年的第几天;
+--------+------------------+--------------+
| 星期 | monthname(now()) | 本年的第几天 |
+--------+------------------+--------------+
| Sunday | September | 259 |
+--------+------------------+--------------+
1 row in set (0.00 sec)
mysql> select datediff(now(),'2008-8-8'); # 日期相减
+----------------------------+
| datediff(now(),'2008-8-8') |
+----------------------------+
| 3691 |
+----------------------------+
1 row in set (0.00 sec)
mysql> select convert(now(),date),convert(now(),time); # 将now()转成日期和时间
+---------------------+---------------------+
| convert(now(),date) | convert(now(),time) |
+---------------------+---------------------+
| 2018-09-16 | 16:07:24 |
+---------------------+---------------------+
mysql> select cast(now() as date),cast(now() as time); # 将now()转成日期和时间
+---------------------+---------------------+
| cast(now() as date) | cast(now() as time) |
+---------------------+---------------------+
| 2018-09-16 | 16:08:03 |
+---------------------+---------------------+
1 row in set (0.00 sec)
4. 加密函数
| 函数 | 作用 |
|---|---|
| md5(字符串) | md5加密 |
| sha(字符创) | sha加密 |
+----------------------------------+------------------------------------------+
| md5('root') | sha('root') |
+----------------------------------+------------------------------------------+
| 63a9f0ea7bb98050796b649e85481845 | dc76e9f0c0006e8f919e0c515c66dbba3982f785 |
+----------------------------------+------------------------------------------+
1 row in set (0.00 sec)
5 判断函数
语法：
if(表达式,值1,值2)
例题：
mysql> select if(10%2=0,'偶数','奇数');
+--------------------------+
| if(10%2=0,'偶数','奇数') |
+--------------------------+
| 偶数 |
+--------------------------+
1 row in set (0.00 sec)
# 语文和数学都超过60分才通过
mysql> select stuname,ch,math,if(ch>=60 && math>=60,'通过','不通过') '是否通过' from stu;
+----------+------+------+----------+
| stuname | ch | math | 是否通过 |
+----------+------+------+----------+
| 张秋丽 | 80 | NULL | 不通过 |
| 李文才 | 77 | 76 | 通过 |
| 李斯文 | 55 | 82 | 不通过 |
| 欧阳俊雄 | NULL | 74 | 不通过 |
| 诸葛丽丽 | 72 | 56 | 不通过 |
| 争青小子 | 86 | 92 | 通过 |
| 梅超风 | 74 | 67 | 通过 |
| Tom | 65 | 67 | 通过 |
| Tabm | 88 | 77 | 通过 |
+----------+------+------+----------+
9 rows in set (0.00 sec)
