论坛数据库设计SQL筑表二

论坛数据库设计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)
)

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)

论坛数据库设计SQL筑表二

热点推荐