SQL 的约束

悠悠 2022-06-06 04:44 226阅读 0赞

###  ###

## 转自:https://www.shiyanlou.com/courses/running ##

## 一、实验介绍 ##

### 1.1 实验内容 ###

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。本节实验将在实践操作中熟悉 MySQL 中的几种约束。

### 1.2 实验知识点 ###

*  SQL 约束

### 1.3 实验环境 ###

课程使用的实验环境为 Ubuntu Linux 14.04 64 位版本。实验中会用到程序：

*  Mysql 5.5.50
 *  Xfce终端

### 2.1 约束分类 ###

听名字就知道，约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。

在MySQL中，通常有这几种约束：

<table style="border-collapse:collapse; border-spacing:0px; background-color:rgb(249,249,249); margin-top:0px; margin-bottom:16px; display:block; width:586px; overflow:auto; word-break:keep-all; color:rgb(51,51,51); font-size:16px"> 
 <thead style=""> 
  <tr style="background-color:rgb(255,255,255); border-top:1px solid rgb(204,204,204)"> 
   <th style="padding:6px 13px; text-align:left; border:1px solid rgb(221,221,221)"> 约束类型：</th> 
   <th style="padding:6px 13px; text-align:left; border:1px solid rgb(221,221,221)"> 主键</th> 
   <th style="padding:6px 13px; text-align:left; border:1px solid rgb(221,221,221)"> 默认值</th> 
   <th style="padding:6px 13px; text-align:left; border:1px solid rgb(221,221,221)"> 唯一</th> 
   <th style="padding:6px 13px; text-align:left; border:1px solid rgb(221,221,221)"> 外键</th> 
   <th style="padding:6px 13px; text-align:left; border:1px solid rgb(221,221,221)"> 非空</th> 
  </tr> 
 </thead> 
 <tbody style=""> 
  <tr style="background-color:rgb(255,255,255); border-top:1px solid rgb(204,204,204)"> 
   <td style="padding:6px 13px; border:1px solid rgb(221,221,221)">关键字：</td> 
   <td style="padding:6px 13px; border:1px solid rgb(221,221,221)">PRIMARY KEY</td> 
   <td style="padding:6px 13px; border:1px solid rgb(221,221,221)">DEFAULT</td> 
   <td style="padding:6px 13px; border:1px solid rgb(221,221,221)">UNIQUE</td> 
   <td style="padding:6px 13px; border:1px solid rgb(221,221,221)">FOREIGN KEY</td> 
   <td style="padding:6px 13px; border:1px solid rgb(221,221,221)">NOT NULL</td> 
  </tr> 
 </tbody> 
</table>

### 2.2 建立含约束的表 ###

当然不用你一行一行地输入命令搭建这个数据库，实验楼已经为你准备好了。首先打开 Xfce 终端，输入命令进入 `/home/shiyanlou/Desktop` 目录：

cd Desktop

使用 git 命令将需要的数据文件下载到本地文件夹：

git clone https://github.com/shiyanlou/SQL3

下载完成后，输入命令开启 MySQL 服务并使用 root 用户登录：

#打开 MySQL 服务
    sudo service mysql start        
    
    #使用 root 用户登录
    mysql -u root

刚才下载的 SQL3 目录下，有个文件 `MySQL-03-01.sql`，其中包含的代码可以新建一个数据库 `mysql_shiyan`,然后在其中创建三张表 `department`，`employee`，`project`，它们包含了各种约束。

（SQL3 目录在桌面上，你可以用Gedit查看里面的 `MySQL-03-01.sql` 文件。）

加载文件中的数据，需要在 MySQL 控制台中输入命令：

source /home/shiyanlou/Desktop/SQL3/MySQL-03-01.sql;

查看一下这个数据库，输入命令 `show tables;`，可见：

![00][]

### 2.3 主键 ###

主键 (PRIMARY KEY)是用于约束表中的一行，作为这一行的唯一标识符，在一张表中通过主键就能准确定位到一行，因此主键十分重要。主键不能有重复且不能为空。

在 `MySQL-03-01.sql` 中，这里有主键：

![07][]

也可以这样定义主键：

![08-][]

还有一种特殊的主键——复合主键。主键不仅可以是表中的一列，也可以由表中的两列或多列来共同标识，比如：

![09-][]

### 2.4 默认值约束 ###

默认值约束 (DEFAULT) 规定，当有 DEFAULT 约束的列，插入数据为空时，将使用默认值。

