第三天MYSQL
第三天MySQL学习 ：分组函数、分组查询、连接查询（等值连接、非等值连接、自连接）
2020/5/6
分组函数：(分组函数用作统计使用，又称聚合函数、统计函数或组函数)
#sum（求和）、avg（平均值）、max（最大值）、min（最小值）、count（计数）
特点：
1. 以上分组函数中都是可以忽略null值 （其中count本身就是计算非null值得个数）
2. sum和avg函数的参数一般只能处理数值型，而max、min以及count可针对任意类型的参数
SELECT SUM(salary) FROM employees;-> 691400.00
SELECT AVG(salary) FROM employees;-> 6461.682243
SELECT MAX(salary) FROM employees;-> 24000.00
SELECT MIN(salary) FROM employees;-> 2100.00
SELECT COUNT(salary) FROM employees;-> 107
#组合使用:
SELECT
SUM(salary) 和,
ROUND(AVG(salary),2) 平均, #嵌套使用round()函数，将值保留至小数点后面2位
MAX(salary) 最大值,
MIN(salary) 最小值,
COUNT(salary) 总数
FROM
employees;
关于分组函数忽略nul值，举例：
SELECT
AVG(commission_pct),
SUM(commission_pct) / COUNT(commission_pct),
SUM(commission_pct) / COUNT(*)
FROM
employees;
这里可以看出avg(commissom_pct)的值等于sum(commission_pct)/ count(commission_pct)(非空的总数)，而不是总体的个数(count(*))
#与DISTINCT（去重）关键字搭配使用
SELECT SUM(DISTINCT salary), SUM(salary) FROM employees;
去重之后，在统计工资之和
SELECT SUM(DISTINCT salary), SUM(salary) FROM employees;
统计工资的种类
#count函数详细介绍
select count(*) from 表名; ->统计表的总行数
select count(1) from 表名; ->相当于在表中多了一列，这一列中根据表内的行数加了相应个数的1，统计1的个数，并返回
效率比较：
MYISAM存储引擎下，count(*)的效率高
INNODB存储引擎下，count(*)和count(1)的效率差不多，比count(字段)(有个判断字段是否为null的过程)要高
注意：和分组函数一同查询的字段要求是group by 后的字段
十六、分组查询
语法：(group by 子句语法)
注意：查询列表必须特殊，要求是分组函数或group by后出现的字段
SELECT
分组函数，列（要求要出现在group by 之后）
FROM
表名
[WHERE
筛选条件]
GROUP BY
分组的列表
[ORDER BY
子句]
特点：
- 分组查询中的筛选条件分为两类
数据源 位置 关键字
分组前筛选 原始表 group by子句前 where
分组后筛选 分组后的结果 group by子句后 having
1.若分组函数做筛选条件则肯定放在having子句中
2.能用分组前筛选的，就优先考虑使用分组前筛选（考虑效率问题）
2. group by 子句中支持单个字段分组，多个字段分组（多个字段用逗号隔开，没有顺序要求，还支持表达式和函数分组（用的较少））
3. 也可以添加排序（排序放在整个分组查询语句的最后）
----------------------------------简单分组查询------------------------
#案例一：查询每个部门的平均工资
SELECT
AVG(salary) 平均工资,
department_id
FROM
employees
GROUP BY
department_id;
#案例二：查询每个工种的最高工资
SELECT
MAX(salary),
job_id
FROM
employees
GROUP BY
job_id;
#案例三：查询每个位置上的部门个数
SELECT
COUNT(*),
location_id FROM
departments
GROUP BY
location_id;
-----------------------------添加筛选条件的分组查询-------------------
1.分组前筛选
#案例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;
2.分组后筛选
#案例1：查询哪个部门的员工个数>2
SELECT
count(*) 员工个数,
department_id 部门编号
FROM
employees
GROUP BY
department_id
HAVING #根据GROUP by 执行后的结果再筛选
count(*) > 2;
SELECT
count(*) 员工个数,
department_id 部门编号
FROM
employees
GROUP BY
department_id
HAVING
员工个数 > 2;#可使用别名
#案例2：查询每个工种有奖金的员工的最高工资>12000
SELECT
MAX(salary) 最高工资,
job_id 工种编号
FROM
employees
WHERE
commission_pct IS NOT NULL
GROUP BY
job_id
HAVING
MAX(salary) > 12000;
-------------------------------------------------
SELECT
MAX(salary) 最高工资,
job_id 工种编号
FROM
employees
WHERE
commission_pct IS NOT NULL
GROUP BY
工种编号
HAVING
MAX(salary) > 12000;
注意：ORDER BY以及GROUP BY子句后都可以使用别名，注意！！！WHERE子句后不可以！！！
#案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资
SELECT
MIN(salary) 最低工资,
manager_id 领导编号
FROM
employees
WHERE
manager_id > 102
GROUP BY
manager_id
HAVING
MIN(salary) > 5000;
对比分组前筛选与分组后筛选：
数据源 位置 关键字
分组前筛选 原始表 group by子句前 where
分组后筛选 分组后的结果 group by子句后 having
注意：
- 若分组函数做筛选条件则肯定放在having子句中
- 能用分组前筛选的，就优先考虑使用分组前筛选（考虑效率问题）
---------------------按表达式或函数分组查询(用的较少)--------------------
#案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>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一致的分为一个小组（与顺序无关）
department_id,
job_id;
----------------------------添加排序条件的分组查询-------------------------
#案例：每个部门每个工种的奖金存在的并且平均工资大于1000的平均工资，并且按平均工资的高低显示
SELECT
AVG(salary) 平均工资,
department_id,
job_id
FROM
employees
WHERE
department_id IS NOT NULL
GROUP BY #department_id与job_id一致的分为一个小组（与顺序无关）
department_id,
job_id
HAVING
AVG(salary)>10000
ORDER BY
AVG(salary) DESC;
十七、连接查询
含义：又称多表查询，当查询的字段来自于多个表时，就会用到
笛卡尔乘积现象：表1 有m行，表2 有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加上有效的连接条件
连接查询分类：
按年代分类：
sq92标准：仅仅支持内连接（对MySQL而言）
sq99标准（推荐）：支持内连接+外连接（左外、右外）+交叉连接
按功能分类：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接
交叉连接
（sq92标准）
#等值连接
特点：
- 多表连接的结果为多表的交集部门
- n表连接，至少需要n-1个连接条件
- 多表的顺序没有要求
- 一般需要为表取别名
- 可以搭配前面介绍的所有子句
#案例1：查询女神名和对应的男神名
SELECT
NAME,
boyname
FROM
beauty,
boys
WHERE
beauty.boyfriend_id = boys.id; #在两个表之间添加了一个连接的条件
#案例2：查询员工名和对应的部门名
SELECT
last_name,
department_name
FROM
employees,
departments
WHERE
employees.department_id = departments.department_id;
#案例3：查询员工名、工种号、工种名
SELECT
last_name,
employees.job_id, #要用表名去限定，否则识别不出来是哪个表中的job_id
job_title
FROM
employees,
jobs #两个表的顺序可调换
WHERE
employees.job_id = jobs.job_id;
------------为表取别名----------------
- 提高语句的简洁度
- 区分多个重名的字段（限定字段）
- 若为表取了别名，则查询的字段就不能使用原来的表名取限定
SELECT
e.last_name,
e.job_id,#用表名去限定
j.job_title
FROM
employees e,
jobs j
WHERE
e.job_id = j.job_id;
#案例4：查询有奖金的员工名、部门名、奖金率【增加筛选条件】
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;
#案例5：查询城市名中第二个字符为"o"的部门名和城市名【增加筛选条件】
SELECT
department_name,
city
FROM
departments d,
locations l
WHERE
d.location_id = l.location_id
AND city LIKE "_o%";
#案例6：查询每个城市的部门个数【与group by子句搭配使用】
SELECT
count(*) 个数,
city
FROM
departments d,
locations l
WHERE
d.location_id = l.location_id
GROUP BY
city;
#案例7：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
【与group by子句搭配使用】
SELECT
department_name,
e.manager_id,
MIN(salary)
FROM
departments d,
employees e
WHERE
e.department_id = d.department_id
AND e.commission_pct IS NOT NULL
GROUP BY
department_name,manager_id;
#案例8：查询每个工种的工种名，和员工个数，并按员工个数降序【与order by 子句搭配使用】
SELECT
job_title,
COUNT(*)
FROM
jobs j,
employees e
WHERE
j.job_id = e.job_id
GROUP BY
job_title
ORDER BY
COUNT(*) DESC;
#案例9：查询员工名、部门名和所在的城市【多表联合查询】
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;
#非等值连接
#案例1：查询员工的工资和工资级别
SELECT
salary,
grade_level
FROM
employees e,
job_grades j
WHERE
salary BETWEEN lowest_sal #salary在这个范围内就显示出来（不是等值的形式，而是一个范围的判断）
AND highest_sal;
#自连接（当前表要要连接当前表，为了不模糊，则需各取别名进行限定！）
#案例：查询员工名和上级的名称
SELECT
e.last_name 员工名,
m.last_name 上级名称
FROM
employees e,
employees m
WHERE
e.manager_id = m.employee_id;
以上是 第三天MYSQL 的全部内容， 来源链接： utcz.com/z/533510.html
