/
envoy.istio
/
oceanbase
GigaCode
beta
ОбзорЦентр заботыВойти

oceanbase

Форк
0
Ветки: 12Коммиты: 12104Теги: 40
oceanbase
/tools
/deploy
/mysql_test
/test_suite
/delete
/r
/mysql
/
delete.result 
400 строк · 10.9 Кб
1
drop table if exists t1, t_part, t_single;
2
drop table if exists t2;
3
drop table if exists t3;
4
drop table if exists t4;
5
result_format: 4
6
create table t1(a int primary key, b int);
7


8
insert into t1 values(1,0),(2,0),(3,0);
9
delete from t1;
10
select * from t1;
11
+---+------+
12
| a | b    |
13
+---+------+
14
+---+------+
15


16
insert into t1 values(1,0),(2,0),(3,0);
17
delete from t1 where a = 1;
18
delete from t1 where a = 2;
19
delete from t1 where a = 3;
20
select * from t1;
21
+---+------+
22
| a | b    |
23
+---+------+
24
+---+------+
25


26
insert into t1 values(1,0),(2,0),(3,0);
27
delete from t1 where a=1;
28
select * from t1;
29
+---+------+
30
| a | b    |
31
+---+------+
32
| 2 |    0 |
33
| 3 |    0 |
34
+---+------+
35


36
insert into t1 values(1,0);
37
replace into t1 values(2,0),(3,0);
38
delete from t1 where a=1 or a=2 or a=3;
39
select * from t1;
40
+---+------+
41
| a | b    |
42
+---+------+
43
+---+------+
44


45
insert into t1 values(1,0),(2,0);
46
delete from t1 where a=1;
47
delete from t1 where a=2;
48
delete from t1 where a=3;
49
select * from t1;
50
+---+------+
51
| a | b    |
52
+---+------+
53
+---+------+
54


55
drop table t1;
56


57
create table t_part (c1 bigint primary key, c2 bigint) partition by hash(c1) partitions 2;
58
create table t_single(c1 int);
59
insert into t_part(c1) values(1231346464513131);
60
insert into t_part values(1, 2), (3, 1);
61
insert into t_part values(2, 3), (4, 3);
62
insert into t_part partition(p0, p1) values(5, 6);
63
insert into t_part values('6', '6'), ('8', '8');
64
delete from t_part where c1 in (select c1 from t_single);
65
select * from t_part;
66
+------------------+------+
67
| c1               | c2   |
68
+------------------+------+
69
|                2 |    3 |
70
|                4 |    3 |
71
|                6 |    6 |
72
|                8 |    8 |
73
|                1 |    2 |
74
|                3 |    1 |
75
|                5 |    6 |
76
| 1231346464513131 | NULL |
77
+------------------+------+
78
drop table t_part, t_single;
79


80
create table t2 (p1 int, p2 int, p3 int, p4 int, primary key(p1,p2,p3));
81


82
insert into t2 values(1,1,1,0),(2,2,2,0),(3,3,3,0),(4,4,4,0),(5,4,4,0),(6,4,4,0),(7,4,6,0),(8,4,6,0),(9,4,6,0),(10,4,6,0),(11,4,6,0),(12,4,6,0), (13,4,6,0);
83


84
delete from t2 where p1 = 1;
85


86
select * from t2;
87
+----+----+----+------+
88
| p1 | p2 | p3 | p4   |
89
+----+----+----+------+
90
|  5 |  4 |  4 |    0 |
91
|  6 |  4 |  4 |    0 |
92
|  7 |  4 |  6 |    0 |
93
|  8 |  4 |  6 |    0 |
94
|  9 |  4 |  6 |    0 |
95
| 10 |  4 |  6 |    0 |
96
| 11 |  4 |  6 |    0 |
97
| 12 |  4 |  6 |    0 |
98
| 13 |  4 |  6 |    0 |
99
+----+----+----+------+
100
select p1,p3 from t2;
101
+----+----+
102
| p1 | p3 |
103
+----+----+
104
|  5 |  4 |
105
|  6 |  4 |
106
|  7 |  6 |
107
|  8 |  6 |
108
|  9 |  6 |
109
| 10 |  6 |
110
| 11 |  6 |
111
| 12 |  6 |
112
| 13 |  6 |
113
+----+----+
114


115
replace into t2 values(1,1,1,0),(2,2,2,0),(3,3,3,0),(4,4,4,0),(5,4,4,0),(6,4,4,0),(7,4,6,0),(8,4,6,0),(9,4,6,0),(10,4,6,0),(11,4,6,0),(12,4,6,0), (13,4,6,0);
116


