SQL子查询高级

书写多列子查询 在 from 子句中使用子查询 在SQL中使用单列子查询 书写相关子查询 使用 exists 和not exists 操作符 使用子查询更新和删除数据 使用 with 子句

子查询

子查询是嵌套在 SQL 语句中的另一个SELECT 语句。

子查询，主查询，内查询，外查询。

Select …..from …..where …..order by….

单列子查询，主查询与子查询返回的单列数据进行比较。

多列子查询,主查询与子查询返回的多个列进行比较

多列子查询的成对比较和不成对比较

单列子查询表达式是在一行中只返回一列的子查询

成对比较

查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id

select emoloyee_id,manager_id,department_id

from employees

where manager_id in (

select manager_id

`from employees

where deployee_id in (141,174))

and department_id in (

select department_id

from employees

where deployee_id in ( 141,174)

)

and employee_id not in (141,174);

不成对比较：

查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id

select employee_id,manager_id,department_id

from employees

where ( manager_id, department_id ) in (

select manager,department

from emloyees

where employee_id in ( 141 ,174))

and employee_id not in (141,174)

在from字句中使用子查询：

–返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资

select last_name,department_id,salary,(列出员工部门平均工资）

from employees e1

where salary >(

select avg(salary)

from employees e2

where e1.department_id = e2.department_id

group by department_id)

员工部门平均工资

( select avg(salary) from employee e3

where e1.department_id = e3.department_id)

如上问题使用from字句

问题：返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资

select a.last_name,a.department_id ,a.salary,b.sal

from employees a ,(

select department_id,avg(salary) sal

from employees

group by department_id ) b

where a.department_id = b.department_id

and a.salary >b.sal

在case表达式中使用单列子查询

问题：显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’,其余则为’USA’。`select employee_id,last_name, case department_id when (

select department_id

from departments

where location_id = 1800) then’Canada’

else ‘USA’ end as ” location”

from employees

在order by字句中使用单列子查询：

问题：查询员工的employee_id,last_name,要求按照员工的department_name排序

select employee_id ,last_name

from employees e

order by ( select department_name

from departments d

where e.department_id = d.department_id)

相关子查询：

相关子查询按照一行接一行的顺序执行，主查询的每一行查询都执行一次完整的子查询。

例如返回比本部门平均工资高的员工信息，就属于 相关子查询。

而返回比‘Albel’工资高的员工信息则不属于 相关子查询

问题： 若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id

select employee_id ,last_name ,job_id

from employees e1

where 2<= (

select count(*)

from job_history b

where e1.employee_id = b.employee_id)

exists 操作符

EXISTS 操作符检查在子查询中是否存在满足条件的行 1，如果在子查询中存在满足条件的行:不在子查询中继续查找,条件返回 TRUE。 2，如果在子查询中不存在满足条件的行，条件返回 FALSE， 继续在子查询中查找。

问题：查询公司管理者的employee_id,last_name,job_id,department_id信息

方法一：

select e1.employee_id,e1.last_name,e1.department_id

from employees e1,employees e2

where e1.employee_id = e2.manager_id

方法二：

select employee_id,last_name,department_id

from employees e1

where e1.employee_id in (

select manager_id

from employees e2

where e1.employee_id = e2.manager_id)

方式三

select employee_id,last_name,department_id

from employees e1

where exists (

select ’ A’

from employees e2

where e1.employee_id = e2.manager_id)

问题：查询departments表中，不存在于employees表中的部门的department_id和department_name

select department_id,department_name

from departments d

where not exists (

select ‘A’

from employees

where department_id = d.department_id)

相关更新：

使用相关子查询依据一个表中的数据更新另一个表的数据。

问题：employees表中添加一列department_name，并为每一行赋相应值。

alter table employees

add (department_name varchar(12));

update employees e

set deparment_name = (

select department_name

from departments

where department_id = e.department_id)

删除表employees中，其与emp_history表皆有的数据

DELETE FROM employees EWHERE employee_id in (SELECT employee_id FROM emp_history WHERE employee_id = E.employee_id);