SQLD 2-1 요약

yoon·2023년 9월 2일
SQLD요약

SQLD

목록 보기
3/7
post-thumbnail

정미나, "유선배 SQL 개발자(SQLD) 과외노트", 시대고시기획
해당 책을 바탕으로 작성하였습니다.

그냥 개인적으로 다시 보고 공부하려고 만들었습니다.😀

관계형 데이터베이스 개요

RDBMS(Relational Database Management System)
SQL(Structured Query Language)

테이블에서

  • Row, 인스턴스, 가로
  • Column, 속성, 세로

SELECT문

SELECT

저장되어 있는 데이터를 조회하고자 할 때 사용하는 명령어.

Alias(별칭) 사용 가능

  • SELECT TAB.COL FROM TAB T WHERE COL = 'A'; => 오류

왜? 테이블명에 Alias를 설정했을 경우 테이블명 대신 Alias를 사용해야 함.

합성 연산자

문자와 문자를 연결할 때 사용하는 연산자.

  • SELECT 'S'||'Q'||'L'||'D'; => 'SQLD'

함수

문자 함수

CHR(ASCII 코드)

해당 ASCII 코드에 매핑되는 문자 반환.

  • SELECT CHR(65); => 'A'

LOWER(문자열)

문자열을 소문자로 반환.

  • SELECT LOWER('HI'); => 'hi'

UPPER(문자열)

문자열을 대문자로 반환.

  • SELECT UPPER('hi'); => 'HI'

LTRIM(문자열 [, 특정문자열])

옵션 X : 왼쪽 공백 제거.
옵션 O : 문자열을 왼쪽부터 한 글자씩 비교하여 특정문자에 포함되어 있으면 제거.

  • SELECT LTRIM('　　　hi'); => 'hi'

  • SELECT LTRIM('DATABASE', 'DE'); => 'ATABASE'

  • SELECT LTRIM('　　　hi', 'h'); => '　　　hi'

'DE'가 동시에 있는 문자열을 지우는 게 아니라 'D', 'E' 따로 진행한다.
즉 왼쪽부터 'D' 일치 시 삭제, 불일치 시 'E' 검사.
이렇게 계속 'D'와 'E'를 각각 검사하다가 한번이라도 'D'나 'E' 둘 다 아니면 멈춤.
띄어쓰기도 포함해서 검사하기 때문에 주의.

RTRIM(문자열 [, 특정문자열])

옵션 X : 오른쪽 공백 제거.
옵션 O : 문자열을 오른쪽부터 한 글자씩 비교하여 특정문자에 포함되어 있으면 제거.

  • SELECT LTRIM('hi　　　'); => 'hi'

TRIM([위치][특정문자][FROM] 문자열)

옵션 X : 문자열의 양쪽 공백 제거
옵션 O : 위치로 지정된 곳부터 한 글자씩 특정 문자와 비교하여 같으면 제거.

LTRIM, RTRIM과 달리 특정 문자는 한 글자만 가능.

  • SELECT TRIM('　　hi　　'); => 'hi'

[위치]는 세 가지
1. LEADING : 좌측 공백 혹은 특정문자 제거. (LTRIM과 동일)
2. TRAILNG : 우측 공백 혹은 특정문자 제거. (RTRIM과 동일)
3. BOTH(default) : 좌우 공백 혹은 특정문자 제거.

SUBSTR(문자열, 시작점 [, 길이])

옵션 X : 문자열의 시작점부터 끝까지 반환.
옵션 O : 문자열의 시작점부터 길이만큼 반환.

  • SELECT SUBSTR('hello', 2, 2); => 'el'

  • SELECT SUBSTR('hello', -5, 3); => 'hel'

LENGTH(문자열)

문자열의 길이 반환. 띄어쓰기 포함.

  • SELECT LENGTH(' hi'); => 3

CONCAT(문자열1, 문자열2)

문자열을 합쳐서 반환.

  • SELECT CONCAT('hello', 'world'); => 'helloworld'

CHAR, VARCHAR 차이?
CHAR은 고정길이를 가지고 있는 문자열 데이터.
VARCHAR은 가변길이를 가지고 있는 문자열 데이터.
CHAR 사용 시 부족하면 공백으로 채워짐.

숫자 함수

ABS(수)

수의 절대값을 반환.

SIGN(수)

수의 부호를 반환. 양수면 1, 음수면 -1, 0이면 0 반환.

