create.result

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· RESULT 代码 · 共 1,266 行 · 第 1/5 页

Note	1050	Table 't1' already exists
select * from t1;
1	2	3
1	2	3
0	1	2
0	0	1
drop table t1;
create table t1 (a int not null, b int, primary key (a));
insert into t1 values (1,1);
create table if not exists t1 select 2;
Warnings:
Note	1050	Table 't1' already exists
Warning	1364	Field 'a' doesn't have a default value
select * from t1;
a	b
1	1
0	2
create table if not exists t1 select 3 as 'a',4 as 'b';
Warnings:
Note	1050	Table 't1' already exists
create table if not exists t1 select 3 as 'a',3 as 'b';
ERROR 23000: Duplicate entry '3' for key 1
select * from t1;
a	b
1	1
0	2
3	4
drop table t1;
create table `t1 `(a int);
ERROR 42000: Incorrect table name 't1 '
create database `db1 `;
ERROR 42000: Incorrect database name 'db1 '
create table t1(`a ` int);
ERROR 42000: Incorrect column name 'a '
create table t1 (a int,);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
create table t1 (a int,,b int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b int)' at line 1
create table t1 (,b int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b int)' at line 1
create table t1 (a int, key(a));
create table t2 (b int, foreign key(b) references t1(a), key(b));
drop table if exists t1,t2;
create table t1(id int not null, name char(20));
insert into t1 values(10,'mysql'),(20,'monty- the creator');
create table t2(id int not null);
insert into t2 values(10),(20);
create table t3 like t1;
show create table t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `name` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t3;
id	name
create table if not exists t3 like t1;
Warnings:
Note	1050	Table 't3' already exists
select @@warning_count;
@@warning_count
1
create temporary table t3 like t2;
show create table t3;
Table	Create Table
t3	CREATE TEMPORARY TABLE `t3` (
  `id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t3;
id
drop table t3;
show create table t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `name` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t3;
id	name
drop table t2, t3;
create database mysqltest;
create table mysqltest.t3 like t1;
create temporary table t3 like mysqltest.t3;
show create table t3;
Table	Create Table
t3	CREATE TEMPORARY TABLE `t3` (
  `id` int(11) NOT NULL,
  `name` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
create table t2 like t3;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t2;
id	name
create table t3 like t1;
create table t3 like mysqltest.t3;
ERROR 42S01: Table 't3' already exists
create table non_existing_database.t1 like t1;
ERROR 42000: Unknown database 'non_existing_database'
create table t3 like non_existing_table;
ERROR 42S02: Unknown table 'non_existing_table'
create temporary table t3 like t1;
ERROR 42S01: Table 't3' already exists
create table t3 like `a/a`;
ERROR 42000: Incorrect table name 'a/a'
drop table t1, t2, t3;
drop table t3;
drop database mysqltest;
SET SESSION storage_engine="heap";
SELECT @@storage_engine;
@@storage_engine
MEMORY
CREATE TABLE t1 (a int not null);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1
drop table t1;
SET SESSION storage_engine="gemini";
ERROR 42000: Unknown table engine 'gemini'
SELECT @@storage_engine;
@@storage_engine
MEMORY
CREATE TABLE t1 (a int not null);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1
SET SESSION storage_engine=default;
drop table t1;
create table t1(a int,b int,c int unsigned,d date,e char,f datetime,g time,h blob);
insert into t1(a)values(1);
insert into t1(a,b,c,d,e,f,g,h)
values(2,-2,2,'1825-12-14','a','2003-1-1 3:2:1','4:3:2','binary data');
select * from t1;
a	b	c	d	e	f	g	h
1	NULL	NULL	NULL	NULL	NULL	NULL	NULL
2	-2	2	1825-12-14	a	2003-01-01 03:02:01	04:03:02	binary data
select a, 
ifnull(b,cast(-7 as signed)) as b, 
ifnull(c,cast(7 as unsigned)) as c, 
ifnull(d,cast('2000-01-01' as date)) as d, 
ifnull(e,cast('b' as char)) as e,
ifnull(f,cast('2000-01-01' as datetime)) as f, 
ifnull(g,cast('5:4:3' as time)) as g,
ifnull(h,cast('yet another binary data' as binary)) as h,
addtime(cast('1:0:0' as time),cast('1:0:0' as time)) as dd 
from t1;
a	b	c	d	e	f	g	h	dd
1	-7	7	2000-01-01	b	2000-01-01 00:00:00	05:04:03	yet another binary data	02:00:00
2	-2	2	1825-12-14	a	2003-01-01 03:02:01	04:03:02	binary data	02:00:00
create table t2
select
a, 
ifnull(b,cast(-7                        as signed))   as b,
ifnull(c,cast(7                         as unsigned)) as c,
ifnull(d,cast('2000-01-01'              as date))     as d,
ifnull(e,cast('b'                       as char))     as e,
ifnull(f,cast('2000-01-01'              as datetime)) as f,
ifnull(g,cast('5:4:3'                   as time))     as g,
ifnull(h,cast('yet another binary data' as binary))   as h,
addtime(cast('1:0:0' as time),cast('1:0:0' as time))  as dd
from t1;
explain t2;
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
b	bigint(11)	NO		0	
c	bigint(11)	NO		0	
d	date	YES		NULL	
e	varchar(1)	NO			
f	datetime	YES		NULL	
g	time	YES		NULL	
h	longblob	NO		NULL	
dd	time	YES		NULL	
select * from t2;
a	b	c	d	e	f	g	h	dd
1	-7	7	2000-01-01	b	2000-01-01 00:00:00	05:04:03	yet another binary data	02:00:00
2	-2	2	1825-12-14	a	2003-01-01 03:02:01	04:03:02	binary data	02:00:00
drop table t1, t2;
create table t1 (a tinyint, b smallint, c mediumint, d int, e bigint, f float(3,2), g double(4,3), h decimal(5,4), i year, j date, k timestamp, l datetime, m enum('a','b'), n set('a','b'), o char(10));
create table t2 select ifnull(a,a), ifnull(b,b), ifnull(c,c), ifnull(d,d), ifnull(e,e), ifnull(f,f), ifnull(g,g), ifnull(h,h), ifnull(i,i), ifnull(j,j), ifnull(k,k), ifnull(l,l), ifnull(m,m), ifnull(n,n), ifnull(o,o) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ifnull(a,a)` tinyint(4) default NULL,
  `ifnull(b,b)` smallint(6) default NULL,
  `ifnull(c,c)` mediumint(8) default NULL,
  `ifnull(d,d)` int(11) default NULL,
  `ifnull(e,e)` bigint(20) default NULL,
  `ifnull(f,f)` float(3,2) default NULL,
  `ifnull(g,g)` double(4,3) default NULL,
  `ifnull(h,h)` decimal(5,4) default NULL,
  `ifnull(i,i)` year(4) default NULL,
  `ifnull(j,j)` date default NULL,
  `ifnull(k,k)` timestamp NOT NULL default '0000-00-00 00:00:00',
  `ifnull(l,l)` datetime default NULL,
  `ifnull(m,m)` varchar(1) default NULL,
  `ifnull(n,n)` varchar(3) default NULL,
  `ifnull(o,o)` varchar(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1,t2;
create table t1(str varchar(10) default 'def',strnull varchar(10),intg int default '10',rel double default '3.14');
insert into t1 values ('','',0,0.0);
describe t1;
Field	Type	Null	Key	Default	Extra
str	varchar(10)	YES		def	
strnull	varchar(10)	YES		NULL	
intg	int(11)	YES		10	
rel	double	YES		3.14	
create table t2 select default(str) as str, default(strnull) as strnull, default(intg) as intg, default(rel) as rel from t1;
describe t2;
Field	Type	Null	Key	Default	Extra
str	varchar(10)	YES		NULL	
strnull	varchar(10)	YES		NULL	
intg	int(11)	YES		NULL	
rel	double	YES		NULL	
drop table t1, t2;
create table t1(name varchar(10), age smallint default -1);
describe t1;
Field	Type	Null	Key	Default	Extra
name	varchar(10)	YES		NULL	
age	smallint(6)	YES		-1	
create table t2(name varchar(10), age smallint default - 1);
describe t2;
Field	Type	Null	Key	Default	Extra
name	varchar(10)	YES		NULL	
age	smallint(6)	YES		-1	
drop table t1, t2;
create table t1(cenum enum('a'), cset set('b'));
create table t2(cenum enum('a','a'), cset set('b','b'));
Warnings:
Note	1291	Column 'cenum' has duplicated value 'a' in ENUM
Note	1291	Column 'cset' has duplicated value 'b' in SET
create table t3(cenum enum('a','A','a','c','c'), cset set('b','B','b','d','d'));
Warnings:
Note	1291	Column 'cenum' has duplicated value 'a' in ENUM
Note	1291	Column 'cenum' has duplicated value 'A' in ENUM
Note	1291	Column 'cenum' has duplicated value 'c' in ENUM
Note	1291	Column 'cset' has duplicated value 'b' in SET
Note	1291	Column 'cset' has duplicated value 'B' in SET
Note	1291	Column 'cset' has duplicated value 'd' in SET
drop table t1, t2, t3;
create database mysqltest;
use mysqltest;
select database();
database()
mysqltest
drop database mysqltest;