[데이터베이스시스템및응용] 2. SQL 기초

1. SQL

SQL(Structured Query Language)

관계형 데이터베이스의 구조를 정의하고 데이터를 조회, 조작하기 위한 언어이다

SQL은 관계형 데이터베이스를 다루는 특정 목적에 특화된 언어이다. 절차적 언어가 처리 과정을 직접 기술하는 데 비해, SQL은 원하는 데이터가 무엇인지 명시한다. 이러한 이유로 SQL을 선언적 언어라고 부른다

선언적 언어(Declarative Language)

작업을 수행하는 구체적인 절차보다, 얻고자 하는 결과를 기술하는 언어이다

SQL에서는 사용자가 어떤 데이터를 원하는지 작성하고, DBMS가 그 데이터를 어떻게 구할지 결정한다

예를 들어, “직업이 TA인 사람의 이름을 조회하라”라고 작성하면 된다. 데이터를 어떤 순서로 읽고 어떤 검색 방법을 사용할지까지 직접 지정할 필요는 없다

집합 단위 처리

• SQL 질의는 여러 행으로 구성된 데이터 집합을 대상으로 한다
• 조건에 맞는 여러 행을 하나의 질의로 조회할 수 있다
• 사용자가 각 행을 처리하는 반복문을 직접 작성할 필요는 없다

SQL은 원하는 결과를 집합 단위로 표현하고, 실제 실행 방법은 DBMS에 맡긴다

장점과 한계

특징	설명
선언적 언어	데이터를 어떻게 구할지보다 어떤 데이터를 원하는지 작성한다
폭넓은 지원	다양한 DBMS에서 구현되어 있으나, 실행 효율과 기능 지원 범위는 서로 다를 수 있다
용도의 제한	일반적인 모든 계산을 표현하는 범용 프로그래밍을 주목적으로 설계된 언어는 아니다
풍부한 기능	오랜 기간 기능이 추가되어 다양한 데이터 처리 작업을 지원한다
확장성	다른 언어로 작성된 기능을 호출하거나 외부 데이터 소스와 연동하도록 확장할 수 있다

1.1 SQL의 구성: DDL, DML

DDL(Data Definition Language, 데이터 정의 언어)

테이블 등 데이터베이스의 구조인 스키마를 정의하고 변경하는 언어이다

• 예를 들어, CREATE TABLE로 테이블의 이름과 속성, 데이터 타입을 정의하는 작업은 DDL에 해당한다

DML(Data Manipulation Language, 데이터 조작 언어)

데이터베이스에 저장된 데이터를 조회하거나 삽입, 수정, 삭제하는 언어이다

• 조회 명령인 SELECT이 DML에 포함한다

DDL은 데이터의 구조를 다루고, DML은 그 구조 안에 저장된 데이터를 다룬다

1.2 RDBMS

RDBMS(Relational Database Management System, 관계형 데이터베이스 관리 시스템)

데이터를 행과 열로 구성된 테이블 형태로 저장하고 관리하는 소프트웨어이다. SQL을 통해 데이터의 정의, 조회, 삽입, 수정, 삭제를 지원하고, 키와 제약 조건을 통해 데이터의 무결성을 유지한다

• 대표적인 예로 MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server가 있다

사용자가 SQL로 원하는 결과를 작성하면, RDBMS는 이를 효율적으로 구할 실행 알고리즘을 선택하고 수행한다

• 사용자는 원하는 데이터와 조건을 작성한다
• RDBMS는 질의를 처리할 적절한 알고리즘을 선택한다
• 어떤 알고리즘을 선택하더라도 질의가 요구하는 결과는 동일해야 한다

---

2. 기본 조회: SELECT-FROM-WHERE

어떤 테이블에서 어떤 조건을 만족하는 행을 찾아 어떤 속성을 출력할지 지정하는 기본 SQL 질의 구조이다

SELECT 출력할_속성
FROM 테이블
WHERE 조건;

구문	역할
SELECT	결과에 포함할 속성을 지정한다
FROM	데이터를 가져올 테이블을 지정한다
WHERE	결과에 포함할 행의 조건을 지정한다

다음은 조회 예시에 사용할 Payroll 테이블이다

UserID	Name	Job	Salary
123	Jack	TA	50000
345	Allison	TA	60000
567	Magda	Prof	90000
789	Dan	Prof	100000

2.1 모든 속성 조회

SELECT *
FROM Payroll;

• *는 모든 속성을 의미한다
• WHERE 조건이 없으므로 모든 행이 조회 대상이다

조회 결과

UserID	Name	Job	Salary
123	Jack	TA	50000
345	Allison	TA	60000
567	Magda	Prof	90000
789	Dan	Prof	100000
단, ORDER BY가 없으면 결과 행의 순서는 보장되지 않는다. 위 표는 내용을 보여주기 위한 표시 순서이다

2.2 별칭

별칭(Alias)

질의 안에서 테이블 등을 간단하게 참조하기 위해 붙이는 임시 이름이다