ROUND(수 [, 자릿수])

수를 지정된 소수점 자릿수까지 반올림하여 반환.
기본 옵션값 0. 반올림된 정수로 반환.
자릿수가 음수일 경우 지정된 정수부를 반올림하여 반환.

  • SELECT ROUND(163.76, 1); => 163.8

  • SELECT ROUND(163.76, -2); => 200

  • SELECT ROUND(-13.5); => -14

자릿수까지 표시라고 생각.

TRUNC(수 [, 자릿수])

수를 지정된 소수점 자릿수까지 버림하여 반환.
기본 옵션값 0. 버림된 정수로 반환.
자릿수가 음수일 경우 지정된 정수부에서 버림하여 반환.

  • SELECT TRUNC(54.29, 1); => 54.2

  • SELECT TRUNC(54.29, -1); => 50

CEIL(수)

소수점 이하의 수를 올림한 정수를 반환.

  • SELECT CEIL(72.86); => 73

  • SELECT CEIL(-33.4); => -33

수를 '올림' => 큰 수가 됨.
즉 음수일 경우 소수부를 버리는 것과 같음.

FLOOR(수)

소수점 이하의 수를 버림한 정수를 반환.

  • SELECT FLORR(22.3); => 22

  • SELECT FLOOR(-22.3); => -23

수를 '버림' => 작은 수가 됨.
즉 음수일 경우 더 작은 수가 됨.

MOD(수1, 수2)

수1을 수2로 나눈 나머지를 반환.

  • SELECT MOD(15, 7); => 1

  • SELECT MOD(15, -4); => 3

  • SELECT MOD(-15, 4); => -3

  • SELECT MOD(-15, -4); => -3

  • SELECT MOD(-15, 0); => -15

수2가 0이면 수1을 그대로 반환.
수 1만 음수이거나 수1, 수2가 모두 음수면 나머지도 음수.

날짜 함수

SYSDATE

현재의 연, 월, 일, 시, 분, 초를 반환.

출력 형식은 달라질 수 있음.

EXTRACT(특정 단위 FROM 날짜 데이터)

날짜 데이터에서 특정 단위(YEAR, MONTH, DAY, HOUR, MINUTE, SECOND)만을 출력해서 반환.

  • SELECT EXTRACT(YEAR FROM SYSDATE); => 2023

ADD_MONTHS(날짜 데이터, 특정 개월수)

날짜 데이터에서 특정 개월 수를 더한 날짜를 반환.

  • SELECT ADD_MONTHS(DATE '2021-01-31', 1); => 2023-02-28

출력 형식은 달라질 수 있음.
해당 날짜가 존재하지 않으면 반환하는 월의 마지막 일자가 반환됨.

명시적 형변환 함수

  • 명시적인 형변환 : 변환 함수를 사용하여 데이터 유형 변환을 명시적으로 나타냄.
  • 암시적인 형변환 : 데이터베이스가 내부적으로 알아서 데이터 유형을 변환함.

암시적인 형변환이 가능하다고 해서 컬럼의 데이터 유형을 고려하지 않고 SQL 작성 시 성능 저하 및 에러 가능하기 때문에 되도록 명시적 형변환을 사용.

TO_NUMBER(문자열)

문자열을 숫자형으로 변환해서 반환.

  • SELECT TO_NUMBER('1234'); => 1234

  • SELECT TO_NUMBER('abc'); => error!

TO_CHAR(수 or 날짜 [, 포맷])

수나 날짜형의 데이터를 포맷 형식의 문자형으로 반환.

  • SELECT TO_CHAR(1234); => '1234'

  • SELECT TO_CHAR(SYSDATE, 'YYYYMMDD HH24MISS'); => 20230818 185312

YYYY : 년
MM : 월
DD : 일
HH : 시(12)
HH24 : 시(24)
MI : (분)
SS : (초)

TO_DATE(문자열, 포맷)

포맷 형식의 문자형의 데이터를 날짜형으로 변환해서 반환.

  • SELECT TO_DATE('20230818', 'YYYYMMDD'); => 2023-08-18

NULL 관련 함수

NVL(인수1, 인수2)

인수1의 값이 NULL일 경우 인수2 반환, NULL이 아닐 경우 인수1을 반환.

MSSQL의 경우 ISNULL(인수1, 인수2)

NULLIF(인수1, 인수2)

