2、数据库的基础学习（中）：分组查询、连接查询 有小例子

二、分组函数

---

功能：用作统计使用，又称为聚合函数或者统计函数或组函数

1、分类：

sum 求和、avg 平均值、max最大值、min 最小值、count 计算个数

2、参数支持哪些类型

​ Sum\avg 一般处理数值型数据

​ max、min 可以数值型也可以字符型

​ count 基本上都可以处理

​ 是否忽略 null 值

​ sum 、avg、min、max、count （以上分组函数都忽略 null 值）忽略 null 值

3、可以和DISTINCT 搭配

4、count 函数的详细介绍

​ SELECT count（*） from employees 统计 employees 表里面的行数

​ select count（1） from employees 也是统计 employees 表中的行数

效率：MYISAM存储引擎下，count（*）效率高 INNODB 存储引擎下，两者效率接近

5、和分组函数一同查询的字段有限制

和分组函数一同查询的字段要求是 groups by 后的字段

小例题：

-- 查询员工表中的最大入职时间和最小入职时间的相差天数
select datediff(max(hiredate),min(hiredate)) as DIFFERENCE 
from employees;-- 查询部门编号为 90 的员工个数
select count(*)
from employees
where department_id=90;

其中 datediff()为求两日期间的差值的函数

---

三、分组查询

引入：查询每个部门的平均工资

语法：

​ SELECT 分组函数,列（要求出现在 group by 的后面）

​ from 表

​ 【where 筛选条件】

​ group by 分组的列表

​ 【order by 字句】

注意：查询列表必须特殊，要求是分组函数和 group by 后出现的字段

-- 查询邮箱中包含字符 a 的，每个部门的平均工资
select avg(salary),department_id
from employees
where email like '%a%'
group by department_id;-- 查询有奖金的每个领导手下员工的最高工资
select max(salary),manager_id
from employees
where commission_pct is not NULL
GROUP BY manager_id;-- 添加复杂的筛选条件-- 案例一 查询哪个部门的员工个数＞2
-- 1️⃣查询每个部门的员工个数
SELECT count(*),department_id
from employees
GROUP BY department_id;-- 2️⃣根据1️⃣的结果进行筛选，查询哪个部门的员工个数大于 2  使用 having 进行连接
SELECT count(*),department_id
from employees
GROUP BY department_id
having count(*)>2;-- 案例二：查询每个工种有奖金的员工的最高工资大于 12000 的工种编号和最高工资
select job_id,max(salary)
from employees
where commission_pct is not null
GROUP BY job_id
having max(salary)>12000 ;-- 案例三：查询领导编号>102 的每个领导手下的最低工资＞5000 的领导编号，以及其最低工资
SELECT min(salary),manager_id
from employees
where manager_id>102
group by manager_id
having min(salary)>5000;
-- 注意这个 group by 别顺手写出 order by了 不然找半天都找不到问题 作者在 navicat 一阵快捷键 弄错了没发现 找半天才找到问题-- 按多个字段分组查询
-- 案例：查询每个部门每个工种的员工的平均工资
select avg(salary),department_id,job_id
from employees
GROUP BY department_id,job_id;

特点：

​ 1、分组查询中的筛选条件分为两类

​ 数据源 位置 关键字

​ 分组前筛选 原始表 group by 字句的前面 where

​ 分组后筛选 分组后的结果表 group by 字句的后面 having

分组函数做条件 肯定是放在 having 字句中

能用分组前筛选的，优先考虑使用分组查询前筛选

​ 2、group by 子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求），表达式或函数（用的较少）

​ 3、也可以添加排序 排序是放在整个分组查询的最后

---

四、连接查询

---

又称：多表查询，当查询到的字段来自多个表时，需要用到连接查询

笛卡尔乘积现象：表 1 有 m行，表 2 有 n 行，结果有 m*n 行

​ 发生原因：没有有效的连接条件

​ 如何 避免：添加有效的连接条件

分类：

​ 按年代分： sql 92 标准：仅支持内连接 ；； sql 99 标准（推荐）：内连接+外连接（左外和右外）+交叉连接

​

​ 按功能分类：

​ 内连接： 等值连接

​ 非等值连接

​ 自连接

​

​ 外连接：左外连接

​ 右外连接

​ 全外连接

​ 交叉连接

为表起别名：提高语句的简洁度； 区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

---

A、Sql92 内连接

