[전공 기말고사 정리] 데이터베이스 Ch 04

1. NULL 값 처리

NULL: 값이 아직 지정되지 않은 특수한 상태(0이나 공백과 다름)

NULL 찾기: WHERE price IS NULL (옳음) / WHERE price = NULL (틀림)
NULL 아닌 것: WHERE price IS NOT NULL (옳음) / WHERE price <> NULL (틀림)
NULL 연산: 결과도 NULL (예: NULL + 100 = NULL)
집계 함수: NULL인 행 제외됨(단 COUNT(*)는 NULL 포함 전체 행 수)

IFNULL: NULL을 다른 값으로 대체
SELECT bookname, IFNULL(price, 0) AS price FROM Book; – price가 NULL이면 0으로 출력

포인트: NULL 비교는 반드시 IS NULL / IS NOT NULL 사용! = 로 비교하면 항상 결과 없음

2. 숫자 함수

ABS(x): 절댓값, 예) ABS(-15) → 15
ROUND(x, n): 소수점 n자리 반올림, 예) ROUND(4.567, 1) → 4.6
CEIL(x): 올림, 예) CEIL(4.1) → 5
FLOOR(x): 내림, 예) FLOOR(4.9) → 4
MOD(x, y): 나머지(=x%y), 예) MOD(10, 3) → 1
POWER(x, y): x의 y승, 예) POWER(2, 3) → 8
SQRT(x): 제곱근, 예) SQRT(16) → 4

복합 사용 예
SELECT ROUND(SUM(saleprice) / COUNT(*), 0) AS 평균판매가 FROM Orders;

3. 문자 함수

UPPER(s): 대문자로, 예) UPPER(‘hello’) → HELLO
LOWER(s): 소문자로, 예) LOWER(‘HELLO’) → hello
LENGTH(s): 바이트 수, 예) LENGTH(‘ABC’) → 3
CHAR_LENGTH(s): 문자 수, 예) CHAR_LENGTH(‘홍길동’) → 3
SUBSTR(s, n, len): n번째부터 len개 추출, 예) SUBSTR(‘홍길동’, 1, 1) → 홍
REPLACE(s, a, b): a를 b로 치환, 예) REPLACE(‘자전거’,‘자’,‘오’) → 오전거
CONCAT(s1, s2): 문자열 연결, 예) CONCAT(‘홍’,‘길동’) → 홍길동
TRIM(s): 양끝 공백 제거, 예) TRIM(’ hello ’) → hello
LPAD(s, n, c): 왼쪽에 c를 채워 n길이, 예) LPAD(‘5’,3,‘0’) → 005

예제) 성(姓)별 고객 수 구하기
SELECT SUBSTR(name, 1, 1) AS 성, COUNT(*) AS 고객수
FROM Customer
GROUP BY SUBSTR(name, 1, 1);

4. 날짜·시간 함수

SYSDATE(), NOW(): 현재 날짜+시간 반환
DATE_FORMAT(d, f): 날짜를 포맷 문자열로 변환, 예) DATE_FORMAT(orderdate, ‘%Y년%m월’)
STR_TO_DATE(s, f): 문자열을 날짜형으로 변환
YEAR(d), MONTH(d), DAY(d): 연/월/일 추출
DATEDIFF(d1, d2): 두 날짜 간 일수 차이, 예) DATEDIFF(‘2024-07-10’,‘2024-07-01’) → 9

날짜 포맷 코드
%Y: 4자리 연도 / %m: 2자리 월 / %d: 2자리 일 / %H: 24시간제 시 / %i: 분 / %s: 초

예제) 주문일을 ‘2024년07월’ 형태로 출력
SELECT DATE_FORMAT(orderdate, ‘%Y년%m월’) AS 주문월, SUM(saleprice) AS 월매출
FROM Orders
GROUP BY DATE_FORMAT(orderdate, ‘%Y년%m월’);

5. 부속질의 정리(심화)

중첩질의(WHERE절): 조건에 다른 SELECT 포함, =, IN, ANY, ALL, EXISTS
스칼라 부속질의(SELECT절): 반드시 단일 값 반환, = 등 비교연산자
인라인 뷰(FROM절): 결과를 테이블처럼 사용, 별칭(AS) 필요
상관 부속질의(WHERE절): 주질의 각 행마다 실행, EXISTS 자주 사용

스칼라 부속질의 예
SELECT od.orderid,
(SELECT cs.name FROM Customer cs WHERE cs.custid=od.custid) AS 고객명,
od.saleprice
FROM Orders od;

인라인 뷰 예
SELECT 고객명, 평균판매
FROM (SELECT custid, AVG(saleprice) AS 평균판매 FROM Orders GROUP BY custid) AS tmp
JOIN Customer cs ON tmp.custid = cs.custid;

6. 뷰(VIEW)

하나 이상의 테이블을 합쳐 만든 가상 테이블. 실제 데이터는 원본 테이블에 저장됨

뷰 생성
CREATE VIEW vBook AS SELECT * FROM Book WHERE bookname LIKE ‘%축구%’;

뷰 사용/수정/삭제
SELECT * FROM vBook;
CREATE OR REPLACE VIEW vBook AS SELECT * FROM Book WHERE bookname LIKE ‘%스포츠%’;
DROP VIEW vBook;

뷰의 장점
편리성: 복잡한 쿼리를 미리 정의해두고 간단히 호출
보안성: 민감한 컬럼 숨기고 필요한 것만 노출
독립성: 원본 테이블 구조 변경 시 앱에 영향 최소화

포인트: 뷰는 실제로 데이터를 저장하지 않음! 원본 데이터가 바뀌면 뷰도 같이 바뀜

7. 인덱스(INDEX)

책의 목차처럼, 데이터를 빠르게 찾기 위한 자료구조(B-tree 구조)

클러스터 인덱스: 기본키에 자동 생성, 데이터 정렬 저장, 테이블당 1개
보조 인덱스: 사용자가 추가로 만드는 인덱스, 여러 개 가능
UNIQUE 인덱스: 중복값 불허, UNIQUE 제약에 자동 생성

CREATE INDEX idx_book_name ON Book(bookname);
CREATE UNIQUE INDEX idx_book_isbn ON Book(isbn);
SHOW INDEX FROM Book;
ANALYZE TABLE Book; – 인덱스 재구성(단편화 해소)
DROP INDEX idx_book_name ON Book;

포인트: 인덱스는 WHERE·JOIN에 자주 쓰는 컬럼, 선택도 낮은 컬럼(값이 다양한 컬럼)에 생성. 너무 많으면(4~5개 초과) 오히려 성능 저하