Sql 简明教程

SQL - Data Types

What are SQL Data types?

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

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

Name of the column
列的数据类型

例如，如果要将学生姓名存储在列中，则应将列名称指定为 student_name ，其数据类型为 char(50) ，这意味着它可以存储最多 50 个字符的字符串。

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

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

Defining a Data Type

在数据库中创建表时，定义 SQL 数据类型。创建表时，需要指定其相应的数据类型和大小以及列的名称。

以下是在 MySQL 中指定数据类型的语法 −

CREATE TABLE table_name(column1 datatype, column2 datatype....)

让我们看一个示例查询以更好地理解。

CREATE TABLE Customers (Name VARCHAR (25), Age INT);

在上面的 SQL 查询中，我们正在创建 Customer 表。由于 Name 列只存储字符串值，我们将其数据类型指定为 "VARCHAR"。VARCHAR 数据类型在 SQL 中表示字符串值。同样，我们使用整数数据类型 "INT" 定义 Age 列。

Types of SQL Data Types

任何 RDBMS 中有三种主要的 SQL 数据类型。它们如下列出 −

String
Numeric
Date and Time

Data Types in MySQL, SQL Server, Oracle, and MS Access Databases

下面详细讨论 MySQL、SQL Server、Oracle（PL/SQL）和 MS Access 数据库中的数据类型。以下说明 SQL 数据类型的所有叁种主要类型（字符串、数字、日期和时间）

MySQL Data Types

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

Data type

Description

CHAR(size)

一个固定长度的字符串，其中可以包含字母、数字和特殊字符。大小参数指定列长度（以字符为单位），可以从 0 到 255 不等。默认大小为 1

VARCHAR(size)

一个可变长度的字符串，其中可以包含字母、数字和特殊字符。大小参数指定字符串最大长度（以字符为单位），可以从 0 到 65535 不等。

BINARY(size)

这等同于 CHAR()，但存储二进制字节字符串。大小参数指定列长度（以字节为单位）。默认大小为 1

VARBINARY(size)

这等同于 VARCHAR()，但存储二进制字节字符串。大小参数指定列最大长度（以字节为单位）。

TINYTEXT

这保存一个字符串，其最大长度为 255 个字符

TEXT(size)

这保存一个字符串，其最大长度为 65,535 个字节

LONGTEXT

这保存一个字符串，其最大长度为 4,294,967,295 个字符

TINYBLOB

这表示一个小的 BLOB（二进制大对象）。最大长度为 255 个字节

BLOB(size)

这表示一个 BLOB（二进制大对象）。保存多达 65,535 个字节的数据

MEDIUMTEXT

这保存一个字符串，其最大长度为 16,777,215 个字符

MEDIUMBLOB

这表示一个中等 BLOB（二进制大对象）。保存多达 16,777,215 个字节的数据

LONGBLOB

这表示一个大型 BLOB（二进制大对象）。保存多达 4,294,967,295 个字节的数据

ENUM(val1, val2, val3, …)

一个字符串对象，只能包含一个值，该值是从可能的取值列表中选出的。您可以在 ENUM 列表中列出多达 65535 个值。如果插入的值不在列表中，系统将插入一个空值。按您输入值顺序对值进行排序

SET(val1, val2, val3, …)

一个字符串对象，可以包含 0 个或更多值，这些值从可能的取值列表中选出。您可以在 SET 列表中列出多达 64 个值

Data type

Description

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 是位数总数（包括小数部分）。对于 FLOAT，小数精度可以达到 24 位。

DOUBLE(M,D)

双精度浮点数，不能是无符号的。您可以定义显示长度 (M) 和小数位数 (D)。这是不必须的，将默认使用 16,4，其中 4 是小数位数。对于 DOUBLE，小数精度可以达到 53 位。REAL 是 DOUBLE 的同义词。

DECIMAL(M,D)

未打包的浮点数，不能是无符号的。在未打包的小数中，每个小数对应于一个字节。要求定义显示长度 (M) 和小数位数 (D)。NUMERIC 是 DECIMAL 的同义词。

Data type

Description

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)），则 YEAR 可以介于 1970 年到 2069 年之间（70 年到 69 年）。如果将长度指定为 4，则 YEAR 可以介于 1901 年到 2155 年之间。默认长度为 4。

MS SQL Server Data Types

正如我们在本章中之前讨论的，MS SQL Server 中有三个主要数据类型。它们是：字符串、数字以及日期和时间。

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

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

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

