SQL 기본

DDL

• DB 를 구성하고 있는 다양한 객체(사용자, 테이블, 인덱스, 뷰, 트리거, 프로시저, 사용자 정의 함수 등)를 정의/변경/제거하는 데 사용함

DML

• 테이블의 데이터를 입력/수정/삭제/조회하는 역할

• 입력/수정/삭제는 SQL 실행 후 영구적으로 저장(COMMIT)하거나 수행 취소(ROLLBACK)할 수 있음

• SELECT 시 Alias 사용 가능
• Alias 시 컬럼이름 ‘’ 붙일 필요 없이 그냥 한글 써도 됨
• 테이블 명에 alias를 붙일때는 as 없이 그냥 뒤에 사용할 테이블 이름 쓰면됨

SELECT 
 tp.POPLTN_SE_CD AS 성별구분코드 ,
 tp.AGRDE_SE_CD AS 연령대구분코드 ,
 SUM(tp.POPLTN_CNT) FROM TB_POPLTN tp
GROUP BY tp.POPLTN_SE_CD, tp.AGRDE_SE_CD;

• 합성 연산자 사용. 여러 개의 컬럼을 한 컬럼으로 조회하고 싶을 때, || 를 이용해서 이어붙일 수 있음

Update from select

UPDATE 
    [테이블A]
SET
    [테이블A].필드 = [테이블B].필드
FROM
    [테이블A],[테이블B]
WHERE 
    [테이블A].id = [테이블B].id
출처: https://shakddoo.tistory.com/entry/Mysql-Select결과를-Update-문에-반영하기 [겨울팥죽 여름빙수:티스토리]

update `user`, (select c._corporation_id, total.user_id from company c join (
select user_id, company_id from broker
	union
select user_id, company_id from driver
	union
select user_id, company_id from caller) as total on c.company_id = total.company_id
where c._corporation_id is not null) B
set user.membership_corporation_id = B._corporation_id
where user.user_id = B.user_id

Insert from select

insert into membership_corporation (
membership_corporation_id , business_type, business_email, business_number, 
address_base, address_detail, address_zip_code,
representative_name, representative_mobile, corporation_name,
join_date_time, created_date_time, contact_name,
bank_name, account_name, account_number ) 

SELECT `_corporation_id` , _business_type, `_email`, business_reg_num, 
address, address_detail, zip_code,
representative , '', name , 
reg_dt , NOW(), contact_name ,
`_bank_name` , `_account_name` , `_account_number` 
from company c where c.`_corporation_id` is not null 
and c.business_reg_num not in (select business_number from membership_corporation); 

TCL

• DML 문의 입력, 수정, 삭제를 실행하면 DB에 곧바로 적용되지 않고 커밋 명령을 실행해야만 DB에 최종 적용됨. 데이터를 변경한 후 커밋하지 않으면 ROLLBACK 으로 데이터의 변경을 취소할 수 있음

• 트랜잭션의 특성

데이터 입력/수정/삭제 후 커밋 혹은 롤백 전 데이터 상태

• 최종 적용된 상태가 아니고 메모리에만 적용된 상태이므로 변경 이전 상태로 복구가 가능

• 입력/수정/삭제를 실행한 사용자는 SELECT 문으로 입력/수정/삭제 결과를 확인할 수 있다

• 입력/수정/삭제를 실행한 사용자 외 다른 사용자는(작업자가 해당 내용을 커밋하지 않는 이상) SELECT 문으로 입력/수정/삭제 결과를 확인할 수 없다

• 변경된 행은 락이 설정되므로 다른 사용자가 변경할 수 없다.

데이터 입력/수정/삭제 후 커밋 실행 후 상황

• 데이터의 입력/수정/삭제 내역이 DB에 완전히 반영

• 이전 데이터는 영원히 잃어버리게 됨

• 변경 사용자 및 변경 사용자가 아닌 다른 모든 사용자는 SELECT 문으로 입력/수정/삭제 결과를 볼 수 있다.

• 관련된 행에 대한 락이 풀리고, 모든 사용자들이 행을 조작할 수 있게 됨

• SAVEPOINT : 하나의 트랜잭션에서 여러 개의 입력/수정/삭제가 발생할 수 있는데 커밋과 롤백으로 여러 개의 변경된 내용을 취소하거나 적용할 수 있음. SAVEPOINT를 활용하면 해당 지점(SAVEPOINT를 선언한)까지의 데이터 변경만 유지하고 그 이후의 데이터 변경은 취소할 수 있음

커밋/롤백과는 상관없이 트랜잭션 적용 처리가 일어나는 상황

