mysql常用函数

MYSQL常用函数

一、单行函数

1.1 字符函数(String)

length(str)：统计字符串的字节数（取决于编码方式，utf8汉字3字节，gbk汉字2字节）
select length(’ abcdefg ')
select * from emp where length(ename)=5

concat(str1, str2)：拼接字符
select concat(‘aaa’,‘bbb’)
select concat(empno,concat(‘_’,ename)) from emp

substr/substring(str, n1 [,n2])：切割字符，n1起始位置（mysql下标从1开始），n2可选，表示切割长度
select substr(‘I like java!’,3)
select substr(‘I like java!’,3 , 1) – 第二个参数表示截取几个
select substring(‘I like java!’ ,3 , 4)
select substr(ename,1,3) from emp

instr(str1, str2)：返回str2在str1中首次出现的位置；如果没有找到，则返回0。不区分大小写。
select instr(‘i like java’,‘java’)
select instr(‘i like java’,‘b’)
select * from emp where instr(ename,‘A’)=0

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
select trim(’ aaa ') – 去除两边的空格
select trim(LEADING ‘x’ from ‘xxxaaaxxx’) – 去除左边的空格
select trim(TRAILING ‘x’ from ‘xxxaaaxxx’) – 去除右边的空格
select trim(BOTH ‘x’ from ‘xxaxaaaxaxx’) – 去除两边的空格

upper(str)：字母变大写
select upper(‘aaaa’)

lower(str)：字母变小写
select lower(‘BBBB’)

lpad(str1,length,str2)。其中str1是第一个字符串，length是结果字符串的长度，str2是一个填充字符串。如果str1的长度没有length那么长，则使用str2填充；如果str1的长度大于length，则截断。
select lpad(‘lifan’, 10 , ’ ')
select lpad(ename,10,’ ') from emp

rpad(str1, length, str2)：参考lpad
select rpad(ename,10,’ ‘),length(rpad(ename,10,’ ')) from emp

replace(object,search,replace)：把object对象中出现的的search全部替换成replace。
select replace(‘i like java’,‘a’,‘A’)

1.2 数学函数(Math)

round(num, 2)：四舍五入，保留两位小数
select round(12.5678,2)

ceil(num)：向上取整
select ceil(-10.1)

floor(num)：向下取整
select floor(-9.1)

truncate(num, 2)：从小数点后两位直接截断，比如truncate(3.1415926, 3) = 3.141
select truncate(12.5698,2)

mod(num, 2)：求余数 num % 2
select mod(10,3)

1.3 日期函数(Date)

now()：当前系统时间，1997-06-03 19:23:12

select now()
select SYSDATE()

curdate()：当前系统时间的日期，1997-06-03
select curdate()

curtime()：当前系统时间的时间，19:23:12
select curtime()

year(x)：年份，1997
select year(hiredate) from emp
select * from emp where year(hiredate)=1981

month(x)：月，6
select * from emp where month(hiredate)=2
select month(now())

monthname()：英文形式，June
select monthname(now())

day()：日， 3
select day(now())

hour()：小时， 19
select hour(now())

minute()：分钟， 23
select minute(now())

second()：秒， 12
select second(now())

– 字符串转日期
str_to_date(“1997-06-03 19:23:12”, “%Y-%m-%d %H:%i:%s”)：-- 通过后面的模式将前面的字符串进行转换成date格式
select str_to_date(‘2020-01-01’,‘%Y-%m-%d’)
insert into emp(empno,hiredate) values(1111,‘2022-01-01’)
select * from emp

– 日期转字符串
date_format(‘1997/6/3’, ‘%Y年%m月%d日’)：将日期转为字符，1997年6月3日
select DATE_FORMAT(‘2022-01-01’,‘%Y年%m月%d日’)

datediff(date1, date2) – 获取2个日期之间的差 单位:天
select DATEDIFF(‘2020-03-01’,‘2020-02-01’)
select DATEDIFF(‘2022-09-08 15:34:20’,‘2022-09-08 00:00:00’)
select now()-STR_TO_DATE(‘2020-01-01’,‘%Y-%m-%d’)

1.4 控制函数

if：类似于三元运算符
case：
第一种情况：类似于switch…case…

1.5 其他函数
version：版本
database：当前数据库
user：当前用户

二、分组函数/聚合函数
功能：用作统计使用，又称为聚合函数或统计函数或组函数。
– 单组
select max(sal) from emp – 最大
select min(sal) from emp – 最小
select sum(sal) from emp – 总和
select avg(sal) from emp – 平均
select count(*) from emp – 记录行
select count(comm) from emp – 统计记录行字段为null则不统计
select count(distinct job) from emp – distinct 去除重复数据

– 多组 group by 分组字段
select 分组字段,聚合函数 from 表 group by 分组字段

– 示例 : 按部门统计每个部门的最高工资最低工资

select deptno,max(sal),min(sal) from emp group by deptno

– 示例 : 安装工作类别统计每个工作类别的平均工资

select job,avg(sal) from emp group by job

– 示例 : 统计每个部门有多少人

select deptno,count(*) from emp group by deptno

– 聚合函数作为查询条件不能写在where后的 必须写到having 后

– 示例 : 统计每个部门的平均工资，查询那些超过2000的部门

select deptno,avg(sal) from emp group by deptno having avg(sal)>2000

select * from (select deptno,avg(sal) avgsal from emp group by deptno) a where a.avgsal>2000

– 排序 order by 排序字段 [asc默认/desc]

select * from emp order by sal desc,empno desc

– sql书写顺序

select * from 表 where 条件 group by 分组 having 分组条件 order by 排序

– 别名 不能直接用于查询条件(子查询可以的) 但是可以用于排序

select empno 编号,ename 姓名,job 工作 from emp order by 编号 desc

select * from (select empno 编号,ename 姓名,job 工作 from emp) a where a.编号=7788

– 示例 统计男女人数

select sex,count(*) from student group by sex

– 示例 统计名称重复的有哪些(统计重复数据)

select name,count(*) from student group by name having count(*)>1

1、查询出部门编号为30中的所有雇员信息。

select * from emp where deptno=30

2、列出所有办事员(CLERK)的姓名、编号和部门。

select ename,empno,deptno from emp where job='CLERK'

3、找出佣金高于薪金的雇员

select * from emp where comm>sal

4、找出佣金高于薪金60%的雇员

select * from emp where comm>sal*0.6

5、找出部门10中所有经理和部门20中所有办事员的详细资料

select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK')

6、找出部门10中所有经理、部门20中所有办事员以及既不是经理又不是办事员但其薪金大于或等于2000的所有雇员的详细资料

select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK') or
(job!='MANAGER' and job!='CLERK' and sal>=2000)

7、找出收取佣金的雇员的不同工作

select distinct job from emp where comm is not null

8、找出不收取佣金或收取的佣金低于100的雇员

select * from emp where comm is null or comm<100

9、找出名称中包含“A”的所有雇员

select * from emp where ename like '%A%'
select * from emp where instr(ename,'A')!=0

10、找出薪资在2000-3000之间的雇员

select * from emp where sal>=2000 and sal<=3000
select * from emp where sal between 2000 and 3000