数据库的完整性

数据库的完整性

在使用数据库的过程中，数据的正确与完整直接影响数据库使用质量。数据不正确，程序功能无论怎样完善，也无法得到正确的结果。在创建数据库时，利用数据完整性是解决这些问题的重要方法。
数据完整性是指数据的精确性和可靠性，是为防止数据库中存在不符合语义规定的数据，防止因错误信息的输入、输出而造成无效的操作或错误信息而提出的，数据完整性在数据库管理系统中是十分重要的。

数据完整性的类型

数据完整性对于数据来说有两个方面的含义，即正确和相容。根据数据完整性所作用的数据库对象和范围不同，可以将其分为以下几类：

  1. 域（Domain）完整性
    域完整性的常见实现机制有默认值（Default）、检查（Check）、外键（Foreign Key）、数据类型（Data Type）和规则（Rule）。

  2. 实体（Entity）完整性
    实体对应的是行，实体完整性是要求表中的每一行具有唯一的标识。实体完整性的实现机制有主键（Primary Key）、唯一码（Unique Key）、唯一索引（Unique Index）和标识列（Identity Column）。

  3. 引用完整性
    引用完整性是指两个表的主键与外键之间定义的数据完整性，将确保主键和外键的关系。引用完整性可以保证两个引用表间的数据的一致性实现。
    引用完整性的实现机制有外键（Foreign Key）、检查（Check）、触发器（Trigger）和存储过程（Stored Procedure）。

  4. 用户定义完整性
    用户定义完整性的实现机制有规则（Rule）、触发器（Trigger）和存储过程（Stored Procedure）及创建数据表时的所有约束（Constraint）

约束

约束（Constraint）是定义关于列中允许值的规则，是强制实施完整性的标准机制。SQL Server 2016通过5种约束可以定义自动强制实施数据库完整性的方式。

注意 约束只会检查添加约束后的数据，添加约束之前的数据不检查。

常见约束

SQL Server 2016支持的约束类型
NOT NULL约束：列的为空性决定表中的行是否可为该列包含空值。
PRIMARY KEY约束：标识具有唯一标识表中行的值的列或列集。在一个表中，不能有两行具有相同的主键值。不能为主键中的任何列输入 NULL 值。每个表都应有一个主键。

FOREIGN KEY约束：外键用于建立和加强两个表数据之间的连接的一列或多列。通过定义FOREIGN KEY 约束来创建外键可以标识并强制实施表间的关系。
UNIQUE约束：强制实施列集中值的唯一性。表中的任何两行都不能有相同的列值。UNIQUE约束可以输入NULL值。
CHECK约束：通过限制可放入列中的值来强制实施域完整性。CHECK 约束指定逻辑表达式来检测输入的相关列值，若输入列值使得计算结果为 FALSE ，则该行被拒绝添加。可以在一个表中为每列指定多个CHECK 约束。

在SSMS中创建约束

创建NOT NULL约束
在SQL Server Management Studio中选择表，利用执行“设计”命令后弹出窗体中，对表中的列的“允许空”项进行选择即可。

创建PRIMARY KEY 约束
在SQL Server Management Studio中选择表，利用执行“设计”命令后弹出窗体中，右击表中被选择的列，在弹出的菜单中执行“设置主键”命令即可。

在SSMS中创建约束
创建FOREIGN KEY 约束。
以score表为例介绍创建FOREIGN KEY 约束步骤如下。
①　在SQL Server Management Studio中选择表score，执行“设计”命令后弹出窗体，单击“关系”按钮，如图4-11所示。
image

② 在弹出的“外键关系”对话框中，单击“添加”按钮，然后选择“表和列规范”后的…按钮,如图4-12所示。
image
③在弹出的“表和列”对话框中，选择主键表student和外键表score及共有的列studentno。如图4-13 所示。单击“确定”按钮，外键约束创建完毕。

image

建立外键时：尽量要求主键表数据比较少，

创建UNIQUE约束
在表score中创建UNIQUE约束的步骤如下。
①在SQL Server Management Studio中选择表course，执行“设计”命令后弹出窗体，单击“管理索引和键”按钮，如图4-14所示。
image

② 在弹出的“索引/键”对话框中，单击“添加”按钮，选择cname列，然后选择“是唯一的”后的列表框按钮,如图4-15所示。选择“是”，单击“关闭”按钮即可。

image

创建CHECK约束
。在表student中创建CHECK约束的步骤如下。
①　在SSMS中选择表student，执行“设计”命令后弹出窗体，单击“管理Check约束”按钮，如图4-16所示。
image

② 在弹出的“CHECK约束”对话框中，单击“添加”按钮，然后选择“表达式”后的…按钮,如图4-17所示。
image

③ 在弹出的“CHECK约束表达式”对话框中，输入表达式“ sex='男' OR sex='女' ”。如图4-18 所示。单击“确定”按钮，CHECK约束创建完毕。
image

