数据库实验数据操纵及数据查询

1、实验二数据操纵及数据查询目的和要求(1) 学会用管理工具和 T-SQL命令中对数据库表进行插入、修改和删除等操作；(2) 数据更新操作时要注意数据完整性；(3) 掌握基于表与视图的简单查询与复杂查询。实验准备(1) 了解对表数据有哪几种更新操作；(2) 样本数据录入；(3) 回顾数据完整性知识，在进行数据更新时保证数据的完整性。实验内容一、数据更新insert语句注：如果插入的数据与约束或规则的要求产生冲突，或者值的数据类型与列的数据类型不匹配，insert将执行失败。【练习1】将样本数据通过工具或 T-SQL命令录入实验一中创建的三张表，并通过管理工具查看S表中的全部数据/* Script

2、for SelecETopNElows 匕亡rMtindl frcrt 5SMS */ SELECT TOP 1000 snojf snarte (speialityr sexrbirthdayf totalcredit J聿ccnniient f entranceuime：mCM【宋皓- Edb5 .出n菇果里崛BHOsnamBspeialhysexbithdaytotalcredtconmerlentrancEtimE1:D0H15:刘期仪计算机男197S-QM2QO:OaOO-NULL201 m29 15:1022 23321101繇计算机骂190502 00:00.00.NULL2D1

3、M3-29 16JD.32.96331102程明计算机里19054&12 00:00:00*NULL20134)32916:10:32,98041103王艳计宜机女1郭乜乐驾W;(X)-0Q,NULL加1班291米世辑瓢5nos林一帆计菖机男190*31 D0:0(W)0耳巳提前修芫门课程201329 16:10:32,53061110僦计算机女1905415-17 00:00.00*三好学生2013-0y291&1D.32.93071勒1王做通信工程里19C54&27 OQ：OODONULL201341291C：W12.98O21204马琳琳通信工程女1905-06-31 00:g 00*N

4、ULL2013-291：10-32.9SO91216髀瞰通信工程男19O54S-31 DO:OD.DO-9NULL20103-29 16:10.32.9801D1221文撰演通信二程女1905-05-21 30:00 DONULL2D134R.2916:10 32,930111?41满恰T理七imMB-umniHin.狂士心学91制总俯10普酬1【练习2】使用SQL命令查看R表中的前5条选课记录El SELECT TOE 5 3ur eno,1 gradeFROM l宋皓.；dto . CR3结果心癖sno cm grade1Hoi I 101302V 031016231104W1904110

5、7W1785110810185姓名、专业名【练习3】创建表S1 (sno,sname,speiality),将S表中 计算机”专业的学生的学号、类信息插入到表S1中，并用select语句查看插入结果。 select snc r 5ilsjt.z . 3rez.aliy from*- B目结果显消息snosnamespeiakty1I刘明仪 .J计算机21101王琳计算机11102程明计算机*11D3王艳计算机51108林一帆计算机31110张维计算机11201王融通信工程1204马琳讲通信工程1216孙祥欣通信工程101221刘燕徽通信工程111?41均处1#UPDATE语句【练习4】更改S表

6、数据，将学号=001101的学生的备注列值改为二好学生（将执行通过的SQL语句记录在此）。El update 3u匚rrir.wn5三好学生 where ？r.cDOHOl I is消息门行受影响，【练习5】将姓名为 罗林琳”的同学的专业改为 通信工程”，备注改为 转专业学习”，学号 改为“001241；并将S表中所有 通信工程”专业的学生的总学分都增加10 update 3sec 3匕eial.工七丫小通信丁程，r ucmraens转专业学习3 sno=O01241 where巴力皿，罗林琳，a update Rset gxade-gxadelOwhere ，通信工程select speia

7、lity from swire re. sueC干受番响）：，行受影响）DELETE语句【练习6】删除S表中的学号=001101的行a delete from s whereJ 50=001101工消息（工行受影响）【练习7】删除S表中总学分小于40的行 ：,deletefrom 3 whereL ccLalcredic,4013013.5227.350101241妻妆讲懂信工立019054)5-14 QQ 00.0050转专让学习201MI3-3C13 5227 350【练习10】查snosno询S表中计算机专业同学的学号、姓名和总学分，结果中各列的标题 分别显示为number, name和

8、mark。（将执行通过的 SQL语句记录在此） select sno *, STiair.e *an.e, F tc taler edit * ica r k1fron: s where-speialicy=，计算机number neme markparii-iiaii-im?imairirj1 i 1101 ;王 50J r，-，e r，e m r r 事21102程明50311D3王艳504110S林一帆5251110张维50【练习11】对数据库的S表只选择专业名和总学分，消除结果集中 select distinct speiality.totalcredit-from s导 p 口己址y

