type_newdecimal.result

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

drop table if exists t1;
select 1.1 IN (1.0, 1.2);
1.1 IN (1.0, 1.2)
0
select 1.1 IN (1.0, 1.2, 1.1, 1.4, 0.5);
1.1 IN (1.0, 1.2, 1.1, 1.4, 0.5)
1
select 1.1 IN (1.0, 1.2, NULL, 1.4, 0.5);
1.1 IN (1.0, 1.2, NULL, 1.4, 0.5)
NULL
select 0.5 IN (1.0, 1.2, NULL, 1.4, 0.5);
0.5 IN (1.0, 1.2, NULL, 1.4, 0.5)
1
select 1 IN (1.11, 1.2, 1.1, 1.4, 1, 0.5);
1 IN (1.11, 1.2, 1.1, 1.4, 1, 0.5)
1
select 1 IN (1.11, 1.2, 1.1, 1.4, NULL, 0.5);
1 IN (1.11, 1.2, 1.1, 1.4, NULL, 0.5)
NULL
select case 1.0 when 0.1 then "a" when 1.0 then "b" else "c" END;
case 1.0 when 0.1 then "a" when 1.0 then "b" else "c" END
b
select case 0.1 when 0.1 then "a" when 1.0 then "b" else "c" END;
case 0.1 when 0.1 then "a" when 1.0 then "b" else "c" END
a
select case 1 when 0.1 then "a" when 1.0 then "b" else "c" END;
case 1 when 0.1 then "a" when 1.0 then "b" else "c" END
b
select case 1.0 when 0.1 then "a" when 1 then "b" else "c" END;
case 1.0 when 0.1 then "a" when 1 then "b" else "c" END
b
select case 1.001 when 0.1 then "a" when 1 then "b" else "c" END;
case 1.001 when 0.1 then "a" when 1 then "b" else "c" END
c
create table t1 (a decimal(6,3));
insert into t1 values (1.0), (NULL), (0.1);
select * from t1;
a
1.000
NULL
0.100
select 0.1 in (1.0, 1.2, 1.1, a, 1.4, 0.5) from t1;
0.1 in (1.0, 1.2, 1.1, a, 1.4, 0.5)
0
NULL
1
drop table t1;
create table t1 select if(1, 1.1, 1.2), if(0, 1.1, 1.2), if(0.1, 1.1, 1.2), if(0, 1, 1.1), if(0, NULL, 1.2), if(1, 0.22e1, 1.1), if(1E0, 1.1, 1.2);
select * from t1;
if(1, 1.1, 1.2)	if(0, 1.1, 1.2)	if(0.1, 1.1, 1.2)	if(0, 1, 1.1)	if(0, NULL, 1.2)	if(1, 0.22e1, 1.1)	if(1E0, 1.1, 1.2)
1.1	1.2	1.1	1.1	1.2	2.2	1.1
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `if(1, 1.1, 1.2)` decimal(2,1) NOT NULL default '0.0',
  `if(0, 1.1, 1.2)` decimal(2,1) NOT NULL default '0.0',
  `if(0.1, 1.1, 1.2)` decimal(2,1) NOT NULL default '0.0',
  `if(0, 1, 1.1)` decimal(2,1) NOT NULL default '0.0',
  `if(0, NULL, 1.2)` decimal(2,1) default NULL,
  `if(1, 0.22e1, 1.1)` double NOT NULL default '0',
  `if(1E0, 1.1, 1.2)` decimal(2,1) NOT NULL default '0.0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 select nullif(1.1, 1.1), nullif(1.1, 1.2), nullif(1.1, 0.11e1), nullif(1.0, 1), nullif(1, 1.0), nullif(1, 1.1);
select * from t1;
nullif(1.1, 1.1)	nullif(1.1, 1.2)	nullif(1.1, 0.11e1)	nullif(1.0, 1)	nullif(1, 1.0)	nullif(1, 1.1)
NULL	1.1	NULL	NULL	NULL	1
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `nullif(1.1, 1.1)` decimal(2,1) unsigned default NULL,
  `nullif(1.1, 1.2)` decimal(2,1) unsigned default NULL,
  `nullif(1.1, 0.11e1)` decimal(2,1) unsigned default NULL,
  `nullif(1.0, 1)` decimal(2,1) unsigned default NULL,
  `nullif(1, 1.0)` int(1) default NULL,
  `nullif(1, 1.1)` int(1) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 (a decimal(4,2));
