SQL - 数据类型


什么是 SQL 数据类型?

SQL数据类型是指可以存储在数据库表的列中的数据类型。例如整数数据、字符数据、货币数据、日期和时间数据、二进制字符串等。

在数据库中创建数据库表时,我们需要指定以下两个属性来定义表列:

  • 列名
  • 列的数据类型
数据库表的列定义数据,而数据库表行将数据填充到表中。

例如,如果您想在列中存储学生姓名,那么您应该为列名称指定类似于Student_name 的名称,并且其数据类型为char(50),这意味着它可以存储最多 50 个字符的字符串。

数据类型为 SQL 提供指导,以了解每列中期望的数据类型,从而防止用户在列中输入任何意外或无效的数据。例如,如果我们希望一列只存储整数值,我们可以将其数据类型指定为 INT。如果除整数之外的任何其他值插入到该特定列中,SQL 将显示错误。

不同的 RDBMS 支持不同类型的数据类型来定义其表。本教程将列出 MySQL、Oracle、SQL Server 和 MS Access 数据库中可用的不同数据类型。

SQL 数据类型的类型

任何 RDBMS 中都提供三种主要类型的 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 中的字符串值。同样,我们使用整数数据类型“INT”定义 Age 列。

当我们为列分配适当的数据类型时,我们可以通过仅分配相关列中的数据所需的系统内存量来有效地利用内存。

MySQL 数据类型

MySQL 数据库中提供三种主要数据类型:字符串、数字以及日期和时间。以下部分列出了 MySQL 8.0 版本中可用的所有数据类型

MySQL - 字符串数据类型

Data type Description
CHAR(size) A fixed length string which can have letters, numbers, and special characters. The size parameter specifies the column length in characters which can vary from from 0 to 255. Default size is 1
VARCHAR(size) A variable length string which can contain letters, numbers, and special characters. The size parameter specifies the maximum string length in characters which can vary from 0 to 65535.
BINARY(size) This is equal to CHAR(), but stores binary byte strings. The size parameter specifies the column length in bytes. Default size is 1
VARBINARY(size) This is equal to VARCHAR(), but stores binary byte strings. The size parameter specifies the maximum column length in bytes.
TINYTEXT This holds a string with a maximum length of 255 characters
TEXT(size) This holds a string with a maximum length of 65,535 bytes
LONGTEXT This holds a string with a maximum length of 4,294,967,295 characters
TINYBLOB This represents a small BLOBs (Binary Large Objects). Max length is 255 bytes
BLOB(size) This represents a BLOBs (Binary Large Objects). Holds up to 65,535 bytes of data
MEDIUMTEXT This holds a string with a maximum length of 16,777,215 characters
MEDIUMBLOB This represents a medium BLOBs (Binary Large Objects). Holds up to 16,777,215 bytes of data
LONGBLOB This represents a large BLOBs (Binary Large Objects). Holds up to 4,294,967,295 bytes of data
ENUM(val1, val2, val3, ...) A string object that can contain only one value, chosen from a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted. The values are sorted in the order you enter them
SET(val1, val2, val3, ...) A string object that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values in a SET list

MySQL - 数字数据类型

数据类型 描述
INT正常大小的整数,可以有符号或无符号。如果有符号,则允许的范围为 -2147483648 到 2147483647。如果无符号,则允许的范围为 0 到 4294967295。您最多可以指定 11 位的宽度。
天音一个非常小的整数,可以有符号或无符号。如果有符号,允许的范围是 -128 到 127。如果无符号,允许的范围是 0 到 255。您可以指定最多 4 位的宽度。
小智一个小整数,可以有符号或无符号。如果有符号,允许的范围是 -32768 到 32767。如果无符号,允许的范围是 0 到 65535。您可以指定最多 5 位的宽度。
中薄荷一个中等大小的整数,可以有符号或无符号。如果有符号,允许的范围是 -8388608 到 8388607。如果无符号,允许的范围是 0 到 16777215。您最多可以指定 9 位的宽度。
BIGINT可以有符号或无符号的大整数。如果有符号,则允许的范围为 -9223372036854775808 到 9223372036854775807。如果无符号,则允许的范围为 0 到 18446744073709551615。您最多可以指定 20 位的宽度。
浮子(中、深)不能无符号的浮点数。您可以定义显示长度 (M) 和小数位数 (D)。这不是必需的,默认为 10,2,其中 2 是小数位数,10 是总位数(包括小数)。FLOAT 的小数精度可以达到 24 位。
双人间(男、女)不能无符号的双精度浮点数。您可以定义显示长度 (M) 和小数位数 (D)。这不是必需的,默认为 16,4,其中 4 是小数位数。对于 DOUBLE,小数精度可以达到 53 位。REAL 是 DOUBLE 的同义词。
小数(M,D)无法无符号的未压缩浮点数。在解压缩的小数中,每个小数对应一个字节。需要定义显示长度 (M) 和小数位数 (D)。NUMERIC 是 DECIMAL 的同义词。

