毕业设计(六)---数据库初步设计(数据库所有表,后续有更改)

/*
Navicat MySQL Data Transfer


Source Server         : localhost_3306
Source Server Version : 50516
Source Host           : localhost:3306
Source Database       : myblog


Target Server Type    : MYSQL
Target Server Version : 50516
File Encoding         : 65001


Date: 2013-01-13 00:23:33
*/


SET FOREIGN_KEY_CHECKS=0;


-- ----------------------------
-- Table structure for `album`
-- ----------------------------
DROP TABLE IF EXISTS `album`;
CREATE TABLE `album` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL COMMENT '相册id',
  `userId` int(11) DEFAULT NULL COMMENT '相册主人',
  PRIMARY KEY (`id`),
  KEY `album_ibfk_1` (`userId`),
  CONSTRAINT `album_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of album
-- ----------------------------
INSERT INTO `album` VALUES ('1', '23', '9');


-- ----------------------------
-- Table structure for `article`
-- ----------------------------
DROP TABLE IF EXISTS `article`;
CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL COMMENT '题目',
  `modelId` int(11) DEFAULT NULL COMMENT '所在模块',
  `time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '发表时间',
  `content` varchar(20000) DEFAULT NULL COMMENT '内容',
  `userId` int(11) DEFAULT NULL COMMENT '作者id',
  `readNum` int(15) DEFAULT NULL COMMENT '阅读量',
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),
  KEY `article_ibfk_1` (`modelId`),
  CONSTRAINT `article_ibfk_1` FOREIGN KEY (`modelId`) REFERENCES `model` (`id`) ON DELETE SET NULL,
  CONSTRAINT `article_ibfk_2` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of article
-- ----------------------------


-- ----------------------------
-- Table structure for `attention`
-- ----------------------------
DROP TABLE IF EXISTS `attention`;
CREATE TABLE `attention` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `atten` int(11) DEFAULT NULL COMMENT '关注',
  `attened` int(11) DEFAULT NULL COMMENT '被关注',
  PRIMARY KEY (`id`),
  KEY `atten` (`atten`),
  KEY `attened` (`attened`),
  CONSTRAINT `attention_ibfk_2` FOREIGN KEY (`attened`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `attention_ibfk_1` FOREIGN KEY (`atten`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of attention
-- ----------------------------


-- ----------------------------
-- Table structure for `document`
-- ----------------------------
DROP TABLE IF EXISTS `document`;
CREATE TABLE `document` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL COMMENT '文档名称',
  `address` varchar(255) DEFAULT NULL COMMENT '存放路径',
  `downloadNum` int(11) DEFAULT NULL COMMENT '下载次数',
  `averageScore` int(11) DEFAULT NULL COMMENT '文档平均分',
  `userId` int(11) DEFAULT NULL COMMENT '上传者',
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),
  CONSTRAINT `document_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of document
-- ----------------------------


-- ----------------------------
-- Table structure for `feed`
-- ----------------------------
DROP TABLE IF EXISTS `feed`;
CREATE TABLE `feed` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL COMMENT '动作执行者',
  `userIded` int(11) DEFAULT NULL COMMENT '被通知者',
  `action` int(2) DEFAULT NULL COMMENT '关注1 回复帖子2 回复空间3 发表帖子4（userIded为空）',
  `replyAndMessageId` int(11) DEFAULT NULL COMMENT '留言回复的id',
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),
  KEY `userIded` (`userIded`),
  KEY `replyAndMessageId` (`replyAndMessageId`),
  CONSTRAINT `feed_ibfk_3` FOREIGN KEY (`replyAndMessageId`) REFERENCES `replyandmessage` (`id`) ON DELETE CASCADE,
  CONSTRAINT `feed_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE NO ACTION,
  CONSTRAINT `feed_ibfk_2` FOREIGN KEY (`userIded`) REFERENCES `users` (`id`) ON DELETE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of feed
-- ----------------------------


-- ----------------------------
-- Table structure for `inform`
-- ----------------------------
DROP TABLE IF EXISTS `inform`;
CREATE TABLE `inform` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL COMMENT '通知给某用户',
  `userIded` int(11) DEFAULT NULL COMMENT '发起通知者',
  `type` int(2) DEFAULT NULL COMMENT '关注1 回复帖子2 空间留言3',
  `state` int(2) DEFAULT '0' COMMENT '是否被阅读 1 为阅读',
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),
  KEY `userIded` (`userIded`),
  CONSTRAINT `inform_ibfk_2` FOREIGN KEY (`userIded`) REFERENCES `users` (`id`),
  CONSTRAINT `inform_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of inform
-- ----------------------------


-- ----------------------------
-- Table structure for `model`
-- ----------------------------
DROP TABLE IF EXISTS `model`;
CREATE TABLE `model` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `intro` varchar(255) DEFAULT NULL COMMENT '简介',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of model
-- ----------------------------


-- ----------------------------
-- Table structure for `onlineusers`
-- ----------------------------
DROP TABLE IF EXISTS `onlineusers`;
CREATE TABLE `onlineusers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL COMMENT '当前在线用户的id',
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of onlineusers
-- ----------------------------


