📄 createdb.sql
字号:
/*==============================================================*/
/* DBMS name: Microsoft SQL Server 2000 */
/* Created on: 2007-1-9 12:40:00 */
/*数据库名StuDB，主文件大小：2M,日志文件大小：1M 。 */
/*在执行以下代码之前，请确认你创建了StuDB数据库 */
/*注：将以下代码导入到查询分析器中运行，在消息提示窗口出现的那些错误信息是正常的，数据库是完好的。 */
/*==============================================================*/
alter table ta_change
drop constraint FK_TA_CHANG_REFERENCE_TA_STUDE
go
alter table ta_change
drop constraint FK_TA_CHANG_REFERENCE_TA_CHANG
go
alter table ta_class
drop constraint FK_TA_CLASS_REFERENCE_TA_MAJOR
go
alter table ta_family
drop constraint FK_TA_FAMIL_REFERENCE_TA_STUDE
go
alter table ta_honour
drop constraint FK_TA_HONOU_REFERENCE_TA_STUDE
go
alter table ta_major
drop constraint FK_TA_MAJOR_REFERENCE_TA_DEPAR
go
alter table ta_resume
drop constraint FK_TA_RESUM_REFERENCE_TA_STUDE
go
alter table ta_reward
drop constraint FK_TA_REWAR_REFERENCE_TA_STUDE
go
alter table ta_score
drop constraint FK_TA_SCORE_REFERENCE_TA_STUDE
go
alter table ta_score
drop constraint FK_TA_SCORE_REFERENCE_TA_CLASS
go
alter table ta_stntCourse
drop constraint FK_TA_STNTC_REFERENCE_TA_STUDE
go
alter table ta_student
drop constraint FK_TA_STUDE_REFERENCE_TA_CLASS
go
if exists (select 1
from sysobjects
where id = object_id('ta_admin')
and type = 'U')
drop table ta_admin
go
if exists (select 1
from sysobjects
where id = object_id('ta_change')
and type = 'U')
drop table ta_change
go
if exists (select 1
from sysobjects
where id = object_id('ta_changeType')
and type = 'U')
drop table ta_changeType
go
if exists (select 1
from sysobjects
where id = object_id('ta_class')
and type = 'U')
drop table ta_class
go
if exists (select 1
from sysobjects
where id = object_id('ta_department')
and type = 'U')
drop table ta_department
go
if exists (select 1
from sysobjects
where id = object_id('ta_family')
and type = 'U')
drop table ta_family
go
if exists (select 1
from sysobjects
where id = object_id('ta_honour')
and type = 'U')
drop table ta_honour
go
if exists (select 1
from sysobjects
where id = object_id('ta_log')
and type = 'U')
drop table ta_log
go
if exists (select 1
from sysobjects
where id = object_id('ta_major')
and type = 'U')
drop table ta_major
go
if exists (select 1
from sysobjects
where id = object_id('ta_resume')
and type = 'U')
drop table ta_resume
go
if exists (select 1
from sysobjects
where id = object_id('ta_reward')
and type = 'U')
drop table ta_reward
go
if exists (select 1
from sysobjects
where id = object_id('ta_score')
and type = 'U')
drop table ta_score
go
if exists (select 1
from sysobjects
where id = object_id('ta_stntCourse')
and type = 'U')
drop table ta_stntCourse
go
if exists (select 1
from sysobjects
where id = object_id('ta_student')
and type = 'U')
drop table ta_student
go
/*==============================================================*/
/* Table: ta_admin */
/*==============================================================*/
create table ta_admin (
userId int identity,
userName varchar(50) not null,
password varchar(50) not null,
role int null default 0,
status int null default 0,
loginTimes int null default 0,
constraint PK_TA_ADMIN primary key (userId)
)
go
/*==============================================================*/
/* Table: ta_change */
/*==============================================================*/
create table ta_change (
id int identity,
stntId varchar(50) not null,
changeId int not null,
changeTime varchar(30) not null,
changeIntro varchar(1000) null default '无具体说明信息',
constraint PK_TA_CHANGE primary key (id)
)
go
/*==============================================================*/
/* Table: ta_changeType */
/*==============================================================*/
create table ta_changeType (
changeId int identity,
changeName varchar(20) not null,
changeIntro varchar(500) null default '无说明',
constraint PK_TA_CHANGETYPE primary key (changeId)
)
go
/*==============================================================*/
/* Table: ta_class */
/*==============================================================*/
create table ta_class (
classId int identity,
majorId int not null,
classYear varchar(10) not null,
classNum varchar(10) not null,
classDemo varchar(1000) null default '目前还没有班级备注信息',
constraint PK_TA_CLASS primary key (classId)
)
go
/*==============================================================*/
/* Table: ta_department */
/*==============================================================*/
create table ta_department (
departmentId int identity,
departmentName varchar(50) not null,
introduction varchar(2000) null default '目前还没有详细说明',
constraint PK_TA_DEPARTMENT primary key (departmentId)
)
go
/*==============================================================*/
/* Table: ta_family */
/*==============================================================*/
create table ta_family (
id int identity,
stntId varchar(50) not null,
relation varchar(20) not null,
memberName varchar(10) not null,
profession varchar(50) null default '职业不详',
birthday varchar(30) null default '未知',
demo varchar(1000) null default '无备注信息',
constraint PK_TA_FAMILY primary key (id)
)
go
/*==============================================================*/
/* Table: ta_honour */
/*==============================================================*/
create table ta_honour (
honourId int not null,
stntId varchar(50) not null,
honourDate varchar(30) not null,
honourDetail varchar(500) not null,
honourPic varchar(100) null default 'honourPic/nohonour.jpg',
constraint PK_TA_HONOUR primary key (honourId)
)
go
/*==============================================================*/
/* Table: ta_log */
/*==============================================================*/
create table ta_log (
logId int identity,
loginName varchar(50) null default '未知登录者',
logTime datetime null default getdate(),
ip varchar(50) null default '‘未知IP’',
constraint PK_TA_LOG primary key (logId)
)
go
/*==============================================================*/
/* Table: ta_major */
/*==============================================================*/
create table ta_major (
majorId int identity,
departmentId int not null,
majorName varchar(100) not null,
majorDetail varchar(2000) null default '目前还没有详细的专业说明',
constraint PK_TA_MAJOR primary key (majorId)
)
go
/*==============================================================*/
/* Table: ta_resume */
/*==============================================================*/
create table ta_resume (
resumeId int identity,
stntId varchar(50) not null,
beginTime varchar(30) not null,
endTime varchar(30) not null,
resumeName varchar(100) not null,
resumeDetail varchar(1000) not null,
constraint PK_TA_RESUME primary key (resumeId)
)
go
/*==============================================================*/
/* Table: ta_reward */
/*==============================================================*/
create table ta_reward (
id int identity,
stntId varchar(50) not null,
rewardTime varchar(30) not null,
rewardThing varchar(1000) not null,
type varchar(10) not null,
constraint PK_TA_REWARD primary key (id)
)
go
/*==============================================================*/
/* Table: ta_score */
/*==============================================================*/
create table ta_score (
scoreId int identity,
stntId varchar(50) not null,
studyYear varchar(10) not null,
studyTerm varchar(10) not null,
classId int not null,
scoreType varchar(30) not null,
courseName varchar(50) not null,
detail varchar(50) not null,
constraint PK_TA_SCORE primary key (scoreId)
)
go
/*==============================================================*/
/* Table: ta_stntCourse */
/*==============================================================*/
create table ta_stntCourse (
id int identity,
stntId varchar(50) not null,
studyYear varchar(10) not null,
studyTerm varchar(10) not null,
courseName varchar(50) not null,
courseDes varchar(1000) null default '目前还没有具体的说明',
demo varchar(1000) null default '无备注信息',
constraint PK_TA_STNTCOURSE primary key (id)
)
go
/*==============================================================*/
/* Table: ta_student */
/*==============================================================*/
create table ta_student (
stntId varchar(50) not null,
stntName varchar(10) not null,
sex char(2) not null,
birthday varchar(30) not null,
folk varchar(30) null default '汉族',
polity varchar(30) null default '未知',
idCard varchar(30) null default '未知',
photo varchar(100) null default 'pictures/nophoto.jpg',
hometown varchar(30) null default '未知',
homeAddress varchar(150) null default '不详',
telphone varchar(30) null default '暂无',
email varchar(50) null default 'l暂无',
studyType varchar(20) not null,
beginTime varchar(10) not null,
departmentId int not null,
majorId int not null,
classId int null,
stntDemo varchar(1000) null default '目前还没有此学生的备注信息',
constraint PK_TA_STUDENT primary key (stntId)
)
go
alter table ta_change
add constraint FK_TA_CHANG_REFERENCE_TA_STUDE foreign key (stntId)
references ta_student (stntId)
go
alter table ta_change
add constraint FK_TA_CHANG_REFERENCE_TA_CHANG foreign key (changeId)
references ta_changeType (changeId)
go
alter table ta_class
add constraint FK_TA_CLASS_REFERENCE_TA_MAJOR foreign key (majorId)
references ta_major (majorId)
go
alter table ta_family
add constraint FK_TA_FAMIL_REFERENCE_TA_STUDE foreign key (stntId)
references ta_student (stntId)
go
alter table ta_honour
add constraint FK_TA_HONOU_REFERENCE_TA_STUDE foreign key (stntId)
references ta_student (stntId)
go
alter table ta_major
add constraint FK_TA_MAJOR_REFERENCE_TA_DEPAR foreign key (departmentId)
references ta_department (departmentId)
go
alter table ta_resume
add constraint FK_TA_RESUM_REFERENCE_TA_STUDE foreign key (stntId)
references ta_student (stntId)
go
alter table ta_reward
add constraint FK_TA_REWAR_REFERENCE_TA_STUDE foreign key (stntId)
references ta_student (stntId)
go
alter table ta_score
add constraint FK_TA_SCORE_REFERENCE_TA_STUDE foreign key (stntId)
references ta_student (stntId)
go
alter table ta_score
add constraint FK_TA_SCORE_REFERENCE_TA_CLASS foreign key (classId)
references ta_class (classId)
go
alter table ta_stntCourse
add constraint FK_TA_STNTC_REFERENCE_TA_STUDE foreign key (stntId)
references ta_student (stntId)
go
alter table ta_student
add constraint FK_TA_STUDE_REFERENCE_TA_CLASS foreign key (classId)
references ta_class (classId)
go
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -