1 /*从一个表向另一个表插入数据*/
2 insert into mrs_veh_snap_2015_2
3 select t.nid, t.ccarnumber, t.dcollectiondate, t.caddresscode, t.ccollectionaddress,t.cpic1path from mrs_veh_snap_2015 t;
4
5 /*查询截取的字符串*/
6 select substr(q.cpic1path, instr(q.cpic1path,'_',1,1)+1, 14) from mrs_veh_snap_2015_2 q;
7
8 /*截取一个字符串列放入另一列*/
9 update mrs_veh_snap_2015_2 set timedate=substr(cpic1path, instr(cpic1path,'_',1,1)+1, 17);
10
11 /*条件查询*/
12 select * from mrs_veh_snap_2015_2 where ccarnumber='蓝鲁B9JU65';
13
14 select * from mrs_veh_snap_2015_2 where cpic1path is null;
15
16 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');
17
18 /*分组查询*/
19 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;
20
21 /*查询不重复值*/
22 select count( DISTINCT ccarnumber) from mrs_veh_snap_2015_2;
23
24 select DISTINCT to_date(time2) from mrs_veh_snap_2015_2;
25
26 /*查询记录数*/
27 select count(*) from mrs_veh_snap_2015_2;
28
29
30 /*将一串字符数字写成timestamp格式*/
31 update mrs_veh_snap_2015_2 set time2 = to_timestamp(timedate, 'YYYYMMDD HH24MISSFF');
32
33 /*查询所有*/
34 select * from mrs_veh_snap_2015_2;
35
36 /*清空表结构*/
37 desc mrs_veh_snap_2015_2
38
39 /*有序查询*/
40 select * from mrs_veh_snap_2015_2 order by time2;
41
42 commit
43
44 -------------------------------------------------------------------------------------
45 /*删除复合条件的值*/
46 delete from mrs_veh_snap_2014_2 where ccarnumber is null;
47 delete from mrs_veh_snap_2014_2 where ccollectionaddress is null;
48
49 /*匹配查询只取数字*/
50 select * from mrs_veh_snap_2014_2 where not REGEXP_LIKE(timedate, '^[2]{1}[[:digit:]]{16}$' )
51 delete from mrs_veh_snap_2014_2 where not REGEXP_LIKE(timedate, '^[2]{1}[[:digit:]]{16}$' )
52
53 /*条件查询*/
54 select * from mrs_veh_snap_2014_2 where caddresscode='621112106000' and ccollectionaddress is null
55
56 /*分组查询*/
57 select caddresscode, ccollectionaddress from mrs_veh_snap_2014_2 group by caddresscode, ccollectionaddress
58
59 select count(*) caddresscode, ccollectionaddress from mrs_veh_snap_2014_2 where caddresscode='621142107000' group by caddresscode, ccollectionaddress
60
61 select ccollectionaddress from mrs_veh_snap_2014_2 group by ccollectionaddress
62
63 /*更改*/
64 update mrs_veh_snap_2014_2 set caddresscode='621142106000' where ccollectionaddress='火炬路与汇智桥路' ;
65
66 COMMIT
67
68 ----------------------------------------------------------------------------------
69 /*查询不重复日期*/
70 select DISTINCT to_char(dcollectiondate,'DD-MON-YY') from mrs_veh_snap_2015_2
71
72 /*分组查询*/
73 select to_char(dcollectiondate,'DD-MON-YY') from mrs_veh_snap_2015_2 group by to_char(dcollectiondate,'DD-MON-YY')
74
75 /*按照日期查询星期几*/
76 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'))
77
78
79 /*每辆车在某一天出现的次数*/
80 select ccarnumber,COUNT(*)
81 from mrs_veh_snap_2015_2
82 where to_date(dcollectiondate,'DD-MON-YY')=to_date('05-1月 -15','DD-MON-YY')
83 group by ccarnumber order by count(*) desc;
84
85 select to_char(dcollectiondate,'DD-MON-YY') from mrs_veh_snap_2015_2 group by to_char(dcollectiondate,'DD-MON-YY')
86
87 /*给定一辆车在每一天出现的次数*/
88 select ccarnumber,to_char(dcollectiondate,'DD-MON-YY'), count(*)
89 from mrs_veh_snap_2015_2
90 where ccarnumber='蓝鲁B9JU65'
91 group by ccarnumber,to_char(dcollectiondate,'DD-MON-YY')
92 order by to_date(to_char(dcollectiondate,'DD-MON-YY'))
93
94
95 /*每一辆车在某个时间段出现的次数*/
96 select ccarnumber, ccolectionaddress, count(*) from mrs_veh_snap_2015_2
98 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')
99 and ccolectionaddress='河东路与岙东路'
100 group by ccarnumber, ccolectionaddress
101 order by count(*) desc;
102
103 ------------------------------------------------------------------------------------
104 /*分组查询*/
105 select ccarnumber,COUNT(*) from mrs_veh_snap_2015_2 group by ccarnumber order by count(*) desc;
106
107 /*更改值*/
108 update mrs_veh_snap_2015_3 set addresscode = '100000' where ccollectionaddress = '河东路与岙东路';
109
110 update mrs_veh_snap_2015_3 set timecode = '100000000000000000000000' where timedate = '00';
111
112 /*查询截取的字符串*/
113 update mrs_veh_snap_2015_3 set timedate=substr(timedate,9, 2);
114
115 /*查询不重复值*/
116 select DISTINCT timedate from mrs_veh_snap_2015_3;
117
118 /*查询记录数*/
119 select count( DISTINCT ccarnumber) from mrs_veh_snap_2015_3;
120
121 /*替换*/
122 update mrs_veh_snap_2015_3 set code2 = replace(code2,'1', addresscode);
123
124 COMMIT
125
126 --------------------------------------------------------
127 /*查询记录数*/
128 select count(*) from mrs_veh_snap_2015_4
129
130 /*插入指定行数*/
131 insert into mrs_veh_snap_2015_4
132 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
133 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;
134
135 /*删除表*/
136 truncate table mrs_veh_snap_2015_4
137
138 /*导出数据*/
139 exp ITMS/123456@orcl file=C:\Users\Administrator\Desktop\file_name.dmp tables=mrs_veh_snap_2015_2
140
141 /*条件查询*/
142 delete from mrs_veh_snap_2015_4
143 where code2='000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000';
144
145 /*删除符合条件值*/
146 delete from mrs_veh_snap_2015_4 where to_date(dcollectiondate,'DD-MON-YY')=to_date('09-1月 -15','DD-MON-YY');
148
149 /*分组统计*/
150 select to_date(dcollectiondate), count(*) from mrs_veh_snap_2015_4 group by to_date(dcollectiondate);
151
152 /*查询不同车牌号*/
153 select DISTINCT ccarnumber from mrs_veh_snap_2015_3
154
155 /*查询不同日期*/
156 select DISTINCT to_date(dcollectiondate) from mrs_veh_snap_2015_3
157
158 /*查询所有*/
159 select * from mrs_veh_snap_2015_3
