MySQL白菜教程（Level 5）

2021-08-17 本文已影响0人七喜丶

1、约束

在 SQL 中，约束是规定表中的数据规则。若存在违反约束的行为，行为就会阻止。它能帮助管理员更好地管理数据库，并且确保数据库中数据的正确性和有效性

非空约束 NOT NULL
NOT NULL 约束强制列不接受 NULL 值，强制字段始终包含值，这意味着，如果不向字段添加值，就无法插入新纪录或者更新记录

实例如下：

创建表的非空约束

CREATE TABLE Users (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

在一个已创建的表的 Age 字段中添加 NOT NULL 约束如下所示：

alter table User modify Age is not null;

在一个已创建的表的 Age 字段中删除 NOT NULL 约束如下所示：

alter table User modify Age is null;

注意
不要把 NULL 值与空串相混淆。NULL 值是没有值

它不是空串。如果指定' '（两个单引号，其间没有字符），这
在 NOT NULL 列中是允许的。空串是一个有效的值，它不是无
值。NULL 值用关键字 NULL 而不是空串指定

唯一约束 UNIQUE
UNIQUE 约束唯一标识数据库表中的每条记录
UNIQUE 和主键约束均为列或列集合提供了唯一性的保证
主键约束会自动定义一个 UNIQUE 约束，或者说主键约束是一种特殊的 UNIQUE 约束。但是二者有明显的区别：每个表可以有多个 UNIQUE 约束，但只能有一个主键约束

实例如下：

create table 的唯一约束

// MYSQL
create table Users (
    id int not null,
    lastName varchar(255) ,
    firstName varchar(255) not null,
    age int，
    unique(lastName)
);

// SQL Server or Oracle
create table Users (
    id int not null,
    lastName varchar(255)  unique,
    firstName varchar(255) not null,
    age int，
);

// 命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束:
// MYSQL or SQL Server or Oracle
create table Users (
    id int not null,
    lastName varchar(255) ,
    firstName varchar(255) not null,
    age int，
    constraint uni_code(lastName, age)
);

alter table 的唯一约束

// Mysql or SQL Server or Oracle
alter table Users add unique(id);

// 当表已被创建时，需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束:
alter table Users add constraint uni_code(lastName, age);

撤销的唯一约束

// Mysql 
alter table Users drop index uni_code;

// SQL Server or Oracle
alter table Users drop constraint uni_code;

主键约束 PRIMARY KEY
PRIMARY KEY 约束唯一标识数据库表中的每条记录，简单的说，PRIMARY KEY = UNIQUE + NOT NULL ,从技术的角度来看，PRIMARY KEY 和 UNIQUE 有很多相似之处。但还是有以下区别：

NOT NULL UNIQUE 可以将表的一列或多列定义为唯一性属性，而 PRIMARY KEY 设为多列时，仅能保证多列之和是唯一的，具体到某一列可能会重复

PRIMARY KEY 可以与外键配合，从而形成主从表的关系，而 NOT NULL UNIQUE 则做不到这一点

更大的区别在逻辑设计上。 PRIMARY KEY 一般在逻辑设计中用作记录标识，这也是设置 PRIMARY KEY 的本来用意，而 UNIQUE 只是为了保证域/域组的唯一性

create table时添加 PRIMARY KEY 约束

// Mysql
create table Users (
    id int not null,
    lastName varchar(255) ,
    firstName varchar(255) not null,
    age int，
    primary key (lastName)
);

// SQL Server or Oracle
create table Users (
    id int not null primary key,
    lastName varchar(255) ,
    firstName varchar(255) not null,
    age int，
);

// 如需命名并定义多个列的 PRIMARY KEY 约束，请使用下面的 SQL 语法：
create table Users (
    id int not null,
    lastName varchar(255) ,
    firstName varchar(255) not null,
    age int，
    constraint pri_code primary key (id, age);
)

ALTER TABLE 时添加主键约束

alter table Users add primary key(id);

// 如需命名并定义多个列的 PRIMARY KEY 约束，可以使用下面的 SQL 语法：
// 如果您使用 ALTER TABLE 语句添加主键，必须把主键列声明为不包含 NULL 值（在表首次创建时)
alter table Users add constraint pri_code primary key(id, age);

撤销 PRIMARY KEY

// Mysql
alter table Users drop primary key;
//通过DESC table_name我们可以发现，Key 列上的约束已经为空了

// SQL Server or Oracle
alter table Users drop constraint pri_code;

外键约束 FOREIGN KEY
什么是外键
一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY
例子：如果一个字段 X 在一张表（表 1 ）中是关键字，而在另一张表（表 2 ）中不是关键字，则称字段 X 为表 2 的外键

外键的作用
外键最根本的作用：保证数据的完整性和一致性
例子：现在有两张表——学生表和院系表，这里的院系就是学生表的外键，外键表是学生表，主键表是院系表。假如院系表中的某个院系被删除了，那么在学生表中要想查询这个被删除的院系号所对应的院信息就会报错，因为已经不存在这个系了，所以，删除院系表（主键表）时必须删除其他与之关联的表，这里就说明了外键的作用，保持数据的一致性、完整性。当然反过来讲，你删除学生表中的记录，并不影响院系表中的数据，你查询院系号也能正确查询。所以删除外键表中的数据并不影响主键表

CREATE TABLE 时的 SQL FOREIGN KEY 约束

// Mysql
CREATE TABLE `Orders`
(
`O_Id` int NOT NULL,
`OrderNo` int NOT NULL,
`P_Id` int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

// SQL Server or Oracle
CREATE TABLE `Orders`
(
`O_Id` int NOT NULL PRIMARY KEY,
`OrderNo` int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)
// REFERENCES 表示 引用一个表

// 如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束:
CREATE TABLE `Orders`
(
`O_Id` int NOT NULL,
`OrderNo` int NOT NULL,
`P_Id` int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)
// CONSTRAINT 表示约束，后面接约束名称，常用于创建约束和删除约束

ALTER TABLE 时的 SQL FOREIGN KEY 约束

// Mysql or SQL Server or Oracle
ALTER TABLE `Orders`
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

// 如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束:
ALTER TABLE `Orders`
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

撤销 FOREIGN KEY 约束

// Mysql
ALTER TABLE `Orders`
DROP FOREIGN KEY fk_PerOrders

// SQL Server or Oracle
 ALTER TABLE `Orders`
DROP CONSTRAINT fk_PerOrders

检查约束 CHECK
CHECK 约束用于限制列中的值的范围，评估插入或修改后的值。满足条件的值将会插入表中，否则将放弃插入操作。可以为同一列指定多个 CHECK约束

CHECK约束既可以用于某一列也可以用于某张表：
如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制。

定义 CHECK 约束条件在某种程度上类似于编写查询的 WHERE 子句，使用不同的比较运算符（例如 AND、OR、BETWEEN、IN、LIKE 和 IS NULL）编写其布尔表达式，该布尔表达式将返回 TRUE、FALSE 或 UNKNOWN 。当条件中存在 NULL 值时，CHECK约束将返回 UNKNOWN 值
CHECK 约束主要用于通过将插入的值限制为遵循定义的值、范围或格式规则的值来强制域完整性

创建表（CREATE TABLE）时添加 CHECK约束
在创建课程表 courses 时，给学生总数 student_count 字段加上一个大于 0 的约束

// Mysql
CREATE TABLE `courses`
(
`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CHECK (`student_count` > 0)
)

// SQL Server or Oracle
CREATE TABLE `courses`
(
`id` int
CHECK (`student_count` > 0),
`name` varchar(255),`student_count` int,
`created_at` date,
`teacher_id` int
)

为多个列添加 CHECK 约束

CREATE TABLE `courses`
(
`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CHECK (`student_count` > 0 AND `teacher_id` > 0)
)

为 CHECK 约束命名

CREATE TABLE `courses`
(
`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CONSTRAINT chk_courses CHECK (`student_count` > 0) ;

// 核心语法：
[CONSTRAINT <constraint name>] CHECK (<condition>)

表已存在时添加 CHECK 约束

ALTER TABLE `courses` 
ADD CHECK ( `student_count` > 0);

//or

ALTER TABLE `courses`  
ADD CONSTRAINT chk_courses CHECK ( `student_count` > 0 AND `teacher_id` > 0);

撤销 CHECK 约束

// Mysql
ALTER TABLE `courses` 
DROP CHECK chk_courses;

// SQL Server or Oracle
ALTER TABLE `courses` 
DROP CONSTRAINT chk_courses

DEFAULT 约束
用法：
1. DEFAULT 约束用于向列中插入默认值
2.如果没有规定其他的值，那么会将默认值添加到所有的新记录
3.例如女同学较多，性别就可以默认为“女”，如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为“女”

CREATE TABLE 时的 DEFAULT 约束

CREATE TABLE `Persons`
(
    `P_Id` int NOT NULL,
    `LastName` varchar(255) NOT NULL,
    `FirstName` varchar(255),
    `Address` varchar(255),
    `City` varchar(255) DEFAULT 'Sandnes'
)

ALTER TABLE 时的 DEFAULT 约束

// Mysql
ALTER TABLE `Persons`
ALTER `City` SET DEFAULT 'SANDNES';

// SQL Server or Oracle
ALTER TABLE `Persons`
ADD CONSTRAINT ab_c DEFAULT 'SANDNES' for `City`;

撤销 DEFAULT 约束

// Mysql
ALTER TABLE `Persons`
ALTER `City` DROP DEFAULT;

//SQL Server or Oracle
ALTER TABLE `Persons`
ALTER COLUMN `City` DROP DEFAULT;

MySQL白菜教程（Level 5）

1、约束

猜你喜欢

热点阅读