MySQL 2 | Practice

공부의 기록·2021년 12월 9일
Query Languagemysql
0

DB MySQL

목록 보기
2/2
post-custom-banner

서론

MySQL 을 사용했으며, DBdiagram.io (ERM) 툴을 사용했다. (현재 문서에 있는 모델 들은 전부 동일함)
조금 더 전문적이고 구체적인 ERM 툴과 비슷한 무료 ERM 툴이 있었으나, DBdiagram.io 이 지원하는 쿼리문 작성 기반의 매핑이 쿼리문 연습에 도움이 될 것 같다는 판단 하에 이 툴을 사용하게 되었다.

1번 수강 신청 프로그램

수강신청 프로그램을 User(student,professor)와 Class(class, class_order)으로 나누었다. User의 개인정보 테이블(presonal_data) 을 만들어서 User 가 외래값 참조를 하게 만들었다.

단, 최종적으로는 개인정보 테이블에 student인지 professor 인지를 가르는 값을 만들지 않아서 기능적으로 별로인 것 같다라고 생각했다.

CREATE DATABASE class_management_system;
USE class_management_system;

CREATE TABLE student(
    serial INTEGER,
    personal_data_id INTEGER,
    class_order_id INTEGER
    CONSTRAINT PRIMARY KEY(serial),
    CONSTRAINT FOREIGN KEY(personal_data_id) REFERENCES personal_data(personal_data_id)
    CONSTRAINT FOREIGN KEY(personal_data_id) REFERENCES class_order(class_order_id)
);

CREATE TABLE Professor (
    serial INTEGER,
    personal_data_id INTEGER,
    CONSTRAINT PRIMARY KEY(serial),
    CONSTRAINT FOREIGN KEY(personal_data_id) REFERENCES personal_data(personal_data_id)
);

CREATE TABLE personal_data (
    personal_data_id INTEGER,
    phone VARCHAR(300),
    name VARCHAR(300),
    age VARCHAR(300),
    CONSTRAINT PRIMARY KEY(personal_data_id)
);

CREATE TABLE class(
    class_id INTEGER,
    class_name INTEGER,
    class_info VARCHAR(300),
    class_now_count INTEGER,
    class_max_count INTEGER,
    CONSTRAINT PRIMARY KEY(class_id)
);

CREATE TABLE class_order(
    order_id INTEGER,
    class_order_id INTEGER,
    CONSTRAINT PRIMARY KEY(class_order_id),
    CONSTRAINT FOREIGN KEY(class_id) REFERENCES class(class_id)
);

SELECT * FROM class;
SELECT * FROM persoanl_data;
SELECT * FROM class_order;

2번 수하물 관리 시스템


이번에는 명확한 탑다운 방식을 따르려고 노력했다.
최상위 계층인 한 명의 클라이언트가 가지고 있는 것들에 대한 공통 특징을 가진 것들을 테이블화 시켰다.

하나의 클라이언트(customer) 는 세 개의 외래값을 참조 하고 있다.
1. 여권 인증(certified_customer)
2. 수하물 (baggage) + 수하물 (baggage_sort)
3. 티켓(airport_ticket) + 항공사(airport_company)

티켓은 다시 세 개의 외래값을 참조하고 있다.
그 중 하나는 중복되어 참조하고 있으나, 사용상의 경우 ticket 과 flight 모두 항공사 정보가 필요하다는 판단에 넣게 되었다.

  1. 항공사(airport_company)
  2. 공항 존재 지역 리스트(country_area)
  3. 탑승 비행기 종류 정보(flight_sort) | 보잉 999 같은 비행기 종을 기록
CREATE DATABASE airport_system;
USE airport_system;

CREATE TABLE customer (
    customer_id INTEGER,
    customer_name VARCHAR(300),
    customer_age INTEGER,
    customer_phone VARCHAR(300),
    customer_certified_customer_id INTEGER,
    customer_baggage_id INTEGER,
    customer_airport_ticket_id INTEGER,
    CONSTRAINT PRIMARY KEY(customer_id),
    CONSTRAINT FOREIGN KEY(customer_certified_customer_id) REFERENCES certified_customer(certified_customer_id),
    CONSTRAINT FOREIGN KEY(customer_baggage_id) REFERENCES baggage(baggage_id),
    CONSTRAINT FOREIGN KEY(customer_airport_ticket_id) REFERENCES airport_ticket(airport_ticket_id)
);

