MySQL-函数
1．数字函数
|
函数名 |
含义 |
用法1 |
用法2 |
|
Rand() |
返回0-1随机数 |
Select * from emp order by rand();随机排序 |
Rand(m)种子数，随机又固定的一个数字 |
|
Pi(x) |
圆周率 |
Select pi(),pi()+0.00000000000;扩展小数位数 |
|
|
Abs(x) |
绝对值 |
ABS(-5)=5；ABS(0)=0 |
|
|
Ceil(ceiling)(x) |
往上取整 |
CEIL(1.25)=2；CEILING(-5.98)=-5 |
|
|
Floor(x) |
向下取整 |
FLOOR(1.25)=1；FLOOR(-5.98)=-6 |
|
|
Round(n,m) |
四舍五入 |
ROUND(1.25)=1；ROUND(-5.98)=-6 |
Round(1.25,1)=1.3 |
|
Truncate(n,m) |
截断 |
TRUNCATE(-5.98,1)=-5.9 |
TRUNCATE(-1.25,0)=--1 |
|
Power/pow(m,n) |
幂次方 |
POW(3,3)=27；POWER(-1.5,2)=2.25 |
POWER(-2,-1)=-0.5 |
|
Sqrt(x) |
平方根 |
SQRT(9)=3；SQRT(-9)=null |
|
|
Least() greatest() |
返回最小值 最大值 |
select least(2,5,6,5); select greatest(2,5,6,5); |
在2、5、6、5中哪个数最小， 哪个最大？ |
RAND():返回 0-1 之间的随机数
例1：随机生成一个三位数的随机数：select floor(rand()*900+100);
select floor(rand()*999);//错误，rand()是介于0到1的数，如果rand()小于0.1，则此式子就不满足三位数
例2：求90°正弦的值： Select Sin((90/180)*pi());
例3：求e^2自然常量： Select Exp(2)
例4：求以10为底，100的对数的值： Select log10(100);
例5：查询3是否在4、5、6中： Select 3 in (4,5,6);
例6：查询3是否在5和9之间： Select 3 between 5 and 9;
2.字符串函数
|
函数名 |
含义 |
用法1 |
用法2 |
|
Char_length() |
长度 |
CHAR_LENGTH('abc')=3； CHAR_LENGTH('1.234')=5 |
CHAR_LENGTH('中国')=2 |
|
Length() |
长度 |
LENGTH('abc')=3，LENGTH('1.234')=5 |
LENGTH('中国')=4 |
|
Concat CONCAT_WS() |
拼接 |
CONCAT_WS (‘-‘,123,'abc','中国') = 123-abc-中国 |
CONCAT(123,'abc','中国') = 123abc中国 |
|
Lower/upper() |
大小写 |
LOWER('ABC'),UPPER('hello') |
|
|
Trim/ltrim/rtrim |
去空格 |
trim(' a b ') =a b；ltrim(' a b ')=a b |
rtrim(' a b ')= a b |
|
Left/right |
截取 |
LEFT(ename,3)只取前3个字符 |
|
|
Substr/substring |
精确截取 |
Substr(‘ABCD’,2,2)=BC |
Substr(str,start,length) |
|
Lpad/rpad |
填充函数 |
RPAD(‘ABC’,5,'*')=ABC** |
LPAD(str, length,填充物) |
|
Replace |
替换 |
replace('ABCA','A','XX')= XXBCXX |
|
|
Insert |
精确替换 |
Insert(‘ABCDE’,1,2,’XXXX’)= XXXXCDE |
|
|
REVERSE(str) |
字符反转 |
返回字符串 str ，顺序和字符顺序相反 |
REVERSE(‘abcd’)=dcba |
|
Strcmp |
比较 |
STRCMP(1,2)=-1; STRCMP(1,1)=0；STRCMP('A','a')=0； STRCMP('A','B')=-1； |
STRCMP('AccA','AC')=1；STRCMP('Acc','AC')=1；STRCMP('AccB','AC')=1 |
|
Locate |
字符串 定位函数 |
LOCATE('1','我们1大家'=3 LOCATE(1,'我们1大家')=7 LOCATE('家','我们大家')=4 |
LOCATE(12,113321)=0 LOCATE('CB','ABCD')=0
|
Null参与的运算，返回的结果都是null
CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。 第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。
字符串比较函数 strcmp，相同返回 0，前者>后者 返回1，其余返回-1。
例1：比较 aa 与bb 大小： select strcmp('aa','bb');
例2：用首字母大写，其它字母小写的方式 显示雇员的ename
SELECT concat(LEFT(UPPER(ename),1),SUBSTR(LOWER(ename),2,LENGTH(ename)-1)) from emp;
3.系统函数和加密函数
|
函数 |
含义 |
|
|
|
Version() |
当前数据库版本 |
SELECT VERSION(),DATABASE(),USER(); VERSION() | DATABASE() | USER() 5.0.22 | NULL | root@localhost |
|
|
Database() |
当前数据库名 |
||
|
User() |
当前用户名 |
||
|
[old_]Password() |
安全HASH算法加密 |
以 40 位字符串的形式显示出来的 |
|
|
Encode/Decode |
Encode：针对blob字段进行加密； Decode：解密 |
||
|
Md5() |
Md5算法加密 |
|
|
|
Sha() |
Sha算法加密 |
|
|
在MySQL 5.7版本后，把以下函数给删除了
Old_password：和password()一样，但是效率差
Encode：针对blob字段，加密
Decode：解密
select encode('root',''),decode('root','');
4．日期和时间函数
|
函数 |
含义 |
显示结果 |
|
|
Sysdate() |
获取系统时间 |
|
|
|
Curdate() |
获取当前日期 |
2020-03-05 |
|
|
Now() |
获取当前日期时间 |
2020-03-05 16:28:39 |
|
|
Curtime() |
获取当前时间 |
16:28:39 |
|
|
Utc |
世界协调时间 |
UTC_TIME\ UTC_DATE\ UTC_TIMESTAMP |
|
|
Unix |
|
|
|
例1：SELECT UTC_DATE(),UTC_TIME(),UTC_TIMESTAMP;
例2：UNIX 时间和当前时间的转换
SELECT FROM_UNIXTIME(1),UNIX_TIMESTAMP(NOW());
1）时间提取函数
year()，month()，day()，hour()，minute()，second()
SELECT year(NOW()),month(NOW()),day(NOW()),hour(NOW()),
minute(NOW()),second(NOW());
2）Dayofweek()，dayofmonth()，dayofyear()
dayofweek(now()) 获取当前日期是一周的第几天
SELECT DAYOFWEEK(NOW()),DAYOFMONTH(NOW()),DAYOFYEAR(NOW());
例题：
返回2017-4-1第几周： Select week('2017-4-1'); //值：13
返回当前时间是星期几：select dayofweek(now()); //1表示星期天，2表示星期一…
返回2017-4-6年当中的第几天：Select dayofyear('2017-4-6'); //值：96;
返回2017-4-1时间的季度：Select quarter('2017-4-1'); //值：2
时间10:20:30转化为秒：Select time_to_sec('10:20:30'); //值：37230
3）日期的算法
a). Date_add(时间,interval 数字 时间单位)
范例：SELECT Date_add(NOW(),interval 1 day),Date_add(NOW(),interval 10 YEAR);
b). datediff(日期1, 日期2)返回两个日期之间的天数
SELECT DATEDIFF('2018-07-01','2018-07-04') // -3
c). TIMESTAMPDIFF(时间单位,时间1,时间2)
计算两个时间之间相差的整数时间单位(FRAC_SECOND毫秒，SECOND秒，MINUTE分钟，HOUR小时，DAY天，WEEK星期，MONTH月，QUARTER季度，YEAR年)
例：TIMESTAMPDIFF(MONTH,hiredate,NOW()) //查询员工工作到今的月数
d). DATE_FORMAT(date,format)
DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据
DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p') //Dec 29 2008 11:45 PM
DATE_FORMAT(NOW(),'%m-%d-%Y') //12-29-2008
DATE_FORMAT(NOW(),'%d %b %y') //29 Dec 08
DATE_FORMAT(NOW(),'%d %b %Y %T:%f') //29 Dec 2008 16:25:46.635
-- 当前月的最后一天 select last_day(curdate());
-- 下月第一天 select date_add(last_day(curdate()), interval 1 day);
-- 当天为当月的第几天 select day(curdate());
-- 当月第一天 select date_add(curdate(), interval 1-(day(curdate())) day);
between '2018-07-01' and '2018-07-04' //结果是1号到3号的数据，这是因为时间范围显示的实际上只是‘2018-07-01 00:00:00’到’2018-07-04 00:00:00’之间的数据
5. 条件判断函数
If(expr,v1,v2)
如果expr表达式为真返回v1，否则返回v2
SELECT IF(2>1,2,1); //2
Ifnull(expr,v1)
如果expr表达式为null返回v1，否则返回expr表达式的值
SELECT IFNULL(2/1,'假'),IFNULL(2/0,'假'); // 2 假
Case..when
SELECT
CASE
WHEN 1>2 THEN 1
WHEN 2>3 THEN 2
ELSE 0
END; //0
SELECT
CASE 'A'
WHEN 'B' THEN 1
WHEN 'A' THEN 2
ELSE 0
END; //2
作者：kerwin-chyl
文章链接：https:////www.cnblogs.com/kerwin-chyl
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利
