子查询
/*
含义：出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询
分类：
按子查询出现的位置：
select 后面：
仅仅支持标量子查询
from 后面：
支持表子查询
where 或 having 后面
支持标量子查询 （单行）
列子查询 （多行）
行子查询
exists 后面 （相关子查询）
表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）
*/
#一、where 或 having后面
1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、行子查询（多列多行）
特点：
-子查询放在小括号内
-子查询一般放在条件的右侧
-标量子查询，一般搭配着单行操作符使用><>=<=<>
-列子查询，一般搭配着多行操作符使用IN、ANY/SOME、ALL
-子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
#1、标量子查询
#案例1、谁的工资比Abel高
1、查询Abel的工资
select salary
from employees
where last_name = 'Abel'
2、查询员工的信息满足salary>1的结果
select *
from employees
where salary > (
select salary
from employees
where last_name = 'Abel'
)
#案例2、返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
#1、查询141号员工的job_id
select job_id
from employees
where employee_id = 141
#2、查询143号员工的salary
select salary
from employees
where employee_id = 143
#3、查询员工的姓名、job_id和工资，要求job_id=1并且salary>2
select last_name, job_id, salary
where job_id = (
select job_id
from employees
where employee_id = 141
) AND salary > (
select salary
from employees
where employee_id = 143
)
#案例3：返回公司工资最少的员工的last_name, job_id和salary
#1、查询公司的最低公司
select min(salary)
from employees
#2、查询last_name, job_id和salary，要求salary=1
select last_name, job_id, salary
from employees
where salary = (
select min(salary)
from employees
)
#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
#1、查询50号部门的最低工资
select min(salary)
from employees
where department_id = 50
#2、查询每个部门的最低工资
select min(salary)
from employees
GROUP BU department_id
#3、在2基础上筛选，满足min(salary) > 1
select min(salary)
from employees
GROUP BU department_id
having min(salary)(
select min(salary)
from employees
where department_id = 50
)
#非法使用标量子查询，子查询的结果不是一行一列
select min(salary)
from employees
GROUP BU department_id
having min(salary)(
select salary
from employees
where department_id = 50
)
#2、列子查询（多行子查询）
#案例1、返回location_id是1400或1700的部门中的所有员工姓名
#1、查询location_id是1400或1700的部门编号
select distinct department_id
from departments
where location_id IN(1400, 1700)
@2、查询员工姓名，要求部门号是1列表中的某一个
select last_name
from employees
where department_id IN (
select distinct department_id
from departments
where location_id IN(1400, 1700)
)
#案例2、返回其他工种中比job_id为‘IT_PROG’工种任一工资低的员工的工号、姓名、job_id以及salary
#1、查询job_id为‘IT_PROG’部门任一工资
select distinct salary
from employees
where job_id = ‘IT_PROG’
#2、查询员工的工号、姓名、job_id以及salary，salary<any(1)的任意一个
select last_name, employee_id, job_id, salary
from employees
where salary < ANY(
select distinct salary
from employees
where job_id = ‘IT_PROG’
)AND job_id <> ‘IT_PROG’
#或
select last_name, employee_id, job_id, salary
from employees
where salary <(
select MAX( salary)
from employees
where job_id = ‘IT_PROG’
)AND job_id <> ‘IT_PROG’
#案例3：返回其他工种中比job_id为‘IT_PROG’工种所有工资低的员工的工号、姓名、job_id以及salary
select last_name, employee_id, job_id, salary
from employees
where salary < ALL(
select distinct salary
from employees
where job_id = ‘IT_PROG’
)AND job_id <> ‘IT_PROG’
#或
select last_name, employee_id, job_id, salary
from employees
where salary <(
select MIN( salary )
from employees
where job_id = ‘IT_PROG’
)AND job_id <> ‘IT_PROG’
#3、行子查询<结果集一行多列，多行多列>
#案例：查询员工编号最小并且工资最高的员工信息
select *
from employees
where (employee_id, salary) = (
select MIN(employee_id), MAX(salary)
from employees
)
#1、查询最小的员工编号
select MIN(employee_id)
from employees
#2、查询最高工资
select MAX(salary)
from employees
#3、查询员工信息
select *
from employees
where employee_id = (
select MIN(employee_id)
from employees
) AND salary = (
select MAX(salary)
from employees
)
#二、select 后面，仅仅支持标量子查询
#案例：查询每个部门的员工个数
select d.*, (
select count(*)
from employees e
where e.department_id = d.department_id
) 个数
from departments d
#案例二：查询员工号=102的部门名
select (
select department_name
from departments d
inner join employees e
ON d.department_id = e.department_id
where e.employee_id = 102
)
#三、from后面
/*
将子查询结果充当一张表，要求必须起别名
*/
#案例：查询每个部门的平均工资的工资等级
#1、查询每个部门的平均工资
selec AVG(salary), department_id
from employees
GROUP BY department_id
#2、连接1的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal
select ag_dep.*, g.grade_level
from (
selec 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
#四、exists后面 （相关子查询）
/*
语法：
exists (完整的查询语句)
结果：
1或者0
*/
select EXISTS(select employee_id from employees where salary = 30000)
#案例1：查询有员工的部门名
#in
select department_name
from departments d
where d.department_id IN (
select department_id
from employees
)
#exists
select epartment_name
from departments
where EXISTS (
select *
from employees e
where d.department_id = e.department_id
)
#案例2：查询没有女朋友的男神信息
# in
select bo.*
from boys bo
where bo.id NOT IN (
select boyfriend_id
from beauty
)
#exists
select bo.*
from boys bo
where NOT EXISTS (
select boyfriend_id
from beauty b
where bo.id = b.boyfriend_id
)
#练习
#1查询和zlotkey相同部门的员工姓名和工资
#1、查询zlotkey的部门
select departmetn_id
from employees
where last_name = 'zlotkey'
#2、查询部门号=1的姓名和工资
select last_name, salary
from employees
where department_id = (
select departmetn_id
from employees
where last_name = 'zlotkey'
)
#2.查询工资比公司平均工资搞的员工的员工号、姓名和工资
#1.查询平均工资
select AVG(salary)
from employees
#2.查询工资>1的员工号，姓名和工资
select last_name, employees_id, salary
from employees
where salary > (
select AVG(salary)
from employees
)
#3、查询各部门中工资比本部门平均工资搞的员工的员工号，姓名和工资
#1.查询各部门的评平均工资
select AVG(salary), department_id
from employees
GROUP BY department_id
#2.连接1结果集和employees表
select employee_id, last_name, salary, e.department_id
from employees e
inner join (
select AVG(salary) ag , department_id
from employees
GROUP BY department_id
) ag_dep
ON e.department_id = ag_dep.department_id
where salary > ag_dep.ag
#4、查询姓名中包含字母u的员工在相同部门的员工号和姓名
#1.查询姓名中包含字母u的员工的部门‘
select distinct department_id
from employees
where last_name LIKE '%u%'
#2.查询部门号=1中的任意一个的员工号和姓名
select last_name, employee_id
from employees
where department_id IN (
select distinct department_id
from employees
where last_name LIKE '%u%'
)
#5.查询在部门的location_id 为1700的部门工作的员工的员工号
#1.查询在部门的location_id 为1700的部门
select departmetn_id
from departments
where location_id = 1700
#2.查询部门号=1中的任意一个的员工号
select employee_id
from employees
where departmetn_id = ANY (
select departmetn_id
from departments
where location_id = 1700
)
#6、查询管理者是king的员工姓名和工资
#1.查询姓名为king的员工编号
select employee_id
from employees
where last_name = 'K_ing'
#2.查询哪个员工的manager_id = 1
select last_name, salary
from employees
where manager_id IN (
select employee_id
from employees
where last_name = 'K_ing'
)
#7、查询工资最高的员工的姓名，要求frist_name和last_naem 显示为一列，列名为姓,名
#1.查询最高工资
select MAX(salary)
from employees
#2.查询工资=1的姓，名
select CONCAT(first_name, last_name) "姓.名"
from employess
where salary = (
select MAX(salary)
from employees
)
#进阶8：分页查询
/*
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
select 查询列表
from 表
【join type join 表2
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit offset, size;
offset要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数
特点：
-limit语句放在查询语句的最后
-公式：要显示的页数page，每页的条目数size
select 查询列表
from 表
limit (page - 1) * size, size
*/
#案例1：查询前五条员工信息
select * from employees limit 0,5
select * from employees limit 5
#案例2：查询第11条---第25条
select * from employees limit 10,15
案例3：有奖金的员工信息，并且工资较高的前10名显示出来
select * from employees where commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 10
#作业
#1、查询工资最低的员工信息
#1.查询最低的工资
select MIN(salary)
from employees
#2.查询last_name, salary要求salary=1
select last_name, salary
from employees
where salary = (
select MIN(salary)
from employees
)
#2、查询平均工资最低的部门信息
#方式一
#1.各部门的平均工资
select AVG(salary), department_id
from employees
GROPU BY ddepartment_id
#2.查询1结果上的最低平均工资
select MIN(ag)
from (
select AVG(salary) ag , department_id
from employees
GROPU BY ddepartment_id
) ag_dep
#3.查询哪个部门的平均工资=2
select AVG(salary), department_id
from employees
GROPU BY ddepartment_id
having AVG(salary) = (
select MIN(ag)
from (
select AVG(salary) ag , department_id
from employees
GROPU BY ddepartment_id
) ag_dep
)
#4.查询部门信息
select d.*
from departments d
where d.department_id = (
select department_id
from employees
GROPU BY ddepartment_id
having AVG(salary) = (
select MIN(ag)
from (
select AVG(salary) ag , department_id
from employees
GROPU BY ddepartment_id
) ag_dep
)
)
#方式二
#1.各部门的平均工资
select AVG(salary), department_id
from employees
GROPU BY ddepartment_id
#2.求出最低平均工资的部门编号
select AVG(salary), department_id
from employees
GROPU BY ddepartment_id
ORDER BY AVG(salary)
LIMIT 1
#.3.查询部门信息
select *
from departments
where = department_id = (
select AVG(salary), department_id
from employees
GROPU BY ddepartment_id
ORDER BYAVG(salary)
LIMIT 1
)
#3、查询平均工资最低的部门信息和该部门的平均工资
#1.各部门的平均工资
select AVG(salary), department_id
from employees
GROPU BY ddepartment_id
#2.求出最低平均工资的部门编号
select AVG(salary), department_id
from employees
GROPU BY ddepartment_id
ORDER BY AVG(salary)
LIMIT 1
#3.查询部门信息
select d.*, ag
from departments d
join (
select AVG(salary), department_id
from employees
GROPU BY ddepartment_id
ORDER BY AVG(salary)
LIMIT 1
) ag_dep
ON d.department_id = ag_dep.department_id
#4、查询平均工资最高job信息
#1.查询每个job的平均工资
select AVG(salary), job_id
from employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1
#2.查询job信息
select *
from jobs
where job_id = (
select job_id
from employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1
)
#5、查询平均工资高于公司平均工资的部门有哪些
#1.查询平均工资
select AVG(salary)
from employees
#2.查询每个部门的平均工资
select AVG(salary), department_id
from employees
GROUP BY department_id
#3.筛选2结果集，满足平均工资>1
select AVG(salary), department_id
from employees
GROUP BY department_id
HAVING AVG(salary) (
select AVG(salary)
from employees
)
#6、查询出公司中所有manager的详细信息
#1.查询所有manage的员工编号
select DISTINCT manager_id
from employees
#2.查询详细信息，满足employe_id=1
select *
from employees
where employee_id = ANY (
select DISTINCT manager_id
from employees
)
#7、各个部门中，最高工资中最低的那个部门的最低工资是多少
#1.查询各部门的最高工资中最低的部门编号
select department_id
from employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1
#2.查询1结果的那个部门的最低工资
select MAX(salary), departmetn_id
from employees
where department_id = (
select department_id
from employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1
)
#8、查询平均工资最高的部门的manage的详细信息:last_name, departmetn_id, email, salary
#1.查询平均工资最高的部门编号
select department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
#2.讲employees和departments连接查询，筛选条件是1
select last_name, d.departmetn_id, email, salary
from employees e
INNER JOIN departments d ON d.manager_id = e.employee_id
where d.department_id = (
select department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT
)
#进阶9：联合查询
/*
union 联合 合并：讲多条查询语句的结果合并成一个结果
语法：
查询语句1
union
查询语句2
union
...
应用场景：
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时
特点：
1、要求多条查询语句的查询列表数一致的
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union关键字默认去重，如果使用union all 可以包含重复项
*/
# 引入的案例：查询部门编号>90或邮箱中包含a的员工信息
select * from employees where email LIKE '%a%' OR department_id > 90;
select * from employees where email LIKE '%a%'
UNION
select * from employees wheredepartment_id > 90;
#案例：查询中国用户中性别=男的信息以及外国用户中性别=男的用户信息
select id, cname, csex from t_ca where csex = '男'
UNION ALL
select t_id, tName, tGender from t_ua where tGender = 'male'
语法：
select 查询列表 7
from 表1 别名 1
连接类型 join 表2 2
on 连接条件 3
where 筛选 4
group by 分组列表 5
having 筛选 6
order by 排序列表 8
limit 其实条目索引，条目数 9
网友评论