FROM Payroll AS P 이 구문에서 P는 Payroll의 별칭이다. 별칭을 붙여도 원래 테이블 이름 자체가 변경되는 것은 아니다. SELECT에서 계산한 결과 열에도 별칭을 붙일 수 있다

2.3 조건에 맞는 행의 특정 속성 조회

SELECT P.Name, P.UserID
FROM Payroll AS P
WHERE P.Job = 'TA';

• FROM Payroll AS P: Payroll 테이블을 사용하고, 이를 P라는 이름으로 부른다
• WHERE P.Job = 'TA': 직업이 TA인 행만 선택한다
• SELECT P.Name, P.UserID: 선택된 행에서 이름과 사용자 ID를 출력한다

조회 결과

Name	UserID
Jack	123
Allison	345

2.4 기본 조회 구문의 확장

단일 테이블을 조회하는 SQL 질의는 다음과 같은 구조로 작성한다

SELECT [DISTINCT] <출력할 열 또는 표현식 목록>
FROM <테이블>
[WHERE <행에 대한 조건>]
[GROUP BY <그룹화할 열 목록>
	[HAVING <그룹에 대한 조건>]]
[ORDER BY <정렬 기준 열 목록>]
[LIMIT <정수>];

• []는 생략 가능한 선택 항목을 나타낸다
• <>는 실제 열 이름이나 조건 등으로 채워 넣을 부분을 나타낸다
• 위의 대괄호와 꺾쇠괄호는 설명용 표기이며, 실제 질의에 그대로 입력하지 않는다
• 여러 열이나 표현식을 나열할 때는 쉼표로 구분한다
• HAVING은 GROUP BY 없이도 사용할 수 있으며, 이 경우 대상 행 전체를 하나의 그룹으로 다룬다

2.5 SELECT에서 표현식 사용

표현식(Expression)

열의 값이나 상수, 연산자 등을 조합하여 하나의 값을 나타내거나 계산하는 식이다

기본적인 SELECT-FROM-WHERE 질의는 조건을 만족하는 행을 선택한 뒤, SELECT에 지정된 표현식의 값을 출력하는 것으로 이해할 수 있다

SELECT에는 열 이름뿐 아니라 열의 값을 이용한 산술 표현식도 작성할 수 있다

예를 들어, 직업이 TA인 사람의 이름과 급여의 두 배를 조회하려면 다음과 같이 작성한다

SELECT P.Name, P.Salary * 2 AS DoubleSalary
FROM Payroll AS P
WHERE P.Job = 'TA';

2.6 산술식과 수학 함수

열에 산술 연산을 적용하거나, FROM 없이 상수와 함수를 계산할 수 있다

SELECT S.age,
       S.age - 5 AS age1,
       2 * S.age AS age2
FROM Sailors AS S
WHERE S.sname = 'Popeye';

age가 22이면 age1은 17, age2는 44이다. 조회용 계산이므로 원본 age 값은 바뀌지 않는다

SELECT LOG(1000::numeric) AS three,
       EXP(LN(2::numeric)) AS two,
       COS(0) AS one;

PostgreSQL에서 이 계산들은 각각 3, 2, 1에 해당한다

함수	의미
LOG(x)	상용로그
LN(x)	자연로그
EXP(x)	자연상수 e의 x제곱
COS(x)	코사인, 입력 각도는 라디안

::numeric은 값을 numeric 자료형으로 변환하는 PostgreSQL 문법이다

수학 함수와 계산 오차

함수의 지원 여부와 LOG의 기본 밑 등은 DBMS에 따라 확인해야 한다. 특히 부동소수점 계산에서는 수학적으로 같은 식도 계산 오차로 정확한 등호 비교 결과가 달라질 수 있다

2.7 문자열 패턴 검색: LIKE

LIKE

문자열 전체가 지정한 패턴에 맞는지 검사한다. %는 0개 이상의 문자, _는 정확히 한 문자를 나타낸다

SELECT S.sname
FROM Sailors AS S
WHERE S.sname LIKE 'B_%';

첫 문자는 B이고 그 뒤에 한 글자 이상 있는 이름을 찾는다. Bob은 포함되지만 B 한 글자만 있는 값은 포함되지 않는다

패턴	의미
'B%'	B로 시작
'%B%'	B를 포함
'_B%'	두 번째 문자가 B
'B_'	B로 시작하는 두 글자 문자열

2.8 정규표현식으로 문자열 검색하기

정규표현식 비교

PostgreSQL의 ~ 연산자는 문자열에서 정규표현식과 일치하는 부분이 있는지 검사한다. LIKE와 달리 기본적으로 문자열 전체가 일치할 필요는 없다

SELECT S.sname
FROM Sailors AS S
WHERE S.sname ~ '^B.';

^는 문자열 시작, .은 임의의 한 문자를 뜻한다. 따라서 B로 시작하고 뒤에 한 글자 이상 있는 이름을 찾는다

기호	의미
^	문자열 시작
$	문자열 끝
.	임의의 한 문자
*	바로 앞 요소의 0회 이상 반복