CREATE TABLE certified_customer (
    certified_customer_id INTEGER,
    certified_date TIMESTAMP,
    certified_expired TIMESTAMP,
    certified_country VARCHAR(100),
    CONSTRAINT PRIMARY KEY(certified_customer_id)
);

CREATE TABLE baggage (
    baggage_id INTEGER,
    baggage_sort_id INTEGER,
    baggage_weight INTEGER,
    CONSTRAINT PRIMARY KEY(baggage_id),
    CONSTRAINT FOREIGN KEY(baggage_sort_id) REFERENCES baggage_sort(baggage_sort_id)
);

CREATE TABLE baggage_sort (
	baggage_sort_id INTEGER,
    baggage_sort_name VARCHAR(300),
    baggage_sort_info VARCHAR(3000),
    CONSTRAINT PRIMARY KEY(baggage_sort_id)
);

CREATE TABLE airport_ticket (
	airport_ticket_id INTEGER,
    airport_company_id INTEGER,
    airport_flight_id INTEGER,
    CONSTRAINT PRIMARY KEY(airport_ticket_id),
    CONSTRAINT FOREIGN KEY(airport_company_id) REFERENCES airport_company(airport_company_id),
    CONSTRAINT FOREIGN KEY(airport_flight_id) REFERENCES airport_flight(airport_flight_id)
);

CREATE TABLE airport_company (
	airport_company_id INTEGER,
    airport_company_name INTEGER,
    airport_company_phone INTEGER,
    CONSTRAINT PRIMARY KEY(airport_company_id)
);

CREATE TABLE airport_flight (
    airport_flight_id INTEGER,
    airport_company_id INTEGER,
    airport_start VARCHAR(100),
    airport_end VARCHAR(1000),
    CONSTRAINT PRIMARY KEY(airport_flight_id),
    CONSTRAINT FOREIGN KEY(airport_company_id) REFERENCES airport_company(airport_company_id),
    CONSTRAINT FOREIGN KEY(airport_start) REFERENCES country_area(country_area_id),
    CONSTRAINT FOREIGN KEY(airport_end) REFERENCES country_area(country_area_id)
);

CREATE TABLE country_area (
    country_area_id INTEGER,
    country_area_name VARCHAR(300),
    country_area_info TEXT,
    CONSTRAINT PRIMARY KEY(country_area_id)
);

3번 영상 호스팅 서비스 (유저모델 제외)


과거 MongoDB 를 기반으로 Wetube 클론코딩 및 Lifefolio 프로젝트를 진행한 경험이 있다.
그리고 DB 없이 서드파티 API 서비스로 영화 앱을 세 번 정도 다양한 툴을 이용해서 만들었다.

그것들을 진행하면서 어느 정도 데이터에 대한 개념이 잡혀서 그런가.
영화 서비스에 대한 것은 꽤나 깔끔하게 모델이 잡힌 것 같다.

위 테이블을 보면서,
movie_detail 에서는 왜 영화 제목이나 포스터가 없나? 라고 생각을 할수도 있는데,
프론트 단에서 받은 데이터를 클릭 시 넘겨주는 방식으로 하면 호출 횟수를 줄이고
직접적으로 movie_detail 만 호출함으로써 자원을 절약할 수 있지 않을까 라고 생각했다.

그리고 카테고리(movie_catgory), 시청연령제한(movie_detail_age), 지원화질(movie_detail_quality) 는 회사 내외부 정책 변화가 없으면 고정값을 계속 쓰는 것이라고 생각했다.

따라서 소규모의 세부 테이블을 만들어서 값을 넣어 주는 것으로 기획하게 되었다.

MongoDB 였다면 아래와 같은 스키마 모델을 사용했을 텐데, 꽤나 차이점이 있다.

movie:{
  movie_id: // 아이디
  movie_name: // 제목
  movie_poster: // url
  movie_category: // 카테고리
  movie_detail:{
  	movie_detial_publish: // 런칭 날자
    movie_detail_age: //시청 연령제한
    movie_detail_quality: //지원화질
  	movie_detail_info: // 상세 설명
  }
}
CREATE DATABASE netflix;
USE netflix;

