USE ygxz GO
INSERT 工资
VALUES ('1110001','张毅','2000','200','300','100','240','2013-05-15') INSERT 工资
VALUES ('1110002','张尔','2000','200','300','100','240','2013-05-15') INSERT 工资
VALUES ('1110003','张三','1800','200','200','100','240','2013-05-15') INSERT 工资
VALUES ('1110004','李四','1800','200','200','100','240','2013-05-15') INSERT 工资
VALUES ('1110005','李梦','1800','200','200','100','240','2013-05-15') INSERT 工资
VALUES ('1110006','李飞','2500','200','400','100','240','2013-05-15') INSERT 工资
VALUES ('1110007','王梦','2100','200','400','100','240','2013-05-15') INSERT 工资
VALUES ('1110008','邢梦','2100','200','400','100','240','2013-05-15') INSERT 工资
VALUES ('1110009','武阳','2100','200','400','100','240','2013-05-15') INSERT 工资
VALUES ('1110010','无双','2700','100','300','200','240','2013-05-15') INSERT 工资
VALUES ('1110011','霸气','2000','200','400','100','140','2013-05-15') INSERT 工资
VALUES ('1110012','吴淑敏','2200','200','400','100','240','2013-05-15') INSERT 工资
VALUES ('1110013','吴飞扬','2250','100','400','100','240','2013-05-15') INSERT 工资
VALUES ('1110014','邢小迪','2750','200','400','100','440','2013-05-15') INSERT 工资
VALUES ('1110015','朱鸿儒','2100','200','300','100','240','2013-05-15') INSERT 工资
VALUES ('1110016','熊朝辉','2100','200','300','100','240','2013-05-15') INSERT 工资
VALUES ('1110017','吴飞扬','2100','200','300','100','240','2013-05-15') INSERT 工资
VALUES ('1110018','杨亚迪','2140','200','300','100','240','2013-05-15') INSERT 工资
VALUES ('1110019','李敏','2100','200','300','100','240','2013-05-15') INSERT 工资
VALUES ('1110020','郭佳','2440','200','300','100','240','2013-05-15') GO
15
SELECT * FROM 工资
3 运用触发器删除表中的信息
create trigger del_yg on 员工
instead of delete as begin
delete 工资where 员工工号g in (select 员工工号y from deleted) delete 员工where 员工工号y in (select 员工工号y from deleted) end
delete 员工
where 员工工号y='1110003' go
SELECT * FROM 工资 SELECT * FROM 员工 I
16
4 视图查询表中的信息
create view Y01( 员工工号y,姓名y,性别,职业,部门名称,出生日期,基本工资,福利补贴,奖励工资,失业保险,住房公积金) as
select 员工工号y,姓名y,性别,职业,部门名称,出生日期,基本工资,福利补贴,奖励工资,失业保险,住房公积金
from 员工,工资,科室
where 员工工号y=员工工号g; 查询职工总人数
select count(distinct 员工工号y) 职工总人数 from Y01
--查询部门总数
--select count(distinct 部门名称) as 科室 --from y01
5 存储过程的创建
1）、创建存储过程查询每个员工的实际发放工资
USE ygxz GO
CREATE PROCEDURE 员工工号y_实际工资 @员工工号y char(10)='%' AS
17
BEGIN
SELECT 员工工号y,姓名y,部门代码y,职业,基本工资+福利补贴+奖励工资-失业保险-住房公积金as 实际工资,日期 FROM 员工,工资
WHERE员工工号y=员工工号g and 员工工号g=@员工工号y END
执行存储过程员工工号y_实际工资
execute 员工工号y_实际工资'1110011'
2）、创建存储过程查询每个员工的扣除工资
----USE ygxz ----GO
----CREATE PROCEDURE 员工工号y_扣除工资 ----@员工工号y char(10)='%' ----AS ----BEGIN
----SELECT 员工工号y,姓名y,部门代码y,职业, 失业保险+住房公积金as 扣除工资,日期 ----FROM 员工,工资
----WHERE 员工工号y=员工工号g and 员工工号g=@员工工号y ----END --
----执行存储过程员工工号y_扣除工资
----execute 员工工号y_扣除工资'1110011'
3）、创建存储过程查询各部门的人数
USE ygxz GO
18
