SQL工資管理系統(tǒng)課程設(shè)計(jì)報(bào)告

設(shè)計(jì)報(bào)告課題名稱:工資管理系統(tǒng)學(xué)院:專業(yè)班級(jí):學(xué)號(hào):學(xué)生:指導(dǎo)教師:青島理工大學(xué)琴島學(xué)院教務(wù)處2009年7月3日?ilVDFD?ilVDFDawn*，W*?c?i^HWSiHIKn^KMxwttitvjwr-z^^料iiwrt氓瑪論"武K討樂(lè)汁HIIUUMK^：jnnixnMMmi!^RH?nn?w<????Sitlffr1-fiWWV'戰(zhàn)幻側(cè)?UfertZI.B：.?I!i￡h!fl農(nóng)長(zhǎng)I出??計(jì)用.不儀佗■?!外人8人miftWM.I：-帀旳?w畑歹lu曲于iHniWiauaw說(shuō)nam.nwntifjivlJMt「4h機(jī)l■啓N訐iFKihE?昭B?咲amaMttfgl和HflWqHJJt?『〕刖并馴悄倫訂MWfiP?利悄GT?INit*n<.wit.wficrwnm?能w人<1?片點(diǎn)?、"仰尺桑紅護(hù)m訪?cm￡i?aox2>ft.釈佯、jtaitww!$rm??取脅氏為腔什桁tlJItt入助矗l?IWMI.GBKMtCAl<J*b濟(jì)&?11知?ll!^JU??？！"M0<?藺X?n.取叭PR.怕“”竇、b■慶HI低1略口口?乜M?n^i<R?IMI<Ift.UM祈￥??WQ.?余電??”勺舎IH金-#€???濱匕?“?auImncy?o.燦3叭??「i翼r?an^n人.arjxn?dthus識(shí)覇口功綠i?毎伐和ww人園刪總猱idht,l.nsn?心Ai?A4：<Q9?MttKttAMi：muMjft^XAarora?<t>M?.2?g他*矗0IGI誕席CAIJUl.tfl*.I曲1!?巴*23禽見(jiàn)筑HA?11賀ar汁用慣“unum■訥曹岡s坂員tQIVsB.HMllEGfi■川AHIRinuiR^.4??hb洌計(jì)和附?■帆awiEE欲i’whe血ant?nc.仔訛判純計(jì)功使i?Lnw^ttu???*?<旣“怖十牌門(mén)疣冬腳從成匕￡1uft?iUf-?!!ni?^a?tu?nafromw^KfWK^iifiictflrf個(gè)"I応8?；i總幵紅〈I的心遲水?WMDAJhI我*鼎?Mt?龍比ir|?Hff?「禺遠(yuǎn)羊?H^ftiiUMI1ftlDilH：?Bi?IF^?lrW.購(gòu)抑介■朋實(shí)■弋0^0i.iG.?yruCl長(zhǎng)發(fā)斂HIM?的IE削UPUe+CSrS賈Q?：??IIU：*??少勿WZ?IK“化?匂便■個(gè)力冷?51II.511?1w*?MtCil貝打U?AUGII黔愿定酢他?T瞳金04>"期1列"1<L.町歹口1噴?蟲(chóng)盡弊EHi.戀H?H?^itljhtif嚴(yán)rW&mm乙廉倉(cāng)分折（皿⑼I)1■I)1■7Ar>*>"也_矽>1r>￡wrAKfa^XlU11<1?tif-iA>2嚴(yán)“r>Mto<IO>M9J<I1■l、mNl■卻_MWJG1MMlM^sunnnfi?nvKBn(?“■"MlMIVH.W>Km?“ghnUhmantrnCftJMlv.?^**4*心?i1<Bl?MW4Ywstw皿?■nili從gWlW<1B