利用Transact-SQL语句

利用Transact-SQL语句创建或修改约束
创建约束可以使用CREATE TABLE或ALTER TABLE语句完成。使用CREATE TABLE语句表示在创建表的时候定义约束，使用ALTER TABLE语句表示在已有的表中添加约束。即使表中已经有了数据，也可以在表中增加约束。
当创建约束时，可以指定约束的名称。否则，系统将提供一个复杂的、系统自动生成的名称。对于一个数据库来说，约束名称必须是唯一的。一般来说，约束的名称应该按照这种格式：约束类型简称_表名_列名_代号。

利用T-SQL语句创建或修改约束的语法格式如下：

 <table_constraint>：：=[ CONSTRAINT constraint_name]                      --定义或修改约束WITH{CHECK|NOCHECK}]
  ADD   {  <table_constraint> } [ ,...n ]
 |DROP 
    { [ CONSTRAINT ] constraint_name 
      | COLUMN column_name } [ ,...n ] 
      | { CHECK | NOCHECK } CONSTRAINT 
      { ALL | constraint_name [ ,...n ] }

【例4.14】为数据库teaching中的班级表class的列classno创建PRIMARY KEY 约束，并将其中的classname、department、monitor的“允许空”修改为NOT NULL。

ALTER TABLE class
ADD CONSTRAINT PK_class PRIMARY KEY CLUSTERED
 (	classno ASC)
GO
ALTER TABLE class
ALTER COLUMN classname nchar(12) NOT NULL   --修改数据类型
GO
ALTER TABLE class
ALTER COLUMN department nchar(12) NOT NULL
GO
ALTER TABLE class
ALTER COLUMN monitor nchar(8) NOT  NULL 
GO

【例4.15】为数据库teaching中的成绩表score的两个列daily和final添加CHECK约束，限定其值在0~100范围内。

ALTER  TABLE  score
ADD CONSTRAINT CK_usually  CHECK(daily >=0  and daily<=100),
    CONSTRAINT CK_final CHECK(final >=0  and final <=100)
GO

【例4.16】为数据库teaching中的学生表student的列Email创建一个UNIQUE约束。

ALTER TABLE student
ADD CONSTRAINT u_Email UNIQUE NONCLUSTERED (Email)
GO

【例4.17】为数据库teaching中表score的列studentno创建一个FOREIGN KEY约束。

ALTER TABLE  score  
   WITH CHECK 
   ADD  CONSTRAINT FK_sc_stud FOREIGN KEY (studentno)
   REFERENCES  student (studentno)
GO

一个表可含有多个 FOREIGN KEY 约束。如果 FOREIGN KEY 约束已经存在，则可以修改或删除它。
一个表添加 FOREIGN KEY 约束后。外键强制一个列只能取一个被引用的表中存在的值。例如，在表score中输入一个student表中不存在的studentno列中的值，则会出现如图4-19所示的提示对话框。

使用 FOREIGN KEY 约束应该注意如下的问题。
FOREIGN KEY 约束只能引用所引用的表的 PRIMARY KEY 或 UNIQUE 约束中的列或所引用的表上 UNIQUE INDEX 中的列。
FOREIGN KEY 约束仅能引用位于同一服务器上的同一数据库中的表。
列级 FOREIGN KEY 约束的 REFERENCES子句只能列出一个引用列。
表级 FOREIGN KEY 约束的 REFERENCES 子句中引用列的数目必须与约束列列表中的列数相同。

数据库关系图

数据库关系图 (database diagram)是数据库中对象的图形表示形式。在数据库设计过程中，可以利用数据库关系图对数据库对象如表、列、键、索引、关系和约束等进行进一步设计和修改。

数据库关系图包括表对象、表所包含的列以及它们之间的相互关联的情况。

数据库关系图中的要素

在数据库关系图中，每个表都可带有3种不同的功能： 标题栏、行选择器和一组属性列，参看图4-23。

  1. 标题栏。
    标题栏显示表的名称。如果修改了某个表，但尚未保存该表，则表名末尾将显示一个星号 (*)，表示未保存更改。

  2. 行选择器。
    可以通过单击行选择器来选择表中的数据库列。如何该列是表的主键，则行选择器将显示一个键符号。

  3. 属性列。
    属性列组仅在表的某些视图中可见。

在数据库关系图中，每个关系都可以带有3种不同的功能： 终结点、线型和相关表，参看图4-23。

  1. 终结点。
    如果某个关系在一个终结点处有键，在另一个终结点处有无穷符号，则该关系是一对多关系。

  2. 线型。
    线本身表示当向外键表添加新数据时，DBMS是否强制关系的引用完整性。如果为实线，则在外键表中添加或修改行时，DBMS将强制关系的引用完整性。

  3. 相关表 。
    关系线表示两个表之间存在外键关系。对于一对多关系，外键表是靠近线的无穷符号的那个表。

