SQL - 특수 연산자, 함수, 조인, 뷰

CS/데이터베이스시스템

SQL - 특수 연산자, 함수, 조인, 뷰

haong_ 2025. 3. 31. 22:21

데이터 정렬과 특수 연산자

데이터의 정렬

ORDER BY 절을 사용
검색 결과를 특정 컬럼에 대해 오름차순(ASC)/내림차순(DESC)으로 정렬
생략시 ASC가 기본 정렬
정렬 기준이 여러개 일경우 각각 오름/내림차순 지정 가능
- ex) ORDER BY 학년 ASC, 가입일 DESC
- 학년으로 오름차순 정렬후 같은 학년 안에서 가입일로 내림차순 정렬 됨

특수 연산자

범위, 부분 일치 여부, 포함 여부 등 RDBMS에서 사용되도록 고안된 연산자

종류	사용 예	의미
BETWEEN	컬럼명 BETWEEN V1 AND V2	컬럼값이 V1~V2 사이에 존재하는지 검사
LIKE	컬럼명 LIKE 'V1%'	V1으로 시작하는 문자열 검사
	컬럼명 LIKE '%V1'	V1으로 끝나는 문자열 검사
	컬럼명 LIKE '%V1%'	V1이 문자열 내부에 존재하는지 검사
	컬럼명 LIKE 'V1_'	V1뒤에 한 문자만 일치하는지 검사
IN	컬럼명 IN (V1, ... Vn)	컬럼값이 V1, ... Vn 중 하나와 일치하는지 검사

사용 예시

-- 나이가 20세 이상 30세 이하인 사람을 조회
SELECT * 
FROM users 
WHERE age BETWEEN 20 AND 30;

-- 이름이 'Kim'으로 시작하는 사람을 조회
SELECT * 
FROM users 
WHERE name LIKE 'Kim%';

-- 지역이 서울, 부산, 대전 중 하나인 사람을 조회
SELECT * 
FROM users 
WHERE city IN ('서울', '부산', '대전');

집합 연산자 (Set Operators)

집합 연산자는 집합 이론에서의 이진 연산 개념에 기반하여, 두 개 이상의 SELECT 결과를 결합하거나 비교할 때 사용
SELECT문의 컬럼 수, 순서, 데이터 타입이 동일해야 함

합집합

UNION - 두 SELECT 결과를 합집합으로 반환, 중복 제거

SELECT name FROM students
UNION
SELECT name FROM teachers;

UNION ALL - 두 SELECT 결과를 모두 반환, 중복 포함

SELECT name FROM students
UNION ALL
SELECT name FROM teachers;

INTERSECT나 EXCEPT는 지원하지 않는 시스템도 있으며 다음과 가이 서브쿼리나 JOIN절로 우회사용

-- students와 teachers에 모두 존재하는 이름 조회 (교집합)
SELECT name 
FROM students
WHERE name IN (
    SELECT name FROM teachers
);

SELECT s.name 
FROM students s
INNER JOIN teachers t ON s.name = t.name;


-- students에는 있지만 teachers에는 없는 이름 조회 (차집합)
SELECT name 
FROM students
WHERE name NOT IN (
    SELECT name FROM teachers
);

SELECT s.name
FROM students s
LEFT JOIN teachers t ON s.name = t.name
WHERE t.name IS NULL;

함수의 사용

특정 목적을 수행하도록 사전에 정의된 연산 및 기능을 수행한 후 결과값을 반환하는 명령어 집합
고도의 데이터 조작을 지원하고 SQL문 작성의 편의성 유지

숫자 함수

숫자 데이터 타입에 적용할 수 있는 계산 및 수학 연산 함수

함수명	설명
ABS(n)	절댓값
CEIL(n)	올림값 (Ceiling)
FLOOR(n)	내림값
ROUND(n)	반올림
TRUNCATE(n, d)	소수점 d자리까지 자르기
MOD(a, b)	나머지 값 (a % b)
POWER(a, b)	a의 b 제곱
RAND()	0 이상 1 미만의 랜덤 실수 반환

