[SQL 데이터베이스 입문] 3주차

PART 3. SQL 활용

6.1 관계란

 

관계: 여러 테이블에 분산 저장된 데이터가 서로 어떻게 연결돼 있는지를 정의하는 개념

• 일대일 관계: 하나의 데이터가 하나의 데이터와만 연결된 관계
• 일대다 관계: 하나의 데이터가 여러 데이터와 연결된 관계
• 다대다 관계: 여러 데이터가 여러 데이터와 연결된 관계, 중간 테이블 필요

기본키(PK): 테이블 내에서 모든 튜플을 구분할 수 있는 칼럼 또는 칼럼의 조합

외래키(FK): 다른 테이블의 기본키를 가리키는 칼럼, 두 테이블을 연결하는 역할

 

제약 조건

• AUTO_INCREMENT: 칼럼의 값을 자동으로 1씩 증가, 정수형 칼럼, 기본키와 함께
• UNIQUE: 칼럼의 값으로 고유한 값만 허용
• NOT NULL: 칼럼에 NULL을 허용하지 X
• DEFAULT: 칼럼에 기본값을 지정
• CHECK: 칼럼의 값이 특정 조건을 만족하도록 조건을 지정
• UNSIGNED: 숫자형 칼럼에서 음수를 허용하지 X, 양수 값만 저장

6.2 다양한 관계 만들기

CREATE TABLE 테이블명 (
	칼럼명1 자료형1,
    칼럼명2, 자료형2,
    ...
    PRIMARY KEY (칼럼명)
    -- 외래키를 지정하는 방법
    FOREIGN KEY (칼럼명) REFERENCES 연결대상_테이블(연결대상_테이블의 기본키)
);

 

 

일대일 관계의 경우, 외래키 칼럼에 UNIQUE 제약 조건을 설정,  외래키의 위치는 사용 빈도가 적은 쪽. 

일대다 관계의 경우 외래키는 '다' 쪽 테이블에 지정한다.

-- doctors 테이블 생성
CREATE TABLE doctors (
	id INTEGER,
    name VARCHAR(255),
    PRIMARY KEY (id)
);

-- patients 테이블 생성
CREATE TABLE patients (
	id INTEGER,
    name VARCHAR(255),
    PRIMARY KEY (id)
);

-- appointments 테이블 생성
CREATE TABLE appointments (
	id INTEGER,
    doctor_id INTEGER,
    patient_id INTEGER,
    date DATE,
    PRIMARY KEY (id),
    FOREIGN KEY (doctor_id) REFERENCES doctors(id),
    FOREIGN KEY (patient_id) REFERENCES patients(id)
);

다대다 관계의 경우, 중간 테이블이 양쪽 테이블의 기본키를 가져와 외래키로 사용.

 

6.3 관계 만들기 실습: 별그램 DB

 

BOOLEAN: TRUE 또는 FALSE만을 값으로 갖는 자료형, 내부적으로는 TINYINT(1)로 선언된 정숫값, 0은 거짓인 FALSE로 해석, 그 외의 정수는 모두 참인 TRUE로 해석 

 

7.1 조인이란

SELECT 칼럼명1, 칼럼명2, ...
FROM 테이블A
JOIN 테이블B ON 테이블A.조인_칼럼 = 테이블B.조인_칼럼;

조인: 테이블 간에 일치하는 칼럼을 기준으로 두 테이블을 하나로 합쳐 보여주는 명령, 따로 저장된 데이터들을 한 번에 조회 가능

조인 칼럼: 두 테이블을 조인하기 위해 사용하는 칼럼

 

조인의 특징

• 조인 칼럼이 필요하다
• 조인 칼럼은 자료형이 일치해야 한다
• 조인 조건을 명시해야 한다
• 연속적으로 조인할 수 있다
• 중복 칼럼은 테이블명을 붙여구분한다
• 테이블명에 별칭을 사용할 수 있다
• 적절한 조인 유형을 선택한다 

SELECT 칼럼명1, 칼럼명2, ...
FROM 테이블A
INNER JOIN 테이블B ON 테이블A.조인_칼럼 = 테이블B.조인_칼럼;

