SQL基础：Sql Server之数据类型详解

1. 数据类型概述

在SQL Server中，数据类型是指数据或者值可以存储的类型。不同数据类型拥有不同的存储要求和格式，对于数据的存储和查询都是非常重要的。

1.1 数据类型的分类

在SQL Server中，数据类型主要可以分为以下几类：

字符型(char, varchar, text)

数字型(int, bigint, numeric, float, money)

日期/时间类型(datetime, date, time)

二进制类型(binary, varbinary, image)

其他类型(cursor, xml, table)

2. 字符类型

2.1 char和varchar类型

char和varchar类型都是用来存储字符型数据的，其中char类型一般用来存储长度固定的字符串，而varchar类型则用来存储长度可变的字符串。例如，我们定义一个长度为10的char(10)类型字段，那么无论实际存储的字符串长度是多少，它都会占用10个字符的存储空间，而定义一个长度为10的varchar(10)类型字段，则可以存储1-10个字符的字符串，存储空间占用根据实际长度变化。

下面是一个使用char和varchar类型的示例：

CREATE TABLE test_char_varchar(
id int,
name_char char(10),
name_varchar varchar(10)
)
INSERT INTO test_char_varchar (id, name_char, name_varchar)
VALUES (1, '12345678', '12345678')
INSERT INTO test_char_varchar (id, name_char, name_varchar)
VALUES (2, 'abcdefgh', 'abcdefghijk')
SELECT * FROM test_char_varchar

执行以上代码后，我们可以看到结果如下：

可以看到，我们定义了一个名为test_char_varchar的表，包含id、name_char和name_varchar三个字段。然后我们执行了两个INSERT语句，插入了两个记录，分别使用了char和varchar类型存储不同长度的字符串，最后通过SELECT语句查询出了表中所有的记录。

2.2 text类型

text类型是用来存储比较长的字符串的。与char和varchar类型不同的是，text类型的存储空间是完全按照实际长度存储，并且支持存储长度相当大的字符串。

下面是一个使用text类型的示例：

CREATE TABLE test_text(
id int,
comments text
)
INSERT INTO test_text (id, comments)
VALUES (1, 'This is a test of text type.')
SELECT * FROM test_text

执行以上代码后，我们可以看到结果如下：

可以看到，我们定义了一个名为test_text的表，包含id和comments两个字段。然后我们执行了一个INSERT语句，插入了一条记录，其中使用了text类型存储了一个较长的字符串，最后通过SELECT语句查询出了表中所有的记录。

3. 数字类型

3.1 int类型

int类型是用来存储整型数据的。它可以存储从-2^31到2^31-1之间的整数。

下面是一个使用int类型的示例：

CREATE TABLE test_int(
id int,
age int
)
INSERT INTO test_int (id, age)
VALUES (1, 18)
SELECT * FROM test_int

执行以上代码后，我们可以看到结果如下：

可以看到，我们定义了一个名为test_int的表，包含id和age两个字段。然后我们执行了一个INSERT语句，插入了一条记录，其中使用了int类型存储了一个整型数据，最后通过SELECT语句查询出了表中所有的记录。

3.2 numeric和float类型

numeric和float类型都是用来存储数值型数据的。其中，numeric类型用来存储固定精度的十进制数，float类型则用来存储浮点数。

下面是一个使用numeric和float类型的示例：

CREATE TABLE test_numeric_float(
id int,
score_numeric numeric(4,2),
score_float float
)
INSERT INTO test_numeric_float (id, score_numeric, score_float)
VALUES (1, 89.5, 89.5)
SELECT * FROM test_numeric_float

执行以上代码后，我们可以看到结果如下：

可以看到，我们定义了一个名为test_numeric_float的表，包含id、score_numeric和score_float三个字段。然后我们执行了一个INSERT语句，插入了一条记录，其中使用了numeric和float类型存储了数值数据，最后通过SELECT语句查询出了表中所有的记录。

4. 日期/时间类型

4.1 datetime类型

datetime类型是用来存储日期和时间的数据类型。它可以存储从1753年1月1日到9999年12月31日之间的日期和时间。

下面是一个使用datetime类型的示例：

