【三层架构】数据库设计

数据库是网站的一个根本的数据载体，也是最终需要呈现的材料，所以说数据库的设计也是很重要的。大概分几步 1.建立数据库和表 IF NOT EXIST CREATE DATABASE CompanySystem 创建表： 用户表 CREATE TABLE [dbo].[custom](     [id] [int] IDENTITY(1,1) NOT NULL,     [cname] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,     [departID] [int] NOT NULL,     [age] [int] NOT NULL,     [ename] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,     [password] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,  CONSTRAINT [PK_custom] PRIMARY KEY CLUSTERED (     [id] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY];   部门表 CREATE TABLE [dbo].[department](     [id] [int] IDENTITY(1,1) NOT NULL,     [departname] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,     [description] [nchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,  CONSTRAINT [PK_department] PRIMARY KEY CLUSTERED (     [id] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY]

2.建完数据库表后,开始写存储过程,插入一条数据:

CREATE PROCEDURE [dbo].[spInsertCustom] @cname nvarchar(50), @ename nvarchar(50), @ageint, @departIDint, @password nvarchar(50) AS BEGIN insertinto custom(cname,departID,age,ename,password) values (@cname,@departID,@age,@ename,@password) END   RETURN @@Identity

create PROCEDURE [dbo].[spInsertDepartment] @departname nvarchar(50), @description nvarchar(50) AS BEGIN     insertinto department(departname,description)values(@departname,@description) END   RETURN @@Identity

3.写一些对数据操作的存储过程，现建两个更新一条数据的存储过程:

CREATE PROCEDURE [dbo].[spupdatecustom] @idint, @cname nvarchar(50), @departIDint, @ageint, @ename nvarchar(50), @password nvarchar(50) AS BEGIN     update        custom     set     cname = @cname,     departID = @departID,     age = @age,     ename = @ename,     password = @password     where id = @id END COMMIT TRAN

create procedure spupdatedepart ( @departname nvarchar(50), @description nchar(10), @idint ) as UPDATE [dbo].[department]    SET [departname] = @departname       ,[description] = @departname  WHERE id=@id

再新建两个取出所有用户的存储过程:

CREATE PROCEDURE [dbo].[spGetcustom]       AS BEGIN     select *from custom order by id desc END

create PROCEDURE [dbo].[spGetAlldepartment]   AS BEGIN     select *from department END

再新建一个根据ID取出一条数据的存储过程:

CREATE PROCEDURE [dbo].[spGetcustomer] @idint AS BEGIN  select *from customwhere id = @id END

现建一个根据部门名取部门ID的存储过程:

create PROCEDURE [dbo].[spGetdepartmenter] @departname nvarchar(50) AS BEGIN     select *from departmentwhere departname = @departname END

再建两个根据ID删除数据的存储过程:

create PROCEDURE [dbo].[spDeletecustom] @idint AS BEGIN      delete custom where id = @id END

CREATE PROCEDURE spdeletedepart @idint AS BEGIN     delete department where id = @id END GO 数据库设计好之后，下面就开始设计数据库的工具类了。