数据库

create database bbs;
use bbs;

数据表

create table user(
uID INT PRIMARY KEY UNIQUE NOT NULL,
userName VARCHAR(20)  NOT NULL,
userPassword VARCHAR(20)  NOT NULL,
userEmail VARCHAR(10)  NOT NULL,
userBirthday DATE  NOT NULL,
userSex BIT  NOT NULL,
userClass INT  NOT NULL,
userStatement VARCHAR(150)  NOT NULL,
userRegDate TIMESTAMP  NOT NULL,
userPoint INT  NOT NULL
);

create table admin(
adminID INT PRIMARY KEY UNIQUE NOT NULL,
adminName VARCHAR(20)  NOT NULL,
adminPassword VARCHAR(20)  NOT NULL
);

create table section(
sID INT PRIMARY KEY UNIQUE NOT NULL,
sName VARCHAR(20)  NOT NULL,
sMasterID INT  NOT NULL,
sStatement VARCHAR(150)  NOT NULL,
sClickCount INT  NOT NULL,
sTopicCount INT  NOT NULL
);

create table topic(
tID INT PRIMARY KEY UNIQUE NOT NULL,
sID INT  NOT NULL,
uID INT  NOT NULL,
tReplyCount INT  NOT NULL,
tEmotion VARCHAR(20)  NOT NULL,
tTopic VARCHAR(20)  NOT NULL,
tContents TEXT  NOT NULL,
tTime TIMESTAMP  NOT NULL,
tClickCount INT  NOT NULL,
tLastClickT TIMESTAMP  NOT NULL
);

create table reply(
rID INT PRIMARY KEY UNIQUE NOT NULL,
tID INT  NOT NULL,
uID INT  NOT NULL,
rEmotion VARCHAR(20)  NOT NULL,
rToptic VARCHAR(20)  NOT NULL,
rContents TEXT  NOT NULL,
rTime TIMESTAMP  NOT NULL,
rClickCount INT  NOT NULL
);

注：
MySQL不允许在BLOB/TEXT,TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT, LONGTEXT,VARCHAR建索引，因为前面那些列类型都是可变长的，MySQL无法保证列的唯一性，只能在BLOB/TEXT前n个字节上建索引

设计索引

create index index_topic_topic on topic(tTopic);
create index index_topic_time on topic(tTime);
create index index_topic_contents on topic(tContents);
alter table topic add index index_topic_contents (tContents);
create index index_section_name on section(sName);
create index index_reply_time on reply(rTime);
create index index_reply_topic on reply(rTopic);
create index index_reply_id on reply(rID);

设计视图

#展示版块及版块下面的主贴			
create view topic_view			
as select s.sID,s.sName,t.tTopic,t.tContents,t.tTime			
from section s,topic t			
where s.sID=t.sID;

设计触发器

#新增版块后，更新主题数			
delimiter &&			
create trigger section_count after insert			
on section for each row			
begin			
    update section set sTopicCount = sTopicCount+1 where sID=new.sID;			
end			
&&			
delimiter ;			
			
#user表中的uID更新，同时更新reply中的uID			
delimiter &&			
create trigger update_uID after update			
on user for each row			
begin			
    update reply set uID=new.userID;			
end			
&&			
delimiter ;