[SQL 첫걸음] 32강 - 36강

32강 : 테이블 결합

테이블 결합

여러 개로 나뉜 데이터를 하나로 묶어 결과를 내는 방법

 

1. 곱집합과 차집합

교차결합 Cross Join

- FROM 구에 복수의 테이블 지정 or CROSS JOIN 사용

mysql> SELECT * FROM sample72_x;
+------+
| x    |
+------+
| A    |
| B    |
| C    |
+------+
3 rows in set (0.03 sec)

mysql> SELECT * FROM sample72_y;
+------+
| y    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.01 sec)

# SELECT * FROM 테이블명1, 테이블명2
mysql> SELECT * FROM sample72_x, sample72_y;
+------+------+
| x    | y    |
+------+------+
| C    |    1 |
| B    |    1 |
| A    |    1 |
| C    |    2 |
| B    |    2 |
| A    |    2 |
| C    |    3 |
| B    |    3 |
| A    |    3 |
+------+------+
9 rows in set (0.00 sec)

# SELECT * FROM 테이블명1 CROSS JOIN 테이블명2
mysql> SELECT * FROM sample72_x CROSS JOIN sample72_y;
+------+------+
| x    | y    |
+------+------+
| C    |    1 |
| B    |    1 |
| A    |    1 |
| C    |    2 |
| B    |    2 |
| A    |    2 |
| C    |    3 |
| B    |    3 |
| A    |    3 |
+------+------+
9 rows in set (0.00 sec)

 

UNION 연결과 결합 연결의 차이

- UNION : 세로 방향으로 더해짐

- FROM : 가로 방향으로 더해짐

 

2. 내부결합

# 상품 테이블 작성하기
CREATE TABLE 상품(
	상품코드 CHAR(4) NOT NULL,
    상품명 VARCHAR(30),
    메이커명 VARCHAR(30),
    가격 INTEGER,
    상품분류 VARCHAR(30),
    PRIMARY KEY (상품코드)
);

# 재고 수 테이블 작성하기
CREATE TABLE 재고수 (
	상품코드 CHAR(4),
    입고날짜 DATE,
    재고수 INTEGER
);

# 상품 테이블과 재고수 테이블을 교차결합하기
mysql> SELECT * FROM 상품, 재고수;
+----------+--------+----------+------+----------+----------+------------+--------+
| 상품코드 | 상품명 | 메이커명 | 가격 | 상품분류 | 상품코드 | 입고일     | 재고수 |
+----------+--------+----------+------+----------+----------+------------+--------+
| 0003     | 상품3  | 메이커3  | 1980 | 생활용품 | 0001     | 2014-01-03 |    200 |
| 0002     | 상품2  | 메이커2  |  200 | 식료품   | 0001     | 2014-01-03 |    200 |
| 0001     | 상품1  | 메이커1  |  100 | 식료품   | 0001     | 2014-01-03 |    200 |
| 0003     | 상품3  | 메이커3  | 1980 | 생활용품 | 0002     | 2014-02-10 |    500 |
| 0002     | 상품2  | 메이커2  |  200 | 식료품   | 0002     | 2014-02-10 |    500 |
| 0001     | 상품1  | 메이커1  |  100 | 식료품   | 0002     | 2014-02-10 |    500 |
| 0003     | 상품3  | 메이커3  | 1980 | 생활용품 | 0003     | 2014-02-14 |     10 |
| 0002     | 상품2  | 메이커2  |  200 | 식료품   | 0003     | 2014-02-14 |     10 |
| 0001     | 상품1  | 메이커1  |  100 | 식료품   | 0003     | 2014-02-14 |     10 |
+----------+--------+----------+------+----------+----------+------------+--------+
9 rows in set (0.02 sec)

# 상품코드가 같은 행을 검색하기
mysql> SELECT * FROM 상품, 재고수
    -> WHERE 상품.상품코드=재고수.상품코드;
+----------+--------+----------+------+----------+----------+------------+--------+
| 상품코드 | 상품명 | 메이커명 | 가격 | 상품분류 | 상품코드 | 입고일     | 재고수 |
+----------+--------+----------+------+----------+----------+------------+--------+
| 0001     | 상품1  | 메이커1  |  100 | 식료품   | 0001     | 2014-01-03 |    200 |
| 0002     | 상품2  | 메이커2  |  200 | 식료품   | 0002     | 2014-02-10 |    500 |
| 0003     | 상품3  | 메이커3  | 1980 | 생활용품 | 0003     | 2014-02-14 |     10 |
+----------+--------+----------+------+----------+----------+------------+--------+
3 rows in set (0.01 sec)

