티스토리 뷰

DB

SQL 기초

yun jjang 2018. 11. 19. 21:47


<목차>

*.알아두어야 할 SQL의 기본적인 작성 규칙

*.테이블 생성

*.알아두어야 할 SQL의 기본적인 명명 규칙

*.데이터형

<SPECIAL TIP!>*.데이터형 varchar과 varchar2의 차이점

<SPECIAL TIP!>*.varchar와 char이 구분지어 존재하는 이유

*.테이블 삭제 != 테이블 데이터 삭제

*.테이블 정의 변경(추가, 삭제, 수정)

*.테이블에 데이터 입력

*.테이블 명 변경

*.테이블 컬럼 명 별칭 주기 – AS문

*. 원하는 상수 넣기

*.중복을 제거한 데이터를 얻는다. – SELECT DISTINCT문

*.주석 처리

*산술 연산자란

*.null은 주의 해야 한다!

*.NOT연산자

*.비교연산자의 =등호는 무조건 오른쪽에 와야 한다.

*.숫자2와 문자’2’는 다르다!

<SPECIAL TIP!>*.빈문자(‘’)와 NULL의 차이점

<SPECIAL TIP!>*.NULL과 IS NULL의 차이

<SPECIAL TIP!>*. AND와 OR 작성시 주의할 점

<SPECIAL TIP!>*.NULL처리 : NULL연산자, NVL(컬럼명, 대체값)문

*.COUNT함수

*. DISTINCT키워드

*.HAVING구보다 WHERE구가 더 좋은 이유

*ORDER BY구

*.NULL의 순서

*.AVG의 문자열 자동 CASTING

*.VIEW –가상의 테이블

<SPECIAL TIP!>*.VIEW를 사용하는 이유

*.스칼라 서브쿼리

<SPECIAL TIP!>*.서브쿼리 사용과 with문 사용의 차이점

*.함수

*.술어 (=반환값이 진릿값인 함수)

*. 조건 분기식 - CASE식

*. 집합연산(행 더하기) – UNION & UNION ALL

*.결합 – JOIN

*. 세로로 나열되어 있는 데이터를 가로로 나열하는 방식 - PIVOT

*. 중분류의 합(소계), 대분류의 합(합계)을 집약해 내는 문법 - ROLL UP

*. 윈도우 함수

<SPECIAL TIP!>*. ROW_NUMBER를 응용한 페이징 처리

*. GROUPING SETS

*. 재귀 쿼리 CONNECT BY

 

*.알아두어야 할 SQL의 기본적인 작성 규칙

1.     sql문 마지막에 세미콜론을 붙여야 sql문장이 종결된다.

2.     대소문자 구분이 없다.(select나 SELECT나 동일)

3.     sql에서는 대소문자 구분이 없지만 테이블에 저장한 문자열의 경우 대소문자가 구별된다.

(ex) ‘minji’  != ‘MINJI’

4.     문자열은 작은 따옴표를 사용한다. “abc” (x) / ‘abc’ (o)

*.테이블 생성

(ex)테이블Goods을 만드는 CREATE TABLE문

CREATE TABLE GOODS(

goods_id         CHAR(4)         NOT NULL, /*상품ID*/

goods_name      VARCHAR(100)    NOT NULL, /*상품명*/

goods_classify  VARCHAR(32)     NOT NULL, /*상품분류*/

sell_price      INTEGER, /*판매단가*/

buy_price       INTEGER, /*매입단가*/

register_date   DATE, /*등록일*/

PRIMARY KEY(goods_id)

)

*.알아두어야 할 SQL의 기본적인 명명 규칙

데이터베이스, 테이블, 열 이름을 지을 때는 영문자, 숫자, 언더바(_)만 사용 가능하다.

이름의 첫 글자는 반드시 영문이어야 한다. (기호, 숫자, 한글 never)

하나의 데이터베이스 안에서 같은 이름의 테이블을 두개 이상 만들 수 없다. 

*.데이터형

1.INTEGER형(숫자)

정수를 넣기 위한 데이터 형. 소수는 넣을 수 없다. 소수는 나중에..

2.CHAR형(문자)

고정 문자열 데이터가 저장되는 데이터형.

고정 문자열에서는 열에 넣는 문자열 길이가 최대 길이보다 작은 경우, 문자 수가 최대 길이가 될 때까지 공백(space)으로 채운다.

예를 들어, CHAR(8)열에 ‘abc’라는 문자를 넣었다고 하자.

그러면 ‘abc^^^^^’(^는 space를 의미) 같이 abc뒤에 공백5개가 붙어서 저장된다.

3.VARCHAR형(문자)

가변 문자열 데이터가 저장되는 데이터형.

가변 문자열에서는 문자 수가 최대 길이보다 작아도 공백으로 채우지 않는다.

 

<SPECIAL TIP!>

*.데이터형 varchar과 varchar2의 차이점

 

*.varchar와 char이 구분지어 존재하는 이유

[대용량 데이터 베이스 솔루션]’s 이화식 저자曰


하드 디스크(기억장치)는 레코드들로 이루어져 있다.

