MySQL基础语法入门-约束SQL主键约束 SQL主键约束是一种数据库约束类型，它用于唯一标识数据库表中的每一行记录。主

SQL主键约束

SQL主键约束是一种数据库约束类型，它用于唯一标识数据库表中的每一行记录。主键具有以下几个特性：(关键字 PRIMARY KEY)

唯一性：主键中的值必须是唯一的，不能重复。这样能够确保每一行记录都可以通过主键值被唯一识别。
非空性：主键列不能包含空值（NULL）。每一行都必须有一个有效的主键值。
不可变性：主键的值在记录存在期间不应更改，因为这可能会导致数据的一致性问题。
自动索引：大多数数据库会自动为主键创建索引，以提高数据的查询效率。

在创建表时，可以通过指定某一列或多列作为主键，例如：

单列主键

单列主键是由一列组成的主键。这意味着这个主键唯一地标识表中的每一行。每个记录在这列中必须有一个唯一的值。单列主键通常用于简单的情况，例如一个用户ID或订单号。

示例：

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

在这个例子中，student_id 列就是单列主键，它唯一标识每个学生。

多列主键

多列主键（也称为组合主键）是由两列或更多列组合而成的主键。它用于在单个表中没有单个列能够唯一标识每一行的情况。多列主键的组合必须是唯一的，并且组合中的每列都不能包含空值。

示例：

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

主键自动增长

MySQL，可以使用 AUTO_INCREMENT 关键字将某一列定义为自动增长列。

示例：

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

修改自动增长值

关键字 AUTO_INCREMENT

示例1 （表已经创建好的情况）

假设我们有以下 users 表：

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

现在，如果您想将下一个 user_id 的自增值设置为 200，可以执行：

ALTER TABLE users AUTO_INCREMENT = 200;

示例2 （表创建时候的情况）

假设我们有以下 users 表：

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
)AUTO_INCREMENT=1000;

delete和truncate删除数据有什么区别

-- 删除表中数据
DELETE FROM user_info;
-- 删除表格数据
TRUNCATE TABLE user_info;

在MySQL中，DELETE 和 TRUNCATE 都是用来删除表中的数据，但它们在实现方式和适用场景上有一些重要的区别：

事务日志记录和回滚：
- DELETE 语句逐行删除数据，因此会被记录到事务日志中，可以回滚（即撤销操作）。
- TRUNCATE 通常用于快速删除表中的所有数据，它不会记录每一行的删除操作，因此在事务中执行 TRUNCATE 后不能回滚。
性能：
- DELETE 语句在删除大量数据时通常比 TRUNCATE 慢，因为它每删除一行都会检查事务日志、触发器等。
- TRUNCATE 语句通常更快，因为它不记录每一行的删除操作，直接释放表的数据页。
重置自增ID：
- DELETE 删除表中的所有数据后，自增ID（AUTO_INCREMENT）不会重置，下次插入数据时自增ID会继续从删除前的最大值开始。
- TRUNCATE 则会重置表的自增ID。
适用性：
- DELETE 可以带条件删除，即只删除满足特定条件的记录。
- TRUNCATE 则直接删除整个表中的所有记录，不能带条件删除。
锁机制：
- DELETE 会锁定每一行的删除操作。
- TRUNCATE 会锁定整个表，通常更快，因为它不需要逐行检查。
触发器：
- 如果表有触发器，DELETE 会执行这些触发器。
- TRUNCATE 则不会触发任何触发器。

非空约束

在 MySQL 中，非空约束（NOT NULL）用于确保列中的每个值都不能为空（NULL）。这意味着当你插入或更新数据时，必须为带有非空约束的列提供一个值。如果尝试插入或更新一个带有非空约束的列而没有提供值，MySQL 将抛出一个错误。

在创建表时添加非空约束

CREATE TABLE 表名 (
    列名1 数据类型 NOT NULL,
    列名2 数据类型,
    ...
);

例如，创建一个 users 表，其中 name 和 email 列不能为空：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT
);

在现有表中添加非空约束

如果你已经创建了一个表，但后来决定为某个列添加非空约束，可以使用 ALTER TABLE 语句。

ALTER TABLE 表名
MODIFY 列名 数据类型 NOT NULL;

例如，为 users 表中的 age 列添加非空约束：

ALTER TABLE users
MODIFY age INT NOT NULL;

移除非空约束

如果你需要移除某个列的非空约束，同样可以使用 ALTER TABLE 语句。

ALTER TABLE 表名
MODIFY 列名 数据类型 NULL;

例如，移除 users 表中 age 列的非空约束：

ALTER TABLE users
MODIFY age INT NULL;

默认值与非空约束

通常，当你为列添加非空约束时，也会为其指定一个默认值。如果插入或更新的语句没有提供该列的值，MySQL 将使用默认值。

CREATE TABLE 表名 (
    列名1 数据类型 NOT NULL DEFAULT 默认值,
    ...
);

例如，创建一个 users 表，其中 age 列不能为空，默认值为 18：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT NOT NULL DEFAULT 18
);

示例

创建表时添加非空约束

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    hire_date DATE NOT NULL DEFAULT '1970-01-01'
);

插入数据

尝试插入缺少 first_name、last_name 或 email 的数据将导致错误：

INSERT INTO employees (last_name, email) VALUES ('Doe', 'john.doe@example.com');
-- 错误：Column 'first_name' cannot be null

正确的插入语句应包含所有非空列的值：

INSERT INTO employees (first_name, last_name, email) VALUES ('John', 'Doe', 'john.doe@example.com');

修改现有表

假设你已经有一个 employees 表，但后来决定为 hire_date 列添加非空约束：

ALTER TABLE employees
MODIFY hire_date DATE NOT NULL DEFAULT '1970-01-01';

唯一约束（UNIQUE）

在 MySQL 中，唯一约束（UNIQUE）用于确保列中的所有值都是唯一的，即不能有重复的值。每个表可以有多个唯一约束，但唯一约束的列组合必须是唯一的。唯一约束可以帮助维护数据的完整性，确保特定列或列组合中的值不会重复。

在创建表时添加唯一约束

可以在单个列或多个列上添加唯一约束。

单个列上的唯一约束：

CREATE TABLE 表名 (
    列名1 数据类型 UNIQUE,
    ...
);

例如，创建一个 users 表，其中 email 列必须是唯一的：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    age INT
);

多个列上的唯一约束：

CREATE TABLE 表名 (
    列名1 数据类型,
    列名2 数据类型,
    ...
    UNIQUE (列名1, 列名2)
);

例如，创建一个 users 表，其中 first_name 和 last_name 的组合必须是唯一的：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    age INT,
    UNIQUE (first_name, last_name)
);

在现有表中添加唯一约束

你可以使用 ALTER TABLE 语句为现有表的列添加唯一约束。

单个列上的唯一约束：

ALTER TABLE 表名
ADD UNIQUE (列名1);

例如，为 users 表中的 email 列添加唯一约束：

ALTER TABLE users
ADD UNIQUE (email);

多个列上的唯一约束：

ALTER TABLE 表名
ADD UNIQUE (列名1, 列名2);

例如，为 users 表中的 first_name 和 last_name 列组合添加唯一约束：

ALTER TABLE users
ADD UNIQUE (first_name, last_name);

移除唯一约束

如果你需要移除某个列的唯一约束，同样可以使用 ALTER TABLE 语句。

ALTER TABLE 表名
DROP INDEX 约束名;

例如，移除 users 表中 email 列的唯一约束（假设约束名为 email）：

ALTER TABLE users
DROP INDEX email;

如果你不确定约束的名称，可以通过以下查询查看：

SHOW INDEX FROM users;

唯一约束和主键约束的对比和作用

唯一约束（UNIQUE）和主键约束（PRIMARY KEY）都是用于确保数据完整性和唯一性的约束，但它们在实现和作用上有一些重要的区别。

主键约束（PRIMARY KEY）

定义：
- 主键约束用于唯一标识表中的每一行记录。
- 一个表只能有一个主键。
- 主键可以由一个列或多个列组成（复合主键）。
作用：
- 确保每一行记录在表中是唯一的。
- 提高数据检索的效率，因为主键通常会自动创建索引。

唯一约束（UNIQUE）

定义：
- 唯一约束确保列中的所有值都是唯一的。
- 一个表可以有多个唯一约束。
- 唯一约束的列组合也可以是唯一的。
作用：
- 确保某一列或列组合中的所有值不重复。
- 提高数据检索的效率，因为唯一约束也会自动创建索引。

