北郵數(shù)據(jù)庫(kù)實(shí)驗(yàn)報(bào)告(3)

1、數(shù)據(jù)庫(kù)實(shí)驗(yàn)報(bào)告（三）姓名：學(xué)號(hào)：班級(jí):1.用Transact-SQL語(yǔ)句、數(shù)據(jù)導(dǎo)入、SQLServer ManagementStudio（企業(yè)管理器）輸入的方法對(duì)所給定的8張表的數(shù)據(jù)輸入到數(shù)據(jù)庫(kù)中。自行決定每張 表的數(shù)據(jù)導(dǎo)入辦法，但每種方法各針對(duì)二或三張表。Tran sact-SQL語(yǔ)句：導(dǎo)入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,課件數(shù)據(jù)庫(kù)database2.xls , student$ );in sert intostude nt courseselect * fromope nrowset(microsoft.jep.oledb.4.0database2.xls , student_course$ );數(shù)據(jù)導(dǎo)入：操作：選中數(shù)據(jù)庫(kù)studentsdb右鍵-任務(wù)-導(dǎo)入數(shù)據(jù) 導(dǎo)入book, class, cours表。課件數(shù)據(jù)庫(kù)課件數(shù)據(jù)庫(kù)法助C上一步（aII下一步（】取內(nèi)txcelEMC.1裒懈左了嘰QWHIWgtt