인수1과 인수2가 같으면 NULL 반환, 같지 않으면 인수1을 반환.

COALESCE(인수1, 인수2, 인수3...)

NULL이 아닌 최초의 인수를 반환.

CASE

함수라기 보다는 구문임.

  • SELECT * FROM smp2

select LINE,
 case when LINE = 1 then 'blue'
  when LINE = 2 then 'green'
  when LINE = 3 then 'orange'
  else 'gray'
 end as result
from smp2

만약 else문이 없다면?
null 반환.

DECODE

  • DECODE(COL1, 'A', 'a', 'B', 'b', 'c');
    COL1이 'A'면 'a' 반환, 'B'면 'b' 반환, 다 아니면 'c' 반환.

  • DECODE(COL1, 'A', 'a', 'B', 'b');
    default 값이 없으면 일치하는 값이 없을 때 NULL 반환.

  • DECODE(COL1, 'A', 'a', 'B', 'b', '');
    이 경우에는 공백('')이 반환.

EXISTS, NOT EXISTS

EXISTS(서브 쿼리)

서브 쿼리의 결과가 한 건이라도 존재하면 TRUE, 없으면 FALSE 반환.
한 건이라도 있으면 쿼리를 더 이상 수행하지 않음.
NOT EXISTS는 반대.

  • select * from smp a where exsists(select 1 from smp2 b where ~)

WHERE절

원하는 데이터만 골라 수행할 수 있도록 해주는 구문.

비교 연산자

연산자의미
=같음
<작음
>큼
<=작거나 같음
>=크거나 같음

비교하는 데이터 타입이 맞지 않으면 에러.

부정 비교 연산자

연산자의미
!=같지 않음
^=같지 않음
<>같지 않음
not 컬럼명 =같지 않음
not 컬럼명 >크지 않음

조건식에서 컬럼명은 좌우 상관 없음.

SQL 연산자

연산자의미
BETWEEN A AND BA와 B 사이(A, B 포함)
LIKE '비교 문자열'비교 문자열을 포함(% 사용)
IN (LIST)LIST 중 하나와 일치
IS NULLNULL 값

비교 문자열에 #, escape을 사용하면 해당 기호 사용 가능.

  • select * from smp where col1 like '%#%%' escape '#';
    => '%'가 포함된 문자열을 찾음.

부정 SQL 연산자

연산자의미
NOT BETWEEN A AND BA와 B 사이가 아님(A, B 미포함)
NOT IN (LIST)LIST 중 일치하는 것이 없음
IS NOT NULLNULL 값이 아님

논리 연산자

연산자의미
AND모든 조건이 TRUE여야 함
OR하나 이상의 조건이 TRUE여야 함
NOTTRUE면 FALSE, FALSE면 TRUE

논리 연산자의 처리 순서는 () -> NOT -> AND -> OR.
즉 아래 두 쿼리는 같은 것.

  • select * from smp where COL1 < 100 OR COL2 = 100 AND COL3 = 'A'
  • select * from smp where COL1 < 100 OR (COL2 = 100 AND COL3 = 'A')

NULL이 아닌 값을 찾을 때는 IS NOT NULL만 사용.
컬럼명 <> NULL, 컬럼명 != NULL, NOT 컬럼명 = NULL 등은 안 됨.

GROUP BY, HAVING 절

GROUP BY

데이터를 그룹별로 묶을 수 있도록 해주는 절. 뒤에는 그룹핑의 기준이 되는 컬럼이 옴. 컬럼은 하나 또는 그 이상 가능.

집계 함수

데이터를 그룹별로 나누면 그룹별로 집계 데이터를 도출하는 것이 가능.

집계 함수의미
COUNT(*)전체 Row를 카운트하여 반환
COUNT(컬럼)컬럼값이 NULL인 Row를 제외하고 카운트하여 반환
COUNT(DISTINCT 컬럼)컬럼값이 NULL이 아닌 Row에서 중복을 제거한 카운트 반환
SUM(컬럼)컬럼값들의 합계를 반환
AVG(컬럼)컬럼값들의 평균을 반환
MIN(컬럼)컬럼값들의 최솟값을 반환
MAX(컬럼)컬럼값들의 최댓값을 반환

MAX, MIN, AVG, SUM은 NULL을 포함하지 않고 계산.

HAVING

