Oracle 常用SQL语句

TRUNC：

有两种：TRUNC（NUMBER）表示截断数字，TRUNC（date）表示截断日期。

1)截断数字：

格式：TRUNC（n1,n2），n1表示被截断的数字，n2表示要截断到那一位。n2可以是负数，表示截断小数点前。注意，TRUNC截断不是四舍五入。

SQL> select TRUNC(15.79) from dual;

TRUNC(15.79) ------------ 15

SQL> select TRUNC(15.79,1) from dual;

TRUNC(15.79,1) -------------- 15.7

SQL> select trunc(15.79,-1) from dual;

TRUNC(15.79,-1) --------------- 10

(2)截断日期：

先执行命令：alter session set nls_date_format='yyyy-mm-dd hh24:mi:hh';

1、截取今天：

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

SYSDATE TRUNC(SYSDATE,‘DD’) ------------------- 2009-03-24 21:31:17 2009-03-24 00:00:00

2、截取本周第一天：

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

SYSDATE TRUNC(SYSDATE,‘D’) ------------------- 2009-03-24 21:29:32 2009-03-22 00:00:00

3、截取本月第一天：

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

SYSDATE TRUNC(SYSDATE,‘MM’)------------------- 2009-03-24 21:30:30 2009-03-01 00:00:00

4、截取本年第一天：

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

SYSDATE TRUNC(SYSDATE,‘Y’)------------------- 2009-03-24 21:31:57 2009-01-01 00:00:00

5、截取到小时：

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

SYSDATE TRUNC(SYSDATE,‘HH’) ------------------- 2009-03-24 21:32:59 2009-03-24 21:00:00

6、截取到分钟：

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

SYSDATE TRUNC(SYSDATE,‘MI’) ------------------- 2009-03-24 21:33:32 2009-03-24 21:33:00

7、获取上月第一天：
SQL> select TRUNC(add_months(SYSDATE,-1),'MM') from dual

===================================================================

–Oracle trunc()函数的用法
日期

1.select trunc(sysdate) from dual --2011-3-18 今天的日期为2011-3-18
2.select trunc(sysdate, 'mm') from dual --2011-3-1 返回当月第一天.
3.select trunc(sysdate,'yy') from dual --2011-1-1 返回当年第一天
4.select trunc(sysdate,'dd') from dual --2011-3-18 返回当前年月日
5.select trunc(sysdate,'yyyy') from dual --2011-1-1 返回当年第一天
6.select trunc(sysdate,'d') from dual --2011-3-13 (星期天)返回当前星期的第一天
7.select trunc(sysdate, 'hh') from dual --2011-3-18 14:00:00 当前时间为14:41
8.select trunc(sysdate, 'mi') from dual --2011-3-18 14:41:00 TRUNC()函数没有秒的精确
 

数字
TRUNC（number,num_digits）
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
TRUNC()函数截取时不进行四舍五入

9.select trunc(123.458) from dual --123
10.select trunc(123.458,0) from dual --123
11.select trunc(123.458,1) from dual --123.4
12.select trunc(123.458,-1) from dual --120
13.select trunc(123.458,-4) from dual --0
14.select trunc(123.458,4) from dual --123.458
15.select trunc(123) from dual --123
16.select trunc(123,1) from dual --123
17.select trunc(123,-1) from dual --120
 

 

1. 当前系统日期、时间
select getdate()
2. dateadd 在向指定日期加上一段时间的基础上，返回新的 datetime 值
例如：向日期加上2天
select dateadd(day,2,'2004-10-15') --返回：2004-10-17 00:00:00.000

3. datediff 返回跨两个指定日期的日期和时间边界数。
select datediff(day,'2004-09-01','2004-09-18') --返回：17

4. datepart 返回代表指定日期的指定日期部分的整数。
SELECT DATEPART(month, '2004-10-15') --返回 10

5. datename 返回代表指定日期的指定日期部分的字符串
SELECT datename(weekday, '2004-10-15') --返回：星期五

6. day(), month(),year() --可以与datepart对照一下

