04551309.sql

数据库课程设计

/* 建立数据库－人事管理 */
create database 人事管理   
on 
(name=per_dat,
 filename='c:\program files\microsoft sql server\MSSQL\Data\perdat.mdf'
 )
log on
(name=per_log,
 filename='c:\program files\microsoft sql server\MSSQL\Data\perlog.ldf'
 )
-------------------------------/* 建表 */-----------------------------

create table 员工 (           /* 创建员工表，需要一些约束条件 */
员工编号 char(10) check(员工编号>1000 and 员工编号<9999) primary key  not null,
姓名 char(10) not null,
性别 char(2) check(性别='f' or 性别='F' or 性别='M' or 性别='m'),
年龄 int check(年龄>0 and 年龄<100),
电话 char(12) check(isnumeric(电话)=1),
部门 char(10) default '教工处'foreign key references 部门类,
职位 char(10) default '讲师',
学历 char(8) default '大学',
email varchar(20),
住址 char(40))

create table 部门类(     /* 创建部门类表 */
部门 char(10) default '教工处'primary key ,
职位 char(10) default '讲师',
状态 char(10) check(状态='固定员工' or 状态='流动员工')default '固定员工')

create table 工资(          /* 创建工资表 */
员工编号 char(10) check(员工编号>1000 and 员工编号<9999) primary key not null foreign key references 员工,
基本工资 int not null default 800,
奖励工资 smallmoney default 0,
福利 smallmoney default 0,
实际工资 smallmoney not null default 800,
月份 int default datepart(month,'月份')not null)

create table 考勤(       /* 创建考勤表 */
员工编号 char(10) check(员工编号>1000 and 员工编号<9999) primary key not null foreign key references 员工,
状态 char(10) check(状态='正常' or 状态='请假' or 状态='迟到' or 状态='早退' or 状态='缺勤')default '正常',
考勤时间 datetime default getdate())

------------------------/* 创建视图 */--------------------
create view v_基本员工信息(员工编号,姓名,性别,年龄,电话,部门,职位,学历,email,住址,基本工资,状态) /* 基本员工信息视图 */
as
select 员工.员工编号,姓名,性别,年龄,电话,员工.部门,员工.职位,学历,email,住址,基本工资,状态
from 员工,工资,部门类
where 员工.员工编号=工资.员工编号 and 员工.部门=部门类.部门

create view v_考勤 as  /* 基本考勤信息视图 */
select 员工.员工编号,姓名,状态,考勤时间
from 员工,考勤
where 员工.员工编号=考勤.员工编号

create view v_统计_讲师(人数,总工资,月份) as   /* 此视图可以统计讲师人数，总工资及月份*/
select count(员工编号),sum(实际工资),月份    /* 利用了count和sum函数*/
from 工资
where 员工编号 in
(select 员工编号 
from 员工 
where 职位='讲师') group by 月份

create view v_统计_财务处(人数,总工资,月份) as /* 此视图可以统计财务处人数，总工资及月份*/
select count(员工编号),sum(实际工资),月份   /* 利用了count和sum函数*/
from 工资
where 员工编号 in
(select 员工编号 
from 员工 
where 部门='财务处') group by 月份

create view v_统计_主任(人数,总工资,月份) as  /* 此视图可以统计主任人数，总工资及月份*/
select count(员工编号),sum(实际工资),月份
from 工资
where 员工编号 in
(select 员工编号 
from 员工 
where 职位='主任') group by 月份 

create view v_人数(人数) as 
select count(员工编号)
from 员工
create view v_统计_秘书(人数,总工资,月份) as /* 此视图可以统计秘书人数，总工资及月份*/
select count(员工编号),sum(实际工资),月份
from 工资
where 员工编号 in
(select 员工编号 
from 员工 
where 职位='秘书') group by 月份

create view v_统计_教工处(人数,总工资,月份) as /* 此视图可以统计教工处人数，总工资及月份*/
select count(员工编号),sum(实际工资),月份
from 工资
where 员工编号 in
(select 员工编号 
from 员工 
where 部门='教工处') group by 月份

-----------------------/* 创建触发器 */------------------------------
create trigger ins1_trigger   /* 在员工表发生插入操作时输出“插入了一个员工元组*/
on 员工
for insert
as print '插入了一个员工元组'

create trigger ins2_trigger  /* 在员工资发生插入操作时输出“插入了一个工资元组*/
on 工资
for insert
as print '插入了一个工资元组'

create trigger ins3_trigger  /* 在考勤表发生插入操作时输出“插入了一个考勤元组*/
on 考勤
for insert
as print '插入了一个考勤元组'