GROUP BY 절을 사용할 때 WHERE처럼 사용하는 조건절.
주로 데이터를 그룹핑한 후 특정 그룹을 골라낼 때 사용.

HAVING 절은 주로 GROUP BY 절 뒤에 오면서 집계 데이터에 대한 조건을 부여하지만 테이블 전체가 한 개의 그룹이 되는 경우 HAVING만 단독으로 사용 가능.

SELECT문의 논리적 수행 순서

⑤ : SELECT
① : FROM
② : WHERE
③ : GROUP BY
④ : HAVING
⑥ : ORDER BY

논리적으로 HAVING절은 SELECT 절보다 먼저 수행되기 때문에 HAVING 절에서 SELECT 절에서 정의한 ALIAS를 사용할 수 없음.

ORDER BY 절

SELECT한 데이터를 정렬. 따로 명시하지 않으면 임의의 순서대로 출력.
기준이 되는 컬럼은 하나 또는 그 이상 가능.

옵션

  • ASC(Ascending) : 오름차순(default)
  • DESC(Descending) : 내림차순

정렬의 기준이 되는 컬럼에 NULL 데이터가 포함되어 있을 경우 DB 종류에 따라 정렬의 위치가 달라짐.
ORACLE의 경우 NULL이 최댓값.
순서를 변경하고 싶다면 ORDER BY 절에 NULLS FIRST, NULLS LAST 옵션 사용 가능.

order by 1, 2 이런 쿼리의 뜻은 select 절에 명시한 1, 2번재 컬럼 순으로 오름차순 정렬.

JOIN

EQUI JOIN

Equal(=) 조건으로 JOIN하는 것.

  • SELECT A.COL1, A.COL2, B.COL1
    FROM SMP1 A, SMP2 B
    WHERE A.COL1 = B.COL1

Non EQUI JOIN

Equla(=) 조건이 아닌 다른 조건(BETWEEN, <, <= >, >=)으로 JOIN하는 것.

  • SELECT A.COL1, B.COL1, B.COL2
    FROM SMP1 A, SMP2 B
    WHERE B.COL2 BETWEEN A.COL2 AND A.COL3

3개 이상 TABLE JOIN

  • SELECT A.COL1, B.COL1, C.COL1
    FROM SMP1 A, SMP2 B, SPM3 C
    WHERE A.COL2 = B.COL2
    AND B.COL3 BETWEEN C.COL2 AND C.COL3

EQUI JOIN과 Non EQUI JOIN은 하나의 쿼리에서 같이 사용 가능.

  • SELECT COL1, COL3
    FROM SMP1 A, SMP2 B
    WHERE A.COL1 = B.COL1;
    => 오류!
    두 테이블 SMP1과 SMP2에 모두 COL1이 존재하므로 해당 컬럼명 앞에 테이블 명 또는 ALIAS를 명시해야 함.

OUTER JOIN

앞서 본 JOIN과 다르게 JOIN 조건에 만족하지 않는 행들도 출력됨.

  • SELECT A.COL1, B.COL2
    FROM SMP1 A, SMP2 B
    WHERE A.COL1 = B.COL1(+)

이렇게 (+)를 작성하면 반대편 테이블의 행이 모두 출력됨.
위의 경우 SMP1의 행이 모두 출력되고 B의 빈 부분은 NULL로 출력.

STANDARD JOIN

많은 RDBMS가 있는데 이 RDBMS마다 SQL 문법 차이가 너무 클 경우 호환성 이슈가 발생하고 사용자들 입장에서도 불편하기 때문에 표준이 되는 ANSI SQL을 지정함. 즉 STANDARD JOIN, ANSI JOIN, 표준 조인 모두 같은 말.

INNER JOIN

JOIN 조건에 충족하는 데이터만 출력.

ON 절을 사용해야 함.

  • SELECT A.COL1, B.COL2
    FROM SMP1 A INNER JOIN SMP2 B
    ON A.COL1 = B.COL1;
  • SELECT A.COL1, B.COL2
    FROM SMP1 A, SMP2 B
    WHERE A.COL1 = B.COL1;
  • SELECT B.COL1, B.COL2
    FROM SMP1 A, SMP2 B
    WHERE A.COL1 = B.COL1;
    두 SQL은 같은 결과를 반환.
    조건이 A.COL1 = B.COL1이기 때문에 A.COL1을 SELECT하든 B.COL1을 SELECT하든 같기 때문.

OUTER JOIN

