【数据库设计和SQL基础语法】--SQL语言概述--数据类型和约束

一、 数据类型

1.1 整数类型

整数类型是一种数据类型，用于存储整数值。在数据库中，常见的整数类型包括：

1. INT（整数）：
   • 定义：用于存储标准整数，通常占用4个字节。
   • 范围：-2,147,483,648 到 2,147,483,647。
2. BIGINT（大整数）：
   • 定义：用于存储大范围的整数，通常占用8个字节。
   • 范围：-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807。
3. SMALLINT（小整数）：
   • 定义：用于存储较小范围的整数，通常占用2个字节。
   • 范围：-32,768 到 32,767。

1.2 小数类型

小数类型是一种数据类型，用于存储包含小数部分的数值。在数据库中，常见的小数类型包括：

1. DECIMAL（精确小数）：
   • 定义：用于存储精确的小数值，其长度由用户指定，包括小数点前后的位数。
   • 示例：DECIMAL(10, 2) 表示总共10位，其中包括2位小数。
2. NUMERIC（数值）：
   • 定义：类似于DECIMAL，用于存储精确的小数值。
   • 示例：NUMERIC(8, 4) 表示总共8位，其中包括4位小数。
3. FLOAT（浮点数）：
   • 定义：用于存储近似值的浮点数，可以指定总位数。
   • 示例：FLOAT(8) 表示总共8位浮点数。
4. DOUBLE（双精度浮点数）：
   • 定义：用于存储双精度的近似值。
   • 示例：DOUBLE 或 DOUBLE PRECISION。

小数类型适用于需要保留小数部分的数值，例如货币金额、百分比等。在选择小数类型时，需要考虑数值的精度和范围，以确保存储和计算的准确性。

1.3 字符类型

字符类型是一种用于存储文本数据的数据类型，常见的字符类型包括：

1. CHAR（定长字符）：
   • 定义：用于存储固定长度的字符串，不足长度的部分会使用空格填充。
   • 示例：CHAR(10) 表示存储长度为10的定长字符串。
2. VARCHAR（可变长字符）：
   • 定义：用于存储可变长度的字符串，根据实际长度占用存储空间。
   • 示例：VARCHAR(255) 表示可存储最大长度为255的可变长字符串。
3. TEXT（文本类型）：
   • 定义：用于存储大量文本数据，通常用于存储较长的字符串。
   • 示例：TEXT 或 LONGTEXT，取决于数据库系统的实现。
4. NCHAR 和 NVARCHAR：
   • 定义：用于存储 Unicode 字符集的字符，NCHAR 是定长的，NVARCHAR 是可变长的。
   • 示例：NCHAR(10) 或 NVARCHAR(255)。

字符类型适用于存储文本、字符串等信息，不同的字符类型在存储方式和使用场景上有一些区别。选择适当的字符类型需要考虑数据的长度、存储需求以及数据库系统的特性。

1.4 日期与时间类型

日期与时间类型是用于存储日期和时间信息的数据类型，常见的日期与时间类型包括：

1. DATE（日期）：
   • 定义：用于存储日期，不包含具体的时间。
   • 示例：DATE。
2. TIME（时间）：
   • 定义：用于存储时间，不包含具体的日期。
   • 示例：TIME。
3. DATETIME（日期和时间）：
   • 定义：用于存储日期和时间的组合。
   • 示例：DATETIME 或 TIMESTAMP。
4. TIMESTAMP（时间戳）：
   • 定义：类似于 DATETIME，用于存储日期和时间的组合，具体表示方式可能因数据库而异。
   • 示例：TIMESTAMP。
5. YEAR（年份）：
   • 定义：用于存储年份信息。
   • 示例：YEAR。

这些类型允许数据库存储和操作与日期和时间相关的信息。选择适当的类型取决于应用的需求，有时需要考虑时区、精度等因素。在处理日期和时间时，确保选择的类型能够满足业务逻辑和查询需求。

1.5 布尔类型

布尔类型是一种用于存储逻辑真值的数据类型，表示两个可能的取值：真（True）或假（False）。在不同的数据库系统中，布尔类型可能有不同的名称，例如在 MySQL 中是 BOOLEAN 或 BOOL，在 PostgreSQL 中是 BOOLEAN，在 SQLite 中是 INTEGER（0 表示假，1 表示真）等。 示例：

代码语言：javascript复制

-- 在创建表时使用 BOOLEAN 类型
CREATE TABLE example_table ( 
    is_active BOOLEAN,
    -- 其他列...
);

-- 插入数据
INSERT INTO example_table (is_active) VALUES (TRUE);

-- 查询数据
SELECT * FROM example_table WHERE is_active = TRUE;

布尔类型通常用于表示条件状态，例如是否激活、是否完成等。在查询中，可以使用布尔类型进行条件过滤，使得对逻辑判断更为直观和方便。

