sqldb.sql

一个简历管理系统

/*
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BasicInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BasicInfo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BringUpLive]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BringUpLive]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ContactInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ContactInfo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CurrentWork]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CurrentWork]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EducateLive]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EducateLive]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[JobPurpose]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[JobPurpose]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SkillInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SkillInfo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WorkLive]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WorkLive]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OtherCert]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OtherCert]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[User]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[User]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Admin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Admin]
GO
*/

/*管理员表*/
CREATE TABLE [dbo].[Admin] (
	[ID] [int] PRIMARY KEY ,/*管理员编号*/
	[AdminName] [nvarchar] (20) NOT NULL ,/*管理员名*/
	[Password] [nvarchar] (32) NULL ,/*密码*/
	[AddTime] [datetime] NULL ,/*添加时间*/
	[LastLoginIP] [nvarchar] (40) NULL ,/*上次登录IP*/
	[LastLoginTime] [datetime] NULL /*上次登录时间*/
)
GO

INSERT INTO [Admin]([ID],[AdminName],[Password],[AddTime]) VALUES(1,'admin','9663111d812d705cef8cb54e2b911ec7',getdate())
GO

/*用户表*/
CREATE TABLE [dbo].[User] (
	[ID] [int] PRIMARY KEY ,/*用户编号*/
	[UserName] [nvarchar] (20) NOT NULL ,/*用户名*/
	[Password] [nvarchar] (32) NULL ,/*密码*/
	[RegisterTime] [datetime] NULL ,/*注册时间*/
	[ReleaseTime] [datetime] NULL /*发布时间*/
)
GO

/*基本信息表*/
CREATE TABLE [dbo].[BasicInfo] (
	[UserID] [int] REFERENCES [User]([ID]) PRIMARY KEY ,/*用户编号*/
	[RealName] [nvarchar] (10) NULL ,/*姓名*/
	[Sex] [bit] NULL ,/*性别*/
	[Birthday] [datetime] NULL ,/*出生年月*/
	[Nation] [nvarchar] (10) NULL ,/*民族*/
	[PolityVisage] [nvarchar] (10) NULL ,/*政治面貌*/
	[NativePlace] [nvarchar] (50) NULL ,/*籍贯*/
	[RegPerRes] [nvarchar] (50) NULL ,/*户口所在地*/
	[Course] [nvarchar] (10) NULL ,/*学历*/
	[CourseType] [nvarchar] (10) NULL ,/*学历类型*/
	[StudyForm] [nvarchar] (10) NULL ,/*学习形式*/
	[AbilityType] [nvarchar] (10) NULL ,/*人才类别*/
	[LeaveSchoolType] [nvarchar] (10) NULL ,/*毕业/结业*/
	[GraduateSchool] [nvarchar] (30) NULL ,/*毕业院校*/
	[Speciality] [nvarchar] (15) NULL ,/*专业*/
	[Photo] [image] NULL /*相片*/
)
GO

/*联系信息表*/
CREATE TABLE [dbo].[ContactInfo] (
	[UserID] [int] REFERENCES [User]([ID]) PRIMARY KEY ,/*用户编号*/
	[MobilePhone] [nvarchar] (20) NULL ,/*手机号码*/
	[LineatePhone] [nvarchar] (20) NULL ,/*固定电话*/
	[Postalcode] [nvarchar] (10) NULL ,/*邮编*/
	[Address] [nvarchar] (50) NULL ,/*联系地址*/
	[Email] [nvarchar] (30) NULL ,/*电子邮件*/
	[WebSite] [nvarchar] (50) NULL /*个人主页*/
)
GO

