03-MySQL中的单行处理函数和多行处理函数

数据处理/单行处理函数

字段名既可以直接进行数字运算也可以被处理函数处理,如函数(字段名) ,即对该字段中所有的数据进行相应处理,处理结果可能是一个输出也可能是多个输出

• 单行处理函数：一个输入对应一个输出
• 多行处理函数：多个输入对应一个输出

以下函数支持嵌套使用

函数	功能
Lower()	转换小写
upper()	转换大写
substr(被截取的字符串,起始下标 , 截取的长度)	取子串
concat()	进行字符串的拼接
length()	取长度
trim()	去空格 , trim可以将传过来的数据中的首尾空格去掉，不会去除中间的空格
str_to_date (日期字符串，匹配格式)	将字符串转换成指定的日期格式
date_format(字段, ‘%Y-%m-%d %H:%i:%s’)	将字段的日期转换为指定格式
format(字段,保留小数位的个数)	设置千分位和保留小数位的个数,0表示不保留,1表示保留一位小数,-1表示保留到十位
round(字段,保留小数位的个数)	四舍五入,0表示不保留,1表示保留一位小数,-1表示保留到十位
rand()	默认生成[0,1)以内的随机数,可以进行运算
ifnull(原数据,将数据当成哪个值)	如果原数据为NULL(没有值)的时候把这个数据转换成一个具体值 在 SQL 语句当中若有 NULL 值参与数学运算，计算结果一定是 NULL, 为了防止计算结果出现 NULL，建议先使用 ifnull空值处理函数预先处理

日期格式的说明

%Y：代表 4 位的年份 
%y：代表 2 位的年份 
%m：代表月, 格式为(01……12)  
%c：代表月, 格式为(1……12) 
%H：代表小时,格式为(00……23)  
%h： 代表小时,格式为(01……12)  
%i： 代表分钟, 格式为(00……59) 
%r：代表 时间,格式为 12 小时(hh:mm:ss [AP]M)  
%T：代表 时间,格式为 24 小时(hh:mm:ss) 
%S：代表 秒,格式为(00……59)  
%s：代表 秒,格式为(00……59) 

lower,upper,length,substr,trim

lower: 查询所有员工并将员工姓名全部转换成小写

select lower(ename) from emp; 

upper: 查询所有员工并将员工姓名全部转换成大写

select upper(ename) from emp;

substr: 查询员工姓名是以M开头所有的员工

# like模糊查询
select ename from emp where ename like 'M%';

# substr函数
select * from emp where substr(ename, 1, 1)=upper('m');

length: 查询员工姓名长度为5的所有员工

select length(ename), ename from emp where length(ename)=5; 

**trim: 查询工作岗位为manager且前后不含空格的所有员工 **

# 去除数据的前后空格
select * from emp where job=trim(lower('manager')); 

str_to_date,date_format,Format

str_to_date: 查询入职时间为1981-02-20的所有员工

# 第一种方法,与数据库的格式匹配上
select * from emp where HIREDATE='1981-02-20'; 

# 第二种方法，将字符串转换成date类型
select * from emp where HIREDATE=str_to_date('1981-02-20','%Y-%m-%d'); 
select * from emp where HIREDATE=str_to_date('02-20-1981','%m-%d-%Y'); 

date_format: 查询入职时间为1981-02-20 之后的所有员工,将入职日期格式化成yyyy-mm-dd hh:mm:ss

select empno, ename, date_format(hiredate, '%Y-%m-%d %H:%i:%s') as hiredate from emp; 

# now()表示获得当前时间 
select date_format(now(),'%Y-%m-%d %H %i %s'); 

Format: 查询所有员工的薪水并加入千分位,查询所有员工薪水加入千分位并保留两位小数

# 保留到整数位
select empno, ename, Format(sal, 0) from emp; 
# 保留两位小数
select empno, ename, Format(sal, 2) from emp;  

round,rand,ifnull

round: 保留数字的位数,select关键字后面即可直接跟字段名 也可以直接跟字面量/字面值

 # 表中的每一行都会返回该字面值
 select 'abc' as bieming from emp;
 # 1000也是被当做一个字面量/字面值
 select 1000 as num from emp; 
 # 默认保留到整数位
 select round(1236.567) as result from emp; 
 # 保留到整数位1237
