Mysql子查询

-- 1.单行单列子查询
-- 查询公司之中工资最低的雇员的完整信息
SELECT * FROM emp WHERE sal=(SELECT MIN(sal) FROM emp);

-- 查询出基本工资比ALLEN低的全部雇员信息
select * from emp where sal<(select sal from emp where ename='ALLEN')

-- 查询基本工资高于公司平均薪金的全部雇员信息
select * from emp where sal>(select avg(sal) from emp)

---

 

-- 2.单行多列子查询
-- 查找出与ALLEN从事同一工作，并且基本工资高于雇员编号为7521的全部雇员信息,
select * from emp where job=(select job from emp where ename='ALLEN') AND sal>(select sal from emp where empno='7521')

-- 查询与SCOTT从事同一工作且工资相同的雇员信息 ,ename <> 'SCOTT'含义是不包括SCOTT本人
select * from emp where job=(select job from emp where ename='SCOTT') and sal=(select sal from emp where ename='SCOTT') and ename <> 'SCOTT'

-- 查询与雇员7566从事同一工作且领导相同的全部雇员信息
select * from emp where job=(select job from emp where empno='7566') and mgr=(select mgr from emp where empno='7566')

-- 查询与ALLEN从事同一工作且在同一年雇佣的全部雇员信息（包括ALLEN）
select * from emp where job=(select job from emp where ename='ALLEN') AND (to_char(hiredate, 'yyyy'))=(select to_char(hiredate, 'yyyy') from emp where ename='ALLEN')

---

 

-- 3.多行单列子查询
-- 查询每个部门工资最低的员工信息 ，对于小于2000的不展示
select * from emp where sal in(select MIN(SAL) from emp group by dept_no having sal>2000)

-- 查询每个部门除开工资最低的其他员工信息
select * from emp where sal not in(select MIN(SAL) FROM emp GROUP BY dept_no)

---

 

-- 4.验证exists结构
SELECT * FROM emp WHERE EXISTS(SELECT * FROM emp WHERE empno=9999); -- 返回空值，没有内容输出
SELECT * FROM emp WHERE EXISTS(SELECT * FROM emp) -- 有内容将返回数据
SELECT * FROM emp WHERE NOT EXISTS(SELECT * FROM emp) -- 有数据，但取返，没有内容输出

---

 

5.表中一个外键编号，查询时想显示这个编号对应的具体信息

select mc,lb,(select info from dictinfo where useryy.lb=id )lbmc from USERYY