SQL单行函数

两种SQL 函数:

单行函数

多行函数

单行函数:

操作数句对象

接受函数返回一个结果

只对一行进行变换

每行返回一个结果

可以转换数据类型

可以嵌套

参数可以是一列或一个值

单行函数包括:

字符函数,数值函数,日期函数,转换函数,通用函数

字符函数：

大小写控制函数---->这类函数改变字符的大小写LOWER('SQL Course') sql course

UPPER('SQL Course') SQL COURSE INITCAP('SQL Course') Sql Course

eg:

SELECT employee_id, last_name, department_id FROM employees

WHERE LOWER(last_name) = 'higgins';

字符控制函数--->这类函数控制字符

CONCAT

SUBSTR

LENGTH

INSTR

LPAD | RPAD

TRIM

REPLACE

函数结果

CONCAT('Hello', 'World') HelloWorld

SUBSTR('HelloWorld',1,5) Hello

LENGTH('HelloWorld') 10

INSTR('HelloWorld', 'W') 6

LPAD(salary,10,'*') *****24000

RPAD(salary, 10, '*') 24000*****

TRIM('H' FROM 'HelloWorld') elloWorld

SQL> select empno,concat(ENAME,JOB) name,length(ENAME),instr(ENAME,'I') "CONTAIN

S 'I'?" FROM emp;

EMPNO NAME LENGTH(ENAME) CONTAINS 'I'?

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

7369 SMITHCLERK 5 3

7499 ALLENSALESMAN 5 0

7521 WARDSALESMAN 4 0

7566 JONESMANAGER 5 0

7654 MARTINSALESMAN 6 5 ......

......

SQL> SELECT lpad(sal,10,'*') from emp;

LPAD(SAL,10,'*')

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

*******800

******1600

******1250

******2975

SQL> SELECT rpad(sal,10,'*') from emp;

RPAD(SAL,10,'*')

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

800*******

1600******

1250******

2975******

1250******

2850******

replace :

SQL> select * from a;

MAPNUM

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

001#001

001#002

002#002

SQL> select replace (mapnum,substr(mapnum,4,1),null) from a;

REPLACE(MAPNUM,SUBSTR(MAPNUM,4,1),NULL)

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

001001

001002

002002

数字函数:

ROUND: 四舍五入

ROUND(45.926, 2) 45.93

TRUNC: 截断

TRUNC(45.926, 2) 45.92

MOD: 求余

MOD(1600, 300) 100

ROUND 函数:

SQL> SELECT ROUND(45.923,2), ROUND(45.923,0),

2 ROUND(45.923,-1)

3 FROM DUAL;

ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)

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

45.92 46 50 DUAL 是一个‘伪表’，可以用来测试函数和表达式

SQL> SELECT ROUND(44.215,-1) from dual;

ROUND(44.215,-1)

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

40

SQL> SELECT ROUND(48.215,-1) from dual;

ROUND(48.215,-1)

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

50

SQL> SELECT ROUND(68.215,-1) from dual;

ROUND(68.215,-1)

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

70

TRUNC 函数:

SQL> SELECT TRUNC(45.923,2), TRUNC(45.923),

2 TRUNC(45.923,-2)

3 FROM DUAL;

TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-2)

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

45.92 45 0

SQL> SELECT TRUNC(445.923,-2) from dual;

TRUNC(445.923,-2)

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

400

SQL> SELECT TRUNC(555.923,-2) from dual;

TRUNC(555.923,-2)

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

500

MOD 函数:

SQL> SELECT empno,ename,sal,mod(sal,1000) from emp;

EMPNO ENAME SAL MOD(SAL,1000)

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

7369 SMITH 800 800

7499 ALLEN 1600 600

7521 WARD 1250 250

7566 JONES 2975 975

7654 MARTIN 1250 250

7698 BLAKE 2850 850

7782 CLARK 2450 450

7788 SCOTT 3000 0

7839 KING 5000 0

7844 TURNER 1500 500

7876 ADAMS 1100 100

EMPNO ENAME SAL MOD(SAL,1000)

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

7900 JAMES 950 950

7902 FORD 3000 0

7934 MILLER 1300 300

日期

Oracle 内部使用数字存储日期: 世纪,年,月,日,小时,分钟,秒。默认的日期格式是DD-MON-RR.

–可以只指定年的后两位在20世纪存放21世纪的日期。

–同样可以在21世纪存放20世纪的日期。

SQL> select HIREDATE from emp;

HIREDATE

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

17-12月-80

20-2月-81

