云顶娱乐平台 23

云顶娱乐平台:SQL ROUND函数的使用

 

       
不取值,number的小数部分存在0.5的时候,round()取靠近的偶数;

MySQL知识树-支持的数据类型,mysql数据类型

本篇学习笔记的主要内容:

介绍MySQL支持的各种数据类型(常用),并讲解其主要特点。

 

MySQL支持多种数据类型,主要包括数值类型、日期和时间类型、字符串类型。

 

数值类型

MySQL的数值类型包括整数类型、浮点数类型、定点数类型、位类型。

 

整数类型

MySQL支持的整数类型有tinyint、smallint、mediumint、int、bigint(范围从小到大)。

 

zerofill

我们在定义整数类型时可以在类型名称后面的小括号内指定显示宽度,例如int(5),当插入的数值宽度小于5位时,MySQL会在数值前面填充宽度。对于int类型如果不手动指定宽度,则默认为int(11)。

显示宽度一般是配合zerofill来使用,即当插入的数值位数未达到指定的显示宽度时,缺少几位就会在数值前填充几个0

图1

云顶娱乐平台 1

图1,我们创建表t_1,两个字段分别为id1和id2,都是int类型。其中id2我们指定了显示宽度为5,而id1没有手动指定显示宽度,因此它的显示宽度会取默认值11。

 

图2

云顶娱乐平台 2

图2,我们向表中插入一条数据后再将其查询出来,虽然现在id1和id2查询出来的数值都是1,但由于id1在定义时没有指定显示宽度,因此在插入数值1后,其前面10位都被填充了宽度。而id2由于指定了显示宽度,因此其面前只有4位被填充宽度。

 

图3

云顶娱乐平台 3

图4

云顶娱乐平台 4

图3、图4,为了更加直观的看到填充宽度的效果,我们将id1和id2的定义稍作修改,使用zerofill来填充宽度。

 

图5

云顶娱乐平台 5

图5,在使用了zerofill后,我们可以看到数值前面被0填充宽度的效果。那么我们在进行查询时使用1或00001作为条件可以得到结果吗?

 

图6

云顶娱乐平台 6

图6,可以看到在使用1或00001作为查询条件时,能查出id2对应的数值。但这里要注意的是在MySQL中实际存储的值仍然是1,而不是00001,因为00001并不是一种整数的表现形式,而是一种字符串的表现形式,下面的图7将证明这个问题。

 

图7

云顶娱乐平台 7

云顶娱乐平台,图7,我们在查询时使用了hex()函数作为对比,可以看到使用hex()函数得到的值是1,假若hex()得到的值是3030303031(字符串1的16进制为31,字符串0的16进制为30),则可以肯定在MySQL中是以00001的字符串形式进行存储的,但很明显这里并不是。

 

注:hex()函数可以将一个数字或字符串转换为十六进制格式的字符串

 

对于指定显示宽度的做法,联想到一个问题,在id2定义为int(5)的情况下,如果插入超过显示宽度的值,会怎么样呢?

 

图8

云顶娱乐平台 8

图8,向id2插入长度为6位的数值111111时,MySQL没有报任何错误也没有将111111截断。因此说明了显示宽度并不会对插入的数值长度产生限制,两者并没有什么关系,除非插入的数值超过了数据类型的范围,见图9。

 

图9

 云顶娱乐平台 9

图9,可以看到虽然插入成功但MySQL有一个警告(当MySQL的SQL
Mode为严格模式时,该插入行为将不能够被完成,同时MySQL会报ERROR),我们在将数据查询出来时可以看到MySQL对原本插入的数据进行了截取,保留值为4294967295。

 

注:int数据类型有符号的最小值为-2147483648,最大值为2147483647,无符号的最小值为0,最大值为4294967295

 

知识点说明:

其实对于显示宽度来说,只有配合使用了zerofill,显示宽度才有意义,否则就让显示宽度为默认值就可以了。不要以为指定显示宽度会对整数类型的取值范围有什么影响,两者之间没有任何关系,而说到整数类型的取值范围只有unsigned才会对其产生影响。

 

 

unsigned

云顶娱乐平台:SQL ROUND函数的使用。当我们在定义整数类型时使用了zerofill,MySQL会为我们自动对该列再添加unsigned(图3、图4对列添加了zerofill后,再查看表的DDL
[数据库定义语句],会发现列多了unsigned,详见图10)。这是因为当使用了zerofill后,插入该列的值就不可能为负数了,因此自动添加unsigned也是理所应当的,同时unsigned也会增加整数类型最大值的取值范围。

 

图10

云顶娱乐平台 10

 

 

