数据库实验答案

1、实验（2）、交互式SQL（1）:利用SQL语句创建数据库、表及表之间关系一、实验目的和注意事项1、实验目的：熟悉创建数据库和数据表的SQL语句。2、注意事项：输入SQL语句时应注意中文、英文的区别。二、实验内容在查询分析器中，完成以下操作。1、用SQL语句创建数据库，名称为“TSC”。2、用SQL语句在TSC数据库创建4张表：T,S,C,SC（参考教材P78例3.1）3、在上面的4张表中插入示例数据（参考教材P21例1.7）三、实验步骤（这部分不需要写作实验报告上）1、打开SQLServer2000的查询分析器2、创建数据库TSC。3、创建4个表（参考教材P78例3.1）。4、在4张表中插入数

2、据（示例数据参考教材P21例1.7,Insert语句参考P973.4.1数据插入）四、实验报告记录部分（在实验报告中记录创建数据库、创建表及向表中插入数据的SQL语句）1、用SQL语句创建数据库，名称为"StudentCourse”。2、用SQL语句创建StudentCourse中的3张表（见下面），同时指定主码、外码、约束等。3、在3张表中插入下面的示例数据表1学生表（Students）数据学号Sno姓名Sname性别Ssex出生日期Sbirthday所在系Sdept备注Memo0602001钟耀华男1987-05-01计算机优秀毕业生0602002吴佳硕女1987-03-24计算

3、机爱好:音乐0602003吴纯斌男1988-07-01计算机0701001王腾飞男1988-05-04机电爱好:音乐0701002林师微女1987-04-03机电爱好:体育0701003李乐怡女1986-03-03机电0703001李奇男1988-09-17工商管理表2课程表(Courses)数据课程号Cno课程名Cname先行课PreCno学分CreditC1数据结构4C2数据库原理C14C3大型数据库C23C4局小夫1表3选课表(SC)数据学号Sno课程号Cno成绩Grade0602001C1610602001C2720602001C3880602002C10602002C26107010

4、01C1500701001C20701002C3780701003C1520701003C387Page1createdatabaseStudentCoursegouses:udentCoursegocreatetableStudents(Snochar(7)primarykey,Snamevarchar(10),Ssexchar(2)default'男二Sbir:hdaysmalldatetine,Sdep:varchar(12),Me敷ovarchar(30)gocreatetableCourses(Cnovarchar(4)primarykey,Cnamevarchar(20),

5、PreCnovarchar(4),Credittinyint)gocreatetableSC(Snochar(7)foreignkeyreferencesStudents(Sno),Cnovarchar(4)foreignkeyreferencesCourses(Cno),Gradetinyint)goselec:*fromstudentsinser:intostudentsvalues('0602001'钟耀华'J男1987-05-01'计算机优秀毕业生,)inser:intostudentsvaluesC0602002吴佳硕'女1987-03-24&

6、#39;J计算机'/爱好:音乐)inser:intostudentsvalues('0602003吴纯斌'J男'J1988-07-01'J计算机'，null)inser:intostudentsvalues('0701001王腾飞'男1988-05-04'J机电J爱好:音乐')inser:intostudentsvalues('0701002'林师微'J女'J1987-04-03'，'机电'/爱好:体育')inser:intostudentsvalue

7、s('0701003李乐怡'J女1986-03-03'/机电',null)inser:intostudentsvalues('0703001'李奇'男'J1988-09-17,J工商管理,null)goselec:*fromcoursesinser:intoCoursesvaluesCC1'数据结构'，NULL,4)inser:intoCoursesvalues('C2数据库原由Cl',4)inser:intoCoursesvalues('C3大型数据库'/C23)inser:int

8、oCoursesvalues('C4'高尔夫,NULL,1)goselec:*fromscinser:intoSCvalues('0602001'/Cl61)inser:intoSCvaluesC0602001,C2572)inser:intoSCvalues('0602001C388)inser:intoSCvalues('0602002Cl',null)inser:intoSCvalues('0602002Cr,61)inser:intoSCvalues('0701001Cl',50)inser:intoSCv

