以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 rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn scott/scott
Connected.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
SAL COMM DEPTNO
---------- ---------- ----------
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
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
SAL COMM DEPTNO
---------- ---------- ----------
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
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
SAL COMM DEPTNO
---------- ---------- ----------
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
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
SAL COMM DEPTNO
---------- ---------- ----------
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
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
SAL COMM DEPTNO
---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81
3000 20
7934 MILLER CLERK 7782 23-JAN-82
1300 10
14 rows selected.
PPAS
[root@test06 ~]# su – enterprisedb
-bash-4.1$ psql -U scott -d scott
Password for user scott:
psql.bin (9.3.1.3)
Type "help" for help.
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
(14 rows)
2查询出公司的雇员雇佣情况，所以希望通过数据库可以查找到每个雇员的编号，姓名，基本工资3个信息进行浏览
Oracle
SQL> select empno,ename,job from emp;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
EMPNO ENAME JOB
---------- ---------- ---------
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
14 rows selected.
PPAS
scott=# select empno,ename,job from emp;
empno | ename | job
-------+--------+-----------
7369 | SMITH | CLERK
7499 | ALLEN | SALESMAN
7521 | WARD | SALESMAN
7566 | JONES | MANAGER
7654 | MARTIN | SALESMAN
7698 | BLAKE | MANAGER
7782 | CLARK | MANAGER
7788 | SCOTT | ANALYST
7839 | KING | PRESIDENT
7844 | TURNER | SALESMAN
7876 | ADAMS | CLERK
7900 | JAMES | CLERK
7902 | FORD | ANALYST
7934 | MILLER | CLERK
(14 rows)
3查询公司中国所有雇员的职位信息
Oracle
SQL> select job from emp;
JOB
---------
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
JOB
---------
CLERK
ANALYST
CLERK
14 rows selected.
PPAS
scott=# select job from emp;
job
-----------
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
(14 rows)
4 显示的职位包含 了太多的重复内容，使用distinct关键字去掉全部的重复内容
Oracle
SQL> select distinct job from emp;
JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
PPAS
scott=# select distinct job from emp;
job
-----------
SALESMAN
CLERK
MANAGER
PRESIDENT
ANALYST
(5 rows)
5 查询雇员编号、职位
Oracle
SQL> select distinct empno,job from emp;
EMPNO JOB
---------- ---------
7369 CLERK
7499 SALESMAN
7521 SALESMAN
7566 MANAGER
7654 SALESMAN
7698 MANAGER
7782 MANAGER
7788 ANALYST
7839 PRESIDENT
7844 SALESMAN
7876 CLERK
EMPNO JOB
---------- ---------
7900 CLERK
7902 ANALYST
7934 CLERK
14 rows selected.
PPAS
scott=# select distinct empno,job from emp;
empno | job
-------+-----------
7844 | SALESMAN
7934 | CLERK
7839 | PRESIDENT
7876 | CLERK
7788 | ANALYST
7698 | MANAGER
7521 | SALESMAN
7369 | CLERK
7900 | CLERK
7499 | SALESMAN
7782 | MANAGER
7654 | SALESMAN
7902 | ANALYST
7566 | MANAGER
(14 rows)
6通过数据库查询出所有雇员的编号，雇员姓名和年基本工资，日常基本工资，以作为年终奖金的发放标准
Oracle
SQL> select empno,ename,sal*12,sal/30 from emp;
EMPNO ENAME SAL*12 SAL/30
---------- ---------- ---------- ----------
7369 SMITH 9600 26.6666667
7499 ALLEN 19200 53.3333333
7521 WARD 15000 41.6666667
7566 JONES 35700 99.1666667
7654 MARTIN 15000 41.6666667
7698 BLAKE 34200 95
7782 CLARK 29400 81.6666667
7788 SCOTT 36000 100
7839 KING 60000 166.666667
7844 TURNER 18000 50
7876 ADAMS 13200 36.6666667
EMPNO ENAME SAL*12 SAL/30
---------- ---------- ---------- ----------
7900 JAMES 11400 31.6666667
7902 FORD 36000 100
7934 MILLER 15600 43.3333333
14 rows selected.
PPAS
scott=# select empno,ename,sal*12,sal/30 from emp;
empno | ename | ?column? | ?column?
-------+--------+----------+----------------------
7369 | SMITH | 9600.00 | 26.6666666666666667
7499 | ALLEN | 19200.00 | 53.3333333333333333
7521 | WARD | 15000.00 | 41.6666666666666667
7566 | JONES | 35700.00 | 99.1666666666666667
7654 | MARTIN | 15000.00 | 41.6666666666666667
7698 | BLAKE | 34200.00 | 95.0000000000000000
7782 | CLARK | 29400.00 | 81.6666666666666667
7788 | SCOTT | 36000.00 | 100.0000000000000000
7839 | KING | 60000.00 | 166.6666666666666667
7844 | TURNER | 18000.00 | 50.0000000000000000
7876 | ADAMS | 13200.00 | 36.6666666666666667
7900 | JAMES | 11400.00 | 31.6666666666666667
7902 | FORD | 36000.00 | 100.0000000000000000
7934 | MILLER | 15600.00 | 43.3333333333333333
(14 rows)
7 公司每个雇员在年底的时候可以领取5000元的年终奖，查询雇员的编号、姓名和增长后的年基本工资（不包括佣金）
Oracle
SQL> select empno,ename,sal*12+5000 from emp;
EMPNO ENAME SAL*12+5000
---------- ---------- -----------
7369 SMITH 14600
7499 ALLEN 24200
7521 WARD 20000
7566 JONES 40700
7654 MARTIN 20000
7698 BLAKE 39200
7782 CLARK 34400
7788 SCOTT 41000
7839 KING 65000
7844 TURNER 23000
7876 ADAMS 18200
EMPNO ENAME SAL*12+5000
---------- ---------- -----------
7900 JAMES 16400
7902 FORD 41000
7934 MILLER 20600
14 rows selected.
PPAS
scott=# select empno,ename,sal*12+5000 from emp;
empno | ename | ?column?
-------+--------+----------
7369 | SMITH | 14600.00
7499 | ALLEN | 24200.00
7521 | WARD | 20000.00
7566 | JONES | 40700.00
7654 | MARTIN | 20000.00
7698 | BLAKE | 39200.00
7782 | CLARK | 34400.00
7788 | SCOTT | 41000.00
7839 | KING | 65000.00
7844 | TURNER | 23000.00
7876 | ADAMS | 18200.00
7900 | JAMES | 16400.00
7902 | FORD | 41000.00
7934 | MILLER | 20600.00
(14 rows)
8公司每个月为雇员增加200元的补助金，此时，要求可以查询出每个雇员的编号，姓名，基本年工资
Oracle
SQL> select empno,ename,(sal+200)*12+5000 from emp;
EMPNO ENAME (SAL+200)*12+5000
---------- ---------- -----------------
7369 SMITH 17000
7499 ALLEN 26600
7521 WARD 22400
7566 JONES 43100
7654 MARTIN 22400
7698 BLAKE 41600
7782 CLARK 36800
7788 SCOTT 43400
7839 KING 67400
7844 TURNER 25400
7876 ADAMS 20600
EMPNO ENAME (SAL+200)*12+5000
---------- ---------- -----------------
7900 JAMES 18800
7902 FORD 43400
7934 MILLER 23000
14 rows selected.
PPAS
scott=# select empno,ename,(sal+200)*12+5000 from emp;
empno | ename | ?column?
-------+--------+----------
7369 | SMITH | 17000.00
7499 | ALLEN | 26600.00
7521 | WARD | 22400.00
7566 | JONES | 43100.00
7654 | MARTIN | 22400.00
7698 | BLAKE | 41600.00
7782 | CLARK | 36800.00
7788 | SCOTT | 43400.00
7839 | KING | 67400.00
7844 | TURNER | 25400.00
7876 | ADAMS | 20600.00
7900 | JAMES | 18800.00
7902 | FORD | 43400.00
7934 | MILLER | 23000.00
(14 rows)
9为查询结果设置别名
Oralce
SQL> select empno 雇员编号,ename 雇员姓名,(sal+200)*12+5000 as 年薪 from emp;
雇员编号雇员姓名 年薪
---------- ---------- ----------
7369 SMITH 17000
7499 ALLEN 26600
7521 WARD 22400
7566 JONES 43100
7654 MARTIN 22400
7698 BLAKE 41600
7782 CLARK 36800
7788 SCOTT 43400
7839 KING 67400
7844 TURNER 25400
7876 ADAMS 20600
雇员编号雇员姓名 年薪
---------- ---------- ----------
7900 JAMES 18800
7902 FORD 43400
7934 MILLER 23000
14 rows selected.
PPAS
scott=# select empno 雇员编号,ename 雇员姓名,(sal+200)*12+5000 as 年薪 from emp;
雇员编号 | 雇员姓名 | 年薪
----------+----------+----------
7369 | SMITH | 17000.00
7499 | ALLEN | 26600.00
7521 | WARD | 22400.00
7566 | JONES | 43100.00
7654 | MARTIN | 22400.00
7698 | BLAKE | 41600.00
7782 | CLARK | 36800.00
7788 | SCOTT | 43400.00
7839 | KING | 67400.00
7844 | TURNER | 25400.00
7876 | ADAMS | 20600.00
7900 | JAMES | 18800.00
7902 | FORD | 43400.00
7934 | MILLER | 23000.00
(14 rows)
10在select子句中使用常量，为以上的查询增加一个货币的描述
Oracle
SQL> select empno as 雇员编号,ename as 雇员姓名,(sal+200)*12+5000 as 年薪,'￥' as 货币 from emp;
雇员编号 雇员姓名 年薪 货币
---------- -------------------- ---------- ------
7369 SMITH 17000 ￥
7499 ALLEN 26600 ￥
7521 WARD 22400 ￥
7566 JONES 43100 ￥
7654 MARTIN 22400 ￥
7698 BLAKE 41600 ￥
7782 CLARK 36800 ￥
7788 SCOTT 43400 ￥
7839 KING 67400 ￥
7844 TURNER 25400 ￥
7876 ADAMS 20600 ￥
雇员编号 雇员姓名 年薪 货币
---------- -------------------- ---------- ------
7900 JAMES 18800 ￥
7902 FORD 43400 ￥
7934 MILLER 23000 ￥
14 rows selected.
PPAS
scott=# select empno as 雇员编号,ename as 雇员姓名,(sal+200)*12+5000 as 年薪,'￥' as 货币 from emp;
雇员编号 | 雇员姓名 | 年薪 | 货币
----------+----------+----------+------
7369 | SMITH | 17000.00 | ￥
7499 | ALLEN | 26600.00 | ￥
7521 | WARD | 22400.00 | ￥
7566 | JONES | 43100.00 | ￥
7654 | MARTIN | 22400.00 | ￥
7698 | BLAKE | 41600.00 | ￥
7782 | CLARK | 36800.00 | ￥
7788 | SCOTT | 43400.00 | ￥
7839 | KING | 67400.00 | ￥
7844 | TURNER | 25400.00 | ￥
7876 | ADAMS | 20600.00 | ￥
7900 | JAMES | 18800.00 | ￥
7902 | FORD | 43400.00 | ￥
7934 | MILLER | 23000.00 | ￥
(14 rows)
11使用“||”进行连接显示
Oracle
SQL> select '编号是:'||empno||'的雇员姓名是:'||ename||',基本工资是:'||sal 雇员信息 from emp;
雇员信息
--------------------------------------------------------------------------------
编号是:7369的雇员姓名是:SMITH,基本工资是:800
编号是:7499的雇员姓名是:ALLEN,基本工资是:1600
编号是:7521的雇员姓名是:WARD,基本工资是:1250
编号是:7566的雇员姓名是:JONES,基本工资是:2975
编号是:7654的雇员姓名是:MARTIN,基本工资是:1250
编号是:7698的雇员姓名是:BLAKE,基本工资是:2850
编号是:7782的雇员姓名是:CLARK,基本工资是:2450
编号是:7788的雇员姓名是:SCOTT,基本工资是:3000
编号是:7839的雇员姓名是:KING,基本工资是:5000
编号是:7844的雇员姓名是:TURNER,基本工资是:1500
编号是:7876的雇员姓名是:ADAMS,基本工资是:1100
雇员信息
--------------------------------------------------------------------------------
编号是:7900的雇员姓名是:JAMES,基本工资是:950
编号是:7902的雇员姓名是:FORD,基本工资是:3000
编号是:7934的雇员姓名是:MILLER,基本工资是:1300
14 rows selected.
PPAS
scott=# select '编号是:'||empno||'的雇员姓名是:'||ename||',基本工资是:'||sal 雇员信息 from emp;
雇员信息
---------------------------------------------------
编号是:7369的雇员姓名是:SMITH,基本工资是:800.00
编号是:7499的雇员姓名是:ALLEN,基本工资是:1600.00
编号是:7521的雇员姓名是:WARD,基本工资是:1250.00
编号是:7566的雇员姓名是:JONES,基本工资是:2975.00
编号是:7654的雇员姓名是:MARTIN,基本工资是:1250.00
编号是:7698的雇员姓名是:BLAKE,基本工资是:2850.00
编号是:7782的雇员姓名是:CLARK,基本工资是:2450.00
编号是:7788的雇员姓名是:SCOTT,基本工资是:3000.00
编号是:7839的雇员姓名是:KING,基本工资是:5000.00
编号是:7844的雇员姓名是:TURNER,基本工资是:1500.00
编号是:7876的雇员姓名是:ADAMS,基本工资是:1100.00
编号是:7900的雇员姓名是:JAMES,基本工资是:950.00
编号是:7902的雇员姓名是:FORD,基本工资是:3000.00
编号是:7934的雇员姓名是:MILLER,基本工资是:1300.00
(14 rows)
本连载博客主要探讨Oracle与PPAS(PostgreSQL)数据库的差异，以帮助更多读者了解如何实现数据库迁移！