3、U!挾用與垃月殛iblH寰小Exel報(bào)S匕F?首行包倉(cāng)列4*0）；QL ServarNBIiv 01 tank乂T0P-70仁:?WTre5ClSEiTL2C梗舄S31 S“、y月臧證（0用戶FJ0S（D：俞曲Ex0lTfSX 祁（!）和目標(biāo)蝕幫片OP上一協(xié)Q）下一于“| ；億 | 財(cái)& SQL Server牛入向牛 Hl-個(gè)或纟個(gè)表咖SI她KQ肚遲頂出于貝別片.找捋牛字以有杰或祕(mì)21刊空部尅捋。廠竭與魚詢戦硫要KMk榔兇此選項(xiàng)用刊跖SX箜詢以佈按訓(xùn)慢ET期訊芒仃魏西樸J SQL Server導(dǎo)入如土彌導(dǎo)達(dá)曉咬密靄冷乘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敎數(shù)S6O4_s0C2JOIdt*p04j003dt-3tM_skD3g9m211的敷2000110410JSH212dep3i0C3_01derpJXUdi?pO4_bO01邦40311200W002310S20depfUsDMGIdepW.WHdepCH門34gf?9O21200昭OW22fK24?字君己UJrpO4.5OT5.Oldfrp04_004dep(H/34盧4G312OOV2OO221051

5、2/fLlWLLwaNULLJVWCZNUUNg導(dǎo)入結(jié)果:Book：100% *理結(jié)果康消息bOQ_l1book_najneTtibl i sh_C ompanya.utlwr pri c?eb pCL_sO01_Ql!電隔波工程電子工業(yè)出版社顧華212dep04_b001_01計(jì)算機(jī)戛礎(chǔ)猜華大學(xué)岀版社洪濤IS3AepCO001-02計(jì)寫機(jī)葩用電子工業(yè)出版社李群19.3卜d( (p04_p001_01f語(yǔ)言程序謨計(jì)溝華大學(xué)出版社鐘軍IS. 35lip04_E001_01SQL箕曰埶搖庫(kù)開發(fā)技術(shù)北方交通岀版社成虎2156dep04_SD02_01拠語(yǔ)言程序設(shè)計(jì)東南大學(xué)岀版社王甲22 5hdp04

6、_003_01單片機(jī)原理東南大學(xué)岀版社肖紅16.3teatb&r idte*cher nametertiidepa rtiTifriTt kprafeiamtelfrpbonehome addrgdep01D011156g09dep_016211M4師先蘭辭1號(hào)210WepO.Din11-!M9-re-06depw02139516O57M盍思*忙”.21depOSOTl住 T11960-01-01dcp_0l3372S543U 崔匚號(hào)護(hù)Drg&e-M-nidrp_D1lvwrSBS7654號(hào)1dep04 30H紀(jì)云11965-09-09HMI ani矢T_QLilepartm

7、ent_D.ani e!無線電depar tmant_Keader王敬遠(yuǎn)teajcher22dep 02ii信與信息工程廉輝13dejjOS電子工程董一平14d即IM計(jì)算機(jī)科學(xué)紀(jì)云5Stude nt:Q童值已成功執(zhí)行.LAPTQP-7044OPDMMSSQLSERVER結(jié)果盟消息1atiidet_idshlden!：_nair!e弓HLS&XKirtkI9fl4-O9ilntrar.ce_lite1999-0901koii6_aldr南京幣疑橇區(qū)古平崗3m&ilKULLclAES_id繆402:94Q20：lAi 8 8 8.vSBiBaaBHaiBsX2聲40202林紅f19

8、4-06-0119*99-09-01刪M五卓銜m號(hào)VULL3卿40203曹雨11963-12-011399-03-01南京玄武區(qū)林確大道4mi94024縛4O2CK辭11963-03-091999-03-01江茄儀征市青年路&號(hào)NULL紳4025犧40205(1964-09-061999-09-01江苗儀顯市青年?yáng)|路1NULL或9402$齊迪賓糯國(guó)ige4-O4-oe1900-0-01扌僚市中対扶平北路HULL94021卿40207李紅小(10S4-OB-231999-09-01輛恵昵1匕齡塢NULL或940284021011965-01091399-001南京中山北路M號(hào)mi或940

9、291laes-onj?1999-09-01躺中山北路 B 號(hào)NULL94021040301左群聲11905-0 OT1399 OBTiNULLNUI1940311縛4030201湘3TO-1Q1999-0301卜循市並盤路s禺tnju羽94M12940303弛雅麗(19641-011999-09-01上海市北京東貉1時(shí)NULL940313妙4們04(1964-0409i軸gm南集中山陷9是mi9403Stude nt_course:ffi 結(jié)果審消息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爭(zhēng)購(gòu)爭(zhēng)購(gòu)iDBTJip_(E擔(dān)授336HjUtT&4闡劭鞋桂國(guó)fedCSS于21E00:：FJLL3k?KJ_aoi董一平

13、11?68HD-QL址_033?285fl3葛紺6T融昱2iUi3&FILL氐如gjice|昨雷昨雷TiT Lhp.CM5aaTS4頁(yè)京忙工路電2LDQiBILL5dtjM.aai1dfP.iH購(gòu)購(gòu)53EI勇京弋江蹈號(hào)2IIKKITJLL6dD*JI(X?和IQ陽(yáng)陽(yáng)*卜卜04d*p_(N302#eieO22即員怎酹3嵋21OM3U1JLL7站M QW1H貧叫貧叫叩叩&dtp JH3圧目鬧圧目鬧3撫閃龍撫閃龍fJLL.8L11*璋璋FMH詢T融融Hi岡岡FITJLL帝IDiggMML罰肌Ml存踴嗨simnTeacher_course_class:囲結(jié)果事消自1*wh.；1gmrh

14、u.id.IdpQl_sOL715_i1Cim“d.i 4d*por.i0oijii IspJl.DOl2lepJl_O51iepUl_SUUL現(xiàn)aa12OD1AIMJEl2CJKUb2U屆pUl_sOUl_Ll3J.ap04._sCOl曰M肚1sooioraL ID3E2JcpOdJiDOl半4icpJOOllcp04_5CO5MD212QQ：住0田L(fēng)30310511就學(xué)擁肚0-J_pO( (Jl_Cl&iq34_D0Zdpa4_pcoigjmOB11993/20U)LJDZDJtpM_pOOl_CL6lq) )(MJ)0EIftDO-CsOO?或!M(J212002/2003104

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

16、name為空，看運(yùn)行分析：course_nam有not null的約束，因此這條語(yǔ)句不能執(zhí)行。3.用Transact-SQL修改Course表中credit為5的記錄，將其credit改為7,credit小于4的改為2,看運(yùn)行的結(jié)果SQL語(yǔ)句：update courseset credit =7where credit =5;執(zhí)行結(jié)果：圉消息涪長(zhǎng)547，裟別16坎感（h篝:fjETftTE語(yǔ)句占cgECF肉費(fèi)ClEJt庚.詼：審?fù)粎疑谳d據(jù)專工蝕丸時(shí)事血1Wdbo.CQUTJI-,co 1UTOTL1，crp4itr,氓包已為止，分析：約束C1指定了credit的范圍為1至6.SQL語(yǔ)句：up

17、date course set credit =2 where credit 4;執(zhí)行結(jié)果:W； %4.刪除一條學(xué)生記錄，看運(yùn)行結(jié)果，對(duì)運(yùn)行結(jié)果進(jìn)行分析SQL語(yǔ)句:delete from studentwhere stude nt_id=g9940201;couiEft_idccpurse_iiMebgb_；dtot al_periorweel：_per ior crediti lep01_E001電菠工程4ep01_sODI_018066dip04_b001計(jì)尊機(jī)坯礎(chǔ)depC4_h001_016044JepO4_pXlC語(yǔ)言程設(shè)計(jì)4ep4_p00i_01希44dj*pO4_0Ol SQL弘紗

18、弐頻據(jù)庫(kù)開岌技祇a&pC4_s001_0.18055dep04_s002丁則帰序設(shè)計(jì)與開發(fā)dpC4_sOO2_Ol沛44Jep04_s003單片機(jī)原理dep04_s003_01se44hp04004釈件幵發(fā)技術(shù)depC4s00401513oIep04_s005網(wǎng)頁(yè)設(shè)計(jì)depC4_g005_013055籍果EP渭息46.8LAPTOP 7O44OPDNM OBSERVE FU IAPTQP 7J44QPDISUPTJ rtudfntidb OOiOOiOD 0庁執(zhí)行結(jié)果：分析：因?yàn)橛袇⒄胀暾约s束，不能刪除謁皂.64 ,敘-優(yōu)咨01亍EELETE譜聞與ftEFERENCE期佗_sz ud

19、efit_eour se_stkide tit*彳5!邃1=1嚶港號(hào)千曲強(qiáng)a tudefit9dbH語(yǔ)旬宙止.5.用Transact-SQL完成將編號(hào)為dep04_b001的課程的選修信息插入到一個(gè)新的選課信息表中。SQL語(yǔ)句：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;執(zhí)行結(jié)果:睜結(jié)果銅消息cours&idstulent=idgradecreditsemestersckool_year1 LpD4-E0Ol j594020270401393/20002dP( (MjbaOl53940203SS401999/20003deptM_bOOL級(jí)4C加456001999/50004Jftp04j00194020B45001999/20005depCW-bOOlS394020S98401959/20006deptM_b0Ol級(jí)40血87401599

21、/50006.用Transact-SQL完成刪除單片機(jī)原理課程的選課信息，分析運(yùn)行結(jié)果SQL語(yǔ)句：delete from student_coursewhere course_id in (select course_id from coursewhere course_name =單片機(jī)原理)S，!idbo . s z；udent_eojf *e teel lannpa tude&idBB執(zhí)行結(jié)果：分析：所有課程號(hào)為depO4_sOO3勺課程被刪除。結(jié)杲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本實(shí)驗(yàn)中遇到的問題和解決方法:本實(shí)驗(yàn)的順利完成需要預(yù)先作很多準(zhǔn)備工作。以下就是我在遇到缺少組件ccessdatabase en gir時(shí)的解決過程的記錄。Access Database Engine的安裝accessdatabaseengine用于和office連接，導(dǎo)入導(dǎo)出數(shù)據(jù)，本實(shí)驗(yàn)中需要導(dǎo)入excel文件。安裝配合office的版本，我安裝的是accessdatabaseengine2017（English）版本。安裝32位版本，因office2016是32位。之前誤操作安裝了不能使用的老舊版本accessdatabaseengine2007,