create.sql

一个关于mysql、jsp、servlet的在线选课系统

CREATE DATABASE ch05;

# -----------------------------------------------------------------------
# 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(20)     NOT NULL,
RECEIVER       VARCHAR(20)     NOT NULL,
SEND_TIME      VARCHAR(20)     NOT NULL,
SUBJECT        VARCHAR(40)     NOT NULL,
CONTENT        TEXT );

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

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