在 `MySQL-03-01.sql` 中，这段代码包含了 DEFAULT 约束：

![10][]

DEFAULT 约束只会在使用 INSERT 语句（上一实验介绍过）时体现出来，INSERT语句中，如果被 DEFAULT 约束的位置没有值，那么这个位置将会被 DEFAULT 的值填充，如语句：

# 正常插入数据
    INSERT INTO department(dpt_name,people_num) VALUES('dpt1',11);
    
    #插入新的数据，people_num 为空，使用默认值
    INSERT INTO department(dpt_name) VALUES('dpt2');

输入命令 `SELECT * FROM department;`，可见表中第二行的people\_num 被 DEFAULT 的值 (10) 填充：

![01][]

### 2.5 唯一约束 ###

唯一约束 (UNIQUE) 比较简单，它规定一张表中指定的一列的值必须不能有重复值，即这一列每个值都是唯一的。

在 `MySQL-03-01.sql` 中，也有 UNIQUE 约束：

![11][]

当 INSERT 语句新插入的数据和已有数据重复的时候，如果有 UNIQUE约束，则 INSERT 失败，比如：

INSERT INTO employee VALUES(01,'Tom',25,3000,110110,'dpt1');
    INSERT INTO employee VALUES(02,'Jack',30,3500,110110,'dpt2');

结果如图：

![02][]

### 2.6 外键约束 ###

外键 (FOREIGN KEY) 既能确保数据完整性，也能表现表之间的关系。

一个表可以有多个外键，每个外键必须 REFERENCES (参考) 另一个表的主键，被外键约束的列，取值必须在它参考的列中有对应值。

![12-][]

在 INSERT 时，如果被外键约束的值没有在参考列中有对应，比如以下命令，参考列 (department 表的 dpt\_name) 中没有dpt3，则INSERT 失败：

INSERT INTO employee VALUES(02,'Jack',30,3500,114114,'dpt3');

可见之后将 dpt3 改为 dpt2（department 表中有 dpt2），则插入成功：

![03][]

### 2.7 非空约束 ###

非空约束 (NOT NULL),听名字就能理解，被非空约束的列，在插入值时必须非空。

![13][]

在MySQL中违反非空约束，不会报错，只会有警告，比如以下语句：

#INSERT 成功 age 为空，因为没有非空约束，表中显示 NULL
    INSERT INTO employee(id,name,salary,phone,in_dpt) VALUES(03,'Jim',3400,119119,'dpt2'); 
    
    #警告 salary 被非空约束，值为空，表中显示0
    INSERT INTO employee(id,name,age,phone,in_dpt) VALUES(04,'Bob',23,123456,'dpt1');

结果如图，出现警告，但还是插入数据成功，实验楼的环境是 `5.5.50`，`5.6` 版本以上的 MySQL 会报错，禁止插入不符合非空约束的数据：

![04][]

此时 employee 表的内容为：

![05][]

## 三、实验总结 ##

本节实验中通过一个数据库实例了解了主键、默认值、外键、非空、唯一这几种约束的特性，这几种约束都是最常见的，需要仔细理解每种约束的含义及使用场景。下一节我们将进入 SELECT 语句的学习。

[00]: https://dn-anything-about-doc.qbox.me/MySQL/sql-03-00.png/logoblackfont
[07]: https://dn-anything-about-doc.qbox.me/MySQL/sql-03-07.png/logoblackfont
[08-]: https://dn-anything-about-doc.qbox.me/1sql-03-08-.png
[09-]: https://dn-anything-about-doc.qbox.me/1sql-03-09-.png
[10]: https://dn-anything-about-doc.qbox.me/MySQL/sql-03-10.png
[01]: https://dn-anything-about-doc.qbox.me/MySQL/sql-03-01.png
[11]: https://dn-anything-about-doc.qbox.me/MySQL/sql-03-11.png
[02]: https://dn-anything-about-doc.qbox.me/MySQL/sql-03-02.png/logoblackfont
[12-]: https://dn-anything-about-doc.qbox.me/1sql-03-12-.png
[03]: https://dn-anything-about-doc.qbox.me/MySQL/sql-03-03.png/logoblackfont
[13]: https://dn-anything-about-doc.qbox.me/MySQL/sql-03-13.png
[04]: https://dn-anything-about-doc.qbox.me/MySQL/sql-03-04.png
[05]: https://dn-anything-about-doc.qbox.me/MySQL/sql-03-05.png