1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
|
CREATE TABLE t1(num_int1 INT(2) PRIMARY KEY,
num_bit1 BIT(8),
num_float1 FLOAT(5,2),
num_double1 DOUBLE(5,2),
char_enum1 ENUM('A','B','C','D'),
char_set1 SET('a','b','c','d','e'),
str_varchar1 VARCHAR(2),
d1 DATE,
dt1 DATETIME,
ts1 TIMESTAMP,
y1 YEAR,
b1 BOOL);
CREATE TABLE t2(num_int2 INT(2) PRIMARY KEY,
num_bit2 BIT(8),
num_float2 FLOAT(5,2),
num_double2 DOUBLE(5,2),
char_enum2 ENUM('A','B','C','D'),
char_set2 SET('a','b','c','d','e'),
str_varchar2 VARCHAR(2),
d2 DATE,
dt2 DATETIME,
ts2 TIMESTAMP,
y2 YEAR,
b2 BOOL);
#
# SIMLPE INSERT STATEMENT
#
INSERT INTO t1(num_int1,num_bit1,num_float1,num_double1,char_enum1,char_set1,
str_varchar1, d1,dt1,ts1,y1,b1) VALUES(1,b'1000011', 123.45, 123.55,
'A','b,e', 'V','120314',"2012-04-19 13:08:22", '2001-07-22 12:12:12',
2012,0) RETURNING *;
num_int1 num_bit1 num_float1 num_double1 char_enum1 char_set1 str_varchar1 d1 dt1 ts1 y1 b1
1 C 123.45 123.55 A b,e V 2012-03-14 2012-04-19 13:08:22 2001-07-22 12:12:12 2012 0
#
# MULTIPLE ROWS IN SINGLE STATEMENT
#
INSERT INTO t1 VALUES(2,b'1000011', 123.45, 123.55, 'A','b,e', 'V',
'120314',"2012-04-19 13:08:22", '2001-07-22 12:12:12',2012,0),(3,b'1000011',
123.45, 123.55, 'A','b,e', 'V','120314',"2012-04-19 13:08:22",
'2001-07-22 12:12:12',2012,1) RETURNING *;
num_int1 num_bit1 num_float1 num_double1 char_enum1 char_set1 str_varchar1 d1 dt1 ts1 y1 b1
2 C 123.45 123.55 A b,e V 2012-03-14 2012-04-19 13:08:22 2001-07-22 12:12:12 2012 0
3 C 123.45 123.55 A b,e V 2012-03-14 2012-04-19 13:08:22 2001-07-22 12:12:12 2012 1
#
# INSERT...SET...RETURNING
#
INSERT INTO t1 SET num_int1=4,num_bit1=b'1000011',num_float1=124.67,
num_double1=231.12,char_enum1='B',char_set1='a,d,e',
str_varchar1='AB',d1='120314',dt1="2012-04-19 13:08:22",
ts1='2001-07-22 12:12:1',y1=2014,b1=1 RETURNING *;
num_int1 num_bit1 num_float1 num_double1 char_enum1 char_set1 str_varchar1 d1 dt1 ts1 y1 b1
4 C 124.67 231.12 B a,d,e AB 2012-03-14 2012-04-19 13:08:22 2001-07-22 12:12:01 2014 1
#
# INSERT...ON DUPLICATE KEY UPDATE
#
INSERT INTO t1 VALUES (5,b'1000011', 123.45, 123.55,'C','b,e', 'V',
'120314',"2012-04-19 13:08:22", '2001-07-22 12:12:12',2012,0)
ON DUPLICATE KEY UPDATE num_float1=111.111 RETURNING *;
num_int1 num_bit1 num_float1 num_double1 char_enum1 char_set1 str_varchar1 d1 dt1 ts1 y1 b1
5 C 123.45 123.55 C b,e V 2012-03-14 2012-04-19 13:08:22 2001-07-22 12:12:12 2012 0
#
# INSERT...SELECT...RETURNING
#
INSERT INTO t2 SELECT * FROM t1 RETURNING *;
num_int2 num_bit2 num_float2 num_double2 char_enum2 char_set2 str_varchar2 d2 dt2 ts2 y2 b2
1 C 123.45 123.55 A b,e V 2012-03-14 2012-04-19 13:08:22 2001-07-22 12:12:12 2012 0
2 C 123.45 123.55 A b,e V 2012-03-14 2012-04-19 13:08:22 2001-07-22 12:12:12 2012 0
3 C 123.45 123.55 A b,e V 2012-03-14 2012-04-19 13:08:22 2001-07-22 12:12:12 2012 1
4 C 124.67 231.12 B a,d,e AB 2012-03-14 2012-04-19 13:08:22 2001-07-22 12:12:01 2014 1
5 C 123.45 123.55 C b,e V 2012-03-14 2012-04-19 13:08:22 2001-07-22 12:12:12 2012 0
DROP TABLE t1;
DROP TABLE t2;
|