二、 约束

2.1 主键约束

主键约束（Primary Key Constraint）是一种用于标识表中唯一记录的约束。主键是表中一列或一组列，其值用于唯一标识每个记录。主键约束的作用是确保表中的每条记录都具有唯一的主键值，同时不允许主键列包含空值（NULL）。主键约束通常在创建表时定义，可以在一个或多个列上应用。以下是主键约束的基本语法：

代码语言：javascript复制

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    ...
);

在这里，column1是主键列，datatype是该列的数据类型。主键列的值必须是唯一的，而且不允许为空。 示例：

代码语言：javascript复制

CREATE TABLE students ( 
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT
);

在上述示例中，student_id 列被定义为主键，确保每个学生的学生ID是唯一的。主键的存在提高了数据的完整性，同时也为数据库系统提供了一种优化查询的方式，因为可以通过主键快速定位和访问特定的记录。 主键约束还可以在表已存在的情况下通过 ALTER TABLE 语句添加。例如：

代码语言：javascript复制

ALTER TABLE students
ADD PRIMARY KEY (student_id);

Tip：一个表只能有一个主键。如果需要使用多列作为唯一标识，可以考虑使用复合主键（Composite Primary Key）。

2.2 唯一约束

唯一约束（Unique Constraint）是一种用于确保表中某列或列组中的所有数据都是唯一的约束。唯一约束与主键约束类似，但不要求唯一标识每个记录，只要求确保表中特定列或列组中的值不重复。 唯一约束的基本语法如下：

代码语言：javascript复制

CREATE TABLE table_name ( 
    column1 datatype UNIQUE,
    column2 datatype,
    ...
);

在这里，column1是应用唯一约束的列，datatype是该列的数据类型。唯一约束允许 NULL 值，即允许在该列中存在多个 NULL 值，但对于非 NULL 值，每个值都必须是唯一的。 示例：

代码语言：javascript复制

CREATE TABLE products (
    product_id INT UNIQUE,
    product_name VARCHAR(50),
    price DECIMAL(10, 2)
);

在上述示例中，product_id 列被定义为唯一约束，确保每个产品的产品ID是唯一的。唯一约束可以应用于单列或多列。如果需要在表已存在的情况下添加唯一约束，可以使用 ALTER TABLE 语句。例如：

代码语言：javascript复制

ALTER TABLE products 
ADD UNIQUE (product_id);

Tip：一个表可以有多个唯一约束，但每个约束必须应用于不同的列或列组。唯一约束在确保数据完整性和查询优化方面发挥重要作用，尤其是在需要保证某列不包含重复值的情况下。

2.3 外键约束

外键约束（Foreign Key Constraint）是一种用于定义表之间关系的约束，确保一个表的外键与另一个表的主键匹配。外键约束创建了两个表之间的引用，这种引用通常表示了表与表之间的关联关系。 外键约束的基本语法如下：

代码语言：javascript复制

CREATE TABLE table_name1 ( 
    column1 datatype PRIMARY KEY,
    ...
);
CREATE TABLE table_name2 (
    column1 datatype,
    column2 datatype,
    FOREIGN KEY (column1) REFERENCES table_name1(column1)
);

在这里，table_name1 中的 column1 被定义为主键（PRIMARY KEY），而在 table_name2 中，column1 被定义为外键（FOREIGN KEY）。外键通过 REFERENCES 关键字引用了另一个表的主键。 示例：

代码语言：javascript复制

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

在上述示例中，departments 表的 department_id 列被定义为主键，而 employees 表的 department_id 列被定义为外键，引用了 departments 表中的主键。 外键约束有助于维护表之间的关系，确保在引用表中的外键列中的值存在于被引用表的主键列中。此外，外键约束还可以定义级联操作，例如，当主键表中的某行被删除时，与之相关的外键表中的相关行也可以被级联删除或设置为 NULL。 如果需要在已存在的表中添加外键约束，可以使用 ALTER TABLE 语句。例如：

代码语言：javascript复制

ALTER TABLE employees 
ADD FOREIGN KEY (department_id) REFERENCES departments(department_id);

Tip：外键约束的使用要谨慎，确保被引用的主键列与外键列的数据类型和值一致，以维护数据的完整性。

2.4 检查约束

检查约束（Check Constraint）是一种用于规定插入到表中的数据必须满足一定条件的约束。通过使用检查约束，可以定义表中数据的有效性规则，确保插入或更新的数据符合指定的条件。 检查约束的基本语法如下：

代码语言：javascript复制

CREATE TABLE table_name ( 
    column1 datatype,
    column2 datatype,
    CHECK (condition),
    ...
);

在这里，table_name 是要创建的表的名称，而 condition 是要应用的条件。条件可以是针对一个或多个列的表达式，如果条件为 true，则允许插入或更新数据；如果条件为 false，则拒绝插入或更新。 示例：

