Chapter3_关系数据库的创建与维护

第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的主要特性,SQLServer主要管理工具,SQLServer提供了一整套数据库系统管理工具和实用程序，打开“开始/程序/MicrosoftSQLServer2005”菜单即可看到如下所示的SQLServer程序组：,1SQLServerManagementStudio,SQLServerManagementStudio是一个管理平台，集成了多个图形工具和丰富的脚本支持。用于建立、访问、配置、控制、管理和开发数据库。,启动SQLServerManagementStudio,选择“开始/程序/MicrosoftSQLServer2005/SQLServerManagementStudio”菜单出现“连接到服务器”窗口。,服务器类型：指明要连接的服务。如果是管理和访问数据库服务器，选择“数据库引擎”。,服务器名称：选择或输入该服务器的计算机名称或IP地址，也可输入“.”或“（local）”，表示本机服务器。,身份验证：指明验证方式。注意SQLServer身份验证需用户名和密码，默认用户为“sa”。,SQLServerManagementStudio,已在本SQLServerManagementStudio下注册的服务器，可启动或停止服务器，更改连接选项,所有数据库对象的树型视图。可以浏览、管理、维护所有数据库对象。当选择某一数据库对象后，中间的“摘要”窗口自动显示该对象所包含的下一级对象或该对象的详细内容。,创建数据库对象所需要使用的基本SQL语句段的文件，对复杂脚本定义提供一种快捷支持，可方便地利用模板创建各种数据库对象,方便对脚本的集中编辑、保存和管理。可建立一个项目对多个脚本进行管理，多个项目又可作为一个解决方案。,SQLServerManagementStudio,“新建查询”可打开查询编辑器窗口，用于编辑SQL语句，执行和查看结果。例如，查询学生表的信息：,2SQLServerConfigurationManager,管理与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工作时所需要的系统级信息，系统自动维护和管理。,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）建立表之间的关系：依次建立各个表，再建立关系。,1SQLServer数据类型,数据表中每一列都需要定义明确的数据类型，数据类型决定了该字段的数据取值类型、范围和存储格式。,SQLServer的系统数据类型,（1）整型整型用于存储整数，有4种整型分别用于存储不同范围的整数。,float和real是近似数值型，以科学计数法表示数据；decimal和numeric是精确数值类型，以明确、完整的精度（固定精度和小数位数）存储数据。,（2）浮点型浮点型用于存储含小数的十进制数。,例如：指定某字段为精确数值型，精度为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字符数据类型和文本数据类型,例如：char(5)为长度为5的定长字符串，varchar(5)为最大长度为5的变长字符串。,char(n)是定长字符类型，字符串长度超出n的部分被截去；如不足n时，则在串的尾部添加空格以达到长度n。如char(5)，当输入Tom字符串时，其存储长度为5个字节，实际字符串值为Tomvarchar(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”。,使用时也可只存储日期或时间。,（5）图像数据类型用于存储照片、图片等，实际存储的是可变长度的二进制数据，其长度最大可达231-1个字节。注意：SQLServer不能显示image类型的数据，必须由应用程序来存取和显示。,在实际应用中，通常将图像以文件的形式存储在磁盘上，而只将图像文件的路径和文件名以字符串类型记录在数据库中。应用程序读取数据库获得存取路径，然后访问磁盘文件。其他多媒体类型数据处理方法类似。,（6）货币型用十进制数表示货币值。,货币型实际上是一种小数，但小数点后只有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。,教务系统数据库中有关字段类型的选取举例,学生表Student,教务系统数据库中有关字段类型的选取举例,课程表Course,教务系统数据库中有关字段类型的选取举例,成绩表Grade,2数据表创建,【例3.3】在数据库School中建立学生表Student，完成各字段的定义。,在对象资源管理器中，选择“数据库/School/表”，右击后从弹出的快捷菜单中选择“新建表”命令，弹出“表设计”窗口。,在表设计窗口，根据表的设计结构依次完成每个字段的名称和数据类型设置。,在对象资源管理器展开“数据库/School/表”可以看到创建好的数据表dbo.Student。,在完成所有字段设计后，可在“属性”窗格的“（名称）”处输入表名“Student”，单击工具栏上的“”按钮或“文件/保存”菜单或关闭表设计窗口，数据表创建完成。,属性设置区：字段描述、默认值、小数精度及位数等,数据表名称设置,设计表中字段,保存按钮,1）修改表结构：增加字段、删除字段、修改字段属性等。,操作步骤：,3.数据表维护,在对象资源管理器中右击要修改的“表”，在弹出菜单中选择“修改”命令，出现“表设计”窗口。,在“表设计”窗口，选中需要修改的一行，右击后弹出快捷菜单可以对表设置主键、插入列、删除列等。也可通过拖动行来调整字段的顺序。,对表的结构修改完毕后，保存对表结构修改。,2）更改表名,3）删除数据表,在对象资源管理器中，右击要删除的表，在快捷菜单中选择“删除”命令。,在对象资源管理器中选择要改名的表右击，在快捷菜单上选择“重命名”命令。,在表名位置输入新的表名即可。,在弹出的“删除对象”对话框中，选择“确定”按钮，即可删除该表。,4.数据表记录插入,在实际运行的信息系统中，数据表必须在完全创建好后（字段建好、各项数据完整性约束设置完成），才添加记录。而且最好是通过应用程序添加。,在系统开发过程中，可以通过手工添加一些记录来检查数据表定义是否合理，调试程序功能是否可用等。,手工将记录添加在数据表的尾部，可以向表中插入多条记录。,操作方法：在对象资源管理器中，右击数据表dbo.Student，在弹出的菜单中选择“打开表”，出现数据窗口“表-dbo.Student”。在该数据窗口中，可插入记录、修改记录和删除记录。,右击数据表dbo.Student,将光标定位在表尾的下一行，逐字段输入值，每输入一个字段值，按方向键“-”或“”键可以移向下一个字段。,当一条记录最后一个字段输入完毕按“回车”键后，光标将自动转到下一行的第一个字段处，便可插入下一条记录。,输入完数据，单击“”按钮或“文件/保存”菜单。或者单击“表-dbo.Student”窗口的“关闭”按钮，添加的记录就保存到数据表中。,插入记录：,5.数据表记录维护,定位将要删除的记录，右击后在弹出的快捷菜单中选择“删除”命令。,2）删除记录,1）修改记录,定位将要修改的记录字段，对该字段值进行编辑修改。,弹出删除确认对话框,单击“是”按钮即删除选择的记录。,数据的完整性控制：指保护数据库中的数据的正确性、有效性和相容性，防止数据库中存在不符合语义的数据，防止错误信息进入数据库，并且使数据符合所描述的业务规则。,SQLServer使提供了定义约束、检查和保持数据符合约束的完整性控制机制。通过限制表的字段、记录以及表之间的数据一致来保证数据完整性。,3.5数据表约束和数据完整性控制,SQLServer常用完整性约束机制,【例3.4】在数据库School中定义学生表T_Student的主键为“StudentCode”。,【例3.5】在数据库School的学生表Student中不允许出现姓名且生日相同的记录，并按姓名升序和生日降序排列，即对“StudentName”和“Birthday”字段组合定义唯一性约束。,主关键字是表中能保证表中记录唯一性的一个或多个字段的组合。主键的值不能重复，且不能为空。一个表只能有一个主键。,唯一性约束可保证非主键列不输入重复值。对非主键字段或字段的组合强制唯一性。对允许空值的字段强制唯一性。,1.实体完整性主要体现在表中记录的唯一性。,【例3.4】在数据库School中定义学生表T_Student的主键为“StudentCode”。,打开Student表的“表设计”窗口，右击任一字段行，从快捷菜单中选择“索引/键”命令，进入“索引/键”窗口。选择“添加”命令，将名称修改为“IX_StudentNameBirth”。点击“列”后“”按钮，打开索引列窗口，分别在两行中选择“StudentName”、“Birthday”，并选择排序顺序，点击“确定”返回“索引/键”页。在“是唯一的”其后下拉列表框中选择“是”。,【例3.5】在数据库School的学生表Student中不允许出现姓名且生日相同的记录，并按姓名升序和生日降序排列，即对“StudentName”和“Birthday”字段组合定义唯一性约束。,默认值约束使用户能够定义一个值，每当用户没有在某一列中输入值时，则系统将所定义的值提供给这一列,非空值约束要求记录中的每一列必须有值，即使是空（NULL）,检查约束限制某一列中可接受的值或格式,2.域完整性主要体现在表中字段值的有效性。,【例3.6】在数据库School的学生表T_Student中，设置性别Sex字段的默认值为“男”。,【例3.7】在数据库School的学生表T_Student中，设置StudentCode、StudentName、ClassCode、Sex字段为非空值，其他字段允许为空。,【例3.8】在数据库School的学生表T_Student中，设置Sex列的值只允许是“男”或“女”。,【例3.6】在数据库School的学生表T_Student中，设置性别Sex字段的默认值为“男”。,打开Student表的“表设计”页面，选择“Sex”字段在“列属性”窗格中的“默认值或绑定”处填写男。注意因为Sex字段是字符类型，男采用西文单引号扩起来。当保存表时，该约束设置即保存在数据库中。,【例3.7】在数据库School的学生表T_Student中，设置StudentCode、StudentName、ClassCode、Sex字段为非空值，其他字段允许为空。,打开Student表的“表设计”窗口，一个字段在新建立时默认允许空（），如果要设置为非空，只需要点击该字段在允许空位置的单元格将“”去掉即可，再次点击又可以设置允许空“”。当保存表时，该约束设置即保存在数据库中。,【例3.8】在数据库School的学生表T_Student中，设置Sex列的值只允许是“男”或“女”。,打开Student表的“表设计”窗口，右击任一字段行，从快捷菜单中选择“CHECK约束”命令，进入“CHECK约束”选择“添加”命令，将名称修改为“CK_Student_Sex”。点击“表达式”后的按钮出现“CHECK约束表达式”对话框，输入：Sex=男orSex=女将“表设计器”列表中的“强制用于INSERT和UPDATE”、“强制用于复制”、“在创建或重新启用时检查现存数据”都改为“是”。,3.参照完整性主要体现在通过表间关系约束字段的值的有效性。,两个建立关联关系的表中，在主表中相关字段定义了主键或唯一性约束，在从表中将相关字段定义为外外键。这样，外键的取值必须是主键中存在的值或空值。,【例3.9】在数据库School的学生表Student和Class表间建立关系，实施参照完整性约束。在该关系中，将Class的ClassCode字段设置为主键，将Student的ClassCode设置为外键，并实施参照完整性。,【例3.9】在数据库School的学生表Student和Class表间建立关系，实施参照完整性约束。,打开外表Student表的“表设计”窗口，右击并选择“关系”命令，出现“外键关系”对话框。单击“添加”按钮，点击“表和列规范”后的按钮，出现“表和列”对话框，将主键表改为“Class”，关系的主键字段为Cl