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
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
|
drop table if exists t1,t2;
CREATE TABLE t1 (c int not null, d char (10) not null);
insert into t1 values(1,""),(2,"a"),(3,"b");
CREATE TEMPORARY TABLE t1 (a int not null, b char (10) not null);
insert into t1 values(4,"e"),(5,"f"),(6,"g");
alter table t1 rename t2;
select * from t1;
c d
1
2 a
3 b
select * from t2;
a b
4 e
5 f
6 g
CREATE TABLE t2 (x int not null, y int not null);
alter table t2 rename t1;
select * from t1;
a b
4 e
5 f
6 g
create TEMPORARY TABLE t2 engine=heap select * from t1;
create TEMPORARY TABLE IF NOT EXISTS t2 (a int) engine=heap;
CREATE TEMPORARY TABLE t1 (a int not null, b char (10) not null);
ERROR 42S01: Table 't1' already exists
ALTER TABLE t1 RENAME t2;
ERROR 42S01: Table 't2' already exists
select * from t2;
a b
4 e
5 f
6 g
alter table t2 add primary key (a,b);
drop table t1,t2;
select * from t1;
c d
1
2 a
3 b
drop table t2;
create temporary table t1 select *,2 as "e" from t1;
select * from t1;
c d e
1 2
2 a 2
3 b 2
drop table t1;
drop table t1;
CREATE TABLE t1 (pkCrash INTEGER PRIMARY KEY,strCrash VARCHAR(255));
INSERT INTO t1 ( pkCrash, strCrash ) VALUES ( 1, '1');
SELECT CONCAT_WS(pkCrash, strCrash) FROM t1;
CONCAT_WS(pkCrash, strCrash)
1
drop table t1;
create temporary table t1 select 1 as 'x';
drop table t1;
CREATE TABLE t1 (x INT);
INSERT INTO t1 VALUES (1), (2), (3);
CREATE TEMPORARY TABLE tmp SELECT *, NULL FROM t1;
drop table t1;
create temporary table t1 (id int(10) not null unique);
create temporary table t2 (id int(10) not null primary key,
val int(10) not null);
insert into t1 values (1),(2),(4);
insert into t2 values (1,1),(2,1),(3,1),(4,2);
select one.id, two.val, elt(two.val,'one','two') from t1 one, t2 two where two.id=one.id order by one.id;
id val elt(two.val,'one','two')
1 1 one
2 1 one
4 2 two
drop table t1,t2;
create temporary table t1 (a int not null);
insert into t1 values (1),(1);
alter table t1 add primary key (a);
ERROR 23000: Duplicate entry '1' for key 1
drop table t1;
CREATE TABLE t1 (
d datetime default NULL
) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('2002-10-24 14:50:32'),('2002-10-24 14:50:33'),('2002-10-24 14:50:34'),('2002-10-24 14:50:34'),('2002-10-24 14:50:34'),('2002-10-24 14:50:35'),('2002-10-24 14:50:35'),('2002-10-24 14:50:35'),('2002-10-24 14:50:35'),('2002-10-24 14:50:36'),('2002-10-24 14:50:36'),('2002-10-24 14:50:36'),('2002-10-24 14:50:36'),('2002-10-24 14:50:37'),('2002-10-24 14:50:37'),('2002-10-24 14:50:37'),('2002-10-24 14:50:37'),('2002-10-24 14:50:38'),('2002-10-24 14:50:38'),('2002-10-24 14:50:38'),('2002-10-24 14:50:39'),('2002-10-24 14:50:39'),('2002-10-24 14:50:39'),('2002-10-24 14:50:39'),('2002-10-24 14:50:40'),('2002-10-24 14:50:40'),('2002-10-24 14:50:40');
flush status;
select * from t1 group by d;
d
2002-10-24 14:50:32
2002-10-24 14:50:33
2002-10-24 14:50:34
2002-10-24 14:50:35
2002-10-24 14:50:36
2002-10-24 14:50:37
2002-10-24 14:50:38
2002-10-24 14:50:39
2002-10-24 14:50:40
show status like "created_tmp%tables";
Variable_name Value
Created_tmp_disk_tables 0
Created_tmp_tables 2
drop table t1;
create temporary table t1 as select 'This is temp. table' A;
create view t1 as select 'This is view' A;
select * from t1;
A
This is temp. table
show create table t1;
Table Create Table
t1 CREATE TEMPORARY TABLE `t1` (
`A` varchar(19) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show create view t1;
View Create View
t1 CREATE ALGORITHM=UNDEFINED VIEW `test`.`t1` AS select _latin1'This is view' AS `A`
drop view t1;
select * from t1;
A
This is temp. table
create view t1 as select 'This is view again' A;
select * from t1;
A
This is temp. table
drop table t1;
select * from t1;
A
This is view again
drop view t1;
create table t1 (a int, b int, index(a), index(b));
create table t2 (c int auto_increment, d varchar(255), primary key (c));
insert into t1 values (3,1),(3,2);
insert into t2 values (NULL, 'foo'), (NULL, 'bar');
select d, c from t1 left join t2 on b = c where a = 3 order by d;
d c
bar 2
foo 1
drop table t1, t2;
|