#一、sql192 标准
#1、等值连接
-- 案例一、查询女神名和对应的男神名
use girls;
select name,boyName
from beauty,boys
where beauty.boyfriend_id=boys.id;-- 案例二、查询员工名和对应的部门名
use myemployees;
select last_name,department_name
from employees,departments
where employees.department_id=departments.department_id;-- 查询城市名中第二个字符为 o 的部门名和城市名
select department_name,city
from departments d,locations l
where d.location_id=l.location_id
and l.city like '_o%';-- 可以加分组不？？？
-- 案例： 查询每个城市的部门个数
SELECT count(*) 个数,city 
from departments d,locations l
where d.location_id=l.location_id
group by city;-- 案例二：查询有奖金的每个部门的部门名和部门的领导编号以及该部门的最低工资
select department_name,e.manager_id,min(salary)
from departments d,employees e
where d.department_id=e.department_id
and e.commission_pct is not null
group by d.department_name,e.manager_id;#2、非等值连接
-- 案例：查询员工的
SELECT salary,grade_level
from employees e,job_grades j
where  salary between j.lowest_sal and j.highest_sal;#3、自连接
#案例：查询 员工名和上级的名称
select e.employee_id,e.last_name,m.manager_id,m.last_name
from employees e,employees m
where  e.employee_id=m.manager_id;

---

B、sql99语法

语法：

​ select 查询列表

​ from 表1 别名 【连接类型】

​ join 表2 别名

​ on 连接条件

​ 【where 筛选条件】

​ 【group by 分组】

​ 【having筛选条件】

​ 【order by 排序】

分类

​ 内连接： 连接类型关键字 inner

​ 外连接：左外连接：left 【outer】（outer 可以省略）

​ 右外连接 right 【outer】

​ 全外连接：full 【outer】

​ 交叉连接：cross

sql99 的 内连接

# 二、sql 99 语法
/**/
#1、等值连接
-- 案例一：查询员工名、部门名
SELECT last_name,department_name
from employees e
inner JOIN departments d
on e.department_id=d.department_id;-- 案例二：查询名字中包含 e 的员工名和工种名（添加筛选）
select last_name,job_title
from employees e
inner join jobs j
on j.job_id=e.job_id
where e.last_name like '%e%';-- 案例三：查询部门个数>3 的城市名和部门个数 （添加分组和筛选）
-- 下面这个建议看看 自己做的时候老是有点问题
select city,count(*)
from departments d
INNER JOIN locations l
on l.location_id=d.location_id
GROUP BY city
HAVING count(*)>3;-- 案例四：查询哪个部门的员工个数>3 的部门名和员工个数，并按个数降序（添加排序）
select department_name,count(*) 员工个数
from departments
inner join employees
on departments.department_id=employees.department_id
GROUP BY department_name
having  count(*)>3
order by count(*) desc;-- 案例五：查询员工名、部门名、工种名，并且按照部门名称降序
-- 注意看三表链接的格式
select last_name,department_name,job_title
from employees
inner join departments on employees.department_id=departments.department_id
INNER JOIN jobs on employees.job_id=jobs.job_id
ORDER BY department_name desc;#2、非等值连接
-- 案例一：查询员工的工资级别
SELECT salary,grade_level
from employees e
INNER JOIN job_grades g
on e.salary between g.lowest_sal and g.highest_sal;
-- 案例二：查询每个工资级别的个数>20的个数，并且按工资级别降序
SELECT count(*),grade_level
from employees e
inner join job_grades g
on e.salary between g.lowest_sal and g.highest_sal
GROUP BY grade_level
having count(*)>20
order by count(*) desc;#3、自连接
-- 案例；查询员工的名字、上级的名字
select e.last_name,m.last_name
from employees e
inner join employees m
on e.manager_id=m.employee_id;

特点：1、添加排序、分组、筛选

2、inner可以省略

3、筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读

4、inner join连接与sql92的等值连接效果是一样的

 having一般用于有分组的筛选条件

sql 99 外连接

#二、外连接
-- 应用 场景：查询一个表中有，另一个表中没有的记录
/*特点：
1、外连接的查询结果为主表中的所有记录如果从表中有和它匹配的，则显示匹配的值如果从表中梅雨欧和它匹配的，则显示 null 值外连接查询结果=内连接结果+主表中有而从表中没有的记录
2、左外连接，left join 左边是主表
3、右外连接，right join 右边是主表
4、左外和右外交换两个表顺序，则可以实现同样的效果5、外连接：=内连接的结果+表 1 中有但表 2 没有的+表 2 中有但表 1 没有的
*/
-- 案例一： 查询男朋友不在男神表的女神名  （左外连接）
use girls;
select b.name
from beauty b
left outer join boys bo
on b.boyfriend_id=bo.id
where bo.id is null;-- 案例二：查询哪个部门没有员工
use myemployees;
select d.department_name,e.department_id
from departments d
left outer join employees e
on d.department_id=e.department_id
where e.employee_id is null;

sql99 交叉连接

