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

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', '[e-mail]');

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 列会自动记录插入时间。

本文由mdnice多平台发布