[SQLD] 최종 정리

 

문장 실행 순서

 

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

 

1. 발췌 대상 테이블을 참조 (FROM)

2. 데이터가 아닌 것은 제거 (WHERE)

3. 그룹화 (GROUP BY)

4. 그룹핑 조건 (HAVING)

5. 출력 (SELECT)

6. 정렬 (ORDER BY)

 

 

SQL 문장들의 종류

데이터 조작어(DML) : SELECT , INSERT , UPDATE , DELETE

데이터 정의어(DDL) : CREATE , ALTER , DROP , RENAME

데이터 제어어(DCL) : GRANT , REVOKE

트랜잭션 제어어(TCL) : COMMIT , ROLLBACK

 

 

DISTINCT

중복된 경우 1건만 출력

 

데이터 그룹화/집계 (GROUP BY)

같은 값을 가진 행끼리 하나의 그룹으로 뭉침

장르를 기준으로 그룹

 

 

DISTINCT deptno,mgr == GROUP BY (deptno,mgr) 같은 의미 

 

 

별칭 사용(ALIAS) AS

 

SELECT - 1. AS 생략가능 /  2. 컬럼명 띄어쓰기 ( ex. " 직원 번호 " ) 

 

FROM - AS 사용불가 

 

 

문자열 합치기(CONCAT)

인수 무조건 2개 

 

SELECT CONCAT( ' abc ' , ' 123 ' ) 문자열결과

FROM 테이블;

 

SQL Server - " + " 

 

ORACLE - " || " 

 

 

연산자의 우선순위

 

1. NOT 

2. AND 

3. OR 

 

A BETWEEN 1 AND 2  -->  1 <= A <= 2 

 

A IN (1,2,3)   -->   A= 1 or 2 or 3 

 

 

LIKE 연산자 

 

 문자열에서 와일드카드(%)를 사용하여 원하는 문자가 포함된 자료를 쉽게 검색할 수 있다.

 

" %  " : 모든 문자   

"  _  " :  한글자

 

 

ESCAPE 식별자

 

_ , % 를 문자로 취급할 수 있게 해주는 식별자 

 

 

"  \ " 와 같은 escape 식별자는 아무 문자나 와도 사용 가능 

 

 

조회 순번 적용하기/원하는 개수만 조회(ROWNUM)

 

ROWNUM(ORACLE) - 1 을 포함

 

 

ORDER BY 가 마지막에 실행 !! 

 

 

상위 데이터 출력 (TOP) 

 

TOP(n) : 상위 n 개의 행을 가져온다. 

 

 

 

 

NULL

 

아직 정의되지 않은 값 , 모르는 값 

 

NULL 은 0 과 공백이랑은 다르다. 
 

연산 

NULL은 특정 값보다 크다,적다라고 표현할 수 없다.
 
NULL값 과의 연산은 NULL값을 리턴한다. ( 비교연산은 FALSE 리턴 ) 
 
NULL + 40 = NULL 
 
NULL 값을 조건절에서 사용하는 경우 IS NULL , IS NOT NULL 이란 키워드 사용 

 

정렬에서의 의미 

ORACLE - 가장 큰 값

SQL Server - 가장 작은 값 

 

ISNULL/NVL 함수

표현식1의 결과값이 NULL이면 표현식2의 값을 출력한다.

NVL(표현식1, 표현식2)
ISNULL(표현식1, 표현식2)

--예시--
ISNULL(Colum,0)

NVL2 함수

 

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

 

- 함수 :  NVL2("값", "지정값1", "지정값2") // NVL2("값", "NOT NULL", "NULL") 

 

NULLIF 함수

표현식1이 표현식2와 같으면 NULL을 , 같지 않으면 표현식1을 리턴한다.

NULLIF(표현식1, 표현식2)

COALESCE 함수

임의의 개수 표현식에서 NULL 이 아닌 최초의 표현식을 나타낸다.
모든 표현식이 NULL이라면 NULL 을 리턴한다.

COALESCE(표현식1, 표현식2, … )

 

 

정렬(ORDER BY)

 

SELECT문을 사용할 때 출력되는 결과물은 테이블에 입력되는 순서대로 출력되는 것이 기본이다.

 

SELECT 문 마지막에 위치 , 가장 마지막에 실행 / 성능이 느려질 수 있다

(null 정렬시 ORACLE 에서는 무한대로 취급 ) 

 

ASC : 오름차순(기본값)   /    DESC : 내림차순

 

 

 

 

 

출력되는 컬럼의 수보다 큰 값은 허용하지 않는다 . 

 

 

인수가 2개 일때 

 

sal DESC , ename ASC : sal 이 같으면 ename 오름차순 

 

 

cf) 출력되지 않는 컬럼명으로 정렬이 가능하다 . 

SELECT ename ORDER BY sal; 

 

sal 이 없어도 정렬가능 

 

 

숫자함수

 

문자열 함수

LOWER(c): 주어진 자료 c의 모든 문자를 소문자로 변환하여 반환
UPPER(c): 주어진 자료 c의 모든 문자를 대문자로 변환하여 반환

 

LPAD(값, 총문자길이, 채울 문자) 

 

오라클에서 왼쪽, 오른쪽에 특정문자를 채워서 문자열 길이를 맞출 때는 LPAD, RPAD  함수를 사용한다.

주로 숫자 앞에 '0'을 채워서 문자열 길이를 맞출 때 자주 사용한다. 문자열의 자릿수를 고정할 때도 사용한다.

 

LPAD(123, 5, '0') → 결과: 00123

LPAD(123, 8, '0') → 결과: 00000123

 

RPAD 함수는 지정한 길이만큼 오른쪽부터 채움문자로 채운다.

 

TRIM 함수

문자열의 양쪽 공백을 제거하는 기본적인 함수

 

  LTRIM("문자열", "옵션") 

LTRIM 함수는 문자열의 왼쪽(좌측) 공백  제거, 문자 왼쪽 반복적인 문자를 제거를 한다. 

 

 

SUBSTR

SQL에서 함수 substring은 하나의 필드 데이터의 일부를 읽는데 사용된다.

 

날짜 함수

 

데이터의 형변환을 일으킨다. 

 

TO_CHAR()

날짜, 숫자 등의 값을 문자열로 변환하는 함수이다.

 

TO_DATE()

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

 

현재 시간 조회

SYSDATE() - ORACLE

GETDATE() - SQL Server

 

 

날짜 데이터 연산 

날짜 + 100 : 100은 day(기본값)

1/24/60은 1일을 24시간으로 나누고 60분으로 나눴으므로 1분을 의미

 

 

조건에 따라서 값을 지정(CASE)

 

CASE 컬럼

WHEN 조건1 THEN 값1 

WHEN 조건2 THEN 값2 

ELSE 값3 

END 

 

컬럼이 조건1 일때는 값1 , 조건2 일때는 값2 반환 

조건에 맞지 않는 경우 값3 반환 

 

ELSE 가 없고 조건1,조건2가 만족하지 않을경우 NULL 을 반환

 

 

 

집계함수(SUM,COUNT)

 

 SUM은 선택한 열(Column)의 합을 계산한다.  NULL값은 무시하고 계산

 

SUM(A+B+C) 와 SUM(A) +SUM(B) + SUM(C) 는 다르다

 

 

COUNT는 특정 열(Column)의 행의 개수를 세는 함수이다.

COUNT(*)로 작성하면 테이블에 존재하는 행의 개수가 반환되고,

특정 열에 대해서 COUNT를 수행하면 해당 열이 NULL이 아닌 행의 개수를 반환한다. 

 

 

 

데이터 그룹화/집계 (GROUP BY)

같은 값을 가진 행끼리 하나의 그룹으로 뭉침

장르를 기준으로 그룹

SELECT 집계함수(컬럼1), 집계함수(컬럼2) ..
FROM 테이블
GROUP BY 컬럼

 

 

HAVING

그룹에 대한 조건절 , 그룹 중 조건에 맞는 그룹만 남깁니다.

 

 

JOIN

 

Natural JOIN 은 중복된 컬럼을 하나의 컬럼으로 나타내고 제일 앞에 등장한다. 

 

USING 조건절 

 

FROM절의 USING 조건절을 이용하면 같은 이름을 가진 컬럼들 중에서 원하는 컬럼에 대해서만 EQUI JOIN을 할 수 있다.

sql server에서는 지원하지 않는다. 

 

SELECT  *
FROM    DEPT JOIN DEPT_TEMP
USING   (DEPTNO) ;

USING 조건절에서도 테이블 이름이나 ALIAS, 접두사를 붙일 수 없다.

 

 

LEFT OUTER JOIN

 

왼쪽 테이블 모든 데이터 + 중복데이터들이 검색됨

 

값이 없는 경우 NULL 값으로 채운다.

 

A LEFT OUTER JOIN  같은의미    Acol = Bcol(+)

 

대상 컬럼(+) : 기준테이블이 아닌 다른 테이블

 

 

서브쿼리

 

 

EXISTS 연산자

 

EXISTS 연산자는 서브쿼리에 데이터가 존재하는지 체크하고 존재할 경우 TRUE를 반환

 

 

 

NOT EXISTS 연산자

 

NOT EXISTS는 EXISTS와 반대로 서브쿼리에 데이터가 존재하지 않을 경우 데이터가 조회된다.

 

(TRUE → FALSE, FALSE → TRUE) 반환 값이 반대로 바뀌어서 서브쿼리에 존재하지 않는 데이터만 조회

 

 

집합연산자(UNION/INTERSECT/MINUS)

 

UNION ALL : 중복데이터 O ,  정렬작업 X , 빠르다 

 

 

 

테이블 삭제 (DROP TABLE)

테이블 자체 삭제 명령어

테이블 자체가 모두 지워지며 , 인덱스도 삭제됨

DROP TABLE 테이블명;

테이블의 데이터 전부 삭제 (TRUNCATE)

해당 테이블의 데이터가 모두 삭제되지만 테이블 자체가 지워지는 것은 아니다.

