SQL语句

--==========================

--SQL基础-->多表查询

--==========================

/*

一、多表查询

    简言之，根据特定的连接条件从不同的表中获取所需的数据

 

    笛卡尔集的产生条件：

       省略连接条件

       连接条件无效

       第一个表中的所有行与第二个表中的所有行相连接

           

二、多表查询语法：*/

    SELECT table1.column, table2.column

    FROM table1, table2

    WHERE table1.column1 = table2.column2;

    /*

    但要注意where 不要省了，省略where 即为笛卡尔集，而且where 条件要有效，

    两张表间有一个相同的字段，才好进行有效的多表查询

 

    查询时列名前，加表名或表别名前辍（如果字段在两个表中是唯一的可以不加）

 

    为了简化SQL书写，可为表名定义别名，格式：from 表名别名

    如：from emp e,dept d

   

    建议使用表的别名及表前缀，使用表别名可以简化查询，而使用表前缀则可以提高查询性能

 

    例：查询每个员工的工号，姓名，工资，部门名和工作地点*/

    select empno,ename,sal,dname,loc from emp,dept

    where emp.deptno=dept.deptno;

 

/*

三、多表连接类型：

    从数据显示方式来讲有：内连接和外连接。

    内连接：只返回满足连接条件的数据。

    外连接：除了返回满足连接条的行以外，还返回左（右）表中，不满足条件的行，

       称为左（右）连接

 

    内连接*/

    select empno,ename,sal,dname,loc from emp,dept

    where emp.deptno=dept.deptno;  --(Oracle 8i 及以前的写法)

 

    --内连接的另一种写法：

    select empno,ename,job,sal,dept.deptno,dname,loc

    from emp join dept on (emp.deptno=dept.deptno); -- (SQL 99的写法)

 

    /*

    外连接：

    两个表的查询中，使用外连接可以查询另一个表或者两个中不满足连接条件的数据。

    外连接的符号是（＋），（＋）要放在字段名后。（＋）对面的那个表，会全部显示。

   

    外连接语法*/

        SELECT table1.column, table2.column   --右外连接

       FROM table1, table2

       WHERE table1.column(+) = table2.column;

      

       SELECT table1.column, table2.column   --左外连接

       FROM table1, table2

       WHERE table1.column = table2.column(+);

 

    --例：

    select empno,ename,job,sal,dept.deptno,dname,loc

    from emp,dept

    where emp.deptno(+)=dept.deptno; -- (Oracle 8i 及以前的写法)

 

    --另一种写法（右连接）：     -- (SQL 99的写法)

    select empno,ename,job,sal,dept.deptno,dname,loc

    from emp right join dept on (emp.deptno=dept.deptno);

 

    --左连接：                    (SQL99的写法)

    select empno,ename,job,sal,dept.deptno,dname,loc

    from emp left join dept on (emp.deptno=dept.deptno);

 

    --全连接（满连接）            (SQL99的写法)

   select empno,ename,job,sal,d.deptno,dname,loc

    from emp e full join dept d on (e.deptno=d.deptno);

 

    /*

    自然连接                    (SQL99的写法)

    以两个表具有相同的字段的所有列为基础，可采用自然连接（natural join)

    它将选择两个表中那些在所有匹配的列中值相等的行。

    如果列具有相同的名称，但数据类型能够不同，则会报错*/

    select empno,ename,job,sal,deptno,dname,loc

    from emp natural join dept;

 

    /*

    自连接：

    将自身表的一个镜像当另一个表来对待。*/

    select ... from emp e,emp d  ...

   

    --下面列出SQL 99的语法供参考

    SELECT table1.column, table2.column

    FROM table1

    [CROSS JOINtable2] |

    [NATURAL JOINtable2] |

    [JOIN table2 USING(column_name)] |

    [JOIN table2

    ON(table1.column_name= table2.column_name)] |

    [LEFT|RIGHT|FULLOUTER JOIN table2

    ON(table1.column_name = table2.column_name)];

   

    --在下面的语法中

    table1.column     --指明从中检索数据的表和列

    CROSS JOIN        --返回两个表的笛卡尔集

    NATURAL JOIN      --根据相同的列名连接两个表

    JOIN table

    USINGcolumn_name --根据列名执行等值连接

    JOIN table ON

    table1.column_name   --根据ON 子句中的条件执行等值连接

    = table2.column_name

    LEFT/RIGHT/FULL OUTER

   

    /*

    使用using子句创建连接

        如果几个列具有相同的名称，但是数据类型不匹配，那么可以使用using子句来修改natural join

       子句以指定要用于等值连接的列

       在多个列匹配时，使用using 子句只匹配一个列

       在引用列中不要使用表名或别名

       naturaljoin 和using 子句是互不相容的 */

      

        --例：

       SELECT l.city, d.department_name

       FROM locations l JOIN departments d USING (location_id)

       WHERE location_id = 1400;

       --下面的语句无效，因为where 子句中限定了location_id

       SELECT l.city, d.department_name

       FROM locations l JOIN departments d USING (location_id)

       WHERE d.location_id = 1400;

       ORA-25154: column part of USING clause cannot have qualifier

      

       --注意：

       --   两个表中名称相同的列在使用时不能有任何限定符，这一限制同样适用于natural join

