数据库系统概论课程实验指导书V12.docx

1、数据库系统概论课程实验指导书V12数据库系统概论课程实验指导书江 涛淮南师范学院计算机信息工程系2011.09目录目录 实验一 数据库的创建和管理 实验二 表的创建、管理及数据操作实验三 SQL数据查询实验四 SQL数据操纵实验五 数据库保护实验六 数据库应用开发附加实验 事务并发控制操作实验一 数据库的创建和管理一、实验目的 熟悉SQL Server 2000实验环境的设置、学会独立使用该系统； 熟悉SQL Server 2000中企业管理器和查询分析器两个常用管理工具； 熟练掌握SQL Server Enterprise Manager 创建和管理数据库； 熟练掌握在查询分析器中使用T-S

2、QL语句创建和管理数据库二、实验内容1. 熟悉Microsoft SQL Server / Enterprise Manager2. 熟悉Microsoft SQL 查询分析器3. 利用企业管理器创建和管理数据库 (1) 创建数据库(2) 删除数据库4. 在查询分析器中使用T-SQL语句创建和管理数据库(1) 创建数据库 (2) 修改数据库(3) 删除数据库三、实验步骤1、启动服务器管理器首先通过【开始】【程序】【Microsoft SQL Servers】【服务器管理器】，在系统托盘中会同时出现SQL服务的图标，单击【开始/继续】前的小三角，此时，在系统托盘中的图标上会有一个绿色箭头，表示服

3、务已经启动。2、SQL Server 2000企业管理器首先通过【开始】【程序】【Microsoft SQL Servers】【企业管理器】进入企业管理器界面。在企业管理器的左边列出的是控制台管理目录，依次是数据库类型（SQL Servers）、数据库群组名（SQL Servers组）、数据库服务器主机名，默认连接为本机）和该数据库服务器提供的所有的管理工具（包括数据库、数据转换服务、管理、复制、安全性、支持服务等）。在“数据库”选项中列出的是当前服务器中所有的数据库名（master、model、msdb、Northwind等），其中master、model、tempdb等为系统数据库，记录其

4、他数据库的公有属性和某些隐藏信息，用户不要直接对系统数据库（也就是master库）中的表和数据项进行修改和删除，否则将会给数据库系统造成不可挽回的损失。3、新建数据库在服务器组的“数据库”上，右击，弹出快捷菜单，选择“新建数据库”。在弹出的对话框中常规（General）页框中，要求用户输入数据库名称以及排序规则名称。点击数据文件（Data Files）页框，该页框用来输入数据库文件的逻辑名称、存储位置、初始容量大小和所属文件组名称，如图点击事务日志（Transaction Log）页框，该页框用来设置事务日志文件信息，如图： 单击“确定”按钮，则开始创建新的数据库。4、表操作新建表 在企业管理

5、器中，展开指定的服务器和数据库，打开想要创建新表的数据库，用右键单击表对象，从弹出的快捷菜单中选择“新建表”选项，或者在工具栏中选择图标，就会出现新建表对话框，在该对话框中，可以定义列的以下属性：列名称、数据类型、长度、精度、小数位数、是否允许为空、缺省值、标识列、标识列的初始值、标识列的增量值和是否有行的标识。 然后根据提示进行设置。增加、删除和修改字段 在企业管理器中，打开指定的服务器中要修改表的数据库，用右键单击要进行修改的表，从弹出的快捷菜单中选择“设计表”选项，则会出现设计表对话框，在该对话框中，可以利用图形化工具完成增加、删除和修改字段的操作。创建、删除和修改约束 在企业管理器中，

6、用右键单击要操作的数据库表，从弹出的快捷菜单中选择“设计表”选项 ，然后根据提示对主键进行添加、删除和修改操作。查看表格在企业管理器中，用右键单击要操作的数据库表，从弹出的快捷菜单中选择“设计表”选项 ，可以查看表格的定义在企业管理器中，用右键单击要操作的数据库表，从弹出的快捷菜单中选择“打开表返回所有行”选项 ，查看表格中的数据 删除表格 在企业管理器中，展开指定的数据库和表格项，用右键单击要删除的表，从快捷菜单中选择“除去表”选项，则会出现除去对象对话框。单击“全部删除”按钮，即可删除表。5、查询分析器(Query Analyzer)首先通过【开始】【程序】【Microsoft SQL S

