2022.02.02 | Notion

E-R 다이어그램
테이블 정보

유저 테이블 - users
구독 테이블 - subscribe
좋아요 테이블 - userlike(유저가 좋아요를 누른 영상)
영상 테이블 - videos
영상 댓글 테이블- comment
월드컵 댓글 테이블 - cupcomment
게시판 테이블-board
게시판 댓글 테이블 - boardcomment
태그 - tag
SQL 쿼리문

-- MySQL Script generated by MySQL Workbench
-- Wed Feb  2 18:15:18 2022
-- 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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

drop database ssafy_dance;
-- -----------------------------------------------------
-- Schema ssafy_dance
-- -----------------------------------------------------

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

-- -----------------------------------------------------
-- Table `ssafy_dance`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ssafy_dance`.`users` (
  `uid` INT NOT NULL AUTO_INCREMENT,
  `id` VARCHAR(45) NOT NULL,
  `password` VARCHAR(16) NOT NULL,
  `email` VARCHAR(30) NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  `profile` VARCHAR(45) NULL,
  `date` DATETIME NULL,
  PRIMARY KEY (`uid`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
  UNIQUE INDEX `email_UNIQUE` (`email` ASC) VISIBLE,
  UNIQUE INDEX `name_UNIQUE` (`name` ASC) VISIBLE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `ssafy_dance`.`videos`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ssafy_dance`.`videos` (
  `vid` INT NOT NULL AUTO_INCREMENT,
  `uid` INT NOT NULL,
  `title` VARCHAR(45) NOT NULL,
  `likecnt` INT NULL DEFAULT 0,
  `views` INT NULL DEFAULT 0,
  `url` VARCHAR(300) NOT NULL,
  `isshow` TINYINT(1) NULL DEFAULT 1,
  `category` INT NOT NULL,
  `thumbnail` VARCHAR(45) NULL,
  `date` DATETIME NULL,
  `comment` TINYINT(1) NULL,
  `score` INT NULL,
  PRIMARY KEY (`vid`),
  INDEX `uid_idx` (`uid` ASC) VISIBLE,
  CONSTRAINT `videos_uid`
    FOREIGN KEY (`uid`)
    REFERENCES `ssafy_dance`.`users` (`uid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `ssafy_dance`.`userlike`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ssafy_dance`.`userlike` (
  `ulid` INT NOT NULL AUTO_INCREMENT,
  `uid` INT NOT NULL,
  `vid` INT NULL,
  `date` DATETIME NULL,
  INDEX `uid_idx` (`uid` ASC) VISIBLE,
  PRIMARY KEY (`ulid`),
  CONSTRAINT `like_vid`
    FOREIGN KEY (`vid`)
    REFERENCES `ssafy_dance`.`videos` (`vid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `like_uid`
    FOREIGN KEY (`uid`)
    REFERENCES `ssafy_dance`.`users` (`uid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `ssafy_dance`.`videocomment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ssafy_dance`.`videocomment` (
  `vcid` INT NOT NULL AUTO_INCREMENT,
  `uid` INT NOT NULL,
  `vid` INT NOT NULL,
  `content` VARCHAR(45) NOT NULL,
  `date` DATETIME NULL,
  PRIMARY KEY (`vcid`),
  INDEX `uid_idx` (`uid` ASC) VISIBLE,
  INDEX `vid_idx` (`vid` ASC) VISIBLE,
  CONSTRAINT `comment_uid`
    FOREIGN KEY (`uid`)
    REFERENCES `ssafy_dance`.`users` (`uid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `comment_vid`
    FOREIGN KEY (`vid`)
    REFERENCES `ssafy_dance`.`videos` (`uid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `ssafy_dance`.`subscribe`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ssafy_dance`.`subscribe` (
  `ssid` INT NOT NULL AUTO_INCREMENT,
  `uid` INT NOT NULL,
  `subid` INT NULL,
  `date` DATETIME NULL,
  INDEX `uid_idx` (`uid` ASC) VISIBLE,
  INDEX `subid_idx` (`subid` ASC) VISIBLE,
  PRIMARY KEY (`ssid`),
  CONSTRAINT `subuid`
    FOREIGN KEY (`uid`)
    REFERENCES `ssafy_dance`.`users` (`uid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `subid`
    FOREIGN KEY (`subid`)
    REFERENCES `ssafy_dance`.`users` (`uid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `ssafy_dance`.`cupcomment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ssafy_dance`.`cupcomment` (
  `vcid` INT NOT NULL AUTO_INCREMENT,
  `uid` INT NOT NULL,
  `vid` INT NOT NULL,
  `content` VARCHAR(45) NOT NULL,
  `date` DATETIME NULL,
  PRIMARY KEY (`vcid`),
  INDEX `uid_idx` (`uid` ASC) VISIBLE,
  INDEX `vid0_idx` (`vid` ASC) VISIBLE,
  CONSTRAINT `cup_uid`
    FOREIGN KEY (`uid`)
    REFERENCES `ssafy_dance`.`users` (`uid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `cup_vid`
    FOREIGN KEY (`vid`)
    REFERENCES `ssafy_dance`.`videos` (`vid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `ssafy_dance`.`board`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ssafy_dance`.`board` (
  `bid` INT NOT NULL AUTO_INCREMENT,
  `category` INT NOT NULL,
  `uid` INT NOT NULL,
  `title` VARCHAR(45) NOT NULL,
  `content` VARCHAR(500) NOT NULL,
  `date` DATETIME NOT NULL,
  `url` VARCHAR(200) NULL,
  PRIMARY KEY (`bid`),
  INDEX `uid_idx` (`uid` ASC) VISIBLE,
  CONSTRAINT `uid`
    FOREIGN KEY (`uid`)
    REFERENCES `ssafy_dance`.`users` (`uid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `ssafy_dance`.`boardcomment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ssafy_dance`.`boardcomment` (
  `vcid` INT NOT NULL AUTO_INCREMENT,
  `uid` INT NOT NULL,
  `bid` INT NOT NULL,
  `content` VARCHAR(200) NOT NULL,
  `date` DATETIME NULL,
  PRIMARY KEY (`vcid`),
  INDEX `uid_idx` (`uid` ASC) VISIBLE,
  INDEX `bid00_idx` (`bid` ASC) VISIBLE,
  CONSTRAINT `board_comment_uid`
    FOREIGN KEY (`uid`)
    REFERENCES `ssafy_dance`.`users` (`uid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `board_comment_bid`
    FOREIGN KEY (`bid`)
    REFERENCES `ssafy_dance`.`board` (`bid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `ssafy_dance`.`tag`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ssafy_dance`.`tag` (
  `tid` INT NOT NULL AUTO_INCREMENT,
  `vid` INT NOT NULL,
  `tname` VARCHAR(45) NULL,
  INDEX `vid_idx` (`vid` ASC) VISIBLE,
  PRIMARY KEY (`tid`),
  CONSTRAINT `tag_vid`
    FOREIGN KEY (`vid`)
    REFERENCES `ssafy_dance`.`videos` (`vid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

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