数据库作业

set nocount on
set dateformat ymd
use master
go
if not exists(select * from syslogins where name='user01')
exec sp_addlogin user01,user01
go
/*create database*/
if exists(select *from sysdatabases where name='OrderDB')
drop database OrderDB
go
create database OrderDB
on primary
(name='OrderDB',
filename='f:\myWork\OrderDB.mdf',
size=1,
maxsize=5,
filegrowth=1)
log on
(name='OrderLog',
filename='f:\myWork\OrderLog.ldf',
size=1,
maxsize=5,
filegrowth=1)
go

/*data mydatabase*/
use OrderDB
go

/* add my user*/
exec sp_adduser user01,user01
go

-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
/*员工人事表*/
print'creat table Employee'
go
CREATE TABLE Employee(
employeeNo char(8) not null /*员工编号*/
check(employeeNo like '[E][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
employeeName varchar(10) not null, /*员工姓名*/
sex char(1) not null, /*员工性别*/
birthday datetime null, /*员工生日*/
address varchar(50) null, /*员工住址*/
telephone varchar(20) null, /*员工电话*/
hireDate datetime not null, /*雇佣日期*/
department varchar(30) not null, /*所属部门*/
headShip varchar(10) not null, /*职务*/
salary numeric(8,2) not null, /*薪水*/
constraint EmployeePK primary key (employeeNo)
)
go
grant all on Employee to user01
go

/*人事表（employee）数据：*/
insert Employee values('E2005001','喻自强','M','19650415','南京市青海路18号', '13817605008', '19900206','财务科','科长',5800.80)
insert Employee values('E2005002','张小梅','F','19731101','上海市北京路8号', '13607405016', '19910328','业务科','职员',2400)
insert Employee values('E2005003','张小娟','F','19730306','上海市南京路66号', '13707305025', '19920328','业务科','职员',2600)
insert Employee values('E2005004','张露', 'F','19670105','南昌市八一大道130号', '15907205134', '19900328','业务科','科长',4100)
insert Employee values('E2005005','张小东','M','19730903','南昌市阳明路99号', '15607105243', '19920328','业务科','职员',1800)
insert Employee values('E2006001','陈辉', 'M','19651101','南昌市青山路100号', '13607705352', '19900328','办公室','主任',4000)
insert Employee values('E2006002','韩梅', 'F','19731211','上海市浦东大道6号', '13807805461', '19901128','业务科','职员',2600)
insert Employee values('E2006003','刘风', 'F','19730521','江西财经大学5栋1-101室', '15907805578', '19910228','业务科','职员',2500)
insert Employee values('E2007001','吴浮萍','M','1

第1页下一页