CREATE TABLE test_datetime(
id int,
birthday datetime
)
INSERT INTO test_datetime (id, birthday)
VALUES (1, '1990-01-01 12:34:56')
SELECT * FROM test_datetime

执行以上代码后，我们可以看到结果如下：

可以看到，我们定义了一个名为test_datetime的表，包含id和birthday两个字段。然后我们执行了一个INSERT语句，插入了一条记录，其中使用了datetime类型存储了一个日期和时间，最后通过SELECT语句查询出了表中所有的记录。

5. 二进制类型

5.1 binary和varbinary类型

binary和varbinary类型是用来存储二进制数据的。其中，binary类型用来存储固定长度的二进制数据，而varbinary类型则用来存储可变长度的二进制数据。

下面是一个使用binary和varbinary类型的示例：

CREATE TABLE test_binary_varbinary(
id int,
image_binary binary(3),
image_varbinary varbinary(max)
)
INSERT INTO test_binary_varbinary (id, image_binary, image_varbinary)
VALUES (1, 0x123456, 0x12345678)
SELECT * FROM test_binary_varbinary

执行以上代码后，我们可以看到结果如下：

可以看到，我们定义了一个名为test_binary_varbinary的表，包含id、image_binary和image_varbinary三个字段。然后我们执行了一个INSERT语句，插入了一条记录，其中使用了binary和varbinary类型存储了二进制数据，最后通过SELECT语句查询出了表中所有的记录。

6. 其他类型

6.1 cursor类型

cursor类型是用来存储游标对象的数据类型。游标是一个指向查询结果集的指针，它可以让我们逐行地操作查询结果集。

下面是一个使用cursor类型的示例：

DECLARE @cursor CURSOR
DECLARE @id INT
DECLARE @name VARCHAR(10)
SET @cursor = CURSOR FOR
SELECT id, name FROM test_char_varchar
OPEN @cursor
FETCH NEXT FROM @cursor INTO @id, @name
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'ID: ' + CONVERT(VARCHAR(10),@id) + ', Name: ' + @name
    FETCH NEXT FROM @cursor INTO @id, @name
END
CLOSE @cursor
DEALLOCATE @cursor

执行以上代码后，我们可以看到结果如下：

ID: 1, Name: 12345678

ID: 2, Name: abcdefgh  

可以看到，我们使用DECLARE语句定义了一个名为@cursor的游标变量，并且使用SELECT语句将test_char_varchar表中的id和name列查询出来。然后通过WHILE循环来逐行输出查询结果，最后通过CLOSE和DEALLOCATE语句关闭了游标。

6.2 xml类型

xml类型是用来存储XML文档的数据类型。XML是一种非常常见的数据交换格式，它可以用来传递和存储各种类型的数据。

下面是一个使用xml类型的示例：

DECLARE @xml XML
SET @xml = 'item1item2'
SELECT @xml

执行以上代码后，我们可以看到结果如下：

<root><item>item1</item><item>item2</item></root>

可以看到，我们使用DECLARE语句定义了一个名为@xml的xml变量，并且通过字符串的方式存储了一个XML文档。然后通过SELECT语句将整个XML文档输出。

6.3 table类型

table类型是用来存储表值的数据类型。它允许我们将一张表作为一个数据值来传递和处理。

下面是一个使用table类型的示例：

CREATE TYPE ctry AS TABLE (id INT, name VARCHAR(10))
DECLARE @ctry AS ctry
INSERT INTO @ctry (id, name)
VALUES (1, 'China'), (2, 'USA'), (3, 'Japan')
SELECT * FROM @ctry

执行以上代码后，我们可以看到结果如下：

可以看到，我们使用CREATE TYPE语句定义了一个名为ctry的table类型，并且使用INSERT语句插入了多条记录到ctry类型的变量中。最后通过SELECT语句查询出了所有的记录。

总结

本文从数据类型的概念和分类开始，分别详细介绍了char、varchar、text、int、numeric、float、datetime、binary、varbinary、cursor、xml和table等常见的数据类型，并且针对每个数据类型都举了一个具体的示例。相信通过本文的学习，读者对于SQL Server中的数据类型已经有了更深入的了解和认识。