1验证UPPER()、LOWER()函数
Oracle
SQL> select UPPER('SongYuejie'),LOWER('VASTDATA') from dual;
UPPER('SONGYUEJIE') LOWER('VASTDATA'
-------------------- ----------------
SONGYUEJIE vastdataPPAS
scott=# select UPPER('SongYuejie'),LOWER('VASTDATA') from dual;
upper | lower
------------+----------
SONGYUEJIE | vastdata
(1 row)2查询出雇员姓名是SMITH的完整信息，但是由于失误没有考虑到数据的大小写问题，此时可以使用UPPER()函数将全部内容变为大写
Oracle
SQL> select * from emp where ename=UPPER('smith');
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
SAL COMM DEPTNO
---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80
800 20PPAS
scott=# select * from emp where ename=UPPER('smith');
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+-------+------+--------------------+--------+------+--------
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20
(1 row)3查询所有雇员的姓名，要求将每个雇员的姓名以首字母大写的形式出现
Oracle
SQL> select ename 原始姓名,INITCAP(ename) 姓名开头首字母大写 from emp;
原始姓名 姓名开头首字母大写
-------------------- --------------------
SMITH Smith
ALLEN Allen
WARD Ward
JONES Jones
MARTIN Martin
BLAKE Blake
CLARK Clark
SCOTT Scott
KING King
TURNER Turner
ADAMS Adams
原始姓名 姓名开头首字母大写
-------------------- --------------------
JAMES James
FORD Ford
MILLER Miller
14 rows selected.PPAS
scott=# select ename 原始姓名,INITCAP(ename) 姓名开头首字母大写 from emp;
原始姓名 | 姓名开头首字母大写
----------+--------------------
SMITH | Smith
ALLEN | Allen
WARD | Ward
JONES | Jones
MARTIN | Martin
BLAKE | Blake
CLARK | Clark
SCOTT | Scott
KING | King
TURNER | Turner
ADAMS | Adams
JAMES | James
FORD | Ford
MILLER | Miller
(14 rows)4查询出所有雇员的姓名，并且将雇员姓名中所有的字母“A”替换成“_”
Oracle
SQL> select ename,REPLACE(ename,'A','_') from emp;
ENAME REPLACE(ENAME,'A','_
-------------------- --------------------
SMITH SMITH
ALLEN _LLEN
WARD W_RD
JONES JONES
MARTIN M_RTIN
BLAKE BL_KE
CLARK CL_RK
SCOTT SCOTT
KING KING
TURNER TURNER
ADAMS _D_MS
ENAME REPLACE(ENAME,'A','_
-------------------- --------------------
JAMES J_MES
FORD FORD
MILLER MILLER
14 rows selected.PPAS
scott=# select ename,REPLACE(ename,'A','_') from emp;
ename | replace
--------+---------
SMITH | SMITH
ALLEN | _LLEN
WARD | W_RD
JONES | JONES
MARTIN | M_RTIN
BLAKE | BL_KE
CLARK | CL_RK
SCOTT | SCOTT
KING | KING
TURNER | TURNER
ADAMS | _D_MS
JAMES | J_MES
FORD | FORD
MILLER | MILLER
(14 rows)5查询书姓名长度是5的所有雇员的信息
Oracle
SQL> select * from emp where length(ename)=5;
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
7566 JONES MANAGER 7839 02-APR-81
2975 20
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
SAL COMM DEPTNO
---------- ---------- ----------
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
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
SAL COMM DEPTNO
---------- ---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87
1100 20
7900 JAMES CLERK 7698 03-DEC-81
950 30
8 rows selected.PPAS
scott=# select * from emp where length(ename)=5;
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
7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20
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
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
(8 rows)6查询姓名前3个字母是JAM的雇员信息
Oracle
SQL> select * from emp where SUBSTR(ename,0,3)='JAM';
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
SAL COMM DEPTNO
---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81
950 30PPAS
scott=# select * from emp where SUBSTR(ename,0,3)='JAM';
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+-------+------+--------------------+--------+------+--------
7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30
(1 row)7查询所有10部门雇员姓名，但是不显示每个雇员姓名的前三个字母
Oracle
SQL> select ename 原姓名,SUBSTR(ename,3) 截取之后的姓名 from emp where deptno=10;
原姓名
--------------------
截取之后的姓名
----------------------------------------------------------------
CLARK
ARK
KING
NG
MILLER
LLERPPAS
scott=# select ename 原姓名,SUBSTR(ename,3) 截取之后的姓名 from emp where deptno=10;
原姓名 | 截取之后的姓名
--------+----------------
CLARK | ARK
KING | NG
MILLER | LLER
(3 rows)8要求显示每个雇员姓名及其姓名的后3个字母
Oracle
SQL> select ename,SUBSTR(ename,LENGTH(ename)-2) from emp;
ENAME
--------------------
SUBSTR(ENAME,LENGTH(ENAME)-2)
--------------------------------------------------------------------------------
SMITH
ITH
ALLEN
LEN
WARD
ARD
ENAME
--------------------
SUBSTR(ENAME,LENGTH(ENAME)-2)
--------------------------------------------------------------------------------
JONES
NES
MARTIN
TIN
BLAKE
AKE
ENAME
--------------------
SUBSTR(ENAME,LENGTH(ENAME)-2)
--------------------------------------------------------------------------------
CLARK
ARK
SCOTT
OTT
KING
ING
ENAME
--------------------
SUBSTR(ENAME,LENGTH(ENAME)-2)
--------------------------------------------------------------------------------
TURNER
NER
ADAMS
AMS
JAMES
MES
ENAME
--------------------
SUBSTR(ENAME,LENGTH(ENAME)-2)
--------------------------------------------------------------------------------
FORD
ORD
MILLER
LER
14 rows selected.PPAS
scott=# select ename,SUBSTR(ename,LENGTH(ename)-2) from emp;
ename | substr
--------+--------
SMITH | ITH
ALLEN | LEN
WARD | ARD
JONES | NES
MARTIN | TIN
BLAKE | AKE
CLARK | ARK
SCOTT | OTT
KING | ING
TURNER | NER
ADAMS | AMS
JAMES | MES
FORD | ORD
MILLER | LER
(14 rows)9在SUBSTR()函数中设置负数截取（-3表示从倒数第三个开始截取）
Oracle
SQL> select ename,SUBSTR(ename,-3) from emp;
ENAME SUBSTR(ENAME,-3)
-------------------- ------------------------
SMITH ITH
ALLEN LEN
WARD ARD
JONES NES
MARTIN TIN
BLAKE AKE
CLARK ARK
SCOTT OTT
KING ING
TURNER NER
ADAMS AMS
ENAME SUBSTR(ENAME,-3)
-------------------- ------------------------
JAMES MES
FORD ORD
MILLER LER
14 rows selected.PPAS
scott=# select ename,SUBSTR(ename,-3) from emp;
ename | substr
--------+--------
SMITH | ITH
ALLEN | LEN
WARD | ARD
JONES | NES
MARTIN | TIN
BLAKE | AKE
CLARK | ARK
SCOTT | OTT
KING | ING
TURNER | NER
ADAMS | AMS
JAMES | MES
FORD | ORD
MILLER | LER
(14 rows)10下标从0开始（由于Oracle数据库设计的灵活性，所有其下标是从0或1开始都是一样的）
Oracle
SQL> select ename,SUBSTR(ename,0,3) from emp;
ENAME SUBSTR(ENAME,0,3)
-------------------- ------------------------
SMITH SMI
ALLEN ALL
WARD WAR
JONES JON
MARTIN MAR
BLAKE BLA
CLARK CLA
SCOTT SCO
KING KIN
TURNER TUR
ADAMS ADA
ENAME SUBSTR(ENAME,0,3)
-------------------- ------------------------
JAMES JAM
FORD FOR
MILLER MIL
14 rows selected.PPAS
scott=# select ename,SUBSTR(ename,0,3) from emp;
ename | substr
--------+--------
SMITH | SMI
ALLEN | ALL
WARD | WAR
JONES | JON
MARTIN | MAR
BLAKE | BLA
CLARK | CLA
SCOTT | SCO
KING | KIN
TURNER | TUR
ADAMS | ADA
JAMES | JAM
FORD | FOR
MILLER | MIL
(14 rows)11下标从1开始
Oracle
SQL> select ename,SUBSTR(ename,1,3) from emp;
ENAME SUBSTR(ENAME,1,3)
-------------------- ------------------------
SMITH SMI
ALLEN ALL
WARD WAR
JONES JON
MARTIN MAR
BLAKE BLA
CLARK CLA
SCOTT SCO
KING KIN
TURNER TUR
ADAMS ADA
ENAME SUBSTR(ENAME,1,3)
-------------------- ------------------------
JAMES JAM
FORD FOR
MILLER MIL
14 rows selected.PPAS
scott=# select ename,SUBSTR(ename,1,3) from emp;
ename | substr
--------+--------
SMITH | SMI
ALLEN | ALL
WARD | WAR
JONES | JON
MARTIN | MAR
BLAKE | BLA
CLARK | CLA
SCOTT | SCO
KING | KIN
TURNER | TUR
ADAMS | ADA
JAMES | JAM
FORD | FOR
MILLER | MIL
(14 rows)12返回指定字符的ASCII码
Oracle
SQL> select ASCII('L') from dual;
ASCII('L')
----------
76PPAS
scott=# select ASCII('L') from dual;
ascii
-------
76
(1 row)13验证CHR()函数，将ASCII码变回字符
Oracle
SQL> select CHR(100) from dual;
CH
--
dPPAS
scott=# select CHR(100) from dual;
chr
-----
d
(1 row)14去掉字符串左边空格函数---LTRIM()
Oracle
SQL> select ' SongYuejie Vastdata ' 原始字符串,LTRIM(' SongYuejie Vastdata ') 去掉左空格 from dual ;
原始字符串
----------------------------------------------------------
去掉左空格
------------------------------------------------
SongYuejie Vastdata
SongYuejie VastdataPPAS
scott=# select ' SongYuejie Vastdata ' 原始字符串,LTRIM(' SongYuejie Vastdata ') 去掉左空格 from dual ;
原始字符串 | 去掉左空格
-------------------------------+--------------------------
SongYuejie Vastdata | SongYuejie Vastdata
(1 row)15去掉字符串右边空格函数---RTRIM()
Oralce
SQL> select ' SongYuejie Vastdata ' 原始字符串,RTRIM(' SongYuejie Vastdata ') 去掉右空格 from dual ;
原始字符串
----------------------------------------------------------
去掉右空格
------------------------------------------------
SongYuejie Vastdata
SongYuejie VastdataPPAS
scott=# select ' SongYuejie Vastdata ' 原始字符串,RTRIM(' SongYuejie Vastdata ') 去掉右空格 from dual ;
原始字符串 | 去掉右空格
-------------------------------+--------------------------
SongYuejie Vastdata | SongYuejie Vastdata
(1 row)16去掉左右两边空格函数—TRIM()
Oracle
SQL> select ' SongYuejie Vastdata ' 原始字符串,TRIM(' SongYuejie Vastdata ') 去掉右空格 from dual ;
原始字符串
----------------------------------------------------------
去掉右空格
--------------------------------------
SongYuejie Vastdata
SongYuejie VastdataPPAS
scott=# select ' SongYuejie Vastdata ' 原始字符串,TRIM(' SongYuejie Vastdata ') 去掉右空格 from dual ;
原始字符串 | 去掉右空格
-------------------------------+---------------------
SongYuejie Vastdata | SongYuejie Vastdata
(1 row)17字符串左、右填充函数—LPAD()、RPAD()
Oracle
SQL> select LPAD('Vastdata',10,'*') LPAD函数使用,RPAD('Vastdata',10,'*') RPAD函数使用,
2 LPAD(RPAD('Vastdata',10,'*'),16,'*') 组合使用 from dual;
LPAD函数使用 RPAD函数使用 组合使用
-------------------- -------------------- --------------------------------
**Vastdata Vastdata** ******Vastdata**PPAS
scott=# select LPAD('Vastdata',10,'*') LPAD函数使用,RPAD('Vastdata',10,'*') RPAD函数使用,
scott-# LPAD(RPAD('Vastdata',10,'*'),16,'*') 组合使用 from dual;
lpad函数使用 | rpad函数使用 | 组合使用
--------------+--------------+------------------
**Vastdata | Vastdata** | ******Vastdata**
(1 row)18字符串查找函数---INSTR()
Oracle
SQL> select INSTR('SongYuejie Vastdata','Vastdata')查找得到,
2 INSTR('SongYuejie Vastdata','SongYuejie')查找得到,
3 INSTR('SongYuejie Vastdata','VASTDATA')查找不到
4 from dual;
查找得到 查找得到 查找不到
---------- ---------- ----------
12 1 0PPAS
scott=# select INSTR('SongYuejie Vastdata','Vastdata')查找得到,
scott-# INSTR('SongYuejie Vastdata','SongYuejie')查找得到,
scott-# INSTR('SongYuejie Vastdata','VASTDATA')查找不到
scott-# from dual;
查找得到 | 查找得到 | 查找不到
----------+----------+----------
12 | 1 | 0
(1 row)数值函数
19验证ROUND()函数的使用
Oracle
SQL> select ROUND(789.652) 不保留小数,ROUND(789.652,2) 保留两位小数,ROUND(789.652,-1) 处理整数进位 from dual;
不保留小数 保留两位小数 处理整数进位
---------- ------------ ------------
790 789.65 790PPAS
scott=# select ROUND(789.652) 不保留小数,ROUND(789.652,2) 保留两位小数,ROUND(789.652,-1) 处理整数进位 from dual;
不保留小数 | 保留两位小数 | 处理整数进位
------------+--------------+--------------
790 | 789.65 | 790
(1 row)20列出每个雇员的一些基本信息和日工资情况
Oracle
SQL> select empno,ename,job,hiredate,sal,ROUND(sal/30,2) 日薪金 from emp;
EMPNO ENAME JOB HIREDATE SAL
---------- -------------------- ------------------ ------------ ----------
日薪金
----------
7369 SMITH CLERK 17-DEC-80 800
26.67
7499 ALLEN SALESMAN 20-FEB-81 1600
53.33
7521 WARD SALESMAN 22-FEB-81 1250
41.67
EMPNO ENAME JOB HIREDATE SAL
---------- -------------------- ------------------ ------------ ----------
日薪金
----------
7566 JONES MANAGER 02-APR-81 2975
99.17
7654 MARTIN SALESMAN 28-SEP-81 1250
41.67
7698 BLAKE MANAGER 01-MAY-81 2850
95
EMPNO ENAME JOB HIREDATE SAL
---------- -------------------- ------------------ ------------ ----------
日薪金
----------
7782 CLARK MANAGER 09-JUN-81 2450
81.67
7788 SCOTT ANALYST 19-APR-87 3000
100
7839 KING PRESIDENT 17-NOV-81 5000
166.67
EMPNO ENAME JOB HIREDATE SAL
---------- -------------------- ------------------ ------------ ----------
日薪金
----------
7844 TURNER SALESMAN 08-SEP-81 1500
50
7876 ADAMS CLERK 23-MAY-87 1100
36.67
7900 JAMES CLERK 03-DEC-81 950
31.67
EMPNO ENAME JOB HIREDATE SAL
---------- -------------------- ------------------ ------------ ----------
日薪金
----------
7902 FORD ANALYST 03-DEC-81 3000
100
7934 MILLER CLERK 23-JAN-82 1300
43.33
14 rows selected.PPAS
scott=# select empno,ename,job,hiredate,sal,ROUND(sal/30,2) 日薪金 from emp;
empno | ename | job | hiredate | sal | 日薪金
-------+--------+-----------+--------------------+---------+--------
7369 | SMITH | CLERK | 17-DEC-80 00:00:00 | 800.00 | 26.67
7499 | ALLEN | SALESMAN | 20-FEB-81 00:00:00 | 1600.00 | 53.33
7521 | WARD | SALESMAN | 22-FEB-81 00:00:00 | 1250.00 | 41.67
7566 | JONES | MANAGER | 02-APR-81 00:00:00 | 2975.00 | 99.17
7654 | MARTIN | SALESMAN | 28-SEP-81 00:00:00 | 1250.00 | 41.67
7698 | BLAKE | MANAGER | 01-MAY-81 00:00:00 | 2850.00 | 95.00
7782 | CLARK | MANAGER | 09-JUN-81 00:00:00 | 2450.00 | 81.67
7788 | SCOTT | ANALYST | 19-APR-87 00:00:00 | 3000.00 | 100.00
7839 | KING | PRESIDENT | 17-NOV-81 00:00:00 | 5000.00 | 166.67
7844 | TURNER | SALESMAN | 08-SEP-81 00:00:00 | 1500.00 | 50.00
7876 | ADAMS | CLERK | 23-MAY-87 00:00:00 | 1100.00 | 36.67
7900 | JAMES | CLERK | 03-DEC-81 00:00:00 | 950.00 | 31.67
7902 | FORD | ANALYST | 03-DEC-81 00:00:00 | 3000.00 | 100.00
7934 | MILLER | CLERK | 23-JAN-82 00:00:00 | 1300.00 | 43.33
(14 rows)本连载博客主要探讨Oracle与PPAS(PostgreSQL)数据库的差异，以帮助更多读者了解如何实现数据库迁移！