五、Sql Server 基础培训《进度5-数据类型（知识点+实际操作）》

知识点：

=================================================

=================================================

1、二进制的概念

计算机运算采用二进制。二进制数值用0和1两个数来表示数据，它的基数为2，进位规则是“逢二进一”。借位规则是“借一当二”

举例（加法）：一串二进制000

加1：     001

再加1：   010

再加1：   011

再加1：   100

再加1：   101

举例（减法）：一串二进制1010

减1：     1001

再减1：   1000

再减1：   0111

再减1：   0110

再减1：   0101

=========================================

=========================================

2、字符、字节、位的概念

位（bit）：音译“比特”，是最小的存储单位，表示二进制位。在1个位中只能表示0或1。

举例：一个二进制位表示：0、1两种状态。

两个二进制位表示：00、01、10、11四种状态。

一个8位二进制数：11010100。

字节（byte）： 1个字节由8位构成，是常用的计算机存储单位。1个8位的字节包含256种可能的0,1组合。

字符（word）：一个英文字符或英文标点占用1个字节，一个中文字符或中文标点占用2个字节。

举例：汉字“严”的二进制编码为：0100111000100101

==========================================

==========================================

3、B/KB/MB/GB/TB的存储容量概念

1B：就是1个字节，由8个位组成。

1KB：就是1024B，即1024个字节。

1MB：就是1024KB，即1024 乘 1024 = 1048576个字节。

1GB，就是1024MB。

1TB，就是1024GB。

================================================

================================================

4、常用数据类型有哪些？

=======

数值型：

=======

bigint：存储范围从-2^63 (-9223372036854775808)到2^63-1(9223372036854775807)的数字。存储大小为 8 个字节。

举例：常用于自增ID、数量

int：存储范围从-2,147,483,648至2,147,483,647的数字。存储大小为4个字节。

举例：常用于自增ID、数量

smallint：存储范围从 -32,768 至 32,767的数字。存储大小为 2 个字节。

tinyint：存储范围从 0 至 255的数字。存储大小为 1 个字节。

举例：常用于类型、状态

=======

精度型：

=======

decimal(p , s)：精确小数类型，不存在精度缺失，存储范围从-10^38+1至10^38-1的固定精度和小数位数字。存储大小为5至17个字节。

项一、有效位数和小数位数

p（有效位数）：有效位数必须是 1 至最大值 38 之间的值。默认有效位数值是 18。

s（小数位数）：小数位数必须是从 0 到 p 的值。只有在指定了有效位数时，才能指定小数位数。默认小数位值是 0。因而 0 <= s <= p。最大存储大小基于精度而变化。

项二、占用的存储字节如下：

精度	存储字节数
1-9	5
10-19	9
20-28	13
29-38	17

 

项三、不同数据类型：对于 decimal 和 numeric 数据类型，Microsoft SQL Server 将有效位数和小数位数的每个不同组合看作是不同的数据类型。不同decimal组合的数值之间互相转换，会引起数据类型之间的转换运算，浪费计算机性能资源。

举例：decimal(5,5) 和 decimal(5,0)被当做不同数据类型来处理。

 

 

 

 

项四、举例：定义变量@a

例子1：declare  @a decimal(38,30)

set @a=12345678901234567890123456789012345678.1234

select @a

执行结果：报错。有效位数+小数位数=42位数，超过最大38

例子2：declare @a decimal(4,6)

执行结果：报错。因为0<=s<=p，小数位数必须小于或等于有效位数。

例子3：declare @a decimal(4,4)

set @a=0.1234

select @a

执行结果：0.1234。因为有效位数是4位，其中小数占了4位，小数点左侧数字只能为0。

例子4：declare @a decimal(4,4)

set @a=1.1234

select @a

执行结果：报错。因为该数值是有效位数5，超出变量@a定义的有效位数4。

例子5：declare @a decimal

set @a=1234.235

select @a

执行结果：1234。因为小数位数默认值是0，表示没有小数位。

例子6：declare @a decimal

set @a=123456789012345678.2

select @a

执行结果：123456789012345678。因为有效位数默认值是18，小数点左侧位数刚好18个数字，刚好等于默认有效位数。所以，小数点右侧的小数直接舍弃。

例子7：declare @a decimal

set @a=12345678901234567890.23

select @a

执行结果：报错。因为该数值小数点左侧的数值超过有效位数默认值18。

例子8：declare @a decimal(10,2)

set @a=1234.1254

select @a