LIKE와 정규표현식의 패턴 차이

LIKE 'B_%'와 ~ 'B.*'는 일반적으로 같은 조건이 아니다. 정규표현식 B.*는 문자열 중간의 B도 찾으며, B 뒤에 문자가 없어도 일치한다. 앞의 LIKE와 같은 검색 의도라면 ~ '^B.'로 표현할 수 있다

~는 PostgreSQL 방식이므로 다른 DBMS에서는 해당 정규표현식 문법과 연산자를 확인해야 한다

---

3. SQL 질의의 의미: For-each Semantics

For-each 의미론(For-each Semantics)

각 행에 대해 조건을 검사하고, 조건을 만족하는 행에서 지정된 값을 출력하는 방식으로 질의의 결과를 설명하는 것이다

3.1 행별 처리로 이해

다음 질의를 다시 살펴보자

SELECT P.Name, P.UserID
FROM Payroll AS P
WHERE P.Job = 'TA';

이를 의사코드로 표현하면 다음과 같다

Payroll의 각 행 row에 대해:
    만약 row.Job이 'TA'라면:
        row.Name과 row.UserID를 출력한다

검사할 행	Job	조건 만족 여부	출력
Jack	TA	참	(Jack, 123)
Allison	TA	참	(Allison, 345)
Magda	Prof	거짓	출력하지 않음
Dan	Prof	거짓	출력하지 않음
따라서 최종 결과에는 Jack과 Allison이 포함된다

3.2 질의의 의미와 실제 실행 방법

For-each 의미론은 질의가 어떤 결과를 계산하는지 설명하는 것이며, DBMS가 반드시 그 반복문대로 실행한다는 뜻은 아니다. DBMS는 같은 결과를 얻기 위해 여러 방법을 사용할 수 있다

실행 방법	설명
테이블 순회	Payroll의 행을 하나씩 읽으면서 조건을 검사한다
Job 인덱스 조회	Job에 대한 인덱스에서 TA를 찾아 해당 행을 읽는다
비트맵 인덱스 활용	Job에 대한 비트맵 인덱스를 이용해 조건에 맞는 행을 찾는다
사용자가 작성하는 것은 원하는 결과에 대한 조건이고, DBMS가 결정하는 것은 그 결과를 구하는 실행 방법이다

---

4. 결과 정렬: ORDER BY

ORDER BY

조회 결과를 지정한 속성의 값을 기준으로 정렬하는 구문이다

SELECT *
FROM Payroll
ORDER BY 정렬할_속성;

정렬 방향을 생략하면 기본적으로 오름차순을 사용한다

4.1 이름으로 정렬

SELECT *
FROM Payroll
ORDER BY Name;

조회 결과

UserID	Name	Job	Salary
345	Allison	TA	60000
789	Dan	Prof	100000
123	Jack	TA	50000
567	Magda	Prof	90000
Allison → Dan → Jack → Magda 순서로 정렬되었다

4.2 여러 속성으로 정렬

SELECT *
FROM Payroll
ORDER BY Job, Name;

정렬 기준은 왼쪽부터 적용된다

• 먼저 Job으로 정렬한다
• Job이 같으면 Name으로 정렬한다

ORDER BY에 속성을 나열하는 순서는 정렬의 우선순위를 결정한다

조회 결과

UserID	Name	Job	Salary
789	Dan	Prof	100000
567	Magda	Prof	90000
345	Allison	TA	60000
123	Jack	TA	50000

4.3 정렬 방향: ASC와 DESC

ORDER BY에서는 각 정렬 기준에 대해 오름차순과 내림차순을 지정할 수 있다

키워드	정렬 방향	숫자 기준
ASC	오름차순	작은 값 → 큰 값
DESC	내림차순	큰 값 → 작은 값
생략	기본적으로 오름차순	ASC와 동일

여러 열로 정렬할 때는 열마다 정렬 방향을 다르게 지정할 수 있다

• ORDER BY S.gpa DESC, S.name ASC, a2;
  • 학점이 높은 학생, 같으면 이름, 모두 같으면 a2를 오름차순으로 정렬

뒤에 작성한 정렬 기준은 앞의 정렬 기준값이 같은 행 사이에서만 적용된다. 이러한 방식을 사전식 정렬(Lexicographic Ordering)이라고 한다

4.4 계산 결과의 별칭으로 정렬

SELECT에서 계산한 결과에 AS로 별칭을 붙이면, 그 별칭을 ORDER BY의 정렬 기준으로 사용할 수 있다

SELECT S.name, S.gpa, S.age * 2 AS a2
FROM Students AS S
ORDER BY a2;

• 위 질의는 나이에 2를 곱한 값에 a2라는 별칭을 붙이고, 그 값을 기준으로 정렬한다

4.5 결과 개수 제한: LIMIT

LIMIT

조회 결과에서 출력할 행의 개수를 제한하는 구문이다

SELECT S.name, S.gpa, S.age * 2 AS a2
FROM Students AS S
WHERE S.dept = 'CS'
ORDER BY S.gpa DESC, S.name ASC, a2
LIMIT 3;

이 질의는 다음 결과를 구한다

• 학과가 CS인 학생을 선택한다
• 학점은 내림차순, 이름과 a2는 오름차순으로 정렬한다
• 졍렬된 결과에서 앞의 최대 3개 행만 출력한다

조건을 만족하는 행이 3개보다 적다면 해당 행만 출력한다

ORDER BY와 함께 사용하는 이유

LIMIT는 일반적으로 ORDER BY와 함께 사용한다

SELECT S.name, S.gpa
FROM Students AS S
WHERE S.dept = 'CS'
LIMIT 3;

위 질의는 최대 3개 행을 반환하지만, 어떤 학생의 행이 선택될지는 보장되지 않는다

• ORDER BY가 없으면 결과 행의 순서가 정해지지 않는다
• DBMS의 실행 방법에 따라 먼저 얻는 행이 달라질 수 있다
• 따라서 결과에서 선택되는 행도 달라질 수 있다

**특정 기준의 상위 행을 구하려면 ORDER BY로 기준을 정한 뒤 LIMIT를 사용한다

다만 정렬 기준값이 모두 같은 행 사이의 선택까지 확정하려면, 기본키처럼 행을 유일하게 구분하는 열을 마지막 정렬 기준으로 추가해야 한다

---

5. 중복 제거: DISTINCT

DISTINCT

조회 결과에서 중복된 행을 제거하는 키워드이다

5.1 일반 SELECT: 중복 유지

SELECT Job
FROM Payroll;

조회 결과

Job
TA
TA
Prof
Prof
원래 테이블의 각 행에서 Job을 출력하므로 같은 값이 여러 번 나타날 수 있다. 이처럼 중복을 허용하는 방식을 백 의미론(Bag Semantics)이라 한다 🔴<- 링크 필요 포스트4

5.2 SELECT DISTINCT: 중복 제거

SELECT DISTINCT Job
FROM Payroll;

조회 결과

Job
TA
Prof
중복된 TA와 Prof가 각각 하나씩만 남는다. 이처럼 결과의 중복을 제거하는 방식은 집합 의미론(Set Semantics)에 해당한다

구분	중복 처리
SELECT Job	중복을 유지한다
SELECT DISTINCT Job	중복을 제거한다

DISTINCT는 중복 제거를 지정하며, 결과의 정렬 순서를 보장하지 않는다

여러 속성을 조회한다면, DISTINCT는 선택된 속성값의 조합 전체가 같은 행을 중복으로 판단한다

---

6. 테이블 생성과 데이터 타입

관계형 데이터베이스(Relational Database)

이름을 가진 여러 릴레이션(테이블)의 집합이다

6.1 CREATE TABLE

CREATE TABLE

테이블의 이름과 속성, 데이터 타입 및 제약 조건을 정의하여 새로운 테이블을 생성하는 명령문이다

다음 스키마를 갖는 테이블을 생성한다고 하자

• Payroll(UserID, Name, Job, Salary)

CREATE TABLE Payroll (
    UserID INT,
    Name TEXT,
    Job TEXT,
    Salary INT
);

속성	데이터 타입	의미
UserID	INT	사용자 ID를 정수로 저장한다
Name	TEXT	이름을 문자열로 저장한다
Job	TEXT	직업을 문자열로 저장한다
Salary	INT	급여를 정수로 저장한다
CREATE TABLE에는 이 밖에도 다양한 옵션과 확장 기능이 있다

6.2 대소문자와 가독성

SQL 키워드는 일반적으로 대소문자를 구분하지 않는다

SELECT *
FROM Payroll;
------------- 둘이 하는 일은 같다
select *
from Payroll;

다만 가독성을 위해 보통 다음과 같이 작성한다

• SQL 키워드: 대문자
• 테이블·속성 이름: 일관된 명명 규칙 사용
• 각 절: 줄을 나누어 구분

테이블, 속성 이름의 대소문자 처리에는 DBMS별 차이가 있으며, 문자열 값의 대소문자 비교까지 항상 무시된다는 뜻은 아니다

6.3 데이터 타입

모든 속성에는 데이터 타입이 있다

분류	타입	의미
문자열	CHAR(20)	고정 길이 문자열 타입
문자열	VARCHAR(50)	최대 길이를 지정하는 가변 길이 문자열 타입
문자열	TEXT	텍스트 저장을 위한 타입
정수	INT, SMALLINT	정수 저장을 위한 타입
실수	FLOAT	부동소수점 수 저장을 위한 타입
기타	MONEY, DATETIME	금액, 날짜·시간 등을 위한 타입
각 타입의 지원 여부와 세부 동작은 DBMS에 따라 다르며, 공급업체 고유의 타입도 많다

타입 검사

• 일반적인 DBMS는 선언된 속성 타입에 따라 저장 가능한 값을 검사한다
• SQLite는 일반적인 테이블에서 유연한 타입 처리를 사용하므로 예외로 언급된다

