头歌:信息安全技术之完整性和安全性

最新推荐文章于 2024-04-05 16:33:23 发布

蜡笔超级小新

最新推荐文章于 2024-04-05 16:33:23 发布

阅读量1.5k

点赞数 24

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/wangmyang0909/article/details/135054954

版权

第1关：完整性约束的SQL定义

任务描述

第1关：完整性约束的SQL定义

任务描述

本关任务：学习完整性约束，根据指定约束创建用户注册表。

相关知识

数据完整性约束指的是为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS 自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。本关我们就一起来学习数据完整性约束的 SQL 定义。

完整性约束

约束是用来确保数据的准确性和一致性，数据的完整性就是对数据的准确性和一致性的一种保证。更新数据库时，表中不能出现不符合完整性要求的记录，以保证为用户提供正确、有效的数据。实现该目的最直接的方法，是在编写数据库应用程序时，对每个更新操作都进行完整性检查。但这种检查往往是复杂、重复、低效的。

SQL 把各种完整性约束作为数据库模式定义的一部分，由数据库管理系统维护，这样即可有效防止对数据库的意外破坏，提高了完整性检测的效率，又减轻了编程人员的负担。一旦定义了完整性约束，MySQL 服务器就会随时检测处于更新状态的数据库内容是否符合相关的完整性约束，从而保证数据的一致性与正确性。如此，既能有效地防止对数据库的意外破坏，又能提高完整性检测的效率，还能减轻数据库编程人员的工作负担。

数据的完整性分为以下四类：

实体完整性：规定表的每一行在表中是惟一的实体（通过约束，唯一约束，主键约束或标识列属性）。
域完整性：是指表中的列必须满足某种特定的数据类型约束，其中约束又包括取值范围、精度等规定。
参照完整性：是指两个表的主关键字和外关键字的数据应一致，保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。
用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用必须满足的语义要求。

约束

设计数据库时，可以对数据库表中的一些字段设置约束条件，由数据库管理系统自动检测输入的数据是否满足约束条件，不满足约束条件的数据，数据库系统拒绝录入。

MySQL 支持的常用约束条件有 7 种：主键（primary key）约束、外键（foregin key）约束、非空（not null）约束、唯一性（unique）约束、默认值（default）约束、自增（auto_increment）约束以及检查（check）约束。其中，检查约束需要借助触发器或者 MySQL 复合数据类型实现。

注意：在 MySQL 数据库中不支持检查约束。可以在语句中对字段添加检查约束，不会报错，但该约束不起作用。

对于基本表的约束可以分为列级约束和表级约束。列级约束有六种：主键、外键、唯一、检查、默认和非空；表级约束有四种：主键、外键、唯一和检查。如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。

主键约束

设计数据库时，建议为所有的数据库表都定义一个主键，用于保证数据库表种记录的唯一性。一张表种只允许设置一个主键，当然这个主键可以是一个字段，也可以是一个字段组（不建议使用复合主键）。

在录入数据的过程中，必须在所有主键字段中输入数据，即任何主键字段的值不允许为 NULL。可以在创建表的时候创建主键，也可以对表已有的主键进行修改或者添加新的主键，

设置主键通常有两种方式：表级完整性约束和列级完整性约束。

如果一个表的主键是单个字段 ID
1. 使用表级完整性约束，就用 PRIMARY KEY 命令单独设置主键为 ID 列，即PRIMARY KEY(字段名)。
2. 使用列级完整性约束，就是直接在该字段的数据类型或者其他约束条件后加上“PRIMARY KEY”关键字，即可将该字段设置为主键约束，语法规则：字段名数据类型[其他约束条件] PRIMARY KEY。
如果一个表的主键是多个字段的组合

例如字段 1 与字段 2 共同组成主键，则定义完所有的字段后，使用下面的语法规则设置复合主键：PRIMARY KEY(字段名1,字段名2)。

下面是两个主键约束创建的示例：

