oracle数据库常用查询总结

1.分组做筛选：

select A from table group by A having A like '%aa%'

2.排序,desc降序，默认为升序，不用写

select * from table order by A desc

多个字段排序，先根据A降序排，后根据排序完成的数据B升序排

select * from table order by A desc ,B

3.值是另一个表的值

select * from table where A in(
	select a from table_A
)

4.分组后统计次数,count(1)大于1

select A,count(1) from table group by A having count(1) > 1 

5.关联查询

select A from table_a a,(select A from table_b) b where a.a = b.a

连续多个左关联查询

select * from table_a a left join table_b  b on a.A =b.A left join table_c c on a.A=c.A 

6.去重distinct,根据A,B去重

select distinct A,B from table

7.多表的某个值count关联

select a.A,a.count_a,b.count_b from 
(select A,count(1) as count_a from table_a group by A) a,
(select B,count(1) as count_b from table_a group by B) b
where a.A = b.B

8.将查询的结果直接入库,查询出来的字段顺序与table_a的字段顺序一致

insert into table_a(
select * from table_b where b ='1'
)

9.交集，差集，并集

交集intersect

select A from table_a 
intersect
select B from table_b

差集minus

select A from table_a 
minus
select B from table_b

并集union (union all 不过滤重复)

select A from table_a
union
select B from table_b

10.时间转化

(1)时间戳转yyyy-MM-dd hh24:mi:ss格式：

select to_char('1548299669'/(60*60*24)+TO_TIMESTAMP('1970-01-01 8:00:00','yyyy-MM-DD HH:MI:SS'),'yyyy-MM-dd hh24:mi:ss')  from dual

(2)yyyy/mm/dd hh24:mi:ss—>yyyy-mm-dd hh24:mi:ss

select to_char(to_date('2019/01/24 12:01:02','yyyy/mm/dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') from dual

(3)Dec1320177:11:34:000AM—>yyyy-mm-dd hh24:mi:ss

select to_char(to_timestamp('Dec1320177:11:34:000AM','MonthddYYYYhh:mi:ss:ff am','NLS_DATE_LANGUAGE=AMERICAN'),'yyyy-mm-dd hh24:mi:ss') from dual

两个日期减

毫秒：

select ceil((to_date('2019-01-24 11:43:01','yyyy-MM-dd hh24:mi:ss')-to_date('2019-01-24 11:42:20','yyyy-MM-dd hh24:mi:ss'))*24*60*60*1000) from dual 

秒：

select ceil((to_date('2019-01-24 11:43:01','yyyy-MM-dd hh24:mi:ss')-to_date('2019-01-24 11:42:20','yyyy-MM-dd hh24:mi:ss'))*24*60*60) from dual 

11.分页查询

oracle：rownum <=(开始的条数+一页的条数)) rn>开始的条数 
select * from (select t.*,rownum as rn from (select * from table t order by A) t where rownum <=((2-1)*10)+10) where rn>(2-1)*10
vertical:
select A from table limit size offset (page-1)*size

12.判断为空的

(1)oracle:A字段为null的赋值为0

select nvl(A,0) from table

(2)vertical:A字段为null的赋值为0

select ifnull(A,0) from table

13.字符串截取

(1)oracle:从第一位开始，截取到2017的位置substr(字段,n)从n开始截取到最后一位，substr(字段,0,n)从0位开始，截取n位

select substr('Dec1320177:11:34:000AM',0,instr('Dec1320177:11:34:000AM','2017')-1) from dual

Dec13

(2)vertical：

从左边截取n位：left(字段,n)
从右边截取n位：right(字段,n)

select A from table where left(A,2)

14.去空格值

select trim(A) from table

15.字符串的长度

select length(A) from table

16.值不等的判断

select * from table where A<>1

17.包含和不包含字符串

select * from table where A like '%字符串%'
select * from table where A not like '%字符串%'

18.在排序后添加一列数据序号

select a.* ,rownum from (
	select * from table order by a desc
)

19.取最小值

select A,min(B) from table group by A

20.字母开头的

select A from table where regexp_like(A,'^[a-zA-z]')

21.vertical数据库的like的内容是一个变量like ‘%’ || 变量 || ‘%’