인덱스도 함께 TRUNCATE 된다.

TRUNCATE TABLE 테이블명;

TRUNCATE 명령어는 UNDO를 위한 데이터를 생성하지 않기 때문에 동일 데이터량 삭제시 DELETE 보다 빠르다. 

 

DROP , TRUNCATE - Auto Commit / DDL 

 

DELETE - 사용자 Commit / Commit 이전에 Rollback 가능  DML 

TRUNCATE 과 DROP 은 로그를 남기지 않는다 .

 

 

 

 

VIEW

 

테이블은 실제로 데이터를 가지고 있는 반면, 뷰(view)는 실제 데이터를 가지고 있지 않다. 

뷰는 단지 뷰 정의 (view definition)만을 가지고 있다. 

질의에서 뷰가 사용되면 뷰 정의를 참조해서 DBMS 내부적으로 질의를 재작성 하여 질의를 수행한다. 

뷰는 실제 데이터를 가지고 있지 않지만 테이블이 수행하는 역할을 수행하기 때문에 가상 테이블(virtual table)이라고도 한다. 

 

뷰의 장점

독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다. 

편리성 : 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다. 

보안성 : 직원의 급여정보와 같이 숨기고 싶은 정보가 존재한다면 뷰를 생성할 때 해당 컬럼을 빼고 생성해서 감출 수 있다. 

 

 

 

 ROLLUP

 

ROLLUP함수는 소그룹간의 합계를 계산하는 함수입니다.

ROLLUP을 사용하면 GROUP BY로 묶은 각각의 소그룹 합계와 전체 합계를 모두 구할 수 있습니다. 

 

SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY ROLLUP(상품ID, 월);

 

NULL값으로 표시된 부분들이 바로 ROLLUP함수를 써서 나온 합계입니다. 

 

 

CUBE

 

CUBE함수는 항목들 간의 다차원적인 소계를 계산합니다.

ROLLUP과 달리 GROUP BY절에 명시한 모든 컬럼에 대해 소그룹 합계를 계산해줍니다.

 

SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY CUBE(상품ID, 월);

ROLLUP함수를 사용했을 때보다 결과가 좀 더 복잡합니다. 상품ID별 합계뿐만 아니라 월별 합계까지 한 번에 볼 수 있습니다.

 

 

GROUPING SETS

 

GROUPING SETS는 특정 항목에 대한 소계를 계산하는 함수입니다. 

SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
GROUP BY GROUPING SETS(상품ID, 월);

앞의 ROLLUP, CUBE에 비해 훨씬 결과가 단순합니다.

 

 

윈도우 함수

SQL의 윈도우 함수란 행과 행 간을 비교, 연산, 정의하기 위한 함수이다. 분석함수 또는 순위함수라고 하기도 한다. 

 

 

 ROWS는 각 행의 위치고, RANGE는 값의 범위

 

ROWS와 RANGE 차이점

ROWS와 RANGE 차이

ROWS :  조회된 데이터를 물리적 위치(ROWNUM)로 구분하여 모든 행이 1개의 행으로 인식한다.

 

RANGE:  ORDER BY 절에 명시된 칼럼으로 논리적인 행 집합을 구성하며, 집합으로 묶인 그룹이 1개의 행으로 인식한다.

위의 예제는 SALES_QUARTER 칼럼의 값으로 행 집합을 지정하였다. ORDER BY에 명시된 칼럼에 따라서 행 집합이 바뀔 수 있다.

 

 

RANK

RANK 함수는 중복 값들에 대해서 동일 순위로 표시하고,

중복 순위 다음 값에 대해서는 중복 개수만큼 떨어진 순위로 출력하도록 하는 함수 입니다.

 

SELECT empNo, empName, salary,

RANK() OVER (ORDER BY salary DESC) RANK등수

FROM employee;

 

 

DENSE_RANK

DENSE_RANK 함수는 중복 값들에 대해서 동일 순위로 표시하고,

중복 순위 다음 값에 대해서는 중복 값 개수와 상관없이 순차적인 순위 값을 출력하도록 하는 함수 입니다.

 

SELECT empNo, empName, salary,

DENSE_RANK() OVER (ORDER BY salary DESC) DENSE_RANK등수

FROM employee;

 

 

 

 

계층형 쿼리(CONNECT BY)

 

계층형 구조는 상하 수직관계의 트리형태의 구조로 이루어진 형태를 말합니다.

 

예를 들자면 특정회사의 부서, 특정학교의 학과등이 있습니다. 

 

계층형 쿼리는 테이블에 저장된 데이터를 계층형 구조로 반환하는 쿼리를 말합니다.

 

 

수행순서

1. START WITH 절에 시작 조건을 찾습니다.

2. CONNECT BY 절에 연결조건을 찾습니다.

 

 

 

 

START WITH : 루트 데이터를 지정한다. / 루트 노드의 레벨값은 0 이다. 

 

ORDER SIBLINGS BY : 형제노드 사이에서 정렬을 수행한다. 

 

CONNECT BY PRIOR 자식 = 부모 : 부모 -> 자식 순방향 전개