分组函数与查询、连接查询 多表连接（等值连接）

• 一、分组函数
• 分组函数
• 分组函数的分类
• 特点
• 1、查询分组函数
• 2、关于sum 和avg
• 3、关于max和min
• 4、关于count

• 分组查询
• 引入：查询各部门的最高工资
• 分组查询的特点
• 普通的分组查询
• 按多个字段分组
• 分组查询+筛选having
• having子句支持别名
• 排序 order by 支持别名

• 1、传统模式的多表连接 等值连接(where)——非等值连接
• 2、sql99推出的标准，使用join关键字实现连接 内连接——外连接
• 3、自连接

一、分组函数

分组函数

函数，类似java中的方法
叫什么？方法名或者函数名
干什么？函数功能
分组函数，又称为统计函数或聚合函数
length(‘john’) //传入一个值，返回一个值，一对一，像这种函数，就不是分组函数，叫做单行函数，分组函数表示这个函数给出一组值,最后返回一个值
max(salary) // 分组函数，给它一组值，比如说给它一列值

分组函数的分类

max():最大值
min():最小值
sum():和
avg():平均值
count():计算非空的个数
这些是通用的，sqlserver、oracle、mysql 都是一样的

特点

1、sum、avg只支持数值型，count、max、min支持任何类型
2、这几个分组函数，都忽略null值
3、count()分组函数，支持以下写法
count(*) 建议使用
count(1)
表示 查询结果集的行数

USE myemployees;
表示查询的数据库

1、查询分组函数

SELECT MAX(salary) 最大值 FROM employees;
SELECT MIN(salary) 最小值 FROM employees;
SELECT SUM(salary) 和 FROM employees;
SELECT AVG(salary) 平均值 FROM employees;
SELECT COUNT(salary) 个数 FROM employees;
SELECT MAX(salary) 最大值,SUM(salary) 和 FROM employees;

2、关于sum 和avg

1.只支持数值型
2.sum和avg都忽略null值

SELECT AVG(last_name) FROM employees;
SELECT SUM(last_name) FROM employees;

SELECT * FROM employees;
SELECT SUM(commission_pct)/107,AVG(commission_pct) FROM employees;

3、关于max和min

1.支持任何类型
2.max和min都忽略null值

SELECT MAX(last_name),MIN(last_name) FROM employees; //判断类型
SELECT MAX(commission_pct),MIN(commission_pct) FROM employees; //判断是否忽略null

4、关于count

SELECT COUNT(commission_pct) FROM employees;
//计算非空的值，忽略null、//查询员工表占了几个部门
SELECT COUNT(DISTINCT department_id) FROM employees;

#查询结果集中的行数，count(1)的性能没有count（）高
因为count()，sql服务器对其进行了优化，所以效率高

SELECT COUNT() FROM employees; //查看有多少条数据 ，计算满足条件的行数
SELECT COUNT() FROM employees WHERE department_id>30;
SELECT COUNT(1) FROM employees; //和count(*)的效果是一样的，但是效率不一样

#问题：查询员工表最大工资和员工号? 答：不能！！！！
#不允许和分组函数，一起执行任意字段，因为employee_id这个字段的值是多个，分组函数的值是一个，一和多无法形成规则的表格
SELECT MAX(salary),employee_id FROM employees;

二、分组查询

分组查询

和分组函数一起搭配使用的查询，我们叫做分组查询，换句话说，分组查询一般都会用到分组函数

引入：查询各部门的最高工资

SELECT MAX(salary) FROM employees WHERE department_id IN(20,90,30);
SELECT MAX(salary) FROM employees GRO UP BY department_id;

语法：
select 分组函数 别名,分组后的字段
from 表
【where 分组前筛选】
group by 分组的字段
【having 分组后筛选】
【order by 排序的字段】
从上到下依次顺序，不能颠倒顺序

分组查询的特点

1、按多个字段分组，字段间用逗号隔开，没有顺序要求
2、可以和分组函数一起查询的字段，只能是分组后的字段，不能是任意字段
3、分组筛选
分组前筛选：
针对的表：原始表
位置：group by的前面
使用的关键字：where

分组后筛选：
针对的表：分组后的结果集
位置：group by的后面
使用的关键字having

顺序：where——group by——having

4、having和group by支持别名

普通的分组查询

#01 案例：查询各部门的最高工资和部门号
SELECT MAX(salary),department_id FROM employees GROUP BY department_id;

按多个字段分组

#案例：查询每个工种、每个部门的平均工资
SELECT AVG(salary),job_id,department_id
FROM employees
GROUP BY job_id,department_id;

分组查询+筛选having

筛选条件分两类：
见分组查询的特点 3 分组筛选

顺序：where——group by——having

#案例：查询每个地区的部门个数，求个数大于2的部门个数和地区号
SELECT COUNT() 个数,location_id
FROM departments
GROUP BY location_id
HAVING COUNT()>2;

having子句支持别名

#案例：查询每个地区的部门个数，求个数大于2的部门个数和地区号
SELECT COUNT(*) 个数,location_id
FROM departments
GROUP BY location_id
HAVING 个数>2;

排序 order by 支持别名

#案例：查询员工姓名中包含字符a，每个部门的最低工资高于3000的部门编号,按照最低工资降序排序
SELECT department_id,MIN(salary) 最低工资
FROM employees
WHERE last_name LIKE ‘%a%’
GROUP BY department_id
HAVING MIN(salary)>3000
ORDER BY 最低工资 DESC;

三、分组查询练习

#1.where子句可否使用组函数进行过滤?
#不可以，分组函数只能放在having子句中

#2.查询公司员工工资的最大值，最小值，平均值，总和
#只用了分组函数，没有用分组查询

#3.查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY job_id;

#4.查询员工最高工资和最低工资的差距使用别名DIFFERENCE）
SELECT MAX(salary)-MIN(salary) DIFFERENCE
FROM employees;

#5.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;

#6.查询所有部门的编号，员工数量和工资平均值,并按平均工资降序
SELECT department_id,COUNT(*),AVG(salary) 平均工资
FROM employees
GROUP BY department_id
ORDER BY 平均工资 DESC;

#7.选择各个job_id的员工人数
SELECT COUNT(*),job_id FROM employees GROUP BY job_id;

四、连接查询

#连接查询
概念：查询的字段来自于多个表
语法：
select 字段1，字段2
from 表1，表2
【where 连接条件】
笛卡尔乘积：
产生原因：没有加连接条件，导致结果为 表1 的行数*表2 的行数
解决方法：添加上有效的

myemployees

分类：

1、传统模式的多表连接 等值连接(where)——非等值连接

1、传统模式的多表连接也叫等值连接
特点：
①表的顺序没有要求
②n表连接，至少需要n-1个连接条件
③一般需要为表起别名，这样可以提高语句简洁度，并且防止字段有歧义
④可以添加分组、排序、筛选，一起混合使用

#①案例1：查询员工名、部门名
SELECT

last_name

,

department_name

FROM

employees

,

departments

WHERE

employees

.

department_id

=

departments

.

department_id

;

#②为表起别名，一般需要为表起别名，好处：
a、提高语句简洁度
b、防止字段有歧义，比如说 两张表中都有name字段，使用b.name 或者a.name 这样表示就没有歧义
c、提高效率如果已经为表起别名，则使用字段时，只能用别名限定而不能用表名限定
SELECT e.

last_name

,d.

department_name

FROM

employees

e,

departments

d
WHERE e.

department_id

=d.

department_id

;

#③添加筛选条件
#案例：查询 工资>5000的工种名和员工名、工资
SELECT job_title,last_name,salary
FROM employees e,jobs j
WHERE e.

job_id

=j.

job_id

AND salary>5000;

#④添加分组和筛选
#案例：查询每个部门的员工个数和部门名
SELECT COUNT(*) 个数,department_name
FROM employees e,departments d
WHERE e.

department_id

=d.

department_id

GROUP BY e.

department_id

HAVING 个数>5;

#⑤排序
#案例：查询每个部门的员工个数和部门名
SELECT COUNT(*) 个数,department_name
FROM employees e,departments d
WHERE e.

department_id

=d.

department_id

GROUP BY e.

department_id

HAVING 个数>5
ORDER BY 个数 DESC;

#⑥ 三表连接
#案例：查询员工名、部门名和所在城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.

department_id

=d.

department_id

AND d.

location_id

=l.

location_id

;

2、sql99推出的标准，使用join关键字实现连接 内连接——外连接

3、自连接