oracle中的exists 和not exists 用法详解
有两个简单例子,以说明 “exists”和“in”的效率问题
1) select * from T1 where exists(select 1 from T2 whereT1.a=T2.a) ;
2) select * from T1 where T1.a in (select T2.a from T2) ;
exists 用法:
请注意 1)句中的有颜色字体的部分 ,理解其含义;
其中 “select 1 from T2 where T1.a=T2.a” 相当于一个关联表查询,相当于
“select 1 fromT1,T2
但是,如果你当当执行 1) 句括号里的语句,是会报语法错误的,这也是使用exists需要注意的地方。
“exists(xxx)”就表示括号里的语句能不能查出记录,它要查的记录是否存在。
因此“select 1”这里的“1”其实是无关紧要的,换成“*”也没问题,它只在乎括号里的数据能不能查找出来,是否存在这样的记录,如果存在,这 1) 句的where条件成立。
in 的用法:
继续引用上面的例子
“2) select * from T1 where T1.a in (select T2.a from T2) ”
这里的“in”后面括号里的语句搜索出来的字段的内容一定要相对应,一般来说,T1和T2这两个表的a字段表达的意义应该是一样的,否则这样查没什么意义。
打个比方:T1,T2表都有一个字段,表示工单号,但是T1表示工单号的字段名叫“ticketid”,T2则为“id”,但是其表达的意义是一样的,而且数据格式也是一样的。这时,用2)的写法就可以这样:
“select * from T1 where T1.ticketid in (select T2.id from T2)”
Select name from employee where name not in (select name fromstudent);
Select name from employee where not exists (select name fromstudent);
第一句SQL语句的执行效率不如第二句。
通过使用EXISTS,Oracle会首先检查主查询,然后运行子查询直到它找到第一个匹配项,这就节省了时间。Oracle在执行IN子查询时,首先执行子查询,并将获得的结果列表存放在一个加了索引的临时表中。在执行子查询之前,系统先将主查询挂起,待子查询执行完毕,存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因
Oralce函数经典 - 日期函数
ADD_MONTHS(d,n) --日期对象d,几个月n,如果加月n就是正数,如果是减月就是负数。
LAST_DAY(d) --日期对象d,得到月份的最后一天。
NEXT_DAY(d,number) --日期对象d,从d日期开始,计算下一个星期几的日期,枚举值:星期日=>1、星期一=>2、星期二=>3、星期三=>4、3,星期四=>5、星期五=>6、星期六=>7
MONTHS_BETWEEN(d1,d2) --计算两个日期的时间间隔,单位月。
NEW_TIME(d,c1,c2) --转换时区,例如:select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') 台北,
to_char(NEW_TIME(sysdate,'EST','GMT'),'YYYY/MM/DD HH24:MI:SS') 格林威治 from dual;
ROUND(d[,fmt]) --对日期做四舍五入,月的四捨五入以每月的15号为基准,年的四捨五入以每年6月为基准。fmt=year、month
TRUNC(d[,fmt]) --对日期做低位舍弃计算,初始化操作,fmt=year、month。例如:2011-09-06 如果 fmt=year 得到 2011-01-01 如果fmt=month 得到 2011-09-01
SELECT TO_CHAR(ADD_MONTHS(to_date('2006-11-25','YYYY-MM-DD'), ROWNUM), 'yyyy-mm-DD')
FROM DUAL
CONNECT BY ROWNUM <=13;
---加法
select sysdate,add_months(sysdate,12) from dual;--–加1年
select sysdate,add_months(sysdate,1) from dual;--–加1月
select sysdate,to_char(sysdate+7,’yyyy-mm-dd HH24:MI:SS’) from dual; --–加1星期
select sysdate,to_char(sysdate+1,’yyyy-mm-dd HH24:MI:SS’) from dual; --加1天
select sysdate,to_char(sysdate+1/24,’yyyy-mm-dd HH24:MI:SS’) from dual; --加1小时
select sysdate,to_char(sysdate+1/24/60,’yyyy-mm-dd HH24:MI:SS’) from dual; --加1分钟
select sysdate,to_char(sysdate+1/24/60/60,’yyyy-mm-dd HH24:MI:SS’) from dual; --加1秒
–减法
select sysdate,add_months(sysdate,-12) from dual; --减1年
select sysdate,add_months(sysdate,-1) from dual; --减1月
select sysdate,to_char(sysdate-7,’yyyy-mm-dd HH24:MI:SS’) from dual; ---–减1星期
select sysdate,to_char(sysdate-1,’yyyy-mm-dd HH24:MI:SS’) from dual; --–减1天
select sysdate,to_char(sysdate-1/24,’yyyy-mm-dd HH24:MI:SS’) from dual; ---–减1小时
select sysdate,to_char(sysdate-1/24/60,’yyyy-mm-dd HH24:MI:SS’) from dual; ---–减1分钟
select sysdate,to_char(sysdate-1/24/60/60,’yyyy-mm-dd HH24:MI:SS’) from dual; ---–减1秒
--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