一篇文章带你彻底搞懂MySQL中表的约束，让你玩转MySQL

于 2024-10-28 11:20:30 发布
阅读量1.1k 收藏 10
点赞数 31
文章标签： 服务器 数据库 mysql sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_51799303/article/details/143289635
版权

1. 空属性

1.1 概念

空属性（或称为可空列）指的是可以存储 NULL 值的数据库表中的列

1.2 定义空属性

CREATE TABLE example (
    id INT PRIMARY KEY,
    name VARCHAR(100), -- 默认可以为空
    age INT NOT NULL   -- 不允许为空
);

1.3 查看表的结构

mysql> describe example;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
| age   | int(11)      | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2. 非空属性

2.1 概念

非空属性（或称为 NOT NULL 列）是指不允许存储 NULL 值的数据库表中的列

2.2 定义非空属性

CREATE TABLE example (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL, -- 非空属性
    age INT NOT NULL             -- 非空属性
);

2.3 查看表的结构

mysql> show columns from example;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | int(11)      | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3. 默认值

3.1 概念

默认值是指在插入新记录时，如果未提供某列的值，则该列将自动填充为指定的默认值。

3.2 定义默认值

CREATE TABLE example (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT DEFAULT 18,           -- 默认年龄为18
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 默认时间戳为当前时间
);

3.3 查看表的结构

在创建表时，可以通过 DEFAULT 关键字为某一列设置默认值。

mysql> show columns from example;
+------------+--------------+------+-----+-------------------+-------+
| Field      | Type         | Null | Key | Default           | Extra |
+------------+--------------+------+-----+-------------------+-------+
| id         | int(11)      | NO   | PRI | NULL              |       |
| name       | varchar(100) | NO   |     | NULL              |       |
| age        | int(11)      | YES  |     | 18                |       |
| created_at | timestamp    | NO   |     | CURRENT_TIMESTAMP |       |
+------------+--------------+------+-----+-------------------+-------+

如果一个列被定义为 NOT NULL 且没有设置默认值，在插入记录时必须提供该列的有效数据，否则会引发错误。

4. 列描述

4.1 概念

列描述（comment）是一种元数据，用于为数据库表中的列提供额外的说明或注释

mysql> CREATE TABLE example_table (
    ->   id INT AUTO_INCREMENT PRIMARY KEY,
    ->   name VARCHAR(100) NOT NULL COMMENT '用户名称',
    ->   email VARCHAR(100) NOT NULL COMMENT '用户邮箱'
    -> );
Query OK, 0 rows affected (0.02 sec)

4.2 查看列描述

mysql> show create table example_table\G;
*************************** 1. row ***************************
       Table: example_table