JOIN 조건에 충족하는 데이터가 아니어도 출력.

LEFT OUTER JOIN


데이터가 없는 우측 테이블 값은 NULL로 출력.

  • SELECT A.COL1, B.COL2
    FROM SMP1 A LEFT OUTER JOIN SMP2 B
    ON A.COL1 = B.COL1

RIGHT OUTER JOIN


데이터가 없는 좌측 테이블 값은 NULL로 출력.

  • SELECT A.COL1, B.COL2
    FROM SMP1 A RIGHT OUTER JOIN SMP2 B
    ON A.COL1 = B.COL1

FULL OUTER JOIN

  • SELECT A.COL1, B.COL2
    FROM SMP1 A FULL OUTER JOIN SMP2 B
    ON A.COL1 = B.COL1

NATURAL JOIN

A 테이블과 B 테이블에서 같은 이름을 가진 컬럼들이 모두 동일한 데이터를 가지고 있을 경우 JOIN.
즉 컬럼명, 데이터가 모두 같은 경우만 출력.

  • SELECT * FROM SMP1 A NATURAL JOIN SMP2 B

ORACLE의 경우 USING 절을 이용하여 원하는 컬럼만 JOIN에 이용 가능.

  • SELECT * FROM SMP1 A NATURAL JOIN SMP2 B
    USING(COL1, COL2)

=> COL1, COL2의 내용만 같으면 JOIN.

단, SELECT 절에서 USING 절로 정의된 컬럼 앞에는 별도의 ALIAS나 테이블명을 붙이면 안 됨.
=> 아래 COL1, COL2에 ALIAS나 테이블명 불가.

  • select COL1, COL2, A.COL3, B.COL3
    from smp1 A natural join smp2 B
    using(COL1, COL2);

공통 컬럼 앞에 ALIAS나 테이블명 또한 불가.

  • select COL1, COL2
    from smp1 A natural join smp2 B

CROSS JOIN

JOIN 조건이 없는 경우 조합할 수 있는 모든 경우를 출력하는 방식.
Cartesian Product라 표현하기도 함.

  • SELECT A.COL1, B.COL1
    FROM SMP1 A CROSS JOIN SMP2 B

주의

  • select * from smp1
  • select * from smp2
  • select * from smp1 A right outer join smp2 B
    on (A.COL1 = B.COL1 and B.COL2 IS NOT NULL);

OUTER JOIN에서 기준이 되는 테이블은 항상 모두 출력.
ON 절에 정의된 조건으로 JOIN의 여부를 판단.
위 smp2 테이블이 기준이 되므로 일단 smp2 테이블은 모두 출력되고 on절을 만족하는 smp1의 데이터만 join되어 최종적으로 함께 출력.

  • select * from smp1
  • select * from smp3
  • select COL1, COL3 from smp1, smp3
  • select COL1, COL3 from smp1 natural join smp3

별도의 JOIN 조건이 없는 경우 두 테이블은 Cartesian Product 즉 CROSS JOIN이 됨.

두 테이블의 컬럼명이 일치하는 것이 하나도 없을 경우 natural join 시 CROSS JOIN 됨.

  • select A.COL1, B.COL2
    from smp1 A, smp2 B
    where B.COL2 IS NULL;

    에러 X
    별도의 JOIN 조건이 없으면 CROSS JOIN 됨.

  • select A.COL1, B.COL2
    from smp1 A natural join smp2 B
    on A.COL1 = B.COL1

    에러 O
    natural join에는 on 절 불가.

  • select A.COL1, B.COL2
    from smp1 A, smp2 B
    where A.COL1(+) = B.COL1(+)

    에러 O
    (+)는 하나에만 표기해야 함.

  • select B.COL1, B.COL2
    from smp1 A natural join smp2 B
    using (COL1, COL2)

    에러 O
    natural join에서 using 절을 사용할 경우 using 절로 정의된 컬럼 앞에는 별도의 테이블명이나 ALIAS를 사용할 수 없음.

  • create table smp3 (
    hire_date varchar(2)
    )
  • insert into smp3 values('a');
  • select * from smp3

ALIAS를 별도로 지정해주지 않으면 컬럼명이 대문자로 출력됨.

profile
yoon
문제 정의 - 이유 분석 - 해결 방안 모색 - 실행
이전 포스트

SQLD 1-2 요약

다음 포스트

SQLD 2-2 요약

0개의 댓글