《卸甲笔记》-多表查询之一

1查询emp表中数据量 14条数据
Oracle

SQL> select count(*) from emp;

  COUNT(*)
----------
    14

PPAS

scott=# select count(*) from emp;
 count 
-------
    14
(1 row)

2查询dept表中的数据库 4条数据
Oracle

SQL> select count(*) from dept;

  COUNT(*)
----------
     4

PPAS

scott=# select count(*) from dept;
 count 
-------
     4
(1 row)

3查询所有的雇员和部门的全部详细信息
Oracle

SQL> select * from emp,dept;

     EMPNO ENAME      JOB        MGR HIREDATE        SAL      COMM       DEPTNO     DEPTNO DNAME         LOC
---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- --------------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800               20      10 ACCOUNTING      NEW YORK
      7499 ALLEN      SALESMAN           7698 20-FEB-81           1600       300           30      10 ACCOUNTING      NEW YORK
      7521 WARD       SALESMAN           7698 22-FEB-81           1250       500           30      10 ACCOUNTING      NEW YORK
      7566 JONES      MANAGER           7839 02-APR-81           2975               20      10 ACCOUNTING      NEW YORK
      7654 MARTIN     SALESMAN           7698 28-SEP-81           1250      1400           30      10 ACCOUNTING      NEW YORK
      7698 BLAKE      MANAGER           7839 01-MAY-81           2850               30      10 ACCOUNTING      NEW YORK
      7782 CLARK      MANAGER           7839 09-JUN-81           2450               10      10 ACCOUNTING      NEW YORK
      7788 SCOTT      ANALYST           7566 19-APR-87           3000               20      10 ACCOUNTING      NEW YORK
      7839 KING       PRESIDENT         17-NOV-81           5000               10      10 ACCOUNTING      NEW YORK
      7844 TURNER     SALESMAN           7698 08-SEP-81           1500         0           30      10 ACCOUNTING      NEW YORK
      7876 ADAMS      CLERK           7788 23-MAY-87           1100               20      10 ACCOUNTING      NEW YORK
      7900 JAMES      CLERK           7698 03-DEC-81        950               30      10 ACCOUNTING      NEW YORK
      7902 FORD       ANALYST           7566 03-DEC-81           3000               20      10 ACCOUNTING      NEW YORK
      7934 MILLER     CLERK           7782 23-JAN-82           1300               10      10 ACCOUNTING      NEW YORK
      7369 SMITH      CLERK           7902 17-DEC-80        800               20      20 RESEARCH         DALLAS
      7499 ALLEN      SALESMAN           7698 20-FEB-81           1600       300           30      20 RESEARCH         DALLAS
      7521 WARD       SALESMAN           7698 22-FEB-81           1250       500           30      20 RESEARCH         DALLAS
      7566 JONES      MANAGER           7839 02-APR-81           2975               20      20 RESEARCH         DALLAS
      7654 MARTIN     SALESMAN           7698 28-SEP-81           1250      1400           30      20 RESEARCH         DALLAS
      7698 BLAKE      MANAGER           7839 01-MAY-81           2850               30      20 RESEARCH         DALLAS
      7782 CLARK      MANAGER           7839 09-JUN-81           2450               10      20 RESEARCH         DALLAS
      7788 SCOTT      ANALYST           7566 19-APR-87           3000               20      20 RESEARCH         DALLAS
      7839 KING       PRESIDENT         17-NOV-81           5000               10      20 RESEARCH         DALLAS
      7844 TURNER     SALESMAN           7698 08-SEP-81           1500         0           30      20 RESEARCH         DALLAS
      7876 ADAMS      CLERK           7788 23-MAY-87           1100               20      20 RESEARCH         DALLAS
      7900 JAMES      CLERK           7698 03-DEC-81        950               30      20 RESEARCH         DALLAS
      7902 FORD       ANALYST           7566 03-DEC-81           3000               20      20 RESEARCH         DALLAS
      7934 MILLER     CLERK           7782 23-JAN-82           1300               10      20 RESEARCH         DALLAS
      7369 SMITH      CLERK           7902 17-DEC-80        800               20      30 SALES         CHICAGO
      7499 ALLEN      SALESMAN           7698 20-FEB-81           1600       300           30      30 SALES         CHICAGO
      7521 WARD       SALESMAN           7698 22-FEB-81           1250       500           30      30 SALES         CHICAGO
      7566 JONES      MANAGER           7839 02-APR-81           2975               20      30 SALES         CHICAGO
      7654 MARTIN     SALESMAN           7698 28-SEP-81           1250      1400           30      30 SALES         CHICAGO
      7698 BLAKE      MANAGER           7839 01-MAY-81           2850               30      30 SALES         CHICAGO
      7782 CLARK      MANAGER           7839 09-JUN-81           2450               10      30 SALES         CHICAGO
      7788 SCOTT      ANALYST           7566 19-APR-87           3000               20      30 SALES         CHICAGO
      7839 KING       PRESIDENT         17-NOV-81           5000               10      30 SALES         CHICAGO
      7844 TURNER     SALESMAN           7698 08-SEP-81           1500         0           30      30 SALES         CHICAGO
      7876 ADAMS      CLERK           7788 23-MAY-87           1100               20      30 SALES         CHICAGO
      7900 JAMES      CLERK           7698 03-DEC-81        950               30      30 SALES         CHICAGO
      7902 FORD       ANALYST           7566 03-DEC-81           3000               20      30 SALES         CHICAGO
      7934 MILLER     CLERK           7782 23-JAN-82           1300               10      30 SALES         CHICAGO
      7369 SMITH      CLERK           7902 17-DEC-80        800               20      40 OPERATIONS      BOSTON
      7499 ALLEN      SALESMAN           7698 20-FEB-81           1600       300           30      40 OPERATIONS      BOSTON
      7521 WARD       SALESMAN           7698 22-FEB-81           1250       500           30      40 OPERATIONS      BOSTON
      7566 JONES      MANAGER           7839 02-APR-81           2975               20      40 OPERATIONS      BOSTON
      7654 MARTIN     SALESMAN           7698 28-SEP-81           1250      1400           30      40 OPERATIONS      BOSTON
      7698 BLAKE      MANAGER           7839 01-MAY-81           2850               30      40 OPERATIONS      BOSTON
      7782 CLARK      MANAGER           7839 09-JUN-81           2450               10      40 OPERATIONS      BOSTON
      7788 SCOTT      ANALYST           7566 19-APR-87           3000               20      40 OPERATIONS      BOSTON
      7839 KING       PRESIDENT         17-NOV-81           5000               10      40 OPERATIONS      BOSTON
      7844 TURNER     SALESMAN           7698 08-SEP-81           1500         0           30      40 OPERATIONS      BOSTON
      7876 ADAMS      CLERK           7788 23-MAY-87           1100               20      40 OPERATIONS      BOSTON
      7900 JAMES      CLERK           7698 03-DEC-81        950               30      40 OPERATIONS      BOSTON
      7902 FORD       ANALYST           7566 03-DEC-81           3000               20      40 OPERATIONS      BOSTON
      7934 MILLER     CLERK           7782 23-JAN-82           1300               10      40 OPERATIONS      BOSTON