9、totalcredrt讦前50计算机 通信工程 通信工程【练习12】查询数据库 S表中通信专业总学分大于等于42的同学的情况（将执行通过的SQL语句记录在此）。fselectsnaxne, spftiality, ssxt tjircJiday, totalcredirf comr.ent, from s where-speiality= *通信工程1ctalcredi=2i s结果信消息“15nosnamespeialitysexbirthdaytctaloBditccmmenterrtrancetime11201 !王敏通信工程1190)27 00:00.0042NULL201 黄以30

10、13:52:2733721204马擀琳通信工程019QM111 00:000042NULL20134)30 13:527733731216林祥欣通信工程11S054)31 0D:D0,0042NULL20134)30 13:52:2733741221刘燕敏通信工程0190M5-21 00:00.0042NULL2013-03-30 13:52:27,35051241罗林擀通信工程0190X)514 00:00 0050转专批学习20134)3013:52:27 350【练习13】查询数据库S表中计算机专业的学生情况。查询数据库S表中姓 主”且单名的学生情况（将执行通过的SQL语句记录在此）。日

11、 selectV.Q rsnaine r speialicy, 自已3c, birthday,ocalcredic, coiHRent r ent rariee tints from 3 where-speiality-，计算机， snsn.e 二1few ，王以结果13消息 5nosriamespei 洲 itysex.birthdaytotakredftcommententrarcetime 11行1 王琳计算机11 邮W02 OOMDO50NULL2011013013:52:27.303 行而王艳计算机G190W&28 DDDQOO50NULL20133015277.303【练习14】查

12、询数据库 S表中不在1989年出生的学生情况Rselect3 no, sname P speialley, sex, birthday totalcredit/ corment entrancetime f rarti s where-birthday naz like *1989%r结果A消息snosnamespeialiiysexbithdaytotalcfEoitcommentertrancetime1101王i#计算机11905-02 OOOOM50NULL2013013 52:27,30321102程明计算机119054&1 ? OOOODO50NULL20134X0 13 :52:

13、27 30 J31103王艳计算机01905415-20C：D:DD5DNULL20133ri0 13:52:27,30341108林一帆计算机1190545-31 00:00:0052已提前修完门课程2D13(3rX 13:52:27,33751110张维计算机019054)M7M:OD：()050三好学生2013413-30 13:52:27,33761201王敏通信工程1190527 00:00 0042NULLj 20134J3r30 U 5227 B7 j71204马琳辩通信工程0190505-31 00:00:00A2NULL201K”30 1152:2713781216林祥欣通信

14、工程11905-D5-31 OG:DD:DO42NULL20133-3D 13:52:27,33791221刘燕徽通信工程0190M5 21 00:00:0042NULL20130 13 52 27 3501。1241方林塔通信工程0190M5-14 00:00:0050转专注学习2013mg 13:52:27350【练习15】查询数据库S表中专业名为 计算机”、计算机”或无线电”的学生的情况 臼 selectH门口.snaiiie.f speiality,sexf birthday, totalcreditr conmenc.e 门七 rancet line fr o io s-where

15、speiality= 计算机oi speialiUy=，i!无线电，口结果消息和口snamespealitysexbirthdaytotakeditcommententrancstime11101 ：王琳计算机119054)2 00;D0W50NULL2013013:522730321102程明计算机1190e-12 00：Q0W5GNULL201 切多甥 13:5227 30331103王艳计算机0150W&23 WQ&0050NULL201330135227.30341103柿一帆计算机11905-05-31 OQgQO显已提前修完门课程2D1 0330 13:52:273375111D雌

16、计算机a19OM&17DO:QttO050三好学生201343r3D 13:52:2737【练习16】查询数据库中总学分尚未确定的学生情况（将执行通过的SQL语句记录在此）SELECT * FROM S WHERE 总学分 IS NULL【练习17】查找选修了课程号为101的课程的学生的情况(将执行通过的SQL语句记录在此)。日 select sn.Dr 3name# Bpeialityf sexr birthday tQt：alcx?editf ccnment f ent rance time frema s where*1011 in (select eno frons R whre R.

17、snc=s ano m已奉果昌消息sia sname spealityir-iiiiiiiinm|1101 :王耕 计算机sex birthday190546200 00:00entrancime20134)3-34) 13:52:2710321103王梢4f一帆计算机11WWJ5r31 0000:00蟒计算机D19054J5-17D0:0D:DQ王勤通信工程113054527 00.00:00林祥欣通信工程119QM1M 100 00:00力博地通信工程01505gzi DO-WM孰协通信工程0190546 14 DO;00:00计算机 0 1WMJ5-2S DO 00:0050NULL2Q