CREATE TABLE movie(
    movie_id INTEGER PRIMARY KEY,
    movie_name VARCHAR(300),
    movie_poster TEXT,
    movie_category integer,
    movie_detail integer,
    CONSTRAINT FOREIGN KEY (movie_category) REFERENCES movie_category (movie_category_id),
    CONSTRAINT FOREIGN KEY (movie_detail) REFERENCES movie_detail (movie_detail_id)
);

CREATE TABLE movie_category(
    movie_category_id INTEGER PRIMARY KEY,
    movie_category_name VARCHAR(1000)
);

CREATE TABLE movie_detail(
    movie_detail_id INTEGER PRIMARY KEY,
    movie_detail_publish VARCHAR(30),
    movie_detail_age INTEGER,
    movie_detail_quality INTEGER,
    movei_detial_info TEXT,
    CONSTRAINT FOREIGN KEY (movie_detail_age) REFERENCES movie_detail_age(movie_detail_age_id),
    CONSTRAINT FOREIGN KEY (movie_detail_quality) REFERENCES movie_detail_quality(movie_detail_quality_id)
);

CREATE TABLE movie_detail_age(
	movie_detail_age_id INTEGER PRIMARY KEY,
    movie_detail_age_type VARCHAR(300)
);

CREATE TABLE movie_detail_quality(
    movie_detail_quality_id INTEGER PRIMARY KEY,
    movie_detail_quality_type text
);

# INSERT (아래에서 위로 값을 넣어줘야 합니다.)
-- 무비 메인 정의
INSERT INTO movie VALUES
(1,'영화 1번', 'http://something.com/',1,1),
(2,'영화 2번', 'http://something.net/',2,2),
(3,'영화 3번', 'http://something.co.kr/',3,3);

-- 무비 디테일 정의
INSERT INTO movie_detail VALUES
(1,'오늘',1,2,'이 영화는 너무 재미없는 영화지만 그래도 영상미는 있네요.'),
(2,'어제',3,4,'이 영화도 진짜 너무 재미없는데 왜 재미없는 영화만 출시할까요.'),
(3,'이틀 전',2,3,'제 인생 영화입니다...');

-- 세부 테이블 값 정의
INSERT INTO movie_detail_quality VALUES
(1,'720'),(2,'1080'),(3,'HD'),(4,'FHD'),(5,'4K');
INSERT INTO movie_detail_age VALUES
(1,'ALL'),(2,'12'),(3,'15'),(4,'19');
INSERT INTO movie_category VALUES
(1,'애니메이션'),(2,'공포 스릴러'),(3,'로멘스'),(4,'다큐멘터리');

# 조회 (값을 넣고 나면 자유롭게 조회할 수 있습니다.)
-- 메인 테이블
SELECT * FROM movie;
SELECT * FROM movie_detail;

# 막연하게 여기실까봐 movie 를 거쳐서 movie_detail 을 호출하는 코드도 추가해봤습니다.
SELECT * FROM movie JOIN movie_detail
	WHERE movie.movie_id = movie_detail.movie_detail_id;

-- 세부 테이블
SELECT * FROM movie_category;
SELECT * FROM movie_detail_age;
SELECT * FROM movie_detail_quality;

# 제거 (위에서 아래로 제거해야 합니다. | 위에서 아래 값을 참조하고 있으므로, 아래값을 먼저 삭제하려고 하면 에러가 뜹니다.)
-- 메인 테이블
DELETE FROM movie;
DELETE FROM movie_detail;

-- 세부 테이블
DELETE FROM movie_category;
DELETE FROM movie_detail_age;
DELETE FROM movie_detail_quality;

# 해볼만한 문제
-- 특정 category 에 해당하는 영화만 불러오기
-- 특정 category 에 해당하는 영화만 삭제하기

4번 영상 호스팅 서비스 (유저모델 포함)

CREATE DATABASE netflix;
USE netflix;

CREATE TABLE user(
	user_serial INTEGER PRIMARY KEY,
    user_id VARCHAR(300) NOT NULL,
    user_email VARCHAR(300),
    user_pw TEXT NOT NULL,
    user_info TEXT,
    user_favor INTEGER REFERENCES movie
);

