SQL 기초부터 시작하기

?>,

 

 

 

2. 모델링

 

23) 논리 모델 → 물리모델

Q. 폭포수 모델에서 인덱스는 언제 생성해야 할까?

• 설계 단계에서 대략적인 스키마를 완성하게 됨, PK, FK 등 기본 키와 참조 무결성 역시 포함
• 인덱스, 파티셔닝, 기존의 테이블을 변경하는 일은 기본적으로 위험하다: 이미 데이터가 많이 쌓였고 사용자가 사용하는 상태이기 때문에 영향도 파악을 완벽하게 해야 함, 변경으로 인한 문제가 생길 가능성이 있음
• 인덱싱은 검색 속도를 높이기 위해 설정하는 것이기 떄문에, 하나의 쿼리에만 활용할 수 있도록 하면 안 된다, 시스템 내에서 어떤 쿼리들이 조회가 되고 있는지가 취합되어야 하는데 이게 되려면 개발은 마무리 단계가 되어야 한다 + 데이터 마이그레이션 고려

그렇다면 ? 

→ 인덱스와 파티셔닝은 서비스 개발이 마무리되고 실제 쿼리 패턴이 확인된 시점에 적용하는 것이 안전하다

 

 

 

2. SQL 입문

 

1. SQL 개요

 

2) SQL 분류

• DDL (Data Definition Language) : 데이터베이스 객체를 정의하거나 변경하는 데 사용
  • CREATE: 테이블, 뷰, 인덱스생성
  • DROP: 객체 삭제
  • TRUNCATE: 테이블 데이터 초기화
  • ALTER: 기존 객체 수정
• DML (Data Manipulation Language) : 데이터베이스의 데이터를 조작하는 데 사용
  • SELECT: 데이터 검색
  • UPDATE: 데이터 수정
  • DELETE: 데이터 삭제
  • INSERT: 데이터 삽입
• DCL (Data Control Language) : 데이터베이스 접근 권한을 관리
  • GRANT: 권한부여
  • REVOKE: 권한회수
• TCL (Transaction Control Language) :트랜잭션 관리를 위한 명령어 제공
  • COMMIT: 트랜잭션 확정
  • SAVEPOINT: 특정 지점 저장
  • ROLLBACK: 트랜잭션 취소

 

Q. DELETE 와 TRUNCATE 모두 테이블 데이터를 지우는데 왜 TRUNCATE 를 쓰는 걸까?

• DELETE : 조건을 걸어 일부 데이터를 지우거나 전체를 지울 수 있지만, 한 행씩 삭제하며 로그를 남기기 때문에 속도가 느리고 수동 커밋이 필요하다
• TRUNCATE : 테이블 전체를 한 번에 초기화하는 방식으로 동작해 매우 빠르다 - 데이터 페이지 제체를 초기화하는 방식으로 동작, 수백 만 건이 있어도 거의 즉시 삭제 됨, 하지만 자동으로 커밋되기 때문에 롤백이 불가능하므로, 대량 데이터 초기화나 테스트 데이터 정리에만 사용하는 것이 안전하다

 

Q. INSERT 하고 COMMIT 을 안 하면 어떻게 될까?

삽입한 나에게는 SELECT 해도 보이지만 다른 사람에게는 보이지 않음, 사실상 한시적으로 나에게만 보이는 거고 세션이 끊기면 사라지게 됨, 그러니까 늘 commit 을 하는 습관을 들여야 한다 !

 

 

 

3. WHERE

 

7) 특수 연산자 - LIKE

 

4. 실습

 

3~5) SQL 작성

SELECT CUSTOMER_ID, EMAIL 
FROM CUSTOMERS;

SELECT ACCOUNT_ID, BALANCE 
FROM ACCOUNTS;

SELECT AMOUNT, TRANSACTION_TYPE
FROM TRANSACTIONS;

SELECT NAME, PHONE 
FROM CUSTOMERS
WHERE ADDRESS LIKE '%Address 100%';

SELECT ACCOUNT_ID, BALANCE 
FROM ACCOUNTS
WHERE BALANCE >= 50000;

SELECT TRANSACTION_ID, AMOUNT 
FROM TRANSACTIONS
WHERE AMOUNT < 0;

SELECT AMOUNT, CUSTOMER_ID 
FROM LOANS
WHERE STATUS = 'APPROVED';

SELECT NAME, EMAIL
FROM CUSTOMERS
WHERE PHONE LIKE '555-005%';

SELECT ACCOUNT_ID, BALANCE
FROM ACCOUNTS
WHERE ACCOUNT_TYPE = 'SAVINGS' AND BALANCE >= 10000;

SELECT TRANSACTION_ID, AMOUNT 
FROM TRANSACTIONS
WHERE TRANSACTION_TYPE = 'DEPOSIT' AND AMOUNT >= 1000;

SELECT LOAN_ID, AMOUNT
FROM LOANS
WHERE AMOUNT >= 5000 AND STATUS = 'PENDING';

 

 

6. 실습

 

1~2) SQL 작성

SELECT name
FROM CUSTOMERS
ORDER BY name ASC;

SELECT account_id, balance
FROM ACCOUNTS
ORDER BY balance DESC;

SELECT account_id, amount
FROM TRANSACTIONS
ORDER BY amount ASC;

SELECT loan_id, customer_id
FROM LOANS
ORDER BY amount DESC;

SELECT name, email
FROM CUSTOMERS
ORDER BY name ASC, email DESC;

SELECT *
FROM ACCOUNTS
ORDER BY balance DESC, account_id ASC;

SELECT *
FROM TRANSACTIONS
ORDER BY TRANSACTION_TYPE ASC, AMOUNT DESC;

SELECT *
FROM LOANS
ORDER BY STATUS ASC, AMOUNT DESC;

 

 

 

8. GROUP BY

 

6) GROUP BY 와 HAVING

HAVING 절에서는 집계 함수로 나온 결과가 사용된다

WHERE 절에서 미리 필터링을 해서 데이터를 줄이고(행 필터링), HAVING 절에서 집계 후 결과를 필터링한다

그래서 이 순서를 지키지 않으면 syntax error 가 발생하게 된다

 

 

 

9. 실습

 

1) SQL 작성

SELECT SUM(amount) AS 총대출금액, COUNT(*) AS 대출건수
FROM LOANS
WHERE STATUS = 'APPROVED';

SELECT MONTH(TRANSACTION_DATE) AS 거래월, SUM(amount) AS 총거래금액
FROM TRANSACTIONS
GROUP BY 거래월
HAVING SUM(amount) >= 10000;

SELECT BRANCH_ID, AVG(AMOUNT) AS 평균대출금액, SUM(AMOUNT) AS 총대출금액
FROM LOANS
WHERE STATUS = 'APPROVED'
GROUP BY BRANCH_ID;

 

 

 

10. 함수

 

4) 문자 함수

Q. 왜 사용하는 걸까?

규격 맞추려고 ! API(Application Programing Interface)는 데이터를 받아서 일련의 과정을 거친 후에 값으로 돌려주는데, 파라미터를 줄 때는 규격을 맞춰서 주게 된다. 예를 들어, 원장 데이터는 굉장히 많은 것들을 담고 있기 때문에 규격을 맞춰서 주고 받아야 하는데 이럴 때 사용한다

 

 

8) NULL 처리 함수

• IFNULL(expr1, expr2) : expr1이 NULL이면 expr2 반환, 아니면 expr1 반환

SELECT IFNULL(phone, '연락처 없음') FROM customers;	// phone이 NULL이면 "연락처 없음", 아니면 phone 값 그대로.

 

• COALESCE(expr1, expr2, expr3, …) : 왼쪽부터 순서대로 평가하여 첫 번째 NULL이 아닌 값 반환, 인자를 2개 이상 줄 수 있음

SELECT COALESCE(email_home, email_work, '이메일 없음') FROM users;	// home → work 순으로 확인하고, 둘 다 NULL이면 '이메일 없음'.

 

• NULLIF(expr1, expr2) : expr1과 expr2가 같으면 NULL 반환, 다르면 expr1 반환, 주로 “값이 같으면 NULL로 치환”할 때 사용

SELECT NULLIF(score, 0) FROM exams;	// score가 0이면 NULL 반환, 아니면 score 그대로.

 

 

ASC(오름차순) 정렬에서는 NULL이 먼저 나오는 게 일반적이다(DBMS마다 다르지만 MySQL은 NULL을 가장 작은 값으로 취급)

 

ISNULL(SALARY)의 역할은 SALARY 가 NULL이면 1, 아니면 0 반환 → 그런데 ASC 이므로 0부터 먼저 나옴, 즉, NOT NULL인 데이터 먼저 출력되는 것임 = NULL 인 애들읕 맨 뒤에 모여서 출력되게 됨

 

 

11. CASE WHEN

 

1) CASE WHEN

SELECT ID
FROM TBL
GROUP BY ID
HAVING COUNT(*) = 2
ORDER BY (CASE WHEN ID = 999 THEN 0 ELSE ID END);

-- 출력
999
100

• CASE WHEN ID = 999 THEN 0 ELSE ID END 인 경우, 999가 맨 앞
• CASE WHEN ID = 999 THEN 1 ELSE ID END 인 경우, 맨 뒤

 

 

12. JOIN

 

3) JOIN 의 종류

 

Q. 아래에서 EMP 테이블과 DEPT 테이블을 C 컬럼을 이용한 equi 조인으로 LEFT, FULL, RIGHT OUTER JOIN 했을 때 각각 생성되는 결과 건수로 가장 적절한 것은?

 

EMP 테이블

A	B	C
1	b	c
2	d	d
3	w	w
5	y	w

 

DEPT 테이블

C	D	E
w	1	10
z	4	11
v	2	22

 

조인 조건은 EMP.C = DEPT.C

 

1. INNER JOIN

• EMP.C 값: c, d, w, w
• DEPT.C 값: w, z, v
• 공통 값: w → EMP에서 2건, DEPT에서 1건 → 2건 매칭

 

2. LEFT OUTER JOIN

• EMP 전체(4건)를 기준
• 매칭 안 되는 경우 DEPT 쪽이 NULL
• 매칭되는 경우: w → 2건
• 매칭 안 되는 경우: c, d → 각각 1건
  → 총 4건

 

3. RIGHT OUTER JOIN

• DEPT 전체(3건)를 기준
• 매칭되는 경우: w → 2건
• 매칭 안 되는 경우: z, v → 각각 1건
  → 총 4건

 

4. FULL OUTER JOIN

• LEFT 결과(4건) + RIGHT 결과 중 LEFT에 없는 것
• LEFT에 없는 DEPT.C 값: z, v → 각각 1건 추가→ 총 6건

 

7) SELF JOIN

하이라키 구조: 한 테이블에 부모 컬럼과 자식 컬럼이 동시에 있는 것

E1.MANAGER_ID = E2.EMPLOYEE_ID; 이 부분이 하이라키 구조인 것

‘위계적 구조’ 또는 ‘계층 구조’라고도 하며, 상하 관계가 명확하게 구분되는 트리 형태의 구조

예를 들어 쇼핑몰 카테고리일 경우 대분류, 중분류, 소분류가 있고 그 안에 다른 세세한 분류들이 있음