SQL 函數

sql函數

character函數  String

1.INITCAP 首字母大小

      select initcap(address) address from student;

      select initcap('ni hao') from dual;   --dual虛擬表  '數據' 列名-數據

    2.LTRIM 從左往右截取

      select ltrim('ni hao ni', 'ni') from dual;    //hao ni

    3.RTRIM 從右往左截取

      select rtrim(‘hao ni hao', 'hao') from dual;  //hao ni

    4.CONCAT  並置運算符 || 

      select concat(stuNo, concat(' ', name)) from student;

      select stuNo || ' ' || name as info from student;

    5.SUBSTR  subString(str, begin, end)

      select substr('nihao hi helloi', 0, 10) from dual;   --包括第一位是0，也包括第10位

    6.INSTR

      select instr('nihao', 'ni') from dual;

    7.LENGTH

      select length(address) from student;

    8.RPAD

      insert into school values(rpad(88, 8, '0'), '南京大學');

---------------------------------------------------------------------------------------------------------------------------------

重要：   

   9.LOWER

      select lower('HELLO') from dual;

  10.UPPER 

      select upper('hello') from dual;

      String inputEmail = "Bob.wang";  -- 用戶名不區分大小寫

      select * from users where upper(email) = upper(inputEmail);

      select * from student where upper(address) = upper('China Wuxi New Area');

  11.LPAD  --  insert PK 序列sequence

      insert into school values(lpad(88, 8, '0'), '江蘇大學');

      insert into school values('S'||lpad(89, 7, '0'), '江蘇科技大學');

 ---------------------------------------------------------------------------------------------------------------------------------

 數值函數  Math

    1.ABS    絕對值

      select abs(-10) from dual;

    2.CEIL >= 最小整數

      select ceil(-10.1) from dual;

    3.FLOOR  <= 最大整數

      select floor(-10.1) from dual;

4.POWER  求冪

      select power(10, 4) from dual;

    5.MOD    取餘

      select mod(-10, 3) from dual;

    6.SQRT   開方

      select sqrt(100) from dual;

    7.TRUNC  直接截取

      select TRUNC(19.99) from dual; 

      select TRUNC(19.99281, 3) from dual;

    8.ROUND  四捨五入

      select round(19.99) from dual;        -- 20

      select round(19.99281, 3) from dual;  -- 19.993

日期函數

   1.系統時間

      select sysdate from dual;

   2. ADD_MONTHS  

計劃  2014-11-25開始project做兩個月 得出開始日期  結束日期

          select '25-11月-14' as "開始時間", add_months('25-11月-2014', 2) as "結束時間" from dual;

   3.GREATEST

      select greatest('25-11月-14', sysdate) from dual; 

 GREATEST 返回值列表中最大值

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

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

                          Value列表必須是相同類型，也可以是一個表的同一行、不同列的值進行比較。

                           當value值列表中有一個為NULL，則返回NULL值。

   4.LEAST

      select least('25-11月-14', sysdate) from dual; 

LEAST 返回值列表中最小值

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

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

                        value列表必須是相同類型，也可以是一個表的同一行、不同列的值進行比較。

                         當value值列表中有一個為NULL，則返回NULL值。

   5.LAST_DAY  月份最後一天日期

      select last_day(sysdate) from dual;

   6.MONTHS_BETWEEN返回兩個日期之間的月份數。

      select MONTHS_BETWEEN('25-11月-14', sysdate) from dual; 

7.NEXT_DAY 下一個星期幾日期  1~7  日~六

      select next_day(sysdate, 6) from dual;

   8.ROUND日期中的四捨五入   （上半月；下半月）

      select round(sysdate, 'month') from dual; 

   9.TRUNC  截取

      select trunc(sysdate, 'year') from dual;   //截取到年份 res：01-01月14

 
格式化函數

    1.隱式轉換

      此轉換類型中，數據類型將根據SELECT語句中函數的要求自動進行轉換。 

      select add_months('10-10月-14', 1) from dual;

      select * from school where schoolcode = '00000088';

      select * from facutly where facutlyNo = 1;   -- 列中數據應為數字的char字元

    2.顯式轉換 

      此轉換類型中，數據類型的轉換通過一些預定義函數完成。轉換函數有：

        日期格式化函數

          TO_DATE 

            insert into student(stuNo, name, birthDate, majorNo) 

                        values('S9722', 'John', to_date('1995-10-10', 'yyyy-mm-dd'), 'M0003');

          TO_CHAR 

            select birthDate from student;

            select to_char(birthDate, 'yyyy-mm-dd') from student;

  3.數值格式化函數

          to_number   -- 列中數據應為數字的char字元

          select to_number(schoolCode) from school where schoolcode = '00000088';

  4． nvl(值1, 值2)   若值1為空  顯示值2的數據   若值1不為空顯示自身值 

    select stuNo, name, nvl(javasescore, 60) from student;

  5．Coalesce函數  Coalesce (exp_name1, exp_name2….. exp_n)   若表達式值不為空顯示

    select coalesce('', '', '', '1', 'value') from dual;

/*

 * 多行函數 

 *  把多行數據組合為一行

 */

1. count 統計 count(*|列名)--列數據為null不參與多行函數操作
2. select count(*)from student;--有多少學生
3. select count(javaSEScore)from student;--有多收學生參與考試
4. select count(*)from student where majorno ='M0001';
5. sum
6. select sum(javaSEScore)from student;
7. select sum(salary)from employee;
8. avg
9. select avg(javaSEScore)from student;
10. max
11. select max(javaSEScore)from student;
12. min
13. select min(javaSEScore)from student;
14. stddev 偏差
15. select stddev(javaSEScore)from student;
16. variance 方差
17. select variance(javaSEScore)from student;
19. --select name, count(*)from student