/*

四、演示：      */ 

--笛卡尔集

    SQL> select empno,ename,dname from emp,dept;

 

        EMPNOENAME      DNAME

    ---------- ---------- --------------

         7369SMITH      ACCOUNTING

         7499ALLEN      ACCOUNTING

         7521WARD       ACCOUNTING

         7566JONES      ACCOUNTING

         7654MARTIN     ACCOUNTING

         7698BLAKE      ACCOUNTING

         7782CLARK      ACCOUNTING

         7788SCOTT      ACCOUNTING

         7839KING       ACCOUNTING

         7844TURNER     ACCOUNTING

         7876ADAMS      ACCOUNTING

         --中间结果省略

         56rows selected.    

--使用cross join 实现交叉连接，即笛卡尔集    

    SQL> select empno,ename,dname from emp

      2  cross join dept;

 

        EMPNOENAME      DNAME

    ---------- ---------- --------------

         7369SMITH      ACCOUNTING

         7499ALLEN      ACCOUNTING

         7521WARD       ACCOUNTING

         7566JONES      ACCOUNTING

         7654MARTIN     ACCOUNTING

         7698BLAKE      ACCOUNTING

         7782CLARK      ACCOUNTING

         7788SCOTT      ACCOUNTING

         7839KING       ACCOUNTING

         7844TURNER     ACCOUNTING

         7876ADAMS      ACCOUNTING

     

--等值连接(Oracle 写法)

    SQL> select empno,ename,dname from emp,dept where emp.deptno = dept.deptno;

 

        EMPNOENAME      DNAME

    ---------- ---------- --------------

         7369SMITH      RESEARCH

         7499ALLEN      SALES

         7876ADAMS      RESEARCH

         --部分结果省略

         7902FORD       RESEARCH

         7934MILLER     ACCOUNTING

 

    14 rows selected.

 

--等值连接(SQL 99 写法)

    SQL> select e.empno,e.ename,d.dname from emp  e

      2  inner join dept  d

      3  on e.deptno = d.deptno;

 

        EMPNOENAME      DNAME

    ---------- ---------- --------------

         7369SMITH      RESEARCH

         7499ALLEN      SALES

         7876ADAMS      RESEARCH

           --部分结果省略

         7902FORD       RESEARCH

         7934MILLER     ACCOUNTING

 

    14 rows selected.

 

--注意：表别名不支持as 用法

    SQL> select e.empno,e.ename,d.dname from emp as e

      2  inner join dept  d

      3  on e.deptno = d.deptno;

    select e.empno,e.ename,d.dname from emp as e

                                       *

    ERROR at line 1:

    ORA-00933: SQLcommand not properly ended

 

--等值连接并增加条件

    SQL> select e.empno,e.ename,d.dname from emp  e,

      2  deptd

      3  where d.deptno = e.deptno

      4    and e.ename = 'SCOTT';

 

        EMPNOENAME      DNAME

    ---------- ---------- --------------

         7788SCOTT      RESEARCH

     

--非等值连接

--查询雇员的姓名、薪水、级别且部门为的记录

    SQL> select ename,sal,grade

      2  from emp,salgrade

      3  where sal between losal and hisal

      4    and emp.deptno = 20;

 

    ENAME             SAL      GRADE

    ---------- ---------- ----------

    SCOTT            3000          4

    FORD             3000          4

    JONES            2975          4

    ADAMS            1100          1

    SMITH             800          1 

 

--使用SQL 99写法实现上述功能

    SQL> select e.ename,e.sal,s.grade

      2  from emp e

      3    join salgrade s

      4      on e.sal between losal and hisal

      5      and e.deptno = 20;

 

    ENAME             SAL      GRADE

    ---------- ---------- ----------

    SCOTT            3000          4

    FORD             3000          4

    JONES            2975          4

    ADAMS            1100          1

    SMITH             800          1

 

--右外连接

--注意：右外连接时，加号在等号的左边

--可以看到，左表emp中的列有为空值的

    SQL> select e.ename,e.deptno,d.dname  

      2  from emp e,dept d

      3  where e.deptno(+) = d.deptno;

 

    ENAME          DEPTNODNAME

    ---------- ---------- --------------

    CLARK              10ACCOUNTING

    KING               10ACCOUNTING

    MILLER             10ACCOUNTING

    JONES              20RESEARCH

    FORD               20RESEARCH

    ADAMS              20RESEARCH

    SMITH              20RESEARCH

    SCOTT              20RESEARCH

    WARD               30SALES

    TURNER             30SALES

    ALLEN              30SALES

 

    ENAME          DEPTNODNAME

    ---------- ---------- --------------

    JAMES              30SALES

    BLAKE              30SALES

    MARTIN             30SALES

                       OPERATIONS

 

    15 rows selected.

 