select round(1236.567, 0) as result from emp; 
# 保留1位小数 , 
select round(1236.567, 1) as result from emp; 
# 保留到十位 240
select round(1236.567, -1) as result from emp; 

rand: 生成[0,1)以内的随机数

# 生成[0,1)以内的随机数
select rand(); 
# 生成100以内的随机数
select round(rand()*100,0) from emp; 
# 随机抽取记录数,order by必须写上
select * from emp order by rand() limit 2; 

ifnull: 计算每个员工的年薪,年薪 = (月薪 + 月补助) *12

# comm中的数据为NULL的时候,将comm中的数据当做0 
select ifnull(comm,0) from emp;
# 计算年薪
select empno,ename,sal,(sal+ifnull(comm,0))*12 as yearsal from emp; 

case..when..then ..else..end

case表示按照哪个字段匹配,when表示匹配的条件,then表满足条件后如何执行,else表示其他条件如何执行,end表示结束

不修改数据库只是修改查询结果,如将工作岗位为MANAGERG的薪水上涨10%,为SALESMAN的工资上涨50%,其他岗位薪水不变

select 
	ename,
	job, 
	sal as oldsal,
(case 
 	job 
 when 
 	'MANAGER' 
 then 
 	sal*1.1 
 when 
 	'SALESMAN' 
 then 
 	sal*1.5 
 else 
 	sal 
 end) as newsal 
from 
	emp;

分组/聚合/多行处理函数

多行处理函数的特点: 输入多行最终输出一行,同理经过多行函数处理后的字段可以直接跟在select关键字后面

• 分组函数在使用的时候必须先进行分组,如果你没有对数据进行分组,整张表默认被分为为一组即group函数默认存在
• 所有的分组函数会自动忽略NULL即不需要手动加where条件提前对NULL进行处理

组合聚合函数: 所有的分组函数可以组合起来一起用,如select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;

函数名	功能
count(字段/*)	统计记录数
sum(字段)	获取当前分组中某字段,null自动会被忽略
avg(字段)	获取当前分组中某字段的平均值
max(字段)	获取当前分组中某字段的最大值
min(字段)	获取当前分组中某字段的最小值

分组函数的特点

因为where条件在group by函数之前执行即当执行where的时候一定没有分组,所以在where关键字后面不能使用分组函数

-- 找出比最低工资高的员工信息
-- ERROR 1111 (HY000): Invalid use of group function
select ename,sal from emp where sal > min(sal);

因为select关键字在group by函数之后执行即当执行select的时候一定已经分完了组(默认整张表为一组),所以selecct后面可以出现分组函数

select min(sal) from emp;								

count

count(具体字段): 统计记录中某个字段下不为NULL(没有值)的记录总数

count(*): 统计表当中的总记录数,*表示所有字段即记录中只要有一个字段不为NULL则count++

查询所有员工数

select count(*) from emp; 

查询津贴不为null的员工数

-- 分组函数字段忽略null
select count(comm) from emp; 

sum

**取得薪水/津贴/薪水加津贴的合计 **

select sum(sal) from emp; 
-- 分组函数会自动忽略null 
select sum(comm) from emp; 
-- 在SQL语句当中若有NULL值参与数学运算结果一定是NULL,但分组函数会自动忽略null 
select sum(sal+comm) from emp; 
-- 为了防止计算结果出现NULL，建议先使用ifnull空值处理函数将comm字段的null值转换成0 
select sum(sal+ifnull(comm, 0)) from emp; 

avg,max,min

**avg: 查询所有员工的平均薪水 **

select avg(sal) from emp; 

max: 查询所有员工的最高薪水,查询所有员工中的最晚入职时间

# 查询所有员工的最高薪水
select max(sal) from emp; 

# 查询所有员工的最晚入职时间
select max(str_to_date(hiredate, '%Y-%m-%d')) from emp; 

min:查询所有员工的最低薪水,查询所有员工中的最早入职时间

# 查询所有员工的最低薪水
select min(sal) from emp; 

# 查询所有员工中的最早入职时间
select min(str_to_date(hiredate, '%Y-%m-%d')) from emp;