# 검색할 행과 반환할 열 제한하기
mysql> SELECT 상품.상품명, 재고수.재고수 FROM 상품, 재고수
    -> WHERE 상품.상품코드=재고수.상품코드
    -> AND 상품.상품분류='식료품';
+--------+--------+
| 상품명 | 재고수 |
+--------+--------+
| 상품1  |    200 |
| 상품2  |    500 |
+--------+--------+
2 rows in set (0.00 sec)

 

3. INNER JOIN으로 내부결합하기

최근 많이 사용하는 두 개의 테이블 가로 결합 방식

mysql> SELECT 상품.상품명, 재고수.재고수
    -> FROM 상품 INNER JOIN 재고수
    -> ON 상품.상품코드=재고수.상품코드
    -> WHERE 상품.상품분류='식료품';
+--------+--------+
| 상품명 | 재고수 |
+--------+--------+
| 상품1  |    200 |
| 상품2  |    500 |
+--------+--------+
2 rows in set (0.00 sec)

 

4. 내부결합을 활용한 데이터 관리

 

# 메이커 테이블 작성하기
CREATE TABLE 메이커 (
	메이커코드 CHAR(4) NOT NULL,
    메이커명 VARCHAR(30),
    PRIMARY KEY (메이커코드)
);

# 상품 테이블과 메이커 테이블을 내부결합하기
mysql> SELECT S.상품명, M.메이커명
    -> FROM 상품2 S INNER JOIN 메이커 M
    -> ON S.메이커코드=M.메이커코드;
+--------+----------+
| 상품명 | 메이커명 |
+--------+----------+
| 상품1  | 메이커1  |
| 상품2  | 메이커1  |
| 상품3  | 메이커2  |
+--------+----------+
3 rows in set (0.02 sec)

 

자기결합

- 테이블에 별명을 붙일 수 있는 기능을 이용해 같은 테이블끼리 결합하는 것

mysql> SELECT S1.상품명, S2.상품명
    -> FROM 상품 S1 INNER JOIN 상품 S2
    -> ON S1.상품코드=S2.상품코드;
+--------+--------+
| 상품명 | 상품명 |
+--------+--------+
| 상품1  | 상품1  |
| 상품2  | 상품2  |
| 상품3  | 상품3  |
+--------+--------+
3 rows in set (0.00 sec)

 

5. 외부결합

어느 한 쪽에만 존재하는 데이터행을 어떻게 다룰지 변경하는 결합 방법

기준이 되는 테이블의 위치에 따라 LEFT JOIN이나 RIGHT JOIN 사용

# 상품3 테이블에 상품코드가 0009인 행을 추가해도 재고수 테이블엔 없어서 내부결합에서는 상품코드가 0009인 상품이 제외된다
mysql> SELECT 상품3.상품명, 재고수.재고수
    -> FROM 상품3 INNER JOIN 재고수
    -> ON 상품3.상품코드=재고수.상품코드
    -> WHERE 상품3.상품분류='식료품';
+--------+--------+
| 상품명 | 재고수 |
+--------+--------+
| 상품1  |    200 |
| 상품2  |    500 |
+--------+--------+
2 rows in set (0.01 sec)

# 외부결합으로 상품코드 0009인 상품도 결과에 포함하기
mysql> SELECT 상품3.상품명, 재고수.재고수
    -> FROM 상품3 LEFT JOIN 재고수
    -> ON 상품3.상품코드=재고수.상품코드
    -> WHERE 상품3.상품분류='식료품';
+----------+--------+
| 상품명   | 재고수 |
+----------+--------+
| 상품1    |    200 |
| 상품2    |    500 |
| 추가상품 |   NULL |
+----------+--------+
3 rows in set (0.00 sec)

 

33강 : 관계형 모델

1. 관계형 모델 Relational Model

릴레이션 Relation

- 관계형 모델의 기본적인 요소

- 관계형 모델의 릴레이션은 SQL에서 말하는 테이블에 해당

 