create trigger e_del_trigger  /* 在员工表发生删除操作时，自动将参照该员工元组置相关工资为null*/
on 员工 for delete
as 
update 工资
set 员工编号=null
where 员工编号 in (select 员工编号 from deleted)

create trigger e_del1_trigger /* 在员工表发生删除操作时，自动将参照该员工元组置相关考勤为null*/ 
on 员工 for delete
as 
update 考勤
set 员工编号=null
where 员工编号 in (select 员工编号 from deleted)

create trigger upd_trigger  /* 更新类触发器，根据变动进行更新*/
on 员工 for update
as
declare @emp_no char(10)
if update(部门)
begin 
if not exists(select * from 部门 where 员工编号=(select 员工编号 from inserted))
begin 
raiserror('没有此部门',16,1)
rollback transaction
end
else
begin
select @emp_no=员工编号 from inserted
end
end  
 

-------------/* 创建存储过程 */--------------------------------------
create PROCedure pro_员工;1  /* 可得到员工的信息 */
AS 
SELECT * FROM 员工

create procedure pro_考勤;1   /* 可得到考勤信息 */
as
select * from 考勤

create PROCedure Pro_工资;1     /* 可得到工资信息 */
as 
select * from 工资

create proc 按编号查询;1   /* 可根据员工编号进行查询 */
(@需查询的员工编号 char(10))
as
if @需查询的员工编号 is null
begin
print '无该员工编号!' 
return -103
end
else 
select 员工.员工编号,姓名,性别,年龄,电话,员工.部门,员工.职位,学历,email,住址,实际工资,月份,状态 from 员工,工资,部门类
where 员工.员工编号=@需查询的员工编号 and 工资.员工编号=@需查询的员工编号 and 部门类.部门=员工.部门

create proc 按姓名查询;1   /* 可根据员工姓名进行查询 */
(@需查询的员工姓名 char(10))
as
if @需查询的员工姓名 is null
begin
print '无该员工编号!' 
return -103
end
else 
select 姓名,性别,年龄,电话,员工.部门,员工.职位,学历,email,住址,实际工资,月份,状态 from 员工,工资,部门类
where  员工.员工编号=工资.员工编号 and 姓名=@需查询的员工姓名 and 部门类.部门=员工.部门

create proc 插入新员工;1    /* 可以插入到员工和工资表中各类数据 */
(@员工编号 char(10)=null,@姓名 char(10)=null,@性别 char(2)=null,@年龄 int=null,@电话 char(12)=null,@部门 char(10)=null,@职位 char(10)=null,@学历 char(8)=null,
         @email varchar(20)=null,@住址 char(40)=null,@基本工资 int=null,@奖励工资 smallmoney=null,@福利 smallmoney=null,@实际工资 smallmoney=null,@月份 int=null)

as
 if @员工编号 is null
  begin
   print '必须提供个编号'
     return 13
end
  if @姓名 is null
    begin
    print '必须提供个姓名'
    return 13
 end
if @基本工资 is null
    begin
    print '必须提供基本工资'
    return 13
 end
if @月份 is null
    begin
    print '必须提供月份'
    return 13
 end
insert into 员工 values (@员工编号,@姓名,@性别,@年龄,@电话,@部门,@职位,@学历,@email,@住址)
select * from 员工
where 员工.员工编号=@员工编号
insert into 工资 values (@员工编号,@基本工资,@奖励工资,@福利,@实际工资,@月份)
select * from 工资
where 工资.员工编号=@员工编号


create proc 删除员工信息;1     /* 可以根据员工编号删除该员工信息*/
(@员工编号 char(10)=null)
as 
if @员工编号 is null
  begin
   print '必须提供个编号'
     return 13
end

delete from 员工
where  员工.员工编号=@员工编号
delete from 工资
where  工资.员工编号=@员工编号
delete from 考勤
where  考勤.员工编号=@员工编号


create proc 插入员工考勤信息;1   /* 可插入员工考勤信息 */
(@员工编号 char(10)=null,@状态 char(10)=null,@考勤时间 datetime)
as
 if @员工编号 is null
  begin
   print '必须提供个编号'
     return 13
end
 
insert into 考勤 values (@员工编号,@状态,@考勤时间)
select * from 考勤
where 考勤.员工编号=@员工编号

-------------------- /* 索引 */-------------------------------------
create index emp_idx on 员工(员工编号) /* 以下为员工、考勤、工资的索引*/
create index 考勤_idx on 考勤(员工编号)
create index 工资_idx on 工资(员工编号)