56 rows selected.

PPAS

scott=# select * from emp,dept;
 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno | deptno |   dname    |   loc    
-------+--------+-----------+------+--------------------+---------+---------+--------+--------+------------+----------
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20 |     10 | ACCOUNTING | NEW YORK
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20 |     30 | SALES      | CHICAGO
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20 |     40 | OPERATIONS | BOSTON
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30 |     10 | ACCOUNTING | NEW YORK
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30 |     20 | RESEARCH   | DALLAS
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30 |     40 | OPERATIONS | BOSTON
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30 |     10 | ACCOUNTING | NEW YORK
  30 |     20 | RESEARCH   | DALLAS| 22-FEB-81 00:00:00 | 1250.00 |  500.00 |   --More--
  30 |     30 | SALES      | CHICAGO 22-FEB-81 00:00:00 | 1250.00 |  500.00 |   --More--
  30 |     40 | OPERATIONS | BOSTON| 22-FEB-81 00:00:00 | 1250.00 |  500.00 |   --More--
  20 |     10 | ACCOUNTING | NEW YORK02-APR-81 00:00:00 | 2975.00 |         |   --More--
  20 |     20 | RESEARCH   | DALLAS| 02-APR-81 00:00:00 | 2975.00 |         |   --More--
  20 |     30 | SALES      | CHICAGO 02-APR-81 00:00:00 | 2975.00 |         |   --More--
  20 |     40 | OPERATIONS | BOSTON| 02-APR-81 00:00:00 | 2975.00 |         |   --More--
  30 |     10 | ACCOUNTING | NEW YORK28-SEP-81 00:00:00 | 1250.00 | 1400.00 |   --More--
  30 |     20 | RESEARCH   | DALLAS| 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |   --More--
  30 |     30 | SALES      | CHICAGO 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |   --More--
  30 |     40 | OPERATIONS | BOSTON| 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |   --More--
  30 |     10 | ACCOUNTING | NEW YORK01-MAY-81 00:00:00 | 2850.00 |         |   --More--
  30 |     20 | RESEARCH   | DALLAS| 01-MAY-81 00:00:00 | 2850.00 |         |   --More--
  30 |     30 | SALES      | CHICAGO 01-MAY-81 00:00:00 | 2850.00 |         |   --More--
  30 |     40 | OPERATIONS | BOSTON| 01-MAY-81 00:00:00 | 2850.00 |         |   --More--
  10 |     10 | ACCOUNTING | NEW YORK09-JUN-81 00:00:00 | 2450.00 |         |   --More--
  10 |     20 | RESEARCH   | DALLAS| 09-JUN-81 00:00:00 | 2450.00 |         |   --More--
  10 |     30 | SALES      | CHICAGO 09-JUN-81 00:00:00 | 2450.00 |         |   --More--
  10 |     40 | OPERATIONS | BOSTON| 09-JUN-81 00:00:00 | 2450.00 |         |   --More--
  20 |     10 | ACCOUNTING | NEW YORK19-APR-87 00:00:00 | 3000.00 |         |   --More--
  20 |     20 | RESEARCH   | DALLAS| 19-APR-87 00:00:00 | 3000.00 |         |   --More--
  20 |     30 | SALES      | CHICAGO 19-APR-87 00:00:00 | 3000.00 |         |   --More--
  20 |     40 | OPERATIONS | BOSTON| 19-APR-87 00:00:00 | 3000.00 |         |   --More--
  10 |     10 | ACCOUNTING | NEW YORK17-NOV-81 00:00:00 | 5000.00 |         |   --More--
  10 |     20 | RESEARCH   | DALLAS| 17-NOV-81 00:00:00 | 5000.00 |         |   --More--
  10 |     30 | SALES      | CHICAGO 17-NOV-81 00:00:00 | 5000.00 |         |   --More--
  10 |     40 | OPERATIONS | BOSTON| 17-NOV-81 00:00:00 | 5000.00 |         |   --More--
  30 |     10 | ACCOUNTING | NEW YORK08-SEP-81 00:00:00 | 1500.00 |    0.00 |   --More--
  30 |     20 | RESEARCH   | DALLAS| 08-SEP-81 00:00:00 | 1500.00 |    0.00 |   --More--
  30 |     30 | SALES      | CHICAGO 08-SEP-81 00:00:00 | 1500.00 |    0.00 |   --More--
  30 |     40 | OPERATIONS | BOSTON| 08-SEP-81 00:00:00 | 1500.00 |    0.00 |   --More--
  20 |     10 | ACCOUNTING | NEW YORK23-MAY-87 00:00:00 | 1100.00 |         |   --More--
  20 |     20 | RESEARCH   | DALLAS| 23-MAY-87 00:00:00 | 1100.00 |         |   --More--
  20 |     30 | SALES      | CHICAGO 23-MAY-87 00:00:00 | 1100.00 |         |   --More--
  20 |     40 | OPERATIONS | BOSTON| 23-MAY-87 00:00:00 | 1100.00 |         |   --More--
  30 |     10 | ACCOUNTING | NEW YORK03-DEC-81 00:00:00 |  950.00 |         |   --More--
  30 |     20 | RESEARCH   | DALLAS| 03-DEC-81 00:00:00 |  950.00 |         |   --More--
  30 |     30 | SALES      | CHICAGO 03-DEC-81 00:00:00 |  950.00 |         |   --More--
  30 |     40 | OPERATIONS | BOSTON| 03-DEC-81 00:00:00 |  950.00 |         |   --More--
  20 |     10 | ACCOUNTING | NEW YORK03-DEC-81 00:00:00 | 3000.00 |         |   --More--
  20 |     20 | RESEARCH   | DALLAS| 03-DEC-81 00:00:00 | 3000.00 |         |   --More--
  20 |     30 | SALES      | CHICAGO 03-DEC-81 00:00:00 | 3000.00 |         |   --More--
  20 |     40 | OPERATIONS | BOSTON| 03-DEC-81 00:00:00 | 3000.00 |         |   --More--
  10 |     10 | ACCOUNTING | NEW YORK23-JAN-82 00:00:00 | 1300.00 |         |   --More--
  10 |     20 | RESEARCH   | DALLAS| 23-JAN-82 00:00:00 | 1300.00 |         |   --More--
  10 |     30 | SALES      | CHICAGO 23-JAN-82 00:00:00 | 1300.00 |         |   --More--
  10 |     40 | OPERATIONS | BOSTON| 23-JAN-82 00:00:00 | 1300.00 |         |   --More--
