一文搞懂 SQL 数据类型：详解与实战举例，让你轻松上手！

大家好，欢迎来到IT知识分享网。

SQL 数据类型及举例

SQL 数据类型根据其存储数据的性质和用途可以分为以下几类：

4.1 数值类型 (Numeric Types)

数值类型用于存储整数和浮点数。这些类型确保数据在存储和计算时具有高效性和准确性。

• INT：存储整数。例如，123。

  CREATE TABLE example ( id INT, age INT ); 

• DECIMAL 或 NUMERIC：用于存储精确的小数。格式为 DECIMAL(M, D)，其中 M 是数字总长度，D 是小数点后的位数。例如，123.45。

  CREATE TABLE example ( salary DECIMAL(10, 2) ); 

• FLOAT：用于存储浮点数。它们在存储和计算时可能会丢失精度。例如，123.456。

  CREATE TABLE example ( temperature FLOAT ); 

4.2 日期和时间类型 (Date and Time Types)

日期和时间类型用于存储日期、时间或两者的组合。

• DATE：存储日期。格式为 YYYY-MM-DD。例如，2024-06-02。

  CREATE TABLE example ( birthdate DATE ); 

• TIME：存储时间。格式为 HH:MM:SS。例如，14:30:00。

  CREATE TABLE example ( appointment_time TIME ); 

• DATETIME：存储日期和时间的组合。格式为 YYYY-MM-DD HH:MM:SS。例如，2024-06-02 14:30:00。

  CREATE TABLE example ( created_at DATETIME ); 

• TIMESTAMP：存储时间戳，通常用于记录行的创建或更新时间。格式同 DATETIME，但具有自动更新特性。例如，2024-06-02 14:30:00。

  CREATE TABLE example ( updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); 

4.3 文本字符串类型 (Character String Types)

文本字符串类型用于存储文本数据。长度可以是固定的或可变的。

• CHAR：存储固定长度的字符串。如果存储的字符串长度小于指定长度，剩余部分将用空格填充。例如，'Hello '（长度为 6）。

  CREATE TABLE example ( gender CHAR(1) ); 

• VARCHAR：存储可变长度的字符串。长度范围在定义时指定，实际存储时仅占用实际字符的空间。例如，'Hello'（长度为 5）。

  CREATE TABLE example ( name VARCHAR(50) ); 

• TEXT：存储大文本数据。适用于存储长字符串，例如文章、博客内容等。

  CREATE TABLE example ( description TEXT ); 

4.4 二进制字符串类型 (Binary String Types)

二进制字符串类型用于存储二进制数据，例如图像、文件等。

• BINARY：存储固定长度的二进制数据。如果数据不足指定长度，剩余部分将用 0x00 填充。

  CREATE TABLE example ( binary_data BINARY(16) ); 

• VARBINARY：存储可变长度的二进制数据。长度范围在定义时指定，实际存储时仅占用实际数据的空间。

  CREATE TABLE example ( binary_data VARBINARY(256) ); 

• BLOB：存储大二进制对象。适用于存储图像、音频、视频等大数据。

  CREATE TABLE example ( picture BLOB ); 

举例

1. 创建包含不同数据类型的表：

   CREATE TABLE example ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), birthdate DATE, email VARCHAR(255), profile_picture BLOB, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); 

2. 插入数据：

   INSERT INTO example (name, birthdate, email) VALUES ('John Doe', '1980-05-15', ''); 

3. 查询数据：

   SELECT * FROM example; 

这些示例展示了如何在 SQL 中使用各种数据类型来创建表、插入数据和查询数据。选择适当的数据类型可以提高数据库的性能和存储效率。

案例1

create table orders ( id int primary key auto_increment, item_id int, amount int, unit_price decimal(12, 4), price decimal(12, 4), ts timestamp default current_timestamp ); 

1. create table orders

这部分语句表示创建一个名为 orders 的表。

2. id int primary key auto_increment

• id：列名
• int：数据类型，表示 id 列是整数类型。
• primary key：定义 id 列为主键。主键在表中必须唯一，并且不能为 NULL。
• auto_increment：表示 id 列的值会自动递增，每插入一行记录，id 值会自动加 1。这确保一个唯一的标识符。

3. item_id int

• item_id：列名。
• int：数据类型，表示 item_id 列是整数类型。

4. amount int

• amount：列名。
• int：数据类型，表示 amount 列是整数类型。

5. unit_price decimal(12, 4)

• unit_price：列名。
• decimal(12, 4)：数据类型，表示 unit_price 列是一个具有 12 位数字和 4 位小数的十进制数。这确保有很高的精度。

6. price decimal(12, 4)

• price：列名。
• decimal(12, 4)：数据类型，表示 price 列是一个具有 12 位数字和 4 位小数的十进制数。

7. ts timestamp default current_timestamp

• ts：列名。
• timestamp：数据类型，表示 ts 列是一个时间戳类型。时间戳用于存储日期和时间。
• default current_timestamp：表示 ts 列的默认值为当前时间。每次插入新记录时，如果没有显式提供 ts 值，数据库将自动使用当前时间作为 ts 列的值。

详细解释 create table orders 语句

create table orders ( id int primary key auto_increment, item_id int, amount int, unit_price decimal(12, 4), price decimal(12, 4), ts timestamp default now() ); 

案例2

create table orders ( id int primary key auto_increment, item_id int, amount int, unit_price decimal(12, 4), price decimal(12, 4), description varchar(2000), picture blob, ts timestamp default now() ); 

解释：

1. id int primary key auto_increment：
   • id 列是整数类型，作为主键，并且是自增的。
2. item_id int：
   • item_id 列是整数类型，用于唯一标识符。
3. amount int：
   • amount 列是整数类型。
4. unit_price decimal(12, 4)：
   • unit_price 列是具有 12 位数字和 4 位小数的十进制数。
5. price decimal(12, 4)：
   • price 列是具有 12 位数字和 4 位小数的十进制数。
6. description varchar(2000)：
   • description 列是可变长度的字符串类型，最大长度为 2000 字节。
7. picture blob：
   • picture 列是 BLOB 类型，用于存储图片等。BLOB 类型是二进制大对象，可以存储二进制数据如图像文件等。标准的 BLOB 类型可以存储最大 64KB 的数据，如果图片大小不超过 100KB，可以使用 MEDIUMBLOB 类型。
8. ts timestamp default now()：
   • ts 列是时间戳类型，默认值为当前时间。每次插入新记录时，ts 列会自动记录插入时间。