#创建单个字段主键
CREATE TABLE IF NOT EXISTS
USER1(
id int primary key,
username varchar(20)
);
#创建多字段主键：
CREATE TABLE IF NOT EXISTS
USER2(
id int,
username varchar(20),
card char(18).
primary key(id,card)
);

外键约束

外键是表的一个特殊字段，被参照的表是主表，外键所在字段的表为子表。设置外键的原则就是依赖于数据库中已存在的表的主键。

外键是建立该表与其父表的关联关系，父表中对记录做操作时，子表中与之对应的信息也应有相应的改变，即外键的作用是保持数据的一致性和完整性。

注意：创建表时，建议先创建父表，再创建子表。外键列和参照列必须具有相似的数据类型，其中数字的长度或是否有符号位必须相同，而字符的长度则可以不同。外键列和参照列必须创建索引，如果外键列不存在索引的话，MySQL 将自动创建索引。

设置外键的两种方式：

在表级完整性下定义外键约束

语法格式如下：
1. FOREIGN KEY(表A的字段名列表) REFERENCES 表B(字段名列表)
2. [ ON DELETE {CASCADE | RESTRICT | SET NULL | NO ACTION}]
3. [ ON UPDATE {CASCADE | RESTRICT | SET NULL | NO ACTION}]
级联选项有 4 种取值，其意义如下：

1）CASCADE：父表记录的删除或者修改操作，会自动删除或修改子表中与之对应的记录。

2）SET NULL：父表记录的删除或者修改操作，会将子表中与之对应记录的外键值自动设置为 NULL 值。

3）NO ACTION：父表记录的删除或修改操作，如果子表存在与之对应的记录，那么删除或修改操作将失败。

4）RESTRICT：与 NO ACTION 功能相同，且为级联选项的默认值。

如果表还没有建立，那么可以在创表时指定，语法如下：
1. constraint <完整性约束条件名> [primary key 短语 | foreign key 短语 | check 短语]
如果表已经建好表，则可以通过 ALTER TABLE 命令添加外键，语法如下：
1. alter table table_name add
2. [constraint 外键名] forrgin key [id](index_col_name,...)
3. references table_name(index_col_name,...)
4. [on delete {cascade | restrict | set null | no action}]
5. [on update {cascade | restrict | set null | no action}]
在列级完整性下定义外键约束

在列级完整性上定义外键约束，就是直接在列的后面添加 references 命名。

下面是创建外键约束的示例：

#创建父表部门表
CRAETE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;
#创建员工表指定外键名称
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depid TINYINT UNSIGNED,
CONSTRAINT emp_fk_dep FOREIGN KEY(depid) REFERENCES department(id)
)ENGINE=INNODB;
#给employee 表添加外键：
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depid) REFERENCES department(id)；
#创建级联外键 —— 删除外键的级联同时删除子表
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depid TINYINT UNSIGNED,
FOREIGN KEY(depid) REFERENCES department(id) ON DELETE CASCADE
)ENGINE=INNODB;

非空约束

被标识了非空的就不能有空值（NULL），再插入级联时不能有空否则会报错。非空一般与默认值一起使用。如果某个字段满足非空约束的要求（例如学生的姓名不能取 NULL 值），则可以向该字段添加非空约束。非空约束限制该字段的内容不能为空，但可以是空白。

设置某个字段的非空约束，直接在该字段的数据类型后面加上“NOT NULL”关键字即可。语法规则如下：

字段名数据类型 NOT NULL

下面是创建非空约束示例：

CREATE TABLE IF NOT EXISTS USER(
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password CHAR(32) NOT NULL,
age TINYINT UNSIGNED
);

默认约束

默认值一般与非空约束使用，在插入级联没有给字段赋值时，就使用默认值。根据具体问题具体分析给那些字段添加默认值。如果某个字段满足默认值约束要求。可以向该字段添加默认值约束。例如可以将课程 course 表的人数上限 up_limit 字段设置默认值为 60。

设置某个字段的默认值约束，直接在该字段数据类型及约束条件后加上“DEFAULT 默认值”即可，语法规则如下：