(56 rows)

4消除查询结果中的笛卡尔积
Oracle

SQL>  select * 
  2  from emp,dept
  3  where emp.deptno=dept.deptno;

     EMPNO ENAME      JOB        MGR HIREDATE        SAL      COMM       DEPTNO     DEPTNO DNAME         LOC
---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- --------------- ----------
      7782 CLARK      MANAGER           7839 09-JUN-81           2450               10      10 ACCOUNTING      NEW YORK
      7839 KING       PRESIDENT         17-NOV-81           5000               10      10 ACCOUNTING      NEW YORK
      7934 MILLER     CLERK           7782 23-JAN-82           1300               10      10 ACCOUNTING      NEW YORK
      7566 JONES      MANAGER           7839 02-APR-81           2975               20      20 RESEARCH         DALLAS
      7902 FORD       ANALYST           7566 03-DEC-81           3000               20      20 RESEARCH         DALLAS
      7876 ADAMS      CLERK           7788 23-MAY-87           1100               20      20 RESEARCH         DALLAS
      7369 SMITH      CLERK           7902 17-DEC-80        800               20      20 RESEARCH         DALLAS
      7788 SCOTT      ANALYST           7566 19-APR-87           3000               20      20 RESEARCH         DALLAS
      7521 WARD       SALESMAN           7698 22-FEB-81           1250       500           30      30 SALES         CHICAGO
      7844 TURNER     SALESMAN           7698 08-SEP-81           1500         0           30      30 SALES         CHICAGO
      7499 ALLEN      SALESMAN           7698 20-FEB-81           1600       300           30      30 SALES         CHICAGO
      7900 JAMES      CLERK           7698 03-DEC-81        950               30      30 SALES         CHICAGO
      7698 BLAKE      MANAGER           7839 01-MAY-81           2850               30      30 SALES         CHICAGO
      7654 MARTIN     SALESMAN           7698 28-SEP-81           1250      1400           30      30 SALES         CHICAGO

