SQL基本函数

SQL基本函数

单行函数：一行数据输入，返回一个值的函数。

多行函数：多行数据输入，返回一个值得函数，典型的就是聚合函数。

一、单行函数的几种类型

1、字符型函数

       1) lower('SQL Course')----->sql course 返回小写

        

       

       2) upper('sql course')----->SQL COURSE 返回大写

        

        

        Upper和lower是对字符串中的英文字母取大取小，对于字符串中的数字、汉字、符号不做改变。

       3） initcap('sql course')-----> Sql Course 每个单词返回首字母大写

        

       4） concat('good','string')---->goodstring 拼接   只能拼接2个字符串

        

       5）substr('String',1,3)----->Str  截取子串，从第一位开始截取3位

        

        第二个参数是1和0 的结果是一样的。都是从第一位开始算。

        

        substr('String',3)   从左到右第三位起始，得到后面所有字符，从1开始数。

        

        substr('String',-2)   倒数第二位，起始，得到最后所有字符。

        

        第三个参数没有的话默认到结尾，如果是0则表示取0个字符，即没有。若字符串中有汉字还是按字符处理。

        

        

        6）instr('t#i#m#r#a#n#','#')--->找第一个#字符在哪个绝对位置，得到的数值。若字符串中没有要查找的字符，则返回0

              instr(字符串，要查找的字符串，开始的位置，出现的次数)

        

        指定的可以说字符串也可以是字符，默认是从左向右第一个出现的位置。

        

        从第一位、第三位、第六位开始数

        

        从右向左（倒着）开始第一位、第三位、第六位数

        

        从左往右和从右往左第几次出现字符串位置

        

        select instr('aunfukk','u',-1,1) from dual; 倒数第一个u是哪个位置，结果返回5

        

        7）length('String')---->6长度，得到的是数值

       

        8）lpad('first',10,'$')左填充

       

        9）rpad(676768,10,'*')右填充

        

        10）replace('JACK and JUE','J','BL')---->BLACK and BLUE

        

       replace('JACK and JUE','J')-----àACK and UE

        

        SQL 中没有提供删除字符串中匹配的子字符串的方法，因为使用 REPLACE()函数就可以达到删除子字符串的方法，那就是将第三个参数设定为空字符串或者省略第三个参数，用空字符串来替换匹配的子字符串也就达到了删除指定子字符串的效果了 。

       

        去除某个字符串或字符

        

        第二个参数为空的时候结果不变

        

        11）trim('m' from 'mmtomlium')---->  tomliu  两头截，这里的‘m’是截取集，仅能有一个字符

        

        Trim只能去除一个字符，会去除两边的字符。

        

        

        12）ltrim---->截去字符串左边的空格

        

        从左边算起，去除第二个参数中出现过的字符，无匹配返回原结果。

        

        

        若都匹配了返回空

        

       13） rtrim------>截去字符串右边的空格

        

        14）ASCII码函数：用来得到一个字符的ASCII码，它有且只有一个参数，如果参数是一个字符串，则返回第一个字符的ASCII码。

        

        15）|| 拼接函数

        

        如果字符串中包含了null，则将null处理成‘’，在拼接。

        16) chr函数

        Ascii函数的反函数，将ASCII码转换为字符

        

        17）translate替换函数

        

        Translate是replace提供的一个超集，如果需要替换的字符比替换的字符多，那么在需要替换的字符中而不在替换的字符中的额外字符将被删除，因为没有相应的字符替换，替换的字符不能为空，如果参数中任何参数为null，结果就是null。

        

        

        需要替换的字符和替换的字符是从左到右一一对应的关系，如果不能对应，则视为空值。

        

        汉字是按字符处理的。

        

        18）greatest      取一行最大值

        Oracle比较一列的最大值最小值：max和min

        Oracle比较一行的最大值最小值：coalesce / greatest/least

        GREATEST 返回值列表中最大值

        格式： GREATEST(value1, value2, value3, ...)

        含义： 返回value列表最大的值。

        value列表必须是相同类型，也可以是一个表的同一行、不同列的值进行比较。

        当value值列表中有一个为NULL，则返回NULL值。

        

        有些可以通过隐式转换的，转换后比较，不能隐式转换的报错。

        

        

        部分为字符型，会把非字符型转换成字符型：

        

        19） least 取一行最小值

        LEAST 返回值列表中最小值

        格式： LEAST(value1, value2, value3, ...)

        含义： 返回value列表最小的值。

        value列表必须是相同类型，也可以是一个表的同一行、不同列的值进行比较。

        当value值列表中有一个为NULL，则返回NULL值。

        

      20) coalesce 返回第一个非空值

        格式： COALESCE(value1, value2, value3, ...)

        含义： 返回value列表第一个非空的值。

        value列表必须是相同类型，也可以是一个表的同一行、不同列的值进行比较。

        