auto_increment

整数类型还有一个属性就是auto_increment,而且这个属性还是整数类型特有的。auto_increment的作用就是使列值保持自动增长,auto_increment的值默认从1开始,也可以手动设置其初始值。对被设置为auto_increment的列插入null值时,实际插入的值是该列当前最大值加1(null并不会影响到被设置为auto_increment列的数据插入,列会正常的进行自动增长)。

当一个列被设置为auto_increment时,通常还需要为该列设置not null和primary
key(主键,一般被设置为auto_increment的列会作为主键使用,这里只是说一般,也有非主键的情况)。

另外需要提醒的是一张表中最多只能有一个字段被设置为auto_increment。

 

 

浮点数和定点数

这两者都是用来表示小数的,浮点数包括float(单精度)、double(双精度),定点数仅为decimal。两者在定义时都可以指定其精度和标度,精度是指一共显示多少位数字(整数位+小数位),标度是指精确到小数点后多少位,表现形式如:decimal(15,2),这里的精度是15位(整数13位,小数2位),标度是2位。

需要说明的是定点数在MySQL内部是以字符串的形式来保存的,属于准确存储,但表现出来的是小数,它比浮点数更精确。

 

图11

云顶娱乐平台 11

图12

云顶娱乐平台 12

图13

云顶娱乐平台 13

图11,我们创建一张表,字段id的数据类型为decimal(5,2),如图12在向表里插入超过标度的值时,虽然插入成功但是插入时的数据却被截断了,这里发生了四舍五入。

图13我们向表里尝试插入超过精度的值,难道也会发生截断并四舍五入?两个值会分别显示为123.12和124.12吗?从结果来看明显不是,我们的猜测是完全错误的。在超过精度的情况下,虽然插入成功但插入的值却是指定精度和标度下的最大值,例如(5,2)下的最大值为999.99。

若是在SQL
Mode严格模式下,上述这些插入操作将不能被执行成功且MySQL会报ERROR。

 

额外知识点:

单精度和双精度的区别,这两者的区别可别理解为单精度是精确到小数点后一位,而双精度是精确到小数点后两位,这明显是错误的。实际上由于float的有效位数是7位,double的有效位数是16位,因此单精度、双精度其实是指代这里的有效位数。

另外需要注意的是有效位数并不等于精确位数,纵然float可以表示到小数点后7位,但只有前6位是精确的,第7位很可能造成数据误差。而对于double来说只有前15位是精确的,第16位也很可能造成数据误差。

 

额外知识点:

关于float、double精度丢失的问题,实际上就是被扩展或截断了,究其缘由是因为存取时标度不一致所导致的。在录入数据时若数据的标度与定义列数据类型时设置的标度不一致,则会导致存入时以近似的值来存储,这就造成了我们上面说到的精度丢失。

那在什么情况下float、double的精度不会丢失呢?其实根据上面出问题的情况,我们可以想到当数据标度与类型标度一样时(录入数据的标度与定义列数据类型时设置的标度一致),就不会发生精度丢失。

鉴于此,我们常选用decimal类型,小于等于其标度的数据都能被正确录入,不会发生精度丢失,因为其是将数据以字符串的形式来存进数据库的,这就保证了精确性。但并不是说decimal就不会发生精度丢失,虽然它不会发生精度扩展但却会发生精度截断。例如当录入数据的标度大于列数据类型设置的标度时,依然会发生四舍五入。

虽然我们说decimal将数据以字符串的形式存入数据库,同时又会存在精度截断的问题(四舍五入),看似两者有文字描述上的冲突,其实不然。我们这样来理解:decimal将发生了四舍五入的数据以字符串的形式存入了数据库,但表现出来的是小数(一个是存储形式,一个是表现形式),且这个小数的精度不会再发生变化,而不管是以什么精度来获取这个值,它都是四舍五入后以字符串形式存入时的值。

 

 

位类型

位类型指的就是BIT,它是用来存放二进制数据的,bit(1)表示存储长度为1位的二进制数据。

图14

云顶娱乐平台 14

图15

云顶娱乐平台 15

我们对图14的表中插入超过位数的数据,从图15的第二个查询结果集中可以发现数据发生了截断,数值2的二进制是10,3的二进制是11,它们的第二位都被截断了。

在图15的第一个查询结果集中,需要说明的是在MySQL命令行窗口中使用select *
from
t_bit_test是无法看到我们需要的数据的,你只能看到有两个笑脸被显示出来,那既然bit中存放的是二进制数据,我们就使用bin()函数以二进制的形式来显示它们。

 

———————未完待续———————

