oracle常用函数

 

1.1 select语句的功能

 选择、投影、连接

 

1.2 select语句的语法

SELECT column1, column2.、、（distinct，expression，alias， || ，AS）--指定提取的列

  FROM table1，table2、、--指定表提取

 WHERE  --设置条件范围

 GROUPBY--分组

HAVING--设置分组条件

 ORDERBYASC / DESC--设置排序

 

 

1.3 select语句的运算符

(算术运算符,连接运算符,关系运算符,其他运算符,逻辑表达式)

1、(),+,-,*,/         --数字和日期数据可以使用进行计算

2、||  （=concat）                   --连接运算符

3、=,>,>=,<,<=                       --关系运算符

4、IS [NOT] NULL,LIKE,[NOT] IN,[NOT] BETWEEN  AND      --其他运算符

5、   NOT,AND,OR                         --逻辑表达式

 

 

2.1 字符函数

2.1.1 CONCAT ,||

     功能：连接字符串

     语法：CONCAT(str1,str2)

SELECTCONCAT('名字','9527'),'名字'||'9527'FROM dual;

 

 

2.1.2 INITCAP

   功能：该函数将参数的第一个字母变为大写，此外其它的字母则转换成小写。

   语法：INITCAP(str1)

   SELECTINITCAP('ADMIN') FROM dual;

 

2.1.3 LOWER和UPPER

   功能：LOWER将参数转换为全部小写字母，而UPPER则把参数全部转换成大写字母。

   语法：LOWER(str1) ,UPPER(str1)

   SELECTLOWER('Admin'),UPPER('Admin') FROM dual;

 

2.1.4 LPAD与RPAD

   功能：补全(填充)

   语法：LPAD/RPAD(待处理字符串(字段),扩充宽度,填充字符(可空))

SELECTLPAD('800',4) FROM dual; 

  SELECTLPAD('800',4,'0') FROM dual;

 

2.1.5 TRIM与LTRIM与RTRIM

  TRIM

功能：去除指定的字符。trim只能去除单个字符

  语法：TRIM(leading|trailing|both string1 FROM string2)

    SELECTtrim(leading'd'from'dfssad') FROM dual;

 SELECTtrim(both'1'from'123sfd111') FROM dual;

    SELECTtrim(trailing'2'from'213dsq12') FROM dual;

 

  LTRIM与RTRIM

  功能：去除多个字符

  语法：LTRIM(string1,string2),RTRIM(string1,string2)

  SELECTLTRIM('Admin','A'),LTRIM(' Admin'),TRIM(' Admin ') FROM dual;

 

2.1.6 REPLACE

功能：把string中的所有的子字符串search_str用可选的replace_str替换，如果没有指定replace_str，所有的string中的子字符串search_str都将被删除。

语法： REPLACE(string，search_str[，replace_str])

 SELECT'Admin',REPLACE('Admin','min','**') 替换后FROM dual;

 

2.1.7 SUBSTR

功能：返回从字母为值a开始b个字符长的string的一个子字符串

语法：SUBSTR(string,a[,b])

 SELECTSUBSTR('admin',1,2),SUBSTR('admin',2) FROM dual;

 

2.1.8 INSTR

功能： 得到在string1中包含string2的位置。

语法： INSTR（string1， string2[a，b]）

SELECTINSTR('admin','n') FROM dual;

 

2.1.9 LENGTH

功能：返回string的字节单位的长度。如果string是NULL，返回结果是NULL，而不是0。

语法： LENGTH(string)

SELECTLENGTH(NULL),LENGTH('admin') FROM dual;

 

2.2 数字函数

 

2.2.1 ABS

功能：ABS函数返回给定数字的绝对值。

语法： ABS(NUMBER)

SELECT -10.45,ABS(-10.45) FROM dual;

 

2.2.2 ROUND

功能：四舍五入至指定小数位数。

语法： ROUND(NUMBER,小数取几位)

SELECT10.45,ROUND(10.45,1) FROM dual;

 

2.2.3 TRUNC

功能：截断指定小数位数。

语法： TRUNC(NUMBER,小数取几位)

 SELECT10.45,TRUNC(10.45,1) FROM dual;

 

2.3 日期函数

ORACLE数据库对日期数据在内部存贮格式:世纪,年,月,日,时,分,秒

默认日期显示格式为：DD-MON-RR.

