1
drop table if exists t1,t2,t3;
4
auto int NOT NULL auto_increment,
5
string char(10) default "hello",
6
tiny int DEFAULT '0' NOT NULL ,
7
short int DEFAULT '1' NOT NULL ,
8
medium int DEFAULT '0' NOT NULL,
9
long_int int DEFAULT '0' NOT NULL,
10
longlong bigint DEFAULT '0' NOT NULL,
11
real_float float DEFAULT 0.0 NOT NULL,
13
utiny int DEFAULT '0' NOT NULL,
14
ushort int DEFAULT '00000' NOT NULL,
15
umedium int DEFAULT '0' NOT NULL,
16
ulong int DEFAULT '0' NOT NULL,
17
ulonglong bigint DEFAULT '0' NOT NULL,
22
tinyblob_col tinyblob,
23
mediumblob_col mediumblob not null default '',
24
longblob_col longblob not null default '',
25
options enum('one','two','tree') not null ,
26
flags enum('one','two','tree') not null default 'one',
31
KEY any_name (medium),
37
KEY (ulonglong,ulong),
41
Field Type Null Default Default_is_NULL On_Update
43
string VARCHAR YES hello NO
46
medium INTEGER NO 0 NO
47
long_int INTEGER NO 0 NO
48
longlong BIGINT NO 0 NO
49
real_float DOUBLE NO 0.0 NO
50
real_double DOUBLE YES YES
52
ushort INTEGER NO 00000 NO
53
umedium INTEGER NO 0 NO
55
ulonglong BIGINT NO 0 NO
56
time_stamp TIMESTAMP YES YES
57
date_field DATE YES YES
58
date_time DATETIME YES YES
60
tinyblob_col BLOB YES YES
61
mediumblob_col BLOB NO NO
62
longblob_col BLOB NO NO
66
Table Unique Key_name Seq_in_index Column_name
71
t1 NO any_name 1 medium
72
t1 NO longlong 1 longlong
73
t1 NO real_float 1 real_float
75
t1 NO umedium 1 umedium
77
t1 NO ulonglong 1 ulonglong
78
t1 NO ulonglong 2 ulong
79
t1 NO options 1 options
81
CREATE UNIQUE INDEX test on t1 ( auto ) ;
82
CREATE INDEX test2 on t1 ( ulonglong,ulong) ;
83
CREATE INDEX test3 on t1 ( medium ) ;
84
DROP INDEX test ON t1;
85
insert into t1 values (10, 1,1,1,1,1,1,1,1,1,1,1,1,1,NULL,NULL,NULL,1,1,1,1,'one','one');
86
insert into t1 values (NULL,2,2,2,2,2,2,2,2,2,2,2,2,2,NULL,NULL,NULL,NULL,NULL,2,2,'two','one');
87
insert into t1 values (0,1/3,3,3,3,3,3,3,3,3,3,3,3,3,NULL,'19970303','19970303101010','','','','3',3,3);
88
ERROR 22001: Data too long for column 'string' at row 1
89
insert into t1 values (0,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,NULL,19970807,19970403090807,-1,-1,-1,'-1',-1,-1);
90
ERROR HY000: Received an invalid enum value '-1'.
91
insert into t1 values (0,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,NULL,0,0,-4294967295,-4294967295,-4294967295,'-4294967295',0,"tree");
92
ERROR 22001: Data too long for column 'string' at row 1
93
insert into t1 values (0,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,NULL,0,0,4294967295,4294967295,4294967295,'4294967295',0,0);
94
ERROR 22003: Out of range value for column 'tiny' at row 1
95
insert into t1 (tiny) values (1);
96
select auto,string,tiny,short,medium,long_int,longlong,real_float,real_double,utiny,ushort,umedium,ulong,ulonglong,mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000),date_field,date_time,blob_col,tinyblob_col,mediumblob_col,longblob_col from t1;
97
auto string tiny short medium long_int longlong real_float real_double utiny ushort umedium ulong ulonglong mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000) date_field date_time blob_col tinyblob_col mediumblob_col longblob_col
98
10 1 1 1 1 1 1 1 1 1 1 1 1 1 NULL NULL NULL 1 1 1 1
99
11 2 2 2 2 2 2 2 2 2 2 2 2 2 NULL NULL NULL NULL NULL 2 2
100
12 hello 1 1 0 0 0 0 NULL 0 0 0 0 0 NULL NULL NULL NULL NULL
102
auto int NOT NULL auto_increment,
104
mediumblob_col mediumblob not null,
108
INSERT INTO t2 (string,mediumblob_col) SELECT string,mediumblob_col from t1 where auto > 10;
110
auto string mediumblob_col new_field
113
select distinct flags from t1;
116
select flags from t1 where find_in_set("two",flags)>0;
118
select flags from t1 where find_in_set("unknown",flags)>0;
120
select options,flags from t1 where options="ONE" and flags="ONE";
124
select options,flags from t1 where options="one" and flags="one";
129
create table t2 select * from t1;
130
update t2 set string="changed" where auto=16;
131
show columns from t1;
132
Field Type Null Default Default_is_NULL On_Update
134
string VARCHAR YES hello NO
136
short INTEGER NO 1 NO
137
medium INTEGER NO 0 NO
138
long_int INTEGER NO 0 NO
139
longlong BIGINT NO 0 NO
140
real_float DOUBLE NO 0 NO
141
real_double DOUBLE YES YES
142
utiny INTEGER NO 0 NO
143
ushort INTEGER NO 0 NO
144
umedium INTEGER NO 0 NO
145
ulong INTEGER NO 0 NO
146
ulonglong BIGINT NO 0 NO
147
time_stamp TIMESTAMP YES YES
148
date_field DATE YES YES
149
date_time DATETIME YES YES
150
blob_col BLOB YES YES
151
tinyblob_col BLOB YES YES
152
mediumblob_col BLOB NO NO
153
longblob_col BLOB NO NO
156
show columns from t2;
157
Field Type Null Default Default_is_NULL On_Update
159
string VARCHAR YES hello NO
161
short INTEGER NO 1 NO
162
medium INTEGER NO 0 NO
163
long_int INTEGER NO 0 NO
164
longlong BIGINT NO 0 NO
165
real_float DOUBLE NO 0 NO
166
real_double DOUBLE YES YES
167
utiny INTEGER NO 0 NO
168
ushort INTEGER NO 0 NO
169
umedium INTEGER NO 0 NO
170
ulong INTEGER NO 0 NO
171
ulonglong BIGINT NO 0 NO
172
time_stamp TIMESTAMP YES YES
173
date_field DATE YES YES
174
date_time DATETIME YES YES
175
blob_col BLOB YES YES
176
tinyblob_col BLOB YES YES
177
mediumblob_col BLOB NO NO
178
longblob_col BLOB NO NO
181
select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and ((t1.string<>t2.string and (t1.string is not null or t2.string is not null)) or (t1.tiny<>t2.tiny and (t1.tiny is not null or t2.tiny is not null)) or (t1.short<>t2.short and (t1.short is not null or t2.short is not null)) or (t1.medium<>t2.medium and (t1.medium is not null or t2.medium is not null)) or (t1.long_int<>t2.long_int and (t1.long_int is not null or t2.long_int is not null)) or (t1.longlong<>t2.longlong and (t1.longlong is not null or t2.longlong is not null)) or (t1.real_float<>t2.real_float and (t1.real_float is not null or t2.real_float is not null)) or (t1.real_double<>t2.real_double and (t1.real_double is not null or t2.real_double is not null)) or (t1.utiny<>t2.utiny and (t1.utiny is not null or t2.utiny is not null)) or (t1.ushort<>t2.ushort and (t1.ushort is not null or t2.ushort is not null)) or (t1.umedium<>t2.umedium and (t1.umedium is not null or t2.umedium is not null)) or (t1.ulong<>t2.ulong and (t1.ulong is not null or t2.ulong is not null)) or (t1.ulonglong<>t2.ulonglong and (t1.ulonglong is not null or t2.ulonglong is not null)) or (t1.time_stamp<>t2.time_stamp and (t1.time_stamp is not null or t2.time_stamp is not null)) or (t1.date_field<>t2.date_field and (t1.date_field is not null or t2.date_field is not null)) or (t1.date_time<>t2.date_time and (t1.date_time is not null or t2.date_time is not null)) or (t1.tinyblob_col<>t2.tinyblob_col and (t1.tinyblob_col is not null or t2.tinyblob_col is not null)) or (t1.mediumblob_col<>t2.mediumblob_col and (t1.mediumblob_col is not null or t2.mediumblob_col is not null)) or (t1.options<>t2.options and (t1.options is not null or t2.options is not null)) or (t1.flags<>t2.flags and (t1.flags is not null or t2.flags is not null)));
183
select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and not (t1.string<=>t2.string and t1.tiny<=>t2.tiny and t1.short<=>t2.short and t1.medium<=>t2.medium and t1.long_int<=>t2.long_int and t1.longlong<=>t2.longlong and t1.real_float<=>t2.real_float and t1.real_double<=>t2.real_double and t1.utiny<=>t2.utiny and t1.ushort<=>t2.ushort and t1.umedium<=>t2.umedium and t1.ulong<=>t2.ulong and t1.ulonglong<=>t2.ulonglong and t1.time_stamp<=>t2.time_stamp and t1.date_field<=>t2.date_field and t1.date_time<=>t2.date_time and t1.tinyblob_col<=>t2.tinyblob_col and t1.mediumblob_col<=>t2.mediumblob_col and t1.options<=>t2.options and t1.flags<=>t2.flags);
186
create table t2 (primary key (auto)) select auto+1 as auto,1 as t1, 'a' as t2, repeat('a',256) as t3, binary repeat('b',256) as t4, repeat('a',4096) as t5, binary repeat('b',4096) as t6, '' as t7, binary '' as t8 from t1;
187
show columns from t2;
188
Field Type Null Default Default_is_NULL On_Update
198
select t1,t2,length(t3),length(t4),length(t5),length(t6),t7,t8 from t2;
199
t1 t2 length(t3) length(t4) length(t5) length(t6) t7 t8
200
1 a 256 256 4096 4096
201
1 a 256 256 4096 4096
202
1 a 256 256 4096 4096
204
create table t1 (c int);
205
insert into t1 values(1),(2);
206
create table t2 select * from t1;
207
create table t3 select * from t1, t2;
208
ERROR 42S21: Duplicate column name 'c'
209
create table t3 select t1.c AS c1, t2.c AS c2,1 as "const" from t1, t2;
210
show columns from t3;
211
Field Type Null Default Default_is_NULL On_Update
216
create table t1 ( myfield INT NOT NULL, UNIQUE INDEX (myfield), unique (myfield), index(myfield));
218
create table t1 ( id integer not null primary key );
219
create table t2 ( id integer not null primary key );
220
insert into t1 values (1), (2);
221
insert into t2 values (1);
222
select t1.id as id_A, t2.id as id_B from t1 left join t2 using ( id );
226
select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
230
create table t3 (id_A integer not null, id_B integer null );
231
insert into t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 using ( id );
237
insert into t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
243
create table t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 using ( id );
249
create table t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id);