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;

https://www.cnblogs.com/jane315/archive/2020/05/06/12839543.html