执行结果：1234.13。因为定义小数位数为2位。超出2位小数位的数值自动舍弃，并四舍五入。

例子9：declare @a decimal(10,8)

set @a=23.12345678

select @a

执行结果：23.12345678。在符合有效位数的情况下，小数点右侧的位数可以比左侧的位数多。

numeric(p,s)

功能上等同于decimal

money和smallmoney

money是小数位的decimal。

比较1：money与decimal对比

字段	字节数	长度（小数点左.小数点右）
Money	8	15位.4位
Decimal(19,4)	9	15位.4位（千万亿以下）

总结：Decimal定义与money数据类型同等精度，decimal占用字节数比money多1个字节。

比较2：smallmone与decimal对比

字段	字节数	长度（小数点左.小数点右）
Smallmoney	4	6位.4位
Decimal(10,5)	9	6位.4位（百万以下）

总结：Decimal定义与smallmoney数据类型同等精度，decimal占用字节数比money多5个字节。

比较3： decimal

字段	字节数	长度（小数点左.小数点右）
Decimal(9,4)	5	5位.4位（十万以下）

总结：Decimal最小精度类型，也要占用5个字节。

float：近似小数类型，存在精度损失，数据类型是 float(n)，n是可选的，默认类型是float(53)，占用8bytes。虽然n的取值范围是1-53，实际上，float 只能表示两种类型float(53) 和 float(24)，分别占用 8字节 和 4字节。

其中n是以科学计数法存储浮点数尾数的位数，因此决定了精度和存储大小。 如果指定了n，则它必须是介于1和53之间的值，n的缺省值是53。

n	精度	字节数
1-24	7位	4字节
25-53	15位	8字节

注意：SQL Server将n视为两个可能值之一。 如果1 <= n <= 24，则将n视为24；如果25 <= n <= 53，则将n视为53。

近似数值很难确定是否相等，因此，应避免对 float 类型做相等比较，而只限于比较 > 或 < 。

double precision

双精度（double precision）数据类型等价于 float(53)，有效位数15位，存储容量为8字节。

举例：declare @a DOUBLE PRECISION

set @a=123.111

select @a

执行结果：123.111

real

real等价于float(24)，有效位数7位，存储容量为4字节。

========

字符型：

========

char：固定长度的字符数据类型，存储容量不大于8KB。char(n)，n是一个介于1至8,000 之间的数值。当你输入的字符小于你指定的数目时，举例：char(8)，定义的char为8个字节，你输入的字符小于8个字节时，它会再后面补空值，所以在读取的时候可能要多次用到trim（）。当你输入的字符大于指定的数时，它会截取超出的字符。

英文字符占1个字节，中文字符占2个字节。

举例：declare @a char(8)

set @a='abcdefghijk'

select @a

执行结果：abcdefgh，总共显示8个英文字符，多出来的字符部分截断。

举例：declare @a char(8)

set @a='孝为德本，孝乃为人之根本。'

select @a

执行结果：孝为德本，总共显示4个中文字符，多出来的字符部分截断。

varchar：可变长度的字符数据类型，存储容量不大于8KB。根据数据长度自动变化,存储效率没有CHAR高。如果一个字段值是不固定长度，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合算的。从存储空间考虑，用varchar合适；从效率考虑，用char合适，根据实际情况找到权衡点。varchar(n)，n是一个介于1至8,000 之间的数值。存储大小为输入数据的字节的实际长度，而不是 n个字节。

总结：char、varchar最多能存储8000个英文，4000个汉字。

text：可变长度的大值字符数据类型，最大长度2^31-1个字符，即2,147,483,647个字符，最大不超过2GB。

nchar：固定长度的字符数据类型，Unicode数据类型，存储容量不大于8KB。在字符中，英文字符存储一个字节，汉字存储两个字节，英文与汉字同时存在容易造成混乱，Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是两个字节。nchar(n)，n是一个介于1至4000 之间的数值。

nvarchar：可变长度的字符数据类型，Unicode数据类型，存储容量不大于8KB。nvarchar(n)，n是一个介于1至4,000 之间的数值。存储大小为输入数据的字节的实际长度，而不是 n个字节。

总结：nchar、nvarchar则最多存储4000个字符，不论是英文还是汉字。

一般情况，如果含有中文字符，用nchar、nvarchar，如果纯英文和数字，用char、varchar。

ntext：可变长度的大值字符数据类型，Unicode数据类型，最大长度2^31-1个字符，即1,073,741,823个字符，最大不超过2GB。

varchar和nvarchar如何选择？