本篇学习笔记的主要内容: 介绍 MySQL
支持的各种数据类型(常用),并讲解其主要特点。 MyS…

123.9990 124.0000

云顶娱乐平台 16

#  ndigits 不取值的时候
print("1:", round(2.5))  # 存在0.5,取值靠近偶数: 2
print("2:", round(2.55))  # 不存在0.5,取值按照四舍五入:3
print("3:", round(3.5))  # 存在0.5,取值靠近偶数:4
print("4:", round(4.5))  # 存在0.5,取值靠近偶数:4
print("5:", round(4.54))  # 不存在0.5,取值按照正常的四舍五入
print("6:", round(5.5))  # 存在0.5,取值靠近偶数:6

#  ndigits 取值的时候
print("7:", round(2.635, 2))  # 存在.5,小数位前奇数: 2.63
print("8:", round(2.645, 2))  # 存在.5,小数位前偶数:2.65
print("9:", round(2.655, 2))  # 存在.5,小数位前奇数:2.65
print("10:", round(2.665, 2))  # 存在.5,小数位前偶数:2.67
print("11:", round(2.675, 2))  # 存在.5,小数位前奇数:2.67
print("12:", round(2.634, 2))  # 不存在.5,按照四舍五入: 2.63
print("13:", round(2.636, 2))  # 不存在.5,按照四舍五入: 2.64

示例 结果
ROUND(748.58, -1) 750.00
ROUND(748.58, -2) 700.00
ROUND(748.58, -3) 1000.00

 

编写函数cut(self,bit)进行处理

说明2:
decimal后面的参数中的2是小数点后取几位, 是2就取两位, 是3就取三位!
并且是四舍五入后的结果!

从上面两次可以看出,默认不使用第三个参数是四舍五入模式。

print(getcontext())
getcontext().prec = 50    # 设置全局精度
b = Decimal(1)/Decimal(3)
print(b)
c = Decimal(1)/Decimal(17)
print(float(c))

语句 结果
Select ROUND(123.4545, 2)
123.4500

 

 

case when DateDiff(dd,借书日期,getdate())>30 then
DateDiff(dd,借书日期,getdate())[单价]1.012 else 0 end

第一个参数是取值的数据,第二个参数是精度,第三个参数是数据取值模式(四舍五入还是截断),其中第三个参数是可选参数,默认是四舍五入模式。

显然前面两种方法是不适用的,round()内置函数其结果只能取到小数点后16位;而格式化处理虽然可以取到30位,但其精度是不准确的。

是要执行的操作类型。function 必须是 tinyint、smallint 或 int。如果省略
function 或 function 的值为 0(默认),numeric_e-xpression
将四舍五入。当指定 0 以外的值时,将截断 numeric_e-xpression。

通过结果发现,后面的5被截断,保留了两位有效小数。

二、格式化处理

注释
ROUND 始终返回一个值。如果 length 是负数且大于小数点前的数字个数,ROUND
将返回 0。

 从上面结果可以看出,数据并非只保留两位小数,而是保留两位有效小数。

print("1: %.30f" % (1/3))  # 输出:1: 0.333333333333333314829616256247
print("2: %s" % round((1/3), 30))  # 输出:2: 0.3333333333333333

下面是结果集:

第三个参数如果是0,则四舍五入,如果是非0,则截断

  python中默认的为17位小数精度,但如果我们需要使用更高精度的时候(意味着超过17位小数),如果处理?

示例 结果
ROUND(748.58, -4) 0

参考: 

一、round()函数内置方法

—————————————————————————-日期之差—————————

SQL
ROUND函数是对数据进行制定精度的取值。

 

B. 使用 ROUND 和四舍五入的近似值
下例显示四舍五入和近似值。

 

  round(number,[ndigits]):round
对传入的数据进行四舍五入,但并不是传统意义上的四舍五入。number:需要被处理的参数;ndigits:保留的位数。

Select ROUND(123.45, -2)
100.00

出现错误是因为987.45为decimal(5,2),它无法表示1000.00,此时需要转换数据类型

总结一下小数精度的处理问题

length

云顶娱乐平台 17

 四、关于小数和取整

是 numeric_e-xpression 将要四舍五入的精度。length 必须是
tinyint、smallint 或int。当 length 为正数时,numeric_e-xpression
四舍五入为 length 所指定的小数位数。当 length
为负数时,numeric_e-xpression 则按 length
所指定的在小数点的左边四舍五入。

另外,如果精度为负数,则为整数部分精确取值。

      取值,number的小数部分没有.5的时候,则按照四舍五入进行取值;

说明1:

SELECT ROUND(987.45,-2)

 