DB의 최적화된 성능을 위해서는 Block단위로 읽고 저장해야 한다.

따라서 char(고정문자형)를 사용하는 것이 varchar(가변문자형)를 사용하는 것보다 성능적으로 좋다. 하지만 테이블을 만들 때 char과 varchar를 함께 사용하면 그 효과가 떨어진다. 따라서 보통 varchar로만 많이 사용하고 통신사 같이 빅테이터를 처리하는 경우에 성능 극대화를 위해 숫자형까지 char로 사용한다.

 

∴ char를 사용하는 경우

1.     DB 성능 향상을 위한 경우

2.     Char(1)처럼 공백이 들어갈 여지가 없는 고정 값이 저장 될 경우

*.테이블 삭제 != 테이블 데이터 삭제

테이블 삭제는 말그대로 테이블 자체를 삭제하는 것이고, 테이블 데이터 삭제는 테이블 안의 내용만 삭제 하는 것이다.

-테이블을 삭제하는 DROP문

DROP TABLE 테이블명

(EX) DROP TABLE GOODS

 

-테이블의 row(=데이터)를 삭제하는 DELETE문

DELETE FROM  테이블명

(EX) DELETE FROM GOODS


*.테이블 정의 변경(추가, 삭제, 수정)

테이블을 다 만든 후 나중에 열을 추가하거나, 열을 삭제해야 하는 경우, 테이블 명을 아예 바꿔야 하는 경우 등등.. 테이블을 새로 만드는 것이 아니라 ALTER TABLE문을 사용하여 테이블 정의를 변경 할 수 있다. 테이블을 수정한 후에는 되돌리기 할 수 없으므로 신중히 작업해야 한다!

-열을 추가하는 ALTER TABLE

ALTER TABLE 테이블명 ADD COLUMN 컬럼명1 (,컬럼명2, 컬럼명3, 컬럼명4…) in mssql

 

ALTER TABLE 테이블명 ADD 컬럼명1 (,컬럼명2, 컬럼명3, 컬럼명4…) in oracle

 

(EX) ALTER TABLE  Goods ADD COLUMN good_color VARCHAR(100)

 

-열을 삭제하는 ALTER TABLE문

ALTER TABLE 테이블명 DROP COLUMN 컬럼명 in mssql

ALTER TABLE 테이블명 DROP (컬럼명) in mssql

(EX) ALTER TABLE GOODS DROP COLUMN good_color


*.테이블에 데이터 입력

-테이블에 데이터 등록하는 INSERT문

INSERT INTO 테이블명 (컬럼명) VALUES (데이터)

(EX)

-특정 컬럼에 데이터를 등록할 때

INSERT INTO GOODS (goods_id) VALUES ('AA')

-모든 컬럼에 데이터를 등록할 때 (등록할 컬럼명을 열거하지 않아도 됨)

INSERT INTO VALUES ('001', '티셔츠', '의류', 10000, 5000, '2016-03-21')


*.테이블 명 변경

ALTER TABLE 기존테이블명 RENAME TO 변경할테이블명 in oracle

RENAME 기존테이블명 TO 변경할테이블명 in mysql

RENAME 기존테이블명 ,  변경할테이블명 in mssql

RENAME TABLE 기존테이블명 TO 변경할테이블명 in DB2


*.테이블 컬럼 명 별칭 주기 – AS문

SELECT MENU_CD FROM MENU    ->    SELECT MENU_CD AS MENU_CODE FROM MENU

*. 원하는 상수 넣기

SELECT '가나다' AS TEMP FROM MENU

 

*.중복을 제거한 데이터를 얻는다. – SELECT DISTINCT문

-여기서 중요한 것은 NULL도 500처럼 하나의 데이터로 인식 된다는 점이다!

-DISTINCT 키워드는 컬럼명 앞에만 놓을 수 있다.

(EX)

SELECT DISTINCT agent_code,ord_amount FROM orders (O)

SELECT agent_code,  DISTINCT ord_amount FROM orders (X)

*.주석 처리

-1행주석 : ‘--‘뒤에 기술한다. 한 줄로만 작성 할 수 있다.

(EX)

SELECT '가나다' AS TEMP

--이 줄은 의미 없는 줄 입니다.

FROM MENU

-복수행주석 : ‘/*’과 ‘*/’사이에 작성한다. 여러 줄에 걸쳐서 작성 할 수 있다.

(EX)

SELECT '가나다' AS TEMP

/*이 줄은 의미 없는 줄

입니다*/

FROM MENU

 

*산술 연산자란 계산이나 대소 비교 등 연산을 하는 기호 연산자를 말한다.

- 괄호를 사용하여 계산할 때 우선순위를 정할 수 있다.

-NULL을 포함하는 연산은 결과가 NULL이 나온다.

-NULL인지 아닌지는 IS NULL연산자 또는 IS NOT NULL연산자를 사용한다. (IS NULL(O), =NULL(X))

-연산은 행 단위로 이루어진다.

 

*.null은 주의 해야 한다!

5 + NULL = NULL

10 – NULL = NULL

2 * NULL = NULL

100 / NULL = NULL

NULL / 9 = NULL

NULL / 0 = NULL

