SQL 첫걸음 7~8장

Tamszero·2025년 4월 10일

sql 데이터베이스

ECC

목록 보기

5/17

7장

집합 연산

복수의 테이블을 사용해 데이터를 검색하는 법을 알아보자

SQL과 집합

DB에서는 테이블의 행이 집합 요소에 해당한다.
수치상으로는 복수의 값이 존재하지만, 집합의 요소라는 측면에서 보면 하나의 행이 곧 하나의 요소가 됨.
select명령어 질의를 통해 반환된 결과 전체를 하나의 집합이라고 보면 된다.

UNION으로 합집합 구하기

두개의 select 명령을 union해서 합집합 구하기

서로 열의 개수와 자료형이 같기때문에 가능
나열 순서는 order by를 지정하지 않은 경우 명령어 순서에 의해 달라질 수 있음
UNION을 사용할 때의 ORDER BY
마지막 select 명령에만 지정해야함
열 이름을 맞춘 뒤 ORDER BY를 해야 오류가 안 남
UNION ALL
중복값을 제거하지 않고 합치고 싶을 때

교집합과 차집합

교집합은 INTERSECT, 차집합은 EXCEPT

테이블 결합

곱집합과 교차결합

곱집합은 두개의 집합을 곱하는 연산 방법으로 적집합 또는 카티전곱 이라고도 불림

교차결합
select * from 테이블명1, 테이블명2

FROM구에 복수의 테이블을 지정하면 교차결합을 한다!
UNION과 결합의 차이
UNION은 세로방향으로만 더해지고 결합으 가로(열) 방향으로 확대되어 더해진다

내부 결합

상품 테이블과 재고수 테이블 교차결합하기

이렇게 만들어진 집합에서 원하는 데이터를 검색하기 위해 where구로 조건을 지정하기 위해선 먼저 상품 코드가 같다는 조건이 필요하다.
상품코드가 같은 행을 검색하기

교차결합으로 계산된 곱집합에서 원하는 조합을 검색하는 것을 '내부결합'이라고 부른다
검색할 행과 반환할 열 제한하기

INNER JOIN으로 내부 결합하기

상품 테이블과 재고수 테이블을 이용한 사례를 INNER JOIN을 활용해 바꾸어보자

내부결합
select * from 테이블명1 INNER JOIN 테이블명2 ON 결합조건

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

상품 테이블과 메이커 테이블을 내부결합하기
외부키
메이커 테이블의 메이커코드는 기본키이다. 상품테이블의 메이커코드는 '외부키'라고 부르는 것으로 다른 테이블의 기본키를 참조하는 열이 외부키가 된다.
자기결합
테이블에 별명을 붙일 수 있는 기능을 이용해 같은 테이블끼리 결합하는 것

외부결합

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

상품 테이블에 새로운 상품 추가(상품코드 0009추가)
재고수 테이블에는 아직 이 상품의 데이터가 없다
내부결합에서는 상품코드가 0009인 상품 제외
외부결합으로 상품코드 0009인 상품도 결과에 포함하기

기준이 되는 상품 테이블을 JOIN의 왼쪽에 기술했으므로 LEFT JOIN이라 지정
상품테이블을 오른쪽에 지정하는 경우나 재고 테이블을 기분으로 삼을땐 RIGHT JOIN
LEFT/RIGHT JOIN으로 외부결합을 할 수 있다!

관계형 모델

관계형 모델의 기본적인 요소는 relation이다.
관계형 모델의 릴레이션은 SQL에서 말하는 테이블에 해당.
relation은 테이블을 말한다!
릴레이션은 튜플의 집합이며, 릴레이션에 대한 연산이 집합 연산에 대응된다는 이론을 '관계대수'라고 한다

관계대수의 기본 규칙

관계형 모델과 SQL

합집합
릴레이션끼리의 덧셈, SQL에서는 UNION에 해당
차집합
릴레이션끼리의 뺄셈, SQL에서는 EXCEPT에 해당
교집합
릴레이션끼리의 교집합, SQL에서는 INTERSECT에 해당
곱집합
릴레이션끼리의 대전표 조합, SQL에서는 FROM구에 복수 테이블 지정에 해당
또한 CROSS JOIN으로 교차결합을하여 곱집합을 구할 수도 있다.
선택
튜플의 추출, where구에 조건을 지정해 데이터를 검색하는 것에 해당
투영
속성의 추출, select구에 결과로 반환할 열을 지정하는 것에 해당
결합
릴레이션끼리의 곱집합에서 결합조건을 만족하는 튜플을 추출하는 연산, SQL에서는 내부결합에 해당

8장

데이터베이스 설계

데이터베이스의 설계

데이터베이스의 스키마에 테이블, 인덱스, 뷰 등의 객체를 정의하는 것.
주로 테이블의 이름이나 열, 자료형을 결정하는 것이다.

논리명과 물리명
물리명 : CREATE TABLE에 지정하는 이름을 말한다
논리명 : 테이블의 '설계상 이름'에 해당한다
물리명은 잘못 정하면 변경하기 힘들지만 논리명은 언제나 바꿀 수 있다.