insert into t1 value (10000), (1.1e10), ("11111"), (100000.1);
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 1
Warning	1264	Out of range value adjusted for column 'a' at row 2
Warning	1264	Out of range value adjusted for column 'a' at row 3
Warning	1264	Out of range value adjusted for column 'a' at row 4
insert into t1 value (-10000), (-1.1e10), ("-11111"), (-100000.1);
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 1
Warning	1264	Out of range value adjusted for column 'a' at row 2
Warning	1264	Out of range value adjusted for column 'a' at row 3
Warning	1264	Out of range value adjusted for column 'a' at row 4
select a from t1;
a
99.99
99.99
99.99
99.99
-99.99
-99.99
-99.99
-99.99
drop table t1;
create table t1 (a decimal(4,2) unsigned);
insert into t1 value (10000), (1.1e10), ("11111"), (100000.1);
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 1
Warning	1264	Out of range value adjusted for column 'a' at row 2
Warning	1264	Out of range value adjusted for column 'a' at row 3
Warning	1264	Out of range value adjusted for column 'a' at row 4
insert into t1 value (-10000), (-1.1e10), ("-11111"), (-100000.1);
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 1
Warning	1264	Out of range value adjusted for column 'a' at row 2
Warning	1264	Out of range value adjusted for column 'a' at row 3
Warning	1264	Out of range value adjusted for column 'a' at row 4
select a from t1;
a
99.99
99.99
99.99
99.99
0.00
0.00
0.00
0.00
drop table t1;
create table t1 (a bigint);
insert into t1 values (18446744073709551615.0);
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 1
insert into t1 values (9223372036854775808.0);
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 1
insert into t1 values (-18446744073709551615.0);
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 1
select * from t1;
a
9223372036854775807
9223372036854775807
-9223372036854775808
drop table t1;
create table t1 (a bigint unsigned);
insert into t1 values (18446744073709551615.0);
insert into t1 values (9223372036854775808.0);
insert into t1 values (9999999999999999999999999.000);
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 1
insert into t1 values (-1.0);
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 1
select * from t1;
a
18446744073709551615
9223372036854775808
18446744073709551615
0
drop table t1;
create table t1 (a tinyint);
insert into t1 values (18446744073709551615.0);
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 1
Warning	1264	Out of range value adjusted for column 'a' at row 1
insert into t1 values (9223372036854775808.0);
Warnings:
Warning	1264	Out of range value adjusted for column 'a' at row 1
Warning	1264	Out of range value adjusted for column 'a' at row 1
select * from t1;
a
127
127
drop table t1;
create table t1 select round(15.4,-1), truncate(-5678.123451,-3), abs(-1.1), -(-1.1);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `round(15.4,-1)` decimal(3,0) unsigned NOT NULL default '0',
  `truncate(-5678.123451,-3)` decimal(4,0) NOT NULL default '0',
  `abs(-1.1)` decimal(2,1) NOT NULL default '0.0',
  `-(-1.1)` decimal(2,1) NOT NULL default '0.0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