NULL은 0이 아니다! NULL을 포함한 계산은 무조건 NULL이다.



*.NOT연산자

-같지 않다는 부정 조건을 나타낼 때 <>를 표준으로 사용한다.

-일반적으로 !=를 많이 사용하지만 이것은 표준이 아니다.

 


*.비교연산자의 =등호는 무조건 오른쪽에 와야 한다.

(EX)

>= : ~이상이다

<= : ~이하이다



*.숫자2와 문자’2’는 다르다!


 <SPECIAL TIP!>

*.빈문자(‘’)와 NULL의 차이점

NULL은 빈공간을 의미하고, 빈문자 ‘’는 길이가 0인 문자열이라고 생각하면 된다.

DB마다 조금씩 인식하는 것이 다른데, DB2, mssql은 빈값(‘’)과 NULL을 구분짓고, 오라클은 구분 짓지 않는다.

따라서, 오라클에서 INSERT로 컬럼에 ‘’값을 넣으면 NULL로 들어간다.

때문에, SELECT로 ‘’는 출력이 불가능하다.



*.NULL과 IS NULL의 차이

(EX)

select * from ~ where col_1 = null è조회는 되지만 아무 결과도 나오지 않는다.

Select * from ~ where col_1 is null è값이 null인 결과가 나온다.

= null 은 표준 sql에 부합하지 않은 문장으로 시스템 내부적인 의미로  = 0(숫자0)과 같다.

따라서 권장하지 않는다.

(※ xml파일에서 <>를 예약어로 사용하므로 쿼리에서 <>는 사용하지 않는 것이 좋다.)

NULL은 IS NULL 또는 IS NOT NULL 연산을 사용해야 한다.



*. AND와 OR 작성시 주의할 점

(EX)

select * from ~

where A and B or C or D and E

-이런식의 나열로는 프로그래머가 코드를 해석하기 어렵다.

-이런식은 (A and B) or C or (D and E)이렇게 ()괄호를 사용하여 명시적으로 분리를 해주어야 한다.

-A  & B & (C | D) & (E | F)처럼 and는 and끼리 or는 or끼리 묶어서 작성하는 것이 좋다.



*.NULL처리 : NULL연산자, NVL(컬럼명, 대체값)문

이해를 돕기 위해 예제로 설명!

위의 테이블A에서 COL_2컬럼이 12가 아닌 ROW를 출력하도록 명령문을 작성해봤다.

SELECT * FROM A WHERE COL_2 != 12

SELECT * FROM A WHERE COL_2 <> 12

ð  결과는 아무것도 출력 되지 않았다. 즉, NULL을 가져오지 않는다.

ð   

è여기서 알 수 있는 것은 ‘!=’와 ‘<>’는 같다는 것

èNULL값을 추출하기 위해서는 반드시 NULL연산자(IS NULL, IS NOT NULL)를 사용해야 한다는 것

è이를 응용하여 오라클에서, ,,NULL인 데이터를 추출해서 다른 값으로 치환하여 작업하는 경우가 있다.

SELECT NVL(컬럼명, 대체값) FROM 테이블명 /* 해당 컬럼이 null이면 대체값으로 바꿔서 출력하라*/

SELECT COL_1, NVL(COL_2, 'N') FROM A /* COL_2컬럼이 null이면 'N'으로 바꿔서 출력하라*/

 

BUT!! NULL처리 함수 NVL(컬럼, 대체값)은 행(ROW)마다 함수가 실행되므로 속도가 느려진다. 따라서 NULL연산자를 사용하는 것이 최선이다!

NULL조심


*.COUNT함수 – null을 제외한 행수를 계산한다.but 인수(파라미터)에 따라서 처리 결과가 달라진다.

 

-SELECT

COUNT(COL_1) AS CNT

FROM 테이블명 

è COL_1컬럼 행이 NULL인 행을 제외하고 COUNT한다.

 

-SELECT

 COUNT(*) AS CNT

FROM 테이블명

è 전체 행 수를 계산한다.



*. DISTINCT키워드 – 집약 함수 사용시 중복데이터 값을 제외하고 계산된다.

-중복을 제외한 집약결과의 DISTINCT는 반드시 괄호 안에 기술해야 한다.

 SELECT COUNT(DISTINCT goods_classify) FROM GOODS 처럼 괄호 밖에 쓰면 먼저 goods_classify열의 행 수를 센후에 결과에서 중복값을 제외하게 되므로 결과는 goods_classify열의 전체 행 수가 된다. 따라서 SELECT DISTINCT COUNT(goods_classify) FROM GOODS로 수정해야 한다.



*.HAVING구보다 WHERE구가 더 좋은 이유

 

WHERE구와 HAVING 구 양쪽 모두에 쓸 수 있는 조건을 일부러 WHERE 구에 써야 하는 이유는 실행 속도의 차이.즉, 성능 차이 때문이다.

WHERE구를 사용해서 조건을 지정하면 SORT전에 행을 걸러내기 때문에 SORT대상 행 수를 줄일 수 있다. 반면,HAVING구는 SORT가 끝난 후 그룹화되고 나서 실행 되기 때문에 WHERE구보다 행 수가 많다. 그리고 WHERE구는 조건으로 지정하는 열에 ‘색인(Index, 인덱스)’을 작성함으로써 처리를 고속화시킨다.



*ORDER BY구

-ORDER BY구는 어떠한 경우라고 SELECT문 제일 마지막에 작성한다.

서브쿼리에서 ORDER BY는 의미도 없을 뿐더러 작동하지 않으므로 사용하지 않는다.



*.NULL의 순서

 NULL은 100보다 큰 값일까, 아니면 작은 값일까? 또한 5000과 NULL중 어느 쪽이 큰 값일까?

NULL은 문자나 날짜, 숫자와도 비교할 수 없다. 따라서 NULL데이터를 포함하는 컬럼을 SORT키로 지정할 경우 NULL은 제일 앞 또는 제일 뒤에 놓인다.

 

(+) NULL의 ORDER BY는 DB의 종류마다 다르다 DB2와 오라클은 ASC로 NULL을 뒤로 보내고, MSSQL은 ASC로 NULL을 앞으로 보낸다.

따라서, DB의 종류에 상관없이 NULL의 정렬을 보장할 수 있도록 ‘NVL(컬럼, 대체값)’을 사용하여 NULL을 가공해준 후, 정렬해 주어야 한다.



*. AVG의 문자열 자동 CASTING

평균을 계산할 때 인수에 문자열을 넣더라도 자동으로 계산해준다.

(EX) AVG('008')  = AVG(CAST '008' AS NUMBER)

 



*.VIEW –가상의 테이블

 

-VIEW는 가상의 테이블이다. 따라서 실제 데이터를 저장하고 있지 않기 때문에 기억 장치 용량을 절약 할 수 있다.

-자주 사용하는 SELECT문을 뷰로 만들어서 반복적으로 사용할 수 있다.

-뷰는 ORDER BY구를 사용할 수 없다. 테이블과 동일하게 ‘행에는 순서가 없다.’

-뷰를 삭제하려면 DROP VIEW를 사용한다.(사용할 경우 거의 없음)

-키워드 AS는 절대 생략해서는 안된다. 뷰의 AS는 컬럼이나 테이블에 별명을 붙이는 AS와는 달라서 생략하면 에러로 처리된다.

-뷰 위에 뷰를 겹치는 것은 문제는 되지 않지만 되도록 사용하면 안된다.

 

 

(+)VIEW를 사용하는 이유


-데이터를  한정적으로 허용하는 경우에 사용한다. (위의 그림 처럼 공용의 데이터만 허용하길 원할경우 공용데이터를 뷰로 따로 빼놓아 사용한다.)

-반복적으로 SELECT해와야 하는 데이터가 있는 경우.중복 쿼리를 줄이고 해석이 용이해지기 위해 사용한다.



*.스칼라 서브쿼리

-‘스칼라’란 ‘단일’의 의미로 ‘1행 1열 값만을 반환한다.

-이해가 잘 가지 않을 수도 있는데 예로 들면 ‘10’아니 ‘서울’ 처럼 하나의 값을 의미한다.



*.special tip

*.서브쿼리 사용과 with문 사용의 차이점

(A)서브쿼리

SELECT * FROM(SELECT ‘A’ AS CHAR_NM FROM DUAL)

UNION ALL

SELECT ‘A’ AS CHAR_NM FROM DUAL

(B)WITH문

WITH SPACE_DATA(CHAR_NM) AS(

SELECT ‘A’ AS CHAR_NM FROM DUAL

)

SELECT CHAR_NM FROM SPACE_DATA

UNION ALL

SELECT CHAR_NM FROM SPACE_DATA

è 같은 구문을 서브쿼리로 반복해서 사용하면 같은 데이터를 가져오기 위해 ‘매번’ 데이터를 조회해서 가져오기 때문에 메모리에 부담을 주게된다. 반면에, 반복해서 사용해야 할 데이터를 WITH문으로 만들어 놓고 SELECT로 반복해서 불러오는 경우는 WITH문으로 ‘한번’ 데이터를 조회하여 가지고 있다가 여러 번 가져다 사용한다.



*.함수

1) 절대값을 구하는 함수 – ABS

‘ABS(수치)’처럼 사용한다. ABS의 인수가 NULL인 경우는 결과도 NULL이다.

 

2)나머지를 구하는 함수 – MOD

‘MOD(피제수, 제수)’처럼 사용한다. MOD는 MODULO의 약자이다. 소수값이 들어가면 ‘나머지’라는 개념이 없어지기 때문에 MOD함수를 사용할 수 있는 것은 필연적으로 정수형 컬럼으로 제한한다.

이 MOD함수는 MSSQL(=SQL SERVER)는 사용할 수 없다.

대신 MSSQL은 %를 사용한다.

 

3)반올림 함수 –ROUND

‘ROUND(대상 값, 반올림 자릿수)’처럼 사용한다. ROUND(M, N)은 M값을 N자릿수까지 반올림한다는 의미이다.

 

4)문자를 더하는 함수 - ||

