MySQL read 查询语句6 多表查询，子表查询

-- 多表查询
-- 显示雇员名ename，工资sal，所在部门名字dname,部门编号deptno
/*分析
  1.名字工资来自emp表，部门名称来自dept表
  2.需要对emp和dept表查询
  3.需要指定显示表的列时，需要 表名.列名
*/
SELECT ename,sal,dname,emp.deptno FROM emp,dept
    WHERE emp.deptno = dept.deptno;

-- 显示部门号为10的部门名，员工名和工资级别
SELECT ename,sal,dname,emp.deptno FROM emp,dept
    WHERE emp.deptno = dept.deptno AND emp.deptno = 10;
    
-- 显示各个员工的姓名，工资，及其工资的级别
-- 姓名，工资在emp表  工资级别在salgrade表
SELECT ename, sal, grade  FROM emp, salgrade
    WHERE emp.sal BETWEEN salgrade.losal AND
        salgrade.hisal;

-- 显示雇员名，工资及所在部门名字，并按照部门名降序
SELECT ename, sal,emp.deptno FROM emp,dept
    WHERE emp.deptno = dept.deptno
    ORDER BY emp.deptno
    
-- 子连接 （同一张表当作两张表来完成目标显示）
-- 在员工表中显示 员工和员工的上司
-- 员工和上级 通过emp的mgr联系
-- 特点 1.一张表当作两张表使用
--      2.需要给emp取别名（表名 表的别名）
--      3.如果列名不明确，可以指定列的别名 使用AS

SELECT worker.ename AS '职员' ,boss.ename AS '上级'
    FROM emp worker ,emp boss
    WHERE worker.mgr = boss.empno

-- 子查询（嵌套查询）
-- 查与smith相同部门号的所有人
/* 1.先查询smith的部门号
   2.在把查询smith当作子语句使用嵌套
*/
SELECT deptno FROM EMP
    WHERE ename = 'SMITH'
    
SELECT * FROM emp
    WHERE deptno =
    (SELECT deptno FROM emp
    WHERE ename = 'SMITH');
    
-- 多行子查询
-- 查询和部门10的工作相同的雇员的
/*查询10号部门的工作
  把上面结构当作子查询使用
*/
-- 名字，岗位，工资，部门号，但不含10自己
SELECT DISTINCT job FROM emp
    WHERE deptno = 10

SELECT ename, job,sal,deptno FROM emp
    WHERE job IN (
    SELECT DISTINCT job FROM emp
    WHERE deptno = 10
    ) AND deptno != 10;

-- all和any的使用
-- 显示工资比部门30的所有员工的工资高的员工姓名，工资和部门号
SELECT ename,sal,deptno FROM emp
    WHERE sal > ALL(SELECT sal FROM emp
                WHERE deptno = 30);
-- 也可以
SELECT ename,sal,deptno FROM emp
    WHERE sal > (SELECT MAX(sal) FROM emp
                WHERE deptno = 30);
                
-- 显示比部门30的其中一个员工高的姓名，工资，部门号
SELECT ename,sal,deptno FROM emp
    WHERE sal > ANY(SELECT sal FROM emp
                WHERE deptno = 30);

-- 多列子查询
-- 显示与smith的部门和岗位完全相同的所有雇员，不含本人
-- 分析：1.得到smith部门和岗位
--       2.第一步查询当作子查询，并且使用多列子查询进行匹配
SELECT deptno , job FROM emp
    WHERE ename = 'SMITH'

SELECT deptno , job FROM emp
    WHERE (deptno, job) = (
        SELECT deptno , job FROM emp
        WHERE ename = 'SMITH'
    ) AND eaname != 'SMITH';

-- 查询和宋江数学，英语，语文成绩完全相同的学生
SELECT `name` ,chinese,english,math FROM student
    WHERE (chinese,english,math) = (
        SELECT chinese,english,math
        FROM student
        WHERE `name` = '宋江'
    )AND `name` != '宋江';