连接查询 子查询 SQL 查询 小记

--连接查询   {自表查寻（一张表自己连接自己）和多表查询（内连接，外连接，交叉连接，全连接}

        这里有有两张表（emp员工表和dept部门表）

 

--1.内连接 inner join.....on（满足多表连接的条件（主外键约束关联））

      --查询emp表关联部门表的所有信息 ：（关联条件为员工表的外键部门编号于部门表的主键部门编号对应）

   --简写

select e.*,d.* from emp e,dept d where e.deptno=d.deptno;
   --全写

select e.*,d.* from emp e inner join dept d on e.deptno=d.deptno;

--2.外链接  （不仅满足多表连接条件，还可以满足过滤条件）

    --left join...on

    --查询部门名称为ACCOUNTING，并且薪水高于5000的员工信息：

select e.deptno, e.ename, d.dname, e.sal  from emp e left join dept d on e.deptno=d.deptno where e.sal>5000 and d.dname='ACCOUNTING';

    --right join...on

    --查询所有经理的姓名：（用到了自表连接查寻）

--3.交叉连接 cross join 得出笛卡尔积
select e.comm,d.dname from emp e,dept d;
select e.*,d.dname from emp e,dept d;
select * from emp cross join dept ;

  --4.自连接（常用语有分级存在）
   --查出员工和对应的经理姓名 :
    --(假设e1的外键列是经理的员工号，该员工号在e2中是主键，对应e2的主键列员工号)

select e1.ename 员工,e2.ename 经理 from emp e1 left join emp e2 on e1.mgr=e2.empno;

--5.子查寻（一个查询的结果集当做外部查询的条件）

如：查询最高薪水员工姓名和薪水：

select ename,sal from emp where sal=(select max(sal) maxsal from emp) ;

--查询每个部门的平均薪水
select d.deptno,d.dname,a.avgsal from dept d inner join
(select e.deptno,round(avg(sal),2) avgsal from emp e group by e.deptno) a

on d.deptno=a.deptno order by a.deptno;

     

         1. 某些查询情况下，需要的条件刚好作为另外一个查询语句的结果，这时就要用到子查询，子查询首先被执行。

            2.  如果查询中的SELECT列表所包含的列来自于多个表，那么这时连接的优势要胜过子查询，所有的连接查询理论上都可以转为子查询，反之不一定。 

         3. 由于子查询会多次运算查询，连接查询利于优化器优化，而且可以使用多线程查询各个连接子句。