MySQL - 日期和时间数据类型

数据类型 描述
日期YYYY-MM-DD 格式的日期,介于 1000-01-01 和 9999-12-31 之间。例如,1973 年 12 月 30 日存储为 1973-12-30。
约会时间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。
时间戳1970 年 1 月 1日午夜到 2037 年某个时间之间的时间戳。这看起来像以前的 DATETIME 格式,只是数字之间没有连字符;1973 年12月 30 日下午 3:30将存储为 19731230153000 (YYYYMMDDHHMMSS)。
时间以 HH:MM:SS 格式存储时间。
年(月)以 2 位或 4 位格式存储年份。如果长度指定为 2(例如 YEAR(2)),则 YEAR 可以介于 1970 到 2069(70 到 69)之间。如果长度指定为 4,则 YEAR 可以是 1901 到 2155。默认长度为 4。

SQL Server 数据类型

正如我们之前在本章中讨论的,SQL Server 中存在三种主要数据类型。它们是:字符串、数字、日期和时间。

SQL Server - 字符串数据类型

SQL 中的字符串数据类型允许我们在表列的记录中存储一组用单引号括起来的字符。这些字符可以是任何类型:数字字母符号等。

用户可以根据自己的喜好存储固定数量的字符或可变数量的字符。

以下是 SQL 中字符串数据类型下包含的数据类型的列表。

数据类型 描述
字符(n)它保存固定宽度的字符串。此数据类型的最大大小为 8000 个字符。
varchar(n)它保存宽度可变的字符串。此数据类型的最大大小也是 8000 个字符。
varchar(最大)它保存宽度可变的字符串。此数据类型的最大大小为 1073741824 个字符。
文本它保存宽度可变的字符串。该数据类型最多可以存储 2GB 文本数据。
恩查尔它保存固定宽度的 Unicode 字符串。此数据类型的最大大小也是 4000 个字符。
nvarchar它保存宽度可变的 Unicode 字符串。此数据类型的最大大小也是 4000 个字符。
文本它保存宽度可变的 Unicode 字符串。该数据类型最多可以存储 2GB 文本数据。
二进制(n)它保存具有固定宽度的二进制字符串。
瓦尔二进制它保存宽度可变的二进制字符串。该数据类型的最大大小也是 8000 字节。
varbinary(最大)它保存最大长度可变宽度的二进制字符串。此数据类型的最大大小为 2 GB。
图像它保存可以存储二进制数据的数据类型的可变长度。此数据类型的最大大小为 2 GB。
Nvarchar(最大)它保存最大长度可变宽度的 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';” 我们获取表的详细信息和每列的数据类型。

列名 类型 计算的 长度 普雷克 规模 可空
姓名 varchar 20
性别 字符 6
城市 文本 16

SQL Server - 数字数据类型

数字数据类型是 SQL 中使用最广泛的数据类型之一。它们仅用于存储数值。

以下是 SQL 中数字数据类型下包含的数据类型列表。

数据类型 描述
少量它保存的整数可以是 0、1 或 NULL。
小整数它允许保存 0 到 255 之间的整数。
小整数它允许保存 -32,768 到 32,767 之间的数字。
整数它允许保存 -2,147,483,648 和 2,147,483,647 之间的整数。
大整数它允许保存 -9,223,372,036,854,775,808 和 9,223,372,036,854,775,807 之间的整数
小数(p,s)

