MySQL分组查询，连接查询

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

#查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

#查询邮箱中包含a字符的每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

#查询有奖金的每个领导手下的员工的最高工资  
SELECT MAX(salary),employee_id
FROM employees
WHERE commission_pct is not NULL
GROUP BY manager_id;

#查询哪个部门的员工个数>2 （分组后 HAVING）
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;

#查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct is not null
GROUP BY job_id
HAVING MAX(salary)>12000;

#查询领导编号>102的每个领导手下的最低工资>5000的领导编号，以及其最低工资
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;

#总结一下 GROUP BY ,HAVING
#分组前筛选   原始表  GROUP BY子句的前面   WHERE
#分组后筛选   分组后的结果表   GROUP BY子句的后面  HAVING


#按表达式或函数分组
# 按员工姓名的长度分组查询每一组的员工个数，筛选员工个数>5的有那些
SELECT COUNT(*),LENGTH(last_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;

#查询每个部门每个工种的员工的平均工资，并按平均工资高低显示

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

#查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id is NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;

#查询所有部门的编号，员工数量和工资平均值，并按平均工资降序
SELECT department_id,COUNT(*),AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC;


#连接查询
#等值连接
#查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;

#查询员工名，工种号，工种名 (为表起表名）
SELECT last_name,e.job_id,job_title
FROM employees as e,jobs as j   #(为表起表名，提高语句简洁度，如果为表起了别名，则查询的字段不能用原始名字了）
WHERE e.job_id=j.job_id;

#两个表的顺序可以调换
SELECT last_name,e.job_id,job_title
FROM jobs as j,employees as e
WHERE e.job_id=j.job_id;

#可以加筛选
#查询有奖金的员工名，部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id AND employees.commission_pct is not null;

#查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments,locations
WHERE departments.location_id=locations.location_id
AND city LIKE '_o%';

#可以加分组
#查询每个城市的部门个数
SELECT COUNT(*) AS 部门个数,city AS 每个城市
FROM departments,locations
WHERE departments.location_id=locations.location_id
GROUP BY city;

#查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,departments.manager_id,MIN(salary)
FROM departments,employees
WHERE departments.department_id=employees.department_id
AND employees.commission_pct is not null
GROUP BY departments.department_id

#可以加排序
#查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM employees,jobs
WHERE employees.job_id=jobs.job_id
GROUP BY employees.job_id
ORDER BY COUNT(*) DESC;

#实现三表连接
#查询员工名，部门名，所在城市
SELECT last_name,department_name,city
FROM employees,departments,locations
WHERE departments.department_id=employees.department_id
AND departments.location_id=locations.location_id

#非等值连接
#查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees,job_grades
WHERE salary BETWEEN job_grades.lowest_sal AND job_grades.highest_sal


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 e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.manager_id=m.employee_id;