我以前做的系统的数据库,给大家贴一个,现在看来那套系统不是很好,大家别见笑
create table usertable --创建普通用户表
(u_id int identity(1,1) primary key,
u_username varchar(20) not null,
u_pwd varchar(8) not null,
u_name varchar(10) not null,
u_class varchar(7),
u_sex char(2) not null check(u_sex='男' or u_sex='女'),
u_no varchar(10),
u_date smalldatetime not null,
u_estate int default(0) not null
)
go
create table admintable --创建管理员表
(a_id int identity(1,1) primary key,
a_adminname varchar(20) not null,
a_pwd varchar(8) not null,
a_name varchar(10) not null,
a_sex char(2) not null check(a_sex='男' or a_sex='女'),
a_status varchar(10) not null,
a_date smalldatetime not null
)
go
create table subject --创建科目管理表
(sub_id int identity(1,1) primary key,
sub_subject varchar(20) not null,
sub_grade varchar(20) not null,
sub_date smalldatetime,
a_id int,
foreign key(a_id) references admintable
)
go
create table theme --创建试题表
(t_id int identity(1,1) primary key,
t_question text not null,
t_answer text not null,
t_rightanswer varchar(10) not null,
t_diff char not null,
t_chapter varchar(30),
t_analyse text,
t_date smalldatetime null,
t_haveselect int default(0) not null,
sub_id int not null,
t_number int,
foreign key(sub_id) references subject
)
go
create table idea --创建试题意见表
(i_id int identity(1,1) primary key,
t_id int not null,
i_idea text,
u_id int not null,
i_date smalldatetime not null,
foreign key(t_id) references theme,
foreign key(u_id) references usertable
)
go
create table headexam --创建考试表(主表)
(h_id int identity(1,1) primary key,
u_id int not null,
c_id int not null,
h_date smalldatetime not null,
h_grade varchar(8),
h_subject varchar(15),
foreign key(u_id) references usertable,
foreign key(c_id) references collocate
)
go
create table success --创建成绩表
(s_id int identity(1,1) primary key,
u_id int not null,
h_id int not null,
s_succ int,
foreign key(u_id) references usertable,
foreign key(h_id) references headexam
)
go
create table new --创建新闻表
(n_id int identity(1,1) primary key,
n_title text,
n_content text,
a_id int,
foreign key(a_id) references admintable
)