drop table if exists province;
create table province(
id bigint primary key auto_increment ,
name varchar(64) ,
municipality int default '1' ,
description varchar(255)
);
insert into province (id,Name,Municipality,Description) values (1,'HuBei',0,'WuHan');
insert into province (id,Name,Municipality,Description) values (2,'ChongQing',1,'ZhiXiaShi');
insert into province (id,Name,Municipality,Description) values (3,'HuNan',0,'ChangSha');
insert into province (id,Name,Municipality,Description) values (4,'BeiJing',1,'ShouDu');
commit；
drop table if exists city;
create table city(
id bigint primary key auto_increment ,
name varchar(255) ,
pid bigint not null,
foreign key (pid) references province (id),
description varchar(255)
);
insert into city (id,name,pid,description) values (1,'WuHan',1,'ShengHui');
insert into city (id,name,pid,description) values (2,'ChongQing',1,'ZhiXiaShi');
insert into city (id,name,pid,description) values (4,'BeiJing',1,'ShouDu');
commit;
drop table if exists county;
create table county(
id bigint primary key auto_increment ,
name varchar(255) ,
cid bigint not null ,
foreign key (cid) references city(id),
description varchar(255)
);
insert into county (id,name,cid,description) values (1,'CaoDianQu',1,'WuHanShiQu');
insert into county (id,name,cid,description) values (2,'JieFangBei',1,'ChongQingShiQr');
drop table if exists detailaddress;
create table dtailaddress(
id bigint primary key auto_increment ,
address varchar(255) not null,
cid bigint,
foreign key(cid) references county(id)
);
drop table if exists faculty;
create table faculty(
id bigint primary key auto_increment ,
name varchar(255) ,
createDate date,
description varchar(255)
);
drop table if exists major;
create table major(
id bigint primary key auto_increment,
code bigint ,
name varchar(255),
createDate date,
fid bigint not null ,
foreign key(fid) references faculty(id),
description varchar(255)
);
drop table if exists calssinfo;
create table classinfo(
id bigint primary key auto_increment,
name varchar(255),
createDate date not null,
studentNo bigint,
mid bigint not null,
foreign key (mid) references major(id),
description varchar(255)
);
drop table if exists students;
create table students(
id bigint primary key auto_increment,
studentNo bigint ,
name varchar(255) ,
gender char(2) default '1',
birth date,
phone varchar(255),
daid bigint,
apartment varchar(255),
desription varchar(255)
);
drop table if exists sunbject;
create table subject(
id bigint primary key auto_increment ,
name varchar(255) ,
oblgatory int(11) default '1' ,
status int(11) default '1',
createdate char(11),
description varchar(255)
);
drop table if exists score;
create table score(
id bigint primary key auto_increment ,
score int(11),
testDate date,
ispass int(11) default '1',
studentsid bigint,
foreign key(studentsid) references students(id),
subjectid bigint,
foreign key(subjectid) references subject(id),
description varchar(255)
);