117
delete from t2 where p1>3 or p3 >=6;
118
select * from t2;
119
+----+----+----+------+
120
| p1 | p2 | p3 | p4   |
121
+----+----+----+------+
122
|  1 |  1 |  1 |    0 |
123
|  2 |  2 |  2 |    0 |
124
|  3 |  3 |  3 |    0 |
125
+----+----+----+------+
126
select p1,p3 from t2;
127
+----+----+
128
| p1 | p3 |
129
+----+----+
130
|  1 |  1 |
131
|  2 |  2 |
132
|  3 |  3 |
133
+----+----+
134
delete from t2 where p1=1 and p3 =2;
135
select * from t2;
136
+----+----+----+------+
137
| p1 | p2 | p3 | p4   |
138
+----+----+----+------+
139
|  1 |  1 |  1 |    0 |
140
|  2 |  2 |  2 |    0 |
141
|  3 |  3 |  3 |    0 |
142
+----+----+----+------+
143
select p1,p3 from t2;
144
+----+----+
145
| p1 | p3 |
146
+----+----+
147
|  1 |  1 |
148
|  2 |  2 |
149
|  3 |  3 |
150
+----+----+
151
delete from t2 where p1=1 and p2 =1 and p3=1;
152
select * from t2;
153
+----+----+----+------+
154
| p1 | p2 | p3 | p4   |
155
+----+----+----+------+
156
|  2 |  2 |  2 |    0 |
157
|  3 |  3 |  3 |    0 |
158
+----+----+----+------+
159
select p1,p3 from t2;
160
+----+----+
161
| p1 | p3 |
162
+----+----+
163
|  2 |  2 |
164
|  3 |  3 |
165
+----+----+
166


167
replace into t2 values(1,1,1,0),(2,2,2,0),(3,3,3,0),(4,4,4,0),(5,4,4,0),(6,4,4,0),(7,4,6,0),(8,4,6,0),(9,4,6,0),(10,4,6,0),(11,4,6,0),(12,4,6,0), (13,4,6,0);
168


169
delete from t2 where p1 in (1,2,3,6,7,8,12,13,0);
170
select * from t2;
171
+----+----+----+------+
172
| p1 | p2 | p3 | p4   |
173
+----+----+----+------+
174
|  4 |  4 |  4 |    0 |
175
|  5 |  4 |  4 |    0 |
176
|  9 |  4 |  6 |    0 |
177
| 10 |  4 |  6 |    0 |
178
| 11 |  4 |  6 |    0 |
179
+----+----+----+------+
180


181
drop table t2;
182


183
create table t3 (p1 int, p2 int, p3 int, primary key(p1,p2));
184


185
insert into t3 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,4,4),(6,4,4),(7,4,6),(8,4,6),(9,4,6),(10,4,6),(11,4,6),(12,4,6), (13,4,6);
186
delete from t3 where p1=1 and p2=1 and p3=1;
187
select * from t3;
188
+----+----+------+
189
| p1 | p2 | p3   |
190
+----+----+------+
191
|  2 |  2 |    2 |
192
|  3 |  3 |    3 |
193
|  4 |  4 |    4 |
194
|  5 |  4 |    4 |
195
|  6 |  4 |    4 |
196
|  7 |  4 |    6 |
197
|  8 |  4 |    6 |
198
|  9 |  4 |    6 |
199
| 10 |  4 |    6 |
200
| 11 |  4 |    6 |
201
| 12 |  4 |    6 |
202
| 13 |  4 |    6 |
203
+----+----+------+
204
select p1,p3 from t3;
205
+----+------+
206
| p1 | p3   |
207
+----+------+
208
|  2 |    2 |
209
|  3 |    3 |
210
|  4 |    4 |
211
|  5 |    4 |
212
|  6 |    4 |
213
|  7 |    6 |
214
|  8 |    6 |
215
|  9 |    6 |
216
| 10 |    6 |
217
| 11 |    6 |
218
| 12 |    6 |
219
| 13 |    6 |
220
+----+------+
221