--使用SQL 99写法实现右外连接

    SQL> select e.ename,e.deptno,d.dname

      2  from empe      

      3    right join dept d

      4      on e.deptno = d.deptno ;

 

    ENAME          DEPTNODNAME

    ---------- ---------- --------------

    CLARK              10ACCOUNTING

    KING               10ACCOUNTING

    MILLER             10ACCOUNTING

    JONES              20RESEARCH

    FORD               20RESEARCH

    ADAMS              20RESEARCH

    SMITH              20RESEARCH

    SCOTT              20RESEARCH

    WARD               30SALES

    TURNER             30SALES

    ALLEN              30SALES

 

    ENAME          DEPTNODNAME

    ---------- ---------- --------------

    JAMES              30SALES

    BLAKE              30SALES

    MARTIN             30SALES

                       OPERATIONS

 

--左外连接

--注意：左外连接时，加号在等号的右边

    SQL> select d.dname,e.ename,e.deptno

      2  from dept d,emp e

      3  where d.deptno = e.deptno(+)

      4  order by d.deptno;

 

    DNAME          ENAME          DEPTNO

    -------------- ---------- ----------

    ACCOUNTING     CLARK              10

    ACCOUNTING     KING               10

    ACCOUNTING     MILLER             10

    RESEARCH       JONES              20

    RESEARCH       FORD               20

    RESEARCH       ADAMS              20

    RESEARCH       SMITH              20

    RESEARCH       SCOTT              20

    SALES          WARD               30

    SALES          TURNER             30

    SALES          ALLEN              30

 

    DNAME          ENAME          DEPTNO

    -------------- ---------- ----------

    SALES          JAMES              30

    SALES          BLAKE              30

    SALES          MARTIN             30

    OPERATIONS

 

    15 rows selected.

 

--使用SQL 99写法实现左外连接               

    SQL> select d.dname,e.ename,e.deptno

      2  from dept d

      3    left join emp e

      4      on d.deptno = e.deptno

      5  order by d.deptno;

 

    DNAME          ENAME          DEPTNO

    -------------- ---------- ----------

    ACCOUNTING     CLARK              10

    ACCOUNTING     KING               10

    ACCOUNTING     MILLER             10

    RESEARCH       JONES              20

    RESEARCH       FORD               20

    RESEARCH       ADAMS              20

    RESEARCH       SMITH              20

    RESEARCH       SCOTT              20

    SALES          WARD               30

    SALES          TURNER             30

    SALES          ALLEN              30

 

    DNAME          ENAME          DEPTNO

    -------------- ---------- ----------

    SALES          JAMES              30

    SALES          BLAKE              30

    SALES          MARTIN             30

    OPERATIONS

 

    15 rows selected.                

 

--自连接

    SQL> select e.ename || ' works for ' || m.ename

      2  from emp e,emp m

      3  where e.empno = m.mgr;

 

    E.ENAME||'WORKSFOR'||M.ENAME

    -------------------------------

    FORD works for SMITH

    BLAKE works for ALLEN

    BLAKE works for WARD

    KING works for JONES

    BLAKE works for MARTIN

    KING works for BLAKE

    KING works for CLARK

    JONES works for SCOTT

    BLAKE works for TURNER

    SCOTT works for ADAMS

    BLAKE works for JAMES

 

    E.ENAME||'WORKSFOR'||M.ENAME

    -------------------------------

    JONES works for FORD

    CLARK works for MILLER

 

    13 rows selected.

 

--自然连接

    SQL> select empno,ename,job,deptno,dname,loc

      2  from emp

      3  natural join dept;

 

        EMPNOENAME      JOB           DEPTNODNAME          LOC

    ---------- ---------- --------- ------------------------ -------------

         7369SMITH      CLERK             20RESEARCH       DALLAS

         7499ALLEN      SALESMAN          30SALES          CHICAGO

         7521WARD       SALESMAN          30SALES          CHICAGO

         7566JONES      MANAGER           20RESEARCH       DALLAS

         7654MARTIN     SALESMAN          30SALES          CHICAGO

         7698BLAKE      MANAGER           30SALES          CHICAGO

         7782CLARK      MANAGER           10ACCOUNTING     NEW YORK

         7788SCOTT      ANALYST           20RESEARCH       DALLAS

         7839KING       PRESIDENT         10ACCOUNTING     NEW YORK

         7844TURNER     SALESMAN          30SALES          CHICAGO

         7876ADAMS      CLERK             20RESEARCH       DALLAS

 

        EMPNOENAME      JOB           DEPTNODNAME          LOC

    ---------- ---------- --------- ------------------------ -------------

         7900JAMES      CLERK             30SALES          CHICAGO

         7902FORD       ANALYST           20RESEARCH       DALLAS

         7934MILLER     CLERK             10ACCOUNTING     NEW YORK

 

    14 rows selected.

 

--使用using 子句创建连接

    SQL> select e.empno,e.ename,d.dname,d.loc

      2  from emp e

      3  join dept d

      4  using (deptno)

      5  where deptno in (20,40);

 

        EMPNOENAME      DNAME          LOC

    ---------- ---------- ---------------------------

         7369SMITH      RESEARCH       DALLAS

         7566JONES      RESEARCH       DALLAS

         7788SCOTT      RESEARCH       DALLAS

         7876ADAMS      RESEARCH       DALLAS

         7902FORD       RESEARCH       DALLAS

/*