oracle一些sql命令（全）

  1 insert into mrs_veh_snap_2015_2
  2 select t.nid, t.ccarnumber, t.dcollectiondate, t.caddresscode, t.ccollectionaddress,t.cpic1path from mrs_veh_snap_2015 t;
  3 
  4 select substr(q.cpic1path, instr(q.cpic1path,‘_‘,1,1)+1, 14) from mrs_veh_snap_2015_2 q;
  5 
  6 update mrs_veh_snap_2015_2 set timedate=substr(cpic1path, instr(cpic1path,‘_‘,1,1)+1, 17);
  7 
  8 select * from mrs_veh_snap_2015_2 where ccarnumber=‘蓝鲁B9JU65‘;
  9 
 10 select * from mrs_veh_snap_2015_2 where ccarnumber=‘蓝鲁B9JU65‘ and to_date(dcollectiondate,‘DD-MON-YY‘)=to_date(‘04-1月 -15‘,‘DD-MON-YY‘);
 11 
 12 select count( DISTINCT ccarnumber) from mrs_veh_snap_2015_2;
 13 
 14 select count(*) from mrs_veh_snap_2015_2;
 15 
 16 update mrs_veh_snap_2015_2  set time2 = to_timestamp(timedate, ‘YYYYMMDD HH24MISSFF‘);
 17 
 18 select DISTINCT to_date(time2) from mrs_veh_snap_2015_2;
 19 
 20 select * from mrs_veh_snap_2015_2;
 21 
 22 desc mrs_veh_snap_2015_2
 23 
 24 select * from mrs_veh_snap_2015_2 order by time2;
 25 
 26 select ccarnumber, ccolectionaddress, count(*) from mrs_veh_snap_2015_2 where to_date(dcollectiondate,‘DD-MON-YY‘)=to_date(‘05-1月 -15‘,‘DD-MON-YY‘) group by ccarnumber, ccolectionaddress having count(*)>1;
 27 
 28 select ccarnumber, ccolectionaddress, count(*) from mrs_veh_snap_2015_2 where to_date(dcollectiondate,‘DD-MON-YY‘)=to_date(‘05-1月 -15‘,‘DD-MON-YY‘) group by ccarnumber, ccolectionaddress having count(*)>1;
 29 
 30 -------------------------------------------------------------------------------------------------------------------------
 31 
 32 
 33 insert into mrs_veh_snap_2014_2
 34 select t.nid, t.ccarnumber, t.dcollectiondate, t.caddresscode, t.ccollectionaddress,t.cpic1path from mrs_veh_snap_2014 t;
 35 
 36 select substr(q.cpic1path, instr(q.cpic1path,‘_‘,1,1)+1, 17) from mrs_veh_snap_2014_2 q;
 37 
 38 update mrs_veh_snap_2014_2 set timedate=substr(cpic1path, instr(cpic1path,‘_‘,1,1)+1, 17);
 39 
 40 select * from mrs_veh_snap_2015_2 where ccarnumber=‘蓝鲁B9JU65‘;
 41 
 42 select * from mrs_veh_snap_2015_2 where ccarnumber=‘蓝鲁B9JU65‘ and to_date(dcollectiondate,‘DD-MON-YY‘)=to_date(‘04-1月 -15‘,‘DD-MON-YY‘);
 43 
 44 select count( DISTINCT ccarnumber) from mrs_veh_snap_2015_2;
 45 
 46 select * from mrs_veh_snap_2014_2 where cpic1path is null;
 47 
 48 select count(*) from mrs_veh_snap_2014_2;
 49 
 50 delete from mrs_veh_snap_2014_2 where ccarnumber is null;
 51 
 52 commit
 53 
 54 select * from mrs_veh_snap_2014_2 where not REGEXP_LIKE(timedate,  ‘^[2]{1}[[:digit:]]{16}$‘ ) 
 55 delete from mrs_veh_snap_2014_2 where not REGEXP_LIKE(timedate,  ‘^[2]{1}[[:digit:]]{16}$‘ ) 
 56 
 57 update mrs_veh_snap_2014_2  set time2 = to_timestamp(timedate, ‘YYYYMMDD HH24MISSFF‘);
 58 
 59 select * from mrs_veh_snap_2014_2 where caddresscode=‘621112106000‘ and ccollectionaddress is null
 60 
 61 select caddresscode, ccollectionaddress from mrs_veh_snap_2014_2 group by caddresscode, ccollectionaddress 
 62 
 63 select count(*) caddresscode, ccollectionaddress from mrs_veh_snap_2014_2 where caddresscode=‘621142107000‘ group by caddresscode, ccollectionaddress 
 64 
 65 select ccollectionaddress from mrs_veh_snap_2014_2 group by ccollectionaddress
 66 
 67 select * from mrs_veh_snap_2015_2 order by time2;
 68 
 69 update mrs_veh_snap_2014_2 set caddresscode=‘621142106000‘ where ccollectionaddress=‘火炬路与汇智桥路‘ ;
 70 
 71 select ccarnumber, ccolectionaddress, count(*) from mrs_veh_snap_2015_2 where to_date(dcollectiondate,‘DD-MON-YY‘)=to_date(‘05-1月 -15‘,‘DD-MON-YY‘) group by ccarnumber, ccolectionaddress having count(*)>1;
 72 
 73 delete from mrs_veh_snap_2014_2 where ccollectionaddress is null;
 74 COMMIT
 75 -------------------------------------------------------------------------------------------------------------
 76 select ccarnumber, ccolectionaddress, count(*) 
 77 from mrs_veh_snap_2015_2 
 78 where to_date(dcollectiondate,‘DD-MON-YY‘)=to_date(‘05-1月 -15‘,‘DD-MON-YY‘) 
 79 group by ccarnumber, ccolectionaddress having count(*)>1;
 80 
 81 
 82 select DISTINCT to_char(dcollectiondate,‘DD-MON-YY‘) from mrs_veh_snap_2015_2 
 83 
 84 select to_char(dcollectiondate,‘DD-MON-YY‘) from mrs_veh_snap_2015_2 group by to_char(dcollectiondate,‘DD-MON-YY‘)
 85 
 86 /*按照日期查询星期几*/
 87 select to_char(dcollectiondate,‘DD-MON-YY‘),to_number(to_char(dcollectiondate,‘D‘)) from mrs_veh_snap_2015_2 group by to_char(dcollectiondate,‘DD-MON-YY‘),to_number(to_char(dcollectiondate,‘D‘))
 88 
 89 
 90 /*每辆车在某一天出现的次数*/
 91 select ccarnumber,COUNT(*)
 92 from mrs_veh_snap_2015_2 
 93 where to_date(dcollectiondate,‘DD-MON-YY‘)=to_date(‘05-1月 -15‘,‘DD-MON-YY‘)
 94 group by ccarnumber order by count(*) desc;
 95 
 96 select to_char(dcollectiondate,‘DD-MON-YY‘) from mrs_veh_snap_2015_2 group by to_char(dcollectiondate,‘DD-MON-YY‘)
 97 
 98 /*给定一辆车在每一天出现的次数*/
 99 select ccarnumber,to_char(dcollectiondate,‘DD-MON-YY‘), count(*)
