mysql查询每个部门的最低工资 mysql查各部门最高工资

1. 分组查询

# 语法
select ...
from ...
[where ...]
[group by ...]
[order by ...]

(0) 注意事项

• where用于分组前筛选，对象是原始数据表
• group用于分组后筛选，对象是分组后的结果集
• 分组函数做条件放到having字句中
• 优先采用分组前筛选
• 添加排序放到最后

(1) 简单分组

样例1

查询每个工种的最高工资

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

样例2

查询每个位置的部门个数

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;

添加复杂筛选条件

查询哪个部门的员工个数>2

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

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

1. 根据1的结果筛选

select count(*), department_id
from employees
group by department_id
having count(*) >2; # 分组后筛选，where属于分组前筛选，针对表不针对组

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

1.查询每个工种有奖金的员工的最高工资

select max(salary),job_id
from employees
where commission_pct is not null
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)>12000;

(2) 表达式分组

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

select count(*) as cnt ,length(last_name) as len
from employees 
group by len
having cnt > 5;

(3) 多字段分组

查询每个部门每个工种的员工的平均工资

select avg(salary) ,department_id, job_id
from employees 
group by department_id, job_id;

(4) 带排序分组

查询每个部门每个工种的员工的平均工资，并排序

select avg(salary) ,department_id, job_id
from employees 
group by department_id, job_id;
order by avg(salary) desc;

2.连接查询（多表查询）

(0) 背景

直接多表查询问题

直接进行多表查询会让第一章表的每一行与第二张表的每一行一一匹配一次。例如 5*6=30

原因：没有有效的连接条件

直接多表查询：笛卡尔乘积
select name,boyname 
from boys,beauty;

修复方式：
select name,boyname 
from boys,beauty
where beauty.boyfriend_id = boys.id;

(1) 分类

按版本：

• SQL92：仅支持内连接
• SQL99：内连接+外连接（不支持全外）+交叉连接

按功能：

• 内连接

• 等值连接
• 非等值连接
• 自连接

• 外连接

• 左外链接
• 右外连接
• 全外连接

• 交叉连接

(2) SQL92标准

等值连接

• 多表等值连接为多表的交集部分
• n表连接至少需要n-1个连接条件
• 多表顺序没有要求
• 一般需要为表AS别名

匹配boyfriend_id和id
select name,boyname 
from boys,beauty
where beauty.boyfriend_id = boys.id;

开发中用别名方式：
select a.name,b.boyname 
from boys a,beauty b
where b.boyfriend_id = a.id;
别名生成之后不能用原名了
select a.name,beauty.boyname # 报错
from boys a,beauty b
where b.boyfriend_id = a.id;

# 查询员工名 工种号 工种名
SELECT 
	e.last_name,e.job_id,j.job_title
FROM # 下面的顺序可以调换
	employees e, jobs j
WHERE
	e.job_id = j.job_id
	
	
# 加入筛选条件
# 查询有将近的员工名和部门名
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;

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

# 加入排序
# 查询每个工种的工种名称和员工个数，并且按照员工的个数进行排序
SELECT job_title,count(*)
FROM  employees e, jobs j
WHERE e.job_id = j.job_id
GROUP BY job_title
ORDER BY count(*) DESC;

# 支持多表连接

非等值连接

上面的e.job_id = j.job_id的等于号换成其他连接符即可

# 查询员工的工资级别
SELECT salary,grade_level
FROM employees e,job_grades j
WHERE	salary BETWEEN j.lowest_sal AND j.highest_sal;

自连接

单张表的连接：一张表作为多张表的功能

# 查询员工名以及领导的名字
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;

(3) SQL99标准

语法

select colm
from table1 [连接类型]
join table2 
on 连接条件
where 筛选条件


连接类型：
	内连接：inner(可以省略)
	外连接：
		左外连接：left outer
		右外连接：right outer
		全外连接： full outer
	交叉连接： cross

内连接

# 等值连接
# 查询员工名和部门名
SELECT last_name, department_name
FROM employees e
INNER JOIN departments d
on e.department_id = d.department_id;

