D3-关系数据库的创建与维护

第3章关系数据库的创建与维护SQlServer2005概述SQLServer数据库组成数据库的创建和维护数据库的创建和维护数据表的创建和维护数据表约数和数据完整性控制索引的创建和维护3.1MicrosoftSQLServer2005概述

MicrosoftSQL是Microsoft公司在Windows平台上开发一个高性能关系数据库管理系统。它支持关系数据库的创建和管理。可以应用在网络环境下客户机/服务器、浏览器/服务器结构的信息系统中，作为支持联机事务处理系统、联机分析处理系统的数据库服务器。类似地，还有很多商用数据库管理系统，例如Oracle公司的Oracle，IBM公司的DB2等也经常用于建设大规模的信息系统。还有，桌面型数据库管理系统，例如Microsoft公司的Foxpro,Access等，可用于构建小型信息系统。数据库管理系统（DataBaseManagementSystem,DBMS）是数据库系统的一个重要组成部分，是位于应用程序与操作系统之间的一层数据管理软件。数据库应用程序提出数据操作要求，通过DBMS，才能访问数据库。数据库管理员也是通过DBMS对数据库实施管理。DBMS是创建和使用数据库必不可少的软件！什么是数据库管理系统？（1）数据定义（2）数据操纵（3）数据库运行管理（4）数据库维护（5）支持数据库语言数据库管理系统的主要功能建立数据库，定义数据库的模式结构、数据库的完整性约束规则和安全性控制方式实现对数据库中数据的检索以及更新（包括插入、删除和修改）包括并发控制、安全性检查、完整性约束条件的检查和执行、数据库的内部管理和维护数据库的备份和恢复、导入和导出功能，数据库的重组功能和性能监视、分析功能等支持使用数据库语言来使用和管理数据库，关系型DBMS支持SQL语言(1)图形化的操作环境

Windows风格的可视化操作环境，易于学习和使用。(2)高性能的数据库管理和使用功能

支持高性能应用，支持分布式查询访问多数据源、充分的数据库完整性保护等。

(3)可伸缩性和高度可用性根据访问量动态使用软、硬件资源；支持从KB到TB数量级的数据库；7*24可用性。

(4)与Internet应用的集成提供了丰富的XML支持，可以轻松地存储和检索数据，并将查询结果直接用于各种网页程序。

(5)对数据仓库的支持

提供数据仓库组建和数据分析工具，有效支持决策分析处理。

SQLServer的主要特性

3.1.3SQLServer主要管理工具

SQLServer提供了一整套数据库系统管理工具和实用程序，打开“开始/程序/MicrosoftSQLServer2005

”菜单即可看到如下所示的SQLServer程序组

：1．SQLServerManagementStudio

SQLServerManagementStudio是一个管理平台，它集成了多个图形工具和丰富的脚本支持，用于建立、访问、配置、控制、管理和开发数据库。使用它可以对SQLServer服务器、数据库及数据库对象进行管理。启动SQLServerManagementStudio选择“开始/程序/MicrosoftSQLServer2005/SQLServerManagementStudio”菜单出现“连接到服务器”窗口。服务器类型：指明要连接的服务。如果是管理和访问数据库服务器，选择“数据库引擎”。服务器名称：选择或输入该服务器的计算机名称或IP地址，也可输入“.”或“（local）”，表示本机服务器。身份验证：指明验证方式。注意SQLServer身份验证需用户名和密码，默认用户为“sa”