핵심은 각 속성에는 타입이 있으며, DBMS의 타입 규칙에 맞는 데이터를 저장해야 한다는 것이다

---

7. 데이터 삽입: INSERT

INSERT

테이블에 새로운 행을 추가하는 명령문이다

한 행씩 삽입

INSERT INTO Payroll
VALUES (123, 'Jack', 'TA', 50000);
 
INSERT INTO Payroll
VALUES (345, 'Allison', 'TA', 60000);

속성 목록을 생략한 위 구문에서는 테이블에 정의된 속성 순서에 맞춰 값을 작성한다. 문자열은 작은따옴표로 감싼다

여러 행을 한 번에 삽입

여러 개의 INSERT 문을 작성하는 대신, 하나의 문장에서 여러 행을 추가할 수 있다

INSERT INTO Payroll
VALUES
    (123, 'Jack', 'TA', 50000),
    (345, 'Allison', 'TA', 60000),
    (999, 'Zack', 'Prof', 90000);

각 행의 값은 괄호로 묶고, 행 사이를 쉼표로 구분한다

7.1 영속성

영속성(Persistence)

저장된 테이블과 데이터가 프로그램이나 컴퓨터를 종료한 뒤에도 유지되는 성질이다

• 일반적인 영구 테이블은 컴퓨터를 종료해도 사라지지 않는다
• 삽입되어 정상적으로 저장된 데이터도 이후에 다시 사용할 수 있다
• 트랜잭션을 사용하는 경우에는 변경 사항이 커밋되어야 확정된다

---

8. 데이터 삭제와 테이블 삭제

8.1 DELETE

DELETE

테이블에서 조건을 만족하는 행을 삭제하는 명령문이다

DELETE FROM 테이블
WHERE 조건;

다음 예제들은 각각 처음의 Payroll 테이블을 기준으로 설명한다

특정 사용자 삭제

DELETE FROM Payroll
WHERE UserID = 789;

UserID가 789인 Dan의 행이 삭제된다

조건을 만족하는 여러 행 삭제

DELETE FROM Payroll
WHERE Job = 'Prof';

직업이 Prof인 Magda와 Dan의 행이 삭제된다

모든 행 삭제

DELETE FROM Payroll;

WHERE 조건이 없으므로 모든 행이 삭제된다. 다만 테이블의 구조는 남아 있으며, 데이터가 없는 빈 테이블이 된다

8.2 DROP TABLE

DROP TABLE

데이터와 함께 테이블 자체를 삭제하는 명령문이다

DROP TABLE Payroll;

일상적인 데이터 삭제 용도로 사용하지 않는다

DELETE와 DROP TABLE의 차이

명령	삭제 대상	테이블 구조
DELETE FROM Payroll WHERE ...	조건을 만족하는 행	유지
DELETE FROM Payroll	모든 행	유지
DROP TABLE Payroll	테이블과 그 안의 데이터	삭제

---

9. 키와 기본키

9.1 키

키(Key)

테이블에서 하나의 행을 유일하게 식별하는 하나 이상의 속성이다

데이터베이스에서는 특정 레코드를 정확히 하나 선택할 수 있는 기준이 필요하다. 예들 들어, 이름이나 직업은 여러 사람이 같을 수 있지만, 사용자 ID는 각 사용자를 구분하도록 설계할 수 있다

9.1.1 어떤 속성이 키로 적절한가

다음 Payroll 테이블을 살펴보자

UserID	Name	Job	Salary
123	Jack	TA	50000
345	Allison	TA	60000
567	Magda	Prof	90000
789	Dan	Prof	100000

Job

• TA와 Prof가 반복된다
• 하나의 값을 지정해도 여러 행이 선택된다
• 따라서 키가 될 수 없다

UserID

• 각 사용자를 구분하는 식별자이다
• 사용자마다 서로 다른 값을 부여하도록 관리하면 키로 적절하다

Salary

• 현재 네 행에서는 급여가 모두 다르다
• 그러나 다른 사람이 같은 급여를 받을 수 있으므로 키로 적절하지 않다

현재 데이터에서 우연히 중복이 없다는 사실만으로 키를 결정해서는 안 된다. 실제 세계의 규칙과 앞으로 저장될 데이터까지 고려해야 한다

9.2 기본키

기본키(Primary Key, PK)

행을 유일하게 식별할 수 있는 키 중 테이블의 대표 식별 기준으로 선택한 키이다

• 키로 사용할 수 있는 선택지가 여러 개일 수 있다
• 그중 하나를 기본키로 지정한다
• 기본키 값은 중복될 수 없고, NULL일 수 없다
• 기본키가 여러 속성의 조합으로 구성될 수도 있다

다만 SQL에서 모든 테이블에 기본키 선언이 문법적으로 필수인 것은 아니다. 일반적으로는 각 테이블에 기본키를 두는 것이 좋은 설계 방법이다

9.2.1 속성 선언에 기본키 지정

CREATE TABLE Payroll (
    UserID INT PRIMARY KEY,
    Name TEXT,
    Job TEXT,
    Salary INT
);

