YGGL数据库SQL脚本

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'YGGL')
DROP DATABASE YGGL
GO

CREATE DATABASE YGGL
go
use yggl
go

--创建表departments
CREATE TABLE [dbo].[DepartMents](
[DepartmentID] [char](3) NOT NULL,
[Departmentname] [char](20) NOT NULL,
[Note] [varbinary](100) NULL,
CONSTRAINT [PK_DepartMents] PRIMARY KEY CLUSTERED
(
[DepartmentID] ASC
)
) ON [PRIMARY]

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'部门编号，主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DepartMents', @level2type=N'COLUMN',@level2name=N'DepartmentID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'部门名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DepartMents', @level2type=N'COLUMN',@level2name=N'Departmentname'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DepartMents', @level2type=N'COLUMN',@level2name=N'Note'
GO

--创建表salary
CREATE TABLE [dbo].[Salary](
[EmployeeID] [char](6) NOT NULL,
[InCome] [float] NOT NULL,
[OutCome] [float] NOT NULL,
CONSTRAINT [PK_Salary] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)
) ON [PRIMARY]

go

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'员工编号，主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Salary', @level2type=N'COLUMN',@level2name=N'EmployeeID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'收入' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Salary', @level2type=N'COLUMN',@level2name=N'InCome'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'支出' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Salary', @level2type=N'COLUMN',@level2name=N'OutCome'
GO

--创建表employees
CREATE TABLE [dbo].[Employees](
[Employeeid] [char](6) NOT NULL,
[Name] [char](10) NOT NULL,
[Education] [char](4) NOT NULL,
[Birthday] [datetime] NOT NULL,
[Sex] [bit] NOT NULL CONSTRAINT [DF_Employees_sex] DEFAULT ((1)),
[WorkYear] [tinyint] NULL,
[Address] [varchar](40) NULL,
[Phonenumber] [char](12) NULL,
[DepartmentID] [char](3) NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[Employeeid] ASC
)
) ON [PRIMARY]

go

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'员工编员，主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employees', @level2type=N'COLUMN',@level2name=N'Employeeid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employees', @level2type=N'COLUMN',@level2name=N'Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'学历' , @lev

el0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employees', @level2type=N'COLUMN',@level2name=N'Education'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'出生日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employees', @level2type=N'COLUMN',@level2name=N'Birthday'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'性别，默认值为1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employees', @level2type=N'COLUMN',@level2name=N'Sex'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'工作时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employees', @level2type=N'COLUMN',@level2name=N'WorkYear'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'地址' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employees', @level2type=N'COLUMN',@level2name=N'Address'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'电话号码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employees', @level2type=N'COLUMN',@level2name=N'Phonenumber'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'员工部门号，外键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employees', @level2type=N'COLUMN',@level2name=N'DepartmentID'
GO

ALTER TABLE [dbo].[Employees] WITH CHECK ADD CONSTRAINT [FK_Employees_DepartMents] FOREIGN KEY([DepartmentID])
REFERENCES [dbo].[DepartMents] ([DepartmentID])
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_DepartMents]


--输入departments表内容
insert departments(DepartmentID,Departmentname,Note) values( '1 ' , '财务部 ' , NULL )
insert departments(DepartmentID,Departmentname,Note) values( '2 ' , '人力资源部 ' , NULL )
insert departments(DepartmentID,Departmentname,Note) values( '3 ' , '经理办公室 ' , NULL )
insert departments(DepartmentID,Departmentname,Note) values( '4 ' , '研发部 ' , NULL )
insert departments(DepartmentID,Departmentname,Note) values( '5 ' , '市场部 ' , NULL )


--输入employees表内容
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '000001' , '王林 ' , '大专' , '1966-01-23 00:00:00.000' , 1 , 8 , '中山路32-1-508' , '83355668 ' , '2 ' )
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '010008' , '伍容华 ' , '本科' , '1976-03-28 00:00:00.000' , 1 , 3 , '北京东路100-2' , '83321321 ' , '1 ' )
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '020010' , '王向容 ' , '硕士' , '1982-12-09 00:00:00.000' , 1 , 2 , '四牌楼10-0-108' , '8379236

1 ' , '1 ' )
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '020018' , '李丽 ' , '大专' , '1960-07-30 00:00:00.000' , 0 , 6 , '中山东路102-2' , '83413301 ' , '1 ' )
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '102201' , '刘明 ' , '本科' , '1972-10-18 00:00:00.000' , 1 , 3 , '虎距路100-2' , '83606608 ' , '5 ' )
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '102208' , '朱俊 ' , '硕士' , '1965-09-28 00:00:00.000' , 1 , 2 , '牌楼巷5-3-106' , '84708817 ' , '3 ' )
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '108991' , '钟敏 ' , '硕士' , '1979-08-10 00:00:00.000' , 0 , 4 , '中山路10-3-105' , '83346722 ' , '3 ' )
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '111006' , '张石兵 ' , '本科' , '1974-10-01 00:00:00.000' , 1 , 1 , '解放路34-1-203' , '84563418 ' , '5 ' )
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '210678' , '林涛 ' , '大专' , '1977-04-02 00:00:00.000' , 1 , 2 , '中山北路24-35' , '83467336 ' , '3 ' )
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '302566' , '李玉珉 ' , '本科' , '1968-09-20 00:00:00.000' , 1 , 3 , '热和路209-3' , '58765991 ' , '4 ' )
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '308759' , '叶凡 ' , '本科' , '1978-11-18 00:00:00.000' , 1 , 2 , '北京西路3-7-52' , '83308901 ' , '4 ' )
insert employees(Employeeid,Name,Education,Birthday,Sex,WorkYear,Address,Phonenumber,DepartmentID) values( '504209' , '陈林琳 ' , '大专' , '1969-09-03 00:00:00.000' , 0 , 5 , '汉中路120-4-12' , '84468158 ' , '4 ' )


--输入salary表内容
insert salary(EmployeeID,InCome,OutCome) values( '000001' , 2100.8 , 123.09 )
insert salary(EmployeeID,InCome,OutCome) values( '010008' , 1582.62 , 88.03 )
insert salary(EmployeeID,InCome,OutCome) values( '020010' , 2860 , 198 )
insert salary(EmployeeID,InCome,OutCome) values( '020018' , 2347.68 , 180 )
insert salary(EmployeeID,InCome,OutCome) values( '102201' , 2569.88 , 185.65 )
insert salary(EmployeeID,InCome,OutCome) values( '102208' , 1980 , 100 )
insert salary(EmployeeID,InCome,OutCome) values( '108991' , 3259.98 , 281.52 )
insert salary(EmployeeID,InCome,OutCome) values( '111006' , 1987.01 , 79.58 )
insert salary(EmployeeID,InCome,OutCome) values( '210678' , 2240 , 121 )
insert salary(EmployeeID,InCome,OutCome) values( '302566' , 2980.7 , 210.2 )
insert salary(EmployeeID,InCome,OutCome) values( '308759' , 2531.98 , 199.08 )
insert salary(EmployeeID,InCome,

OutCome) values( '504209' , 2066.15 , 108 )