MySQL子查询--代码示例

-- 子查询

-- 查询SMITH的工作地点

-- (SELECT deptno FROM scott.emp WHERE ename='SMITH')=20

SELECT loc FROM scott.dept WHERE deptno=(SELECT deptno FROM scott.emp WHERE ename='SMITH');

-- 查询SMITH的领导姓名

-- (SELECT mgr FROM scott.emp WHERE ename='SMITH')=7902

SELECT ename FROM scott.emp WHERE empno=(SELECT mgr FROM scott.emp WHERE ename='SMITH');

-- 查询不在北京工作的员工信息

SELECT * FROM scott.emp WHERE deptno !=(SELECT deptno FROM scott.dept WHERE loc='北京');

SELECT * FROM scott.emp WHERE deptno in(SELECT deptno FROM scott.dept WHERE loc!='北京');

  -- 给在北京工作的所有员工加薪1块

UPDATE scott.emp SET sal=sal+1 WHERE deptno=(SELECT deptno FROM scott.dept WHERE loc='北京');

  -- 让JAMES所有在部门中，工资在1500以下的员工，加薪7块

-- update和delete语名中嵌套子查询时，修改的表和子查询的表是同一样张表时需要加一层嵌套

UPDATE scott.emp

SET sal=sal+7

WHERE deptno in(SELECT deptno from (SELECT deptno FROM scott.emp WHERE ename='JAMES') t)

AND sal<1500;

-- 查询公司平均每个部门的人数（没人数的部门不参与计算）

-- 先查出每个部门的人数

    -- 再求平均值

SELECT avg(xxx.n) from

(SELECT deptno,count(*) n FROM scott.emp GROUP BY deptno) xxx;

-- 查询工资高于公司平均工资的员工

SELECT * FROM scott.emp WHERE sal>(SELECT avg(sal) FROM scott.emp);

-- 查询工资与公司平均工资差距在500以内的员工信息

SELECT * FROM scott.emp WHERE sal-(SELECT avg(sal) FROM scott.emp) BETWEEN -500 and 500;

-- 查询在SMITH所在的部门人数占公司总人数的多少

SELECT

(SELECT count(*) FROM scott.emp WHERE deptno=(SELECT deptno FROM scott.emp WHERE ename='SMITH'))/

(SELECT count(*) FROM scott.emp)

FROM dual;

-- 把和ALLEN同年入职的员工全部删掉

DELETE

FROM

scott.emp

WHERE

DATE_FORMAT(hiredate, '%Y') = (

SELECT

T.D

FROM

(

SELECT

DATE_FORMAT(hiredate, '%Y') D

FROM

scott.emp

WHERE

ename = 'ALLEN'

) T

);

-- 查询工资大于员工所在部门平均工资的员工信息   

SELECT * FROM scott.emp t WHERE sal>(SELECT avg(sal) FROM scott.emp WHERE deptno=t.deptno);