什么是SQL函数?

函数的分类

单行函数特点

  • 操作数据对象
  • 接受参数返回一个结果
  • 只对一行进行变换
  • 每行返回一个结果
  • 可以转换数据类型
  • 可以嵌套
  • 参数可以是一列或一个值

funtion_name[(arg1,arg2,...)]

单行函数分类

字符函数

大小写控制

--字符函数
--mysql
select lower('Hello World') 转小写,upper('Hello World') 转大写
from dual;

--oracle
select lower('Hello World') 转小写,upper('Hello World') 转大写,initcap('hello world') 首字母大写
from dual;

转小写      转大写      首字母大写                                              
----------- ----------- -----------                                             
hello world HELLO WORLD Hello World 

字符控制函数

函数 结果 说明
CONCAT("Hello","World") HelloWorld 拼接函数,连接两个参数
SUBSTR("HelloWorld",1,5) Hello 截取函数,参数2为开始索引,参数3为截取长度
LENGTH("HelloWorld") 10 获取字符串的长度
REPLACE("HelloWorld","l","x") HexxoWorxd 字符串替换,参数2为被替换的字符,参数3为替换字符
LENGTH("HelloWorld") 10 获取字符串的长度
...

SQL> --substr(a,b) 从a中,第b位开始取
SQL> select substr('Hello World',4) 子串 from dual;

子串                                                                            
--------                                                                        
lo World                                                                        

SQL> --substr(a,b,c) 从a中,第b位开始取,取c位
SQL> select substr('Hello World',4,3) 子串 from dual;

子串                                                                              
-------                                                                             
lo                                                                              

SQL> --length 字符数 lengthb 字节数
SQL> select length('Hello World') 字符,lengthb('Hello World') 字节 from dual;

      字符       字节                                                           
---------- ----------                                                           
        11         11                                                           
SQL> select length('北京') 字符,lengthb('北京') 字节 from dual

      字符       字节                                                           
---------- ----------                                                           
         2          4                                                           

SQL> --instr(a,b)
SQL> --在a中,查找b
SQL> select instr('Hello World','ll') 位置 from dual;

      位置                                                                      
----------                                                                      
         3                                                                      

SQL> --lpad 左填充  rpad 右填充
SQL> -- abcd  ---> 10位
SQL> select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右 from dual;

左         右                                                                   
---------- ----------                                                           
******abcd abcd******                                                           

SQL> --trim 去掉前后指定的字符
SQL> select trim('H' from 'Hello WorldH') from dual;

