进阶5 分组查询

1、语法：
select 分组函数，列（要求出现在group by后面）
from 表
where 条件
group by 分组列表
order by 排序列表

2、执行过程：
from 表 where条件 group by分组 having筛选 select查询 order by 排序

3、要求：查询列表是分组函数或者出现在group by之后

4、特点：

  1、分组筛选分为两类
                    数据源         位置及关键字
  分组前筛选       原始表中有      where 放在group by前面
  分组后筛选       需要先查一下    hanving  放在group by 后面
  
  ①分组函数做条件放在having中，因为分组函数是不在原始表中的
  ②都可以筛选时优先使用原始表里的where筛选
  
  2、多字段分组，逗号隔开，顺序无影响

查询每个部门的平均工资

SELECT department_id 部门,AVG(salary) 平均工资
FROM employees
GROUP BY department_id;

分组前的查询(在表中可以找到筛选字段，如：email）

查询邮箱中有a字母的每个部门的员工数

SELECT COUNT(*) ,department_id 
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

分组后查询（在表中找不到要筛选的字段）

在每个员工数>2的部门查询员工奖金

#①每个部门的员工数
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
#②对①的结果进行筛选，筛选员工数>2
SELECT commission_pct,COUNT(*) AS 员工人数,department_id
FROM employees
GROUP BY department_id
HAVING 员工人数>2;

查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

SELECT job_id,MAX(salary) AS 最高工资
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id 
HAVING 最高工资>12000;

查询领导编号>102的每个领导手下的最低工资>5000的领导编号，及最低工资

SELECT manager_id,MIN(salary) 最低工资
FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING 最低工资>5000;

按表达式或者函数分组

按员工姓名的长度分组，查询每组的员工个数，筛选员工个数>5

SELECT COUNT(*) AS 员工个数,LENGTH(last_name) AS 姓名长度
FROM employees
GROUP BY 姓名长度
HAVING 员工个数>5;
#不同数据库where 不支持别名查询

按多个字段进行分组

查询不同工种不同部门的平均工资

SELECT AVG(salary),job_id,department_id
FROM employees
GROUP BY job_id,department_id;
#前后循序没有影响

查询不同工种不同部门的平均工资，降序排列

SELECT AVG(salary) 平均工资,job_id,department_id
FROM employees
GROUP BY job_id,department_id
ORDER BY 平均工资 DESC;

进阶六：连接查询

1、分类

按年代分类
sql92：仅支持内连接
sql99【推荐】：支持内连接+外连接（左外，右外）+交叉连接

按功能分类
(1)内连接：
等值连接,非等值连接,自连接
(2)外连接：
左外连接,右外连接,全外连接
(3) 交叉连接：

一、sql92标准：直只支持内连接

①、多表等值连接的结果为多表的交集
②、n个表连接至少需要n-1个连接条件
③、多表的顺序没有要求
④、一般需要为表起别名，用了别名就不能再用原来的表名了
⑤、可以搭配前面所有的子句使用：where、group by 、(两者的后面一般不用别名)order by（可以使用别名）

查询女神和对应的男神的名字

SELECT NAME,boyName FROM beauty,boys
WHERE beauty.boyfriend_id=boys.id;
#其实他还是笛卡尔积的形式去匹配，只不过有where条件进行了筛选

查询员工名和对应的部门名

SELECT last_name,department_name FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;

为表起别名，如果用了别名，在限定时就不能再用原来的表名了

查询员工名，工种号、工种名

SELECT last_name,a.job_id,job_title
FROM employees a,jobs b
WHERE a.`job_id`=b.`job_id`;
#当要查的字段在两个表中都存在时，要通过表名进行限定

加筛选条件 where 等值连接后用 and 关键词连接

查询城市中第二个字符为o的部门名和城市名

SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
AND l.`city` LIKE '_o%';

加分组条件

查询有奖金的每个部门的部门名和领导编号以及最低工资

SELECT department_name,e.manager_id,MIN(salary)
FROM departments d ,employees e
WHERE d.`department_id`=e.`department_id`
AND e.`commission_pct` IS NOT NULL
GROUP BY department_name,e.manager_id;

有了group by就要时刻关注查询的后面的字段除了聚合函数之外有没有都写上,不写在group by后面默认的是查询的字段一一对应，最好都写上，尽管分组也用不到

ps:

最后根据e.namage_id 和 根据 d.manager_id分组的结果是不一样的
根据employees表的领导分，会出现Sal部门里的多个领导
根据departments表的领导分，只会出现一个领导
employees里有departments不存在的领导编号【好像发现了什么，哈哈】

实现三表连接 (表的顺序无所谓）

查询员工名、、部门名、城市

SELECT last_name,department_name,city
FROM departments d ,locations l,employees e 
WHERE d.`location_id`=l.`location_id`
AND e.`department_id`=d.`department_id`;

非等值连接 （表的顺序没有关系，之后也可正常加其他子句）

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 salary,grade_level
FROM employees e ,job_grades j
WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`;

自连接

查询员工名和他的领导的名字
首先，这些字段都在一张表里
其次，我根据last_name找到manager_id,然后根据manager_id去找emploee_id(emploee_id实际就是要找的manager_id）

select e.last_name,e.employee_id,m.employee_id,m.last_name
from employees e ,employees m
where e.manager_id=m.`employee_id`;

sql99

1.语法：

select 字段列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where】
【group by】
【order by】

2.连接类型：

内连接：inner
    等值连接
    非等值连接
    自连接
外连接：
    左外：left 【outer】
    右外：right【outer】
    全外：full 【outer】
交叉连接：cross

一、内连接 inner join （inner可以省略）

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 jobs j
inner join employees e
on j.`job_id`=e.`job_id`
where last_name like '%e%';

案例3：查询每个城市部门个数>3的城市名和部门个数(分组+筛选）

select city,count(*) 部门个数
from locations l
inner join departments d
on l.`location_id`=d.`location_id`
group by l.`city`
having 部门个数>3;

案例4：查询每个部门的员工个数>3的员工个数和部门名，并按个数降序排列

select count(*) 员工个数,department_name
from employees e
inner join departments d
on d.`department_id`=e.`department_id`
group by d.department_name
having count(*)>3
order by 员工个数 desc;

案例5：查询员工名、工种名、部门名，并按部门名降序(三表连接）

select last_name,job_title,department_name
from employees e 
inner join jobs j on e.`job_id`=j.`job_id`
inner join departments d on d.`department_id`=e.`department_id`
order by department_name desc;

非等值连接

查询员工的工资级别

select salary ,`grade_level`
from employees e
join `job_grades` jg
on e.`salary` between jg.`lowest_sal` and jg.`highest_sal`;

查询每个工资级别的个数>20，降序排列

SELECT 
    #salary ,#写上salary也可以显示，但是不建议写，因为不是group by里的字段
    `grade_level`,count(*)
FROM employees e
JOIN `job_grades` jg
ON e.`salary` BETWEEN jg.`lowest_sal` AND jg.`highest_sal`
group by jg.`grade_level`
having COUNT(*)>20
order by count(*) desc;

自连接

查询员工的名字、领导的名字

select e.last_name,m.last_name
from employees e
join employees m on e.`manager_id`=m.`employee_id`;

二、外连接

内连接干的都是些查询交集的事情，对于不是交集的事情就干不了了
外连接查的是：在一个表中有，另一个表没有的数据，没有的数据用null填充
怎么理解呢？就是两个表先join吧，
此时内连接只选取两个表都有数据的部分
外连接根据主表选数据，不管另一个表的数据是否完整
也就是说，会存在主表的字段有值，从表的字段是null填充的情况

特点：

1、外连接有主从表之分，主表会全部显示。
    对于匹配不上的用null值填充
    对于匹配上的正常显示
2、左外连接左边是主表，右外右边的是主表
3、通过交换两个表的顺序可以实现左外和右外的转换
4、全连接=内连接+表1有但表2没有（用null填充）+表2有表1没有（用null填充）

查询男朋友不在男神表的女神的名字

SELECT g.`name`, b.*
FROM beauty g
LEFT JOIN boys b ON g.`boyfriend_id`=b.`id`#看一下数据

select *
from beauty g
left join boys b on g.`boyfriend_id`=b.`id`
where b.`id` is null;

案例：查询没有员工的部门

#左外
select d.department_id,department_name
from departments d
left join employees e
on d.`department_id`=e.`department_id`
where e.`department_id` is null;

#右外
SELECT d.department_id,department_name
FROM employees e
right JOIN departments d
ON d.`department_id`=e.`department_id`
WHERE e.`department_id` IS NULL;

3、全连接 （不支持全连接查询）只是演示一下

表1与表2的内连接+表1在表2中匹配不到用null填充的部分+表2在表1中匹配不到用null填充的部分

use girls；
select b.*,bo.*
from beauty b
full join boys bo
on b.`boyfriend_id`=bo.`id`

4、交叉连接 (笛卡尔乘积） 不需要on条件

SELECT b.*,bo.*
FROM beauty b
cross JOIN boys bo

—————————做题线——————————
1、查询编号>3的女神的男朋友的信息，如有信息详细列出，没有信息用null填充

select b.id,b.name ,bo.*
from beauty b
left join boys bo
on b.boyfriend_id=bo.id
where b.id>3;

2、查询哪个城市没有部门

select city 
from locations l
left join departments d
on l.`location_id`=d.`location_id`
where d.`department_id` is null;

3、查询部门名为SAL 或者IT的员工的信息
部门表为主表，为什么呢？因为有部门的不一定有员工

select department_name,e.*
from departments d
left join employees e
on e.`department_id`=d.`department_id`
where d.`department_name` ='SAL'
OR D.`department_name`='IT';

in的用法

select department_name,e.*
from departments d
left join employees e
on e.`department_id`=d.`department_id`
where d.`department_name` in('SAL','IT');

看一下连接的各种状况

image.png image.png
说明一下这个beauty和boys表 beauty.png boys.png 表里的信息不一定对，仅供操作实验。
参考出处真心讲的不错，推荐！！！这是跟课手敲的笔记，follow 我也是可以的！