目录
接上篇
(2)分组函数
功能:用作统计使用,又称为聚合函数或统计函数或组函数
分类:
- sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数
简单使用:
- 案例1:
SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数 FROM employees;
- 案例2:
SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数 FROM employees;
特点:
1.sum、avg一般用于处理数值型
SELECT SUM(last_name) ,AVG(last_name) FROM employees; //错误 SELECT SUM(hiredate) ,AVG(hiredate) FROM employees; //虽然有结果,但没有意义
2.max、min、count可以处理任何类型
SELECT MAX(last_name),MIN(last_name) FROM employees;
SELECT MAX(hiredate),MIN(hiredate) FROM employees;
3.以上分组函数都忽略null值
SELECT SUM(commission_pct) ,AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees;
4.可以和distinct搭配实现去重的运算
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
5.count函数:
- count的常见用法:
- count(字段)
SELECT COUNT(commission_pct) FROM employees;
- 一般使用count(*)用作统计行数(忽略null)
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;
count效率:
MYISAM存储引擎下 ,COUNT(*)的效率高
INNODB存储引擎下,COUNT(*)和COUNT(1)的效率差不多,比COUNT(字段)要高一些6.和分组函数一同查询的字段要求是group by后的字段
SELECT AVG(salary),employee_id FROM employees; //查询得到的表是不规则的,虽然不报错,但employee_id没有意义
5.分组查询
语法:
select 查询列表
from 表
【where 筛选条件】
group by 分组的字段
【order by 排序的字段】;
特点:
- 1、和分组函数一同查询的字段必须是group by后出现的字段
- 2、筛选分为两类:分组前筛选和分组后筛选
针对的表 位置 连接的关键字 分组前筛选 原始表 group by前 where 分组后筛选 group by后的结果集 group by后 having
- (1)分组函数做条件肯定是放在having子句中
- (2)一般来讲,能用分组前筛选的,尽量使用分组前筛选,提高效率
- 3、group by支持单个字段的分组也支持多个字段(字段之间用逗号隔开并且没有顺序要求,)
- 4、可以搭配着排序使用(放在整个分组查询的最后)
(1)分组查询的简单使用
案例1:查询每个工种的员工平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
案例2:查询每个位置的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;
(2)分组前的筛选(对原始表做筛选:where)
案例1:查询邮箱中包含a字符的 每个部门的最高工资
SELECT MAX(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
案例2:查询有奖金的每个领导手下员工的平均工资
SELECT AVG(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
(3)分组后的筛选(对分组后的新表做筛选:having)
案例:查询哪个部门的员工个数>5
- 1.查询每个部门的员工个数
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;
- 2.筛选刚才1结果
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>5;
案例2:每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
- 1.查询每个工种的工种编号和最高工资
SELECT job_id,MAX(salary)
FROM employees
GROUP BY job_id;
- 2.查询每个工种有奖金的工种编号和最高工资(从原始表可以筛选)
SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id;
- 3.每个工种有奖金的员工的最高工资>12000的工种编号和最高工资(必须从2的结果表才能筛选)
SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
案例3:领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资
- 1.查询每个领导手下的员工固定最低工资
SELECT manager_id,MIN(salary)
FROM employees
GROUP BY manager_id;
- 2.添加筛选条件:领导编号>102
SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id>102
GROUP BY manager_id;
- 3.添加筛选条件:最低工资>5000
SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;
(4)按表达式或函数后别名分组查询
案例1:按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些
- 1.查询每个长度的员工个数
SELECT COUNT(*),LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name);
- 2.添加筛选条件:员工个数大于5
SELECT COUNT(*),LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;
(5)按多个字段分组
案例:查询每个工种每个部门的最低工资
SELECT MIN(salary),job_id,department_id
FROM employees
GROUP BY department_id,job_id; //顺序没有影响
(6)添加排序的分组查询
案例:每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
SELECT job_id,MAX(salary) m
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m>6000
ORDER BY m ;
6.连接查询
连接查询含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
所使用的示例表2的结构如下:
笛卡尔乘积现象:表1 有m行,表2有n行,结果=m*n行,
笛卡尔乘积原理:用主表的每一项去搭配从表的每一项并显示
发生原因:没有有效的连接条件
如何避免:添加有效的连接条件
示例:
SELECT name,boyname
FROM beauty,boys;
更改后:
SELECT NAME,boyname
FROM beauty,boys
WHERE beauty.`boyfriend_id`=boys.`id`;
(1)连接查询的分类
按年代分类:
- sql92标准:仅仅支持内连接
- sql99标准【推荐】:支持内连接+外连接(左外和右外)+交叉连接
- sql92和 sql99pk
- 功能:sql99支持的较多
- 可读性:sql99实现连接条件和筛选条件的分离,可读性较高
按功能分类:
- 内连接:
- 等值连接
- 非等值连接
- 外连接:
- 左外连接
- 右外连接
- 全外连接
- 交叉连接
注意:mysql中不支持全外连接
(2)sql92标准
1)等值连接
原理:用一张表的每一项去匹配另一张表的每一项,找到满足where后的条件的就显示
特点:
- 多表等值连接的结果为多表的交集部分
- n表连接,至少需要n-1个连接条件
- 多表的顺序没有要求
- 一般需要为表起别名
- 可以搭配前面介绍的所有子句使用,比如排序、分组、筛选
案例1:查询女神名和对应的男神名
SELECT NAME,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;
案例2:查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;
为表起别名
好处:
- 提高语句的简洁度
- 区分多个重名的字段
注意:如果为表起了别名,则查询的字段就不能使用原来的表名去限定
案例3:查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.`job_id`,j.job_title
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`;
两个表的顺序可以调换
案例4:查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM jobs j,employees e
WHERE e.`job_id`=j.`job_id`;
可以加筛选
案例5:查询有奖金的员工名、部门名
SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;
案例6:查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%';
可以加分组
案例7:查询每个城市的部门个数
SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;
案例8:查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,d.`manager_id`,MIN(salary)
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name,d.`manager_id`;
可以加排序
案例9:查询每个工种的工种名和员工的个数,并且按员工个数降序
SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;
可以实现三表连接
#案例10:查询员工名、部门名和所在的城市
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`
AND city LIKE 's%'
ORDER BY department_name DESC;
2)非等值连接
即将等值连接中的=换成不等号
案例1:查询员工的工资和工资级别
工资级别表中如下:
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level`='A';
3)自连接
含义:把原来的一张表当成多张表去使用
案例1:查询 员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
(3)sql99标准
语法:
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
分类:
内连接(★):inner
外连接
左外(★):left 【outer】
右外(★):right 【outer】
全外:full【outer】 //mysql不支持
交叉连接:cross
1)内连接
原理:用一张表的每一项去匹配另一张表的每一项,找到满足on后的条件的就显示
分类:
等值
非等值
自连接
特点:
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面,连接条件放在on后面,提高分离性,便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的,都是查询多表的交集
a.等值连接
案例1.查询员工所属部门名
SELECT last_name,department_name
FROM departments d
JOIN employees e
ON e.`department_id` = d.`department_id`;
案例2:查询名字中包含e的员工名和工种名(添加筛选)
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`= j.`job_id`
WHERE e.`last_name` LIKE '%e%';
案例3:查询部门个数>3的城市名和部门个数,(添加分组+筛选)
- 1.查询每个城市的部门个数
-
SELECT city,COUNT(*) 部门个数 FROM departments d INNER JOIN locations l ON d.`location_id`=l.`location_id` GROUP BY city
- 2.在1结果上筛选满足条件的
-
SELECT city,COUNT(*) 部门个数 FROM departments d INNER JOIN locations l ON d.`location_id`=l.`location_id` GROUP BY city HAVING COUNT(*)>3;
案例4.查询哪个部门的员工个数>3的部门名和员工个数,并按个数降序(添加排序)
- 1.查询每个部门的员工个数
-
SELECT COUNT(*),department_name FROM employees e INNER JOIN departments d ON e.`department_id`=d.`department_id` GROUP BY department_name;
- 2.在1结果上筛选员工个数>3的记录,并排序
-
SELECT COUNT(*) 个数,department_name FROM employees e INNER JOIN departments d ON e.`department_id`=d.`department_id` GROUP BY department_name HAVING COUNT(*)>3 ORDER BY COUNT(*) DESC;
案例5:查询员工名、部门名、工种名,并按部门名降序(添加三表连接)
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.`department_id`=d.`department_id`
INNER JOIN jobs j ON e.`job_id` = j.`job_id`
ORDER BY department_name DESC;
b.非等值连接
案例1:查询员工的工资级别
SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
案例2:查询工资级别的个数>20的个数,并且按工资级别降序
SELECT COUNT(*),grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;
c.自连接
案例1:查询员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`= m.`employee_id`;
案例2:查询姓名中包含字符k的员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`= m.`employee_id`
WHERE e.`last_name` LIKE '%k%';
2)外连接
应用场景:用于查询一个表中有,另一个表没有的记录
原理:用主表中每一项去匹配从表中每一项,
如果遍历完找到与之匹配的,就显示匹配到的记录;
如果遍历完从表也没有找到与之匹配的,在该匹配字段的值显示null;
左右外连接最终查询结果为主表中的所有记录
左右外连接查询结果=内连接结果+主表中有而从表没有的记录
特点:
1、左外连接,left join左边的是主表
右外连接,right join右边的是主表
2、左外和右外交换两个表的顺序,可以实现同样的效果
3、全外连接=内连接的结果+表1中有但表2没有的(表2中的字段用null填充)+表2中有但表1没有的(表1中的字段用null填充)
4、一般要查询的信息主要来自哪个表,该表就是主表
引入示例:查询男朋友不在男神表的的女神名
首先两张表如下:
beauty表:
boys表:
SELECT b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL; //在此选择id是因为,id为主键,不可能为null
a.左外连接
案例1:查询哪个部门没有员工
SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
b.右外连接
案例1:查询哪个部门没有员工
SELECT d.*,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
c.全外连接(mysql不支持,oracle支持)
示例:
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.id;
3)交叉连接
即笛卡尔乘积
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;