软件测试学习笔记丨SQL常见约束

本文转自测试人社区，原文链接： https://ceshiren.com/t/topic/31939

一、简介

• SQL约束是表中数据的限制条件，包括主键约束、唯一约束、外键约束、非空约束等。
• 这些约束可以确保表中数据的完整性、唯一性和有效性。

• 主键约束用于确保表中每行数据都有唯一标识符；
• 唯一约束用于保证数据唯一性，外键约束用于建立表与表之间的引用关系；
• 非空约束用于限制列中是否允许有空值；
• 检查约束用于限制列中输入的数据类型和范围。

二、常见约束：

• 主键约束：PRIMARY KEY
• 非空约束：NOT NULL
• 唯一约束：UNIQUE
• 默认约束：DEFAULT
• 外键约束：FOREIGN KEY

2.1 主键约束

• 主键：一列（或一组列），其值能够唯一标识表中每一行；
• 特点：不可重复，唯一，非空；
• 语法：列名 字段类型 PRIMARY KEY

2.1.1 添加主键约束

登录后复制

-- 选择 hog_demo 数据库
USE hog_demo;
-- 创建一个带主键的表
CREATE TABLE emp1(
    -- 设置主键 唯一 非空
    eid INT PRIMARY KEY,
    ename VARCHAR(20),
    gender CHAR(1)
);
-- 查看表的详细信息
DESC emp1;

-- 给存在的表添加主键
CREATE TABLE emp2(
    eid INT ,
    ename VARCHAR(20),
    gender CHAR(1)
);

-- 通过 DDL 语句进行设置
ALTER TABLE emp2 ADD PRIMARY KEY(eid);
-- 查看表的详细信息
DESC emp2;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.

2.1.2 创建主键自增的表

• 关键字：AUTO_INCREMENT 表示自动增长（字段类型必须是整数类型）

登录后复制

-- 创建主键自增的表
CREATE TABLE emp3(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    gender CHAR(1)
);

-- 查看表的详细数据
DESC emp3;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

2.1.3 修改主键自增的起始值

• 默认 AUTO_CREMENT 的起始值是1，如果希望修改起始值，请使用以下方式：

登录后复制

-- 创建主键自增的表，自定义自增起始值
CREATE TABLE emp4(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    gender CHAR(1)
)AUTO_INCREMENT=100;

-- 插入数据，观察主键的起始值
INSERT INTO emp4(ename,gender) VALUES('小李','男');
INSERT INTO emp4(ename,gender) VALUES('小赵','女'),

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.

• 注意：使用不同的删除数据的语法，对自增长的影响不同。

• DELETE：只是删除表中所有数据，对自增没有影响，还会按照原来的顺序继续往下排；
• TRUNCATE：是将整个表删除掉，然后重新创建一个新的表，所以主键自增会从1重新开始。

2.1.4 删除主键约束

• 删除表中的主键：ALTER TABLE 表名 DROP PRIMARY KEY;

登录后复制

-- 使用 DDL 语句删除表中的主键
ALTER TABLE emp2 DROP PRIMARY KEY;

-- 查看表的详细数据
DESC emp2;

• 1.
• 2.
• 3.
• 4.
• 5.

2.1.5 选择主键原则

什么类型的字段适合作为主键？

• 通常针对业务去设计主键，往往每张表都设计一个主键；
• 主键是给数据库和程序使用的，跟最终的客户无关，所以主键没有意义也没关系，主要能够保证不重复就可以。

2.2 非空约束

• 非空约束特点：某一列不允许为空；
• 语法：字段名 字段类型 NOT NULL

• 非空约束 NOT NULL强制这个列不接受NULL值。如果不向字段添加值，就无法插入新纪录或者更新记录。

示例：

登录后复制

-- 选择 hog_demo 数据库
USE hog_demo;

-- 添加非空约束
CREATE TABLE emp5(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    -- ename 字段限制不能为空
    ename VARCHAR(20) NOT NULL,
    gender CHAR(1)
);

-- 正常插入一条数据
INSERT INTO emp5(eid, ename,gender) VALUES(1,'小钱','女');

-- 插入一条ename为空的数据
INSERT INTO emp5(eid, ename,gender) VALUES(1,NULL,'男');

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.

2.3 唯一约束

• 唯一约束：在不是主键的特定列中，不会有重复的值；
• 对NULL不做唯一判断，也就是说，唯一约束的列中可以有NULL值。
• 语法：列名 字段类型 UNIQUE

示例：

登录后复制

-- 为 ename 字段添加唯一约束
CREATE TABLE emp6(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    -- ename 字段限制不能重复
    ename VARCHAR(20) UNIQUE,
    gender CHAR(1)
);

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.

2.3.1 主键约束与唯一约束的区别

特性	主键约束（Primary Key Constraint）	唯一约束（Unique Constraint）
唯一性	是	是
允许空值	否	是（在许多数据库中多个NULL值被视为不相同）
每个表的数量	一个	多个
自动创建索引	是	是
标识行	是	否
用途	唯一标识每一行记录	确保特定列的值在表中是唯一的
是否可以由多个列组成	可以（复合主键）	可以（复合唯一约束）

• 主键约束：唯一且不能为空；
• 唯一约束：唯一但可以为空；
• 一个表中只能有一个主键，但可以有多个唯一约束。

2.4 默认值

• 默认值约束：用来指定某列的默认值；
• 语法：字段名 字段类型 DEFAULT 默认值

2.4.1 字段指定默认值

登录后复制

-- 创建带有默认值的表
CREATE TABLE emp7(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    -- 为 gender 字段添加默认值
    gender CHAR(1) DEFAULT '女',
);

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.

原创作者: u_16547786 转载于: https://blog.51cto.com/u_16547786/11805542