第一步:数据库设计
- MailServer物理模型图
- 表键的清单
名称 |
代码 |
用户昵称 |
nick_name |
邮箱地址 |
mail_address |
用户密码 |
user_password |
我的邮箱 |
mail_address |
好友邮箱 |
friend_address |
好友备注 |
remark_name |
邮件编号 |
mail_id |
接收邮箱 |
mail_receive |
发送时间 |
send_time |
邮件主题 |
mail_subject |
邮件正文 |
mail_content |
发送状态 |
state_send |
读取状态 |
state_read |
正文编码 |
content_encoding |
协议版本 |
protocol_version |
定时发送 |
timing |
邮件编号 |
mail_id |
附件编号 |
adjunct_id |
附件格式 |
adjunct_type |
附件大小 |
adjunct_size |
附件路径 |
adjunct_url |
帐号 |
account |
密码 |
password |
- 数据库脚本(先创建数据库再运行脚本)
/*
Navicat MySQL Data Transfer
Source Server : localhost_3306
Source Server Version : 50720
Source Host : localhost:3306
Source Database : mail_server
Target Server Type : MYSQL
Target Server Version : 50720
File Encoding : 65001
Date: 2018-03-31 13:39:54
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for address_book
-- ----------------------------
DROP TABLE IF EXISTS `address_book`;
CREATE TABLE `address_book` (
`mail_address` varchar(25) NOT NULL COMMENT '我的邮件地址',
`friend_address` varchar(25) NOT NULL COMMENT '好友邮件地址',
`remark_name` varchar(25) DEFAULT NULL COMMENT '好友备注',
PRIMARY KEY (`mail_address`,`friend_address`),
CONSTRAINT `FK_Reference_1` FOREIGN KEY (`mail_address`) REFERENCES `user_entity` (`mail_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='通讯录';
-- ----------------------------
-- Records of address_book
-- ----------------------------
-- ----------------------------
-- Table structure for adjunct
-- ----------------------------
DROP TABLE IF EXISTS `adjunct`;
CREATE TABLE `adjunct` (
`mail_id` varchar(25) NOT NULL COMMENT '邮件ID',
`adjunct_id` varchar(25) NOT NULL COMMENT '附件ID',
`adjunct_type` varchar(5) DEFAULT NULL COMMENT '附件格式',
`adjunct_size` double NOT NULL COMMENT '附件大小',
`adjunct_url` varchar(100) NOT NULL COMMENT '附件地址',
PRIMARY KEY (`adjunct_id`),
KEY `FK_Reference_3` (`mail_id`),
CONSTRAINT `FK_Reference_3` FOREIGN KEY (`mail_id`) REFERENCES `mail_entity` (`mail_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='附件表';
-- ----------------------------
-- Records of adjunct
-- ----------------------------
-- ----------------------------
-- Table structure for admin
-- ----------------------------
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
`account` varchar(25) NOT NULL COMMENT '管理员帐号',
`password` varchar(25) NOT NULL COMMENT '管理员密码',
PRIMARY KEY (`account`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='管理员';
-- ----------------------------
-- Records of admin
-- ----------------------------
-- ----------------------------
-- Table structure for mail_entity
-- ----------------------------
DROP TABLE IF EXISTS `mail_entity`;
CREATE TABLE `mail_entity` (
`mail_id` varchar(25) NOT NULL COMMENT '邮件编号',
`mail_address` varchar(25) NOT NULL COMMENT '发送方邮箱',
`mail_receive` varchar(25) NOT NULL COMMENT '接收方邮箱',
`send_time` datetime NOT NULL COMMENT '发送时间',
`mail_subject` varchar(50) NOT NULL COMMENT '邮件主题',
`mail_content` varchar(2048) NOT NULL COMMENT '邮件正文',
`state_send` varchar(4) NOT NULL COMMENT '发送方邮件状态',
`state_read` varchar(2) DEFAULT NULL COMMENT '接收方邮件状态',
`content_encoding` varchar(10) DEFAULT NULL COMMENT '邮件正文编码',
`protocol_version` varchar(10) DEFAULT NULL COMMENT '邮件协议版本',
`timing` datetime DEFAULT NULL COMMENT '定时发送时间',
PRIMARY KEY (`mail_id`),
KEY `FK_Reference_2` (`mail_address`),
CONSTRAINT `FK_Reference_2` FOREIGN KEY (`mail_address`) REFERENCES `user_entity` (`mail_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='邮件';
-- ----------------------------
-- Records of mail_entity
-- ----------------------------
-- ----------------------------
-- Table structure for user_entity
-- ----------------------------
DROP TABLE IF EXISTS `user_entity`;
CREATE TABLE `user_entity` (
`nick_name` varchar(25) NOT NULL COMMENT '用户昵称',
`mail_address` varchar(25) NOT NULL COMMENT '邮箱地址',
`user_password` varchar(15) NOT NULL COMMENT '用户密码',
PRIMARY KEY (`mail_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户';
-- ----------------------------
-- Records of user_entity
-- ----------------------------
INSERT INTO `user_entity` VALUES ('佚阳', '155146872@belief', '155764