--1.含in谓词的子查询--基于单表的含in谓词的子查询 查询职工号与001进行过相同培训的职工号select wid --查询谁和001进行了相同培训
from study
where wid <> '001'and study_id in
( --查询001职工进行了那些培训
select study_id
from study
where wid = '001'
)
--基于多表的含in谓词的子查询 查询职工号与001进行过相同培训的姓名
select wname --查询姓名
from worker
where wid in
(select wid --查询谁和001进行了相同培训
from study
where wid <> '001'and study_id in
( --查询001职工进行了那些培训
select study_id
from study
where wid = '001'
))
2.
--2.含比较运算符的子查询--单独使用比较运算符的子查询 查询2011年1月的实发工资低于平均实发工资的姓名和职工号select wname,wid
from worker
where wid in
(
select wid
from salary
whereYEAR(sdate) = 2011andMONTH(sdate) = 1and actualsalary <
(
selectAVG(actualsalary)
from salary
whereYEAR(sdate) = 2011andMONTH(sdate) = 1
))
--与any或all同时使用的子查询 查询比职工号为1的职工年龄都小的职工姓名和出生年月
select wname,wbirthdate
from worker
where wbirthdate> all --all为比多个数据都小的
(
select wbirthdate
from worker
where depid = '1'
)
select wname,wbirthdate
from worker
where wbirthdate> any --any为比任意一个数据小的
(
select wbirthdate
from worker
where depid = '1'
)
--等价的多表连接查询 显示最高工资的职工所在的部门名
select dname as 部门名 --得到部门名
from depart
where did =
(select depid --得到部门号
from worker
where wid=
(
select wid --得到职工号
from salary
where totalsalary =
(
selectMAX(totalsalary) --得到最高工资
from salary
)))
--等价的多表连接查询
select dname as 部门名 --得到部门名
from depart innerjoin worker on worker.depid = depart.did innerjoin salary on worker.wid = salary.wid
where totalsalary =
(
selectMAX(totalsalary) --得到最高工资
from salary
)
3.
--3.子查询代替表达式--显示所有职工的职工号，姓名和平均工资 聚集函数select worker.wid,wname,AVG(totalsalary) as 平均工资
from worker innerjoin salary on worker.wid = salary.wid
groupby worker.wid,wname
--与上一个等价的SQL语句 利用子查询
select wid,wname,
(selectAVG(totalsalary) from salary where worker.wid = salary.wid) as 平均工资
from worker
4.
--4.exists谓词子查询--存在测试 查询所有进行过岗前培训的职工号和职工姓名select wid,wname
from worker
whereexists
(
select *
from study
where worker.wid = study.wid and study_name = '岗前培训'
)
--不存在测试 查询所有未进行过岗前培训的职工号和职工姓名
select wid,wname
from worker
wherenotexists
(
select *
from study
where worker.wid = study.wid and study_name = '岗前培训'
)
