0


MySQL查询进阶——从函数到表连接的使用你还记得吗

✅作者简介:C/C++领域新星创作者,为C++和java奋斗中
✨个人社区:微凉秋意社区
🔥系列专栏:MySql一点通
📃推荐一款模拟面试、刷题神器👉注册免费刷题

🔥前言

书接上文,今天学习查询的进阶操作,包括函数、分组、分页、子查询和表连接。内容比较实用,建议收藏或者订阅专栏方便学习和复习。

文章目录

一、MySQL函数的使用

  • mysql中内置了很多函数,每个函数都代表一个特定功能

1、单行函数

  • 特点:每行数据都会产生一个对应的结果 - 表中有多少行,就会有多少行的结果
  • 位置:select之后,from之前 或作为筛选条件
  1. concat(…,列名,…,列名)用来拼接多列的值- 例如:以firstname+lastname的形式显示所有员工的姓名select concat(first_name,last_name) as 全名 from employees
  2. mod(值1,值2)求值1和值2的取余结果 ,相当于数学运算中的 %(值1%值2)- 标准SQL规范中不存在%取余的写法,所以取余操作应该使用mod()- 例如:select mod(10,3) from dual 注: dual虚拟表,作用为使当前SQL符合语法规范,不写时会做自动填充
  3. length(列名|数据) 获取长度- 查询所有firstname长度>6的员工信息 select * from employees where length(first_name)>6
  4. now() | sysdate() 获取系统当前时间- select now() select SYSDATE() select now() from dual

2、多行函数(组函数)

  • 以组为单位获取结果,一组得到一个结果
  • 如果未手动进行分组,则默认整张表为一组
  1. sum(列名) - 求某一列的总和
  2. avg(列名) - 求某一列的平均值
  3. max(列名) - 求某一列的最大值
  4. min(列名) - 求某一列的最小值
  5. count(列名) - 求某一列值的个数(非空)

示例:

-- 组函数操作salaryselectSUM(salary) 总和,AVG(salary) 平均值,MAX(salary) 最大值,min(salary) 最小值,COUNT(salary) 非空值的数量
from employees
-- 计算表中绩效列不为空的员工数量selectCOUNT(commission_pct)from employees
-- 求表中的行数selectCOUNT(*)from employees
selectCOUNT(employee_id)from employees

二、MySQL的分组

  • 在MySQL中一个组就是一个操作单位
  1. 简单分组select 列名 from 表名 group by 列名- 以部门为单位求每个部门的平均薪资select department_id,AVG(salary) 平均薪资 from employees group by department_id
  2. where + 分组
-- 查询部门id为10、20、30的部门的平均薪资、select department_id,AVG(salary) 平均薪资 
from employees
where department_id in(10,20,30)-- 先筛选GROUPBY department_id -- 后分组查询
  1. having + 分组
select department_id,AVG(salary) 平均薪资 
from employees
groupby department_id -- 先分组having department_id in(10,20,30)-- 后筛选
  1. 分组中 where 和 having 的区别 - where是在分组前执行,having是在分组后执行- where不能在分组时使用组函数,但是having可以- 当两者都可使用时,优先使用where,效率更高

三、分页以及查询关键字的执行顺序

  • limit:用来限制显示的查询结果条数,通常用于分页查询select 列名 from 表名 limit 显示的起始下标,显示条数- 查询所有员工数据的前十条select * from employees limit 0,10``````select * from employees limit 10下标为0时可以省略
  • 当limit和其他查询关键字联用时,limit一定最后执行

查询的执行顺序:

语法顺序        执行顺序
select5from1where2groupby3having4orderby6limit7

四、子查询

  • 当一个SQL需要借助于另一个SQL的执行结果,在当前SQL中嵌套另一个SQL,该写法称为子查询

1、where子查询

1.1、单值子查询

  • 嵌套的子SQL只会返回一行一列的结果
