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 from table1, table2 where table1.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 2 emp.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 2 job_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 2 where 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.1 Using字句
在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 2 on (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.
以上是个人学习之后做的笔记，刚刚入门，还有很多欠缺的地方。但是还是一点态度吧，大家一起学习进步！