2022년 2월 17일 - SQLD 핵심 개념 정리 필기 (1) : SELECT 문장 ~ 트랜잭션 관리 언어(TCL)

◎ 본글은 아래의 김강민 강사님의 강의를 필기한 내용입니다.

 

https://jonhyuk0922.tistory.com/115

[SQL] SQLD 공부방법 & 합격후기 (꿀팁 많음)

 

 

SQL 명령문 개괄

1. 연산순서 정렬 문제 : From - where - group by - having - select - order by (셀프웨구해오)

※ having 다음 select 주의

 

2. 종류쓰는 문제 - 정조제

DDL (데이터 정의어) - alter, create, modify, truncate, drop (크알드트) 등등

DML (데이터 조작어) - select, insert, update, delete (세인업데)

DCL (데이터 제어어) - grant, revoke

TCL (트랜잭션 제어어) - rollback, commit

 

 

SELECT

1. Distinct : 집약의 기능 - 중복 제거, 집약

※ DISTINCT deptno, mgr = DISTINCT (deptno, mgr) = group by (deptno, mgr) : 사실상 괄호친 것과 같음

 

 

Alias 

1. Select 절에서 사용 

1) as 생략 가능

2) 컬럼명에 띄어쓰기 존재할 경우 : "직원 번호"

 

2. From 절에서 사용 : 집약의 기능 - 중복 제거, 집약

1) as 사용 불가

 

 

Concat 연산자 기호

1. +

1) SQL server

 

2. ||

1) Oracle

 

3. concat ( ), ( )

1) 인수가 반드시 2개 

 

 

논리연산자

1. and

1) A and B

 

2. or

1) A or B

 

3. not

1) not A, B

 

4. 연산 순위

1) (1순위) NOT > (2순위) AND > (3순위) OR  (NAO - 나오)

 

ex) NOT <조건> AND <조건> AND NOT <조건> OR <조건>

→ (NOT <조건>) AND <조건> AND (NOT <조건>) OR <조건>

= (조건1) AND <조건> AND (조건2) OR <조건>

→ ((조건1) AND <조건> AND (조건2)) OR <조건>

= (조건1) OR <조건>

 

 

SQL 연산자

1. between

1) between A and B

→ 논리 변환 : 1 <= A <= 2

 

2. IN

1) A IN (1, 2, 3)

→ 논리 변환 : A = 1 or A = 2 or A = 3

 

3. Rownum 

→ Oracle

1) (where 조건 절에서) Rownum = 1 인 경우 포함 - 범위에서

2) 적층구조 - 범위형태로 주어야 하며, 1은 무조건 포함한다.

ex) Select empnum, sal From emp Where row num <= 3 Order by sal (Order by 가장 마지막 실행)

→ Q. Order by가 언제 실행되는가? 정렬 전 기준인가 아니면 정렬 후 기준인가.

→ 정렬 전에 Rownum에 의한 조건절 시행

→ 정렬 전 데이터에 Rownum이 걸려서 실행, 3개만 뽑고 나서 Order by가 실행됨

 

4. TOP

→ SQL Server

1) Select 절 옆에 쓴다

ex) Select TOP (n) 컬럼명 = 컬럼을 가져올 때 상위 n개를 가져오겠다 

 

 

LIKE

1. 와일드 카드

1) _

→ 미지의 한 글자

 

2) % 

→ 0 이상의 글자

 

ex) '_L%'

→ 이름의 두번째 글자가 L인 사원들 다 고르는 것

 

3) Escape

→ 와일드 카드( _ , %)를 문자로 취급하는 함수 

ex) ename like 'A_A' → 'A@_A'   [도둑이 짐을 들고 가는 모양]

     escape → '@'   [아무 문자나 됨]

 

ex) 다음은 FRUIT 테이블 내 DESCRIPTION 값이 100%_로 시작하는 행을 찾는 오라클 쿼리이다.
SELECT * FROM FRUIT WHERE DESCRIPTION LIKE '%100\%\_%' ESCAPE '\';

 

 

 

NULL

1. NULL의 정의

1) 부재, 모르는 값

 

2. NULL 연산

1) 산출연산

ex) [null+2] & [null-4] & [null x null]

= NULL

 

2) 비교연산

ex) [null = null] & [null = 2]

→ 알수없음 (Unknwon) 

→ where 조건절에 unknwon 조건이 들어갈 경우 → "False" 출력

 

3. 정렬 상의 의미

ex) NULL이 있는 값을 정렬할때

1) Oracle : ∞ (최댓값)

2) SQL Server : -∞ (최솟값)

 

4.

NULL 함수

------------------------------ (널뛰기)

1) NvL 

→ NVL("값", "지정값") 

= 값이 NULL인 경우 지정값을 출력하고, NULL이 아니면 원래 값을 그대로 출력한다.

 