14 rows selected.

PPAS

scott=# select * 
scott-# from emp,dept
scott-# where emp.deptno=dept.deptno;
 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno | deptno |   dname    |   loc    
-------+--------+-----------+------+--------------------+---------+---------+--------+--------+------------+----------
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30 |     30 | SALES      | CHICAGO
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10 |     10 | ACCOUNTING | NEW YORK
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10 |     10 | ACCOUNTING | NEW YORK
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30 |     30 | SALES      | CHICAGO
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10 |     10 | ACCOUNTING | NEW YORK
(14 rows)

5查询每个雇员的编号、姓名、职位、基本工资、部门名称、部门位置信息
Oracle

SQL> select emp.empno,emp.ename,emp.job,emp.sal,dept.dname,dept.loc
  2  from emp,dept
  3  where emp.deptno=dept.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

14 rows selected.

PPAS

scott=# select emp.empno,emp.ename,emp.job,emp.sal,dept.dname,dept.loc
scott-# from emp,dept
scott-# where emp.deptno=dept.deptno;
 empno | ename  |    job    |   sal   |   dname    |   loc    
-------+--------+-----------+---------+------------+----------
  7369 | SMITH  | CLERK     |  800.00 | RESEARCH   | DALLAS
  7499 | ALLEN  | SALESMAN  | 1600.00 | SALES      | CHICAGO
  7521 | WARD   | SALESMAN  | 1250.00 | SALES      | CHICAGO
  7566 | JONES  | MANAGER   | 2975.00 | RESEARCH   | DALLAS
  7654 | MARTIN | SALESMAN  | 1250.00 | SALES      | CHICAGO
  7698 | BLAKE  | MANAGER   | 2850.00 | SALES      | CHICAGO
  7782 | CLARK  | MANAGER   | 2450.00 | ACCOUNTING | NEW YORK
  7788 | SCOTT  | ANALYST   | 3000.00 | RESEARCH   | DALLAS
  7839 | KING   | PRESIDENT | 5000.00 | ACCOUNTING | NEW YORK
  7844 | TURNER | SALESMAN  | 1500.00 | SALES      | CHICAGO
  7876 | ADAMS  | CLERK     | 1100.00 | RESEARCH   | DALLAS
  7900 | JAMES  | CLERK     |  950.00 | SALES      | CHICAGO
  7902 | FORD   | ANALYST   | 3000.00 | RESEARCH   | DALLAS
  7934 | MILLER | CLERK     | 1300.00 | ACCOUNTING | NEW YORK