select 当前日期=convert(varchar(10),getdate(),120)
,当前时间=convert(varchar(8),getdate(),114)

7. select datename(dw,'2004-10-15')

select 本年第多少周=datename(week,getdate())
,今天是周几=datename(weekday,getdate())

函数 参数/功能
GetDate( ) --返回系统目前的日期与时间
DateDiff (interval,date1,date2) --以interval 指定的方式，返回date2 与date1两个日期之间的差值 date2-date1
DateAdd (interval,number,date) --以interval指定的方式，加上number之后的日期
DatePart (interval,date) ---返回日期date中，interval指定部分所对应的整数值
DateName (interval,date) --返回日期date中，interval指定部分所对应的字符串名称

 

1、数值型常用函数

ceil(n) 大于或等于数值n的最小整数　　
floor(n) 小于等于数值n的最大整数　 
mod(m,n) m除以n的余数,若n=0,则返回m 
power(m,n) m的n次方　　　　　　　　
round(n,m) 将n四舍五入,保留小数点后m位　　
sign(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1 
sqrt(n) n的平方根　

2、常用字符函数 

initcap(char) 把每个字符串的第一个字符换成大写　　
lower(char) 整个字符串换成小写　　　　　　　　
replace(char,str1,str2) 字符串中所有str1换成str2 
substr(char,m,n) 取出从m字符开始的n个字符的子串　
length(char) 求字符串的长度　　

3、日期型函数

sysdate 当前日期和时间
last_day 　本月最后一天 
add_months(d,n)　当前日期d后推n个月 
months_between(d,n) 日期d和n相差月数 
next_day(d,day) d后第一周指定day的日期 

5、字符函数 

字符函数主要用于修改字符列。这些函数接受字符输入，返回字符或数字值。Oracle 提供的一些字符函数如下。 
1. CONCAT (char1, char2)  返回连接“char2”的“char1”。 
2. INITCAP(string)  将“string”的字符转成大写。 
3. LOWER (string) 
4. LPAD(char1,n [,char2]) ：返回“char1”，左起由“char2”中的字符补充到“n”个字符长。如果“char1”比“n”长，则函数返回“char1”的前“n”个字符。 
5. LTRIM(string,trim_set)  ：从左边删除字符，此处“string”是数据库的列，或者是字面字符串，而“trim_set” 是我们要去掉的字符的集合。 
6. REPLACE(string, if, then)  用 0 或其他字符代替字符串中的字符。“if”是字符或字符串，对于每个出现在“string”中的“if”，都用“then”的内容代替。 
7. RPAD(char1, n [,char2])  返回“char1”，右侧用“char2”中的字符补充到“n”个字符长。如果 “char1”比“n” 长，则函数返回“char1”的前“n”个字符。 
8. RTRIM(string,trim_set)  从右侧删除字符，此处“string”是数据库的列，或者是字面字符串，而“trim_set” 是我们要去掉的字符的集合。 
9. SOUNDEX(char)  返回包含“char”的表意字符的字符串。它允许比较英语中拼写不同而发音类似的字。 
10. SUBSTR(string, start [,count]) 返回“string”中截取的一部分。该命令截取“string”的一个子集，从“start”位置开始，持续“count”个字符。如果我们不指定“count”，则                                              从“start”开始截取到“string”的尾部。
11. TRANSLATE(string, if, then)  “if”中字符的位置，并检查“then”的相同位置，然后用该位置的字符替换 “string”中的字符。 
12. UPPER(string) 返回大写的“string”。 

13. ASCII(string) 该命令是“American Standard Code for Information Interchange”的缩写。它是使用数字表示可打印字符的基本规则。该函数返回 “string”中第一个（最左边）字符的 ASCII 值。 
14. INSTR (string, set[, start[, occurrence] ] ) 
该命令“string”中从“start”位置开始查找字符集合的位置，再查找“set”出现的第一次、第二次等等的“occurrence”（次数）。“start”的值也可以是负数，代表从字符串结尾开始向反方向搜索。该函数也用于数字和日期数据类型。 
15. LENGTH(string) 
返回“string”的长度值。

 

一、Oracle的函数

Oracle提供了很多函数用于数据的处理、统计和转换，这些函数增强了SQL语言的功能。

Oracle的函数分为单行函数、聚合函数和分析函数三大类。

1、单行函数

单行函数应用于SQL语句中时，只能输入一个数据，返回一个结果，常用的单行函数包括字符串函数、数字函数、日期函数、转换函数。

2、聚合函数

聚合函数应用于SQL语句中时，同时对多行数据进行操作，返回一个结果，例如求结果集的记录数、最小值、最大值、平均值、统计值等。

3、分析函数

分析函数用于计算基于分组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。

分析函数的应用场景比较少，概念难以理解，本文就不介绍了。

二、字符串函数

字符串函数接受字符串参数，参数可以是表中的列名，也可以是字符串表达式或字符串常量。

1、ASCII码转换ascii和chr

ascii(str)：返回字符串str第一个字符的ASCII码值。

chr(ii)：返回ASCII码为数字ii的字符。

2、字符串拼接concat

concat(str1,str2)：把字符串str1和str2拼接成一个字符串，在实际应用中，我们更愿意用||拼接字符串，因为||可以拼接多个字符串。

3、字符串查找instr

instr(str1,str2,start,n)：在字符串str1中查找str2。

start：从str1的哪个位置开始查找，可选参数，缺省为1。如果start为正数，从左到右查找，如果start为负数，从右到左查找。

n表示要查找第几次出现的str2，可选参数，缺省为1，如果为负数系统会报错。

4、字符串的长度length

length (str)：返回字符串str的长度。

5、字符串大小写转换lower和upper

lower (str)：把字符串str转换为小写，忽略不是字母的字符。

upper (str)：把字符串str转换为大写，忽略不是字母的字符。

6、截去字符串ltrim、rtrim和trim

ltrim(str1,str2)：从字符串str1的左边截去字符串str2，str2是可选参数，缺省为空格。

rtrim(str1,str2)：从字符串str1的右边截去字符串str2，str2是可选参数，缺省为空格。

trim(leading|trailing|both chr from str)：从str中截去 左侧|右侧|两侧的chr字符，缺省是both（两侧），注意，chr是单个字符，不是字符串，缺省是空格。

7、字符串替换replace

replace(str,old,new)：把字符串str中的old字符串替换成new字符串。

8、字符串截取substr

substr(str,start,len)：从字符串str的start位置开始，截取len个字符，如果len不填就截取start之后全部的字符。

9、字符串补齐lpad的rpad

lpad(str,len,[chr])，从左边补齐。

rpad(str,len,[chr])，从右边补齐。

str：待补齐的原字符串。

len：最终返回的字符串的长度，如果最终返回的字符串的长度比源字符串的小，那么此函数将对源串进行截断处理。

chr：用于填充的字符，缺省为空字符。

三、数字函数

数字函数接受数字参数，参数可以是表中的列名，也可以是数字表达式或数字常量。

函数	说明	示例
abs(x)	x绝对值。	abs(-10)=10
cos(x)	余弦。	cos(1)= 0.540302306
acos(x)	x的反余弦。	acos(1)=0
ceil(x)	大于或等于x的最小整数。	ceil(3.14)=4
floor(x)	小于或等于x的最大整数。	floor(3.14)=3
log(x,y)	x为底y的对数。	log(2,4)=2
mod(x,y)	x除以y的余数。	mod(7,3)=1
power(x,y)	x的y次幂。	power(2,4)=16
round(x,y)	x在第y小数位四舍五入。	round(3.1415,3)=3.142
sign(x)	判断x的值，大于0返回1，等于0返回0，小于0返回-1。	sign(100)=1、sign(0)=0、sign(-100)=-1
sqrt(x)	x的平方根。	sqrt(9)=3
trunc(x,y)	x在第y位截断。	trunc(3.14159,3)=3.141

补充说明：

1）round函数

round(x[,y])，四舍五入函数。

y的缺省值0，例如：round(3.14)=3、round(3.56)=4。

y是正整数，四舍五入到小数点后y位，例如：round(3.14159,3)=3.142。

y是负整数，四舍五入到小数点左边y位，round(314.159,-2)=300。

2）trunc函数

trunc(x[,y])，直接截断，不四舍五入。

y的缺省值0，例如：trunc(3.14)=3。

y是正整数，小数点y位后截断，trunc(3.141592,3)=3.141。

y是负整数，小数点y位前截断。trunc(314.159,-2)=300。

四、日期函数

Oracle的日期函数极其重要，在《Oracle日期函数》文章中有详细的说明。

五、转换函数

Oracle的数据类型转换主要有三种：1）日期与字符串；2）rowid与字符串；3）数字与字符串。

1、日期和字符串之间的转换

日期与字符串的转换用to_date和to_char函数完成，在《Oracle日期函数》文章中有详细的说明。

2、rowid和字符串之间的转换

Oracle数据库能隐式的在rowid和字符串之间的转换。

3、数字和字符串之间的转换

Oracle数据库能隐式的在数字和字符串之间的转换，但是要注意几个问题：

1）任意数字都可以转换成字符串。

2）只有包含了合法数字的字符串才能转换为数字，否则提示错误。

3）空字符串转换为数字后是null，不是0，这个特点太棒了。

还有，Oracle提供了to_number函数把字符串转换为数字，to_number支持多种格式，用法比较复杂，不建议使用。

六、其它的单行函数

1、nvl函数

NVL2(x,value)

如果x为空，返回value，否则返回x。

示例，运行以下脚本生成测试数据：

create table tt(name varchar2(20),age number(3));
insert into tt values('西施',20);
insert into tt values('杨玉环',null);
insert into tt values(null,18);
select nvl(name,'匿名美女'),nvl(age,0) from tt;

2、decode函数

decode(条件,值1,返回值1,值2,返回值2,......,值n,返回值n,缺省值)

该函数的含义如下：

if (条件==值1) return 返回值1;
else if (条件==值2) return 返回值2;
else if (条件==值3) return 返回值3;
  ......
else return 缺省值;

示例，运行以下脚本生成测试数据：

create table tt(name varchar2(20),yz number(1),sc number(1));
insert into tt values('西施',1,2);
insert into tt values('杨玉环',2,1);
insert into tt values('夏姬',3,3);
insert into tt values('芙蓉姐姐',4,4);
select name,decode(yz,1,'漂亮',2,'可爱',3,'狐媚','不合格'),decode(sc,1,'丰满',2,'火辣',3,'苗条','不及格') from tt;

3、userenv函数

userenv(string)，获取当前用户会话的信息。

string的取值如下：

isdba，如果用户已经被认证为DBA或者是通过操作系统或口令文件具有DBA特权的，返回TRUE，否则返回FALSE。

language，返回数据库当前会话的语言、地域和字符集。

lang，返回ISO缩写语言名称，一个比现有的“语言”参数较短的形式。

sid ，返回数据库会话ID。

terminal，返回当前会话的终端操作系统的标识符。在分布式SQL语句，此参数返回了标识符为本地会话。在分布式环境中，此参数只支持远程SELECT语句，不用于远程INSERT，UPDATE或DELETE操作。

sessionid，返回审计会话标识符，在分布式SQL语句不能指定此参数。

client_info，返回最高可达64个字节存储的用户会话信息，可由应用程序使用DBMS_APPLICATION_INFO包。

entryid，返回当前审计条目编号，审计的EntryID序列细粒度的审计记录和定期审计记录之间共享，在分布式SQL语句不能使用这个属性。

测试一下比较常用的几个参数：

4、user函数

查看当前会话的用户名。

七、聚合函数

聚合函数也叫分组函数或集合函数，它对多行记录中的某个列执行计算并返回一个值。

聚合函数经常与 select 语句的 group
by 子句一同使用，所以有的时候也把它称之为分组函数。

1、分组函数

函数	说明	示例
min(x)	求最小值。	select min(sal) from EMP;
max(x)	求最大值。	select max(sal) from EMP;
avg(x)	求平均值。	select avg(sal) from EMP;
sum(x)	求合计值。	select sum(sal) from EMP;
count(*)	求记录数。	select count(*) from EMP;
stddev(x)	求标准差。	select stddev(sal) from EMP;
variance(x)	求协方差。	select variance(sal) from EMP;
median(x)	求中位数。	select median(sal) from EMP;

stddev(x)、variance(x)、median(x)这三个函数极少使用。

2、空值处理

有一点需要注意的是，除了count函数，其它的分组函数均会忽略值为null的列，这个特点极其重要，一定要记住它。

我们来测试一下，先生成测试数据：

create table tt(cc number(3));
insert into tt values(10);
insert into tt values(20);
insert into tt values(null);
select min(cc),max(cc),avg(cc),sum(cc),count(*) from tt;

注意，表中一共有三条数据，两条非空记录，一条空记录，avg函数得到的结果是(10+20)/2=15，忽略了空值记录，min、max、sum函数表面看不出来，但我们知道它们会忽略空值。

count函数不会忽略空值记录，除非指定列名，例如：

3、group by子句

group by子句的意思就是按一定的规则进行分组，把数据集划分成若干个子集，然后针对若干个子集进行数据统计，group by子句要与分组函数结合使用，否则没有意义。

1）对全部的员工进行统计

select min(sal),max(sal),avg(sal),sum(sal),count(*) from EMP;

2）按部门分类（组）统计

select deptno,min(sal),max(sal),avg(sal),sum(sal),count(*) from EMP group by deptno;

在上面的示例中，select的字段列表中有deptno，那么在group by后面一定也要有deptno，否则会出现语法错误。

select deptno,min(sal),max(sal),avg(sal),count(*) from EMP;

但是，在group by中有deptno，select的字段列表中可以没有deptno，不会出现语法错误，但这种用法很少。

select min(sal),max(sal),avg(sal),sum(sal),count(*) from EMP group by deptno;

3）按部门和职位分类（组）统计

select deptno,job,avg(sal),count(*) from EMP group by deptno,job;

4）where子句一定要在group by子句之前，where子句中不能使用分组函数。

select deptno,avg(sal),count(*) from EMP where deptno in (20,30) group by deptno;

select deptno,avg(sal),count(*) from EMP group by deptno where deptno in (20,30);

select deptno,avg(sal),count(*) from EMP where avg(sal)>1000;

4、having子句

having 子句对 group by统计出来的结果进行筛选，语法与 where 类似，但 having可以包含分组函数。

1）先按部门分组统计

select deptno,avg(sal),count(*) from EMP group by deptno;

2）从统计结果中筛选人数>=5的记录。

select deptno,avg(sal),count(*) from EMP group by deptno having count(*)>=5;

3）从统计结果中筛选平均公司超过两千并且部门代码等于20的记录。

select deptno,avg(sal),count(*) from EMP group by deptno having avg(sal)>2000 and deptno=20;

八、应用经验

本文介绍了Oracle常用的函数和用法，还有少部分的函数没有介绍，并不是我偷懒，而是没有必要。

数据库提供函数的目的是为了增强SQL语言的功能，但是我的看法有些不同，从一个程序员的视角，说说我的看法：

1）手工处理数据的时候，在SQL中使用函数确实可以扩展SQL的功能，我也经常这么做；

2）不同数据库提供的函数名、参数和功能有差异，从软件项目的兼容性方面考虑，应用程序中能不用函数就不用函数。

3）在实际开发中，日期转换函数（to_date和to_char）不得不用，但是，我在freecplus框架中已做了兼容性处理。

4）分组函数也得不用，包括min、max、avg、sum、count，幸运的是不同的数据库基本上兼容，只是细节上也有点差别，不兼容功能的不建议使用。

所以，大家在学习函数的时候，了解一些常用的、不同数据库能通用的函数即可，不要花太多的时间和精力去研究那些生辟和不通用的函数。