题目六数据库大作业

员工（员工编号，部门编号，，，出生年月，参加工作时间，所属部门，主键4否是4否否2否否 否是是4否4否婚4否是否是否是否否金养老否否4否是否人否否ssalary-dsalary的值。T-SQL。createindexsnoonstaff(snoASC)createindexsnoonsalary(snocreateindexdnoondepartment(dnoT-SQLcreatedatabaseT-SQLstaffcreatetablestaff notnull, char(4) notnull, notnullconstraintCKC__STAFFcheck in('男','女 notconstraintCKC_BIRTH_STAFFcheck(birthbetween'1930.01.01'and notnull, notnullconstraintCKC_PSTATUS_STAFFcheck(pstatusin('党员','团员',')

notconstraintCKC_MSTATUS_STAFFcheckmstatusin已婚','未婚')),constraintPK_STAFFprimarykeynonclustered(sno)创建salarycreatetablesalary notnull, notnull, notnullconstraintCKC_MONTH_SALARYcheck(monthbetween1and12), notnull, notnull, notnullconstraintCKC_INSURANCE_SALARYcheck(insurance>0), notnull, AS(bsalary+psalary+hsubsidy+subsidy), AS(dues+utilities+hfund+insurance+rp),constraintPK_SALARYprimarykeynonclustered(month,sno,)创建departmentcreatetabledepartment not not not notnullconstraintCKC_DNUM_DEPARTMEcheck(dnum>=0),constraintPK_DEPARTMENTprimarykeynonclustered)T-SQLcreateviewView_1hfund,insurance,rp,ssalary,dsalary,salary=ssalary-dsalaryfromsalaryT-SQL返回指定员工信息的过程createprocedurepro_staff@snochar(4)select*fromstaffwheresno=@sno返回指定部门信息的过createprocedurepro2@dnochar(4)select*fromdepartmentwheredno=@dnocreateprocedurepro3@yearint,@sumintoutputselect@sum=sum(ssalary)-sum(dsalary)fromsalarywhereyear=@year（5）T-SQLexecpro2execpro_staffT-SQLcreatetriggertr1onstaffforinsertdeclare@snochar(4),@dnochar(4),@snamevarchar(20), char(2),@birthdatetime,@worktimedatetime,@jobvarchar(20),@titlevarchar(20),@pstatuschar(4),@mstatuschar(4)select@sno=sno,@dno=dno,@sname=sname,@ frominsertedupdatedepartmentsetdnum=dnum+1wheredno=@dnoinsertstaffvalues('3','1','朱秀康','男T-SQL语句deletestaffwhereT-SQLupdatesetdno=1wheresno=4packagework;importjava.awt.*;importjava.awt.event.*;importjava.sql.*;importjavax.swing.*;publicclassselect{publicstaticvoidmain(String[]args)StringdriverName="com..sqlserver.jdbc.SQLServerDriver";StringdbURL="jdbc:sqlserver://localhost:1433;DatabaseName=manage";StringuserName="sa";Stringtry{Class.forName(driverNameSystem.out.println("}catch(Exception }ConnectiondbConn=DriverManager.getConnection(dbURL,userName,userPwd);}catch(Exceptione){}}}classDatabaseWinextendsJFrameimplementsActionListener{ ShowRecordshowRecord;ModifyRecordmodifyRecord;InsertRecordinsertRecord;DeleteRecorddeleteRecord;DatabaseWin(Strings){bar=newJ=newJ("部门管理");itemShow=newJItem("显示所有信息");itemModify=newJItem("修改信息");itemInsert=newJItem("插入信息");itemDelete=newJItem("删除信息"); modifyRecord=newModifyRecord("欢迎进入修改信息");insertRecord=newInsertRecord("增加信息");deleteRecord=newDeleteRecord("删除信息");}publicvoidactionPerformed(ActionEvente){elseelseif(e.getSource()==itemInsert)elseif(e.getSource()==itemDelete)}}publicvoidactionPerformed(ActionEvente){Stringa[][]=newString[10][10];String StringuserPwd="123456"; Statementstmt=dbConn.createStatement();Stringquery="select*fromdepartment";ResultSetrs=stmt.executeQuery(query);}

");//}catch(Exceptione1){}table=newJTable(a,name);add(newJScrollPane(table),BorderLayout.CENTER);}；publicvoidactionPerformed(ActionEvente)StringuserName="sa"; StringuserPwd="123456"; Statementstmt=dbConn.createStatement();Stringquery="INSERTINTOdepartment}catch(Exceptione1){}JOptionPane.showMessageDialog(this,"插入记录成功","成功 ；publicvoidactionPerformed(ActionEvent