- 浏览: 31635 次
- 性别:
- 来自: 北京
-
最新评论
一、Single-Row Functions(单行函数)
单行函数为每一行查询的表或视图返回一个单独的结果行，单行函数可出现 在SELECT列中、WHERE子句中、START WITH 和 CONNECT BY子句 中、HAVING子句中。
单行函数可操作多种数据类型：
1、Numeric Functions(数值函数)：用于操作数值类型的函数
常用的函数如下：
1.1、ABS(n)：该函数返回一个数值n的绝对值
SQL> SELECT ABS(-10) FROM dual;
ABS(-10)
----------
10
1.2、FLOOR(n)：该函数返回一个最大的等于或小于n的数值
SQL> SELECT FLOOR(12.8) FROM DUAL;
FLOOR(12.8)
-----------
12
SQL> SELECT FLOOR(-12.8) FROM DUAL;
FLOOR(-12.8)
------------
-13
1.3、MOD(n2,n1)：该函数返回n2除以n1所得的余数，如果n1为0
则返回n2
SQL> SELECT MOD(11,4) FROM DUAL;
MOD(11,4)
----------
3
SQL> SELECT MOD(11,0) FROM DUAL;
MOD(11,0)
----------
11
1.4、ROUND(n,integer)：该函数对数值n进行四舍五入运算后返回
SQL> SELECT ROUND(13.265,1) FROM DUAL;
ROUND(13.265,1)
---------------
13.3
SQL> SELECT ROUND(13.265,-1) FROM DUAL;
ROUND(13.265,-1)
----------------
10
SQL> SELECT ROUND(13.265,0) FROM DUAL;
ROUND(13.265,0)
---------------
13
SQL> SELECT ROUND(3.6) FROM DUAL;
ROUND(3.6)
----------
4
1.5、TRUNC(n1,n2)：该函数对数值n进行截断操作
SQL> SELECT TRUNC(13.68,1) FROM DUAL;
TRUNC(13.68,1)
--------------
13.6
SQL> SELECT TRUNC(13.68,-1) FROM DUAL;
TRUNC(13.68,-1)
---------------
10
SQL> SELECT TRUNC(13.68) FROM DUAL;
TRUNC(13.68)
------------
13
SQL> SELECT TRUNC(13.68,0) FROM DUAL;
TRUNC(13.68,0)
--------------
13
1.6、LOG(n2,n1)：该函数返回以n2为底n1的对数，其中n1必须
是一个大于0的正数，n2必须是大于1的正数
SQL> SELECT LOG(10,100) FROM DUAL;
LOG(10,100)
-----------
2
SQL>
SQL> SELECT LOG(10,1) FROM DUAL;
LOG(10,1)
----------
0
1.7、POWER(n2,n1)：该函数用于求n2的n1次方
SQL> SELECT POWER(3,3) FROM DUAL;
POWER(3,3)
----------
27
SQL>
SQL> SELECT POWER(-3,3) FROM DUAL;
POWER(-3,3)
-----------
-27
2、Character Functions Returning Character Values(字符函
数)：用于操作字符类型的函数并返回字符数据
常用的函数如下：
2.1、CONCAT(char1,char2)：该函数用于连接char1和char2
两个串，可嵌套使用
SQL> SELECT CONCAT('Hello',' World') "message" FROM DUAL;
message
-----------
Hello World
SQL>
SQL> SELECT CONCAT(CONCAT(ename,'的工作是'),job) "job"
2 FROM emp
3 WHERE empno='7369';
job
---------------------------
SMITH的工作是CLERK
2.2、POWER(char)：该函数将字符串首字母变为大写
SQL> SELECT INITCAP('HELLO WorLD') FROM DUAL;
INITCAP('HE
-----------
Hello World
2.3、LOWER(char)：该函数将字符串中的所有大写字母转化为
小写字母
SQL> SELECT LOWER('HELLO WORLD') FROM DUAL;
LOWER('HELL
-----------
hello world
2.4、UPPER(char)：该函数将字符串中的所有小写字母转化为
大写字母
SQL> SELECT LOWER('hello world') FROM DUAL;
LOWER('HELL
-----------
hello world
2.5、SUBSTR(char,position,substring_length)：该函数截
取一个char的字串，指定截取的开始位置，截取长度，如果不
指定截取长度则会从开始位置截取到字符串末尾，如果指定的
开始位置是一个正数，则从左边起第一个为1，如果指定的开
始位置是负数，则从右边起向左数第一个为-1
SQL> SELECT SUBSTR('HELLO WORLD',7) FROM DUAL;
SUBST
-----
WORLD
SQL>
SQL> SELECT SUBSTR('HELLOWORLD',1,3) FROM DUAL;
SUB
---
HEL
SQL>
SQL> SELECT SUBSTR('HELLOWORLD',-3,3) FROM DUAL;
SUB
---
RLD
2.6、TRIM(trim_character FROM trim_source)：该函数从
trim_source字符串中消除指定的trim_character字符串
但只能消除开头和末尾的指定字符
SQL> SELECT TRIM('H' FROM 'HELLO WORLD') FROM DUAL;
TRIM('H'FR
----------
ELLO WORLD
SQL> SELECT TRIM('D' FROM 'HELLO WORLD') FROM DUAL;
TRIM('D'FR
----------
HELLO WORL
2.7、LPAD(exp1,n,exp2)：该函数使用exp2表达式的值从左边
开始填充exp1表达式，填充后的总长度为n
SQL> SELECT LPAD('WORLD',10,'*') FROM DUAL;
LPAD('WORL
----------
*****WORLD
2.8、RPAD(exp1,n,exp2)：该函数使用exp2表达式的值从右边
开始填充exp1表达式，填充后的总长度为n
SQL> SELECT RPAD('WORLD',10,'*') FROM DUAL;
RPAD('WORL
----------
WORLD*****
3、Character Functions Returning Number Values(字符函
数)：用于操作字符类型的函数并返回数字类型的结果
常用的函数如下：
3.1、INSTR(string,substring,position,occurrence)：该函数
根据指定的position位置开始string中搜索第occurrence次 出现substring的位置
3.1.1、position：必须是一个数据类型为NUMBER的数或者
能够被隐式的转换为NUMBER类型，该值指明了
Oracle从string中搜索substring的起始位置，如果
指定的position是一个负数，那么Oracle将会从
string的最后一个字符开始计算起始位置（string的
最后一个字符索引为-1），如果不设置该position值
则默认值为1
3.1.2、occurrence：必须是一个数据类型为NUMBER的数
或者可以被隐式转化为NUMBER类型，该值指明了
Oracle从string中查找第position次出现substring
的位置，如果不设置该occurrence值则默认值为1(
该occurrence的值必须大于0，否则会报参数超出范
围的错误)
如果查找到指定的substring，则返回该字串第occurrence次出现
的位置，若不存在则返回0
SQL> SELECT INSTRB('CORPORATE FLOOR','OR',3,2) "Substring occurrence index"
2 FROM DUAL;
Substring occurrence index
--------------------------
14
SQL> SELECT INSTRB('CORPORATE FLOOR','OR') "Substring occurrence index"
2 FROM DUAL;
Substring occurrence index
--------------------------
2
SQL> SELECT INSTRB('CORPORATE FLOOR','OR',-2) "Substring occurrence index"
2 FROM DUAL;
Substring occurrence index
--------------------------
14
3.2、INSTR(char)：该函数用于计算char字符串的长度，如果char是
null，则会返回一个null
SQL> SELECT LENGTH('HELLO WORLD') "Length in char" FROM DUAL;
Length in char
--------------
11
SQL> SELECT LENGTH('') "Length in char" FROM DUAL;
Length in char
--------------
4、Datetime Functions(日期函数)：用于操作日期类型的数据
常用的函数如下：
4.1、ADD_MONTHS(date,integer)：该函数返回一个在date
日期上加上一个integer类型的月的日期类型，如果date这个
日期是本月的最后一天或者加上integer月后生成的这个月的
天数比date中月的天数少，则返回生成的新月份的最后一天(
比如：'31-1月-12'，31是本月的最后一天，如果让该日期加
上1个月则到了2月，2月有29天，则显示效果为'29-2月12')
否则，返回与date的天数相同的日期类型
SQL> SELECT ADD_MONTHS('31-1月-12',1) "Next Month" FROM DUAL;
Next Month
--------------
29-2月 -12
SQL> SELECT ADD_MONTHS('23-1月-12',1) "Next Month" FROM DUAL;
Next Month
--------------
23-2月 -12
4.2、LAST_DAY(date)：该函数用于返回date这个日期中当前月
的最后一天所在的日期值
SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL; LAST_DAY(SYSDA -------------- 31-3月 -12
4.3、MONTHS_BETWEEN(date1,date2)：该函数返回date1 到date2之间相隔的月份值，如果date1迟于date2则返回一
个正数，如果date1早于date2则返回一个负数；如果date1
和date2是它们各自所在日期的月份中的同一天或者是各自
月份中的最后一天，则会返回一个integer类型的数字，否则
会返回一个小数，Oracle数据库会以每月31天为基准来计算
这个小数代表的月份
SQL> SELECT MONTHS_BETWEEN(
2 TO_DATE('2012-06-12','YYYY-MM-DD'),
3 TO_DATE('2012-04-12','YYYY-MM-DD')
4 ) "Months"
5 FROM DUAL;
Months
----------
2
SQL> SELECT MONTHS_BETWEEN(
2 TO_DATE('2012-04-30','YYYY-MM-DD'),
3 TO_DATE('2012-03-31','YYYY-MM-DD')
4 ) "Months"
5 FROM DUAL;
Months
----------
1
SQL> SELECT MONTHS_BETWEEN(
2 TO_DATE('2012-04-23','YYYY-MM-DD'),
3 TO_DATE('2012-03-12','YYYY-MM-DD')
4 ) "Months"
5 FROM DUAL;
Months
----------
1.35483871
SQL> SELECT MONTHS_BETWEEN(
2 TO_DATE('2012-03-23','YYYY-MM-DD'),
3 TO_DATE('2012-05-12','YYYY-MM-DD')
4 ) "Months"
5 FROM DUAL;
Months
----------
-1.6451613
4.4、NEXT_DAY(date,char)：该函数用于返回date这个日期中
指定月份的下一个工作日所在的日期
SQL> SELECT NEXT_DAY('02-2月-2001','星期二') "NEXT DAY"
2 FROM DUAL;
NEXT DAY
--------------
06-2月 -01
SQL> SELECT NEXT_DAY('04-3月-2012','星期日') "NEXT DAY"
2 FROM DUAL;
NEXT DAY
--------------
11-3月 -12
4.5、SYSDATE(date)：该函数返回当前操作系统的日期
SQL> SELECT SYSDATE FROM DUAL; SYSDATE -------------- 22-3月 -12
4.6、ROUND(date,format)：该函数返回对当前date日期进行
格式处理后的日期：
4.6.1、format的值为'YYYY'时，如果date日期中的月份
大于6月，则会将该date日期置为下一年的1月1日
否则，会将date日期置为该年的1月1日
4.6.2、format的值为'MONTH'时，如果date日期中的天
数大于15，则会将该date日期置为该年的该月的下
一个月的1号；否则，会将该date日期置为该年该月
的1号
SQL> select round(to_date('2012-06-12','YYYY-MM-DD'),'YYYY') from dual;
ROUND(TO_DATE(
--------------
01-1月 -12
SQL> select round(to_date('2012-07-12','YYYY-MM-DD'),'YYYY') from dual;
ROUND(TO_DATE(
--------------
01-1月 -13
SQL> select round(to_date('2012-03-15','YYYY-MM-DD'),'MONTH') from dual;
ROUND(TO_DATE(
--------------
01-3月 -12
SQL> select round(to_date('2012-03-16','YYYY-MM-DD'),'MONTH') from dual;
ROUND(TO_DATE(
--------------
01-4月 -12
4.7、TRUNC(date,format)：该函数返回当前date日期进行格式
处理后的日期
4.7.1、format值为'YYYY'时，此时会将该date日期置为该
年的1月1日
4.7.2、format值为'MONTH'时，此时会将date日期置为该
该年该月的1号
SQL> SELECT TRUNC(TO_DATE('2012-04-13','YYYY-MM-DD'),'YYYY') FROM DUAL;
TRUNC(TO_DATE(
--------------
01-1月 -12
SQL> SELECT TRUNC(TO_DATE('2012-07-13','YYYY-MM-DD'),'YYYY') FROM DUAL;
TRUNC(TO_DATE(
--------------
01-1月 -12
SQL> SELECT TRUNC(TO_DATE('2012-03-13','YYYY-MM-DD'),'MONTH') FROM DUAL;
TRUNC(TO_DATE(
--------------
01-3月 -12
SQL> SELECT TRUNC(TO_DATE('2012-08-13','YYYY-MM-DD'),'MONTH') FROM DUAL;
TRUNC(TO_DATE(
--------------
01-8月 -12
4.8、TO_CHAR(date,format)：该函数返回当前date日期进行
格式处理后的日期
SQL> SELECT TO_CHAR(SYSDATE,'DD-MM-YYYY') FROM DUAL; TO_CHAR(SY ---------- 22-03-2012 SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL; TO_CHAR(SY ---------- 2012-03-22 SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH:SS') FROM DUAL; TO_CHAR(SYSDATE, ---------------- 2012-03-22 12:20 SQL> SELECT TO_CHAR(SYSDATE,'HH24:MI:SS AM') FROM DUAL; TO_CHAR(SYSDA ------------- 00:49:25 上午 SQL> SELECT TO_CHAR(SYSDATE,'HH24:MI:SS AM') FROM DUAL; TO_CHAR(SYSDA ------------- 00:49:30 上午
5、Conversion Functions(日期函数)：用于完成数据类型的转换
转换的方式有一下两种：
5.1、显示转换：
5.2、隐式转换：
常用的转化函数如下：
5.1.1、TO_NUMBER(expr,format)：使用指定的format格式
格式化expr，返回一个number值，该函数属于显示显示
转换函数
SQL> SELECT TO_NUMBER('300.0','9G999D99') FROM DUAL;
TO_NUMBER('300.0','9G999D99')
-----------------------------
300
5.1.2、TO_DATE(expr,format)：同4.8中，该函数属于显示
转换函数
6、Generation Functions(通用函数)：这些函数适用于任何数据类
型，同时也适用于空值
6.1、NVL(expr1,expr2)：下面举例将emp表中得comm为空
的值替换为0
SQL> SELECT empno,ename,job,sal,NVL(comm,0) FROM EMP;
EMPNO ENAME JOB SAL NVL(COMM,0)
---------- ---------- --------- ---------- -----------
7369 SMITH CLERK 800 0
7499 ALLEN SALESMAN 1600 300
7521 WARD SALESMAN 1250 500
7566 JONES MANAGER 2975 0
7654 MARTIN SALESMAN 1250 1400
7698 BLAKE MANAGER 2850 0
7782 CLARK MANAGER 2450 0
7788 SCOTT ANALYST 3000 0
7839 KING PRESIDENT 5000 0
7844 TURNER SALESMAN 1500 0
7876 ADAMS CLERK 1100 0
7900 JAMES CLERK 950 0
7902 FORD ANALYST 3000 0
7934 MILLER CLERK 1300 0
已选择14行。
6.2、NVL2(expr1,expr2,expr3)：该函数对NVL进行了增强
当expr1=null时，返回expr3;否则返回expr2
SQL> SELECT NVL2(NULL,'A','B') FROM DUAL;
N
-
B
SQL> SELECT NVL2('HH','A','B') FROM DUAL;
N
-
A
6.3、NULLIF(expr1,expr2)：当expr1=expr2时，返回null
否则返回expr1
SQL> SELECT NULLIF('A','A') FROM DUAL;
N
-
SQL> SELECT NULLIF('A','B') FROM DUAL;
N
-
A
6.4、COALESCE(expr1,expr2)：如果expr1不为null，则显示
expr1否则显示expr2
SQL> SELECT COALESCE(NULL,'A') FROM DUAL;
C
-
A
SQL> SELECT COALESCE('S','C') FROM DUAL;
C
-
S
SQL> select ename,sal,comm,coalesce(comm,sal) from emp; ENAME SAL COMM COALESCE(COMM,SAL) ---------- ---------- ---------- ------------------ SMITH 800 800 ALLEN 1600 300 300 WARD 1250 500 500 JONES 2975 2975 MARTIN 1250 1400 1400 BLAKE 2850 2850 CLARK 2450 2450 SCOTT 3000 3000 KING 5000 5000 TURNER 1500 0 0 ADAMS 1100 1100 JAMES 950 950 FORD 3000 3000 MILLER 1300 1300 已选择14行。
二、Aggregate Functions(统计函数)
统计函数基于一组数据返回一个值，统计函数可以出现在SELECT列表、
ORDER BY 子句、HAVING子句
常用的统计函数如下：
1、AVG(expr)：该函数用于求出指定的expr表达式的平均值，expr
必须是一个数字类型或者可以被隐式的转换为数字类型
SQL> select deptno,avg(sal+nvl(comm,0))
2 from emp
3 group by deptno;
DEPTNO AVG(SAL+NVL(COMM,0))
---------- --------------------
30 1933.33333
20 2191
10 2916.66667
2、COUNT(expr)：该函数用于求出指定的expr表达式出现的次数
SQL> select deptno,count(*) "人数"
2 from emp
3 group by deptno
4 order by deptno;
DEPTNO 人数
---------- ----------
10 3
20 5
30 6
3、MAX(expr)：该函数用于求出指定的expr表达式的最大值
SQL> select empno,ename,job,max(sal) "最高工资"
2 from emp
3 where sal=(select max(sal) from emp)
4 group by empno,ename,job;
EMPNO ENAME JOB 最高工资
---------- ---------- --------- ----------
7839 KING PRESIDENT 5000
4、MIN(expr)：该函数用于求出指定的expr表达式的最小值
SQL> select empno,ename,job,min(sal) "最低工资"
2 from emp
3 where sal=(select min(sal) from emp)
4 group by empno,ename,job;
EMPNO ENAME JOB 最低工资
---------- ---------- --------- ----------
7369 SMITH CLERK 880
5、SUM(expr)：该函数用于对给出的expr表达式求和
SQL> select deptno,sum(sal+nvl(comm,0)) "总工资"
2 from emp
3 group by deptno;
DEPTNO 总工资
---------- ----------
30 11600
20 10955
10 8750
发表评论
相关推荐
第7章 sql单行函数 第8章 操纵数据 第9章 复杂查询 第10章 管理常用对象 第三部分 pl/sql 第11章 pl/sql基础 第12章 访问oracle 第13章 编写控制结构 第14章 使用复合数据类型 第15章 使用游标 ...
Oracle数据库实践开发教程，便于初学者学习，培训的好教材，包括数据库安装、简单查询、限定查询与排序显示、单行函数、多表查询、分组统计查询、子查询等。
第7章SQL单行函数 第8章操纵数据 第9章复杂查询 第10章管理常用对象 第三部分PL/SQL 第11章PL/SQL基础 第12章访问Oracle 第13章编写控制结构 第14章使用复合数 据类型 第15章使用游标 第16章...
第7章 sql单行函数 第8章 操纵数据 第9章 复杂查询 第10章 管理常用对象 第三部分 pl/sql 第11章 pl/sql基础 第12章 访问oracle 第13章 编写控制结构 第14章 使用复合数据类型 第15章 使用游标 ...
第7章 sql单行函数 第8章 操纵数据 第9章 复杂查询 第10章 管理常用对象 第三部分 pl/sql 第11章 pl/sql基础 第12章 访问oracle 第13章 编写控制结构 第14章 使用复合数据类型 第15章 使用游标 ...
第7章 sql单行函数 第8章 操纵数据 第9章 复杂查询 第10章 管理常用对象 第三部分 pl/sql 第11章 pl/sql基础 第12章 访问oracle 第13章 编写控制结构 第14章 使用复合数据类型 第15章 使用游标 ...
内容分为2大部分，1-13章为oracle sql，介绍了sql*plus的使用，基本查询语句，单行函数，多表查询，组函数和分组统计。。。。。。
六、 单行函数的使用 11 6.1数值类型 11 6.2日期类型 11 6.3字符类型 13 6.4转换函数 14 6.5其他注意事项 14 七、 SQL语句中的分支 15 7.1分支表达式 15 7.2分支函数 15 八、 组函数 16 8.1报表统计常用 16 8.2缺省...
第10章 单行函数与转换函数 347 10.1 描述和使用SQL中的字符、数字和日期函数 348 10.1.1 定义函数 348 10.1.2 函数类型 348 10.1.3 使用大小写转换函数 349 10.1.4 使用字符操作函数 350 10.1.5 使用数字...
2，having：子查询会返回单行单列，同时表示要使用统计函数； 3，from：子查询返回多行多列数据（表结构）； 4，select：返回单行单列 （一般不使用）； 示例详解 where（进行数据行的筛选操作）： a:查询出低于公司...
教程名称：Oracle SQL入门级PPT教程汇总课程目录：【】第1章 简单查询语句【】第2章 限定查询和排序【】第3章 单行函数【】第4章 多表查询【】第5章 组函数和分组统计 资源太大，传百度网盘了，链接在附件中，有需要...
5.统计函数——列名应指定别名 35 6. Group By分组 36 7.Having子句 36 8.练习：表的查询 36 五． 在SQL *Plus中使用函数 37 1.字符串函数 37 2.数字函数 37 3.日期时间函数 38 4.转换函数 38 第五讲 修改SQL数据与...
3.2.5 单行函数 (重点) 26 3.2.6 SQL语句强化训练 39 3.2.7 多表查询 40 3.2.8 组函数及分组统计 (重点) 52 3.2.9 子查询 58 3.2.10 ROWNUM伪列(重点) 63 3.2.11 集合操作 65 3.2.12 连接查询及分组查询强化训练 68 ...
5.统计函数——列名应指定别名 35 6. Group By分组 36 7.Having子句 36 8.练习：表的查询 36 五． 在SQL *Plus中使用函数 37 1.字符串函数 37 2.数字函数 37 3.日期时间函数 38 4.转换函数 38 第五讲 修改SQL数据与...
第10章 单行函数与转换函数 347 10.1 描述和使用SQL中的字符、数字和日期函数 348 10.1.1 定义函数 348 10.1.2 函数类型 348 10.1.3 使用大小写转换函数 349 10.1.4 使用字符操作函数 350 10.1.5 使用数字...
八、单行函数： 函数一般在数据上执行，它给数据的转换和处理提供了方便。不同的DBMS提供的函数不同。 函数可能会带来系统的不可移植性(可移植性:所编写的代码可以在多个系统上运行)。 加入注释是一个使用函数的...
注意：alter modify不支持一次修改多个列，但是Oracle支持多列修改 但是MySQL可以通过多个modify的方式完成： alter table user modify tel varchar(15) default '02087654321' first, modify name varchar(20) ...
12.4.2 单行输出 194 12.4.3 PHP动态生成JavaScript的 12.4.3 实例——进度条 194 12.5 在JavaScript中调用PHP程序 196 12.5.1 页面的跳转 196 12.5.2 隐性调用PHP程序 196 12.6 JavaScript和PHP的综合实例——表单 ...
0893 使用一个单行的子查询来更新列 524 0894 使用IN引入子查询限定查询范围 524 0895 在UPDATE语句中应用子查询 525 0896 如何应用子查询 525 0897 EXISTS与子查询联合应用 525 0898 在FROM子句中的子...