数据库实验1-6参考答案.doc

实验一 SQL Server使用初步一、实验目的1、熟悉SQL Server2000的组成及基本功能。2、掌握SQL Server2000的登录及注册。3、掌握SQL Server2000企业管理器的使用方法。4、熟悉查询分析器的基本使用。二、实验预习1、什么是数据库管理系统DBMS？你所知道的DBMS有哪些？答：DBMS是位于用户和操作系统之间的一层数据管理软件。常见的DBMS主要有：Oracle、db2、SQL Server、MySQL、PostgreSQL、SQLite、Firebird等等。2、SQL Server 2000(2005)的安装步骤？答：以企业版安装为例，步骤为： 将企业版安装光盘插入光驱后，出现以下提示框。请选择 “安装 SQL Server 2000 组件” 出现下面对话框后，选择 安装数据库服务器 。 选择 下一步，然后选择 本地计算机 进行安装。 在 安装选择 窗口，选择 创建新的SQL Server实例.。对于初次安装的用户，应选用这一安装模式，不需要使用 高级选项 进行安装。 高级选项 中的内容均可在安装完成后进行调整。 在 用户信息 窗口，输入用户信息，并接受软件许可证协议。 在 “安装定义”窗口，选择 “服务器和客户端工具” 选项进行安装。 在 “实例名” 窗口，选择 “默认” 的实例名称。 在 “安装类型” 窗口，选择 “典型” 安装选项，并指定 “目的文件夹”。 在 服务账号 窗口，请选择 对每个服务使用统一账户. 的选项。 在 身份验证模式 窗口，选择 混合模式. 选项，并设置管理员sa账号的密码。 最后按“下一步”即可完成安装。 检测安装：如果安装成功，应该能成功启动SQL Server，并且能和SQL Server客户端连接上。 可以通过服务管理器来进行启动。三、实验内容和要求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结果记录数：3查询语句2：SELECT FirstName, HomePhoneFROM Northwind.dbo.EmployeesORDER BY FirstName ASC结果记录数：9查询语句3：SELECT ROUND( (UnitPrice * .9), 2) AS DiscountPriceFROM ProductsWHERE ProductID = 58结果记录数：1查询语句4：SELECT OrderID, DATEDIFF(dd, ShippedDate, GETDATE() ) AS DaysSinceShippedFROM Northwind.dbo.OrdersWHERE ShippedDate IS NOT NULL结果记录数：809查询语句5：SELECT ProductID, ProductNameFROM Northwind.dbo.ProductsWHERE CategoryID = 1 OR CategoryID = 4 OR CategoryID = 5结果记录数：29四、实验小结五、评阅成绩实验预习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(WEIGHT0 AND WEIGHT=15 and Sage0),semster smallint check(semster 0),period smallint check(period0)（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精益20天津S2盛锡10北京继续往表中增加一条记录：四个字段的数据分别是S1,东方红，30,北京，会出现什么情况？为什么？不能增加，因为SNO是主键，不能取重复值。（2）零件表P：PNOPNAMECOLORWEIGHTP1螺母红12P2螺栓绿17P3螺丝刀蓝105.5是否能够正常输入三条数据？如果否，问题出在哪里，为什么？第三条记录不能输入，因为P3的WEIGHT属性取值违法了其检查约束。（3）工程项目表J：JNOJNAMECITYJ1一汽北京J2半导体厂南京J3常州第三条记录是否能够正常输入，若否，会出现什么问题，为什么？第三条记录不能输入，因为JNAME不能取NULL值。（4）供应情况表SPJ：SN0PNOJNOQTYS1P1J1200S1P1J3S2P2J4输入过程中是否会遇到问题，若有，出在哪，为什么？第三条记录不能输入，因为在工程项目表J中没有J3，破坏了参考完整性。四、实验小结五、评阅成绩实验预习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 Grade60（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=course.Cno) 或：select student.Sno,Sname from student,sc,Course where student.sno=sc.sno and o=o and cname=高等数学 and student.sno in (select sno from sc where cno=(select cno from course where cname=计算机导论)或：select student.Sno,Sname from student where sno in (select sno from sc where cno=(select cno from course where cname=高等数学) and sno in (select sno from sc where cno=(select cno from course where cname=计算机导论)（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 course.Cno=SC.Cno 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 course.Cno=SC.Cno 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 course.Cno,Cname,avg(Grade),count(Sno) from course,Sc where course.Cno=SC.Cno and Grade is not null group by Course.Cno,Cname（14）统计各门课程的不及格人数，要求输出课程代号，课程名，不及格人数。select Course.Cno,Cname,count(Sno) from SC,Course where SC.Cno=course.Cno and Grade75或：select sno,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 where cno in(select cno from course where cname in(VB,数据库基础)或：select sno,sname,sdept from Student where exists(select * from Course where cname=VB and not exists(select * from SC where sno=Student.sno and cno=Co and Came!=数据库基础)（19）查询选修了全部课程的学生的学号，姓名，系名。select sno,sname,sdept from student where not exists(select * from course where not exists (select * from sc where sno=student.sno and cno=o ) )或：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 o=o and cname=高等数学 order by grade 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 join course on o=o and cname=高等数学order by sno （5）将SC表中“C05”课程的选课记录输出至一个新表中，表名为Gs01。select * into Gs01from sc where cno=c052、修改数据（1） 将所有学生的年龄增加1岁。update student set sage=sage+1（2）修改“9512101”学生的“C01”课程成绩为85。update sc set grade=85where sno=9512101 and cno=c01（3）修改“9531102”学生的“C01”课程成绩为70。update sc set grade=70where sno=9531102 and cno=c01（4）将所有平均分为75分以上的学生的各门课成绩在原来基础上增加10。update sc set grade=grade*1.1where sno in (select sno from sc group by sno having avg(grade)=75)3、删除数据（1）删除“9531102”学生“C05”课程的成绩记录delete from sc where sno=9531102 and cno=c05（2）删除所有课程为“C05”的选课记录delete from sc where cno=c054、数据的导出（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 where cno=C013、用户自定义完整性（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,rj107grant 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=信息系（2）建立视图CJ_STUDENT,视图中包含所有成绩不及格的学生的学号，姓名，课程名，成绩。CREATE VIEW CJ_STUDENTASSELECT Student.Sno,Sname,Cname,Grade FROM STUDENT,Course,SC WHERE Grade602、存储过程（1）建立存储过程student_Grade1，功能是查询计算机系学生的成绩，包括学号，姓名，课程名，成绩，按学号升序排序。存储过程代码：CREATE PROC STUDENT_GRADE1ASSELECT STUDENT.SNO,SNAME,CNAME,GRADEFROM STUDENT,SC,COURSEWHERE STUDENT.SNO=S