7、ervers】【查询分析器】进入查询分析器界面。窗口的顶部是菜单和工具栏，在工具栏中有一个数据库列表框，列出主机中所有的数据库，用户可直接在列表框中选择进行操作的数据库名（如：Northwind）。主窗口的左边是对象浏览器（包括对象和模板两页），右边是Transact-SQL程序编辑器。查询分析器是SQL Server2000的另一个非常主要的客户端工具，查询分析器用Transact-SQL程序对数据库系统进行分析和管理，企业管理器用图形界面和向导对数据库进行操作。对于那些更高级的查询分析、批处理创建删除，使用查询分析器就比较灵活。用户可直接在编辑器中输入查询分析语句“select * fro

8、m categories”，其中categories是Northwind数据库中的一张表，用户可以在左边对象浏览器的数据库用户表中找到。然后点击工具栏上的绿色三角形按钮进行执行。查询结果将出现在编辑器的下面，如下图：查询分析器的有一实用功能程序模板，选择左边的模板类型，右边编辑框将自动生成相应的Transact-SQL程序。修改程序中的参数就执行相应的功能。例如要创建一个数据库，就在左边选择Create Database 模板类型，再选择详细类型Create Database Basic Template，右边出现一段程序，其功能是判断主机中是否存在数据库N，如果存在的话，就将其删除，然后再创

9、建数据库N。用户也可以通过程序模板来进一步学习Transact-SQL语言。6、在查询分析器使用T-SQL语句管理数据库 利用T-SQL语句创建数据库打开查询分析器，输入如下语句： CREATE DATABASE MYDBON(NAME=MY_DB_DATA,FILENAME=D:PROGRAM FILESMicrosoft SQL ServerMSSQLdataMY_DB_DATA.MDF,SIZE=5, MAXSIZE=15, FILEGROWTH=2)LOG ON(NAME=MY_DB_LOG,FILENAME=D:PROGRAM FILESMicrosoft SQL ServerMSS

10、QLdataMY_DB_LOG.LDF,SIZE=3, MAXSIZE=10, FILEGROWTH=1)GO 利用T-SQL语句修改数据库用T-SQL语句完成修改数据库的操作，语句如下。 ALTER DATABASE MYDBADD FILE(NAME=MY_DB_DATA1,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQLdataMY_DB_DATA1.NDF,SIZE=3, MAXSIZE=15, FILEGROWTH=2)GOALTER DATABASE MYDBMODIFY FILE(NAME=MY_DB_LOG,MAXSIZE=20

11、, FILEGROWTH=2)GO 利用T-SQL语句删除数据库 DROP DATABASE MYDB实验二 表的创建、管理及数据操作一、实验目的 熟练掌握使用企业管理器和T-SQL语句创建、修改和删除表。 熟练掌握使用企业管理器和T-SQL语句插入、修改和删除表数据。二、实验内容1、使用企业管理器创建学生选修课程数据库Student_Courses（参照课本）；2、Student_Courses数据库包含如下三个表：学生表、选修表、课程表；主要设置表的完整性；3、在企业管理器中插入、修改和删除表数据。4、使用T-SQL重复上述操作5、分别使用企业管理器和T-SQL修改和删除表三、实验步骤1、

12、建立studentdb数据库和spjdb数据库；2、在studentdb数据库中利用企业管理器和查询分析器创建以下3个表，同时完成数据完整性的定义（实体完整性、参照完整性和用户定义的域完整性）：student（学生信息表）：主码列名数据类型宽度小数位空否取值范围备 注Pksnochar9N学号snamechar10N姓名ssexchar2Y性别sagesmallintY不小于12年龄sdeptchar15Y系名course（课程表）：主码列名数据类型宽度小数位空否备 注PkcnoChar4N课程号cnameChar20Y课程名称cpnoChar4Y先行课号ccreditsmallintY学分s