正常情况下，varchar也可以存储中文字符，但是如果遇到操作系统是英文操作系统并且对中文字体的支持不全面时, 在SQL Server存储中文字符为varchar就会出现乱码(显示为??)。而正常情况下，主机都会支持中文的环境，使用varchar来存储数据，在开发阶段是发现不了问题的。多数情况下，在布署的时候也不会有问题。（一套软件面对成千上万的客户，大部分客户部署不会出现问题。个别问题出现时，就是下面所说的情况。）

但是，如果布署的主机是英文操作系统，并且不支持中文环境，那问题就出来了。所有的varchar字段在存储中文的时候都会变成乱码（显示为??）。而且一般情况下你不会知道这是因为你采用了错误的数据类型来存储所造成的，你会试着去装中文字体，试着去设置操作系统的语言环境。这些都不能解决问题，唯一能解决问题的是把数据库字段的类型设置为nvarchar（或者nchar）。对项目管理比较熟悉的朋友应该都知道，到布署阶段再来修改数据库是一个很恐怖的事情。

建议使用NVARCHAR来存放非英文字符数据理由：

理由1：VARCHAR类型存放特殊字符或生僻字时存在乱码或字符被转变的问题

理由2：对于中文字符，使用VARCHAR和NVARCHAR消耗同样的空间，对于英文字符，使用VARCHAR比NVARCHAR节省一倍的空间，但随着磁盘成本越来越低，其提升的性能和节省的成本有限。（例外：如果数据中存在大量英文字符和少量非英文字符，则可以考虑VARCHAR类型）

理由3：对于需要国际化的企业，后期将VARCHAR升级为NVARCHAR的成本太高或难以实现

理由4：使用VARCHAR存放非英文字符时，容易生成错误的预估值，尤其在执行LIKE这类前缀匹配的预估时。

举例：SELECT N'刘䶮','刘䶮'

执行结果：刘䶮, 刘?。因为第一个是nvarchar类型，显示正常。第二个字符是varchar类型，生僻字显示中文乱码？。

大值数据类型

SQL Server 2005之后版本:请使用 varchar(max)、nvarchar(max) 和 varbinary(max) 数据类型，而不要使用 text、ntext 和 image 数据类型。

Microsoft SQL Server 2005 中引入了 max 说明符。此说明符增强了 varchar、nvarchar 和 varbinary 数据类型的存储能力。varchar(max)、nvarchar(max) 和 varbinary(max) 统称为大值数据类型。您可以使用大值数据类型来存储最大为 2^31-1 个字节的数据。

 

========

日期型：

========

日期型详细展开讲解，篇幅很大，此处简略之。

datetime：数据类型存储从 1753 年 1 月 1 日至 9999 年 12 月 31 日的日期。每个数值要求 8 个字节的存储空间。

smalldatetime：数据类型存储从 1900 年 1 月 1 日至 2079 年 6 月 6 日的日期。每个数值要求 4 个字节的存储空间。

==========

特殊类型：

==========

此处略去不讲

============

二进制类型：

============

此处略去不讲

========================================================

========================================================

学习作业5：

问题1：假设创建的案例数据库为schooldb

步骤1：打开数据库，对着你建立的案例数据库，右键点击属性，在文件页签，查看数据库路径，并复制路径。打开我的电脑，把路径复制到我的电脑最上面的路径输入栏里面，按回车，打开数据库所在的路径，查看数据库文件mdf的存储容量大小。

步骤2：在数据库里，创建测试表test。

create table test

(

t1 varchar(10),

t2 nvarchar(10),

t3 int

)

插入t1字段，100万条数据

--1、插入10万条

declare @i int --变义变量@i

set @i = 0 --初始化@i为0

while @i < 100

begin ------------------

insert into test(t1)   select '1234567890';

insert into test(t1)   select '1234567890';

insert into test(t1)   select '1234567890';

insert into test(t1)   select '1234567890';

insert into test(t1)   select '1234567890';

insert into test(t1)   select '1234567890';

insert into test(t1)   select '1234567890';

insert into test(t1)   select '1234567890';

insert into test(t1)   select '1234567890';

insert into test(t1)   select '1234567890';

insert into test(t1) select Top 10 t1 from test

insert into test(t1) select Top 20 t1 from test

insert into test(t1) select Top 40 t1 from test

insert into test(t1) select Top 80 t1 from test

insert into test(t1) select Top 160 t1 from test

insert into test(t1) select Top 320 t1 from test

insert into test(t1) select Top 360 t1 from test

