数据库课程设计报告

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