数据库原理实验指导书-参考答案.doc

数据库原理与应用实验指导/学年 第学期姓 名：_学 号：_班 级：_指导教师：_实验一 SQL Server使用初步一、实验目的1、熟悉SQL Server2000的组成与基本功能。2、掌握SQL Server2000的登录与注册。3、掌握SQL Server2000企业管理器的使用方法。4、熟悉查询分析器的基本使用。二、实验预习1、什么是数据库管理系统DBMS？你所知道的DBMS有哪些？2、SQL Server 2000(2005)的安装步骤？三、实验容和要求1、注册服务器基本步骤：（1）打开企业管理器（2）右击SQL Server组新建SQL Server注册（3）添加可用的服务器（实际数据库服务器的名称或IP地址）（4）选择身份验证模式（选“系统管理员分配给我的登录信息”）（5）输入正确的登录名和密码（6）选择SQL Server组（选“在现有的SQL Server组中添加SQL Server”）（7）完成注册若注册成功，则显示注册成功的信息。2、连接SQL Server服务器（1）右键单击上面注册的数据库服务器，选择连接，建立与数据库服务器的连接。观察连接后服务器图标的变化；（2）右键单击选择编辑SQL Server注册属性，观察已注册数据库服务器的属性信息；（3）右键单击选择删除SQL Server注册。为保证数据库的安全性，使用完毕自己的数据库后，可采取删除的方式，断开与数据库的连接；（4）重复注册服务器的步骤，再次建立与数据库的连接；3、熟悉企业管理器（1）单击建立的服务器连接，观察服务器的7个项目，写出它们的名称。通过查看联机帮助，总结7个项目的基本功能。（2）单击数据库，观察Northwind数据库下的11个项目，写出项目名称，通过联机帮助了解它们的基本功能。（3）查看Northwind的表项目，单击表，观察表的名称、所有者、类型以与创建日期。回答：§ 这些表的所有者有哪几种？§ 这些表的类型有哪几种？§ 选择表Employees，在右键菜单中选择打开表->返回所有行，观察表中的数据，说出这些数据的实际含义。观察其他用户类型的表，你还能说出它们数据的实际含义吗？（4）查看Northwind的视图项目，单击视图，观察视图的名称、所有者、类型以与创建日期。选择视图Product Sales for 1997，同上面观察表中数据的方法一样，观察视图中的数据，说出这些数据的意义。（5）查看Northwind的用户项目，单击用户。回答：§ 有哪类用户？查看它们的属性对话框，它们的角色和权限是否相同？§ 通过联机帮助，写出dbo、Guest用户的区别。（6）查看Northwind的角色项目，单击角色。回答：§ 有哪些角色类型？通过查看联机帮助，写出它们各自的含义和作用。（7）查看Northwind的安全性项目，单击项目中的登录，观察不同的登录名称、类型、服务器访问、默认数据库，找到你自己的登录名称，右键单击打开属性对话框，观察对话框中的设置，写出登录属性对话框中的设置作用。4、熟悉查询分析器（1）打开查询分析器。基本步骤：§ 单击注册的数据库服务器，确定服务器已经连接；§ 在主菜单“工具”中选择“SQL查询分析器”；§ 在打开的查询分析器窗口中，左侧的对象浏览器为注册的服务器，在工具栏的数据库选择中选择Northwind数据库为当前数据库；右侧窗口为查询窗口。（2）分别在查询窗口中输入如下查询语句，观察查询结果，写出结果的数据记录个数：查询语句1：SELECT *FROM ShippersORDER BY CompanyName结果记录数：查询语句2：SELECT FirstName, HomePhoneFROM Northwind.dbo.EmployeesORDER BY FirstName ASC结果记录数：查询语句3：SELECT ROUND( (UnitPrice * .9), 2) AS DiscountPriceFROM ProductsWHERE ProductID = 58结果记录数：查询语句4：SELECT OrderID, DATEDIFF(dd, ShippedDate, GETDATE() ) AS DaysSinceShippedFROM Northwind.dbo.OrdersWHERE ShippedDate IS NOT NULL结果记录数：查询语句5：SELECT ProductID, ProductNameFROM Northwind.dbo.ProductsWHERE CategoryID = 1 OR CategoryID = 4 OR CategoryID = 5结果记录数：四、实验小结五、评阅成绩实验预习20%实验过程20%实验结果30%实验报告30%总成绩实验二 数据定义一、实验目的1、掌握SQL数据定义功能：数据库定义、表的定义、索引定义。2、掌握利用企业管理器和SQL语句定义表、索引的方法。二、实验预习1、SQL中基本表定义语句格式：2、SQL中修改基本表语句格式：三、实验容和要求1、在企业管理器中，利用菜单操作的方式在各自的数据库中建立如下四个基本表：（1）供应商表S：列名说明数据类型约束SNO供应商号CHAR(6)PRIMARY KEYSNAME供应商名VARCHAR(20)NOT NULLSTATUS供应商状态VARCHAR(50)CITY所在城市VARCHAR(50)（2）零件表P：列名说明数据类型约束PNO零件号CHAR(6)PRIMARY KEYPNAME零件名VARCHAR(20)NOT NULLCOLOR颜色CHAR(2)WEIGHT重量NUMERIC(9,2)CHECK(WEIGHT>0 AND WEIGHT<=100)（3）工程项目表J：列名说明数据类型约束JNO项目号CHAR(6)PRIMARY KEYJNAME项目名VARCHAR(20)NOT NULLCITY城市VARCHAR(50)（4）供应情况表SPJ：列名说明数据类型约束SNO供应商号CHAR(6)NOT NULLPNO零件号CHAR(6)NOT NULLJNO项目号CHAR(6)NOT NULLQTY供应数量SMALLINTDEFAULT 1002、用CREATE语句建立如下三个表，并写出相应的语句。（1）学生表Student：列名说明数据类型约束SNO学号CHAR(7)主码SNAMECHAR(10)NOT NULLSSEX性别CHAR(2)取“男”或“女”SAGE年龄SMALLINT取值15-45SDEPT所在系VARCHAR(20)默认“计算机系”语句：CREATE TABLE Student(Sno char(7)PRIMARY KEY,Sname char(10)not null,Ssexchar(2)CHECK(Ssex='男' or Ssex='女') ,Sagesmallint CHECK(Sage>=15 and Sage<=45),Sdept char(20)DEFAULT '计算机系')（2）课程表Course：列名说明数据类型约束CNO课程号CHAR(10)主码CNAME课程名VARCHAR(20)NOT NULLCCREDIT学分SMALLINT大于0SEMSTER学期SMALLINT大于0PERIOD学时SMALLINT大于0语句：CREATE TABLE Course(Cno char(10) PRIMARY KEY,Cname varchar(20) NOT NULL,Ccreditsmallint check(ccredit>0),semster smallint check(semster >0),period smallint check(period>0)（3）选课表Sc：列名说明数据类型约束SNO学号CHAR(7)主码，引用Student的外码CNO课程号CHAR(10)主码，引用Course的外码GRADE成绩SMALLINT大于0语句：CREATE TABLE SC(Sno char(7) , Cno char(10),Grade smallint check(grade>=0), PRIMARY KEY (Sno,Cno),FOREIGN KEY (Cno) REFERENCES Course(Cno),FOREIGN KEY (Sno) REFERENCES Student(Sno)3、利用SQL语句对表结构进行修改。（1）为零件表P增加一个规格(GUIGE)列，数据类型为字符，长度50；Alter Table P Add GUIGE char(50)（2）修改课程表Course的CNAME属性列的类型为VARCHAR(30)；Alter Table Course Alter Column CNAME varchar(30)（3）为供应情况表SPJ添加参照完整性约束；SPJ的SNO列参照S表SNO列Alter Table SPJ Add Foreign Key (SNO) References S(SNO)SPJ的PNO列参照P表PNO列Alter Table SPJ Add Foreign Key (PNO) References P(PNO)SPJ的JNO列参照J表JNO列Alter Table SPJ Add Foreign Key (JNO) References J(JNO)（4）删除零件表P的规格(GUIGE)列Alter Table P Drop Column GUIGE4、利用企业管理器向表中添加数据（1）供应商表S：SNOSNAMESTATUSCITYS1精益20S2盛锡10继续往表中增加一条记录：四个字段的数据分别是S1,红，30,，会出现什么情况？为什么？（2）零件表P：PNOPNAMECOLORWEIGHTP1螺母红12P2螺栓绿17P3螺丝刀蓝105.5是否能够正常输入三条数据？如果否，问题出在哪里，为什么？（3）工程项目表J：JNOJNAMECITYJ1一汽J2半导体厂J3第三条记录是否能够正常输入，若否，会出现什么问题，为什么？（4）供应情况表SPJ：SN0PNOJNOQTYS1P1J1200S1P1J3S2P2J4输入过程中是否会遇到问题，若有，出在哪，为什么？四、实验小结五、评阅成绩实验预习20%实验过程20%实验结果30%实验报告30%总成绩实验三 数据查询一、实验目的1、掌握查询语句的基本组成和使用方法2、掌握常用查询技巧二、实验预习1、SQL中查询语句格式：2、连接查询有哪些不同的连接方式？有什么特点。三、实验容和要求1、按照下表中的容，在企业管理器中为数据库表输入相应的数据。学生表：StudentSnoSnameSsexSageSdept9512101勇男19计算机系9512103王敏女20计算机系9521101莉女22信息系9521102吴宾男21信息系9521103海男20信息系9531101钱小平女18数学系9531102王大力男19数学系课程表：CourseCnoCnameCcreditSemsterPeriodC01计算机导论313C02VB434C03计算机网络474C04数据库基础664C05高等数学818选课表：SCSnoCnoGrade9512101C03959512103C03519512101C05809512103C05609521101C05729521102C05809521103C05459531101C05819531102C05949512101C01NULL9531102C01NULL9512101C02879512101C04762、完成以下查询（1）查询全体学生的信息。select * from student（2）查询“信息系”学生的学号，和出生年份。select Sno,Sname,2009-Sage as Birthyear from student where Sdept='信息系'（3）查询考试不与格的学生的学号。select Distinct Sno from SC where Grade<60（4）查询无考试成绩的学生的学号和相应的课程号。select Sno,Cno from SC where Grade is null（5）将学生按年龄升序排序。select * from student order by Sage asc（6）查询选修了课程的学生的学号和。Select Sno,Sname from Student Where Sno in (Select Sno From Sc)或：select distinct student.Sno,Sname from student,SC where student.Sno=SC.Sno （7）查询年龄在20-23岁之间的学生的，系，年龄。select Sname,Sage,Sdept from student where Sage between 20 and 23（8）查询同时选修了“计算机导论”，“高等数学”课程的学生的学号，。select student.Sno,Sname from student where NOT EXISTS( select* from course where Cname in('高等数学','计算机导论') AND NOT EXISTS(select* from SC where Sno=student.Sno and Cno=courseo)或：select student.Sno,Sname from student,sc,Course where student.sno=sc.sno and sco=courseo andame='高等数学' and student.sno in (select sno from sc whereo=(selecto from course whereame='计算机导论')或：select student.Sno,Sname from student where sno in (select sno from sc whereo=(selecto from course whereame='高等数学') and sno in (select sno from sc whereo=(selecto from course whereame='计算机导论')（9）查询姓“”的学生的基本信息。select * from student where Sname like '%'（10）查询“95211”班学生的选课情况，要求输出学号，课程名，成绩，按照学号升序排序。select student.Sno,Sname,Cname,Grade from student,SC,course where student.Sno=SC.Sno and courseo=SCo and student.Sno like '95211%' order by student.Sno或：select student.Sno,Sname,Cname,Grade from student,SC,course where student.Sno=SC.Sno and courseo=SCo and left(student.sno,5)= '95211' order by 1（11）查询选修了课程的学生的总人数。select count(distinct sno) from SC（12）查询选修了“C05”课程的的学生成绩单，要求输出学号，成绩，结果按班级升序，成绩降序排列。select student.Sno,Sname,Grade from student,SC where student.Sno=SC.Sno and Cno='C05' order by left(student.Sno,5) asc,Grade desc（13）统计各门课程的成绩，要求输出课程代号，课程名，平均成绩，选修人数。（成绩为NULL值的不统计）select courseo,Cname,avg(Grade),count(Sno) from course,Sc where courseo=SCo and Grade is not null group by Courseo,Cname（14）统计各门课程的不与格人数，要求输出课程代号，课程名，不与格人数。select Courseo,Cname,count(Sno) from SC,Course where SCo=courseo and Grade<60 group by Courseo,Cname（15）查询选修平均成绩在75分以上的学生的学号，所在系。select sc.sno,sname,sdept from student,sc where student.sno=sc.snogroup by sc.sno,sname,sdept having avg(grade)>75或：selectsno,sname,sdept from student where Sno in (Select Sno From SC Group By Sno Having Avg(Grade)>75)（16）查询与“王大力”同一个系的学生的基本信息select * from student where sdept in(select sdept from student where sname='王大力')（17）查询选修平均分高于所有学生平均分的学生的学号，并按学号升序排列。select student.Sno from student,SC where student.Sno=SC.Sno group by student.Sno having avg(Grade)>(select Avg(Grade) from SC) order by student.Sno asc（18）查询未选修“VB”或“数据库基础”两门课的学生的学号，系名。（要求用嵌套查询）select sno,sname,sdept from student where sno not in(select sno from sc whereo in(selecto from course whereame in('VB','数据库基础')或：select sno,sname,sdept from Student where exists(select * from Course whereame='VB' and not exists(select * from SC where sno=Student.sno ando=Courseo and Courseame!='数据库基础')（19）查询选修了全部课程的学生的学号，系名。select sno,sname,sdept from student where not exists(select * from course where not exists (select * from sc where sno=student.sno ando=courseo ) )或：select sno,sname,sdept from student where sno in (select sno from sc group by sno having count(cno)=(select coount(*) from course)（20）输出“高等数学”课程成绩前三名的学生的学号，系名select top 3 student.sno,sname,sdept from student,sc,course where student.sno=sc.sno and sco=courseo andame='高等数学' order bygrade desc四、实验小结五、评阅成绩实验预习20%实验过程20%实验结果30%实验报告30%总成绩实验四 数据更新一、实验目的1、掌握SQL语言的数据更新操作2、掌握SQL Server 2000企业管理器的数据导入和导出功能 二、实验预习1、数据插入语句格式：2、数据修改语句格式：3、数据删除语句格式：4、SQL Server中可进行批量数据导入和导出，可支持哪些格式的数据导入导出？（举常见格式类型）三、实验容和要求（执行操作后，将语句填写在下面的空白处）1、插入数据（1）在学生表Student中插入数据：Sno：9512102Sname:晨 Ssex：男Sage:20Sdept：计算机系insert into Studentvalues ('9512102','晨','男',20,'计算机系')（2）在课程表Course中插入数据：Cno：C06Cname：数据结构Ccredit：5Semster：4insert into Course(cno,cname,ccredit,semster)values ('C06','数据结构',5,4)或：insert into Course values ('C06','数据结构',5,4，null)（3）在选课表SC中插入95211班学生选修C04的选课信息。提示：插入的数据的Sno从Student表中查询而来，插入的Cno为“C04”insert into SC(sno,Cno)select sno,'c04' from Student where sno like '95211%'（4）查询高等数学的成绩，包括学号，成绩，并按学号升序排序。将查询的结果输出到一个名为gs_cj的表中。select sno,grade into gs_cj from sc,course where sco=courseo andame='高等数学'order by sno （5）将SC表中“C05”课程的选课记录输出至一个新表中，表名为Gs01。select * into Gs01from sc whereo='c05'2、修改数据（1） 将所有学生的年龄增加1岁。update student set sage=sage+1（2）修改“9512101”学生的“C01”课程成绩为85。update sc set grade=85where sno='9512101' ando='c01'（3）修改“9531102”学生的“C01”课程成绩为70。update sc set grade=70where sno='9531102' ando='c01'（4）将所有平均分为75分以上的学生的各门课成绩在原来基础上增加1。update sc set grade=grade*1.01where sno in (select sno from sc group by sno having avg(grade)>=75)3、删除数据（1）删除“9531102”学生“C05”课程的成绩记录delete from sc where sno='9531102' ando='c05'（2）删除所有课程为“C05”的选课记录delete from sc whereo='c05'4、数据的导出（1）将数据库中的S,P,J,SPJ表导出为一个ACCESS数据库，名为DB_SPJ。（2）Student表中数据导出到一个文本文件，用,作为数据项的分隔符。（3）将Course表中数据导出到一个Excel文件中。5、批量增加数据（1）利用查询，将其他表中的数据增加到需要的数据表中。将Gs01表中的数据添加到SC表中。（用Insert 语句实现）insert into sc select * from Gs01（2）从外部其他数据源导入数据（选做）§ 从文本文件中导入§ 从ACCESS中导入数据§ 从EXECEL中导入数据四、实验小结五、评阅成绩实验预习20%实验过程20%实验结果30%实验报告30%总成绩实验五 数据控制一、实验目的1、掌握数据库完整性约束条件的设置2、掌握触发器的基本使用3、掌握SQL Server2000的安全性控制方法二、实验预习1、关系数据库的完整性约束有哪些？2、什么是触发器？其作用是什么？SQL Server2000中的触发器主要有哪些？三、实验容和要求1、实体完整性约束（1）为供应商表SPJ建立完整性约束，约束列为SNO,PNO,JNO。方法1：在表设计器中建立，请写出建立的步骤：方法2：写出创建表的同时定义完整性约束的SQL语句。create table spj(sno char(6) foreign key references s(sno),pno char(6) foreign key references p(pno),jno char(6) foreign key references j(jno),qty smallint default 100,primary key(sno,pno,jno)（2）根据实验2中的容，输入数据下面的数据，能否成功？为什么？S1P1J1200（3）输入下面的数据能否成功，为什么？S1NULLJ1200（4）根据上面实验的结果，请说明实体完整性约束的含义。2、参照完整性约束（1）修改Student表中记录，将学号9512101改为9512103，更改是否成功？若不成功请说出原因。update student set sno='9512103' where sno='9512101'（2）修改SC表中记录，将学号为9512101的记录均改为学号9512109，是否更改成功？若不成功请说出原因。update sc set sno='9512109' where sno='9512101'（3）设置SC表的参照完整性为更新和删除时均为级联。方法1：在表设计器中设置，请写出设置的步骤：方法2：在创建表时同时设置参照完整性约束，并设置规则为级联。请写出SQL语句。CREATE TABLE SC(Sno char(10) ,Cno char(10),Grade numeric(6,2),PRIMARY KEY (Sno,Cno),FOREIGN KEY (Cno) REFERENCES Course(Cno)on delete cascadeon update cascade,FOREIGN KEY (Sno) REFERENCES Student(Sno)on delete cascadeon update cascade)（4）修改Student表学号9512101为9512109，观察SC表中相应记录是否更新？update student set sno='9512109' where sno='9512101'（5）在Course表中删除课程代号为C01的记录，观察SC表中选课C01的记录是否删除？delete from course whereo='C01'3、用户自定义完整性（1）设置Student表的Sno输入长度必须为7个字符的约束。请将CHECK约束子句写在下面：alter table student add check(sno like '_')（2）设置student表的Sdept只能为“计算机系”，“数学系”，“信息系”，“物理系”。请将CHECK约束子句写在下面：alter table student add check(sdept in('计算机系','数学系','信息系','物理系')4、触发器（1）定义一个触发器，其基本功能是在SC表中增加或修改一个选课记录时，检查该课程的选课人数是否超过限定（可自行定义一个限定值，根据表中数据的情况而定）。若超过限定值，则拒绝操作。触发器代码：CREATE TRIGGER trig1 ON SCFOR INSERT,UPDATEASIF(SELECT COUNT(*) FROM SC WHERE CNO=(select CNO from INSERTED)>10 BEGINPRINT '选课人数超过上限'ROLLBACK END（2）定义一个触发器，当删除Student表中数据时，先将删除的数据插入到另一个专门存放已删除数据的表中（实验时，首先定义一个与Student表结构相同的表用来存放删除的数据），然后执行删除操作。触发器代码：select top 0 * into s1 from studentcreate trigger trig2 on studentfor deleteas insert into s1 select * from deleted5、用户权限控制（1）在自己数据库中添加其他用户。（2） 为添加的用户进行授权和权限收回。相互检查是否获得了相应的权限。sp_adduser 'rj107','rj107'grant select on student to rj107grant select,update(cname) on course to rj107revoke select on course from rj107sp_dropuser 'rj107'四、实验小结五、评阅成绩实验预习20%实验过程20%实验结果30%实验报告30%总成绩实验六 视图与存储过程一、实验目的1、掌握视图的定义与使用2、掌握存储过程的建立和调用 二、实验预习1、基本表与视图有什么不同？哪种视图可以更新？2、视图定义语句格式：3、什么是存储过程，其作用主要是什么？三、实验容和要求1、视图（将执行的SQL语句写在下面）（1）建立视图IS_STUDENT,视图中包含信息系全体学生的基本信息。create view IS_STUDENTasselect * from student where sdept='信息系'select * from IS_STUDENT（2）建立视图CJ_STUDENT,视图中包含所有成绩不与格的学生的学号，课程名，成绩。create view CJ_STUDENTas select sc.sno,sname,cname,grade from sc,student,coursewhere sc.sno=student.sno and sco=courseo and gra