테이블을
데이터 파일 기반으로 테이블을 만드는 순서
테이블을 생성하는 순서와 기준
순서와 기준
공공데이터 파일 CSV형태로 다운로드.
테이블 및 컬럼명, 타입, 사이즈 정의 및 생성.
CSV파일 편집/가공 후 테이블에 업로드.
테이블 구조 및 데이터 조정.
제공되는 CSV파일을 PC에 다운로드
WebBrowser, Excel
CSV파일 내용을 참조하여 테이블 및 컬럼들을 정의, 생성
Excel, HeidiSQL
CSV파일을 편집/가공 후, 생성된 데이터를 테이블에 업로드
Notepad++, HeidiSQL
테이블 구조와 데이터를 조정하여 마스터,실적,참조 정보 제공
HeidiSQL, MariaDB
"서울시 지하철역 정보 검색 (역명)"
http://data.seoul.go.kr/dataList/OA-121/S/1/datasetView.do
제공되는 CSV파일을 PC에 다운로드
WebBrowser, Excel
해당 페이지
CSV파일 내용을 참조하여 테이블 및 컬럼들을 정의, 생성
Excel, HeidiSQL
한글명을 영문명으로
seoul_subway_station_info
구글 번역기를 통해 한글명을 영문으로 해석
https://translate.google.co.kr/?hl=ko&sl=ko&tl=en&op=translate
영문 컬럼명 정의
적절한 네이밍을 통해 컬럼명 정의
전철역코드 전철역명 호선 외부코드
station_cd VC(50) station_nm VC(100) line_num VC(100) fr_code VC(50)
API문서에 기재된 컬럼값명
JSON에 기재된 내부 컬럼값명
<공통표준용어> 문서 참조
사내 명명규칙서 혹은 개발자 정의
관련된 테이블이 있다면 유사, 동일 의미 컬럼명 정의시 주의
HeidiSQL을 이용하여
컬럼명, 타입 정의 시 Tips
컬럼명 만들 때 카멜구조 참조.
VC20, VC50, VC100, V500, V1000
INT(6)
DATE, DATETIME
NULL의 경우,
station_nm VARCHAR(500) NULL DEFAULT NULL, (지양)
station_nm VARCHAR(500) NOT NULL,(권장)
위도lat DEC(16,14), 경도lon DEC(17,14)
일자는 VC(50)으로 가져와 테이블 상에서 DATE나 DATETIME 형식으로 변경
CSV파일을 편집/가공 후 생성된 데이터를 테이블에 업로드
Notepad++, HeidiSQL
Notepad++을 이용한 파일 처리
정의한 테이블명으로 CSV파일명을 변경
(한글->영문)
CSV파일을 UTF8 문서 형식으로 변경
CR/LF 문자 체크 (CR/LF 모두 포함 확인)
첫 라인이 컬럼명을 포함한 헤더라면 쌍따옴표를 제거하세요.
HeidiSQL의 "CSV 파일 가져오기"
메뉴를 이용하여 테이블에 업로드
첫 라인(헤더)을 제외할 것인가?
컬럼 감싸는 "(쌍따옴표) 사용할 것인가?
import시 원하지 않는 컬럼이 있는가?
줄 종결자로 인해 업로드가 실패하는가?(CR,LF)
쌍따옴표로 컬럼을 구분짓는 경우 모든 레코드의 끝부분에 공백이나 여타 문자가 있으면 안되며 반드시 마지막 레코드는 빈 줄이어야합니다. 또한 헤더는 쌍따옴표로 묶으면 안 됩니다.
테이블 구조와 데이터를 조정하여 마스터,실적,참조 정보 제공
HeidiSQL
검증
CSV파일의 건수와(헤더 및 마지막 공백라인 제외)
테이블에 업로드 된 데이터 건수 비교
-- 건수 검증 748
SELECT COUNT() FROM seoul_subway_station_info
;
컬럼 타입 변경, 추가 및 삭제 등
DELETE NULL DATA
일자 데이터를 DATE 포맷으로 변경
일자 컬럼 타입을 변경
VC(50) -> DATE
정규화
데이터 수정
PK 컬럼 정의
PK 후보 컬럼 검증
-- PK 후보 컬럼 검증
SELECT station_cd, COUNT() FROM seoul_subway_station_info
GROUP BY station_cd
HAVING COUNT() > 1
;
seoul_subway_station_info
CREATE TABLE seoul_subway_station_info (
station_cd VARCHAR(50) NOT NULL,
station_nm VARCHAR(50) NULL DEFAULT NULL,
line_num VARCHAR(50) NULL DEFAULT NULL,
fr_code VARCHAR(50) NULL DEFAULT NULL,
PRIMARY KEY (station_cd) USING BTREE
)
COMMENT='서울시 지하철역 정보 검색 (역명)'
COLLATE='utf8mb3_general_ci'
ENGINE=InnoDB
;
SELECT COUNT() FROM
seoul_subway_station_info
;
SELECT station_cd, COUNT() FROM
seoul_subway_station_info
GROUP BY station_cd
HAVING COUNT() > 1
;
Tip) HeidiSQL
우측 상단창, 도움말 및 컬럼명 리스트업
데이터 탭에서 "필터" 기능
station_nm LIKE '%종로3가%' AND ...
SQL 퀴즈
SELECT FROM seoul_subway_station_info
;
1호선에 속한 역들을 역명으로 정렬하여 조회
-- 1호선에 속한 역들을 역명으로 정렬하여 조회
SELECT FROM seoul_subway_station_info
WHERE line_num = '01호선'
ORDER BY station_cd
;
호선별로 포함된 역들의 개수를 조회
-- 호선별로 포함된 역들의 개수를 조회
SELECT line_num, COUNT() FROM seoul_subway_station_info
GROUP BY line_num
ORDER BY line_num
;
종로3가역을 지나는 모든 호선을 조회
-- 종로3가역을 지나는 모든 호선을 조회
SELECT FROM seoul_subway_station_info
WHERE station_nm LIKE '%종로3가%'
;
환승역이 있는 모든 역명을 조회
-- 환승역이 있는 모든 역명을 조회
SELECT station_nm, COUNT() FROM seoul_subway_station_info
GROUP BY station_nm
HAVING COUNT() > 1
;
가락시장역과 공덕역을 지나는 모든 호선을 조회
-- 가락시장역과 공덕역을 지나는 모든 호선을 조회
SELECT FROM seoul_subway_station_info
WHERE station_nm IN ('가락시장','공덕')
ORDER BY station_nm
;