创建数据库关系图

可以通过创建关系图或打开现有的关系图来打开数据库关系图设计器。

数据的导入和导出

数据转换概述

SQL Server Integration Services (SSIS)是一种企业数据转换和数据集成解决方案，用户可以以此从不同的数据源

  1. 提取
  2. 转换
  3. 复制（动态的）
  4. 合并数据，

并将其移至单个或多个目标。

SSIS的典型用途：

  • 合并来自异类数据存储区的数据，包括文本格式、Excel和Access等数据。
  • 自动填充数据仓库，进行数据库的海量导入、导出操作。
  • 对数据的格式在使用前进行数据标准化转换。
  • 将商业智能置入数据转换过程。
  • 使数据库的管理功能和数据处理自动化。

启动SSIS

启动SQL Server Integration Services:
在使用SSIS之前，要求运行SQL Server Integration Services。启动集成服务的步骤如下：
在“开始”菜单中，单击“Microsoft SQL Server 2016” “Microsoft SQL Server 2016CTP2.0配置工具”命令，启动如图4-24所示的窗体。
image

展开左侧窗体的“SQL Server服务”选项，在右侧窗口中选择SQL Server Integration Services服务，右击，然后选择菜单中的“启动”命令即可。

Integration Services 的数据转换类型
数据转换将输入列中的数据转换为其他数据类型，然后将其复制到新的输出列。

例如，可从多种数据源中提取数据，然后用此转换将列转换为目标数据存储所需的数据类型。

如果需要配置数据转换，可以采用下列方法：

  • 指定包含要转换的数据的列和要执行的数据转换的类型。
  • 指定转换输出列是使用 Microsoft SQL Server Integration Services (SSIS) 提供的不区分区域设置的较快分析例程，还是使用标准的区分区域设置的分析例程。

Integration Services 数据引擎支持具有多个源、多个转换和多个目标的数据流。（这些转换会自动实现）
这些转换包括：

  • 条件性拆分和多播转换，用于将数据行分布到多个下游数据流组件。

  • 合并和合并联接转换，用于组合来自多个上游数据流组件的数据行。

  • 排序转换，用于排序数据和标识重复的数据行。

  • 模糊分组转换，用于标识相似的数据行。

  • 查找和模糊查找转换，用于扩展包含查找表中的值的数据

  • 字词提取和字词查找转换，用于文本挖掘应用程序。

  • 聚合、透视、逆透视和渐变维度转换，用于数据仓库。

  • 百分比抽样和行抽样转换，用于提取样本行集。

  • 复制列转换、数据转换和派生列转换。

  • 聚合转换，用于汇总数据。

  • 透视和逆透视转换，用于从非规范化的数据创建规范化的数据行，以及从规范化的数据创建非规范化的数据行。

  • Integration Services 还包括用于简化自定义转换的开发工作的脚本组件。

SQL Server数据的导入导出向导
SQL Server 导入和导出向导提供了最低限度的数据转换功能。
除了支持在新的目标表和目标文件中设置列的名称、数据类型和数据类型属性之外，SQL Server 导入和导出向导不支持任何列级转换。

向导功能：

  1. 主要功能：复制数据。

  2. 在SQL Server 还能够更好地支持平面文件中的数据和对数据的实时预览。使用 SQL Server 导入和导出向导创建的已保存的包可以在BI DS中打开，并可以使用 SSIS 设计器进行扩展。

  3. 访问的数据源。

操作实例

启动导入导出 向导 常用方法
方法1：在 Business Intelligence Development Studio 中，右键单击“SSIS包”文件夹，再单击“SSIS导入和导出向导”。

方法2：在 Business Intelligence Development Studio 中的“项目”菜单上，单击“SSIS 导入和导出向导”。

方法3：在 SQL Server Management Studio 中，连接到数据库引擎服务器类型，展开数据库，右键单击一个数据库，指向“任务”，再单击“导入数据”或“导出数据”。
image

导入数据具体步骤参考

  • 启动导入向导。
  • 选择数据源类型。
  • 选择目标。
  • 指定复制或查询操作。
  • 编辑和保存文件。

导出和导入类似

小结

    在SQL Server 2016中，可以使用SSMS的功能和命令来完成对数据表的创建、修改及删除操作，也可在查询编辑器中使用Transact-SQL语句来完成对数据表的操作。同时，还可以对表进行数据完整性的设置。在学习过程中，重点掌握如下几方面的基本操作。

各种数据类型的特点和用途。
数据库表结构的创建、修改和删除等基本操作和命令。
表数据的插入、更新和删除。
如何在创建表时进行数据完整性的设置。
各种数据格式之间的转换。

posted @ 2022-10-31 10:06  kingwzun  阅读(910)  评论(0)  编辑  收藏  举报