3.常见函数(分组函数)、分组查询、连接查询
分组函数
/*
功能:用作统计使用,又称为聚合函数或统计函数或组函数
分类:sum 求和
avg 平均值
max 最大值
min 最小值
count 计算非空的个数
特点:
1.sum、avg 一般用于处理数值型
max、min、count可以处理任何类型
2.以上分组函数都忽略null值
3.可以和distinct搭配实现去重的运算
4.count 函数的介绍
一般使用count(*)用于统计行数
count(常量) 表示在列表前加一列常量 统计总行数
5.和分组函数一同查询的字段要求是group by后的字段 其他不可
#1.简单的使用
SELECT SUM(salary) FROM employees; SELECT AVG(salary) FROM employees; SELECT MIN(salary) FROM employees; SELECT MAX(salary) FROM employees; SELECT COUNT(salary) FROM employees; SELECT SUM(salary) 和, AVG(salary) 平均, MIN(salary) 最小值, MAX(salary) 最大值, COUNT(salary) 总数 FROM employees;
#2.是否忽略null
SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees;
#3.和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
#4.count函数的详细介绍
SELECT COUNT(salary) FROM employees; SELECT COUNT(*) FROM employees;#统计总行数 SELECT COUNT(1) FROM employees;#统计总行数
#5.两个日期相差的天数 datediff(后面的日期,前面的日期)
SELECT DATEDIFF('2020-1-31','2020-2-3');
五:分组查询
/*
语法:select 分组函数,列(要求出现在group by的后面)
from 表
【where 筛选条件】
group by 分组的列表
【order by 子句】
注意:查询列表比较特殊,要求是在分组函数和group by后出现的字段
特点:1,分组查询中的筛选条件分为两类
数据源 位置 关键字
分组签的筛选 原始表 group by子句前面 where
分组后的筛选 分组后的结果集 group by子句后面 having
①分组函数做条件肯定是放在having字句中
②能用分组前筛选的,就优先考虑使用分组前筛选
2.group by子句支持单个字段分组,多个字段分组(多个字段之间用逗号隔开没有顺序要求),表达式和函数
3.可以增加排序;
*/
#简单的分组查询
#案例1:查询每个工种的最高工资
SELECT MAX(salary),job_id FROM employees GROUP BY job_id;
#案例2:查询每个位置上的部门个数
SELECT COUNT(*),location_id FROM departments GROUP BY location_id;
#添加分组前的筛选条件
#案例1:查询邮箱中包含a字符的,每个部门的平均工资
SELECT AVG(salary),department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id;
#案例2:查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;
#添加分组后的筛选条件 having
#案例1:查询哪个部门的员工个数>2
--①查询每个部门的员工个数 SELECT COUNT(*),department_id FROM employees GROUP BY department_id; --②根据①的结果进行筛选,查询那个部门的员工数>2 SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;
#案例2:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT MAX(salary),job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary)>12000;
#按表达式或函数分组
#案例:按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些
SELECT COUNT(*) ,LENGTH(last_name) len_name FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*)>5;
#按多个字段分组
#案例:查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id FROM employees GROUP BY department_id,job_id;
#添加排序
#案例:查询每个部门每个工种的员工的平均工资,并且按平均工资的高低显示
SELECT AVG(salary),department_id,job_id FROM employees GROUP BY department_id,job_id ORDER BY AVG(salary) DESC;
六、连接查询
/*
含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
笛卡尔乘积现象:表1有m行,表2 有n行,结果为m*n行
发生原因:没有有效的连接条件(where)
如何避免:添加有效的连接条件
连接分类:
①按照年代分类
sql92标准:仅仅支持内连接
sql99标准【推荐】:支持内连接+外连接(左外、右外)+交叉连接
②按照功能分类
内连接:等值连接
非等值连接
自连接
外连接:左外连接
右外连接
全外连接
交叉连接
SELECT * FROM beauty;
SELECT * FROM boys;
#sql92标准
#1. 等值连接
/*
①多表等值连接的结果为多表的交集部分
②n表连接,至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配之前介绍的所有子句使用,比如排序、分组、筛选
*/
#案例1:查询女神名和对应的男神名
SELECT beauty.name,boys.`boyName` FROM beauty,boys WHERE beauty.`boyfriend_id`= boys.`id`;
#表名过长,语句复杂 可以为表起别名
#①提高语句的简洁度
#②区分多个重名的字段
#注意:如果为表起了别名,则查询的字段就不能使用原来的表名去限定
#案例2.查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,job_title FROM employees e,jobs j WHERE e.`job_id` = j.`job_id`;
#两个表的顺序可以交换
#案例1:查询每个城市的部门个数
SELECT COUNT(*) ,city FROM departments d,locations l WHERE d.location_id = l.`location_id` GROUP BY city;
#2.非等值连接
#案例1:查询员工的工资和工资级别
SELECT salary,grade_level FROM employees e,job_grades g WHERE salary BETWEEN g.`lowest_sal`AND g.`highest_sal`;
#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`;
#二、sql99
语法:
select 查询列表
from 表1 别名
【连接类型】join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
分类:
内连接:inner
等值、非等值、自连接
特点:①添加排序、分组、筛选
② inner可以省略
③ 筛选条件放在where后面,连接条件放在on后面,提高了可读性
④inner join连接和sql92语法中的等值连接效果相同,都是多表交集
外连接
左外连接:left 【outer】
右外连接:right【outer】
全外连接:full 【outer】
交叉连接:cross
#一、内连接
#1.等值连接
--案例1:查询员工名、部门名 SELECT last_name 员工名,department_name 部门名 FROM employees e INNER JOIN departments d 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的城市名和部门个数,
SELECT city,COUNT(*) FROM departments d INNER JOIN locations l ON d.`location_id` = l.`location_id` GROUP BY l.`city` HAVING COUNT(*)>3
#案例4 查询哪个部门的员工个数>3的部门名和员工个数,并按个数降序
SELECT d.`department_name`,COUNT(*) FROM departments d INNER JOIN employees e ON d.`department_id` = e.department_id GROUP BY d.`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 d.`department_name` DESC;
#2.非等值连接
#查询员工的工资级别
SELECT salary,grade_level FROM employees e INNER JOIN job_grades j ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;
#3.自连接
#查询员工的名字、上级的名字
SELECT e.last_name,m.last_name FROM employees e INNER JOIN employees m ON e.`manager_id` = m.`employee_id`
#二、外连接
/*
应用场景:用于查询一个表中有,一个表中没有的记录
特点:
1.外连接的的查询结果是主表中的所有记录
如果从表中有和他匹配的,则显示匹配的值
如果从表中没有和它匹配的值,则显示null
外连接查询结果 = 内连接结果+主表中有而从表中没有的记录
2.左外连接,left join左边的是主表
右外连接,right join右边的是主表
3.左外和右外交换连个表的顺序,可以实现同样的效果
4.全外连接=内连接的结果+表1中有表2中没有+表2中有但表1中没有的
*/
#查询男朋友 不在男神表中的女神名
#左外连接
SELECT b.name,bo.* FROM beauty b LEFT JOIN boys bo ON b.boyfriend_id = bo.`id` WHERE bo.id IS NULL;
#查询哪个部门没有员工
SELECT department_name FROM departments d LEFT JOIN employees e ON d.`department_id` = e.`department_id` WHERE e.`employee_id` IS NULL;
#全外 但mysql不支持
USE girls; selectb.*,bo.* FROM beauty b FULL OUTER JOIN boys bo ON b.boyfrienf_id = bo.id;
#交叉连接 实现笛卡乘积
SELECT b.*,bo.* FROM beauty b CROSS JOIN boys bo;