function

云顶娱乐平台 18

  ndigits:不取值,number的小数部分没有0.5的时候,则按照四舍五入进行取值;

语句 结果
Select ROUND(150.75, 0)
151.00

云顶娱乐平台 19

三、超过17位精度分析

当 length 是负数时,无论什么数据类型,ROUND 都将返回一个四舍五入的
numeric_e-xpression。

云顶娱乐平台 20

  输出格式%.mf:处理机制个round()函数一致,m指取得位数,f是指float数据类型

C. 使用 ROUND 截断
下例使用两个 Select
语句说明四舍五入和截断之间的区别。第一个语句四舍五入结果。第二个语句截断结果。

 

五、截取小数点后bit位

Select ROUND(150.75, 0, 1)
150.00

云顶娱乐平台 21

#  m不取值的时候
print("1: %.f" % 2.5)  # 存在0.5,取值靠近偶数: 2
print("2: %.f" % 3.5)  # 存在0.5,取值靠近偶数: 4
print("3: %.f" % 3.4)  # 不存在0.5,按照四色五入: 2
print("4: %.f" % 3.6)  # 不存在0.5,按照四舍五入: 2

#  m=2 取值的时候
print("4: %.2f" % 2.635)  # 存在.5,小数位前奇数: 2.63
print("5: %.2f" % 2.645)  # 存在.5,小数位前偶数:2.65
print("6: %.2f" % 2.655)  # 存在.5,小数位前奇数:2.65
print("7: %.2f" % 2.665)  # 存在.5,小数位前偶数:2.67
print("8: %.2f" % 2.675)  # 存在.5,小数位前奇数:2.67
print("9: %.2f" % 2.634)  # 不存在.5,按照四舍五入: 2.63

这里的decimal英文为: 小数, 十进制
decimal(38,2)
这里的38是这个小数的位数有多少位, 一般最大不超过38位,
所以写38是不会出错的!
如果:
SELECT CAST(‘123.456’ as decimal(2, 2))
就会出错, 为什么呢, 因为这个123.456小数点后是3位值,
所以这个38这个位置最少是3!

云顶娱乐平台 22

1.math模块下的ceil(x)函数:取大于或者等于x的最小整数

SELECT CAST(‘123.456’ as decimal) 将会得到
123(小数点后面的将会被省略掉)。
如果希望得到小数点后面的两位。
则需要把上面的改为
SELECT CAST(‘123.456’ as decimal(38, 2)) ===>123.46

云顶娱乐平台 23

from math import ceil, floor

# ceil():取大于或等于x的最小整数
print("1: %s" % ceil(2.5))  # 1: 3
print("2: %s" % ceil(2.3))  # 2: 3
print("3: %s" % ceil(2.6))  # 3: 3

# floor():取小于或等于x的最大整数
print("4: %s" % floor(2.5))  # 4: 2
print("5: %s" % floor(2.3))  # 5: 2
print("6: %s" % floor(2.6))  # 6: 2

精确数字或近似数字数据类型类别的表达式(bit 数据类型除外)。

 

下面介绍一种方法:高精度使用
decimal模块,配合getcontext

Select ROUND(123.9994, 3), ROUND(123.9995, 3)
GO

2.math模块下的floor(x)函数:取小于或者等于x的最大整数

说明3:
cast应该就是转换数值的意思了

  使用前面两种方法进行处理,查看一下处理结果:

方法2:
SELECT ROUND(123.75633, 2, 1), ROUND(123.75633, 2)
上面的SQL得到的2个值是不一样的,前一个是:123.75000,后一个是:123.76000。
因为前者在进行四舍五入之前,小数点后已经被截取,保留了2位。
而后者则没有被截取,四舍五入时自然就会得到123.76000

      取值,number的小数部分存在.5的时候,小数位前是奇数则舍弃,小数位前是偶数则向上取(意味着,一旦出现小数位后为.5的,则小数位前取值,无法取到偶数);

ROUND
返回数字表达式并四舍五入为指定的长度或精度。

def cut(self, bit):
    str_sli = str(self).split('.', 1)
    sli = len(str_sli[0]) + bit + 1
    result = str(self)[:sli]
    return result

a = cut(3.1356, 2)
print(a)    #输出结果为: 3.13

语法
ROUND ( numeric_e-xpression , length [ , function ] )

参数
numeric_e-xpression

自动四舍五入了!

示例
A. 使用 ROUND 和估计值
下例显示两个表达式,说明使用 ROUND 函数且最后一个数字始终是估计值。

返回类型
返回与 numeric_e-xpression 相同的类型。

方法1: