# 子查询
select * from student where borndate > ( select borndate from student where stuname='崔今生' );
/**
先进行"子查询"后进行"父查询"
**/
①先进行“子查询”
select * from student where studentno =( select studentno from subject where studentresult =100 and subjectid=( select subjectid from result where subjectname='JavaSE' ));
②进行“父查询”
select max(studentresult),min(studentresult) from result where subjectid=( select subjectid from subject where subjectname ='HTML和CSS课程' ) and examdate =( select max(examdate) from result where subjectid =( select subjectid from subject where subjectname ='HTML和CSS课程' ) );
# in子查询
--查询"JavaSE"课程考试成绩不及格的学生名单
select stuname from student where studnetno in ( select studentno from result where studentresult<60 and subjectid=( select subjectid from subject where subjectname = 'JavaSE' ) );
# where子查询
--查询员工编号最大的是谁?
select * from s_emp where id =( select max(id) from s_emp );
--查询最大的部门编号是谁?
select * from s_emp where dept_id =( select max(dept_id) from s_emp )
--查询公司所有的领导
select * from s_emp where id in ( select distinct manager_id from s_emp where mangager_id is not null )
# having和from子查询
--查询部门中平均工资大于32号部门平均工资的部门信息(显示部门编号和平均工资)
select dept_id 部门编号,avg(salary)平均工资 from s_emp group by dept_id having avg(salary)>( select avg(salary) from s_emp where dept_id=32 )
--查询员工信息,条件为工资大于1400中,5号员工是谁?
select * from ( select * from s_emp where salary>1400 ) where id =5;
--小结:三个关键字支持子查询:where.../ having.../ from...