Data type

Description

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)

它包含最大长度为可变宽度二进制字符串。该数据类型的最大大小为 2 GB。

image

它包含可变长度的数据类型，可以存储二进制数据。该数据类型的最大大小为 2 GB。

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

gender

char

city

text

MS SQL Server - Numeric Data Types

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

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

Data type

Description

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)

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

int

myBoolean

bit

Fee

money

yes

Celsius

float

NULL

MS SQL Server - Date and Time Data Types

datetime 数据类型在 SQL 中用于同时包含日期和时间的数值。 datetime 和 time 值分别定义在下列格式里： yyyy-mm-dd 、 hh:mm:ss.nnnnnnn （n 取决于列定义）。

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

Data type

Description

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

它与 datetime2 相同，但增加了时区偏移量。

timestamp

它存储每次创建或修改行时更新的唯一编号。它不对应于真实时间，并且基于内部时间。每个表只可能有一个时间戳变量。

Note − 在此处，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

OrderDateTime

datetime

yes

ScheduleFrom

time

ShippingDateTime

datetime2

yes

注意：

如果您使用 MySQL 工作台来运行 SQL 数据类型及其查询，那么存在一些 SQL 数据类型和日期和时间格式不起作用；比如 "money"、"datetime2"、"yyyy/mm/dd" 和 "time AM"。指定的所有这些数据类型仅与 SQL 服务器兼容。
这些数据类型的尺寸可能会在未来更新中发生变化，请持续查看 SQL 文档。

Oracle Data Types

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

Data type

Description

CHAR(size)

用于存储预定义长度内的字符数据。它最多可存储 2000 字节。

NCHAR(size)

用于存储预定义长度内的国家字符数据。它最多可存储 2000 字节。

VARCHAR2(size)

用于存储预定义长度内的可变字符串数据。它最多可存储 4000 字节。

VARCHAR(SIZE)

它与 VARCHAR2(size) 相同。您也可以使用 VARCHAR(size)，但建议使用 VARCHAR2(size)。

NVARCHAR2(size)

用于存储预定义长度内的 Unicode 字符串数据。我们必须指定 NVARCHAR2 数据类型的大小。它最多可存储 4000 字节。

Data type

Description

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 字节，包括长度字节。

Data type

Description

DATE

用于存储具有固定长度的有效日期时间格式。其范围从公元前 4712 年 1 月 1 日到公元 9999 年 12 月 31 日。

TIMESTAMP

用于以 YYYY-MM-DD 和 hh:mm:ss 格式存储有效日期。

Data type

Description

BLOB

用于指定非结构化的二进制数据。其范围可达 232-1 字节或 4 GB。

BFILE

用于将二进制数据存储在外部文件中。其范围可达 232-1 字节或 4 GB。

CLOB

用于单字节字符数据。其范围可达 232-1 字节或 4 GB。

NCLOB

用于指定单字节或固定长度多字节国家字符集 (NCHAR) 数据。其范围为最多 232-1 字节或 4 GB。

RAW(size)

用于指定可变长度的原始二进制数据。其范围为每行最多 2000 字节。必须指定其最大大小。

LONG RAW

用于指定可变长度的原始二进制数据。其范围为每行最多 231-1 字节或 2 GB。

MS Access Data Types

MS Access 数据库还提供了四类数据类型：字符串、数字、日期和时间，以及其他特殊数据类型。

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

Data type

Description

Short Text (formerly "Text")

它是一种可以容纳字母数字数据（如名称、标题等）的字符串数据类型。它可以容纳多达 255 个字符。

Long Text (formerly "Memo")

它也是一种可以容纳大量字母数字数据（如段落等）的字符串数据类型。它可以容纳多达 1GB 或 64,000 个字符。

Data type

Description

Number

它只容纳数字数据。大小范围从 1 到 16 个字节。

Large Number

它也容纳数字数据。此数据类型的最大大小为 8 个字节。

Data type

Description

Date/Time

它容纳日期和时间数据。此数据类型的最大大小为 8 个字节。

Date/Time Extended

它也容纳日期和时间数据。此数据类型的最大大小为 42 个字节的编码字符串。

Data type

Description

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

查询向导在技术上不是一种数据类型。但当选择此条目时，会启动一个向导帮助用户定义一个查找字段。此数据类型的长度取决于查找字段的长度。