-- 查询员工id为100的员工的部门idselect department_id from employees where employee_id=100-- 子查询::查询员工id为100的员工所在的部门信息select*from departments 
where department_id=(select department_id from employees where employee_id=100)
  • 优先执行内层SQL,再执行外层SQL
  • 子SQL需写入小括号

1.2、多值子查询

  • 嵌套的子SQL会返回多个结果
--查询firstname中包含s的员工所在的部门信息select*from departments 
where department_id in(select department_id from employees where first_name like'%s%')

2、from子查询

  • 将子SQL的查询结果临时看做一张表进行后续操作
-- 获取薪资最高的前十个员工的平均薪资-- 先获取薪资最高的前十名员工的薪资select salary from employees ORDERBY salary descLIMIT10-- 子查询:再根据子SQL的查询结果临时通过别名构建一张数据表进行查询selectavg(salary)from(select salary from employees ORDERBY salary descLIMIT10)as e

五、表连接

  • 当查询结果需要从多张表中获取时,则需要将多张表连接起来进行查新操作

表连接的前提:

  1. 多张表之间必须存在关联关系(外键)
  2. 外键通常连接的是另一张表的主键
  3. 在进行表连接时作为连接条件使用

1、内连接

语法:

select 表1.列名,表2.列名,...from 表1innerjoin 表2on 连接条件
  • 特点:对两张表同时进行约束,只有当所有表都符合连接条件,才会显示信息
  • 使用:inner可省

2、(左)外连接

语法:

select 表1.列名,表2.列名,...from 表1(左表)leftouterjoin 表2(右表)
on 连接条件
  • 特点:只对右表做约束,左表中的数据都会显示,右表中只有符合连接条件的才会显示
  • 使用:outer可省
  • 左外连接和右外连接就是leftright的不同,位置不同,通常用左外连接
  • 使用:
-- 查询所有的员工信息及符合连接条件的部门信息select e.*,d.*from employees e LEFTJOIN departments d
ON e.department_id=d.department_id

全外连接:

  • 将两个查询结果进行合并显示 查询语句1 union 查询语句2
  • 使用:
-- 内连接select e.*,d.*from employees e innerjoin departments d
on  e.department_id=d.department_id

union-- 对内连接和左连接的查询结果进行合并-- 左连接select e.*,d.*from employees e LEFTJOIN departments d
ON e.department_id=d.department_id
  • union会对合并结果进行去重
  • union all不会对结果去重
  • 特点:合并双方都不做约束
  • 合并双方的查询结果字段的个数、顺序必须一致

实际开发中,使用频率较高的是内连接和左外连接

3、自连接

  • 概念:是特殊的表连接,参与连接的是同一张表

具体使用:

  1. 表中的某两个字段之间存在关联关系
-- 查询:员工id、员工姓名-firstname、直接领导的id、直接领导的姓名select e1.employee_id 员工id,e1.first_name 员工姓名,e2.employee_id 领导id,e2.first_name 领导姓名
from employees e1 LEFTJOIN employees e2-- e1代表获取员工信息的表,e2代表获取领导信息的表ON e1.manager_id=e2.employee_id -- 把两个有关联关系的字段作为连接条件
  1. 判断同一字段,作为连接条件
-- 查询工资相同的员工信息select e1.employee_id 员工1的id,e1.salary 员工1的工资,e2.employee_id 员工2的id,e2.salary 员工2的工资
from employees e1 LEFTJOIN employees e2
on e1.salary=e2.salary -- 连接条件where e1.employee_id<e2.employee_id -- 防止判断的双方是同一个人,并去重

写在最后

这篇博客算是MySQL里最重磅的内容了,希望大家可以妥善吸收并正确利用,一起加油!!!

本文转载自: https://blog.csdn.net/m0_58618795/article/details/126073173
版权归原作者 微凉秋意 所有, 如有侵权,请联系我们删除。

“MySQL查询进阶——从函数到表连接的使用你还记得吗”的评论:

还没有评论