exp3.sql

-- MySQL Script generated by MySQL Workbench
-- 2017年05月18日 星期四 21时39分13秒
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`user` (
  `iduser` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(64) NOT NULL,
  `sex` CHAR(8) NULL,
  `birthday` DATE NULL,
  `addr` CHAR(255) NULL,
  `passwd` VARCHAR(64) NOT NULL,
  `mail` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`iduser`),
  UNIQUE INDEX `mail_UNIQUE` (`mail` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mydb`.`edu_expe`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`edu_expe` (
  `idedu_expe` INT NOT NULL AUTO_INCREMENT,
  `edu_level` CHAR(64) NULL,
  `edu_start_time` DATE NULL,
  `edu_end_time` DATE NULL,
  `edu_schoolname` CHAR(64) NULL,
  `edu_degree` CHAR(64) NULL,
  `edu_iduser` INT NULL,
  PRIMARY KEY (`idedu_expe`),
  INDEX `iduser_idx` (`edu_iduser` ASC),
  CONSTRAINT `edu_iduser`
    FOREIGN KEY (`edu_iduser`)
    REFERENCES `mydb`.`user` (`iduser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mydb`.`work_expe`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`work_expe` (
  `idwork_expe` INT NOT NULL AUTO_INCREMENT,
  `work_place` VARCHAR(255) NULL,
  `work_start_time` DATE NULL,
  `work_end_time` DATE NULL,
  `work_job` VARCHAR(45) NULL,
  `work_iduser` INT NULL,
  PRIMARY KEY (`idwork_expe`),
  INDEX `iduser_idx` (`work_iduser` ASC),
  CONSTRAINT `work_iduser`
    FOREIGN KEY (`work_iduser`)
    REFERENCES `mydb`.`user` (`iduser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`friend_group`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`friend_group` (
  `idgroup` INT NOT NULL AUTO_INCREMENT,
  `groupname` VARCHAR(255) NULL,
  `iduser` INT NULL,
  PRIMARY KEY (`idgroup`),
  INDEX `iduser_idx` (`iduser` ASC),
  CONSTRAINT `iduser`
    FOREIGN KEY (`iduser`)
    REFERENCES `mydb`.`user` (`iduser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mydb`.`friend`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`friend` (
  `friend_iduser` INT NOT NULL,
  `friend` INT NOT NULL,
  `friend_idgroup` INT NULL,
  PRIMARY KEY (`friend_iduser`, `friend`),
  INDEX `friend_idx` (`friend` ASC),
  INDEX `idgroup_idx` (`friend_idgroup` ASC),
  CONSTRAINT `friend_iduser`
    FOREIGN KEY (`friend_iduser`)
    REFERENCES `mydb`.`user` (`iduser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `friend`
    FOREIGN KEY (`friend`)
    REFERENCES `mydb`.`user` (`iduser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `friend_idgroup`
    FOREIGN KEY (`friend_idgroup`)
    REFERENCES `mydb`.`friend_group` (`idgroup`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mydb`.`diary`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`diary` (
  `iddiary` INT NOT NULL AUTO_INCREMENT,
  `diary_name` VARCHAR(255) NOT NULL,
  `diary_touch` DATETIME NULL,
  `diary_iduser` INT NULL,
  `diary_content` VARCHAR(2048) NOT NULL,
  PRIMARY KEY (`iddiary`),
  INDEX `diary_iduser_idx` (`diary_iduser` ASC),
  CONSTRAINT `diary_iduser`
    FOREIGN KEY (`diary_iduser`)
    REFERENCES `mydb`.`user` (`iduser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`diary_reply`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`diary_reply` (
  `idreply` INT NOT NULL AUTO_INCREMENT,
  `reply_diary` INT NOT NULL,
  `reply_content` VARCHAR(512) NOT NULL,
  `reply_time` TIMESTAMP(6) NULL,
  `reply_user` INT NOT NULL,
  PRIMARY KEY (`idreply`),
  INDEX `reply_user_idx` (`reply_user` ASC),
  INDEX `reply_diary_idx` (`reply_diary` ASC),
  CONSTRAINT `reply_user`
    FOREIGN KEY (`reply_user`)
    REFERENCES `mydb`.`user` (`iduser`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `reply_diary`
    FOREIGN KEY (`reply_diary`)
    REFERENCES `mydb`.`diary` (`iddiary`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mydb`.`message_reply`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`message_reply` (
  `idmessage_reply` INT NOT NULL AUTO_INCREMENT,
  `message_iduser` INT NOT NULL,
  `message_content` VARCHAR(1024) NOT NULL,
  `message_to_who` INT NOT NULL,
  `message_time` TIMESTAMP(6) NULL,
  PRIMARY KEY (`idmessage_reply`),
  INDEX `iduser_idx` (`message_iduser` ASC),
  INDEX `message_to_who_idx` (`message_to_who` ASC),
  CONSTRAINT `message_iduser`
    FOREIGN KEY (`message_iduser`)
    REFERENCES `mydb`.`user` (`iduser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `message_to_who`
    FOREIGN KEY (`message_to_who`)
    REFERENCES `mydb`.`user` (`iduser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mydb`.`share`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`share` (
  `idshare` INT NOT NULL AUTO_INCREMENT,
  `share_diary` INT NOT NULL,
  `share_time` TIMESTAMP(6) NULL,
  `share_comment` VARCHAR(512) NULL,
  `share_iduser` INT NULL,
  PRIMARY KEY (`idshare`),
  INDEX `share_diary_idx` (`share_diary` ASC),
  INDEX `iduser_idx` (`share_iduser` ASC),
  CONSTRAINT `share_diary`
    FOREIGN KEY (`share_diary`)
    REFERENCES `mydb`.`diary` (`iddiary`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `share_iduser`
    FOREIGN KEY (`share_iduser`)
    REFERENCES `mydb`.`user` (`iduser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`mail`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`mail` (
  `idmail` INT NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) NOT NULL,
  `mail_iduser` INT NULL,
  PRIMARY KEY (`idmail`),
  UNIQUE INDEX `email_UNIQUE` (`email` ASC),
  INDEX `mail_iduser_idx` (`mail_iduser` ASC),
  CONSTRAINT `mail_iduser`
    FOREIGN KEY (`mail_iduser`)
    REFERENCES `mydb`.`user` (`iduser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

USE `mydb` ;

-- -----------------------------------------------------
-- Placeholder table for view `mydb`.`view1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`view1` (`id` INT);

-- -----------------------------------------------------
-- Placeholder table for view `mydb`.`view2`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`view2` (`id` INT);

-- -----------------------------------------------------
-- Placeholder table for view `mydb`.`diary_view`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`diary_view` (`iddiary` INT, `diary_name` INT, `diary_touch` INT, `diary_iduser` INT, `diary_content` INT, `iduser` INT, `name` INT);

-- -----------------------------------------------------
-- Placeholder table for view `mydb`.`diary_reply_view`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`diary_reply_view` (`reply_content` INT, `reply_time` INT, `name` INT, `reply_diary` INT);

-- -----------------------------------------------------
-- View `mydb`.`view1`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`view1`;
USE `mydb`;


-- -----------------------------------------------------
-- View `mydb`.`view2`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`view2`;
USE `mydb`;


-- -----------------------------------------------------
-- View `mydb`.`diary_view`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`diary_view`;
USE `mydb`;
CREATE  OR REPLACE VIEW `diary_view` AS 
select iddiary, diary_name, diary_touch, diary_iduser, diary_content, iduser, name 
from diary, user
where diary_iduser = iduser;

-- -----------------------------------------------------
-- View `mydb`.`diary_reply_view`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`diary_reply_view`;
USE `mydb`;
CREATE  OR REPLACE VIEW `diary_reply_view` AS
select reply_content, reply_time, name, reply_diary from diary, diary_reply, user
where diary_reply.reply_diary = diary.iddiary and diary_reply.reply_user = user.iduser;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;