(14 rows)

6通过别名查询雇员的编号、姓名、职位、基本工资、部门名称、部门位置
Oracle

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

14 rows selected.

PPAS

scott=# select e.empno,e.ename,e.job,e.sal,d.dname,d.loc
scott-# from emp e,dept d
scott-# where e.deptno=d.deptno;
 empno | ename  |    job    |   sal   |   dname    |   loc    
-------+--------+-----------+---------+------------+----------
  7369 | SMITH  | CLERK     |  800.00 | RESEARCH   | DALLAS
  7499 | ALLEN  | SALESMAN  | 1600.00 | SALES      | CHICAGO
  7521 | WARD   | SALESMAN  | 1250.00 | SALES      | CHICAGO
  7566 | JONES  | MANAGER   | 2975.00 | RESEARCH   | DALLAS
  7654 | MARTIN | SALESMAN  | 1250.00 | SALES      | CHICAGO
  7698 | BLAKE  | MANAGER   | 2850.00 | SALES      | CHICAGO
  7782 | CLARK  | MANAGER   | 2450.00 | ACCOUNTING | NEW YORK
  7788 | SCOTT  | ANALYST   | 3000.00 | RESEARCH   | DALLAS
  7839 | KING   | PRESIDENT | 5000.00 | ACCOUNTING | NEW YORK
  7844 | TURNER | SALESMAN  | 1500.00 | SALES      | CHICAGO
  7876 | ADAMS  | CLERK     | 1100.00 | RESEARCH   | DALLAS
  7900 | JAMES  | CLERK     |  950.00 | SALES      | CHICAGO
  7902 | FORD   | ANALYST   | 3000.00 | RESEARCH   | DALLAS
  7934 | MILLER | CLERK     | 1300.00 | ACCOUNTING | NEW YORK
(14 rows)

7查询出每个雇员的编号、姓名、雇用日期、基本工资、工资等级
Oracle

SQL> select e.empno,e.ename,e.hiredate,e.sal,s.grade
  2  from emp e,salgrade s
  3  where e.sal between s.losal and s.hisal;

     EMPNO ENAME      HIREDATE          SAL       GRADE
---------- ---------- ------------ ---------- ----------
      7369 SMITH      17-DEC-80       800           1
      7900 JAMES      03-DEC-81       950           1
      7876 ADAMS      23-MAY-87      1100           1
      7521 WARD       22-FEB-81      1250           2
      7654 MARTIN     28-SEP-81      1250           2
      7934 MILLER     23-JAN-82      1300           2
      7844 TURNER     08-SEP-81      1500           3
      7499 ALLEN      20-FEB-81      1600           3
      7782 CLARK      09-JUN-81      2450           4
      7698 BLAKE      01-MAY-81      2850           4
      7566 JONES      02-APR-81      2975           4
      7788 SCOTT      19-APR-87      3000           4
      7902 FORD       03-DEC-81      3000           4
      7839 KING       17-NOV-81      5000           5

14 rows selected.

PPAS

scott=# select e.empno,e.ename,e.hiredate,e.sal,s.grade
scott-# from emp e,salgrade s
scott-# where e.sal between s.losal and s.hisal;
 empno | ename  |      hiredate      |   sal   | grade 
-------+--------+--------------------+---------+-------
  7369 | SMITH  | 17-DEC-80 00:00:00 |  800.00 |     1
  7876 | ADAMS  | 23-MAY-87 00:00:00 | 1100.00 |     1
  7900 | JAMES  | 03-DEC-81 00:00:00 |  950.00 |     1
  7521 | WARD   | 22-FEB-81 00:00:00 | 1250.00 |     2
  7654 | MARTIN | 28-SEP-81 00:00:00 | 1250.00 |     2
  7934 | MILLER | 23-JAN-82 00:00:00 | 1300.00 |     2
  7499 | ALLEN  | 20-FEB-81 00:00:00 | 1600.00 |     3
  7844 | TURNER | 08-SEP-81 00:00:00 | 1500.00 |     3
  7566 | JONES  | 02-APR-81 00:00:00 | 2975.00 |     4
  7698 | BLAKE  | 01-MAY-81 00:00:00 | 2850.00 |     4
  7782 | CLARK  | 09-JUN-81 00:00:00 | 2450.00 |     4
  7788 | SCOTT  | 19-APR-87 00:00:00 | 3000.00 |     4
  7902 | FORD   | 03-DEC-81 00:00:00 | 3000.00 |     4
  7839 | KING   | 17-NOV-81 00:00:00 | 5000.00 |     5
