#혼공학습단 #혼공 #혼자공부하는SQL #혼공S
본 글은 2023년 7월 3일 ~ 2023년 8월 20일까지 진행하는 혼공학습단에 참여하며 정리한 글입니다.
02-1 건물을 짓기 위한 설계도: 데이터베이스 모델링
•
데이터베이스 모델링(database modeling) : 테이블의 구조를 미리 설계하는 개념
•
프로젝트 진행시 대표적으로 폭포수 모델 (waterfall model)을 사용 → 테이블 구조가 결정
프로젝트 진행 단계
소프트웨어 개발 절차 중 하나인 폭포수 모델(waterfall model)방식을 살펴보자.
1.
프로젝트 계획
2.
업무 분석
3.
시스템 설계
4.
프로그램 구현
5.
테스트
6.
유지보수
데이터베이스 모델링
데이터베이스 모델링이란 우리가 살고 있는 세상에서 사용되는 사물이나 작업을 DBMS의 데이터베이스 개체로 옮기기 위한 과정이라고 할 수 있다. 현실에서 쓰이는 것을 테이블로 변경하기 위한 작업.
전체 데이터베이스 구성도
데이터베이스 구성에서 나오는 용어를 정리한다.
•
데이터 : 단편적인 정보
•
테이블 : 데이터를 입력하기 위한 표 형태의 자료
•
데이터베이스 : 데이터의 저장소
•
데이터베이스 관리 시스템 : 데이터베이스를 관리하는 시스템 또는 소프트웨어
•
열 : 각 열을 구분하기 위한 이름
•
데이터 형식 : 열에 저장될 데이터의 형식
•
행 : 테이블의 가로. 실질적인 진짜 데이터 (행데이터라고도 부름)
•
기본 키 : 각 행을 구분하는 유일한 열
02-2 데이터베이스 시작부터 끝까지
•
데이터베이스 구축 절차
1.
데이터베이스 만들기 → 2. 테이블 만들기 → 3. 데이터 입력/수정/삭제 → 4. 데이터 조회/활용
데이터베이스 만들기
(이 책에서는 MySQL Workbench 를 이용해 실습을 수행한다.)
먼저 [SCHEMA] 탭에서 마우스 오른쪽 클릭을 하여 ‘Create Schema..’를 클릭해 데이터베이스를 만들자. 데이터베이스 이름은 shop_db . SQL 코드로는 다음과 같다.
CREATE SCHEMA 'shop_db';
SQL
복사
테이블 만들기
이번 실습에서 사용할 테이블은 2개이다. 쇼핑몰 데이터베이스 안에 회원 테이블과 제품 테이블이다.
•
테이블 설계하기
테이블을 생성하기 위해서는 설계가 필요하다. 열 이름과 데이터 형식을 지정해야 한다.
회원 테이블 설계
열 이름(한글) | 영문 이름 | 데이터 형식 | 최대 길이 | 널 허용 안함(Not Null) |
아이디(기본 키) | member_id | 문자(CHAR) | 8글자 | Yes |
회원 이름 | member_name | 문자(CHAR) | 5글자 | Yes |
주소 | member_addr | 문자(CHAR) | 20글자 | No |
제품 테이블 설계
열 이름(한글) | 영문 이름 | 데이터 형식 | 문자 최대 길이 | 널 허용 안함(Not Null) |
제품 이름(기본키) | product_name | 문자(CHAR) | 4글자 | Yes |
가격 | cost | 숫자(INT) | Yes | |
제조일자 | make_date | 날짜(DATE) | No | |
제조회사 | company | 문자(CHAR) | 5글자 | No |
남은 수량 | amount | 숫자(INT) | Yes |
•
테이블 생성하기
테이블 생성도 Workbench 안에서 진행한다. shop_db 데이터베이스에서 마우스 오른쪽 클릭한 후에 ‘Create Table…’을 클릭한다.
회원 테이블명은 member, 제품 테이블명은 product 이다.
위에서 테이블 설계한대로 테이블을 만들어 준다.
CREATE TABLE `shop_db`.`member` (
`member_id` CHAR(8) NOT NULL,
`member_name` CHAR(5) NOT NULL,
`member_addr` CHAR(20) NULL,
PRIMARY KEY (`member_id`));
SQL
복사
CREATE TABLE `shop_db`.`product` (
`product_name` CHAR(4) NOT NULL,
`cost` INT NOT NULL,
`make_date` DATE NULL,
`company` CHAR(5) NULL,
`amount` INT NOT NULL,
PRIMARY KEY (`product_name`));
SQL
복사
데이터 입력하기
회원 테이블에는 4건, 제품 테이블에는 3건의 데이터를 입력한다.
회원 테이블
INSERT INTO `shop_db`.`member` (`member_id`, `member_name`, `member_addr`) VALUES ('tess', '나훈아', '경기 부천시 중동');
INSERT INTO `shop_db`.`member` (`member_id`, `member_name`, `member_addr`) VALUES ('hero', '임영웅', '서울 은평구 증산동');
INSERT INTO `shop_db`.`member` (`member_id`, `member_name`, `member_addr`) VALUES ('iyou', '아이유', '인천 남구 주안동');
INSERT INTO `shop_db`.`member` (`member_id`, `member_name`, `member_addr`) VALUES ('jyp', '박진영', '경기 고양시 장항동');
SQL
복사
제품 테이블
INSERT INTO `shop_db`.`product` (`product_name`, `cost`, `make_date`, `company`, `amount`) VALUES ('바나나', '1500', '2021-07-01', '델몬트', '17');
INSERT INTO `shop_db`.`product` (`product_name`, `cost`, `make_date`, `company`, `amount`) VALUES ('삼각김밥', '800', '2023-09-01', 'CJ', '22');
INSERT INTO `shop_db`.`product` (`product_name`, `cost`, `make_date`, `company`, `amount`) VALUES ('카스', '2500', '2022-03-01', 'OB', '3');
SQL
복사
데이터 활용하기
1.
먼저 회원 테이블의 모든 행을 조회하기 위해 다음의 SQL 문을 입력한다.
SELECT * FROM member;
SQL
복사
2.
회원 테이블 중에 이름과 주소만 출력해보기
SELECT member_name, member_addr FROM member;
SQL
복사
3.
아이유 회원에 대한 정보만 추출해보자. WHERE 절
SELECT * FROM member WHERE member_name='아이유';
SQL
복사
02-3 데이터베이스 개체
인덱스
인덱스는 데이터가 많아질수록 조회 시간을 줄여줄 수 있도록 한다.
•
인덱스 개념 이해하기
인덱스란 “찾아보기”, “부록”과 같은 개념이다. 수천만~수억 건 이상의 데이터를 처리할 때 인덱스 없이 전체 데이터를 찾아보는 것은 불가능에 가까운 일이다.
•
인덱스 실습하기
현재 회원 테이블에는 인덱스가 따로 설정되어 있지 않다. 테이블에서 ‘아이유’ 회원을 찾는다면 회원 테이블의 1행부터 끝까지 전체를 살펴볼 것이다. 다음 SQL 문을 실행시켜 보자.
SELECT * FROM member WHERE member_name='아이유';
SQL
복사
결과는 당연히 다음과 같이 나오겠지만, 우리가 여기서 살펴봐야 하는 것은 실행계획(Execution Plan)이다.
•
실행계획: Execution Plan
아래 그림을 보면, Full Table Scan이라고 나온다. 이는 전체 테이블 검색한다는 뜻이다. 처음부터 끝까지 테이블을 살펴보면서 데이터를 찾은 것이다. 현재는 인덱스가 없기 때문에 별다른 방법은 없다.
•
인덱스 생성하기
다음 SQL 문을 실행하면 인덱스가 생성된다. SQL의 마지막에 ON member(member_name)가 member 테이블의 member_name 열에 인덱스를 지정하라는 의미이다.
CREATE INDEX idx_member_name ON member(member_name);
SQL
복사
인덱스를 걸고나서 아이유를 다시 찾아보면, 실행계획이 달라진 것을 확인할 수 있다.
Non-Unique Key Lookup: Key Lookup을 인덱스를 통해 결과를 찾았다는 의미 → 인덱스 검색 (Index Scan) - 자세한 내용은 6장에서 계속.
뷰
뷰는 테이블과 비슷한 성격의 데이터베이스 개체이다. 뷰를 활용하면 보안도 강화하고 SQL 문도 간단하게 사용할 수 있다.
•
뷰 개념 이해하기
뷰를 한마디로 정의하면 ‘가상의 테이블’이라고 할 수 있다. 일반 사용자의 입장에서는 테이블과 뷰를 구분할 수 없다. 즉 일반 사용자는 테이블과 뷰를 동일하게 취급하면 된다. 다만 뷰는 실제 데이터를 가지고 있지 않으며 진짜 테이블에 링크된 개념이라고 생각하면 된다.
◦
뷰는 ‘바로 가기 아이콘’과 같은 개념
•
뷰 실습하기
기본적인 뷰를 만들기 위해 다음 SQL 문을 실행시켜 보자.
CREATE VIEW member_view
AS
SELECT * FROM member;
SQL
복사
그리고 회원 뷰(member_view)를 살펴보면,
SELECT * FROM member_view;
SQL
복사
결과는 회원 테이블을 그냥 본 것과 같고, 다만 위에서 만든 member_view 회원 뷰를 조회한 것이다.
◦
뷰의 장점
1.
보안에 도움이 된다.
2.
긴 SQL 문을 간략하게 만들 수 있다.
스토어드 프로시저
스토어드 프로시저를 통해 SQL 안에서도 일반 프로그래밍 언어처럼 코딩 할 수 있다.
•
스토어드 프로시저 개념 이해하기
스토어드 프로시저란 MySQL에서 제공하는 프로그래밍 기능으로, 여러 개의 SQL문을 하나로 묶어서 편리하게 사용할 수 있다.
•
스토어드 프로시저 실습하기
다음 두 SQL을 입력하고 한꺼번에 실행하자. 이렇게 되면 두 개의 결과 탭이 되면서 조회가 된다. (불편..)
SELECT * FROM member WHERE member_name = '나훈아';
SELECT * FROM product WHERE product_name = '삼각김밥';
SQL
복사
이제 스토어드 프로시저를 만들어보자. 스토어드 프로시저는 첫 행과 마지막 행에 DELIMITER// ~DELIMITER; 를 추가함으로써 스토어드 프로시저를 묶어주는 문법이다. 그리고 BEGIN과 END사이에 SQL문을 넣으면 된다.
DELIMITER //
CREATE PROCEDURE myProc()
BEGIN
SELECT * FROM member WHERE member_name='나훈아';
SELECT * FROM product WHERE product_name='삼각김밥';
END //
DELIMITER ;
SQL
복사
이렇게 되면 이제 스토어드 프로시저를 호출하기만 하면 된다.
CALL myProc();
SQL
복사
[혼공학습단 10기 미션]
기본미션
p. 80의 shop_db의 회원 테이블(member)에서 아이유 회원에 대한 정보만 추출한 후 결과 화면 인증하기
선택미션
데이터베이스 개체 3가지 설명하기
•
인덱스 : 테이블의 조회 시간을 단축하기 위해 열을 기준으로 색인하는 기술
•
뷰 : 사용자에게 접근이 허용된 데이터만 제한적으로 보여주기 위한 가상테이블. 보안 및 SQL 문을 간략하게 할 수 있다.
•
스토어드 프로시저 : 일련의 쿼리를 하나의 함수처럼 실행하기 위한 쿼리 집합