13、c（学生选课表）：主码列名数据类型宽度小数空否外码参照关系取值范围备 注PksnoChar9NFkstudent学号cnoChar4NFkcourse课程号gradeDecimal51Y0x100成绩3、在spjdb数据库中利用企业管理器和查询分析器创建以下4个表，同时完成数据完整性的定义（实体完整性、参照完整性和用户定义的域完整性）：S（供应商信息表）：主码列名数据类型宽度小数位空否取值范围备 注Pksnochar2N供应商号snamechar10N供应商名称statussmallintY大于0供应商状态citychar10Y所在城市P（零件信息表）：主码列名数据类型宽度小数位空否取值范围备

14、 注Pkpnochar2N零件号pnamechar10N零件名称colorchar2Y颜色weightsmallintY大于0重量J（工程项目表）：主码列名数据类型宽度小数位空否取值范围备 注Pkjnochar2N工程项目号jnamechar10N工程项目名称citychar10Y所在城市SPJ（供应情况表）：主码列名数据类型宽度小数空否外码参照关系取值范围备 注PksnoChar2NFkS供应商号pnoChar2NFkP零件号jnoChar2NFkJ工程项目号qtysmallintYx0数量4、修改表结构，具体要求如下：(1) 将表course的cname列的数据类型改为varchar(40

15、).(2) 为表student增加一个新列: birthday(出生日期), 类型为datetime, 默认为空值.(3) 将表sc中的grade列的取值范围改为小于等于150的正数.(4) 为Student表的“Sex”字段创建一个缺省约束，缺省值为男(5)为“Sdept”字段创建一个检查约束，使得所在系必须是CS、MA或IS之一。(6)为Student表的“Sname”字段增加一个唯一性约束(7)为SC表建立外键,依赖于Student表的fk_S_c约束。(8)禁止启用Student表的“Sdept”的CHECK约束ck_student。5、分别建立以下索引(如果不能成功建立,请分析原因)

16、(1) 在student表的sname列上建立普通降序索引.(2) 在course表的cname列上建立唯一索引.(3) 在sc表的sno列上建立聚集索引.(4) 在spj表的sno(升序), pno(升序)和jno(降序)三列上建立一个普通索引.Transact-SQL提示: 1. 建立表, 修改表, 建立索引需具有create table的权限. 2. 创建基本表的SQL语句是CREATE TABLE . 简单语法格式: CREATE TABLE 表名 ( 列名 数据类型 default 缺省值 not null ，列名 数据类型 default 缺省值 not null ，primary

17、 key（列名 ，列名 ） ，foreign key （列名 ，列名 ） references 表名 （列名 ，列名 ） ，check（条件） );一般语法格式:CREATE TABLE database_name.owner. table_name(| column_name | ,n )ON filegroup | DEFAULT TEXTIMAGE_ON filegroup | DEFAULT :=column_name data_typeDEFAULT constant_expression| IDENTITY (seed, increment ) ROWGUIDCOL n参数说明：d

18、atabase_name.owner. table_name：定义表的名字，表名的长度不得超过128个字节，如果是临时表，则表名不能超过116个字符。：列的定义。column_name：列的名字。列的命名必须遵守有关数据库对象的命名规则。data_type：列的数据类型。DEFAULT constant_expression：定义该列的默认值。IDENTITY：定义该列是一个标识列。当一个新的数据行插入表中的时候，SQL Server2000为标识列提供一个唯一的、递增的数值。在一张表格中，只能定义一个标识列。在定义标识列时，必须同时定义起始值和增量。Seed：定义标识列的起始值。所谓起始值就

19、是插入表的第一行的数据的标识列的值。Increment：定义标识列的增量。所谓增量就是插入表的最近一行相对与前一行标识列的数据值的增量。ROWGUIDCOL：定义该列是一个行全局唯一的标识列。在一张表中只有一个唯一标识符列可以被定义为ROWGUIDCOL。column_constraint：定义与列相关联的约束。table_constraint：定义对表的约束。ON filegroup | DEFAULT ：定义将表存储在某一个指定的文件组中，DEFAULT表示将表存储在默认文件组中。TEXTIMAGE_ON filegroup | DEFAULT ：如果表中有ntext、text或image