UserID를 정의하면서 PRIMARY KEY를 함께 작성한다

9.2.2 테이블 정의의 마지막에 기본키 지정

CREATE TABLE Payroll (
    UserID INT,
    Name TEXT,
    Job TEXT,
    Salary INT,
    PRIMARY KEY (UserID)
);

이 방식도 UserID를 기본키로 지정한다

---

10. 복합

복합키(Composite Key)

둘 이상의 속성을 조합하여 하나의 행을 유일하게 식별하는 키이다

하나의 속성만으로는 행을 구분할 수 없더라도, 여러 속성의 조합으로는 구분할 수 있다

다음 테이블을 살펴보자

Name	Job	Salary
Alice	TA	20000
Alice	Prof	200000
Bob	Prof	200000

각 속성을 단독으로 사용하면 중복이 발생한다. 그러나 (Name, Job)을 조합하면 각 행을 구분할 수 있다. 따라서 (Name, Job)을 키로 선택한다

실제 설계에서도 이 조합을 키로 사용하려면, 같은 이름과 직업의 조합이 중복되지 않는다는 규칙이 성립해야 한다

복합 기본키 선언

CREATE TABLE Payroll (
    Name TEXT,
    Job TEXT,
    Salary INT,
    PRIMARY KEY (Name, Job)
);

• Name과 Job을 묶어서 하나의 기본키로 지정한다
• 각 속성의 값은 개별적으로 중복될 수 있다
• 두 속성의 값이 모두 같은 행은 중복될 수 없다

예를 들어 (Alice, TA)와 (Alice, Prof)는 허용되지만, (Alice, TA)를 갖는 행을 다시 추가하는 것은 허용되지 않는다. 복합 기본키는 PRIMARY KEY (속성1, 속성2, ...)처럼 테이블 수준에서 선언한다

---

11. 외래키

외래키(Foreign Key, FK)

같은 테이블 또는 다른 테이블의 기본키나 참조 가능한 유일 키를 참조하는 하나 이상의 속성이며, 참조 무결성을 유지하는 데 사용된다

데이터베이스에는 여러 테이블이 존재할 수 있으며, 서로 관련된 데이터를 연결할 방법이 필요하다

Payroll

UserID	Name	Job	Salary
123	Jack	TA	50000
345	Allison	TA	60000
567	Magda	Prof	90000
789	Dan	Prof	100000

Regist

UserID	Car
123	Charger
567	Civic
567	Pinto

두 테이블의 UserID를 연결하면 차량이 어느 사용자에게 등록되어 있는지 알 수 있다

• Charger는 UserID = 123인 Jack과 연결된다
• Civic과 Pinto는 UserID = 567인 Magda와 연결된다

이때 Regist.UserID가 Payroll.UserID를 참조하도록 설정한다

구분	속성	역할
참조하는 쪽	Regist.UserID	차량이 어떤 사용자와 연결되는지 나타내는 외래키
참조받는 쪽	Payroll.UserID	사용자를 유일하게 식별하는 기본키

11.1 외래키의 중복과 참조 방향

11.1.1 외래키 값은 중복될 수 있다

Regist에서는 UserID = 567이 두 번 등장한다. 이는 Magda에게 두 대의 차량이 등록되어 있음을 나타낸다

• 외래키 값은 참조하는 테이블에서 중복될 수 있다
• 참조 대상의 키는 유일성이 보장되어야 한다

외래키의 역할은 참조 대상과의 연결을 보장하는 것이다. 외래키 자체가 현재 테이블에서의 유일성을 요구하지는 않지만, 같은 속성이 기본키나 유일 키 역할을 함께 할 수 있다

11.1.2 참조 방향을 반대로 설정할 수 있는가?

다음처럼 반대 방향으로 참조한다고 하자

• Payroll.UserID가 Regist.UserID를 참조한다

현재 구조에서는 적절하지 않다

• Regist.UserID에는 567이 중복된다
• 따라서 567만으로 Regist의 특정 행 하나를 유일하게 식별할 수 없다

외래키가 참조하는 속성 또는 속성 조합은 유일성이 보장되어야 하며, 보통 기본키를 참조한다

11.2 외래키 제약의 의미

외래키 제약은 참조하는 값이 참조 대상의 키에 존재하는지 검사하여 참조 무결성을 유지한다

예를 들어, Payroll에 존재하지 않는 사용자 ID를 Regist에 저장하려 하면 외래키 제약을 위반한다. 단, 외래키에 NOT NULL을 별도로 지정하지 않았다면 NULL이 허용될 수 있다

11.3 외래키 선언 방법

참조받는 테이블 생성

먼저 기본키를 가진 Payroll을 생성한다

CREATE TABLE Payroll (
    UserID INT PRIMARY KEY,
    Name TEXT,
    Job TEXT,
    Salary INT
);

속성 선언에 REFERENCES 작성

CREATE TABLE Regist (
    UserID INT REFERENCES Payroll(UserID),
    Car TEXT
);