실무에서 ‘가나’+’다’+’라마’ = ‘가나다라마’같이 문자열을 연결해야 할 경우에 사용한다.

SQL에서 는 ‘가나’||’다’||’라마’로 구현할 수 있다. 결합하는 문자열이 NULL이면 결과도 NULL이 된다. ||함수는 MSSQL(=SQL SERVER), MYSQL에서는 사용할 수 없다.

MSSQL에서는 ‘+’기호를 사용해 ‘가나’ + ‘다’+’라마’ 이렇게 구현할 수 있고,

MYSQL에서는 CONCAT함수를 사용해 CONCAT(‘가나’,’다’,’라마’)처럼 사용할 수 있다.

 

5)문자열의 길이를 반환하는 함수 – LENGTH

‘LENGTH(문자열)’처럼 사용한다. 이 함수는 MSSQL(=SQL SERVER)에서는 ‘LEN(문자열)’로 사용된다.

이때, 주의할 점은. 영문자는 1글자에 1바이트인 반면에 한글이나 한자는 2바이트 유니코드이기 때문에 2바이트 이상을 차지하여 MYSQL의 LENGTH처럼 바이트 수를 세는 경우 LENGTH(‘김’)의 문자열 길이 반환값은 1이 아닌 2가된다. 이처럼 같은 LENGTH라는 함수도 DB의 종류에 따라 다른 동작을 보인다.

 

6)소문자로 변환하는 함수 – LOWER

‘LOWER(영문자열)’처럼 사용된다. LOWER함수는 알파벳에만 적용되는 함수로 모두 소문자로 변환한다.알파벳 이외의 문자열에는 효과가 없다,

 

7)문자열 치환 함수 –REPLACE

‘REPLACE(대상문자열, 대상값, 치환값)’처럼 사용한다. 예를 들어 설명하자면 REPLACE(‘김철수’, ‘김’, 박’) è 결과는 ‘김철수’가 ‘박철수’로 변경 되었다.

 

8)문자열 잘라내는 함수 – SUBSTRING

‘SUBSTRING(문자열 FROM 시작위치 FOR 잘라낼 문자 수)’처럼 사용한다.

예를 들어, SUBSTRING(‘김철수’ FROM 1 FOR 1) è ‘김철수’에서 1부터 1글자만을 잘라내면 ‘철수’만 남는다. 이 같은 함수를

MSSQL(=SQL SERVER)에서는 SUBSTRING(문자열, 시작위치, 잘라낼 문자 수)

ORACLE, DB2에서는 SUBSTRING(문자열, 시작위치, 잘라낼 문자 수)  처럼 사용한다.

 

9)대문자로 변환하는 함수 – UPPER

‘UPPER(영문자열)’처럼 사용한다. UPPER함수는 알파벳에만 대문자로 변환해준다.

 

10)현재 날짜를 반환해주는 함수 – CURRENT_DATE

‘CURRENT_DATE’처럼 사용한다. 이 함수는 이SQL 쿼리를 수행한 날을 반환 값으로 출력한다.

인수가 없기 때문에 괄호가 필요 없다. 이 같은 함수를

MSSQL(=SQL SERVER)에서는 CURRENT_TIMESTAMP

ORACLE과 DB2는 현재 날짜를 더미 테이블로 만들어서 가져다가 사용해야 한다.

하지만 ORACLE과 DB2는 각각 더미 테이블을 만드는 방법이 조금 다르다.

ORACLE에서는 SELECT CURRENT_DATE FROM DUAL

DB2에서는 SELECT CURRENT DATE FROM SUSIBM.SYSDUMMY1 처럼 사용한다.

 

11)현재 시간을 반환하는 함수 – CURRENT TIME

‘CURRENT_TIME’처럼 사용한다. 이 함수는 이SQL 쿼리를 수행한 날을 반환 값으로 출력한다.

인수가 없기 때문에 괄호가 필요 없다. 이 같은 함수를

MSSQL(=SQL SERVER)에서는 CURRENT_TIME를 CAST해서 날짜형으로 변환하여 사용한다.

(EX)SELECT CAST(CURRENT_TIME AS TIME) AS CUR_TIME;

ORACLE과 DB2는 현재 시간을 더미 테이블로 만들어서 가져다가 사용해야 한다.

하지만 ORACLE과 DB2는 각각 더미 테이블을 만드는 방법이 조금 다르다.

ORACLE에서는 SELECT CURRENT_TIME FROM DUAL

DB2에서는 SELECT CURRENT TIME FROM SUSIBM.SYSDUMMY1 처럼 사용한다.

 

12)현재 일시를 반환하는 함수 – CURRENT_TIMSTAMP

CURRENT_TIMESTAMP는 CURRENT_DATE 와 CURRENT_TIME을 더한 것이다. 따라서 현재 날짜와 현재 시간을 같이 알 수 있다.

ORACLE과 DB2는 현재 일시를 더미 테이블로 만들어서 가져다가 사용해야 한다.

하지만 ORACLE과 DB2는 각각 더미 테이블을 만드는 방법이 조금 다르다.

ORACLE에서는 SELECT CURRENT_TIMESTEMP FROM DUAL

DB2에서는 SELECT CURRENT TIMESTAMP FROM SUSIBM.SYSDUMMY1 처럼 사용한다.

 

13)날짜 데이터에서 일부분을 추출하는 함수 – EXTRACT

‘EXTRACT(날짜요소 FROM 날짜)’ 처럼 사용한다. 이 함수는 날짜 데이터에서 ‘년’,’월’ 또는 ‘시간’이나 ‘초’만을 추출할 때 사용한다. 따라서 반환 값은 날짜 형이 아닌 숫자 형이 된다.

(EX)SELECT EXTRACE(YEAR FROM CURRENT_TIMSTAMP) AS YEAR;

이 같은 함수를

MSSQL(=SQL SERVER)에서는 DATEPART(날짜요소, 날짜)함수를 사용한다.

ORACLE과 DB2는 더미 테이블로 만들어서 가져다가 사용해야 한다.

하지만 ORACLE과 DB2는 각각 더미 테이블을 만드는 방법이 조금 다르다.

ORACLE에서는 SELECT EXTRACE(YEAR FROM CURRENT_TIMSTAMP) AS YEAR FROM DUAL

DB2에서는 SELECT EXTRACE(YEAR FROM CURRENT_TIMSTAMP) AS YEAR FROM SYSIBM.SYSDUMMY1; 처럼 사용한다.

 

14) 데이터 형 변환 함수 – CAST

‘CAST(대상 값 AS 변환할 데이터 형)’처럼 사용한다.

 

15) NULL을 값으로 변환하는 표준 함수 – COALESCE

‘COALESCE(컬럼1, 데이터1, 컬럼2…)’ 처럼 사용한다. ()괄호 안에 여러 개의 인자가 들어가며 2개씩 쌍으로 의미를 둔다.

예를 들어 SELECT COALESCE(COL1, ‘NULL입니다’) 는 컬럼COL1의 데이터를 처음부터 끝까지 읽어가며 NULL이 있는 데이터를 ‘NULL입니다’ 로 바꿔준다.

 

이와 같이 NULL을 포함하는 열이라도 COALESCE로 별도 값으로 변환 후에 다른 함수나 연산에 입력 함으로써 결과가 NULL이 되는 것을 방지할 수 있다. 또한 이 함수는 어떤 DB의 종류에도 상관없이 작동한다. 추가적으로 NULL 체크 함수에는 DB2의 IFNULL()과 MSSQL의 ISNULL()이 있다.(NVL()은 표준이 아니다.)

 

여기서 주의 할 점은,

SELECT COALESCE(문자열 데이터형 컬럼, ‘AAA’) AS A_NM FROM ~에서

원하는 컬럼의 데이터 값이 NULL이면 ‘AAA’를 넣으라는 의미이다.

이때, ‘COALESCE(컬럼X, 값Y)’형태에서 컬럼X의 데이터 형은 NUMBER인데 넣을 값Y는 문자열 데이터 일 수 없다.

즉, 컬럼 데이터 형과 들어갈 값 데이터 형이 일치해야 한다.

 

 

*. 술어 (=반환값이 진릿값인 함수)

1) 문자열 부분 일치를 검색하는 술어 – LIKE 술어

LIKE절에서 사용할 수 있는 ‘%’는 ‘0’문자 이상의 임의 문자열을 의미하는 특수기호다.

(EX)

(a)SELECT * FROM SAMPLETABLE WHERE P_NM = ‘김성은’ è 컬럼P_NM데이터가 ‘김성은’인 행

(b)SELECT * FROM SAMPLETABLE WHERE P_NM LIKE ‘김’||’%’ è 컬럼P_NM데이터가 ‘김’~인 행

(c)SELECT * FROM SAMPLETABLE WHERE P_NM LIKE ‘%’||’성’||’%’ è 컬럼P_NM데이터가 ~’성’~인 행

 

참고로 ‘%’EOTLSDP ‘_’(언더바)를 사용할 수도 있지만, 언더바는 %와는 다르게 ‘임의의 1문자’를 의미한다. 따라서

SELECT * FROM SAMPLETABLE WHERE P_NM LIKE ‘abc__’ è 컬럼P_NM데이터가 다섯글자이고, abc~인 행을 의미한다.

 

2)범위를 검색하는 술어 – column BETWEEN a AND b

‘대상컬럼 BETWEEN a AND b’처럼 사용하며 a부터 b까지를 의미하므로 양극단 값까지 포함한다.

따라서 양극단의 값을 제외하고 싶다면 <와 >를 사용하면 된다.

 

3) OR의 간략 버전 - IN술어

‘IN(값1,값2,값3…)’처럼 사용한다.

기존의 다중 OR문은

SELECT * FROM GOODS

WHERE PRICE=’10’ OR PRICE =’20’ OR PRICE =’30’…

이렇게 가독성이 떨어진다.

이때는 IN술어를 사용하여

SELECT * FROM GOODS WHERE PRICE  IN(’10’, ‘20’, ‘30’…) 이렇게 깔끔하게 바꿀 수 있다.

IN술어의 부정은 NOT IN을 사용한다.

 

 

*. 조건 분기식 - CASE식

