[DB] SQL 기본

티스토리 뷰

CS/데이터베이스

[DB] SQL 기본

토랑 2025. 2. 5. 16:57

SQL의 이해와 필요성

SQL(Structured Query Language)은 관계형 데이터베이스와 소통하기 위한 표준 언어
기본적인 데이터 조회, 입력, 수정, 삭제(CRUD) 작업뿐 아니라 JOIN, 서브쿼리, 그룹핑, 정렬 등의 기능을 통해 복잡한 데이터 처리 및 분석을 지원
또한, 인덱스와 성능 최적화 기법, 고급 기능(윈도우 함수, 뷰, 저장 프로시저, 트리거 등)을 적절히 활용하면 대규모 시스템에서도 안정적이고 효율적인 데이터 관리를 할 수 있음

기본 CRUD 명령어

SELECT

정의 - 데이터베이스 테이블에서 원하는 데이터를 조회

-- 모든 컬럼 조회
SELECT * FROM Employees;

-- 특정 컬럼과 조건을 통한 조회
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE Department = 'Sales';

INSERT

정의 - 테이블에 새로운 레코드를 추가

-- 단일 레코드 삽입
INSERT INTO Employees (FirstName, LastName, Department, Salary)
VALUES ('John', 'Doe', 'Marketing', 50000);

UPDATE

정의 - 기존 레코드의 데이터를 수정

-- 특정 조건에 해당하는 레코드의 값 수정
UPDATE Employees
SET Salary = Salary * 1.1
WHERE Department = 'Sales';

DELETE

정의 - 테이블에서 특정 레코드를 삭제

-- 조건에 해당하는 레코드 삭제
DELETE FROM Employees
WHERE EmployeeID = 1001;

JOIN

정의

여러 테이블에서 관련된 데이터를 연결하여 조회할 때 사용

INNER JOIN

정의 - 양쪽 테이블 모두에 일치하는 레코드만 반환

-- Employees와 Departments 테이블에서 부서 정보가 있는 직원만 조회
SELECT 
    e.EmployeeID, 
    e.FirstName, 
    e.LastName, 
    d.DepartmentName
FROM 
    Employees e
INNER JOIN 
    Departments d 
ON 
    e.DepartmentID = d.DepartmentID;

LEFT (OUTER) JOIN

정의 - 왼쪽(첫 번째) 테이블의 모든 레코드를 반환하고, 오른쪽 테이블에 일치하는 레코드가 없으면 NULL을 반환

-- 모든 직원 정보를 조회하며, 부서 정보가 없는 경우에는 NULL로 반환
SELECT 
    e.EmployeeID, 
    e.FirstName, 
    e.LastName, 
    d.DepartmentName
FROM 
    Employees e
LEFT JOIN 
    Departments d 
ON 
    e.DepartmentID = d.DepartmentID;

RIGHT (OUTER) JOIN

정의 - 오른쪽(두 번째) 테이블의 모든 레코드를 반환하고, 왼쪽 테이블에 일치하는 레코드가 없으면 NULL을 반환

-- 모든 부서 정보를 조회하며, 부서에 속한 직원이 없는 경우에도 부서 정보는 출력
SELECT 
    e.EmployeeID, 
    e.FirstName, 
    e.LastName, 
    d.DepartmentName
FROM 
    Employees e
RIGHT JOIN 
    Departments d 
ON 
    e.DepartmentID = d.DepartmentID;

FULL OUTER JOIN

정의 - 양쪽 테이블의 모든 레코드를 반환하며, 일치하지 않는 경우에는 NULL을 반환
참고 - 일부 DBMS(MySQL 등)는 FULL OUTER JOIN을 직접 지원하지 않으므로, LEFT JOIN과 RIGHT JOIN의 결과를 UNION하여 구현할 수 있음

-- 모든 직원과 모든 부서 정보를 조회 (일치하지 않는 경우 NULL 반환)
SELECT 
    e.EmployeeID, 
    e.FirstName, 
    e.LastName, 
    d.DepartmentName
FROM 
    Employees e
FULL OUTER JOIN 
    Departments d 
ON 
    e.DepartmentID = d.DepartmentID;

-- LEFT JOIN 결과와 RIGHT JOIN 결과를 합쳐서 FULL JOIN 효과 구현
SELECT 
    e.EmployeeID, 
    e.FirstName, 
    e.LastName, 
    d.DepartmentName
FROM 
    Employees e
LEFT JOIN 
    Departments d 
