database.sql

小E单班同学录系统

/*
建立一个SQL数据库
执行完后设置好web.config，然后在网站中注册一个新用户，在进入数据库把Users表里该用户的IsAdmin字段设为True，即成为管理员
后台地址为：http://您的网址/Admin/
用户李宏 密码 fdsjkl 进入后台管理设置管理员,然后可删除该用户
*/

Create DataBase Class
Go

use Class
Go


Create Table Users						
(
	ID int identity(1,1),				
	UserName varchar(16) primary key not null,
	UserPwd varchar(32) not null,			
	RealName varchar(10) not null,			
	Sex char(2) not null,				
	Birthday datetime default 0,					
	Phone varchar(15),						
	Mobile varchar(16),					
	QQ varchar(12),					
	ICQ varchar(12),						
	MSN varchar(30),					
	Face Varchar(50),				
	Mail Varchar(30),				
	PwdQue Varchar(50) not null,		
	PwdAnswer Varchar(50) not null,		
	Web Varchar(30),					
	Address Varchar(60),				
	WorkAdd Varchar(50),				
	Zipcode char(6),				
	UnderWrite Varchar(255),		
	LastLogin datetime,			
	IsVaild bit default 0,			
	IsTeacher bit default 0,	
	IsAdmin bit default 0,					
	RegTime datetime default getdate(),
	SendMail int default year(getdate()),
	MessageCount int default 0,		
)

Create Table Message					
(
	ID int identity(1,1),				
	Sender varchar(16),				
	Receiver varchar(16),				
	Contents varchar(256),
	IsRead bit default 0,
	SendTime datetime default getdate()
)
Create Table Topic					
(
	ID int identity(1,1) primary key,		
	UserName varchar(16),					
	LastReplyUserName varchar(16),		
	Title varchar(50) not null,			
	Contents varchar(max) not null,		
	Reverts int default 0,					
	Hits int default 0,							
	LastReplyTime datetime default getdate(),
	PostTime datetime default getdate()	
)

Create Table ReTopic					
(
	ID int identity(1,1) primary key,	
	TopicID int not null,				
	UserName varchar(16) not null,				
	Contents varchar(max) not null,		
	PostTime datetime default getdate()	
)

Create Table Photo							
(
	ID int identity(1,1) primary key,		
	UserName varchar(16) not null,		
	ThePath varchar(50) not null,			
	Title varchar(50) not null,			
	Contents varchar(500),				
	ClassName varchar(20) not null,				
	ClassID int,							
	Hits int default 0,	
	Reverts int default 0,			
	PostTime datetime default getdate()	
)

Create Table RePhoto					
(
	ID int identity(1,1) primary key,		
	PhotoID int not null,					
	UserName varchar(16) not null,		
	Contents varchar(max) not null,		
	PostTime datetime default getdate()
)

Create Table PhotoClass				
(
	ID int identity(1,1) primary key,	
	ClassName varchar(20),
	TheDesc int default 0				
)

Create Table ClassInfo					
(
	ClassName varchar(50) primary key,	
	ClassYear char(4),	
	About varchar(max),					
	ClassNotice varchar(max),				
	ClassEnounce varchar(max),					
	ClassMode bit default 1,					
	AllUserCount int default 0,
	AllPhotoCount int default 0,
	AllTopicCount int default 0,
	SiteName varchar(50),
	SiteUrl varchar(50),
	SendMail bit default 0,
	MailServer varchar(50),
	MailUserName varchar(50),
	MailUserPwd varchar(50),
	MailTitle varchar(50),
	MailContext varchar(max),
	MailBirthdayTitle varchar(50),
	MailBirthdayContext varchar(max)
)

Create Table Vote
(
	ID int identity(1,1) primary key,
	Title varchar(16),
	Coun int default 0,
	Member bit default 0
)	

Create Table VoteCon
(
	ID int identity(1,1) primary key,
	Cont varchar(16),
	Coun int default 0,
	VID int
)	

insert into ClassInfo(SiteName,ClassYear,ClassMode,ClassName) values('贵州大学明德学院计科04151',2004,1,'贵州大学明德学院计科04151班级同学录')
insert into Users(UserName,UserPwd,RealName,Sex,Mail,PwdQue,PwdAnswer,IsAdmin,IsVaild) values('leehom','D91037F7905FCEBB6CAAA49A7957A52D','李宏','男','[e-mail]','fdsjkl','D91037F7905FCEBB6CAAA49A7957A52D',1,1)