工作中经典sql总结

海查oracle查询语句：
create table ga_gps_history_2011_01_01 as  select * from ga_gps_history_2012_03_01 t where 1=2
insert into ga_gps_history_2011_05  select * from ga_gps_history_2011_06_12 t 
select * from ga_gps_history_2012_03_01 t where gpsid='5110016' and  to_char(time,'yyyyMMddHH24miss')='20120301095955'

修改Oracle数据库技巧
1.更改年份：
update ezcrm_jq_pt t
   set t.alarmtime = add_months(t.alarmtime,-12) 
select to_char(t.alarmtime,'YYYY-MM-DD') from ezcrm_jq_ptt t 
2.更新字段部分值：
update cartport1 t set t.hphm=replace(t.hphm,'苏','京');
update EZCRM_AJ t set  t.afdz=replace( t.afdz,'阳','湖南省')  where t.afdz like '%阳%'
3.截取字符串前几位替换：
(1)追加在前面
update EZCRM_CBA_YSXX t
   set t.xgdwmc = '联合国'||substr(t.xgdwmc,1,2)
(2)追加在后面
update EZCRM_CBA_YSXX t
   set t.xgdwmc = substr(t.xgdwmc,1,2)||'联合国'
4.截取字符串后几位替换：
(1)追加在后面
update EZCRM_CBA_YSXX t
   set t.xgdwmc = substr(t.xgdwmc,length(t.xgdwmc)-2)||'联合国'
(2)追加在前面
update EZCRM_CBA_YSXX t
   set t.xgdwmc = '联合国'||substr(t.xgdwmc,length(t.xgdwmc)-2)
5.删除某用户下所有表及约束
drop user FZFX cascade;

6.查询oracle数据表最后一行

查询Oracle表中最后一行数据(行顺序与DATE字段无关)：
select * from (select * from TABLE order by rownum) where rownum < 2;
查询Oracle表中最后一行数据（行顺序与DATE字段有关）：
select * from (select t.* from TABLE t order by t.DATE) where rownum < 2

查询oracle数据表最后20行

select * from (select * from CARTPORT4 t order by t.jgsj desc) where rownum<=20; 

7.查出多少条数据导入领一张表中
insert into GA_GPS_HISTORY_2015_03_01 b
  (select * from GA_GPS_HISTORY_2012_03_01 t where rownum <= 200000)

8.创建一张新表把已存在表的数据放到新表中
create table GA_GPS_HISTORY_2015_03_02  as 
select * from  GA_GPS_HISTORY_2012_03_02 t where rownum <= 2000000

9.复制表结构和数据
create table 新表名as select * from 旧表名

10.只复制表结构
create table 新表名 as select * from 旧表名 where 1<>1;

11.复制其他数据库中的表结构和数据,建立一个dblink.
create table 新表名 as select * from 旧表名@dblink的名称

12.复制数据到已经建立的表
insert into TBL_A select * from TBL_B

13.解锁编辑表数据

第一种解决方法：

锁上编辑锁,加上查询他的ROWID就可以打开修改锁修改数据了
例:
select t.*,t.rowid from scott.emp t

第二种解决方法：

也可以 select t.* from scott.emp t for update 二种方式

-----------------------------------------------------------------------------------
海查hbase数据库sql：

describe ‘gps01’查看表结构

scan ‘gps01’ 查看表具体信息分布

实例：get 'gps01','10020#20120330',{COLUMN =>'DEFAULT:TIME',TIMERANGE => [1333037398000, 1333123199000], VERSIONS => 876}

实例：scan 'reg3500w', {COLUMNS => ['DEFAULT:JGSJ'], LIMIT => 10, STARTROW => '\xE6\xB2\xAA\xE8\x93\x89\xE9\xAB\x98\xE9\x80\x9F\xE9\x9D\x92\xE6\xB4\x8B\xE5\x8C\x97\xE8\xB7\xAF\xE6\xAE\xB5(\xE4\xB8\x9C\xE5\x90\x91\xE8\xA5\xBF)#2012010710'}

注释：get ‘表名’，‘行号ID’，{‘列号’，‘时间戳范围’，‘版本号对应oracle数据行数’}

-----------------------------------------------------------------------------------
查找指定车辆在指定时间段内的基本轨迹数据
select count(*)
   from ga_gps_history_2011_01_01 t
  where to_char(t.time, 'yyyy-MM-dd hh24:mm:ss') between
        '2012-03-01 00:00;00' and '2012-03-01 00:00;00'

    and t.gpsid = '10000';          

select count(*)20131222203654
  from ga_gps_history_2011_01_02 t
 where to_char(t.time, 'yyyy-MM-dd hh24:mm:ss') between
       '2012-03-03 10:00;00' and '2012-03-03 16:00;00'
   and t.gpsid in ('10001','10000','10003');

查找在指定时间、指定区域内所有车辆的第一次和最后一次基本位置信息
 select count(*)
   from ga_gps_history_2011_01_02 t
  where (to_char(t.time, 'yyyy-MM-dd hh24:mm:ss') between
        '2012-03-03 10:00;00' and '2012-03-03 12:00;00')
    and ((t.x between 116.37854 and 116.44274) and
        (t.y between 39.89843 and 39.96801));

查找指定车辆在指定时间，指定区域内的第一次和最后一次的基本信息
 select count(*)
   from ga_gps_history_2011_01_02 t
  where (to_char(t.time, 'yyyy-MM-dd hh24:mm:ss') between
        '2012-03-12 00:00;00' and '2012-03-18 00:00;00')
      and ((t.x between 115.37646 and 117.50927) and (t.y between  39.43896 and 40.97802))
    and t.gpsid in ('10001','10000','10003');

delete from ga_gps_history_2011_01_01 where rownum<=20

确定oracle中gpsid的空间范围
select * from ga_gps_history_2012_03_03 t where  (t.x between 116.646366 and 116.64637) and (t.y between  39.70825 and 39.7684216666667)
\xE6\xB2\xAA\xE8\x93\x89\xE9\xAB\x98\xE9\x80\x9F\xE9\x9D\x92\xE6\xB4\x8B\xE5\x8C\x97\xE8\xB7\xAF\xE6\xAE\xB5(\xE4\xB8\x9C\xE5\x90\x91\xE8\xA5\xBF)#2012010710