。SQLServerManagementStudio已在本SQLServerManagementStudio下注册的服务器，可启动或停止服务器，更改连接选项所有数据库对象的树型视图。可以浏览、管理、维护所有数据库对象。当选择某一数据库对象后，中间的“摘要”窗口自动显示该对象所包含的下一级对象或该对象的详细内容。创建数据库对象所需要使用的基本SQL语句段的文件，对复杂脚本定义提供一种快捷支持，可方便地利用模板创建各种数据库对象方便对脚本的集中编辑、保存和管理。可建立一个项目对多个脚本进行管理，多个项目又可作为一个解决方案。SQLServerManagementStudio“新建查询”可打开查询编辑器窗口，用于编辑SQL语句，执行和查看结果。例如，查询学生表的信息：2．SQLServerConfigurationManager管理与SQLServer相关联的服务。启动：“开始/程序/MicrosoftSQLServer2005/配置工具/SQLServerConfigurationManager”。点击左边的“SQLServer2005服务”，右边窗格显示所有SQLServer服务，包括已注册的不同数据库服务器实例的服务。这些服务是SQLServer提供各项管理和服务功能的基础，如果服务停止，相关功能就无法使用。服务有3种状态：停止（红色）、启动（绿色）、暂停（蓝色）。SQLServer的核心服务组件，是实际的数据库服务器，该服务启动后，客户端才可以连接到服务器。一般的数据库功能都由它提供。作业调度和管理，支持数据库定时、自动备份、维护，监视数据库、异常告警等功能，必需和SQLServer服务一起使用。対数据仓库、商务智能和line-of-business解决方案提供分析支持侦听对SQLServer资源的传入请求，提供计算机上安装的SQLServer实例的有关信息支持基于全文索引的数据库表的全文搜索服务可用于数据质量管理和数据清洗。通过数据分析和数据挖掘，实现数据提取、转换和加载。SQLServer2005网络配置，支持完成本计算机上的SQLServer服务器的网络协议管理，如SharedMemory、TCP/IP、NamedPipes等，主要任务包括：启动或停止某个网络协议，配置网络协议，例如修改协议所用端口、加密方法等。SQLNativeClient配置，支持配置本服务器上运行的客户机程序的网络协议。其他客户机上需要安装并配置SQLNativeClient来支持客户机程序与SQLServer连接。主要功能：指定连接到SQLServer时的协议顺序，配置客户端连接协议，创建SQLServer的别名，使客户端能用自定义连接字符串进行连接。数据库在信息系统中用于实现数据的存储和管理，它是SQLServer的主要管理对象。3.2SQLServer数据库基础

数据库不仅存储数据，所有与数据结构、数据处理操作有关的信息也都存储其中。

因此，数据库并不是简单的数据集合！SQLServer数据库管理的数据库对象有两类：系统数据库和用户自定义数据库。SQLServer数据库分类

(1)系统数据库：存放SQLServer工作时所需要的系统级信息，系统自动维护和管理。数据库主要作用master从整体上控制SQLServer系统和用户数据库的运行。保存登录标识、系统配置、用户数据库基本信息等。该库非常重要，应设置权限禁止一般用户访问，另外要及时备份model是新建数据库的模板，包含了每个用户数据库都需要的一些系统表，SQLServer以它为基础创建新的数据库msdb支持SQLServerAgent服务，它记录有关作业、警报、操作员等信息tempdb是一个临时数据库，它为SQLServer的查询、存储过程等的执行提供临时存储SQLServer数据库分类

(2)用户自定义数据库：用户根据数据管理的需要建立的数据库，由用户创建和维护。例如，为了管理教务信息，可建立教务系统数据库，为了实现网上书店，建立图书销售数据库等。

SQLServer自带了3个样本数据库，供用户学习使用：AdventureWorks是个OLTP数据库示例AdventureWorksDW是个OLAP数据库仓库示例AdventureWorksAS是个分析服务数据库示例表：存储数据的二维关系表视图：通过查询从一个或多个数据表获得的虚拟表，可简化用户数据显示、增强数据库安全性可编程性：包括存储过程、触发器、规则、函数、默认值等安全性：包括用户、角色、密钥、证书等数据库由一组存储、管理和使用数据的对象构成。这些对象及相互关系构成了数据库逻辑存储结构。观察master数据库，可看到数据库所包含的主要对象：SQLServer数据库组成数据库文件在磁盘上存储的数据库文件有三种：数据库的物理存储结构主数据文件：用来存储数据库的系统表和所有对象启动信息，并且存储数据库的数据。每个数据库都有且只有1个主数据文件。主数据文件使用.MDF为扩展名。次数据文件：用来存储不能置于主数据文件中的其他数据。每个数据库可以包含0个或多个次数据文件。扩展名为.NDF。事务日志文件：记录SQLServer执行的所有事务以及由这些事务操作引起的数据库的变化，可用于恢复数据库。每个数据库至少包含1个事务日志文件，扩展名为.LDF。每个数据库至少包含2个文件：主数据文件和事务日志文件。(2)数据文件组用户自定义文件组(User_defined)：指在创建或更改数据库时，用户明确创建的任何文件组。