ftj.QttAcjUMS*uim笳tin,nrruii,■jumhm,議??mmwSiT^s-偽口|刪鼬0期乩tiujflj?amt?mtn?tctoitii弘imz舫G.?號(hào)?SflSKbEi制您?mn;；b?仏II：I?AuUl^C?U4.Itt.EE“妙?；,礎(chǔ)?ftfikF:!'.frtwtt?究%.aitn^￡U?Q?簿”名F陣門(mén)鳧宜人?■門(mén)人叩優(yōu)和100爲(wèi)a)0喪因1?u*rff>%&川丸丹?4屮*???倩機(jī)I劃12卜?込劃Wh?乜盤(pán)WLPM矗??l■ftitaihftWfva^tv/ittow?z?J9令?yuàn)亖A的疋怖廉審&勺乞更卡atm?？蒳UUMKmilSiR^.M(Vmi?MM1ftMSttV?:i：ftt?kVHr#S?G?I匝W4tft?Ka!>CVi.；曲汩沖，3￡口遼總、￡久我眄足S,恬怡”編*itlilttt?E整.■“簣日爐n.卩q、i.第北致快z<Tt*r如iwrIOffHPmr；二洽—廠1一|m巴—VAKW4lbXiM>[MUI1*1^lir?f.MWVARCMUblOM/UI4XKU；■1M.UrtttilQ?v4iw~匚恢伽_￥AWH5WM1.L_1日刑.￥mfw<4)r4^>Hw.lllM12州|*Fr?T叫巧1*叫*1CTFMy.Miii.wiiiWMIIH11M.Mttfcr>iwrmtiKl>lftjlMM陡時(shí)mm札MMVRMwuif■■啤SadKLU^feK.MUllanitt?|ZdttrV-OHLMilH.WflMVU>1EMM<IU?dc|aWTK_ddnriimi皿■>/?uun^?l?in<mB.vMtia*IU,k亠航■敦W>VlR**f4(?c*9r??>dwl：llhMH<kMi???-M23MMlniv^yk>-?fl?><krwin?_*?**?guwai-Mrt??■Il■人仕Ef^v.BAMKtsiXiadmil.k*oqn_r><<?xwtkMIH>miul,g?L_rw3-noTt會(huì)-ttieg“Wk：l**i<kdWJI.'Ill?uaUM*wO>vxt?iirrim<?4l-?>?■MwilBOanTX-MA?UTZMHMHGMMMcj葉呷Ictk^oMc??I9xli?unrdMfiWimhm1pvtnsr5lHct^nsri(Wi4c<B4i15?>Xm?T?MicB_dalCkrvne*d.幡SU2fi<U?(Tt>*rtoHcY>iE>.ML>vshftH.flpt??rrIcy.-WtAHCT?M*KgW0?“MI*.>?<isow'H'r*G?°■、hrti_(terdMctracmaiL4ms?wi?<r*kC?P亠?IY?r?tdefErwe?eid?t??；41?Kra41Itvtmplg3M1JU1/0k*.rwHiwitv,IQl?*ail}厶屮」》?1"pR?1fl*teA|K4MNM?卄MlIIJI??■irmn?U€l*t￡■*■-n?5：W4>十八.和?A?L3imnnif4r<nM)U^rw~r^P_n?i'Ck并??i.MA.ftlKumui、iKKJ*ivrtr?h?.msi-r,,2iwi->r.Tiin；*vi■-.-^.liMM^xn十bi、g?<?rtinoje.H.Td.iwsi-rMMj.in.Fi??is.'M'itMUMir..?r[、由zh.e.aa9.inr.-?A7iic>■01C.，林wYWHmxM.xwz/W]厶頤律第i此“cwjhtwww.o■kr砂\gwTiwmwr.xwNr處■mal.in?hcii-%ii?ni*^/=U.IIflmk.MMir'Wh十：丨Iq^?-xcnjuii-.uijmaTwwyioWVlt-unHIM3C5WTMMLQJOXMM：.11UMU1I??v-f*nr)?j?”12fr?4.eitt?認(rèn)mctmcJc4*￡l嚴(yán)jdcGimUczxjMdcH嚴(yán)尸.dK&cixcif.hKHui^a<ri_dct&?tt*.>Mijau?'cvcv”戈|n>?a.Je{&JIJ甘*L<ti?心th口醫(yī)即』i—ldtuh*現(xiàn)pijklLi|C3DCE|l?blCklKlAfWVl.dcLl.>|C.mumv-ifBhftddKiigsM|uBdclaal|-aluLvfbnz'昌訕I(yè)fiaPlMh?fMkKift*川葉申IXFLMItuEHh-wispnminhlIffl-afefclrdcktrkMI氣Ri*brtre**V尢咐］址諭網(wǎng)肝敵CMrtdlSchifffrntiIT*時(shí)叭強(qiáng)prr!iirtkt^w馴削c旳袖JeMc-kH'I'靈醫(yī)沖*岬4hI^Kl，■機(jī)出IrldiJOt；fi!*4G?rOAi4■kLnclui；:.bdtf叩刨1U「hi!L［音??!的幣■如支ItiI2IfIlim昨■如II?Ifr-QdSrJH.^1,ILK門(mén)沖E4UrUMMfctvH-^^ij^nhii?Iff!JckH-'feHi詁L里出葉鹹］如疔用世/IM■喪附I?IK：區(qū)阿■LiktviinAL?|?kn,4fivH?_LAl準(zhǔn)*?i片■啊訐|71|中fl|rIaTHL||jrlifif.tf前廳■打IfjijdIU4■，空止％■raimiLFTi^-triJFI石丄br-dgdxft-jnaB.asdeclare@qvarchar(12declare@mvarchar(12declare@sfloatselect@q=emp_nofromDeletedselect@m=zhiwufromInsertedselect@s=jibengongzifrom基本工資wherezhiwu=@mupdate月工資setjibengongzi=@swhere月工資.Emp_no=@q結(jié)果:命令已成功完成6.創(chuàng)建函數(shù)a.函數(shù)查詢員工基本信息createfunctionygxx(@yg_idvarchar(10returnstableasreturn(select*from員工whereemp_no=@yg_id結(jié)果:命令已成功完成驗(yàn)證：select*fromdbo.ygxx('20070202'結(jié)果：b.函數(shù)查詢部門(mén)信息createfunctionbumen(@bumen_idvarchar(4returnstableasreturn(select*from部門(mén)wheredepartment_id=@bumen_idgo結(jié)果:命令已成功完成驗(yàn)證：select*fromdbo.bumen('03'go結(jié)果:7.創(chuàng)建存儲(chǔ)過(guò)程a.存儲(chǔ)過(guò)程查詢員工基本信息createprocygxx2@yg_idvarchar(10asselect*from員工whereemp_no=@yg_idgo結(jié)果:命令已成功完成。b.存儲(chǔ)過(guò)程查詢指定某個(gè)部門(mén)或全部部門(mén)的基本信息createprocbumen2@bumen_id2varchar(4asselect*from部門(mén)wheredepartment_id=@bumen_id2createprocbumenqasselect*from部門(mén)go結(jié)果:命令已成功完成。c.存儲(chǔ)過(guò)程查詢某個(gè)員工的工資信息createprocgzxx@yg_idvarchar（10asselect*from月工資whereemp_no=@yg_idgo結(jié)果:請(qǐng)輸入員工id-d.匯總部門(mén)月份工資createprocgzhz@dep_idvarchar（04,@month_yearvarchar（10asselectdepartment_id,gz_monthfrom月工資wheredepartment_id=@dep_idandgz_month=@month_yearorderbydepartment_id,gz_monthgo結(jié)果:命令已成功完成e.匯總工資總和execgzhz'03','200905'createprocsum_sumasselectdepartment_name,gz_month,sum（wagecountfrom部門(mén),月工資where部門(mén).department_id=月工資.department_idgroupbydepartment_name,gz_monthgo結(jié)果:命令已成功完成五、總結(jié)1.過(guò)程（1）在對(duì)員工表創(chuàng)建刪除觸發(fā)器時(shí)，無(wú)法刪除表記錄，刪除與其他表的外鍵約束后，觸發(fā)器能正常運(yùn)行。（2）觸發(fā)器、存儲(chǔ)過(guò)程等的建立與使用語(yǔ)句同時(shí)運(yùn)行無(wú)法實(shí)現(xiàn)，先創(chuàng)建再執(zhí)行程序正常運(yùn)行。（3）在創(chuàng)建員工表時(shí)職務(wù)的類型是text，但在創(chuàng)建基本工資表時(shí)職務(wù)作為主鍵出現(xiàn)錯(cuò)誤。把職務(wù)改成varchar類型后運(yùn)行正常（4）在插入數(shù)據(jù)時(shí)文字長(zhǎng)度超出數(shù)據(jù)類型的長(zhǎng)度出現(xiàn)文字被截取的現(xiàn)象。（5）對(duì)基本的SQL語(yǔ)句掌握不牢固，通過(guò)查詢資料后基本掌握。2.體會(huì)此系統(tǒng)的規(guī)劃雖然已經(jīng)完成，功能基本實(shí)現(xiàn)了目標(biāo)規(guī)劃一些重要功能，但由于時(shí)間倉(cāng)