oracle.sql

跑步社区网站

drop table memberspace;
drop table memberinfo;
drop table graderecord;
drop table pointrecord;
drop table pointaction;
drop table messagerecord;
drop table blackrecord;
drop table friendrecord;
drop table provinces;

create table graderecord(
	id number primary key,
	minpoint number not null,
	maxpoint number not null,
	gradename varchar2(20) not null,
	iconpath varchar2(50) not null
);
create  table memberinfo (
	id number primary key,
	nickname varchar2(20) unique not null,
	password varchar2(50) not null,
	gender varchar2(10) not null,
	age number not null,
	email varchar2(100) not null,
	provincecity varchar2(20),
	address varchar2(200),
	phone varchar2(50),
	passwordquestion varchar2(200),
	passwordanswer varchar2(200),
	recommender varchar2(20),
	point number default 0,
	registerdate date,
	latestdate date,
	status number default 0,
	isonline number default 0,
	gradeid number,
	constraint info_grade foreign key(gradeid) references graderecord(id)
	);
create table memberspace(
	id number primary key,
	opinion varchar2(200),
	runtime varchar2(20),
	runplace varchar2(20),
	runstar varchar2(50),
	runhabit varchar2(50),
	cellphone varchar2(50),
	icon varchar2(200),
	memberid number,
	constraint space_info foreign key(memberid) references memberinfo(id)
	);
create table pointaction(
	id number primary key,
	actionname varchar2(20),
	point number not null,
	description varchar2(200)
	);
create table pointrecord(
	id number primary key,
	nickname varchar2(20) not null,
	receivedate date not null,
	pointactionid number ,
	constraint record_action foreign key(pointactionid)  references pointaction(id)
	);
create table messagerecord(
	id number primary key,
	sender varchar2(20) not null,
	receiver varchar2(20) not null,
	senddate date  not null,
	title varchar2(100) not null,
	content varchar2(300) not null,
	senderstatus number default 0,
	receiverstatus number default 0,
	status number default 0
	);
create table friendrecord(
	id number primary key,
	selfname varchar2(20) not null,
	friendname varchar2(20) not null
	);
create table blackrecord(
	id number primary key,
	selfname varchar2(20) not null,
	blackname varchar2(20) not null
	);

create table provinces(
	id	number	primary key,
	province varchar2(20) not null
);

create sequence SEQ_COMMON increment by 1 start with 1;

INSERT INTO PointAction
(id,ActionName,Point,Description) Values(1,'REGISTER',50,N'注册会员');
INSERT INTO PointAction
(id,ActionName,Point,Description) Values(2,'RECOMMEND',20,N'推荐会员');
INSERT INTO PointAction
(id,ActionName,Point,Description) Values(3,'LOGIN',3,N'登录');
INSERT INTO PointAction
(id,ActionName,Point,Description) Values(4,'LOGINDESKHELPER',3,N'登录桌面助手');
INSERT INTO PointAction
(id,ActionName,Point,Description) Values(5,'CREATEPERSONALSPACE',25,N'创建个人空间');
INSERT INTO PointAction
(id,ActionName,Point,Description) Values(6,'SENDSTICK',25,N'发帖');
INSERT INTO PointAction
(id,ActionName,Point,Description) Values(7,'REPLYSTICK',25,N'回帖');
INSERT INTO PointAction
(id,ActionName,Point,Description) Values(8,'GOODSTICK',30,N'精华贴');
INSERT INTO PointAction
(id,ActionName,Point,Description) Values(9,'SUPERGOODSTICK',60,N'超级精华贴');
INSERT INTO PointAction
(id,ActionName,Point,Description) Values(10,'BBSMANAGER',200,N'成为版主');
INSERT INTO PointAction
(id,ActionName,Point,Description) Values(11,'REPLYSTICK',5,N'每10个回复帖子楼主+5');
INSERT INTO PointAction
(id,ActionName,Point,Description) Values(12,'EDM',10,N'成为版主');
INSERT INTO PointAction
(id,ActionName,Point,Description) Values(13,'JOINRUNNING',500,N'参加都市跑活动');
INSERT INTO PointAction
(id,ActionName,Point,Description) Values(14,'WINRUNNING1',5000,N'都市跑活动得名次');
INSERT INTO PointAction
(id,ActionName,Point,Description) Values(15,'WINRUNNING2',4000,N'都市跑活动得名次');

INSERT INTO GradeRecord VALUES(1,0,300,'业余爱好者','/images/face1.gif');
INSERT INTO GradeRecord VALUES(2,300,700,'跑步小将','/images/face2.gif');
INSERT INTO GradeRecord VALUES(3,700,1200,'跑步健将','/images/face3.gif');
INSERT INTO GradeRecord VALUES(4,1200,1800,'专业运动员','/images/face4.gif');
INSERT INTO GradeRecord VALUES(5,1800,2500,'顶级运动员','/images/face5.gif');



insert into provinces values(1,'北京');
insert into provinces values(2,'上海');
insert into provinces values(3,'天津');
insert into provinces values(4,'重庆');
insert into provinces values(5,'辽宁');
insert into provinces values(6,'广东');
insert into provinces values(7,'浙江');
insert into provinces values(8,'江苏');
insert into provinces values(9,'山东');
insert into provinces values(10,'四川');
insert into provinces values(11,'黑龙江');
insert into provinces values(12,'湖南');
insert into provinces values(13,'湖北');
insert into provinces values(14,'福建');
insert into provinces values(15,'陕西');
insert into provinces values(16,'河南');
insert into provinces values(17,'安徽');
insert into provinces values(18,'河北');
insert into provinces values(19,'吉林');
insert into provinces values(20,'江西');
insert into provinces values(21,'广西');
insert into provinces values(22,'山西');
insert into provinces values(23,'内蒙古');
insert into provinces values(24,'甘肃');
insert into provinces values(25,'贵州');
insert into provinces values(26,'新疆');
insert into provinces values(27,'云南');
insert into provinces values(28,'宁夏');
insert into provinces values(29,'海南');
insert into provinces values(30,'青海');
insert into provinces values(31,'西藏');
insert into provinces values(32,'港澳台');
insert into provinces values(33,'海外');
insert into provinces values(34,'其他');

commit;