CREATE TABLE movie(
    # 금일 배운 auto_increase 를 적용하였습니다.
    movie_id INTEGER PRIMARY KEY AUTO_INCREMENT,
    movie_name VARCHAR(300),
    movie_poster TEXT,
    # 이 문법이 더 직관적이고 목적에 맞는 것 같아서 변경했습니다.
    # 기존에 CONSTRAINT 로 사용했던 것을 아래 처럼 작성하면 movie_category 의 기본값을 자동으로 참조하게 됩니다.
    # 또 제가 설명 못드렸던 부분인데 이렇게 세부 테이블을 선언해서 참조 시켜놓으면 세부 테이블에서 선언한 기본값 영역을 벗어나는
    # 순간 에러가 발생해서 자동으로 데이터가 필터링 되게 됩니다.
    # 예를 들어, movie_category 의 pk 가 1부터 10까지만 존재하는데
    # movie_category 에 11을 넣어주게 되면 에러가 발생해서 데이터가 안들어가집니다.
    movie_category integer REFERENCES movie_category,
    movie_detail integer,
    CONSTRAINT FOREIGN KEY (movie_detail) REFERENCES movie_detail (movie_detail_id)
);

CREATE TABLE movie_category(
    movie_category_id INTEGER PRIMARY KEY,
    movie_category_name VARCHAR(1000)
);

CREATE TABLE movie_detail(
    movie_detail_id INTEGER PRIMARY KEY AUTO_INCREMENT,
    movie_detail_publish VARCHAR(30),
    movie_detail_age INTEGER REFERENCES movie_detail_age,
    movie_detail_quality INTEGER REFERENCES movie_detail_quality,
    movei_detial_info TEXT
);

CREATE TABLE movie_detail_age(
	movie_detail_age_id INTEGER PRIMARY KEY AUTO_INCREMENT,
    movie_detail_age_type VARCHAR(300) 
);

CREATE TABLE movie_detail_quality(
    movie_detail_quality_id INTEGER PRIMARY KEY AUTO_INCREMENT,
    movie_detail_quality_type text
);

# INSERT (아래에서 위로 값을 넣어줘야 합니다.)
-- 무비 메인 정의
INSERT INTO movie VALUES
(1,'영화 1번', 'http://something.com/',1,1),
(2,'영화 2번', 'http://something.net/',2,2),
(3,'영화 3번', 'http://something.co.kr/',3,3);

-- 무비 디테일 정의
INSERT INTO movie_detail VALUES
(1,'오늘',1,2,'이 영화는 너무 재미없는 영화지만 그래도 영상미는 있네요.'),
(2,'어제',3,4,'이 영화도 진짜 너무 재미없는데 왜 재미없는 영화만 출시할까요.'),
(3,'이틀 전',2,3,'제 인생 영화입니다...');

-- 세부 테이블 값 정의
INSERT INTO movie_detail_quality VALUES
(1,'720'),(2,'1080'),(3,'HD'),(4,'FHD'),(5,'4K');
INSERT INTO movie_detail_age VALUES
(1,'ALL'),(2,'12'),(3,'15'),(4,'19');
INSERT INTO movie_category VALUES
(1,'애니메이션'),(2,'공포 스릴러'),(3,'로멘스'),(4,'다큐멘터리');

# 조회 (값을 넣고 나면 자유롭게 조회할 수 있습니다.)
-- 메인 테이블
SELECT * FROM movie;
SELECT * FROM movie_detail;

# 막연하게 여기실까봐 movie 를 거쳐서 movie_detail 을 호출하는 코드도 추가해봤습니다.
SELECT * FROM movie JOIN movie_detail
	WHERE movie.movie_id = movie_detail.movie_detail_id;

-- 세부 테이블
SELECT * FROM movie_category;
SELECT * FROM movie_detail_age;
SELECT * FROM movie_detail_quality;

# 제거 (위에서 아래로 제거해야 합니다. | 위에서 아래 값을 참조하고 있으므로, 아래값을 먼저 삭제하려고 하면 에러가 뜹니다.)
-- 메인 테이블
DELETE FROM movie;
DELETE FROM movie_detail;

-- 세부 테이블
DELETE FROM movie_category;
DELETE FROM movie_detail_age;
DELETE FROM movie_detail_quality;

# 해볼만한 문제
-- 특정 category 에 해당하는 영화만 불러오기
-- 특정 category 에 해당하는 영화만 삭제하기
profile
공부의 기록
2022년 12월 9일 부터 노션 페이지에서 작성을 이어가고 있습니다.
이전 포스트

MySQL 1 | Basic

post-custom-banner

0개의 댓글