set session sql_mode='traditional';
select 1e10/0e0;
1e10/0e0
NULL
Warnings:
Error	1365	Division by 0
create table wl1612 (col1 int, col2 decimal(38,10), col3 numeric(38,10));
insert into wl1612 values(1,12345678901234567890.1234567890,12345678901234567890.1234567890);
select * from wl1612;
col1	col2	col3
1	12345678901234567890.1234567890	12345678901234567890.1234567890
insert into wl1612 values(2,01234567890123456789.0123456789,01234567890123456789.0123456789);
select * from wl1612 where col1=2;
col1	col2	col3
2	1234567890123456789.0123456789	1234567890123456789.0123456789
insert into wl1612 values(3,1234567890123456789012345678.0123456789,1234567890123456789012345678.0123456789);
select * from wl1612 where col1=3;
col1	col2	col3
3	1234567890123456789012345678.0123456789	1234567890123456789012345678.0123456789
select col1/0 from wl1612;
col1/0
NULL
NULL
NULL
Warnings:
Error	1365	Division by 0
Error	1365	Division by 0
Error	1365	Division by 0
select col2/0 from wl1612;
col2/0
NULL
NULL
NULL
Warnings:
Error	1365	Division by 0
Error	1365	Division by 0
Error	1365	Division by 0
select col3/0 from wl1612;
col3/0
NULL
NULL
NULL
Warnings:
Error	1365	Division by 0
Error	1365	Division by 0
Error	1365	Division by 0
insert into wl1612 values(5,5000.0005,5000.0005);
insert into wl1612 values(6,5000.0005,5000.0005);
select sum(col2),sum(col3) from wl1612;
sum(col2)	sum(col3)
1234567903703703580370380357.1491481468	1234567903703703580370380357.1491481468
insert into wl1612 values(7,500000.000005,500000.000005);
insert into wl1612 values(8,500000.000005,500000.000005);
select sum(col2),sum(col3) from wl1612 where col1>4;
sum(col2)	sum(col3)
1010000.0010100000	1010000.0010100000
insert into wl1612 (col1, col2) values(9,1.01234567891);
Warnings:
Note	1265	Data truncated for column 'col2' at row 1
insert into wl1612 (col1, col2) values(10,1.01234567894);
Warnings:
Note	1265	Data truncated for column 'col2' at row 1
insert into wl1612 (col1, col2) values(11,1.01234567895);
Warnings:
Note	1265	Data truncated for column 'col2' at row 1
insert into wl1612 (col1, col2) values(12,1.01234567896);
Warnings:
Note	1265	Data truncated for column 'col2' at row 1
select col1,col2 from wl1612 where col1>8;
col1	col2
9	1.0123456789
10	1.0123456789
11	1.0123456790
12	1.0123456790
insert into wl1612 (col1, col3) values(13,1.01234567891);
Warnings:
Note	1265	Data truncated for column 'col3' at row 1
insert into wl1612 (col1, col3) values(14,1.01234567894);
Warnings:
Note	1265	Data truncated for column 'col3' at row 1
insert into wl1612 (col1, col3) values(15,1.01234567895);
Warnings:
Note	1265	Data truncated for column 'col3' at row 1
insert into wl1612 (col1, col3) values(16,1.01234567896);
Warnings:
Note	1265	Data truncated for column 'col3' at row 1
select col1,col3 from wl1612 where col1>12;
col1	col3
13	1.0123456789
14	1.0123456789
15	1.0123456790
16	1.0123456790
select col1 from wl1612 where col1>4 and col2=1.01234567891;
col1
select col1 from wl1612 where col1>4 and col2=1.0123456789;
col1
9
10
select col1 from wl1612 where col1>4 and col2<>1.0123456789;
col1
5
6
7
8
11
12
select col1 from wl1612 where col1>4 and col2<1.0123456789;
col1
select col1 from wl1612 where col1>4 and col2<=1.0123456789;
col1
9
10
select col1 from wl1612 where col1>4 and col2>1.0123456789;
col1
5
6
7
8
11
12
select col1 from wl1612 where col1>4 and col2>=1.0123456789;
col1
5
6
7
8
9
10
11
12
select col1 from wl1612 where col1>4 and col2=1.012345679;
col1
11
12
select col1 from wl1612 where col1>4 and col2<>1.012345679;
col1
5
6
7
8
9
10
select col1 from wl1612 where col1>4 and col3=1.01234567891;
col1
select col1 from wl1612 where col1>4 and col3=1.0123456789;
col1
13
14
select col1 from wl1612 where col1>4 and col3<>1.0123456789;
col1
5
6
7
8
15
16
select col1 from wl1612 where col1>4 and col3<1.0123456789;
col1
select col1 from wl1612 where col1>4 and col3<=1.0123456789;
col1
13
14
select col1 from wl1612 where col1>4 and col3>1.0123456789;
col1
5
6
7
8
15
16
select col1 from wl1612 where col1>4 and col3>=1.0123456789;
col1
5
6
7
8
13
14
15
16
select col1 from wl1612 where col1>4 and col3=1.012345679;
col1
15
16
select col1 from wl1612 where col1>4 and col3<>1.012345679;
col1
5
6
7
8
13
14
drop table wl1612;
select 1/3;
1/3
0.3333
select 0.8=0.7+0.1;
0.8=0.7+0.1
1
select 0.7+0.1;
0.7+0.1
0.8
create table wl1612_1 (col1 int);
insert into wl1612_1 values(10);
select * from wl1612_1 where 0.8=0.7+0.1;
col1
10
select 0.07+0.07 from wl1612_1;
0.07+0.07
0.14
select 0.07-0.07 from wl1612_1;
0.07-0.07
0.00
select 0.07*0.07 from wl1612_1;
0.07*0.07
0.0049
select 0.07/0.07 from wl1612_1;
0.07/0.07
1.000000
drop table wl1612_1;
create table wl1612_2 (col1 decimal(10,2), col2 numeric(10,2));
insert into wl1612_2 values(1,1);
insert into wl1612_2 values(+1,+1);
insert into wl1612_2 values(+01,+01);
insert into wl1612_2 values(+001,+001);
select col1,count(*) from wl1612_2 group by col1;
col1	count(*)
1.00	4
select col2,count(*) from wl1612_2 group by col2;
col2	count(*)
1.00	4
drop table wl1612_2;
create table wl1612_3 (col1 decimal(10,2), col2 numeric(10,2));
insert into wl1612_3 values('1','1');
insert into wl1612_3 values('+1','+1');
insert into wl1612_3 values('+01','+01');
insert into wl1612_3 values('+001','+001');
select col1,count(*) from wl1612_3 group by col1;
col1	count(*)
1.00	4
select col2,count(*) from wl1612_3 group by col2;
col2	count(*)
1.00	4
drop table wl1612_3;
select mod(234,10) ;
mod(234,10)
4
select mod(234.567,10.555);
mod(234.567,10.555)
2.357
select mod(-234.567,10.555);
mod(-234.567,10.555)
-2.357
select mod(234.567,-10.555);
mod(234.567,-10.555)
2.357
select round(15.1);
round(15.1)
15
select round(15.4);
round(15.4)
15
select round(15.5);
round(15.5)
16
select round(15.6);
round(15.6)
16
select round(15.9);
round(15.9)
16
select round(-15.1);
round(-15.1)
-15
select round(-15.4);
round(-15.4)
-15
select round(-15.5);
round(-15.5)
-16
select round(-15.6);
round(-15.6)
-16
select round(-15.9);
round(-15.9)