oracle数据库 SQL语句、内置函数大全

时间:2021-02-14 01:04:35

  1、数值函数

     函数      返回值             样例            显示

    CEIL(n)      大于或等于数值n的最小整数  SELECT CEIL(10.6) FROM TABLE_NAME; 11

    FLOOR(n)  小于等于数值n的最大整数   SELECT FLOOR (10.6) FROM TABLE_NAME; 10

    MOD(m,n)  m除以n的余数,若n=0,则返回m SELECT MOD (7,5) FROM TABLE_NAME l; 2

    POWER(m,n) m的n次方         SELECT POWER (3,2) FROM TABLE_NAME; 9

    ROUND(n,m) 将n四舍五入,保留小数点后m位  SELECT ROUND (1234.5678,2) FROM TABLE_NAME; 1234.57

    SIGN(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1 SELECT SIGN (12) FROM TABLE_NAME; 1

    SQRT(n) n的平方根         SELECT SQRT (25) FROM TABLE_NAME; 5

  2、常用字符函数

    INITICAP(char) 把每个字符串的第一个字符换成大写  SELECT INITICAP ('mr.ecop') FROM TABLE_NAME; Mr.Ecop

    LOWER(char) 整个字符串换成小写         SELECT LOWER ('MR.ecop') FROM TABLE_NAME; mr.ecop

    REPLACE(char,str1,str2) 字符串中所有str1换成str2 SELECT REPLACE ('Scott','s','Boy') FROM TABLE_NAME; Boycott

    SUBSTR(char,m,n) 取出从m字符开始的n个字符的子串  SELECT SUBSTR ('ABCDEF',2,2) FROM TABLE_NAME;  CD

    LENGTH(char) 求字符串的长度    SELECT LENGTH ('ACD') FROM TABLE_NAME; 3

    || 并置运算符    SELECT 'ABCD'||'EFGH' FROM TABLE_NAME;  ABCDEFGH

  3、日期型函数 

    SYSDATE 当前日期和时间 SELECT SYSDATE FROM TABLE_NAME;

    LAST_DAY  本月最后一天 SELECT LAST_DAY(SYSDATE) FROM TABLE_NAME;

    ADD_MONTHS(d,n) 当前日期d后推n个月 SELECT ADD_MONTHS (SYSDATE,2) f FROM TABLE_NAME;

    MONTHS_BETWEEN(d,n)

        日期d和n相差月数 SELECT MONTHS_BETWEEN(SYSDATE,TO_DATE('20020812','YYYYMMDD'))FROMTABLE_NAME;

    NEXT_DAY(d,day) d后第一周指定day的日期 SELECT NEXT_DAY (SYSDATE,'Monday') FROM TABLE_NAME;

      day 格式  有  'Monday' 星期一  'Tuesday' 星期二

      'wednesday'  星期三   'Thursday' 星期四    'Friday' 星期五

      'Saturday' 星期六   'Sunday' 星期日

  4、特殊格式的日期型函数

    Y或YY或YYY 年的最后一位,两位,三位 SELECTTO_CHAR(SYSDATE,'YYY') FROM TABLE_NAME;

    Q 季度,1-3月为第一季度    SELECT TO_CHAR(SYSDATE,'Q') FROM TABLE_NAME;

    MM  月份数           SELECT TO_CHAR(SYSDATE,'MM') FROM TABLE_NAME;

    RM 月份的罗马表示 SELECT TO_CHAR(SYSDATE,'RM') FROM TABLE_NAME; IV

    month 用9个字符表示的月份名 SELECT TO_CHAR(SYSDATE,'month') FROM TABLE_NAME;

    ww 当年第几周         SELECT TO_CHAR(SYSDATE,'ww') FROM TABLE_NAME l;

    w 本月第几周         SELECT TO_CHAR(SYSDATE,'w') FROM TABLE_NAME;

    DDD 当年第几天,一月一日为001 ,二月一日032 SELECT TO_CHAR(SYSDATE,'DDD') FROM TABLE_NAME;

    DD 当月第几天 SELECT TO_CHAR(SYSDATE,'DD') FROM TABLE_NAME;

    D 周内第几天 SELECT TO_CHAR(SYSDATE,'D') FROM TABLE_NAME; 如 sunday

    DY 周内第几天缩写       SELECT TO_CHAR(SYSDATE,'DY') FROM TABLE_NAME; 如 sun

    hh12 12小时制小时数       SELECT TO_CHAR(SYSDATE,'hh12') FROM TABLE_NAME;

    hh24 24小时制小时数       SELECT TO_CHAR(SYSDATE,'hh24') FROM TABLE_NAME;

    Mi 分钟数            SELECT TO_CHAR(SYSDATE,'Mi') FROM TABLE_NAME;

    ss 秒数             SELECT TO_CHAR(SYSDATE,'ss') FROM TABLE_NAME;

    SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH:24:mi:ss') FROM TABLE_NAME;

    TO_NUMBER() 将合法的字符串转换为数字   SELECT TO_NUMBER ('88877') FROM TABLE_NAME; 88877

    TO_CHAR() 将数字转换为字符串  SELECT TO_CHAR(88877) FROM TABLE_NAME; '88877'

  5、字符函数

    字符函数主要用于修改字符列。这些函数接受字符输入,返回字符或数字值。Oracle 提供的一些字符函数如下。

    1. CONCAT (char1, char2)

      返回连接“char2”的“char1”。

      示例  SELECT CONCAT( CONCAT( COLUMN_NAME, ' is a '), job) FROM TABLE_NAME;

    2. INITCAP(string)

      将“string”的字符转成大写。

      SELECT INITCAP( COLUMN_NAME) from TABLE_NAME;

    3. LOWER (string)

      将“string”转成小写。

      SELECT LOWER( COLUMN_NAME) from TABLE_NAME;

    4. LPAD(char1,n [,char2])

      返回“char1”,左起由“char2”中的字符补充到“n”个字符长。如果“char1”比“n”长,则函数返回“char1”的前“n”个字符。

        SELECT LPAD( COLUMN_NAME,15,'*') FROM TABLE_NAME;

    5. LTRIM(string,trim_set)

      从左边删除字符,此处“string”是数据库的列,或者是字面字符串,而“trim_set” 是我们要去掉的字符的集合。

      SELECT LTRIM('abcdab','a') FROM TABLE_NAME;

    6. REPLACE(string, if, then)

      用 0 或其他字符代替字符串中的字符。“if”是字符或字符串,对于每个出现在“string”中的“if”,都用“then”的内容代替。

      SELECT REPLACE('JACK and JUE','J','BL') FROM TABLE_NAME;

    7. RPAD(char1, n [,char2])

      返回“char1”,右侧用“char2”中的字符补充到“n”个字符长。如果 “char1”比“n” 长,则函数返回“char1”的前“n”个字符。

      SELECT RPAD( COLUMN_NAME,15,'*') FROM TABLE_NAME;

    8. RTRIM(string,trim_set)

      从右侧删除字符,此处“string”是数据库的列,或者是字面字符串,而“trim_set” 是我们要去掉的字符的集合。

      SELECT RTRIM('abcdef', 'f') FROM TABLE_NAME;

    9. SOUNDEX(char)

      返回包含“char”的表意字符的字符串。它允许比较英语中拼写不同而发音类似的字。

      SELECT  COLUMN_NAME FROM TABLE_NAME WHERE SOUNDEX( COLUMN_NAME) = SoUNDEX('SMYTHE');

    10. SUBSTR(string, start [,count])

      返回“string”中截取的一部分。该命令截取“string”的一个子集,从“start”位置开始,持续“count”个字符。如果我们不指定“count”,

      则从“start”开始截取到“string”的尾部。

      SELECT SUBSTR('ABCDEFGIJKLM',3,4) FROM TABLE_NAME;

    11. TRANSLATE(string, if, then)

      “if”中字符的位置,并检查“then”的相同位置,然后用该位置的字符替换 “string”中的字符。

      SELECT TRANSLATE( COLUMN_NAME,'AEIOU', 'XXXXX') FROM TABLE_NAME;

    12. UPPER(string)

      返回大写的“string”。

      SELECT UPPER('aptech computer education') FROM TABLE_NAME;

    13. ASCII(string)

      该命令是“American Standard Code for Information Interchange”的缩写。它是使用数字表示可打印字符的基本规则。

      该函数返回 “string”中第一个(最左边)字符的 ASCII 值。

      SELECT ASCII('APTECH') FROM TABLE_NAME;

    14. INSTR (string, set[, start[, occurrence] ] )

      该命令“string”中从“start”位置开始查找字符集合的位置,再查找“set”出现的第一次、第二次等等的“occurrence”(次数)。

      “start”的值也可以是负数,代表从字符串结尾开始向反方向搜索。该函数也用于数字和日期数据类型。

      SELECT INSTR('aptech is aptech','ap',1,2) FROM TABLE_NAME;

    15. LENGTH(string)

      返回“string”的长度值。

      SELECT COLUMN_NAME, LENGTH( COLUMN_NAME) FROM TABLE_NAME;

  6、NULL函数

    1.COALESCE(expr)

      返回表达式列表中第一个不为NULL的函数,如果都为NULL,则返回一个null

      SELECT COALESCE(NULL,9-9,NULL) FROM TABLE_NAME;

    2.LNNVL(condition)

      返回除了condition要求的条件之外的数据,包括NULL的条件,通常用于WHERE条件中

      SELECT COLUMN_NAME1,...COLUMN_NAME2 FROM TABLE_NAME WHERE LNNVL(condition);

    3.NVL(expr1,expr2[,expr3])

      NVL(expr1,expr2),替换NULL值,如果expr1为NULL值,则返回expr2的值,否则返回expr1的值。该函数要求两个参数类型一致至少相互

      之间可以进行隐式的转换

      SELECT NVL(COLUMN_NAME1,COLUMN_NAME2) FROM TABLE_NAME;

      NVL(expr1,expr2,expr3),,替换NULL值,如果expr1为NULL值,则返回expr2的值,否则返回expr3的值。该函数要求expr2、expr3参数

      类型一致至少相互之间可以进行隐式的转换

      SELECT NVL(COLUMN_NAME1,COLUMN_NAME2,COLUMN_NAME3) FROM TABLE_NAME;

  7、集合函数

    1.AVG([distinct l all] expr)

      求取列的平均值,表示某组的平均值,返回数值类型

      distinctlall:去除重复的值

      all:表示所有的值,包括重复的,也是默认值

      expr:表达式,只能是数值类型

      SELECT AVG(ALL COLUMN_NAME) FROM TABLE_NAME [WHERE CONDITION];

    2.MAX([distinct l all] expr) MIN([distinct l all] expr)

      指定列的最大值和最小值

      SELECT * FROM TABLE_NAME WHERE COLUMN_NAME = (SELECT MAX(COLUMN_NAME) FROM TABLE_NAME);

      SELECT * FROM TABLE_NAME WHERE COLUMN_NAME = (SELECT MIN(COLUMN_NAME) FROM TABLE_NAME);

    3.SUM([distinct l all] expr) MIN([distinct l all] expr)

      求和函数,返回制定列的数值和

      SELECT SUM(ALL COLUMN_NAME) FROM TABLE_NAME [WHERE CONDITION];

    4.COUNT([distinct l all] expr) MIN([distinct l all] expr)

      返回查询的记录数

      SELECT COUNT(ALL COLUMN_NAME) FROM TABLE_NAME [WHERE CONDITION];

  8、其他函数

    1.USER

      返回登录名函数

      SELECT USER FROM DUAL;

    2.USERENV(parameter)

      返回当前会话信息

      parameter:Language 返回当前会话对应语言和字符集

      parameter:SESSIONID 返回当前会话ID

      parameter:ISDBA 返回当前用户是否DBA

      SELECT USERENV('ISDBA') FROM TABLE_NAME [WHERE CONDITION];

    3.SYS_CONTEXT(namespace,parameter)

      返回oracle已经创建的context,名为USERENV的属性对应值

    4.DECODE(expr,search,result[,search1,result1][,default])

      表达式匹配函数,当expr符合条件search时返回result的值,该过程可以重复多个,如果没有匹配结果放回默认值default,这是一对一匹配过程,

      类似switch