9、alues('0701001C?,null)inser:intoSCvalues('0701002C378)inser:intoSCvalues('0701003'/Cl52)inser:intoSCvaluesC0701003*/U3*,«7)go实验（3）、交互式SQL（2）一简单查询2012-04-2622:12:00|分类：,默认分类|字号订阅一、实验目的和注意事项实验目的：掌握单表查询SELECT、FROM、WHERE的用法，特别要掌握比较运算符、逻辑运算符的使用。注意事项：子句WHERE条件表示元组筛选条件；子句HAVING条件表示元组选择

10、条件；子句HAVING条件必须和GROUPBY子句配合使用；二、教材例题练习（不需要记录在实验报告上）在实3会（2）创建的表及插入的示例数据（数据库TSQ基础上，完成教材P83例3.9三、实验内容及步骤（在实验报告中记录每个查询的SQL语句）在实32（2）创建的表及插入的示例数据（数据库StudentCourse）基础上，完成查询计算机系全体学生的信息查询姓“李”的学生的学号和姓名。查询课程表中先行课为空的课程名。查询考试成绩有不及格的学生的学号。求选修了C1课程或C2课程的学生的学号及成绩。查询全体计算机系学生的姓名及其年龄。查询计算机系在19861987年之间出生的学生的姓名。查询姓“李”

11、的前2个学生（按姓名升序排列）的学号和姓名。查询选修了两门以上课程的学生学号与课程数。查询选修课程数大于等于2的学生的学号、平均成绩和选课门数，并按平均成绩降序排列。附：参考解答POEE1一查询i+算机系全体学生的信息select*frosstudentswhereKdeptH,tt算机，一查询姓”李”的学生的学号和姓名.selectSno,SnaaefroastudentswhereSnaaelike'李售'一查询课程耒中先行课为空的课程名*seicctCnaefroacourseswherePreCnoISNULL查询考试成境有不及格的学生的学号.seiectSnofroa

12、£Cwhererade<60一求选修了C1课程或C2课程的学生的学号、课程号、成绩。selectsno,eno.gradefroaSCwhereCno""C1rORCno="C2T一®查询全体甘莽机系学生的姓名及其年龄.uetectSnake,year(getdatc0)-year(Sbirthday)fro>Studentswhere£dcpt='计算机*一查询计算机系在1986-1987年之间出生的学生的姓名.selectSnaftefro«StudentshereSbirthdaybetweenrl

13、9867T'AND'19吐12-3ANOSdepU计算机-®查询姓''李"的前2个学生(按姓名升序排列)的学号和姓名.selectTOP2SnoTSnaneTronstudentswheresnaseiike'李方orderbySnaae查询选修了两门以上课程的学生学号与课程数.患electsno.count(eno)frgSCgroupbvSnohavingcount(Cno)>2查询选修课程数大于等于2的学生的学号、平均成绩和窿源门数.并按平均成绩降序排列.selectsnoravg(fifsde),count(Cno)Ir

14、oaSCgroupbySnohavicount(Cno)>1orderbyavg(grade)DESCselect*fTonsc实验(4)、交互式SQL(3)复杂查询2012-04-2622:16:24|分类：默认分类|字号订阅一、实验目的和注意事项实验目的：1 .理解多表查询的概念；掌握多表查询的SELECTFROMWHEREJOIN子句的用法，特别要对比内连接、外连接的结果差异。2 .掌握查询中嵌套查询和组合查询的操作方法，进一步加深对SELECT语句的理解。注意事项：1、内连接、左外连接和右外连接的含义及表达方法；2、子句的使用，注意语句的层次嵌套关系和括号的配对使用问题；二、教材

