多表查询--内连接查询-- 外连接查询--子查询--多表查询练习

100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
1.多表查询
        笛卡尔积：有两个集合A,B,取这两个集合的所有组成情况
        要完成多表查询，需要消除无用的数据
            内连接查询
                隐式内连接
                -- 查询所有员工信息和对应的部门信息
                SELECT * FROM emp,dept WHERE emp.'dept_id' = dept.'id';
                -- 查询员工表的名称，性别，部门名称
                SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.'dept_id' = dept.'id';
                SELECT
                    t1.name, -- 员工表的姓名
                    t1.gender, -- 员工表的性别
                    t2.name -- 部门表的名称
                FROM
                    emp t1,
                    dept t2
                WHERE
                    t1.'dept_id' = t2.'id';    
                显式内连接
                    语法：select 字段列表 from 表名1 inner join 表名2 on 条件
                    SELECT * FROM emp INNER JOIN dept ON emp.'dept_id' = dept.'id';
                    SELECT * FROM emp JOIN dept ON emp.'dept_id' = dept.'id';   
            外连接查询
                1.左外连接：
                    语法:select 字段列表 from 表1 left [outer] join 表2 on 条件;
                    -- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
                    SELECT
                        t1.*,t2.'name'
                    FROM
                        emp t1,dept t2
                    WHERE
                        t1.'dept_id' = t2.'id';   
                    -------
                    查询的是左表所有的数据以及其交集部分
                    SELECT
                        t1.*,t2.'name'
                    FROM
                        emp t1 LEFT JOIN dept t2
                    ON
                        t1.'dept_id' = t2.'id';
                 1.右外连接： 
 2.2.13.1                            
            子查询-
                查询中嵌套查询，称嵌套查询为子查询 
                -- 查询工资最高的员工信息
                -- 1.查询最高的工资是多少 9000
                    SELECT MAX(salary) FROM emp;
                -- 2.查询员工信息，并且工资等于9000的      
                    SELECT * FROM emp WHERE emp.'salary' = 9000;
                -- 一条sql就完成这个操作
                    SELECT * FROM emp WHERE emp.'salary' = (SELECT MAX(salary) FROM emp);
            1.子查询的结果是单行单列的；--运算符：> >= < <= =
            2.子查询的结果是多行单列的；
            3.子查询的结果是多行多列的；
            -- 查询财务部和市场部所有的员工信息
            SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
            SELECT * FROM emp WHERE dept_id = 3 OR dept = 2;
            SELECT * FROM emp WHERE dept_id IN (3,2);
            -- 多行单列的 使用运算符 IN
            SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');
            -- 多行多列
                子查询可以作为一张虚拟表
                --查询员工入职日期是2011-11-11日之后的员工信息和部门信息
                SELECT * FROM emp WHERE emp.'join_date' > '2011-11-11';
                SELECT * FROM dept t1,(SELECT * FROM emp WHERE emp.'join_date' > '2011-11-11') t2 WHERE t1.id = t2.dept_id     
                -- 用普通的内连接实现
                SELECT * FROM emp t1,dept t2 WHERE t1.'dept_id' = t2.'id' AND t1.'join_data' > '2011-11-11';
2.2.14.8
            多表查询练习
            3.查询员工姓名，工资，工资等级
                分析：1.员工姓名，工资emp 工资等级 salarygrade
                     SELECT
                        t1.ename,
                        t1.'salary',
                        t2.*
                     FROM emp t1,salarygrade t2
                     WHERE t1.'salary' BETWEEN t2.'losalary' AND t2.'hisalary';   
            4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
                分析：1.分别对应四张表
                        2.条件 emp.job_id = job.id and emp.dept_id = dept.id and emp.salary BETWEEN
                    SELECT
                        t1.'ename',
                        t1.'salary',
                        t2.'jname',
                        t2.'description',
                        t3.'dname',
                        t3.'loc',
                        t4.'grade'
                    FROM
                        emp t1,job t2,dept t3,salarygrade t4
                    WHERE
                        t1.'job_id' = t2.'id'
                        AND t1.'dept_id' = t3.'id'
                        AND t1.'salary' BETWEEN t4.'losalary' AND t4.'hisalary';   
            5.查询出部门编号，部门名称，部门位置，部门人数
             分析：1.dept 表 emp表
                    2.使用分组查询，按照emp.dept_id完成分组，查询count(id)   
                    3.使用子查询将第二步的查询结果和dept表进行关联查询 
                    SELECT
                        dept_id,COUNT(id)
                    FROM
                        emp
                    GROUP BY dept_id
                    -------------
                    SELECT
                        t1.'id',t1.'dname',t1.'loc',t2.total
                    FROM
                        dept t1,
                        (SELECT
                             dept_id,COUNT(id) total
                         FROM
                             emp
                         GROUP BY dept_id) t2       
                    WHERE
                        t1.'id' = t2.dept_id;    
             6.查询所有员工的姓名及其直接上级的名称，没有领导的员工也需要查询   
              分析：1.姓名 emp ,直接上级的姓名 emp
                                emp表的id和mgr是自关联
                   2.条件emp.id = emp.mgr 
                   3.查询左表的所有数据，和交集数据-使用左外连接查询  
               SELECT
                    t1.ename,
                    t1.mgr,
                    t2.'id',
                    t2.ename
               FROM emp t1,emp t2
               WHERE t1.mgr = t1.'id' 
               ----------------
               SELECT
                    t1.ename,
                    t1.mgr,
                    t2.'id',
                    t2.'ename'
               FROM
                    emp t1
               LEFT JOIN emp t2
               ON t1.'mgr' = t2.'id';   