1.3 常用函数
1.3.1 字符函数（11个）
许多SQL解释器都提供了字符和字符串的处理功能。本部分覆盖了大部分字符串处理
函数，这一部分的例子使用字符函数示范表。
输入\输出：
SQL> select * from 字符函数示范;
姓名 M CODE
---------- ---------- ---------- ----------
PURVIS KELLY A 32
TAYLOR CHUCK J 67
CHRISTINE LAURA C 65
ADAMS FESTER M 87
COSTALES ARMANDO A 77
KONG MAJOR G 52
1.3.1.1 CHR
该函数返回与所给数值参数等当的字符，返回的字符取决于数据库所依赖的字符集。
例如，示例的数据库采用了ASCLL字符集。示例数据库的代码列的内容为数字。
输入：
SQL> SELECT CODE,CHR(CODE)FROM 字符函数示范;
CODE CHR (CODE)
---------- ---------
32
67 C
65 A
87 W
77 M
52 4
在数值32处显示为空白，因为32在ASCLL码表中是空格。
1.3.1.2 CONCAT
我们在以前学到过一个与这个函数所执行的功能相当的操作，|| 符号表示将两个字符串连接起来，CONCAT也是完成这个功能的。使用方法如下：输入：
SQL> SELECT CONCAT(姓,名)"姓名" FROM 字符函数示范;
姓名
--------------------
PURVISKELLY
TAYLORCHUCK
CHRISTINELAURA
ADAMSFESTER
COSTALESARMANDO
KONGMAJOR
当用多个词来做为别名时可以它们使用引号。
1.3.1.3 INITCAP
该函数将参数的第一个字母变为大写，此外其它的字母则转换成小写。
输入：
SQL> SELECT 姓前,INITCAP(姓) 后 FROM 字符函数示范;
前后
---------- ----------
PURVIS Purvis
TAYLOR Taylor
CHRISTINE Christine
ADAMS Adams
COSTALES Costales
KONG Kong
1.3.1.4 LOWER和UPPER
LOWER将参数转换为全部小写字母，而UPPER则把参数全部转换成大写字母。下例是用LOWER函数和UPDATE函数来把数据库的内容转变为小写字母。
输入：
SQL> UPDATE 字符函数示范 SET 名='kelly' WHERE 名='KELLY';
输出：
1 row updated
输入：
SQL> SELECT 名 FROM 字符函数示范;
输出：
名
----------
kelly
CHUCK
LAURA
FESTER
ARMANDO
MAJOR
输入\输出：
SQL> select 名,upper(名),lower(名) from 字符函数示范;
名 UPPER(名) LOWER(名)
---------- ---------- ----------
kelly KELLY kelly
CHUCK CHUCK chuck
LAURA LAURA laura
FESTER FESTER fester
ARMANDO ARMANDO armando
MAJOR MAJOR major
现在你明白这两个函数的作用了吧！
1.3.1.5 LPAD与RPAD
这两个函数最少需要两个参数最多需要三个参数，每一个参数是需要处理的字符串。
第二个参数是需要将字符串扩充的宽度，第三个参数表示加宽部分用什么字符来做填补。
第三个参数的默认值为空格，但也可以是单个的字符或字符串。
输入\输出：
SQL> SELECT 名,LPAD(名,20,'*') FROM 字符函数示范;
名 LPAD(名,20,'*')
--------------- -----------------------------------------
KELLY ***************KELLY
CHUCK ***************CHUCK
LAURA ***************LAURA
FESTER **************FESTER
ARMANDO *************ARMANDO
MAJOR ***************MAJOR
输入\输出：
SQL> SELECT 名,RPAD(名,20,'*') FROM 字符函数示范;
名 RPAD(名,20,'*')
--------------- -----------------------------------------
KELLY KELLY***************
CHUCK CHUCK***************
LAURA LAURA***************
FESTER FESTER**************
ARMANDO ARMANDO*************
MAJOR MAJOR***************
1.3.1.6 LTRIM与RTRIM
LTRIM和RTRIM至少需要一个参数，最多允许两个参数。第一个参数与LPAD和RPAD
类似，是一个字符串；第二个参数也是一个字符或字符串，默认则是空格。如果第二个参
数不是空格的话，那么该函数将会像剪除空格那样剪除所指定的字符。如下例：输入\输出：
SQL> SELECT 姓,RTRIM(姓) FROM 字符函数示范;
姓 RTRIM(姓)
--------------- ---------------
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE CHRISTINE
ADAMS ADAMS
COSTALES COSTALES
KONG KONG你可以用下边的语句来确认字符中的空格已经被剪除了;
输出证明的确已经进行了剪除工作现在请再试一个LTRIM；
输入\输出：
SQL> SELECT 姓,LTRIM(姓,'C') FROM 字符函数示范;
姓 LTRIM(姓,'C')
--------------- ---------------
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE HRISTINE
ADAMS ADAMS
COSTALES OSTALES
KONG KONG
注意第三行和第五行的C已经没有了。
1.3.1.7 REPLACE
该函数需要三个参数，第一个参数是需要搜索的字符串，第二个参数是搜索的内容，第三个参数则是需要替换成的字符串。如果第三个参数省略或者是NULL，那么将只执行搜索操作而不会替换任何内容。
输入\输出：
SQL> SELECT 名,REPLACE(名,'ST','**') 替换后 FROM 字符函数示范;
名替换后
--------------- ------------------------------
KELLY KELLY
CHUCK CHUCK
LAURA LAURA
FESTER FE**ER
ARMANDO ARMANDO
MAJOR MAJOR
1.3.1.8 SUBSTR
这个函数有三个参数，允许你将目标字符串的一部份输出。第一个参数为目标字符串，
第二个字符串是将要输出的子串的起点，第三个参数是将要输出的子串的长度。输入\输出：
SQL> SELECT 名,SUBSTR(名,2,3) FROM 字符函数示范;
名 SUBSTR(名,2,3)
--------------- --------------
KELLY ELL
CHUCK HUC
LAURA AUR
FESTER EST
ARMANDO RMA
MAJOR AJO
●如果第二个参数为负数，那么将会从源串的尾部开始向前定位至负数的绝对
值的位置。
1.3.1.9 TRANSLATE
这一函数有三个参数，目标字符串、源字符串和目的字符串。在目标字符串与源字符
串中均出现的字符，将会被替换成对应的目的字符串的字符。
输入\输出：
SQL>SELECT
名,TRANSLATE(名,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','NNNNNNNNNN AAAAAAAAAAAAAAAAAAAAAAAAA') FROM 字符函数示范;
名 TRANSLATE(名,'0123456789ABCDEF
--------------- ------------------------------
kelly kelly
CHUCK AAAAA
LAURA AAAAA
FESTER AAAAAA
ARMANDO AAAAAAA
MAJOR AAAAA
6 rows selected
●这个函数对大小写是敏感的。
1.3.1.10 INSTR
如果需要知道在一个字符串中满足特定的内容的位置可以使用INSTR，它的第一个参数是目标字符串，第二个参数是匹配的内容，第三和第四个参数是数字，用以指定开始搜索的起点以及指出第几个满足条件的将会被返回。下例将从字符串的第二个字符开始搜索，并返回第一个以O开头的字符的位置。
输入\输出：
SQL> SELECT 姓,INSTR(姓,'O',2,1) FROM 字符函数示范;
姓 INSTR(姓,'O',2,1)
--------------- -----------------
PURVIS 0
TAYLOR 5
CHRISTINE 0
ADAMS 0
COSTALES 2
KONG 2
默认第三个与第四个参数的数值均为1，如果第三个数值为负数，那么将会从后向前搜索。
1.3.1.11 LENGTH
LENGTH将返回指定字符串的长度。例如：
输入\输出：
SQL> SELECT 名,LENGTH(RTRIM(名)) FROM 字符函数示范;
名 LENGTH(RTRIM(名))
--------------- -----------------
kelly 5
CHUCK 5
LAURA 5
FESTER 6
ARMANDO 7
MAJOR 5
这里使用了函数RTRIM，否则LENGTH将全部返回15。
1.3.2 数字函数（6个）
大多数情况下，你所检索到的数据在使用时，需要用到数学函数，大多数SQL 的解释器都
提供了与这里相类似的一些数学函数。这里的例子使用的表名字叫数学函数示范内容如下：
输入\输出：
SQL> SELECT * FROM 数学函数示范;
A B
---------- ----------
3.1415 4
-45 0.707
5 9
-57.667 42
15 55
-7.2 5.3
1.3.
2.1 ABS
ABS函数返回给定数字的绝对值。例如：
输入\输出：
SQL> SELECT ABS(A) FROM 数学函数示范;
ABS(A)
----------
3.1415
45
5
57.667
15
7.2
1.3.
2.2 CEIL和FLOOR
CEIL返回与给定参数相等或比给定参数在的最小整数。FLOOR则正好相反，它返回
与给定参数相等或比给定参数小的最大整数。例如：
输入\输出：
SQL> SELECT CEIL(B) FROM 数学函数示范;
CEIL(B)
----------
4
1
9
42
55
输入\输出：
SQL> SELECT FLOOR(A) FROM 数学函数示范;
FLOOR(A)
----------
3
-45
5
-58
15
-8
1.3.
2.3 MOD
MOD返回数A与数B相除后的余数。例如：
输入\输出：
SQL> SELECT A,B,MOD(A,B) FROM 数学函数示范;
A B MOD(A,B)
---------- ---------- ----------
3.1415 4 3.1415
-45 0.707 -0.459
5 9 5
-57.667 42 -15.667
15 55 15
-7.2 5.3 -1.9
1.3.
2.4 SIGN
如果参数的值为负数，那么SIGN返回-1。如果参数的值为正数，那么SIGN返回1。
如果参数为零，那么SIGN也返回零。请看下例：
输入\输出：
SQL> SELECT A,SIGN(A) FROM 数学函数示范;
A SIGN(A)
---------- ----------
3.1415 1
-45 -1
5 1
-57.667 -1
15 1
-7.2 -1
1.3.
2.5 ROUND
ROUND的作用是四舍五入至指定小数位数。例如：
输入\输出：
SQL> SELECT A,ROUND(A) FROM 数学函数示范;
A ROUND(A)
---------- ----------
3.1415 3
-45 -45
5 5
-57.667 -58
15 15
-7.2 -7
1.3.
2.6 TRUNC
TRUNC的作用是截断指定小数位数。例如：
输入\输出：
SQL> SELECT A,TRUNC(A,1) FROM 数学函数示范;
A TRUNC(A,1)
---------- ----------
3.1415 3.1
-45 -45
5 5
-57.667 -57.6
15 15
-7.2 -7.2
1.3.3日期函数（7个）
在学习日期函数之前，我们先要了解一下基本内容：
ORACLE数据库对日期数据在内部存贮格式:世纪,年,月,日,时,分,秒默认日期显示格式为：DD-MON-RR.
允许你以两位数字表示年份。
SYSDATE函数返回当前系统日期时间。
格式串：
YYYY 年份用四位数字表示
MM 月份用两位数字表示
DD 日期用两位数字表示
HH24:MI:SS AM 用24小时制表示时分秒，AM表示要输出上下午标志
1.3.3.1 SYSDATE
SYSDATE函数返回当前系统日期时间。例如：
输入\输出：
SQL> select sysdate from dual;
SYSDATE
-----------
2005-08-02
1.3.3.2 MONTHS_BETWEEN
如果你想知道在给定的两个日期中有多少个月可以像这样来使用
MONTHS_BETWEEN。例如：
输入\输出：
SQL> select months_between(to_date('2005-8-2','yyyy-mm-dd'),
to_date('2004-8-2','yyyy-mm-dd')) 月数 from dual;
月数
----------
12
1.3.3.3 ADD_MONTHS
该函数的功能是将给定的日期增加一个月。举例：
输入\输出：
SQL> select to_date('2005-8-2','yyyy-mm-dd') 原计划,add_months
(to_date('2005-8-2','yyyy-mm-dd'),2) 修改后计划 from dual;
原计划修改后计划
----------- -----------
2005-08-02 2005-10-02
1.3.3.4 NEXT_DAY
NEXT_DAY将返回与指定日期在同一个星期或之后一个星期内的，你所要求的星期天
数的确切日期。如果你想知道你所指定的日期的星期五是几号可以这样做：
输入\输出：
SQL> select SYSDATE 原日期,next_day(SYSDATE,'星期五') from dual;
原日期 NEXT_DAY(SYSDATE,'星期五')
----------- --------------------------
2005-08-022005-08-05 18:25:52
●本例中的星期五，同样可以用数字5来代替。
1.3.3.5 LAST_DAY
LAST_DAY可以返回指定月份的最后一天。例如，如果你想知道在本月的最后一天是几号时你可以输入：
输入\输出：
SQL> select last_day(SYSDATE) 月末 from dual;
月末
-----------
2005-08-31
1.3.3.6 ROUND
ROUND可以四舍五入日期。
输入\输出：
select sysdate 当前时间,round(sysdate,'year') 格式化后时间 from dual; 当前时间格式化后时间
----------- ------------
2005-08-03 2006-01-01
1.3.3.7 TRUNC
TRUNC可以截断制定日期。
输入\输出：
select sysdate 当前时间,trunc(sysdate,'year') 格式化后时间 from dual;
当前时间格式化后时间
----------- ------------
2005-08-03 2005-01-01
●请比较ROUND和TRUNC的输出结果的差异。
1.3.4 转换函数（3个）
1.3.4.1 TO_NUMBER
该函数的作用是将一个字符串转换成数值。TO_NUMBER(X,Y[,Z])，例如：
输入\输出：
SQL> select to_number('2005') year from dual;
YEAR
---------
2005
1.3.4.2 TO_CHAR
该函数作用是将一个日期或数字转换成一个字符串。TO_CHAR(date,’format_model’)。例如：
输入\输出：
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2005/08/03 09:14:41
1.3.4.2 TO_DATE
该函数作用是将字符串转化为ORACLE中的一个日期。TO_DATE(string,'format')。例如：
输入\输出：
SQL> select to_date('2005-08-03 09:21:00','yyyy-mm-dd hh24:mi:ss') from dual;
TO_DATE('2005-08-0309:21:00','
------------------------------
2005-08-03 9:21:00
1.3.5 聚合函数（5个）
1.3.5.1 COUNT
该函数将返回满足WHERE条件子句中记录的个数。例如：
SQL> select count(*) from dual;
COUNT(*)
----------
1
1.3.5.2 SUM
该函数将返回某一列的所有数值的和。例如：
SQL> select sum(病人id) from 病人信息;
SUM(病人ID)
-----------
25
1.3.5.3 AVG
AVG可以返回某一列的平均值。例如：
SQL> select avg(实收金额) from 病人费用记录 where 门诊标志=1;
AVG(实收金额)
-------------
98.67
1.3.5.4 MAX
MAX(DISTINCT|ALL)
求最大值，ALL表示对所有的值求最大值，DISTINCT表示对不同的值求最大值，相同的只取一次。例如：
SQL> select max(年龄) from 病人信息;
MAX(年龄)
----------
79
1.3.5.5 MIN
MIN(DISTINCT|ALL)
求最小值，ALL表示对所有的值求最小值，DISTINCT表示对不同的值求最小值，相同的只取一次。例如：
SQL> select min(年龄) from 病人信息;
MIN(年龄)
----------
4
1.3.6.2 NVL
该函数转换NVL值，数据类型必须匹配：例如：
NVL(实收金额,0)
NVL(填制日期,‘2005-08-03')
NVL(开单人,‘无名氏')
SQL> select distinct(nvl(开单人,'无名氏')) 开单人姓名 from 病人费用记录 where 门诊标志=1 and 开单人 is null;
开单人姓名
----------
无名氏
1.3.6.3 DECODE
DECODE(AA﹐V1﹐R1﹐V2﹐R2....)函数
解释:
IF AA=V1 THEN RETURN R1
IF AA=V2 THEN RETURN R2
..…
ELSE
RETURN NULL
例如：
decode(收费细目,'西药费','药费','成药费','药费','草药费','药费',收费细目)
1.4 常用的分组函数：
1.4.1 GROUP BY 子句的使用
SQL无法把正常的列和汇总函数结合在一起，这时就需要GROUP BY子句。它可以对SELECT的结果进行分组后在应用汇总函数。例如：
SQL> select 姓名,sum(实收金额) from 病人费用记录 where rownum<7 group by 姓名;
姓名 SUM(实收金额)
-------------------- -------------
秉仁 3.11
杜子滕 3.5
李卫国 70
毛鸭鸭 3.5
王复玉 2
赵卫生 1
1.4.2 HAVING 子句的使用
HAVING子句允许你将汇总函数作为条件，使用在查询语句中。HAVING子句为一组记录设置查询的条件，通常having子句允许用户指定对一个记录组的搜索条件。而通常的where查询条件只针对单记录，不针对记录组例如：
select 姓名,sum(实收金额) from 病人费用记录 where rownum<20 group by 姓名 having sum(实收金额)>2;
姓名 SUM(实收金额)
-------------------- -------------
秉仁 3.11
杜子滕 3.5
李卫国 70
毛鸭鸭 3.5