主文件组(Primary)：包含主数据文件和其它文件。所有系统表都包含在主文件组中例如：将两个不同物理磁盘上的数据库文件组成一个文件组，在该组上建立的数据表中的数据会存储在不同的物理设备上，查询操作可能并行。

文件组是用户对数据库文件（不包括事务日志文件）进行分组而形成的。合理使用文件组对数据进行分布存储，可以提高表中数据的操作性能。若不指定用户定义文件组，则所有数据文件都包含在主文件组中。一个文件只能是一个文件组的成员，一个文件或文件组只能被一个数据库使用!3.3数据库的创建与维护1.数据库创建数据库创建就是在磁盘上建立数据库文件的过程。下面通过一个实例介绍使用ManagementStudio创建数据库的步骤和方法。【例3.2】创建数据库School，它包含主数据文件SchoolDB.MDF，初始大小为5MB，事务日志文件SchoolDB_log.LDF，并将这两个文件建立在C:\DBExample文件夹下，其他按照默认设置不变。【例3.2】操作步骤：①在桌面上选择“我的电脑”，在C:\下新建文件夹，命名为“DBExample”。②在对象资源管理器窗口右击“数据库”，从快捷菜单中选择“新建数据库”命令，将弹出“新建数据库”对话框。提示：SQLServer以model数据库为模板创建一个新的数据库，其数据文件的默认值为：初始大小3MB，以1MB自动增长，不限制增长；事务日志文件的默认值为：初始大小1MB，按10%的比例自动增长，不限制增长。④将数据文件的逻辑名称修改为“SchoolDB”，在路径下的文本框输入或点击其后“…”按钮选择修改数据库文件的位置为“C:\DBExample”。在初始大小后的文本框中把3改为5。⑤将日志文件的逻辑名称修改为“SchoolDB_log”，“路径”设置为“C:\DBExample”。⑥单击“确定”按钮，数据库创建完成。③“选择页”默认为“常规”，在“数据库名称”文本框中输入“School”。使用“我的电脑”查看“C:\DBExample”文件夹，可以看到文件SchoolDB和SchoolDB_log。建好的School数据库在对象资源管理器展开的“数据库”下可以看到，并且系统在School下自动创建了其包含的相关对象。

2.数据库维护数据库创建后，磁盘上的数据文件和日志文件名就不能改变了。对已存在的数据库可以进行如下的修改：增加或删除数据文件改变数据文件的大小和增长方式改变日志文件的大小和增长方式增加或删除日志文件增加或删除文件组重命名数据文件，书库文件和日志文件逻辑名。（1）修改数据库右单击需要修改的数据库，在快捷菜单中选择“属性”命令，弹出“数据库属性”窗口。其中包含8个选项，每个选项下可对数据库的相关属性进行修改。（2）删除数据库操作步骤如下：