/*教育经历表*/
CREATE TABLE [dbo].[EducateLive] (
	[ID] [int] IDENTITY(1,1) PRIMARY KEY ,/*自动编号*/
	[UserID] [int] REFERENCES [User]([ID]) NOT NULL ,/*用户编号*/
	[CurrentCalling] [nvarchar] (20) NULL ,/*现从事行业*/
	[From] [datetime] NULL ,/*开始时间*/
	[To] [datetime] NULL ,/*结束时间*/
	[Speciality] [nvarchar] (15) NULL ,/*所属专业*/
	[Course] [nvarchar] (10) NULL /*学历*/
)
GO

/*现职业概况表*/
CREATE TABLE [dbo].[CurrentWork] (
	[UserID] [int] REFERENCES [User]([ID]) PRIMARY KEY ,/*用户编号*/
	[SchoolName] [nvarchar] (30) NULL ,/*学校名称*/
	[CurrentProfession] [nvarchar] (20) NULL ,/*现从事职业*/
	[ProfessionalLevel] [nvarchar] (10) NULL ,/*现职位级别*/
	[CurrentPay] [smallmoney] NULL /*目前薪水*/
)
GO

/*工作经历表*/
CREATE TABLE [dbo].[WorkLive] (
	[ID] [int] IDENTITY(1,1) PRIMARY KEY ,/*自动编号*/
	[UserID] [int] REFERENCES [User]([ID]) NOT NULL ,/*用户编号*/
	[UnitsName] [nvarchar] (30) NULL ,/*单位名称*/
	[From] [datetime] NULL ,/*开始时间*/
	[To] [datetime] NULL ,/*结束时间*/
	[DeptJob] [nvarchar] (30) NULL ,/*所属部门及所任职位*/
	[Responsibility] [nvarchar] (30) NULL /*工作职责*/
)
GO

/*培训经历表*/
CREATE TABLE [dbo].[BringUpLive] (
	[ID] [int] IDENTITY(1,1) PRIMARY KEY ,/*自动编号*/
	[UserID] [int] REFERENCES [User]([ID]) NOT NULL ,/*用户编号*/
	[CoursesName] [nvarchar] (10) NULL ,/*课程名称*/
	[From] [datetime] NULL ,/*开始时间*/
	[To] [datetime] NULL ,/*结束时间*/
	[Certificate] [nvarchar] (20) NULL ,/*获得证书*/
	[Organization] [nvarchar] (20) NULL /*培训机构*/
)
GO

/*能力信息表*/
CREATE TABLE [dbo].[SkillInfo] (
	[UserID] [int] REFERENCES [User]([ID]) PRIMARY KEY ,/*用户编号*/
	[OnesStrongSuit] [ntext] NULL ,/*职业技能与特长*/
	[Language] [ntext] NULL ,/*外语及方言*/
	[ITSkill] [ntext] NULL ,/*IT技能*/
	[SelfEvaluate] [ntext] NULL ,/*自我评价*/
	[StateAtSchool] [ntext] NULL ,/*在校情况*/
	[Remark] [ntext] NULL /*特别说明*/
)
GO

/*求职意向表*/
CREATE TABLE [dbo].[JobPurpose] (
	[ID] [int] IDENTITY(1,1) PRIMARY KEY ,/*自动编号*/
	[UserID] [int] REFERENCES [User]([ID]) NOT NULL ,/*用户编号*/
	[Job] [nvarchar] (20) NULL ,/*应聘职位*/
	[ExpectPay] [smallmoney] NULL ,/*期望薪水（税前）*/
	[JobProperty] [nvarchar] (20) NULL ,/*工作性质*/
	[OnDutyTime] [nvarchar] (20) NULL /*到岗时间*/
)
GO

/*其他证书表*/
CREATE TABLE [dbo].[OtherCert] (
	[ID] [int] IDENTITY(1,1) PRIMARY KEY ,/*自动编号*/
	[UserID] [int] REFERENCES [User]([ID]) NOT NULL ,/*用户编号*/
	[OtherCertificate] [nvarchar] (20) NULL ,/*证书名称*/
	[GetTime] [datetime] NULL /*获得时间*/
)
GO