REFERENCES Payroll(UserID)는 이 속성이 Payroll의 UserID를 참조한다는 뜻이다

참조 대상의 기본키 속성 생략

CREATE TABLE Regist (
    UserID INT REFERENCES Payroll,
    Car TEXT
);

이 구문을 지원하는 DBMS에서는 참조할 속성 목록을 생략하면 대상 테이블의 기본키를 참조한다. 여기서는 Payroll의 기본키가 UserID이므로 이를 참조한다. 속성 이름이 같아서 자동으로 연결되는 것이 아니라, 참조 대상의 기본키가 사용되는 것이다

테이블 수준에서 외래키 선언

외래키도 속성 선언과 분리해서 작성할 수 있다

CREATE TABLE Regist (
    UserID INT,
    Car TEXT,
    FOREIGN KEY (UserID)
        REFERENCES Payroll(UserID)
);

• FOREIGN KEY (UserID): 외래키로 사용할 현재 테이블의 속성
• REFERENCES Payroll(UserID): 참조할 테이블과 속성

11.4 여러 속성으로 구성된 외래키

11.4.1 복합키 참조

참조 대상의 기본키가 여러 속성으로 구성되어 있다면, 외래키도 그 속성 조합을 함께 참조한다. 다음은 앞의 UserID 기반 설계와는 별도의 예시이다

참조받는 Payroll

CREATE TABLE Payroll (
    Name TEXT,
    Job TEXT,
    Salary INT,
    PRIMARY KEY (Name, Job)
);

여기서 기본키는 (Name, Job)이다

참조하는 Regist

CREATE TABLE Regist (
    Name TEXT,
    Job TEXT,
    Car TEXT,
    FOREIGN KEY (Name, Job)
        REFERENCES Payroll(Name, Job)
);

Regist의 (Name, Job) 조합이 Payroll의 (Name, Job) 조합을 참조한다

11.4.2 속성 조합 전체를 참조한다

예를 들어 Regist에 다음 정보를 저장한다고 하자

(Alice, TA, Civic)

이 행은 Payroll에서 Name이 Alice이면서 Job이 TA인 행을 참조한다

• Name과 Job을 각각 독립적으로 참조하는 것이 아니다
• 두 속성의 조합이 하나의 참조 관계를 구성한다
• 외래키 속성과 참조 대상 속성은 순서대로 대응하며 타입도 호환되어야 한다

11.4.3 기본키 속성 목록을 생략한 구문

CREATE TABLE Regist (
    Name TEXT,
    Job TEXT,
    Car TEXT,
    FOREIGN KEY (Name, Job)
        REFERENCES Payroll
);

이 구문을 지원하는 DBMS에서는 Payroll의 기본키인 (Name, Job)을 참조한다

여러 속성으로 구성된 외래키는 FOREIGN KEY (속성1, 속성2, ...)처럼 테이블 수준에서 선언한다

---

12. 집계 함수

집계 함수(Aggregate Function)

여러 행의 값을 대상으로 평균, 합계, 개수, 최댓값, 최솟값 등의 요약 값을 계산하는 함수이다

함수	역할	예시
AVG	평균 계산	AVG(S.gpa)
SUM	합계 계산	SUM(S.gpa)
COUNT	행 또는 값의 개수 계산	COUNT(*), COUNT(S.name)
MAX	최댓값 계산	MAX(S.gpa)
MIN	최솟값 계산	MIN(S.gpa)

12.1 조건을 만족하는 행의 집계

SELECT AVG(S.gpa)
FROM Students AS S
WHERE S.dept = 'CS';

• WHERE로 학과가 CS인 학생을 선택한다
• 선택된 학생들의 gpa 평균을 계산한다
• 개별 학생의 행 대신 평균값 하나를 출력한다

예제처럼 GROUP BY 없이 전체 대상에 집계 함수를 적용하면 결과는 한 행이다. 위 질의는 평균만 조회하므로 한 행, 한 열의 결과를 반환한다. 집계 함수에는 열뿐 아니라 계산식을 사용할 수도 있다

집계 함수와 NULL

• AVG, SUM, MAX, MIN은 대상 값이 NULL인 경우 제외하고 계산한다
• COUNT(열)은 해당 열의 값이 NULL이 아닌 행의 개수를 센다
• COUNT(*)는 열의 NULL 여부와 관계없이 모든 대상 행의 개수를 센다

12.2 집계 함수와 DISTINCT

DISTINCT가 집계 함수 안에 있는지, SELECT 바로 뒤에 있는지에 따라 적용 대상이 달라진다

집계하기 전에 중복 제거

SELECT COUNT(DISTINCT S.name)
FROM Students AS S
WHERE S.dept = 'CS';

• CS 학생들의 이름에서 중복을 제거한다
• NULL을 제외한 서로 다른 이름의 개수를 센다
• 동명이인이 있다면 같은 이름은 한 번만 센다

집계 결과에서 중복 제거

SELECT DISTINCT COUNT(S.name)
FROM Students AS S
WHERE S.dept = 'CS';