속성 attribute

- SQL에서 말하는 열에 해당

- 속성 이름과 형 이름으로 구성됨

 

튜플tuple

- SQL에서의 행에 해당

 

관계대수

- 릴레이션은 튜플의 집합이며, 릴레이션에 대한 연산이 집합의 대한 연산에 대응된다는 이론

- 관계 대수의 기본 규칙

    - 하나 이상의 관계를 바탕으로 연산한다.

    - 연산한 결과, 반환되는 것 또한 관계이다.

    - 연산을 중첩 구조로 실행해도 상관없다.

 

2. 관계형 모델과 SQL

관계대수에서는 자주 사용될 것 같은 릴레이션의 연산 방법을 몇 가지 규정하고 있음

 

합집합 union

- 릴레이션끼리의 덧셈

- SQL의 UNION에 해당

 

차집합 difference

- 릴레이션끼리의 뺄셈

- SQL의 EXCEPT에 해당

 

교집합 intersection

- 릴레이션끼리의 공통부분(교집합)

- SQL의 INTERSECT에 해당

 

곱집합 cartesian product

- 릴레이션끼리의 대전표를 조합하는 연산

- SQL의 FROM 구에 복수의 테이블을 지정한 경우 or CROSS JOIN에 해댱

 

선택 selection

- 튜플의 추출

- 제한이라 불리기도 함

- 튜플은 SQL의 행이므로 WHERE 구에 조건 지정하여 데이터를 검색하는 것에 해당

 

투영 projection

- 속성의 추출

- SQL의 열이므로 SELECT 구에 결과로 반환할 열을 지정하는 것에 해당

 

결합 join

- 릴레이션끼리 교차결합해 계산된 곱집합에서 결합조건을 만족하는 튜플을 추출하는 연산

- SQL에서는 내부결합에 해당

- 관계대수에도 내부결합과 외부결합이 존재함

 

8장 : 데이터베이스 설계

34강 : 데이터베이스 설계

1. 데이터베이스 설계

데이터베이스의 스키마 내에 테이블, 인덱스, 뷰 등의 데이터베이스 객체를 정의하는 것

스키마 내에 정의한다는 뜻에서 '스키마 설계'로 불리기도 함

 

논리명과 물리명

- 테이블 설계 시 테이블 정의서 혹은 설계도 등 작성

- 테이블 정의서 예시 ex. 상품 테이블

열명(물리명)	논리명	자료형	NULL	기본값	비고
item_code	상품코드	CHAR(4)	No
item_name	상품명	VARCHAR(30)	Yes
price	가격	INTEGER	Yes

- 물리명 : 데이터베이스에서 사용될 이름, CREATE TABLE에 실제로 지정하는 이름 ex. item_master

- 논리명 : 테이블의 '설계상 이름' ex. 상품 마스터

 

자료형

- 테이블의 열에 지정해야함

- 'yes, no 중에 하나', 혹은 '1, 2, 3 중에 하나' 등은 데이터베이스 기능으로 제약(CHECK 제약)을 걸 수 있음

- 데이터베이스 시스템에서 데이터 정합성 체크가능할 땐 데이터베이스에서 처리하는 게 제일 확실함

 

고정길이와 가변길이

- 문자열의 자료형 종류

- 고정길이 : 자리수가 이미 정해져 있는 경우

- 가변길이 : 자리수가 변동폭이 큰 경우

- VARCHAR < LOB(Large Object :인덱스 지정 불가)

 

기본키

- 마땅한 열이 없을 경우 자동증가 열 사용을 추천(INSERT 시 자동으로 번호 증가)

- AUTO_INCREMENT 지정하여 자동증가 열을 만들 수 있음

 

2. ER 다이어그램

ER : Entity Relationship

'설계상 이렇게 연관 되어 있다'를 나타내는 역할

개체 간의 관계 표현(Relationship)

Entity(개체) : 테이블 또는 뷰

가장 기본적인 관계 표기법

- 일대일 1:1

- 일대다 1:多

- 다대다 多:多

 

35강 : 정규화

1. 정규화

테이블을 올바른 형태로 변경하고 분할하는 것

데이터베이스의 설계 단계에서 주로 행해지나 기존 시스템 재검토 시 정규화하기도 함

