3주차 과제 Netflix ERD 설계

sua·2022년 8월 6일

Database

Rising Camp 8기 Server

목록 보기

2/9

RDS 생성

aws 로그인
표준 생성
엔진 유형 - MySQL 8.0.28
템플릿 - 프리티어
스토리지 - 20GiB
새 VPC 보안 그룹 생성, 가용 영역은 기본설정 없음으로, 추가 구성에서 데이터페이스 포트 3306인 것 확인
데이터베이스 생성 클릭
생성 완료된 모습
보안그룹 인바운드 규칙 편집
파라미터 그룹 생성하고 파라미터 편집하기

timezone Seoul로 변경
character_set을 utf8mb4로 변경
collation_connection에서 utf8mb4_general_ci로 변경
collation_server에서 utf8mb4_general_ci로 변경

RDS 인스턴스에서 생성한 파라미터그룹으로 변경
ubuntu에서 RDS MySQL 서버 접속 확인

mysql -h <endpoint> -u <master username> -p

13. 파라미터 그룹에서 변경해준 설정 잘 적용됐는지 확인

show variables like 'c%';

→ 잘 적용 됐음을 알 수 있음

ERD 설계

DetailContent 상세 콘텐츠

CREATE TABLE `test`.`detailcontent` (
  `dtContentIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `season` VARCHAR(45) NULL,
  `episode` INT NOT NULL,
  `playtime` TIME NULL,
  `title` VARCHAR(45) NOT NULL,
  `info` VARCHAR(100) NULL,
  `canDownload` INT NULL DEFAULT 0,
  `status` VARCHAR(10) NULL DEFAULT 'ACTIVE',
  `createdAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP(),
  `updatedAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP(),
  `quality` VARCHAR(10) NULL DEFAULT 'HD',
	`videoUrl` TEXT NULL
  PRIMARY KEY (`dtContentIdx`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

Content 콘텐츠
History 시청기록

CREATE TABLE `test`.`history` (
  `historyIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `watchTime` TIME NULL,
  `status` VARCHAR(10) NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`historyIdx`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

User 회원

CREATE TABLE IF NOT EXISTS `user` (
  `userIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(45) NULL,
  `password` TEXT NULL,
  `phone` VARCHAR(20) NULL,
  `status` VARCHAR(10) NULL DEFAULT 'ACTIVE',
  `createdAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP(),
  `updatedAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP(),
  PRIMARY KEY (`userIdx`))
DEFAULT CHARACTER SET = utf8;

Profile 프로필

CREATE TABLE `test`.`profile` (
  `profileIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `image` TEXT NULL,
  `password` VARCHAR(4) NULL,
  `status` VARCHAR(10) NULL DEFAULT 'ACTIVE',
  `createdAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP(),
  `updatedAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP(),
  `isKids` INT NULL DEFAULT 0,
  `isNext` INT NULL DEFAULT 0,
  `isPreview` INT NULL DEFAULT 0,
  PRIMARY KEY (`profileIdx`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

Recommend 추천

CREATE TABLE `test`.`recommend` (
  `recommendIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(45) NULL,
  PRIMARY KEY (`recommendIdx`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

Credit 결제정보

테이블 생성 sql 스크립트

-- 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';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `mydb` ;

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

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

-- -----------------------------------------------------
-- Table `mydb`.`Membership`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Membership` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Membership` (
  `membershipIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `status` VARCHAR(10) NULL,
  `type` VARCHAR(45) NULL,
  `cost` INT NULL,
  PRIMARY KEY (`membershipIdx`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test`.`user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test`.`user` ;

CREATE TABLE IF NOT EXISTS `test`.`user` (
  `userIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(45) NULL DEFAULT NULL,
  `password` TEXT NULL DEFAULT NULL,
  `phone` VARCHAR(20) NULL DEFAULT NULL,
  `status` VARCHAR(10) NULL DEFAULT 'ACTIVE',
  `createdAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `Membership_membershipIdx` BIGINT NOT NULL,
  PRIMARY KEY (`userIdx`),
  INDEX `fk_user_Membership_idx` (`Membership_membershipIdx` ASC) VISIBLE,
  CONSTRAINT `fk_user_Membership`
    FOREIGN KEY (`Membership_membershipIdx`)
    REFERENCES `mydb`.`Membership` (`membershipIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb3;


-- -----------------------------------------------------
-- Table `mydb`.`Card`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Card` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Card` (
  `cardIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(45) NULL,
  `cardInfo` VARCHAR(45) NULL,
  `yymm` VARCHAR(4) NULL,
  `user_userIdx` BIGINT NOT NULL,
  PRIMARY KEY (`cardIdx`),
  INDEX `fk_Card_user1_idx` (`user_userIdx` ASC) VISIBLE,
  CONSTRAINT `fk_Card_user1`
    FOREIGN KEY (`user_userIdx`)
    REFERENCES `test`.`user` (`userIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Credit`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Credit` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Credit` (
  `creditIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `createdAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP(),
  `updatedAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP(),
  `cost` INT NULL,
  `unit` VARCHAR(10) NULL,
  `tax` INT NULL,
  `user_userIdx` BIGINT NOT NULL,
  `Card_cardIdx` BIGINT NOT NULL,
  `info` VARCHAR(45) NULL,
  `period` VARCHAR(45) NULL,
  PRIMARY KEY (`creditIdx`),
  INDEX `fk_Credit_user_idx` (`user_userIdx` ASC) VISIBLE,
  INDEX `fk_Credit_Card1_idx` (`Card_cardIdx` ASC) VISIBLE,
  CONSTRAINT `fk_Credit_user`
    FOREIGN KEY (`user_userIdx`)
    REFERENCES `test`.`user` (`userIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Credit_Card1`
    FOREIGN KEY (`Card_cardIdx`)
    REFERENCES `mydb`.`Card` (`cardIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test`.`content`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test`.`content` ;

CREATE TABLE IF NOT EXISTS `test`.`content` (
  `contentIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `class` VARCHAR(45) NULL DEFAULT NULL,
  `info` VARCHAR(100) NULL DEFAULT NULL,
  `canDownload` INT NULL DEFAULT '0',
  `age` INT NULL DEFAULT NULL,
  `playtime` TIME NULL DEFAULT NULL,
  `previewUrl` TEXT NULL DEFAULT NULL,
  `title` VARCHAR(45) NOT NULL,
  `releasedAt` VARCHAR(4) NULL DEFAULT NULL,
  `quality` VARCHAR(10) NULL DEFAULT 'HD',
  `videoUrl` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`contentIdx`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb3;


-- -----------------------------------------------------
-- Table `test`.`profile`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test`.`profile` ;

CREATE TABLE IF NOT EXISTS `test`.`profile` (
  `profileIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL DEFAULT NULL,
  `image` TEXT NULL DEFAULT NULL,
  `password` VARCHAR(4) NULL DEFAULT NULL,
  `status` VARCHAR(10) NULL DEFAULT 'ACTIVE',
  `createdAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `isKids` INT NULL DEFAULT '0',
  `isNext` INT NULL DEFAULT '0',
  `isPreview` INT NULL DEFAULT '0',
  `user_userIdx` BIGINT NOT NULL,
  PRIMARY KEY (`profileIdx`),
  INDEX `fk_profile_user1_idx` (`user_userIdx` ASC) VISIBLE,
  CONSTRAINT `fk_profile_user1`
    FOREIGN KEY (`user_userIdx`)
    REFERENCES `test`.`user` (`userIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb3;


-- -----------------------------------------------------
-- Table `mydb`.`LikeContent`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`LikeContent` ;

CREATE TABLE IF NOT EXISTS `mydb`.`LikeContent` (
  `likeContentIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `content_contentIdx` BIGINT NOT NULL,
  `profile_profileIdx` BIGINT NOT NULL,
  PRIMARY KEY (`likeContentIdx`),
  INDEX `fk_LikeContent_content1_idx` (`content_contentIdx` ASC) VISIBLE,
  INDEX `fk_LikeContent_profile1_idx` (`profile_profileIdx` ASC) VISIBLE,
  CONSTRAINT `fk_LikeContent_content1`
    FOREIGN KEY (`content_contentIdx`)
    REFERENCES `test`.`content` (`contentIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_LikeContent_profile1`
    FOREIGN KEY (`profile_profileIdx`)
    REFERENCES `test`.`profile` (`profileIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Evaluate`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Evaluate` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Evaluate` (
  `evaluateIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `like` INT NULL,
  `profile_profileIdx` BIGINT NOT NULL,
  `content_contentIdx` BIGINT NOT NULL,
  PRIMARY KEY (`evaluateIdx`),
  INDEX `fk_Evaluate_profile1_idx` (`profile_profileIdx` ASC) VISIBLE,
  INDEX `fk_Evaluate_content1_idx` (`content_contentIdx` ASC) VISIBLE,
  CONSTRAINT `fk_Evaluate_profile1`
    FOREIGN KEY (`profile_profileIdx`)
    REFERENCES `test`.`profile` (`profileIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Evaluate_content1`
    FOREIGN KEY (`content_contentIdx`)
    REFERENCES `test`.`content` (`contentIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Alarm`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Alarm` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Alarm` (
  `alarmIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(45) NULL,
  `profile_profileIdx` BIGINT NOT NULL,
  `content_contentIdx` BIGINT NOT NULL,
  PRIMARY KEY (`alarmIdx`),
  INDEX `fk_Alarm_profile1_idx` (`profile_profileIdx` ASC) VISIBLE,
  INDEX `fk_Alarm_content1_idx` (`content_contentIdx` ASC) VISIBLE,
  CONSTRAINT `fk_Alarm_profile1`
    FOREIGN KEY (`profile_profileIdx`)
    REFERENCES `test`.`profile` (`profileIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Alarm_content1`
    FOREIGN KEY (`content_contentIdx`)
    REFERENCES `test`.`content` (`contentIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`SaveContent`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`SaveContent` ;

CREATE TABLE IF NOT EXISTS `mydb`.`SaveContent` (
  `saveContentIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `profile_profileIdx` BIGINT NOT NULL,
  `content_contentIdx` BIGINT NOT NULL,
  PRIMARY KEY (`saveContentIdx`),
  INDEX `fk_SaveContent_profile1_idx` (`profile_profileIdx` ASC) VISIBLE,
  INDEX `fk_SaveContent_content1_idx` (`content_contentIdx` ASC) VISIBLE,
  CONSTRAINT `fk_SaveContent_profile1`
    FOREIGN KEY (`profile_profileIdx`)
    REFERENCES `test`.`profile` (`profileIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_SaveContent_content1`
    FOREIGN KEY (`content_contentIdx`)
    REFERENCES `test`.`content` (`contentIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Actor`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Actor` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Actor` (
  `actorIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`actorIdx`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Producer`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Producer` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Producer` (
  `producerIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`producerIdx`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`MostSearch`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`MostSearch` ;

CREATE TABLE IF NOT EXISTS `mydb`.`MostSearch` (
  `mostSearchIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `content_contentIdx` BIGINT NOT NULL,
  PRIMARY KEY (`mostSearchIdx`),
  INDEX `fk_MostSearch_content1_idx` (`content_contentIdx` ASC) VISIBLE,
  CONSTRAINT `fk_MostSearch_content1`
    FOREIGN KEY (`content_contentIdx`)
    REFERENCES `test`.`content` (`contentIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`ContentActor`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`ContentActor` ;

CREATE TABLE IF NOT EXISTS `mydb`.`ContentActor` (
  `contentActorIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `Actor_actorIdx` BIGINT NOT NULL,
  `content_contentIdx` BIGINT NOT NULL,
  PRIMARY KEY (`contentActorIdx`),
  INDEX `fk_ContentActor_Actor1_idx` (`Actor_actorIdx` ASC) VISIBLE,
  INDEX `fk_ContentActor_content1_idx` (`content_contentIdx` ASC) VISIBLE,
  CONSTRAINT `fk_ContentActor_Actor1`
    FOREIGN KEY (`Actor_actorIdx`)
    REFERENCES `mydb`.`Actor` (`actorIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ContentActor_content1`
    FOREIGN KEY (`content_contentIdx`)
    REFERENCES `test`.`content` (`contentIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`ContentProducer`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`ContentProducer` ;

CREATE TABLE IF NOT EXISTS `mydb`.`ContentProducer` (
  `contentProducerIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `Producer_producerIdx` BIGINT NOT NULL,
  `content_contentIdx` BIGINT NOT NULL,
  PRIMARY KEY (`contentProducerIdx`),
  INDEX `fk_ContentProducer_Producer1_idx` (`Producer_producerIdx` ASC) VISIBLE,
  INDEX `fk_ContentProducer_content1_idx` (`content_contentIdx` ASC) VISIBLE,
  CONSTRAINT `fk_ContentProducer_Producer1`
    FOREIGN KEY (`Producer_producerIdx`)
    REFERENCES `mydb`.`Producer` (`producerIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ContentProducer_content1`
    FOREIGN KEY (`content_contentIdx`)
    REFERENCES `test`.`content` (`contentIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`VideoGrade`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`VideoGrade` ;

CREATE TABLE IF NOT EXISTS `mydb`.`VideoGrade` (
  `videoGradeIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `category` VARCHAR(45) NULL,
  PRIMARY KEY (`videoGradeIdx`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`ContentVideoGrade`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`ContentVideoGrade` ;

CREATE TABLE IF NOT EXISTS `mydb`.`ContentVideoGrade` (
  `idx` BIGINT NOT NULL AUTO_INCREMENT,
  `content_contentIdx` BIGINT NOT NULL,
  `VideoGrade_videoGradeIdx` BIGINT NOT NULL,
  PRIMARY KEY (`idx`),
  INDEX `fk_ContentVideoGrade_content1_idx` (`content_contentIdx` ASC) VISIBLE,
  INDEX `fk_ContentVideoGrade_VideoGrade1_idx` (`VideoGrade_videoGradeIdx` ASC) VISIBLE,
  CONSTRAINT `fk_ContentVideoGrade_content1`
    FOREIGN KEY (`content_contentIdx`)
    REFERENCES `test`.`content` (`contentIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ContentVideoGrade_VideoGrade1`
    FOREIGN KEY (`VideoGrade_videoGradeIdx`)
    REFERENCES `mydb`.`VideoGrade` (`videoGradeIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`LanguagePack`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`LanguagePack` ;

CREATE TABLE IF NOT EXISTS `mydb`.`LanguagePack` (
  `languagePackIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `nation` VARCHAR(45) NULL,
  PRIMARY KEY (`languagePackIdx`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`SettingLang`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`SettingLang` ;

CREATE TABLE IF NOT EXISTS `mydb`.`SettingLang` (
  `settingLangIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `LanguagePack_languagePackIdx` BIGINT NOT NULL,
  `profile_profileIdx` BIGINT NOT NULL,
  PRIMARY KEY (`settingLangIdx`),
  INDEX `fk_SettingLang_LanguagePack1_idx` (`LanguagePack_languagePackIdx` ASC) VISIBLE,
  INDEX `fk_SettingLang_profile1_idx` (`profile_profileIdx` ASC) VISIBLE,
  CONSTRAINT `fk_SettingLang_LanguagePack1`
    FOREIGN KEY (`LanguagePack_languagePackIdx`)
    REFERENCES `mydb`.`LanguagePack` (`languagePackIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_SettingLang_profile1`
    FOREIGN KEY (`profile_profileIdx`)
    REFERENCES `test`.`profile` (`profileIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`PresentLang`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`PresentLang` ;

CREATE TABLE IF NOT EXISTS `mydb`.`PresentLang` (
  `presentLangIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `LanguagePack_languagePackIdx` BIGINT NOT NULL,
  PRIMARY KEY (`presentLangIdx`),
  INDEX `fk_PresentLang_LanguagePack1_idx` (`LanguagePack_languagePackIdx` ASC) VISIBLE,
  CONSTRAINT `fk_PresentLang_LanguagePack1`
    FOREIGN KEY (`LanguagePack_languagePackIdx`)
    REFERENCES `mydb`.`LanguagePack` (`languagePackIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Subtitle`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Subtitle` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Subtitle` (
  `subtitleIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `SettingLang_settingLangIdx` BIGINT NOT NULL,
  `content_contentIdx` BIGINT NOT NULL,
  PRIMARY KEY (`subtitleIdx`),
  INDEX `fk_Subtitle_SettingLang1_idx` (`SettingLang_settingLangIdx` ASC) VISIBLE,
  INDEX `fk_Subtitle_content1_idx` (`content_contentIdx` ASC) VISIBLE,
  CONSTRAINT `fk_Subtitle_SettingLang1`
    FOREIGN KEY (`SettingLang_settingLangIdx`)
    REFERENCES `mydb`.`SettingLang` (`settingLangIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Subtitle_content1`
    FOREIGN KEY (`content_contentIdx`)
    REFERENCES `test`.`content` (`contentIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Dubbing`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Dubbing` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Dubbing` (
  `dubbingIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `SettingLang_settingLangIdx` BIGINT NOT NULL,
  `content_contentIdx` BIGINT NOT NULL,
  PRIMARY KEY (`dubbingIdx`),
  INDEX `fk_Dubbing_SettingLang1_idx` (`SettingLang_settingLangIdx` ASC) VISIBLE,
  INDEX `fk_Dubbing_content1_idx` (`content_contentIdx` ASC) VISIBLE,
  CONSTRAINT `fk_Dubbing_SettingLang1`
    FOREIGN KEY (`SettingLang_settingLangIdx`)
    REFERENCES `mydb`.`SettingLang` (`settingLangIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Dubbing_content1`
    FOREIGN KEY (`content_contentIdx`)
    REFERENCES `test`.`content` (`contentIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Top10`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Top10` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Top10` (
  `top10Idx` BIGINT NOT NULL AUTO_INCREMENT,
  `content_contentIdx` BIGINT NOT NULL,
  PRIMARY KEY (`top10Idx`),
  INDEX `fk_Top10_content1_idx` (`content_contentIdx` ASC) VISIBLE,
  CONSTRAINT `fk_Top10_content1`
    FOREIGN KEY (`content_contentIdx`)
    REFERENCES `test`.`content` (`contentIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Hot`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Hot` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Hot` (
  `hotIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `content_contentIdx` BIGINT NOT NULL,
  PRIMARY KEY (`hotIdx`),
  INDEX `fk_Hot_content1_idx` (`content_contentIdx` ASC) VISIBLE,
  CONSTRAINT `fk_Hot_content1`
    FOREIGN KEY (`content_contentIdx`)
    REFERENCES `test`.`content` (`contentIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`New`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`New` ;

CREATE TABLE IF NOT EXISTS `mydb`.`New` (
  `newIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `openDate` VARCHAR(45) NULL,
  `content_contentIdx` BIGINT NOT NULL,
  PRIMARY KEY (`newIdx`),
  INDEX `fk_New_content1_idx` (`content_contentIdx` ASC) VISIBLE,
  CONSTRAINT `fk_New_content1`
    FOREIGN KEY (`content_contentIdx`)
    REFERENCES `test`.`content` (`contentIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Category`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Category` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Category` (
  `categoryIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(45) NULL,
  PRIMARY KEY (`categoryIdx`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`ContentCategory`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`ContentCategory` ;

CREATE TABLE IF NOT EXISTS `mydb`.`ContentCategory` (
  `idx` BIGINT NOT NULL AUTO_INCREMENT,
  `content_contentIdx` BIGINT NOT NULL,
  `Category_categoryIdx` BIGINT NOT NULL,
  PRIMARY KEY (`idx`),
  INDEX `fk_ContentCategory_content1_idx` (`content_contentIdx` ASC) VISIBLE,
  INDEX `fk_ContentCategory_Category1_idx` (`Category_categoryIdx` ASC) VISIBLE,
  CONSTRAINT `fk_ContentCategory_content1`
    FOREIGN KEY (`content_contentIdx`)
    REFERENCES `test`.`content` (`contentIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ContentCategory_Category1`
    FOREIGN KEY (`Category_categoryIdx`)
    REFERENCES `mydb`.`Category` (`categoryIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

USE `test` ;

-- -----------------------------------------------------
-- Table `test`.`detailcontent`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test`.`detailcontent` ;

CREATE TABLE IF NOT EXISTS `test`.`detailcontent` (
  `dtContentIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `season` VARCHAR(45) NULL DEFAULT NULL,
  `episode` INT NOT NULL,
  `playtime` TIME NULL DEFAULT NULL,
  `title` VARCHAR(45) NOT NULL,
  `info` VARCHAR(100) NULL DEFAULT NULL,
  `canDownload` INT NULL DEFAULT '0',
  `status` VARCHAR(10) NULL DEFAULT 'ACTIVE',
  `createdAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `quality` VARCHAR(10) NULL DEFAULT 'HD',
  `videoUrl` TEXT NULL DEFAULT NULL,
  `content_contentIdx` BIGINT NOT NULL,
  PRIMARY KEY (`dtContentIdx`),
  INDEX `fk_detailcontent_content1_idx` (`content_contentIdx` ASC) VISIBLE,
  CONSTRAINT `fk_detailcontent_content1`
    FOREIGN KEY (`content_contentIdx`)
    REFERENCES `test`.`content` (`contentIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb3;


-- -----------------------------------------------------
-- Table `test`.`history`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test`.`history` ;

CREATE TABLE IF NOT EXISTS `test`.`history` (
  `historyIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `watchTime` TIME NULL DEFAULT NULL,
  `status` VARCHAR(10) NULL DEFAULT 'ACTIVE',
  `profile_profileIdx` BIGINT NOT NULL,
  `content_contentIdx` BIGINT NOT NULL,
  PRIMARY KEY (`historyIdx`),
  INDEX `fk_history_profile1_idx` (`profile_profileIdx` ASC) VISIBLE,
  INDEX `fk_history_content1_idx` (`content_contentIdx` ASC) VISIBLE,
  CONSTRAINT `fk_history_profile1`
    FOREIGN KEY (`profile_profileIdx`)
    REFERENCES `test`.`profile` (`profileIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_history_content1`
    FOREIGN KEY (`content_contentIdx`)
    REFERENCES `test`.`content` (`contentIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb3;


-- -----------------------------------------------------
-- Table `test`.`recommend`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test`.`recommend` ;

CREATE TABLE IF NOT EXISTS `test`.`recommend` (
  `recommendIdx` BIGINT NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(45) NULL DEFAULT NULL,
  `content_contentIdx` BIGINT NOT NULL,
  PRIMARY KEY (`recommendIdx`),
  INDEX `fk_recommend_content1_idx` (`content_contentIdx` ASC) VISIBLE,
  CONSTRAINT `fk_recommend_content1`
    FOREIGN KEY (`content_contentIdx`)
    REFERENCES `test`.`content` (`contentIdx`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb3;


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

최종 ERD 설계

엑셀에 화면 캡쳐 & 필요한 컬럼 적기

영화 보기 화면
시리즈 보기 화면
내가 찜한 콘텐츠
저장한 콘텐츠 목록
Top 10 시리즈
카테고리별 검색

한방쿼리 작성 후 결과 화면 캡처

더미 데이터 삽입하기

Content 테이블

insert into Content(class, info, canDownload, age, previewUrl, title, releasedAt, quality, thumbnail)
values ('시리즈', '탄생과 죽음이 공존하는, 인생의 축소판이라고 불리는 병원에서 특별한 하루하루를 살아가는 사람들의 이야기를 그린 드라마', 1, 15, 'https://www.netflix.com/kr/title/81239224', '슬기로운 의사생활', '2021', 'HD',
'https://www.yna.co.kr/view/AKR20210810061200005');

insert into Content(class, info, canDownload, age, previewUrl, title, releasedAt, quality, thumbnail)
values ('시리즈', '천재적인 두뇌의 소유자 우영우. 대형 로펌의 신입 변호사이자 자폐 스펙트럼 장애를 가진 여성으로서 법정 안팎에서 다양한 난관을 헤쳐간다.', 1, 15, 'https://www.youtube.com/watch?v=5AXXoEvGrv4', '이상한 변호사 우영우', '2022', 'HD',
'https://w.namu.la/s/31f67b04e8b6761effb26f1886877d9092342f963889c83822f0e0cfb5811d76d4c72d82042a05b9113971a82466b465c44759feac029af6c1887d463c130930785cd2d5513eb14e44bb590531b8e6f1f89c7c301a28ae2f8a88d787438586f2f6cc1aa9066c08827811fc2ca863c3d4');

insert into Content(class, info, canDownload, age, previewUrl, title, releasedAt, quality, thumbnail)
values ('시리즈', '초능력 탓에 인생이 피곤했던 사이키 쿠스오. 그래서 아예 초능력을 숨기고 살기로 했다. 평온한 인생도 그렇게 나쁘지 않은걸. 고등학교 가서 그 녀석들을 만나기 전까지는!', 1, 15, 'https://www.youtube.com/watch?v=87IkhpL1jVo', '사이키 쿠스오의 재난', '2018', 'HD',
'https://postfiles.pstatic.net/MjAyMTA0MTJfNDIg/MDAxNjE4MjA0MDM0Njkx.0wFGVYbfK5bdHRbHyItVjZoceZpRHcfeSA9spnxs4eAg.zeJejmcFxxF6em3sR6VXjGMJQtDT6_rDkeALQrfWpZkg.JPEG.everever0305/IMG_7774.JPG?type=w966');

insert into Content(class, info, canDownload, age, previewUrl, title, releasedAt, quality, thumbnail)
values ('시리즈', '불사신 다쓰로 불리며 어둠의 세계에서 숱한 전설을 남기고 홀연히 사라졌던 야쿠자. 이 남자가 하드보일드 전업주부로 다시 돌아왔다.', 1, 18, 'https://www.netflix.com/kr/title/81261669', '극주부도', '2021', 'HD',
'https://mblogthumb-phinf.pstatic.net/MjAyMTA0MTFfMTc2/MDAxNjE4MTUwNjE1OTQx.HkIwqpRUy9Uk03MEg_OE7bSwcItDIYa5gZJ5Wr6jFgcg.Hg2ajNtR9QfQCTc5gtxCUc2qwMdxiPNIKvinoKEC9HQg.JPEG.hwana7696/%EB%8B%A4%EC%9A%B4%EB%A1%9C%EB%93%9C.jpeg%EF%BC%8D71.jpg?type=w800');

insert into Content(class, info, canDownload, age, previewUrl, title, releasedAt, quality, thumbnail)
values ('시리즈', '태생부터 잘못된 운명이었다. 명문가 장씨 중 유일하게 술법을 못쓴다는 이유로 집안의 약자이자 치부가 되었다. 이제, 잃어버린 능력을 되찾으려 한다.', 1, 15, 'https://www.netflix.com/kr/title/81517188', '환혼', '2022', 'HD',
'https://img.hankyung.com/photo/202207/BF.30608992.1.jpg');

insert into Content(class, info, canDownload, age, previewUrl, title, releasedAt, quality, thumbnail)
values ('시리즈', '평범한 고등학생 소년이 저주와 주술의 세계에 휘말린다. 수백만부가 판매된 아쿠타미 게게의 동명 인기 만화를 원작으로 한 애니메이션 시리즈', 1, 15, 'https://namu.wiki/w/%EC%A3%BC%EC%88%A0%ED%9A%8C%EC%A0%84(%EC%95%A0%EB%8B%88%EB%A9%94%EC%9D%B4%EC%85%98%201%EA%B8%B0)', '주술회전', '2020', 'HD',
'https://an2-img.amz.wtchn.net/image/v2/PHUfGPYtliuSNLkhVgmE_g.jpg?jwt=ZXlKaGJHY2lPaUpJVXpJMU5pSjkuZXlKdmNIUnpJanBiSW1SZk56STVlREV3T0RCeE9EQWlYU3dpY0NJNklpOTJNaTl6ZEc5eVpTOXBiV0ZuWlM4eE5qTTFNakk0TURFNU5qVTRPVE01TkRNNUluMC43c2EweHBDbmFLMGxhWXNfajlGbnR3amN2UVJGQzhDYnpwb1lLMmlVWXdz');

insert into Content(class, info, canDownload, age, previewUrl, title, releasedAt, quality, thumbnail)
values ('시리즈',  '유서 깊은 죠스타 가문에 얽힌 기묘한 이야기를 아는가. 각기 다른 세상에서 애칭 죠죠를 공유하며 살아가는 주인공들.', 1, 18, 'https://www.netflix.com/kr/title/80179831', '죠죠의 기묘한 모험', '2021', 'HD',
'https://mblogthumb-phinf.pstatic.net/20160503_88/sshk777_1462273738360ECnqY_JPEG/Jojo_egypt_key.png?type=w800');

insert into Content(class, info, canDownload, age, previewUrl, title, releasedAt, quality, thumbnail)
values ('시리즈', '집도 절도 없던 노숙 여고생에게 신사가 생겼다. 얼떨결에 신사를 물려받고 토지신이 된 나나미.', 1, 15, 'https://laftel.net/item/23078', '오늘부터 신령님', '2015', 'HD',
'https://mblogthumb-phinf.pstatic.net/MjAxODEwMDRfMTM5/MDAxNTM4NTg2ODczNjI1.e15iVuNM0SKcdwcMAyB-vaF8Ik4AqDTjjpHlWgy4XUAg.gh9fjhL5GtWGV3TYUhp-BCzjmXKcKXBh6vYu2WJIuxog.JPEG.bola6507/downloadfile-2.jpg?type=w800');

insert into Content(class, info, canDownload, age, previewUrl, title, releasedAt, quality, thumbnail)
values ('시리즈', '돈 vs 도덕성. 생존 vs 인간성. 이 잔혹한 게임의 참가자들은 둘 중 하나를 택해야 한다.', 1, 18, 'https://www.netflix.com/kr/title/81040344', '오징어 게임', '2021', 'HD',
'https://img.khan.co.kr/news/2021/09/30/l_2021093001003585000310901.jpg');

insert into Content(class, info, canDownload, age, previewUrl, title, releasedAt, quality, thumbnail)
values ('시리즈', '우리는 언제부터, 무슨 이유로 삶의 쳇바퀴에 갇히게 됐을까? 정확히 기억나지는 않는다. 하지만 이렇게 끝낼 순 없어. 뭔가 해야만 한다.', 1, 15, 'https://www.netflix.com/kr/title/81568411', '나의 해방일지', '2022', 'HD',
'https://www.kukinews.com/data/kuk/image/2022/05/30/kuk202205300066.680x.0.jpg');

insert into Content(class, info, canDownload, age, previewUrl, title, releasedAt, quality, thumbnail)
values ('시리즈', '고등학교 시절 촬영한 다큐멘터리가 역주행하며 화제가 됐다. 서로 안 좋게 헤어졌건만, 어쩔 수 없이 다시 카메라 앞에 선 두 사람.', 1, 15, 'https://www.netflix.com/kr/title/81486372', '그 해 우리는', '2021', 'HD',
'https://upload.wikimedia.org/wikipedia/ko/thumb/3/3b/%EA%B7%B8_%ED%95%B4_%EC%9A%B0%EB%A6%AC%EB%8A%94.jpeg/250px-%EA%B7%B8_%ED%95%B4_%EC%9A%B0%EB%A6%AC%EB%8A%94.jpeg');

insert into Content(class, info, canDownload, age, previewUrl, title, releasedAt, quality, thumbnail)
values ('시리즈', '서울 도심에 수상한 호텔이 있다. 천년 영업에 숙박한 사람이 없다. 왜? 산 사람은 안 받으니까.', 1, 14, 'https://namu.wiki/w/%ED%98%B8%ED%85%94%20%EB%8D%B8%EB%A3%A8%EB%82%98', '호텔 델루나', '2019', 'HD',
'https://newsimg.sedaily.com/2019/07/02/1VLJB1IL7Y_1.jpg');

insert into Content(class, info, canDownload, age, playtime, previewUrl, title, releasedAt, quality, thumbnail, updatedAt)
values ('영화', '도시 한복판에 독가스가 유출됐다! 아수라장 속에서 대학 시절 산악부 경험을 살려 탈출을 돕는 두 청년. 짠 내 나는 지질한 청춘은 잠시 잊어라.', 1, 12, '01:43:00.0', 'https://namu.wiki/w/%EC%97%91%EC%8B%9C%ED%8A%B8', '엑시트', '2019', 'HD',
'http://t1.daumcdn.net/movie/840094b1d3714d98a3f1841cac3b82d81563931141135', '2022-08-14 00:00:00');

insert into Content(class, info, canDownload, age, playtime, previewUrl, title, releasedAt, quality, thumbnail, updatedAt)
values ('영화', '도시 한복판에 독가스가 유출됐다! 아수라장 속에서 대학 시절 산악부 경험을 살려 탈출을 돕는 두 청년. 짠 내 나는 지질한 청춘은 잠시 잊어라.', 1, 12, '01:43:00.0', 'https://namu.wiki/w/%EC%97%91%EC%8B%9C%ED%8A%B8', '엑시트', '2019', 'HD',
'http://t1.daumcdn.net/movie/840094b1d3714d98a3f1841cac3b82d81563931141135', '2022-08-14 00:00:00');

DetailContent 테이블

insert into DetailContent(season, episode, playtime, title, info, canDownload, quality, videoUrl, contentIdx)
values ('시즌 1', 1, '00:22:00.0', '1.사랑이란?', '오늘도 카페에 모여 앉은 친구들. 이혼으로 실의에 빠진 로스를 달래느라 다들 여념이 없는 이때, 웨딩드레스를 입은 여성이 카페로 달려든다.', 1, 'HD',
'https://namu.wiki/w/%ED%94%84%EB%A0%8C%EC%A6%88', 13);

insert into DetailContent(season, episode, playtime, title, info, canDownload, quality, videoUrl, contentIdx)
values ('시즌 1', 2, '00:22:00.0', '2.홀로서기', '헤어진 레즈비언 아내 캐럴이 내 아이를 가졌단다. 당혹감을 감추지 못하는 로스. 이토록 특별한 상황, 들어나 봤니?', 1, 'HD',
'https://namu.wiki/w/%ED%94%84%EB%A0%8C%EC%A6%88', 13);

insert into DetailContent(season, episode, playtime, title, info, canDownload, quality, videoUrl, contentIdx)
values ('시즌 1', 3, '00:22:00.0', '3.모니카의 남자친구', '음료수 캔에서 나온 엄지손가락. 계좌에 들어온 7천 달러. 대체 피비한테 무슨 일이 생긴 걸까.', 1, 'HD',
'https://namu.wiki/w/%ED%94%84%EB%A0%8C%EC%A6%88', 13);

insert into DetailContent(season, episode, playtime, title, info, canDownload, quality, videoUrl, contentIdx)
values ('시즌 1', 4, '00:22:00.0', '4.애인 만들기', '캐럴과의 추억에 잠겨 한없이 우울해하는 로스. 옛 친구들을 만나고 온 후로 잔뜩 의기소침해진 레이철.', 1, 'HD',
'https://namu.wiki/w/%ED%94%84%EB%A0%8C%EC%A6%88', 13);

Actor 테이블

insert into Actor(name)
values ('제니퍼 애니스턴');

insert into Actor(name)
values ('코트니 콕스');

insert into Actor(name)
values ('리사 쿠드로');

insert into Actor(name)
values ('맷 르블랑');

insert into Actor(name)
values ('매튜 페리');

insert into Actor(name)
values ('데이비드 슈위머');

insert into Actor(name)
values ('조정석');

insert into Actor(name)
values ('임윤아');

insert into Actor(name)
values ('고두심');

insert into Actor(name)
values ('박인환');

insert into Actor(name)
values ('김지영');

insert into Actor(name)
values ('강기영');

Producer 테이블

insert into Producer(name)
values ('데이비드 크레인');

insert into Producer(name)
values ('마타 카우프먼');

insert into Producer(name)
values ('이상근');

Category 테이블

insert into Category(type)
values ('힐링');

insert into Category(type)
values ('향수 자극');

insert into Category(type)
values ('드라마 장르');

insert into Category(type)
values ('멀티캐스팅');

insert into Category(type)
values ('감명을 주는');

insert into Category(type)
values ('진심 어린');

insert into Category(type)
values ('익살스러운');

insert into Category(type)
values ('색다른');

insert into Category(type)
values ('위트 있는');

insert into Category(type)
values ('유쾌 발랄');

insert into Category(type)
values ('기분 좋아지는');

insert into Category(type)
values ('거부할 수 없는 이끌림');

insert into Category(type)
values ('로맨틱');

insert into Category(type)
values ('어두운');

insert into Category(type)
values ('흥미진진');

insert into Category(type)
values ('설렘주의');

insert into Category(type)
values ('긴장감 넘치는');

insert into Category(type)
values ('잔잔한');

insert into Category(type)
values ('판타지 아니메');

insert into Category(type)
values ('한국 드라마');

insert into Category(type)
values ('판타지 시리즈');

insert into Category(type)
values ('귀신');

ContentCategory 테이블

insert into ContentCategory(contentIdx, categoryIdx)
values (1, 1);

insert into ContentCategory(contentIdx, categoryIdx)
values (1, 2);

insert into ContentCategory(contentIdx, categoryIdx)
values (1, 3);

insert into ContentCategory(contentIdx, categoryIdx)
values (1, 4);

insert into ContentCategory(contentIdx, categoryIdx)
values (2, 1);

insert into ContentCategory(contentIdx, categoryIdx)
values (2, 5);

insert into ContentCategory(contentIdx, categoryIdx)
values (2, 6);

insert into ContentCategory(contentIdx, categoryIdx)
values (3, 7);

insert into ContentCategory(contentIdx, categoryIdx)
values (3, 8);

insert into ContentCategory(contentIdx, categoryIdx)
values (3, 9);

insert into ContentCategory(contentIdx, categoryIdx)
values (4, 7);

insert into ContentCategory(contentIdx, categoryIdx)
values (4, 10);

insert into ContentCategory(contentIdx, categoryIdx)
values (4, 11);

insert into ContentCategory(contentIdx, categoryIdx)
values (5, 12);

insert into ContentCategory(contentIdx, categoryIdx)
values (5, 13);

insert into ContentCategory(contentIdx, categoryIdx)
values (6, 14);

insert into ContentCategory(contentIdx, categoryIdx)
values (6, 15);

insert into ContentCategory(contentIdx, categoryIdx)
values (6, 19);

insert into ContentCategory(contentIdx, categoryIdx)
values (7, 15);

insert into ContentCategory(contentIdx, categoryIdx)
values (7, 19);

insert into ContentCategory(contentIdx, categoryIdx)
values (8, 16);

insert into ContentCategory(contentIdx, categoryIdx)
values (8, 13);

insert into ContentCategory(contentIdx, categoryIdx)
values (8, 19);

insert into ContentCategory(contentIdx, categoryIdx)
values (9, 17);

insert into ContentCategory(contentIdx, categoryIdx)
values (9, 20);

insert into ContentCategory(contentIdx, categoryIdx)
values (10, 1);

insert into ContentCategory(contentIdx, categoryIdx)
values (10, 18);

insert into ContentCategory(contentIdx, categoryIdx)
values (10, 20);

insert into ContentCategory(contentIdx, categoryIdx)
values (11, 1);

insert into ContentCategory(contentIdx, categoryIdx)
values (11, 12);

insert into ContentCategory(contentIdx, categoryIdx)
values (11, 13);

insert into ContentCategory(contentIdx, categoryIdx)
values (11, 20);

insert into ContentCategory(contentIdx, categoryIdx)
values (14, 13);

insert into ContentCategory(contentIdx, categoryIdx)
values (14, 16);

insert into ContentCategory(contentIdx, categoryIdx)
values (14, 10);

insert into ContentCategory(contentIdx, categoryIdx)
values (14, 22);

insert into ContentCategory(contentIdx, categoryIdx)
values (14, 21);

Membership 테이블

insert into Membership(type, cost)
values ('Basic', 9500);

insert into Membership(type, cost)
values ('Standard', 12000);

insert into Membership(type, cost)
values ('Premium', 14500);

User 테이블

insert into User(email, password, phone, membershipIdx)
values ('test@gmail.com', '1234', '01012341234', 3);

Profile 테이블

insert into Profile(name, image, password, userIdx)
values ('sua', 'https://ih0.redbubble.net/image.618427277.3222/flat,1000x1000,075,f.u2.jpg', '1234', 1);

LikeContent 테이블

insert into LikeContent(profileIdx, contentIdx)
values (1, 2);

insert into LikeContent(profileIdx, contentIdx)
values (1, 3);

insert into LikeContent(profileIdx, contentIdx)
values (1, 4);

Top10 테이블

insert into Top10(contentIdx)
values (2);

insert into Top10(contentIdx)
values (5);

ContentActor 테이블

INSERT INTO ContentActor(actorIdx, contentIdx)
VALUES (7, 12);
INSERT INTO ContentActor(actorIdx, contentIdx)
VALUES (8, 12);
INSERT INTO ContentActor(actorIdx, contentIdx)
VALUES (9, 12);
INSERT INTO ContentActor(actorIdx, contentIdx)
VALUES (10, 12);
INSERT INTO ContentActor(actorIdx, contentIdx)
VALUES (11, 12);
INSERT INTO ContentActor(actorIdx, contentIdx)
VALUES (12, 12);
INSERT INTO ContentActor(actorIdx, contentIdx)
VALUES (1, 13);
INSERT INTO ContentActor(actorIdx, contentIdx)
VALUES (2, 13);
INSERT INTO ContentActor(actorIdx, contentIdx)
VALUES (3, 13);
INSERT INTO ContentActor(actorIdx, contentIdx)
VALUES (4, 13);
INSERT INTO ContentActor(actorIdx, contentIdx)
VALUES (5, 13);
INSERT INTO ContentActor(actorIdx, contentIdx)
VALUES (6, 13);

ContentProducer 테이블

INSERT INTO ContentProducer(producerIdx, contentIdx)
VALUES (3, 12);
INSERT INTO ContentProducer(producerIdx, contentIdx)
VALUES (1, 13);
INSERT INTO ContentProducer(producerIdx, contentIdx)
VALUES (2, 13);

SaveContent 테이블

INSERT INTO SaveContent(profileIdx, contentIdx)
VALUES (1, 2);

한방 쿼리 작성 → 결과 화면 캡쳐

영화 상세 페이지

SELECT c.previewUrl, c.title, c.releasedAt, c.age, c.playtime, c.quality, CONCAT('넷플릭스에서 시청할 수 있는 마지막 날: ', MONTH(c.updatedAt), '월 ', DAY(c.updatedAt), '일')  '마감일',
	CASE WHEN (SELECT sc.contentIdx FROM SaveContent sc WHERE sc.contentIdx = c.contentIdx AND sc.profileIdx = 1)  IS NOT NULL
		THEN '저장함' ELSE '저장 안함' END AS '저장여부', c.info, a.name, p.name,
    CASE WHEN(SELECT lc.contentIdx FROM LikeContent lc WHERE lc.contentIdx = c.contentIdx AND lc.profileIdx = 1) IS NOT NULL
		THEN '찜함' ELSE '찜 안함' END AS '찜 여부',
    CASE WHEN (SELECT e.like FROM Evaluate e WHERE e.contentIdx = c.contentIdx AND e.profileIdx = 1) IS NOT NULL
		THEN '평가함' ELSE '평가 안함' END AS '평가여부'
FROM Content c JOIN ContentActor ca
ON c.contentIdx = ca.contentIdx
JOIN Actor a
ON a.actorIdx = ca.actorIdx
JOIN ContentProducer cp ON c.contentIdx = cp.contentIdx
JOIN Producer p ON p.producerIdx = cp.producerIdx
WHERE c.title = '엑시트';

시리즈 상세 페이지

SELECT DISTINCT c.previewUrl, c.title, c.releasedAt, c.age, c.quality,
	CASE WHEN (SELECT sc.contentIdx FROM SaveContent sc WHERE sc.contentIdx = c.contentIdx AND sc.profileIdx = 1)  IS NOT NULL
		THEN '저장함' ELSE '저장 안함' END AS '저장여부', c.info, a.name, p.name,
    CASE WHEN(SELECT lc.contentIdx FROM LikeContent lc WHERE lc.contentIdx = c.contentIdx AND lc.profileIdx = 1) IS NOT NULL
		THEN '찜함' ELSE '찜 안함' END AS '찜 여부',
    CASE WHEN (SELECT e.like FROM Evaluate e WHERE e.contentIdx = c.contentIdx AND e.profileIdx = 1) IS NOT NULL
		THEN '평가함' ELSE '평가 안함' END AS '평가여부',
	dc.season, dc.videoUrl, dc.title, dc.playtime, dc.info
FROM Content c JOIN ContentActor ca
ON c.contentIdx = ca.contentIdx
JOIN Actor a
ON a.actorIdx = ca.actorIdx
JOIN ContentProducer cp ON c.contentIdx = cp.contentIdx
JOIN Producer p ON p.producerIdx = cp.producerIdx
JOIN DetailContent dc
ON dc.contentIDx = c.contentIdx
WHERE c.title = '프렌즈';

내가 찜한 콘텐츠

SELECT c.title, c.thumbnail
FROM Content c JOIN LikeContent lc ON c.contentIdx = lc.contentIdx
JOIN Profile p ON p.profileIdx = lc.profileIdx
WHERE p.profileIdx = 1;

저장한 콘텐츠 목록

SELECT c.thumbnail, c.title, c.age, CONCAT('에피소드 ', COUNT(c.title), '개') '개수' 
FROM Content c JOIN SaveContent sc ON c.contentIdx = sc.contentIdx
JOIN Profile p ON p.profileIdx = sc.profileIdx
WHERE p.profileIdx = 1
GROUP BY c.thumbnail, c.title, c.age;

Top10 시리즈

SELECT DISTINCT c.previewUrl, c.age,
    CASE WHEN(SELECT lc.contentIdx FROM LikeContent lc WHERE lc.contentIdx = c.contentIdx AND lc.profileIdx = 1) IS NOT NULL
		THEN '찜함' ELSE '찜 안함' END AS '찜 여부', c.title, c.info, ca.type
FROM Content c JOIN ContentCategory cc 
ON c.contentIdx = cc.contentIdx
JOIN Category ca
ON cc.categoryIdx = ca.categoryIdx
Join Top10
ON Top10.contentIdx = c.contentIdx;

카테고리별 검색

SELECT c.thumbnail, c.title, ca.type,
	CASE WHEN(SELECT lc.contentIdx FROM LikeContent lc WHERE lc.contentIdx = c.contentIdx AND lc.profileIdx = 1) IS NOT NULL
		THEN '찜함' ELSE '찜 안함' END AS '찜 여부'
FROM Content c JOIN ContentCategory cc 
ON c.contentIdx = cc.contentIdx
JOIN Category ca
ON cc.categoryIdx = ca.categoryIdx
WHERE c.contentIdx IN (SELECT c2.contentIdx
FROM Content c2 JOIN ContentCategory cc2
ON c2.contentIdx = cc2.contentIdx
JOIN Category ca2
ON cc2.categoryIdx = ca2.categoryIdx
WHERE ca2.type = '로맨틱' AND c2.title = '호텔 델루나');

넷플릭스 화면별 컬럼 정리 엑셀 파일

https://docs.google.com/spreadsheets/d/1xJELGkarLCXbp5psMLAQh_cJ-I3KLodX2x0aNVxh8BY/edit?usp=sharing

sua

가보자고

다음 포스트

4주차 Netflix API 개발

1개의 댓글

Sss Ho

2023년 3월 16일

안녕하세요 혹시 데이터 크롤링하셨나요? 😅

답글 달기