oracle常用语句

 

#添加数据列
alter table table_name add 字段 数据类型   是增加字段的
update table_name SET ID='G'+substr(ID,0)// 批量修改某一字段

create table table_name1 select (A,B,C...,W,Y,1,2,3,4,5) from table_name;
drop table table_name;
alter table table_name1 rename table_name; 

insert into book(bookid) values('100121')

#修改数据表中的数据
update tablename
set column1 = 'xxx'
where pk = 'aaaa';
commit;

create table records_copy as select * from records_tab
# 不计重复
select distinct id from test t

select distinct LENGTHB(szsource)from taxi_records_tab;
#查询每一个字段所占空间


#svn 日常操作
#http://www.blogjava.net/jelver/articles/88124.html
#原文http://blog.csdn.net/happy4nothing/archive/2005/05/19/376604.aspx

#编译器选项
#http://msdn.microsoft.com/zh-cn/library/xsa71f43.aspx

#查表空间
select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name
# 查看表所占空间大小
SELECT segment_name AS TABLENAME,BYTES B,BYTES/1024 KB,BYTES/1024/1024 MB FROM user_segments WHERE segment_name='TAXI_RECORDS_TAB' OR segment_name='TAXI_UPDOWN_TAB'OR segment_name='TAXI_CAR_TAB'
#安装降序查看某一个表空间里的表大小
SELECT segment_name AS TABLENAME,BYTES B,BYTES/1024 KB,BYTES/1024/1024 MB FROM USER_SEGMENTS WHERE TABLESPACE_NAME='RTTI_BASE_DATA' ORDER BY BYTES DESC

select * from testrecords where szcarid='180421' and iyear = 2011 and imon = 12 and iday=1
and ihour = 0 and imin=1 and isec=53

update taxi_updown_tab set bgridn=56*floor((bflatitude-39.678255)/0.009)+floor((bflongitude-116.079018)/0.009) where floor((bflatitude-39.678255)/0.009) <58 and floor((bflongitude-116.079018)/0.009)<57;
commit

select * from cz group by c1,c10,c20 having count(*) >1;

#是否有中文存在
select szcarid from records_week_tab where asciistr(szcarid) like '%\%'

select seqnum,length(replace(matchpath,';','--'))-length(matchpath) as time from odmatch_111201_3 order by time


select seqnum,length(replace(matchpath,';','--'))-length(matchpath) as time, 
length(replace(direction,';','--'))-length(direction) as time2  from odmatch_111201_3 
where length(replace(matchpath,';','--'))-length(matchpath) = length(replace(direction,';','--'))-length(direction)

select * from user_objects_ae 
#查看表信息，创建时间，最后更新时间等

select * from recyclebin;#查看已删除的表
purge talbe test;#从回收站删除表test

flushback table tablename to before drop # 恢复删除的表
drop table tablename purge #彻底删除该表

select truncTime, count(truncTime) times from (select trunc(worktime/3600) truncTime from car_111201_tab) group by truncTime order by truncTime
#统计工作时长不同时间段车辆次数

 

较为复杂的语句

select a.carid,a.times, b.totaltimes, a.times/b.totaltimes ratio from (select carid, count(carid) times from test_wrongcar_tab group by carid) a, 
(select szcarid, count(szcarid) totaltimes from new_records_tab group by szcarid) b where a.carid=b.szcarid order by ratio desc


select a.carid,a.times, b.totaltimes, a.times/b.totaltimes ratio,c.ivalid,c.ivalid/b.totaltimes ivalidratio from 
(select carid, count(carid) times from test_wrongcar_tab group by carid) a, 
(select szcarid, count(szcarid) totaltimes from new_records_tab group by szcarid) b,
(select szcarid,count(iload) ivalid from new_records_tab where iload=2 group by szcarid ) c
 where a.carid=b.szcarid and b.szcarid=c.szcarid order by ratio desc
 
 
 create view Error_Car_111201_View(carid,times,totaltimes,ratio)
as select a.carid,a.times, b.totaltimes, a.times/b.totaltimes ratio from
(select carid, count(carid) times from ERROR_CAR_111201_TAB group by carid) a, 
(select szcarid, count(szcarid) totaltimes from RECORDS_111201_TAB group by szcarid) b 
where a.carid=b.szcarid order by ratio desc

create view load_freqency_111201(szcarid,total,d0,d1,d2) as
select szcarid,count(1) total, count(case when iload like '0' then 1 else null end) D0, 
count(case when iload like '1' then 1 else null end) D1 ,
count(case when iload like '2' then 1 else null end) D2 from records_111201_tab group by szcarid

select szcarid,ddate,count(1) time from records_111201_tab 
group by szcarid,ddate,fspeed,flongitude,flatitude,iangle,iload,ivalidity
having count(*)>1 order by time