222
replace into t3 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,4,4),(6,4,4),(7,4,6),(8,4,6),(9,4,6),(10,4,6),(11,4,6),(12,4,6), (13,4,6);
223
delete from t3 where p1>3 or p3 >=6;
224
select * from t3;
225
+----+----+------+
226
| p1 | p2 | p3   |
227
+----+----+------+
228
|  1 |  1 |    1 |
229
|  2 |  2 |    2 |
230
|  3 |  3 |    3 |
231
+----+----+------+
232
select p1,p3 from t3;
233
+----+------+
234
| p1 | p3   |
235
+----+------+
236
|  1 |    1 |
237
|  2 |    2 |
238
|  3 |    3 |
239
+----+------+
240


241
delete from t3 where p1=1 and p3 =2;
242
select * from t3;
243
+----+----+------+
244
| p1 | p2 | p3   |
245
+----+----+------+
246
|  1 |  1 |    1 |
247
|  2 |  2 |    2 |
248
|  3 |  3 |    3 |
249
+----+----+------+
250
select p1,p3 from t3;
251
+----+------+
252
| p1 | p3   |
253
+----+------+
254
|  1 |    1 |
255
|  2 |    2 |
256
|  3 |    3 |
257
+----+------+
258


259
replace into t3 values(1,1,1),(2,2,2);
260
delete from t3 where p1=1 and p2 =1 and p3=1;
261
select * from t3;
262
+----+----+------+
263
| p1 | p2 | p3   |
264
+----+----+------+
265
|  2 |  2 |    2 |
266
|  3 |  3 |    3 |
267
+----+----+------+
268
select p1,p3 from t3;
269
+----+------+
270
| p1 | p3   |
271
+----+------+
272
|  2 |    2 |
273
|  3 |    3 |
274
+----+------+
275


276
replace into t3 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,4,4),(6,4,4),(7,4,6),(8,4,6),(9,4,6),(10,4,6),(11,4,6),(12,4,6),(13,4,6);
277
delete from t3 where p1 in (1,2,3,6,7,8,12,13,0);
278
select * from t3;
279
+----+----+------+
280
| p1 | p2 | p3   |
281
+----+----+------+
282
|  4 |  4 |    4 |
283
|  5 |  4 |    4 |
284
|  9 |  4 |    6 |
285
| 10 |  4 |    6 |
286
| 11 |  4 |    6 |
287
+----+----+------+
288


289
drop table t3;
290


291
create table t4 (p1 varchar(100), p2 int, p3 datetime(6), p4 int, primary key(p1,p2,p3));
292


293
insert into t4 values('a', 41, '2012-10-23 17:14:00',0), ('b', 42, '2012-10-23 17:15:00',0), ('c', 43, '2012-10-23 17:16:00',0);
294
delete from t4 where p3='2012-10-23 17:14:00';
295
select * from t4;
296
+----+----+----------------------------+------+
297
| p1 | p2 | p3                         | p4   |
298
+----+----+----------------------------+------+
299
| b  | 42 | 2012-10-23 17:15:00.000000 |    0 |
300
| c  | 43 | 2012-10-23 17:16:00.000000 |    0 |
301
+----+----+----------------------------+------+
302


303
replace into t4 values('a', 41, '2012-10-23 17:14:00',0), ('b', 42, '2012-10-23 17:15:00',0), ('c', 43, '2012-10-23 17:16:00',0);
304
delete from t4 where p2='42';
305
select * from t4;
306
+----+----+----------------------------+------+
307
| p1 | p2 | p3                         | p4   |
308
+----+----+----------------------------+------+
309
| a  | 41 | 2012-10-23 17:14:00.000000 |    0 |
310
| c  | 43 | 2012-10-23 17:16:00.000000 |    0 |
311
+----+----+----------------------------+------+
312


313
replace into t4 values('a', 41, '2012-10-23 17:14:00',0), ('b', 42, '2012-10-23 17:15:00',0), ('c', 43, '2012-10-23 17:16:00',0);
314
delete from t4 where p2<'401' and p1='c';
315
select * from t4;
316
+----+----+----------------------------+------+
317
| p1 | p2 | p3                         | p4   |
318
+----+----+----------------------------+------+
319
| a  | 41 | 2012-10-23 17:14:00.000000 |    0 |
320
| b  | 42 | 2012-10-23 17:15:00.000000 |    0 |
321
+----+----+----------------------------+------+
322


323
delete from t4 where p1='a' and p2=41 and p3='2012-10-23 17:14:00';
324
select * from t4;
325
+----+----+----------------------------+------+
326
| p1 | p2 | p3                         | p4   |
327
+----+----+----------------------------+------+
328
| b  | 42 | 2012-10-23 17:15:00.000000 |    0 |
329
+----+----+----------------------------+------+
330


331
drop table t4;
332