允许你以两位数字表示年份。

SYSDATE函数返回当前系统日期时间。

   格式串：

YYYY	年份用四位数字表示
MM	月份用两位数字表示
DD	日期用两位数字表示
HH24:MI:SS AM	用24小时制表示时分秒，AM表示要输出上下午标志

 

2.3.1 SYSDATE

功能：返回当前系统日期时间。

语法：SYSDATE

SELECT  SYSDATE  FROM  dual;

 

2.3.2 ADD_MONTHS

功能：将给定的日期增加一个月。

语法： ADD_MONTHS(DATE,NUMBER)

SELECTSYSDATE ,add_months(SYSDATE,2) FROM dual;

 

 

2.3.3 NEXT_DAY

功能：返回与指定日期在同一个星期或之后一个星期内的，你所要求的星期天

数的确切日期。

语法： NEXT_DAY(DATE,星期)

 SELECTSYSDATE, next_day(SYSDATE, 5) FROM dual;

 

2.3.4 LAST_DAY

功能：返回指定月份的最后一天。

语法： LAST_DAY(DATE)

SELECT last_day(SYSDATE) FROM dual;

 

2.3.5 ROUND

功能：四舍五入日期。

语法：ROUND(DATE)

SELECTSYSDATE, ROUND(SYSDATE, 'year'),ROUND(SYSDATE, 'month') ,ROUND(SYSDATE) FROM dual;

 

2.3.6 TRUNC

 功能：截断指定日期。

 语法：TRUNC(DATE)

selectSYSDATE,TRUNC(sysdate,'year'),TRUNC(SYSDATE)  from dual;

 

 

2.4 转换函数

 

2.4.1 TO_NUMBER

功能：将一个字符串转换成数值。

语法： TO_NUMBER(varchar2orchar,’format model’)

select TO_NUMBER('2005') from dual;

select TO_NUMBER('$123,623.3400','$999,999.0000') from dual;

9	代表一个数字
0	强迫0显示
$	显示美元符号
L	强制显示一个当地的货币符号
.	显示一个小数点
,	显示一个千位分隔符号

 

2.4.2 TO_CHAR

功能：将一个日期或数字转换成一个字符串。

语法：TO_CHAR(DATE,’format_model’)

 SELECT TO_CHAR(SYSDATE, 'yyyy/mm/dd hh24:mi:ss') FROM dual;

 

2.4.3 TO_DATE

功能：将字符串转化为ORACLE中的一个日期。

语法：TO_DATE(string,'format')

select to_date('2016-03-16 14:21:00','yyyy-mm-dd hh24:mi:ss')  from dual;

select to_date('2016-03-16 14:21:00','yyyy-mm-dd hh:mi:ss')  from dual;

 

 

2.5 聚合函数

 

2.5.1 COUNT

功能：返回满足WHERE条件子句中记录的个数。

语法：COUNT()

SELECTCOUNT(*) FROM tm_department;

 

 

COUNT(*)将返回表格中所有存在的行的总数包括值为null的行，然而count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入）

 

如果你的数据表没有主键，那么count(1)比count(*)快  

如果有主键的话，那主键（联合主键）作为count的条件也比count(*)要快  

如果你的表只有一个字段的话那count(*)就是最快的啦  

count(*) count(1) 两者比较。主要还是要count(1)所相对应的数据字段。  

如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。  

因为count(*),自动会优化指定到那一个字段。所以没必要去count(?)，用count(*),sql会帮你完成优化的。

 

 

2.5.2 SUM

功能：返回某一列的所有数值的和。

语法：SUM(列名)

selectSUM(money) from (SELECT10 money FROM dual

        UNIONALL

        SELECT15FROM dual);

 

2.5.3 AVG

功能：返回某一列的平均值。

语法：AVG(列名)

SELECTAVG(money)

  FROM (SELECT10 money FROM dual

        UNIONALL

        SELECT15FROM dual)

 

2.5.4 MAX

功能：求最大值,ALL表示对所有的值求最大值，DISTINCT表示对不同的值求最大值，相同的只取一次。

语法：MAX(DISTINCT|ALL)

求最大值，ALL表示对所有的值求最大值，DISTINCT表示对不同的值求最大值，相同的只取一次。例如：

selectmax(年龄) from病人信息;

 

2.5.5 MIN

