Oracle1入门作业20130909

--创建一个名为DAVID的用户，口令为davidpass--
--并授予其连接数据库和创建表对象的权限。--
--同时授予其访问另一用户MARTIN的表TEST的权限.--
create user DAVID identified by davidpass;
grant connect to DAVID;
grant select on MAERIN.TEST to DAVID;


--bbsUsers用户表，存储注册的用户信息，用户注册后才能发贴和回贴--
--bbsTopic主贴表，存储主贴的内容--
--bbsReply跟贴表，存储跟贴的内容--
--bbsSection存储版块的信息--
create sequence bbs_seq
increment by 1
start with 1
maxvalue 999999999
nocycle
cache 10;


create table bbsUsers(
       uuid int primary key not null,
       upwd varchar2(20) default '888888' check(length(upwd)>=6),
       u易做图 char(1) default '1',
       urdate date default sysdate,
       ustate int default 0 not null,
       ujb int default 1 not null,
       ujf number default 20,
       uemail varchar2(30) check(uemail like '%@%')
);


create table bbsSection(
       sid int primary key not null,
       sname varchar2(32) not null,
       smasterID int references bbsUsers(uuid),
       sprofile varchar2(20),
       sclickCount int,
       stopicCount int
);
create table bbsTopic(
       tid int primary key not null,
       tsid int references bbsSection(sid),
       tuid int references bbsUsers(uuid),
       treplyCount int,
       tface int,
       ttopic varchar2(20) not null,
       tcontents varchar2(30) not null,
       ttime date,
       tclickCount int,
       tstate int not null,
       tlastReply date
);
create table bbsReply(
       rid int primary key not null,
       rtid references bbsTopic(tid),
       rsid references bbsSection(sid),
       ruid references bbsUsers(uuid),
       易做图ce int,
       rcontents varchar2(20) check (length(rcontents)>6) not null,
       rtime date,
       rclickCount int
);


select * from bbsusers;
select * from bbsTopic;
select * from bbsReply;
select * from bbsSection;


drop table bbsTopic;
drop table bbsReply;
drop table bbsSection;
drop table bbsusers;