• 오라클 DBMS 기준으로 CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE 등 DDL 문장을 실행하면 그 실행 전과 실행 후 시점에 자동으로 커밋이 됨
• DML 문 실행 후 커밋하지 않고 DDL 문을 실행하면 DDL 문 실행 전에 DML 문의 변경 사항이 자동으로 커밋됨

• 또한 DB 에 대한 접속을 종료하면 자동으로 이전 DML의 변경사항이 커밋이 됨

WHERE절

연산자의 종류

• 비교 연산자 : =, >, >=, <, <=

CHAR vs VARCHAR2

• CHAR는 고정 길이 문자열이고 VARCHAR2는 가변 길이 문자열임.

• CHAR(5)일 때, ‘SQLD’만 저장하면 뒤에 공백 붙어서 저장됨
• CHAR 데이터 칼럼끼리 비교할 때 길이가 서로 달라도 공백만 다르다면 같다고 판단함

• VARCHAR(5)일 경우 ‘SQLD’ 저장하면 그대로 ‘SQLD’로 저장됨

• 공백을 주의하여 비교해야 함

ROWNUM

• SQL 예약어로, 데이터가 조회될 때 출력되는 행의 순번을 의미하는 Pseudo 컬럼

• 이 ROWNUM을 WHERE 절의 조건으로 이용하여 결과 행의 건수를 제한할 수 있음.

• SQL 문 조회 시 ROWNUM에 명시한 값만큼만 조회를 하고 조회 연산이 멈춤. 이러한 SQL 문 조회처리를 부분 범위 처리라고 함

함수

단일행 함수

데이터 형변환의 방식

• 명시적 형변환 : 데이터 형변환 함수로 데이터형을 변환하도록 명시해 주는 경우

SELECT 
	TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
	TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
	TO_CHAR(10.25, '$999,999,999,99') -- 숫자형을 문자형으로 변환. $10.25
	TO_CHAR(12500, 'L999,999,999'), --숫자형을 문자형으로 변환 $12,500

• 암시적 형변환 : DBMS가 자동으로 데이터형을 변환하는 경우
• SQL 문 작성시 문자형과 숫자형을 서로 비교할 때 명시적으로 형변환을 하지 않으면 DBMS 내부에서 자동으로 2개의 각기 다른 데이터형을 동일한 데이터형으로 변환한 후 연산을 처리하게 됨
• 이러한 상황을 “암시적 형변환이 일어났다”고 함
• 암시적 형변환은 인덱스를 정상적으로 스캔하지 못할 수 있기에 명시적 형변환이 좋음!

단일행 CASE 표현의 종류

• 특정 값에 대해서 조건에 따라 각기 다른 값을 리턴하도록 하는 것을 CASE 표현이라고 함

• 이러한 CASE 표현은 CASE 문의 사용 혹은 DECODE 함수를 이용하여 구현할 수 있음

SELECT
	CASE WHEN A.INDUTY_CL_SE_CD = 'ICS001'
	THEN '대'
				WHEN A.INDUTY_CL_SE_CD = 'ICS002'
				THEN '중'
				WHEN A.INDUTY_CL_SE_CD = 'ICS003'
				THEN '소'
				ELSE ''
	END AS "업종분류구분명"
	FROM TB_DINTUY_CL_SE A;

NULL 관련함수

• NULLIF(A, B) : A, B 의 문자열이 다르면 첫번째 문자열 출력. 두 문자열이 같다면 NULL을 출력

• NVL(A, B) : A의 값이 NULL이면 B를 출력

• COALESCE(A, B, C) : NULL이 아닌 첫번째 인자를 출력

GROUP BY, HAVING 절

집계함수

GROUP BY

HAVING 절

• HAVING 절은 일반적으로 GROUP BY 절의 뒤에 위치함

• HAVING 절은 WHERE 절과 유사한 기능을 하지만, WHERE 절이 테이블에서 추출할 행을 제한한다고 하면 HAVING 절은 그룹핑한 결과집합에 대한 조건을 주어 추출할 집계 데이터를 제한(필터링)하는 역할을 함

SELECT A.POPLTN_SE_CD, A.AGRDE_SE_CD, SUM(A.POPLTN_CNT) AS "인구수합계"
FROM TB_POPLTN A
WHERE A.STD_Y = '202010'
GROUP BY A.POPLTN_SE_CD, A.AGRDE_SE_CD
HAVING SUM(A.POPLTN_CNT) < 1000000;

CASE WHEN 문 사용

• CASE WHEN 문을 이용하여 결과집합을 출력할 때 사용자가 원하는 조건에 따라 분기처리하여 결과집합을 출력할 수 있음

