SQL 개념 및 실제 쿼리 정리

 

SQLD 공부했다고 SQL의 쿼리를 잘 짤수는 없다.

최근에 면접을 보았고 DB 관련된 내용 질문이 많았지만 한마디도 못했다.

내가 생각했던거 이상으로 백엔드에서 DB는 중요했다. 

 

SQLD 공부가 무의미한 것은 아니다.

단지 내가 이론적으로만 학습했기 때문에,

외우기에만 급급했고 정말 실제 SQL 사용측면에서의 학습이 부족했다. 

 

SQLD 필수 개념 요약 정리 / 벼락치기, 독학, 요약본

 

그래서 이번에 지난번에 SQLD 시험을 위해 정리했던 내용들을, 

좀 더 실용성을 바탕으로 보충해보려 한다.

(좀 헷갈리는거 실용적인 개념 및 쿼리 위주로 정리를 한다 !)

 

---

DBMS (Database Management System)

DBMS는 데이터베이스의 데이터 조작과 관리를 극대화한 시스템 소프트웨어이다. 

이를 통해서 효율적인 관리와 검색을 위해 구조화한 데이터 집합을 구성한다. 

DBMS의 종류로는 MySQL, PostgreSQL, Oracle DB, MongoDB 등이 있다. 

 

보통 DBMS를 "도서관 사서", 데이터베이스를 "도서관의 책장" 같이 비유하곤 한다.

---

DB 테이블 구조 

1. 엔티티(Entity), 릴레이션(Relation), 테이블(Table)

.

• Entity 
  • 현실 세계에서 관리하고 싶은 객체 또는 개념
•  Relation
  • 수학적 모델에 해당하는 개념으로 이론적인 개념이다. 
  • 속성과 튜플로 구성되어, 데이터는 집합으로 저장 (원자성)
    • 순서가 의미없고 각 속성은 원자값을 가짐
  • 즉, 중복된 튜플이 존재하지 않음
• Table
  • 릴레이션을 실제 DBMS에서 구현한 객체
  • 현실적인 제약 때문에 중복된 행이 허용
  • NULL 값 포함할 수 있음 

 

현실적인 개념(엔티티) → 릴레이션(논리적 모델)  → 테이블 (실제 구현) 의 순서대로 구현된다고 생각하면 된다.

 

 

2. 속성, 튜플, 도메인, 스키마, 인스턴스

• 속성(Attribute) = 열(Column)
  • 테이블에서 열을 의미
  • Degree (속성 수)
• 튜플(Tuple) = 레코드(Record) = 행(Row)
  
  • 테이블에서 행을 의미
  • Cardinality (튜플의 수)
• 도메인 (Domain)
  • 한 속성에서 나타날 수 있는 값들의 집합
• 스키마
  • Relation 이름 + 속성 집합
  • ex) Relation_Name(Attribute1​,Attribute2​,...,Attributen​)
• 인스턴스
  • 릴레이션 어느 시점에 들어 있는 튜플들의 집합
  • 시간의 흐름에 따라 계속 변함

---

테이블 Key 

DB에서 Key는 어느 테이블에서나 들어가는 꼭 필수적인 것이다. 

1. 기본키 (Primary Key)

CREATE TABLE 학생 (
    학번 INT PRIMARY KEY,
    이름 VARCHAR(50),
    전공 VARCHAR(50)
);

• 테이블에서 각 행을 유일하게 식별하는 Key
• NULL이 될 수 없고, 중복이 될 수 없음 
• 한 테이블에 하나만 존재 함

 

2. 보조키 (Alternate Key)

CREATE TABLE 학생 (
    학번 INT UNIQUE,
    이메일 VARCHAR(100) UNIQUE,
    이름 VARCHAR(50),
    전공 VARCHAR(50),
    PRIMARY KEY (학번)
);

• 후보키 (Candidate Key) 중에서 기본키로 선택되지 않은 Key
• 테이블에서 기본키 외에도 유일성을 보장하는 속성

 

3. 외래키 (Foreign Key)

CREATE TABLE 학생 (
    학번 INT PRIMARY KEY,
    이름 VARCHAR(50),
    전공 VARCHAR(50)
);

