Oracle SQL常用函数

概述

SQL函数有单行函数和多行函数,其区别为：

• 单行：输入一行,返回一行,如字符、数字、转换、通用函数等
• 多行：输入多行,返回一行,也称为分组函数、组函数、聚合函数,且多行函数会自动滤空

单行函数

字符函数

• CONCAT(X,Y)： 连接字符串X和Y
• INSTR(X,STR)： 后面STR在前面字符串X第一次出现的位置,一般用于判断STR是否存在于X中,若存在,则结果肯定大于0,否则结果为0表示不存在
• LOWER(X)： X转换成小写
• UPPER(X)： X转换成大写
• INITCAP(X)： X转换首字母大写

数字函数

• ROUND(X[,Y])： 四舍五入
  • 在缺省y时,默认y=0;比如：ROUND(3.56)=4
  • y是正整数,就是四舍五入到小数点后y位。ROUND(5.654,2)=5.65
  • y是负整数,四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400
• TRUNC(x[,y]): 直接截取取整,不进行四舍五入
  • 在缺省y时,默认y=0;比如：TRUNC(3.56)=3
  • Y是正整数,就是四舍五入到小数点后y位。TRUNC (5.654,2)=5.65
  • y是负整数,四舍五入到小数点左边|y|位。TRUNC (351.654,-2)=300

转换函数

转换函数将值从一种数据类型转换为另外一种数据类型。常见的转换函数有

• TO_CHAR(d|n[,fmt]): 把日期和数字转换为制定格式的字符串。Fmt是格式化字符串
  • TO_CHAR对日期的处理
    • SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS')"date" FROM dual;
• TO_DATE(X,[,fmt]): 把一个字符串以fmt格式转换成一个日期类型
• TO_NUMBER(X,[,fmt]): 把一个字符串以fmt格式转换为一个数字

通用函数

• NVL函数: 将空值转换为已知值,可以使用的数据类型为：数字、日期、字符,数据类型必须匹配
• NVL2(expr1,expr2,expr3): 如果参数1非空not null,则返回参数2的值,否则返回参数3的值
• 条件表达式:在使用if-then-else逻辑时可以通过如下两种方式表示
  • decode: 条件表达式,在使用if-then-else逻辑时可以通过decode(col/expression,search1,result1,search2,result2,....,,.........,default)
  • case expr when comparison_expr1 then return_expr1 when comparison_expr2 then return_expr2... else_expr

多行函数

嵌套函数distinct

distinct可用来过滤掉多余的重复记录只保留一条,但往往只用它来返回不重复记录的条数,而不是用它来返回显示不重记录的所有值。一般和count配合使用,作为统计非空且不重复的记录数

SELECT COUNT(DISTINCT(Column)) FROM TableName;

注意:DISTINCT关键字效率会比较低,如果仅仅是为了显示不重复的记录,建议使用group by,
因为distinct只有用二重循环查询来解决,而这样对于一个数据量非常大的表来说,无疑是会直接影响到效率的

其他

开发扩展

• || 连接符,在连接符中的字符串或者日期必须使用单引号,不能使用双引号;
• escape转义字符,用于特殊字符查询
  • select * form table where column like '%#_%' escape '#‘,用于查询包含_字符的数据

• 在Oracle中,join=inner join,left join=left outer join,right join=right outer join

Oracle与Mysql区别

• 数据类型方面
  • Oracle支持更多的数据类型,包括blob、clob、nclob、bfile等,MySQL则没有这些类型
• 存储引擎方面
  • Oracle支持多种存储引擎,MySQL也是如此,但是Oracle默认的存储引擎是ACID-compliant的,而MySQL默认使用的是不具备ACID特性的MyISAM存储引擎
• 查询优化方面
  • Oracle具有更加成熟的查询优化器,能够更好的处理复杂查询,MySQL则需要手动进行查询优化