[SQL] SQLD 복습
SQLD를 취득한지 1년즈음 되니 복습의 필요성을 체감해 간략히 정리하였다.
참고) SQLD에서 다루는 DBMS는 Oracle 기준이라고 한다. mySQL에 구현되지 않은 것도 고려하여 정리해보았다.
SQL 이란
- Structured Query Language
- 관계형 데이터베이스(RDBMS)에서 데이터 조회 및 조작, DBMS 시스템 관리 기능을 명령하는 언어
* 관계형 데이터베이스란 : 계정과 테이블, 스키마(기본적인 구조)를 가진 데이터베이스(데이터의 집합)
테이블 : 행(row)과 열(column, 속성)을 갖는 2차원 구조로 데이터를 입력하여 저장하는 최소 단위
하나의 테이블은 반드시 하나의 유저 소유여야 하며, 소유자가 같은 경우 테이블명은 중복될 수 없다
SQL 종류
- DDL (정의어) - 테이블을 조작 : CREATE, ALTER, DROP, TRUNCATE
- DML (조작어) - 데이터 조작 : INSERT, DELETE, UPDATE, MERGE
- DCL (데이터 제어) - 권한 조작 : GRANT, REVOKE
- TCL (Transaction Control) - 트랜잭션 조작 (트랜잭션 : 데이터베이스에서 논리적인 작업 단위) : COMMIT, ROLLBACK
- DQL (Data Query) - Select문 그 자체. : SELECT
트랜잭션 특징
- Atomicity (원자성)
- 트랜잭션 내의 모든 작업이 완전히 실행되거나, 전혀 실행되지 않아야 함
- 하나라도 실패하면 전체 취소(ROLLBACK)
- Consistency (일관성)
- 트랜잭션이 실행되기 전과 후에 데이터베이스의 무결성이 유지되어야 함
- ex) 계좌 이체에서 돈이 빠져나가면, 반대쪽 계좌에 반드시 입금되어야 함
- Isolation (고립성, 독립성)
- 여러 트랜잭션이 동시에 실행될 때, 서로 영향을 주지 않아야 함
- ex) A 사용자가 상품을 구매하는 동안 B 사용자가 같은 상품을 동시에 구매할 경우, 데이터 충돌이 발생하면 안 됨
- Durability (지속성)
- 트랜잭션이 성공적으로 완료되면(Commit), 그 변경 사항은 영구적으로 저장됨
- ex) 데이터베이스 장애가 발생해도 commit된 내용은 유지되어야 함.
데이터 무결성 : 데이터의 정확성과 일관성을 유지하고, 데이터에 결손이 없다는 걸 보증하는 것 -> 이를 유지하는 것이 DBMS의 중요한 기능
데이터 무결성 종류
1. 개체 무결성
2. 참조 무결성
3. 도메인 무결성
4. NULL 무결성
5. 고유 무결성
6. 키 무결성
SELECT의 구조
집합 연산자
- 두 개 이상의 SELECT 결과를 하나로 합치는 SQL 연산자
- MYSQL에서는 UNION과 UNION ALL만 지원하고, INTERSECT와 MINUS는 따로 구현해야함
1. UNION (합집합, 중복 제거)
SELECT name FROM employees
UNION
SELECT name FROM customers;: 두개의 SELECT 결과를 하나로 합치되, 중복된 데이터가 제거
2. UNION ALL (합집합, 중복 허용)
SELECT name FROM employees
UNION ALL
SELECT name FROM customers;
: UNION과 동일하나 중복 데이터를 제거하지 않음
3. INTERSECT (교집합)
-- oracle
SELECT name FROM employees
INTERSECT
SELECT name FROM customers;
-- mySQL
SELECT name FROM employees
INNER JOIN customers USING(name);
: 두 SELECT 결과에서 공통으로 존재하는 데이터만 반환
mySQL의 경우 inner join으로 구현
4. MINUS (차집합)
-- oracle
SELECT name FROM employees
MINUS
SELECT name FROM customers;
-- mySQL
SELECT name FROM employees
WHERE name NOT IN (SELECT name FROM customers);
: 첫 번째 SELECT 결과에서 두 번째 SELECT 결과를 제외한 데이터만 반환
mySQL의 경우 not in 또는 left join으로 구현
JOIN 연산자
- 두 개 이상의 테이블을 결합하여 원하는 데이터를 조회할 때 사용
JOIN 종류 |
설명 |
| INNER JOIN | 두 테이블에서 조건이 일치하는 행만 반환 |
| LEFT JOIN | 왼쪽(첫 번째) 테이블의 모든 행을 반환, 오른쪽(두 번째) 테이블에서 일치하는 행이 없으면 NULL 반환 |
| RIGHT JOIN | 오른쪽(두 번째) 테이블의 모든 행을 반환, 왼쪽(첫 번째) 테이블에서 일치하는 행이 없으면 NULL 반환 |
| FULL OUTER JOIN | 양쪽 테이블의 모든 행을 반환, 일치하지 않는 행은 NULL 반환 (MySQL에서는 직접 지원하지 않음) |
| CROSS JOIN | 두 테이블의 모든 가능한 조합(카테시안 곱)을 반환 |
| SELF JOIN | 같은 테이블을 자기 자신과 JOIN |
1. INNER JOIN (교집합)
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
: employees와 departments 테이블에서 부서가 일치하는 직원 정보 조회
2. LEFT JOIN (왼쪽 기준, 왼쪽은 모두 유지)
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
: employees의 모든 직원 정보를 가져오고, 해당 직원이 속한 departments 정보를 가져오되,
부서가 없는 직원도 포함 (부서가 없으면 NULL 표시)
왼쪽(employtees) 테이블은 모두 표시되며, 오른쪽(departments) 테이블에 없는 값은 NULL로 채워짐
3. RIGHT JOIN ( 오른쪽 기준, 오른쪽은 모두 유지 )
SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;
: 모든 부서 정보를 가져오고, 해당 부서에 속한 직원을 가져오되,
직원이 없는 부서도 포함 (직원이 없으면 NULL 표시)
오른쪽(departments) 테이블은 모두 표시되며, 왼쪽(employees) 테이블에 없는 값은 NULL로 채워짐
4. FULL OUTER JOIN (양쪽 모두 유지, MySQL에서는 직접 지원 X)
-- oracle
SELECT e.name, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON e.department_id = d.department_id;
-- mySQL
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
UNION
SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;
: employees와 departments의 모든 데이터 출력,
어느 한쪽에 없는 데이터는 NULL로 표시
mySQL에서는 left join과 right join을 union하여 구현
양쪽 테이블의 모든 데이터를 가져오면서, 일치하지 않는 경우 NULL로 채움
5. CROSS JOIN (모든 조합 반환)
SELECT p.name, c.color
FROM products p
CROSS JOIN colors c;
: products와 colors 테이블에서 모든 가능한 조합을 생성
6. SELF JOIN (자기 자신과 조인)
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.id;
: 직원 테이블에서 매니저와 직원 정보를 같은 테이블에서 연결
같은 테이블을 두 번 사용하여, 직원과 매니저의 관계를 표현
서브쿼리
- 하나의 SQL문 안에 포함되어 있는 또 다른 SQL 문
SELECT * FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');SELECT문 안의 WHERE 절에 또다른 SELECT문으로 선택되어 있음 (가장 일반적인 서브쿼리)
-> departments 테이블에서 Sales 부서의 department_id를 가져와 employees 테이블에서 해당 id로 부서 직원들을 조회
이때 WHERE 절 서브쿼리의 경우 서브쿼리 결과로 반환되는 행의 개수에 따라
단일행 서브쿼리, 다중행 서브쿼리로 나뉘는데
1) 단일행 서브쿼리
2) 다중행 서브쿼리
뷰(view)
- 하나 이상의 테이블을 기반으로 생성된 가상의 테이블
- 즉, 실제 데이터를 저장하지 않고 쿼리의 결과를 저장하는 개념
- 특정 열과 행만 노출해 민감한 데이터를 보호할 수 있음
CREATE VIEW high_salary_employees AS
SELECT id, name, salary
FROM employees
WHERE salary >= 5000;
: 직원 테이블에서 salary가 5000 이상인 직원만 조회하는 뷰 생성 (쿼리의 결과만 보기 위해)
계층형 질의 (oracle)
- 상위(부모) - 하위(자식) 관계가 있는 데이터를 계층적으로 조회하는 쿼리
- CONNECT BY 절 사용
SELECT LEVEL, employee_id, name, manager_id
FROM employees
START WITH manager_id IS NULL -- 최상위(루트) 노드 설정
CONNECT BY PRIOR employee_id = manager_id; -- 부모-자식 관계 정의
START WITH → 최상위(루트) 노드를 지정
CONNECT BY PRIOR → 부모-자식 관계 정의
LEVEL → 현재 노드의 계층(레벨)을 나타냄
예시 테이블
| employee_id | name | manager_id |
| 1 | CEO | NULL |
| 2 | Manager1 | 1 |
| 3 | Manager2 | 1 |
| 4 | Staff1 | 2 |
| 5 | Staff2 | 2 |
| 6 | Staff3 | 3 |
위 테이블을 바탕으로 위 쿼리를 실행했을 때 결과
| LEVEL | employee_id | name | manager_id |
| 1 | 1 | CEO | NULL |
| 2 | 2 | Manager1 | 1 |
| 2 | 3 | Manager2 | 1 |
| 3 | 4 | Staff1 | 2 |
| 3 | 5 | Staff2 | 2 |
| 3 | 6 | Staff3 | 3 |