mysql中常用函数之分组,连接查询功能实现-kb88凯时官网登录

函数

在mysql中函数是一组预定义的指令,用于执行特定的操作并返回结果,可类比java中的方法.在sql中函数根据其作用范围和返回结果方法分为两大类:单行函数,分组函数

单行函数

单行函数的特点为对一行数据进行操作,并只返回一种结果.单行函数通常用于处理单个记录数据

• 单行函数又可分为:字符函数,数学函数,其他函数,流程控制函数

字符函数

• char_length(s),length(s):返回字符串的长度

eg:查询员工姓名，姓名字数。

select emplyee_name,character_length(emplyee_name) from emplyees;

• concat(s1,s2,…sn):将两个以上的字符串连接

eg:将字符串'aaa','bbb','ccc'进行拼接。

select concat('aaa','bbb','ccc');

• upper(),lower():对字符进行大小写转化

eg:：查询员工邮箱，并转为大写显示

select upper(email) from emplyees;

• substr,substring(s, start, length):提取字符串s从start位置开始,长度为length的字字符串

eg:提取hello world中的hello

select substr('hello world',1,5);`

• replace(s, old, new): 在字符串s中将所有的old替换为new

eg:查询员工电话号码，要求去除中间的横线 ’-’

select replace(phone_number, '-', '') from emplyees;

数学函数

round(x):对浮点数x进行四舍五入

eg:查询员工工资，和其四舍五入的整数值

select salary,round(salary) from employees;

ceil(x):对浮点数x向上取整,即返回≥x的最小整数

eg:查询员工工资，并且向上取整

select salary,ceil(salary) from employees;

floor(x):对浮点数x向下取整,即返回≤x的最大整数

eg:查询员工工资，并且向下取整

select salary,floor(salary) from employees;

truncate(x,length):对浮点数的小数部分进行截取→常用于进行保留小数操作

select truncate(1.9999,2);->1.99

• mod(x,y):对两个数进行区域操作即x%y

日期函数

• now(),sysdate():返回当前系统日期 时间
• curdate():返回当前系统日期,不包括时间
• curtime():返回当前系统时间,不包括日期
• date_format(date,format): 用于格式化日期,date是要格式化的数据,format是格式化的模式,格式化的通配符号如下表:

eg:查询员工姓名、入职时间，入职时间按照xxxx年xx月xx日输出

select date_format(hiredate,'%y年%m月%d日') from employees;

流程控制函数

流程控制函数在sql中根据条件选择性地返回不同的结果,其允许在查询过程中实现条件逻辑

• if(expr,true_val,false_val): 若表达式expr为真,则返回结果true_val,否则返回false_val的结果

eg: 如果查询的年纪大于18则返回adult,否则返回minor

select age,if(age>=18,'adult','minor');

• case语法结构:类似于switch…case结构,用于实现多支路条件选择

select exper1,exper2...,
case exper1
		 when value1 then result1
		 when value2 then result2
		 when value3 then result3
		 ...
		 else result
end
from table_name;

eg:根据查询的部门号返回部门名称

select department_id
case department_id
		 when 1 then '经理办公室'
		 when 2 then '财务部'
		 when 3 then '后勤部'
		 else 'unkown'
end as department_name
from departments;

• 搜索case:语法结构与朴素case类似,但搜索case可根据多种不同的表达式条件返回不同值

select exper1,exper2...,
case 
		 when condition 1 then result1
		 when condition 2 then result2
		 ...
		 else result
end 
from table_name;

eg:查询员工姓名以及工资，工资按照一定规则发放，入职时间在2015-01-01之前的员工工资*2，入职时间在2018-01-01之前的员工工资*1.5，其他不变

select 
employee_name,hiredate,salary 原工资,
case
    when hiredate<'2015-01-01' then salary*2
		when hiredate<'2018-01-01' then salary*1.3
		else salary
end as 新工资
from employees;

分组函数

分组函数也称为聚合函数,用于对一组值进行操作,并返回单个结构

• count(*):计算指定列中非null值的数量,sum(column):计算指定列之和,avg(column):计算指定列平均数,max(colum):取出指定列最大值,min(colum):取出指定列最小值

eg:查询所有员工工资总和、平均值、最大值、最小值、员工个数;

select sum(salary),avg(salary),max(salary),min(salary),count(*) from employees;

分组查询

分组查询可根据某个或某些列对数据进行分组

按单个字段分组

• 通过group by关键字:按指定列进行分组

select 列表
from 表
[where 筛选条件]
group by 分组
[order by 排序]

eg:查询每个部门的最高工资

select max(salary) 
from employees 
group by department_id;

在分组前进行条件筛选

在group by语句之间使用 where语句对查询结果降序筛选

eg:查询每个部门入职时间在2010-01-01之后，并且工资最高的员工信息

select *
from employees
where hiredate >'2010-01-01'
group by department_id;

在分组之后进行条件筛选

通过having语句可以在gruop by语句之后进行条件筛选

eg:查询员工人数大于120的部门

select *
from employees
group by department_id
having count(*)>120;

按多字段分组

• 在gruop by语句后可接多个字段实现多字段分组

eg:查询每个部门，男女员工的平均工资

select department_id,sex,avg(salary) as 平均工资
from employees
group by department_id,sex;

连接查询

连接查询是sql中十分重要的知识点,就有”连接不会,通宵也白搭”,连接查询也称为多表查询,用于将两个以上的表的数据基于某些相关条件组合在一起,通过连接查询,可以从表中提取数据,生成一个新的结果集

• 笛卡尔积现象:假设有两个表,表1有n行数据,表2有m行数据,在使用连接查询时会产生n*m条数据,因此在条件查询时需要假设连接的条件

内连接(inner join)

内连接用于返回两个表中所有满足连接条件的所有行数据,内连接可分为:等值连接,非等值连接,自连接

等值连接

等值连接是一种常见的连接方式,其基于两表中某一列的相等条件进行连接

其语法结构如下:

select colum1,colum2,....,
from table1 
inner join table2
on table1.colum = table.colum;

eg:查询员工姓名以及所在的部门名称

select employee_name as 员工名,department_name as 部门名
from employees e	
inner join departments d on 
e.department_id=d.department_id;

非等值连接

非等值连接基于两表中某一列的不等条件进行连接.如大于,小于,不等于等等

语法结构:

select colum1,colum2,....,
from table1 
inner join
on table1.colum  table2.colum;

其中operator可以是>,<,≥,≤,≠,between…and等等;

eg:查询员工工资及工资等级

select e.salary,j.grade_level
from employees e
inner join job_grades j
on e.salary between j.lowest_sal and j.higest_sal;

自连接

自连接是指同一个表的连接.这种连接通常用于处理表中有层次结构或函数递归关系的数据

eg:查询员工姓名以及对应的直系领导

select t1.employee_name as 员工,t2.employee_name as 领导
from employees t1
inner join employees t2
on t1.manager_id=t2.employee_id;

外连接

外连接用于返回主表中满足连接条件的行,同时保留另一个表中没有匹配的行

左/右外连接

• 左(右)连接顾名思义即根据表位置区分主表,即在左连接即左表,右连接即右侧

语法结构:

select colum1,colum2...,
from table1 [left|right]
join on [连接条件];

eg:查询员工姓名以及所在的部门名称，没有部门信息的员工也要查询出来

select employee_name as 员工姓名,department_name as 部门名称
from employees e left
join departments d
on e.department_id=d.department_id;