JS FULL STACK 4장 , Sql Query쿼리문으로 DB설계

왜 공부했지?

이전의 2장,3장에서 관계형데이터모델링을 배우기위해 선행조건으로

SQL query 즉 테이블 생성,조회,수정,삭제,join하는 방법을 숙지할것을 필요로하고있었다.

이전의 2장과 3장에서 데이터모델링을 배우면서 배웠던 핵심이 바로 중복의제거였으며 
우리가 현실의문제를해결하기위한 서비스를 만든다고할때

현실의 문제 ex: 영화관 티켓을 오프라인으로 예매하던 시절

현실의 문제를 해결하고싶어 의뢰하러 개발자인 나에게 온 사람 ex :온라인으로 영화관예매를할수있지않을까? 웹서비스를 만들어줘! 

 

일을 의뢰한 사람과 소통하면서 목적(영화예매사이트)을 달성하기 위해서 관계형 데이터모델링단계를 거침

1. 같이 만들 웹서비스 ui를 직접 그려보면서 업무파악

2. 개념적 데이터모델링 과정에서 개념들을찾음(영화예매웹사이트를만들기 위한 필요한 데이터들 찾아서 ERD설계)

3. 논리적 데이터모델링 과정에서 ERD를 통해 더 명확한 표(테이블)로 설계함. 

3. 정규화과정을통해서 현재만들어진 표안의 값들이 원자적인지 종속되어있지는않은지 중복이없는지 문제사항들을 제거
함으로써 여러가지 문제사항들에 대처한다.

 

여기까지 이전 포스팅들의 내용이였고

 

이제부터 물리적데이터모델링 과정을 통해 실제 테이블로 만드는것이 목표이다.
실제 테이블로 만들기 위해서 바로 SQL query문을 통해서 테이블을 생성,수정,조회,삭제 하는방법을 배울것이다.

SQL문 공부방법? = Cheet Sheet

나는 강사님이 주신 자료를 통해 SQL문을 공부했다.

공부방법의 핵심은 바로 정리된 sql문 표를 보고 실전 문제를 보며 직접 문제를 해결하는 방법이였다.

SQL 조회를 위한 query문에 select가있고 조건에맞는 조회를 위해선 where를쓰고 이런 query문들을 하나하나 외우는것이아니라

쓰다가보니 외워지고 이해하는 구조로 공부해야한다 프로그래밍 언어를 공부하는 과정과 비슷하다 직접 만들어보는것이 공부의핵심이였다.

이렇게 무언가를 직접 만들어보는 과정에서 하나하나 필요로하는 기능을 검색하는 귀찮은 사람들을 위해서

Cheet sheet라는게 존재하는것이다. 사용방법이 잘 정리된 표라고 생각하면된다. 
즉 sql문을 제대로 공부하기 위해서 직접 본인이 만들고싶은 서비스가있다고생각해보고 이전 포스팅에서 다룬 관계형 데이터모델링과정을통해서

데이터들을 분석하고 표로 만들어두었다면 그걸 실제 물리적데이터로 설계하기위해서 필요로하는 query문을 찾아 직접 설계하면되는것이다.

 

다음의 사진은 강사님이 제공해주신사이트에있던 정리표랑 테스트문제이다.

 

 

이전 포스팅에서 만들어둔 ERD를 실제 테이블로 설계해보자

이전 포스팅에서 ERD CLOUD

를 통해서 논리적 ERD를 만드는것까지 성공했었다. (데이터가 단순한것들만 존재해서 정규화검증은 더 이상 하지않아도 될것같다고 판단.)

 

ERD CLOUD에는 아주 특별한 기능이 존재한다. 

만들어둔 ERD페이지 하단의 내보내기 -> SQL 미리보기 버튼을누르면 바로 SQL QUERY문을 얻을수 있음

CREATE TABLE `Todo` (
	`id`	int	NOT NULL	COMMENT 'auto_increment , unsigned',
	`contents`	varchar(100)	NOT NULL,
	`completed`	boolean	NOT NULL	COMMENT '완료 여부 파악을 통해 할일 완료 가능'
);

CREATE TABLE `Member` (
	`id`	int	NOT NULL	COMMENT 'auto_increment, unsigned',
	`name`	varchar(10)	NOT NULL,
	`password`	varchar(20)	NOT NULL,
	`callnum`	varchar(13)	NOT NULL
);

CREATE TABLE `write` (
	`id`	int	NOT NULL	COMMENT '홍길동',
	`id2`	int	NOT NULL	COMMENT '기상'
);

ALTER TABLE `Todo` ADD CONSTRAINT `PK_TODO` PRIMARY KEY (
	`id`
);

ALTER TABLE `Member` ADD CONSTRAINT `PK_MEMBER` PRIMARY KEY (
	`id`
);

 

자 그럼 이제 이것을 DBMS로 옮겨 작업하기위해서 내가 선택한 RDBMS인 MYSQL을 설치

그리고 이런 SQL Query문들을 어두운 터미널창에서 작업하지말고 조금더이쁘고 편안한방법으로 작업하기위해

