oracle多部门查询,oracle多表查询

SQL> --等值连接

SQL> --查询员工信息：员工号  姓名 月薪 部门名称

SQL> set linesize 80

SQL> desc dept

名称                                      是否为空? 类型

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

DEPTNO                                    NOT NULL NUMBER(2)

DNAME                                              VARCHAR2(14)

LOC                                                VARCHAR2(13)

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

2  from emp e,dept d

3  where e.deptno=d.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

EMPNO ENAME             SAL DNAME

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

7900 JAMES             950 SALES

7902 FORD             3000 RESEARCH

7934 MILLER           1300 ACCOUNTING

已选择 14 行。

SQL> --不等值连接

SQL> --查询员工信息：员工号  姓名 月薪 工资级别

SQL>

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

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

DEPT                           TABLE

EMP                            TABLE

BONUS                          TABLE

SALGRADE                       TABLE

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.empno,e.ename,e.sal,s.grade

2  from emp e,salgrade s

3  where e.sal between s.losal  and s.hisal;

EMPNO ENAME             SAL      GRADE

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

7369 SMITH             800          1

7900 JAMES             950          1

7876 ADAMS            1100          1

7521 WARD             1250          2

7654 MARTIN           1250          2

7934 MILLER           1300          2

7844 TURNER           1500          3

7499 ALLEN            1600          3

7782 CLARK            2450          4

7698 BLAKE            2850          4

7566 JONES            2975          4

EMPNO ENAME             SAL      GRADE

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

7788 SCOTT            3000          4

7902 FORD             3000          4

7839 KING             5000          5

已选择 14 行。

SQL> host cls

SQL> --外连接：

SQL> --按部门统计员工信息：部门号 部门名称  人数

SQL> select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数

2  from emp e,dept d

3  where e.deptno=d.deptno

4  group by d.deptno,d.dname;

部门号 部门名称             人数

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

10 ACCOUNTING              3

20 RESEARCH                5

30 SALES                   6

SQL> select count(*) from emp;

COUNT(*)

----------

14

SQL> select * from dept;

DEPTNO DNAME          LOC

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

10 ACCOUNTING     NEW YORK

20 RESEARCH       DALLAS

30 SALES          CHICAGO

40 OPERATIONS     BOSTON

SQL> select * from emp where deptno=40;

未选定行

SQL> /*

SQL> 希望把某些不成立的记录(40号部门)，任然包含在最后的结果中 ---> 外连接

SQL> 左外连接: 当where e.deptno=d.deptno不成立的时候，等号左边的表任然被包含在最后的结果中

SQL>     写法:where e.deptno=d.deptno(+)

SQL> 右外连接: 当where e.deptno=d.deptno不成立的时候，等号右边的表任然被包含在最后的结果中

SQL>     写法:where e.deptno(+)=d.deptno

SQL> */

SQL> select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数

2  from emp e,dept d

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

4  group by d.deptno,d.dname;

部门号 部门名称             人数

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

10 ACCOUNTING              3

40 OPERATIONS              0

20 RESEARCH                5

30 SALES                   6

SQL> host cls

SQL> --自连接

SQL> --查询员工信息：员工姓名  老板姓名

SQL> set linesize 200

SQL> select * from emp;

EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO

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

7369 SMITH      CLERK           7902 17-12月-80            800                    20

7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30

7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30

7566 JONES      MANAGER         7839 02-4月 -81           2975                    20

7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30

7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30

7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10

7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20

7839 KING       PRESIDENT            17-11月-81           5000                    10

7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30

7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20

EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO

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

7900 JAMES      CLERK           7698 03-12月-81            950                    30

7902 FORD       ANALYST         7566 03-12月-81           3000                    20

7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

已选择 14 行。

SQL>  --自连接: 通过表的别名，将同一张表视为多张表

SQL> select e.ename 员工姓名,b.ename 老板姓名

2  from emp e,emp b

3  where e.mgr=b.empno;

员工姓名   老板姓名

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

FORD       JONES

SCOTT      JONES

JAMES      BLAKE

TURNER     BLAKE

MARTIN     BLAKE

WARD       BLAKE

ALLEN      BLAKE

MILLER     CLARK

ADAMS      SCOTT

CLARK      KING

BLAKE      KING

员工姓名   老板姓名

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

JONES      KING

SMITH      FORD

已选择 13 行。

SQL> select count(*)

2  from emp e,emp b;

COUNT(*)

----------

196

SQL> --自连接不适合操作大表

SQL> --层次查询

SQL> select level,empno,ename,mgr

2  from emp

3  connect by prior empno=mgr

4  start with mgr is null

5  order by 1;

LEVEL      EMPNO ENAME             MGR

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

1       7839 KING

2       7566 JONES            7839

2       7698 BLAKE            7839

2       7782 CLARK            7839

3       7902 FORD             7566

3       7521 WARD             7698

3       7900 JAMES            7698

3       7934 MILLER           7782

3       7499 ALLEN            7698

3       7788 SCOTT            7566

3       7654 MARTIN           7698

LEVEL      EMPNO ENAME             MGR

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

3       7844 TURNER           7698

4       7876 ADAMS            7788

4       7369 SMITH            7902

已选择 14 行。

SQL> spool off