多表查询
连接的类型:
等值连接，不等值连接，外连接，自连接
等值连接 SQL> --等值连接: 查询员工信息：员工号，姓名，薪水和部门名称
SQL> select empno,ename,sal,dname
2 from emp,dept
3 where emp.deptno=dept.deptno;
EMPNO ENAME SAL DNAME
---------- -------- ----- --------------
7369 SMITH 800 RESEARCH
7499 ALLEN 1600 SALES
7521 WARD 1250 SALES
7566 JONES 2975 RESEARCH
7654 MARTIN 1250 SALES
7698 BLAKE 2850 SALES
7782 CLARK 2450 ACCOUNTING
7788 SCOTT 3000 RESEARCH
7839 KING 5000 ACCOUNTING
7844 TURNER 1500 SALES
7876 ADAMS 1100 RESEARCH
7900 JAMES 950 SALES
7902 FORD 3000 RESEARCH
7934 MILLER 1300 ACCOUNTING
已选择14行。
SQL> --等值连接: 查询员工信息：员工号，姓名，薪水,部门名称,部门号（两张表都有部门号，会出错）
SQL> ed
已写入 file afiedt.buf
1 select empno,ename,sal,dname,deptno
2 from emp,dept
3* where emp.deptno=dept.deptno
SQL> /
select empno,ename,sal,dname,deptno
*
第 1 行出现错误:
ORA-00918: 未明确定义列
SQL> ed
已写入 file afiedt.buf
1 select e.empno,e.ename,e.sal,d.dname,d.deptno
2 from emp e,dept d
3* where emp.deptno=dept.deptno
SQL> /
where emp.deptno=dept.deptno
*
第 3 行出现错误:
ORA-00904: "DEPT"."DEPTNO": 标识符无效
（解决办法：给每张表起一个别名，然后表明数据是哪一个别名的）
SQL> ed
已写入 file afiedt.buf
1 select e.empno,e.ename,e.sal,d.dname,d.deptno
2 from emp e,dept d
3* where e.deptno=d.deptno
SQL> /
EMPNO ENAME SAL DNAME DEPTNO
---------- -------- ----- -------------- ----------
7369 SMITH 800 RESEARCH 20
7499 ALLEN 1600 SALES 30
7521 WARD 1250 SALES 30
7566 JONES 2975 RESEARCH 20
7654 MARTIN 1250 SALES 30
7698 BLAKE 2850 SALES 30
7782 CLARK 2450 ACCOUNTING 10
7788 SCOTT 3000 RESEARCH 20
7839 KING 5000 ACCOUNTING 10
7844 TURNER 1500 SALES 30
7876 ADAMS 1100 RESEARCH 20
7900 JAMES 950 SALES 30
7902 FORD 3000 RESEARCH 20
7934 MILLER 1300 ACCOUNTING 10
已选择14行。
不等值连接 SQL> --不等值连接:查询员工的工资级别
SQL> select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL> select e.ename,e.sal,s.grade
2 from emp e,salgrade s
3 where e.sal between s.losal and s.hisal;
ENAME SAL GRADE
-------- ----- ----------
SMITH 800 1
JAMES 950 1
ADAMS 1100 1
WARD 1250 2
MARTIN 1250 2
MILLER 1300 2
TURNER 1500 3
ALLEN 1600 3
CLARK 2450 4
BLAKE 2850 4
JONES 2975 4
SCOTT 3000 4
FORD 3000 4
KING 5000 5
已选择14行。
SQL> host cls
外链接 SQL> --按部门统计员工人数:部门号，部门名称，人数
SQL> select d.deptno,d.dname,count(e.empno) 人数
2 from dept d,emp e
3 where d.deptno=e.deptno
4 group by d.deptno,d.dname;
DEPTNO DNAME 人数
---------- -------------- ----------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
（上述结果少一个人数为null的值）
SQL> select count(*) from emp where deptno=10;
COUNT(*)
----------
3
SQL> select count(*) from emp where deptno=20;
COUNT(*)
----------
5
SQL> select count(*) from emp where deptno=30;
COUNT(*)
----------
6
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select count(*) from emp where deptno=40;
COUNT(*)
----------
0
SQL> --希望：在结果中包含某些不成立时的记录
SQL> /*
SQL> 外连接：
SQL> 左外连接：where d.deptno=e.deptno,当条件不成立时，等号左边所代表的表的信息任然被显示
SQL> 写法: where d.deptno=e.deptno(+)
SQL> 右外连接：where d.deptno=e.deptno,当条件不成立时，等号右边所代表的表的信息任然被显示
SQL> 写法：where d.deptno(+)=e.deptno
SQL> 全外连接：where d.deptno=e.deptno,当条件不成立时，等号两边所代表的表的信息任然被显示
SQL> 写法：where d.deptno(+)=e.deptno(+)
SQL> */
SQL> select d.deptno,d.dname,count(e.empno) 人数
2 from dept |