9.子查询

1.子查询介绍:

　　(1).含义:

　　(2).分类:

　　(3).特点:

2.出现where和having后面的子查询3.子查询位于select后面

　　(1).标量子查询(单行子查询)

　　(2).列子查询(多行子查询)

　　(3).行子查询(用的较少)

4.子查询位于from后面

5.子查询放在exists后面(相关子查询)

---

1.子查询介绍:

(1).含义:

出现在其他语句中的select语句, 称为子查询或内查询(其他语句包括但不限于SQL的增删改查语句), 内部嵌套其他select语句的查询, 称为外查询或主查询.

返回顶部

---

 

(2).分类:

1).按子查询的出现位置:
　　select后面:标量子查询
　　from后面:支持表子查询
　　where或having后面:\*****标量子查询(单行子查询)\*****,列子查询(多行子查询),行子查询(行子查询用的较少)
　　exits后面

2).按结果集的行列数不同:
　　标量子查询(结果集只有一行一列)
　　列子查询(结果集只有一列多行)
　　行子查询(结果集有一行多列)
　　表子查询(结果集一般为多行多列)

 

返回顶部

---

 

(3).特点:

(1).子查询放在小括号内
(2).子查询一般放在条件的右侧
(3).标量子查询,一般搭配着单行操作符使用:>,<,=,>=,<=,<>
　  列子查询,一般搭配多行操作符使用:in, any/some, all
(4).子查询的执行优先于主查询,主查询要应用子查询的结果

 

返回顶部

---

2.出现where和having后面的子查询

(1)标量子查询(单行子查询)

# 案例1: 查询谁的工资比Abel高

# 分析:
　　　　1).查询出Abel的工资
　　　　　　select salary
　　　　　　from employees
　　　　　　where last_name='Abel';
　　　　2).查询员工信息,筛选条件为工资大于上面的查询结果(最后整合的查询语句)
　　　　　　select*
　　　　　　from employees
　　　　　　where salary>(
　　　　　　select salary
　　　　　　from employees
　　　　　　where last_name ='Able'
　　　　　　);

# 案例1最后的查询语句:
　　　　　　select*
　　　　　　from employees
18　　　　　　 where salary>(
　　　　　　select salary
　　　　　　from employees
　　　　　　where last_name ='Able'
22　　　　　　 );

 

返回顶部

# 案例2: 返回job_id与141号员工相同, salary比143号员工多的员工的姓名, job_id和工资

# 分析:
　　　　1).查询141号员工的job_id
　　　　　　select job_id
　　　　　　from employees
　　　　　　where employee_id=141;
　　　　2).查询143号员工的工资
　　　　　　select salary
　　　　　　from employees
　　　　　　where employee_id=143;
　　　　3).查询员工的姓名, job_id,工资,筛选条件为job_id等于1的查询结果,工资大于2的查询结果
　　　　　　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
　　　　　　);

# 案例2最后的查询语句:
　　　　　　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
　　　　　　);

 

返回顶部

# 案例3: 返回工资工资最少的员工的last_name, job_id 和 salary

# 分析:
　　　　1).查询公司的最低工资
　　　　　　select min(salary)
　　　　　　from employees;
　　　　2).查询员工的名字, job_id, salary,筛选条件是工资等于上面的查询结果
　　　　　　select last_name, job_id, salary
　　　　　　from empl0yees
　　　　　　where salary =(
　　　　　　select min(salary)
　　　　　　from employees
　　　　　　);

# 案例3最后查询语句:
　　　　　　select last_name, job_id, salary
　　　　　　from empl0yees
　　　　　　where salary =(
17　　　　　　 select min(salary)
　　　　　　from employees
　　　　　　);

 

返回顶部

# 案例4: 查询最低工资大于50号部门最低工资的部门id和其最低工资
# 分析:
　　　　1).查询50号部门的最低工资
　　　　　　select min(salary)
　　　　　　from employees
　　　　　　where department_id =50;
　　　　2).查询出各部门最低工资
　　　　　　select min(salary), department_id
　　　　　　from employees
　　　　　　groupby department_id
　　　　3)查询部门和该最低工资,筛选条件为其最低工资大于上面的查询结果
　　　　　　select min(salary), department_id
　　　　　　from employees
　　　　　　groupby department_id
　　　　　　having (
　　　　　　select min(salary)
　　　　　　from employees
　　　　　　where department_id =50
　　　　　　);

# 案例4最后查询语句:
　　　　　　select min(salary), department_id
　　　　　　from employees
　　　　　　groupby department_id
　　　　　　having (
　　　　　　select min(salary)
　　　　　　from employees
　　　　　　where department_id =50
　　　　　　);

 

(注意: 非法使用标量子查询:子查询结果不是一行一列, 会出现查询错误)

返回顶部

---

(2)列子查询(多行子查询)

多行子查询:
　　　　-返回多行数据
　　　　-使用多行比较操作符:in/notin等于列表中的任意一个
　　　　　　　　　　　　　　any / some 和子查询返回的某一个值比较
　　　　　　　　　　　　　　all 和子查询返回的所有值比较


# 案例1: 返回location_id是1400或1700的部门中的所有员工姓名


# 分析:
　　　　1).查询location_id是1400或1700的部门
　　　　　　select department_id
　　　　　　from departments as d
　　　　　　where d.location_id in(1400,1700);
　　　　2).查询所有的员工姓名,筛选条件为:员工的部门id在上面的查询结果中
　　　　　　select last_name
　　　　　　from employees as e
　　　　　　where e.department_id in(
　　　　　　select department_id
　　　　　　from departments as d
　　　　　　wherer d.location_id in(1400,1700)
　　　　　　);

# 案例1的最终查询语句为:
　　　　　　select last_name
　　　　　　from employees as e
　　　　　　where e.department_id in(
　　　　　　select department_id
　　　　　　from departments as d
　　　　　　wherer d.location_id in(1400,1700)
　　　　　　);

 

返回顶部

# 案例2: 返回其他工种中比job_id为`IT_PROG`工种任一工资低的员工的员工号, 姓名, job_id以及salary

# 分析:
　　　　1).查询job_id为`IT_PROG`的工种的所有工资
　　　　　　select distinct salary
　　　　　　from employees
 6　　　　　　 where job_id =`IT_PROG`;
　　　　2).查询其他员工的员工号,名字, job_id, salary,筛选条件为员工的工资低于上面结果的任一值
　　　　　　select employee_id, last_name, job_id, salary
　　　　　　from employees as e
　　　　　　where e.salary < any(
　　　　　　select salary
　　　　　　from employees
　　　　　　where job_id =`IT_PROG`
　　　　　　)and job_id<>'IT_PROG';

# 案例2的最终查询语句为:
　　　　　　select employee_id, last_name, job_id, salary
　　　　　　from employees as e
　　　　　　where e.salary < any(
　　　　　　select salary
　　　　　　from employees
　　　　　　where job_id =`IT_PROG`
　　　　　　)and job_id<>'IT_PROG';

 

返回顶部

# 案例3:返回其他部门中比job_id为`IT_PROG`部门所有工资都低的员工的员工号, 姓名, job_id以及salary
2　　　　　　 select last_name, employee_id, job_id, salary
　　　　　　from employees
4　　　　　　 where salary < all(
　　　　　　select distinct 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
　　　　　　);

 

返回顶部 

---

 

3.子查询位于select后面

# 案例: 查询每个部门的员工个数
　　　　　　select d.*,(
　　　　　　select count(*)
　　　　　　from employees as e
　　　　　　where e.department_id = d.department_id
　　　　　　)个数
　　　　　　from departments as d;

 

返回顶部

---

4.子查询位于from后面

# 案例: 查询每个部门的平均工资的工资等级


# 分析:
　　　　1).查询每个部门的平均工资
　　　　　　　　select avg(salary), department_id
　　　　　　　　from employees as e
　　　　　　　　groupby department_id;
　　　　2)在上面的查询结果的基础上连表查询
　　　　　　　　select ag_dep.*, g.grade_level
　　　　　　　　from(
　　　　　　　　　　select avg(salary)as ag, department_id # 该表中有两个字段, ag(平均工资), 部门id
　　　　　　　　　　from employees as e
　　　　　　　　　　groupby department_id;
　　　　　　　　　　)as ag_dep
　　　　　　　　inner join job_grades as j
　　　　　　　　on ag_dep.ag between j.lowest_sal and highest_sal;# 连接条件是部门平均工资在工资级别表的相应范围

 

返回顶部

---

5.子查询放在exists后面(相关子查询)

1.语法:
　　exists(完整的查询语句)
2.结果:返回布尔值,1或0
3.执行顺序:先执行主查询,再根据子查询来过滤主查询的结果

# 案例: 查询有员工的部门的部门名
　　　　　　select department_name
　　　　　　from departments as d
　　　　　　where exists(
　　　　　　select*
　　　　　　from employees as e
　　　　　　where d.department_id = e.department_id
　　　　　　);

 

返回顶部