1、数据库系统原理及技术实验报告数据库系统原理实验报告 实验1 SQL Server管理工具的使用一、目的与要求1掌握SQL Server服务器的安装2掌握企业管理器的基本使用方法3掌握查询分析器的基本使用方法4掌握服务管理器的基本使用方法5对数据库及其对象有一个基本了解6. 学会使用SQL Server联机丛书和Transact-SQL帮助二、实验准备1了解SQL Server各种版本安装的软、硬件要求2了解SQL Server支持的身份验证模式3了解SQL Server各组件的主要功能4对数据库、表和数据库对象有一个基本了解5了解在查询分析器中执行SQL语句的方法三、实验内容1安装SQL Se
2、rver 2000 根据软硬件环境，选择一个合适版本的SQL Server 2000。2利用企业管理器访问系统自带的pubs数据库。（1）启动SQL Server服务管理器。通过“开始=程序=Microsoft SQL Server=服务管理器”打开“SQL Server服务管理器”，启动“SQL Server服务管理器”，并记录当前运行的服务器名。图1.1 启动SQL Server服务管理器（2）启动企业管理器。通过“开始=程序=Microsoft SQL Server=企业管理器”打开“SQL Server Enterprise Manager”图1.2 启动企业管理器（3）在企业管理器的
3、树形目录中展开数据库，找到pubs并展开，则列出该数据库的所有对象，如表、视图、存储过程、默认和规则等。（4）选中“表”，将列出pubs数据库的所有表（包括系统表和用户表），在此以用户表titles为例，选中该表，单击鼠标右键，弹出快捷菜单，执行“打开表返回所有行”菜单项，打开该表，查看其内容。(5)选中表titles，单击鼠标右键，弹出快捷菜单，执行“设计表”菜单项，查看其结构，并记录该表的结构。(6)用同样的方法查看pubs数据库中的其它用户表，并记录这些表的结构。3利用查询分析器访问系统自带的pubs数据库的表（1）启动SQL Server查询分析器通过“开始=程序=Microsoft
4、SQL Server=查询分析器”打开“SQL Server 查询分析器”，并以系统管理员sa的身份连接到SQL Server。图1.3 连接SQL Server服务器(2) 使用系统存储过程（sp_tables和sp_help ）查看系统自带的pubs数据库中的表titles的结构信息在查询分析器的命令窗口中键入如下SQL批命令：use pubsgosp_tables titlesgosp_help titlesgo按“F5”键或者单击工具栏中的“”按钮执行SQL查询图1.4 在查询分析器中查看表的结构信息(3)利用SQL命令查看系统自带的pubs数据库中的表titles的行信息select
5、 * from titlesgo图1.5 在查询分析器中执行SQL查询4在查询分析器中打开“帮助”菜单，使用SQL Server联机丛书和Transact-SQL帮助。实验2 数据定义一、目的和要求1了解SQL Server数据库的逻辑结构和物理结构。2了解表的结构特点。3了解SQL Server的基本数据类型。4学会在企业管理器中创建数据库和表。5学会使用T-SQL语句创建数据库和表。6学会定义索引二、实验准备1要明确能够创建数据库的用户必须是系统管理员，或是被授权使用CREATE DATABASE语句的用户。2创建数据库必须要确定数据库名、所有者（即创建数据库的用户）、数据库大小（最初的大
6、小、最大的大小、是否允许增长及增长的方式）和存储数据的文件。3确定数据库包含哪些表以及包含的各表的结构，还要了解SQL Server的常用数据类型，以创建数据库的表。4了解常用的创建数据库和表的方法。5. 了解聚集索引和非聚集索引三、实验内容设有一学籍管理系统，其数据库名为“STUDENT”，初始大小为 10MB，最大为50MB，数据库自动增长，增长方式是按5比例增长；日志文件初始为2MB，最大可增长到5MB，按1MB增长。数据库的逻辑文件名为“student_data”, 物理文件名为“student_data.mdf，存放路径为“E:sql_data”。日志文件的逻辑文件名为“studen
7、t_log”, 物理文件名为“student_log.ldf”，存放路径为“E:sql_data”。各数据表的结构如下：表2.1 student表（学生信息表）字段名称类 型宽 度允许空值主 键说 明snochar8NOT NULL是学生学号snamechar8NOT NULL学生姓名sexchar2NULL学生性别nativechar20NULL籍贯birthdaysmalldate4NULL学生出生日期dnochar6NULL学生所在院系编号（外键）spnochar8NULL专业代码（外键）classnochar4NULL班级号entimesmalldate4NULL学生入校时间homev
8、archar40NULL学生家庭住址telvarchar40NULL学生联系电话表2.2 course表（课程信息表）字段名称类 型宽 度允许空值主 键说 明cnochar10NOT NULL是课程编号spnochar8NULL专业代码（外键）cnamechar20NOT NULL课程名称ctnotinyint1NULL课程类型编号（外键）experimenttinyint1NULL实验时数lecturetinyint1NULL授课学时semestertinyint1NULL开课学期credittinyint1NULL课程学分表2.3 student_course表（学生选课成绩表）字段名称类
9、 型宽 度允许空值主 键说 明snochar8NOT NULL是学生学号tcidsmallint2NOT NULL是上课编号scoretinyint1NULL学生成绩表2.4 teacher表（教师信息表）字段名称类 型宽 度允许空值主 键说 明tnochar8NOT NULL是教师编号tnamechar8NOT NULL教师姓名sexchar2NULL教师性别birthdaysmalldate4NULL教师出生日期dnochar6NULL教师所在院系编号（外键）pnotinyint1NULL教师职务或职称编号homevarchar40NULL教师家庭住址zipcodechar6NULL邮政编
10、码telvarchar40NULL联系电话emailvarchar40NULL电子邮件表2.5 teacher_course表（教师上课课表）字段名称类 型宽 度允许空值主 键说 明tcidsmallint2NOT NULL是上课编号tnochar8NULL教师编号（外键）spnochar8NULL专业代码（外键）classnochar4NULL班级号cnochar10NOT NULL课程编号（外键）semesterchar6NULL学期schoolyearchar10NULL学年classtimevarchar40NULL上课时间classroomvarchar40NULL上课地点weekt
11、imetinyint1NULL每周课时数1创建数据库(1) 使用企业管理器创建数据库图2.1 新建数据库图2.2 数据库属性对话框图2.3 建立student数据库(2) 使用SQL语句创建数据库CREATE DATABASE studentON (NAME=student_data,FILENAME=E:sql_datastudent_data.mdf ,SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=10%)LOG ON(NAME=student_log,FILENAME=E:sql_datastudent_log.ldf ,SIZE=2MB,MAXSIZE=5MB,F
12、ILEGROWTH=1MB)图2.4 CREATE DATABASE语句创建数据库2. 创建表(1) 使用企业管理器创建表图2.5 新建表图2.6 表设计器图2.7 插入删除字段(2) 使用SQL语句创建表CREATE TABLE student( sno char(8) PRIMARY KEY, -学号(主键) sname char (8) NOT NULL, -姓名 sex char(2), -性别 native char(20), -籍贯 birthday smalldatetime, -出生日期 dno char(6), -所在院系编号(外键) spno char(8), -专业代码(
13、外键) classno char(4), -班级号 entime smalldatetime, -入校时间 home varchar (40), -家庭住址 tel varchar (40) -联系电话)CREATE TABLE course( cno char (10) PRIMARY KEY, -课程编号(主键) cname char (20) NOT NULL, -课程名称 spno char (8), -专业代码 ctno tinyint, -课程类型编号(外键) lecture tinyint, -理论学时 experiment tinyint, -实验时数 semester tin
14、yint, -开课学期 credit tinyint -课程学分)图2.8 创建student表图2.9 创建course表CREATE TABLE teacher( tno char (8) PRIMARY KEY, -教师编号(主键) tname char (8) NOT NULL, -教师姓名 sex char (2), -教师性别 birthday smalldatetime, -出生日期 dno char (6), -所在院系编号(外键) pno tinyint, -职务或职称编号 home varchar(40), -家庭住址 zipcode char (6), -邮政编码 tel
15、 varchar (40), -联系电话 email varchar (40) -电子邮件)图2.10 创建teacher表CREATE TABLE student_course ( sno char(8), -学生学号 tcid smallint, -上课编号 score tinyint , -学生成绩 PRIMARY KEY ( sno, -主键 tcid -主键 )图2.131 创建student_course表CREATE TABLE teacher_course ( tcid smallint PRIMARY KEY, -上课编号(主键) tno char (8), -教师编号(外键
16、) spno char (8), -专业代码(外键) classno char (4), -班级号 cno char (10) NOT NULL, -课程编号(外键) semester char (6), -学期 schoolyear char (10), -学年 classtime varchar(40), -上课时间 classroom varchar(40), -上课地点 weektime tinyint -每周课时数)图2.12 创建teacher_course表3. 修改表(1) 使用企业管理器修改表图2.13 student表设计器(2) 使用SQL语句修改表ALTER TABLE
17、 teacherALTER COLUMN email varchar(50) NULLEXEC sp_rename teacher.email, 电子邮件, COLUMN图2.14 ALTER TABLE语句修改表(3) 使用SQL语句删除表DROP TABLE teacher3. 使用SQL语句建立与删除索引(1) 为表student的列sname建立聚集索引Create clustered index myindex on student(sname)(2) 删除表student的列sname的聚集索引myindexDrop index student.myindex(3)为student
18、表按姓名升序建立唯一索引（取名为StuSname） create unique index StuSname on student(sname ASC) (4)为Student_course表按学号升序和课程号降序建立唯一索引（取名为SCSnoCno）。 create unique index SCSnoCno on Student_course (sno ASC,cno DESC) 四、实验作业题1数据库分析（1）创建用于员工考勤的数据库，数据库名为YGKQ， 初始大小为 10MB，最大为50MB，数据库自动增长，增长方式是按5比例增长；日志文件初始为2MB，最大可增长到5MB，按1MB增长
19、。数据库的逻辑文件名和物理文件名均采用默认值。（2）数据库YGKQ包含员工的信息和缺勤类型信息，其YGKQ包含下列2个表：JBQK：员工基本情况表；QQLX：缺勤信息表。各表的结构见表 2.1和表2.2所示：表 2.1 JBQK（员工基本情况表结构）字段名字段类型字段宽度说明员工号姓名缺勤时间缺勤天数缺勤类型缺勤理由CHARCHARDATETIMEINTCHARCHAR48420主键表 2.2 QQLX（缺勤类型信息表结构）字段名字段类型字段宽度说明缺勤类型缺勤名称缺勤描述CHARCHARCHAR4820主键各表的数据内容见表 2.3和2.4所示： 表 2.3 JBQK （员工基本情况记录）职
20、工号姓名缺勤时间缺勤天数缺勤类型缺勤理由001李华2006-09-0331事假002张敏2006-09-1222病假003付丽2006-09-0353旷工004张晓华2006-09-1021事假005邓刚2006-09-1612病假表 2.4 QQLX （缺勤类型信息记录）缺勤类别缺勤名称缺勤描述1事假本人必须提前 1天申请，1-2天由部门准许，2天以上由经理批准2病假1-2天由部门准许，2天以上由经理批准3旷工无故不到者按旷工处理4迟到在规定上班时间 1小时后到岗2在企业管理器中创建和删除数据库和数据表（1）在企业管理器中创建的YGKQ数据库。（2）在企业管理器中删除YGKQ数据库。（3）在
21、企业管理器中分别创建表JBQK和QQLX。（4）在企业管理器删除创建的JBQK和QQLX表。3在查询分析器中创建数据库和数据表 （1）用T-SQL语句创建数据库YGKQ。（2）使用T-SQL语句创建JBQK和QQLX表。4创建索引(1)为数据库YGKQ中JBQK表的“缺勤天数”字段创建一个非聚集索引，其名称为kczccj_index。(2)使用系统存储过程sp_helpindex查看JBQK表上的索引信息。(3)用SQL语句删除kcvc_index。实验3 数据更新（插入、修改和删除）一、目的和要求1学会在企业管理器中对表进行插入、修改和删除数据操作。2学会使用T-SQL语句对表进行插入、修改
22、和删除数据操作。3了解T-SQL语句对表数据库操作的灵活控制功能。二、实验准备1了解表的更新操作，即数据的插入、修改和删除，对表数据的操作可以在企业管理器中进行，也可以由T-SQL语句实现。2掌握T-SQL中用于对表数据进行插入（INSERT）、修改（UPDATE）和删除（DELETE或TRANCATE TABLE）命令的用法。3了解使用T-SQL语句在对表数据进行插入、修改及删除时，比在企业管理器中操作表数据灵活，功能更强大。三、实验内容1在企业管理器中对数据库student中的表进行插入、删除数据的操作。(1)在企业管理器的树形目录中展开数据库student，找到表并展开，则列出该数据库的
23、所有对象，如表、视图、存储过程、默认和规则等。(2)选中“表”，将列出student数据库的所有表（包括系统表和用户表），在此以用户表student为例，选中该表，单击鼠标右键，弹出快捷菜单，执行“打开表返回所有行”菜单项，打开该表，并向表中录入若干条记录。图3.1 向表中插入数据(3) 在企业管理器中删除数据库student中的表数据。在打开的表student中，选定将要删除的行，单击鼠标右键，弹出快捷菜单，执行“删除”菜单项，在打开的删除对话框中回答“是”。图3.2 删除表中的数据2. 使用SQL语句插入、修改和删除表数据首先启动SQL Server查询分析器，并以系统管理员sa的身份连接
24、到SQL Server。依次执行下列SQL语句：INSERT INTO studentVALUES(02080001,李平,男,江西, 1985-1-2,610000, 080605,0201, 2002-9-1,江西省南昌市上海路11,8305670)图3.3 INSERT语句向表中插入数据UPDATE studentSET native=湖南WHERE native=江西图3.4 UPDATE语句修改表中数据DELETE studentWHERE native=湖南图3.5 DELETE语句删除表中数据四、实验作业题根据某一单位的员工考勤情况，对实验2所创建数据库YGKQ的各表进行数据更新
25、操作。实验4 数据查询一、目的与要求1掌握SELECT语句的基本语法。2掌握子查询的表示。3掌握连接查询的表示。4掌握SELECT语句的统计函数的作用和使用方法。5掌握SELECT语句的GROUPBY和ORDERBY子句的作用和使用方法。二、实验准备1了解SELECT语句的基本语法格式。2了解SELECT语句的执行方法。3了解子查询的表示方法。4了解SELECT语句的统计函数的作用。5了解SELECT语句的GROUP BY和ORDER BY子句的作用。三 、实验内容首先启动SQL Server查询分析器，并以系统管理员sa的身份连接到SQL Server。以下所有查询都在查询分析器中执行。1.
26、 简单查询SELECT sno,sname,sex,birthday FROM student图4.1 查询结果SELECT sno,sname,CAST(GETDATE()-birthday AS int)/365 AS age FROM student图4.2 查询结果中年龄列名为别名ageSELECT sno,sname,sex,birthdayFROM studentWHERE dno=610000图4.3 查询结果SELECT sno,sname,birthday FROM student WHERE spno=080605 AND classno=0201图4.4 查询结果SELE
27、CT sno,sname,sex,birthdayFROM studentWHERE birthday1985/01/01图4.5 查询结果SELECT * FROM teacher WHERE pno4图4.6 查询结果SELECT sno,snameFROM studentWHERE sname LIKE 陈%图4.7 查询结果SELECT sno,sname FROM studentWHERE sname LIKE 陈_ _图4.8 查询结果SELECT sno,sname FROM student WHERE sname LIKE 陈李% 图4.9 查询结果SELECT sno,sname,sex,birthday FROM student WHERE (dno=610000) AND (birthday1985/01/01)图4.10 查询结果SELECT *FROM studentWHERE native NOT LIKE