特性	主键约束 (PRIMARY KEY)	唯一约束 (UNIQUE)
唯一性	确保列（或列组合）唯一	确保列（或列组合）唯一
唯一性数量	一个表只能有一个主键	一个表可以有多个唯一约束
空值允许	不允许 NULL 值	可以允许 NULL 值（但唯一值必须唯一）
索引	自动创建唯一索引	自动创建唯一索引
自动填充	常见用 `AUTO_INCREMENT`	通常不自动填充

小结

主键约束 是用于唯一标识表中每一行记录的约束，每个表只能有一个主键，并且不允许 NULL 值。
唯一约束 是用于确保列（或列组合）中的所有值都是唯一的约束，一个表可以有多个唯一约束，并且允许 NULL 值（但唯一值本身必须唯一）。
两者都会自动创建唯一索引，以提高数据检索的效率。
主键约束通常用于单个列，而唯一约束可以用于一个或多个列。

默认约束（DEFAULT）

默认约束（DEFAULT）用于在插入新记录时，如果没有为某个列提供值，MySQL 会自动为该列使用一个默认值。这有助于确保数据的一致性和完整性，避免插入 NULL 值或不明确的值。

在创建表时添加默认约束

CREATE TABLE 表名 (
    列名1 数据类型 DEFAULT 默认值,
    ...
);

例如，创建一个 users 表，其中 age 列的默认值为 18：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    age INT NOT NULL DEFAULT 18
);

在现有表中添加默认约束

你可以使用 ALTER TABLE 语句为现有表的列添加默认约束。

ALTER TABLE 表名
ALTER COLUMN 列名 SET DEFAULT 默认值;

例如，为 users 表中的 age 列添加默认值 18：

ALTER TABLE users
ALTER COLUMN age SET DEFAULT 18;

注意：在某些版本的 MySQL 中，ALTER COLUMN 语法可能不被支持。你可以使用 MODIFY COLUMN 语法来实现相同的效果。

ALTER TABLE 表名
MODIFY COLUMN 列名 数据类型 DEFAULT 默认值;

例如：

ALTER TABLE users
MODIFY COLUMN age INT NOT NULL DEFAULT 18;

零填充约束

在 MySQL 中，并没有直接称为“零填充约束”（Zero-Fill Constraint）的标准约束。不过，MySQL 提供了 ZEROFILL 属性，可以用于数值类型的列，以在显示时自动填充前导零。这个属性通常与 UNSIGNED 属性一起使用，并且不会影响列的唯一性和非空性。

零填充属性（ZEROFILL）

定义

ZEROFILL 属性用于在显示数值时自动填充前导零，以确保数值显示的宽度一致。
通常用于 INT 或其他数值类型的列。

作用

当数值类型的数据被插入或查询时，MySQL 会在数值前面填充足够的零以达到指定的宽度。
这个属性主要用于格式化输出，而不影响数据的存储或验证。

基本语法

在创建表时添加零填充属性：

CREATE TABLE 表名 (
    列名1 数据类型(ZEROFILL),
    ...
);

例如，创建一个 products 表，其中 product_id 列使用零填充属性：

CREATE TABLE products (
    product_id INT(5) ZEROFILL,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

在这个例子中，product_id 列将自动填充前导零以确保显示的宽度为 5 位。

插入数据：

INSERT INTO products (product_id, product_name, price)
VALUES (123, 'Widget', 19.99);

效果如图

外键约束 (FOREIGN KEY)

外键约束（FOREIGN KEY）是 SQL 中用于维护表之间引用完整性的约束。它确保一个表中的列的值必须出现在另一个表的特定列中，从而保持数据的一致性和关联性。

外键约束的基本概念

父表（Parent Table） ：包含被引用键值的表。
子表（Child Table） ：包含外键的表，外键指向父表的主键或唯一键。
外键（Foreign Key） ：子表中的列，其值必须在父表的主键或唯一键中存在。
引用完整性（Referential Integrity） ：确保子表中的外键值与父表中的主键值匹配。

外键约束的特点

唯一性：外键可以指向父表的主键或唯一键，但必须是唯一的。
级联操作：可以指定级联操作来处理外键列的更新或删除操作，例如 CASCADE、SET NULL、SET DEFAULT、NO ACTION 和 RESTRICT。

外键约束的基本语法

在创建表时添加外键约束

CREATE TABLE 子表名 (
    列名1 数据类型,
    列名2 数据类型,
    ...
    FOREIGN KEY (列名1) REFERENCES 父表名(父列名),
    FOREIGN KEY (列名2, 列名3) REFERENCES 父表名(父列名1, 父列名2)
);

在现有表中添加外键约束

ALTER TABLE 子表名
ADD CONSTRAINT 约束名
FOREIGN KEY (列名1) REFERENCES 父表名(父列名),
FOREIGN KEY (列名2, 列名3) REFERENCES 父表名(父列名1, 父列名2);

示例：

假设我们已经创建了 customers 和 orders 表，但后来决定在 orders 表中添加外键约束。

ALTER TABLE orders
ADD CONSTRAINT fk_customer_id
FOREIGN KEY (customer_id) REFERENCES customers(id);

示例：

假设我们有两个表 customers 和 orders，其中 customers 表包含客户信息，orders 表包含订单信息。我们希望 orders 表中的 customer_id 列引用 customers 表中的 id 列。

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL DEFAULT '1970-01-01',
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

级联操作

外键约束可以指定级联操作来处理更新或删除操作。以下是一些常见的级联操作：

CASCADE：
- 当父表中的记录被删除或更新时，子表中的相关记录也会被删除或更新。
SET NULL：
- 当父表中的记录被删除或更新时，子表中的外键列值将被设置为 NULL。
- 要使用 SET NULL，子表的外键列必须允许 NULL 值。
SET DEFAULT：
- 当父表中的记录被删除或更新时，子表中的外键列值将被设置为默认值。
- 要使用 SET DEFAULT，子表的外键列必须有默认值。
NO ACTION：
- 当父表中的记录被删除或更新时，子表中的相关记录不会受到影响。
- 这通常用于在某些数据库系统中阻止级联操作。
RESTRICT：
- 当父表中的记录被删除或更新时，如果子表中有相关记录，操作将被阻止。
- 这通常用于在某些数据库系统中阻止级联操作。

级联操作的语法

ALTER TABLE 子表名
ADD CONSTRAINT 约束名
FOREIGN KEY (列名1) REFERENCES 父表名(父列名)
ON DELETE 级联操作
ON UPDATE 级联操作;

示例：

假设我们希望在删除 customers 表中的记录时，自动删除 orders 表中相关的记录。

ALTER TABLE orders
ADD CONSTRAINT fk_customer_id
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

注意事项

存储引擎：
- 外键约束在 InnoDB 存储引擎中得到支持，而在 MyISAM 存储引擎中不支持。
数据类型匹配：
- 外键列和被引用列的数据类型必须匹配。
索引：
- MySQL 要求外键列和被引用列上必须有索引。通常，主键和唯一键列已经具有索引。

示例

创建表时添加外键约束

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL DEFAULT '1970-01-01',
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

插入数据

INSERT INTO customers (name, email) VALUES ('John Doe', 'john.doe@example.com');
INSERT INTO customers (name, email) VALUES ('Jane Doe', 'jane.doe@example.com');

INSERT INTO orders (customer_id, order_date) VALUES (1, '2025-01-14');
INSERT INTO orders (customer_id, order_date) VALUES (2, '2025-01-14');

总结

约束类型	定义	作用	特点
主键约束 (PRIMARY KEY)	唯一标识表中的每一行记录	确保列中的值唯一且非空	每个表只能有一个主键，可以是单个列或多列组合
唯一约束 (UNIQUE)	确保列中的值唯一	防止列中出现重复值	每个表可以有多个唯一约束，列可以允许 NULL 值（但唯一值必须唯一）
非空约束 (NOT NULL)	确保列中的值不能为空	强制列必须包含数据	每个表可以对多个列应用非空约束
默认约束 (DEFAULT)	在插入新记录时使用默认值	提供一个默认值，确保列中总是有数据	每个表可以对多个列应用默认约束，支持具体的值或表达式
外键约束 (FOREIGN KEY)	确保一个表中的列的值必须出现在另一个表的特定列中	维护表之间的引用完整性	可以指定级联操作，如 `CASCADE`、`SET NULL` 等
检查约束 (CHECK)	确保列中的值满足特定的条件	限制列中可以接受的值	MySQL 对检查约束的支持有限，某些版本可能不支持
零填充属性 (ZEROFILL)	在显示数值时自动填充前导零	格式化数值的显示	通常与 `UNSIGNED` 属性一起使用，不影响数据存储和验证
自动递增属性 (AUTO_INCREMENT)	自动为列生成唯一的递增整数值	确保列中的每个值都是唯一的，并且自动递增	通常用于主键列，每个表只能有一个列具有 `AUTO_INCREMENT` 属性