ON 
    e.DepartmentID = d.DepartmentID
UNION
SELECT 
    e.EmployeeID, 
    e.FirstName, 
    e.LastName, 
    d.DepartmentName
FROM 
    Employees e
RIGHT JOIN 
    Departments d 
ON 
    e.DepartmentID = d.DepartmentID;

CROSS JOIN

정의 - 두 테이블의 모든 가능한 조합(카테시안 곱)을 반환

-- Employees와 Departments 테이블의 모든 조합을 조회
SELECT 
    e.EmployeeID, 
    e.FirstName, 
    e.LastName, 
    d.DepartmentName
FROM 
    Employees e
CROSS JOIN 
    Departments d;

SELF JOIN

정의 - 동일한 테이블 내에서 자기 자신과 JOIN을 수행하고, 보통 계층적 구조(예: 관리자-직원 관계)를 표현할 때 사용됩니다

-- 각 직원과 그 상사의 정보를 조회 (ManagerID가 NULL인 경우, 상사가 없는 것으로 간주)
SELECT 
    e.EmployeeID AS EmployeeID,
    e.FirstName AS EmployeeFirstName,
    e.LastName AS EmployeeLastName,
    m.EmployeeID AS ManagerID,
    m.FirstName AS ManagerFirstName,
    m.LastName AS ManagerLastName
FROM 
    Employees e
LEFT JOIN 
    Employees m 
ON 
    e.ManagerID = m.EmployeeID;

서브쿼리

정의 - 하나의 쿼리 내부에 포함된 또 다른 쿼리로, 조건절이나 SELECT 절에서 주로 사용

-- 평균 급여보다 높은 직원 조회
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

GROUP BY, HAVING, ORDER BY

GROUP BY

정의 - 데이터를 하나 이상의 컬럼 기준으로 그룹화하여 집계할 때 사용

-- 부서별 평균 급여 계산
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department;

HAVING

정의 - GROUP BY로 그룹화한 결과에 조건을 적용할 때 사용
WHERE 절은 개별 행에, HAVING은 집계 결과에 조건을 부여

-- 평균 급여가 60000 이상인 부서만 조회
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) >= 60000;

ORDER BY

정의 - 결과 집합을 특정 컬럼 기준으로 정렬할 때 사용

-- 급여가 높은 순으로 직원 정보 정렬
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
ORDER BY Salary DESC;

인덱스와 성능 최적화

인덱스란?

인덱스는 테이블의 특정 컬럼 또는 컬럼 조합에 대해 빠른 검색을 가능하게 하는 자료구조
보통 B-트리나 해시 등의 내부 구조를 사용하며, SELECT 쿼리에서 조건절(WHERE), 조인(JOIN), 정렬(ORDER BY), 그룹화(GROUP BY) 등에 활용

인덱스의 종류

클러스터드 인덱스 (Clustered Index)

특징 - 테이블의 실제 데이터 순서를 결정하고, 한 테이블당 하나만 생성할 수 있으며, 주로 기본 키(primary key)에 사용

-- 기본 키 생성 시, 자동으로 클러스터드 인덱스가 생성됨 (MySQL InnoDB 기본 동작)
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50)
);

비클러스터드 인덱스 (Non-Clustered Index)

특징 - 테이블 데이터의 순서와는 별도로 별도의 자료구조에 인덱스 정보를 저장하고, 한 테이블에 여러 개 생성할 수 있음

-- Employees 테이블의 Department 컬럼에 비클러스터드 인덱스 생성
CREATE INDEX idx_department ON Employees(Department);

복합 인덱스 (Composite Index)

특징 - 둘 이상의 컬럼을 조합하여 인덱스를 생성하고, 다중 컬럼 검색 시 유리하며, 인덱스의 컬럼 순서가 매우 중요

-- 부서와 성을 기준으로 하는 복합 인덱스 생성
CREATE INDEX idx_dept_lastname ON Employees(Department, LastName);

커버링 인덱스 (Covering Index)

특징 - 인덱스 자체가 쿼리에서 필요한 모든 컬럼을 포함하는 경우, 테이블에 접근하지 않고 인덱스만으로 쿼리 결과를 반환할 수 있어 성능을 극대화할 수 있음

-- SELECT 쿼리에서 Department와 Salary만 필요할 때
CREATE INDEX idx_dept_salary ON Employees(Department, Salary);

인덱스 사용 시 고려사항

인덱스 오버헤드

