반응형
1. 조인(Join)
- 두 개 이상의 테이블에서 행을 결합하는 방법
- 일반적으로 조인조건은 PK(Primary Key) 및 FK(Foregin Key)로 구성
- PK 및 FK 관계가 없더라도 논리적인 연관만 있으면 JOIN 가능
- 내부 조인 (INNER JOIN)
- 두 테이블의 교집합을 반환. 조인 조건을 충족하는 행만 반환
- 동등 조인(Equi-join)이라고도 함
- N개의 테이블 조인 시 N-1개의 조인 조건 필요
- 외부 조인 (OUTER JOIN): 하나의 테이블에는 존재하지만 다른 테이블에는 존재하지 않는 행도 포함하여 결과를 반환
- LEFT OUTER JOIN: 첫 번째 (왼쪽) 테이블의 모든 행과 일치하는 오른쪽 테이블의 행을 반환
- RIGHT OUTER JOIN: 두 번째 (오른쪽) 테이블의 모든 행과 일치하는 왼쪽 테이블의 행을 반환
- FULL OUTER JOIN: 양쪽 테이블의 모든 행을 반환
MySQL에서는 FULL OUTER JOIN을 직접 지원 X, 하지만 UNION이나 UNION ALL을 사용하여 비슷한 결과를 얻을 수 있음
- 자연 조인 (NATURAL JOIN): 두 테이블에서 이름과 자료형이 같은 모든 열을 기준으로 조인을 수행
- 교차 조인 (CROSS JOIN): 두 테이블의 모든 가능한 조합을 반환
1.1. 조인시 주의사항
- 조인 조건 누락
- 조인 조건을 누락하면 두 테이블의 모든 가능한 조합이 결과로 반환.
- 이를 카테시안 곱이라고 하며, 의도하지 않은 큰 결과 세트를 생성할 수 있음
- NULL 처리
- 외부 조인을 사용할 때, 일치하는 행이 없는 경우 NULL 값이 결과에 포함
- 따라서, 이러한 NULL 값을 처리하는 로직이 필요할 수 있음
- 성능 이슈
- 큰 테이블을 조인할 때는 성능이 크게 저하될 수 있음
- 이를 최적화하기 위한 적절한 인덱스 설정이 필요
1.2. 쿼리 속도를 높이는 방법
- 인덱스 사용: 조인 조건에 사용되는 열에 인덱스를 생성시 인덱스를 사용하여 조인을 빠르게 수행 가능
- 필요한 열만 선택: SELECT * 대신 필요한 열만 명시적으로 선택시 불필요한 데이터를 처리하는 시간을 줄일 수 있음
- WHERE 절 사용: 가능한 한 조인 전에 WHERE 절을 사용하여 필터링을 수행시 조인해야 하는 행의 수를 줄일 수 있음
- 조인 순서 최적화: MySQL은 자동으로 최적의 조인 순서를 결정, 경우에 따라서는 STRAIGHT_JOIN을 사용하여 조인 순서를 명시적으로 지정하는 것이 유리함
1.3. 예시
Employees 테이블
| EmployeeID | EmployeeName | DepartmentID |
| 1 | John | 1 |
| 2 | Jane | 2 |
| 3 | Mark | 1 |
| 4 | Emily | 3 |
Departments 테이블
| DepartmentID | DepartmentName |
| 1 | Sales |
| 2 | Marketing |
| 3 | HR |
| 4 | IT |
INNER JOIN (내부 조인)
<code />
SELECT Employees.EmployeeID, Employees.EmployeeName, Employees.DepartmentID, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
| EmployeeID | EmployeeName | DepartmentID | DepartmentName |
| 1 | John | 1 | Sales |
| 2 | Jane | 2 | Marketing |
| 3 | Mark | 1 | Sales |
| 4 | Emily | 3 | HR |
- INNER JOIN은 두 테이블 간의 일치하는 값을 기준으로 결과를 반환
- 위의 예제에서는 "Employees" 테이블과 "Departments" 테이블을 DepartmentID 열을 기준으로 INNER JOIN하여 일치하는 값을 가져옴
OUTER JOIN (외부 조인)
<code />
SELECT Employees.EmployeeID, Employees.EmployeeName, Departments.DepartmentID, Departments.DepartmentName
FROM Employees
LEFT OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
| EmployeeID | EmployeeName | DepartmentID | DepartmentName |
| 3 | Mark | 1 | Sales |
| 1 | John | 1 | Sales |
| 2 | Jane | 2 | Marketing |
| 4 | Emily | 3 | HR |
| NULL | NULL | 4 | IT |
- OUTER JOIN은 두 테이블 간의 일치하지 않는 값을 포함하여 결과를 반환
- 위의 예제에서는 "Employees" 테이블과 "Departments" 테이블을 DepartmentID 열을 기준으로 LEFT OUTER JOIN하여 모든 직원과 일치하는 부서를 가져옴
- 오른쪽에는 "Employees" 테이블에는 없는 부서인 IT 부서를 나타내기 위해 NULL 값을 포함
NATURAL JOIN (자연 조인)
<code />
SELECT EmployeeID, EmployeeName, DepartmentID
FROM Employees
NATURAL JOIN Departments;
| EmployeeID | EmployeeName | DepartmentID |
| 1 | John | 1 |
| 2 | Jane | 2 |
| 3 | Mark | 1 |
| 4 | Emily | 3 |
- NATURAL JOIN은 두 테이블 간의 동일한 열 이름을 기준으로 일치하는 값을 가져옴
- 위의 예제에서는 "Employees" 테이블과 "Departments" 테이블을 DepartmentID 열을 기준으로 NATURAL JOIN하여 일치하는 값을 가져옴
- 결과에는 EmployeeID, EmployeeName, DepartmentID 열만 포함
CROSS JOIN (교차 조인)
<code />
SELECT Employees.EmployeeID, Employees.EmployeeName, Departments.DepartmentID, Departments.DepartmentName
FROM Employees
CROSS JOIN Departments
ORDER BY Employees.EmployeeID;
| EmployeeID | EmployeeName | DepartmentID | DepartmentName |
| 1 | John | 1 | Sales |
| 1 | John | 2 | Marketing |
| 1 | John | 3 | HR |
| 1 | John | 4 | IT |
| 2 | Jane | 1 | Sales |
| 2 | Jane | 2 | Marketing |
| 2 | Jane | 3 | HR |
| 2 | Jane | 4 | IT |
| 3 | Mark | 1 | Sales |
| 3 | Mark | 2 | Marketing |
| 3 | Mark | 3 | HR |
| 3 | Mark | 4 | IT |
| 4 | Emily | 1 | Sales |
| 4 | Emily | 2 | Marketing |
| 4 | Emily | 3 | HR |
| 4 | Emily | 4 | IT |
- CROSS JOIN은 두 테이블의 모든 가능한 조합을 반환
- 위의 예제에서는 "Employees" 테이블과 "Departments" 테이블을 CROSS JOIN하여 모든 직원과 모든 부서의 조합을 가져옴
2. 서브 쿼리(SubQuery)
2.1. 서브 쿼리(SubQuery)란?
- 하나의 SQL문 안에 포함되어 있는 SQL문
- 서브 쿼리를 포함하는 SQL을 외부 쿼리(outer query) 또는 메인 쿼리라고 부르며, 서브 쿼리는 내부 쿼리(inner query)라고도 부름
2.2. 서브 쿼리의 종류
- 중첩 서브 쿼리(Nested SubQuery) - WHERE 절에 작성하는 서브 쿼리
- 단일-행, 다중-행, 다중-열
- 인라인 뷰(Inline-view) - FROM 절에 작성하는 서브 쿼리
- 스칼라 서브 쿼리(Scalar SubQuery) - SELECT 문에 작성하는 서브 쿼리
2.3. 서브 쿼리를 포함할 수 있는 SQL문
- SELECT, FROM, WHERE, HAVING, ORDER BY
- INSERT문의 VALUES
- UPDATE문의 SET
2.4. 서브 쿼리의 사용시 주의사항
- 서브 쿼리는 반드시 ()로 감싸서 사용
- 서브 쿼리는 단일 행 또는 다중 행 비교 연산자와 함께 사용 가능
- 단일 행 비교 연산자는 서브 쿼리 결과가 1건 이하이어야 함
- 복수 행 비교 연산자는 결과 건수와 상관없음
2.5. 서브 쿼리의 필요성
2.6. 중첩 서브 쿼리(Nested SubQuery), 단일 행
2.6.1. 사번이 7788인 사원의 부서 이름을 조회
- 서브쿼리 x
<sql />
SELECT d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.empno = 7788;
- 서브쿼리 o
<sql />
SELECT dname
FROM dept
WHERE deptno = (SELECT deptno
FROM emp
WHERE empno = 7788);
서브쿼리 사용 이유
dname을 조회하기 위해 INNER JOIN 수행 → 쿼리 복잡해질 시 카테시안곱으로 인해 속도 느려짐 → Sub Query로 JOIN 없이 조회 가능
2.6.2. 매니저 이름이 'KING'인 사원의 사번, 이름, 부서번호, 업무 조회
<sql />
SELECT empno, ename, deptno, job
FROM emp
WHERE mgr = (SELECT empno
FROM emp
WHERE ename = 'KING');
2.6.3. 7566 사원보다 급여를 많이 받는 사원의 이름, 급여를 조회
<sql />
SELECT ename, sal
FROM emp
WHERE sal > (SELECT sal
FROM emp
WHERE empno = 7566);
2.6.4. 20번 부서의 평균 급여가 많은 사원의 사번, 이름, 업무, 급여 조회
<sql />
SELECT empno, ename, job, sal
FROM emp
WHERE sal > (SELECT AVG(sal)
FROM emp
WHERE deptno = 20);
2.6.5. 업무가 'TURNER'와 같고, 사번이 7934인 직원보다 급여가 많은 사원의 사번, 이름, 업무를 조회
<sql />
SELECT empno, ename, job
FROM emp
WHERE job = (SELECT job
FROM emp
WHERE ename = 'TURNER')
AND sal > (SELECT sal
FROM emp
WHERE empno = 7934);
2.7. 서브 쿼리 - 중첩 서브 쿼리(Nested SubQuery), 다중 행
- 서브 쿼리 결과가 다중 행을 반환 : IN, ANY, ALL 연산자와 함께 사용
2.7.1. 업무가 'SALESMAN'인 직원들 중 최소 한 명 이상보다 많은 급여를 받는 사원의 이름, 급여, 업무를 조회
<sql />
SELECT ename, sal, job
FROM emp
WHERE sal > ANY (SELECT sal
FROM emp
WHERE job = 'SALESMAN');
- > ANY : 최솟값보다 큼
- < ANY : 최댓값보다 작음
2.7.2. 업무가 'SALESMAN'인 모든 직원보다 급여(커미션포함)를 많이 받는 사원의 이름, 급여, 업무, 입사일, 부서번호 조회
<sql />
SELECT ename, sal, job, hiredate, deptno
FROM emp
WHERE sal > ALL (SELECT sal + IFNULL(comm, 0)
FROM emp
WHERE job = 'SALESMAN');
2.7.3.
2.7.4. 직원이 최소 한명이라도 근무하는 부서의 부서번호, 부서이름, 위치
- DISTINCT 키워드를 이용해 중첩되는 행 제거
- in 다중행에 하나라도 일치하면 조회,= ANY와 같음
<sql />
SELECT deptno, dname, loc
FROM dept
WHERE deptno IN (SELECT DISTINCT deptno
FROM emp);
-- ANY
SELECT deptno, dname, loc
FROM dept
WHERE deptno = ANY (SELECT DISTINCT deptno
FROM emp);
2.8. 서브 쿼리 - 중첩 서브 쿼리(Nested SubQuery), 다중 열
- 서브 쿼리의 결과값이 두 개 이상의 column을 반환하는 서브 쿼리
- PK가 복합키(Composite Key)이거나, 여러 column의 값을 한꺼번에 비교해야 할 경우 사용
- 행 생성자(row constructor)를 이용하여 다중 열 서브 쿼리를 비교
- 아래 두 SQL은 의미상 동일하고 동일한 방식으로 처리됨
<sql />
SELECT * FROM t1 WHERE (column1, column2) = (1,1);
SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;
- 결과가 다중 행일 경우 IN 연산자를 이용
<sql />
SELECT column1, column2, column3
FROM t1
WHERE (column1, column2, column3)
IN (SELECT column1, column2, column3
FROM t2);
2.8.1. 이름이 FORD인 사원과 매니저 및 부서가 같은 사원의 이름, 매니저번호, 부서번호 조회
<sql />
SELECT ename, mgr, deptno
FROM emp
WHERE (mgr , deptno) = (SELECT mgr, deptno
FROM emp
WHERE ename = 'FORD')
AND ename <> 'FORD';
2.8.2. 각 부서별 입사일이 가장 빠른 사원의 사번, 이름, 부서번호, 입사일 조회
<sql />
SELECT empno, ename, deptno, hiredate
FROM emp
WHERE (deptno , hiredate) IN (SELECT deptno, MIN(hiredate)
FROM emp
GROUP BY deptno);
2.9. 서브 쿼리 - 상호연관 서브 쿼리(Correlated SubQueries)
- 외부 쿼리에 있는 테이블에 대한 참조를 하는 서브 쿼리를 의미
- 예시
<sql />
SELECT * FROM t1
WHERE column1 = ANY(SELECT column1
FROM t2
WHERE t2.column2 = t1.column2);
- 서브 쿼리의 FROM에는 t1에 대한 선언이 존재하지 않음 → 서브 쿼리는 외부 쿼리(메인 쿼리)에서 t1 참조
- 테이블에서 행을 먼저 읽어서 각 행의 값을 관련된 데이터와 비교하는 방법 중 하나
- 기본 질의에서 고려된 각 후보행에 대해 서브 쿼리가 다른 결과를 반환해야 하는 경우 사용
- 서브 쿼리에서는 메인 쿼리의 컬럼명을 사용할 수 있으나, 메인 쿼리에서는 서브 쿼리의 컬럼명 사용 불가
2.9.1. 소속 부서의 평균 급여보다 많은 급여를 받는 사원의 이름, 급여, 부서번호, 입사일, 업무 조회
<sql />
SELECT ename, sal, deptno, hiredate, job
FROM emp e
WHERE sal > (SELECT AVG(sal)
FROM emp
WHERE deptno = e.deptno)
ORDER BY deptno;
2.10. 서브 쿼리 - 인라인 뷰(Inline View)
- FROM절에서 사용되는 서브 쿼리
- 동적으로 생성된 테이블로 사용 가능
- 뷰와 같은 역할
<sql />
SELECT ... FROM (subquery) [AS] tbl_name(col_list)...
- 인라인 뷰는 SQL문이 실행될 때만 임시적으로 생성되는 뷰
→ 데이터베이스에 해당 정보가 저장 X → 동적 뷰(Dynamic View)라고도 부름
2.10.1. 모든 사원의 평균 급여보다 적게 받는 사원들과 같은 부서에서 근무하는 사원의 사번, 이름, 급여, 부서번호 조회
<sql />
SELECT e.empno, e.ename, e.sal, e.deptno
FROM emp e, (SELECT DISTINCT deptno
FROM emp
WHERE sal < (SELECT AVG(sal)
FROM emp)
) AS d
WHERE e.deptno = d.deptno;
2.10.2. 모든 사원에 대하여 사원의 이름, 부서번호, 급여, 사원이 소속된 부서의 평균 급여를 조회(단, 이름 오름차순)
<sql />
SELECT e.ename, e.deptno, e.sal, d.avgsal
FROM emp e, (SELECT deptno, AVG(sal) AS avgsal
FROM emp
GROUP BY deptno) d
WHERE e.deptno = d.deptno
ORDER BY e.ename;
2.11. 서브 쿼리 - 스칼라 서브 쿼리(Scalar SubQuery)
- 하나의 행에서 하나의 컬럼 값만 반환하는 서브 쿼리
- 다음과 같은 경우에 사용 가능
- GROUP BY를 제외한 SELECT의 모든 절
- INSERT문의 VALUES
- 조건 및 표현식 부분
- UPDATE문의 SET 또는 WHERE절에서 연산자 목록
2.11.1. 사원의 이름, 부서번호, 급여, 소속부서의 평균 급여를 조회
<sql />
SELECT ename, deptno, sal, (SELECT AVG(sal)
FROM emp
WHERE deptno = e.deptno) AS avgsal
FROM emp e;
2.11.2. 부서번호가 10인 부서의 총 급여, 20인 부서의 평균 급여, 30인 부서의 최고, 최저 급여
<sql />
SELECT (SELECT SUM(sal)
FROM emp
WHERE deptno = 10) AS SUM10,
(SELECT AVG(sal)
FROM emp
WHERE deptno = 20) AS AVG20,
(SELECT MAX(sal)
FROM emp
WHERE deptno = 30) AS MAX30,
(SELECT MIN(sal)
FROM emp
WHERE deptno = 30) AS MIN30
FROM dual;
2.11.3. 모든 사원의 번호, 이름, 부서번호, 입사일을 조회(단, 부서이름기준으로 내림차순)
<sql />
UPDATE emp SET deptno = 40
WHERE empno = 4168;
SELECT empno, ename, deptno, hiredate
FROM emp e
ORDER BY (SELECT dname
FROM dept
WHERE deptno = e.deptno) DESC;
2.11.4. emp table을 emp_copy라는 이름으로 복사(컬럼 이름 동일)
<sql />
CREATE TABLE emp_copy
(SELECT * FROM emp);
2.11.5. emp table 구조만 emp_blank라는 이름으로 복사하여 생성
<sql />
CREATE TABLE emp_blank
(SELECT * FROM emp WHERE 1 = 0);
2.11.6. 부서 번호가 30인 사원의 모든 정보를 emp_blank에 INSERT
<sql />
INSERT INTO emp_blank
(SELECT * FROM emp WHERE deptno = 30);
3. 데이터베이스 모델링(Database Modeling)
현실세계에서 데이터베이스까지 만들어지는 과정
개념적 데이터 모델 → 논리적 데이터 모델 → 물리적 데이터 모델
| 개념적 데이터 모델링 | 추상화 수준이 높고 업무중심적이고 포괄적인 수준의 모델링 진행. 전사적 데이터 모델링, EA 수립시 많이 사용 |
| 논리적 데이터 모델링 | 시스템으로 구축하고자 하는 업무에 대해 Key, 속성, 관계 등을 정확하게 표현, 재사용성이 높음 |
| 물리적 데이터 모델링 | 실제로 데이터베이스에 이식할 수 있도록 성능, 저장 등 물리적인 성격을 고려하여 설계 |
4. ERD 기호
4.1. 개념적 데이터베이스 모델링
- 개체(Entity)
- 사용자와 관계가 있는 주요 객체(데이터로 관리되어야 하는 것)
- Entity 찾는 법
- 영속적으로 존재하는 것
- 새로 식별이 가능한 데이터 요소를 가짐
- Entity는 Attribute를 가져야 함
- 속성(Attribute)
- 저장할 필요가 있는 실체에 관한 정보
- 개체(Entity)의 성질, 분류, 수량, 상태, 특성 등을 나타내는 세부사항
- 개체에 포함되는 속성의 숫자는 10개 내외로 하는 것이 바람직함
- 최종 DB 모델링 단계를 통해 테이블의 컬럼으로 활용
개념적 데이터베이스 모델링
식별자: 한 개체(Entity) 내에서 인스턴스를 구분할 수 있는 단일 속성 또는 속성 그룹
후보키(Candidate Key): 개체 내에서 각각의 인스턴스를 구분할 수 있는 속성(기본키 될 수 있음)
기본키(Primary Key): 개체(Entity)에서 각 인스턴스를 유일하게 식별하는데 적합한 Key
대체키(Alternate Key): 후보키 중에서 기본키로 선정되지 않은 Key
복합키(Composite Key): 하나의 속성으로 기본키가 될 수 없는 경우 둘 이상의 컬럼을 묶어서 식별자로 정의
대리키(Surrogate Key): 식별자가 너무 길거나 여러 개의 속성으로 구성되어 있는 경우 인위적으로 추가
- 관계(Relationship): 두 Entity 간의 업무적인 연관성 또는 관련 사실
- 각 Entity 간에 특정한 존재여부 결정
- 현재의 관계 뿐만 아니라 장래에 사용될 경우도 고려
4.1.1. ERD 관계를 설정하는 순서
- 관계가 있는 두 실체를 실선(점선)으로 연결하고 관계 부여
- 관계 차수를 표현
- 선택성을 표시
4.2. 논리적 데이터베이스 모델링
4.2.1. 기본키(Primary Key)
- 후보키 중에서 선택한 주 키
- 널(Null)의 값을 가질 수 없다(Not Null).
- 동일한 값이 중복해서 저장될 수 없다(Unique).
4.2.2. 참조키, 이웃키(Foreign Key)
- 관계를 맺는 두 엔티티에서 서로 참조하는 릴레이션의 attribute로 지정되는 키
4.3. 정규화
일반적인 겨우 1차 정규화에서 3차 정규화까지만 이뤄진다.
4.3.1. 1차 정규화
- 테이블 하나의 컬럼에는 여러 개의 데이터 값이 중복되어 나타나지 않아야 함
- 모든 엔티티타입의 속성은 하나의 속성값만을 가지고 있어야 하며 반복되는 속성의 집단은 별도의 엔티티타입으로 분리
4.3.2. 2차 정규화
- 수출여부, 고객번호, 사업자번호, 우선순위는 '제품번호+주문번호'로 구성된 주 식별자에 의해 종속적이지 않고 단지 주문번호에 의해 종속적인 관계를 가지고 있음
- 주문수량은 제품에 대해서 주문한 양이 되므로 '제품번호+주문번호'에 종속적임
4.3.3. 3차 정규화
- 3차 정규화의 대상이 되는 속성들을 이전종속(Transitive Dependence) 관계속성이라 함
- 주식별자에 의해 종속적인 속성 중에서 다시 다른 속성을 결정하는 결정자가 존재하여 다른 속성이 이 결정자 속성에 종속적인 관계를 나타내는 관계
4.3.4. 정규화(Normalization)의 목적
- 데이터베이스의 변경 시 이상 현상 제거
- 데이터베이스 구조 확장 시 재 디자인 최소화
- 사용자에게 데이터 모델을 더욱 의미있게 작성하도록 함
- 다양한 질의 지원
4.4. 물리적 데이터베이스 모델링
- 논리적 데이터베이스 모델링 단계에서 얻어진 데이터베이스 스키마를 좀 더 효율적으로 구현하기 위한 작업
- DBMS 특성에 맞게 실제 데이터베이스 내의 개체들을 정의하는 단계
- Column의 domain 설정(int, varchar, date, ...)
- 데이터 사용량 분석과 업무 프로세스 분석을 통해서 보다 효율적인 데이터베이스가 될 수 있도록 효과적인 인덱스를 정의하고 상황에 따른 역정규화 작업 수행
- Index, Trigger, 역정규화
4.4.1. 역정규화(Denormalization)
- 시스템 성능을 고려하여 기존 설계를 재구성하는 것
- 정규화에 위배되는 행위
- 테이블의 재구성
4.4.2. 역정규화 방법
- 데이터 중복(컬럼 역정규화)
- 파생 컬럼 생성
- 테이블 분리
- 요약 테이블 생성
- 테이블 통합
반응형
'TIL' 카테고리의 다른 글
| [TIL-51/240327] 그래프 비용(서로소 집합, MST, 크루스칼) (1) | 2024.03.28 |
|---|---|
| [TIL-50/240322] JDBC (0) | 2024.03.23 |
| [TIL-48/240320] DDL & DML (0) | 2024.03.21 |
| [TIL-47/240319] MySQL, SELECT (0) | 2024.03.20 |
| [TIL-46/240318] File Upload & Download (1) | 2024.03.19 |