①右击要删除的数据库，在弹出的快捷菜单中选择“删除”命令。②在弹出的“删除对象”对话框中单击“确定”按钮，即可删除要删除的数据库。提示：注意数据库一旦被删除，数据库文件及其数据都被从服务器上的磁盘中删除，该数据库中所有的对象均被删除，即永久性删除，不能恢复，所以删除操作要慎重！新建一个数据库操作完成后,只是建立了数据库框架和相关的系统对象,接下来的任务是建立具体的数据表。例如：建立如下教务系统的数据库，要建立6个数据表，并建立表之间的关系。具体定义见教材P37页。3.4数据表的创建和维护1）设计表结构,包括：定义字段(1)创建列名、数据类型(2)设置列属性也称列约束。包括：PRIMARYKEY：主关键字约束UNIQUE：唯一性约束CHECK：检查约束DEFAULT：默认值约束NOTNULL：非空约束IDENTITY：标识规范保存表3）表创建后，可输入一些记录数据来检验表结构。创建数据表的一般过程：2）建立表之间的关系：依次建立各个表，再建立关系。1．SQLServer数据类型数据表中每一列都需要定义明确的数据类型，数据类型决定了该字段的数据取值类型、范围和存储格式。数据类型主要类型符号标识整型bigint、int、smallint、tinyint浮点型float、real、decimal、numeric字符型char、varchar、nchar、nvarchar日期时间型datetime、smalldatetime文本型text、ntext图像型image货币型money、smallmoney位型bit二进制型binary、varbinary其他uniqueidentifierSQLServer的系统数据类型（1）整型整型用于存储整数，有4种整型分别用于存储不同范围的整数。数据类型数据范围占用存储空间bigint-263~263-18个字节int-231~231-14个字节smallint-215~215-12个字节tinyint0~2551个字节float和real是近似数值型，以科学计数法表示数据；decimal和numeric是精确数值类型，以明确、完整的精度（固定精度和小数位数）存储数据。（2）浮点型浮点型用于存储含小数的十进制数。

数据类型数据范围精确度占用存储空间float-1.79E+308~1.79E+308可精确到第15位小数8个字节real-3.40E+38~3.40E+38可精确到第7位小数4个字节decimal-1038+1~1038-1完整精度2~17个字节numeric-1038+1~1038-1完整精度2~17个字节例如：指定某字段为精确数值型，精度为5，小数位数为5，即decimal(5,2)或numeric(5,2)，可表示数据范围为：-999.99--999.99。decimal和numeric格式如下：decimal|numeric(p[,s])其中：p为精度，s为小数位数，s的默认值为0。

decimal和numeric的区别在于decimal不能应用于带有Identity的列。（3）字符型和文本型

用于存储由字母、符号和数字组成的字符串。字符串常量要用单引号作为起止界限。例如：’No1’。SQLServer字符数据类型和文本数据类型数据类型定义格式占用存储空间charchar(n)，存储ANSI字符，n表示字符串的长度，1=<n<=8000定长，最大8KBvarcharvarchar(n)，存储ANSI字符，n表示字符串可达到的最大长度1=<n<=8000变长，最大8KBncharnchar(n)，存储Unicode字符，n表示字符串的长度，1=<n<=4000定长，最大8KBnvarcharnvarchar(n)，存储Unicode字符，n表示字符串可达到的最大长度1=<n<=4000变长，最大8KBtexttext,存储ANSI字符变长，大于8KB例如：char(5)为长度为5的定长字符串，varchar(5)为最大长度为5的变长字符串。char(n)是定长字符类型，字符串长度超出n的部分被截去；如不足n时，则在串的尾部添加空格以达到长度n。如char(5)，当输入'Tom'字符串时，其存储长度为5个字节，实际字符串值为'Tom

'varchar(n)是变长字符类型，长度为字符串的实际字符个数。如varchar(5)，当输入‘Tom’字符串时，其存储长度为3个字节，实际字符串值为'Tom'。通常，当某一字段的字符串长度固定时使用char类型；当字符串长度明显不一致时使用varchar类型。Text是文本数据类型，用来存储可变长度的大量字符数据，其存储长度最多可达20亿个ASCII字符。char(n)和varchar(n)存储ANSI字符，用1个字节（8bits）表示一个字符，每个汉字占2个字节。适合存储英文字符串。char(n)

是定长字符串，varchar(n)是变长字符串。

nchar(n)和nvarchar(n)存储Unicode字符集，用2个字节（16bits）表示一个字符。适合存储中文或中英文混合字符串。nchar(n)

是定长字符串，nvarchar(n)是变长字符串。提示：ANSI(AmericanNationalStandardInstitite：美国国家标准协会)和Unicode(UniversalMultiple-OctetCodedCharacterSet：通用字符集)是两种不同的编码标准。例如：char(5)可存储5个英文字符，但只能存储2个汉字,而nchar(5)可存储5个英文或汉字构成的字符串。（4）日期时间型用来存储日期和时间数据，默认数据格式为：”YYYY-MM-DDHH:MM:SS”。例如“2012-12-0812:35:29”。数据类型数据范围占用存储空间datetime1753年1月1日~9999年12月31日之间的日期时间8个字节smalldatetime1900年1月1日~2079年6月6日之间的日期时间4个字节使用时也可只存储日期或时间。

（5）图像数据类型用于存储照片、图片等，实际存储的是可变长度的二进制数据，其长度最大可达231-1个字节。注意：SQLServer不能显示image类型的数据，必须由应用程序来存取和显示。

在实际应用中，通常将图像以文件的形式存储在磁盘上，而只将图像文件的路径和文件名以字符串类型记录在数据库中。应用程序读取数据库获得存取路径，然后访问磁盘文件。其他多媒体类型数据处理方法类似。（6）货币型用十进制数表示货币值。

货币型实际上是一种小数，但小数点后只有4位，且自动四舍五入。数据类型数据范围占用存储空间money-922,337,203,685,477.5808~+922,337,203,685,477.5807精度为4位小数8个字节smallmoney-214,748.3648~214,748.3647，精度为4位小数4个字节（8）uniqueidentifie唯一标识符类型。数据库管理系统会随着记录的加入自动为字段产生唯一标识符。它是一个16字节长的二进制数。（7）位类型bit用来作为逻辑变量使用，其数值有两种取值：1和0，分别代表“TRUE”和“FALSE”或YES和NO。例如：在T_Student表中，字段LiveInDorm存储“是否住校”信息使用bit。（8）uniqueidentifie唯一标识符类型。数据库管理系统会随着记录的加入自动为字段产生唯一标识符。它是一个16字节长的二进制数。（7）位类型bit用来作为逻辑变量使用，其数值有两种取值：1和0，分别代表“TRUE”和“FALSE”或YES和NO。例如：在T_Student表中，字段LiveInDorm存储“是否住校”信息使用bit。教务系统数据库中有关字段类型的选取举例字段名称字段说明类型定义StudentCode学号char(4)StudentName学生姓名nvarchar(20)ClassCode班号char(2)Sex性别nchar(1)Birthday出生日期smalldatetimeLiveInDorm是否住校bitTelephone联系电话nvarchar(40)Photo照片（存放路径和文件名）nvarchar(50)Description个人介绍nvarchar(100)PassWord密码nvarchar(16)学生表Student教务系统数据库中有关字段类型的选取举例课程表Course字段名称字段说明类型定义CourseCode课程号char(3)CourseName课程名称nvarchar(16)Credit学分numeric(3,1)Academy开课学院nvarchar(5)ClassTime上课时间nvarchar(20)ClassPlace上课地点nvarchar(20)ClassHour上课学时tinyintLabHour实验学时tinyintTotalSeats最大学生数smallintLeftSeats剩余名额smallintTeacherCode任课教师char(4)Description课程简介textGradeAnalysis成绩分析评价nvarchar(100)教务系统数据库中有关字段类型的选取举例成绩表Grade字段名称字段说明类型定义StudentCode学号char(4)CourseCode课程号char(3)Grade成绩numeric(4,1)WriteStamp录入时间datetime2．数据表创建【例3.3】在数据库School中建立学生表Student，完成各字段的定义。①在对象资源管理器中，选择“数据库/School/表”，右击后从弹出的快捷菜单中选择“新建表”命令，弹出“表设计”窗口。②在表设计窗口，根据表的设计结构依次完成每个字段的名称和数据类型设置。④在对象资源管理器展开“数据库/School/表”可以看到创建好的数据表dbo.Student。③在完成所有字段设计后，可在“属性”窗格的“（名称）”处输入表名“Student”，单击工具栏上的“”按钮或“文件/保存”菜单或关闭表设计窗口，数据表创建完成。属性设置区：字段描述、默认值、小数精度及位数等数据表名称设置设计表中字段保存按钮1）修改表结构：增加字段、删除字段、修改字段属性等。

操作步骤：3.数据表维护①在对象资源管理器中右击要修改的“表”，在弹出菜单中选择“修改”命令，出现“表设计”窗口。②在“表设计”窗口，选中需要修改的一行，右击后弹出快捷菜单可以对表设置主键、插入列、删除列等。也可通过拖动行来调整字段的顺序。③对表的结构修改完毕后，保存对表结构修改。2）更改表名3）删除数据表①在对象资源管理器中，右击要删除的表，在快捷菜单中选择“删除”命令。①在对象资源管理器中选择要改名的表右击，在快捷菜单上选择“重命名”命令。②在表名位置输入新的表名即可。②在弹出的“删除对象”对话框中，选择“确定”按钮，即可删除该表。4.数据表记录插入

在实际运行的信息系统中，数据表必须在完全创建好后（字段建好、各项数据完整性约束设置完成），才添加记录。而且最好是通过应用程序添加。

在系统开发过程中，可以通过手工添加一些记录来检查数据表定义是否合理，调试程序功能是否可用等。手工将记录添加在数据表的尾部，可以向表中插入多条记录。操作方法：在对象资源管理器中，右击数据表dbo.Student，在弹出的菜单中选择“打开表”，出现数据窗口“表-dbo.Student”。在该数据窗口中，可插入记录、修改记录和删除记录。右击数据表dbo.Student①将光标定位在表尾的下一行，逐字段输入值，每输入一个字段值，按方向键“->”或“<Tab>”键可以移向下一个字段。②当一条记录最后一个字段输入完毕按“回车”键后，光标将自动转到下一行的第一个字段处，便可插入下一条记录。

③输入完数据，单击“”按钮或“文件/保存”菜单。或者单击“表-dbo.Student”窗口的“关闭”按钮，添加的记录就保存到数据表中。插入记录：5.数据表记录维护①定位将要删除的记录，右击后在弹出的快捷菜单中选择“删除”命令。2）删除记录1）修改记录定位将要修改的记录字段，对该字段值进行编辑修改。②弹出删除确认对话框,单击“是”按钮即删除选择的记录。3.5数据表约束和数据完整性控制

数据的完整性控制:指保护数据库中的数据的正确性、有效性和相容性，防止数据库中存在不符合语义的数据，防止错误信息进入数据库，并且使数据符合所描述的业务规则。SQLServer使用表约束实现数据库完整性控制的方法：通过限制表的字段、记录以及表之间的数据来保证数据完整性。

表4-10SQLServer数据常用完整性约束机制完整性约束作用实体完整性主关键字约束（PrimaryKey）保证表中记录行的唯一性唯一性约束（Unique）保证在非关键字字段不出现重复值域完整性默认值约束（Default）对没有插入值的列自动添加表定义时对该列设置的默认值非空值约束（NotNull）限定某一列必须有值，即不允许空值检查约束（Check）限定某一列中可接受的值或数据格式参照完整性外关键字（ForeignKey）通过表间关系约束字段值的有效性SQLServer常用数据完整性约束机制：实体完整性

主关键字约束（PrimaryKey）唯一性约束（Unique）

表中记录的唯一性主关键字（简称主键）是表中能保证表中记录唯一性的一个或多个字段的组合。主键的值不能重复，且不能为空。一个表只能有一个主键。

唯一性约束可保证非主键列不输入重复值。对非主键字段或字段的组合强制唯一性。对允许空值的字段强制唯一性。

【例3.4】在数据库School中定义学生表Student的主键为“StudentCode”。【例3.5】在数据库School中的学生表Student不允许出现姓名且生日相同的记录，并按姓名升序和生日降序排列，即对StudentName和Birthday字段组合定义唯一性约束。域完整性默认值约束（Default）

非空值约束（NotNull）

检查约束（Check）

表中字段（或称列或域）的值的有效性。

默认值约束使用户能够定义一个值，每当用户没有在某一列中输入值时，则系统将所定义的值提供给这一列。

记录中的每一列均必须有值。CHECK约束限制某一列中可接受的值或格式。

【例3.6】在数据库School的学生表Stud