员工管理系统SQL(4)

附录一 系统资源

1. 数据库创建代码

create database department on

( name = department_dat1,

filename = 'e:\\department_dat1.mdf', size = 2MB, maxsize = 5, filegrowth = 20% ),

( name = department_dat2,

filename = 'e:\\department_dat2.ndf', size = 2MB, maxsize = 5, filegrowth = 20%)

- 16 -

log on

( name =department_log,

filename = 'e:\\department_log.ldf', size = 1MB, maxsize = 2MB, filegrowth = 20% ) go

use department go

create table information ( ) go

use department go

create table operation (

num char(11) not null constraint pk_xh primary key, name char(10) not null,

idnum char(20) not null constraint un_xh unique, hometown varchar(50) not null, address varchar(50) not null, tel char(7) not null, base_pay char(20),

constraint ck_len check (len(idnum) = 15 or len(idnum) = 17)

onum char(5) not null constraint pk_kcdh primary key, oname char(20) not null, worth char(20) not null

- 17 -

) go

create unique index inx_course_kcm on dbo.operation(onum) go

use department go

create table wages (

num char(11) not null, onum char(5) not null, amount char(10) not null,

CONSTRAINT pk_id PRIMARY KEY(num,onum), CONSTRAINT fk_xh FOREIGN KEY (num) REFERENCES dbo.information (num) on delete cascade on update cascade, CONSTRAINT fk_nh FOREIGN KEY (onum) REFERENCES dbo.operation (onum) on delete cascade on update cascade )

use department go

create table users (

username char(11) not null CONSTRAINT pk_od PRIMARY KEY, code char(11) not null, limit char(10) not null,

- 18 -

CONSTRAINT fk_ah FOREIGN KEY (username) REFERENCES dbo.information (num) on delete cascade on update cascade ) go

use department go

insert into information

values('9607039','张昆','530120169021101','安徽','江岸小区45幢3单元408','5033226','1800') insert into information

values('9907002','李明','530120170060701','云南','江岸小区50幢2单元409','5033228','2000') insert into information

values('9801055','王风','530120171072501','湖北','白马小区15幢3单元101','4133224','2200') insert into information

values('9902006','黄光美','530120170122901','湖南','金星小区8幢3单元208','3133218','2500') insert into information

values('9704001','钱丽','530120168121101','云南','静园小区12幢3单元109','2133227','2600') insert into information

values('9603001','王迷','530120174050101','黑龙江','江岸小区40幢1单元612','5033219','1300') insert into information

values('9606005','赵人','530120175040702','江苏','江岸小区30幢4单元222','5033123','1000') insert into information

- 19 -

values('9803011','李游','530120173021201','四川','白马小区25幢3单元410','4133124','1200') insert into information

values('9908088','孙客如','530120172092801','贵州','金星小区55幢3单元214','3133177','1100') insert into information

values('9608066','于云','530120174092201','云南','阳光小区55幢5单元112','3133222','1500') insert into information

values('9805026','李春','530120174110901','云南','阳光小区2幢1单元444','3133789','3000') insert into information

values('9702033','高城','530120170080401','江苏','阳光小区8幢2单元808','3148212','2800')

use department go

insert into operation values('20511','业务一',4) insert into operation values('10101','业务二',2) insert into operation values('20801','业务三',3) insert into operation values('10218','业务四',4) insert into operation values('11001','业务五',6) insert into operation

- 20 -

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库员工管理系统SQL(4)在线全文阅读。