它是固定精度和小数位数。允许从 -10^38 + 1 到 10^38-1 的数字。

p参数表示小数点左右两边可以存储的最大总位数。它的值必须介于 1 到 38 之间。默认值为 18。

s参数表示小数点右边的最大位数。S 必须是 0 到 p 之间的值。默认情况下该值设置为 0。

数字(p,s)

它是固定精度和小数位数。允许从 -10^38 + 1 到 10^38-1 的数字。

p参数表示小数点左右两边可以存储的最大总位数。它的值必须介于 1 到 38 之间。默认值为 18。

s参数表示小数点右边的最大位数。S 必须是 0 到 p 之间的值。默认情况下该值设置为 0。

小钱它保存从-214,748.3648到214,748.3647的货币数据。
它保存的货币数据从-922,337,203,685,477.5808到922,337,203,685,477.5807。

浮点数(n)

它保存或存储从-1.79E + 308到1.79E + 308的浮动进动数数据。

n 参数指示该字段应保存 4 个字节还是 8 个字节。Float(24) 包含一个 4 字节字段,而 Float(53) 包含一个 8 字节字段。n 的默认值为 53。

真实的它保存从-3.40E + 38到3.40E + 38的浮点精度数字数据。

例子

在以下示例中,我们将创建一个名为员工的表,其中仅包含数字数据类型值。

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;” 我们获取表的详细信息和每列的数据类型。

列名 类型 计算的 长度 普雷克 规模 可空
ID 整数 4 10 0
我的布尔值 少量 1
费用 18 19 4 是的
摄氏度 漂浮 8 53 无效的

SQL Server - 日期和时间数据类型

日期时间数据类型在 SQL 中用于包含日期和时间的值。日期时间时间值分别以以下格式定义:yyyy-mm-ddhh:mm:ss.nnnnnnn(n 取决于列定义)。

以下是 SQL 中日期和时间数据类型下包含的数据类型列表。

数据类型 描述
约会时间它存储从1753年1月1日到9999年12月31日的日期和时间,精度为3.33毫秒。
日期时间2它存储从 0001 年 1 月 1 日到 9999 年 12 月 31 日的日期和时间,精度为 100 纳秒。
小日期时间它存储从1900年1月1日到2079年6月6日的日期和时间,精度为1分钟。
日期它存储的日期仅从 0001 年 1 月 1 日到 9999 年 12 月 31 日。
时间它存储时间的精度仅为 100 纳秒。
日期时间偏移量它与 datetime2 相同,只是添加了时区偏移量。
时间戳它存储每次创建或修改行时都会更新的唯一编号。它不对应于实时时间,而是基于内部时间。每个表可能只有一个时间戳变量。

注意- 这里,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;” 我们获取表的详细信息和每列的数据类型。

列名 类型 计算的 长度 普雷克 规模 可空
假期日期 日期 3 10 0
订单日期时间 约会时间 8 是的
时间表从 时间 5 16 7
发货日期时间 日期时间2 8 27 7 是的

笔记:

  • 如果您使用 MySQL 工作台运行 SQL 数据类型及其查询,则某些 SQL 数据类型以及日期和时间格式将不起作用;例如“金钱”、“datetime2”、“yyyy/mm/dd”和“上午时间”。所有这些指定的数据类型仅与 SQL Server 兼容。
  • 这些数据类型的大小可能会在未来的更新中发生变化,请继续检查 SQL 文档。

Oracle 数据类型

Oracle 数据库中有四种主要的数据类型:字符串、数字、日期和时间以及大对象数据类型。以下部分列出了 Oracle 数据库中可用的所有数据类型。

Oracle - 字符串数据类型

数据类型 描述
字符(大小)它用于存储预定义长度内的字符数据。最多可存储2000字节。
NCHAR(大小)用于存储预定长度内的国家字符数据。最多可存储2000字节。
VARCHAR2(大小)它用于存储预定义长度内的可变字符串数据。最多可存储4000字节。
VARCHAR(大小)它与 VARCHAR2(size) 相同。也可以使用VARCHAR(size),但建议使用VARCHAR2(size)
NVARCHAR2(大小)它用于存储预定义长度内的Unicode字符串数据。我们必须指定 NVARCHAR2 数据类型的大小。最多可存储 4000 字节。