100 from mrs_veh_snap_2015_2 
101 where ccarnumber=‘蓝鲁B9JU65‘
102 group by ccarnumber,to_char(dcollectiondate,‘DD-MON-YY‘) 
103 order by to_date(to_char(dcollectiondate,‘DD-MON-YY‘)) 
104 
105 
106 /*每一辆车在某个时间段出现的次数*/
107 select ccarnumber, ccolectionaddress, count(*) 
108 from mrs_veh_snap_2015_2 
109 where to_timestamp(time2,‘DD-MON-YY HH.MI.SS.FF PM‘) between to_timestamp(‘04-1月 -15 03.00.00.00000000 下午‘,‘DD-MON-YY HH.MI.SS.FF PM‘) and to_timestamp(‘04-1月 -15 04.00.00.00000000 下午‘,‘DD-MON-YY HH.MI.SS.FF PM‘)
110 and ccolectionaddress=‘河东路与岙东路‘
111 group by ccarnumber, ccolectionaddress
112 order by count(*) desc;
113 
114 -------------------------------------------------------------------------------------------------------------
115 select * from mrs_veh_snap_2015_2 where ccarnumber=‘蓝鲁B9JU65‘ and to_date(dcollectiondate,‘DD-MON-YY‘)=to_date(‘04-1月 -15‘,‘DD-MON-YY‘);
116 
117 select ccarnumber,COUNT(*)
118 from mrs_veh_snap_2015_2 
119 group by ccarnumber order by count(*) desc;
120 
121 desc mrs_veh_snap_2015_2
122 
123 desc mrs_veh_snap_2015_3
124 
125 insert into mrs_veh_snap_2015_3
126 select t.nid, t.ccarnumber, t.dcollectiondate, t.caddresscode, t.ccolectionaddress,t.time2 from mrs_veh_snap_2015_2 t;
127 
128 update mrs_veh_snap_2015_3  set addresscode = ‘100000‘ where ccollectionaddress = ‘河东路与岙东路‘;
129 update mrs_veh_snap_2015_3  set addresscode = ‘010000‘ where ccollectionaddress = ‘火炬路与聚贤桥路‘;
130 update mrs_veh_snap_2015_3  set addresscode = ‘001000‘ where ccollectionaddress = ‘河东路与聚贤桥路‘;
131 update mrs_veh_snap_2015_3  set addresscode = ‘000100‘ where ccollectionaddress = ‘新悦路与岙东路‘;
132 update mrs_veh_snap_2015_3  set addresscode = ‘000010‘ where ccollectionaddress = ‘火炬路与汇智桥路‘;
133 update mrs_veh_snap_2015_3  set addresscode = ‘000001‘ where ccollectionaddress = ‘汇智桥与智力岛路‘;
134 
135 select * from mrs_veh_snap_2015_3
136 
137 update mrs_veh_snap_2015_3 set timedate=substr(timedate,9, 2);
138 
139 select DISTINCT timedate from mrs_veh_snap_2015_3;
140 
141 update mrs_veh_snap_2015_3  set timecode = ‘100000000000000000000000‘ where timedate = ‘00‘;
142 
143 COMMIT
144 
145 select count( DISTINCT ccarnumber) from mrs_veh_snap_2015_3;
146 
147 select * from mrs_veh_snap_2015_3 where ccarnumber=‘蓝鲁B9JU65‘ and to_date(dcollectiondate,‘DD-MON-YY‘)=to_date(‘05-1月 -15‘,‘DD-MON-YY‘);
148 
149 update mrs_veh_snap_2015_3 set code2 = replace(code2,‘1‘, addresscode);
150 --------------------------------------------------------
151 select count(*) from mrs_veh_snap_2015_4
152 
153 insert into mrs_veh_snap_2015_4
154 select t.nid, t.ccarnumber, t.dcollectiondate, t.caddresscode, t.ccolectionaddress, t.timedate, t.time2 from mrs_veh_snap_2015_2 t where rownum <= 1825042 
155 MINUS select t.nid, t.ccarnumber, t.dcollectiondate, t.caddresscode, t.ccolectionaddress, t.timedate, t.time2 from mrs_veh_snap_2015_2 t where rownum <= 1200000; 
156 
157 truncate table mrs_veh_snap_2015_4
158 
159 exp userid=system/orcl file=C:\Users\Administrator\Desktop\file_name.dmp tables=mrs_veh_snap_2015_2;
160 
161 /*导出数据*/
162 exp ITMS/123456@orcl file=C:\Users\Administrator\Desktop\file_name.dmp tables=mrs_veh_snap_2015_2
163 ---------------------------------------------------------------
164 select * from mrs_veh_snap_2015_4
165 
166 select count(*) from mrs_veh_snap_2015_4
167 
168 delete from mrs_veh_snap_2015_4 
169 where code2=‘000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000‘;
170 
171 delete from mrs_veh_snap_2015_4 where to_date(dcollectiondate,‘DD-MON-YY‘)=to_date(‘09-1月 -15‘,‘DD-MON-YY‘);
172 commit
173 
174 select to_date(dcollectiondate), count(*) from mrs_veh_snap_2015_4 group by to_date(dcollectiondate);
175 
176 select DISTINCT ccarnumber from mrs_veh_snap_2015_3
177 
178 select DISTINCT to_date(dcollectiondate) from mrs_veh_snap_2015_3