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

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)数据库的差异，以帮助更多读者了解如何实现数据库迁移！”

时间： 2024-08-22 14:56:03

《卸甲笔记》-多表查询之一的相关文章

《卸甲笔记》-限定查询与排序显示

1查看emp表中的数据量 Oracle SQL> select count(*) from emp; COUNT(*) ---------- 14 PPAS scott=# select count(*) from emp; count ------- 14 (1 row) 2 统计出基本工资高于1500元的全部雇员信息 Oracle SQL> select * from emp where sal>1500; EMPNO ENAME JOB MGR HIREDATE ---------

《卸甲笔记》-分组统计查询对比之二

13在分组查询的SELECT子句中出现其他字段(ename) Oracle SQL> select deptno,ename,COUNT(empno) 2 from emp 3 GROUP BY deptno; select deptno,ename,COUNT(empno) * ERROR at line 1: ORA-00979: not a GROUP BY expression PPAS scott=# select deptno,ename,COUNT(empno) scott-# f

《卸甲笔记》-PostgreSQL和Oracle的SQL差异分析之三：rownum和聚合函数

PostgreSQL是世界上功能最强大的开源数据库,在国内得到了越来越多机构和开发者的青睐和应用.随着PostgreSQL的应用越来越广泛,Oracle向PostgreSQL数据库的数据迁移需求也越来越多.数据库之间数据迁移的时候,首先是迁移数据,然后就是SQL.存储过程.序列等程序中不同的数据库中数据的使用方式的转换.下面根据自己的理解和测试,写了一些SQL以及数据库对象转换方面的文章,不足之处,尚请多多指教. rownum rownum是Oracle内部的一个伪列,用来表示数据在结果集中的行

《卸甲笔记》-基础语法对比

以Oracle中sottt用户下的数据为例,PPAS 中scott用户下面的数据由Oracle迁移而来 1 查询emp表中的数据 Oracle [root@test03 ~]# su - oracle [oracle@test03 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 16 22:05:19 2016 Copyright (c) 1982, 2013, Oracle. All righ

《卸甲笔记》-单行函数对比之一

1验证UPPER().LOWER()函数 Oracle SQL> select UPPER('SongYuejie'),LOWER('VASTDATA') from dual; UPPER('SONGYUEJIE') LOWER('VASTDATA' -------------------- ---------------- SONGYUEJIE vastdata PPAS scott=# select UPPER('SongYuejie'),LOWER('VASTDATA') from dua

《卸甲笔记》-PostgreSQL和Oracle的SQL差异分析之四：特殊字符和符号

PostgreSQL是世界上功能最强大的开源数据库,在国内得到了越来越多机构和开发者的青睐和应用.随着PostgreSQL的应用越来越广泛,Oracle向PostgreSQL数据库的数据迁移需求也越来越多.数据库之间数据迁移的时候,首先是迁移数据,然后就是SQL.存储过程.序列等程序中不同的数据库中数据的使用方式的转换.下面根据自己的理解和测试,写了一些SQL以及数据库对象转换方面的文章,不足之处,尚请多多指教. 空字符串( '' ) Oracle中,空字符串( '' )很多时候是和null同样

《卸甲笔记》-PostgreSQL和Oracle的数据类型的对比系列一：字符类型

PostgreSQL是世界上功能最强大的开源数据库,在国内得到了越来越多机构和开发者的青睐和应用.随着PostgreSQL的应用越来越广泛,Oracle向PostgreSQL数据库的数据迁移需求也越来越多.数据库之间数据迁移的时候,首先遇到的,并且也是最重要的,就是数据类型之间的转换.下面根据自己的理解和测试,写了一些数据类型之间的差异以及迁移时的注意事项的文章,不足之处,尚请多多指教. 字符类型 大家知道,Oracle有四种字符类型,分别是char,varchar2,nchar,nvarcha

《卸甲笔记》-PostgreSQL和Oracle的SQL差异分析之二：序列的使用

PostgreSQL是世界上功能最强大的开源数据库,在国内得到了越来越多机构和开发者的青睐和应用.随着PostgreSQL的应用越来越广泛,Oracle向PostgreSQL数据库的数据迁移需求也越来越多.数据库之间数据迁移的时候,首先是迁移数据,然后就是SQL.存储过程.序列等程序中不同的数据库中数据的使用方式的转换.下面根据自己的理解和测试,写了一些SQL以及数据库对象转换方面的文章,不足之处,尚请多多指教. 序列 序列是一种数据库对象.可以供多个用户同时使用,得到不重复的.递增的数字值.O

《卸甲笔记》-单行函数对比之三

41查询出所有在每年2月份雇佣的雇员信息 Oracle SQL> select * from emp where TO_CHAR(hiredate,'MM')='02'; EMPNO ENAME JOB MGR HIREDATE ---------- -------------------- ------------------ ---------- ------------ SAL COMM DEPTNO ---------- ---------- ---------- 7499 ALLEN