Oracle - 数字数据类型

数据类型 描述
数量(p,s)它包含精度 p 和标度 s。精度 p 的范围为 1 到 38,小数位 s 的范围为 -84 到 127。
浮动(p)它是 NUMBER 数据类型的子类型。精度 p 的范围为 1 到 126。
BINARY_FLOAT它用于二进制精度(32 位)。它需要 5 个字节,包括长度字节。
BINARY_DOUBLE它用于双二进制精度(64 位)。它需要 9 个字节,包括长度字节。

Oracle - 日期和时间数据类型

数据类型 描述
日期它用于存储具有固定长度的有效日期时间格式。其范围从公元前4712年1月1日到公元9999年12月31日。
时间戳它用于以 YYYY-MM-DD 格式存储有效日期,时间格式为 hh:mm:ss。

Oracle - 大对象数据类型(LOB 类型)

数据类型 描述
BLOB它用于指定非结构化二进制数据。其范围可达 232-1 字节或 4 GB。
B文件它用于将二进制数据存储在外部文件中。其范围可达 232-1 字节或 4 GB。
CLOB它用于单字节字符数据。其范围可达 232-1 字节或 4 GB。
NCLOB它用于指定单字节或固定长度多字节国家字符集(NCHAR)数据。其范围最大为 232-1 字节或 4 GB。
原始(大小)它用于指定可变长度的原始二进制数据。其范围最多为每行 2000 字节。必须指定其最大尺寸。
长生它用于指定可变长度的原始二进制数据。每行的范围可达 231-1 字节或 2 GB。

MS Access 数据类型

MS Access 数据库还提供四类数据类型:字符串、数字、日期和时间以及其他专用数据类型。

以下是 MS Access 2013 版本及更高版本提供的所有数据类型。

MS Access - 字符串数据类型

数据类型 描述
短文本(以前称为“文本”)它是一种字符串数据类型,可保存字母数字数据,例如姓名、标题等。最多可容纳 255 个字符。
长文本(以前的“备忘录”)它也是一种字符串数据类型,可容纳大型字母数字数据,如段落等。它最多可容纳 1GB 或 64,000 个字符。

MS Access - 数字数据类型

数据类型 描述
数字它仅保存数字数据。大小范围为 1 到 16 字节。
大数字它还保存数字数据。该数据类型的最大大小为 8 个字节。

MS Access - 日期和时间数据类型

数据类型 描述
约会时间它保存日期和时间数据。该数据类型的最大大小为 8 个字节。
延长日期/时间它还保存日期和时间数据。该数据类型的最大大小为 42 字节的编码字符串。

MS Access - 专用数据类型

数据类型 描述
货币此数据类型存储货币数据,精度最多为小数点后 4 位。该数据类型的大小为 8 个字节。
自动编号这存储了 MS Access 为每个新记录生成的唯一值。该数据类型的大小为 4 个字节。
是/否它以 0 和 1 的形式保存布尔数据。“0”表示 false,“-1”表示 true。最大大小为 1 字节。
OLE对象它存储来自另一个基于 Window 的应用程序的图片、图表或其他 ActiveX 对象。最大可存储2GB大小。
超级链接它存储 Internet、Intranet、局域网 (LAN) 或本地计算机上的文档或文件的链接地址。大小最多可达 8,192 个字符。
依恋MS Access 中的附件数据类型允许用户附加图片、文档、电子表格或图表等。每条记录可以有无限数量的附件;但最多只能达到数据库文件大小的存储限制。
已计算使用此数据类型,您可以创建一个使用一个或多个字段中的数据的表达式。然后,还可以根据该表达式创建结果数据类型。但是,此数据类型在 MDB 文件格式中不可用。此数据类型的大小可能因结果类型而异。
查找向导从技术上讲,查找向导并不是一种数据类型。但是,当选择此条目时,向导将开始帮助用户定义查找字段。该数据类型的大小取决于查找字段的大小。