当前位置：首页 > article >正文

SQL基础入门—— SQL 数据类型

article 2024/12/2 21:31:19

SQL 数据类型用于指定数据库表中列的内容和类型。不同的数据库系统可能支持不同的数据类型，但常见的标准数据类型被广泛使用。掌握各种数据类型对于合理设计数据库结构、确保数据的正确性和高效存储至关重要。

1. 数字类型、字符类型、日期时间类型、布尔类型

1.1 数字类型

数字类型用于存储各种数值数据，包括整数、浮点数和精度更高的数值。常见的数字类型包括整数类型、浮动点类型和定点类型。

整数类型：
- INT（或 INTEGER）：存储有符号的整数，范围通常为 -2,147,483,648 到 2,147,483,647。
- TINYINT：非常小的整数类型，通常存储范围为 -128 到 127。
- SMALLINT：小范围的整数类型，通常存储范围为 -32,768 到 32,767。
- BIGINT：大范围整数类型，通常存储范围为 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807。
示例：
```
CREATE TABLE example (
    id INT PRIMARY KEY,
    age TINYINT,
    population BIGINT
);
```
浮点数类型：
- FLOAT：用于存储单精度浮动点数，适合存储浮动小数值。
- DOUBLE（或 DOUBLE PRECISION）：双精度浮动点数，提供更高的精度。
- DECIMAL（或 NUMERIC）：用于存储精确的小数，通常用于财务计算等需要高精度的应用，支持固定小数位数。
示例：

1.2 字符类型

字符类型用于存储文本数据。SQL中的字符类型大致分为定长字符和变长字符类型。

定长字符类型：
- CHAR(n)：定长字符类型，固定占用 n 个字符，不足的部分会用空格填充。适用于存储长度固定的字符串。
示例：
```
CREATE TABLE users (
    username CHAR(50)  -- 定长50字符
);
```
变长字符类型：
- VARCHAR(n)：变长字符类型，可以存储长度小于等于 n 的字符串。与 CHAR 相比，VARCHAR 在存储时不占用多余空间，更加节省存储。
示例：
```
CREATE TABLE users (
    username VARCHAR(255)  -- 最大255字符
);
```
TEXT：用于存储长文本数据，不指定长度限制。通常用于存储大块的文本，如文章、评论等。

示例：
```
CREATE TABLE blog_posts (
    content TEXT  -- 用于存储长文本
);
```

1.3 日期时间类型

日期时间类型用于存储日期和时间数据。在SQL中，日期时间类型的具体实现和名称可能会有所不同，但常见的数据类型包括：

DATE：只存储日期（年、月、日），不包括时间部分。

示例：
```
CREATE TABLE events (
    event_date DATE
);
```
TIME：只存储时间（小时、分钟、秒），不包括日期部分。

示例：
```
CREATE TABLE events (
    event_time TIME
);
```
DATETIME：同时存储日期和时间。大多数数据库系统支持这个类型。

示例：
```
CREATE TABLE events (
    event_datetime DATETIME
);
```
TIMESTAMP：表示时间戳，通常用于存储记录的创建时间或更新时间。TIMESTAMP 的存储方式依赖于数据库系统，并且通常会自动跟踪数据的变更。

示例：
```
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```
YEAR：用于存储年份（例如：2024）。

示例：
```
CREATE TABLE products (
    release_year YEAR
);
```

1.4 布尔类型

布尔类型用于存储真（TRUE）或假（FALSE）的值。虽然标准SQL没有定义布尔类型，但大多数数据库管理系统都支持这种类型。

BOOLEAN 或 BOOL：用于存储布尔值。在大多数数据库中，布尔类型通常映射为整数值 0（假）和 1（真）。

示例：
```
CREATE TABLE users (
    id INT PRIMARY KEY,
    is_active BOOLEAN
);
```
在许多数据库系统中（如MySQL），布尔类型实际上被当作 TINYINT(1) 处理，其中 0 表示 FALSE，1 表示 TRUE。

2. 字符串和日期的处理

SQL提供了许多内置函数来处理字符串和日期。这些函数能有效地简化开发者在进行数据操作时所需的计算和转换工作。

2.1 字符串处理

CONCAT：连接多个字符串。

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

LENGTH：返回字符串的长度。

SELECT LENGTH(username) FROM users;

SUBSTRING：从字符串中提取子串。

SELECT SUBSTRING(username, 1, 5) FROM users;  -- 获取用户名的前5个字符

UPPER 和 LOWER：将字符串转换为大写或小写。

SELECT UPPER(username) FROM users;
SELECT LOWER(username) FROM users;

REPLACE：替换字符串中的指定部分。

SELECT REPLACE(email, 'example.com', 'newdomain.com') FROM users;

2.2 日期时间处理

CURRENT_DATE 或 NOW：获取当前日期或当前时间。

SELECT CURRENT_DATE;  -- 返回当前日期
SELECT NOW();         -- 返回当前日期和时间

DATE_FORMAT：格式化日期或时间。

SELECT DATE_FORMAT(order_date, '%Y-%m-%d') FROM orders;

常用格式化符号：

%Y：四位年份
%m：两位月份
%d：两位日期
%H：小时（00-23）
%i：分钟（00-59）
%s：秒（00-59）
DATEDIFF：计算两个日期之间的天数差。

SELECT DATEDIFF(NOW(), created_at) AS days_since_created FROM users;

DATE_ADD 和 DATE_SUB：在日期上增加或减少指定的时间。

SELECT DATE_ADD(order_date, INTERVAL 7 DAY) FROM orders;  -- 增加7天
SELECT DATE_SUB(order_date, INTERVAL 1 MONTH) FROM orders;  -- 减少1个月

总结

SQL的数据类型用于指定表中列的数据类型，正确选择合适的数据类型能够帮助优化数据库性能、确保数据的完整性和一致性。数字类型用于存储各种数值数据，字符类型用于存储文本数据，日期时间类型用于存储时间相关信息，布尔类型则用于表示逻辑真值。此外，SQL还提供了丰富的字符串和日期函数，用于进行各种数据操作，帮助开发者简化复杂的查询任务。掌握这些基本数据类型及处理技巧是开发高效数据库应用的基础。