数据库SQL语句试验报告.doc

《数据库SQL语句试验报告.doc》由会员分享，可在线阅读，更多相关《数据库SQL语句试验报告.doc（26页珍藏版）》请在三一文库上搜索。

1、数据库原理及应用实验报告SQL语句部分总成绩：实验一 熟悉SQL SERVER，数据定义实验实验一成绩：一、实验目的1、利用查询分析器创建数据库2、利用查询分析器用 SQL语言定义基本表、索引并进行相关操作二、实验步骤及内容在以下实验中，使用学生 -课程数据库，它描述了学生的基本信息、课程的基本信息及学生选修课程的信 息。1 创建学生课程数据库2 .创建学生关系表 S :学号姓名性别年龄所在系SnoSn ameSsexSagesdept3 创建课程关系表 C :】田千口 i=r. 课程号课程名先行课学分CnoCn ameCpnoccredit4 创建课程关系表 SC :学号】田千口 i=r.

2、课程号成绩SnoCnograde5、 将以上创建表 S、C SC的SQL命令以.SQL文件的形式保存在磁盘上。6、在表S上增加“出生日期”属性列。7、删除表S的“年龄” 属性列。&删除S姓名列的唯一值约束。9、修改SC表成绩属性列为精确数字型。10、在表S上，按“ Sno”属性列的唯一值方式建立索引。11、 删除表SC利用磁盘上保存的.SQL文件重新创建表 SCo12、 创建教材P74页习题5中SPJ数据库中的四个关系：S、P、J、SP丄三、实验结果：1 创建学生课程数据库create database Studend2 .创建学生关系表 S :学号姓名性别年龄所在系SnoSn ame

3、SsexSagesdeptcreate table S(Sno CHAR9) PRIMARYKEYSname CHAR20) UNIQUESsex CHAR2),Sage smallint ,Sdept char (20),);3 创建课程关系表 C :】田千口 i=r. 课程号课程名先行课学分CnoCn ameCpnoccreditcreate table C(Cno char ( 4) primary key ,Cn ame CHAR40),Cpno char (4),Ccredit SMALLINT,);4 创建课程关系表 SC :学号】田千口 i=r. 课程号成绩SnoCnograde

4、create table SC(Sno char ( 9),Cno char (4),Grade smallint ,);S、C SC的SQL命令以.SQL文件的形式保存在磁盘上。5 .将以上创建表6、在表S上增加“出生日期”属性列。alter table S add Sbirthday datetimeEl LJ Studend圧J逛库关豕图0 LJ田a dbo.Cs3 dbcrS口 口列? Sno (PK, char(9)rTl Gnam? czhsrQQ3 (charC2)r ni ji1亡(smallint, r0 Sdept (char(2O)r3 Sbirthday (dartet

5、、二 1 cJ L>O.S回3列T Snc (PK 3 Sname char(2O；7、删除表S的“年龄” 属性列。alter table S drop column Sage;H Ssex (charC2J, irn(3 Sdept (charO)pH Sbirthday datet&删除S姓名列的唯一值约束。alter table S drop UQ_S_7D78A4E7f Sro (PK,not null圍 Sname (char(2O), null)3 Ssfrx (char(2), null)| Sage (amallint, nulli_3 Sdept (char(2

6、0). null)? PK_S_7CB4S0AE3鉤克9、修改SC表成绩属性列为精确数字型。alter table SC altercolumn Grade int_J列圍 SnoftharWl 测 It)J 匚no (rhar4X nullL3 Grade tint, null10、在表S上，按“ Sno”属性列的唯一值方式建立索引。create uni que in dex Stus no on S(Sno);-：血 PK_S_7C8480AE 儼集) 缶Stusno腑一，弓逮園11、删除表SC利用磁盘上保存的.SQL文件重新创建表 SCodrop table SC;l-J I 一 IZI

7、二球表 +j _ dbo.C 刃 口 dbd$14 口视圏B ca1討口輙表田dbo.CdboSS-Jdbo.SC12、创建教材P74页习题5中SPJ数据库中的四个关系：S、P、J、SP丄实验二数据更新实验实验二成绩：一、实验目的1、熟悉和掌握数据表中数据的插入、修改、删除操作和命令的使用2、加深理解表的定义对数据更新的作用二、实验步骤及内容（一）利用查询分析器在表中插入数据打开在实验一中建立的“学生课程”数据库；在"SCDB数据库中的 student、course、sc表中插入数据； student表的内容：SNO SNAME SSEX SAGESDEPT95001李勇男20CS9

8、5002刘晨女19IS95003王敏女18MAcourse表的内容CNO CNAMECPNOCREDIT1数据库542数学NULL23信息系统144 操作系统 NULL 35 数据处理 NULL 2sc表的内容SNOCNOGRADE9500119295001265950013889500229095002380（二）修改表中的数据在插入数据后的三张表 s、C、SC中进行相关的更新操作：1、 将student表中李勇的名字改为李明；2、 将student表中王敏的年龄改为 20岁；3、 将所有学生的年龄增加1岁；4、修改学生的学号95002'为95005',注意表之间的关系，保持

9、一致性;5、 把选修了“ 2”号课程，且成绩低于 70分的成绩提高5%6、将“ CS系全体学生的成绩置零；（三）删除表中的数据1、把选修了“ 2”号课程，且成绩低于 70分的学生的成绩删除掉；2、删除学号为“ 95003 ”的相关信息；3、删除"IS"系选修了 "2"号课程的选课记录；4、删除“ CS系的全体学生的选课记录；5、删除整张表的数据，注意表之间的关系，保持一致性。三、实验结果：（一）利用查询分析器在表中插入数据 打开在实验一中建立的“学生课程”数据库；在"SCDB数据库中的 student、course、sc表中插入数据; stud

10、ent 表的内容：SNO SNAME SSEX SAGESDEPT95001李勇男20CS95002刘晨女19IS95003王敏女18MAcourse表的内容CNO CNAMECPNOCREDIT1数据库542数学NULL23信息系统 144操作系统 NULL 35数据处理 NULL 2sc 表的内容SNO CNOGRADE9500119295001265950013889500229095002380insertinto S (Sno, Sname, Ssex, Sage, Sdept )values ( '95001' ,' 李勇 ', '男'

11、; ,20, 'CS' );insertinto S (Sno, Sname, Ssex, Sage, Sdept ) values ( '95002' ,' 刘晨 ', '女' ,19, 'IS' );insertinto S (Sno, Sname, Ssex, Sage, Sdept )values ( '95003' ,' 王敏 ', '男' ,20, 'CS' );select *from S;insertinto C ( Cno, Cnam

12、e, Cpno, Ccredit )values ('1' ,'数据库 ', '5' ,4);insertinto C ( Cno, Cname, Cpno, Ccredit )values ('2' ,'数学' ,NULL, 2);insertinto C ( Cno, Cname, Cpno, Ccredit ) values ('3' , '信息系统 ', '1' ,4);insertinto C ( Cno, Cname, Cpno, Ccredit ) va

13、lues ('4' ,' 操作系统 ' ,NULL, 3);insertinto C ( Cno, Cname, Cpno, Ccredit ) values ('5' ,' 数据处理 ' ,NULL, 2);insertinto SC( Sno, Cno, Grade)values ( '95001' , '1' ,92);insertinto SC( Sno, Cno, Grade)values ( '95001' , '2' ,65);insertinto SC

14、( Sno, Cno, Grade)values ( '95001' , '3' ,88);insertinto SC( Sno, Cno, Grade)values ( '95002' , '2' ,90);insertinto SC( Sno, Cno, Grade) values ( '95002' , '3' ,80);二结果消息95002刘晨95003 王敏1 I234595001 395002 195QD1 j 19500? J 2sdcslscs9臭文男deGra滋融珊同紀»

15、=-am9sn李Crio1M"麺擔障E422数学NULL233ttJS-1444摄作NULL355HULL295002 3Crio Criarne Cpno Ccredit（二）修改表中的数据在插入数据后的三张表 s、C、SC中进行相关的更新操作:1、将student表中李勇的名字改为李明'update Sset Sname='李明'where Sn o ='95001'* I弟结果J涓息Sno Snane+i lllk-HII IHH Illi b-HI j1 j 9500夢月2 *融厂戈嗔J M003 王敏5玄1 ZS3男女男2、将stud

16、ent表中王敏的年龄改为20岁;update Sset Sage =20where Sname='王敏SnaSname Ssex 5ageSdepl1测01 李明 男20CS255002划晨 女 1SIS3旳）03王融 男2DLesj3、将所有学生的年龄增加 1岁;update Sset Sage =Sage+1;4、修改学生的学号update Sset Sno ='95005'where Sno ='95002'95002'为95005注意表之间的关系，保持一致性;-r-G|SnoSnameSsacSageSdept195001李明21cs23

17、5M3王敏21cs39500520IS5、把选修了“ 2”号课程，且成绩低于update SCset Grade =Grade*70分的成绩提高5%where Cno='2' and Grade <70;平SnoCnoGrade1ir'iaiiivinamBiimi95M1 ¥12950012395M14950022595M216、将“CS'系全体学生的成绩置零;update SCset Grade =0where 'CS' =( select Sdept from Swhere（三）删除表中的数据1把选修了“ 2 ”号课程，且成

18、绩低于70分的学生的成绩删除掉;delete from SC| LJ鉛耒Ij,消息SewCnoGradewhere Cno ='2' and Grade <70;J_ 950011a29500130355W22W4950023B02、删除学号为“ 95003 ”的相关信息;delete from Swhere Sno ='95003'SnoSnamaSsexSage射ept1 j &&ooi i 李明21CS2晒005划晨20IS3、删除"IS"系选修了"2"号课程的选课记录;delete from

19、SC where Cno ='2' and Sno in (select Sno from S where Sdept ='IS');4、删除“ CS系的全体学生的选课记录;delete from SCSno CnoI：JUS 1 i 95002 j 22 方逾"JGradeSO仙where 'CS' =（ select Sdept from S where =;5、删除整张表的数据，注意表之间的关系，保持一致性。delete from SC;pSnp Crio实验三数据查询实验实验三成绩： 一、实验目的熟悉和掌握对数据表中数据的查询操

20、作和SQL命令的使用，学会灵活熟练的使用SQL语句的各种形式;加深理解关系运算的各种操作（尤其的关系的选择，投影，连接和除运算）二、实验方法及步骤1、在表student,course,sc 上进行简单查询；2 .在表student,course,sc 上进行连接查询；3 .在表student,course,sc 上进行嵌套查询；4. 使用聚合函数的查询；5. 对数据的分组查询；6. 对数据的排序查询。三、实验内容在表 student , course , sc上完成以下查询：1查询学生的基本信息；2、查询“ CS系学生的基本信息；3、查询“ CS系学生年龄不在19到21之间的学生的学号、姓名；

21、4、找出最大年龄；5、找出“ CS系年龄最大的学生，显示其学号、姓名；6、统计“ CS系学生的人数；7、统计各系学生的人数，结果按升序排列；8、按系统计各系学生的平均年龄，结果按降序排列；9、查询每门课程的课程名；10、统计无先修课的课程的学分总数；11、查询选修了“ 1”或“ 2”号课程的学生学号和姓名；12、查询选修了“ 1”和“ 2”号课程的学生学号和姓名；13、 查询选修了课程名为“数据库系统”且成绩在60分以下的学生的学号、姓名和成绩；14、查询每位学生选修了课程的学生信息（显示：学号，姓名，课程号，课程名，成绩）；15、查询没有选修课程的学生的基本信息；16、查询选修了 3门以上课

22、程的学生学号；17、 查询选修课程成绩至少有一门在80分以上的学生学号；18、 查询选修课程平均成绩在80分以上的学生学号；19、（选做）统计每位学生选修课程的门数、学分及其平均成绩；20、 （选做）找出平均成绩在 85分以上的学生，结果按系分组，并按平均成绩的升序排列。四、实验结果：1查询学生的基本信息;select * from S ;StiqSnanreSagsSdept17 a. B IIS A-95001臭21CS296003王敏2Dcs3&5DO5女20IS49500920CS595010陈杰禺22IS695012季告女1Bcs195013女20IS2、查询“ CS系学生的

23、基本信息;select *SnameSsexSageSdeptfrom S1I 95W1郛月胃21CSwhere Sdept ='CS'29S003壬锁20CSg9500920CS495012疫1GCS3、查询“ CS系学生年龄不在19到21之间的学生的学号、姓名;select Sno , Snamefrom Swhere sdept ='CS' ANDSage not between 19 and 21 ;4、找出最大年龄；seleCt maX(Sage)飞from S;.Li_ 5、找出“ CS系年龄最大的学生，显示其学号、姓名；select Sno , S

24、namefrom Swhere Sage =(select max( Sage)from Swhere Sdept ='CS');7、统计“ CS系学生的人数;SELECTSdept , count (*) from Swhere Sdept ='CS'Sdept氐歹茗)jl.Ul IIMJIII Uli 111 _j 4GROUPBYSdept ;8、统计各系学生的人数，结果按升序排列;SELECTSdept , COUNT*)FROMSgroup by sdept ;Sdept1 : CS2 IS439、按系统计各系学生的平均年龄，结果按降序排列;selec

25、t Sdept , avg (Sage) from Sgroup by Sdeptorder by avg (Sage) desc;或“ 2”号课程的学生学号和姓名;select , Snamefrom S, SCWHERE= AND(Cno='1' or Cno ='2');1菊告2&5010II m » MBiiiaaj 汴钛j b uii iiiniaui iiubr3Sno bname二1錯杲消息Cnanie1Minimi nana m|Z2数据处理3藪据库4教学5信息累统10、查询每门课程的课程名;select Cn amefrom

26、Cgroup by Cname;10、统计无先修课的课程的学分总数;select sum( Ccredit )from Cwhere Cpno is null;11、查询选修了“ 1”512、查询选修了“ 1”和“ 2”号课程的学生学号和姓名;select , Sname from SC, Swhere Cn o='1' AND = and IN(select Snofrom SCwhere Cno='2'）;13、查询选修了课程名为“数据库系统”且成绩在60分以下的学生的学号、姓名和成绩;select , Sname Gradefrom S, SC, Cwhe

27、re = and = and Cname='数据库'and Grade <60;14、查询每位学生选修了课程的学生信息（显示：学号，姓名，课程号，课程名，成绩）Sno Snarre Cno Cnane Gradeselect , Sname, Cname Grade from SC, C, SWHERE= and =;910陈杰1数据库盘9M10陈杰2数学70味衣3信息系统B015、查询没有选修课程的学生的基本信息;selectfrom S1 SnoSnarneSSEDCSageSdeptWHERE not existsIII FWMLIHU-rWMITI 95003 I

28、王敏卑20痉(select *295013王媚15from SCWHER匡S nc);16、查询选修了 3门以上课程的学生学号;select Snofrom SCSnogroup by Sno1 sMia'ihaving count (*)> 3;17、 查询选修课程成绩至少有一门在80分以上的学生学号；select disti netfrom S, SCWHERE= and Grade >80;18、 查询选修课程平均成绩在80分以上的学生学号；select disti netfrom S, SCwhere = and in(select Snofrom SCGROUPB

29、Y Snohaving avg( Grade)> 80);19、(选做)SC20、 (选做)找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排列。实验四视图的定义、使用实验实验四成绩：、实验目的熟悉和掌握对数据表中视图的定义操作和熟悉和掌握对数据表中视图的查询操作和熟悉和掌握对数据表中视图的更新操作和SQL命令的使用；SQL命令的使用；SQL命令的使用，并注意视图更新与基本表更新的区别与联系;学习灵活熟练的进行视图的操作，认识视图的作用;二、实验方法及步骤1.视图的定义2 .视图的查询3 .视图的修改三、实验内容：以students ，courses , sc表为基础完成

30、以下视图定义（使用SQL和企业管理器两种方法）及使用1.定义“ IS”系学生基本情况视图V_IS并查询结果；2将students ，courses，sc表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_G并查询结果；3将各系学生人数，平均年龄定义为视图V_NUM_AV并查询结果；4.定义一个反映学生出生年份的视图V_YEAF并查询结果；5将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_（并查询结果；6. 建立视图V_AVG平均成绩为90分以上的学生学号；7. 通过视图 V_IS，分别将学号为“ 95001 ”和“ 95004”的学生姓名更改为“ S0001” , ”

31、S0004”并查询结 果；8. 通过视图V_IS，新增加一个学生记录 （S0012' , ' YAN XI ' ,19, ' IS '），并查询结果；9. 通过视图V_IS，新增加一个学生记录 （S0013' , ' YAN XI ' ,19, ' MA ），并查询结果；10. 通过视图V_IS，删除学号为“ S0012'和“ 95003”的学生信息，并查询结果；四、实验结果以students ，courses，sc表为基础完成以下视图定义（使用SQL和企业管理器两种方法）及使用Sdept Sdept_count

32、Sage_avg12 Will! Ml mi csIS41332D1.定义“ IS”系学生基本情况视图V_IS并查询结果；create view V_ISSnoSnam 已SsexSdeptas1JI IIM JIIIIBIHIIIKIII 111i rao5 i刘晨女20ISselect *295010s22ISfrom S3K013王媚玄20I5where Sdept ='IS'；2.将students ，courses，sc表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_G并查询create view V_S_C_G$noSn ameCnoCraneGra

33、deasiiiiiiii n-gHiimiiHirii'i1沖5013 i壬媚3信息亲统55select , Sname, Cname Grade295009陈慷250from S, SC, C结果；where = and =；3.将各系学生人数，平均年龄定义为视图V_NUM_AV并查询结果create view V_NUM_AV(Sdept, Sdept_count , Sage_avg)asselect Sdept , count (Sdept), avg( Sage)from Sgroup by Sdept ；4.定义一个反映学生出生年份的视图V_YEAF并查询结果create

34、view V_YEAR( Sname Sbirthday )1n iiFani李明11翊2王做1M33刘晨1993451W1£孝浩1王媚1M3Srame Sbirthwia/asselect Sname, 2013- Sagefrom S ；5将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_（并查询结果；create view V_AVG_S_GSno, Cno_count, Grade_avg)asselect Sno , count (Cno), avg( Grade) from SCgroup by Sno;6.建立视图V_AVG平均成绩为90分以上的学生学号;cr

35、eate viewV_AVG( Grade_over90_Sno )asselectfrom S, SC where = andin(select Snofrom SCgroup by Snohaving avg( Grade)> 90);7.通过视图V_IS，分别将学号为“ 95001 ” 果；update V_ISset Sname='王明'和“ 95004”的学生姓名更改为“ S0001” , ” S0004”并查询结where Sno ='95001'update V_ISset Sname='王强' where Sno ='

36、;95002'8.通过视图V_IS，新增加一个学生记录in sertinto V_IS (Sno, Sname Sage, Sdept)VALUES( '95005','宋明','19' , 'IS');in ame胡零1 95M1李明21CS236002王强女19IS395003王锁20csJ主芳NULL19WIA595OTS里20C56&501C卑22巧795012垂皓女18CS895013王媚女20S0012' , ' YAN XI ',19,'IS ')并查询结果；S

37、noSriameSsex.SageSdeptbnnil irMim HHBinag95001：再21CS2即 5003王融20匚吕395005宋明MULL19154手 5009卑20CS595010陈杰22156&5012聿浩女18CS7&5013王媚2015(9.通过视图V_IS，新增加一个学生记录in sertS0013' , ' YAN XI' ,19, ' MA )，并查询结果;into V_IS (Sno, Sname Sage, Sdept)VALUES( '95006','王芳','19

38、9; , 'MA');SnoSnarme気exSsgeSdepti3&66?!卿月臭21C5.敘亍”王敏20CS39W05NULLBIS*»0OGNULL19MA5起匚5怛10.通过视图V_IS，删除学号为“ S0012'和deletefrom V_ISwhere Sno ='95005' or Sno ='95006'95003 ”的学生信息，并查询结果;结果區消息10Sname5眄吕5 dept195M1事明21CS2啊3王敏男20CS3 95006 |NULL19MA4GIIILI «l I 1 FL Til II IB I /S20csS9501DfJSS里22IS£9M13季洁女15CS7HQ13王媚玄20IS