22-2月-81

02-4月-81

28-9月-81

01-5月-81

09-6月-81

19-4月-87

17-11月-81

函数SYSDATE 返回:

日期

时间

日期的数学运算

在日期上加上或减去一个数字结果仍为日期。

两个日期相减返回日期之间相差的天数。

可以用数字除24来向日期中加上或减去小时。

SQL> select ename,(sysdate-HIREDATE)/7 as weeks from emp;

ENAME WEEKS

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

SMITH 1494.36653

ALLEN 1485.08081

WARD 1484.7951

JONES 1479.22367

MARTIN 1453.65224

BLAKE 1475.08081

CLARK 1469.50938

SQL> select ename,(sysdate-HIREDATE)/365 as weeks from emp; ENAME WEEKS

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

SMITH 28.659085

ALLEN 28.4810029

WARD 28.4755234

JONES 28.3686741

MARTIN 27.8782631

BLAKE 28.289222

CLARK 28.1823727

SCOTT 22.319359

日期函数:

MONTHS_BETWEEN 两个日期相差的月数

ADD_MONTHS 向指定日期中加上若干月数NEXT_DAY 指定日期的下一个日期(星期几）LAST_DAY 本月的最后一天

ROUND 日期四舍五入

TRUNC 日期截断

? MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')

19.6774194

? ADD_MONTHS ('11-JAN-94',6) '11-JUL-94'

? NEXT_DAY ('01-SEP-95','FRIDAY')

'08-SEP-95'

? LAST_DAY('01-FEB-95') '28-FEB-95'

? ROUND(SYSDATE,'MONTH') 01-AUG-95

? ROUND(SYSDATE ,'YEAR') 01-JAN-96

? TRUNC(SYSDATE ,'MONTH') 01-JUL-95

? TRUNC(SYSDATE ,'YEAR') 01-JAN-95

eg:

SQL> alter session set NLS_DATE_FORMAT='DD-MON-YY'; Session altered.

SQL> select sysdate from dual;

SYSDATE

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

14-OCT-09

SQL> select months_between('14-OCT-09','14-OCT-08') from dual;

MONTHS_BETWEEN('14-OCT-09','14-OCT-08')

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

12

SQL> select add_months('14-OCT-09',6) from dual;

ADD_MONTHS('

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

14-APR-10

SQL> select last_day(sysdate) from dual;

LAST_DAY(SYSDA

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

31-10月-09

SQL> select next_day(sysdate,3) from dual;--〉下个周二

NEXT_DAY(SYSDA

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

27-10月-09

SQL> select next_day('2009-10-20',4) from dual;

NEXT_DAY('2

-----------

2009-10-21 ---〉指定的时间2009-10-20为周二，4代表周三，那下个周三就是21号

1-7：7代表周六，6代表周五，....2代表周一，1代表周日

SQL> SELECT SYSdate from dual;

SYSDATE

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

07-8月-09

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

ROUND(SYSDATE,

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

01-8月-09

SQL> select round(sysdate,'year') from dual;

ROUND(SYSDATE,

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

01-1月-10

SQL> select trunc(sysdate,'year') from dual;

TRUNC(SYSDATE,

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

01-1月-09

转换函数：

数据类型转换---〉隐性显性

隐式数据类型转换：

TO_CHAR 函数对日期的转换

TO_CHAR(date, 'format_model')

格式:

? 必须包含在单引号中而且大小写敏感。

? 可以包含任意的有效的日期格式。

? 可以使用fm 去掉多余的空格或者前导零。? 与日期指用逗号隔开。

eg:

TO_CHAR 函数对日期的转换

SQL> select ename,to_char(hiredate,'fmDD Month yyyy') from emp;----->fm可以去除0

ENAME TO_CHAR(HIREDA

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

SMITH 17 12月1980

ALLEN 20 2月1981

WARD 22 2月1981

JONES 2 4月1981

TO_CHAR 函数对数字的转换

TO_CHAR(number, 'format_model')

下面是在TO_CHAR 函数中经常使用的几种格式:

9 数字

0 零

$ 美元符

L 本地货币符号

. 小数点

, 千位符

SQL> select to_char(sal,'$99,999.00') salary from emp;

SALARY

-----------

$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

嵌套函数

单行函数可以嵌套。

嵌套函数的执行顺序是由内到外。

通用函数:

这些函数适用于任何数据类型，同时也适用于空值：

NVL (expr1, expr2)

NVL2 (expr1, expr2, expr3)

NULLIF (expr1, expr2) 8

COALESCE (expr1, expr2, ..., exprn)

NVL 函数

将空值转换成一个已知的值：

可以使用的数据类型有日期、字符、数字。

函数的一般形式:

–NVL(commission_pct,0)

–NVL(hire_date,'01-JAN-97')

–NVL(job_id,'No Job Yet')

SQL> SELECT HIREDATE FROM EMP;

HIREDATE

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

17-DEC-80

20-FEB-81

22-FEB-81

02-APR-81

...

SQL> SELECT ename,sal,nvl(COMM,0),nvl(HIREDATE,'17-DEC-80') from emp; ENAME SAL NVL(COMM,0) NVL(HIREDATE

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

SMITH 800 0 17-DEC-80

ALLEN 1600 300 20-FEB-81

WARD 1250 500 22-FEB-81

JONES 2975 0 02-APR-81

MARTIN 1250 1400 28-SEP-81

BLAKE 2850 0 01-MAY-81

CLARK 2450 0 09-JUN-81

SCOTT 3000 0 19-APR-87

......

使用NVL2 函数:

SQL> select ename,comm,nvl2(comm,'sal+comm','sal') income from emp;

ENAME COMM INCOME

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

SMITH sal

ALLEN 300 sal+comm

WARD 500 sal+comm

JONES sal

MARTIN 1400 sal+comm

BLAKE sal

CLARK sal

SCOTT sal

KING sal

TURNER 0 sal+comm

ADAMS sal

.....

.....

使用NULLIF 函数:

返回类型

返回类型与第一个expression 相同。

如果两个表达式不相等，NULLIF 返回第一个expression 的值。如果相等，NULLIF 返回第一个expression 类型的空值。

SQL> select ename,length(ename) ,job,length(job),nullif(length(ename),length(job )) result from emp;

ENAME LENGTH(ENAME) JOB LENGTH(JOB) RESULT

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

SMITH 5 CLERK 5

ALLEN 5 SALESMAN 8 5

WARD 4 SALESMAN 8 4

JONES 5 MANAGER 7 5 MARTIN 6 SALESMAN 8 6

BLAKE 5 MANAGER 7 5

CLARK 5 MANAGER 7 5

SCOTT 5 ANALYST 7 5

KING 4 PRESIDENT 9 4

TURNER 6 SALESMAN 8 6

ADAMS 5 CLERK 5

ENAME LENGTH(ENAME) JOB LENGTH(JOB) RESULT

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

JAMES 5 CLERK 5

FORD 4 ANALYST 7 4

MILLER 6 CLERK 5 6

使用COALESCE 函数:

COALESCE 与NVL 相比的优点在于COALESCE 可以同时处理交替的多个值。如果第一个表达式费空,则返回这个表达式，对其他的参数进行COALESCE 。

SQL> select sal ,comm,deptno,empno,coalesce(sal,comm,deptno,empno) from emp;

SAL COMM DEPTNO EMPNO

COALESCE(SAL,COMM,DEPTNO,EMPNO)

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

20 7369

20

300 30 7499

300

...

...

条件表达式

在SQL 语句中使用IF-THEN-ELSE 逻辑。

使用两种方法:

–CASE 表达式

–DECODE 函数

CASE 表达式:

CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2

WHEN comparison_exprn THEN return_exprn

ELSE else_expr]

END

SQL> select ename,job,sal ,case job when 'CLERK' then 1.1*sal

2 when 'SALESMAN' then 1.2*sal

3 else sal end "re" from emp;

ENAME JOB SAL re

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

SMITH CLERK 800 880

ALLEN SALESMAN 1600 1920

WARD SALESMAN 1250 1500

JONES MANAGER 2975 2975 MARTIN SALESMAN 1250 1500

DECODE 函数:

DECODE(col|expression, search1, result1

[, search2, result2,...,]

[, default])

SQL> select ename,job,sal ,decode(job,'CLERK',1.1*sal, 2 'SALESMAN',1.2*sal,sal) re from emp;

ENAME JOB SAL RE ---------- --------- ---------- ----------

SMITH CLERK 800 880 ALLEN SALESMAN 1600 1920 WARD SALESMAN 1250 1500 JONES MANAGER 2975 2975 MARTIN SALESMAN 1250 1500 BLAKE MANAGER 2850 2850 CLARK MANAGER 2450 2450 SCOTT ANALYST 3000 3000 KING PRESIDENT 5000 5000 TURNER SALESMAN 1500 1800 ADAMS CLERK 1100 1210 ...

...