# 多表连接就写多个
INNER JOIN ... 
on ...

# 非等值连接
# 查询员工工资级别
SELECT salary,grade_level
FROM employees e
INNER JOIN job_grades j
ON salary BETWEEN j.lowest_sal AND j.highest_sal;

# 自连接
# 同一张表起多个别名即可

外连接

用于查询一个表中有，另一个表没有的。

查询结果分为两部分：

• 符合内连接不服
• NULL部分

查询结果：

• 主表中的所有记录

分类：

• 左外链接：left join 左边的是主表
• 右外连接：right join 右边的是主表

# 查询没有男朋友的女的
SELECT b.name,boys.*
FROM beauty b
LEFT JOIN boys 
on b.boyfriend_id = boys.id
# where boys.id is not NULL 

# 查询哪个部门没有员工
SELECT d.*,e.employee_id
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
WHERE e.employee_id is NULL;

# 全外连接
从表中有，主表中没有的行也可以查出来

交叉连接

结果集是笛卡尔积

SELECT beauty.*, boys.*
FROM beauty
CROSS JOIN boys

3.子查询

(0) 简介

定义

出现在其他语句内部的select语句，称为子查询或者内查询。

内部嵌套了其他select语句的查询语句，称为外查询或主查询

分类

出现的按照位置：

• select

• 支持标量子查询

• from

• 支持表子查询

• where having

• 支持标量子查询（单行子查询）
• 支持列子查询（多行子查询）
• 支持行子查询

• exist （相关子查询）

• 支持表子查询

结果集的行列数不同：

• 标量子查询（结果集只有一行一列）
• 列子查询（一列多行）
• 行子查询（一行多列）
• 表子查询（多行多列）

(1)where having

特点：

• 子查询放到小括号内
• 一般放到条件的右侧
• 标量子查询 搭配单行操作符（条件运算符 < > =）
• 列子查询 搭配多行操作符（in any some all）
• 子查询优先执行

标量子查询-单行子查询

# 谁的工资比Abel高
# 先查询Abel工资
SELECT salary
FROM employees 
WHERE last_name = 'Abel'
# 查询员工的薪资大于value
SELECT * 
FROM employees
WHERE salary > ?
# 整合
SELECT * 
FROM employees
WHERE salary > (
	SELECT salary
	FROM employees 
	WHERE last_name = 'Abel'
);

# 查询员工的姓名，job_id和工资  要求job_id与141号员工相同 salary比143号员工多
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (
	SELECT job_id
	FROM employees
	WHERE employee_id = 141
)
AND salary > (
	SELECT salary
	FROM employees
	WHERE employee_id = 143
);

列子查询-多行子查询

IN / NOT IN: 等于列表中的任意一个
ANY / SOME ： 和子查询返回的某一个值比较
ALL ： 和子查询返回的所有值比较

# 返回location_id是1400或1700部门中所有员工的姓名
# 查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400,1700);

# 查询员工姓名，要求其部门号是上一个中的某一个
SELECT last_name
FROM employees 
WHERE department_id IN (
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN (1400,1700)
);

行子查询

结果集是一行多列或者多行多列

# 查询员工编号最小并且工资最高的员工信息
# 查询最小的员工编号
# 查询最高工资
# 查询员工信息
SELECT MIN(employee_id)
FROM employees;

SELECT MAX(salary)
FROM employees;

SELECT *
FROM employees
WHERE employee_id=(
	SELECT MIN(employee_id)
	FROM employees
)AND salary=(
	SELECT MAX(salary)
	FROM employees
);

# 行子查询的格式
SELECT *
FROM employees
WHERE (employee_id,salary) =(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);

(3) select

# 查询每个部门员工的个数
SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.department_id
)
FROM departments d;

(4)from

子查询结果充当一张表，并且必须起别名，否则无法找到。

# 每个部门平均工资的工资等级
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;

SELECT * FROM job_grades;

SELECT ag_dep.*, g.grade_level
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
)ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

(5)exists

exists(查询语句)

结果为 1或者0

# 查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.department_id = e.department_id
);