20、类型的数据，则将这些数据存储在某一个指定的文件组里。 3. 修改表结构的SQL语句是ALTER TABLE, 修改的内容包括：修改、增加、删除列或约束、使约束和触发器无效等。简单语法格式:alter table add / 增加新列 drop / 删除约束 drop column / 删除列 alter column ; /修改列定义 其中列级完整性约束包括:空值约束 NOT NULL和NULL主关键字约束 PRIMARY KEY唯一性约束 UNIQUE参照完整性约束 FOREIGN KEY一般语法格式:ALTER TABLE table ALTER COLUMN column_name ne

21、w_data_type NULL| NOT NULL|ADD |DROP ROWGUIDCOL |ADD | column_name AS computed_column_expression ,n |ADD ,n |DROP CONSTRAINT constraint_name |COLUMN column ,n |CHECK |NOCHECK CONSTRAINT ALL | constraint_name ,n |ENABLE |DISABLE TRIGGER ALL |trigger_name ,n参数说明：ALTER COLUMN：修改已经存在的列的属性。ADD |DROP ROWG

22、UIDCOL：将指定列定义成ROWGUIDCOL，或者删除该列的ROWGUIDCOL属性。ADD | column_name AS computed_column_expression,n：增加新的列。ADD ,n:定义新的表约束。DROP CONSTRAINT constraint_name |COLUMN column ,n：删除约束或删除列。CHECK |NOCHECK CONSTRAINT ALL | constraint_name ,n：使所有约束或者指定的约束有效或失效。例：使cnst_example表中的约束salary_cap失效。ALTER TABLE cnst_exampl

23、e NOCHECK CONSTRAINT salary_capENABLE |DISABLE TRIGGER ALL |trigger_name ,n：使所有或部分触发器有效或无效。例：使用T_SQL语句对学生表进行各种修改。向表中添加新的字段：在学生表中添加一个“class”字段，数据类型为字符型。ALTER TABLE student add class char(10)删除表中的旧列：将学生表中的“class”字段删除。ALTER TABLE student DROP column class更改表中的约束：删除某列的约束：将SC表中的外码约束FK_sc_sno删除。ALTER TABL

24、E sc DROP FK_sc_sno将Student表中的主码约束PK_student_sno删除。ALTER TABLE student DROP PK_student_sno为某列添加约束：为Student表的“Ssex”字段创建一个缺省约束，缺省值为男。ALTER TABLE student ADD constraint def_ssex DEFAULT 男 FOR ssex更改Student表中的“sno”字段的宽度为10及非空约束。ALTER TABLE student ALTER COLUMN sno char(10) NOT NULL为Student表添加一个主码约束PK_st

25、udent_sno。ALTER TABLE student ADD CONSTRAINT PK_student_sno PRIMARY KEY(sno)为SC表添加一个外码约束FK_sc_sno。ALTER TABLE sc ADD CONSTRAINT FK_sc_sno FOREIGN KEY (sno) REFERENCES student(sno) 给学生表增加“grade”字段并加上CHECK约束，让其不可以大于100。ALTER TABLE student ADD grade int Constraint ch_grade check(grade100)Exec sp_help s

26、tudent给学生表中添加“birthday”字段，并且这个日期不能在录入当天的日期之后。Alter table student Add birthday DATETIME NULL CONSTRAINT ch_birthday CHECK (birthdaygetdate()添加具有默认值的可为空的列：在学生表中加入“matriculationday”字段，并且这一字段的默认值为录入当天的日期。Alter table student Add matriculationday smalldatetime NULL Constraint adddateflt Default getdate()4.建立索引的SQL语句是CREATE INDEX, 语法格式: CREATE UNIQUE CLUSTERED INDEX ON ( ASC | DESC , ASC | DESC )其中 UNIQUE指出是唯一索引, CLUSTERED指出是聚集索引。实验三 SQL数据查询一、实验目的 熟练掌握使用T-SQL语句进行单表查询，尤其要熟练掌握GROUP BY子句、HAVING 子句和集函数； 熟练掌握使用SQL标准语句和T-SQL扩展语句进行连接查询。 熟练掌握使用IN、比较符、ANY或ALL和EXISTS操作符进行嵌套查询操作。二、实验内容 完成如下单表查询：