(14 rows)

8使用DECODE()函数完成查询出每个雇员的编号、姓名、雇用日期、基本工资、工资等级
Oracle

SQL> select e.empno,e.ename,e.hiredate,e.sal,
  2  DECODE(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资')grade
  3  from emp e,salgrade s
  4  where e.sal between s.losal and s.hisal;

     EMPNO ENAME      HIREDATE          SAL GRADE
---------- ---------- ------------ ---------- --------------------
      7369 SMITH      17-DEC-80       800 E等工资
      7900 JAMES      03-DEC-81       950 E等工资
      7876 ADAMS      23-MAY-87      1100 E等工资
      7521 WARD       22-FEB-81      1250 D等工资
      7654 MARTIN     28-SEP-81      1250 D等工资
      7934 MILLER     23-JAN-82      1300 D等工资
      7844 TURNER     08-SEP-81      1500 C等工资
      7499 ALLEN      20-FEB-81      1600 C等工资
      7782 CLARK      09-JUN-81      2450 B等工资
      7698 BLAKE      01-MAY-81      2850 B等工资
      7566 JONES      02-APR-81      2975 B等工资
      7788 SCOTT      19-APR-87      3000 B等工资
      7902 FORD       03-DEC-81      3000 B等工资
      7839 KING       17-NOV-81      5000 A等工资

14 rows selected.

PPAS

scott=# select e.empno,e.ename,e.hiredate,e.sal,
scott-# DECODE(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资')grade
scott-# from emp e,salgrade s
scott-# where e.sal between s.losal and s.hisal;
 empno | ename  |      hiredate      |   sal   |  grade  
-------+--------+--------------------+---------+---------
  7369 | SMITH  | 17-DEC-80 00:00:00 |  800.00 | E等工资
  7876 | ADAMS  | 23-MAY-87 00:00:00 | 1100.00 | E等工资
  7900 | JAMES  | 03-DEC-81 00:00:00 |  950.00 | E等工资
  7521 | WARD   | 22-FEB-81 00:00:00 | 1250.00 | D等工资
  7654 | MARTIN | 28-SEP-81 00:00:00 | 1250.00 | D等工资
  7934 | MILLER | 23-JAN-82 00:00:00 | 1300.00 | D等工资
  7499 | ALLEN  | 20-FEB-81 00:00:00 | 1600.00 | C等工资
  7844 | TURNER | 08-SEP-81 00:00:00 | 1500.00 | C等工资
  7566 | JONES  | 02-APR-81 00:00:00 | 2975.00 | B等工资
  7698 | BLAKE  | 01-MAY-81 00:00:00 | 2850.00 | B等工资
  7782 | CLARK  | 09-JUN-81 00:00:00 | 2450.00 | B等工资
  7788 | SCOTT  | 19-APR-87 00:00:00 | 3000.00 | B等工资
  7902 | FORD   | 03-DEC-81 00:00:00 | 3000.00 | B等工资
  7839 | KING   | 17-NOV-81 00:00:00 | 5000.00 | A等工资
(14 rows)

9查询每个雇员的姓名、职位、基本工资、部门名称、工资所在公司的工资等级
Oracle

SQL> select e.ename,e.job,e.sal,d.dname,s.grade,
  2  DECODE(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资')grade
  3  from emp e,dept d,salgrade s
  4  where e.deptno=d.deptno and e.sal between s.losal and s.hisal;

ENAME       JOB             SAL DNAME              GRADE GRADE
---------- ---------- ---------- --------------- ---------- --------------------
KING       PRESIDENT        5000 ACCOUNTING          5 A等工资
FORD       ANALYST        3000 RESEARCH          4 B等工资
SCOTT       ANALYST        3000 RESEARCH          4 B等工资
JONES       MANAGER        2975 RESEARCH          4 B等工资
BLAKE       MANAGER        2850 SALES              4 B等工资
CLARK       MANAGER        2450 ACCOUNTING          4 B等工资
ALLEN       SALESMAN        1600 SALES              3 C等工资
TURNER       SALESMAN        1500 SALES              3 C等工资
MILLER       CLERK        1300 ACCOUNTING          2 D等工资
WARD       SALESMAN        1250 SALES              2 D等工资
MARTIN       SALESMAN        1250 SALES              2 D等工资
ADAMS       CLERK        1100 RESEARCH          1 E等工资
JAMES       CLERK         950 SALES              1 E等工资
SMITH       CLERK         800 RESEARCH          1 E等工资

14 rows selected.

PPAS

scott=# select e.ename,e.job,e.sal,d.dname,s.grade,
scott-# DECODE(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资')grade
scott-# from emp e,dept d,salgrade s
scott-# where e.deptno=d.deptno and e.sal between s.losal and s.hisal;
 ename  |    job    |   sal   |   dname    | grade |  grade  
--------+-----------+---------+------------+-------+---------
 SMITH  | CLERK     |  800.00 | RESEARCH   |     1 | E等工资
 ADAMS  | CLERK     | 1100.00 | RESEARCH   |     1 | E等工资
 JAMES  | CLERK     |  950.00 | SALES      |     1 | E等工资
 WARD   | SALESMAN  | 1250.00 | SALES      |     2 | D等工资
 MARTIN | SALESMAN  | 1250.00 | SALES      |     2 | D等工资
 MILLER | CLERK     | 1300.00 | ACCOUNTING |     2 | D等工资
 ALLEN  | SALESMAN  | 1600.00 | SALES      |     3 | C等工资
 TURNER | SALESMAN  | 1500.00 | SALES      |     3 | C等工资
 JONES  | MANAGER   | 2975.00 | RESEARCH   |     4 | B等工资
 BLAKE  | MANAGER   | 2850.00 | SALES      |     4 | B等工资
 CLARK  | MANAGER   | 2450.00 | ACCOUNTING |     4 | B等工资
 SCOTT  | ANALYST   | 3000.00 | RESEARCH   |     4 | B等工资
 FORD   | ANALYST   | 3000.00 | RESEARCH   |     4 | B等工资
 KING   | PRESIDENT | 5000.00 | ACCOUNTING |     5 | A等工资
(14 rows)

表的连接操作
10向emp表中 添加一条新的记录
Oracle

SQL> insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
  2  values (8888,'SongYuejie','CLERK','7369',SYSDATE,800,100,null);

1 row created.

SQL> select * from emp;

     EMPNO ENAME      JOB        MGR HIREDATE        SAL      COMM       DEPTNO
---------- ---------- ---------- ---------- ------------ ---------- ---------- ----------
      8888 SongYuejie CLERK           7369 22-JUN-16        800       100
      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           1500         0           30
      7876 ADAMS      CLERK           7788 23-MAY-87           1100               20
      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

15 rows selected.

PPAS

scott=# insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
scott-# values (8888,'SongYuejie','CLERK','7369',SYSDATE,800,100,null);
INSERT 0 1
scott=# select * from emp;
 empno |   ename    |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno 
-------+------------+-----------+------+--------------------+---------+---------+--------
  7369 | SMITH      | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20
  7499 | ALLEN      | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD       | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7566 | JONES      | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20
  7654 | MARTIN     | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7698 | BLAKE      | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30
  7782 | CLARK      | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10
  7788 | SCOTT      | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20
  7839 | KING       | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10
  7844 | TURNER     | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
  7876 | ADAMS      | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20
  7900 | JAMES      | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30
  7902 | FORD       | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20
  7934 | MILLER     | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10
  8888 | SongYuejie | CLERK     | 7369 | 22-JUN-16 11:09:17 |  800.00 |  100.00 |       
(15 rows)

11将emp和dept表联合查询,使用内连接(等值连接)
Oracle

SQL> select *
  2  from emp e,dept d
  3  where e.deptno=d.deptno;

     EMPNO ENAME      JOB        MGR HIREDATE        SAL      COMM       DEPTNO     DEPTNO DNAME         LOC
---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- --------------- ----------
      7839 KING       PRESIDENT         17-NOV-81           5000               10      10 ACCOUNTING      NEW YORK
      7934 MILLER     CLERK           7782 23-JAN-82           1300               10      10 ACCOUNTING      NEW YORK
      7782 CLARK      MANAGER           7839 09-JUN-81           2450               10      10 ACCOUNTING      NEW YORK
      7369 SMITH      CLERK           7902 17-DEC-80        800               20      20 RESEARCH         DALLAS
      7902 FORD       ANALYST           7566 03-DEC-81           3000               20      20 RESEARCH         DALLAS
      7876 ADAMS      CLERK           7788 23-MAY-87           1100               20      20 RESEARCH         DALLAS
      7788 SCOTT      ANALYST           7566 19-APR-87           3000               20      20 RESEARCH         DALLAS
      7566 JONES      MANAGER           7839 02-APR-81           2975               20      20 RESEARCH         DALLAS
      7900 JAMES      CLERK           7698 03-DEC-81        950               30      30 SALES         CHICAGO
      7499 ALLEN      SALESMAN           7698 20-FEB-81           1600       300           30      30 SALES         CHICAGO
      7698 BLAKE      MANAGER           7839 01-MAY-81           2850               30      30 SALES         CHICAGO
      7654 MARTIN     SALESMAN           7698 28-SEP-81           1250      1400           30      30 SALES         CHICAGO
      7844 TURNER     SALESMAN           7698 08-SEP-81           1500         0           30      30 SALES         CHICAGO
      7521 WARD       SALESMAN           7698 22-FEB-81           1250       500           30      30 SALES         CHICAGO

14 rows selected.

PPAS

scott=# select *
scott-# from emp e,dept d 
scott-# where e.deptno=d.deptno;
 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno | deptno |   dname    |   loc    
-------+--------+-----------+------+--------------------+---------+---------+--------+--------+------------+----------
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30 |     30 | SALES      | CHICAGO
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10 |     10 | ACCOUNTING | NEW YORK
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10 |     10 | ACCOUNTING | NEW YORK
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30 |     30 | SALES      | CHICAGO
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10 |     10 | ACCOUNTING | NEW YORK
(14 rows)

12使用左外连接,显示雇员8888的信息
Oracle

SQL> select *
  2  from emp e,dept d
  3  where e.deptno=d.deptno(+);

     EMPNO ENAME      JOB        MGR HIREDATE        SAL      COMM       DEPTNO     DEPTNO DNAME         LOC
---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- --------------- ----------
      7934 MILLER     CLERK           7782 23-JAN-82           1300               10      10 ACCOUNTING      NEW YORK
      7839 KING       PRESIDENT         17-NOV-81           5000               10      10 ACCOUNTING      NEW YORK
      7782 CLARK      MANAGER           7839 09-JUN-81           2450               10      10 ACCOUNTING      NEW YORK
      7902 FORD       ANALYST           7566 03-DEC-81           3000               20      20 RESEARCH         DALLAS
      7876 ADAMS      CLERK           7788 23-MAY-87           1100               20      20 RESEARCH         DALLAS
      7788 SCOTT      ANALYST           7566 19-APR-87           3000               20      20 RESEARCH         DALLAS
      7566 JONES      MANAGER           7839 02-APR-81           2975               20      20 RESEARCH         DALLAS
      7369 SMITH      CLERK           7902 17-DEC-80        800               20      20 RESEARCH         DALLAS
      7900 JAMES      CLERK           7698 03-DEC-81        950               30      30 SALES         CHICAGO
      7844 TURNER     SALESMAN           7698 08-SEP-81           1500         0           30      30 SALES         CHICAGO
      7698 BLAKE      MANAGER           7839 01-MAY-81           2850               30      30 SALES         CHICAGO
      7654 MARTIN     SALESMAN           7698 28-SEP-81           1250      1400           30      30 SALES         CHICAGO
      7521 WARD       SALESMAN           7698 22-FEB-81           1250       500           30      30 SALES         CHICAGO
      7499 ALLEN      SALESMAN           7698 20-FEB-81           1600       300           30      30 SALES         CHICAGO
      8888 SongYuejie CLERK           7369 22-JUN-16        800       100

15 rows selected.

PPAS

scott=# select *
scott-# from emp e,dept d 
scott-# where e.deptno=d.deptno(+);
 empno |   ename    |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno | deptno |   dname    |   loc    
-------+------------+-----------+------+--------------------+---------+---------+--------+--------+------------+----------
  7369 | SMITH      | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7499 | ALLEN      | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO
  7521 | WARD       | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO
  7566 | JONES      | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7654 | MARTIN     | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO
  7698 | BLAKE      | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30 |     30 | SALES      | CHICAGO
  7782 | CLARK      | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10 |     10 | ACCOUNTING | NEW YORK
  7788 | SCOTT      | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7839 | KING       | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10 |     10 | ACCOUNTING | NEW YORK
  7844 | TURNER     | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO
  7876 | ADAMS      | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7900 | JAMES      | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30 |     30 | SALES      | CHICAGO
  7902 | FORD       | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20 |     20 | RESEARCH   | DALLAS
  7934 | MILLER     | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10 |     10 | ACCOUNTING | NEW YORK
  8888 | SongYuejie | CLERK     | 7369 | 22-JUN-16 11:09:17 |  800.00 |  100.00 |        |        |            | 
(15 rows)

本连载博客主要探讨Oracle与PPAS(PostgreSQL)数据库的差异,以帮助更多读者了解如何实现数据库迁移!”

上一篇:《卸甲笔记》-单行函数对比之二


下一篇:JMM模型 java内存模型