인덱스는 조회 성능을 향상시키지만, 데이터 삽입, 수정, 삭제 시 인덱스도 함께 갱신되어야 하므로 오버헤드가 발생

컬럼 선택

자주 검색되는 컬럼이나 WHERE, JOIN, ORDER BY, GROUP BY 절에서 사용되는 컬럼에 인덱스를 적용하는 것이 효과적

복합 인덱스의 순서

복합 인덱스의 경우, 쿼리 조건에 자주 사용되는 컬럼을 인덱스의 선두에 배치하는 것이 중요합니다

CREATE INDEX idx_dept_lastname ON Employees(Department, LastName);

인덱스 조각화(Fragmentation)

데이터의 삽입, 수정, 삭제 작업 후 인덱스가 조각화되면 성능 저하가 발생할 수 있음
주기적으로 인덱스를 재구성(rebuild)하거나 최적화하는 것이 좋음

실행 계획(EXPLAIN) 분석

쿼리 성능 최적화를 위해 EXPLAIN 명령어를 사용하여 인덱스가 실제로 활용되고 있는지 확인

EXPLAIN SELECT * FROM Employees WHERE Department = 'Sales';

인덱스 최적화 전략

적절한 인덱스 생성

테이블의 데이터 분포와 쿼리 사용 패턴을 분석하여 필요한 인덱스만 생성

커버링 인덱스 활용

자주 사용되는 SELECT 쿼리에 대해 커버링 인덱스를 설계하면, 테이블 액세스를 줄여 성능을 향상시킬 수 있음

인덱스 모니터링 및 관리

데이터베이스 관리 도구를 사용해 인덱스 사용률을 주기적으로 모니터링하고, 사용 빈도가 낮거나 조각화된 인덱스는 재구성 또는 제거

성능 최적화 기법

쿼리 최적화

실행 계획(EXPLAIN)을 분석하여 불필요한 서브쿼리 또는 JOIN을 제거

인덱스 활용

자주 검색되는 컬럼이나 조인 조건에 대해 적절한 인덱스 생성

정규화/반정규화

데이터 중복 제거와 조회 성능 간의 균형 유지

캐싱

Redis와 같은 캐시 시스템 도입으로 DB 부하 감소

파티셔닝

대용량 테이블의 경우 파티셔닝을 통해 데이터 분산 관리

-- 실행 계획 확인 (MySQL 예시)
EXPLAIN SELECT * FROM Employees WHERE Department = 'Sales';

고급 SQL 기능

윈도우 함수 (Window Functions)

정의 - 윈도우 함수는 행 집합 내에서 순위, 누적합, 이동 평균 등 집계 계산을 수행할 수 있도록 도와줌

-- 직원들의 급여 순위 계산
SELECT EmployeeID, Salary,
       RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

뷰(View)와 저장 프로시저 (Stored Procedures)

뷰(View) - 복잡한 쿼리를 단순화하고 재사용하기 위해 논리적 테이블로 사용할 수 있음

-- Sales 부서 직원만 조회하는 뷰 생성
CREATE VIEW SalesEmployees AS
SELECT EmployeeID, FirstName, LastName, Department, Salary
FROM Employees
WHERE Department = 'Sales';

저장 프로시저 - 반복되는 비즈니스 로직을 캡슐화하여 데이터베이스 내에서 실행할 수 있도록 함

DELIMITER //
CREATE PROCEDURE IncreaseSalary(IN dept VARCHAR(50), IN inc_percentage DECIMAL(5,2))
BEGIN
  UPDATE Employees
  SET Salary = Salary * (1 + inc_percentage/100)
  WHERE Department = dept;
END //
DELIMITER ;

트리거(Trigger)

정의 - 특정 이벤트(INSERT, UPDATE, DELETE)가 발생할 때 자동으로 실행되는 SQL 코드 블록

CREATE TRIGGER before_employee_update
BEFORE UPDATE ON Employees
FOR EACH ROW
BEGIN
   -- 변경 전후 급여를 감사 로그 테이블에 기록
   INSERT INTO EmployeeAudit(EmployeeID, OldSalary, NewSalary, ChangeDate)
   VALUES (OLD.EmployeeID, OLD.Salary, NEW.Salary, NOW());
END;

오류 처리와 예외 관리

일부 데이터베이스에서는 저장 프로시저 내에서 오류를 처리할 수 있도록 예외 처리 구문을 지원
예를 들어, MySQL에서는 DECLARE ... HANDLER를 사용하여 특정 에러 발생 시 대체 동작을 정의할 수 있음

DELIMITER //
CREATE PROCEDURE SafeUpdateSalary(IN empID INT, IN newSalary DECIMAL(10,2))
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- 오류 발생 시 롤백 또는 에러 로깅 처리
    ROLLBACK;
  END;

  START TRANSACTION;
    UPDATE Employees
    SET Salary = newSalary
    WHERE EmployeeID = empID;
  COMMIT;
END //
DELIMITER ;

NULL 값 처리와 함수 사용

설명 - SQL에서 NULL 값은 특수한 의미를 가지므로, IS NULL, IS NOT NULL, COALESCE 등 함수로 적절히 처리

SELECT EmployeeID, COALESCE(PhoneNumber, 'N/A') AS PhoneNumber
FROM Employees;

복잡한 쿼리의 가독성과 유지보수

설명 - 복잡한 쿼리는 뷰, CTE(Common Table Expression) 또는 임시 테이블로 분리하여 가독성과 유지보수를 높일 수

WITH DeptSalary AS (
  SELECT Department, AVG(Salary) AS AvgSalary
  FROM Employees
  GROUP BY Department
)
SELECT e.EmployeeID, e.FirstName, e.LastName, d.AvgSalary
FROM Employees e
JOIN DeptSalary d ON e.Department = d.Department;

면접 대비 질문

기본 질문

Q1. SQL의 기본 CRUD 명령어와 각각의 역할에 대해 설명해 주세요.

답변 예시:
- SELECT: 데이터를 조회
- INSERT: 새로운 레코드 삽입
- UPDATE: 기존 데이터 수정
- DELETE: 데이터 삭제

Q2. INNER JOIN과 OUTER JOIN의 차이점을 설명해 주세요.

답변 예시:
- INNER JOIN은 두 테이블에 모두 존재하는 레코드만 반환하고, OUTER JOIN(LEFT/RIGHT)은 한쪽 테이블의 모든 데이터를 포함하여 반환합니다.

Q3. GROUP BY와 HAVING의 차이점을 설명해 주세요.

답변 예시:
- GROUP BY는 데이터를 그룹화하며, HAVING은 그룹화된 결과에 조건을 적용합니다.

심화 질문

Q4. 서브쿼리와 JOIN 중 언제 어떤 방식을 선택해야 하는지 설명해 주세요.

답변 예시:
- 서브쿼리는 단순 조건 필터링에 유리하며, JOIN은 여러 테이블 간의 관계를 명확하게 하여 데이터를 결합할 때 효율적입니다. 단, 데이터 양과 실행 계획에 따라 성능 차이가 발생할 수 있으므로 상황에 맞게 선택합니다.

Q5. 인덱스가 성능에 미치는 영향과 인덱스 생성 시 주의사항은 무엇인가요?

답변 예시:
- 인덱스는 조회 성능을 향상시키지만, INSERT, UPDATE, DELETE 작업 시 추가 부하가 발생할 수 있습니다. 따라서 자주 조회되는 컬럼에 한정하여 생성하고, 불필요한 인덱스는 주기적으로 제거하는 것이 좋습니다.

Q6. 윈도우 함수의 장점과 사용 사례를 설명해 주세요.

답변 예시:
- 윈도우 함수는 행 전체를 유지하면서 순위, 누적합, 이동 평균 등 집계 계산을 수행할 수 있어 복잡한 분석 쿼리에 유용합니다.

Q7. 뷰(View)와 저장 프로시저(Stored Procedure)를 사용함으로써 얻을 수 있는 이점은 무엇인가요?

답변 예시:
- 뷰는 복잡한 쿼리를 단순화하여 재사용성을 높이고, 저장 프로시저는 비즈니스 로직을 캡슐화하여 코드 중복을 줄이며 성능 최적화에 도움을 줍니다.

압박 질문

Q8. 대규모 데이터베이스 환경에서 JOIN, 서브쿼리, 그리고 고급 기능 사용으로 인한 성능 문제를 해결한 경험이 있다면 구체적으로 설명해 주세요.

답변 예시:
“한 프로젝트에서 다수의 JOIN과 서브쿼리로 인해 응답 속도가 크게 저하되었습니다.
- EXPLAIN을 통해 병목 구간을 파악하고, 일부 서브쿼리를 JOIN 또는 CTE로 변환했습니다.
- 또한, 윈도우 함수와 저장 프로시저를 활용하여 반복 계산 로직을 데이터베이스 내부로 옮기고, 불필요한 인덱스를 제거하여 최적화하였습니다.”

Q9. 복잡한 쿼리 작성 시 가독성과 유지보수를 높이기 위한 구체적인 방법은 무엇인가요?

답변 예시:
“복잡한 쿼리는 뷰, CTE, 또는 임시 테이블을 사용하여 단계별로 분리하고, 주석을 통해 각 단계의 목적과 로직을 명확히 기록합니다.”

Q10. 트리거나 저장 프로시저에서 발생할 수 있는 오류를 어떻게 처리하며, 성능 저하를 방지하기 위한 전략은 무엇인가요?

답변 예시:
“오류 처리 구문(DECLARE HANDLER 등)을 활용하여 예상치 못한 예외 발생 시 롤백하거나 로그를 남기고, 트리거는 최소한의 로직만 수행하여 데이터 변경 시 과도한 부하가 발생하지 않도록 설계합니다.”

저작자표시 비영리 동일조건 (새창열림)

'CS > 데이터베이스' 카테고리의 다른 글

[DB] ORM (1)	2025.02.20
[DB] 분산 시스템과 Sharding (0)	2025.02.10
[DB] B-tree와 B+tree (0)	2025.02.07
[DB] 트랜잭션 (0)	2025.02.06
[DB] 데이터 모델링 및 설계 (1)	2025.02.04

공지사항

최근에 올라온 글

최근에 달린 댓글

Total

Today

Yesterday

링크

TAG more

« 2025/12 »
일	월	화	수	목	금	토
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31

글 보관함

티스토리 뷰

[DB] SQL 기본

SQL의 이해와 필요성

기본 CRUD 명령어

SELECT

INSERT

UPDATE

DELETE

JOIN

정의

INNER JOIN

LEFT (OUTER) JOIN

RIGHT (OUTER) JOIN

FULL OUTER JOIN

CROSS JOIN

SELF JOIN

서브쿼리

GROUP BY, HAVING, ORDER BY

GROUP BY

HAVING

ORDER BY

인덱스와 성능 최적화

인덱스란?

인덱스의 종류

클러스터드 인덱스 (Clustered Index)

비클러스터드 인덱스 (Non-Clustered Index)

복합 인덱스 (Composite Index)

커버링 인덱스 (Covering Index)

인덱스 사용 시 고려사항

인덱스 오버헤드

컬럼 선택

복합 인덱스의 순서

인덱스 조각화(Fragmentation)

실행 계획(EXPLAIN) 분석

인덱스 최적화 전략

적절한 인덱스 생성

커버링 인덱스 활용

인덱스 모니터링 및 관리

성능 최적화 기법

쿼리 최적화

인덱스 활용

정규화/반정규화

캐싱

파티셔닝

고급 SQL 기능

윈도우 함수 (Window Functions)

뷰(View)와 저장 프로시저 (Stored Procedures)

트리거(Trigger)

오류 처리와 예외 관리

NULL 값 처리와 함수 사용

복잡한 쿼리의 가독성과 유지보수

면접 대비 질문

기본 질문

Q1. SQL의 기본 CRUD 명령어와 각각의 역할에 대해 설명해 주세요.

Q2. INNER JOIN과 OUTER JOIN의 차이점을 설명해 주세요.

Q3. GROUP BY와 HAVING의 차이점을 설명해 주세요.

심화 질문

Q4. 서브쿼리와 JOIN 중 언제 어떤 방식을 선택해야 하는지 설명해 주세요.

Q5. 인덱스가 성능에 미치는 영향과 인덱스 생성 시 주의사항은 무엇인가요?

Q6. 윈도우 함수의 장점과 사용 사례를 설명해 주세요.

Q7. 뷰(View)와 저장 프로시저(Stored Procedure)를 사용함으로써 얻을 수 있는 이점은 무엇인가요?

압박 질문

Q8. 대규모 데이터베이스 환경에서 JOIN, 서브쿼리, 그리고 고급 기능 사용으로 인한 성능 문제를 해결한 경험이 있다면 구체적으로 설명해 주세요.

Q9. 복잡한 쿼리 작성 시 가독성과 유지보수를 높이기 위한 구체적인 방법은 무엇인가요?

Q10. 트리거나 저장 프로시저에서 발생할 수 있는 오류를 어떻게 처리하며, 성능 저하를 방지하기 위한 전략은 무엇인가요?

'CS > 데이터베이스' 카테고리의 다른 글

티스토리툴바