-- 절댓값 구하기
SELECT ABS(-15);  -- 결과: 15

-- 소수점 둘째 자리에서 반올림
SELECT ROUND(3.14159, 2);  -- 결과: 3.14

-- 소수점 한 자리까지 자르기
SELECT TRUNCATE(9.876, 1);  -- 결과: 9.8

-- 올림값
SELECT CEIL(2.3);  -- 결과: 3

-- 나머지 계산
SELECT MOD(10, 3);  -- 결과: 1

문자 함수

문자열을 처리, 변환, 검색하기 위한 함수

함수명	설명
LENGTH(str)	바이트 단위 문자열 길이
CHAR_LENGTH(str)	문자 수 기준 길이
CONCAT(a, b)	문자열 연결
UPPER(str)	대문자로 변환
LOWER(str)	소문자로 변환
SUBSTRING(str, start, len)	부분 문자열 추출
REPLACE(str, from, to)	문자열 치환
TRIM(str)	앞뒤 공백 제거
LEFT(str, n)	왼쪽 n글자 추출
RIGHT(str, n)	오른쪽 n글자 추출

-- 문자열 길이
SELECT CHAR_LENGTH('Hello');  -- 결과: 5

-- 문자열 연결
SELECT CONCAT('My', 'SQL');  -- 결과: MySQL

-- 부분 문자열 추출
SELECT SUBSTRING('Database', 5, 3);  -- 결과: bas

-- 문자열 대체
SELECT REPLACE('2025-03-31', '-', '/');  -- 결과: 2025/03/31

-- 대소문자 변환
SELECT UPPER('abc');  -- 결과: ABC

날짜함수

날짜/시간 데이터를 계산하거나 포맷하기 위한 함수

함수명	설명
NOW()	현재 날짜와 시간 반환
CURDATE()	현재 날짜만 반환
CURTIME()	현재 시간만 반환
DATE_FORMAT(dt, fmt)	날짜를 형식에 맞게 포맷팅
DATEDIFF(d1, d2)	날짜 차이 (d1 - d2, 일 단위)
ADDDATE(dt, n)	날짜에 n일 더하기
SUBDATE(dt, n)	날짜에서 n일 빼기
YEAR(dt), MONTH(dt), DAY(dt)	연, 월, 일 추출
STR_TO_DATE(str, fmt)	문자열을 날짜로 변환

-- 현재 날짜와 시간
SELECT NOW();  -- 결과: 2025-03-31 14:30:00 (예시)

-- 날짜 차이 구하기
SELECT DATEDIFF('2025-04-10', '2025-03-31');  -- 결과: 10

-- 날짜 형식 변경
SELECT DATE_FORMAT(NOW(), '%Y년 %m월 %d일');  -- 결과: 2025년 03월 31일

-- 날짜 더하기
SELECT ADDDATE('2025-03-31', 5);  -- 결과: 2025-04-05

-- 연도 추출
SELECT YEAR('2025-03-31');  -- 결과: 2025

집계 함수의 사용

특정 컬럼에 집계함수를 통해 다양한 통계 연산을 수행할수 있는 기능
SELECT 절 또는 HAVING 절에 기술

집계 함수 종류

함수명	설명
COUNT()	행의 개수 계산
SUM()	값의 합계
AVG()	평균값 계산
MAX()	최대값
MIN()	최소값

-- 각 고객별 총 주문 금액
SELECT customer_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id;

-- 각 카테고리별 평균 가격
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category;


-- 총 주문 금액이 100만 원 이상인 고객만 조회
SELECT customer_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING total_amount >= 1000000;

WHERE은 집계 전 조건 필터링, HAVING은 집계 후 조건 필터링에 사용

중첩 질의 (Sub Query)

SELECT 문 내부에서 독립적으로 실행가능한 또 다른 SELECT 문이 내포되어 있는 질의
단일 행, 다중 행, 다중 열 결과 모두 가능
FROM, WHERE, SELECT, HAVING 등 여러 곳에 사용 가능

FROM 절(Inline View)

