目录
前言
书接上文，今天学习查询的进阶操作，包括函数、分组、分页、子查询和表连接。内容比较实用，建议收藏或者订阅专栏方便学习和复习。
一、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(列名)
求某一列值的个数(非空)
示例：
-- 组函数操作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中一个组就是一个操作单位
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) -- 先筛选 GROUP BY department_id -- 后分组查询
3.having + 分组
select department_id,AVG(salary) 平均薪资 from employees group by department_id -- 先分组 having department_id in(10,20,30) -- 后筛选
4.分组中 where 和 having 的区别
- where是在分组前执行，having是在分组后执行
- where不能在分组时使用组函数，但是having可以
- 当两者都可使用时，优先使用where，效率更高
三、分页以及查询关键字的执行顺序
1.limit：用来限制显示的查询结果条数，通常用于分页查询
select 列名 from 表名 limit 显示的起始下标,显示条数
查询所有员工数据的前十条
select * from employees limit 0,10 select * from employees limit 10 下标为0时可以省略
下标为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 -- 防止判断的双方是同一个人，并去重
