func_group.result

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

execute stmt1;
max(a)
2
execute stmt1;
max(a)
2
execute stmt1;
max(a)
2
deallocate prepare stmt1;
drop table t1;
CREATE TABLE t1 (a int primary key);
INSERT INTO t1 VALUES (1),(2),(3),(4);
SELECT MAX(a) FROM t1 WHERE a > 5;
MAX(a)
NULL
SELECT MIN(a) FROM t1 WHERE a < 0;
MIN(a)
NULL
DROP TABLE t1;
CREATE TABLE t1 (
id int(10) unsigned NOT NULL auto_increment,
val enum('one','two','three') NOT NULL default 'one',
PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO t1 VALUES
(1,'one'),(2,'two'),(3,'three'),(4,'one'),(5,'two');
select val, count(*) from t1 group by val;
val	count(*)
one	2
two	2
three	1
drop table t1;
CREATE TABLE t1 (
id int(10) unsigned NOT NULL auto_increment,
val set('one','two','three') NOT NULL default 'one',
PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO t1 VALUES
(1,'one'),(2,'two'),(3,'three'),(4,'one'),(5,'two');
select val, count(*) from t1 group by val;
val	count(*)
one	2
two	2
three	1
drop table t1;
create table t1(a int, b datetime);
insert into t1 values (1, NOW()), (2, NOW());
create table t2 select MAX(b) from t1 group by a;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `MAX(b)` datetime default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1(f1 datetime);
insert into t1 values (now());
create table t2 select f2 from (select max(now()) f2 from t1) a;
show columns from t2;
Field	Type	Null	Key	Default	Extra
f2	datetime	YES		NULL	
drop table t2;
create table t2 select f2 from (select now() f2 from t1) a;
show columns from t2;
Field	Type	Null	Key	Default	Extra
f2	datetime	NO		0000-00-00 00:00:00	
drop table t2, t1;
CREATE TABLE t1(
id int PRIMARY KEY,
a  int,
b  int,
INDEX i_b_id(a,b,id),
INDEX i_id(a,id)
);
INSERT INTO t1 VALUES 
(1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1);
SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;
MAX(id)
NULL
DROP TABLE t1;
CREATE TABLE t1(
id int PRIMARY KEY,
a  int,
b  int,
INDEX i_id(a,id),
INDEX i_b_id(a,b,id)
);
INSERT INTO t1 VALUES 
(1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1);
SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;
MAX(id)
NULL
DROP TABLE t1;
create table t1m (a int) engine=myisam;
create table t1i (a int) engine=innodb;
create table t2m (a int) engine=myisam;
create table t2i (a int) engine=innodb;
insert into t2m values (5);
insert into t2i values (5);
select min(a) from t1m;
min(a)
NULL
select min(7) from t1m;
min(7)
NULL
select min(7) from DUAL;
min(7)
NULL
explain select min(7) from t2m join t1m;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
select min(7) from t2m join t1m;
min(7)
NULL
select max(a) from t1m;
max(a)
NULL
select max(7) from t1m;
max(7)
NULL
select max(7) from DUAL;
max(7)
NULL
explain select max(7) from t2m join t1m;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
select max(7) from t2m join t1m;
max(7)
NULL
select 1, min(a) from t1m where a=99;
1	min(a)
1	NULL
select 1, min(a) from t1m where 1=99;
1	min(a)
1	NULL
select 1, min(1) from t1m where a=99;
1	min(1)
1	NULL
select 1, min(1) from t1m where 1=99;
1	min(1)
1	NULL
select 1, max(a) from t1m where a=99;
1	max(a)
1	NULL
select 1, max(a) from t1m where 1=99;
1	max(a)
1	NULL
select 1, max(1) from t1m where a=99;
1	max(1)
1	NULL
select 1, max(1) from t1m where 1=99;
1	max(1)
1	NULL
select min(a) from t1i;
min(a)
NULL
select min(7) from t1i;
min(7)
NULL
select min(7) from DUAL;
min(7)
NULL
explain select min(7) from t2i join t1i;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2i	ALL	NULL	NULL	NULL	NULL	1	
1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	
select min(7) from t2i join t1i;
min(7)
NULL
select max(a) from t1i;
max(a)
NULL
select max(7) from t1i;
max(7)
NULL
select max(7) from DUAL;
max(7)
NULL
explain select max(7) from t2i join t1i;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2i	ALL	NULL	NULL	NULL	NULL	1	
1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	
select max(7) from t2i join t1i;
max(7)
NULL
select 1, min(a) from t1i where a=99;
1	min(a)
1	NULL
select 1, min(a) from t1i where 1=99;
1	min(a)
1	NULL
select 1, min(1) from t1i where a=99;
1	min(1)
1	NULL
select 1, min(1) from t1i where 1=99;
1	min(1)
1	NULL
select 1, max(a) from t1i where a=99;
1	max(a)
1	NULL
select 1, max(a) from t1i where 1=99;
1	max(a)
1	NULL
select 1, max(1) from t1i where a=99;
1	max(1)
1	NULL
select 1, max(1) from t1i where 1=99;
1	max(1)
1	NULL
explain select count(*), min(7), max(7) from t1m, t1i;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1m	system	NULL	NULL	NULL	NULL	0	const row not found
1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	
select count(*), min(7), max(7) from t1m, t1i;
count(*)	min(7)	max(7)
0	NULL	NULL
explain select count(*), min(7), max(7) from t1m, t2i;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1m	system	NULL	NULL	NULL	NULL	0	const row not found
1	SIMPLE	t2i	ALL	NULL	NULL	NULL	NULL	1	
select count(*), min(7), max(7) from t1m, t2i;
count(*)	min(7)	max(7)
0	NULL	NULL
explain select count(*), min(7), max(7) from t2m, t1i;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2m	system	NULL	NULL	NULL	NULL	1	
1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	
select count(*), min(7), max(7) from t2m, t1i;
count(*)	min(7)	max(7)
0	NULL	NULL
drop table t1m, t1i, t2m, t2i;
create table t2 (ff double);
insert into t2 values (2.2);
select cast(sum(distinct ff) as decimal(5,2)) from t2;
cast(sum(distinct ff) as decimal(5,2))
2.20
select cast(sum(distinct ff) as signed) from t2;
cast(sum(distinct ff) as signed)
2
select cast(variance(ff) as decimal(10,3)) from t2;
cast(variance(ff) as decimal(10,3))
0.000
select cast(min(ff) as decimal(5,2)) from t2;
cast(min(ff) as decimal(5,2))
2.20
create table t1 (df decimal(5,1));
insert into t1 values(1.1);
insert into t1 values(2.2);
select cast(sum(distinct df) as signed) from t1;
cast(sum(distinct df) as signed)
3
select cast(min(df) as signed) from t1;
cast(min(df) as signed)
0
select 1e8 * sum(distinct df) from t1;
1e8 * sum(distinct df)
330000000
select 1e8 * min(df) from t1;
1e8 * min(df)
110000000
create table t3 (ifl int);
insert into t3 values(1), (2);
select cast(min(ifl) as decimal(5,2)) from t3;
cast(min(ifl) as decimal(5,2))
1.00
drop table t1, t2, t3;
CREATE TABLE t1 (id int(11),value1 float(10,2));
INSERT INTO t1 VALUES (1,0.00),(1,1.00), (1,2.00), (2,10.00), (2,11.00), (2,12.00), (2,13.00);
select id, stddev_pop(value1), var_pop(value1), stddev_samp(value1), var_samp(value1) from t1 group by id;
id	stddev_pop(value1)	var_pop(value1)	stddev_samp(value1)	var_samp(value1)
1	0.816497	0.666667	1.000000	1.000000
2	1.118034	1.250000	1.290994	1.666667
DROP TABLE t1;
CREATE TABLE t1 (col1 decimal(16,12));
INSERT INTO t1 VALUES (-5.00000000001),(-5.00000000002),(-5.00000000003),(-5.00000000000),(-5.00000000001),(-5.00000000002);
insert into t1 select * from t1;
select col1,count(col1),sum(col1),avg(col1) from t1 group by col1;
col1	count(col1)	sum(col1)	avg(col1)
-5.000000000030	2	-10.000000000060	-5.00000000003000000
-5.000000000020	4	-20.000000000080	-5.00000000002000000
-5.000000000010	4	-20.000000000040	-5.00000000001000000
-5.000000000000	2	-10.000000000000	-5.00000000000000000
DROP TABLE t1;
create table t1 (col1 decimal(16,12));
insert into t1 values (-5.00000000001);
insert into t1 values (-5.00000000001);
select col1,sum(col1),max(col1),min(col1) from t1 group by col1;
col1	sum(col1)	max(col1)	min(col1)
-5.000000000010	-10.000000000020	-5.000000000010	-5.000000000010
delete from t1;
insert into t1 values (5.00000000001);
insert into t1 values (5.00000000001);
select col1,sum(col1),max(col1),min(col1) from t1 group by col1;
col1	sum(col1)	max(col1)	min(col1)
5.000000000010	10.000000000020	5.000000000010	5.000000000010
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(400));
INSERT INTO t1 (a) VALUES ("A"), ("a"), ("a "), ("a   "),
("B"), ("b"), ("b "), ("b   ");
SELECT COUNT(DISTINCT a) FROM t1;
COUNT(DISTINCT a)
2
DROP TABLE t1;
CREATE TABLE t1 (a int, b int, c int);
INSERT INTO t1 (a, b, c) VALUES
(1,1,1), (1,1,2), (1,1,3),
(1,2,1), (1,2,2), (1,2,3),
(1,3,1), (1,3,2), (1,3,3),
(2,1,1), (2,1,2), (2,1,3),
(2,2,1), (2,2,2), (2,2,3),
(2,3,1), (2,3,2), (2,3,3),
(3,1,1), (3,1,2), (3,1,3),
(3,2,1), (3,2,2), (3,2,3),
(3,3,1), (3,3,2), (3,3,3);
SELECT b/c as v, a FROM t1 ORDER BY v;
v	a
0.33333	3
0.33333	1
0.33333	2
0.50000	1
0.50000	2
0.50000	3
0.66667	2
0.66667	1
0.66667	3
1.00000	3
1.00000	2
1.00000	3
1.00000	1
1.00000	2
1.00000	3
1.00000	2
1.00000	1
1.00000	1
1.50000	3
1.50000	2
1.50000	1
2.00000	1
2.00000	3
2.00000	2
3.00000	3
3.00000	2
3.00000	1
SELECT b/c as v, SUM(a) FROM t1 GROUP BY v;
v	SUM(a)
0.33333	6
0.50000	6
0.66667	6
1.00000	18
1.50000	6
2.00000	6
3.00000	6
SELECT SUM(a) FROM t1 GROUP BY b/c;
SUM(a)
6
6
6
18
6
6
6
DROP TABLE t1;
set div_precision_increment= @sav_dpi;