极客教程SQL 数据类型
SQL数据类型是什么
SQL数据类型指的是可以存储在数据库表列中的数据的类型。例如整数数据，字符数据，货币数据，日期和时间数据，二进制字符串等等。
在数据库中创建表时，我们需要指定以下两个属性来定义表列：
- 列名
- 列的数据类型
数据库表的列定义了数据，而数据库表的行将数据填入表中。
例如，如果您想在列中存储学生姓名，则列名应该为 student_name ，它的数据类型将为 char(50) ，意味着它可以存储最长为50个字符的字符串。
数据类型为SQL提供了对于每个列内预期的数据类型的理解，从而防止用户在列中输入任何意外或无效的数据。例如，如果我们只希望一个列存储整数值，我们可以将其数据类型指定为INT。如果除了整数之外的任何其他值被插入到该特定列中，SQL将显示错误。
不同的关系数据库管理系统支持不同类型的数据类型来定义它们的表。本教程将列出MySQL、Oracle、SQL Server和MS Access数据库中可用的不同数据类型。
SQL数据类型的类型
任何关系数据库管理系统中有三种主要类型的SQL数据类型。它们如下所示 –
- 字符串
- 数值
- 日期和时间
每种数据类型将在本章后面讨论。
定义数据类型
在数据库中创建表时，需要定义SQL数据类型。创建表时，需要在列名称旁边指定其相应的数据类型和大小。
以下是在MySQL中指定数据类型的语法 –
CREATE TABLE table_name(column1 datatype, column2 datatype…)
让我们看一个下面的示例查询，以更好地理解。
CREATE TABLE Customers (Name VARCHAR (25), Age INT)
在上述的SQL查询中，我们正在创建一个名为Customers的表。由于Name列只存储字符串值，我们将其的数据类型指定为“VARCHAR”。VARCHAR数据类型代表SQL中的字符串值。同样地，我们将Age列定义为整数数据类型“INT”。
当我们为列分配适当的数据类型时，可以通过仅为相关列中的数据分配所需的系统内存数量来有效利用内存。
MySQL数据类型
MySQL数据库中有三种主要的数据类型：字符串、数值和日期时间。下面的部分列出了MySQL版本8.0中的所有数据类型。
MySQL – 字符串数据类型
| 数据类型 | 描述 |
|---|---|
| CHAR(size) | 固定长度字符串，可包含字母、数字和特殊字符。 size 参数指定列的长度，长度可从0到255个字符变化。默认大小为1。 |
| VARCHAR(size) | 变长字符串，可包含字母、数字和特殊字符。 size 参数指定字符串的最大长度，长度可从0到65535个字符变化。 |
| BINARY(size) | 与CHAR()相同，但存储二进制字节字符串。 size 参数指定列的长度，长度以字节为单位。默认大小为1。 |
| VARBINARY(size) | 这相当于VARCHAR()，但存储二进制字节字符串。参数 size 指定了列的最大长度（以字节为单位）。 |
| TINYTEXT | 这可以保存最大长度为255个字符的字符串。 |
| TEXT(size) | 这可以保存最大长度为65,535个字节的字符串。 |
| LONGTEXT | 这可以保存最大长度为4,294,967,295个字符的字符串。 |
| TINYBLOB | 这表示小型二进制大对象（BLOB）。最大长度为255个字节。 |
| BLOB(size) | 这表示二进制大对象（BLOB）。最多可以存储65,535个字节的数据。 |
| MEDIUMTEXT | 这个字段可以存储最多 16,777,215 个字符的字符串 |
| MEDIUMBLOB | 这个字段可以存储最多 16,777,215 字节的中型 BLOB（二进制大对象）数据 |
| LONGBLOB | 这个字段可以存储最多 4,294,967,295 字节的大型 BLOB（二进制大对象）数据 |
| ENUM(val1, val2, val3, …) | 这是一个字符串对象，只能包含从可能值列表中选择的一个值。你可以在 ENUM 列表中列出最多 65535 个值。如果插入的值不在列表中，会插入一个空值。 这些值按你输入的顺序排序 |
| SET(val1, val2, val3, …) | 一个字符串对象，可以有0个或多个值，从可能值的列表中选择。您可以在SET列表中列出最多64个值。 |
MySQL – 数字数据类型
| 数据类型 | 描述 |
|---|---|
| INT | 一个普通大小的整数，可以是有符号或无符号。如果是有符号的，可允许的范围为-2147483648至2147483647。如果是无符号的，可允许的范围为0至4294967295。您可以指定最多11位数的宽度。 |
| TINYINT | 一个非常小的整数，可以是有符号或无符号。如果是有符号的，可允许的范围为-128至127。如果是无符号的，可允许的范围为0至255。您可以指定最多4位数的宽度。 |
| SMALLINT | 一个小整数，可以是有符号或无符号。如果是有符号的，可允许的范围为-32768至32767。如果是无符号的，可允许的范围为0至65535。您可以指定最多5位数的宽度。 |
| MEDIUMINT | 一个中等大小的整数，可以是有符号或无符号。如果是有符号的，可允许的范围为-8388608至8388607。如果是无符号的，可允许的范围为0至16777215。您可以指定最多9位数的宽度。 |
| BIGINT | 一个大整数，可以是有符号或无符号。如果是有符号的，可允许的范围为-9223372036854775808至9223372036854775807。如果是无符号的，可允许的范围为0至18446744073709551615。您可以指定最多20位数的宽度。 |
| FLOAT(M,D) | 一个浮点数，不能是无符号的。您可以定义显示长度(M)和小数位数(D)。这不是必需的，将默认为10,2，其中2是小数位数，10是总位数（包括小数位）。浮点数的小数精度可以达到24位。 |
| DOUBLE(M,D) | 一个双精度浮点数，不能是无符号的。您可以定义显示长度(M)和小数位数(D)。这不是必需的，将默认为16,4，其中4是小数位数。双精度浮点数的小数精度可以达到53位。REAL是DOUBLE的同义词。 |
| DECIMAL(M,D) | 一个非编码的浮点数，不能是无符号的。在非编码的小数中，每个小数对应一个字节。定义显示长度(M)和小数位数(D)是必需的。NUMERIC是DECIMAL的同义词。 |
MySQL – 日期和时间数据类型
| 数据类型 | 描述 |
|---|---|
| DATE | 以YYYY-MM-DD格式表示的日期，介于1000-01-01和9999-12-31之间。例如，1973年12月30日将存储为1973-12-30。 |
| DATETIME | 以YYYY-MM-DD HH:MM:SS格式表示的日期和时间组合，介于1000-01-01 00:00:00和9999-12-31 23:59:59之间。例如，1973年12月30日下午3点30分将存储为1973-12-30 15:30:00。 |
| TIMESTAMP | 介于1970年1月1日午夜和2037年某个时间之间的时间戳。它的格式与前面的DATETIME相似，只是数字之间没有连字符；例如，1973年12月30日下午3点30分将存储为19731230153000（YYYYMMDDHHMMSS）。 |
| TIME | 以HH:MM:SS格式存储时间。 |
| YEAR(M) | 以2位数或4位数的格式存储年份。如果长度指定为2（例如YEAR(2)），年份可以在1970年到2069年之间（70到69）。如果长度指定为4，则年份可以在1901年到2155年之间。默认长度为4。 |
SQL Server数据类型
如我们之前在这章节讨论过的，SQL server有三种主要的数据类型，它们分别是：字符串、数字和日期时间。
SQL Server – 字符串数据类型
SQL中的字符串数据类型允许我们在表的列记录中存储一组字符，这些字符用单引号括起来。这些字符可以是任何类型的： 数字 ， 字母 ， 符号 等等。
用户可以根据他们的喜好存储固定数量的字符或可变数量的字符。
下面是SQL中包含的字符串数据类型列表。
| 数据类型 | 描述 |
|---|---|
| char(n) | 它以固定宽度存储字符串。该数据类型的最大大小为8000个字符。 |
| varchar(n) | 它以可变宽度存储字符串。该数据类型的最大大小也是8000个字符。 |
| varchar(max) | 它以可变宽度存储字符串。该数据类型的最大大小为1073741824个字符。 |
| text | 它以可变宽度存储字符串。该数据类型可以存储最大2GB的文本数据。 |
| nchar | 它以固定宽度存储Unicode字符串。该数据类型的最大大小也是4000个字符。 |
| nvarchar | 它以可变宽度存储Unicode字符串。该数据类型的最大大小也是4000个字符。 |
| ntext | 它以可变宽度存储Unicode字符串。该数据类型可以存储最大2GB的文本数据。 |
| binary(n) | 它以固定宽度存储二进制字符串。 |
| varbinary | 它以可变宽度存储二进制字符串。该数据类型的最大大小也是8000个字节。 |
| varbinary(max) | 它以可变宽度存储最大长度的二进制字符串。该数据类型的最大大小为2GB。 |
| image | 它以可变长度存储二进制数据的数据类型。该数据类型的最大大小为2GB。 |
| Nvarchar(max) | 它以可变宽度存储最大长度的Unicode字符串。该数据类型的最大大小为536870912个字符。 |
示例
在以下示例中，我们将创建一个名为“students”的表，其中只包含字符串数据类型的值：varchar和char。
CREATE TABLE students (
name varchar(20) NOT NULL,
gender char(6) NOT NULL,
city text NOT NULL
);
输出
执行查询后，输出将显示为−
(0 rows affected)
验证
在执行SQL查询“EXEC sp_help’ dbo.students’;”时，我们可以获取表的详细信息以及每个列的数据类型。
| Column_name | Type | Computed | Length | Prec | Scale | Nullable |
|---|---|---|---|---|---|---|
| name | varchar | no | 20 | no | ||
| gender | char | no | 6 | no | ||
| city | text | no | 16 | no |
SQL Server – 数值数据类型
数值数据类型是SQL中最常用的数据类型之一。它们用于存储数值值。
以下是SQL中包含的数值数据类型列表。
| 数据类型 | 描述 |
|---|---|
| bit | 它保存可以为0、1或NULL的整数。 |
| tinyint | 它可以保存从0到255的整数。 |
| smallint | 它可以保存从-32,768到32,767之间的数字。 |
| int | 它可以保存从-2,147,483,648到2,147,483,647之间的整数。 |
| bigint | 它可以保存从-9,223,372,036,854,775,808到9,223,372,036,854,775,807之间的整数。 |
| decimal(p, s) | 它是固定精度和标度数字。可以保存从-10^38 + 1到10^38-1的数字。 p参数指示可以在小数点左右两侧存储的最大总位数。它必须是1到38之间的值。默认值为18。 s参数指示小数点右侧的最大数字。s必须是0到p之间的值。默认值为0。 |
| numeric(p, s) | 它是固定精度和标度数字。可以保存从-10^38 + 1到10^38-1的数字。 p参数指示可以在小数点左右两侧存储的最大总位数。它必须是1到38之间的值。默认值为18。 s参数指示小数点右侧的最大数字。s必须是0到p之间的值。默认值为0。 |
| smallmoney | 它保存从-214,748.3648到214,748.3647的货币数据。 |
| money | 它保存从-922,337,203,685,477.5808到922,337,203,685,477.5807的货币数据。 |
| float(n) | 它保存浮点精度数字数据，范围从-1.79E + 308到1.79E + 308。 n参数指示字段应该保存4字节还是8字节。Float(24)包含4字节字段，而Float(53)包含8字节字段。n的默认值为53。 |
| real | 它保存浮点精度数字数据，范围从-3.40E + 38到3.40E + 38。 |
示例
在下面的示例中，我们创建了一个名为employees的表，其中只包含数值型数据类型的值。
CREATE TABLE employees (
ID int NOT NULL,
myBoolean bit NOT NULL,
Fee money,
celsius float NOT NULL
);
输出
执行查询后，将显示输出如下-
(0 rows affected)
验证
在执行SQL查询”EXEC sp_help ‘dbo.employees;”时，我们可以获取表的详细信息以及每列的数据类型。
| Column_name | Type | Computed | Length | Prec | Scale | Nullable |
|---|---|---|---|---|---|---|
| ID | int | no | 4 | 10 | 0 | no |
| myBoolean | bit | no | 1 | no | ||
| Fee | money | no | 18 | 19 | 4 | yes |
| Celsius | float | no | 8 | 53 | NULL | no |
SQL Server – 日期和时间数据类型
datetime 数据类型用于在SQL中存储既包含日期又包含时间的值。 datetime 和 time 的值分别以以下格式定义： yyyy-mm-dd ， hh:mm:ss.nnnnnnn （n取决于列定义）。
以下是SQL中包含日期和时间数据类型的列表。
| 数据类型 | 描述 |
|---|---|
| datetime | 它可以存储从1753年1月1日至9999年12月31日的日期和时间，并具有3.33毫秒的精度。 |
| datetime2 | 它可以存储从0001年1月1日至9999年12月31日的日期和时间，并具有100纳秒的精度。 |
| smalldatetime | 它可以存储从1900年1月1日至2079年6月6日的日期和时间，并具有1分钟的精度。 |
| date | 它只能存储从0001年1月1日至9999年12月31日的日期。 |
| time | 它可以存储时间，并具有100纳秒的精度。 |
| datetimeoffset | 这与日期时间2相同，但添加了时区偏移。 |
| timestamp | 它存储每次创建或修改行时更新的唯一数字。它不对应实际时间，而是基于内部时间。每个表只能有一个时间戳变量。 |
注意 − 这里，datetime的精度为3.33毫秒，而smalldatetime的精度为1分钟。
示例
在下面的示例中，我们创建了一个名为Cust_details的表，其中只包含日期和时间数据类型的值。
CREATE TABLE Cust_details (
HolidayDate DATE NOT NULL,
OrderDateTime DATETIME,
ScheduleFrom TIME NOT NULL,
ShippingDateTime DATETIME2
);
输出
执行查询后，将显示以下输出：
(0 rows affected)
验证
在执行SQL查询“EXEC sp_help ‘dbo.Cust_details;”时，我们可以获取表格的详细信息和每列的数据类型。
| Column_name | Type | Computed | Length | Prec | Scale | Nullable |
|---|---|---|---|---|---|---|
| HolidayDate | date | no | 3 | 10 | 0 | no |
| OrderDateTime | datetime | no | 8 | yes | ||
| ScheduleFrom | time | no | 5 | 16 | 7 | no |
| ShippingDateTime | datetime2 | no | 8 | 27 | 7 | yes |
注意：
- 如果您正在使用MySQL Workbench运行SQL数据类型及其查询，那么有一些日期和时间的SQL数据类型和格式将不起作用，如”money”、”datetime2″、”yyyy/mm/dd”和”time AM”。所有这些指定的数据类型只与SQL服务器兼容。
- 这些数据类型的大小可能会在将来的更新中发生变化，请持续查看SQL文档。
Oracle数据类型
Oracle数据库中有四种主要类型的数据类型：字符串、数字、日期和时间以及大对象数据类型。以下部分列出了在Oracle数据库中的所有数据类型。
Oracle – 字符串数据类型
| 数据类型 | 描述 |
|---|---|
| CHAR(size) | 用于存储预定义长度内的字符数据。它最多可以存储2000个字节。 |
| NCHAR(size) | 用于存储预定义长度内的国家字符数据。它最多可以存储2000个字节。 |
| VARCHAR2(size) | 用于存储预定义长度内的可变字符串数据。它最多可以存储4000个字节。 |
| VARCHAR(SIZE) | 与VARCHAR2(size)相同。您也可以使用VARCHAR(size)，但建议使用VARCHAR2(size)。 |
| NVARCHAR2(size) | 用于存储预定义长度内的Unicode字符串数据。我们必须明确指定NVARCHAR2数据类型的大小。它最多可以存储4000个字节。 |
Oracle – 数值数据类型
| 数据类型 | 描述 |
|---|---|
| NUMBER(p, s) | 它包含精度 p 和标度 s。精度 p 的取值范围为 1 到 38，标度 s 的取值范围为 -84 到 127。 |
| FLOAT(p) | 它是 NUMBER 数据类型的子类型。精度 p 的取值范围为 1 到 126。 |
| BINARY_FLOAT | 它用于二进制精度（32 位）。它需要 5 个字节，包括长度字节。 |
| BINARY_DOUBLE | 它用于双精度二进制（64 位）。它需要 9 个字节，包括长度字节。 |
Oracle – 日期和时间数据类型
| 数据类型 | 描述 |
|---|---|
| DATE | 用于存储有效的日期-时间格式，长度固定。范围从公元前4712年1月1日到公元9999年12月31日。 |
| TIMESTAMP | 用于存储有效的日期，格式为YYYY-MM-DD，带有时间hh:mm:ss。 |
Oracle – 大对象数据类型（LOB类型）
| 数据类型 | 描述 |
|---|---|
| BLOB | 用于指定非结构化的二进制数据。其范围高达232-1字节或4GB。 |
| BFILE | 用于将二进制数据存储在外部文件中。其范围高达232-1字节或4GB。 |
| CLOB | 用于单字节字符数据。其范围高达232-1字节或4GB。 |
| NCLOB | 用于指定单字节或固定长度的多字节国家字符集（NCHAR）数据。其范围高达232-1字节或4GB。 |
| RAW(size) | 用于指定可变长度的原始二进制数据。每行的范围高达2000字节。必须指定最大大小。 |
| LONG RAW | 用于指定可变长度的原始二进制数据。每行的范围高达231-1字节或2GB。 |
MS Access数据类型
MS Access数据库还提供了四个数据类型的类别：字符串、数值、日期和时间以及其他专门的数据类型。
以下是MS Access 2013版本及之后提供的所有数据类型。
MS Access – 字符串数据类型
| 数据类型 | 描述 |
|---|---|
| Short Text (formerly “Text”) | 它是一种字符串数据类型，用于存储字母数字数据，如姓名、标题等。它可以容纳最多255个字符。 |
| Long Text (formerly “Memo”) | 它也是一种字符串数据类型，用于存储大型字母数字数据，如段落等。它可以容纳最多1GB或64,000个字符。 |
MS Access – 数值数据类型
| 数据类型 | 描述 |
|---|---|
| Number | 只能容纳数字数据。大小范围从1到16字节不等。 |
| Large Number | 也能容纳数字数据。该数据类型的最大大小为8字节。 |
MS Access – 日期和时间数据类型
| 数据类型 | 描述 |
|---|---|
| Date/Time | 它保存日期和时间数据。这种数据类型的最大大小是8个字节。 |
| Date/Time Extended | 它也保存日期和时间数据。这种数据类型的最大大小是42个字节的编码字符串。 |
MS Access – 专业数据类型
| 数据类型 | 描述 |
|---|---|
| Currency | 此数据类型可存储货币数据，精确到4位小数。数据类型的大小为8字节。 |
| AutoNumber | 此类型保存由MS Access为每个新记录生成的唯一值。数据类型的大小为4字节。 |
| Yes/No | 它以0和1的形式保存布尔数据。’0’表示false，’-1’表示true。最大大小为1字节。 |
| OLE Object | 它存储来自另一个基于Windows的应用程序的图片、图表或其他ActiveX对象。大小可存储多达2GB。 |
| Hyperlink | 它存储到互联网、局域网(LAN)或本地计算机上的文档或文件的链接地址。大小可达到8,192个字符。 |
| Attachment | MS Access中的附件数据类型允许用户附加图片、文档、电子表格或图表等。每个记录可以有无限数量的附件；但只能达到数据库文件大小的存储限制。 |
| Calculated | 使用此数据类型，您可以创建一个表达式，该表达式使用一个或多个字段的数据。然后，可以从该表达式创建结果数据类型。但是，此数据类型不适用于MDB文件格式。数据类型的大小可以根据结果类型而变化。 |
| Lookup Wizard | 查找向导在技术上不是一种数据类型。但是当选择此项时，向导会开始帮助用户定义一个查找字段。此数据类型的大小取决于查找字段的大小。 |