代码语言：javascript复制

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    CHECK (age >= 18)
);

在上述示例中，students 表的 age 列被定义为必须满足 age >= 18 的条件，即学生的年龄必须大于等于 18 岁。 检查约束可以应用于一个或多个列，也可以组合多个条件。例如，如果要确保学生的年龄在 18 到 25 岁之间，可以使用以下约束：

代码语言：javascript复制

CHECK (age >= 18 AND age <= 25)

如果需要在已存在的表上添加检查约束，可以使用 ALTER TABLE 语句。例如：

代码语言：javascript复制

ALTER TABLE students 
ADD CHECK (age >= 18);

检查约束在确保数据的合法性和一致性方面起着关键作用，可以避免不符合业务规则的数据被插入到表中。

2.5 默认约束

默认约束（Default Constraint）是一种用于为列指定默认值的约束。当插入新记录时，如果没有提供该列的值，则将使用默认值。默认约束可以应用于表的列，为其提供一个预定义的默认值，从而在插入数据时简化操作。 默认约束的基本语法如下：

代码语言：javascript复制

CREATE TABLE table_name (
    column1 datatype DEFAULT default_value,
    column2 datatype,
    ...
);

在这里，table_name 是要创建的表的名称，而 column1 datatype DEFAULT default_value 则表示 column1 列的默认值为 default_value。如果插入数据时没有为该列指定值，数据库系统将使用默认值。 示例：

代码语言：javascript复制

CREATE TABLE employees (   
    employee_id INT PRIMARY KEY, 
    first_name VARCHAR(50), 
    last_name VARCHAR(50),
    hire_date DATE DEFAULT CURRENT_DATE 
);

在上述示例中，employees 表的 hire_date 列被定义为默认值为当前日期（使用 CURRENT_DATE 函数）。 如果需要在已存在的表上添加默认约束，可以使用 ALTER TABLE 语句。例如：

代码语言：javascript复制

ALTER TABLE employees
ALTER COLUMN hire_date SET DEFAULT CURRENT_DATE;

这将为 employees 表的 hire_date 列添加默认约束，将默认值设置为当前日期。默认约束提供了一种方便的方式来确保在插入数据时对某些列进行初始化，同时避免了需要显式提供默认值的麻烦。

2.6 非空约束

非空约束（NOT NULL Constraint）是一种用于确保列中的数据不为空的约束。在定义表结构时，可以通过应用非空约束来防止在插入或更新记录时将空值（NULL）插入到特定列中。这有助于维护数据的完整性和一致性。 非空约束的基本语法如下：

代码语言：javascript复制

CREATE TABLE table_name ( 
    column1 datatype NOT NULL,
    column2 datatype,
    ...
);

在这里，table_name 是要创建的表的名称，而 column1 datatype NOT NULL 表示 column1 列是不允许包含空值的。 示例：

代码语言：javascript复制

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE
);

在上述示例中，employees 表的 first_name 和 last_name 列都被定义为非空列。这意味着在插入或更新记录时，必须为这两列提供非空的值。 如果需要在已存在的表上添加非空约束，可以使用 ALTER TABLE 语句。例如：

代码语言：javascript复制

ALTER TABLE employees   
ALTER COLUMN first_name SET NOT NULL;

这将为 employees 表的 first_name 列添加非空约束。 非空约束对于确保关键字段不缺失是非常有用的，同时也能够简化对数据库中的数据的处理，因为可以信任特定列中的数据不会是空的。

三、 数据类型和约束的综合应用

3.1 创建表时的数据类型和约束

在创建表时，通过指定每个列的数据类型和约束，可以定义表的结构和规则。以下是一个示例，演示如何在创建表时指定数据类型和约束：

代码语言：javascript复制

CREATE TABLE products (  
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10, 2) CHECK (price >= 0),
    stock_quantity INT DEFAULT 0,
    in_stock BOOLEAN,
    date_added DATE DEFAULT CURRENT_DATE
);

在这个示例中，创建了一个名为 products 的表，其中包含了不同类型的列，并应用了各种约束：

• product_id INT PRIMARY KEY: 定义了一个整数类型的主键列，用于唯一标识每个产品。
• product_name VARCHAR(100) NOT NULL: 定义了一个最大长度为 100 的字符串类型的列，且不允许为空，用于存储产品名称。
• category VARCHAR(50): 定义了一个最大长度为 50 的字符串类型的列，用于存储产品所属的类别。可以为空。
• price DECIMAL(10, 2) CHECK (price >= 0): 定义了一个十进制类型的列，用于存储产品价格，同时通过 CHECK 约束确保价格不为负数。
• stock_quantity INT DEFAULT 0: 定义了一个整数类型的列，表示产品库存数量，默认值为 0。
• in_stock BOOLEAN: 定义了一个布尔类型的列，表示产品是否有库存。可以为空。
• date_added DATE DEFAULT CURRENT_DATE: 定义了一个日期类型的列，表示产品添加的日期，默认为当前日期。

