[인프런] 맛집지도 만들기 (11)

비전공자를 위한 풀스택 맛집지도 만들기 프로젝트!: Front, Back-end 그리고 배포까지

비전공자를 위한 풀스택 맛집지도 만들기 프로젝트!: Front, Back-end 그리고 배포까지 - 인프런 | 강

※ 해당 링크 강의 내용을 바탕으로 작성된 포스팅입니다.

 

 

 

데이터를 저장하는 공간,  MySQL로 배우는 DB 설계 방법과  SQL

데이터베이스란 어떤 것인지 정리

데이터베이스란 데이터 저장소. 데이터를 관리하는 시스템은 DBMS.

프로그램 실행 여부와 관련 없이 데이터 저장 가능.

 

DBMS는 데이터 저장 형태에 따라 관계를 기반으로 한 정형 데이터인 RDBMS, key-value 값으로 이루어진 비정형 데이터 NoSQL로 나눌 수 있다.

MySQL은 RDBMS로 관계형 데이터베이스이다.

 

학번	이름
1	김철수
2	홍길동

 

RDBMS에서는 표 전체를 테이블이라고 하고,  학번, 이름과 같이 어떤 데이터가 들어올지를 열, column이라고 하고, 실제 들어오는 데이터 한 줄 한 줄을 행, row라고 한다.

RDBMS는 엑셀과 유사하다. 엑셀 파일 생성은 DB(스키마) 생성, 시트 생성은 테이블 생성으로 비유할 수 있다.

 

SQL은 RDBMS 데이터 관리를 위한 언어로, RDBMS를 통해 DB 권한설정, 테이블 CRUD, 데이터 CRUD가 이루어진다.

1. DCL: 데이터베이스 접근 권한 관리
2. DDL: 테이블 CRUD
3. DML: 데이터 CRUD

 

 

 

DB를 설계하는 노하우

Tip
서비스의 핵심 기능을 한 문장으로 요약해 본다.
ex) 수강신청: 어떤 학생이 어떤 강의를 신청했는가

 

학생을 정의할 테이블을 만든다고 하면, 학번, 이름, 학과, 생년월일, 주소 등과 같이 학생을 정의할 컬럼이 필요할 것이다.

 

학생 테이블
학번	이름	학과	생년월일	주소
1	홍길동	컴공	990101	서울
2	김길동	영어	980101	인천
3	최길동	수학	970101	부천

 

또한, 강의 테이블을 정의하기 위해서는 과목번호, 과목명, 담당교수, 시간, 장소 등의 컬럼이 필요할 것이다.

 

강의 테이블
과목번호	과목명	담당교수	담당교수 교번	시간	장소
1	객체지향	박철수	1	월요일	1호관
2	고급영어	김철수	2	수요일	3호관
3	선형대수	김철수	3	수요일	4호관

 

마지막으로 누가 어떤 강의를 신청했는지 명시될 수강신청 테이블이 필요하다.

 

수강신청 테이블
학번	이름	과목번호	과목명	신청상태	시간
1	홍길동	1	객체지향	취소	3시
2	김길동	2	고급영어	신청	3시 30분
3	최길동	2	고급영어	신청	4시

 

Tip
데이터베이스 정규화
다른 테이블에서 가져와서 쓸 수 있는 중복 컬럼이나 쓸모 없는 컬럼을 파악한다.
ex) 위의 수강신청 테이블에서 이름과 과목명 컬럼을 지워 준다. 

컬럼이 확장성이 생겨 버리면 테이블을 분리해 준다.
ex) 제 2전공 테이블 생성

제 2전공 테이블
학번	학과	상태
1	수학	복수전공
2	미술	부전공

담당 교수의 김철수가 동명이인인 것과 애매한 상황이 생길 경우.
ex) 교수 테이블을 만들고, 교수명 대신 담당교수 교번을 넣어 준다.

교수 테이블
교번	교수명	담당학과
1	박철수	컴공
2	김철수	영어
3	김철수	수학

테이블에 생성, 수정, 삭제 컬럼은 있는 것이 좋다.

 

 

 

복잡한 코드 없이 테이블을 만드는 방법

MySQL 자료형

숫자

INT: 정수형 자료형.

FLOAT: 소수형 자료형.

 

문자열

CHAR(): 고정 길이 문자열. 지정 길이보다 짧게 입력하면 나머지 공간은 공백으로 채워짐.

VARCHAR(): 가변 길이 문자열. 지정 길이보다 짧게 입력하면 나머지 공간 사용 X.

TEXT: 최대 65535 byte까지 저장 가능한 문자열 자료형. 주로 URL, 게시글 등 긴 문자열 저장 시 사용.

 

시간 및 날짜

TIMESTAMP: YYYY-MM-DD HH:MM:SS 형태로 시간 표시.

DATETIME: 1001년 ~ 9999년 사이의 더 넓은 범위의 시간을 나타낼 수 있음.

생물정보 전문위키, 인코덤

 

 

 

MySQL 키(Key)

1. 기본키(Primary Key)

 NULL을 허용하지 않고, 유니크한 성질을 가지고 있어 테이블 당 단 하나의 값만 가질 수 있다.

고유한 값을 지정할 때 PK로 지정. ex) 학번

 

2. 외래키(Foreigner Key)

