Oracle SQL之--多表查询基础用法
多表查询，顾名思义就是从多个表中查询你所要的数据。这里以scott用户下的emp表与dept表进行试验，两个表的结构如下：SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- -------------10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
使用连接可在多个表中查询数据：
基本语句如下：
SELECT table1.column, table2.column fromtable1, table2 wheretable1.column1 = table2.column2
连接的类型分成很多种：Equijoins（等值连接） Non-equijoin（非等值连接） Outer-join（外连接） Self-join（自连接） Cross joins（交叉连接） Natural joins（自然连接）
1、Equijoins
Equijoins（等值连接）--利用emp表中的deptno与dept表中的deptno进行等值连接。
SQL> select emp.empno,emp.ename,emp.deptno,dept.deptno,dept.dname from emp,dept where
2emp.deptno=dept.deptno;
EMPNO ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- ---------- --------------
7369 SMITH 20 20 RESEARCH
7499 ALLEN 30 30 SALES
7521 WARD 30 30 SALES
7566 JONES 20 20 RESEARCH
7654 MARTIN 30 30 SALES
7698 BLAKE 30 30 SALES
7782 CLARK 10 10 ACCOUNTING
7788 SCOTT 20 20 RESEARCH
7839 KING 10 10 ACCOUNTING
7844 TURNER 30 30 SALES
7876 ADAMS 20 20 RESEARCH
EMPNO ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- ---------- --------------
7900 JAMES 30 30 SALES
7902 FORD 20 20 RESEARCH
7934 MILLER 10 10 ACCOUNTING
14 rows selected.
2、Non-equijoin
Non-equijoin（非等值连接）--新建一个名为job_grades的表，并往其中添加数据：
SQL> select * from job_grades
2;
GRA LOWEST_SAL HIGHEST_SAL
----- ---------- -----------
A 500 1499
B 1500 2499
C 2500 3500
SQL> select emp.ename,emp.sal,job_grades.gra from emp,job_grades where emp.sal between
2job_grades.lowest_sal and job_grades.highest_sal;
ENAME SAL GRA
---------- ---------- -----
SMITH 800 A
WARD 1250 A
MARTIN 1250 A
ADAMS 1100 A
JAMES 950 A
MILLER 1300 A
ALLEN 1600 B
CLARK 2450 B
TURNER 1500 B
JONES 2975 C
BLAKE 2850 C
ENAME SAL GRA
---------- ---------- -----
SCOTT 3000 C
FORD 3000 C
13 rows selected.
3、Self-join
Self-join（自连接）---是sql语句中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。
SQL> select worker.ename || ' works for ' || manager.ename from emp worker,emp manager
2where worker.mgr = manager.empno;
WORKER.ENAME||'WORKSFOR'||MANAG
-------------------------------
SMITH works for FORD
ALLEN works for BLAKE
WARD works for BLAKE
JONES works for KING
MARTIN works for BLAKE
BLAKE works for KING
CLARK works for KING
SCOTT works for JONES
TURNER works for BLAKE
ADAMS works for SCOTT
JAMES works for BLAKE
WORKER.ENAME||'WORKSFOR'||MANAG
-------------------------------
FORD works for JONES
MILLER works for CLARK
13 rows selected.
4、Natural joins
Natural joins（自然连接）---是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。而等值连接并不去掉重复的属性列。
SQL> select deptno,ename,dname,loc from emp natural join dept;
DEPTNO ENAME DNAME LOC
---------- ---------- -------------- -------------
20 SMITH RESEARCH DALLAS
30 ALLEN SALES CHICAGO
30 WARD SALES CHICAGO
20 JONES RESEARCH DALLAS
30 MARTIN SALES CHICAGO
30 BLAKE SALES CHICAGO
10 CLARK ACCOUNTING NEW YORK
20 SCOTT RESEARCH DALLAS
10 KING ACCOUNTING NEW YORK
30 TURNER SALES CHICAGO
20 ADAMS RESEARCH DALLAS
DEPTNO ENAME DNAME LOC
---------- ---------- -------------- -------------
30 JAMES SALES CHICAGO
20 FORD RESEARCH DALLAS
10 MILLER ACCOUNTING NEW YORK
14 rows selected.
4.1Using字句
在natural join字句创建等值连接时，可以使用using字句指定等值连接中需要用到的列。使用using可以在有多个列满足条件时进行选择。
SQL> select e.empno,e.ename,d.loc from emp e join dept d using(deptno);
EMPNO ENAME LOC
---------- ---------- -------------
7369 SMITH DALLAS
7499 ALLEN CHICAGO
7521 WARD CHICAGO
7566 JONES DALLAS
7654 MARTIN CHICAGO
7698 BLAKE CHICAGO
7782 CLARK NEW YORK
7788 SCOTT DALLAS
7839 KING NEW YORK
7844 TURNER CHICAGO
7876 ADAMS DALLAS
EMPNO ENAME LOC
---------- ---------- -------------
7900 JAMES CHICAGO
7902 FORD DALLAS
7934 MILLER NEW YORK
14 rows selected.
4.2 ON字句
自然连接中是以具有相同名字的列作为连接条件的。我们可以使用ON字句指定额外的连接条件，这个连接条件时与其他条件分开的。
SQL> select emp.empno,emp.ename,emp.deptno,dept.deptno,dept.loc from emp join dept
2on (emp.deptno = dept.deptno);
EMPNO ENAME DEPTNO DEPTNO LOC
---------- ---------- ---------- ---------- -------------
7369 SMITH 20 20 DALLAS
7499 ALLEN 30 30 CHICAGO
7521 WARD 30 30 CHICAGO
7566 JONES 20 20 DALLAS
7654 MARTIN 30 30 CHICAGO
7698 BLAKE 30 30 CHICAGO
7782 CLARK 10 10 NEW YORK
7788 SCOTT 20 20 DALLAS
7839 KING 10 10 NEW YORK
7844 TURNER 30 30 CHICAGO
7876 ADAMS 20 20 DALLAS
EMPNO ENAME DEPTNO DEPTNO LOC
---------- ---------- ---------- ---------- -------------
7900 JAMES 30 30 CHICAGO
7902 FORD 20 20 DALLAS
7934 MILLER 10 10 NEW YORK
14 rows selected.
5、Outer-join
outer-join（外连接）---使用外连接可以查询不满足连接条件的数据。外连接的符号是（+）。外连接还可以分为左外连接、右外连接。所谓左外连接，就是返回左表中不满足条件的行。右外连接，则是返回右表中不满足条件的行。
上面的dept表中有一个40号的deptno，但是emp表中却没有这个dept的数据。故可以用来做外连接的实验：
5.1 右外连接：
下面两条SQL语句的效果是一样的：
SQL> select e.ename,e.deptno,d.dname from emp e right outer join dept d on (e.deptno = d.deptno);
SQL> select e.ename,e.deptno,d.dname from emp e,dept d where e.deptno(+) = d.deptno;
ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
JONES 20 RESEARCH
MARTIN 30 SALES
BLAKE 30 SALES
CLARK 10 ACCOUNTING
SCOTT 20 RESEARCH
KING 10 ACCOUNTING
TURNER 30 SALES
ADAMS 20 RESEARCH
ENAME DEPTNO DNAME
---------- ---------- --------------
JAMES 30 SALES
FORD 20 RESEARCH
MILLER 10 ACCOUNTING
OPERATIONS
5.2 左外连接
左外连接的sql语句与右外连接的sql语句相似：
SQL> select e.ename,e.deptno,d.dname from emp e,dept d where e.deptno = d.deptno(+);
SQL> select e.ename,e.deptno,d.dname from emp e left outer join dept d on (e.deptno = d.deptno);
6、Cross joins（交叉连接）
使用corss join字句是连接表产生叉集。叉集合笛卡尔集是相同的
SQL> select ename,dname from emp cross join dept;
ENAME DNAME
---------- --------------
SMITH ACCOUNTING
ALLEN ACCOUNTING
WARD ACCOUNTING
JONES ACCOUNTING
MARTIN ACCOUNTING
BLAKE ACCOUNTING
CLARK ACCOUNTING
SCOTT ACCOUNTING
KING ACCOUNTING
TURNER ACCOUNTING
ADAMS ACCOUNTING
ENAME DNAME
---------- --------------
JAMES ACCOUNTING
FORD ACCOUNTING
MILLER ACCOUNTING
SMITH RESEARCH
ALLEN RESEARCH
WARD RESEARCH
JONES RESEARCH
MARTIN RESEARCH
BLAKE RESEARCH
CLARK RESEARCH
SCOTT RESEARCH
ENAME DNAME
---------- --------------
KING RESEARCH
TURNER RESEARCH
ADAMS RESEARCH
JAMES RESEARCH
FORD RESEARCH
MILLER RESEARCH
SMITH SALES
ALLEN SALES
WARD SALES
JONES SALES
MARTIN SALES
ENAME DNAME
---------- --------------
BLAKE SALES
CLARK SALES
SCOTT SALES
KING SALES
TURNER SALES
ADAMS SALES
JAMES SALES
FORD SALES
MILLER SALES
SMITH OPERATIONS
ALLEN OPERATIONS
ENAME DNAME
---------- --------------
WARD OPERATIONS
JONES OPERATIONS
MARTIN OPERATIONS
BLAKE OPERATIONS
CLARK OPERATIONS
SCOTT OPERATIONS
KING OPERATIONS
TURNER OPERATIONS
ADAMS OPERATIONS
JAMES OPERATIONS
FORD OPERATIONS
ENAME DNAME
---------- --------------
MILLER OPERATIONS
56 rows selected.
以上是个人学习之后做的笔记，刚刚入门，还有很多欠缺的地方。但是还是一点态度吧，大家一起学习进步！
页:
[1]