set @i = @i + 1;

end

--2、插入剩下90万条

insert into test(t1) select top 100000 t1 from test

insert into test(t1) select top 200000 t1 from test

insert into test(t1) select top 400000 t1 from test

insert into test(t1) select top 200000 t1 from test

然后记录下数据库文件mdf的存储容量大小，计算出增长多少存储容量，并计算出每一条记录占用多少字节存储容量。

步骤3：在数据库里，创建测试表test1

create table test1

(

t1 varchar(5)

)

插入t1字段，100万条数据

insert into test1(t1) select left(t1,5) from test

然后记录下数据库文件mdf的存储容量大小，计算出增长多少存储容量，并计算出每一条记录占用多少字节存储容量。

问题2：本进度中提到的数据类型适用于哪些用途？（*发挥）

问题3：通过本主题的学习，你有什么收获？（*发挥）

问题4：通过本主题的学习，你有什么建议？（可选）

学习5作业答案公布：

问题1解答：步骤1的目的是查看数据库当前的初始存储容量大小
步骤2的目的是测试一下长度10字节的字符串，插入100万条数据后，数据库增长的存储容量为多少字节，然后除1000000，算出每插入1条记录增长的字节数是多少。
步骤3的目的是测试一下长度5字节的字符串，插入插入100万条数据后，数据库增长的存储容量为多少字节，然后除1000000，算出每插入1条记录增长的字节数是多少。

最终做个比较，同样增长100万条记录，会发现长度5字节字符串增长的存储容量，小于长度10字节字符串增长的存储容量。
（如果验证到这个结论，基本上表示你的实验测试算成功了。）

当然，本章知识点的理论知识上是说，可变长的varchar，按实际我们插入的数据长度算字节存储容量。
第一个实验，插入10字节，100万条记录，
理论上是增长10*1000000/1024/1024=9.5367MB。
但是，考虑是varchar可变长类型，自己本身也需要预留字节存储实际长度，数据表的增长，数据库本身内部机制等等，实际实验增长会大于我们理论的增长长度。
第二个实验，插入5字节，100万条记录，
理论上是增长5*1000000/1024/1024=4.7683MB。
但是，考虑是varchar可变长类型，自己本身也需要预留字节存储实际长度，数据表的增长，数据库本身内部机制等等，实际实验增长会大于我们理论的增长长度。

本实验的目的是让同学更直观的了解到数据类型作为日常使用的最小维度，或者称为最小存储容器，它都有一定长度精度，他跟数据容量空间的增长息息相关，影响到数据库的容量膨胀，也跟查询性能效率有影响、实际场景使用情况下做何种数据类型的选择也有关。

===========================
===========================
问题2解答：
bigint：这是个整型，你在设计字段时，要看他的数值范围是否足够你使用。如果int整型的数值范围不够你使用的情况下，你可以用bigint长整型，这个数值范围更大。常用于主键ID、计算数量、统计数值上，适用于计量单位是不带小数位的情况。

数值够用的情况下，选择数值范围较小的整型数据类型，占用的存储容量更小，随着数据量增长，占用的硬盘空间更小。

举个例子，今天天上有2颗星星。你这星星总不会用1/2个、1/3个计算吧，这时候计算星星的数量，就要用整数。

那什么时候用到精度型呢，就是带小数位的。比如，我们用钱，钱的计算有小数位。还有像重量，公斤、千克、克这些称重下来也会有小数位。

int：这是个整型，数值范围最大20多亿数值，常用于主键ID、计算数量、统计数值上，适用于计量单位是不带小数位的情况。

smallint：整型，数值范围最大3万多数值。适用于一些数值范围要求较小的情况。

tinyint：整型，数值范围0至255，常用于类型、状态字段。

decimal:精确精度，常用于财务方面，表示金额，也适用于一些对精度要求较高的情况下。比如化工、医药、机械制造、航空等精度要求较高情况。

money：金钱字段，和精确精度功能类似。同样精度下，money占用字节比decimal多占用1位。

smallmoney：金钱字段，同样精度下，smallmoney占用字节仅4字节，decimal占用9字节。

float：近似精度，适用于一些有近似值的情况

char：定长字符串，适用于字符串长度固定的一些情况。比如，单据编号、编码规则之类的。

varchar：可变长字符串，适用于各种字符串存储。比如：文字、名称之类。

text或varchar(max)：较大长度的可变长度字符串，适用于更大长度的字符串存储。

nchar、nvarchar：含中文字符的情况，对中文支持度较好。不容易出问题。