[SQLD] SQL 기본 - 2

💡 SQLD 자격증 시험 대비 학습한 내용을 요약정리합니다.

WHERE 조건절

WHERE 절을 사용하지 않고 필요 없는 많은 자료들을 DB로부터 요청하게 되면 CPU, 메모리와 같은 시스템 자원을 과다하게 사용하게 된다.

WHERE 절은 조회하려는 데이터의 특정 조건을 부여할 목적으로 사용하기 때문에 FROM 절 뒤에 온다.

SELECT 컬럼명 FROM 테이블명 WHERE 조건식;

조건식의 구성

• 컬럼명, 비교연산자, 문자, 숫자, 표현식, 비교 컬렴명

연산자의 종류

1. 비교 연산자 : =, >, >=, <, <=
2. SQL 연산자 : BETWEEN a AND b, IN (list), LIKE 문자열, IS NULL
3. 논리 연산자 : AND, OR, NOT
4. 부정 비교 연산자 : !=, ^=, <>, NOT 컬럼명 =, NOT 컬럼명 >
5. 부정 SQL 연산자 : NOT BETWEEN a AND b, NOT IN (list), IS NOT NULL

연산자 우선순위

괄호() -> NOT 연산자 -> 비교, SQL 비교 연산자 ->AND ->OR

ROWNUM(오라클)

컬럼과 비슷한 성격의 Pseudo Column, 처리 결과 집합의 각 행에 대해 임시로 부여되는 일련번호이며, WHERE 절에서 행의 개수를 제한하는 목적으로 사용한다.

SELECT 컬럼명 FROM 테이블명 WHERE ROWNUM <= n;

TOP절 : 결과 집합으로 출력되는 행의 수를 제한(SQL Server)

SELECT TOP(n) 컬럼명 FROM 테이블명;

---

함수(Function)

내장 함수 : 벤더에서 제공하는 함수
사용자 정의 함수 : 사용자가 정의하는 함수

내장 함수

• 단일행 함수 : 입력 값이 단일행 값이 입력
• 다중행 함수 : 여러 행의 값이 입력

💡함수 = 입력값 : 출력값 = m : 1

단일행 함수의 종류(오라클/SQL Server)

1. 문자형 함수

• LOWER : 알파벳 대 -> 소
• UPPER : 알파벳 소 -> 대
• ASCII : 문자, 숫자 -> ASCII 코드
• CHA/CHAR : ASCII 코드 -> 문자, 숫자
• CONCAT : 문자열을 연결
  CONCAT(‘abc, ‘def); => ‘abcdef’
• SUBSTR/SUBSTRING : m부터 n개의 문자를 반환
  SUBSTR(‘012345’, 2, 3); => ‘234’
• LENGTH/LEN : 문자열의 길이 반환
• LTRIM : 문자열에 첫 문자부터 시작해 지정문자를 삭제
  LTRIM(‘study spring’, ‘s’); => ‘tudy spring’’
• RTRIM : 문자열에 뒷 문자부터 시작해 지정문자 삭제
  RTRIM(‘study spring’, ‘s’); => ‘study pring’
• TRIM : 문자열에서 머리말, 꼬리말, 양쪽 지정문자를 제거
  TRIM(‘a’, FROM ‘aaBBccDDBaDaa’); => ‘BBccDDBaD’

2. 숫자형 함수

• ABS : 숫자의 절대값
• SIGN : 숫자가 양수, 음수, 0 구별
• MOD : 수1을 수2로 나누어 나머지 값을 반환(% 대체가능)
  MOD(7, 3) = 7 % 3
• CEIL/CEILING : 소수점 올림
• FLOOR : 소수점 내림
• ROUND : 숫자를 소수점 m자리에서 반올림
  ROUND(38.5235, 3) = 38.524
• TRUNC : 숫자를 소수 m자리에서 잘라서 버림
  TRUNC(38.5235, 3) = 38.523
• SIN, COS, TAN : 숫자의 삼각함수 값을 반환
• EXP(지수), POWER(거듭제곱), SQRT(제곱근), LOG(로그), LN(자연로그)

