sxgps51876373GPS@gpsdb


select t.simid,t.vehiclenuber,t.licenseplate,t.gpsid,t.pingtai,t.registerdate,t.endtime,t.enddate,t.rowid from VEHICLEINFO t where t.endtime<'2017-12-31';
select t.simid,t.vehiclenuber,t.licenseplate,t.gpsid,t.pingtai,t.registerdate,t.endtime,t.enddate,t.rowid from VEHICLEINFO t where t.endtime>'2017-12-31' and t.endtime<'2018-12-31' ;

update VEHICLEINFO t set t.enddate='2018-12-31' where t.endtime>'2017-12-31' and t.endtime<'2018-12-31' ;

select t.*, t.rowid from KLSTATUS201906 t;
select t.*, t.rowid from KLSTATUS201906 t where t.simid='14440958477'


select t.simid,t.vehiclenuber,t.licenseplate,t.gpsid,t.pingtai,t.motorcadeid,t.registerdate,t.remark,t.rowid from VEHICLEINFO t where t.pingtai = 'ZZTXD'
select t.simid,t.vehiclenuber,t.licenseplate,t.gpsid,t.pingtai,t.motorcadeid,t.registerdate,t.remark,t.rowid from VEHICLEINFO t where t.pingtai='SHSX';



修改TOTAL仓库，提货，送货量时间段分配；0提货，1送货
select t.*, t.rowid from WAREHOUSEINFO t  where t.warehouse = 'H502';

查询公司账号：
select t.*, t.rowid from userinfo t where t.companyname like '%海门%';

查询车编号：
select t.*, t.rowid from VEHICLEINFO t where t.vehiclenuber like '%京达%';
select t.simid,t.vehiclenuber,t.licenseplate,t.simid,t.registerdate,t.remark,t.endtime,t.startdate,t.expense,t.finallytime,t.rowid from VEHICLEINFO t where t.vehiclenuber like 'CML苏%';

update VEHICLEINFO t set t.expense='480' where t.vehiclenuber like '京达%';


select t.*, t.rowid from VEHICLEINFO t where   t.gpsid='9161018340';

查询平台中费用为空的车
select t.simid,t.vehiclenuber,t.licenseplate,t.gpsid,t.registerdate,t.remark,t.endtime,t.startdate,t.expense,t.finallytime,t.rowid from vehicleinfo t where t.expense is null and t.pingtai in ('SHSX','TOTAL','TOTALFUEL')

COPY表downloadsms的全部数据到表downloadsms1中；
insert into downloadsms1 select * from downloadsms;
commit;

收到未发给用户的短信息表  
清空表
TRUNCATE TABLE DOWNLOADSMS;   
清理表中部分内容
delete from DOWNLOADSMS where ( TIME <'2017-04');

查询车牌
select t.*, t.rowid from VEHICLEINFO t where t.licenseplate like '江铃%';

修改指定车牌与车编号的车的年费；
update VEHICLEINFO t set t.expense='240' where t.licenseplate like '宏宇%';
update VEHICLEINFO t set t.expense='720' where t.vehiclenuber like '松林%';

修改指定账号所有车的年费  =====；
update  VEHICLEINFO   set expense='240' where simid in ( select t.simid from  VEHICLEINFO t, USERVEHICLE V where T.SIMID =V.SIMID and v.userid='shzx1'); 

查询shzx1账号下所有车：
select t.*, t.rowid from VEHICLEINFO t ,USERVEHICLE V where T.SIMID =V.SIMID and v.userid='hmfy1'; 

查询shzx1账号下所有车(简化为常用列)：
select t.simid,t.vehiclenuber,t.licenseplate,t.gpsid,t.registerdate,t.remark,t.endtime,t.startdate,t.expense,t.finallytime,t.rowid from VEHICLEINFO t ,USERVEHICLE V where T.SIMID =V.SIMID and v.userid='xsh1';


设备账号shzx1下所有车的截止日期改为'2018-07-26'：
update  VEHICLEINFO   set endtime='2018-04-21' where simid in ( select t.simid from  VEHICLEINFO t, USERVEHICLE V where T.SIMID =V.SIMID and v.userid='hmfy1')

通过平台号查询车辆详细信息
select t.simid,t.vehiclenuber,t.licenseplate,t.gpsid,t.registerdate,t.endtime,t.startdate,t.finallytime,t.rowid from vehicleinfo t where t.pingtai='XXCWD'


查询近三个月用户使用的最后日期
select distinct userid,companyname ,max(operatingtime) from (select t.userid,t.companyname, v.operatingtime from USERINFO t LEFT JOIN LOGOPERATING201708 v on t.userid=v.username where t.pingtai in ('SHSX','TOTAL')
union
select distinct userid,t.companyname,  v.operatingtime from USERINFO t LEFT JOIN LOGOPERATING201707 v on t.userid=v.username where t.pingtai in ('SHSX','TOTAL')
union
 select distinct userid,t.companyname,  v.operatingtime from USERINFO t LEFT JOIN LOGOPERATING201706 v on t.userid=v.username where t.pingtai in ('SHSX','TOTAL'))  group by userid,companyname

select t.*, t.rowid from VEHICLEINFO t where pingtai='zyyj';

通过平台号关联查询车辆信息表和最新状态表数据，删除最新状态表里面的最新状态时间。
delete from  latelygpsinfo a  where  a.simid in( select t.simid from VEHICLEINFO t  
 join latelygpsinfo l on t.simid=l.simid  where t.pingtai='TOTAL' )

复制表结构及其数据：
create table VEHICLEINFO_new as select * from VEHICLEINFO;
create table userinfo_new as select * from userinfo;
create table userVEHICLE_new as select * from userVEHICLE;

select t.*, t.rowid from VEHICLEINFO t where pingtai='ZYYJ';
select t.*,t.rowid from userinfo t where pingtai='ZYYJ'
select t.*,t.rowid from uservehicle t where pingtai='ZYYJ'

delete from VEHICLEINFO t where pingtai='ZYYJ';
delete from USERINFO t where pingtai='ZYYJ';
delete from USERVEHICLE t where pingtai='ZYYJ';

imp sxgps/sxgps51876373GPS@gpsdb;

delete from inceptsms where sms like '%请提醒司机不要疲劳驾驶%';
delete from inceptsms where time<'2019-06-01 00:00:00';
delete from vehicledistance where begintime<'2019-06-01 00:00:00';
delete from contactalarm where begintime<'2019-06-01 00:00:00';
delete from parkinglog where begintime<'2019-06-01 00:00:00';
delete from linealarmreprot where begintime<'2019-06-01 00:00:00';
delete from speedalarm where timelog<'2019-06-01 00:00:00';
delete from  VEHICLEWORKTIME where begintime<'2019-06-01 00:00:00';
TRUNCATE TABLE ALARMINFOWORKTIME;
TRUNCATE TABLE USERAPPLOG;
TRUNCATE TABLE DOWNLOADSMS;
commit;


通过命令行导出指定用户信息
exp userid=sxgps/sxgps51876373GPS@SXDBTEMP_222.73.54.14 tables=(vehicleinfo,LATELYGPSINFO) file=d:\daochu20191118.dmp  

通过命令行导入指定dmp文件里面的具体表数据
imp gygps/sx51876373@sxgps  file=d:\error.dmp tables=(gpsdata20171208)