DBeaver라는 DB 관리툴을 설치한다.

 

MYSQL 설치

mysql을 설치하기위해 앞서 윈도우 운영체제를 사용하는 개발자와  , MAC을 사용하는 개발자가 MYSQL을 설치하는 방법이 조금달랐다.

윈도우 유저의경우에는 XAMPP를 먼저 설치해줘야하고 mac유저는 brew를 통해 아주 쉽게 설치할수 있었다.

나는 mac을 사용중이라서 homebrew를 통해 설치하였고 설치 과정은 블로그를 통해 직접 따라하며 설치하였다. 
블로그 내용에서 1번인 Sequel PRO도 일종의 DB툴인데 나는 DBeaver를 쓸거라서 설치하지않았다.

[Sequel Pro + MySQL] DB 관리하는법

Dbeaver 설치후 작업 환경설정

 

DBeaver 설치후 강사님이 주신 Youtube 자료를 통해 Dbevar 환경설정(색상,글꼴,자동대문자) 를 할수 있었고 Root계정을 이용해 작업계정을 만든뒤 작업계정으로 접속하여 작업환경을 먼저 준비해뒀다.

작업 계정 만들기 Ex 

# 모든 DB의 모든 테이블에 대한 모든 권한을 sbsst 라는 계정에게 부여하고, 그 계정의 비번을 sbsst123414 로 한다.
DROP USER sbsst;
CREATE USER sbsst@`%` IDENTIFIED BY 'sbsst123414';
GRANT ALL PRIVILEGES ON *.* TO sbsst@`%`;

# 잘 만들어 졌는지 확인
SELECT *
FROM mysql.user
WHERE `User` = 'sbsst';

Todo 데이터베이스 만들기

1. sql 편집기 새로만들기

New script로 편집기 생성

 

2. Todo 데이터베이스 생성 및 조회

# 전체 데이터베이스 조회
SHOW DATABASES;

# 기존에 todo 데이터베이스가 존재 한다면 삭제 
DROP DATABASE IF EXISTS `todo`;

# 새 데이터베이스 todo 생성
CREATE DATABASE todo;

# todo 데이터 베이스 선택
USE todo;

# 테이블 조회
SHOW TABLES;

 

Dbevar sql문 실행법 

실행하고자하는 query문 영역에 커서를 둔상태에서

좌측상단의 > 세모버튼을 클릭하거나

or

control + enter키 누름(윈도우,맥둘다가능) 나는 이방법을 더선호함.

 

테이블 만들기

# 전체 데이터베이스 조회
SHOW DATABASES;

# 기존에 todo 데이터베이스가 존재 한다면 삭제 
DROP DATABASE IF EXISTS `todo`;

# 새 데이터베이스 todo 생성
CREATE DATABASE todo;

# todo 데이터 베이스 선택
USE todo;

# 테이블 조회
SHOW TABLES;

# 할일 테이블 생성
CREATE TABLE todo (
	id int	PRIMARY KEY NOT NULL AUTO_INCREMENT,
	contents	varchar(100)	NOT NULL,
	completed	boolean	NOT NULL DEFAULT FALSE
);

# 회원 테이블생성
CREATE TABLE member (
	id	int	PRIMARY KEY NOT NULL AUTO_INCREMENT,
	name	varchar(10)	NOT NULL,
	password	varchar(20)	NOT NULL UNIQUE,
	callnum 	varchar(13)	NOT NULL
);

# 할일_회원 테이블 생성 할일,회원테이블의 매핑테이블
CREATE TABLE todo_member (
   member_id INT NOT NULL,
   todo_id INT NOT NULL,
   FOREIGN KEY (member_id) REFERENCES member(id) ON DELETE CASCADE,
   FOREIGN KEY (todo_id) REFERENCES todo(id) ON DELETE CASCADE
);

-- 회원 가입
INSERT INTO member (name, password, callnum) VALUES ('사용자명', '패스워드', '전화번호');

-- 할일 등록
INSERT INTO todo (contents, completed) VALUES ('할일 내용', FALSE);

-- 할일_회원 매핑
INSERT INTO todo_member (member_id, todo_id) VALUES (1, 2);

-- 할일 수정
UPDATE todo SET contents = '수정된 내용' WHERE id = 1;

-- 할일 삭제
DELETE FROM todo WHERE id = 1;

-- 회원별 작성한 할일 컨텐츠 조회
SELECT member.name AS 회원이름, todo.contents AS 할일
FROM member
INNER JOIN todo_member ON member.id = todo_member.member_id
INNER JOIN todo ON todo_member.todo_id = todo.id;

-- 특정 회원의 작성한 할일 컨텐츠 조회
SELECT member.name AS 회원이름, todo.contents AS 할일내역
FROM member
JOIN todo_member ON member.id = todo_member.member_id
JOIN todo ON todo_member.todo_id = todo.id
WHERE member.name = '사용자명';

예시 표

매핑테이블(todo_member)의

todo_id칼럼은 todo 테이블의 id를 참조하고있어야하고

member_id칼럼은 member 테이블의 id를 참조하고있어야된다.

이를 외래키라고 한다.