北邮数据库实验报告(3)

1、数据库实验报告（三）姓名：学号：班级:1.用Transact-SQL语句、数据导入、SQLServer ManagementStudio（企业管理器）输入的方法对所给定的8张表的数据输入到数据库中。自行决定每张 表的数据导入办法，但每种方法各针对二或三张表。Tran sact-SQL语句：导入departme nt, stude nt, stude nt_cours表。insertinto departmentselect * fromope nrowset(microsoft.jep.oledb.4.0,database2.xls , department$ );insertinto stu

2、dentselect * fromope nrowset(microsoft.jep.oledb.4.0,课件数据库database2.xls , student$ );in sert intostude nt courseselect * fromope nrowset(microsoft.jep.oledb.4.0database2.xls , student_course$ );数据导入：操作：选中数据库studentsdb右键-任务-导入数据 导入book, class, cours表。课件数据库课件数据库法助C上一步（aII下一步（】取內txcelEMC.1裒懈左了嘰QWHIWgtt

3、U!挾用与垃月殛iblH寰小Exel报S匕F?首行包倉列4*0）；QL ServarNBIiv 01 tank乂T0P-70仁:?WTre5ClSEiTL2C梗舄S31 S“、y月臧证（0用户FJ0S（D：俞曲Ex0lTfSX 祁（!）和目标蚀帮片OP上一协Q）下一于“| ；亿 | 财& SQL Server牛入向牛 Hl-个或纟个表咖SI她KQ肚迟顶出于貝別片.找捋牛字以有杰或祕21刊空部尅捋。厂竭与鱼詢戦硫要KMk榔凶此选项用刊跖SX箜询以佈按訓慢ET期讯芒仃魏西朴J SQL Server导入如土彌导达曉咬密霭冷乘T S： 乐件WiE AtEH k甸垃用咎氛：J?F刈冃低l/PTC

