[DB] #7 ERD Cloud 이용해서 DB 설계 및 SQL 사용하기

자 DataGrip이랑 CSV 파일도 연동했으니, 이제 ERD Cloud를 이용해서 DB를 설계해보고 SQL을 사용해 데이터 분할을 해보는 작업을 할것이다! 

 

1. ERD Cloud 이용해서 DB 설계

ERD Cloud란? ERD를 설계하기 위한 데이터 모델링 도구이다! 아래 사이트로 가면 사용할 수 있다. 사용법은 꽤나 간단하다. 

https://www.erdcloud.com/

ERDCloud

사이트로 들어가면 아래와 같은 화면을 보게 될 것이다.

우선 회원가입부터 진행하고, 로그인을 해준다! 

그리고 상단에 내 프로필 옆에 + 표시가 있을텐데, 해당 버튼을 클릭해주면 된다.

아래와 같이 ERD 이름을 지정해주면 CREATE 해주면

비어있는 화면이 나온다! 나는 이미 설계를 한 뒤에 사진을 캡쳐한거라,,,내 ERD가 보이는것! 

https://org9899.tistory.com/150

이전 게시글을 보면, 나는 서울시 식품 위생 업소 데이터를 활용했다.

정말 다양한 데이터가 있었고 이 중에서 나는 업소명, 업태명, 소재지 도로명을 이용하기로 했다! 아래는 간단하게 내 ERD를 소개해보았당,,! 

FoodInfo

우선 업소명과 업태명으로 가게 이름을 나타내주는 테이블을 하나 생성해주었다. 업소를 나누었기때문에 해당 테이블 기본키를 업종 아이디로 지정해두었고, LocationInfo와 join해서 사용할 예정이므로 외래키로 LocationInfo의 아이디를 가져왔다.

LocationInfo

도로명 주소는 따로 빼와서 Spring을 이용해 도로명 주소를 위도, 경도 값으로 변환해서 사용해줄예정이었기 때문에 테이블을 분리 시켜주었다! FoodInfo와 join해서 사용할생각이었기 때문이다! 

사용법은 간단간단하다! 아래 보면 왼편에 새로운 테이블을 생성할 수 있는 버튼이 있다. 버튼을 클릭해주고 

아래와 같이 새로운 테이블을 생성할 수 있다! 

테이블 이름을 정의해주고, 파란색 + 버튼을 누르면 Field를 추가할 수 있으니, 자유롭게 이용해보자! 

2. SQL 사용하기

우선 전체코드를 보여주고, 천천히 설명해보도록 하겠다!

# 데이터베이스 생성
create database cleanDiningDB;

# 데이터베이스 사용하기
use cleanDiningDB;

# 테이블 생성
CREATE TABLE LocationInfo (
    locationinfo_id INT AUTO_INCREMENT PRIMARY KEY,
    latitude DOUBLE,
    longitude DOUBLE,
    address VARCHAR(100)
);
CREATE TABLE FoodInfo (
    foodinfo_id INT AUTO_INCREMENT PRIMARY KEY,
    locationinfo_id INT,
    name VARCHAR(50),
    type VARCHAR(50),
    FOREIGN KEY (locationinfo_id) REFERENCES LocationInfo(locationinfo_id)
);

-- LocationInfo 테이블에 데이터 삽입
INSERT INTO LocationInfo (latitude, longitude, address)
SELECT 0.0, 0.0, `소재지도로명`
FROM dataTotal;

# SELECT * FROM LocationInfo;

-- FoodInfo 테이블에 데이터 삽입
INSERT INTO FoodInfo (locationinfo_id, name, type)
SELECT li.locationinfo_id, dt.`업소명`, dt.`업태명`
FROM dataTotal dt
INNER JOIN LocationInfo li ON dt.`소재지도로명` = li.address
WHERE dt.`업태명` IN ('한식', '분식', '일식', '커피숍', '제과영업점', '경양식', '호프/통닭', '패밀리 레스토랑', '중국식', '패스트푸드');

-- 테이블 데이터 확인
# SELECT * FROM FoodInfo;

 

 

코드는 꽤나 간단했다,,!

큰 프로젝트를 생각하지않았기 때문,,,하하,,,

 

데이터베이스 생성 및 사용

데이터베이스를 생성하고, 사용해주는 SQL문이다.

# 데이터베이스 생성
create database cleanDiningDB;

# 데이터베이스 사용하기
use cleanDiningDB;

 

테이블 생성

미리 ERD로 설계해두었던 테이블을 생성해주었다. String 값은 varchar값으로 지정해주고, Spring에서는 id를 long으로 쓰지만, 어짜피 int형이라 int로 지정해두었다!

그리고 각각의 id값을 1부터 순서대로 입력되게 해주었고, FoodInfo에는 외래키까지 지정해주어 join을 사용할 수 있도록 했다! 

# 테이블 생성
CREATE TABLE LocationInfo (
    locationinfo_id INT AUTO_INCREMENT PRIMARY KEY,
    latitude DOUBLE,
    longitude DOUBLE,
    address VARCHAR(100)
);
CREATE TABLE FoodInfo (
    foodinfo_id INT AUTO_INCREMENT PRIMARY KEY,
    locationinfo_id INT,
    name VARCHAR(50),
    type VARCHAR(50),
    FOREIGN KEY (locationinfo_id) REFERENCES LocationInfo(locationinfo_id)
);

 

데이터 삽입 

-- LocationInfo 테이블에 데이터 삽입
INSERT INTO LocationInfo (latitude, longitude, address)
SELECT 0.0, 0.0, `소재지도로명`
FROM dataTotal;

locationInfo에는 일단 위도, 경도는 초기화해주고, 소재지 도로명만 가져오도록 했다!

소재지도로명을 가져올때는 `` 이런식으로 백틱을 사용해주어야 제대로 가져온다! 주의하장!

select로 데이터가 잘 들어왔는지 확인해보면, 내가 원하는대로 잘 들어간것을 볼 수 있다! 

FoodInfo에는 업소명과 업태명을 입력해주어야했는데, 업태명에서는 where절을 사용해서 태그별로 분리해주었다! 

-- FoodInfo 테이블에 데이터 삽입
INSERT INTO FoodInfo (locationinfo_id, name, type)
SELECT li.locationinfo_id, dt.`업소명`, dt.`업태명`
FROM dataTotal dt
INNER JOIN LocationInfo li ON dt.`소재지도로명` = li.address
WHERE dt.`업태명` IN ('한식', '분식', '일식', '커피숍', '제과영업점', '경양식', '호프/통닭', '패밀리 레스토랑', '중국식', '패스트푸드');

왜냐면, 데이터중에 일반 음식점이 아닌 가공판매업? 이런것들은 직접 가서 먹는 음식점이 아니었기 때문! 

그리고 join 구문을 이용해서 LocationInfo와 FoodInfo를 연결해주었다,,,!

짠!,,,데이터가 아주 잘 분리된것을 볼 수 있당! 히히

 

[참고자료]

https://pgmjun.tistory.com/29

[데이터베이스] ERD cloud 사용법 - 무료 ERD 모델링 도구

https://araikuma.tistory.com/495

[SQL][DDL] Foreign Key : 외래 키