-- 각 부서별 평균 급여를 구한 뒤, 평균 급여가 300 이상인 부서만 조회
SELECT dept_avg.dept_id, dept_avg.avg_salary
FROM (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
) AS dept_avg
WHERE dept_avg.avg_salary >= 300;

WHERE 절

-- '서울'에 근무하는 부서의 직원 조회
SELECT name 
FROM employees 
WHERE dept_id IN (
    SELECT id 
    FROM departments 
    WHERE location = '서울'
);

SELECT 절 (Scalar SubQuery)

SELECT 항목안에 서브쿼리가 들어가서 1개의 값을 반환

SELECT 
    name,
    (SELECT dept_name FROM departments WHERE departments.id = employees.dept_id) AS department
FROM employees;

EXISTS / NOT EXISTS

상관 서브쿼리로 내부 쿼리에서 외부 쿼리를 참조
매 행마다 서브쿼리가 실행되므로 성능 주의 필요
- 행마다 조건이 달라야 할 경우가 아니면 비상관 쿼리 사용하는게 좋음

-- 직원이 없는 부서만 조회
SELECT d.dept_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1 
    FROM employees e 
    WHERE e.dept_id = d.id
);

-- employees의 각 행마다 해당 부서가 존재하는지 확인하기 위해 서브쿼리가 반복 실행됨.

조인 질의

여러 테이블을 연결(join) 해서 하나의 결과로 조회하는 방식
보통 공통 컬럼(primary key / foreign key) 을 기준으로 연결

내부 조인(INNER JOIN)

두 테이블 간 매칭되는 행만 결과로 출력됨
즉, 조건을 만족하는 교집합만 조회

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

자연조인(NATURAL JOIN)

두 테이블에서 동일한 이름을 가진 컬럼을 기준으로 자동 조인
ON 조건 없이 사용
실무에서는 명시적 조건이 없어 예측이 어렵고 잘 안 쓰이는 편

외부 조인

조인 조건을 만족하지 않아도, 한쪽 테이블의 모든 행을 포함
방향에 따라 3가지가 있음:

종류	방식
LEFT OUTER JOIN	왼쪽 테이블의 모든 행 + 오른쪽 매칭된 행
RIGHT OUTER JOIN	오른쪽 테이블의 모든 행 + 왼쪽 매칭된 행
FULL OUTER JOIN	양쪽 모두 포함 (UNION으로 가능)

셀프 조인(SELF JOIN)

자기 자신과 조인하는 방식
같은 테이블에서 계층적 구조나 관계를 표현할 때 사용 (예: 직원-상사)

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

뷰(view)

실제 데이터를 저장하지 않고, 하나 이상의 테이블을 조합한 결과를 가상의 테이블 (virtual table)처럼 보여주는 객체

항목	설명
독립성	원본 테이블 구조가 바뀌어도 뷰를 통해 접근하는 코드는 유지될 수 있음
보안	민감한 정보(급여 등)를 제외한 컬럼만 노출 가능
다양한 구조	복잡한 조인 결과나 계산 컬럼을 뷰로 미리 정의해 둘 수 있음
작업 단순화	복잡한 쿼리를 매번 쓰지 않고 뷰로 재사용 가능
데이터 무결성	비즈니스 규칙을 반영한 뷰로 잘못된 직접 접근 방지

생성, 수정, 삭제

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

-- 기존 뷰를 덮어씀
CREATE OR REPLACE VIEW view_name AS
SELECT ...
FROM ...;

DROP VIEW view_name;

뷰를 통한 데이터 삽입

뷰에 대한 INSERT 문은 원본 테이블에서 실행됨
INSERT 문 실행 불가능한 경우
- PK, NOT NULL등 제약사항이 위배되는 경우
- 원본 테이블에 존재하는 컬럼이지만 뷰에는 없는 컬럼에 삽입하는 경우
- JOIN, GROUP BY, DISTINCT가 적용된 경우
- WITH CHECK OPTION이 적용된 경우 뷰에 맞지 않는 조건일 경우

저작자표시