-CASE문은 두가지 스타일로 작성 할 수 있다.

첫번째로, 검색 CASE를 사용한 경우.두번째, 단순CASE를 사용한 경우.

1)    검색 CASE è여러 종류의 컬럼에 다양한 조건을 걸 수 있다.

SELECT goods_classify = ‘의류’ THEN ‘A:’ || goods_classify

WHEN goods_classify = ‘사무용품’ THEN ‘B:’ || goods_classify

WHEN goods_classify = ‘주방용품’ THEN ‘C:’ || goods_classify

ELSE null AS classify_play FROM GOODS

2)    단순 CASE1è단일한 컬럼에 대해서만 조건을 걸 수 있다.

SELECT goods_classify WHEN ‘의류’ THEN ‘A:’ || goods_classify

WHEN ‘사무용품’ THEN ‘B:’ || goods_classify

WHEN ‘주방용품’ THEN ‘C:’ || goods_classify

ELSE null AS classify_play FROM GOODS

 

-CASE식의 ELSE구는 생략할 수 있지만 명시적으로 기술해야 한다.

-CASE식의 마지막 END는 생략 불가능하기 때문에 주의 해야 한다.



*. 집합연산(행 더하기) – UNION & UNION ALL

<주의할점>

1) 연산 대상이 되는 행의 컬럼 수가 같아야 한다.

2) 연산 대상이 되는 행의 컬럼 순서도 같아야 한다.

3)덧셈 대상이 되는 행의 컬럼이 같은 데이터 형이여야 한다.

4)ORDER BY구는 마지막에 하나만 가능하다.

<UNION과 UNION ALL의 차이점>

UNION은 중복된 값을 제거하지만 UNION ALL은 중복 행을 포함한다.

즉, UNION은 전체 컬럼을 DISTINCT한것과 같다.



*.결합 – JOIN

 

-JOIN의 기본적인 서브쿼리 형식

 


-쿼리의 확장성을 위해 되도록 RIGHT가 아닌 LEFT JOIN만 쓰기로 한다!

- 첫번째 사진 위에 있는 쿼리나 두번째 사진에 있는 쿼리처럼 CODE테이블을 DUMMY로 감싸서 가져오면 절대 안된다. 하단이 옳은 형태이다. 이미 CODES테이블에서 ON에 명시되어 있는 조건 값을 가져와서 명시하고 JOIN뒤에 테이블을 선언 함으로써 모든 값을 가져오기 때문에 B테이블을 더미로 감싸지 않고도 SELECT절에서 A테이블의 값과 B테이블의 값을 모두 가져 올 수 있다.

 

 

 


(EX1) INNER JOIN

테이블 A JOIN 테이블B = 테이블 A INNER JOIN 테이블B와 같다.

(inner join에서 inner은 생략 가능하다. 하지만 명시적으로 적어주는 것이 좋다.)

SALES테이블과 CODES테이블이 있다. CODES테이블에 있는 코드 값으로 ITEM의 이름을 가져오고자 할 때

상단의 쿼리처럼 INNER JOIN의 조인조건ON을 사용해도 되고 아래 쿼리처럼 WHERE의 절대 조건을 사용 해도 결과는 같다.

하지만 명시적으로 위에 쿼리처럼 조인조건(ON)과 절대조건(WHERE)을 사용하는 것이 바람직하다.


 

(EX2) LEFT JOIN

 

SALES테이블 A와 CODES테이블 B가 있다. 코드테이블B의 CD값으로 SALES테이블A의 ITEM이름을 뽑아낼 수 있다. 그래서 JOIN의 ON조건에 A의ITEM_CD = B의CD값이 들어가게 된다.

이때, 순수한A의 값만을 뽑아 내고 싶다면 LEFT EXCLUDING JOIN을 하게 되는데

이것은 A에만 있고 B에는 없는 '순수A'값을 뜻한다.

따라서, 쿼리는 A LEFT JOIN후에 B가 NULL인 것을 뽑게 된다.

 


(EX3)LEFT OUTER JOIN

(EX2)처럼 A LEFT JOIN B하면 A에는 있고 B에는 없는 경우 B에 관련된 값에 NULL이 들어 가게 되므로 B에 NULL을 빼준다.


*. 세로로 나열되어 있는 데이터를 가로로 나열하는 방식 - PIVOT

-pivot에서 group by의 대상 컬럼이 pivot의 dynamic한 집약의 대상이 되곤 한다.

 

(EX) select * from goods

1)GOODS테이블의 모든 데이터를 출력

 


2)피봇 쿼리 실행


3) 피봇 결과

행으로 존재하던 데이터들이 열 데이터로 변했다.

 

4)또다른 예시


 

*. 중분류의 합(소계), 대분류의 합(합계)을 집약해 내는 문법 - ROLL UP

-아래는 기본적으로 가지고 있는 데이터의 내용이다.

(SALES테이블에는 ITEM에 대한 데이터가 있고, CODES테이블에는 해당하는 코드의 상품명이 있다.

LEFT OUTER JOIN을 하여 CODES테이블에서 ITEM_NM을 추출해 낼 수 있었다.)


 

 

