基本概念(只有表与表之间有关联,才可以使用|关联字段)
统计emp表、dept表的数据量
SQL> select count(*) from emp;
COUNT(*)
----------
14
-------------------------------------
SQL> select count(*) from dept;
COUNT(*)
----------
4
笛卡尔积
执行select from emp,dept;结果为164的笛卡尔积
SQL> select *from emp,dept;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- ---------- ---------------------------- --------------------------
7369 SMITH CLERK 7902 17-12月-80 800 20 10 ACCOUNTING NEW YORK
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 10 ACCOUNTING NEW YORK
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 02-4月 -81 2975 20 10 ACCOUNTING NEW YORK
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 10 ACCOUNTING NEW YORK
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-11月-81 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 10 ACCOUNTING NEW YORK
7876 ADAMS CLERK 7788 23-5月 -87 1100 20 10 ACCOUNTING NEW YORK
7900 JAMES CLERK 7698 03-12月-81 950 30 10 ACCOUNTING NEW YORK
7902 FORD ANALYST 7566 03-12月-81 3000 20 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 23-1月 -82 1300 10 10 ACCOUNTING NEW YORK
7369 SMITH CLERK 7902 17-12月-80 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 02-4月 -81 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 20 RESEARCH DALLAS
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 20 RESEARCH DALLAS
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-11月-81 5000 10 20 RESEARCH DALLAS
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-5月 -87 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-12月-81 950 30 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-12月-81 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-1月 -82 1300 10 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-12月-80 800 20 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-4月 -81 2975 20 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 30 SALES CHICAGO
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 30 SALES CHICAGO
7839 KING PRESIDENT 17-11月-81 5000 10 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 23-5月 -87 1100 20 30 SALES CHICAGO
7900 JAMES CLERK 7698 03-12月-81 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-12月-81 3000 20 30 SALES CHICAGO
7934 MILLER CLERK 7782 23-1月 -82 1300 10 30 SALES CHICAGO
7369 SMITH CLERK 7902 17-12月-80 800 20 40 OPERATIONS BOSTON
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 40 OPERATIONS BOSTON
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 40 OPERATIONS BOSTON
7566 JONES MANAGER 7839 02-4月 -81 2975 20 40 OPERATIONS BOSTON
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 40 OPERATIONS BOSTON
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 40 OPERATIONS BOSTON
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 40 OPERATIONS BOSTON
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 40 OPERATIONS BOSTON
7839 KING PRESIDENT 17-11月-81 5000 10 40 OPERATIONS BOSTON
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 40 OPERATIONS BOSTON
7876 ADAMS CLERK 7788 23-5月 -87 1100 20 40 OPERATIONS BOSTON
7900 JAMES CLERK 7698 03-12月-81 950 30 40 OPERATIONS BOSTON
7902 FORD ANALYST 7566 03-12月-81 3000 20 40 OPERATIONS BOSTON
7934 MILLER CLERK 7782 23-1月 -82 1300 10 40 OPERATIONS BOSTON
消除笛卡尔积
SQL> select *from emp,dept where emp.deptno=dept.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- ---------- ---------------------------- --------------------------
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-11月-81 5000 10 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 23-1月 -82 1300 10 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 02-4月 -81 2975 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-12月-81 3000 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-5月 -87 1100 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-12月-80 800 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 30 SALES CHICAGO
7900 JAMES CLERK 7698 03-12月-81 950 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 30 SALES CHICAGO
数据量大时不建议使用多表查询
设置别名(更为方便)
SQL> select *from emp e,dept d where e.deptno=d.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- ---------- ---------------------------- --------------------------
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-11月-81 5000 10 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 23-1月 -82 1300 10 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 02-4月 -81 2975 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-12月-81 3000 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-5月 -87 1100 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-12月-80 800 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 30 SALES CHICAGO
7900 JAMES CLERK 7698 03-12月-81 950 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 30 SALES CHICAGO
查询每个雇员的编号、姓名、职位、工资、部门名称、部门位置
SQL> select e.empno,e.ename,e.job,e.sal
2 from emp e;
EMPNO ENAME JOB SAL
---------- -------------------- ------------------ ----------
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7902 FORD ANALYST 3000
7934 MILLER CLERK 1300
SQL> select e.empno,e.ename,e.job,e.sal,d.dname,d.loc
2 from emp e,dept d
3 where e.deptno=d.deptno;
EMPNO ENAME JOB SAL DNAME LOC
---------- -------------------- ------------------ ---------- ---------------------------- --------------------------
7782 CLARK MANAGER 2450 ACCOUNTING NEW YORK
7839 KING PRESIDENT 5000 ACCOUNTING NEW YORK
7934 MILLER CLERK 1300 ACCOUNTING NEW YORK
7566 JONES MANAGER 2975 RESEARCH DALLAS
7902 FORD ANALYST 3000 RESEARCH DALLAS
7876 ADAMS CLERK 1100 RESEARCH DALLAS
7369 SMITH CLERK 800 RESEARCH DALLAS
7788 SCOTT ANALYST 3000 RESEARCH DALLAS
7521 WARD SALESMAN 1250 SALES CHICAGO
7844 TURNER SALESMAN 1500 SALES CHICAGO
7499 ALLEN SALESMAN 1600 SALES CHICAGO
7900 JAMES CLERK 950 SALES CHICAGO
7698 BLAKE MANAGER 2850 SALES CHICAGO
7654 MARTIN SALESMAN 1250 SALES CHICAGO
查询雇员的编号、姓名、职位、工资、工资等级
![查询](http://upload-images.jianshu.io/upload_images/1734279-dd710b572a5953e7.png?
imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
第一步:
SQL> select e.empno,e.ename,e.job,e.sal from emp e;
EMPNO ENAME JOB SAL
---------- -------------------- ------------------ ----------
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7902 FORD ANALYST 3000
7934 MILLER CLERK 1300
第二步:
SQL> select e.empno,e.ename,e.job,e.sal,s.grade from emp e,salgrade s
2 where e.sal between s.losal and s.hisal;
EMPNO ENAME JOB SAL GRADE
---------- -------------------- ------------------ ---------- ----------
7369 SMITH CLERK 800 1
7900 JAMES CLERK 950 1
7876 ADAMS CLERK 1100 1
7521 WARD SALESMAN 1250 2
7654 MARTIN SALESMAN 1250 2
7934 MILLER CLERK 1300 2
7844 TURNER SALESMAN 1500 3
7499 ALLEN SALESMAN 1600 3
7782 CLARK MANAGER 2450 4
7698 BLAKE MANAGER 2850 4
7566 JONES MANAGER 2975 4
7788 SCOTT ANALYST 3000 4
7902 FORD ANALYST 3000 4
7839 KING PRESIDENT 5000 5
查询每个雇员的姓名、职位、编号、工资、工资等级、部门名称
第一步:查询每个雇员的编号、姓名、职位、工资;
SQL> select e.empno,e.ename,e.sal,e.job from emp e;
EMPNO ENAME SAL JOB
---------- -------------------- ---------- ------------------
7369 SMITH 800 CLERK
7499 ALLEN 1600 SALESMAN
7521 WARD 1250 SALESMAN
7566 JONES 2975 MANAGER
7654 MARTIN 1250 SALESMAN
7698 BLAKE 2850 MANAGER
7782 CLARK 2450 MANAGER
7788 SCOTT 3000 ANALYST
7839 KING 5000 PRESIDENT
7844 TURNER 1500 SALESMAN
7876 ADAMS 1100 CLERK
7900 JAMES 950 CLERK
7902 FORD 3000 ANALYST
7934 MILLER 1300 CLERK
第二步:加入工资等级的查询,此时用where子句筛选
SQL> select e.empno,e.ename,e.sal,e.job,s.grade
2 from emp e,salgrade s
3 where e.sal between s.losal and s.hisal;
EMPNO ENAME SAL JOB GRADE
---------- -------------------- ---------- ------------------ ----------
7369 SMITH 800 CLERK 1
7900 JAMES 950 CLERK 1
7876 ADAMS 1100 CLERK 1
7521 WARD 1250 SALESMAN 2
7654 MARTIN 1250 SALESMAN 2
7934 MILLER 1300 CLERK 2
7844 TURNER 1500 SALESMAN 3
7499 ALLEN 1600 SALESMAN 3
7782 CLARK 2450 MANAGER 4
7698 BLAKE 2850 MANAGER 4
7566 JONES 2975 MANAGER 4
7788 SCOTT 3000 ANALYST 4
7902 FORD 3000 ANALYST 4
7839 KING 5000 PRESIDENT 5
第三步:查询部门信息,增加dept表的查询,用and消除笛卡尔积
SQL> select e.empno,e.ename,e.sal,e.job,s.grade,d.dname
2 from emp e,salgrade s,dept d
3 where e.sal between s.losal and s.hisal
4 and e.deptno=d.deptno;
EMPNO ENAME SAL JOB GRADE DNAME
---------- -------------------- ---------- ------------------ ---------- ----------------------------
7839 KING 5000 PRESIDENT 5 ACCOUNTING
7902 FORD 3000 ANALYST 4 RESEARCH
7788 SCOTT 3000 ANALYST 4 RESEARCH
7566 JONES 2975 MANAGER 4 RESEARCH
7698 BLAKE 2850 MANAGER 4 SALES
7782 CLARK 2450 MANAGER 4 ACCOUNTING
7499 ALLEN 1600 SALESMAN 3 SALES
7844 TURNER 1500 SALESMAN 3 SALES
7934 MILLER 1300 CLERK 2 ACCOUNTING
7521 WARD 1250 SALESMAN 2 SALES
7654 MARTIN 1250 SALESMAN 2 SALES
7876 ADAMS 1100 CLERK 1 RESEARCH
7900 JAMES 950 CLERK 1 SALES
7369 SMITH 800 CLERK 1 RESEARCH
表的连接
內连接(等值连接):所有满足条件的数据都会被显示出来;
外连接(左外连接、右外连接、全外连接):控制左表与右表的数据是否全部显示。
创建一行记录
SQL> insert into emp(empno,ename,job)values(8888,'张三','CLERK');
等值连接
此时设置的张三并没有显示,where只筛选满足条件的记录,而空值不会显示。
SQL> select e.empno,e.ename,e.sal,e.job,d.dname,d.deptno,d.loc
2 from emp e,dept d
3 where e.deptno=d.deptno;
EMPNO ENAME SAL JOB DNAME DEPTNO LOC
---------- -------------------- ---------- ------------------ ---------------------------- ---------- --------------------------
7839 KING 5000 PRESIDENT ACCOUNTING 10 NEW YORK
7934 MILLER 1300 CLERK ACCOUNTING 10 NEW YORK
7782 CLARK 2450 MANAGER ACCOUNTING 10 NEW YORK
7369 SMITH 800 CLERK RESEARCH 20 DALLAS
7902 FORD 3000 ANALYST RESEARCH 20 DALLAS
7876 ADAMS 1100 CLERK RESEARCH 20 DALLAS
7788 SCOTT 3000 ANALYST RESEARCH 20 DALLAS
7566 JONES 2975 MANAGER RESEARCH 20 DALLAS
7900 JAMES 950 CLERK SALES 30 CHICAGO
7499 ALLEN 1600 SALESMAN SALES 30 CHICAGO
7698 BLAKE 2850 MANAGER SALES 30 CHICAGO
7654 MARTIN 1250 SALESMAN SALES 30 CHICAGO
7844 TURNER 1500 SALESMAN SALES 30 CHICAGO
7521 WARD 1250 SALESMAN SALES 30 CHICAGO
外连接
让等值判断的两端又一边的数据可以全部显示出来,如果实现外连接则使用(+)的语法完成。
左外连接: 字段 = 字段(+);
右外连接: 字段 (+) = 字段;
左外连接
SQL> select e.empno,e.ename,e.sal,e.job,d.dname,d.deptno,d.loc
2 from emp e,dept d
3 where e.deptno=d.deptno(+);
EMPNO ENAME SAL JOB DNAME DEPTNO LOC
---------- -------------------- ---------- ------------------ ---------------------------- ---------- --------------------------
7934 MILLER 1300 CLERK ACCOUNTING 10 NEW YORK
7839 KING 5000 PRESIDENT ACCOUNTING 10 NEW YORK
7782 CLARK 2450 MANAGER ACCOUNTING 10 NEW YORK
7902 FORD 3000 ANALYST RESEARCH 20 DALLAS
7876 ADAMS 1100 CLERK RESEARCH 20 DALLAS
7788 SCOTT 3000 ANALYST RESEARCH 20 DALLAS
7566 JONES 2975 MANAGER RESEARCH 20 DALLAS
7369 SMITH 800 CLERK RESEARCH 20 DALLAS
7900 JAMES 950 CLERK SALES 30 CHICAGO
7844 TURNER 1500 SALESMAN SALES 30 CHICAGO
7698 BLAKE 2850 MANAGER SALES 30 CHICAGO
7654 MARTIN 1250 SALESMAN SALES 30 CHICAGO
7521 WARD 1250 SALESMAN SALES 30 CHICAGO
7499 ALLEN 1600 SALESMAN SALES 30 CHICAGO
8888 张三 CLERK
右外连接
SQL> select e.empno,e.ename,e.sal,e.job,d.dname,d.deptno,d.loc
2 from emp e,dept d
3 where e.deptno(+)=d.deptno;
EMPNO ENAME SAL JOB DNAME DEPTNO LOC
---------- -------------------- ---------- ------------------ ---------------------------- ---------- --------------------------
7839 KING 5000 PRESIDENT ACCOUNTING 10 NEW YORK
7934 MILLER 1300 CLERK ACCOUNTING 10 NEW YORK
7782 CLARK 2450 MANAGER ACCOUNTING 10 NEW YORK
7369 SMITH 800 CLERK RESEARCH 20 DALLAS
7902 FORD 3000 ANALYST RESEARCH 20 DALLAS
7876 ADAMS 1100 CLERK RESEARCH 20 DALLAS
7788 SCOTT 3000 ANALYST RESEARCH 20 DALLAS
7566 JONES 2975 MANAGER RESEARCH 20 DALLAS
7900 JAMES 950 CLERK SALES 30 CHICAGO
7499 ALLEN 1600 SALESMAN SALES 30 CHICAGO
7698 BLAKE 2850 MANAGER SALES 30 CHICAGO
7654 MARTIN 1250 SALESMAN SALES 30 CHICAGO
7844 TURNER 1500 SALESMAN SALES 30 CHICAGO
7521 WARD 1250 SALESMAN SALES 30 CHICAGO
OPERATIONS 40 BOSTON
查询每个雇员的姓名、职位、领导姓名
SQL> select e.ename,e.job,m.ename
2 from emp e,emp m
3 where e.mgr=m.empno;
ENAME JOB ENAME
-------------------- ------------------ --------------------
FORD ANALYST JONES
SCOTT ANALYST JONES
TURNER SALESMAN BLAKE
ALLEN SALESMAN BLAKE
WARD SALESMAN BLAKE
JAMES CLERK BLAKE
MARTIN SALESMAN BLAKE
MILLER CLERK CLARK
ADAMS CLERK SCOTT
BLAKE MANAGER KING
JONES MANAGER KING
CLARK MANAGER KING
SMITH CLERK FORD
SQL> select e.ename,e.job,m.ename
2 from emp e,emp m
3 where e.mgr=m.empno(+);
ENAME JOB ENAME
-------------------- ------------------ --------------------
FORD ANALYST JONES
SCOTT ANALYST JONES
JAMES CLERK BLAKE
TURNER SALESMAN BLAKE
MARTIN SALESMAN BLAKE
WARD SALESMAN BLAKE
ALLEN SALESMAN BLAKE
MILLER CLERK CLARK
ADAMS CLERK SCOTT
CLARK MANAGER KING
BLAKE MANAGER KING
JONES MANAGER KING
SMITH CLERK FORD
KING PRESIDENT
张三 CLERK
SQL:1999语法
一、交叉连接( cross join),产生笛卡儿积
SQL> select *from emp cross join dept;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- ---------- ---------------------------- --------------------------
8888 张三 CLERK 10 ACCOUNTING NEW YORK
7369 SMITH CLERK 7902 17-12月-80 800 20 10 ACCOUNTING NEW YORK
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 10 ACCOUNTING NEW YORK
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 02-4月 -81 2975 20 10 ACCOUNTING NEW YORK
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 10 ACCOUNTING NEW YORK
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-11月-81 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 10 ACCOUNTING NEW YORK
7876 ADAMS CLERK 7788 23-5月 -87 1100 20 10 ACCOUNTING NEW YORK
7900 JAMES CLERK 7698 03-12月-81 950 30 10 ACCOUNTING NEW YORK
7902 FORD ANALYST 7566 03-12月-81 3000 20 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 23-1月 -82 1300 10 10 ACCOUNTING NEW YORK
8888 张三 CLERK 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-12月-80 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 02-4月 -81 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 20 RESEARCH DALLAS
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 20 RESEARCH DALLAS
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-11月-81 5000 10 20 RESEARCH DALLAS
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-5月 -87 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-12月-81 950 30 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-12月-81 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-1月 -82 1300 10 20 RESEARCH DALLAS
8888 张三 CLERK 30 SALES
CHICAGO
7369 SMITH CLERK 7902 17-12月-80 800 20 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-4月 -81 2975 20 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 30 SALES CHICAGO
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 30 SALES CHICAGO
7839 KING PRESIDENT 17-11月-81 5000 10 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 23-5月 -87 1100 20 30 SALES CHICAGO
7900 JAMES CLERK 7698 03-12月-81 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-12月-81 3000 20 30 SALES CHICAGO
7934 MILLER CLERK 7782 23-1月 -82 1300 10 30 SALES CHICAGO
8888 张三 CLERK 40 OPERATIONS BOSTON
7369 SMITH CLERK 7902 17-12月-80 800 20 40 OPERATIONS BOSTON
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 40 OPERATIONS BOSTON
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 40 OPERATIONS BOSTON
7566 JONES MANAGER 7839 02-4月 -81 2975 20 40 OPERATIONS BOSTON
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 40 OPERATIONS BOSTON
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 40 OPERATIONS BOSTON
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 40 OPERATIONS BOSTON
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 40 OPERATIONS BOSTON
7839 KING PRESIDENT 17-11月-81 5000 10 40 OPERATIONS BOSTON
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 40 OPERATIONS BOSTON
7876 ADAMS CLERK 7788 23-5月 -87 1100 20 40 OPERATIONS BOSTON
7900 JAMES CLERK 7698 03-12月-81 950 30 40 OPERATIONS BOSTON
7902 FORD ANALYST 7566 03-12月-81 3000 20 40 OPERATIONS BOSTON
7934 MILLER CLERK 7782 23-1月 -82 1300 10 40 OPERATIONS BOSTON
二、自然连接(natural join),消除笛卡儿积,属于內连接;默认条件下将关联条件设置在第一列上。
SQL> select *from emp natural join dept;
DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC
---------- ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------------------------- --------------------------
10 7839 KING PRESIDENT 17-11月-81 5000 ACCOUNTING NEW YORK
10 7934 MILLER CLERK 7782 23-1月 -82 1300 ACCOUNTING NEW YORK
10 7782 CLARK MANAGER 7839 09-6月 -81 2450 ACCOUNTING NEW YORK
20 7369 SMITH CLERK 7902 17-12月-80 800 RESEARCH DALLAS
20 7902 FORD ANALYST 7566 03-12月-81 3000 RESEARCH DALLAS
20 7876 ADAMS CLERK 7788 23-5月 -87 1100 RESEARCH DALLAS
20 7788 SCOTT ANALYST 7566 19-4月 -87 3000 RESEARCH DALLAS
20 7566 JONES MANAGER 7839 02-4月 -81 2975 RESEARCH DALLAS
30 7900 JAMES CLERK 7698 03-12月-81 950 SALES CHICAGO
30 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 SALES CHICAGO
30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 SALES CHICAGO
30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 SALES CHICAGO
30 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 SALES CHICAGO
30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 SALES CHICAGO
------------------------------------------------------------------------------------------------------------------
SQL> select *from emp,dept where emp.deptno=dept.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- ---------- ---------------------------- --------------------------
7839 KING PRESIDENT 17-11月-81 5000 10 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 23-1月 -82 1300 10 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 10 ACCOUNTING NEW YORK
7369 SMITH CLERK 7902 17-12月-80 800 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-12月-81 3000 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-5月 -87 1100 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 02-4月 -81 2975 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-12月-81 950 30 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 30 SALES CHICAGO
三、设置关联字段(join...using)
SQL> select *from emp join dept using(deptno);
DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC
---------- ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------------------------- --------------------------
10 7839 KING PRESIDENT 17-11月-81 5000 ACCOUNTING NEW YORK
10 7934 MILLER CLERK 7782 23-1月 -82 1300 ACCOUNTING NEW YORK
10 7782 CLARK MANAGER 7839 09-6月 -81 2450 ACCOUNTING NEW YORK
20 7369 SMITH CLERK 7902 17-12月-80 800 RESEARCH DALLAS
20 7902 FORD ANALYST 7566 03-12月-81 3000 RESEARCH DALLAS
20 7876 ADAMS CLERK 7788 23-5月 -87 1100 RESEARCH DALLAS
20 7788 SCOTT ANALYST 7566 19-4月 -87 3000 RESEARCH DALLAS
20 7566 JONES MANAGER 7839 02-4月 -81 2975 RESEARCH DALLAS
30 7900 JAMES CLERK 7698 03-12月-81 950 SALES CHICAGO
30 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 SALES CHICAGO
30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 SALES CHICAGO
30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 SALES CHICAGO
30 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 SALES CHICAGO
30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 SALES CHICAGO
四、join...on(如果没有关联字段,则使用on来设置条件)
SQL> select *from emp e join salgrade s on(e.sal between s.losal and s.hisal);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO GRADE LOSAL HISAL
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20 1 700 1200
7900 JAMES CLERK 7698 03-12月-81 950 30 1 700 1200
7876 ADAMS CLERK 7788 23-5月 -87 1100 20 1 700 1200
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 2 1201 1400
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 2 1201 1400
7934 MILLER CLERK 7782 23-1月 -82 1300 10 2 1201 1400
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 3 1401 2000
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 3 1401 2000
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 4 2001 3000
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 4 2001 3000
7566 JONES MANAGER 7839 02-4月 -81 2975 20 4 2001 3000
7902 FORD ANALYST 7566 03-12月-81 3000 20 4 2001 3000
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 4 2001 3000
7839 KING PRESIDENT 17-11月-81 5000 10 5 3001 9999
五、全连接、左右连接
//左
SQL> select *from emp left outer join dept using(deptno);
DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC
---------- ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------------------------- --------------------------
10 7934 MILLER CLERK 7782 23-1月 -82 1300 ACCOUNTING NEW YORK
10 7839 KING PRESIDENT 17-11月-81 5000 ACCOUNTING NEW YORK
10 7782 CLARK MANAGER 7839 09-6月 -81 2450 ACCOUNTING NEW YORK
20 7902 FORD ANALYST 7566 03-12月-81 3000 RESEARCH DALLAS
20 7876 ADAMS CLERK 7788 23-5月 -87 1100 RESEARCH DALLAS
20 7788 SCOTT ANALYST 7566 19-4月 -87 3000 RESEARCH DALLAS
20 7566 JONES MANAGER 7839 02-4月 -81 2975 RESEARCH DALLAS
20 7369 SMITH CLERK 7902 17-12月-80 800 RESEARCH DALLAS
30 7900 JAMES CLERK 7698 03-12月-81 950 SALES CHICAGO
30 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 SALES CHICAGO
30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 SALES CHICAGO
30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 SALES CHICAGO
30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 SALES CHICAGO
30 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 SALES CHICAGO
8888 张三 CLERK
//右
SQL> select *from emp right outer join dept using(deptno);
DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC
---------- ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------------------------- --------------------------
10 7839 KING PRESIDENT 17-11月-81 5000 ACCOUNTING NEW YORK
10 7934 MILLER CLERK 7782 23-1月 -82 1300 ACCOUNTING NEW YORK
10 7782 CLARK MANAGER 7839 09-6月 -81 2450 ACCOUNTING NEW YORK
20 7369 SMITH CLERK 7902 17-12月-80 800 RESEARCH DALLAS
20 7902 FORD ANALYST 7566 03-12月-81 3000 RESEARCH DALLAS
20 7876 ADAMS CLERK 7788 23-5月 -87 1100 RESEARCH DALLAS
20 7788 SCOTT ANALYST 7566 19-4月 -87 3000 RESEARCH DALLAS
20 7566 JONES MANAGER 7839 02-4月 -81 2975 RESEARCH DALLAS
30 7900 JAMES CLERK 7698 03-12月-81 950 SALES CHICAGO
30 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 SALES CHICAGO
30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 SALES CHICAGO
30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 SALES CHICAGO
30 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 SALES CHICAGO
30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 SALES CHICAGO
40 OPERATIONS BOSTON
//全
SQL> select *from emp full outer join dept using(deptno);
DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC
---------- ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------------------------- --------------------------
8888 张三 CLERK
20 7369 SMITH CLERK 7902 17-12月-80 800 RESEARCH DALLAS
30 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 SALES CHICAGO
30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 SALES CHICAGO
20 7566 JONES MANAGER 7839 02-4月 -81 2975 RESEARCH DALLAS
30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 SALES CHICAGO
30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 SALES CHICAGO
10 7782 CLARK MANAGER 7839 09-6月 -81 2450 ACCOUNTING NEW YORK
20 7788 SCOTT ANALYST 7566 19-4月 -87 3000 RESEARCH DALLAS
10 7839 KING PRESIDENT 17-11月-81 5000 ACCOUNTING NEW YORK
30 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 SALES CHICAGO
20 7876 ADAMS CLERK 7788 23-5月 -87 1100 RESEARCH DALLAS
30 7900 JAMES CLERK 7698 03-12月-81 950 SALES CHICAGO
20 7902 FORD ANALYST 7566 03-12月-81 3000 RESEARCH DALLAS
10 7934 MILLER CLERK 7782 23-1月 -82 1300 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON