《数据库原理》实验讲义.docx

1、数据库原理实验讲义数据库原理实验讲义SQL Server版龚 安中国石油大学（华东）计算机科学系2011年12月目 录实验一 文件管理一、实验目的 1了解数据管理技术之文件系统管理数据的特点； 2掌握应用程序存取数据文件的方法； 3通过实际体会与比较，为后面感受数据库系统管理数据的优点做准备。二、实验学时2学时三、实验内容设有一个学生-课程数据库，包括学生表student，课程表course和选修表sc，如下表所示：Student学号姓名性别年龄所在系95001李勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男19ISCourse课程号课程名先行课学分1数据库542

2、数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言64SC学号课程号成绩9500119295001285950013889500229095002380用C或C+语言编写应用程序，建立3个数据文件分别保存学生信息、课程信息和选修信息，并且完成以下任务： 1编写数据插入功能模块，将以上各个数据表的数据分别插入到对应的数据文件中，并显示输出各个数据表的数据信息； 2编写数据修改功能模块，将所有学生的年龄增加1岁，并显示输出所有学生的信息；3编写数据删除功能模块，删除学号为95004的学生记录，并显示输出所有学生的信息。4编写数据查询功能模块，查询选修了课程名为“信息系统

3、”的学生学号和姓名。四、实验报告提交源程序（加必要的注释）和数据文件，并将程序的每个任务的输出结果附上。实验二 DBMS软件的安装、卸载及认识一、实验目的 1了解SQL Server 2005的安装环境配置； 2掌握SQL Server 2005的安装方法与过程； 3掌握SQL Server 2005的卸载方法与过程；4了解SQL Server 2005的系统数据库、系统表及系统存储过程；5掌握SQL Server 2005的SQL Server管理控制台、SQL Server配置管理器的使用。二、实验学时2学时三、实验准备1SQL Server 2005安装环境的配置在安装SQL Serve

4、r2005前，需做好两项准备工作： 保证计算机的硬软件环境能满足SQL Server 2005的需要。 依据所需的用途和计算机的软硬件环境选择合适的版本和部件。(1) SQL Server 2005的版本企业版（Enterprise Edition）企业版分为支持32位和支持64位两个版本。企业版达到支持超大型企业进行联机事务处理(OLTP)、高度复杂的数据分析、数据仓库系统和网站所需的性能水平。其全面商业智能和分析能力，以及高可用性功能，使它可以处理大多数关键业务的企业工作负荷。企业版是最全面的SQL Server版本，适用于超大型企业，能够满足最复杂的要求。标准版（Standard Edi

5、tion）标准版分为支持32位和支持64位两个版本。这是适用于中小型企业的数据管理和分析平台。其包括电子商务、数据仓库和业务流解决方案所需的基本功能。其集成商业智能和高可用性功能可以为企业提供支持其运营所需的基本功能。该版本功能虽没有企业版那样齐全，但它所具有的功能已经能够满足企业的一般要求，是需要全面的数据管理和分析平台的中小型企业的理想选择。工作组版（Workgroup Edition）工作组版仅适用于32位机。它适用于那些需要在大小和用户数量上没有限制的数据库的小型企业，能为这些企业提供理想的数据管理解决方案。该版本包括SQL Server产品的核心数据库功能，并且可以容易地升级到标准版

6、或企业版。工作组版本是理想的入门级数据库，具有可靠、功能强大且易于管理的特点，可以用作前端Web服务器，也可以用于部门或分支机构的运营。开发版（Developer Edition）开发版分为支持32位和支持64位两个版本。该版本主要适用于应用程序开发人员在SQL Server 2005上开发用做数据存储的任何类型的应用程序。开发版包括企业版的所有功能，具有特殊的最终用户许可协议，因此只能将开发版作为开发和测试系统使用，不能作为生产服务器使用。开发版适用于独立软件供应商、咨询人员、系统集成商、解决方案供应商以及创建和测试应用程序的企业开发人员，也可以根据生产需要升级到企业版。精简版（Expres

7、s Edition）精简版仅用于32位机。该版本是一个免费、易于使用且便于管理的数据库。SQL Server Express 与 Microsoft Visual Studio 2005集成在一起，可以轻松开发功能丰富、存储安全、可快速部署的数据驱动应用程序。该版本是免费且可以再分发的商业化产品，可以起到客户端数据库以及基本服务器数据库的作用。它适用于低端独立软件供应商、低端服务器用户、创建Web应用程序的非专业开发人员以及创建客户端应用程序的用户。(2) SQL Server 2005的硬件环境设置要安装SQL Server 2005，还需要了解SQL Server 2005对硬件的安装要求

8、。以下以32位平台上安装SQL Server 2005为例，列出安装SQL Server 2005不同版本所必需的最低硬件需求，如表2-1所示。表2-1 32位平台上安装和运行SQL Server 2005 的硬件要求SQL Server 2005(32位)处理器类型处理器速度内存企业版开发版标准版Pentium 兼容处理器及以上处理器600MHz以上最小512MB建议1GB工作组版Pentium 及以上处理器600MHz以上最小512MB建议1GB精简版Pentium 及以上处理器600MHz以上最小192MB建议512MB注：若硬件环境不满足处理器类型要求，系统配置检查器（SCC）将阻止安

9、装程序运行。若硬件环境不满足最低处理器速度要求或是不能满足最低或建议的RAM要求，SCC将发出警告，但不会阻止安装程序运行。 安装SQL Server 2005，除了表2-1所列对于处理机的最低要求之外，对其他硬件方面的要求如下：硬盘空间：SQL Server 2005各个版本功能不同，所需组件数目也不相同，因此实际硬盘空间要求取决于系统配置和选择安装的应用程序和功能。概略地说，SQL Server 2005完整的组件安装需要600MB硬盘空间。监视器：SQL Server 2005图形工具需要VGA或更高分辨率，分辨率至少为1024*768像素。其他设备：需要Microsoft鼠标及CD或D

10、VD驱动器。(3) SQL Server 2005的软件环境设置安装SQL Server 2005的软件要求主要指操作系统方面的要求。不同版本的SQL Server 2005对操作系统的要求不尽相同。此外，还需要必备的网络软件及其他Internet软件，如表2-2所示。表2-2 32位平台的各种操作系统对SQL Server 2005不同版本的支持软件SQL Server 2005Windows平台企业版标准版开发版工作组版精简版Windows XP SP2Windows 2000 Server SP4Windows 2003 Server SP1Windows Small BusinessS

11、erver 2003 SP1IE 6.0 SP1或更高版本各个版本均需要.NET Framework 2.0各个版本均需要注： SQL Server 2005标准版、工作组版不能安装和运行在Windows XP Home Edition SP2操作系统之上。除SQL Server 2005精简版以外，其他各版本不能安装和运行在Windows 2003 Web Edition SP1之上。2SQL Server 2005系统数据库SQL Server 2005中的数据库由表的集合组成，这些表用于存储一组特定的结构化数据。表中包含行（也称为记录或元组）和列（也称为属性）的集合。当SQL Serve

12、r 2005成功安装后，系统会自动创建5个系统数据库，如图2-1所示。图2-1 系统数据库实际上，SQL Server的数据库可分为“用户数据库”与“系统数据库”两类，用户数据库是指用户自行创建的数据库，而系统数据库则是SQL Server内置的，它主要是基于管理上的需求而存在。(1) 系统数据库SQL Server数据库是由各自的数据库文件和日志文件所组成，系统数据库也是如此。系统数据库文件在默认安装下存储在目录C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData中，其中，数据库文件的扩展名为.mdf，数据库日志文件的扩展名为.ldf。一个典

13、型的SQL Server 2005系统数据库文件名称特性如表2-3所示。表2-3 系统数据库文件系统数据库文件类型逻辑文件名物理文件名默认大小(MB)master主要数据文件mastermaster.mdf4日志文件mastlogmastlog.ldf2model主要数据文件modeldevmodel.mdf3日志文件modellogmodellog.ldf1msdb主要数据文件msdbdatamsdbdata.mdf6日志文件msdblogmsdblog.ldf2tempdb主要数据文件tempdevtempdb.mdf8日志文件templogtemplog.ldf1资源主要数据文件mssq

14、lsystemresource.mdf日志文件mssqlsystemresource.ldf注：SQL Server 2005 各个版本的系统数据库文件大小及文件增长大小有所不同。另外，系统数据库文件大小不能超出SQL Server 2005系统的限制。以数据文件而言，最大只能到16TB，而日志文件最大只能到2TB。master数据库系统数据库master内含有许多系统表(SystemTables)，用来追踪与记录SQL Server系统的所有系统级别信息。这些系统信息包括所有的登录账户信息、端点、链接服务器和系统配置设置信息、SQL Server的初始化信息和其他系统数据库及用户数据库是否存

15、在以及这些数据库文件的位置等相关信息。每当用户创建一个数据库，此数据库的相关信息（例如主要数据文件的位置）便会记录在master中，也就是说，master会记录系统拥有哪些数据库。如果进行的是分布式操作，master还会记录有哪些其他的SQL Server。由此可见，master数据库是重要的系统数据库，如果该数据库不可用，则SQL Server无法启动。因此，最好随时保留一份master的最新备份。所以特此建议读者，在创建了一个数据库、更改系统的配置设置或添加了一个登录账号，以及执行任何会更改系统数据库master的操作之后，立即备份系统数据库master。model数据库该数据库是所有用户

16、数据库和tempdb数据库的模板数据库。既然model是一个模板数据库，则当用户每创建一个数据库时，这个新的数据库就会自动完成某些设定或包含某些数据库对象。实际上，当创建数据库时，系统首先通过复制model数据库中的内容来创建数据库的第一部分，然后用空页填充新数据库的剩余部分。系统数据库model是所有新建数据库的模板，也就是说，可以直接针对model做改动。这样，每当创建一个新的数据库，SQL Server便会复制model数据库的所有内容，以此作为新数据库的基础。按照上述方法修改了model数据库，在此之后所创建的数据库都将继承这些修改。例如，可以设置权限或数据库选项或者添加对象，诸如表、

17、函数或存储过程。如此一来，新创建的数据库将会继承系统数据库model的一切，而不需要每次再设定。这样做对简化数据库的创建和配置操作将有很大的帮助。msdb数据库系统数据库msdb主要提供SQL Server代理程序调度警报和作业。它用于提供SQL Server、SQL Server Agent、Database Mail以及Service Broker 等服务程序存储诸如日程安排、邮件副本等数据，如果要让警报、备份、复制等各项操作能够自动定期执行，就需要启动SQL Server Agent服务。当需要进行数据备份和还原操作时，SQL Server会将备份和还原的信息记录到msdb中，这些信息包

18、括执行备份的个体名称、备份时间，以及存储备份的文件名称。tempdb数据库简言之，tempdb数据库是一个工作空间，用于保存所有的临时对象或中间结果集。该数据库由整个系统的所有数据库使用。即tempdb系统数据库是连接到SQL Server实例的所有用户都可用的全局资源。SQL Server每次启动时，tempdb数据库被重新建立，因此，不需要担心tempdb会因为存放过多的数据而占用太多磁盘空间，因为每当用户断开与SQL Server的联机，该用户在联机期间所生成的临时表与存储过程会自动从tempdb中删除。因此每次重新联机后，该数据库总是空的。用户使用tempdb数据库，主要保存以下内容：

19、 显式创建的临时对象，例如表、存储过程、表变量或游标。 所有版本的更新记录（如果启用了快照隔离）。 SQL Server Database Engine在执行查询、排序、创建cursor等处理时所创建的内部工作表。 创建或重新生成索引时，临时排序的结果。资源系统数据库资源系统数据库mssqlsystemresource用来存储SQL Server中诸如名称以sp_开头的存储过程等系统对象。因为系统对象很重要，所以这个名为“资源”的系统数据库设定成只读数据库。该系统数据库是SQL Server 2005新增加的，在SQL Server 2000及以前版本中没有这个数据库。因为资源系统数据库是一个

20、只读数据库，所以它不出现在SQL Server Management Studio的“对象资源管理器”窗口中，如图2-1所示。同样道理，作为十分重要的系统数据库，用户不能删除或重命名资源系统数据库文件，否则无法启动SQL Server。(2) SQL Server 2005系统表SQL Server将定义服务器配置及其所有表的数据存储在一组特殊的表中，这组表称为系统表。系统表用于记录所有服务器活动的信息。系统表中的信息组成了SQL Server系统的数据字典。与以前版本的重要不同之处是，在SQL Server 2005版本中用户无法直接查询或更新系统表。SQL Server 2005中的系统表

21、已作为只读视图实现，目的是保证SQL Server 2005系统的安全性和向后兼容性。用户只能通过使用目录视图访问SQL Server元数据。重要的系统表1) objects系统表这是SQL Server的主系统表，出现在每个数据库中。在数据库中创建的每个用户定义的架构范围内的对象在本表中都有相对应的一行记录。其实是将SQL Server 2005中master数据库内的系统表映射到它们在SQL Server 2005中对应的一个或多个系统视图。2) columns表该表出现在master数据库和每个用户创建的数据库中，它记录数据库中所有包含属性列的对象（如表或视图）的相关信息，即该表对于数据

22、库中表或者视图的每个列都要使用一行来记录。3) indexes表出现在每个数据库中，它对每个表对象（表、视图、表值函数）的索引，或没有索引而称为堆的表对象使用一行记录。4) users表出现在每个数据库中，该表对数据库中的每个Windows NT用户、Windows NT用户组、SQL Server用户或者SQL Server角色等主体使用一行记录。5) files表该表存在于每个数据库中，是一个基于每个数据库的视图。其对于每个存储在数据库本身中的数据库文件在该表中占用一行。6) servers表该表只出现在master数据库中。在此表中，已注册的每个链接服务器或远程服务器都对应有一行，表中字

23、段server_id值为0的一行记录与本地服务器对应。7) databases表该表对SQL Server系统上的每个系统数据库和用户自定义的数据库含有一行记录，它只出现在master数据库中。使用系统表系统数据库以及系统表都是SQL Server 2005中最重要的内置的数据库和表，任何用户都不应直接更改系统表。但用户在使用SQL Server 2005进行数据管理时，可能需要系统数据库中表的数据和名称、系统表中的列数以及每一列的名称、数据类型、小数位数和精度，也可能需要了解为该表定义的约束、定义的索引和键等信息。也就是说，需要检索存储在系统表中的信息。SQL Server 2005系统目录

24、可以为SQL Server数据库提供此信息。SQL Server 2005系统目录的核心是一个视图集，这些视图显示了描述SQL Server实例中的对象的元数据。所谓视图，其实是与表有类似结构的由行和列组成的数据集合。而元数据是描述系统中对象属性的数据。用户或基于SQL Server的应用程序可以使用目录视图方式访问系统目录中的信息。SQL Server 2005引入了目录视图，将其作为系统目录元数据的常规界面，通过这些视图可访问服务器上各数据库中存储的元数据。这也是获取、转换以及表示此自定义形式的元数据的最直接的方式。因为目录视图与表结构相似，因此可以使用SQL语句在SQL Server 2

25、005的查询分析器中完成检索系统表信息的工作。例如，在SQL Server Management Studio中运行以下SQL语句：select * from sys.objects将显示所有在数据库中的对象及其描述内容，如图2-2所示。图2-2 通过目录视图显示系统表内容(3) SQL Server 2005系统存储过程以上对于系统表的检索操作，还可以通过SQL Server所提供的大量系统存储过程来进行。 系统存储过程是预先经过编译的SQL语句集合。SQL Server 2005中许多管理活动都是通过系统存储过程完成的。系统存储过程是SQL Server 2005系统自动创建的存储过程，S

26、QL Server的系统存储过程都记录在master数据库中，归系统管理员（SA）所有，系统存储过程均以sp_或xp_开头。使用系统存储过程可以方便地实现系统数据查询功能，前面已提及，对应系统表中的内容，直接使用DML语句操作很危险，而使用系统存储过程则能保证系统操作的完整性和安全性。例如，通过系统存储过程sp_databases查询databases系统表中内容，则在SQL Server Management Studio的查询分析器中执行下列语句：EXEC sp_databases该系统存储过程运行结果如图2-3所示。图 2-3 执行系统存储过程查询系统表内容3SQL Server 200

27、5常用工具(1) 管理控制台SQL Server 2005中使用最多的管理工具就是SQL Server管理控制台(SQL Server Management Studio)。这个集成的管理工具用于管理和监视SQL Server关系数据库、集成服务、分析服务、报表服务、通知服务以及分布式服务器和数据库上的SQL Server Mobile，从而大大简化了管理的复杂程度。SQL Server管理控制台的作用按照默认安装，SQL Server 2005控制台的相关的文件放在C:Program FilesMicrosoft SQLServer90ToolsBinnVSShellCommon7IDESq

28、lWb.exe。它是集成在Visual Studio 2005之内的。利用SQL Server管理控制台可完成的工作很多，如连接到上述各类服务的实例以设置服务器属性，可以创建和管理诸如数据库、数据表、存储过程、方体（Cube）、维度、程序集、登录账号和数据库用户权限、报表服务器的目录等各类服务器对象。另外，还有如管理数据库的文件和文件夹、附加或分离数据库、管理安全性、视图存放在文件上的SQL Server系统记录、监视目前的活动、设置复制、管理全文检索索引、视图与设置Agent Services的作业、警报、操作员、Proxy等。就是说，通过单一的SQL Server管理控制台，可以同时访问、

29、设置和管理SQL Server数据库引擎、SQL Server Mobile、分析服务（Analysis Services）、集成服务（Integration Services）以及报表服务（Reporting Services）等服务内的各种对象，让DBA可以有统一的平台来管理各种服务实例。还可以让开发人员或DBA以项目的方式组织与管理日常使用的各类型查询语言文件，如T-SQL、MDX、DMX、XML/A等。SQL Server 管理控制台的使用从Windows开始程序菜单的SQL Server 2005项的下拉列表菜单子项中选择执行Management Studio，即进入SQL Serv

30、er管理控制台，在该集成环境中，首先要进行连接，即需要在弹出的“连接到服务器”对话框中指定各项连接信息，如指定要连接的服务器类型、所要连接的服务器名称以及指定用于登录的身份认证方式，若采用“SQL Server验证”方式，则需输入用户名称和密码。该对话框会保留上次使用的设置，当需要创建新的连接时，例如打开新的T-SQL、MDX等语法编辑环境时，会使用这些设置。连接对话框的操作界面如图2-4所示。图2-4 进入SQL Server管理控制台时的连接操作界面单击图2-4的“选项”按钮，打开如图2-5所示的连接属性设置对话框，在此对话框的“连接属性”选项卡中可以进一步进行各项细节设置，以将Management Studio连接到不同服务实例。在图2-5中，“连接到数据库”的连接属性是要求从其右侧的下拉列表中选择该服务器中可用的数据库，在列表中只会出现有权查看的数据库。而“网络协议”选项则提供“共用存储器”、TCP/IP、“命名管道”三种连接方式的选择。“网络数据包大小”以字节为单位，默认值为4096字节。“连接超时值”是指等候与服务器建立