（九）mysql语法-分组函数和多表连接查询

count：函数只能统计不为空的个数，为空的个数是不能够进行统计的
所有的分组函数都是忽略null值的
和分组函数查询的字段一般情况下是group by后面的字段
#datediff查询两个日期之间相差的天数
select DATEDIFF('2019-02-02','2019-02-01');--结果：1

#分组函数
语法：select 分组函数,列（要求出现再group by的后面）
　　from 表
　　【where筛选条件】
　　group by 分组的列表
　　【order by 子句】
注意： 查询列表必须特殊，要求是分组函数和group by 后出现的字段
#查询每个部门的平均工资
use myemployees
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;
select count(1),location_id from departments group by location_id;--这两种写法查询出来的结果是一样的
#查询有奖金的每个领导手下员工的最高工资
select max(salary),manager_id
from employees
where commission_pct is not null group by manager_id;
#查询哪个部门的员工个数大于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
#第二步：查询最高工资>12000
select max(salary),job_id from employees
where commission_pct is not null
group by job_id
having max(salary)>12000

#查询每个领导手下的员工的最低工资大于5000 的领导编号，和最低工资，并且领导编号要大于102
select min(salary),manager_id
from employees
where manager_id>102
GROUP by manager_id
having min(salary)>5000;
#大招：分组函数做条件一定是放在having子句中

#按照表达式或者函数进行分组
#案例：按员工的姓名的长度分组，查询每一组的员工个数，然后在筛选员工个数>5的有哪些
#第一步 查询每个长度的员工个数
select count(1),LENGTH(last_name)
from employees
GROUP BY LENGTH(last_name);
#第二步 添加筛选条件
select count(1),LENGTH(last_name)
from employees
GROUP BY LENGTH(last_name)
having count(1)>5
#别名同样也是支持的
select count(1) c,LENGTH(last_name) d
from employees
GROUP BY d
having c>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 job_id,department_id;--group by后面的顺序是能够颠倒的，最终查询出来的结果是一样的(视频中是这样说的，但是我本地试的是不行的，建议还是按照真实的需求来)

 

#案例：查询每个部门每个工种的员工的平均工资，按照平均工资诱导到底进行显示
select avg(salary),department_id,job_id
from employees
where department_id is not null
GROUP BY department_id,job_id
having avg(salary)>=24000 --这里随便的演示了一下having子句的放置位置
order by avg(salary) desc
use girls
show TABLES
select * from beauty

 

#连接查询
#又称作多表查询，当查询的字段来自于多个表的时候，就会使用到多表连接查询。
#查询每个女神的男朋友 这样拆线呢会产生笛卡尔集的错误情况
select name,boyname from beauty,boys;--这种写法是错误的
select name,boyname from beauty,boys where beauty.boyfriend_id=boys.id;

#sql语句的分类
按照年代进行分类：
sql92标准 仅仅支持内连接
sql99标准 推荐使用 支持内连接+外连接（左外和右外）+交叉连接

按照功能进行分类：
内连接
　　等值连接
　　非等值连接
　　自然连接

外连接
　　左外连接
　　右外连接
　　全外连接
　　交叉连接
#sql92标准
#等值连接
#查询女神名和对应的男神名
select name,boyname from beauty,boys where beauty.boyfriend_id=boys.id;

#查询员工名和对应的部门名 有null的那个部门没有进行显示 在查询的时候我们是可以给表起别名的，有过某个字段显示的时候有歧义的话 对应的视频是71
#如果为表起了别名，则查询到的字段就不能使用原来的表名进行限定了
SELECT last_name,department_name FROM employees,departments where employees.department_id=departments.department_id;

#案例：查询有奖金的员工名和部门名
select a.last_name,b.department_name,a.commission_pct from employees a,departments b where a.department_id=b.department_id
and a.commission_pct is not null

#查询每个城市的部门个数
select count(*),b.city from departments a,locations b
where a.location_id=b.location_id
GROUP BY b.city;

#实现三表连接查询
#案例：查询员工名、部门名、和所在的城市
select a.last_name,b.department_name,c.city
from employees a,departments b,locations c
where a.department_id=b.department_id and b.location_id=c.location_id
and c.city LIKE '%s%';

#工资登记表的sql脚本

/*CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal int,
highest_sal int);

INSERT INTO job_grades
VALUES ('A', 1000, 2999);

INSERT INTO job_grades
VALUES ('B', 3000, 5999);

INSERT INTO job_grades
VALUES('C', 6000, 9999);

INSERT INTO job_grades
VALUES('D', 10000, 14999);

INSERT INTO job_grades
VALUES('E', 15000, 24999);

INSERT INTO job_grades
VALUES('F', 25000, 40000);*/

SELECT * from job_grades

#非等值连接查询
#案例1：查询员工的工资和工资级别
select a.salary,b.grade_level from employees a,job_grades b
where a.salary BETWEEN b.lowest_sal and b.highest_sal;

#自连接;简单的理解就是自己连接自己进行查询
#查询员工名和上级的名称 这种情况说明是这张表中有一些比较特殊的字段，一共查询了这张表两遍 一张表当做两张表来使用，有些情况下，甚至当作更多张表来使用，分别当作了员工表和领导表
SELECT a.employee_id,a.last_name,b.manager_id,b.last_name FROM employees a,employees b where a.employee_id=b.manager_id;
SELECT a.employee_id,a.last_name,b.employee_id,b.last_name FROM employees a,employees b where a.employee_id=b.manager_id;--这两种写法，查询出来的效果是一样的