(ⅰ) 일반적으로 UNION으로 ROW를 이어 붙이는 가장 미련한...(비용 高)

 

(ⅱ) WITH문을 이용한 방법. (비용 中)


 

 

 

(ⅲ) ‘ROLL UP(Column)’사용 방법


*****이 세 가지 방법을 NODE비용 측면에서 보자.*****

위에서 아래 순으로 첫 번째, 두 번째, 세 번째 방법을 사용한 쿼리의 NODE비용을 나타낸 것이다.

* 결과.

첫 번째 방법에서 중복된 쿼리를 UNION ALL을 사용하여 이어 붙임으로써 세 번의 FULL SCAN이 실행됐다.

è 따라서 비용이 많이 든다.

두 번째 방법에서는 중복 쿼리를 WITH문으로 따로 빼서 사용 함으로써 FULL SCAN이 두 번으로 줄었다 è 따라서 비용은 감소 되었으나 여전히 많이 든다.

세 번째 방법에서는 ROLL UP문법을 사용해 합계를 내었다. 지금 예제로는 WITH구문사용한것과 ROLL UP사용한 것이 차이 없게 보이지만 대용량의 데이터를 집약해보면 ROLL UP을 사용한 것이 제일 비용이 적게 든다는 것을 알 수 있다.

 

 

*. 윈도우 함수

-group by와 윈도우 함수의 차이>

group by : 결과(select절) 전에 집약하는 선처리 작업을 의미한다. 쉽게 말해서 집약 후 select한다.

윈도우 함수 : group by의 사용 유무에 상관 없이 select 된 결과를 가지고 계산하는 함수이다.

아래는 윈도우 함수 중 rank()에 관한 예시다.

-윈도우 함수는 그룹을 구분 짓지만 집약 되지는 않는다.

 

1) 랭킹(순위)을 산출하는 윈도우 함수 - RANK함수

-PARTITION BY를 통해 구분된 부분집합을 ‘윈도우’라고 한다.

-각 윈도우들은 절대로 공통 부분을 가지지 않는다. 이 특징은 GROUP BY구의 부분집합 조건과 같다.

-예시에서 PARTITION BY는 순위를 정할 대상 범위를 설정한다.

-예시에서 ORDER BY는 어떤 열을 어떤 순서로 순위를 정할지 지정한다.

-PARTITION BY는 GROUP BY를 사용 하지 않고도 그룹별로 모아놓을 수 있다.(집약은 하지 않는다.)

-PARTITION BY구에는 GROUP BY구가 가진 집약 기능이 없기 때문에 RANK함수를 사용한 결과는 원래 테이블의 행 수가 줄지 않고 출력 된다.

(EX) 월별로 매출액이 가장 높은 순으로 순위를 매겨라.


 

2) 순위에 상관없이 연속 번호를 부여하는 함수 - ROW_NUMBER 함수

-표준은 ROW_NUMBER(), 오라클에서는 ROW_NUM()


SQL 결과는 동일하다. è 

 

<SPECIAL TIP!!!!!!>

*. ROW_NUMBER를 응용한 페이징 처리

ROW_NUMBER로 ROW마다 번호를 매기고 한 페이지에 게시물을 10개씩 넣는다고 가정하고 ROW를 10씩 순서대로 끊는다.

ROW번호 1~10, 11~20, 21~30이런 순으로 ROW번호를 가지고 ROW를 나눠 끊는다.


 

*. GROUPING SETS

-GROUPING SETS(<그룹화 집합> [,...n])를 GROUP BY 목록으로 지정하는 것은 각각 그룹화 집합 중 하나를 해당 GROUP BY 목록으로 갖는 쿼리의 UNION ALL과 동일하다.


 

 

-GROUPING SETS(컬럼1, 컬럼2) = GROUPING SETS((컬럼1), (컬럼2))

 

-grouping sets((YYYYMM), (ITEM_CD), ()) 에서 grouping sets(())의 의미 


*. 재귀 쿼리 CONNECT BY

-재귀호출 = 자기 자신을 또 다시 호출 하는 방식을 말한다.

-아래에 나오는 예제처럼 row가 트리 구조 순으로 나열 되어 출력 되기 위해서는 반드시 재귀 쿼리를 사용 해야 한다.

-오라클에서는 start with ~ connect by ~를 사용하고 다른 디비에서는 connect by를 허용하지 않기 때문에 with문으로 선언한 테이블을 inner join하여 다시 한번 재귀호출 해야 한다.

 

-상위 메뉴를 PARENT_MENU로 가지는 데이터를 트리 구조 순으로 출력 하고자 할 때,

오라클에서는 START WITH ~ CONNECT BY PRIOR ~ 를 사용한다.

-'START WITH 상위메뉴 시작의 기준

CONNECT BY PRIOR 상위메뉴와 하위메뉴의 연결 조건'형태로 사용한다.


-트리구조 순서 나열 기준은 ‘SIBLINGS’ 키워드를 사용한다.



'DB' 카테고리의 다른 글

[SQL] - 계층 구조 쿼리는 성능을 위해 RECURSIVE하게 짜지 말자  (0) 2021.01.01