Mysql的分组查询

大一暑假第一次去面试了一个java实习生，面试官疯狂问数据库，很无奈，数据库就学了最基本的增删查改就完全够用了，可是随便吹吹嘛，说了会索引，面试官一问不会，在问一个最简单的分组查询，我也给忘记了害，回家自闭了3 4天，现在开始恶补数据库，做一做学习笔记。
分组查询：
select 分组函数,列(要求出现在group by的后面)
from 表
[where 筛选条件]
grounp by 分组列表
[order by 字句]
注意 查询列表比较特殊，要求分组函数和group by 后出现的字段
特点：
1、分组查询中的筛选条件分为两类
          数据源          位置         关键字
分组前筛选      原始表 ;     group by子句前面     where
分组后筛选      筛选出的表   group by子句后面     having
1.分组函数做为筛选肯定放在having字句中
2.能用分组前筛选的，就优先考虑使用分组前筛选放在where后面
3.grounp by字句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求），表达式函数（用的比较少）
3.也可以添加排序（排序放在整个分组查询的最后）

#案例1：查询每个工种的最高工资

SELECT MAX(salary),job_id 
FROM employees
GROUP BY job_id;

#案列2：查询每个位置上的部门个数

SELECT COUNT(*),location_id
FROM department
GROUP BY location_id;

#案列3:查询邮箱中包含a字符，每个部门的平均工资

select avg(salary),department_id
from employees
where email like '%a%'
group by department_id;

#案例4：查询有奖金的每个领导手下员工的最高工资

select max(salary),manager_id
from employees
where commission_pct is not null
group by manager_id;

难度加强
添加分组后的帅选
添加分组后的筛选就不要用where这个时候需要用的是having

#案例5:查询那个部门的员工个数大于2

拆分：1.查询每个部门的员工个数

select count(*),department_id
from employees
group by department_id;

拆分2：根据拆分1的结构进行筛选,查询那个部门的员工个数大于2

select count(*),department_id
from employees
group by department_id
having count(*) > 2;

#案列6：查询每个工种有奖金的员工的最高工资>12000的工种和最高工资

拆分：1.查询每个工种的最高工资

select max(salary),job_id
from employees
group by job_id;

拆分:在1的基础上筛选出有奖金的员工和最高工资大于>12000

select max(salary),job_id
from employees
where commission_pct is not null
group by job_id
having max(salary)>120000;

#案列7：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资

拆分：1.查询领导编号>102的最低工资

select min(salary),manager_id
from employees 
where manager_id>102
group by manager_id;

拆分2：在1的基础上筛选最低工资>5000的领导编号是哪个，已经最低工资

select min(salary),manager_id
from employees 
where manager_id>102
group by manager_id
having min(salary)>5000;

#案例7：按员工姓名长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些

拆分1：按照员工长度分组

select count(*) as length_name
from employees
group by length(last_name);

拆分2：在1的基础上筛选员工个数>5的有哪些

SELECT COUNT(*),LENGTH(last_name) as length_name
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*) >5;

#按多个字段分组

#案例8：查询每个部门 每个工种的员工的平均工资

SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;

#案例9：查询每个部门 每个工种的员工的平均工资,并且按平均工资的高低显示

SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id
ORDER BY AVG(salary) DESC;

连接查询
含义：又称为多表查询，当我们查询的字段来自多个表或者用到多个表

笛卡尔乘积现象：表1 有m行 表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

等值连接
1.多表等值连接的结果为多表的交集部分
2.n表连接，至少需要n-1个连接条件
3.多表的顺序没有要求
4.一般需要为表起别名
5.可以搭配前面介绍的所有字句使用，比如排序、分组、筛选

#案列1：查询员工名和对应的部门名

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

#案列2：查询员工名、工种号、工种名
为表取别名：
1.提高语句的简洁度
2.区分多个重名的字段
注意：如果为表气了别名，则查询的字段就不能使用原来的表名去限定

SELECT last_name,e.job_id,job_title
FROM employees  e,jobs  j
WHERE e.`job_id`=j.`job_id`;

#案例3：查询有奖金的员工名、部门名

select last_name,d.department_name
from employees e,departments d
where e.department_id = d.department_id
and e.commission_pct IS NOT NULL;

#案列4:查询城市名中第二个字符为o的部门名和城市名

select l.city,d.department_name
from locations l,departments d
where l.city like '_o%' and l.location_id= d.location_id;

#案列5：查询每个城市的部门个数

select count(*) 个数,city
from locations l,departments d
where l.location_id = d.location_id
group by city;

#案列6：查询有奖金的每个部门的部门名和部门领导编号和该部门的最低工资

select d.department_name,d.manager_id,min(salary)
from departments d,employees e
where d.department_id = e.department_id and commission_pct IS NOT NULL
group by manager_id;

#案列7:#查询出每个工种的工种名和员工的个数，并且按员工个数降序

select job_title,count(*) 个数
from jobs j,employeese
where j.job_id = e.job_id
group by job_title
order by count(*) desc;

三表连接查询
#案列8:查询员工名、部门名和所在的城市
在筛选出城市中含有s的字符城市，降序排序

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
HAVING  city LIKE 's%'
ORDER BY city DESC

表明job_grades

非等值连接

#案列1：根据上个等级表查询员工的工资和工资级别，显示员工A的等级

select salary,grade_level
from employees e,job_grades g
where salary between g.lowest_sal and g.highest_sal
and g.grade_level = 'A'

#自连接
#案例1：查询 员工名和上级的名称

SELECT e.last_name,e.employee_id,m.last_name,m.employee_id
FROM employees e,employees m
WHERE e.manager_id = m.employee_id;

sql 99语法
语法
  select 查询列表
  from 表1 别名 【连接类型】
  inner  join 表2 别名
  on 连接条件
  【where 筛选条件】
  【group by 分组】
  【having 筛选条件】
  【order by 排序列表】

内连接:inner

外连接
  左外:left 【outer】
  右外:right 【outer】
  全外:full 【outer】
交叉连接:cross

等值连接
#案例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 employees e
INNER JOIN jobs j
ON e.`job_id` = j.`job_id`
WHERE last_name LIKE '%e%';

#案列3:#查询部门个数>3的城市名和部本个数，(分组+筛选)

SELECT COUNT(*),l.city
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY l.`city`
HAVING COUNT(*) > 3;

#案列4:查询那个部门的部门员工个数>3的部门名和员工个数，并按个数降序(添加排序)

select count(*),department_name
from employees e
inner join departments d
where e.department_id = d.department_id
GROUP BY department_name
having count(*) > 3
order by count(*) desc;

#5.查询员工名、部门名、工种名，并按部门名排序

SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.`department_id` = d.`department_id`
INNER JOIN jobs j ON e.`job_id` = j.`job_id`
ORDER BY department_name DESC;