15、例题练习(不需要记录在实验报告上)在实3会22)创建的表及插入的示例数据(数据库TSC)基础上，完成1、教材P93例3.19,注意some,all的使用方法，并掌握其相应的IN、NOTIN的等价表不2、教材P94例3.20,注意SQLServer2000不支持Unique谓词，请给出解决这个问题的SQL语句，并记录在教材例3.20的旁边。三、实验内容及步骤(在实验报告中记录每个查询的SQL语句)在查询分析器中输入SELECT查询语句，对在实验(2)中已完成的StudentCourse数据库中的表，进行下列查询操作：1、多表连接查询(1)查询选修了【数据库原理】的计算机系的学生学号和姓名。(2)

16、查询每一门课的间接先行课(即先行课的先行课)。(3)查询学生的学号、姓名、选修课程的名称和成绩。(4)查询选修了课程的学生姓名。(5)查询所有学生的信息和所选修的课程，没有选课的同学对应的选课信息为空。(6)查询所有课程的课程编号、课程名称及选课人数，没有被选的课程选课人数显示为0。(7)列出学生所有可能的选修情况。(8)查找计算机系的学生选修课程数大于2的学生的姓名、平均成绩和选课门数，并按平均成绩降序排列。2、嵌套查询和组合查询操作。(1)统计选修了【数据库原理】课程的学生人数。(2)查询没有选修【数据库原理】课程的学生信息。(3)查询其他系中比计算机系学生年龄都小的学生。(4)查询被06

17、02001学生或0602002学生所选修的课程的课程号(用UNION组合查询与IN条件查询两种方法实现)。查询0602001学生和0602002学生同时选修的课程的课程号(用IN子查询与EXISTS嵌套子查询两种方法实现)。(6)查询被学号0602001学生所选修的课程的课程号。附：参考解答Page1一1、多表连接查询(1)查询选修了【数据库原理】的计算机系的学生学号和姓名.selectstudents.sno9snaae9cnaaefroastudents,courses,scwherestudents,sno=sc.snoANDo=sc.enoANDcnae='数据库原理，(2)查

18、询每一门课的间接先行课(即先行课的先行课).otc1.preCnovc2.preCnofro.coursesclleftjoincoursesc2ONcl.preCno=c2.eno(3)查询学生的学号、姓名、选修课程的名称和成绫.seIectStudents.sno9snaae9cnaae9gradefroastudentsvsc.courseswherestudents.sno=sc.snoANDsc.eno=courses.eno(4)查询选修了课程的学生姓名.seIectdistinctsnaaefroastudents,scwherestudents.sno=sc.snoselect

19、snaaefroastudentswheresnoIN(selectsnofroasc)-(5)查询所有学生的信息和所选修的课程，没有选课的同学对应的选课信息为空。selectstudents.*9enofroastudentsleftjoinscONstudents.sno=sc.sno(6)查询所有课程的课程编号、课程名称及选课人数，没有被选的课程选课人数显示:selectcourses,eno,cnaaevcount(sc.eno)as选课人数o=o9cnaaeselect*fromsc(7)列出学生所有可能的选修情况.selectsno9enofroastudents,courseso

20、rderbysno(8)查找计算机系的学生选修课程数大于2的学生的姓名、平均成绩和选课门数，#成绩降序排列.select*froastudentsselectsnaae,avg(grade)as平均成绫,count(eno)as选课门数froastudents,scwherestudents.sno=sc.snoandsdept二计算机groupbysnaaehavingcount(eno)>2orderbyavg(grade)desc一2、青套查询和组合查询操作。(1)统计选修了【数据库原理】课程的学生人数.selectcnotcount(sno)froascgroupbyenohav

