论坛数据库设计SQL建表二

数据库设计(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','[e-mail]','管理员',sysdate)

insert into editionMaster(editionMaster_Id,user_Id,subBoard_Id,Accredit_Time)
values(seq_editionMaster.nextval,42,10,sysdate)