SQL 数据类型详解及应用（七）
- Redis
- 8天前
- 7热度
- 0评论
在数据库开发中，正确选择和使用数据类型对于确保数据的准确性和性能至关重要。本文将详细介绍 SQL 中常见的数据类型，并探讨不同数据库系统（如 Microsoft Access、MySQL 和 SQL Server）中的具体实现。此外，我们还将介绍 SQL 中的聚合函数和标量函数，帮助你在实际项目中更好地利用这些工具。
SQL 数据类型概览
SQL 数据类型用于定义表中列可以存储的数据种类。每个列在创建时都需要指定一个名称和数据类型。数据类型不仅决定了列可以存储的数据种类，还影响了 SQL 如何与这些数据进行交互。
常见的 SQL 数据类型
以下是一些常见的 SQL 数据类型及其用途：
- CHAR(n)：固定长度的字符串，长度为 n。
- VARCHAR(n)：可变长度的字符串，最大长度为 n。
- BINARY(n)：固定长度的二进制字符串，长度为 n。
- BOOLEAN：存储布尔值，即 TRUE 或 FALSE。
- VARBINARY(n)：可变长度的二进制字符串，最大长度为 n。
- INTEGER(p)：整数值，精度为 p。
- SMALLINT：整数值，精度为 5。
- INTEGER：整数值，精度为 10。
- BIGINT：整数值，精度为 19。
- DECIMAL(p, s)：精确数值，精度为 p，小数点后位数为 s。
- NUMERIC(p, s)：与 DECIMAL 相同，精确数值。
- FLOAT(p)：近似数值，尾数精度为 p。
- REAL：近似数值，尾数精度为 7。
- DOUBLE PRECISION：近似数值，尾数精度为 16。
- DATE：存储年、月、日的值。
- TIME：存储小时、分、秒的值。
- TIMESTAMP：存储年、月、日、小时、分、秒的值。
- INTERVAL：表示一段时间，由多个整数字段组成。
- ARRAY：固定长度的有序元素集合。
- MULTISET：可变长度的无序元素集合。
- XML：存储 XML 数据。
不同数据库系统中的数据类型
虽然 SQL 标准定义了一些通用的数据类型，但不同的数据库系统可能会有不同的实现。以下是一些常见数据库系统中的数据类型对比：
Microsoft Access 数据类型
| 数据类型 | 描述 | 存储 |
|---|---|---|
| Text | 用于文本或文本与数字的组合，最多 255 个字符。 | |
| Memo | 用于更大数量的文本，最多存储 65,536 个字符。无法排序，但可搜索。 | |
| Byte | 允许 0 到 255 的数字。 | 1 字节 |
| Integer | 允许 -32,768 到 32,767 的数字。 | 2 字节 |
| Long | 允许 -2,147,483,648 到 2,147,483,647 的数字。 | 4 字节 |
| Single | 单精度浮点数。 | 4 字节 |
| Double | 双精度浮点数。 | 8 字节 |
| Currency | 用于货币，支持 15 位整数和 4 位小数。 | 8 字节 |
| AutoNumber | 自动为每条记录分配数字，通常从 1 开始。 | 4 字节 |
| Date/Time | 用于日期和时间。 | 8 字节 |
| Yes/No | 逻辑字段，显示为 Yes/No、True/False 或 On/Off。 | 1 比特 |
| OLE Object | 存储图片、音频、视频等二进制大对象。 | 最多 1GB |
| Hyperlink | 包含指向其他文件的链接。 | |
| Lookup Wizard | 创建下拉列表选项。 | 4 字节 |
MySQL 数据类型
文本类型
| 数据类型 | 描述 |
|---|---|
| CHAR(size) | 固定长度的字符串，最多 255 个字符。 |
| VARCHAR(size) | 可变长度的字符串，最多 255 个字符。超过 255 个字符时转换为 TEXT。 |
| TINYTEXT | 最大长度为 255 个字符的字符串。 |
| TEXT | 最大长度为 65,535 个字符的字符串。 |
| BLOB | 用于二进制大对象，最多 65,535 字节。 |
| MEDIUMTEXT | 最大长度为 16,777,215 个字符的字符串。 |
| MEDIUMBLOB | 用于二进制大对象，最多 16,777,215 字节。 |
| LONGTEXT | 最大长度为 4,294,967,295 个字符的字符串。 |
| LONGBLOB | 用于二进制大对象，最多 4,294,967,295 字节。 |
| ENUM(x, y, z, etc.) | 允许输入预定义的值列表，最多 65535 个值。 |
| SET | 允许输入预定义的值列表，最多 64 个值，可存储多个选择。 |
数字类型
| 数据类型 | 描述 |
|---|---|
| TINYINT(size) | 带符号范围 -128 到 127，无符号范围 0 到 255。 |
| SMALLINT(size) | 带符号范围 -32768 到 32767，无符号范围 0 到 65535。 |
| MEDIUMINT(size) | 带符号范围 -8388608 到 8388607，无符号范围 0 到 16777215。 |
| INT(size) | 带符号范围 -2147483648 到 2147483647，无符号范围 0 到 4294967295。 |
| BIGINT(size) | 带符号范围 -9223372036854775808 到 9223372036854775807，无符号范围 0 到 18446744073709551615。 |
| FLOAT(size, d) | 浮点数，最大位数为 size，小数点后最大位数为 d。 |
| DOUBLE(size, d) | 双精度浮点数，最大位数为 size，小数点后最大位数为 d。 |
| DECIMAL(size, d) | 固定精度的数值，最大位数为 size，小数点后最大位数为 d。 |
日期类型
| 数据类型 | 描述 |
|---|---|
| DATE | 日期，格式为 YYYY-MM-DD。支持范围从 '1000-01-01' 到 '9999-12-31'。 |
| DATETIME | 日期和时间的组合，格式为 YYYY-MM-DD HH:MM:SS。支持范围从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'。 |
| TIMESTAMP | 时间戳，格式为 YYYY-MM-DD HH:MM:SS。支持范围从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC。 | | TIME | 时间，格式为 HH:MM:SS。支持范围从 '-838:59:59' 到 '838:59:59'。 | | YEAR | 年份，2 位或 4 位格式。4 位格式支持 1901 到 2155，2 位格式支持 70 到 69（表示 1970 到 2069）。 |
SQL Server 数据类型
字符串类型
| 数据类型 | 描述 | 存储 |
|---|---|---|
| char(n) | 固定长度的字符串，最多 8,000 个字符。 | 定义的宽度 |
| varchar(n) | 可变长度的字符串，最多 8,000 个字符。 | 2 字节 + 字符数 |
| varchar(max) | 可变长度的字符串，最多 1,073,741,824 个字符。 | 2 字节 + 字符数 |
| text | 可变长度的字符串，最多 2GB 文本数据。 | 4 字节 + 字符数 |
| nchar | 固定长度的 Unicode 字符串，最多 4,000 个字符。 | 定义的宽度 x 2 |
| nvarchar | 可变长度的 Unicode 字符串，最多 4,000 个字符。 | 2 字节 + 字符数 |
| nvarchar(max) | 可变长度的 Unicode 字符串，最多 536,870,912 个字符。 | 2 字节 + 字符数 |
| ntext | 可变长度的 Unicode 字符串，最多 2GB 文本数据。 | 4 字节 + 字符数 |
| bit | 允许 0、1 或 NULL。 | 1 比特 |
| binary(n) | 固定长度的二进制字符串，最多 8,000 字节。 | |
| varbinary(n) | 可变长度的二进制字符串，最多 8,000 字节。 | |
| varbinary(max) | 可变长度的二进制字符串，最多 2GB。 | |
| image | 可变长度的二进制字符串，最多 2GB。 |
数字类型
| 数据类型 | 描述 | 存储 |
|---|---|---|
| tinyint | 允许 0 到 255 的数字。 | 1 字节 |
| smallint | 允许 -32,768 到 32,767 的数字。 | 2 字节 |
| int | 允许 -2,147,483,648 到 2,147,483,647 的数字。 | 4 字节 |
| bigint | 允许 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807 的数字。 | 8 字节 |
| decimal(p, s) | 固定精度和比例的数字，最大位数为 p，小数点后最大位数为 s。 | 5-17 字节 |
| numeric(p, s) | 与 decimal 相同，固定精度和比例的数字。 | 5-17 字节 |
| smallmoney | 介于 -214,748.3648 到 214,748.3647 之间的货币数据。 | 4 字节 |
| money | 介于 -922,337,203,685,477.5808 到 922,337,203,685,477.5807 之间的货币数据。 | 8 字节 |
| float(n) | 浮点数，n 参数指示 4 字节或 8 字节。 | 4 或 8 字节 |
| real | 浮点数，范围从 -3.40E + 38 到 3.40E + 38。 | 4 字节 |
日期类型
| 数据类型 | 描述 | 存储 |
|---|---|---|
| datetime | 从 1753 年 1 月 1 日到 9999 年 12 月 31 日，精度为 3.33 毫秒。 | 8 字节 |
| datetime2 | 从 1753 年 1 月 1 日到 9999 年 12 月 31 日，精度为 100 纳秒。 | 6-8 字节 |
| smalldatetime | 从 1900 年 1 月 1 日到 2079 年 6 月 6 日，精度为 1 分钟。 | 4 字节 |
| date | 仅存储日期，从 0001 年 1 月 1 日到 9999 年 12 月 31 日。 | 3 字节 |
| time | 仅存储时间，精度为 100 纳秒。 | 3-5 字节 |
| datetimeoffset | 与 datetime2 相同，外加时区偏移。 | 8-10 字节 |
| timestamp | 存储唯一的数字，每当创建或修改某行时更新。 | 8 字节 |
其他数据类型
| 数据类型 | 描述 |
|---|---|
| sql_variant | 存储最多 8,000 字节的不同数据类型的数据，除 text、ntext 和 timestamp。 |
| uniqueidentifier | 存储全局唯一标识符 (GUID)。 |
| xml | 存储 XML 格式的数据，最多 2GB。 |
| cursor | 存储对数据库操作指针的引用。 |
| table | 存储结果集，供稍后处理。 |
SQL 聚合函数
SQL 聚合函数用于对列中的值进行计算，返回一个单一的值。以下是一些常用的聚合函数：
- AVG()：返回平均值。
- COUNT()：返回行数。
- FIRST()：返回第一个记录的值。
- LAST()：返回最后一个记录的值。
- MAX()：返回最大值。
- MIN()：返回最小值。
- SUM()：返回总和。
AVG() 函数示例
假设我们有一个 orders 表，其中包含 order_id 和 amount 列。我们可以使用 AVG() 函数计算订单金额的平均值：
SELECT AVG(amount) AS average_amount
FROM orders;SQL 标量函数
SQL 标量函数基于输入值返回一个单一的值。以下是一些常用的标量函数：
- UPPER()：将字符串转换为大写。
- LOWER()：将字符串转换为小写。
- LEN()：返回字符串的长度。
- SUBSTRING()：提取字符串的一部分。
- ROUND()：四舍五入数值。
- CONVERT()：将数据类型转换为另一种类型。
示例
假设我们有一个 employees 表，其中包含 first_name 和 last_name 列。我们可以使用 UPPER() 函数将员工的名字转换为大写：
SELECT UPPER(first_name) AS first_name_upper, last_name
FROM employees;总结
正确选择和使用数据类型是数据库设计的重要环节。不同的数据库系统提供了丰富的数据类型选择，开发者需要根据具体需求选择合适的数据类型。此外，SQL 提供了多种聚合函数和标量函数，帮助我们在查询中进行复杂的计算和数据处理。希望本文能为你在数据库开发中提供有价值的参考。