21、ingenoIN(seIectenofroacourseswherecnae='数据库原理)-(2)查询没有选修【数据库原理】课程的学生信息。select*froastudentswheresnoNOTIN(selectsnofroascwhereenoIN(seIectenofroacourseswherecnae=数任)-ORselectfroastudentswhereNOTexists(select*o=courses.enoandsc.sno=students,snANDa.e二，数据库原理,)(3)查询其他系中比计算机系学生年龄都小的学生。select*froastuden

22、ts实验（5）、交互式SQL（4）一数据更新2012-04-2622:19:24|分类：2010级数据库教学|字号订阅一、实验目的和注意事项实验目的：熟悉插入、修改和删除SQL语句的用法注意事项：对于INSERT语句，要求理解默认值约束、非空约束在插入时所起的作用。二、教材例题练习（不需要记录在实验报告上）在实3会（2）创建的表及插入的示例数据（数据库TSC）基础上，完成1、教材P98例3.25,注意更正教材上第,小题的错误，并记录在该题的旁边2、教材P99例3.263、教材P99例3.27,注意更正教材上第小题的错误，并记录在该题的旁边三、实验内容及步骤（在实验报告中记录每个查询的SQL语句

23、）在实32（2）创建的表及插入的示例数据（数据库StudentCourse）基础上，完成向表Students中插入（0601001,赵林，男,1985-09-08,计算机）的记录。向SC表中添加一个学生的选课记录，学号为0601001,所选的课程号为C2。SC表中有Sno、Cno、Grade这3个列。这里只知道学号和课程号，不知道成绩值。向表Students中插入（0601002,张修雨，default）记录，该记录的数据中default表示默认值男，其他数据为空值。用CREATE语句建立表StudentBAK1,包含（与Students的Sno、Sname、Sdept相同）3个字段，然后用I

24、NSERTSELECT语句实现向StudentBAK1添加Students表中的计算机系学生的学号、姓名、所在系的信息。用SELECTINTO语句实现把Students表中1986年后（包含1986年）出生的学生的学号、姓名存储到一个新表StudentBAK2。将Students表中姓名为【赵林】的同学所在系改为【机电】，爱好改为【足球】。将选修了课程名为【数据库原理】并且有成绩的学生成绩加5分。将StudentCourse数据库的StudentBAK1表中所有姓赵的同学删除。删除机电系课程成绩不及格或者没有登记成绩的学生选课记录。将StudentCourse数据库的StudentBAK2表中

25、的所有行删除。附：参考解答Page1一向表Students中插入(0601001.赵林,男，1985-09-08,计算机)的记录select*froastudentsinsertintostudentsvalues('0601001'赵林,，男，'1985-09-08','计算机，r一向SC表中添加一个学生的选课记录，学号为0601001,所选的课程号为C2。SC来Cno、Grade这3个列.这里只知道学号和课程号，不知道成绩1insertintoSC(sno,eno)vaIuesC0601001*,*C2*)一向表Students中插入S601002,

26、张修雨，default)记录，该记录的数据中defau默认值'男'，其他数据为空值。insertintostudentsvalues('0601002','张修雨'，default,nulI,nulI,nulI)®用CREATE语句建立表StudentBAKI,包含(与Students的Sno、Sna-e、Sdept节字段，然后用INSERTSELECT语句实现向StudentBAKI手加Students表中的计算机J!学号、姓名、所在系的信息.createtablestudentBAKI(Snochar(7)priaarykey,Sn

27、aaevarchar(20),Sdeptvarchar(10)goselectfroastudentBAklinsertintostudentBAKIselectsnovsnaae,sdeptfroastudentswheresdept二计算机一用SELECT-INTO语句实现把Students表中1986年后(包含1986年)出生的学、姓名存储到一个新表StudentBAK2.selectsno,snaae9sbirthdayINTOStudentBAK2frohstudentswhereyear(sbirthday)>=1986selectfroaStudentBAK2-®

28、将Students表中姓名为【赵林】的同学的所在系改为【机电系】，差好改为【足石select*froastudentsupdatestudentssetsdept='机电'，Meo二'足球，wheresna-e二赵林'一将选修了课程名为【数据库原理】并且有成绩的学生成绩加5分.select*froasctcourseswheresc.eno=courses,enoandcnae二数据库原理，updatescsetgrade=grade+5froasc9courseso=courses.enoandcnae二数据库原理，一®将StudentCourse数

29、据库的StudentBAKI表中所有姓赵的同学除。select*froastudentBAKIdeIetefroastudentBAKIwheresnaaeIike'赵%'一除机电系课程成绩不及格或者没有登记成绩的学生选课记录.select*froascvstudentswheresc.sno=students.snoANDsdept二机电deletescfroascfstudentswheresc.sno=students,snoANDsdept='机电AND(grade<60ORgradeISNULL)实验（六）、第四章数据库安全性2011-11-0113:0

30、0:58|分类：,默认分类|字号订阅一、实验目的1 .熟悉通过SQL语句对数据进行安全控制2 .完成数据库系统概论（第四版）一书中第四章例题的上机练习二、实验平台MSSQLServer2000三、实验要求1、使用SQL语句对数据进行安全控制，包括授权和权力回收。2、授权操作完成后，检查用户是否真正具有所授予的数据操作权力；在权力回收操作之后，用户是否确实丧失了所回收的数据操作权力。四、实验内容及完成情况（实验参考解答见教学博客）在实验开始之前，需要把即（网盘上亦提供）上所提供的2个数据库文件（实验（6）student.mdf,实验（6）studentlog.ldf）下载到本机,并将其附加到SQ

31、LServer上。附加完成以后，会出现数据库student,在这个数据库中已建立了三张基本表：student、course、SC,下面的实验是在这3张表的基础上进行的。1 .在数据库student中创建7个用户U1,U2,U3,U4,U5,U6,U7记录下创建用户的过程，需要自己完成记录你的实际操作过程2 .完成教材4.2.4小节例1例7（P138139）的授权语句。记录下授权的过程，需要自己完成3 .在授权完成以后，验证用户是否拥有相应的权限注意：验证时需要分别以不同的用户（U1U7）身份登录到数据库，进行相关的操作，检查他们是否具有教材表4.4（P139）中的相应权限。记录下验证过程，需要

32、自己完成4 .完成教材4.2.4小节例8例10（P140）的权限回收语句，并验证在权限回收以后，该用户是否真正丧失了对数据的相应权限。记录下权限回收及验证过程，需要自己完成5 .数据库角色的使用SCexecantdbaccefis=U1*exccsp_grantdbaccess'U史'exec零pj;rantdbaccess'UW"程口理:以熠相痛司授锲0两用我的藉瞬强举用瑞邑移除用户等操作，角色概念介绍RF3H*seIeu>onstudentTOu1薪教材4.2.5小节数据库角色的内容（P142）,而在SQLServer上机操作需要参考教学博客上提侏好

33、侬鬼叫行完成。.j-4.2|艳对妻tudent表和Cuurs:后表达全部厚作权PR授予用户U2、U3?能加博强也楷年州三期呷翳小噌阳快W浒繇.ONgu兔工评注日在实验过程中出现的问题及解决方法，需要自己完成>GRAN1SELECTONSCFOPUBLICgo-4.4例4把查询StudEl表和修改Stud5t表中学生学号的权限擅绐用户U4execsp_adillogtnu4.u4execsp_rantdbaccessu4gogrant.update(sno)TseIectONstudenttou4£<J4例5把对SC的INSERT权限授予用户U5a并允许U5将此权限再转授给

34、其他用户execsp_add<0fiinu5.u5cxecsp_grantdbaccessu5触录二：本次实验参考解答grantinscrtGNstudenttou5withgrantoption一4例6用户Ub把对SC的IMSEH1权限转授给用户U6execspaddIoginijG,u6execsp_grantdbuccessu6so一以u5登录grantinscrtONstudenttou5withgrantoptionRo%7|例7用户U6杷对SC的INSERT权限转授靖用户U7.请验证U7不能再情播此权限exec«p_addlGfltnu7.u7execspgrant

35、dbaccessu/go以片6登录&raril,irisertONstudentlou7一注宣：下面1例8I例10需要EUa登录数据库才能执行48例用户U4修京Student表中学号的权限收回revokeupdateCsno)onstudentfronul4.9|例收回所有用户对表3c的查询权限revokeseicatonscfronpubIic一4.10|例10k算把用户U5对SC表的FSERI权限收回.注章CASC”DF的使用revokeinnertonsftidentfrobli5cascade4.”例11角色的使用一创建角色RIexecspaddroIeRIso一为角色授权gr

36、antseIect4update,inscrIONstudentTOR1-创建，录U1.密码为UIcxccsp.addlogin*Ur/Ur«o一创建登录U2、U3,密码分别为U2、U3execsp.addlogintU2tU2-cxccsp.addlogin,U3*.*U3'«o一授予U1.U2,U3在数搪库student中执行活动的权限usestudent«oexecsp_grantdbacccss*UI*execsp_grantdbacccss*U2"execsp_.grantdbaccesswU3"«o一4.1例1把查

37、询student表的权限授给用户UIgrantselectonstudentTOul«o4.2洌2把对student表和Course表达全部操作权限授予用户U2、U3xrantallprivilegesONstudentTou2,u3gograntalIprivilegesONcourseTou2,u38。-4.3洌3把对SC表的查询权限授给所有用户GRANTSELECTONSCTOPUBLICgo一4.4洌4把查曰Student表和修改Studcnl表中学生学号的权限授给用户U4cxccsp_addloginu4.u4cxccsp_srantdbaccessu4«ogra

38、ntupdate(sno).selectONstudenttou4g。T.5洌5把对SC的IXSERI权索授予用户U5,并允许U5#此权限再转授给其他用户execsp_addloginu5.ubexecsp.grantdbaccessub«ograntinsertONstudentto115withgrantoption4.61例6J用户U5把对SC的INSFRI权限转授给用户U6execsp_addloginu6.u6execsp_grantdbacccssu68°一以u5登录xrantinsertONstudenttou5withxrantoption«o4.

39、”例7用户U6把对SC的INSERT权限转投给用户U7,请处汪U7不能再传播此杈限execsp.addloxinu7.u7cxccsp_grantdbacccssul«o一以u6登录grantinsertONstudenttou7go一注宣：下面例8例10需要以so登录数据库才能执行-4.8例8sa把用户U4修改Student表中学号的权限收回revokeupdate(sno)onstudentfro，ul一4.9洌9sii收回所有用户对豪SC的查询权限revokeselectonscffobpubIic4.10例lOkalfi用户U5对SC泉的IKSERI权*收回.注宜CASCAD

40、E的使用revokeinsertonstudentfroau5cascade一4.11例11角色的侵用一创建金色RIexecsp_addroleRI8。一为角色授权grantselect.update,insertONstudentTORI8。-将用户U5漆加制角色RI,并独证用户U5的权限execsp.addRoIeMcaberRI.u5go-将用户U5从角色RI中移除execspdropRolelieaberRI.u5实验（七）、第五章数据库完整性2011-11-0714:37:12|分类：默认分类|字号订阅一、实验目的1 .熟悉通过SQL语句对数据进行完整性控制2 .完成数据库系统概论一

41、书中第五章例题的上机练习二、实验平台MSSQLServer2000三、实验要求使用SQL语句对数据进行完整性控制，包括3类完整性约束、CHECK短语、CONSTRAINT子句、触发器。通过实验证实，当操作违法完整性约束条件时，系统是如何处理的。四、实验内容及完成情况在本次实验中，对数据完整T行上机练习。包括3类完整性、CHECK短语、CONSTRAINT子句、触发器。因为完整性约束绝大部分是在定义表结构时进行的，因此可能需要多次重复定义同一张表。如果表名发生重复，则需要先删除旧表后再重新建立。在开始实验之前，先创建一个名为Practice7的数据库，下面所有的SQL语句都是在Practice7这个数据库内执行的1 .在数据库Practice7中创建教材P82的student,course