subselect.result

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

select ROW(1, 1, 'a') IN (select b,a,c from t1);
ROW(1, 1, 'a') IN (select b,a,c from t1)
1
select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);
ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null)
1
select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);
ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null)
0
select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);
ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null)
1
select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');
ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a')
1
select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');
ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a')
0
select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');
ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a')
1
select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
drop table t1;
create table t1 (a int);
insert into t1 values (1);
do @a:=(SELECT a from t1);
select @a;
@a
1
set @a:=2;
set @a:=(SELECT a from t1);
select @a;
@a
1
drop table t1;
do (SELECT a from t1);
ERROR 42S02: Table 'test.t1' doesn't exist
set @a:=(SELECT a from t1);
ERROR 42S02: Table 'test.t1' doesn't exist
CREATE TABLE t1 (a int, KEY(a));
HANDLER t1 OPEN;
HANDLER t1 READ a=((SELECT 1));
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 'SELECT 1))' at line 1
HANDLER t1 CLOSE;
drop table t1;
create table t1 (a int);
create table t2 (b int);
insert into t1 values (1),(2);
insert into t2 values (1);
select a from t1 where a in (select a from t1 where a in (select b from t2));
a
1
drop table t1, t2;
create table t1 (a int, b int);
create table t2 like t1;
insert into t1 values (1,2),(1,3),(1,4),(1,5);
insert into t2 values (1,2),(1,3);
select * from t1 where row(a,b) in (select a,b from t2);
a	b
1	2
1	3
drop table t1, t2;
CREATE TABLE `t1` (`i` int(11) NOT NULL default '0',PRIMARY KEY  (`i`)) ENGINE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES (1);
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
select * from t1;
i
1
drop table t1;
CREATE TABLE t1 (a int(1));
EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
2	UNCACHEABLE SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
Warnings:
Note	1003	select sql_no_cache (select sql_no_cache rand() AS `RAND()` from `test`.`t1`) AS `(SELECT RAND() FROM t1)` from `test`.`t1`
EXPLAIN EXTENDED SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
2	UNCACHEABLE SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
Warnings:
Note	1003	select sql_no_cache (select sql_no_cache encrypt(_latin1'test') AS `ENCRYPT('test')` from `test`.`t1`) AS `(SELECT ENCRYPT('test') FROM t1)` from `test`.`t1`
EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
2	UNCACHEABLE SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
Warnings:
Note	1003	select sql_no_cache (select sql_no_cache benchmark(1,1) AS `BENCHMARK(1,1)` from `test`.`t1`) AS `(SELECT BENCHMARK(1,1) FROM t1)` from `test`.`t1`
drop table t1;
CREATE TABLE `t1` (
`mot` varchar(30) character set latin1 NOT NULL default '',
`topic` mediumint(8) unsigned NOT NULL default '0',
`date` date NOT NULL default '0000-00-00',
`pseudo` varchar(35) character set latin1 NOT NULL default '',
PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
KEY `pseudo` (`pseudo`,`date`,`topic`),
KEY `topic` (`topic`)
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
CREATE TABLE `t2` (
`mot` varchar(30) character set latin1 NOT NULL default '',
`topic` mediumint(8) unsigned NOT NULL default '0',
`date` date NOT NULL default '0000-00-00',
`pseudo` varchar(35) character set latin1 NOT NULL default '',
PRIMARY KEY  (`mot`,`pseudo`,`date`,`topic`),
KEY `pseudo` (`pseudo`,`date`,`topic`),
KEY `topic` (`topic`)
) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
CREATE TABLE `t3` (
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
`maxnumrep` int(10) unsigned NOT NULL default '0',
PRIMARY KEY  (`numeropost`),
UNIQUE KEY `maxnumrep` (`maxnumrep`)
) ENGINE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES ('joce','1','','joce'),('test','2','','test');
Warnings:
Warning	1265	Data truncated for column 'date' at row 1
Warning	1265	Data truncated for column 'date' at row 2
INSERT INTO t2 VALUES ('joce','1','','joce'),('test','2','','test');
Warnings:
Warning	1265	Data truncated for column 'date' at row 1
Warning	1265	Data truncated for column 'date' at row 2
INSERT INTO t3 VALUES (1,1);
SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE
numeropost=topic);
topic
2
select * from t1;
mot	topic	date	pseudo
joce	1	0000-00-00	joce
test	2	0000-00-00	test
DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT
EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
select * from t1;
mot	topic	date	pseudo
joce	1	0000-00-00	joce
drop table t1, t2, t3;
SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
a	(SELECT a)
1	1
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bigint(1) NOT NULL default '0',
  `(SELECT 1)` bigint(1) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bigint(1) NOT NULL default '0',
  `(SELECT a)` bigint(1) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bigint(1) NOT NULL default '0',
  `(SELECT a+0)` bigint(3) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