#交叉连接  相当于笛卡尔乘积
SELECT b.*,bo.*
from beauty b
cross join boys bo;

子查询

# 子查询： 出现在其他语句中的select 语句 称为子查询或内查询
/*
分类：按子查询出现的位置：select 后面	： 仅仅支持标量子查询from 后面		：	支持表子查询（**重要的**）where 或 having 后面		：标量子查询、列子查询、行子查询exists 后面		：表 子查询按结果集的行列数不同分：标量子查询（结果集只有一行一列）列子查询（结果集只有一列多行）行子查询（结果集有一行多列）表子查询（一般为多行多列）
*/
#一、where 或 having 后面-- 1、标量子查询（单行子查询）-- 2、列子查询（多行子查询）-- 3、行子查询（多行多列）/*特点：子查询放在小括号内子查询一般放在条件的右侧标量子查询，一般搭配着单行操作符使用>< >= <=  <>列子查询，一般搭配着多行操作符使用： in、any/some、all子查询执行顺序优于主查询*/#1、标量子查询-- 案例一：谁的工资比 Abel 高-- 第一步： 先查询 abel 工资
select salary
from employees
where last_name='Abel';
-- 第二步：查询员工的信息，满足 salary>1 的结果
select *
from employees
where salary>(select salaryfrom employeeswhere last_name='Abel'
);		# 案例二：返回 job_id 与 141 号员工相同，salary 比143号员工多的员工 姓名，job_id 和工资
-- 第一步：查询 141 号员工的 job_id
select job_id 
from employees
where employee_id=141-- 第二步：查询 143 号员工的工资
select salary 
from employees
where employee_id=143-- 第三步 返回 job_id 与 141 号员工相同，salary 比143号员工多的员工 姓名，job_id 和工资
select last_name,job_id,salary
from employees
where job_id=(select job_id from employeeswhere employee_id=141)and salary>(select salary from employeeswhere employee_id=143);# 案例三：查询最低工资大于 50 号部门最低工资的部门 id 和其最低工资
-- 第一步查询 50 号部门的最低工资
select min(salary)
from employees
where department_id=50;
-- 第二步：查询每个部门的最低工资
select min(salary)
from employees
GROUP BY department_id；
-- 第三步：筛选 min（salary）>1
select department_id,min(salary)
from employees
GROUP BY department_id
HAVING min(salary)>(select min(salary)from employeeswhere department_id=50);#列子查询（多行子查询）
/*使用多行比较操作符in/not inany|someall
*/
--  案例一：返回 location_id 是 1400 或 1700 的部门中的所有员工姓名
-- 第一步：查询 location_id 是 1400 或 1700de 部门编号
select distinct department_id
from departments
where location_id in(1400,1700)-- 第二步 查询员工姓名，要求部门编号是 1 列表中的某一个
select last_name
from employees
where department_id in(select distinct department_idfrom departmentswhere location_id in(1400,1700)
);#3、行 子查询（使用比较少 了解）
-- 案例：查询员工编号最小并且工资最高的员工信息
-- 第一步：查询最小的员工编号
select min(employee_id)
from employees;
-- 第二步：查询最高工资SELECT max(salary)from 	employees-- 第三步:查询员工信息
select *
from employees
where employee_id=(select min(employee_id)from employees
) and salary=(SELECT max(salary)from 	employees
);
-- 行子查询的格式
select *
from employees
where (employee_id,salary)=(select min(employee_id),max(salary)from employees);

分页查询

#分页查询
/*
应用场景：当我们要显示的数据一页显示不全的时候，需要分页提交 sql 请求语法：select 查询列表from 表[join type】 join 表二on 连接条件where 筛选条件group by 分组字段having 分组后的筛选order by 排序的字段limit offset，size注解：offset：是要显示条目的起始索引（起始索引从 0 开始 如果从 0 开始 这个可以省略） ； size  要显示的条目个数*/-- 案例一：查询前五条 员工信息SELECT * from employees LIMIT 0,5;	
/*
特点：limit 语句放在查询语句的最后公式： 要显示的页数 page，每页的条目是：sizeselect 查询列表from 表limit  (page-1)*size，size；*/

联合查询

#联合查询
/*
union 联合 合并：将多条查询语句的结果合并成一个结果语法：查询语句 1union查询语句 2union...特点（注意事项）： 要求多条查询语句的查询列数 是一致的要求多条查询语句的查询的每一列的类型和顺序最好一致union 关键字默认去重 如果使用 union all 可以包含重复项*/#引入的案例：查询部门编号>90 或 邮箱包含 a 的员工信息
SELECT * from employees where email like '%a%' or department_id>90;select * from employees where email like '%a%'
UNION
SELECT * from employees where department_id>90;

---

---