MySQL常用函数复习

开发过程中,sql函数建议大写提高可读性和可维护性,以下均小写只是方便阅读记忆(大写英文有时看不懂)

常见sql语句中聚合函数有count统计数量、max最大值、min最小值、avg平均值、sum求和，除这些常见的函数之外还有：

1.字符串函数

1.select concat("1","2","3","4"),将多个字符串拼接合并成一个字符串

可将人的姓名与昵称拼接起来

2.select char_length("1234"),返回字符串的字符数

可用于统计人名几个字组成

3.select length("1234"),返回字符串的字节数

4.select upper("abcd"),将字符串转换为大写

5.select lower("ABCD"),将字符串转换为小写

6.select locate('cd','abcde'),从字符串abcde中获取cd位置,位置从1开始,也就是返回3

7select trim("a     bc   "),字符串去除空格

8.select replace('abc','a','*'),字符串abc中a被替换成*,即返回*bc

9.select substr("abcde",2,3),字符串abcde中,从2开始截取(从1开始计数),截取长度为3的字符串,即返回bc

可用于截取人名中的姓氏

2.日期函数

1.now()\curdate()\curtime();

获取系统当前日期时间\日期\时间

select now();

2.year(date)\month(date)\day(date)

从日期中获取年月日

select year(now());

3.last_day(date)

返回月份的最后一天

select last_day(now());

4.adddate(date,n)/subdate(date,n)

计算起始日期date加减n天的日期

select adddate(now(),10);

5.quarter(date)

返回日期date是第几季度,1到4

select quarter(now())

6.datediff(d1,d2)

计算日期d1->d2之间相隔的天数

select datediff('2024-02-06','2023-12-01');

数据库中有员工入职日期,可用于统计员工距今的入职天数,/365可计算员工的工龄,where datediff(now(),joindate)/365>10即可条件查询10年工龄以上员工

7.date_format(d,f)

按照表达式f显示日期d

select date_format(now(),'%Y-%m-%d')这里年月日区分大小写,含义不一样

where date_format(joindate,"%Y") = '2023',可截取年份统计2023年入职的员工

select * from emp where datediff(now,joindate)>365 and date_format(jiondate,"%m")='02'查找工龄超过10年且2月份入职的员工信息

select * from emp where date_formate(joindate,"%Y") =(select date_formate(jiondate,"%Y") from emp where ename = '张三') 查找与张三同一年入职的员工子查询

3.数字函数

1.abs(x)

返回x的绝对值

select abs(-1)

2.ceil(x)/floor(x)

向上向下取整

select ceil(1.5)

3.mod(x,y)

取余数

select mod(5,4)

4.rand()

返回0到1的随机数

select rand()

5.round(x)

四舍五入

select round(3.1415926)

统计工龄时超过半年为一年round(datediff(now(),joindate)/365)

6.truncate(x,y)

返回数值x保留到小数点后y位的值

select truncate(3.1415926,3)

统计部门平均薪资保留两位小数select dept_id,avg(salary),truncate(avg(salary),2) from emp group by dept_id,薪资也可以ceil向下取整,floor向上取整

4.高级函数

1.select if(布尔表达式,为真时的值,为假时的值) from 表名

select ename,salary+ifnull(bonus,0),if((salary+ifnull(bonus,0))>20000,"有钱人","穷B") from emp

这里ifnull表示有奖金就加没奖金就用0代替null,null不能做数学运算

2.select

        case[字段]

               where 判断条件1 then 希望得到的值1

               where 判断条件n then 希望得到的值n

               else 前面条件都没有满足情况下得到值

        else

   from

          表名