SELECT A.SUBWAY_STATN_NO
     , SUM(CASE WHEN A.TK_GFF_SE_CD = 'TGS001'
                THEN A.TK_GFF_CNT ELSE 0 END) AS "승차인원수합계"
     , SUM(CASE WHEN A.TK_GFF_SE_CD = 'TGS002'
                THEN A.TK_GFF_CNT ELSE 0 END) AS "하차인원수합계"
     , SUM(CASE WHEN A.BEGIN_TIME = '0800' AND A.END_TIME = '0900' AND A.TK_GFF_SE_CD = 'TGS001'
                THEN A.TK_GFF_CNT ELSE 0 END) AS "출근시간대승차인원수합계"
     , SUM(CASE WHEN A.BEGIN_TIME = '0800' AND A.END_TIME = '0900' AND A.TK_GFF_SE_CD = 'TGS002'
                THEN A.TK_GFF_CNT ELSE 0 END) AS "출근시간대하차인원수합계"
     , SUM(CASE WHEN A.BEGIN_TIME = '1800' AND A.END_TIME = '1900' AND A.TK_GFF_SE_CD = 'TGS001'
                THEN A.TK_GFF_CNT ELSE 0 END) AS "퇴근시간대승차인원수합계"
     , SUM(CASE WHEN A.BEGIN_TIME = '1800' AND A.END_TIME = '1900' AND A.TK_GFF_SE_CD = 'TGS002'
                THEN A.TK_GFF_CNT ELSE 0 END) AS "퇴근시간대하차인원수합계"
     , SUM(TK_GFF_CNT) AS "승하차인원수합계"
  FROM TB_SUBWAY_STATN_TK_GFF A
 WHERE A.STD_YM = '202010'
GROUP BY A.SUBWAY_STATN_NO
HAVING SUM(CASE WHEN A.TK_GFF_SE_CD = 'TGS001' --승차인원수
                THEN A.TK_GFF_CNT ELSE 0 END) >= 1000000
    OR SUM(CASE WHEN A.TK_GFF_SE_CD = 'TGS002' --하차인원수
                THEN A.TK_GFF_CNT ELSE 0 END) >= 1000000
;

집계 함수와 NULL

• NULL 은 집계 함수의 계산에 포함되지 않고 집계 대상에서 제외됨. COUNT(*) 만이 NULL을 포함해서 갯수를 셈

ORDER BY 절

• NULL의 정렬 — 오라클은 ORDER BY 절에 기재한 칼럼의 값이 NULL이면 가장 큰값이라고 인식함

• ORDER BY 절에 칼럼명 대신에 SELECT 절에서 사용한 앨리어스 명이나 칼럼 순서를 나타내는 정수도 사용 가능함!
• ORDER BY 1 : 첫번째 컬럼 기준으로 정렬

• ORDER BY 시, 컬럼의 필드를 참조해서 정렬하면 그 값 그대로를 정렬하는 것이고 앨리어스 명이나 컬럼 순서 정수를 사용해서 정렬하면 함수가 적용된 값에 대해 정렬하게 됨

SELECT 문의 실행 순서

• SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY 까지 SELECT 문을 구성하는 모든 문법이 나왔음

• SELECT 문 실행 시 오라클 DBMS 내부에서는 아래 순서로 수행을 하게 됨
1. FROM 절 — 조회 테이블을 참조함
2. WHERE 절 — 조회 대상 행을 조회함
3. GROUP BY — 대상 행을 그룹화함
4. HAVING — 그룹화한 값에서 조건에 맞는 것을 출력
5. SELECT — SELECT 절에 기재한 칼럼이나 식을 계산
6. ORDER BY — 출력되는 결과집합을 정렬

• ORDER BY 절을 수행할 때 SELECT 절에 기재하지 않은 칼럼을 기준으로도 정렬이 가능함

SELECT A.SUBWAY_STATN_NO, A.LN_NM, A.STATN_NM FROM TB_SUBWAY_STATN A
WHERE A.SUBWAY_STATN_NO = '000607';

• ORDER BY 절 수행 시 SELECT 문에 GROUP BY 절을 사용한 경우 반드시 SELECT 절에 기재한 칼럼 혹은 표현식을 ORDER BY 절에 기재해야 함

• ORDER BY 절에 기재하는 컬럼이 SELECT절에 존재하는 칼럼이나 표현식이 아니라면 에러가 발생함 → not a GROUP BY expression

SELECT A.AGRDE_SE_CD
     , SUM(A.POPLTN_CNT) AS SUM_POPLTN_CNT
  FROM TB_POPLTN A
 WHERE A.STD_YM = '202010' --2020년 10월 기준
   AND A.POPLTN_SE_CD IN ('M', 'F') --'남', '여'
GROUP BY A.AGRDE_SE_CD
ORDER BY SUM(A.POPLTN_CNT) DESC
;