字段名数据类型[其他约束条件] DEFAULT 默认值

下面为创表时带有默认约束示例：

CREATE TABLE IF NOT EXISTS USER(
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password CHAR(32) NOT NULL,
age TINYINT UNSIGNED DEFAULT 18,
addr VARCHAR(50) NOT NULL DEFAULT '北京',
sex ENUM('男','女','保密') NOT NULL DEFAULT '男'
);

唯一约束

唯一约束就是唯一性索引，一个表中只有一个主键，一个表中可以有多个唯一。被标志了唯一字段的值不允许出现重复，但是有一个特例 NULL 不算重复值。设置某个字段为唯一性约束，直接在该字段数据类型就加上“UNIQUE”关键字即可，语法规则如下：

字段名数据类型 UNIQUE

唯一性约束实质上是通过唯一性索引实现的，因此唯一性约束的字段一旦创建，那么该字段将自动创建唯一性索引。如果要删除唯一性约束，只需要删除对应的唯一性索引即可。

下面是创建唯一约束示例：

CREATE TABLE IF NOT EXISTS USER(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
card CHAR(18) UNIQUE
);

自增约束

AUTO_INCREMENT 是 MySQL 唯一扩展的完整性约束，当为数据库表中插入新记录时，字段上的值会自动生成唯一的 ID。具体设置自增约束时，一个数据库表中只能由一个字段使用该约束，该字段的数据类型必须是整型类型。

自增长要配合主键使用，被标志位自增长的一定是主键，但是主键不一定是自增长。MySQL 中通过 SQL 语句的 AUTO_INCREMENT 来实现，语法规则如下：

属性名数据类型 AUTO_INCREMENT

下面是自增约束示例：

#从0开始增长的每次加1
CREATE TABLE IF NOT EXISTS USER(
id SMALLINT KEY AUTO_INCREMENT,
username VARCHAR(20)
);
#指定从100开始增长
CREATE TABLE IF NOT EXISTS USER(
id SMALLINT KEY AUTO_INCREMENT,
username VARCHAR(20)
)AUTO_INCREMENT=100;

检查约束和删除约束

检查约束

检查约束是用来检查数据表中字段值的有效性的一个手段。例如学生信息表中的年龄字段是没有负数的，并且数值也是有限制的，当前大学生的年龄一般在 15~30 岁之间。其中前面讲述的默认值约束和非空约束可以看作是特殊的检查约束。

在创建表时设置列的检查约束有两种：设置列级约束和表级约束。

删除约束

在 MySQL 数据库中，一个字段的所有约束都可以用 alter table 命名删除。

编程要求

在右侧编辑器中的Begin-End之间补充 SQL 代码，创建一个学生注册表（reg_user），具体要求如下：

字段名	数据类型	约束	说明
id	SMALLINT	自增主键	编号
username	VARCHAR(20)	非空，唯一	用户名
password	CHAR(32)	非空	密码
email	VARCHAR(50)	非空，默认值为“mysql@educoder.net”	邮箱
age	TINYINT	默认值为18	年龄
sex	ENUM('男','女','保密')	默认值为保密	性别
addr	VARCHAR(200)	非空，默认值为北京	地址
face	CHAR(100)	非空，默认值为“default.jpg”	头像图片

测试说明

平台会对你编写的 SQL 进行测试，比对你输出的结果，只有表信息与预期一致才能通过测试。

开始你的任务吧，祝你成功！

话不多说, 直接贴答案:

#创建 reg_user 表并添加约束
########## Begin ##########
CREATE TABLE reg_user (
    id SMALLINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(20) NOT NULL UNIQUE,
    password CHAR(32) NOT NULL,
    email VARCHAR(50) NOT NULL DEFAULT 'mysql@educoder.net',
    age TINYINT DEFAULT 18,
    sex ENUM('男', '女', '保密') DEFAULT '保密',
    addr VARCHAR(200) NOT NULL DEFAULT '北京',
    face CHAR(100) NOT NULL DEFAULT 'default.jpg'
);



########## End ##########