CREATE TABLE 수강 (
    수강번호 INT PRIMARY KEY,
    학번 INT,
    과목명 VARCHAR(50),
    성적 CHAR(1),
    FOREIGN KEY (학번) REFERENCES 학생(학번)
);

• 한 테이블의 열이 다른 테이블의 기본키를 참조하는 키 
• 테이블 간의 관계를 정의
• 위의 코드 예시 
  • 수강 테이블의 학번은 학생 테이블의 기본키를 참조
  • 학생이 삭제되면, 수강 테이블의 데이터 삭제도 가능 

 

4. 복합키 (Composite Key)

CREATE TABLE 수강 (
    학번 INT,
    과목명 VARCHAR(50),
    성적 CHAR(1),
    PRIMARY KEY (학번, 과목명)

• 두 개 이상의 열을 조합하여 하나의 Primary Key로 사용 
• 단일 속성만으로 유일성 보장하기 어려울 때 사용

---

연산자

1. 논리 연산자 

• AND 
• OR
• NOT

 

2. 산술 연산자 

• + , - , * , /

 

3. 비교 연산자 

• >, >=, <, <= 
• =, <> (!=, ^=)
  • <> 이 비교 연산자를 가장 많이 사용

 

4. 집합 연산자 

• UNION 
  • 두 개 이상의 SELECT 결과를 세로로 합침
  • 중복을 제거하여 반환

SELECT name FROM employees
UNION
SELECT name FROM customers;

 

• UNION ALL 
  • UNION과 다른 점은 중복을 제거하지 않고 모든 결과 반환

SELECT name FROM employees
UNION ALL
SELECT name FROM customers;

 

그 외에 MINUS, INTERSEC와 같은 것들이 있지만, 

MySQL에서는 지원하지 않는다.  

 

 

5. 기타 연산자 

• IN 연산자

SELECT * FROM users WHERE age IN (20,25,30);

 

• BETWEEN 연산자

SELECT * FROM users WHERE age BETWEEN 20 AND 30;

 

• LIKE 연산자

SELECT * FROM users WHERE name LIKE "J%"

SELECT * FROM users WHERE email LIKE "%gmail.com";

 

• IS NULL

SELECT * FROM users WHERE email IS NULL;

 

 

아직 손에 덜 익혀져 잘 쓰지 못하는 연산자이다.

쿼리문 작성에 필수니까 꼭 알아두자

---

주요 함수

문자열 가공 함수

• UPPER(문자열)
• LOWER(문자열)
• LENGTH(문자열)
• SUBSTR(문자열, 시작위치, 추출 길이)
• INSTR(문자열, 찾으려는 문자)
• REPLACE(문자열, 찾는 문자, 대체 문자)
• LPAD( 문자열, 문자열 길이 제한, 공간 채울 문자)
• RPAD( 문자열, 문자열 길이 제한, 공간 채울 문자)
• LTRIM(문자열, 삭제할 문자 집합)
• RTRIM(문자열, 삭제할 문자 집합)

 

숫자 가공 함수

• ROUND(숫자, 반올림 위치)
• TRUNC(숫자, 버림위치)
• CEIL(숫자)
• FLOOR(숫자)
• MOD(숫자, 나눌 숫자)

 

날짜 데이터 가공 함수

• SYSDATE → 현재 날짜와 시간
• ADD_DATE(날짜 데이터, 정수)
• MONTHS_BETWEEN(날짜 데이터, 날짜 데이터)
• NEXT_DAY(날짜 데이터, 요일) → 돌아오는 요일의 날짜 
• LAST_DAY(날짜 데이터) → 해당 달의 마지막 날짜

날짜 데이터도 ROUND나 TRUNC 사용 가능 ! 

 

 

여기에 함수를 다 외우자는 것이 아니고, 그때마다 필요한 함수를 찾아쓰면 된다. 

---

다중행 함수

다중행 함수는 여러 행을 입력하여 하나의 행으로 결과를 출력하는 특징을 가지고 있다.

다중행 함수를 쓰다보면 가끔 출력에서 헷갈린다.

아래 집계함수는 거의 밥먹듯이 쓰는 함수들이다. 

 

집계 함수 종류 (Aggregate Functions)

• SUM(데이터)
• COUNT(데이터)
• MAX(데이터)
• MIN(데이터)
• AVG(데이터)

 

1. 집계 함수 파라미터에 DISTINCT를 넣어 중복 제외한 연산 가능

2. 또한 OVER (PARTITION BY ~ ORDER BY ~ ) 와 같이 활용도 가능 

SUM(amount) OVER (
    PARTITION BY employee
    ORDER BY sale_date
)

• OVER( )가 의외로 유용하다.

---

JOIN

JOIN 종류

• INNER JOIN (기본 JOIN) → 교집합
• LEFT JOIN (LEFT OUTER JOIN) → 왼쪽 테이블 모든 행 포함
• RIGHT JOING (RIGHT OUTER JOIN) → 오른쪽 테이블 모든 행 포함
• FULL JOIN (FULL OUTER JOIN) → 두 테이블의 모든 데이터 포함 (합집합)
• CROSS JOIN → 모든 행의 조합 , 카테시안 곱

보통 쿼리문을 작성할 때 INNER JOIN과 LEFT JOIN을 많이 쓰는 것 같다.

---

서브쿼리

서브쿼리는 SQL에서 하나의 쿼리 안에 포함된 또 다른 쿼리를 의미한다. 

다음으로 서브쿼리의 종류에 대해 알아보자. 

 

 

단일행 서브쿼리

SELECT name, salary 
FROM employees 
WHERE salary = (SELECT MAX(salary) FROM employees);

• 다중행 함수 (SUM, MAX, COUNT 등)로 하나의 행만 반환되는 서브 쿼리
• = , >, <, >=, <=, <> 연산자와 함께 사용 가능

 

다중행 서브쿼리

SELECT name, department
FROM employees
WHERE department IN (SELECT department FROM departments WHERE location = 'Seoul');

• 결과가 여러 행으로 반환되는 서브 쿼리 
• IN, ANY, ALL, EXISTS 연산자와 함께 사용 

 

다중열 서브쿼리

SELECT name, salary 
FROM employees 
WHERE (department, salary) IN (
    SELECT department, MAX(salary)
    FROM employees
    GROUP BY department
);

• 비교하는 열이 여러 개인 서브쿼리
• EXISTS, IN, JOIN과 함께 사용

 

FROM 절에서 사용하는 서브쿼리 (Inline View)

SELECT avg_salary.department, avg_salary.avg_salary
FROM (SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department) AS avg_salary;

• 서브쿼리를 가상의 테이블 처럼 활용
• 일회성으로 만들어서 사용하는 뷰
• 대부분에 DB에서 Alias를 필수로 해줘야 함

 

SELECT절에서 사용하는 서브쿼리 (스칼라 서브쿼리)

SELECT name, salary, 
       (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

• 서브쿼리 실행 결과가 하나의 값
• 컬럼값을 동적으로 계산할 때 사용

 

WITH 절

WITH avg_salary AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT e.name, e.salary, a.avg_salary
FROM employees e
JOIN avg_salary a ON e.department = a.department;

• WITH 절은 서브쿼리를 미리 정의하고, 해당 서브쿼리를 마치 테이블처럼 사용할 수 있도록 한다.
• 복잡한 쿼리를 더 가독성 높게 작성하고, 재사용이 가능하다.

 

참고로 서브쿼리에서는 메인쿼리 참조 가능하다 !

서브쿼리에 대해 전혀 어렵게 생각 할 필요가 없다. 

쿼리문을 짜다 필요에 따라 적절한 서브쿼리를 사용하면 되는 것이다.

---

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

1. SELECT

 

2. UPDATE

UPDATE employees
SET salary = salary * 1.1  -- 급여 10% 인상
WHERE department = 'Sales';

• UPDATE() - SET() - WHERE()

 

3. DELETE

DELETE FROM employees
WHERE department = 'Sales';

• DELETE FROM() - WHERE

 

4. INSERT 

INSERT INTO employees (name, department, salary)
VALUES ('Jerry', 'IT', 60000);

• INSERT INTO() - VALUES()

---

트랜잭션

• 우선 트랜잭션에 대한 잘못된 생각을 갖고 있었다. 
  • 내가 생각했던 하나의 트랜잭션은 하나의 쿼리라고 생각했었다.
  • 하지만 BEGIN ~ COMMIT 사이에 실행되는 모든 쿼리가 하나의 트랜잭션으로 간주된다.

START TRANSACTION;

-- A 계좌에서 100원 차감
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- B 계좌에 100원 추가
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 정상적으로 실행되었으면 커밋
COMMIT;

• 이게 하나의 트랜잭션이다.

BEGIN;

-- 여러 개의 SQL 실행 (여러 테이블에서 업데이트 가능)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
INSERT INTO logs (message) VALUES ('Transfer successful');
UPDATE customers SET status = 'VIP' WHERE total_spent > 10000;

-- 트랜잭션 확정
COMMIT;

• 이것도 트랜잭션이다.
• 하지만 트랜잭션이 길어질수록 성능 저하 발생한다는 점을 기억하자 

 

SELECT 문

BEGIN;

SELECT * FROM accounts WHERE id = 1;
SELECT * FROM accounts WHERE balance > 1000;
SELECT COUNT(*) FROM transactions;

COMMIT;

• SELECT문은 단순히 데이터를 조회하는 작업으로 트랜잭션 내에서 직접적인 영향을 주지 않는다.
• 하지만 트랜잭션의 격리 수준에 따라 다른 트랜잭션에서 조회되는 데이터가 달라진다.

 

트랜잭션의 특징 (ACID)

• 트랜잭션하면 이 특징을 기억해야한다.
  1. 원자성 (Atomicity) : 쪼개질 수 없음
  2. 일관성 (Consistency) : 데이터 정합
  3. 고립성 (Isolation) : 독립 수행 
  4. 영속성 (Durability) : 영구 저장

 

모놀리식 아키텍처는 단일 DB 활용으로 오라클과 같은 DB 내 트랜잭션 제어 장치만으로 비교적 안적하게 데이터 제어가 가능했다.

이와는 달리 MSA에서는 서비스의 유연성을 위해 각 서비스가 별개의 DB를 활용한다. 따라서 하나의 DB만으로 트랜잭션을 보장하기 어렵고 다양한 방법을 활용해야 한다.

 

TCL 종류 (Transaction Control  Language, 트랜잭션 제어어)

• COMMIT
• ROLLBACK
• SAVEPOINT

 

세션

• 세션은 사용자가 애플리케이션에 접속하여 서버와 연결을 유지하는 상태이다.
• 하나의 세션에는 최소 하나 이상의 트랜잭션이 존재한다.
  • 즉, 세션안에는 여러 COMMIT, ROLLBACK 작업들이 있다.
• 하나의 DB에는 수많은 세션들이 연결되어있고, 여러 트랜잭션이 끊임 없이 시작되고 종료된다.
• 데이터를 직접 변경 중인 해당 세션을 제외한 모든 세션은 다른 세션의 데이터 변경과 상관없이 이미 확정된 데이터만 검색하도록 읽기 일관성이 보장된다.

 

Lock

• Lock의 종류
  • 공유락(Shared Lock, S Lock) / 베타락(Exclusibe Lock, X Lock)

• DB에서 Lock은 동시성 제어를 위해 사용
• 여러 트랜잭션이 동일한 데이터를 동시에 읽거나 변경할 때, 데이터 정합성을 보장 및 충돌 방지

• Lock의 범위
  • 데이터베이스 락 / 테이블 락 / 페이지 락 / 행 락
• SQL 구문을 어떤 방식으로 작성하느냐에 따라 테이블 일부만 LOCK 될 수 있고, 전체가 LOCK 될 수도 있다.

---

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

• DDL 명령어는 자동커밋이 된다.
• DDL 종류
  • CREATE
  • ALERT
  • DROP
  • TRUNCATE
  • RENAME

---

Index

• 인덱스는 DB에서 검색 속도를 향상시키기 위해 특정 열에 생성하는 자료 구조이다.
• 데이터를 정렬하고 저장하여 빠른 조회를 가능하게 한다.
• 인덱스로 검색 속도를 향상시킬 수 있다.
• 하지만 INSERT, DELETE, UPDATE는 인덱스를 유지야 하므로 쓰기 연산 속도가 느려질 수 있다.
• 또한 인덱스 데이터를 따로 저장해야 해서 디스크 공간을 더 차지한다.
• 정리하자면 읽기 성능은 향상 시키지만, 쓰기 성능이 저하될 수 있다.

---

View

CREATE VIEW user_info AS
SELECT id, name, email FROM users;

• 뷰는 가상의 테이블이다. 
• 실제 데이터를 저장하지 않고, 기존 테이블의 테이터에 대한 참조 역할을 한다.
• 조회 전용
• 보안 강화 (특정 컬럼만 포함하도록 설정하여 민감한 데이터 보호 가능)
• 복잡한 쿼리 단순화
• 뷰의 단점으로는 데이터를 물리적으로 저장하지 않아 매번 쿼리 실행해야 한다.

---

TOP N 쿼리

1. ROWNUM (오라클 전용)

SELECT ROWNUM, name FROM employees WHERE ROWNUM <= 5;

• 각 행에 번호 부여 

SELECT ROWNUM, name
FROM (SELECT name FROM employees ORDER BY salary DESC)
WHERE ROWNUM <= 5;

• ORDER BY 적용 시 주의
  • ROWNUM이 데이터 가져오는 순서대로 번호를 부여하지만, ORDER BY 적용전에 ROWNUM이 할당되어 번호가 꼬이게 된다.
  • 해결 방법은 서브 쿼리를 사용하는 것이다.
• 조건이 FALSE가 되면 바로 종료
• 이렇게 하면 정렬된 상위 N개의 데이터를 가져올 수 있다.

 

2. 윈도우 함수

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK() OVER (ORDER BY salary DESC) AS rank_num,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num
FROM employees;

• ROW_NUMBER
  • 1,2,3,4,5,6,7,8 ...
  • 순차적으로 번호 부여 (동일 값이 있어도 고유 번호)
• RANK
  • 1,2,2,4,5,6,7,8 ...
  • 동일 값이 있으면 같은 순위, 다음 순위 건너 뜀
• DENSE_RANK
  
  • 1,2,2,3,4,5,6,7,8
  • 동일 값이 있으면 같은 순위, 다음 순위는 계속 연속

 

3. TOP WITH TIES (SQL Server 전용)

SELECT TOP 3 name, salary
FROM employees
ORDER BY salary DESC;

• TOP N개를 가져올 수 있음
• 반드시 ORDER BY와 함께 사용해야 함
• 동일한 값까지 포함함 ( N개 초과 할 수 있음)

---

데이터 무결성 (Data Integrity)

1. Entity 무결성

• PRIMARY KEY에 의해 보장
• 각 행이 고유한 식별자를 가져야 하며, NULL 값을 허용하지 않음

 

2. 참조 무결성

• FOREIGN KEY에 의해 보장
• 다른 테이블을 참조할 때, 존재하지 않는 값 참조 불가능

 

3. 도메인 무결성

• CHECK, NOT NULL, UNIQUE 등에 의해 보장
• 특정 열의 값이 미리 정의된 형식이나 범위를 벗어나지 않도록 제한

 

제약조건의 종류

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,       -- Entity 무결성 보장
    name VARCHAR(50) NOT NULL,    -- NULL 값 허용 X
    email VARCHAR(100) UNIQUE,    -- 중복된 이메일 X
    department_id INT,  
    salary DECIMAL(10,2) CHECK (salary > 0),  -- 도메인 무결성 보장
    FOREIGN KEY (department_id) REFERENCES departments(department_id)  -- 참조 무결성 보장
);

• 제약조건 예시

 

1. PRIMARY KEY

• 한 테이블에서 각 행을 유일하게 식별하는 열
• NULL 값을 가질 수 없음

 

2. FOREIGN KEY

• 다른 테이블의 PRIMARY KEY를 참조하는 열
• 참조 무결성을 유지 

 

3. UNIQUE

• 중복된 값을 가질수 없음
• NULL 값은 허용 (NULL은 값이 아니기 때문에 중복 가능)

 

4. NOT NULL

• 반드시 값이 존재해야 하며, NULL을 허용하지 않음

 

5. CHECK

• 특정 조건을 만족하는 값만 입력 가능하도록 제한

 

6. DEFAULT

• 값이 입력되지 않을 경우 기본값을 설정