功能：求最小值，ALL表示对所有的值求最小值，DISTINCT表示对不同的值求最小值，相同的只取一次。

语法：MIN(DISTINCT|ALL)

SELECTMIN(dept_id) FROM tm_department;

 

 

2.6 其他函数

 

2.6.1 NVL

功能：第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

语法：NVL(expr1,expr2)

SELECTNVL(NULL,'1'),NVL('','1'),NVL(3,'1') FROM dual;

 

 

2.6.2 NVL2

功能：如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值。

语法：NVL2(expr1,expr2, expr3)

SELECTNVL2(NULL,'2','3'),NVL2('1','2','3') FROM dual;

 

2.6.3 NULLIF

功能：如果exp1和exp2相等则返回空(NULL)，否则返回第一个值。

语法：NULLIF(exp1,expr2)

SELECTNULLIF(2,3) FROM dual;

 

2.6.4 DECODE

功能：如果exp1和exp2相等则返回空(NULL)，否则返回第一个值。

语法：DECODE(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

该函数的含义如下：

IF条件=值1 THEN

　　　　RETURN(返回值1)

ELSIF条件=值2 THEN

　　　　RETURN(返回值2)

　　　　......

ELSIF条件=值n THEN

　　　　RETURN(返回值n)

ELSE

　　　　RETURN(缺省值)

ENDIF;

 

SELECTDECODE(money,10,11,15,16,0)

  FROM (SELECT10 money FROM dual

        UNIONALL

        SELECT15FROM dual);

       

       

2.6.5 caseWHEN

功能：根据条件选择值。

  Case具有两种格式。简单Case函数和Case搜索函数

  简单Case函数

简单CASE表达式,使用表达式确定返回值.

　　语法:

　　CASE search_expression

　　WHEN expression1 THEN result1

　　WHEN expression2 THEN result2

　　...

　　WHEN expressionN THEN resultN

　　ELSE default_result

 

 搜索CASE表达式,使用条件确定返回值.

　　语法:

　　CASE

　　WHEN condition1 THEN result1

　　WHEN condistion2 THEN result2

　　...

　　WHEN condistionN THEN resultN

　　ELSE default_result

　　END

 

--简单Case函数 

CASE salary  

WHEN200THEN'等级1' 

WHEN400THEN'等级2' 

ELSE'等级3'END 

--Case搜索函数 

CASEWHEN salary = 200THEN'等级1' 

WHEN salary = 400THEN'等级2' 

ELSE'等级3'END 

 

 

SELECT salary,

       CASE

         WHEN salary = 200THEN

          '等级1'

         WHEN salary = 400THEN

          '等级2'

         ELSE

          '等级3'

       END

  FROM (SELECT200 salary FROM dual

        UNIONALL

        SELECT400FROM dual

        UNIONALL

        SELECT500FROM dual);

 

2.6.6 UNION与UNIONAll

集合查询有UNION , INTERSECT及MINUS

UNION：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序;

UNIONAll：对两个结果集进行并集操作，包括重复行，不进行排序；

 

SELECT15 money FROM dual

UNIONALL

SELECT10FROM dual

UNIONALL

SELECT10FROM dual;

 

SELECT15 money FROM dual

UNION

SELECT10FROM dual

UNION

SELECT10FROM dual;

 

 

2.7 分组函数

 

2.7.1 GROUPBY

功能： 分组。

语法：GROUPBY列名1,....

 

SELECT a_name,SUM(salary) FROM

(SELECT'张三' a_name ,200 salary FROM dual

        UNIONALL

        SELECT  '李四' a_name,400FROM dual

        UNIONALL

        SELECT  '李四' a_name ,500FROM dual)

 GROUPBY a_name  

 

 

2.7.2 HAVING

功能： 分组搜索(HAVING子句允许你将汇总函数作为条件，使用在查询语句中。HAVING子句为一组记录设置查询的条件，

通常having子句允许用户指定对一个记录组的搜索条件)。

语法：HAVING查询条件

 

SELECT a_name,COUNT(1) FROM

(SELECT'张三' a_name ,200 salary FROM dual

        UNIONALL

        SELECT  '李四' a_name,400FROM dual

        UNIONALL

        SELECT  '李四' a_name ,500FROM dual)

 GROUPBY a_name

 HAVINGCOUNT(1)>1