create.sql

来自「Jsp做的网络邮件管理系统」· SQL 代码 · 共 69 行

CREATE DATABASE webMail;


USE    webMail;

# -----------------------------------------------------------------------
# user
# -----------------------------------------------------------------------
drop table if exists user;

CREATE TABLE USER (
USERNAME   VARCHAR(20)  NOT NULL,
PASSWORD   VARCHAR(20)  NOT NULL,
REALNAME   VARCHAR(20)  NOT NULL,
PRIMARY KEY (USERNAME) );

# -----------------------------------------------------------------------
# mail_setting
# -----------------------------------------------------------------------
drop table if exists mail_setting;

CREATE TABLE MAIL_SETTING (
USERNAME   VARCHAR(20)     NOT NULL,
POP3_IP    VARCHAR(40)     NOT NULL,
POP3_USER  VARCHAR(40)     NOT NULL,
POP3_PASS  VARCHAR(40)     NOT NULL,
SMTP_IP    VARCHAR(40)     NOT NULL,
SMTP_USER  VARCHAR(40)     NOT NULL,
SMTP_PASS  VARCHAR(40)     NOT NULL,
MAIL_ADDRESS VARCHAR(40)   NOT NULL,
PRIMARY KEY (USERNAME) );

# -----------------------------------------------------------------------
# address
# -----------------------------------------------------------------------
drop table if exists address;

CREATE TABLE ADDRESS (
USERNAME       VARCHAR(20)     NOT NULL,
FRIEND_NAME    VARCHAR(40)     NOT NULL,
FRIEND_MAIL    VARCHAR(40)     NOT NULL,
PRIMARY KEY (USERNAME, FRIEND_NAME) );

# -----------------------------------------------------------------------
# inbox
# -----------------------------------------------------------------------
drop table if exists inbox;

CREATE TABLE INBOX (
USERNAME       VARCHAR(20)     NOT NULL,
SENDER         VARCHAR(100)     NOT NULL,
RECEIVER       VARCHAR(100)     NOT NULL,
SEND_TIME      VARCHAR(20)     NOT NULL,
SUBJECT        VARCHAR(100)     NOT NULL,
CONTENT        TEXT,
READ_FLG       CHAR(1)         NOT NULL );

# -----------------------------------------------------------------------
# sendbox
# -----------------------------------------------------------------------
drop table if exists sendbox;

CREATE TABLE SENDBOX (
USERNAME       VARCHAR(20)     NOT NULL,
RECEIVER       VARCHAR(100)     NOT NULL,
SUBJECT        VARCHAR(100)     NOT NULL,
CONTENT        TEXT,
SAVE_TIME      VARCHAR(20)     NOT NULL );