Create Table: CREATE TABLE `example_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT '用户名称',
  `email` varchar(100) NOT NULL COMMENT '用户邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

5. ZEROFILL

5.1 概念

ZEROFILL 是一种用于数值类型列的属性。当你在定义一个整数列时，如果使用 ZEROFILL，则当插入的值不足以填满指定的宽度时，MySQL 会用零填充这个值。

CREATE TABLE example (
    id INT(5) ZEROFILL PRIMARY KEY,
    score INT(3) ZEROFILL
);

5.2 查看表结构

mysql> show columns from example;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type                     | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| id    | int(5) unsigned zerofill | NO   | PRI | NULL    |       |
| score | int(3) unsigned zerofill | YES  |     | NULL    |       |
+-------+--------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

5.3 插入数据

mysql> INSERT INTO example (id, score) VALUES (1, 25);
Query OK, 1 row affected (0.00 sec)

5.4 查询

mysql> SELECT * FROM example;
+-------+-------+
| id    | score |
+-------+-------+
| 00001 |   025 |
+-------+-------+
1 row in set (0.00 sec)

5.5 使用方式

要使用 ZEROFILL，你需要在创建或修改表时为整数字段指定它。示例如下：

sqlCopy codeCREATE TABLE example (
    id INT(5) ZEROFILL PRIMARY KEY,
    score INT(3) ZEROFILL
);

在这个例子中：

  • id 列是一个宽度为 5 的整型字段。
  • score 列是一个宽度为 3 的整型字段。

5.6 插入数据

当插入数据时，如果提供的数值长度小于定义的宽度，MySQL 将用零填充。例如：

sqlCopy code
INSERT INTO example (id, score) VALUES (1, 25);

查询后，你会看到：

sqlCopy code
SELECT * FROM example;

输出将会是：

Copy code+------+-------+
| id   | score |
+------+-------+
| 00001| 025   |
+------+-------+

注意事项

  1. ZEROFILL 只影响显示，不改变实际存储的数据。存储仍然是原始数值，只是在输出时进行了格式化。
  2. 如果列定义了 UNSIGNED 属性（即不允许负数），并且你尝试插入负值，将导致错误。
  3. 指定的宽度仅影响输出格式，而不是存储空间。
  4. 当你在定义一个整数列时，如果使用 ZEROFILL，则当插入的值不足以填满指定的宽度时，MySQL 会用零填充这个值。

6. 主键

6.1 概念

主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键；主键所在的列通常是整数类型

6.2 定义主键

mysql> CREATE TABLE students (
    ->     id INT NOT NULL,
    ->     name VARCHAR(100),
    ->     age INT,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.01 sec)
#id 列被定义为主键

6.3 查看列描述

mysql> show columns from students;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

6.4 复合主键

CREATE TABLE enrollment (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)
);
#student_id 和 course_id 共同构成了复合主键

6.5 查看列描述

mysql> show columns from enrollment;
+-----------------+---------+------+-----+---------+-------+
| Field           | Type    | Null | Key | Default | Extra |
+-----------------+---------+------+-----+---------+-------+
| student_id      | int(11) | NO   | PRI | NULL    |       |
| course_id       | int(11) | NO   | PRI | NULL    |       |
| enrollment_date | date    | YES  |     | NULL    |       |
+-----------------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

6.6 追加主键

alter table 表名 add primary key(字段列表)

6.7 删除主键

alter table 表名 drop primary key;

7. 自增长

7.1 概念

auto_increment：当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键。

7.2 特性

  1. 唯一性：自增长列的每个值都是唯一的，这对于标识每条记录非常重要。
  2. 递增：默认情况下，自增长列会从 1 开始，并且每次插入新行时都会增加 1。不过，你可以通过设置不同的起始值和步长来调整这一行为。
  3. 非空：自增长列不能为 NULL，因为它必须有一个有效的数字作为唯一标识符。

7.3 定义自增长

mysql> CREATE TABLE students (
    ->     id INT AUTO_INCREMENT,
    ->     name VARCHAR(100),
    ->     age INT,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.01 sec)

7.4 插入数据

INSERT INTO students (name, age) VALUES ('Alice', 20);
INSERT INTO students (name, age) VALUES ('Bob', 22);

7.5 查看表中信息

mysql> select * from students;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | Alice |   20 |
|  2 | Bob   |   22 |
+----+-------+------+
2 rows in set (0.00 sec)

7.6 重置自增长的值

ALTER TABLE students AUTO_INCREMENT = 100;
#把students表的自增长起始值重置为1

注意事项

  • 自增长属性只能用于整数类型的列（例如INT、SMALLINT、MEDIUMINT、BIGINT）。
  • 一个表中只能有一个自增长列。
  • 自增长列通常用作主键，以确保唯一性和顺序性。
  • 如果删除了某些行，相应的 ID 值不会被回收或重新利用，因此可能会出现间隔。这是正常行为，确保每个 ID 的唯一性。
  • 表中有大量的并发插入操作，可能会导致自增长值的争用，从而影响性能。

8. 唯一键

8.1 概念

unique，唯一性是一种约束，用于确保表中的某一列或某几列的值是唯一的，即不允许有重复的记录。这个特性对于维护数据完整性和避免数据冗余非常重要。

8.2 特性

  1. 唯一约束：通过使用 UNIQUE 关键字，可以为一个或多个列设置唯一约束。这意味着在这些列中不能出现重复值。
  2. 可以包含 NULL 值：与主键不同，唯一约束允许 NULL 值，但每个 NULL 值都是被视为不同的。因此，一个字段可以有多个 NULL 值，但不能有相同的非 NULL 值。
  3. 单列或多列：唯一约束可以应用于单个列，也可以应用于多个列组合，这称为复合唯一键。

8.3 定义唯一约束

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE,
    username VARCHAR(100),
    PRIMARY KEY (id)
);
#email 列被定义为具有唯一约束

8.4 定义多列复合唯一约束

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE,
    username VARCHAR(100),
    PRIMARY KEY (id)
);
#product_id 和 user_id 的组合必须是唯一的

8.5 插入数据

INSERT INTO users (email, username) VALUES ('alice@example.com', 'alice');

8.6 查看表中信息

mysql> select * from users;
+----+-------------------+----------+
| id | email             | username |
+----+-------------------+----------+
|  1 | alice@example.com | alice    |
+----+-------------------+----------+
1 row in set (0.00 sec)

8.7 插入重复值时

mysql> INSERT INTO users (email, username) VALUES ('alice@example.com', 'bob'); 
ERROR 1062 (23000): Duplicate entry 'alice@example.com' for key 'email'

8.8 添加/删除唯一约束

ALTER TABLE users ADD UNIQUE (username);

8.9 删除唯一约束

ALTER TABLE users DROP INDEX username; -- 假设索引名为username

8.10 主键和唯一性的关系

每个表只能有一个主键，而可以有多个唯一区域。主键隐含了惟一性，并且不允许 NULL 值；而唯一区域则允许 NULL 值（但每个非 NULL 值必须是独特的）。

9. 外键

9.1 概念

外键（Foreign Key）是一种约束，用于建立和维护两个表之间的关系。外键确保一个表中的某一列（或多列）的值必须对应于另一个表中主键或唯一键的值，从而实现数据的完整性和一致性。

9.2 定义外键

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

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT,
    employee_name VARCHAR(100),
    department_id INT,
    PRIMARY KEY (employee_id),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

9.3 插入数据

INSERT INTO departments (department_name) VALUES ('HR');
INSERT INTO departments (department_name) VALUES ('IT');

-- 插入有效的员工记录
INSERT INTO employees (employee_name, department_id) VALUES ('Alice', 1); -- 假设 'HR' 的 ID 是 1
INSERT INTO employees (employee_name, department_id) VALUES ('Bob', 2);   -- 假设 'IT' 的 ID 是 2

-- 尝试插入无效的员工记录，会引发错误
INSERT INTO employees (employee_name, department_id) VALUES ('Charlie', 3); -- 错误，因为没有部门ID为3

9.4 添加修改外键信息

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

9.5 删除外键信息

ALTER TABLE employees DROP FOREIGN KEY fk_department;
追梦阿杰
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值