56,677
社区成员
发帖
与我相关
我的任务
分享
---mysql数据库
--权限表
CREATE TABLE `bbs`.`limit` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(10) NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO `limit` VALUES (1,'管理员');
INSERT INTO `limit` VALUES (2,'版主');
INSERT INTO `limit` VALUES (3,'普通用户');
--用户表
CREATE TABLE `bbs`.`users` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`username` VARCHAR(20) NOT NULL,
`password` VARCHAR(20) NOT NULL,
`email` VARCHAR(45) NOT NULL,
`nickname` VARCHAR(10) NOT NULL,
`posttime` DATETIME DEFAULT null,
limitid int NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_general_ci;
alter table users add constraint un_username unique(username);
INSERT INTO `users` VALUES (1,'admin','admin','mengsy831120@163.com','mm','2009-06-18',1);
--版块表
CREATE TABLE `spaces` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(50) NOT NULL,
`describe` varchar(200) NOT NULL,
`userid` int(10) unsigned default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `un_name` (`name`),
KEY `fk02` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--帖子表
CREATE TABLE `bbs`.`article` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(50) NOT NULL,
`content` VARCHAR(225) NOT NULL,
`userid` INTEGER UNSIGNED NOT NULL,
`spaceid` INTEGER UNSIGNED NOT NULL,
`addtime` datetime NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `article`
ADD CONSTRAINT `fk03` FOREIGN KEY (`userid`) REFERENCES `users` (`id`);
ALTER TABLE `article`
ADD CONSTRAINT `fk04` FOREIGN KEY (`spaceid`) REFERENCES `spaces` (`id`);
--帖子回复表
CREATE TABLE `bbs`.`articlereply` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`articleid` INTEGER UNSIGNED NOT NULL,
`userid` INTEGER UNSIGNED NOT NULL,
`content` VARCHAR(500) NOT NULL,
`replaytime` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `fk05` (`articleid`),
KEY `fk06` (`userid`),
CONSTRAINT `fk05` FOREIGN KEY (`articleid`) REFERENCES `article` (`id`),
CONSTRAINT `fk06` FOREIGN KEY (`userid`) REFERENCES `users` (`id`)
)
ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_general_ci;
--公告表
CREATE TABLE `bbs`.`bulletin` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(50) NOT NULL,
`content` VARCHAR(1000) NOT NULL,
`posttime` DATETIME DEFAULT NULL,
PRIMARY KEY (`id`)
)
ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_general_ci;
-- 后面紧跟空格