4、 344CPWJMSSGLSEA；EP2J 来BF来厂9 dbcj. onrso73 * ft B-3，谦1二dbol Ccl2咖(Mgdcp(M_sOCf112OOJ/2OO3210450敎數S6O4_s0C2JOIdt*p04j003dt-3tM_skD3g9m211的敷2000110410JSH212dep3i0C3_01derpJXUdi?pO4_bO01邦40311200W002310S20depfUsDMGIdepW.WHdepCH门34gf?9O21200昭OW22fK24?字君己UJrpO4.5OT5.Oldfrp04_004dep(H/34卢4G312OOV2OO221051

5、2/fLlWLLwaNULLJVWCZNUUNg导入结果:Book：100% *理结果康消息bOQ_l1book_najneTtibl i sh_C ompanya.utlwr pri c?eb pCL_sO01_Ql!电隔波工程电子工业出版社顾华212dep04_b001_01计算机戛础猜华大学岀版社洪涛IS3AepCO001-02计写机葩用电子工业出版社李群19.3卜d( (p04_p001_01f语言程序谟计溝华大学出版社钟军IS. 35lip04_E001_01SQL箕曰埶摇库开发技术北方交通岀版社成虎2156dep04_SD02_01拠语言程序设计东南大学岀版社王甲22 5hdp04

6、_003_01单片机原理东南大学岀版社肖紅16.3teatb&r idte*cher nametertiidepa rtiTifriTt kprafeiamtelfrpbonehome addrgdep01D011156g09dep_016211M4師先兰辭1号210WepO.Din11-!M9-re-06depw02139516O57M盍思*忙”.21depOSOTl住 T11960-01-01dcp_0l3372S543U 崔匚号护Drg&e-M-nidrp_D1lvwrSBS7654号1dep04 30H紀云11965-09-09HMI ani矢T_QLilepartm

7、ent_D.ani e!无线电depar tmant_Keader王敬远teajcher22dep 02ii信与信息工程廉辉13dejjOS电子工程董一平14d即IM计算机科学纪云5Stude nt:Q童值已成功执行.LAPTQP-7044OPDMMSSQLSERVER结果盟消息1atiidet_idshlden!：_nair!e弓HLS&XKirtkI9fl4-O9ilntrar.ce_lite1999-0901koii6_aldr南京币疑橇区古平崗3m&ilKULLclAES_id缪402:94Q20：lAi 8 8 8.vSBiBaaBHaiBsX2声40202林红f19

8、4-06-0119*99-09-01删M五卓衔m号VULL3卿40203曹雨11963-12-011399-03-01南京玄武区林确大道4mi94024縛4O2CK辞11963-03-091999-03-01江茄仪征市青年路&号NULL紳4025牺40205(1964-09-061999-09-01江苗仪显市青年东路1NULL或9402$齊迪宾糯国ige4-O4-oe1900-0-01扌僚市中対扶平北路HULL94021卿40207李红小(10S4-OB-231999-09-01輛恵昵1匕齡塢NULL或940284021011965-01091399-001南京中山北路M号mi或940

9、291laes-onj?1999-09-01躺中山北路 B 号NULL94021040301左群声11905-0 OT1399 OBTiNULLNUI1940311縛4030201湘3TO-1Q1999-0301卜循市並盘路s禺tnju羽94M12940303弛雅丽(19641-011999-09-01上海市北京东貉1时NULL940313妙4们04(1964-0409i轴gm南集中山陷9是mi9403Stude nt_course:ffi 结果审消息1coiir se1 ddep04_lb001stu.dent_i d( (3940201gxaleasCFe JJ. t4semester0S

10、CLLOQL year1399/200024ep(M_lKL59402027SA01翻曲 E3ltp04_E001gMS4020388401999/2001?4dep( (M_bOUl8394020456001399/20005epfMjOOlSJ&94C20E45001959/2000hpOODOL那4吃M98401999/20007dep 043 00194020787401999/EOOOdnpM_i00i40201S9E12001/2002SdepMsOOl繆4吃垃78E12001/200210dep04_ED0L59402034401001/20011dtpCMOOl或940

11、如4旳E12001/200212depCH-gOOL6394020552012001/200213depD4_sD01940206佩512001/200214depD4_i0Ol4020777E12001/200215dep 0400 L94030173502002/200316depD4_sOOig!994030263502002/00317dp(M_iW2辭94畑0156C12001/200218-Iep04_s00240202784I2001/200219dtp04_s00?99400399412001/2002如4pD4_i002繆4020466412001/200221l&p

12、D4_s0O259940205能412001/200222depD4 iD0294020654012001/2002Teacher:toch*r_i4ts-tck*r_sn 4亡冈birtlIc-p-ar trfi*iii_a 1prafEL二nt.Lbphan野karri*_s.lrJi tt i&m &.Oi:dejOlJJUlIMEBV K! 土劇3*Ld.Tp_QBKtrS6?4写弟魅:路Jl昌21EOOEBULL2depLE-QOHBE豁L爭购爭购iDBTJip_(E担授336HjUtT&4阐劭鞋桂国fedCSS于21E00:：FJLL3k?KJ_aoi董一平

13、11?68HD-QL址_033?285fl3葛紺6T融昱2iUi3&FILL氐如gjice|昨雷昨雷TiT Lhp.CM5aaTS4頁京忙工路电2LDQiBILL5dtjM.aai1dfP.iH购购53EI勇京弋江蹈号2IIKKITJLL6dD*JI(X?和IQ阳阳*卜卜04d*p_(N302#eieO22即员怎酹3嵋21OM3U1JLL7站M QW1H貧叫貧叫叩叩&dtp JH3圧目鬧圧目鬧3抚闪龙抚闪龙fJLL.8L11*璋璋FMH询T融融Hi岡岡FITJLL帝IDiggMML罚肌Ml存踊嗨simnTeacher_course_class:囲结果事消自1*wh.；1gmrh

14、u.id.IdpQl_sOL715_i1Cim“d.i 4d*por.i0oijii IspJl.DOl2lepJl_O51iepUl_SUUL现aa12OD1AIMJEl2CJKUb2U屆pUl_sOUl_Ll3J.ap04._sCOl曰M肚1sooioraL ID3E2JcpOdJiDOl半4icpJOOllcp04_5CO5MD212QQ：住0田L30310511就学擁肚0-J_pO( (Jl_Cl&iq34_D0Zdpa4_pcoigjmOB11993/20U)LJDZDJtpM_pOOl_CL6lq) )(MJ)0EIftDO-CsOO?或!M(J212002/2003104

15、20封字瞬 EWjOOl.Cl7lep5_Dft3iepCsCOS?&40212001002L20410穀学寒11lep(M_sDOE_Cl3IspMJOOOdepC4_J005gMDZ1J999/2QOOL10410教学槪1iep( (M_sOO( (3_CL9kp04J)Mi*pU4_KJ01刈辄盟120时72002$10520dfl) )W_EQ04_ClIDlc：p34jp0-lJ.ttpC4i_C04. s9C4D2120D2/20CS220521o；列不亢讦肖Null隹c INSERT夬L禺洪iL2.用Transact-SQL向Course表中插入一条记录,course_

16、name为空，看运行分析：course_nam有not null的约束，因此这条语句不能执行。3.用Transact-SQL修改Course表中credit为5的记录，将其credit改为7,credit小于4的改为2,看运行的结果SQL语句：update courseset credit =7where credit =5;执行结果：圉消息涪長547，裟别16坎感（h篝:fjETftTE语句占cgECF肉费ClEJt庚.诙：审突呉生于载据專工蚀丸时事血1Wdbo.CQUTJI-,co 1UTOTL1，crp4itr,氓包已為止，分析：约束C1指定了credit的范围为1至6.SQL语句：up

17、date course set credit =2 where credit 4;执行结果:W； %4.删除一条学生记录，看运行结果，对运行结果进行分析SQL语句:delete from studentwhere stude nt_id=g9940201;couiEft_idccpurse_iiMebgb_；dtot al_periorweel：_per ior crediti lep01_E001电菠工程4ep01_sODI_018066dip04_b001计尊机坯础depC4_h001_016044JepO4_pXlC语言程设计4ep4_p00i_01希44dj*pO4_0Ol SQL弘纱

18、弐频据库开岌技祇a&pC4_s001_0.18055dep04_s002丁则帰序设计与开发dpC4_sOO2_Ol沛44Jep04_s003单片机原理dep04_s003_01se44hp04004釈件幵发技术depC4s00401513oIep04_s005网页设计depC4_g005_013055籍果EP渭息46.8LAPTOP 7O44OPDNM OBSERVE FU IAPTQP 7J44QPDISUPTJ rtudfntidb OOiOOiOD 0庁执行结果：分析：因为有参照完整性约束，不能删除谒皂.64 ,叙-优咨01亍EELETE谱聞与ftEFERENCE期佗_sz ud

19、efit_eour se_stkide tit*彳5!邃1=1嘤港号千曲强a tudefit9dbH语旬宙止.5.用Transact-SQL完成将编号为dep04_b001的课程的选修信息插入到一个新的选课信息表中。SQL语句：Creat table student_course2 (course_idchar (20),stude nt_idchar (20)grade int5credit int5semesterint ,school_year char (20)，primary key( course_id , student_id );nsertinto student course

20、2select * from student coursewhere course_id =dep04_b001;执行结果:睜结果銅消息cours&idstulent=idgradecreditsemestersckool_year1 LpD4-E0Ol j594020270401393/20002dP( (MjbaOl53940203SS401999/20003deptM_bOOL级4C加456001999/50004Jftp04j00194020B45001999/20005depCW-bOOlS394020S98401959/20006deptM_b0Ol级40血87401599

21、/50006.用Transact-SQL完成删除单片机原理课程的选课信息，分析运行结果SQL语句：delete from student_coursewhere course_id in (select course_id from coursewhere course_name =单片机原理)S，!idbo . s z；udent_eojf *e teel lannpa tude&idBB执行结果：分析：所有课程号为depO4_sOO3勺课程被删除。結杲Ei消見conj5e_ids t ident_i dgradecreli t;hool_yearLj d曲U4_b0tH !飙402

22、0278401999/20002dtpaijooi9408033341999/20003Jep04_b001S4O2O456001999/20004depO4_bOOlg?S4O2O54501999/20005aapa4jooi凹94020693401999/20000JepO4_ljCiOl37401955/2000?dep04_00194020278512001/20028dap04_i001删402034412001/20029dep04_s001缈4020465512001/2002L0d5pO4_0Dlg?4020552012001/200211JpO4_iOOl9402069251

23、2001/20021Zdep04_s0015554020777512001/2002L3dap04_s001S394O3O178502002/200314depO4_iOOl99403028952002/200315dep(J4_sOU2伊40202?S412UQ1/2002L6ep04_sOCl2g?94O2O398412001/200217aspa4_tOQ29940204eg412001/2002闿dsp(J4_50U2期4020E7S412001/200219Jep04_s002g394O2OS54012001/200220dsp04_50Q2g394O2O737412001/2002

24、事depOAsODJ斜4Q2Q295312001/2002Jep04_5004084020370312001/2002本实验中遇到的问题和解决方法:本实验的顺利完成需要预先作很多准备工作。以下就是我在遇到缺少组件ccessdatabase en gir时的解决过程的记录。Access Database Engine的安装accessdatabaseengine用于和office连接，导入导出数据，本实验中需要导入excel文件。安装配合office的版本，我安装的是accessdatabaseengine2017（English）版本。安装32位版本，因office2016是32位。之前误操作安装了不能使用的老旧版本accessdatabaseengine2007,