• 먼저 이름이 NULL이 아닌 행의 개수를 센다
• 그 후 출력되는 집계 결과 행에 DISTINCT를 적용한다
• 이 질의는 집계 결과가 이미 한 행이므로, DISTINCT를 붙여도 결과가 달라지지 않는다

함수 안의 DISTINCT는 집계할 값의 중복을 제거하고, SELECT DISTINCT는 출력 결과 행의 중복을 제거한다

---

13. 그룹별 집계: GROUP BY

GROUP BY

지정한 열의 값이 같은 행들을 하나의 그룹으로 묶는 구문이다. 집계 함수와 함께 사용하면 그룹별 요약 값을 계산할 수 있다

SELECT S.dept, AVG(S.gpa)
FROM Students AS S
GROUP BY S.dept;

• S.dept의 값이 같은 학생들을 같은 그룹으로 묶는다
• 각 학과에 속한 학생들의 평균 학점을 계산한다
• 학과별로 한 행의 결과를 출력한다

예를 들어 다음 데이터가 있다고 하자

name	dept	gpa
Kim	CS	4.0
Lee	CS	3.0
Park	BUSINESS	3.5
Choi	BUSINESS	4.5

조회 결과

dept	AVG(S.gpa)
CS	3.5
BUSINESS	4.0
ORDER BY를 사용하지 않았으므로 결과 행의 순서는 보장되지 않는다

13.1 여러 열을 기준으로 그룹화

GROUP BY에는 여러 열을 지정할 수 있다

SELECT S.dept, S.gender, AVG(S.gpa)
FROM Students AS S
GROUP BY S.dept, S.gender;

이 경우 학과와 성별의 조합이 같은 행들을 하나의 그룹으로 묶는다

그룹 기준	의미
CS, F	CS 학과의 여학생
CS, M	CS 학과의 남학생
BUSINESS, F	BUSINESS 학과의 여학생

각 조합에 해당하는 그룹별로 평균 학점을 계산한다

카디널리티(Cardinality)

여기서는 결과에 포함된 행의 개수를 의미한다

그룹마다 한 행을 출력하므로, HAVING, DISTINCT, LIMIT 등으로 결과가 줄어들지 않는 기본 그룹 집계에서는 결과 행의 개수 = 서로 다른 그룹의 개수이다

13.2 GROUP BY와 SELECT의 관계

기본적인 그룹 집계 질의에서는 SELECT 절에는 다음과 같은 항목을 사용한다

항목	예시	이유
그룹화 기준 열	S.dept	한 그룹 안에서 값이 동일하다
집계 함수	AVG(S.gpa), COUNT(*)	그룹의 여러 행을 하나의 값으로 요약한다
학과별로 묶었을 때 한 학과에는 여러 학생이 있으므로, 개별 학생의 이름을 그대로 출력하면 어떤 이름을 선택해야 하는지 정해지지 않는다. 따라서 기본 규칙은 집계하지 않고 출력하는 열을 GROUP BY에 포함하는 것이다

---

14. 그룹 조건 지정: HAVING

HAVING

그룹화와 집계 이후, 조건을 만족하는 그룹만 결과에 포함하는 구문이다

SELECT S.dept, AVG(S.gpa)
FROM Students AS S
GROUP BY S.dept
HAVING COUNT(*) > 2;

• 학과별로 학생들을 묶는다
• 각 학과의 학생 수와 평균 학점을 계산한다
• 학생 수가 2명을 초과하는 학과, 즉 3명 이상인 학과만 출력한다

HAVING은 필요한 경우에만 작성하는 선택적인 구문이다

WHERE와 HAVING의 차이

구분	WHERE	HAVING
조건 적용 대상	개별 행	그룹
논리적인 적용 시점	그룹화·집계 이전	그룹화·집계 이후
주요 용도	집계할 원본 행을 선택	집계한 그룹을 선택
예시	S.gender = 'F'	COUNT(*) >= 2
WHERE는 어떤 행을 집계할지 정하고, HAVING은 어떤 그룹을 출력할지 정한다

현재 질의의 그룹 집계 결과를 조건으로 삼을 때는 WHERE가 아니라 HAVING을 사용한다

• HAVING AVG(S.gpa) >= 3.5 위 조건은 평균 학점이 3.5 이상인 그룹만 선택한다

14.1 HAVING에서 사용할 수 있는 항목

HAVING의 조건에는 기본적으로 집계 함수와 그룹화 기준 열을 사용할 수 있다

SELECT S.dept, AVG(S.gpa)
FROM Students AS S
GROUP BY S.dept
HAVING COUNT(*) > 2
   AND S.dept = 'CS';

• COUNT(*) > 2: 그룹의 학생 수에 대한 조건
• S.dept = 'CS': 그룹화 기준 열에 대한 조건

조건에 사용하는 집계 함수를 반드시 SELECT에도 출력할 필요는 없다. 위 질의는 학생 수로 그룹을 걸러내지만, 결과에는 학과와 평균 학점만 출력한다