【软件工程综合实践专题-第一次个人作业-1759106】
该代码来自同学编写的一个数据库：
create table worker(
w# char(6),
wname varchar(8),
age int,
sex char(2),
c# char(6),
yearone char(6),
yeartwo char(6),
primary key(w#)
);
insert into worker
(w#,wname,age,sex,c#,yearone,yeartwo)
values
('001','安爱民',35,'M','J03','已发放',''),
('002','毛华尧',26,'M','K01','已发放',''),
('003','安庭彪',39,'M','J03','已发放',''),
('004','王琦',26,'M','K01','已发放',''),
('005','王建华1',33,'M','K02','已发放',''),
('006','安志杰',46,'M','D33','已发放',''),
('007','曹长安',37,'M','M15','已发放',''),
('008','贺斌',47,'M','P02','已发放',''),
('009','陈海波',44,'M','T03','已发放',''),
('010','蔡向军',39,'M','F18','已发放',''),
('011','陈海',27,'M','J03','已发放',''),
('012','李鲲',32,'M','R12','已发放',''),
('013','梁瑞斌',31,'M','R12','已发放',''),
('014','白天宇',47,'M','M18','已发放',''),
('015','陈围',31,'M','J04','已发放',''),
('016','贾小强',49,'M','H09','已发放',''),
('017','李晨阳',59,'M','H09','已发放',''),
('018','陈文浩',47,'M','J04','已发放',''),
('019','常文红',34,'M','W12','已发放',''),
('020','王建华3',32,'M','K02','已发放',''),
('021','黄保义',48,'M','R19','已发放',''),
('022','刘存杰',53,'M','J06','已发放',''),
('023','成云',39,'M','T04','已发放',''),
('024','柴兴军',38,'M','J07','已发放',''),
('025','陈逯民',34,'M','U04','已发放',''),
('026','吴俊杰',42,'M','U07','已发放',''),
('027','陈强',31,'M','G15','已发放',''),
('028','陈生涛',47,'M','S12','已发放',''),
('029','陈帅帅',31,'M','Q15','已发放',''),
('030','郭春龙',35,'M','R20','已发放',''),
('031','李成家',31,'M','J07','已发放',''),
('032','陈云峰',35,'M','A15','已发放',''),
('033','成波',48,'M','B15','已发放',''),
('034','王建忠',41,'M','K02','已发放',''),
('035','崔贵玉',29,'M','T04','已发放',''),
('036','程建军',32,'M','U12','已发放',''),
('037','程军平',36,'F','N15','已发放',''),
('038','崔广江',33,'M','T05','已发放',''),
('039','刘军洪',26,'M','R25','已发放',''),
('040','王清云',27,'M','P02','已发放',''),
('041','王瑞珍',38,'M','P02','已发放',''),
('042','崔旋',47,'M','G15','已发放',''),
('043','常志锋',25,'M','P09','已发放',''),
('044','曾吉吉',33,'M','J08','已发放',''),
('045','代志明',32,'M','B15','已发放',''),
('046','陈浩',37,'M','J08','已发放',''),
('047','董光伟',42,'M','V18','已发放',''),
('048','崔建明',41,'M','P09','已发放',''),
('049','冯霞',37,'F','T05','已发放',''),
('050','杜兴益',35,'M','J08','已发放',''),
('051','郭林武',32,'M','U12','已发放',''),
('052','常临生',42,'M','J12','已发放',''),
('053','王艳',37,'F','R25','已发放',''),
('054','晁雨童',40,'F','R26','已发放',''),
('055','高宝',35,'F','T05','已发放',''),
('056','张赫',33,'M','H09','已发放',''),
('057','郭延生',41,'M','T05','已发放',''),
('058','代海萍',35,'F','P09','已发放',''),
('059','葛红岷',34,'M','S13','已发放',''),
('060','巩昱',36,'M','E21','已发放',''),
('061','陈探真',36,'M','R26','已发放',''),
('062','郭永明',36,'M','E28','已发放',''),
('063','常亚民',36,'M','P11','已发放',''),
('064','白迎斌',37,'M','H10','已发放',''),
('065','曹启雄',42,'M','H10','已发放',''),
('066','刘毅',48,'M','P11','已发放',''),
('067','郝建业',27,'M','P14','已发放',''),
('068','李慧',38,'F','T05','已发放',''),
('069','安勇',42,'M','K09','已发放',''),
('070','侯润民',23,'M','P14','已发放',''),
('071','常引生',44,'M','K09','已发放',''),
('072','黄兵兵',38,'M','D33','已发放',''),
('073','黄鑫',22,'M','O01','已发放',''),
('074','霍宝平',28,'M','C22','已发放',''),
('075','吉安喜',38,'M','S13','已发放',''),
('076','吉文',38,'M','C22','已发放',''),
('077','贾向前',44,'M','C22','已发放',''),
('078','李东昌',26,'M','P14','已发放',''),
('079','李蕾',35,'F','T06','已发放',''),
('080','晋云龙',49,'M','W12','已发放',''),
('081','郭新顺',37,'M','R30','已发放',''),
('082','景小娃',34,'M','D33','已发放',''),
('083','李洁',43,'F','R30','已发放',''),
('084','刘书锋',35,'M','T06','已发放',''),
('085','李晨雨',24,'M','I01','已发放',''),
('086','李瑞平',30,'M','P14','已发放',''),
('087','李福胜',47,'M','W12','已发放',''),
('088','李国梁',26,'M','C14','已发放',''),
('089','齐琪',35,'F','R30','已发放',''),
('090','王子瑞',28,'M','R31','已发放',''),
('091','李林',49,'M','B16','已发放',''),
('092','李强1',28,'M','W12','已发放',''),
('093','范林中',23,'M','U14','已发放',''),
('094','王建华2',47,'M','J17','已发放',''),
('095','李长安',53,'M','S13','已发放',''),
('096','魏俊明',28,'M','R31','已发放',''),
('097','荆长春',42,'M','R34','已发放',''),
('098','牛健',49,'M','P18','已发放',''),
('099','梁萍萍',35,'F','M27','已发放',''),
('100','冯爽',57,'M','R36','已发放',''),
('101','梁泽峰',44,'M','E28','已发放',''),
('102','王睿',46,'M','J17','已发放',''),
('103','赵革平',48,'M','P19','已发放',''),
('104','刘建成',26,'M','O01','已发放',''),
('105','郭景川',33,'M','R36','已发放',''),
('106','成亚军',29,'M','K09','已发放',''),
('107','刘卫民',52,'M','E21','已发放',''),
('108','崔建荣',37,'M','P21','已发放',''),
('109','康梦远',47,'M','R36','已发放',''),
('110','芦乔斌',28,'M','M27','已发放',''),
('111','陆回',34,'M','S13','已发放',''),
('112','马怡昕',25,'F','I01','已发放',''),
('113','任金栓',33,'M','P21','已发放',''),
('114','崔李强',44,'M','K09','已发放',''),
('115','孟坚柱',45,'M','S13','已发放',''),
('116','张佳佳',36,'F','T06','已发放',''),
('117','孟永生',37,'M','S18','已发放',''),
('118','韩中平',32,'M','P22','已发放',''),
('119','董小勇',36,'M','K09','已发放',''),
('120','庞珺',25,'M','O01','已发放',''),
('121','李忠杰',34,'M','R36','已发放',''),
('122','蒋伟',52,'M','P22','已发放',''),
('123','尚建伟',31,'M','V18','已发放',''),
('124','李宗元',39,'M','R36','已发放',''),
('125','张睿',31,'M','T06','已发放',''),
('126','洪永祥',32,'M','K11','已发放',''),
('127','孙志杰',37,'M','E28','已发放',''),
('128','牛润军',32,'M','K11','已发放',''),
('129','田旭',26,'M','I01','已发放',''),
('130','刘俊龙',53,'M','T12','已发放',''),
('131','万迎新',23,'M','O01','已发放',''),
('132','安志刚',49,'M','K18','已发放',''),
('133','董志勇',27,'M','K21','已发放',''),
('134','王纪光',45,'M','V18','已发放',''),
('135','刘路林',27,'M','K21','已发放',''),
('136','席雅斌',38,'M','J25','已发放',''),
('137','宋满利',52,'M','K21','已发放',''),
('138','王春跃',32,'M','K21','已发放',''),
('139','王国祥',48,'M','K21','已发放',''),
('140','安建新',48,'M','K22','已发放',''),
('141','程鸿斌',27,'M','K22','已发放',''),
('142','常峰',39,'M','P24','已发放',''),
('143','马永红',42,'M','P24','已发放',''),
('144','郭爱珍',35,'F','L23','已发放',''),
('145','阴立栋',44,'M','P35','已发放',''),
('146','王雪清',35,'F','J29','已发放',''),
('147','卫光军',38,'M','K30','已发放',''),
('148','单红彦',37,'F','L32','已发放',''),
('149','侯张保',37,'M','K32','已发放',''),
('150','田三平',38,'M','K35','已发放',''),
('151','王未',32,'M','B16','已发放',''),
('152','贾莲蓬',32,'M','J34','已发放',''),
('153','王晋传',37,'M','K35','已发放',''),
('154','王玉照',44,'M','M27','已发放',''),
('155','王跃军',42,'M','B16','已发放',''),
('156','王振升',38,'M','E21','已发放',''),
('157','边东波',35,'M','T17','已发放',''),
('158','范彬彬',41,'M','J37','已发放',''),
('159','陈皓',33,'M','U15','已发放',''),
('160','赵文龙',46,'M','H10','已发放',''),
('161','柴朝辉',48,'M','H11','已发放',''),
('162','李新有',39,'M','U17','已发放',''),
('163','徐喜旺',56,'M','U17','已发放',''),
('164','郭裕民',25,'M','T17','已发放',''),
('165','景利民',47,'M','H11','已发放',''),
('166','杨海青',39,'M','U29','已发放',''),
('167','雷海瑜',27,'M','H11','已发放',''),
('168','李兴舜',46,'M','J37','已发放',''),
('169','杨林祥',41,'M','V18','已发放',''),
('170','刘瑜',44,'F','J37','已发放',''),
('171','杨媛',35,'F','R38','已发放',''),
('172','张俊萍',45,'F','R38','已发放',''),
('173','韩忠生',52,'M','P37','已发放',''),
('174','石秀丽',48,'F','T18','已发放',''),
('175','郝培峰',46,'M','H12','已发放',''),
('176','张忠泽',48,'M','T18','已发放',''),
('177','黄为',36,'M','R17','已发放',''),
('178','曹林平',35,'M','R19','已发放',''),
('179','武利荣',38,'M','U38','已发放',''),
('180','郭峰',37,'M','T22','已发放',''),
('181','付勤俭',24,'M','H14','已发放',''),
('182','张小涛',56,'M','S18','已发放',''),
('183','李强2',49,'M','H18','已发放',''),
('184','闫刚',35,'M','H18','已发放',''),
('185','雷文龙',39,'M','T34','已发放',''),
('186','白跃恭',44,'M','P09','已发放',''),
('187','狄红杰',37,'M','H34','已发放',''),
('188','蒋晓勇',41,'M','T37','已发放',''),
('189','赵泳先',56,'M','M27','已发放',''),
('190','左玺朋',37,'M','M27','已发放','')
create table position(
d# char(2),
dname varchar(12),
c# char(6),
cname varchar(16),
primary key(c#)
);
insert into position
(d#,dname,c#,cname)
values
('A','安全检查队','A15','计量钳工'),
('B','安全科','B15','计量钳工'),
('B','安全科','B16','视频监控人员'),
('C','办公室','C22','汽车驾驶员'),
('C','办公室','C14','服务员'),
('D','保卫科','D33','巡守工'),
('E','材料科','E21','搬运工'),
('E','材料科','E28','管库员'),
('F','财务科','F18','业务主管'),
('G','党群办','G15','计量钳工'),
('H','动态监测车间','H09','值班员'),
('H','动态监测车间','H10','红外线维修工'),
('H','动态监测车间','H11','学习技术员'),
('H','动态监测车间','H12','质检员'),
('H','动态监测车间','H14','服务员'),
('H','动态监测车间','H18','业务主管'),
('H','动态监测车间','H34','政工人员'),
('I','高职生','I01','见习生'),
('J','侯北检修车间','J03','车辆钳工'),
('J','侯北检修车间','J04','车辆钳工（配件）'),
('J','侯北检修车间','J06','制动钳工（外）'),
('J','侯北检修车间','J07','铆工'),
('J','侯北检修车间','J08','轴承钳工'),
('J','侯北检修车间','J12','质检员'),
('J','侯北检修车间','J17','熔接工'),
('J','侯北检修车间','J25','机床工'),
('J','侯北检修车间','J29','天车司机'),
('J','侯北检修车间','J34','政工人员'),
('J','侯北检修车间','J37','探伤工'),
('K','侯北运用车间','K01','见习生'),
('K','侯北运用车间','K02','货车检车员'),
('K','侯北运用车间','K09','值班员'),
('K','侯北运用车间','K11','学习技术员'),
('K','侯北运用车间','K18','业务主管'),
('K','侯北运用车间','K21','搬运工'),
('K','侯北运用车间','K22','汽车驾驶员'),
('K','侯北运用车间','K30','工具钳工'),
('K','侯北运用车间','K32','化验工'),
('K','侯北运用车间','K35','工程技术员'),
('L','化验室','L23','污水净化工'),
('L','化验室','L32','化验工'),
('M','计量室','M15','计量钳工'),
('M','计量室','M18','业务主管'),
('M','计量室','M27','仪表修理工'),
('N','技术科','N15','计量钳工'),
('O','见习生','O01','见习生'),
('P','介休运用车间','P02','货车检车员'),
('P','介休运用车间','P09','值班员'),
('P','介休运用车间','P11','学习技术员'),
('P','介休运用车间','P14','服务员'),
('P','介休运用车间','P18','业务主管'),
('P','介休运用车间','P19','锅炉工'),
('P','介休运用车间','P21','搬运工'),
('P','介休运用车间','P22','汽车驾驶员'),
('P','介休运用车间','P24','电脱修理工'),
('P','介休运用车间','P35','工程技术员'),
('P','介休运用车间','P37','探伤工'),
('Q','劳人科','Q15','计量钳工'),
('R','设备车间','R12','质检员'),
('R','设备车间','R17','熔接工'),
('R','设备车间','R19','锅炉工'),
('R','设备车间','R20','管道工'),
('R','设备车间','R25','机床工'),
('R','设备车间','R26','电机钳工'),
('R','设备车间','R30','工具钳工'),
('R','设备车间','R31','助理工程师'),
('R','设备车间','R34','政工人员'),
('R','设备车间','R36','机械钳工'),
('R','设备车间','R38','空压机司机'),
('S','调度科','S12','质检员'),
('S','调度科','S13','调度员'),
('S','调度科','S18','业务主管'),
('T','修配车间','T03','车辆钳工'),
('T','修配车间','T04','车辆钳工（配件）'),
('T','修配车间','T05','制动钳工（内）'),
('T','修配车间','T06','制动钳工（外）'),
('T','修配车间','T12','质检员'),
('T','修配车间','T17','熔接工'),
('T','修配车间','T18','业务主管'),
('T','修配车间','T22','汽车驾驶员'),
('T','修配车间','T34','政工人员'),
('T','修配车间','T37','探伤工'),
('U','站修','U04','车辆钳工（配件）'),
('U','站修','U07','铆工'),
('U','站修','U12','质检员'),
('U','站修','U14','服务员'),
('U','站修','U15','计量钳工'),
('U','站修','U17','熔接工'),
('U','站修','U29','天车司机'),
('U','站修','U38','空压机司机'),
('V','职教科','V18','业务主管'),
('W','质检科','W12','质检员')
create table issuance(
c# char(6),
workcloth int,
helmet int,
shortsleeve int,
anticlothes int,
raincoat int,
overcoat int,
insushoes int,
antishoes int,
coldhat int,
antihat int,
savehat int,
threadgloves int,
coldgloves int,
rubbergloves int,
viscosegloves int,
weldgloves int,
mirror int,
primary key(c#)
);
insert into issuance
(c#,workcloth,helmet,shortsleeve,anticlothes,raincoat,overcoat,insushoes,antishoes,coldhat,antihat,savehat,threadgloves,coldgloves,rubbergloves,viscosegloves,weldgloves,mirror)
values
('A15',3,2,2,0,2,2,2,0,0,2,1,3,1,2,0,0,2),
('B15',3,2,2,0,2,2,2,0,0,2,1,3,1,2,0,0,2),
('B16',3,2,2,0,0,0,2,0,0,0,1,0,0,0,0,0,2),
('C22',3,2,2,0,0,0,2,0,0,0,1,0,0,0,0,0,2),
('C14',3,2,2,0,2,2,2,0,0,2,1,3,0,1,0,0,2),
('D33',3,2,2,0,2,0,2,0,0,2,1,3,0,3,0,0,2),
('E21',3,2,2,0,2,2,2,0,0,2,1,3,1,0,1,0,2),
('E28',3,2,2,0,0,2,2,0,0,2,1,0,0,3,0,0,2),
('F18',3,2,2,0,0,0,2,0,0,0,1,0,0,2,0,0,2),
('G15',3,2,2,0,2,2,2,0,0,2,1,3,1,2,0,0,2),
('H09',3,2,2,0,0,0,2,0,0,0,1,0,0,0,1,0,2),
('H10',3,2,2,0,2,2,2,0,0,2,1,3,0,0,1,3,2),
('H11',3,2,2,0,2,2,2,0,0,2,1,3,0,0,1,0,2),
('H12',3,2,2,0,2,2,2,0,0,2,1,3,1,0,1,0,2),
('H14',3,2,2,0,0,0,2,0,0,0,1,0,0,0,0,0,2),
('H18',3,2,2,0,0,0,2,0,0,0,1,0,0,2,0,0,2),
('H34',3,2,2,0,2,2,2,0,0,2,1,3,0,0,1,0,0),
('I01',3,2,2,0,2,0,2,0,0,2,1,3,0,0,1,0,2),
('J03',3,2,2,0,0,0,2,0,0,0,1,0,1,0,1,3,2),
('J04',3,2,2,0,2,2,2,0,0,2,1,3,1,0,1,3,2),
('J06',3,2,2,0,0,0,2,0,0,0,1,0,1,2,1,3,2),
('J07',3,2,2,0,0,0,2,0,0,0,1,0,1,0,1,0,2),
('J08',3,2,2,0,0,0,2,0,0,0,1,0,1,0,1,3,2),
('J12',3,2,2,0,2,2,2,0,0,2,1,3,1,0,1,0,2),
('J17',3,2,2,4,0,0,0,2,2,0,1,0,0,0,1,3,2),
('J25',3,2,2,0,0,0,2,0,0,0,1,0,0,0,1,0,2),
('J29',3,2,2,0,0,0,2,0,0,0,1,0,0,0,0,0,0),
('J34',3,2,2,0,2,2,2,0,0,2,1,3,0,0,1,0,0),
('J37',3,2,2,0,2,0,2,0,0,0,1,0,1,0,1,0,2),
('K01',3,2,2,0,2,0,2,0,0,2,1,3,0,0,1,0,2),
('K02',3,2,2,0,2,2,2,0,0,2,1,3,1,0,1,0,2),
('K09',3,2,2,0,0,0,2,0,0,0,1,0,0,0,1,0,2),
('K11',3,2,2,0,2,2,2,0,0,2,1,3,0,0,1,0,2),
('K18',3,2,2,0,0,0,2,0,0,0,1,0,0,2,0,0,2),
('K21',3,2,2,0,2,2,2,0,0,2,1,3,1,0,1,0,2),
('K22',3,2,2,0,2,2,2,0,0,2,1,3,0,1,0,0,2),
('K30',3,2,2,0,0,0,0,0,0,0,1,0,0,0,0,0,0),
('K32',3,2,2,0,2,2,2,0,0,2,1,0,0,0,0,0,0),
('K35',3,2,2,0,0,0,0,0,0,0,1,0,0,0,0,0,0),
('L23',3,2,2,0,0,0,2,0,0,0,1,0,0,2,0,0,2),
('L32',3,2,2,0,2,2,2,0,0,2,1,0,0,0,0,0,0),
('M15',3,2,2,0,2,2,2,0,0,2,1,3,1,2,0,0,2),
('M18',3,2,2,0,0,0,2,0,0,0,1,0,0,2,0,0,2),
('M27',3,2,2,0,2,2,2,0,0,2,1,3,0,3,0,0,2),
('N15',3,2,2,0,2,2,2,0,0,2,1,3,1,2,0,0,2),
('O01',3,2,2,0,2,0,2,0,0,2,1,3,0,0,1,0,2),
('P02',3,2,2,0,2,2,2,0,0,2,1,3,1,0,1,0,2),
('P09',3,2,2,0,0,0,2,0,0,0,1,0,0,0,1,0,2),
('P11',3,2,2,0,2,2,2,0,0,2,1,3,0,0,1,0,2),
('P14',3,2,2,0,0,0,2,0,0,0,1,0,0,0,0,0,2),
('P18',3,2,2,0,0,0,2,0,0,0,1,0,0,2,0,0,2),
('P19',3,2,2,0,2,2,2,0,0,2,1,3,0,0,1,3,2),
('P21',3,2,2,0,2,2,2,0,0,2,1,3,1,0,1,0,2),
('P22',3,2,2,0,2,2,2,0,0,2,1,3,0,1,0,0,2),
('P24',3,2,2,0,0,0,2,0,0,0,1,0,0,0,1,3,2),
('P35',3,2,2,0,0,0,0,0,0,0,1,0,0,0,0,0,0),
('P37',3,2,2,0,2,0,2,0,0,0,1,0,1,0,1,0,2),
('Q15',3,2,2,0,2,2,2,0,0,2,1,3,1,2,0,0,2),
('R12',3,2,2,0,2,2,2,0,0,2,1,3,1,0,1,0,2),
('R17',3,2,2,4,0,0,0,2,2,0,1,0,0,0,1,3,2),
('R19',3,2,2,0,2,2,2,0,0,2,1,3,0,0,1,3,2),
('R20',3,2,2,0,2,2,2,0,0,2,1,3,0,0,1,0,2),
('R25',3,2,2,0,0,0,2,0,0,0,1,0,0,0,1,0,2),
('R26',3,2,2,0,2,2,0,0,0,2,1,3,1,1,0,3,2),
('R30',3,2,2,0,0,0,0,0,0,0,1,0,0,0,0,0,0),
('R31',3,2,2,0,0,2,2,0,0,2,1,0,0,0,1,0,2),
('R34',3,2,2,0,2,2,2,0,0,2,1,3,0,0,1,0,0),
('R36',3,2,2,0,2,2,2,0,0,2,1,3,0,0,1,0,2),
('R38',3,2,2,0,0,0,2,0,0,2,1,0,0,2,0,0,2),
('S12',3,2,2,0,2,2,2,0,0,2,1,3,1,0,1,0,2),
('S13',3,2,2,0,2,2,2,0,0,2,1,3,1,2,0,0,2),
('S18',3,2,2,0,0,0,2,0,0,0,1,0,0,2,0,0,2),
('T03',3,2,2,0,0,0,2,0,0,0,1,0,1,0,1,3,2),
('T04',3,2,2,0,2,2,2,0,0,2,1,3,1,0,1,3,2),
('T05',3,2,2,0,0,0,0,0,0,0,1,0,1,0,1,3,2),
('T06',3,2,2,0,0,0,2,0,0,0,1,0,1,2,1,3,2),
('T12',3,2,2,0,2,2,2,0,0,2,1,3,1,0,1,0,2),
('T17',3,2,2,4,0,0,0,2,2,0,1,0,0,0,1,3,2),
('T18',3,2,2,0,0,0,2,0,0,0,1,0,0,2,0,0,2),
('T22',3,2,2,0,2,2,2,0,0,2,1,3,0,1,0,0,2),
('T34',3,2,2,0,2,2,2,0,0,2,1,3,0,0,1,0,0),
('T37',3,2,2,0,2,0,2,0,0,0,1,0,1,0,1,0,2),
('U04',3,2,2,0,2,2,2,0,0,2,1,3,1,0,1,3,2),
('U07',3,2,2,0,0,0,2,0,0,0,1,0,1,0,1,0,2),
('U12',3,2,2,0,2,2,2,0,0,2,1,3,1,0,1,0,2),
('U14',3,2,2,0,0,0,2,0,0,0,1,0,0,0,0,0,2),
('U15',3,2,2,0,2,2,2,0,0,2,1,3,1,2,0,0,2),
('U17',3,2,2,4,0,0,0,2,2,0,1,0,0,0,1,3,2),
('U29',3,2,2,0,0,0,2,0,0,0,1,0,0,0,0,0,0),
('U38',3,2,2,0,0,0,2,0,0,2,1,0,0,2,0,0,2),
('V18',3,2,2,0,0,0,2,0,0,0,1,0,0,2,0,0,2),
('W12',3,2,2,0,2,2,2,0,0,2,1,3,1,0,1,0,2)
create table personal(
c# char(6),
w# char(6),
pdate varchar(50),
workcloth int,
helmet int,
shortsleeve int,
anticlothes int,
raincoat int,
overcoat int,
insushoes int,
antishoes int,
coldhat int,
antihat int,
savehat int,
threadgloves int,
coldgloves int,
rubbergloves int,
viscosegloves int,
weldgloves int,
mirror int,
);
insert into personal
(c#,w#,pdate,workcloth,helmet,shortsleeve,anticlothes,raincoat,overcoat,insushoes,antishoes,coldhat,antihat,savehat,threadgloves,coldgloves,rubbergloves,viscosegloves,weldgloves,mirror)
values
('A15','032','2018-01-05',0,1,0,0,0,0,1,0,0,1,0,0,1,0,0,0,1),
('B16','155','2018-02-14',1,1,0,0,0,0,1,0,0,0,0,0,0,0,0,0,1),
('E21','156','2018-03-09',0,0,1,0,1,0,1,0,0,1,0,0,1,0,0,0,0),
('K09','114','2018-03-22',0,1,0,0,0,0,1,0,0,0,0,0,0,0,1,0,0),
('P14','067','2018-04-05',1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
('U07','026','2018-06-17',0,1,1,0,0,0,0,0,0,0,0,0,1,0,0,0,1),
('D33','006','2018-06-27',0,1,0,0,1,0,1,0,0,1,0,0,0,0,0,0,0),
('G15','042','2018-06-28',0,1,1,0,0,0,1,0,0,1,0,0,1,0,0,0,1),
('V18','047','2018-07-05',0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
('S13','059','2018-07-11',0,1,0,0,0,0,1,0,0,1,0,0,0,0,0,0,0),
('T05','068','2018-08-05',0,1,0,1,0,0,0,0,0,0,0,0,1,0,0,0,1),
('W12','080','2018-08-15',0,1,0,0,0,0,0,0,0,1,0,0,1,0,1,0,1),
('R19','178','2018-08-19',0,1,0,0,0,0,1,0,0,1,0,0,0,0,1,1,0),
('R31','096','2018-08-20',1,0,0,0,0,0,0,0,0,1,0,0,0,0,1,0,0),
('R36','124','2018-09-12',0,0,0,0,0,0,1,0,0,0,1,0,0,0,1,0,0),
('R38','172','2018-11-02',1,1,1,0,0,0,1,0,0,1,0,0,0,0,0,0,1),
('R34','097','2018-11-13',0,1,0,0,1,1,1,0,0,1,0,0,0,0,1,0,0),
('J03','001','2018-01-17',0,1,0,0,0,0,1,0,0,1,0,0,0,0,0,0,0),
('J03','003','2018-02-18',1,1,0,0,0,0,1,0,0,0,0,0,0,0,0,0,1),
('J03','011','2018-03-19',0,0,1,0,0,0,1,0,0,0,1,0,1,0,0,0,0),
('K01','002','2018-03-24',0,1,0,0,1,0,0,0,0,0,1,1,0,0,1,0,1),
('K01','004','2018-04-18',1,1,0,0,0,0,1,0,0,0,0,0,0,0,1,0,1),
('R12','012','2018-06-19',0,1,1,0,0,0,0,0,0,0,1,1,1,0,1,0,1),
('R12','013','2018-06-30',0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,0),
('R12','013','2018-07-03',0,0,0,0,0,1,0,0,0,1,1,0,0,0,0,0,0),
('P02','008','2018-07-13',0,0,1,0,0,0,1,0,0,0,0,0,1,0,1,0,1),
('P02','040','2018-08-26',1,0,0,1,0,0,0,0,0,1,0,0,1,0,0,0,0),
('P02','041','2018-09-10',0,0,1,0,0,0,0,0,0,0,0,0,0,0,1,0,1),
('M27','099','2018-10-03',0,1,0,0,0,1,0,0,0,0,0,1,0,1,0,0,0),
('M27','099','2018-11-26',1,0,0,0,0,0,0,0,0,1,0,0,0,1,0,0,0),
('M27','110','2018-12-21',1,1,1,0,0,0,1,0,0,1,0,0,0,0,0,0,1),
('M27','110','2018-12-30',0,0,0,0,1,1,1,0,0,1,0,0,0,1,0,0,0),
('E21','060','2018-01-20',0,1,0,0,1,0,1,0,0,0,1,0,1,0,1,0,1),
('E21','107','2018-02-05',1,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,1),
('E21','107','2018-03-01',1,1,1,0,0,0,1,0,0,0,1,0,0,0,0,0,1),
('J03','001','2018-04-17',0,0,1,0,0,0,0,0,0,0,1,0,1,0,1,0,0),
('J03','001','2018-05-23',0,1,1,0,0,0,0,0,0,0,0,0,1,0,0,0,1),
('R34','097','2018-06-29',0,1,0,1,1,0,1,0,0,1,0,0,0,0,0,0,0),
('R34','097','2018-07-27',0,0,0,0,0,0,0,0,0,1,1,1,0,0,1,0,0),
('H11','161','2018-11-25',1,1,0,0,0,0,1,0,0,0,1,0,0,0,1,0,1),
('H11','165','2018-12-15',1,1,0,0,0,1,1,0,0,1,0,1,0,0,0,0,1),
('H11','167','2018-12-29',0,1,0,1,0,0,0,0,0,1,0,1,0,0,1,0,1),
('V18','123','2018-07-01',0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,1),
('V18','123','2018-07-14',1,0,0,0,0,0,1,0,0,0,1,0,0,0,0,0,0),
('V18','134','2018-07-09',0,0,0,0,0,0,0,0,0,1,0,0,1,0,0,0,0),
('V18','134','2018-07-05',0,0,0,0,0,0,0,0,0,0,1,0,1,0,0,0,0),
('V18','169','2018-07-28',1,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0),
('V18','169','2018-07-22',0,0,1,0,0,0,0,0,0,0,1,0,0,0,0,0,0)
create table month(
c# char(6),
mdate varchar(50),
workcloth int,
helmet int,
shortsleeve int,
anticlothes int,
raincoat int,
overcoat int,
insushoes int,
antishoes int,
coldhat int,
antihat int,
savehat int,
threadgloves int,
coldgloves int,
rubbergloves int,
viscosegloves int,
weldgloves int,
mirror int,
);
create table countall(
c# char(6),
cdate varchar(50),
workcloth int,
helmet int,
shortsleeve int,
anticlothes int,
raincoat int,
overcoat int,
insushoes int,
antishoes int,
coldhat int,
antihat int,
savehat int,
threadgloves int,
coldgloves int,
rubbergloves int,
viscosegloves int,
weldgloves int,
mirror int,
primary key(c#)
);
create table warehouse(
wdate date
workclothnum int,
helmetnum int,
shortsleevenum int,
anticlothesnum int,
raincoatnum int,
overcoatnum int,
insushoesnum int,
antishoesnum int,
coldhatnum int,
antihatnum int,
savehatnum int,
threadglovesnum int,
coldglovesnum int,
rubberglovesnum int,
viscoseglovesnum int,
weldglovesnum int,
mirrornum int,
)
insert into warehouse values ('600','600','600','600','600','600','600','600','600',
'600','600','600','600','600','600','600','600','2018-01-01')
查询职工表中的所有职工的职工编号，姓名，性别，年龄
select w#,wname,age,sex
from worker
查询部门为“安全科”的所有职工
select *
from worker
where c# like 'B%'
查询所有职工的职工编号，姓名，所在部门，部门编号
select w#,wname,d#,dname
from worker,position
where worker.c#=position.c#
查询每名职工的信息及发放记录
select *
from worker,position
where worker.c#=position.c#
查询发放“防烫服”的所有职工信息及发放记录（包括整体及申请）
select *
from worker,issuance
where worker.c#=issuance.c# and anticlothes not like '0'
当库存少于200件时，输出警示语句同时将该条记录存入系统
create trigger worker_ware
on warehouse
for insert
as
if exists(
select 'ture'
from inserted i
where
i.workclothnum <'200' or
i.helmetnum <'200' or
i.shortsleevenum <'200' or
i.anticlothesnum <'200' or
i.raincoatnum <'200' or
i.overcoatnum <'200' or
i.insushoesnum <'200' or
i.antishoesnum <'200' or
i.coldhatnum <'200' or
i.antihatnum <'200' or
i.savehatnum <'200' or
i.threadglovesnum <'200' or
i.coldglovesnum <'200' or
i.rubberglovesnum <'200' or
i.viscoseglovesnum <'200' or
i.weldglovesnum <'200' or
i.mirrornum <'200'
)
begin
print('库存少于200件，请注意及时购买物资!')
end
else
print('库存成功修改！')
insert into warehouse values ('2018-12-01','300','260','260','100','300','118','380','300','330',
'400','200','200','300','200','300','200','200')
每到1月1日，所有职工年龄+1，若此时，某职工达到退休年龄（60），得到退休职工名单
alter table worker
add riqi date null--添加日期列
create trigger worker_age
on worker
for update
as
begin
select *
from deleted
where age>58
select *
from inserted
where age>59
end
--执行触发器
update worker
set riqi='2019-01-01'
update worker
set age=age+1
where riqi like '%-01-01'
--激活触发器
删除上表中的退休职工
create trigger worker_delete
on worker
for update
as
begin
delete worker
where age>59
end
--执行触发器
update worker
set riqi='2019-01-01'
update worker
set age=age+1
where riqi like '%-01-01'
--激活触发器
查看职工表【worker】
可见，职工编号为017的员工已经被删除
修改“熔接工”发放物资中标准发放数量
create trigger worker_update
on issuance
for update
as
if exists(
select 'true'
from inserted i
join deleted d
on i.c#=d.c#)
begin
select *
from deleted
select *
from inserted
end
update语句
update issuance
set workcloth='4',helmet='3',savehat='1'
where c#='R17'
现如今，在大型工厂，车间的员工衣服，由于人多，工作类型多，发放物品杂乱。款式型号多等方面问题造成防具等用品发放难统计，人员与防具数量难计算的问题。传统的手工操作方式，易发生数据丢失，统计错误，劳动强度高，且速度慢。随着计算机技术的不断应用和提高，计算机技术也在各方面得到广泛的应用。但是，很多公司仍采用Excel表格记录信息的方法，不仅效率低、易出错，而且耗费大量的时间。为了满足公司对职工信息、部门信息、工种信息、劳动保障用品发放信息等进行高效的管理，特编职工管理及劳保发放系统以提高公司的管理效率，因此设计了这样一个数据库，该数据库可以完成对职工的管理，如增减、退休，可以对劳保物品进行管理，如每年的发放和额外申请，以及对物品库存的监管。
浙公网安备 33010602011771号