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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL查询进阶——从函数到表连接的使用你还记得吗

一、MySQL函数的使用


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


1、单行函数


特点:每行数据都会产生一个对应的结果

表中有多少行,就会有多少行的结果

位置:select之后,from之前 或作为筛选条件

concat(…,列名,…,列名)

用来拼接多列的值


例如:以firstname+lastname的形式显示所有员工的姓名

select concat(first_name,last_name) as 全名 from employees

mod(值1,值2)

求值1和值2的取余结果 ,相当于数学运算中的 %(值1%值2)


标准SQL规范中不存在%取余的写法,所以取余操作应该使用mod()

例如:select mod(10,3) from dual

注: dual为虚拟表,作用为使当前SQL符合语法规范,不写时会做自动填充

length(列名|数据)

获取长度


查询所有firstname长度>6的员工信息

select * from employees where length(first_name)>6

now() | sysdate()

获取系统当前时间


select now()

select SYSDATE()

select now() from dual


2、多行函数(组函数)


以组为单位获取结果,一组得到一个结果

如果未手动进行分组,则默认整张表为一组

sum(列名)

求某一列的总和

avg(列名)

求某一列的平均值

max(列名)

求某一列的最大值

min(列名)

求某一列的最小值

count(列名)

求某一列值的个数(非空)

示例:


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


二、MySQL的分组


在MySQL中一个组就是一个操作单位

简单分组

select 列名 from 表名 group by 列名

以部门为单位求每个部门的平均薪资

select department_id,AVG(salary) 平均薪资 from employees group by department_id

where + 分组


-- 查询部门id为10、20、30的部门的平均薪资、
select department_id,AVG(salary) 平均薪资 
from employees
where department_id in(10,20,30) -- 先筛选
GROUP BY department_id -- 后分组查询


having + 分组


select department_id,AVG(salary) 平均薪资 
from employees
group by department_id -- 先分组
having department_id in(10,20,30)  -- 后筛选


分组中 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一定最后执行


查询的执行顺序:


语法顺序  执行顺序


select    5
from    1
where   2
group by  3
having    4
order by  6
limit   7


四、子查询


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


1、where子查询


1.1、单值子查询


嵌套的子SQL只会返回一行一列的结果

-- 查询员工id为100的员工的部门id

select 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 ORDER BY salary desc LIMIT 10
-- 子查询:再根据子SQL的查询结果临时通过别名构建一张数据表进行查询
select avg(salary) from (select salary from employees ORDER BY salary desc LIMIT 10) as e


五、表连接


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

表连接的前提:


多张表之间必须存在关联关系(外键)

外键通常连接的是另一张表的主键

在进行表连接时作为连接条件使用


1、内连接

语法:


select 表1.列名,表2.列名,... 
from 表1 inner join 表2
on 连接条件


特点:对两张表同时进行约束,只有当所有表都符合连接条件,才会显示信息

使用:inner可省


2、(左)外连接

语法:


select 表1.列名,表2.列名,... 
from 表1(左表) left outer join 表2(右表)
on 连接条件


特点:只对右表做约束,左表中的数据都会显示,右表中只有符合连接条件的才会显示

使用:outer可省

左外连接和右外连接就是left和right的不同,位置不同,通常用左外连接

使用:

-- 查询所有的员工信息及符合连接条件的部门信息


select e.*,d.*
from employees e LEFT JOIN departments d
ON e.department_id=d.department_id


全外连接:


将两个查询结果进行合并显示

查询语句1 union 查询语句2

使用:


-- 内连接
select e.*,d.*  
from employees e inner join departments d
on  e.department_id=d.department_id
union -- 对内连接和左连接的查询结果进行合并
-- 左连接
select e.*,d.*
from employees e LEFT JOIN departments d
ON e.department_id=d.department_id


union会对合并结果进行去重

union all不会对结果去重

特点:合并双方都不做约束

合并双方的查询结果字段的个数、顺序必须一致

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


3、自连接


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

具体使用:


表中的某两个字段之间存在关联关系


-- 查询:员工id、员工姓名-firstname、直接领导的id、直接领导的姓名
select e1.employee_id 员工id,e1.first_name 员工姓名,e2.employee_id 领导id,e2.first_name 领导姓名
from employees e1 LEFT JOIN employees e2-- e1代表获取员工信息的表,e2代表获取领导信息的表
ON e1.manager_id=e2.employee_id -- 把两个有关联关系的字段作为连接条件


判断同一字段,作为连接条件


-- 查询工资相同的员工信息
select e1.employee_id 员工1的id,e1.salary 员工1的工资,e2.employee_id 员工2的id,e2.salary 员工2的工资
from employees e1 LEFT JOIN employees e2
on e1.salary=e2.salary -- 连接条件
where e1.employee_id<e2.employee_id -- 防止判断的双方是同一个人,并去重
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5天前
|
存储 关系型数据库 MySQL
提高MySQL查询性能的方法有很多
提高MySQL查询性能的方法有很多
42 6
|
5天前
|
存储 关系型数据库 MySQL
提高MySQL的查询性能
提高MySQL的查询性能
24 4
|
1天前
|
关系型数据库 MySQL 数据库
mysql的查询
MySQL数据库查询语句的基本用法,包括基础查询、条件查询、排序、分组查询、聚合函数的使用以及分页查询的方法。
8 2
|
23小时前
|
缓存 关系型数据库 MySQL
MySQL 满足条件函数中使用查询最大值函数
MySQL 满足条件函数中使用查询最大值函数
9 1
|
27天前
|
SQL 缓存 关系型数据库
MySQL高级篇——关联查询和子查询优化
左外连接:优先右表创建索引,连接字段类型要一致、内连接:驱动表由数据量和索引决定、 join语句原理、子查询优化:拆开查询或优化成连接查询
MySQL高级篇——关联查询和子查询优化
|
11天前
|
存储 关系型数据库 MySQL
MySQL索引失效及避免策略:优化查询性能的关键
MySQL索引失效及避免策略:优化查询性能的关键
75 3
|
13天前
|
关系型数据库 MySQL 数据库
MySQL 表的CRUD与复合查询
【9月更文挑战第26天】本文介绍了数据库操作中的 CRUD(创建、读取、更新、删除)基本操作及复合查询。创建操作使用 `INSERT INTO` 语句插入数据,支持单条和批量插入;读取操作使用 `SELECT` 语句查询数据,可进行基本查询、条件查询和排序查询;更新操作使用 `UPDATE` 语句修改数据;删除操作使用 `DELETE FROM` 语句删除数据。此外,还介绍了复合查询,包括连接查询(如内连接、左连接)和子查询,以及聚合函数与分组查询,并提供了示例代码。
|
16天前
|
关系型数据库 MySQL 数据库
Python MySQL查询返回字典类型数据的方法
通过使用 `mysql-connector-python`库并选择 `MySQLCursorDict`作为游标类型,您可以轻松地将MySQL查询结果以字典类型返回。这种方式提高了代码的可读性,使得数据操作更加直观和方便。上述步骤和示例代码展示了如何实现这一功能,希望对您的项目开发有所帮助。
52 4
|
17天前
|
JSON 关系型数据库 MySQL
MySQL 8.0常用函数汇总与应用实例
这些函数只是MySQL 8.0提供的众多强大功能的一部分。通过结合使用这些函数,你可以有效地处理各种数据,优化数据库查询,并提高应用程序的性能和效率。
20 3
|
19天前
|
NoSQL 关系型数据库 MySQL
当Redis与MySQL数据一致性校验中Redis数据量小于MySQL时的全量查询处理方法
保持Redis和MySQL之间的数据一致性是一个需要细致规划和持续维护的过程。通过全量数据同步、建立增量更新机制,以及定期执行数据一致性校验,可以有效地管理和维护两者之间的数据一致性。此外,利用现代化的数据同步工具可以进一步提高效率和可靠性。
40 6