WITH emp AS (
  SELECT 7839 empno, 'KING'   ename, 'PRESIDENT' job, NULL comm FROM dual UNION ALL
  SELECT 7654 empno, 'MARTIN' ename, 'SALESMAN'  job, 1400 comm FROM dual
)  

SELECT empno
     , ename
     , comm         AS comm      
     , NVL(comm, 0) AS comm2 --커미션이 NULL 이면 '0'   
  FROM emp

 

2) NvL2

→ NVL2("값", "지정값1", "지정값2") // NVL2("값", "NOT NULL", "NULL") 

= NULL이 아닌 경우 지정값1을  출력하고, NULL인 경우 지정값2를 출력한다.

 

WITH emp AS (
  SELECT 7839 empno, 'KING'   ename, 'PRESIDENT' job, NULL comm FROM dual UNION ALL
  SELECT 7654 empno, 'MARTIN' ename, 'SALESMAN'  job, 1400 comm FROM dual
)  

SELECT empno
     , ename
     , comm                 AS comm 
     , NVL2(comm, 'Y', 'N') As comm2 --커미션이 있으면 'Y' 없으면 'N' 
  FROM emp

 

3) isNull

→ NvL과 같음

 

 

------------------------------ (같이 놀자)

4) Nullif

→NULLIF("값1", "값2")

= 값1과 값2가 같다면 NULL을 리턴

= 값1과 값2가 같지 않다면 1을 리턴

 

SELECT
NULLIF(1, 1)
FROM DUAL;

 

------------------------------ (널 아닌 첫번째 값)

5) Coalesce (합치다)

→ (값1, 값2 ......)

= 여러 인자값들 중에서 앞에서부터 null이 아닌 값을 찾는 함수로 가장 먼저 null이 아닌 값을 뱉어 냄

ex) COALESCE(NULL, NULL, 2, 3, NULL) = 2

 

 

정렬

1. 정렬의 특성

1) 가장 마지막에 실행

2) 성능이 느려질 가능성이 존재

3) null과의 관계 : Oracle에서는 무한대로 취급

 

 

컬럼번호 정렬

1. 특징

1) 출력되는 컬럼의 수보다 큰값 불허 - 오류 발생

2) select 절에서 출력되지 않는 컬럼을 기준으로 정렬 가능

ex) select ename order by sal

 

 

인수 두개 정렬

1. 특징

ex) sal desc, ename asc

→ sal이 같으면 ename 오름차순으로 

 

 

숫자함수

1. Round

→ ROUND("값", "자리수") - 0은 생략 가능

→ 0 : 소수 첫째 자리서 서 반올림

→ 1 : 소수 둘째 자리서 서 반올림

→ 2 : 소수 셋째 자리서 서 반올림

→ 음수 : 정수에서 한칸씩 올라가며 반올림

 

SELECT ROUND(1235.543)    --①
     , ROUND(1235.443)    --②
     , ROUND(1235.443, 0) --③
  FROM dual

 

 

SELECT ROUND(1235.345, -1) --①
     , ROUND(1235.345, -2) --②
     , ROUND(1235.345, -3) --③
  FROM dual

 

 

2. Ceil (Oracle) / Ceiling (SQL)

ex) Ceil(99.2) = 100

= 올림하는 함수

 

 

3. Floor

ex) Floor(99.2) = 99

= 내림하는 함수

 

 

문자열 함수

1. Upper

= 대문자 변환

 

2. Lower

= 소문자 변환

 

 

3. LPad

ex) SELECT LPAD(CUSNO,5,'*') FROM CUSTOMER; =  **350

= 지정한 길이 만큼 왼쪽부터 특정 문자로 채워준다.

→ 총 다섯 자리가 되도록

 

WITH EMP AS (
    SELECT '7839' EMPNO, 'JAMES' ENAME, '30' DEPTNO FROM DUAL
)

SELECT EMPNO
     , ENAME
     , DEPTNO
     , LPAD(DEPTNO, 5)      --1
     , LPAD(DEPTNO, 5, ' ') --2
     , LPAD(DEPTNO, 5, '0') --3
     , LPAD(DEPTNO, 5, 'A') --4
  FROM EMP

 

 

4. RPad

= 지정한 길이 만큼 오른쪽부터 특정 문자로 채워준다.

 

5. Trim

= 문자의 앞 뒤 공백 제거

ex) SELECT TRIM('   HELLO ') FROM DUAL; = 'HELLO'

 

6. LTrim

= 문자의 앞 공백 제거

ex) SELECT LTRIM('   HELLO ') FROM DUAL; = 'HELLO '

 

7. RTrim

= 문자의 뒤 공백 제거

ex) SELECT TRIM('   HELLO ') FROM DUAL; = '   HELLO'

 