CREATE TABLE t1 SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
select * from t1;
a
2
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bigint(20) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 (a int);
insert into t1 values (1), (2), (3);
explain extended select a,(select (select rand() from t1 limit 1)  from t1 limit 1)
from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
2	UNCACHEABLE SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
3	UNCACHEABLE SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
Warnings:
Note	1003	select sql_no_cache `test`.`t1`.`a` AS `a`,(select sql_no_cache (select sql_no_cache rand() AS `rand()` from `test`.`t1` limit 1) AS `(select rand() from t1 limit 1)` from `test`.`t1` limit 1) AS `(select (select rand() from t1 limit 1)  from t1 limit 1)` from `test`.`t1`
drop table t1;
select t1.Continent, t2.Name, t2.Population from t1 LEFT JOIN t2 ON t1.Code = t2.Country  where t2.Population IN (select max(t2.Population) AS Population from t2, t1 where t2.Country = t1.Code group by Continent);
ERROR 42S02: Table 'test.t1' doesn't exist
CREATE TABLE t1 (
ID int(11) NOT NULL auto_increment,
name char(35) NOT NULL default '',
t2 char(3) NOT NULL default '',
District char(20) NOT NULL default '',
Population int(11) NOT NULL default '0',
PRIMARY KEY  (ID)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES (130,'Sydney','AUS','New South Wales',3276207);
INSERT INTO t1 VALUES (131,'Melbourne','AUS','Victoria',2865329);
INSERT INTO t1 VALUES (132,'Brisbane','AUS','Queensland',1291117);
CREATE TABLE t2 (
Code char(3) NOT NULL default '',
Name char(52) NOT NULL default '',
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',
Region char(26) NOT NULL default '',
SurfaceArea float(10,2) NOT NULL default '0.00',
IndepYear smallint(6) default NULL,
Population int(11) NOT NULL default '0',
LifeExpectancy float(3,1) default NULL,
GNP float(10,2) default NULL,
GNPOld float(10,2) default NULL,
LocalName char(45) NOT NULL default '',
GovernmentForm char(45) NOT NULL default '',
HeadOfState char(60) default NULL,
Capital int(11) default NULL,
Code2 char(2) NOT NULL default '',
PRIMARY KEY  (Code)
) ENGINE=MyISAM;
INSERT INTO t2 VALUES ('AUS','Australia','Oceania','Australia and New Zealand',7741220.00,1901,18886000,79.8,351182.00,392911.00,'Australia','Constitutional Monarchy, Federation','Elisabeth II',135,'AU');
INSERT INTO t2 VALUES ('AZE','Azerbaijan','Asia','Middle East',86600.00,1991,7734000,62.9,4127.00,4100.00,'Az鋜baycan','Federal Republic','Heyd鋜 膌iyev',144,'AZ');
select t2.Continent, t1.Name, t1.Population from t2 LEFT JOIN t1 ON t2.Code = t1.t2  where t1.Population IN (select max(t1.Population) AS Population from t1, t2 where t1.t2 = t2.Code group by Continent);
Continent	Name	Population
Oceania	Sydney	3276207
drop table t1, t2;
CREATE TABLE `t1` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`pseudo` varchar(35) character set latin1 NOT NULL default '',
PRIMARY KEY  (`id`),
UNIQUE KEY `pseudo` (`pseudo`)
) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
INSERT INTO t1 (pseudo) VALUES ('test');
SELECT 0 IN (SELECT 1 FROM t1 a);
0 IN (SELECT 1 FROM t1 a)
0
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a`)) AS `0 IN (SELECT 1 FROM t1 a)`
INSERT INTO t1 (pseudo) VALUES ('test1');
SELECT 0 IN (SELECT 1 FROM t1 a);
0 IN (SELECT 1 FROM t1 a)
0
EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a`)) AS `0 IN (SELECT 1 FROM t1 a)`
drop table t1;
CREATE TABLE `t1` (
`i` int(11) NOT NULL default '0',
PRIMARY KEY  (`i`)
) ENGINE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES (1);
UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i));
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t);
ERROR 42S22: Unknown column 't.i' in 'field list'
select * from t1;
i
1
drop table t1;
CREATE TABLE t1 (
id int(11) default NULL
) ENGINE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES (1),(1),(2),(2),(1),(3);
CREATE TABLE t2 (
id int(11) default NULL,
name varchar(15) default NULL
) ENGINE=MyISAM CHARSET=latin1;
INSERT INTO t2 VALUES (4,'vita'), (1,'vita'), (2,'vita'), (1,'vita');
update t1, t2 set t2.name='lenka' where t2.id in (select id from t1);
select * from t2;
id	name
4	vita
1	lenka
2	lenka
1	lenka
drop table t1,t2;
create table t1 (a int, unique index indexa (a));
insert into t1 values (-1), (-4), (-2), (NULL);
select -10 IN (select a from t1 FORCE INDEX (indexa));
-10 IN (select a from t1 FORCE INDEX (indexa))
NULL
drop table t1;
create table t1 (id int not null auto_increment primary key, salary int, key(salary));
insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ref	salary	salary	5	const	1	Using where
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
Warnings:
Note	1003	select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) AS `MAX(salary)` from `test`.`t1`))
drop table t1;
CREATE TABLE t1 (
ID int(10) unsigned NOT NULL auto_increment,
SUB_ID int(3) unsigned NOT NULL default '0',
REF_ID int(10) unsigned default NULL,
REF_SUB int(3) unsigned default '0',
PRIMARY KEY (ID,SUB_ID),
UNIQUE KEY t1_PK (ID,SUB_ID),
KEY t1_FK (REF_ID,REF_SUB),
KEY t1_REFID (REF_ID)
) ENGINE=MyISAM CHARSET=cp1251;
INSERT INTO t1 VALUES (1,0,NULL,NULL),(2,0,NULL,NULL);
SELECT DISTINCT REF_ID FROM t1 WHERE ID= (SELECT DISTINCT REF_ID FROM t1 WHERE ID=2);
REF_ID
DROP TABLE t1;
create table t1 (a int, b int);
create table t2 (a int, b int);
insert into t1 values (1,0), (2,0), (3,0);
insert into t2 values (1,1), (2,1), (3,1), (2,2);
update ignore t1 set b=(select b from t2 where t1.a=t2.a);
Warnings:
Error	1242	Subquery returns more than 1 row