18、1M3-3013 52:27 30352已提前修完i理程20134)3-3013:52:27.3375D三好学生20134)3-3( 13:52:27,33742NULL20134)3-3013:52:2733742NULL20134)3-30 13:52:27,33742NULL2Q1 却$30 0 5227 湖50转E学习即 13g30 1152J7 350tetn credit commentNULL将执行通过该SQL命令的界面截图于此）【练习18】查找未选修离散数学的学生的情况( birthdayr tcalcreditP commentf entrance t we fraiE 3

19、wJiere bro 口口七m ( select sno frm R where eno in( select eno from c*here .离散数学.- select cname tram cwhere R.cn口 a and Ftrsncahino -111结果,全消息isnosniamespealitysexbirthdaytatalcreditcommententrancetime1| 1201I王敬通脩工猩1190X)427 00 00 00A2NULL201M301511M 83321202王林通信工程11905-05-14 W:00 00有门功课不凝格，待补考201V0* 却

20、 15:11:56.833312D4马琳琳通信工程019031 时:00 : 0042NULL2013nD3-3O 15:11:56.3334121D李红青通信工程115054)6书7 伽:的0044已提前修完门课程，并获得学分201 知上 对 15:11:56.83351216林祥成通信工程1ISQMSI 00:00:0042NULL2013-03015:11 56 3336刘燕融通值工程0190时421 00:00 0042NULL20130151156 8337U41罗林琳通信工程0190W)5rH 00:00:00W传专业学习201X)*3015:11.56 333【练习19】查找比所

21、有计算机系的学生年龄都大的学生（将执行通过的SQL语句记录在此）Owg.anAKi&j speialityr aexrtotalcredit r Connnent; f ent rance txre.e from 3 whfe birthdays (3 el eat jtjl： . (b xrthday frem 3 where speialitsno sname王琳 福丽王艳 李明林张维 严红计算机，过的SQL语句记录在此）国结果消息snosnamespeiaitysettxrthdaylotalcredrtcommententrancetime1 i闵i =口 . ;王敬通信工程11B8S

22、O&10 00:00:0042NULL期孙 30 15:3243,72321M2王林通信工程11381 29 00:00:00如有il功保不期，待扑着20134B-30 15:32:43,723312B4马林时通信工程0198&O2-10 0000:0042NULL2013-03-30 15:32:43,72341210杂工青通信工程119(mi5-01 00:00:0044已提前信完门祟程，并舞得学分2013-0y30 15:32:43,7225121S孙祥欣通信工程1198aO34H 00:00:0042NULL2013413-30 15:32:43,723【练习20】查找课程号206的成

23、绩不低于课程号101的最低成绩的学生的学号（ 将执行通O日 selectbhc from R where grade = select niz. (grade from R where cno=101-)三二日 cnc=206|二结果逍星EXISTS子查询EXISTS子查询和普通子查询不同的是：一般的子查询，内层查询只处理一次，得到一个结 果集，再依次处理外层查询；而 EXISTS子查询的内查询要处理多次，因为内层查询与外层查询中的鲜号有关（即相关子查询），外层查询中S表的不同行有着不同的学号值。因为子查询的条件依赖于外层查询中的某些值，所以EXISTS子查询的处理过程是：首先查找外层查询中的

24、S表中的第一行，根据该行的学号列值处理内层查询，若结果不为空，则WHERE条件就为真，就把该行的姓名值取出作为结果集的一行，然后再找S表的第2, 3.行，重复上述过程直到 S表的所有行都查找完为止。【练习21】查询选修206号课程的学生姓名（将执行通过的SQL语句记录在此）。曰 selecc stajEm frore s where 206_：-sel&ct cnc from 宜where 5.3ncR-s-c结果一消息【练习22】查询选修了全部课程的同学的姓名（将执行通过的SQL语句记录在此）臼 select snainefrom awhere 二二七 k二三二彳select . from

25、c where not 三式工3七品 select , from R where R.sno =s.ano and R.cno=c 4 eno 一）SQL【练习23】查找选修了 206课程且成绩在80分以上的学生姓名及成绩（将执行通过的语句记录在此）。 select 3,fR.grade i where R,sno=s,sno 3nd R* sno in select sne from R where grade80id one n 匚 . select eno from c where crLaiLe=1 y 卜算机基础（国结果消息snamegrade1!林一帆！ 七iiiEiiiiiaii

26、ii.052张维953王敏904郁祥欣91如刘燕敏866穿林拼1007林一帆&4S张维9林一帆&710张维39【练习24】查询选修了 计算机基础”课程且成绩在80分以上的学生姓名、课程名及成绩臼 select s . snair.e . R. grade * c. cnaice f r-oiti sf R, cwhere R.cno=o 3二3 R.sno=5.sna and Rsno in ( select sno from R wiiere gradedSOaTid one 工匚 ( select eno fram c where cnair.e=计算机基础， ，Iw结果消息snamegr

27、adecname1i麻一敏：、，FH Hl 1E E 1 1 Fl H1 m .85计算机基础2张维95计算机基础3王敏W计算机基础4林祥成91计算机基础5刘燕康晓计箕机基础E罗琳琳100计其机基础7林一帆64程序设计语言8张维90程序设计语言9林一帆87离散麴学W张维89离散数学【练习25】查找所有学生情况及他们选修的课程号，若某学生未选修任何课，也要包括他的情况说明（将执行通过的SQL语句记录在此。（提示：使用外连接操作，左外连接LEFTOUTER JOIN ,右外连接 RIGHT OUTER JOIN ,完全外连接 FULL OUTER JOIN ）E3 seleccS SElQj 1s

28、玛也小型iiSCKrbirthday,.-tSTi!Llet*dl.Er conni!encr entranc-et i/rae Che from 3 left join R on ntbR-nmL 耐甚2/2 3 _R . cm结果心消息明ameBpa则触birthdaytMakm dtowwienlentrancfliimeeno1! 1W1珊计算机11地也加曲Oft50NULL2013g3(115.3243 7D71012王照计葬机DwNUIX州 1乳1301532 抬 707向3ne?趣计苴机11S9O-C5-O1 峰处皿5提前诙完“冲揖结构，并承得学分201W-3015-3?J3?n

29、7Wi4l1103林一帆计苴机11器双以05 OftOttOB52已提前偿门课程州 13440 15:3243.7231015111D阚计算机019S1-D7-22 NkOOOO50三好学生201M3-3015:3243.723iaif1113当I计算机01加各0411 W.00.00和助津襁心待补君2(HW-3(H532437B101711壬成通信1iseaicnjfrooc41NULLMimao 15-3243 723totgTSE王#通情11383-01-23 CftOttOO40有门蝌不属格，待同者201M4和 15:羽m9121D较（吉谩倍.1四眸曲 OttOttOO已提前健宸门课程

30、，弃蔡母学分20134301&3S43.723101W121卧祥欣tt11 邮smos oo：o(i.oo般NULL201 WrS015.3243.723101iwiiI3Stn力 MfWVMhJL II nVHJUYL-Vl 14H i，mifli【练习26】求选修101课程的学生的平均成绩，要求结果表显示列名为课程101平均成绩”臼 selectAV r (g工ade ;课程;平均成绩* from R where cnc_101m 结果 0 msI 课程101平均成箍! r I ri，l，ri I i r I I m I I m I ri r I in，m I l， i e，n，n，31r

31、J_网IJ i -，ri，mi ri i/，ri i V，ri i ri r，e e i ri e r，-，r e，rn?【练习27】求选修101课程的学生的最高分和最低分，要求结果表显示列名分别为郡程101的最高分”和郡程101的最低分”（将执行通过的SQL语句记录在此）。臼 selectgK旧一日日，课程2G的最高分3IWJyradd，课程LCH的最低分 from R-where cno=lQl西果直消息课程101的最高分课程15的最低分! n ： n hibibi 1 ib 1 e 1D062E - -1J r, i Ft r i mi i e m，e i i e l i e ! i i

32、 e T【练习28】求学生的总人数，要求结果表显示列名为学生总人数日 selectCOUNT学生总人数，-f TOIL 3Q结果|为消息 学生息人手”namaui-aa-id，！1 j 14huUlUUIfalMIMUIUMIUMIUIJI t【练习29】求选修了课程的学生总人数0 select匚CETT distinct 一口选修课程学生卷人数，-f TQIE 良结果m消息选修课程学生1 j 14【练习30】统计离散数学课程成绩在85分以上的学生人数，要求结果表显示列名为离散数学85分以上的人数”l- elecu:CUNT l 一口；，离散数学8 5分以上的人数， fraiE R where

33、 gradeB5 and eno in ( select eno from c where cmaim，离散型学(国结果白消息离散数字8粉以上的人数【练习31】将数据库中各专业名输出（即只输入不同的专业名称）select distinct 辱已二三2士ty frorri 巴【练习32】求数据库中各专业的学生人数 select distinct speialit;y 君而文 * , count(speiality)，人数from s|L group by speiaiity7亩结果品.消正 学业名 人数 miBiEiiBiBimammBiMiBiMii !计算机I 7 Hrainra ? nraiBiiaraH1 通信工程7 提示：结果表显示为两列，一列为 专业名”，一列为学生人数【练习33】求被选修的各门课程的平均成绩和选修该课程的人数，显示结果表列名分别为: 课程号、平均成绩、选修人数 select eno,课程号3avg(grade) :平我成绩I-七3n选修人数，Cxom R-group by cna结果.3消息黑程百平均成绩远修人数1 Fibi-jsi132 10277S3 206758【练习34】查找数据库中平均成绩在 85分以上的学生的学号和平均成绩，结果表列名显示 为 学号”和 平均