第三天MYSQL

第三天Mysql学习 ：分组函数、分组查询、连接查询（等值连接、非等值连接、自连接）

2020/5/6

分组函数：(分组函数用作统计使用，又称聚合函数、统计函数或组函数)

 #sum（求和）、avg（平均值）、max（最大值）、min（最小值）、count（计数）

 特点：

以上分组函数中都是可以忽略null值 （其中count本身就是计算非null值得个数）

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

　　　　子句]

特点：

1. 分组查询中的筛选条件分为两类

                      数据源                  　 位置               　　    关键字

分组前筛选   原始表                  group by子句前              　where

分组后筛选   分组后的结果       group by子句后                having

若分组函数做筛选条件则肯定放在having子句中

能用分组前筛选的，就优先考虑使用分组前筛选（考虑效率问题）

group by 子句中支持单个字段分组，多个字段分组（多个字段用逗号隔开，没有顺序要求，还支持表达式和函数分组（用的较少））

也可以添加排序（排序放在整个分组查询语句的最后）

----------------------------------简单分组查询------------------------

#案例一：查询每个部门的平均工资

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：查询邮箱中包含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;

分组后筛选

#案例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

注意：

1. 若分组函数做筛选条件则肯定放在having子句中
2. 能用分组前筛选的，就优先考虑使用分组前筛选（考虑效率问题）

 

 

---------------------按表达式或函数分组查询(用的较少)--------------------

#案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>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标准）

#等值连接                     

特点：

1. 多表连接的结果为多表的交集部门
2. n表连接，至少需要n-1个连接条件
3. 多表的顺序没有要求
4. 一般需要为表取别名
5. 可以搭配前面介绍的所有子句

#案例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;

 

------------为表取别名----------------

1. 提高语句的简洁度
2. 区分多个重名的字段（限定字段）
3. 若为表取了别名，则查询的字段就不能使用原来的表名取限定

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;