333
create table t2(c1 int primary key, c2 int, c3 int);
334
create table t3 (c1 int, c2 int, c3 int);
335


336
insert into t2 values (1, 1, 1);
337
insert into t2 values (2, 2, 2);
338
insert into t2 values (3, 2, 3);
339
insert into t2 values (4, 6, 4);
340
insert into t2 values (5, 4, 3);
341
insert into t2 values (6, 4, 2);
342
insert into t3 values(2, 2, 2);
343
insert into t3 values(3, 3, 3);
344
insert into t3 values(4, 4, 4);
345
insert into t3 values(5, 5, 5);
346


347
delete t3 from t3, t2 where t3.c1 = t2.c2;
348
delete t3 from t3, t2 where t3.c1 = t2.c1;
349
delete t3, t33 from t3 left join t3 as t33 on t3.c1 = t33.c1;
350
ERROR 0A000: multiple aliases to same table not supported
351
select * from t3;
352
+------+------+------+
353
| c1   | c2   | c3   |
354
+------+------+------+
355
+------+------+------+
356
select * from t2;
357
+----+------+------+
358
| c1 | c2   | c3   |
359
+----+------+------+
360
|  1 |    1 |    1 |
361
|  2 |    2 |    2 |
362
|  3 |    2 |    3 |
363
|  4 |    6 |    4 |
364
|  5 |    4 |    3 |
365
|  6 |    4 |    2 |
366
+----+------+------+
367


368
drop table t2;
369
drop table t3;
370


371
drop table if exists dns_inner_resource_record_info;
372
CREATE TABLE `dns_inner_resource_record_info`( 
373
         `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', 
374
         `owner` varchar(256) NOT NULL COMMENT '资源',
375
         `cluster_name` varchar(20) NOT NULL COMMENT '所属集群 冗余字段', 
376
         `zone_name` varchar(255) NOT NULL DEFAULT '' COMMENT 'ZONE NAME', 
377
         `view_group_name` varchar(60) DEFAULT '' COMMENT 'VIEW GRP name', 
378
         `domain_group_name` varchar(60) DEFAULT NULL COMMENT '域名组', 
379
         `idc_id` varchar(20) DEFAULT NULL COMMENT 'IDC ID',
380
         `rr_type` varchar(10) NOT NULL COMMENT '资源类型', 
381
         `ttl` varchar(20) NOT NULL DEFAULT '' COMMENT 'TTL', 
382
         `rr_class` varchar(12) NOT NULL DEFAULT '' COMMENT 'RECODR 类型', 
383
         `data` varchar(500) NOT NULL COMMENT '数据', 
384
         `status` varchar(10) DEFAULT NULL COMMENT '状态', 
385
         `gmt_create` datetime NOT NULL COMMENT '创建时间', 
386
         `gmt_modified` datetime DEFAULT NULL COMMENT '修改时间',
387
         PRIMARY KEY (`id`),
388
         KEY `rrdata` (`data`(383)) BLOCK_SIZE 16384 GLOBAL,
389
         KEY `rrtype` (`rr_type`) BLOCK_SIZE 16384 GLOBAL,
390
         KEY `owner` (`owner`) BLOCK_SIZE 16384 GLOBAL,
391
         KEY `cluster` (`cluster_name`) BLOCK_SIZE 16384 GLOBAL,
392
         KEY `zonename` (`zone_name`) BLOCK_SIZE 16384 GLOBAL,
393
         KEY `viewgroupname` (`view_group_name`) BLOCK_SIZE 16384 GLOBAL,
394
         KEY `domain_group_name` (`domain_group_name`) BLOCK_SIZE 16384 GLOBAL,
395
         KEY `idc_id` (`idc_id`) BLOCK_SIZE 16384 GLOBAL
396
         ) COMMENT = 'dns 内网集群资源记录表';
397
delete from dns_inner_resource_record_info WHERE cluster_name = "1";
398
set binlog_row_image='MINIMAL';
399
delete from dns_inner_resource_record_info WHERE cluster_name = "1";
400
drop table dns_inner_resource_record_info;
401


402

Использование cookies

Мы используем файлы cookie в соответствии с Политикой конфиденциальности и Политикой использования cookies.

Нажимая кнопку «Принимаю», Вы даете АО «СберТех» согласие на обработку Ваших персональных данных в целях совершенствования нашего веб-сайта и Сервиса GitVerse, а также повышения удобства их использования.

Запретить использование cookies Вы можете самостоятельно в настройках Вашего браузера.