자료형
수치형을 문자형으로 변환 -> 문제X
문자열을 수치형으로 변환 -> 에러 발생
일반적으로 데이터베이스 시스템에서 데이터 정합성을 체크 가능하다면 데베에 맡기자
고정길이와 가변길이
제조번호처럼 자리수가 이미 정해져있는 경우 -> 고정길이
비고란과 같이 입력되는 문자열의 변동폭이 클 경우 -> 가변길이
VARCHAR형은 최대 수천바이트 -> 큰 파일은 LOB형을 사용한다
기본키
기본키로 지정할 열이 생각나지 않는 경우 -> 자동증가 열을 이용하여 기본키로 지정한다
자동증가 열은 INSERT를 할 경우 번호를 자동으로 증가시킴

#MySQL의 경우
AUTO_INCREMENT
#로 열을 지정하면 됨

ER다이어그램

E : 개체를 뜻하는 Entity의 약자(여기서 개체란 테이블 또는 뷰)
R : Relationship의 약자
-> 객체 간의 관계를 표현한다

ER다이어그램에서 개체는 사각형으로 표현한다
사각형 상단 -> 개체의 이름
사각형 안 -> 개체의 속성(테이블의 열)

개체와 개체가 서로 연계되는 경우에는 선으로 이어서 표현한다.

연계를 표기할 때는 몇 대 몇 관계를 가지는지 나타앨 수 있다.

정규화

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

정규화

아래의 주문 시스템 테이블을 정규화 해보자

제1정규화

관계형 DB의 테이블에는 하나의 셀에 하나의 값만 저장할 수 있다.
먼저 주문상품 데이터를 상품코드,상품명,개수로 분할하자

하나의 셀에 하나의 값만 저장하고 반복되는 부분은 세로방향으로 늘리는 것이 바로 제1정규화 단계이다
이때 동일한 값을 가지는 행이 여러 개 존재하지 않도록한다(아래 그림에서 굵은 선 부분)
먼저 주문테이블을 주문상품 테이블과 주문 테이블로 나눈다

-> 반복되는 부분이 하나로 정리되었다 -> 주문 데이터가 변경되더라도 한 군데만 수정하면 됨
분할 이후의 주문 테이블에서 주문번호는 중복값X므로 기본키로 지정이 가능하다
제1정규화에서는 테이블의 분할과 기본키 지정이 이루어진다!

제2정규형

기본키에 의해 특정되는 열과 그렇지 않은 열로 나누는 것으로 제2정규화가 이루어진다.

주문번호 기본키를 바탕으로 보면 주문번호1에 상품코드가 0001인 상품의 주문량은 총 1개이다
즉 개수 열은 기본키가 결정되고 나면 특정할 수 있다.
하지만 상품명은 주문번호와 관계없이 상품코드만드로 특정이 가능하다.
(상품코드는 기본키의 일부지만 단독으로의 역할은 X)
따라서 테이블을 두가지로 분할한다

이처럼 부분 함수종속성을 찾아서 테이블을 분할하는 것이 제2정규화이다.
함수종속성이란? 키 값을 이용해 데이터를 특정지을 수 있는 것

제3정규형

이 또한 중복을 찾아내 분할하는 수법이다.
제3정규화에서는 기본키 이외의 부분에서 중복이 없는지를 조사한다.

주문 테이블에서 이름을 기준으로 연락처를 특정지을 수 있다.
단 주문테이블의 기본키는 어디까지나 주문번호로 이른은 기본키와 관계X
고객 테이블에서 이름을 기본키로 지정하면 동명이인의 경우 제대로 저장을 못하므로 고객번호를 기본키로 지정하여 테이블을 완성하였다.

분할할 때에는 서로 결합이 가능하도록 기본키를 추가하여 분할한다!!

정규화 후의 테이블
정규화 후의 테이블을 ER다이어그램으로 표기

(FK는 외부키)

정규화의 목적

'하나의 데이터는 한 곳에 있어야한다'는 규칙에 근거하여 중복/반복 되는 부분을 찾아내 분할하는 것.

트랜잭션

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

트랜잭션

정규화에 의해 분할된 테이블의 관계를 보자

주문테이블에 행이 존재 -> 주문상품 테이블에는 적어도 하나의 행이 존재해야한다.

발주처리
주문이 발생 -> 주문번호를 지정해야한다.
이때 기존 주문과 구분되는 주문번호를 발행하는 처리가 필요하다.
자동증가를 사용하면 자동적으로 번호가 부여됨. 그렇지 않은 경우에는 '번호 중 가장 큰 값을 select명령으로 가져와 그 값에 1을 더한다'라는 처리가 필요하다.
최대값은 MAX()로 검색이 가능 -> MAX+1이라 할 수도 있다.

번호를 발행받은 후 -> 해당번호를 키로 삼아 INSERT가 이루어짐
(주문 테이블에는 INSERT 한 번, 주문상품 테이블에는 상품 수만큼 INSERT)
즉 실행명령은 최고 두 번이다!!

이때 만약 특정 원인으로 INSERT가 에러가 발생 -> 트랜잭션을 사용하지 않을 때는 실행 전으로 되돌릴 수 없어 따로 DELETE명령을 실행하여 지워야한다.
->위 예제에서 두 개의 실행 명령에 의해 추가된 데이터를 DELETE로 삭제하는 처리가 필요하다.

롤백과 커밋

몇 단계로 처리를 나누어 SQL 명령을 실행하는 경우에 트랜잭션을 자주 사용한다.
에러가 발생해도 트랜잭션을 롤백해서 종료할 수 있다.
롤백시 트랜잭션 내에 행해진 모든 변경사항을 없었던 것으로 할 수 있음
아무런 에러가X -> 변경사항 적용 후 트랜잭션 종료 -> 이때 커밋을 사용한다