INNER 조인: 양쪽 테이블에서 조인 조건을 만족하는 데이터를 찾아 조인, INNER 키워드 생략 가능

SELECT 칼럼명1, 칼럼명2, ...
FROM 테이블A
LEFT JOIN 테이블B ON 테이블A.조인_칼럼 = 테이블B.조인_칼럼;

LEFT 조인: 왼쪽 테이블(FROM 절의 테이블)의 모든 데이터에 대해 오른쪽 테이블(JOIN 절의 테이블)을 조인,

                   오른쪽 테이블에 해당하는 데이터가 없으면 NULL 값으로 채운다.

SELECT 칼럼명1, 칼럼명2, ...
FROM 테이블A
RIGHT JOIN 테이블B ON 테이블A.조인_칼럼 = 테이블B.조인_칼럼;

RIGHT 조인: 오른쪽 테이블(JOIN 절의 테이블)의 모든 데이터에 대해 왼쪽 테이블(FROM 절의 테이블)을 조인,

                      왼쪽 테이블에 해당하는 데이터가 없다면 NULL 값으로 채움

SELECT 칼럼명1, 칼럼명2, ...
FROM 테이블A
FULL JOIN 테이블B ON 테이블A.조인_칼럼 = 테이블B.조인_칼럼;

FULL 조인: 두 테이블의 모든 데이터를 결합하는 조인, 빈 칼럼은 NULL 값으로 채움, 중복 튜플 제거

-- 중복 튜플 제거하고 합치기
(쿼리A)
UNION
(쿼리B);

-- 중복 튜플 그대로 둔 채 합치기
(쿼리A)
UNION ALL
(쿼리B);

MYSQL은 FULL 조인을 지원X → UNION 연산자 사용

UNION 연산자: 두 쿼리의 결과 테이블을 하나로 합치는 집합 연산자

                          두 쿼리의 결과 테이블 내 칼럼 개수와 각 칼럼의 자료형이 정확히 일치해야 함.

UNION ALL: 중복 튜플 제거 X

(LEFT 조인 결과 테이블) UNION (RIGHT 조인 결과 테이블) = FULL 조인 결과 테이블

 

8.1 그룹화란

SELECT 그룹화_칼럼, 집계_합수(일반_칼럼)
FROM 테이블명
WHERE 조건
GROUP BY 그룹화_칼럼;

그룹화: 데이터 분석 기법의 하나로,

             데이터를 특정 기준에 따라 여러 그룹으로 나누고 그룹별 데이터를 요약하거나 분석하는 것

 

그룹화의 특징

• 집계 함수와 함께 사용
• 여러 칼럼으로 그룹화 가능
• SELECT 절에 올 수 있는 칼럼이 제한적

8.2 그룹화 필터링, 정렬, 조회 개수 제한

SELECT 그룹화_칼럼, 집계_함수(일반_칼럼)
FROM 테이블명
WHERE 일반_필터링_조건
GROUP BY 그룹화_칼럼
HAVING 그룹_필터링_조건;

 

그룹화 필터링: 그룹화한 결과에서 특정 조건을 만족하는 그룹의 데이터만 가져오는 것

WHERE 절은 개별 튜플에 대해 필터링 수행, HAVING 절은 그룹화한 결과, 즉 그룹 단위로 필터링 수행

SELECT *
FROM 테이블명
WHERE 조건
ORDER BY 정렬_칼럼1 [ASC | DESC], 정렬_칼럼2 [ASC | DESC], ...;

정렬: 쿼리 결과의 데이터를 오름차순(기본값) 또는 내림차순으로 재열하는 것

SELECT 칼럼1, 칼럼2, ...
FROM 테이블명
LIMIT N OFFSET M; -- N: 가져올 튜플의 개수, M: 건너뛸 튜플의 개수

조회 개수 제한: 상위 N개 튜플을 조회할 때 사용하는 명령

OFFSET 키워드: 중간 튜플을 가져오고 싶을 때, LIMIT 절에 추가해 시작 지점 조정