3. 날짜형 함수

• SYSDATE/GETDATE() : 현재 날짜와 시간을 출력
• EXTRACT/DATEPART : 년/월/일 데이터 출력
• TO_NUMBER(TO_CHAR(d, ‘YYYY’))/ YEAR(d)
• TO_NUMBER(TO_CHAR(d, ‘MM’))/ MONTH(d)
• TO_NUMBER(TO_CHAR(d, ‘DD’))/ DAY(d)

4. 변환형 함수

특정 데이터 타입을 다양한 형식으로 출력하고 싶을 경우 사용

• 명시적 데이터 유형 반환 : 직접 변환 함수를 사용
• 암시적 데이터 유형 반환 : 자동으로 DB가 변환해줌

명시적 데이터 유형 반환의 함수

오라클

• TO_NUMBER(문자열) : 문자열 -> 숫자
• TO_CHAR(숫자|날짜 [, FORMAT]) : 숫자나 날짜를 주어진 FORMAT 형태로 문자열 타입으로 변환
• TO_DATE(문자열 [, FORMAT]) : 문자열 -> 날짜타입

SQL Server

• CAST(expression AS data_type [(length)])
• CONVERT(data_type [(length)], expression [, style ])

CASE 표현

IF-THEN-ELSE 논리와 유사한 방식으로 표현식을 작성해 SQL의 비교 연산 기능을 보완하는 역할을 한다.

CASE SIMPLE(SEARCHED)_CASE_EXPRESSION 조건 ELSE 표현절 END;
SIMPLE(SEARCHED)_CASE_EXPRESSION 조건이 맞으면 해당 조건 내의 THEN절을 수행하고 맞지 않으면 ELSE절을 수행한다.

DECODE(표현식, 기준값1, 값1 [, 기준값2, 값2, …, 디폴트값)

오라클에서만 사용하는 함수로, 표현식의 값이 기준값1이면 값1을 출력하고, 기준값2면 값2를 출력한다. 만약 기준값이 없으면 디폴트값을 출력한다.

NULL 관련 함수

• NVL(식1,식2)/ISNULL(식1,식2) : 식1의 값이 NULL 이면 식2 출력
• NULLIF(식1,식2) : 식1이 식2와 같으면 NULL을 아니면 식1을 출력
• COALESCE(식1,식2) : 임의의 개수표현식에서 NULL이 아닌 최초의 표현식, 모두 NULL이면 NULL 반환ex)COALESCE(NULL,NULL,‘abc’) -> ‘abc

---

GROUP BY, HAVING 절

집계 함수

• 다중행 함수 중 하나로 여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수
• GROUP BY절은 행들을 소그룹화 한다.
• SELECT절, HAVING절, ORDER BY 절에 사용할 수 있다.

집계 함수의 종류

• COUNT(*) : NULL 포함 행의 수
• COUNT(표현식) : NULL 제외 행의 수
• SUM, AVG : NULL 제외 합계, 평균 연산
• STDDEV : 표준 편차
• VARIAN : 분산
• MAX, MIN : 최대값, 최소값

GROUP BY절

데이터들을 작은은 그룹으로 분류하여 소그룹에 대한 항목별로 통계 정보를 얻을 때 추가 사용된다.

HAVING 절

WHERE절과 비슷하지만 그룹을 나태는 결과 집합의 행에 조건이 적용된다는 점에서 차이가 있다.

SELECT 컬럼명 FROM 테이블명 [WHERE 조건식] [GROUP BY 컬럼이나 표현식] [HAVING 그룹조건식];

SELECT position 포지션 FROM player GROUP BY position HAVING AVG(height) >= 180;

GROUP BY, HAVING 절의 특징

1. GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용한다.
2. 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행한다.
3. GROUP BY 절에서는 ALIAS 사용 불가
4. 집계 함수는 WHERE 절에 올 수 없다.(GROUP BY절보다 WERER절이 먼저 수행된다)
5. HAVING 절에는 집계함수를 이용하여 조건 표시o
6. HAVING 절은 일반적으로 GROUP BY 뒤에 위치

