数据库设计(1)后，按着步骤往下走。2.1，2.2，2.3是步骤。
1. 授予权限
grant connect,resource,dba to ceran
------------------------------------------------------------------------------------------------------------
2. 创建注册用户表
2.1
CREATE TABLE users (
user_Id number(10) NOT NULL,
user_Name varchar2(20),
user_Password varchar2(20),
true_Name varchar2(20),
user_Email varchar2(100) ,
user_Type char(5),
register_Time Date,
constraint PK_users primary key(user_Id)
)
TABLESPACE bbs_data
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
2.2
CREATE SEQUENCE seq_users
START WITH 1
INCREMENT BY 1
CACHE 20
NOCYCLE
NOORDER;
2.3
insert into users(userId)
values(seq_users.nextval);
------------------------------------------------------------------------------------------------------------
3. 创建主版区
3.1
CREATE TABLE discuss(
discuss_Id number(10) not null,
discuss_Name varchar2(50),
disscuss_Order integer,
disscuss_Desc varchar2(200),
constraint PK_discuss primary key(discuss_Id)
)
TABLESPACE bbs_data
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
3.2
CREATE SEQUENCE seq_discuss
START WITH 1
INCREMENT BY 1
CACHE 20
NOCYCLE
NOORDER;
3.3
insert into discuss(discussId)
values(seq_discuss.nextval);
------------------------------------------------------------------------------------------------------------
4. 创建子版块
4.1
CREATE TABLE subBoard(
subBoard_Id number(10) not null,
discuss_Id number(10) not null,
subBoard_Name varchar2(20),
subBoard_Desc varchar2(20),
topic_Number number(10),
reply_Number number(10),
rowsTopic_Per_Page number(10),
rowsReply_Per_Page number(10),
//topic_Point number(10),
//reply_Point number(10),
subBoard_Order_Number number(10),
creat_Time date,
constraint PK_subBoard primary key(subBoard_Id)
)
TABLESPACE bbs_data
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
4.2
CREATE SEQUENCE seq_subBoard
START WITH 1
INCREMENT BY 1
CACHE 20
NOCYCLE
NOORDER;
------------------------------------------------------------------------------------------------------------
5. 创建主题贴
5.1
CREATE TABLE topic(
topic_Id number(10) NOT NULL,
base_Id number(10),
discuss_Id number(10),
user_Id number(10),
topic_Article varchar2(50),
topic_Content varchar2(2000),
publish_Time Date,
reply_Count Integer,
consraint PK_topic primary key(topic_Id),
constraint FK_topic_REFE foreign key(discussId) references users(user_Id),
constraint FK_topic_REFE foreign key(userId) references topic(topic_Id)
)
TABLESPACE bbs_data
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
5.2
CREATE SEQUENCE seq_topic
START WITH 1
INCREMENT BY 1
CACHE 20
NOCYCLE;
5.3
insert into topic(topicId)
values(seq_topic.nextval);
------------------------------------------------------------------------------------------------------------
6. 创建版主
6.1
CREATE TABLE editionMaster(
editionMaster_Id number(10) not null,
user_Id number(10) not null,
subBoard_Id number(10) not null,
accredit_Time date,
constraint PK_editionMaster primary key(editionMaster_Id)
)
TABLESPACE bbs_data
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
6.2
CREATE SEQUENCE seq_editionMaster
START WITH 1
INCREMENT BY 1
CACHE 20
NOCYCLE
NOORDER;
------------------------------------------------------------------------------------------------------------
7 插入数据库代码
insert into discuss(discuss_Id,discuss_Name,discuss_Order,discuss_Desc)
values(seq_discuss.nextval,'数据库技术讨论区',3,'Oracle,Sql Server,My SQL等')
insert into reply(reply_Id,topic_Id,user_Id,subBoard_Id,reply_Content,reply_Time)
values(seq_reply.nextval,253,66,4,' 恩，踩踩。主题确实不错啊！',sysdate)
insert into subBoard(discuss_Id,subBoard_Id,subBoard_Name,subBoard_Desc,topic_Number,reply_Number,rowsTopic_Per_Page,
rowsReply_Per_Page,subBoard_Order_Number,creat_Time) values(41,seq_subBoard.nextval,'My SQL学习区','My SQL学习
区',0,0,8,6,3,sysdate)
insert into topic(topic_Id,base_ID,discuss_ID,user_Id,topic_article,topic_content,publish_time,reply_count)
values(seq_topic.nextval,1,1,21,'java定义','Java，是由Sun Microsystems公司于1995年5月推出的Java程序设计语言和Java平台的总称。用Java实
现的HotJava浏览器（支持Java applet）显示了Java的魅力：跨平台、动态的Web、Internet计算。从此，Java被广泛接受并推动了Web的迅速发展，
常用的浏览器现在均支持Java applet。'
,sysdate,5)
insert into users(user_Id,user_Name,user_Password,true_Name,user_Email,user_Type,register_Time)
values(seq_users.nextval,'wangjie','7762298','wangjie','wangjie@163.com','管理员',sysdate)
insert into editionMaster(editionMaster_Id,user_Id,subBoard_Id,Accredit_Time)
values(seq_editionMaster.nextval,42,10,sysdate)