효율적인 데이터베이스 설계에 도움이 됨

ex. 고객 주문 테이블

주문번호	날짜	성명	연락처	주문상품
1	1/1	박준용	010 XXX	0001 OO 1개, 0002 XX 10개
2	2/1	김재진	010 XXX	0001 OO 2개, 0002 XX 3개
3	2/5	박준용	010 XXX	0001 OO 3개, 0003 xx 1개

 

1. 제1 정규형

반복되는 데이터를 가로(열 방향)가 아닌 세로(행 방향)로 늘리고, 중복을 제거하는 등 테이블 분할 및 기본키 지정

ex. 주문 테이블(주문번호: 기본키)

주문번호	날짜	성명	연락처
1	1/1	박준용	010 XXX
2	2/1	김재진	010 XXX
3	2/5	박준용	010 XXX

ex.주문상품 테이블 (주문상품 -> 상품코드, 상품명, 개수로 분할)

주문번호	상품코드	상품명	개수
1	0001	OO	1
1	0002	XX	10
2	0001	OO	2
2	0002	XX	3
3	0001	OO	3
3	0003	xx	1

 

2. 제2정규형

데이터가 중복하는 부분을 찾아 테이블 분할, 이때 기본키에 의해 특정되는 열과 그렇지 않은 열로 나눔

ex. 주문상품 테이블(개수: 기본키로 특정)

주문번호	상품코드	개수
1	0001	1
1	0002	10
2	0001	2
2	0002	3
3	0001	3
3	0003	1

ex. 상품 테이블(상품명: 기본키의 일부로 특정)

상품코드	상품명
0001	OO
0002	XX
0003	xx

 

4. 제3정규형

기본키 이외의 부분에 중복 검사 후 테이블 분할

ex. 주문 테이블(같은 사람이 반복해서 주문)

주문번호	날짜	고객번호
1	1/1	1
2	2/1	2
3	2/5	1

ex. 고객 테이블(같은 사람이 반복해서 주문)

고객번호	성명	연락처
1	박준용	010 XXX
2	김재진	010 XXX

 

-> 주문상품 테이블, 상품 테이블, 주문 테이블, 고객 테이블 총 4개로 분할됨

 

5. 정규화의 목적

하나의 데이터는 한 곳에 저장되어야 함

-> 데이터를 변경하더라도 한 곳만 변경하는 것으로 끝낼 수 있음

-> 기본키가 변경될 일은 거의 음

=> 정규화를 통해 테이블에 대한 인덱스의 재구축을 억제할 수 있음

 

36강 : 트랜잭션

1. 트랜잭션

데이터베이스에서 제공하는 기능

INSERT나 UPDATE 명령으로 데이터를 추가, 갱신할 때도 트랜잭션 기능을 사용함

자동 커밋이 동작했기 때문에 의식할 필요 X

'SQL을 통해 이루어지는 사용자가 원하는 작업 단위'로 여러 단계로 나뉘어 이루어짐

가령, 위의 4개의 테이블을 기준으로 주문이 추가되었을 때, INSERT 명령이 주문 테이블과 주문상품 테이블에 이루어져야 함. 하지만 주문 테이블에는 정상실행한 INSERT 명령이 주문상품 테이블에서 에러가 발생했을 때, DELETE 명령으로 정상 추가된 데이터를 삭제해야 함

 

2. 롤백과 커밋

롤백 rollback

트랜잭션 내에서 행해진 모든 변경사항을 없었던 것으로 할 수 있음

 

커밋 commit

아무런 에러가 발생하지 않을 시엔 변경사항을 적용하고 트랜잭션을 종료함

 

자동커밋

트랜잭션을 사용해서 데이터 추가 시엔 자동커밋을 꺼야함

MySQL 클라이언트에서 명령 실행시엔 자동커밋이 켜져있음

자동커밋을 끄기 위해선 명시적으로 트랜잭션 시작을 선언해야 함

# 트랜잭션 시작
START TRANSACTION

# 트랜잭션 내에서 실행한 명령을 적용 후 종료
COMMIT

# 트랜잭션 내에서 실행한 명령을 파기 후 종료
ROLLBACK

 

PostgreSQL이나 SQL Server에서는 'START TRANSACTION' 대신 'BEGIN TRANSACTION' 명령 사용