GROUP BY 소그룹의 데이터 중 일부만 추출하는 방법

1. GROUP BY 연산 전 WHERE 절에서 조건을 적용하여 필요한 데이터만 추출하여 GROUP BY 연산을 하는 방법
2. GROUP BY 연산 후 HAVING 절에서 필요한 데이터만 필터링

가능하면 1번 방법을 사용하는 것이 자원 사용 측면에서 효율적이다.

---

ORDER BY 절

: SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정 컬럼을 기준으로 정렬하여 출력하는데 사용.

ORDER BY 절의 특징

• ALIAS 명이나 컬럼 순서를 나타내는 정수 사용 가능
  (유지보수성, 가독성을 고려하여 컬럼명, ALIAS명 사용 권고)
• 기본적으로 오름차순 적용
• SQL 문장의 제일 마지막에 위치치

SELECT 컬럼명 FROM 테이블명 [WHERE 조건식] [GROUP BY 컬럼이나 표현식] [HAVING 그룹조건식] [ORDER BY 컬럼이나 표현식[ASC or DESC]];

• ASC(Ascending) : 오름차순(기본값, 생략가능)
• DESC(Descending) : 내림차순

💡 Null의 취급
오라클 : null 값을 가장 큰 값으로 취급
SQL Server : null 값을 가장 작은 값으로 취급

SELECT 문장 실행 순서

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

관계형 데이터베이스가 데이터를 메모리에 올릴 때 행 단위로 모든 컬럼을 가져오게 되므로, SELECT 절에서 일부 컬럼만 선택하더라도 ORDER BY 절에서 메모리에 올라와 있는 다른 컬럼의 데이터를 사용할 수 있다.

SELECT 컬럼1, 컬럼2 FROM 테이블명 ORDER BY 컬럼3;

---

조인(JOIN)

: 두 개 이상의 테이블들을 연결 또는 결합하여 데이터를 출력하는 것. 일반적으로 행들은 PK, FK 값의 연관에 의해 JOIN이 성립되지만 어떤 경우에는 PK, FK 관계가 없어도 논리적인 값들의 연관만으로 성립 가능!

관계형 데이터베이스에서 가장 큰 장점이자 대표 핵심 기능!

🚨 주의할 점!
FROM 절에 여러 테이블이 나열되더라도 SQL에서 데이터를 처리할 때는 단 두 개의 집합 간에만 조인이 일어난다.
JOIN에 참여하는 대상 테이블 : N개
필요한 JOIN의 조건 : N-1개 이상

JOIN의 조건은 WHERE 절에 기술한다.

EQUI JOIN(등가 조인, ‘=’)

• 두 개의 테이블 간에 컬럼 값들이 서로 정확하게 일치하는 경우 사용되는 방법. 대부분 PK, FK의 관계를 기반으로 한다.

SELECT 테이블1.컬럼명, 테이블2.컬럼명 FROM 테이블1, 테이블2 WHERE 테이블1.컬럼명 = 테이블2.컬럼명;

SELECT 테이블1.컬럼명, 테이블2.컬럼명 FROM 테이블1 INNER JOIN 테이블2 ON 테이블1.컬럼명 = 테이블2.컬럼명;

• FROM절의 테이블에 대해서 ALIAS를 사용 할 수 있다.
• 가독성을 위해 테이블명 대신 ALIAS 사용을 권고.

Non EQUI JOIN(비등가 조인)

• 두 개의 테이블 간에 컬럼 값들이 서로 정확하게 일치하지 않는 경우 사용.
• BETWEEN, >, >=, <, <= 등의 연산자를 사용

SELECT 테이블1.컬럼명, 테이블2.컬럼명 FROM 테이블1, 테이블2 WHERE 테이블1.컬럼명1 BETWEEN 테이블2.컬럼명1 AND 테이블2.컬럼명2;