创建库、表.sql

有需要的同仁载回去慢慢研究

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'工时管理')
	DROP DATABASE [工时管理]
GO

CREATE DATABASE [工时管理]  ON (NAME = N'工时管理_Data', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL\Data\工时管理_Data.MDF' , SIZE = 5, FILEGROWTH = 10%) LOG ON (NAME = N'工时管理_Log', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL\Data\工时管理_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
 COLLATE Chinese_PRC_CI_AS
GO

exec sp_dboption N'工时管理', N'autoclose', N'false'
GO

exec sp_dboption N'工时管理', N'bulkcopy', N'false'
GO

exec sp_dboption N'工时管理', N'trunc. log', N'false'
GO

exec sp_dboption N'工时管理', N'torn page detection', N'true'
GO

exec sp_dboption N'工时管理', N'read only', N'false'
GO

exec sp_dboption N'工时管理', N'dbo use', N'false'
GO

exec sp_dboption N'工时管理', N'single', N'false'
GO

exec sp_dboption N'工时管理', N'autoshrink', N'false'
GO

exec sp_dboption N'工时管理', N'ANSI null default', N'false'
GO

exec sp_dboption N'工时管理', N'recursive triggers', N'false'
GO

exec sp_dboption N'工时管理', N'ANSI nulls', N'false'
GO

exec sp_dboption N'工时管理', N'concat null yields null', N'false'
GO

exec sp_dboption N'工时管理', N'cursor close on commit', N'false'
GO

exec sp_dboption N'工时管理', N'default to local cursor', N'false'
GO

exec sp_dboption N'工时管理', N'quoted identifier', N'false'
GO

exec sp_dboption N'工时管理', N'ANSI warnings', N'false'
GO

exec sp_dboption N'工时管理', N'auto create statistics', N'true'
GO

exec sp_dboption N'工时管理', N'auto update statistics', N'true'
GO

use [工时管理]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_每日工时_员工信息]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[每日工时] DROP CONSTRAINT FK_每日工时_员工信息
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[员工信息视图]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[员工信息视图]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[公司工作项目]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[公司工作项目]
GO

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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[操作用户信息]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[操作用户信息]
GO

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

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

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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[轮班分组信息]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[轮班分组信息]
GO

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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[部门工作项目]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[部门工作项目]
GO

CREATE TABLE [dbo].[公司工作项目] (
	[公司工作项目编号] [tinyint] IDENTITY (1, 1) NOT NULL ,
	[公司工作项目名称] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[是否在线] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[员工信息] (
	[工号] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[姓名] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[部门编号] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[性别] [bit] NULL ,
	[人员类别] [bit] NULL ,
	[分组编号] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[操作用户信息] (
	[用户工号] [varchar] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[姓名] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
	[系统管理员] [bit] NULL ,
	[密码] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[权限] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[每日工时] (
	[序号] [int] IDENTITY (1, 1) NOT NULL ,
	[日期] [char] (8) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[工号] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[部门工作项目编号] [int] NOT NULL ,
	[工时] [float] NOT NULL ,
	[上班类型] [tinyint] NULL ,
	[备注] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[系统功能] (
	[功能编号] [smallint] IDENTITY (1, 1) NOT NULL ,
	[功能名称] [char] (30) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[轮班信息] (
	[轮班编号] [smallint] IDENTITY (1, 1) NOT NULL ,
	[轮班名称] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[轮班分组信息] (
	[分组编号] [int] IDENTITY (1, 1) NOT NULL ,
	[分组名称] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[轮班编号] [smallint] NOT NULL ,
	[分组管理员工号] [varchar] (300) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[备注] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[部门信息] (
	[部门编号] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[部门名称] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[工时汇报员工号] [varchar] (400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[部门工作项目] (
	[部门工作项目编号] [int] IDENTITY (1, 1) NOT NULL ,
	[部门编号] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[部门工作项目名称] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[公司工作项目编号] [tinyint] NOT NULL 
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.员工信息视图
AS
SELECT dbo.轮班信息.轮班名称, dbo.员工信息.工号, dbo.员工信息.姓名, 
      dbo.部门信息.部门编号, dbo.部门信息.部门名称, dbo.部门信息.工时汇报员工号, 
      dbo.员工信息.性别, dbo.员工信息.人员类别, dbo.轮班信息.轮班编号, 
      dbo.轮班分组信息.分组编号, dbo.轮班分组信息.分组名称, 
      dbo.轮班分组信息.分组管理员工号
FROM dbo.轮班分组信息 INNER JOIN
      dbo.轮班信息 ON dbo.轮班分组信息.轮班编号 = dbo.轮班信息.轮班编号 INNER JOIN
      dbo.员工信息 ON dbo.轮班分组信息.分组编号 = dbo.员工信息.分组编号 INNER JOIN
      dbo.部门信息 ON dbo.员工信息.部门编号 = dbo.部门信息.部门编号
UNION
SELECT NULL, dbo.员工信息.工号, dbo.员工信息.姓名, dbo.部门信息.部门编号, 
      dbo.部门信息.部门名称, dbo.部门信息.工时汇报员工号, dbo.员工信息.性别, 
      dbo.员工信息.人员类别, NULL, NULL, NULL, NULL
FROM 员工信息, 部门信息
WHERE (员工信息.分组编号 IS NULL) AND (部门信息.部门编号 = 员工信息.部门编号)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO