[SQL] SQL 기본과 활용

1. 관계형 DB

- DB종류: 계층형(1:N), 네트워크형(1:N or M:N), 관계형(집합연산과 관계연산)

• 집합연산: 합집합(UNION), 차집합(MINUS), 교집합(INTERSECTION), 곱집합(카티안 프로덕트)
• 관계연산: 선택연산, 투영연산, 결합연산, 나누기 연산

 

- 구조: 릴레이션은 DBMS에서 테이블로 최종적으로 생성된다, 행(튜플, 갯수: 카디널리티)과 열(칼럼, 필드, 속성, 갯수: 차수)으로 구성된다.

 

2.  SQL

- 종류

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

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

DCL: 데이터 제어어(GRANT, REVOKE), 권한을 부여

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

 

-트랜잭션의 특징

Atomicity(원자성): All or Nothing

Consistency(일관성): 트랜잭션 이후에도 일관성이 유지되어야 한다.
Isolation(고립성): 트랜잭션 중 다른 트랜잭션이 끼어들 수 없다.

Durability(연속성): 결과는 영구적으로 보장해야 한다.

 

- 실행순서

: 파싱(Parsing)->실행(Execution)->인출(Fetch)

소프트파싱: 라이브러리 캐시에 실행된 SQL을 저장해놓아서, 파싱 단계를 생략하고 실행한다.

하드파싱: 파싱부터 진행한다.

 

- Dynamic SQL & Static SQL

• 다이나믹 SQL은 String변수에 SQL문을 문자열로 넣고 실행하는 것이다.

 

3. DDL

- CREATE

--1. 생성 방법1
CREATE TABLE EMP (
	EMPNO 	NUMBER(10) 	PRIMARY KEY,
    ENAME 	VARCHAR2(20) NOT NULL,
    SAL		NUMBER(6)	DEFAULT 0,
    CREATEDATE	DATE DEFAULT SYSDATE
);

--2. 생성방법2
CREATE TABLE EMP (
	EMPNO 	NUMBER(10),
    ENAME 	VARCHAR2(20) NOT NULL,
    SAL		NUMBER(6)	DEFAULT 0,
    DEPTNO	VARCHAR2(4) NOT NULL,
    CREATEDATE	DATE DEFAULT SYSDATE,
    CONSTRAINT EMPPK PRIMARY KEY(EMPNO)
    CONSTRAINT DEPTFK FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
    	ON DELETE CASCADE --: dept 데이터가 삭제될 때 참조되는 데이터도 삭제(OPTION)
);

--3. 테이블명 변경
ALTER TABLE EMP RENAME TO NEW_EMP;

   * ON DELETE CASCADE를 통해 참조무결성을 준수할 수 있다.  

 

- ALTER

--칼럼 추가
ALTER TABLE EMP 
  ADD (AGE NUMBER(2) DEFAULT 1);
 
--칼럼 제거
ALTER TABLE EMP
DROP COLUMN AGE; 
 
--칼럼 자료형 변경  
ALTER TABLE EMP
MODIFY (ENAME VARCHAR2(40) NOT NULL);

--칼럼 이름 변경
ALTER TABLE EMP
RENAME COLUMN ENAME TO NEW_ENAME;

--제약조건 추가
ALTER TABLE EMP
ADD CONTRAINT FK_DEPT FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)

 

- DROP

DROP TABLE DEPT;

DROP TABLE DEPT CASCADE CONSTRAINT;
--참조되어지는 제약사항(테이블:EMP)도 모두 제거

 

-VIEW

: 가상테이블, 데이터 딕셔너리에 SQL문으로 저장되고 실행시에 참조된다.

• 참조테이블이 변경되면 뷰도 변경
• 장점: 보안성을 향상시킨다, 데이터 관리가 간단한다. 하나의 테이블에 여러 뷰 생성이 가능하다
• 단점: ALTER를 이용해 뷰 변경 불가, 입력, 수정, 삭제는 못한다. 구조 변경 불가, 독자적인 인덱스를 만들 수 없다.

CREATE VIEW T_EMP AS
SELECT * 
  FROM EMP;
  
SELECT * FROM T_EMP;

DROP VIEW T_EMP;

* DDL은 묵시적으로 commit을 진행한다.

 

4. DML

- INSERT

INSERT INTO EMP(EMPNO, ENAME)
VALUES(1000, '홍길동');

INSERT INTO T_DEPT
SELECT * FROM DEPT;

--NOLOGGING
ALTER TABLE DEPT LONOGGING;
--: 로그파일 기록을 최소화시켜 입력시 성능향상을 시키는 방법, Buffer Cache 메모리영역 생략하고 기록

* default가 지정되어 있는 칼럼에 null을 삽입하면 default값이 아닌 null값이 들어간다.

 

- UPDATE

UPDATE EMP
   SET ENAME = '춘향이'
 WHERE EMPNO = 100;

 

- DELETE

DELETE FROM EMP
 WHERE EMPNO = 100;
--데이터만 삭제, 테이블 용량이 초기화되지는 않는다.

TRUNCATE TABLE EMP;
--데이터 삭제, 테이블 용량 초기화

* Delete는 from을, Truncate는 Table을 사용

* Truncate는 외래키 무결성을 확인하지 않고 테이블을 삭제한다.

 

- SELECT

--인덱스를 사용해 성능을 향상시킬 수 있다.
SELECT /*+ INDEX_DESC(A) */
  FROM EMP A;
  
--test이후 한글자만 더 있는 것을 조회한다.  
SELECT *
  FROM EMP
 WHERE ENAME LIKE 'test_'
 
 --IN 활용
 SELECT *
   FROM EMP
  WHERE (JOB, ENAME) IN (('CLEAK', 'TEST1'), ('MANAGER', 'TEST4'))
  
--인덱스 활용 시 주의사항
SELECT *
  FROM EMP
 WHERE EMPNO = '100';
-- 이 때, EMPNO가 자동으로 TO_CHAR(EMPNO)이 되면서 인덱스를 타지 못한다.
-- WHERE EMPNO = TO_NUMBER('100');으로 변경이 필요하다.

 

* NULL: 모든 비교는 알 수 없음을 반환한다. 숫자 또는 날짜를 더하면 NULL이 된다.

* NULL 관련 함수

• NVL(MGR, 0): MGR이 NULL이면 0을 반환
• NVL2(MGR, 1, 0): MGR이 NULL이 아니면 1, NULL이면 0을 반환
• NULLIF(EMPNO,  NEW_EMPNO): 두개의 값이 같으면 NULL, 아니면 첫번째 값 반환
• COALEACE(MGR, 1): NULL이 아닌 인자값을 반환한다. MGR이 NULL이 아니면 1을 반환 (COALEACE(NULL, 1, 2)인 경우 1을 반환)

* COUNT(*) vs COUNT(EMP_NO): 전자는 NULL값을 포함, 후자는 제외한 행의 수

* 숫자가 문자열보다 더 우선순위가 높다. 따라서 숫자형과 문자형을 비교 시 문자형에 암시적 형변환이 적용된다.

* 메모리에 데이터가 없으면 물리적 IO를 실행하고, 데이터가 있으면 논리적 IO를 실행한다.

5. 내장형함수

- 문자형 함수

• ACSII(), CHAR(), SUBSTR(), CONCAT(), LOWER(), UPPER(), LENGTH() or LEN()
• LTRIM(문자열, 지정된 문자), RTRIM(문자열, 지정된 문자), TRIM(문자열, 지정된 문자) : 지정된 문자 제거

* LTRIM('ABCD', 'BCD')

--> 'ABCD' : LTRIM은 맨 왼쪽부터 검사하여 두번째 파라미터로 들어온 값을 제거한다. 따라서 결과값은 'ABCD'

 

- 날짜형 함수

--년도 추출
SELECT EXTRACT(YEAR FROM SYSDATE) 
  FROM DUAL;

 

- 숫자형 함수

• ABS(), SIGN(), MOD(4,2), CEIL/CEILING(), FLOOR()
• ROUND(12.577, 1) : 12,6
• TRUNC(12.577, 1) : 12.5

 

6. DECODE, CASE

SELECT DECODE(EMPNO, 100, 'TRUE', 'FALSE')
  FROM EMP;
  
SELECT CASE
		WHEN EMP = 1000 THEN 'A'
        WHEN EMP = 1001 THEN 'B'
        ELSE 'C'
       END
  FROM EMP;

 

7. ROWNUM, ROWID, WITH

• ROWNUM: SELECT문에 논리적인 일렬번호를 부여한다.
• ROWID: 오라클DB 내에서 데이터를 구분할 수 있는 유일한 값이다.
• WITH: 서브쿼리를 사용해서 임시테이블이나 뷰처럼 사용할 수 있는 구문이다.

SELECT *
  FROM ( SELECT ROWNUM LIST, ENAME FROM EMP)
 WHERE LIST <= 5;
 
SELECT ROWID, ENAME
  FROM EMP;

 

8. DCL

GRANT SELECT, INSERT, UPDATE, DELETE
  ON EMP
  TO LSJ WITH GRANT OPTION;
--WITH GRANT OPTION: 특정 사용자에게 권한을 부여할 수 있는 권한을 부여
--	A가 B에게 권한을 부여하고, B가 C에게 권한을 부여 후 A가 권한을 취소하면 모든 권한이 취소된다.
--WITH ADMIN OPTION: 테이블에 대한 모든 권한을 부여
--	A가 B에게 권한을 부여하고, B가 C에게 권한을 부여 후 A가 권한을 취소하면 B사용자 권한만 취소된다.

REVOKE DELETE
    ON EMP
  FROM LSJ;

 

9. TCL

- COMMIT, ROLLBACK

- SAVEPOINT: 트랜잭션을 작게 분할하여 관리, 지정된 위치까지만 트랜잭션을 ROLLBACK 가능

SAVEPOINT T1;

UPDATE EMP SET ENAME='JYP' WHERE EMPNO=1003;

SAVEPOINT T2;

UPDATE EMP SET ENAME='YG' WHERE EMPNO=1004;

ROLLBACK TO T2;
--T2로 상태가 돌아간다.

UPDATE EMP SET ENAME='JYP' WHERE EMPNO=1003;

SAVEPOINT T1;

UPDATE EMP SET ENAME='LSJ' WHERE EMPNO=1003;

SAVEPOINT T1;

UPDATE EMP SET ENAME='YG' WHERE EMPNO=1004;

SAVEPOINT T2;

ROLLBACK TO T1;
--처음 T1로 상태가 돌아간다. : JYP

10. JOIN

- EQUI조인

SELECT * 
  FROM EMP
     , DEPT
 WHERE EMP.DEPTNO = DEPT.DEPTNO;
 
SELECT *
  FROM EMP INNER JOIN DEPT
    ON EMP.DEPTNO = DEPT.DEPTNO;
    
--left outer join
SELECT * 
  FROM EMP
     , DEPT
 WHERE EMP.DEPTNO = DEPT.DEPTNO(+);
 
SELECT *
  FROM EMP LEFT OUTER JOIN DEPT
    ON EMP.DEPTNO = DEPT.DEPTNO;
    
--cross join: 카테시안 곱이 발생한다.
SELECT *
  FROM EMP CROSS JOIN DEPT;

* 해쉬조인이란 테이블을 해시메모리에 적재한 후 해시함수로써 연결하는 방법이고, EQUI조인에서만 나타나는 실행계획이다. 

* NON EQUI조인이란 정확하게 일치하지 않는 것을 조인하는 것(=가 아닌 >, >=, <, <=을 사용해 조인한다.)

* UNION은 정렬을 발생시킨다. UNION ALL은 정렬을 발생시키지 않는다.

 

-  Cross Join & Natural Join

• Cross Join은 두 테이블의 곱집합, 모든 경우의 수를 반환한다. MxN 만큼 행이 생긴다. where절 추가 가능
• Natural Join은 조인시 묵시적으로 동일한 칼럼 기준으로 조인을 진행한다. where절 추가 불가능

 

- 계층형 조회

• CONNECT BY PRIOR: 조인조건
• START WITH: 시작조건

SELECT MAX(LEVEL)
  FROM EMP
 START WITH MGR IS NULL
 CONNECT BY PRIOR EMPNO = MGR;
 --4: 최대 깊이
 
SELECT LEVEL, EMPNO, MGR, ENAME, CONNECT_BY_ISLEAF --계층구조에서 최하위일 때 1로 표시
-- leaf인경우 1로 출력, 아니면 0으로 출력한다.
  FROM EMP
 START WITH MGR IS NULL
 CONNECT BY PRIOR EMPNO = MGR;
--CONNECT BY ROOT: 계층구조에서 최상위일 때 표시 
--SYS_CONNECT_BY_PATH: 계층구조의 전체 전개경로 표시

SELECT LEVEL, EMPNO, MGR, ENAME, CONNECT_BY_ISCYCLE 
  FROM EMP
 START WITH MGR IS NULL
 CONNECT BY NOCYCLE PRIOR EMPNO = MGR;

--NOCYCLE: 순환구조가 발생지점까지만 전개(무한루프여도 한번만 출력한다)
--CONNECT_BY_ISCYCLE: 순환구조 발생지점을 표시, 정상일 경우 0, 무한루프일 경우 1

* CONNECT BY PRIOR 자식컬럼 = 부모컬럼 : 부모에서 자식으로 트리구성 (Top Down)

* CONNECT BY PRIOR 부모컬럼 = 자식컬럼 : 자식에서 부모로 트리 구성 (Bottom Up)

11. 서브쿼리

• FROM절 내: 인라인 뷰
• SELECT문: 스칼라 서브쿼리
• WHERE구: 서브쿼리

SELECT *
  FROM EMP
 WHERE DEPTNO <= ALL(20, 30)
 -- 20보다 작아야 된다.
 
--EXISTS 
SELECT *
  FROM EMP, DEPT
 WHERE EMP.DEPTNO = DEPT.DEPTNO
   AND EXISTS (SELECT 1 FROM EMP WHERE SAL > 2000);
   
--연관 서브쿼리: 서브쿼리 내 메인쿼리 내의 칼럼을 사용하는 것
SELECT *
  FROM EMP A
 WHERE A.DEPTNO = (SELECT DEPTNO FROM DEPT B WHERE B.DEPTNO = A.DEPTNO);

* Access Subquery: 쿼리의 변형은 없고, 제공자 역할을 하는 서브쿼리

* View Merging: 옵티마이저가 SQL 실행을 최적화 하기 위해서 SQL문을 가공하는 작업을 의미

================View Merging이 불가능한 경우=======================

* 집합연산자(union , union all , minus, intersect)를 사용하는 경우

* Connect by 절을 사용하는 경우

* ROWNUM을 사용하는 경우

* select구에 avg,count,max,min,sum과 같은 집계함수를 사용하는 경우

* 분석함수를 사용하는 경우 

 

12. 그룹함수

--ROLLUP
--: GROUP BY 칼럼에 대해 subtotal을 만들어 준다.
SELECT DECODE(DEPTNO, NULL, '전체합계', DEPTNO), SUM(SAL)
  FROM EMP
 GROUP BY ROLLUP(DEPTNO);
 
SELECT DEPTNO, JOB, SUM(SAL)
  FROM EMP
 GROUP BY ROLLUP(DEPTNO, JOB);
 --부서별직업별 합계, 부서별합계, 전체합계를 나타낸다.
 
 
 --GROUPING
 --: 소계와 합계를 구분해준다.
 SELECT DEPTNO, GROUPING(DEPTNO), JOB, GROUPING(JOB), SUM(SAL)
  FROM EMP
 GROUP BY ROLLUP(DEPTNO, JOB);
 
 
 --GROUPING SETS
 --: DEPTNO합계와 JOB합계가 개별적으로 조회된다.
SELECT DEPTNO, JOB, SUM(SAL)
  FROM EMP
 GROUP BY GROUPING SETS(DEPTNO, JOB);
 
 
 --CUBE
 --: 결합 가능한 모든 집계 계산
SELECT DEPTNO, JOB, SUM(SAL)
  FROM EMP
 GROUP BY CUBE(DEPTNO, JOB);

 

13. 윈도우 함수

- 윈도우함수

: 행간 관계를 정의하기 위해 제공되는 함수

SELECT EMPNO, ENAME, SAL
     , SUM(SAL) OVER(ORDER BY SAL 
     				 ROWS BETWEEN UNBOUNDED PRECEDING --윈도우의 시작위치가 첫번째 행
                     AND CURRENT ROW) TOTSAL -- 윈도우 시작위치가 현재 행, 누적합계 구할 수 있다.
  FROM EMP;
--ROWS는 물리적 결과의 행수, RANGE는 논리적인 값에 의한 범위(행집합)
--UNBOUNDED FOLLOWING: 윈도우 마지막 위치가 마지막 행

 

- 순위함수

SELECT EMPNO, ENAME, SAL
    , RANK() OVER (ORDER BY SAL DESC) ALL_RANK,
    -- 순위가 동일하면 같은 값을 부여한다.
    -- 1 2 2 4
    
    , ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUM,
    -- ROW_NUMBER() 함수는 동일한 순위에 대해 고유번호를 부여한다.
    -- 1 2 3 4
    
    , DENSE_RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) DENSE_RANK
    -- DENSE는 동일한 순위를 하나의 건수로 인식한다.
    -- 1 2 2 3
    
    , RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
    -- JOB으로 파티션 만들고 JOB별 순위 조회
  FROM EMO;

* Order By 를 이용해 오름차순 또는 내림차순으로 출력할 수 있다.

 

- 집계함수

SELECT ENAME, SAL
     , SUM(SAL) OVER (PARTITION BY MGR) SUM_MGR
  FROM EMP;

 

- 행 순서 관련 함수

--부서내 급여가 가장 많은 사람을 가져온다.
SELECT DEPTNO, ENAME, SAL
     , FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO 
								ORDER BY SAL DESC
                                ROWS UNBOUNDED PRECEDING) AS DEPT_A
  FROM EMP;


--부서내 급여가 가장 적은 사람을 가지고 온다.
SELECT DEPTNO, ENAME, SAL
     , LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO 
								ORDER BY SAL DESC
                                ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS DEPT_A
  FROM EMP;
  
  
--LAG함수: 이전 행의 값을 리턴한다.
SELECT DEPTNO, ENAME, SAL
     , LAG(SAL) OVER (ORDER BY SAL DESC) AS DEPT_A
  FROM EMP;


--LEAD함수: 다음 행의 값을 리턴한다.
SELECT DEPTNO, ENAME, SAL
     , LEAD(SAL, 2) OVER (ORDER BY SAL DESC) AS PRE_SAL
  FROM EMP;

 

- 비율관련함수

SELECT DEPTNO, ENAME, SAL
    , PERCENT_RANK() OVER (PARTITION BY DEPTNO  --먼저 나온 것을 0, 제일 늦게 나온 것을 1로 한다.
    					   ORDER BY SAL DESC) PERCENT_SAL
  FROM EMP;
  
SELECT DEPTNO, ENAME, SAL
    , NTILE(4) OVER (ORDER BY SAL DESC) N_TILE
  FROM EMP;
-- 4개로 등분/분할하여 값(1~4)을 순서대로 부여한다.  

 

14. 테이블 파티션

• 테이블의 데이터가 물리적으로 분리된 파일에 저장되면 CRUD 성능이 향상된다.
• 테이블 스페이스 간 이동이 가능하다.
• 조회 속도를 올린다.
• 파티션 전용 인덱스 생성이 가능하다.

 

- 종류: Range(범위기준), List(값기준), Hash, Composite

 

* 파티션 인덱스: Global/Local(전체/지역), Prefixed/Non Prefixed(파티션키와 인덱스 키가 동일한지)

ex. 파티션 키를 a로 생성, 인덱스 키를 b(조건: Local)로 생성했다면, 인덱스 키는 Local Non-Prefixed Partition Index가 된다.