조인과 같이 다른 DB와 관계를 맺는 경우, 다른 테이블의 PK를 참조하는 컬럼.

 

3. 고유키(Unique Key)

기본키와 비슷하지만 NULL을 허용함.

 

 

 

그 외 설정

NN(not null): Null 미허용

BIN(binary): 이진 데이터

UN(unsigned): 부호 없음

ZF(zero filled): 0으로 채워짐

AI(auto increment): 자동 증가

G(generated): 다른 열을 기반으로하는 수식에 의해 생성

 

 

 

테이블 만들기

 

mysql 모델 설계 탭

 

• + 버튼을 눌러 모델을 추가한다.
• mydb 부분을 더블클릭하면 나오는 스키마 창으로 데이터베이스의 이름을 정할 수 있다. 
• Enrollment(수강 신청)으로 이름을 정하고 Rename Reference를 클릭해 준다.
• Add Diagram 버튼을 더블클릭하면 EER Diagram 창이 나오는데, GUI 형태로 테이블을 쉽게 만들 수 있다.

• 위 버튼을 더블클릭하고 격자무늬 칸을 클릭해 테이블을 만든다.
• 테이블 이름을 Students(학생)로 지정하고, 테이블을 하나 더 만들어 이름을 Lectures(강의)로 지정한다.

Students 테이블

• Students 테이블에 studentIdx(학번), studentName(이름), major(전공), birth(생년월일), address(주소), status(상태), createdAt(생성), updatedAt(삭제) 칼럼을 만들어 준다.
• studentIdx-INT-PK-NN-AI, studntName-VARCHAR(45)-NN, major-VARCHAR(45)-NN, birth-DATE-NN, address-VARCHAR(300)-NN, status-CHAR(1)-NN, createdAt-TIMESTAMP-NN, updatedAt-TIMESTAMP로 설정해 준다.
• Default/Expression 칸에 status는 A, createdAt은 current_timestamp, updatedAt은 current_timestamp on update current_timestamp를 넣어 준다.

Lectures 테이블

• Lectures 테이블에 lectureIdx(과목번호), lectureName(과목명), professorIdx(담당교수 교번), lectureTime(시간), place(장소), status(상태), createdAt(생성), updatedAt(삭제) 칼럼을 만들어 준다.
• lectureIdx-INT-PK-NN-AI, lectureName-VARCHAR(45)-NN, professorIdx-INT-NN, lectureTime-VARCHAR(45)-NN, place-VARCHAR(45)-NN, status-CHAR(1)-NN, createdAt-TIMESTAMP-NN, updatedAt-TIMESTAMP로 설정해 준다.
• Default/Expression 칸에 status는 A, createdAt은 current_timestamp, updatedAt은 current_timestamp on update current_timestamp를 넣어 준다.

지금까지 만든 EER Diagram은 그림에 불과하다. DDL 자동화 툴로 만들어 줘야 한다.

• File → Export → Forward Enginner SQL CREATE Script(Ctrl+Shift+G)
• Next를 눌러 Review SQL Script 페이지가 나오면 Copy to Clipboard 눌러 복사 후 Finish.
• 이전에 생성한 Connection을 선택해, Query 1 창에 붙여넣기를 해 준다.

• 해당 버튼을 누르면 아래와 같이 바뀐다.

 

 

 

데이터 삽입하기

 

맨 오른쪽 아이콘 클릭

 

 

• 작성 후 Apply 버튼을 클릭해 준다.

※ lectureIdx는 AI가 적용되어 있어서 넣어주지 않아도 된다.

 

 

 

 

• 여기서 status 값을 D로 바꿔 주면 updatedAt 시간이 바뀌는 것을 볼 수 있다.

 

 

MySQL 기초 SQL

Students 테이블로 실습. Query 1 코드 지워 주기.

 

Query 1

use Enrollment;

 

Tip

해당 아이콘을 누르면 코드 정렬을 해 준다.

Ctrl+Enter를 해 주면 쿼리가 실행된다.

 

 

 

생성(insert)

 

INSERT INTO 테이블이름(컬럼이름1, 컬럼이름2, 컬럼이름3, ...) VALUES (데이터값1, 데이터값2, 데이터값3, ...);

 

Query 1 

INSERT INTO Students(studentName, major, birth, address) VALUES ('홍길동', '컴공', '1999-01-01', '서울');

 

 

 

 

수정(update)

Students에 코드 입력.

 

UPDATE 테이블이름
SET 컬럼이름1=데이터값1, 컬럼이름2=데이터값2, ...
WHERE 필드이름=데이터값;

 

Students

use Enrollment;

 

UPDATE Students
SET major='미술'
WHERE studentIdx = 1;

 

 

 

 

 

삭제(delete)

 

DELETE FROM 테이블이름
WHERE 필드이름=데이터값;

 

Students

DELETE FROM Students
WHERE studentIdx=1;

 

 

 

 

조회(select)

 

select 컬럼 from 테이블 where 조건

 

Students

-- *은 all을 의미한다.
select * from Students;

 

 

 

Students

-- 조건 걸기
select * from Students where studentIdx=4;

 

 

 

Students

-- 다중 조건
select * from Students where studentIdx=4 and major='미술';

 

 

 

Students

-- 가져오는 범위 지정
select studentName from Students where studentIdx=4;