结构化查询语言(Structured Query Language)
SQL 数据类型:预定义数据类型简介
SQL 标准定义了三种数据类型:
- 预定义数据类型(如字符、数字、日期等)。
- 构造类型(如数组、集合、引用、行类型)。
- 用户自定义类型(类似于面向对象语言中的类,可包含构造器、方法等)。
以下主要介绍 预定义数据类型,它们是最常用的数据类型。
数据类型概览
SQL 标准将预定义数据类型分为以下几组:
-
字符类型:
- CHAR:定长字符串。
- VARCHAR:变长字符串。
- CLOB:大对象字符串。
-
二进制类型:
- BINARY:定长二进制数据。
- VARBINARY:变长二进制数据。
- BLOB:大对象二进制数据(如图像、音频)。
-
数值类型:
- 精确数值类型(NUMERIC, DECIMAL, SMALLINT, INTEGER, BIGINT)。
- 近似数值类型(FLOAT, REAL, DOUBLE PRECISION)。
-
日期和时间类型:
- DATE:日期(年、月、日)。
- TIME:时间(小时、分钟、秒,可带时区)。
- TIMESTAMP:时间戳(日期 + 时间,可带时区)。
- INTERVAL:时间间隔。
-
布尔类型:BOOLEAN(值为
TRUE
或FALSE
)。 -
扩展类型:
- XML(SQL:1999 引入)。
- JSON(SQL:2016 引入)。
数据类型详细说明
字符类型
字符类型用于存储字符串:
- CHAR
:定长字符串,长度固定为
n
。 - VARCHAR
:变长字符串,长度上限为
n
。 - CLOB:存储超大字符串(例如,MySQL 中称为
LONGTEXT
)。
示例:
-- 表中包含定长、变长和大对象字符串
CREATE TABLE datatypes_1 (
id DECIMAL PRIMARY KEY,
col_1 CHAR(10), -- 恰好 10 个字符
col_2 VARCHAR(150), -- 最多 150 个字符
col_3 CLOB -- 大型字符串
);
二进制类型
二进制类型存储任意二进制数据(如文件、图片):
- BINARY
:定长二进制数据,长度为
n
字节。 - VARBINARY
:变长二进制数据,最大长度为
n
字节。 - BLOB:大对象二进制数据。
示例:
-- 表中包含定长、变长和大对象二进制数据
CREATE TABLE datatypes_2 (
id DECIMAL PRIMARY KEY,
col_1 BINARY(10), -- 恰好 10 字节
col_2 VARBINARY(150), -- 最多 150 字节
col_3 BLOB -- 大型二进制对象,如 JPEG、MP3
);
精确数值类型
精确数值类型包括带小数和不带小数的数值:
- NUMERIC(p, s) 和 DECIMAL(p, s):
p
(精度):总位数。s
(标度):小数位数,必须满足p ≥ s ≥ 0
。
- SMALLINT、INTEGER、BIGINT:整数类型(不带小数)。
示例:
-- 表中使用精确数值类型
CREATE TABLE datatypes_3 (
id DECIMAL PRIMARY KEY,
col_1 DECIMAL(5,2), -- 总 5 位,小数 2 位
col_2 SMALLINT, -- 小整数,无小数点
col_3 INTEGER, -- 普通整数
col_4 BIGINT -- 大整数(Oracle 不支持)
);
近似数值类型
近似数值类型存储浮点数,通常用于对精度要求不高的计算:
- FLOAT:存储近似值。
- REAL 和 DOUBLE PRECISION:提供不同精度的浮点数。
创建表和列定义
在创建表时,使用 CREATE TABLE
定义数据类型。例如:
CREATE TABLE example_table (
column1 CHAR(10), -- 定长字符列
column2 VARCHAR(255), -- 变长字符列
column3 NUMERIC(10, 2), -- 精确数值列
column4 DATE -- 日期列
);
注意事项
-
默认值:可以为列设置默认值。例如:
CREATE TABLE example ( id INTEGER PRIMARY KEY, status CHAR(10) DEFAULT 'active' );
-
兼容性:不同 DBMS 对数据类型的支持可能存在差异。例如:
- Oracle 使用
RAW
表示变长二进制数据。 - MySQL 使用
TEXT
和BLOB
表示大型对象。
- Oracle 使用
通过这些数据类型的定义,您可以为数据库表的设计提供更多灵活性。
近似数值类型 (Approximate Numeric)
近似数值类型用于存储具有实现定义精度的数值(小数点后的精度由具体实现决定)。所有近似数值类型均为带符号类型,主要用于科学计算。
近似数值类型包括以下三种:
- FLOAT(p):其中
p
表示保证的小数点后位数。 - REAL 和 DOUBLE PRECISION:精度由具体实现定义。
示例:使用近似数值类型创建表
CREATE TABLE datatypes_4 (
id DECIMAL PRIMARY KEY,
col_1 FLOAT(2), -- 小数点后两位或更多
col_2 REAL, -- 实现定义的精度
col_3 DOUBLE PRECISION -- 实现定义的更高精度
);
时间类型 (Temporal)
与时间相关的数据类型包括:
- DATE:存储年、月、日。
- TIME:存储小时、分钟、秒。
- TIMESTAMP:存储年、月、日、小时、分钟、秒,秒数可以有小数点后的精度。
- INTERVAL:存储时间间隔。
SQL 标准定义了两种类型的 INTERVAL:
- INTERVAL YEAR TO MONTH:表示年和月的间隔。
- INTERVAL DAY TO SECOND:表示天、小时、分钟、秒的间隔(秒数可以有小数位)。
示例:使用时间数据类型创建表
CREATE TABLE datatypes_5 (
id DECIMAL PRIMARY KEY,
col_1 DATE, -- 存储年、月、日(Oracle:还包括小时、分钟、秒)
col_2 TIME, -- 存储时间
col_3 TIMESTAMP(9), -- 时间戳,小数点后 9 位精度
col_4 TIMESTAMP WITH TIME ZONE, -- 包含时区信息的时间戳
col_5 INTERVAL YEAR TO MONTH, -- 表示年到月的时间间隔
col_6 INTERVAL DAY TO SECOND(6) -- 表示天到秒的时间间隔,秒数小数点后 6 位精度
);
注意:
- Oracle 用户:不支持
TIME
类型,使用DATE
替代。 - MySQL 用户:不支持时区作为数据类型的一部分,需使用其他方法处理时区。不支持秒的小数部分和
INTERVAL
类型,但支持INTERVAL
数据值。
布尔类型 (Boolean)
SQL 支持三值逻辑(3-value-logic):TRUE
(真)、FALSE
(假)和 UNKNOWN
(未知)。布尔数据类型的列可以存储 TRUE
或 FALSE
,UNKNOWN
通过存储 NULL
表示。
示例:布尔类型
CREATE TABLE datatypes_6 (
id DECIMAL PRIMARY KEY,
col_1 BOOLEAN -- 不被 Oracle 支持
);
XML 类型
SQL 标准(第 14 部分)扩展了预定义数据类型,新增了 XML 类型,并为其定义了大量专用函数。
示例:XML 数据类型
CREATE TABLE datatypes_7 (
id DECIMAL PRIMARY KEY,
col_1 XML
);
注意:
- Oracle 用户:将 XML 类型表示为
XMLType
。 - MySQL 用户:不支持 XML 数据类型。
域 (Domains)
域(Domain)是数据类型的一种扩展,用于约束列中可以存储的有效值集合。域的概念是用户自定义类型的早期前身,可能已经过时。
清理示例表
DROP TABLE datatypes_1;
DROP TABLE datatypes_2;
DROP TABLE datatypes_3;
DROP TABLE datatypes_4;
DROP TABLE datatypes_5;
DROP TABLE datatypes_6;
DROP TABLE datatypes_7;