-- ----------------------------
-- Table structure for `photo`
-- ----------------------------
DROP TABLE IF EXISTS `photo`;
CREATE TABLE `photo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL COMMENT '图片名称',
  `address` varchar(255) DEFAULT NULL COMMENT '存放路径',
  `userId` int(11) DEFAULT NULL COMMENT '上传者',
  `albumId` int(11) DEFAULT NULL COMMENT '所在相册',
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),
  KEY `albumId` (`albumId`),
  CONSTRAINT `photo_ibfk_2` FOREIGN KEY (`albumId`) REFERENCES `album` (`id`) ON DELETE CASCADE,
  CONSTRAINT `photo_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of photo
-- ----------------------------


-- ----------------------------
-- Table structure for `replyandmessage`
-- ----------------------------
DROP TABLE IF EXISTS `replyandmessage`;
CREATE TABLE `replyandmessage` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL COMMENT '发起留言 回复的id',
  `userOrArticleid` int(11) DEFAULT NULL COMMENT '被留言 被回复的用户id  或者articleid',
  `type` int(2) DEFAULT NULL COMMENT '1为留言 2为回复',
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),
  CONSTRAINT `replyandmessage_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of replyandmessage
-- ----------------------------


-- ----------------------------
-- Table structure for `resource`
-- ----------------------------
DROP TABLE IF EXISTS `resource`;
CREATE TABLE `resource` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL COMMENT '资源名称',
  `address` varchar(255) DEFAULT NULL COMMENT '存放路径',
  `downloadNum` int(11) DEFAULT NULL COMMENT '下载次数',
  `averageScore` int(11) DEFAULT NULL COMMENT '资源平均分',
  `userId` int(11) DEFAULT NULL COMMENT '上传者',
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),
  CONSTRAINT `resource_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of resource
-- ----------------------------


-- ----------------------------
-- Table structure for `users`
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户主键',
  `username` varchar(255) DEFAULT NULL COMMENT '用户帐号',
  `password` varchar(255) DEFAULT NULL COMMENT '用户密码,md5（用户名，密码）',
  `sex` varchar(255) DEFAULT NULL COMMENT '性别',
  `city` varchar(255) DEFAULT NULL COMMENT '所在城市',
  `zonetitle` varchar(255) DEFAULT NULL COMMENT '空间标题',
  `email` varchar(255) DEFAULT NULL COMMENT '邮箱',
  `registerDate` timestamp NULL DEFAULT NULL COMMENT '注册时间',
  `lastLoginTime` timestamp NULL DEFAULT NULL COMMENT '上次登录时间',
  `headImage` varchar(255) DEFAULT NULL COMMENT '头像路径',
  `locked` bit(1) DEFAULT b'0' COMMENT '是否被锁定 1为锁定',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `telephone` varchar(255) DEFAULT NULL COMMENT '电话',
  `zoneVisitedNum` int(11) DEFAULT '0' COMMENT '空间访问量',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;




-- ----------------------------
-- Table structure for `video`
-- ----------------------------
DROP TABLE IF EXISTS `video`;
CREATE TABLE `video` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL COMMENT '视频名称',
  `address` varchar(255) DEFAULT NULL COMMENT '存放路径',
  `downloadNum` int(11) DEFAULT NULL COMMENT '播放次数',
  `userId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),
  CONSTRAINT `video_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------
-- Records of video
-- ----------------------------