[정보 처리] 15. DBMS 활용 (SQL, 인덱스, 뷰, 트랜젝션, 병렬 처리, DB Lock)
- 이번 포스팅에서는 DBMS 를 조작하고 활용하는 방식에 대해 정리할 것입니다.
대부분의 내용이 DBMS 에 명령을 내리는 SQL 에 해당하는 것으로,
SQL 을 다뤄본 적이 없으신 분이라면 MySQL, MariaDB 등의 DBMS 중 하나를 컴퓨터에 설치하고, Workbench, DBeaver 등의 DBMS 사용툴을 설치하여 실제로 SQL 을 DB 에 명령내려보는 방식으로 진행하는 것을 추천드립니다.
- 개발 실무에서는 SQL 을 잘 사용하는 것이 중요합니다.
자주 사용하게 될 DBA, 백엔드 개발자 뿐 아니라 모든 분야의 개발자들이 필수적으로 숙지해야하는 스킬이며,
SQL 을 어떻게 사용하는지에 따라서 구현한 기능의 성능이 크게 차이가 날 수도 있으므로, 기본과 원리를 잘 이해한 상태에서 SQL 을 최적화하여 프로그램의 성능을 높이는 작업도 비중이 높은 실무 작업이 될 것입니다.
[SQL 설명]
(SQL(Structured Query Language) 개념)
- SQL 이란, 관계형 데이터베이스를 제어하는 DBMS 에 명령을 내리기 위한 표준 언어로, 관계 대수(아래 설명)를 기초로 만들어졌습니다.
쉽게 말하자면 DBMS 에 이러한 정보를 저장해주세요, 저장된 정보들 중 이러한 정보를 가져와주세요 라고 명령을 내리는 방식으로, 영어 문법, 즉 사람의 언어와 유사한 구조로 약속된 명령어이기 때문에 학습과 사용이 용이한 고급 언어입니다.
- SQL 은 DBMS 에 명령을 내리기 위한 언어라고 했는데,
언어에 표준어가 있고 방언이 있듯이, SQL 역시 DBMS 종류별 방언(Dialect)이 존재합니다.
프로그래밍을 할 때에는 DBMS 의 종류에 종속되지 않고 언제든 다른 DBMS 를 활용할 수 있도록, 이러한 방언을 걷어내고 개발하는 것을 권장하며, 본 게시글에서는 표준으로 사용되는 기본 SQL 에 대해서만 정리할 것입니다.
- DBMS 를 주로 다루는 것은 DB 관리자, 관련 개발자 이외에 해당 데이터를 사용하는 애플리케이션이 있습니다.
이런 애플리케이션에서 DBMS 에 요청을 보내는 방법을 알아야 DBMS 를 다루는 기능을 수행하는 소프트웨어를 개발할 수 있겠죠?
프로그래밍 언어에서 관계형 데이터베이스를 제어하기 위해 내장 SQL 이라는 방식이 제공됩니다.
C 언어와 같은 호스트 프로그래밍 언어에 SQL 문을 직접 삽입하여 사용하는 방식으로,
#include <stdio.h>
#include <sqlca.h>
EXEC SQL BEGIN DECLARE SECTION;
int id;
char name[20];
EXEC SQL END DECLARE SECTION;
int main() {
EXEC SQL CONNECT TO mydb USER 'user' IDENTIFIED BY 'password';
id = 1;
EXEC SQL SELECT name INTO :name FROM users WHERE id = :id;
printf("이름: %s\n", name);
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
return 0;
}
이런 식으로 프로그래밍 언어 내에서 SQL 을 사용할 수 있게(EXEC SQL 부분) 해주는 해주는 것입니다.
참고로 Java, Kotlin 과 같은 C 언어보다 뒤에 나온 언어들은 위와 같이 코딩 규칙 속에 SQL 을 녹여내는 방식 대신,
import java.sql.DriverManager
fun main() {
val conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "user", "password")
val stmt = conn.prepareStatement("SELECT name FROM users WHERE id = ?")
stmt.setInt(1, 1)
val rs = stmt.executeQuery()
while (rs.next()) {
val name = rs.getString("name")
println("이름: $name")
}
rs.close()
stmt.close()
conn.close()
}
위의 JDBC 라는 라이브러리(요즘은 JDBC 는 잘 사용하지 않습니다. 2025 년 현 시점 사용한다면 JPA 와 같은 ORM 을 추천합니다.)를 사용하는 것과 같은 방식으로 SQL 쿼리문을 String 으로 다루고, DBMS 를 객체로 다루는 방식으로 보다 논리적으로 DBMS 를 다룰 수 있도록 제공해주는 DBMS 조작 전용 라이브러리를 제공하고 있습니다.
각 프로그래밍 언어, 프레임워크를 학습하여 소프트웨어 개발을 배울 때는 기본 사용법의 바로 다음으로 DBMS 를 다루는 가장 보편적이고 좋은 방식을 찾아서 학습하는 것이 프로그래밍 공부의 기본이라 할 수 있습니다.
(SQL 명령어 분류)
- SQL 에 명령을 내리는 명령어는 아래와 같은 종류로 분류됩니다.
1. 데이터 정의어(DDL : Data Definition Language)
데이터베이스 객체를 정의, 수정, 변경하는 종류의 언어입니다.
쉽게 말하자면 데이터베이스 테이블을 만들거나 스키마를 수정하거나 삭제하는 등의 테이블 단위의 조작을 하는 명령어입니다.
이 명령어는 테이블에 저장된 데이터를 명령어 한번으로 전부 소실 시킬 가능성이 있는 위험한 명령어이므로,
일반적으로 데이터를 입력하고 가져오는 기능만 필요한 일반 사용자 및 애플리케이션에서는 사용을 엄금하며,
DBA(Database Administration) 만 사용하는 것이 일반적이고 권장됩니다.
앞서 쉽게 예시를 드느라 테이블을 조작하는 언어라고 말을 했지만, 정확히는 데이터베이스 객체를 조작하는 언어로,
데이터베이스 객체에는 스키마, 도메인, 테이블, 뷰, 인덱스 등을 포함합니다.(자세한 내용은 아래에 설명)
다시 한번 강조하자면, DDL 은 무척이나 위험한 명령어이며, 한번 수행되면 이전 상태로 되돌릴 수 없다는 특징이 있으므로, 많은 사람들이 중요하게 사용하는 서비스의 데이터베이스 객체를 다룰 때에는 무척이나 신중하게 진행해야 합니다.(이를 다루는 실무자라면 자기 방어적으로 행동해야만 합니다. 명령어 입력 시점에 본인에게 책임이 갈만한 일이라면 무척 신중하게 다루고, 그렇지 않더라도 전후 사정을 정확히 파악하여 기록을 남기는 것이 좋습니다.)
2. 데이터 조작어(DML : Data Manipulation Language)
사용자(응용 프로그램)가 DBMS 를 통해 데이터베이스를 조작하기 위한 인터페이스를 제공하는 언어입니다.
쉽게 말하자면, DDL 로 정의된 테이블에 데이터를 저장하거나, 갱신, 삭제, 조회를 할 수 있는 명령어로,
애플리케이션 개발을 한다면 가장 많이 다루어야하는 SQL 입니다.
이 중에서도 기존 데이터를 삭제하거나 덮어쓰는 명령어는 DDL 과 마찬가지로 위험한 명령어라 할 수 있지만, DML 은 DDL 과 다르게 이전 상태로 되돌아 갈 수 있는 백업 기능이 제공됩니다.
그럼에도 데이터 손실은 치명적인 것이므로 삭제와 수정 명령어를 사용할 때에는 주의를 기울여야 하며, 데이터 변경이 일어난 이후에는 꼭 조회를 하여 제대로 데이터가 반영되었는지를 확인하는 것을 추천합니다.
3. 데이터 제어어(DCL : Data Control Language)
사용자의 데이터 접근에 대한 통제 등을 제어하기 위한 언어입니다.
DDL, DML 외에는 DCL 이라고 생각해도 나쁘지는 않습니다.
사용자 권한 부여, 사용자 권한 회수,
트랜젝션 복귀지점 설정(명령을 내린 이후에도 되돌아 올 수 있는 세이브 포인트),
트랜젝션 결과 반영(트랜젝션 복귀 지점을 제거하고 이제까지의 명령 수행을 확정.)
트랜젝션 작업 취소(트랜젝션 복귀지점에서부터 지금까지 일어난 모든 명령을 취소하고 복귀 지점으로 돌아가기)
위와 같은 종류가 있습니다.
이중에 트랜젝션 관련 명령어는 그 목적성으로 그룹화가 가능하므로,
이를 TCL(Transaction Control Language) 라고 분류하기도 합니다.
실무에서 DBA 가 명확히 정해지지 않은 중소규모 개발팀에서 사용자 권한 처리는 그다지 사용되지 않는 경우가 많지만, TCL 의 경우는 애플리케이션을 개발할 때 처리해야 하는 필수 기능입니다.
예를들어 DB 의 데이터를 입력, 수정, 삭제하는 함수가 작업을 진행할 때,
절차적으로 DB 정보를 수정하였지만, 도중에 에러가 발생한 경우, 이는 작업이 완료되지 않은 것이므로 기존 DB 조작 내역을 취소하고 함수가 실행되기 이전의 상태로 돌아가지 않으면 데이터간 불일치가 발생할 수 있기 때문입니다.
고로 프로그래밍 공부시 DBMS 조작 라이브러리를 공부했다면 반드시 트랜젝션 처리 관련 처리 방식도 주의 깊게 배워서 DML 의 데이터 변경 부분이 일어나는 기능 단위로 적용해주어야만 DBMS 조작 프로그래밍 공부가 완료되었다고 할 수 있을 것입니다.
JDBC 코드로 예시를 보자면 아래와 같습니다.
import java.sql.Connection
import java.sql.DriverManager
fun main() {
val url = "jdbc:mysql://localhost:3306/mydb"
val user = "user"
val password = "password"
val conn: Connection = DriverManager.getConnection(url, user, password)
try {
// 트랜잭션 시작
conn.autoCommit = false
// 첫 번째 쿼리 실행
val insertStmt = conn.prepareStatement("INSERT INTO users (id, name) VALUES (?, ?)")
insertStmt.setInt(1, 101)
insertStmt.setString(2, "홍길동")
insertStmt.executeUpdate()
// 두 번째 쿼리 실행
val updateStmt = conn.prepareStatement("UPDATE users SET name = ? WHERE id = ?")
updateStmt.setString(1, "김길동")
updateStmt.setInt(2, 101)
updateStmt.executeUpdate()
// 성공 시 커밋
conn.commit()
println("트랜잭션 커밋 완료")
} catch (e: Exception) {
// 오류 발생 시 롤백
println("에러 발생: ${e.message}")
conn.rollback()
println("트랜잭션 롤백됨")
} finally {
// 리소스 정리
conn.close()
}
}
[관계 대수]
- SQL 을 다루기 전에, SQL 의 근본이 되는 관계 대수에 대해 먼저 알아보겠습니다.
SQL 과의 관계를 먼저 알아보자면,
데이터를 어떻게 조회할 수 있는지에 대한 관계대수 표현 방법이 먼저 나온 후,
이를 모티브로 하여 DBMS 의 SQL 이 나왔습니다.
(관계 해석(Relational Calculus))
- 관계 해석이란, 원하는 정보가 무엇인지에 대해 정의하고, 어떻게 실행할 것인지는 기술하지 않는 비절차적 언어입니다.
E.F.Codd 박사가 제안한 것으로, 수학의 술어 해석(Predicate Calculus, 객체에 대한 명제 해석으로, 실행 결과가 반드시 참, 거짓으로 나타남) 에 기반합니다.
- 관계 해석은 튜플을 기준으로 데이터를 조회하는 튜플 관계 해석과,
속성을 기준으로 데이터를 조회하는 도메인 관계 해석으로 구성됩니다.
- 관계 해석의 논리 기호는 OR, AND, NOT 이 있습니다.
AND(∧) : 원자식 간의 관계를 '그리고'로 연결
OR(∨) : 원자식 간의 관계를 '또는'으로 연결
NOT(¬) : 원자식을 부정
- 관계 해석 정량자는 전칭 전량자와 존재 정량자가 존재합니다.
전칭 정량자(Universal Quantifier, ∀) : 모든 것에 대하여(for all)
존재 정량자(Existential Quantifier, ∃) : 어느 것 하나라도 존재(there exists)
(관계 대수(Relational Algebra))
- 관계대수란, 원하는 정보와 그 정보를 어떻게 유도하는가를 정의하는 절차적 언어입니다.
연산자와 연산 규칙을 이용하여 주어진 릴레이션으로부터 원하는 릴레이션을 유도합니다.
- 관계대수의 연산자는 아래와 같습니다.
1. 일반 집합 연산자 : 합집합 (∪), 차집합 (−), 교집합 (∩), 교차곱(X)
2. 순수 관계 연산자 : SELECT(𝜎), PROJECT(π), JOIN(⋈), DIVISION(÷)
이를 순서대로 알아보겠습니다.
- 합집합 (∪, Union)
두 릴레이션을 튜플 중복 없이 합하는 연산으로, 식으로 표현하면 아래와 같습니다.
R∪S = R Union S
예를 들어 설명하면,
| empno | ename | deptno |
| :---: | :---: | :----: |
| 103 | 이영희 | 10 |
| 104 | 박민수 | 30 |이것과
| empno | ename | deptno |
| :---: | :---: | :----: |
| 101 | 홍길동 | 10 |
| 102 | 김철수 | 20 |
이것의 합집합은,
| empno | ename | deptno |
| :---: | :---: | :----: |
| 101 | 홍길동 | 10 |
| 102 | 김철수 | 20 |
| 103 | 이영희 | 10 |
| 104 | 박민수 | 30 |
이것입니다.
- 차집합 (−, Difference)
대상 릴레이션 R에서 다른 릴레이션 S 와 동일한 부분을 제거하여 추출하는 연산으로, 식으로 표현하면 아래와 같습니다.
R−S = R MINUS S
예를 들어 설명하자면,
| empno | ename | deptno |
| :---: | :-----: | :----: |
| 101 | Alice | 10 |
| 102 | Bob | 20 |
| 103 | Charlie | 10 |
에서,
| empno | ename | deptno |
| :---: | :---: | :----: |
| 102 | Bob | 20 |
| 104 | David | 30 |
를 빼면,
| empno | ename | deptno |
| :---: | :-----: | :----: |
| 101 | Alice | 10 |
| 103 | Charlie | 10 |
동일 튜플인 102 가 제거되고 이러한 결과가 반환됩니다.
차집합의 결과는 항상 대상 릴레이션 R 의 부분집합이 됩니다.
- 교집합 (∩, Intersection)
두 릴레이션에서 중복되는 동일한 튜플을 추출할 때 사용하는 연산으로, 식으로 표현하면 아래와 같습니다.
R∩S = R INTERSECT S
예를 들어 설명하자면,
| empno | ename | deptno |
| :---: | :-----: | :----: |
| 101 | Alice | 10 |
| 102 | Bob | 20 |
| 103 | Charlie | 10 |
이것과
| empno | ename | deptno |
| :---: | :-----: | :----: |
| 102 | Bob | 20 |
| 103 | Charlie | 10 |
| 104 | David | 30 |
이것을 교집합 하면, 공통으로 존재하는 102, 103 이 반환되며,
| empno | ename | deptno |
| :---: | :-----: | :----: |
| 102 | Bob | 20 |
| 103 | Charlie | 10 |
이러한 결과가 나오게 됩니다.
- 교차곱(X, Catesian Product)
두 릴레이션의 튜플을 합치고, 순서쌍의 집합을 만드는 연산입니다.
카테시안 곱이라고도 하며, 두 릴레이션의 조합으로 나타날 수 있는 모든 경우의 수를 반환하는 개념입니다.
식으로 표현하면 아래와 같습니다.
R X S
예를 들어 설명하자면,
| empno | ename |
| :---: | :---: |
| 101 | Alice |
| 102 | Bob |
이런 테이블과,
| deptno | dname |
| :----: | :---- |
| 10 | 총무부 |
| 20 | 인사부 |
이런 테이블은 동일한 속성이 없습니다.
이를 곱하려면, 각 속성값별 존재할 수 있는 모든 조합을 만들어 곱하는 것으로,
| empno | ename | deptno | dname |
| :---: | :---: | :----: | :---- |
| 101 | Alice | 10 | 총무부 |
| 101 | Alice | 20 | 인사부 |
| 102 | Bob | 10 | 총무부 |
| 102 | Bob | 20 | 인사부 |
위와 같이 2 x 2 로 하여 4개의 결과가 반환되는 것을 볼 수 있습니다.
- SELECT(𝜎)
릴레이션에서 조건에 맞는 튜플을 추출하여 수평적 부분 집합을 구하는 연산입니다.
식으로 표현하면 아래와 같습니다.
𝜎 <조건> R
예를 들어 설명하자면,
EMP
| empno | ename | deptno | sal |
|:-----:|:-------:|:------:|-----:|
| 101 | Alice | 10 | 3000 |
| 102 | Bob | 20 | 2500 |
| 103 | Charlie | 10 | 4000 |
| 104 | David | 30 | 2000 |
위와 같은 R 이 있을 때,
여기서 sal 이 3000 이상인 사원만을 선택하려면,
𝜎 sal >= 3000 R
EMP
| empno | ename | deptno | sal |
|:-----:|:-------:|:------:|-----:|
| 101 | Alice | 10 | 3000 |
| 102 | Bob | 20 | 2500 |
| 103 | Charlie | 10 | 4000 |
| 104 | David | 30 | 2000 |
위와 같이 조건에 맞는 조건을 추출해 옵니다.
- PROJECT(π)
릴레이션에서 지정된 속성만을 추출하여 수직적 부분집합을 구하는 연산입니다.
식으로 표현하면 아래와 같습니다.
π <속성명> R
예를 들어 설명하자면,
EMP
| empno | ename | deptno | sal |
|:-----:|:-------:|:------:|-----:|
| 101 | Alice | 10 | 3000 |
| 102 | Bob | 20 | 2500 |
| 103 | Charlie | 10 | 4000 |
| 104 | David | 30 | 2000 |
위와 같은 테이블에서 empno 와 ename 만 보고 싶다면,
π empno,ename R
| empno | ename |
|:-----:|:-------:|
| 101 | Alice |
| 102 | Bob |
| 103 | Charlie |
| 104 | David |
이렇게 수직적으로 조건에 맞는 결과값이 반환됩니다.
- JOIN(⋈)
두 릴레이션에서 조건에 맞는 튜플을 하나로 합친 릴레이션을 생성하는 연산입니다.
관계형 데이터베이스의 SQL 에서의 Join 명령어와 동일한 개념입니다.
두 릴레이션을 합칠 때 기준이 되는 공통 속성이 한개 이상 존재해야 합니다.
조인의 종류는 4가지가 존재합니다.
동일 조인이란, 동일 속성을 기준으로 조인하며, 동일 속성이 중복됩니다.
식으로 표현하면 아래와 같습니다.
R ⋈ R.A=S.A S
R.A 와 S.A 속성을 기준으로 R 과 S 를 합친다는 의미로,
예시로 알아보자면,
| empno | ename | deptno |
| :---: | :-----: | :----: |
| 101 | Alice | 10 |
| 102 | Bob | 20 |
| 103 | Charlie | 10 || deptno | dname |
| :----: | :---: |
| 10 | 총무부 |
| 20 | 인사부 |
위 두 테이블을 deptno 를 기준으로 join 한다면,
| empno | ename | deptno | deptno | dname |
| :---: | :-----: | :----: | :----: | :---: |
| 101 | Alice | 10 | 10 | 총무부|
| 103 | Charlie | 10 | 10 | 총무부|
| 102 | Bob | 20 | 20 | 인사부|
위와 같이 Join 되며, deptno 는 남습니다.
자연 조인이란, 동일 조건과 같지만, 중복 속성은 제외됩니다.
식으로 표현하면 아래와 같습니다.
R⋈ N S
위에서 N 을 통해 중복 속성을 제거하라는 의미가 됩니다.
예시를 알아보자면,
앞서 동일 조인에서 사용한 예시를 기반으로 결과값이,
| empno | ename | deptno | dname |
| :---: | :-----: | :----: | :---: |
| 101 | Alice | 10 | 총무부 |
| 103 | Charlie | 10 | 총무부 |
| 102 | Bob | 20 | 인사부 |
이렇게 나오면서 중복되었던 deptno 가 제거된 것을 볼 수 있습니다.
외부 조인이란, 조건에 맞지 않는 튜플도 결과에 포함하는 확장 조인입니다.(Null 포함)
식으로 표현하면 아래와 같습니다.
R⟕S(왼쪽 외부 조인) : R 의 모든 튜플을 기본으로, S 와 매칭되지 않으면 S 측 속성값들이 Null 로 표시됩니다.
R⟖S(오른쪽 외부 조인) : S 의 모든 튜플을 기본으로, R 과 매칭되지 않으면 R 측 속성값들이 Null 로 표시됩니다.
R⟗S(전체 외부 조인) : 양쪽 모든 튜플이 표시되며, 매칭되지 않은 모든 속성값들이 Null 로 표시됩니다.
이해하기 쉽게 왼쪽 외부 조인의 예시만 알아보자면,
| empno | ename | deptno |
| :---: | :-----: | :----: |
| 101 | Alice | 10 |
| 102 | Bob | 20 |
| 103 | Charlie | 30 || deptno | dname |
| :----: | :---: |
| 10 | 총무부 |
| 20 | 인사부 |
위와 같은 테이블이 있을 때, 좌측에 해당하는 위쪽 테이블의 deptno 30 은 두번째 테이블에는 존재하지 않습니다.
그럼에도 좌측 테이블의 모든 튜플이 유지되어야 하므로, empno 103 의 join 된 dname 은 NULL 로 표시되어,
| empno | ename | deptno | dname |
| :---: | :-----: | :----: | :---- |
| 101 | Alice | 10 | 총무부 |
| 102 | Bob | 20 | 인사부 |
| 103 | Charlie | 30 | NULL |
이렇게 반환됩니다.
세타 조인이란, 조인 조건으로 등호(=) 이외의 비교 연산자(<,>,≤,≥,≠ 등)도 허용하는 범용 조인입니다.
식으로 표현하면 아래와 같습니다.
R⋈ θ S
예시를 알아보자면,
θ는 등호 이외의 연산자(<,>,≤,≥,≠ 등)를 포함한 술어(predicate)입니다.
릴레이션 R(A)와 S(B)가 각각 다음 튜플을 가질 때,
| A |
| :-: |
| 1 |
| 2 |
| 3 || B |
| :-: |
| 2 |
| 3 |
| 4 |
R⋈ A<B S
이렇게 하여 A가 B보다 작은 모든 조합을 반환하도록 할 것입니다.
| A | B |
| :-: | :-: |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 2 | 3 |
| 2 | 4 |
| 3 | 4 |
결과는 위와 같습니다.
- DIVISION(÷)
대상 릴레이션 R 에서 다른 릴레이션 S 속성의 데이터와 일치하는 튜플 중, 다른 릴레이션 S 의 속성과 일치하는 속성을 제거한 릴레이션을 생성하는 연상입니다.
식으로 나타내자면,
R ÷ S
예시로 알아보자면,
| Student | Course |
|:-------:|:-------:|
| Alice | Math |
| Alice | English |
| Bob | Math |
| Bob | History |
| Charlie | Math |
| Charlie | English |
여기서
| Course |
|:-------:|
| Math |
| English |
이것을 나눈다면,
위 테이블에서 어떤 학생이 아래의 Course 를 '모두' 수강했는지를 나타내는 결과로,
| Student |
| :-----: |
| Alice |
| Charlie |
이런 값이 반환됩니다.
(쿼리 최적화 규칙)
- 데이터 모델은 설계 및 운영 방식, 현실 상황에 따라 다양하게 구성되므로 절대적인 최적화 규칙은 존재하지 않습니다.
다만, 다양한 경험에 따라 증명된 일반적인 최적화 규칙이 몇가지 존재합니다.
- 쿼리 최적화의 일반적 규칙
1. 원치않는 데이터를 가장 많이 제거 할 수 있는 추출(Project) 연산을 최대한 일찍 수행합니다.
2. 원치 않는 튜플이 제거되는 선택(Select) 연산을 최대한 일찍 수행합니다.
3. 조인(Join) 연산은 조합을 통해 처리해야할 데이터를 많이 만들어내어 성능에 영향을 많이 끼치므로 가급적 마지막에 수행합니다.
요약하자면, 처리해야할 데이터의 수를 줄이는 것을 최우선으로 두는 방식이 일반적인 최적화 방식이라 할 수 있습니다.
[SQL 사용법]
- 여기서부터는 DBMS 에 실제로 명령을 내릴 수 있는 SQL 문법을 정리하겠습니다.
SQL 의 처리 기능에 따라 DDL, DML 등으로 먼저 그룹화를 하고, 그 아래에 각 명령어에 대한 상세 설명을 기술할 것입니다.
(데이터 정의어(DDL, Data Definition Language))
((CREATE 문))
- DB, TABLE, INDEX, VIEW 등의 데이터베이스 객체를 생성하는 명령어입니다.
CREATE <객체 유형> <객체명> (<옵션>);
위와 같은 형태로 이루어집니다.
위 명령어 설명을 하자면,
CREATE 로 시작하며, <객체 유형> 에는 TABLE, DB 와 같은 객체 유형을 대체해서 넣어주는 것입니다.
CREATE TABLE
이런 식으로요.
<객체명> <옵션> 역시 원하는 설정으로 대체하여 입력하면 되는데,
옵션은 () 로 감싸져 있죠?
이는 옵션 부분이 실질적으로 길기에 축약한다는 의미입니다.
- 테이블 생성
아래에서는 데이터베이스 객체중 일반적으로 많이 사용되는 TABLE 객체를 생성하는 문법을 정리하겠습니다.
CREATE TABLE <테이블명>(
<컬럼명> <데이터유형> [<제약조건>] [, ...],
[테이블 제약조건]
);
위와 같습니다.
위 문법의 메타언어를 설명하자면,
CREATE TABLE 로 <테이블명> 이름의 테이블을 만드는데,
<컬럼명> 부터, [<제약조건>] 까지 해당 테이블의 컬럼 정보를 입력합니다.
[] 로 감싸진 부분은 필수가 아닌 옵션입니다.
그리고 [, ...] 으로 되어있는 부분은, 앞서 기술된 컬럼 설정을 계속 추가할 수 있다는 것이고,
테이블 옵션의 가장 마지막에는 테이블 제약조건(unique, join 등)을 넣을 수도 있고 넣지 않을 수도 있다는 의미입니다.
예시를 보고 이해하겠습니다.
CREATE TABLE 도서대여(
대여일 DATE NOT NULL,
학번 INT,
도서명 CHAR(20) NOT NULL,
FOREIGN KEY (학번) REFERENCES 학생(학번)
ON UPDATE CASCADE
ON DELETE RESTRICT
);
처음 SQL 을 접하시는 분이시라면 무슨 문장인지 알 수 없을텐데,
문법 부분과 비교하며 보자면,
도서대여라는 이름의 TABLE 을 만들고, 테이블 안에는 대여일, 학번, 도서명이라는 컬럼이 설정되어 있습니다.
컬럼 설정은 보이는데로 컬럼명, 데이터 타입으로 이루어져 있고, NOT NULL 과 같은 옵션을 붙일 수도 있고, 이러한 컬럼 설정을 반복해서 설정 가능합니다.
컬럼설정 다음으로는 테이블 제약사항을 설정하는 것으로,
위 예시에서는 위쪽에 정의한 컬럼 정보들 중에서 학번이라는 속성을 학생이란 테이블의 학번과 연결하는 외래키로 지정한다는 제약사항을 설정한 것입니다. FOREIGN KEY 설정줄의 아래쪽은 외래키의 Origin 이 되는 학생 테이블의 값이 update 되면 그 값을 반영하고, DELETE 되면 해당 테이블을 연결중인 하위 테이블이 존재할 시에 DELETE 를 못 하게 막는 제약입니다.
예시를 통해 형태를 파악했으므로 세부 사항을 알아보겠습니다.
- 데이터 유형
각 컬럼은 데이터 유형을 설정해야 합니다.
데이터 유형의 의의는, 데이터베이스 테이블에 저장된 데이터를 어떤 공간으로 저장할지, 어떤식으로 해석할지에 대한 설정입니다.
아시다시피 소프트웨어 데이터는 "10011101010001" 이런식의 1과 0의 무수한 조합으로 이루어집니다.
데이터가 물리적으로 위와 같이 저장되어 있을 때, 이를 문자열로 해석할지, 정수로 해석할지, 실수로 해석할지, 참/거짓으로 해석할지 등의 해석 방식을 모른다면 데이터를 복원할 수 없을 것입니다.
이뿐아니라 물리적 저장 공간은 한계가 존재합니다.
이진수를 10개 저장할 공간을 마련해두겠다, 이진수를 100개 저장할 공간을 마련해두겠다.
이런식으로 저장될 최대 공간을 정해두어야 하며, 데이터 해석시에도 마련된 공간 단위로 데이터를 가져와서 이를 해석하는 것이죠.
데이터 유형이 이러한 역할을 합니다.
기본적인 변수 타입을 몇가지 나열해보자면,
1. INT : 정수
2. DOUBLE : 실수(부동소수점)
3. CHAR(N) : 최대 N 개의 문자 입력이 가능한 고정크기 문자열
4. VARCHAR(N) : 최대 N개의 문자 입력이 가능한 가변크기 문자열
5. DATE : 날짜
위와 같은 종류가 있습니다.
DBMS 별 지원해주는 특화된 변수도 있으며, 각 데이터 타입의 세부 내용을 잘 이해하고 적절히 설정해야 할 것입니다.
데이터 타입에 대한 프로그래밍과 연관된 설명은 이 링크의 예시를 보시면 어떤 종류가 있는지를 대략 파악하실 수 있을 것입니다.
- 컬럼 제약조건
각 컬럼별 지정할 수 있는 대표적 제약조건은 아래와 같습니다.
1. UNIQUE : 유일키 정의
2. NOT NULL : NULL 값 허용 안함
3. CHECK : 컬럼에 허용되는 값을 제한
- 테이블별 제약조건
각 테이블에 지정 가능한 제약조건은 대표적으로 아래와 같습니다.
1. PRIMARY KEY (<컬럼명>[, ...]) : 해당 컬럼을 기본키(UNIQUE + NOT NULL)로 만듭니다.
2. FOREIGN KEY (<컬럼명>[, ...]) REFEFENCES (<컬럼명>[, ...]) : 본 테이블의 컬럼을 다른 테이블 컬럼에 대한 외래키로 만듭니다. 추가로 붙을 수 있는 ON UPDATE 와 ON DELETE 옵션은 위에서 설명했으므로 생략합니다.
- ON UPDATE, ON DELETE 의 처리 옵션은 아래와 같습니다.
1. NO ACTION : 무시
2. SET DEFAULT : 관련 튜플을 기본값으로 수정
3. SET NULL : 관련 튜플을 NULL 값으로 수정
4. CASCADE : 관련 튜플을 동일한 명령으로 일괄처리
5. RESTRICT : 관련 튜플이 없는 경우에만 처리
- CREATE 를 사용하여 테이블을 생성할 때에는 다른 테이블을 복사할 수도 있습니다.
CREATE TABLE <테이블명> AS <SELECT 문>
아직 설명하지는 않았지만, SELECT 문을 사용하면 특정 테이블의 현재 저장된 데이터를 조회할 수 있습니다.
이렇게 얻어온 조회 결과와 동일한 형태의 테이블을 생성하고, 그 데이터를 새로 생성된 테이블 안에 복사하여 입력하게 됩니다.
((ALTER 문))
- 이미 생성된 테이블의 정보를 수정할 때 사용하는 명령어입니다.
- ADD
ALTER TABLE <테이블명> ADD <필드명> <데이터타입> [<위치옵션>];
새로운 컬럼을 추가할 때에는 위와 같이 추가합니다.
위치옵션은 해당 컬럼이 몇번째 컬럼인지를 설정하는 옵션으로, 만약 이를 입력하지 않으면 모든 컬럼의 마지막에 생성하게 됩니다.
- MODIFY
컬럼의 데이터 유형을 수정할 때 사용하는 명령어입니다.
ALTER TABLE <테이블명> MODIFY <컬럼명> <데이터유형>;
- RENAME COLUMN
컬럼의 컬럼명을 수정할 때 사용하는 명령어입니다.
ALTER TABLE <테이블명> RENAME COLUMN <원본컬럼명> TO <변경컬럼명>;
- 제약 조건 추가
테이블에 제약 조건을 추가하는 명령어입니다.
ALTER TABLE <테이블명> ADD CONSTRAINT <제약조건명> <제약조건>;
- 제약 조건 삭제 혹은 비활성화
존재하는 제약 조건을 삭제하거나 비활성화 할 때에 사용하는 명령어입니다.
ALTER TABLE <테이블명> ENABLE|DISABLE|DROP CONSTRAINT <제약조건명>;
위와 같이 특정 제약조건 활성을 설정할 수 있는데,
활성화하는 ENABLE, 비활성화 하는 DISABLE, 삭제하는 DROP 중 하나의 옵션을 사용합니다.
((DROP 문))
- 데이터베이스 객체를 삭제하는 명령어입니다.
다른 DDL 보다 훨씬 위험도가 높은 명령어이므로 사용시 주의해야 합니다.
DROP <객체유형> <객체명> [<삭제옵션>];
- 삭제 명령 관련해서는, 데이터베이스 객체 중 테이블 그자체를 제거하는 DROP 문 외에도, 테이블 구조는 유지한 상태에서 저장되어 있는 모든 레코드를 삭제하는 TRUNCATE 명령어도 존재합니다.
TRUNCATE TABLE <테이블명>;
(데이터 조작어(DML, Data Manipulation Language))
- DML 은 데이터베이스 객체 중 테이블이 보유한 데이터 레코드를 조회하고 조작하는 종류의 명령어 그룹을 의미합니다.
DBMS 를 사용하는 애플리케이션은 이를 사용하여 서비스를 구성합니다.
- 실무적으로는 CRUD 라고 부르며,
데이터를 입력(CREATE = 생성), 조회(READ), 수정(UPDATE), 삭제(DELETE) 하는 것이 DB를 사용하는 애플리케이션의 가장 기본이 되는 기능이고 전부라고도 할 수 있습니다.
참고로 실무에서 C, U, D 와 같은 데이터 조작 명령어는 작업량이 적은 편이고, 서비스 기능에서 요구되는 정보를 조회하는 R 부분의 작업량은 월등히 많은 편이라서 저는 C,U,D 를 먼저 만들고, R 을 나중에 만드는 편입니다.
((INSERT 문))
- 특정 테이블에 데이터 Row 를 하나 입력하기 위해선,
INSERT INTO <테이블명> VALUES (<값>[,....]);
위와 같이 입력합니다.
값의 순서는 현재 테이블 구조의 컬럼 순서와 동일한 순서로 입력하면 되며,
누락된 데이터가 존재하거나 순서가 다르면 에러가 발생할 수 있습니다.
만약 컬럼명을 명시해서 입력하려면,
INSERT INTO <테이블명> (<컬럼명>[,...]) VALUES (<값>[,...]);
위와 같이 설정한 컬럼명의 순서에 맞춰서 값을 입력하면 됩니다.
만약 다른 테이블에 저장된 레코드를 그대로 복사하여 값을 삽입하기 위해선,
INSERT INTO <테이블명> (<필드>[, ...]) <SELECT문>
이렇게 SELECT 문으로 받아온 값을 필드 값으로 입력하도록 할 수 있습니다.
((UPDATE 문))
- 지정한 조건에 만족하는 레코드들의 해당 컬럼의 필드값을 갱신하는 명령어입니다.
UPDATE <테이블명> SET <컬럼명>=<값>[, ...] [WHERE <조건식>];
컬럼명 = 값을 반복함으로써 각 컬럼별 수정해야할 값들을 전부 지정하며, WHERE 문을 작성하지 않으면 모든 데이터, WHERE 문을 적용하면 해당 조건에 맞는 값들만 수정됩니다.
앞서 WHERE 문에 대해서는 설명하지 않았기에 예시를 들어 설명하자면,
UPDATE employees
SET salary = 5000
WHERE name = '홍길동';
위와 같이 employees 테이블 안의 name 컬럼 값이 '홍길동' 인 모든 rows 의 salary 속성값을 5000 으로 입력하는 의미로,
WHERE name = '홍길동' AND (age = 18 OR (gender IS NOT NULL AND gender != 'M'))
이런식으로 조건을 작성하면 됩니다.
((DELETE 문))
- 지정한 조건을 만족하는 레코드를 삭제하는 명령어입니다.
UPDATE 문과 비슷하며, 역시나 조건문을 작성하지 않으면 테이블 내 모든 데이터가 삭제됩니다.
DELETE FROM <테이블명> [WHERE <조건식>];
((SELECT 문))
- 지정된 형식과 조건에 대응되는 결과를 릴레이션 형태로 출력하는 명령어입니다.
SQL 명령어중 가장 많이 사용되는 명령어로, 가장 복잡하기도 합니다.
SELECT [ALL|DISTINCT] [<컬럼명>[, ...]] FROM <테이블명>
[WHERE <조건식> [GROUP BY <컬럼명> [HAVING <조건식>]]]
[ORDER BY <컬럼명> [ASC|DESC]];
위 명령어 구조를 본다면 조건이 많은 편인데,
SELECT * FROM 테스트;
가장 축약된 예시로, 위와 같이 작성하는 것도 가능하다는 것에서 시작합시다.
위 예시는 테스트 테이블에서 모든 컬럼의 모든 데이터를 가져온다는 뜻이고,
* 가 아닌 특정 컬럼명을 나열하면 해당 컬럼명으로 반환한다는 뜻입니다.
ALL 과 DISTINCT 옵션은 안 적는다면 기본값이 ALL 이고, DISTINCT 를 적는다면, 조회 결과 중 중복된 값은 하나만 가져온다는 뜻입니다.
DISTINCT 예시로 보자면,
| order\_id | customer\_name | product | quantity |
| --------- | -------------- | -------- | -------- |
| 1 | Alice | Laptop | 1 |
| 2 | Bob | Mouse | 2 |
| 3 | Alice | Laptop | 1 |
| 4 | Charlie | Monitor | 1 |
| 5 | Bob | Mouse | 2 |
| 6 | Alice | Keyboard | 1 |
위 테이블을 기준으로,
단일 컬럼 DISTINCT 는,
SELECT DISTINCT customer_name FROM orders;customer_name
Alice
Bob
Charlie
이렇게 단일 컬럼 내의 중복값이 없도록 반환하고,
다중 컬럼 DISTINCT 는,
SELECT DISTINCT customer_name, product FROM orders;| customer\_name | product |
| -------------- | -------- |
| Alice | Laptop |
| Bob | Mouse |
| Charlie | Monitor |
| Alice | Keyboard |
위와 같이 값의 조합에 중복값이 없도록 반환합니다.
보통은,
SELECT COUNT(DISTINCT customer_name) FROM orders;
이렇게 COUNT 함수와 함께 사용하여 중복 없는 값의 개수를 셀 때에 사용합니다.
WHERE 문에 대해서는 생략합니다.
그리고, ORDER BY 명령어는 SELECT 명령어 가장 뒤에 명시하며,
조회된 데이터 결과를 조건에 따라 정렬하는 역할을 합니다.(그렇기에 모든 결과가 나온 마지막에 수행됩니다.)
ORDER BY orders DESC
이런식으로 작성한다면, 이 뜻은 검색된 결과의 orders 컬럼의 값을 기준으로 내림차순으로 정렬하라는 뜻입니다.
ASC 는 오름차순으로, 만약 DESC, ASC 를 모두 안 적는다면 기본값인 ASC 가 적용됩니다.
ORDER BY 옵션은,
ORDER BY column1 ASC, column2 DESC
이렇게 1번 컬럼을 기준으로 먼저 오름차순으로 정렬하고, 다음으론 2번 컬럼을 기준으로 내림차순으로 정렬하는 방식으로 여러 컬럼을 정렬의 기준으로 만들 수도 있습니다.
GROUP BY 옵션은,
SELECT 결과를 특정 컬럼 값을 기준으로 그룹으로 묶는 옵션입니다.
예를들어,
| order\_id | customer\_name | product | quantity |
| --------- | -------------- | -------- | -------- |
| 1 | Alice | Laptop | 1 |
| 2 | Bob | Mouse | 2 |
| 3 | Alice | Laptop | 1 |
| 4 | Charlie | Monitor | 1 |
| 5 | Bob | Mouse | 1 |
| 6 | Alice | Keyboard | 1 |
이런 데이터가 있을 때,
SELECT customer_name, COUNT(*) AS order_count
FROM orders
GROUP BY customer_name;| customer\_name | order\_count |
| -------------- | ------------ |
| Alice | 3 |
| Bob | 2 |
| Charlie | 1 |
이렇게 고객별 주문 횟수를 셀 때에 응용할 수 있습니다.
(참고로 컬럼 우측에 붙은 AS 는 검색 결과의 컬럼에 별칭을 설정하는 것으로, 원래는 COUNT(*) 로 표시될 것을 보기 좋게 위와 같이 order_count 로 표시하게 한 것입니다.)
GROUP BY 와 함께 쓸 수 있는 또다른 옵션으로는 HAVING 이 있습니다.
WHERE 과 동일한 조건문이지만 WHERE 과 다른점으로는 HAVING 이란 그룹핑 이후에 실행되는 조건이며, WHERE 은 그룹핑 이전에 실행되는 조건이므로, GROUP BY 에서 그룹핑한 결과에 조건을 지정할 때에는 HAVING 을 사용해야 한다는 것입니다.
위 GROUP BY 와 동일한 예시 테이블에서,
고객별 총 주문 수량이 2 이상인 고객만을 추려내기 위해서는,
SELECT customer_name, SUM(quantity) AS total_quantity
FROM orders
GROUP BY customer_name
HAVING SUM(quantity) >= 2;| customer\_name | total\_quantity |
| -------------- | --------------- |
| Alice | 3 |
| Bob | 2 |
이런 식으로 HAVING 을 사용하면 됩니다.
- 집계 함수
SQL 에는 기본으로 제공되는 함수가 제공됩니다.
앞서 예시에서 보았듯 조회된 Rows 개수를 세어 반환하는 COUNT 함수나, 조회된 모든 결과를 더한 결과를 반환하는 SUM 등의 함수가 있습니다.
1. COUNT : 레코드 개수
2. SUM : 값의 합
3. AVG : 값의 평균
4. MAX : 조회된 값 중 최대값
5. MIN : 조회된 값 중 최소값
6. STDDEV : 표준편차
7. VARIANCE : 분산
- 윈도우 함수
윈도우 함수는, 결과 집합의 각 행에 대해 집계 계산을 수행하면서도, 결과 집합을 그룹화하지 않고 각 행을 유지할 수 있게 해주는 함수입니다. OLAP(OnLine Analytical Processing) 함수라고도 부릅니다.
이 함수는 집계 함수와 유사하지만, GROUP BY와 달리 전체 집합을 그룹화하지 않고 개별 행을 기준으로 계산을 수행합니다.
윈도우 함수는 보통 누적 합, 순위 계산, 이동 평균과 같은 분석 작업에 사용됩니다.
예를 들어가며 설명하겠습니다.
| emp\_id | emp\_name | dept | score\_date | score |
| ------- | --------- | --------- | ----------- | ----- |
| 1 | Alice | Sales | 2025‑01‑01 | 10 |
| 2 | Alice | Sales | 2025‑01‑05 | 20 |
| 3 | Alice | Sales | 2025‑01‑10 | 30 |
| 4 | Bob | Sales | 2025‑01‑02 | 15 |
| 5 | Bob | Sales | 2025‑01‑06 | 25 |
| 6 | Carol | Marketing | 2025‑01‑03 | 12 |
| 7 | Carol | Marketing | 2025‑01‑07 | 18 |
| 8 | Carol | Marketing | 2025‑01‑12 | 24 |
이런 테이블이 있다고 할 때,
1. dale_date 에 따른 amount 의 누적합
SELECT
emp_id,
emp_name,
score_date,
score,
SUM(score)
OVER (
PARTITION BY emp_name
ORDER BY score_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM employee_scores;| emp\_id | emp\_name | score\_date | score | running\_total |
| ------- | --------- | ----------- | ----- | -------------- |
| 1 | Alice | 2025‑01‑01 | 10 | 10 |
| 2 | Alice | 2025‑01‑05 | 20 | 30 |
| 3 | Alice | 2025‑01‑10 | 30 | 60 |
| 4 | Bob | 2025‑01‑02 | 15 | 15 |
| 5 | Bob | 2025‑01‑06 | 25 | 40 |
| 6 | Carol | 2025‑01‑03 | 12 | 12 |
| 7 | Carol | 2025‑01‑07 | 18 | 30 |
| 8 | Carol | 2025‑01‑12 | 24 | 54 |
2. 순위 함수: RANK(), DENSE_RANK(), ROW_NUMBER()
SELECT
emp_name,
score_date,
score,
RANK() OVER (PARTITION BY emp_name ORDER BY score DESC) AS rank_desc,
DENSE_RANK()OVER (PARTITION BY emp_name ORDER BY score DESC) AS dense_rank_desc,
ROW_NUMBER()OVER (PARTITION BY emp_name ORDER BY score_date ASC) AS row_num
FROM employee_scores;| emp\_name | score\_date | score | rank\_desc | dense\_rank\_desc | row\_num |
| --------- | ----------- | ----- | ---------- | ----------------- | -------- |
| Alice | 2025‑01‑10 | 30 | 1 | 1 | 1 |
| Alice | 2025‑01‑05 | 20 | 2 | 2 | 2 |
| Alice | 2025‑01‑01 | 10 | 3 | 3 | 3 |
| Bob | 2025‑01‑06 | 25 | 1 | 1 | 1 |
| Bob | 2025‑01‑02 | 15 | 2 | 2 | 2 |
| Carol | 2025‑01‑12 | 24 | 1 | 1 | 1 |
| Carol | 2025‑01‑07 | 18 | 2 | 2 | 2 |
| Carol | 2025‑01‑03 | 12 | 3 | 3 | 3 |
RANK(): 동점이 있으면 순위가 건너뜁니다.
DENSE_RANK(): 동점 있어도 순위가 건너뛰지 않습니다.
ROW_NUMBER(): 조건 순서대로 고유 일련번호를 매깁니다.
3. 이동 평균 (Moving Average)
SELECT
emp_name,
score_date,
score,
AVG(score)
OVER (
PARTITION BY emp_name
ORDER BY score_date
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS moving_avg_2
FROM employee_scores;| emp\_name | score\_date | score | moving\_avg\_2 |
| --------- | ----------- | ----- | -------------- |
| Alice | 2025‑01‑01 | 10 | 10.0 |
| Alice | 2025‑01‑05 | 20 | 15.0 |
| Alice | 2025‑01‑10 | 30 | 25.0 |
| Bob | 2025‑01‑02 | 15 | 15.0 |
| Bob | 2025‑01‑06 | 25 | 20.0 |
| Carol | 2025‑01‑03 | 12 | 12.0 |
| Carol | 2025‑01‑07 | 18 | 15.0 |
| Carol | 2025‑01‑12 | 24 | 21.0 |
현재 행과 바로 이전 행의 평균을 계산합니다.
4. 첫 번째·마지막 값: FIRST_VALUE(), LAST_VALUE()
SELECT
emp_name,
score_date,
score,
FIRST_VALUE(score)
OVER (PARTITION BY emp_name ORDER BY score_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS first_score,
LAST_VALUE(score)
OVER (PARTITION BY emp_name ORDER BY score_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_score
FROM employee_scores;| emp\_name | score\_date | score | first\_score | last\_score |
| --------- | ----------- | ----- | ------------ | ----------- |
| Alice | 2025‑01‑01 | 10 | 10 | 30 |
| Alice | 2025‑01‑05 | 20 | 10 | 30 |
| Alice | 2025‑01‑10 | 30 | 10 | 30 |
| Bob | 2025‑01‑02 | 15 | 15 | 25 |
| Bob | 2025‑01‑06 | 25 | 15 | 25 |
| Carol | 2025‑01‑03 | 12 | 12 | 24 |
| Carol | 2025‑01‑07 | 18 | 12 | 24 |
| Carol | 2025‑01‑12 | 24 | 12 | 24 |
FIRST_VALUE(): 파티션에서 가장 이른(ORDER BY) 행의 값을 항상 반환
LAST_VALUE(): 파티션에서 가장 늦은 행의 값을 반환 (윈도우 프레임 설정 주의)
5. 이전/다음 행 값 참조: LAG(), LEAD()
SELECT
emp_name,
score_date,
score,
LAG(score, 1) OVER (PARTITION BY emp_name ORDER BY score_date) AS prev_score,
LEAD(score, 1) OVER (PARTITION BY emp_name ORDER BY score_date) AS next_score
FROM employee_scores;| emp\_name | score\_date | score | prev\_score | next\_score |
| --------- | ----------- | ----- | ----------- | ----------- |
| Alice | 2025‑01‑01 | 10 | NULL | 20 |
| Alice | 2025‑01‑05 | 20 | 10 | 30 |
| Alice | 2025‑01‑10 | 30 | 20 | NULL |
| Bob | 2025‑01‑02 | 15 | NULL | 25 |
| Bob | 2025‑01‑06 | 25 | 15 | NULL |
| Carol | 2025‑01‑03 | 12 | NULL | 18 |
| Carol | 2025‑01‑07 | 18 | 12 | 24 |
| Carol | 2025‑01‑12 | 24 | 18 | NULL |
LAG(col, 1): 이전 행의 col 값을, 없으면 NULL
LEAD(col, 1): 다음 행의 col 값을, 없으면 NULL
- 조건식에 사용할 수 있는 예약어는 아래와 같습니다.
1. AND
연산자 좌우의 모든 조건을 만족해야 할 때 사용합니다.
-- Sales 부서이면서 연봉이 50,000 이상인 직원 조회
SELECT *
FROM employees
WHERE department = 'Sales'
AND salary >= 50000;
이렇게 AND 조건을 사용할 수 있고,
-- 연봉이 50,000 이상 60,000 이하인 직원 조회
SELECT emp_id, name, department, salary
FROM employees
WHERE salary BETWEEN 50000 AND 60000;
숫자 범위를 BETWEEN 으로 제약할 수 있습니다.( 위 예시는 salary >= a AND salary <= b 와 동일합니다.)
참고로 모든 조건문은 (1 + 1) * 3 이런 수식과 같이, 소괄호를 사용하여 먼저 적용될 조건의 범위를 지정할 수 있습니다.
2. OR
연산자 좌우 조건 중 하나만 만족하면 될 경우에 사용합니다.
-- Sales 부서이거나 HR 부서인 직원 조회
SELECT *
FROM employees
WHERE department = 'Sales'
OR department = 'HR';-- 부서가 Sales, HR 중 하나인 직원 조회
SELECT *
FROM employees
WHERE department IN ('Sales', 'HR');
위 예시와 같이 연속된 OR 은 IN 으로 대체 가능합니다.
3. IS NULL
값이 비어있는 경우를 검색해야 하는 경우에는 <컬럼> = NULL 이 아니라, <컬럼> IS NULL 로 검색합니다.
-- 이메일 정보가 없는 직원
SELECT emp_id, name
FROM employees
WHERE email IS NULL;
위와 같이 사용하며, IS NOT NULL 이라면 반대로 값이 비어있지 않는 레코드를 검색합니다.
4. LIKE
특정 문자 패턴을 가지는 문자열을 검색해야 하는 경우에 사용합니다.
-- 이름이 'A'로 시작하는 직원
SELECT *
FROM employees
WHERE name LIKE 'A%';-- 이름 세 글자 중 가운데 문자가 'o'인 직원 (예: B_o)
SELECT *
FROM employees
WHERE name LIKE '_o_';
위와 같이 사용합니다.
LIKE %강 : 강으로 끝나는 문자열 패턴
LIKE 강% : 강으로 시작하는 문자열 패턴
LIKE %강% : 강을 포함하는 문자열 패턴
LIKE 강_ : 강으로 시작하는 2글자 문자열 패턴
LIKE _강_ : 강이 가운데 있는 3글자 문자열 패턴
LIKE 강__ : 강으로 시작하는 3글자 문자열 패턴
이렇습니다.
- 하위 질의(Sub Query)
메인 쿼리문 안에 포함된 또 하나의 쿼리를 서브쿼리라고 합니다.
서브 쿼리는 소속된 메인 쿼리의 실행 이전에 한번만 실행되며, 결과값은 외부 쿼리에서 활용하는 요소가 됩니다.
서브 쿼리는 소괄호로 묶으며, 서브 쿼리의 결과는 외부 쿼리가 기대하는 데이터 형태와 일치해야 합니다.
서브 쿼리는 외부 쿼리가 사용할 목적일 뿐이므로 ORDER BY 로 정렬하지 않는 것이 좋습니다.
예시를 보고 이해하겠습니다.
SELECT *
FROM employees
WHERE emp_id IN (
SELECT emp_id
FROM orders
WHERE amount > 250
);
이렇게 서브쿼리를 작성했다면,
소괄호 안의 서브쿼리의 조건으로 조회한 emp_id 값들에 일치하는 employees 테이블의 emp_id 의 rows 들을 반환하도록 한 것입니다.
'서브 쿼리 결과값을 상위 쿼리에서 활용' 하는 것을 이로써 이해하실 수 있을 것입니다.
- GROUP BY 응용
앞서 GROUP BY 가 컬럼별 그룹을 만드는 것이라 설명을 했습니다.
이번에는 이를 응용하여 보다 다양한 그루핑 방식을 적용하는 방법을 알아보겠습니다.
1. 기본
SELECT region, product, SUM(sales)
FROM sales_data
GROUP BY region, product;
region, product별로 sales를 합산.
출력: (region A, product X), (region A, product Y), ...
2. ROLLUP
지정한 컬럼 순서대로 차례로 집계를 “위로” 올려가며 계산
SELECT region, product, SUM(amount)
FROM sales
GROUP BY ROLLUP(region, product);| region | product | sum |
| ------ | ------- | --- |
| East | A | 100 |
| East | B | 150 |
| East | NULL | 250 |
| West | A | 200 |
| West | B | 250 |
| West | NULL | 450 |
| NULL | NULL | 700 |
(region, product) 별 합계
(region) 별 합계 (모든 product 합)
전체 합계 (region·product 무시)
3. CUBE
지정한 컬럼들의 모든 조합에 대해 교차 집계
SELECT region, product, SUM(amount)
FROM sales
GROUP BY CUBE(region, product);| region | product | sum |
| ------ | ------- | --- |
| East | A | 100 |
| East | B | 150 |
| East | NULL | 250 |
| West | A | 200 |
| West | B | 250 |
| West | NULL | 450 |
| NULL | A | 300 |
| NULL | B | 400 |
| NULL | NULL | 700 |
(region, product) 별
(region) 별
(product) 별
전체 합계
4. GROUPING SETS
필요한 집계 조합을 명시적으로 나열
SELECT region, product, SUM(amount)
FROM sales
GROUP BY GROUPING SETS (
(region, product),
(region),
(product),
()
);
ROLLUP이나 CUBE가 자동으로 만들어주는 것보다 더 세밀하게 조정할 수 있습니다.
위와 같이 SETS 설정을 한다면 CUBE 예시와 동일한 집계 결과를 얻습니다.
5. GROUPING() 함수
ROLLUP, CUBE, GROUPING SETS 등의 다차원 집계에서 발생하는 NULL 값이 “실제 데이터의 NULL”인지 “집계 레벨에서 생성된 NULL”인지를 구분하기 위해 사용합니다.
GROUPING(col_name)
또는 여러 컬럼을 한 번에 확인할 때는
GROUPING_ID(col1, col2, …)
GROUPING(col) 은 해당 컬럼이 현재 행의 집계 레벨에서 “묶음 차원을 축소(위로 집계)”하여 NULL로 표현된 것이라면 1을,
실제 데이터의 NULL 값이거나 기본 차원 집계라면 0을 반환합니다.
GROUPING_ID(...) 는 각 컬럼별 GROUPING() 값을 비트연산으로 조합한 정수 ID를 반환합니다.
SELECT
region,
product,
SUM(amount) AS total_amount,
GROUPING(region) AS is_region_rolled_up,
GROUPING(product) AS is_product_rolled_up,
GROUPING_ID(region, product) AS grouping_id
FROM sales
GROUP BY ROLLUP(region, product);| region | product | total\_amount | is\_region\_rolled\_up | is\_product\_rolled\_up | grouping\_id |
| ------ | ------- | ------------- | ---------------------- | ----------------------- | ------------ |
| East | A | 100 | 0 | 0 | 0 |
| East | B | 150 | 0 | 0 | 0 |
| East | NULL | 250 | 0 | 1 | 1 |
| West | A | 200 | 0 | 0 | 0 |
| West | B | 250 | 0 | 0 | 0 |
| West | NULL | 450 | 0 | 1 | 1 |
| NULL | NULL | 700 | 1 | 1 | 3 |
is_region_rolled_up :
1 → region 컬럼이 집계 레벨에서 NULL로 표현된 경우 (전체 집계)
0 → 실제 값 또는 단일 레벨 집계
is_product_rolled_up :
1 → product 컬럼이 상위 집계(지역별 합계 또는 전체합)에서 NULL로 표현된 경우
0 → 실제 값 또는 지역별 집계
grouping_id :
비트로 결합된 값 (region 이 상위 집계면 2의 자리, product 가 상위 집계면 1의 자리)
예를 들어 3(2+1)은 둘 다 롤업된 최상위 전체 합계 행임을 의미
- JOIN 조회
RDBMS 는 외래키로 두 테이블간 관계가 성립될 수 있습니다.
이러한 외래키를 바탕으로 다른 테이블에 저장된 Row 를 타고 올라가 해당 테이블에 있는 매칭되는 데이터들을 조회하는 것은 SQL 조회의 주된 패턴입니다.
JOIN 은 아래와 같은 여러 종류가 존재합니다.
예시를 위한 테이블은 아래와 같으며,
-- 테이블 정의 및 샘플 데이터
-- customers
customer_id | name
----------- | -----
1 | Alice
2 | Bob
3 | Charlie
-- orders
order_id | customer_id | product
-------- | ----------- | -------
101 | 1 | TV
102 | 1 | Radio
103 | 3 | Laptop
104 | 4 | Phone
1. INNER JOIN
두 테이블의 기준 필드가 일치하는 레코드만 조인합니다.
예를 들자면,
SELECT
c.customer_id,
c.name,
o.order_id,
o.product
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id;| customer\_id | name | order\_id | product |
| -----------: | ------- | --------: | ------- |
| 1 | Alice | 101 | TV |
| 1 | Alice | 102 | Radio |
| 3 | Charlie | 103 | Laptop |
2. OUTER JOIN
두 테이블의 기준 필드가 일치하지 않는 모든 레코드도 함께 조인합니다.
예를 들자면,
SELECT
c.customer_id,
c.name,
o.order_id,
o.product
FROM customers AS c
FULL OUTER JOIN orders AS o
ON c.customer_id = o.customer_id;| customer\_id | name | order\_id | product |
| -----------: | ------- | --------: | ------- |
| 1 | Alice | 101 | TV |
| 1 | Alice | 102 | Radio |
| 2 | Bob | NULL | NULL |
| 3 | Charlie | 103 | Laptop |
| NULL | NULL | 104 | Phone |
위와 같이 좌우측 일치하지 않는 정보에는 NULL 이 입력됩니다.
3. LEFT JOIN
왼쪽 테이블 레코드는 전부 포함하고, 오른쪽 테이블 레코드는 기준 필드가 일치하는 것만 조인합니다.
예를 들자면,
SELECT
c.customer_id,
c.name,
o.order_id,
o.product
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id;| customer\_id | name | order\_id | product |
| -----------: | ------- | --------: | ------- |
| 1 | Alice | 101 | TV |
| 1 | Alice | 102 | Radio |
| 2 | Bob | NULL | NULL |
| 3 | Charlie | 103 | Laptop |
위와 같이 우측에 일치하지 않는 데이터는 NULL 이 입력됩니다.
4. RIGHT JOIN
오른쪽 테이블 레코드는 전부 포함하고, 왼쪽 테이블 레코드는 기준 필드가 일치하는 것만 조인합니다.
예를 들자면,
SELECT
c.customer_id,
c.name,
o.order_id,
o.product
FROM customers AS c
RIGHT JOIN orders AS o
ON c.customer_id = o.customer_id;| customer\_id | name | order\_id | product |
| -----------: | ------- | --------: | ------- |
| 1 | Alice | 101 | TV |
| 1 | Alice | 102 | Radio |
| 3 | Charlie | 103 | Laptop |
| NULL | NULL | 104 | Phone |
위와 같이 좌측에 일치하지 않는 데이터는 NULL 이 입력됩니다.
(데이터 제어어(DCL, Data Control Language))
((GRANT 문))
- 특정 사용자 및 그룹(역할)에게 DBMS 에 관련된 특정 권한을 부여합니다.
옵션에 따라서 부여받은 권한에 대해서 다른 사용자에게 다시 부여가 가능합니다.
GRANT <권한 유형> TO <대상>
[WITH GRANT OPTION|WITH ADMIN OPTION];
위의 옵션중,
WITH GRANT OPTION 은, 부여받은 권한을 다른 사용자에게 부여, 회수가 가능한 권한을 주는 것이고,
WITH ADMIN OPTION 은, 부여받은 권한을 다른 사용자에게 부여만 가능합니다.
만약 이 옵션을 적용하지 않는다면 부여받은 권한을 다른 사용자에게 부여하지도 못합니다.
- 권한 유형은 기존 SQL 명령들과 특정 ROLE 을 지정할 수 있습니다.
- 권한 부여 대상은 특정 사용자 및 ROLE, 모든 인원(PUBLIC)에게 부여할 수 있습니다.
((REVOKE 문))
- 대상에게 부여된 권한을 회수합니다.
REVOKE <권한 유형> FROM <대상>;
WITH GRANT OPTION 으로 권한을 부여한 사용자의 권한을 회수하면, 권한을 부여받은 사용자가 부여한 또 다른 사용자의 권한도 함께 회수됩니다.
((ROLE 문))
- 사용자에게 허가 가능한 권한들의 집합 또는 같은 권한을 부여받는 사용자 그룹을 뜻합니다.
사용자 그룹의 관리는 역할 기반 접근 제어(RBAC, Role Based Access Control) 방식을 사용합니다.
- RBAC 는, 개별적 분할이 아닌, 수행하는 역할을 기반으로 나누고 사용자 그룹에 권한을 부여하는 방식입니다.
- ROLE 을 생성하기 위해선 CREATE ROLE 권한이 필요합니다.
- 한 사용자가 여러 ROLE 을 가질 수 있고, 여러 사용자에게 같은 ROLE 을 부여할 수 있습니다.
- ROLE 생성 및 부여 명령어는 아래와 같습니다.
CREATE ROLE <역할명>;
위와 같은 역할명의 역할을 생성합니다.
아직 이 역할에는 권한이 없습니다.
GRANT <권한>[, ...] TO <역할명>;
DB 권한을 역할명에 부여합니다.
이제 이 역할에는 이러한 권한들이 있는 것입니다.
GRANT <역할명> TO <사용자>[, ...];
사용자에게 역할명을 부여합니다.
이로인해 사용자는 해당 역할명이 부여받은 모든 권한을 부여받은 것과 같습니다.
((트랜잭션 제어 TCL, Transaction Control Language))
- 트랜잭션이란, 데이터베이스를 조작하는 논리적 연산들이 하나 이상 모인 단위 작업입니다.
예를들어 상품 결제 기능이 있을 때,
데이터베이스 테이블에서 "상품 정보 조회", "결제 테이블에 데이터 입력", "주문 테이블에 데이터 입력", "히스토리 테이블에 별도 히스토리 입력", "상품 수량 정보 수정" 이러한 DB 관련 논리적 연산들이 하나가 되어 처리되어야 합니다.
이를 하나의 트랜잭션으로 취급하며, 트랜잭션에 속한 작업들의 완료나 트랜젝션이 진행되며 수행된 작업들을 복원하는 기준이 됩니다.
- 트랜잭션의 특성(ACID)
1. 원자성(Atomicity) : 모든 연산이 수행되거나 하나도 수행되지 말아야 합니다.
2. 일관성(Consistency) : 시스템 고정 요소는 트랜잭션 이후에도 같아야 합니다.
3. 고립성(Isolation) : 트랜잭션 실행 도중 다른 트랜잭션의 영향을 받지 않아야 합니다.
4. 지속성(Durability) : 트랜잭션의 결과는 항상 유지되어야 합니다.
- 트랜잭션 상태 제어
트랜잭션은 수행 즉시 반영되는 것이 아니므로 진행 상황에 따라 다양한 상태를 가집니다.
1. 활동(Active) : 실행중인 상태
2. 부분 완료(Committed) : 마지막 연산을 끝내고 결과를 반영하기 직전의 상태
3. 완료(Committed) : 연산을 완료하고 결과를 데이터베이스에 반영한 상태
4. 실패(Failed) : 연산 실행 중 어떤 오류로 인해 더이상 연산이 진행될 수 없는 상태
5. 철회(Aborted) : 트랜잭션 실패로 트랜잭션 실행 전 상태로 복구 된 상태
6. 실행 취소(Undo) : 변경되었던 데이터를 취소하고 원래의 내용으로 복원
7. 다시 실행(Redo) : Undo 를 통해 원래 내용으로 변경되었던 데이터를 다시 앞의 내용으로 복원
- 트랜젝션 명령
1. START TRANSACTION / BEGIN
트랜잭션을 시작합니다.
START TRANSACTION;
-- 또는
BEGIN;
2. COMMIT
트랜잭션 내의 모든 변경 작업을 확정하고, 데이터베이스에 영구 반영합니다.
-- 예: 주문 처리 트랜잭션 완료 시
COMMIT;
3. ROLLBACK
트랜잭션 내에서 수행된 모든 변경을 취소하고, 트랜잭션 직전 상태로 되돌립니다.
-- 오류 발생 시
ROLLBACK;
4. SAVEPOINT
트랜잭션 중간에 “이 지점까지는 확실히 유지”라는 명칭의 체크포인트를 설정합니다.
SAVEPOINT sp_before_payment;
5. ROLLBACK TO SAVEPOINT
지정한 저장점까지의 변경만 취소하고, 그 이전의 변경은 유지합니다.
-- 결제 기록 중 오류 발생 시, 저장점으로만 되돌리기
ROLLBACK TO SAVEPOINT sp_before_payment;
6. RELEASE SAVEPOINT
더 이상 사용할 필요가 없는 저장점을 해제하여 관리 부담을 줄입니다.
RELEASE SAVEPOINT sp_before_payment;
7. SET TRANSACTION
트랜잭션 단위의 격리 수준(Isolation Level)이나 읽기 전용(Read Only) 속성을 설정합니다.
-- 이 트랜잭션을 SERIALIZABLE 격리 수준으로 실행
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
[절차형 SQL]
(절차형 SQL 개념)
- C 언어와 같이 명령어를 좌측 위에서부터 우측 아래까지 차례로 명령문을 작성하고, 그 순서와 절차에 따라 진행되는 프로그래밍 방법론을 절차지향 프로그래밍이라 합니다.
SQL 에서도 이와 같이 단발형 SQL 명령문의 실행만으로 끝나는 것이 아니라, 프로그래밍을 하듯 순차적으로 쿼리문을 실행시키도록 작성할 수 있습니다. 이를 절차형 SQL 이라고 합니다.
- 절차형 SQL 의 종류는, 프로시저, 사용자 정의 함수, 트리거로 나뉩니다.
- 절차형 SQL 의 의의는, SQL 로 구성되는 비즈니스 로직을 하나의 이름으로 캡슐화하여 간편하게 사용할 수 있도록 하여 데이터 관리 및 무결성의 유지가 용이해집니다.(데이터베이스를 설계하고 이해하고 있는 사람만 다룰 수 있는 데이터 처리를 외부인이 함수명 하나를 호출하여 처리할 수 있게 됨)
- DBMS 별 문법의 차이가 있으므로 개발 및 유지보수의 효율성이 떨어질 수 있습니다.
- 개인적 생각으로는, DBA 가 따로 존재하는 조직에서의 개발이 아닌 이상, 절차형 SQL 을 사용하는 것은 전체 시스템의 복잡성이 높아진다고 생각합니다.
이유는, 코드나 기능은 응집성이 있어야 하지만, 애플리케이션이 담당해야 할 로직과 기능적 부분을 DBMS 가 담당하게 된다면 기능 분산으로 인한 복잡성 증가로 유지보수성이 떨어질 것이기 때문입니다.
그럼에도 실제 존재하는 기능이므로 익혀두는 것은 나쁘지 않을 것입니다.
(프로시저(Procedure))
- 호출을 통해 실행되는 절차형 SQL 블록입니다.
- 여러 개의 SQL 문과 제어 흐름(조건문, 반복문 등)을 포함할 수 있으며, 복잡한 비즈니스 로직을 서버 사이드에서 한 번에 처리할 수 있습니다.
- 트랜잭션 제어(COMMIT/ROLLBACK)를 프로시저 내부에서 직접 수행할 수도 있습니다.
-- 1) 간단한 주문 처리 프로시저 예시 (MySQL 문법)
DELIMITER //
CREATE PROCEDURE ProcessOrder(
IN p_order_id INT,
IN p_customer_id INT,
IN p_amount DECIMAL(10,2)
)
BEGIN
DECLARE v_stock INT;
-- 재고 확인
SELECT stock INTO v_stock
FROM products
WHERE product_id = p_order_id
FOR UPDATE;
IF v_stock < 1 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '재고 부족';
ELSE
-- 결제 기록
INSERT INTO payments(order_id, customer_id, amount)
VALUES (p_order_id, p_customer_id, p_amount);
-- 재고 차감
UPDATE products
SET stock = stock - 1
WHERE product_id = p_order_id;
COMMIT;
END IF;
END;
//
DELIMITER ;
-- 호출
CALL ProcessOrder(1001, 200, 299.99);
(사용자 정의 함수(User Defined Function))
- 입력값을 받아 단일값(스칼라 함수) 또는 테이블(테이블 함수)을 반환합니다.
- SELECT 절, WHERE 절 등 일반 SQL 문 내에서 자유롭게 호출 가능하여 계산 로직을 캡슐화할 때 유용합니다.
- 프로시저와 달리 트랜잭션 제어나 COMMIT/ROLLBACK이 불가하며, 반드시 하나의 값만 반환해야 합니다.
-- 1) 부가세(VAT) 계산 스칼라 함수 예시 (PostgreSQL 문법)
CREATE OR REPLACE FUNCTION calc_vat(
subtotal NUMERIC,
vat_rate NUMERIC DEFAULT 0.1
) RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
RETURN ROUND(subtotal * vat_rate, 2);
END;
$$;
-- 호출 예시
SELECT order_id,
subtotal,
calc_vat(subtotal) AS vat_amount,
subtotal + calc_vat(subtotal) AS total_amount
FROM orders
WHERE order_date = '2025-05-14';
(트리거(Trigger))
- 특정 테이블에 INSERT/UPDATE/DELETE 같은 이벤트가 발생할 때 자동으로 실행되는 특별한 종류의 저장 프로시저입니다.
- BEFORE 또는 AFTER 타이밍을 지정할 수 있으며, 단일 행(FOR EACH ROW) 또는 문(FOR EACH STATEMENT) 단위로 동작합니다.
- 로깅, 무결성 검증, 연쇄 테이블 갱신 등 자동화된 후처리가 필요할 때 유용합니다.
- 주의: 복잡한 트리거 로직은 성능 저하 및 디버깅 난이도를 높일 수 있으므로 가급적 단순하게 유지해야 합니다.
-- 1) 재고 변경 이력 자동 기록 트리거 예시 (Oracle 문법)
CREATE OR REPLACE TRIGGER trg_products_stock_change
AFTER UPDATE OF stock ON products
FOR EACH ROW
BEGIN
INSERT INTO stock_history(
product_id,
old_stock,
new_stock,
changed_at
) VALUES (
:OLD.product_id,
:OLD.stock,
:NEW.stock,
SYSTIMESTAMP
);
END;
/
[인덱스와 뷰]
(인덱스(INDEX))
- 인덱스란, 데이터를 빠르게 검색할 수 있도록 구성된 자료 구조 및 방법입니다.
데이터를 조회할 때 조건문에 따라 적절한 데이터를 검색할 때, 적절한 기준으로 정렬되지 않은 데이터를 검색하기 위해선 모든 데이터를 전부 조회하는 방식 밖에 없습니다.
하지만 이미 정렬된 정보들이라면, 정렬 기준으로 데이터가 있을만한 위치를 예측하면서 데이터를 찾을 수 있으므로 탐색 속도가 훨씬 빨라질 것입니다.
이미 저장된 데이터는 그 순서를 바꿀수는 없겠지만, 순서를 기록한 테이블은 만들 수 있습니다.
예를들어,
도서관에 수천 권의 책이 있고, 책은 아무 순서 없이 선반에 배치되어 있을 때,
도서관에 제목순으로 정리된 책 목록이 있는 카탈로그(인덱스)가 있다고 가정합니다.
우리는 이 인덱스 목록을 빠르게 검색하여, 책이 어느 선반에 있는지 정확히 알 수 있습니다.
원하는 책이 '파이썬' 이라면, 제목으로 정렬된 인덱스 카탈로그에서 ㅍ 로 시작되는 부분을 찾아서 파이썬 책의 위치를 확인하면, 해당 위치로 바로 이동해서 책을 가져올 수 있을 것이니까요.
인덱스의 원리는 위 예시와 같습니다.
- 인덱스를 설계시 고려해야할 사항으로는, 한 테이블에 인덱스를 너무 많이 설정해서는 안된다는 것입니다.
이유는, 위 예시에서 보듯, 인덱스 기준이 많으면 그만큼 인덱스를 저장할 위치가 필요하다는 것이고, 데이터 입력, 수정, 삭제 시점에 인덱스에도 정렬 기준에 맞게 입력해야 하기 때문입니다.
- 인덱스 명령어
CREATE [UNIQUE] INDEX <인덱스명> ON <테이블명> (<컬럼명><정렬옵션>[, ...]);
인덱스를 생성하는 명령어입니다.
UNIQUE 옵션은 중복값을 허용하지 않는 인덱스를 생성하는 옵션입니다.
ALTER TABLE <테이블명> DROP INDEX <인덱스명>;
인덱스를 삭제하는 명령어입니다.
ALTER [UNIQUE] INDEX <인덱스명> ON <테이블명> (<컬럼명>[, ...]);
인덱스를 변경하는 명령어입니다.
SHOW INDEX FROM <테이블명>;
인덱스를 조회하는 명령어입니다.
(뷰(VIEW))
- 뷰란, 하나 이상의 테이블로부터 유도되는 실체가 없는 논리적인 가상 테이블입니다.
예를들어 실제 데이터로 상품 데이터가,
| product\_id | product\_name | price |
| ----------- | ------------- | --------------- |
| 1 | 노트북 | 1,500,000 |
| 2 | 무선 마우스 | 25,000 |
| 3 | 키보드 | 50,000 |
이런식으로 되어있을 때,
SELECT * FROM products;
라고 조회한다고 합시다.
그런데, price 에서 10% 할인된 가격을 조회하려면,
SELECT
product_id,
product_name,
price,
price * 0.9 AS discounted_price
FROM products;
위와 같이 SELECT 문을 구성해야 하는데,
이렇게 매번 값을 조회할 때마다 처리하는 것은 불편합니다.
고로,
CREATE VIEW discounted_products AS
SELECT
product_id,
product_name,
price,
price * 0.9 AS discounted_price
FROM products;
이런식으로 뷰를 만들면, 조회 시점에,
SELECT * FROM discounted_products;| product\_id | product\_name | price | discounted\_price |
| ----------- | ------------- | ------- | ----------------- |
| 1 | 노트북 | 1500000 | 1350000.00 |
| 2 | 무선 마우스 | 25000 | 22500.00 |
| 3 | 키보드 | 50000 | 45000.00 |
이렇게 생성한 뷰를 SELECT 하기만 하면 위에서 작성한 SELECT 문이 자동으로 실행된 결과가 반환되는 것입니다.
위에서는 간단한 예시를 들었는데,
보다 복잡한 쿼리를 단순하게 활용하기 위해 뷰를 만든다면 유용하게 사용할 수 있을 것입니다.
- 뷰의 특징
1. 뷰는 실체가 없기 때문에 종속된 테이블이 제거되면 같이 제거됩니다.
2. 생성된 뷰를 기준으로 새로운 뷰를 생성할 수도 있습니다.
3. 뷰의 정보는 시스템 카탈로그에 저장됩니다.
4. 물리적 실체가 없으므로 뷰에 자체적인 인덱스를 부여할 수 없습니다.
5. 뷰를 변경하기 위해서는 삭제 후 다시 생성해야 합니다.
- 뷰의 장점은,
데이터의 논리적 독립성을 유지하며 접근 방법을 단순화 할 수 있다는 것과, 테이블의 일부 데이터에 대해서만 접근을 허용함으로써 데이터 보안을 유지할 수 있다는 것입니다.
- 뷰 명령어
CREATE VIEW <뷰 이름> (<컬럼 목록>) AS SELECT문 [옵션];
위에서 옵션은,
REPLACE : 뷰가 이미 존재하는 경우 재생성
FORCE : 원본 테이블의 존재 여부에 관계 없이 뷰 생성
NOFORCE : 원본 테이블이 존재할 때만 뷰 생성
WITH CHECK OPTION : 조건에 사용된 컬럼의 값을 수정 불가능하도록 설정
WITH READ ONLY : 모든 컬럼의 값 수정 불가능(DML 작업 불가능)
DROP VIEW <뷰 이름>;
뷰를 삭제합니다.
SELECT * FROM <뷰 이름>;
뷰를 이용하여 조회합니다.
[SQL 지원도구]
(시스템 카탈로그 및 데이터 사전)
- 데이터베이스 시스템 카탈로그란, 데이터 사전이라고도 부르며,
데이터베이스 객체들에 대한 정의와 명세를 메타 데이터 형태로 유지 관리하는 시스템 테이블입니다.
테이블, 뷰가 있으면 이 정의를 저장할 위치도 존재해야 DBMS 에서도 사용할 수 있으므로 당영히 필요한 것입니다.
- 시스템 카탈로그 특성
1. DBMS 가 스스로 생성하고 유지하며 데이터 디렉토리에 저장된 접근 정보를 통해 접근할 수 있습니다.
2. DML 을 통해 내용 조회가 가능하지만 직접적인 변경은 불가능합니다.
3. DDL 을 통해 데이터베이스 객체가 변경되면 DBMS 에 의해 자동으로 변경됩니다.
(SQL 지원도구 종류)
- SQL 관련 지원 도구는 아래와 같은 종류가 있습니다.
1. PL/SQL : 프로그래밍 언어의 특성을 통합한 확장 SQL 기능입니다.
스크립트 형태로 실행 가능하며 모듈화, 절차적 프로그램 작성이 가능합니다.
식별자 선언, 에러 처리가 가능하고, 성능 향상을 기대할 수 있습니다.
2. SQL*Plus : Oracle 사에서 제공하는 SQL 지원 도구입니다.
키워드 축약, 다중 행 입력, 종료 문자 생략이 가능합니다.
3. APM(Application Performance Management)
안정적인 시스템 운영을 위한 모니터링 도구로 시스템 부하량과 접속자 파악, 장애 진단 기능이 있습니다.
시스템 리소스 모니터링과 사용자 대상 모니터링이 있습니다.
4. TKPROF : 실행되는 SQL 문장을 추적, 분석하여 지침을 제공해줍니다.
분석 가능한 정보는 아래와 같습니다.
- Parse, Execute, Fetch 수
- CPU 작업시간
- 물리적, 논리적 Reads
- 처리된 튜플 수
- 라이브러리 캐시 Misses
- Commit / Rollback
인스턴스 수준과 세션 수준의 추적이 있으며,
- Instance Level : 지속적인 설정 방법으로 모든 SQL 수행에 대한 추적, 많은 부하 발생
- Session Level : 임시적 설정 방법으로 특정 프로세스별로 추적
5. EXPLAIN PLAN
SQL 문장의 경로를 분석하여 성능 개선 지침을 제공해줍니다.
6. 소스 코드 인스펙션 도구(Source Code Inspection)
데이터베이스를 조작하는 프로시저 코드 등을 분석하여 성능의 문제점을 개선 함으로써 데이터베이스의 성능을 향상시키는 도구입니다.
[병행 제어 및 로킹]
(병행 제어(Concurrency Control, 동시성 제어))
- 병행 제어란, 데이터베이스의 활용도를 극대화하기 위해 여러 사용자들의 데이터베이스 공동 사용을 최대화하는 기술입니다.
- 공동 사용시에 일어날 수 있는 문제점은 아래와 같습니다.
1. 분실된 갱신(Lost Update) : 데이터를 두개의 트랜잭션이 갱신하면서 하나의 작업이 진행되지 않는 경우의 문제점
| 시점 | 트랜잭션 A | 트랜잭션 B | 데이터 (잔액) |
| -- | ------------ | ----------- | -------------------------- |
| T1 | 읽기 (잔액=100) | | 100 |
| T2 | | 읽기 (잔액=100) | 100 |
| T3 | +50 → 150 저장 | | 150 |
| T4 | | -30 → 70 저장 | **🔴 70 (A의 갱신 150이 사라짐)** |
2. 모순성(Inconsistency) : 데이터를 두개의 트랜잭션이 갱신하면서 사용자가 원하는 결과와 일치하지 않는 상태가 되는 경우의 문제점
| 시점 | 트랜잭션 A (송금) | 트랜잭션 B (총 자산 계산) | A 잔액 | B 잔액 |
| -- | ----------------- | ------------------------ | ---- | ---- |
| T1 | 읽기 A(100) → A -10 | | 90 | 100 |
| T2 | | 읽기 A(90), B(100) → 총 190 | | |
| T3 | 쓰기 B = B +10 | | 90 | 110 |
3. 연쇄 복귀(Cascading Rollback) : 데이터를 두개의 트랜잭션이 갱신하면서 문제가 발생하면 두 트랜잭션 모두 갱신 전으로 복귀하는 상태가 되는 경우의 문제점
| 시점 | 트랜잭션 A | 트랜잭션 B | 설명 |
| -- | ------------------------- | --------------------- | ---------------------- |
| T1 | X=100 → X=200 저장 (커밋 안 됨) | | A는 아직 완료되지 않음 |
| T2 | | 읽기 X (200) | B는 A의 결과를 참조 |
| T3 | | 계산 및 저장 | |
| T4 | A 실패 및 롤백 → X=100 복구 | | **B는 잘못된 값을 기반으로 실행됨** |
| T5 | | 🔴 B도 롤백되어야 함 (연쇄 복귀) | |
4. 비완료 의존성(Uncommitted Dependency) : 하나의 트랜잭션이 실패하고 회복이 이뤄지기 전에 다른 트랜잭션이 실패한 수행 결과를 참조하는 경우의 문제점
| 시점 | 트랜잭션 A | 트랜잭션 B | 설명 |
| -- | -------------------------- | ------------ | -------------------- |
| T1 | X = 100 → X = 500 (커밋 안 됨) | | A는 작업 중 |
| T2 | | 읽기 X = 500 | B는 A가 변경한 값을 사용 |
| T3 | A 실패 → 롤백 (X=100 복구) | | **B는 잘못된 500을 사용했음** |
| T4 | | 🔴 오류 가능성 있음 | |
- 병행제어는 위와 같은 문제점을 해결하기 위한 방식이며, 제어 기법에는 로킹과 회복이 있습니다.
(로킹(Locking))
- 로킹이란, 트랜잭션이 현재 갱신중인 데이터를 다른 트랜잭션이 접근하지 못하도록 잠그는 것입니다.
- 낙관적 락
트랜잭션을 진행하는 동안 접근을 막지는 않습니다.
다만 트랜잭션 완료 시점에 데이터에 대한 무결성을 검사하여 이상이 있다면 롤백하는 방식의 락입니다.
비관적 락에 비해 성능상 이점이 있습니다.
- 비관적 락
트랜잭션을 수행하는 동안 접근 자체를 막는 락입니다.
병행 제어를 제대로 수행할 수 있지만, 락이 풀릴 때 까지 다른 트랜잭션들은 접근할 수 없으므로 성능상 불리합니다.
(회복(Recovery))
- 특정 장애로 인해 데이터베이스에 문제(무결성 훼손)가 발생했을 때, 문제 발생 이전의 상태로 복원하는 것입니다.
- 장애의 유형은 아래와 같습니다.
1. 트랜잭션 장애(Transaction Failure) : 트랜잭션 내의 논리적 오류로 인한 장애
2. 시스템 장애(System Failure) : 하드웨어 오작동으로 인한 장애
3. 미디어 장애(Media Failure) : 디스크 고장으로 인한 장애
- 회복 방식
1. 로그를 이용한 회복 : 트랜잭션 결과를 그 즉시 반영하고, 문제 발생시 로그를 기반으로 복원하는 즉시 갱신 방식과,
갱신 결과들을 그냥 로그에만 기록하다가 문제가 없다면 로그를 기반으로 반영하는 지연 갱신 방법이 있습니다.
2. 검사 시점에 의한 회복 : 로그에 있는 내용이 데이터베이스에 반영될 때마다 CheckPoint 를 두고, 이를 기반으로 회복을 하는 방법입니다.
3. 그림자 페이징 기법 : 문제 발생시 로그가 아닌 그림자 페이지란 이름의 복사본으로 대체하여 회복하는 기법입니다.
데이터베이스를 일정 크기의 페이지 단위로 구분하여 각 페이지에 복사본을 유지합니다.