这个示例展示了如何结合使用不同的数据类型和约束来定义表的结构，确保数据的完整性和一致性。在实际应用中，根据具体需求和业务规则，可以灵活选择和组合适当的数据类型和约束。

3.2 修改表结构时的数据类型和约束

在修改表结构时，可以使用ALTER TABLE语句来更改列的数据类型和应用约束。以下是一个例子，演示如何修改表结构时进行数据类型和约束的调整： 假设有一个名为 employees 的表，初始结构如下：

代码语言：javascript复制

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE
);

现在，我们要对该表进行修改，添加一个新的列 salary，并调整一些列的数据类型和约束：

代码语言：javascript复制

-- 添加新列
ALTER TABLE employees  
ADD salary DECIMAL(10, 2) CHECK (salary >= 0);

-- 修改数据类型和约束
ALTER TABLE employees
ALTER COLUMN first_name VARCHAR(100),    -- 将first_name列的最大长度修改为100
ALTER COLUMN hire_date DATE NOT NULL;    -- 将hire_date列设置为不允许为空

在上述例子中，我们使用 ALTER TABLE 语句进行了两种类型的修改：

1. 添加新列 (ADD salary DECIMAL(10, 2) CHECK (salary >= 0))： 这一行将在 employees 表中添加一个名为 salary 的新列，其数据类型为十进制（DECIMAL），保留两位小数，同时通过 CHECK 约束确保 salary 不为负数。
2. 修改数据类型和约束 (ALTER COLUMN ...)： 这一行通过 ALTER COLUMN 子句修改了 first_name 列的最大长度为 100，并将 hire_date 列设置为不允许为空。

这个例子说明了如何使用 ALTER TABLE 语句在表已存在的情况下进行结构的调整，包括添加新列和修改现有列的数据类型以及约束。在实际应用中，修改表结构时需要谨慎操作，尤其是在生产环境中。

3.3 插入、更新、删除数据时的数据类型和约束

在插入、更新和删除数据时，需要确保操作的数据满足表定义的数据类型和约束，以维护数据的完整性和一致性。以下是插入、更新和删除数据时的数据类型和约束的示例：

1. 插入数据： 假设有一个名为 employees 的表，结构如下：

代码语言：javascript复制

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE,
    salary DECIMAL(10, 2) CHECK (salary >= 0)
);

• 插入数据满足约束：

代码语言：javascript复制

-- 插入满足约束的数据
INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
VALUES (1, 'John', 'Doe', '2023-01-01', 50000.50);

• 插入数据违反约束：

代码语言：javascript复制

-- 尝试插入违反约束的数据，将无法执行
INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)   
VALUES (2, 'Jane', 'Smith', '2023-01-01', -1000);

1. 更新数据：

假设现有一条员工记录如下：

代码语言：javascript复制

SELECT * FROM employees WHERE employee_id = 1;  

结果可能如下：

代码语言：javascript复制

employee_id | first_name | last_name | hire_date   | salary 
------------ ------------ ----------- ------------- ---------
1           | John       | Doe       | 2023-01-01  | 50000.50

现在，我们尝试更新数据：

代码语言：javascript复制

-- 更新数据满足约束
UPDATE employees 
SET salary = 55000.75
WHERE employee_id = 1;

如果更新操作违反了约束，将无法执行：

代码语言：javascript复制

-- 尝试更新数据违反约束，将无法执行
UPDATE employees
SET salary = -2000
WHERE employee_id = 1;

1. 删除数据：

假设现有一条员工记录如下：

代码语言：javascript复制

SELECT * FROM employees WHERE employee_id = 1;

结果可能如下：

代码语言：javascript复制

employee_id | first_name | last_name | hire_date   | salary  
------------ ------------ ----------- ------------- ---------
1           | John       | Doe       | 2023-01-01  | 55000.75

• 删除数据：

代码语言：javascript复制

-- 删除数据
DELETE FROM employees
WHERE employee_id = 1;

• 尝试删除不存在的数据：

代码语言：javascript复制

-- 尝试删除不存在的数据，将无法执行
DELETE FROM employees  
WHERE employee_id = 1;

这些例子强调了在进行插入、更新和删除操作时，需要确保操作的数据满足表定义的数据类型和约束，以保持数据库的一致性。

四、总结

数据类型和约束是SQL中关键的概念。数据类型定义了存储数据的格式，如整数、字符等。约束规定了数据的完整性，如主键、唯一性、外键等。它们共同确保数据库中的数据结构和内容得以有效管理。

sql 基础 数据库设计 数据类型 语法

0 人点赞