2、数值型函数

        1) round对指定的值做四舍五入,round(p,s) ，以小数点为0，小数点后为正数 ，小数点左边为负数。

        

        若round后只有一个参数，按第二位为0 处理，也就是取整。

        

        2) trunc 对指定的值取整 trunc(p,s)

        trunc:按指定精度截断十进制数,如:trunc(45.923,1),结果,45.9

        

        3) mod 返回除法后的余数。

        

        4）求绝对值：ABS（）函数，求一个数的绝对值。

        

        abs函数的参数是null的时候，结果也是null，因为ABS函数处理的是列，大多数处理列的函数，对null的处理都是null。

        5）求指数：power（）函数，接受两个参数，第一个参数为带求幂的表达式，第二个为幂。

        

       6） 求平方根：sqrt（）函数，用来计算平方根。

        

        7）求随机数：oracle没有内置的生成随机数的函数，不过oracle提供了包dbms_random用来生成随机数，使用方法如下：

        dbms_random.value

        

        ORACLE的PL/SQL提供了生成随机数和随机字符串的多种方式，罗列如下:

       a、小数( 0 ~ 1)

             select dbms_random.value from dual

        b、指定范围内的小数 ( 0 ~ 100 )     

            select dbms_random.value(0,100) from dual

            

        c、指定范围内的整数 ( 0 ~ 100 )

             select trunc(dbms_random.value(0,100)) from dual

            

        d、长度为20的随机数字串
             select substr(cast(dbms_random.value as varchar2(38)),3,20) from dual

           

        e、正态分布的随机数

             select dbms_random.normal from dual；

            

        8）舍入到最大整数

            ceil()函数，用来舍掉一个数的小数点后的部分，并且向上舍入到临近的最大整数。

            

            

        9）舍入到最小整数

            floor()函数，和ceil函数相反，用来舍掉一个数的小数点后的部分，并且向下舍入到临近的最小整数。

            

        10）求符号

            sign()函数用来返回一个数的符号，如果大于0则返回1，如果等于0 则返回0 ，如果小于0则返回-1 。

            

        11）还有数学公式函数：正余弦函数sin()、cos()、自然对数log()等。

3、日期型函数

        日期在oracle中以数字形式存储，所以可以对他进行加减运算。计算是以天为单位。

        

        1）当前时间：current_date, 返回该函数执行的时间。

       

        2）current_timestamp  当前日期和时间

        

        3）extract  截取日期元素

        

        

        4）cast  转换函数

        cast（转换前的值  as  想要转换的数据类型）

        

        5）MONTHS_BETWEEN   计算两个日期之间的月数，大的在前

        

         比如查找emp表中参加工作时间>30年的员工。

        

        6）ADD_MONTHS   给日期增加月份

        

        

        7） LAST_DAY  日期当前月份的最后一天

        

        8）next_day  第二个参数是数字1-7，代表周日到周六。

            比如取下一周五

        

        9）round(p,s)在日期中的应用，如何舍入要看具体情况，s是month按30天计，应该15舍16入，s是year则按6舍7入计算。       

附：

    date类型中存储有年、月、日，时、分、秒的全部信息，但默认情况下，在SQLPLUS中，只显示年月日的信息。所以，用以下命令将当前会话中的日期显示参数改为显示全部信息：

        alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"

    然后你可以再次测试和DATE相关的函数操作，比如TRUNC，LAST_DAY，NEXT_DAY，add_months等。对于add_months,如果是如下的操作，其结果又是什么？建议先自己推测，然后再实验验证：

  add_months(date'2018-01-09',1)

  add_months(date'2018-01-30',1)

  add_months(date'2018-01-31',1)

  add_months(date'2018-02-28',1)

  add_months(date'2018-01-09',-1)

  add_months(date'2018-02-28',-1)

  add_months(date'2018-03-30',-1)

  add_months(date'2018-03-31',-1)

   
    
   

    add_month给日期增加月份，加参数个月，到该月的当日。若加之后的月比加之前的月的天数少，加之后原本应该超过的时候结果取该月最后一天。比如以下例子，一月份加一个月到二月份，1月29之前的日期都是到2月的同一天，而28之后的日期因为二月份只有28天，所以都会显示到2月28。所以直接月份相减不会超过参数个。不是直接加30或31天。

   

    当参数为负，也就是月份减的时候，也是同样的道理。

   

结论：

        1、add_months是对第一个参数的日期进行第二个参数的加减操作。

      2、add_months的单位是月，不是单纯的加30或31天，比如add_months('2018-01-15',1)，结果就是2018-02-15。add_months('2018-03-25',-1)结果就是2018-02-25。但也有例外，如下。

        3、add_months('2018-01-30',1)结果是2018-02-28，因为18年2月只有28天，月数加的也是1 ，所以结果只能是2 月，这种情况下结果就是2月的最后一天。也就是加月份的时候，若加前后的那月天数比加之后多，结果只能是加之后那月的最后一天。

        4、add_months('2018-03-30',-1)结果是2018-02-28，和加的情况类似，视情况而定。

trunc: 

        

        使用trunc函数对日期操作时，默认都是舍去时分秒，对天取整。

trunc可以有参数：

        

        参数是年取当年第一天0时，参数为月取当月第一天0时，参数为日取当天0时

        

        参数为时取当时0分，参数为分取当时当分0秒。

        注意DD和D的区别：

        

        DD是当天0时，D是当周第一天0时

last_day:

        

        当使用last_day求当月最后一天时，年月日是当月最后一天，具体时分秒也是执行SQL的当前时分秒。也就是本月最后一天的此时此刻。

next_day:

        

        当使用next_day求下个星期几时，年月日是下一个星期几的年月日，具体时分秒也是执行SQL的当前时分秒。也就是下个星期几的此时此刻。