TRIM('H'FR                                                                      
----------                                                                      
ello World                                                                      

SQL> --replace
SQL> select replace('Hello World','l','*') from dual;

REPLACE('HE                                                                     
-----------                                                                     
He**o Wor*d        

数字函数

函数 结果 说明
ROUND(45.926,2) 45.93 四舍五入
TRUNC(45.926,2) 45.92 截断
MOD(1600,300) 100 求余
--四舍五入
select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三,round(45.926,-1) 四,round(45.926,-2) 五
from dual;

        一         二         三         四         五                          
---------- ---------- ---------- ---------- ----------                          
     45.93       45.9         46         50          0                          

--截断
select trunc(45.926,2) 一,trunc(45.926,1) 二,trunc(45.926,0) 三,trunc(45.926,-1) 四,trunc(45.926,-2) 五
from dual

        一         二         三         四         五                          
---------- ---------- ---------- ---------- ----------                          
     45.92       45.9         45         40          0           

日期函数

参考自网络,点此查看原文

--mysql
-- 获得当前日期+时间(date + time)函数:now(),sysdate()
-- now() 在执行开始时值就得到了, sysdate() 在函数执行时动态得到值
select now();
select sysdate();
-- 获得当前时间戳函数:current_timestamp, current_timestamp()
-- Date/Time to Str(日期/时间转换为字符串)函数:date_format(date,format), time_format(time,format)
select date_format('2018-08-08 22:23:01', '%Y%m%d%H%i%s');
--str_to_date(str,format) 把字符串转换为日期
select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30
--(日期、天数)转换函数:to_days(date), from_days(days)
--(时间、秒)转换函数:time_to_sec(time), sec_to_time(seconds)
...


--oracle
--当前时间
select sysdate from dual;
--29-9月 -16                                                                      
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
--2016-09-29 11:58:32                                                             
--昨天 今天  明天
select (sysdate-1) 昨天,sysdate 今天,(sysdate+1) 明天
from dual;

--计算员工的工龄:天 星期  月 年
select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,
(sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年
from emp;

ENAME      HIREDATE               天       星期         月         年           
---------- -------------- ---------- ---------- ---------- ----------           
SMITH      17-12月-80     13070.5013 1867.21448 435.683378 35.8095927           
ALLEN      20-2月 -81     13005.5013 1857.92876 433.516711 35.6315105           
WARD       22-2月 -81     13003.5013 1857.64305 433.450044  35.626031           
JONES      02-4月 -81     12964.5013 1852.07162 432.150044 35.5191817           
MARTIN     28-9月 -81     12785.5013 1826.50019 426.183378 35.0287708           
BLAKE      01-5月 -81     12935.5013 1847.92876 431.183378 35.4397297           
CLARK      09-6月 -81     12896.5013 1842.35733 429.883378 35.3328804           
SCOTT      19-4月 -87     10756.5013 1536.64305 358.550044 29.4698667           
KING       17-11月-81     12735.5013 1819.35733 424.516711 34.8917845           
TURNER     08-9月 -81     12805.5013 1829.35733 426.850044 35.0835653           
ADAMS      23-5月 -87     10722.5013  1531.7859 357.416711  29.376716    

--53个月后
select add_months(sysdate,53) from dual;
--last_day
select last_day(sysdate) from dual;
--next_day
--下一个星期四
select next_day(sysdate,'星期四') from dual;

转换函数

SQL> --查询员工的薪水:两位小数、千位符、本地货币代码
SQL> select to_char(sal,'L9,999.99') from emp;

TO_CHAR(SAL,'L9,999                                                             
-------------------                                                             
           ¥800.00                                                             
         ¥1,600.00                                                             
         ¥1,250.00                                                             
         ¥2,975.00                                                             
         ¥1,250.00                                                             
         ¥2,850.00                                                             
         ¥2,450.00                                                             
         ¥3,000.00                                                             
         ¥5,000.00                                                             
         ¥1,500.00                                                             
         ¥1,100.00      

通用函数

通用函数适用于任何数据类型,也适用于空值

--oracle
SQL> --nvl2(a,b,c) 当a=null的时候,返回c;否则返回b
SQL> select sal*12+nvl2(comm,comm,0) from emp;

SAL*12+NVL2(COMM,COMM,0)                                                        
------------------------                                                        
                    9600                                                        
                   19500                                                        
                   15500                                                        
                   35700                                                        
SQL>--nullif(a,b) 当a=b的时候,返回null;否则返回a
SQL> select nullif('abc','abc') 值 from dual;
值                                                                              
---                                                                                                                                                             
SQL> select nullif('abc','abcd') 值 from dual;
值                                                                              
---                                                                             
abc                                                                             

SQL> --coalesce 从左到右 找到第一个不为null的值
SQL> select comm,sal,coalesce(comm,sal) "第一个不为null的值"
  2  from emp;

      COMM        SAL 第一个不为null的值                                        
---------- ---------- ------------------                                        
                  800                800                                        
       300       1600                300                                        
       500       1250                500                                        
                 2975               2975                                        
      1400       1250               1400                                                                      
         0       1500                  0                                        
--mysql
...

条件表达式

SQL> --给员工涨工资,总裁1000 经理800 其他400

SQL> select ename,job,sal 涨前,
            case job when 'PRESIDENT' then sal+1000
                     when 'MANAGER' then sal+800
                     else sal+400
             end 涨后
     from emp;

ENAME      JOB             涨前       涨后                                                                                                                                                              
---------- --------- ---------- ----------                                                                                                                                                              
SMITH      CLERK            800       1200                                                                                                                                                              
ALLEN      SALESMAN        1600       2000                                                                                                                                                              
WARD       SALESMAN        1250       1650                                                                                                                                                              
JONES      MANAGER         2975       3775                                                                                                                                                              
MARTIN     SALESMAN        1250       1650                                                                                                                                                              
BLAKE      MANAGER         2850       3650                                                                                                                                                              
CLARK      MANAGER         2450       3250                                                                                                                                                              
SCOTT      ANALYST         3000       3400                                                                                                                                                              
KING       PRESIDENT       5000       6000                                                                                                                                                              
TURNER     SALESMAN        1500       1900                                                                                                                                                              
ADAMS      CLERK           1100       1500                                                                                     
JAMES      CLERK            950       1350                                                                                                                                                              
FORD       ANALYST         3000       3400                                                                                                                                                              
MILLER     CLERK           1300       1700        
最后修改于 2019-05-09 17:26:38
如果觉得我的文章对你有用,请随意赞赏
扫一扫支付
上一篇