8. substr (헷갈림 주의)

= 문자열을 자르는 함수

→ 인덱스 번호로 계산하지 않는다.

 

WITH EXAMPLE AS(
SELECT '동해물과 백두산이 마르고 닳도록' AS STR FROM DUAL
)

SELECT
SUBSTR(STR,1,4), --STR의 1번째 글자부터 4개 자르기
SUBSTR(STR,5,5), --STR의 5번째 글자부터 5개 자르기
SUBSTR(STR,10), --STR의 10번째 글자부터 끝까지 자르기
SUBSTR(STR,-7,3), --STR의 뒤에서 7번째 글자부터 3글자 자르기
SUBSTR(STR,-3), --STR의 뒤에서 3번째 글자부터 끝까지 자르기
FROM
EXAMPLE

 

 

9. instr 

= 문자열을 위치 찾기

→ 인덱스 번호로 계산하지 않는다.

 

WITH EXAMPLE AS(
SELECT '동해물과 백두산이 마르고 닳도록' AS STR FROM DUAL
)

SELECT
INSTR(STR, ' '), --' ' STR에서 띄어쓰기 있는 문자 위치 찾기
INSTR(STR, ' ', 7), --STR의 7째 문자부터 띄어쓰기가 있는 문자 위치 찾기
INSTR(STR, ' ', 7,2), --STR의 7째 문자부터 2번째 띄어쓰기가 있는 문자 찾기
SUBSTR(STR, 0, INSTR(STR, ' ', 1, 2)) --띄어쓰기 2번째 문자열까지 문자열 자르기
FROM
EXAMPLE

 

 

10. replace

= 문자열을 교체하기

→ ~를 ~로 바꾸겠다.

 

WITH TEMP AS (
    SELECT 'Steven King' NM FROM DUAL
)


SELECT A.NM
     , REPLACE(A.NM, 'Steven', 'S.') RE_NM
  FROM TEMP A

 

 

 

 

날짜 함수

1. To_char

= 날짜를 서식에 맞춰서 문자열로 변환

→ TO_CHAR(날짜데이터, 형식)

→ 데이터의 형변환을 일으킴

ex) SELECT TO_CHAR(hire_date, 'YYYY-MM-DD') AS hire_date FROM employees; = 2003-06-17

 

2. To_date

= 문자를 날짜 데이터 타입으로 변화해 주는 함수

→ 데이터의 형변환을 일으킴

 

SELECT TO_DATE('20200324', 'YYYYMMDDHH24MISS')
  FROM DUAL;

 

3. Sysdate

= Oracle에서 현재 함수를 출력시키주는 함수

 

4. Getdate( )

= 오늘 날짜를 가지고 오는 함수

ex) SELECT GETDATE( ) = 2019-07-29 17:27:41.857

 

5. 날짜데이터 + N

= N일 이후

 

 

Decode/Case

1. Decode

= 프로그래밍에서의  if else 와 비슷한 기능을 수행

→ ELSE 부분은 생략이 가능하다. 해당 조건이 없으면 NULL

 

 

2. Case

= 조건에 따라 출력 결과값 다르게 분류

→ ELSE가 없을 때, 모든 조건들을 만족하지 않으면 NULL 출력

 

 

 

집계함수

1. NULL과의 관계

= Oracle에서 현재 함수를 출력시키주는 함수

→ COUNT(*) : NULL 값을 포함한 행의 수

→ 기본적으로 NULL값 자체를 계산에서 제외함

→ 조건절에 해당하는 데이터가 없을 때 COUNT(*)의 결과 값은 0

→ 연산 + NULL = NULL

Q. SUM(A+B+C) ?

→ A+B+C 라는 새로운 컬럼을 만든다.

→ null 값을 제외하고 더한다.

= 7

 

→ 답은 120

 

 

그룹함수

1. Group By

→ 집약기능

→ Where 다음에 실행

→ 그룹 수준 정보를 가지고 온다.

→ Having절을 조건절로 갖는다.

 

 

JOIN

1. Natural Join

→ 중복된 컬럼이 하나만 출력되며, 가장 맨 앞에 등장한다.

 

2. Using

→ 중복된 컬럼이 하나만 출력되며, 가장 맨 앞에 등장한다.

→ alias 사용 불가

 

3. LEFT OUTER JOIN

ex) [COL1이 조인키일때] 

A LEFT OUTER JOIN B  라는 쿼리는 A COL1 = B COL1 (+) 

→ OUTER JOIN 방향의 반대로 (+)를 붙인다. (선행절과 후행절의 위치가 동일했을때)

 

ect) 조인순서

ex) FROM A,B,C

→ FROM (A,B 조인), C

→ FROM ((AB 테이블), C 조인)

 

 

서브쿼리

1. 구조(셀프웨구해오)에서 서브쿼리가 못들어가는 곳

Select - 단일행 서브쿼리인 Scalar 서브쿼리가 들어감

From - Inline View : 메인쿼리의 컬럼 사용가능

Where - 거의 모든 서브 쿼리가 들어감 : 중첩 서브쿼리

Group By X

Having - 거의 모든 서브 쿼리가 들어감 : 중첩 서브쿼리

Order by - Scalar 서브쿼리

 

2. 이외

1) IN

2) Any / Some

= 주로 서브 쿼리와 함께 사용되며 서브쿼리에서 여러 개의 값이 나오게 되는 경우 이 값들에 대해 어떤 한 값 보다만 어떻다라고 비교할 때 사용된다.

ex) sal > any (500, 600, 700)

→ sal > 500 or sal > 600 or sal > 700 형태로 해석되어 3개의 값 중 하나의 값인 500보다 크면 TRUE가 된다(sal > 500). 만약 700보다 크다면 모든 값보다 크게 된다.

 

3) All

= ALL은 집합의 모든 값과 비교를 하게 되는데, 모든 값과 비교하여 TRUE가 되어야 한다.

ex) sal > all(500, 600, 700)

→ sal > 500 and sal > 600 and sal > 700의 의미로 결국 sal > 700와 같다.

 

4) Exist

= 서브 쿼리의 결과가 "한 건이라도 존재하면" TRUE 없으면 FALSE를 리턴한다

→ 모든 문자 사용 가능

 

 

집합연산자

1. UNION

→ 정렬작업 존재 & 느리다

→ 중복 없음

 

2. INTERSECT

→ 정렬작업 존재  & 느리다

 

3. MINUS (SQL : EXCEPT)

→ 정렬작업 존재  & 느리다

 

4. UNION ALL

→ 중복 데이터 등장

→ 정렬 작업 없음

→ 빠르다 

 

 

DDL

1. TRUNCATE (입주민 퇴거 : 구조 남음)  VS  DROP (건물철거 : 구조 삭제)

→ LOG DATA : 이전 기록 정보들이 남아있음

 

2. TRUNCATE (DDL)  VS  DELETE (DML)

→ TRUNCATE는 단순한 조건일 것이고 DELETE는 다양한 조건

 

 

DML

1. INSERT

1) 오류 상황

ex) INSERT (A, B, C) VALUES(1, 2) 

→ INSERT와 VALUES에 걸린 값의 수가 다를 경우

 

2. UPDATE

 

3. DELETE

 

etc) MERGE - 신유형 37회 

 

 

제약조건

1. PK = UNIQUE + NOT NULL : 하나만 존재

2. UNIQUE :

3. NOT NULL 

 

 

DCL (데이터 제어어)

1. GRANT - With Grant Option

2. REVOKE - ONTO 기출문제 확인

3. ROLL 특징 [객체 - Object]

1) 사람에게도 부여가능

2) ROLE은 ROLE에게 부여가능

3) 사람들은 같은 ROLE을 갖을 수 있음

4) ROLE을 부여하려면 권한이 필요

5) ROLE은 권한 있는 사용자에 의해 만들어짐

 

 

VIEW (독편보)

1. 독립성

→ 기존 테이블 구조가 변경되면 뷰의 구조가 같이 변경된다

→ 따로 업데이트할 필요가 없다

 

2. 편리성

→ 계속 테이블 조작할 필요 X

 

3. 보안성

→ 원하는 정보만 출력하고 나머지는 숨길 수 있음

 

4. 테이블이 아니라 SQL 명령문을 저장함

→ 따로 저장 공간 필요

 

 

그룹함수

※ 결과값 주고 이게 무엇에 관한 것인지 물어봄 : 비교표

1. ROLL UP

Q. Rollup(A, B)와 Rollup(B, A)가 같은가? X

→ 계층 구조로 진행되기에 같은 결과가 나오지 않는다.

 

2. CUBE

Q. Cube(A, B)와 Cube(B, A)가 같은가?

→ 같은 결과가 나온다.

 

3. GROUPING SETS

 

4. GROUPING

 

ect) 표에 구멍을 뚫어서 그룹함수 중 무엇인지 판단하는 문제 유형 대처법

1) NULL 다찾기

2) 총합행 있는 것 찾기

→ 있다 O

(1) 양쪽으로 둘다 결과가 나온다 : CUBE - 행의 수가 많아 보인다

(2) 한쪽만 결과가 계층으로 나온다 : ROLLUP - 행의 수가 적어보인다

→ 없다 X : GROUPING SETS

 

 

TCL

1. COMMIT 

1) auto commit off

2) begin transaction - DDL의 커밋 기능 없애는 것

 

2. ROLLBACK