*해당 문서는 Oracle을 기본으로 하고 있습니다.
명령어 종류 명령어 설명
| 데이터 조작어 (DML : Data Manipulation Language) |
SELECT INSERT UPDATE DELETE |
| 데이터 제어어 (DCL : Data Control Language) |
GRANT REVOKE |
| 데이터 정의어 (DDL : Data Definition Language) |
CREATE ALTER DROP RENAME TRUNCATE |
| 트랜젝션 제어어 (TCL : Transaction Control Language) |
COMMIT ROLLBACK SAVEPOINT |
- 테이블 생성과 데이터 조회 / 입력 / 수정 / 삭제
CREATE TABLE 테이블명 (
칼럼명1 데이터 유형 [제약조건],
칼럼명2 데이터 유형 [제약조건],
...
);
-- 예시
-- 테이블 생성
CREATE TABLE USERS (
ID NUMBER PRIMARY KEY,
NAME VARCHAR2(20) NOT NULL,
SAL NUMBER NOT NULL,
HEIGHT NUMBER(3),
GENDER CHAR(1) DEFAULT 'M',
CONSTRAINT USER_SAL_CK CHECK (SAL BETWEEN 1000 AND 5000),
CONSTRAINT USER_GENDER_CK CHECK (GENDER IN('M', 'F'))
);
-- 데이터 입력
INSERT INTO USERS VALUES(1, 'DALI', 5000, 123, 'M');
INSERT INTO USERS VALUES(2, 'DALI2', 5001, 123, 'A'); -- SAL, GENDER 제약조건 위반
INSERT INTO USERS (ID, NAME, SAL) VALUES(3, 'DALI', 1000);
-- 데이터 조회
SELECT * FROM USERS;
-- 데이터 수정
UPDATE USERS SET HEIGHT=200 WHERE ID=1;
-- 데이터 삭제
DELETE FROM USERS WHERE ID=3;
- 테이블 구조 확인
DESC 테이블명;
-- 또는
DESCRIBE 테이블명;
- 칼럼 추가 / 수정 / 삭제
ALTER TABLE USERS ADD (ADDRESS VARCHAR2(80));
ALTER TABLE USERS DROP COLUMN ADDRESS;
ALTER TABLE USERS MODIFY (ADDRESS VARCHAR2(80));
ALTER TABLE USERS ALTER COLUMN ADDRESS VARCHAR2(80);
ALTER TABLE USERS RENAME COLUMN ADDRESS TO ADD
ALTER TABLE USERS DROP CONSTRAINT 제약조건명;
ALTER TABLE USERS ADD CONSTRAINT 제약조건명 제약조건 (칼럼명);
-- 여러 칼럼 수정
ALTER TABLE 테이블명
MODIFY (칼럼명1 데이터 유형 [DEFAULT 식] [NOT NULL], 칼럼명2 데이터 유형...);
- 테이블 복사 및 SELECT 문장을 통한 생성
CREATE TABLE 테이블명 AS SELECT ~~~
- 테이블 구조 유지하고 데이터만 전부 삭제
TRUNCATE TABLE 테이블명;
- NULL
SELECT * FROM USER;
SELECT * FROM USER WHERE NAME IS NULL;
SELECT * FROM USER WHERE NAME IS NOT NULL;
- NVL() 함수 : 첫번째 매개변수인 값이 null이면, 두번째 매개변수인 지정값을 리턴하고 null이 아니면 원래값 리턴
SELECT NVL(AGE, 0) FROM USER;
- NVL2() 함수 : 널이 아니면 1, 널이면 0. NVL()와 DECODE()를 하나로 만든 것
SELECT NVL2(JOB, 1, 0) FROM USER;
- NULLIF() 함수 : 두개의 매개변수 값이 같지 않으면 첫번째 값 출력, 같으면 NULL 리턴
SELECT NULLIF(10, 20) FROM DUAL; // 10
SELECT NULLIF(10, 10) FROM DUAL; // NULL
- COALESCE() 함수 : NULL이 아닌 최초의 인자값을 리턴
SELECT COALESCE(10, 20, NULL) FROM DUAL; // 10
SELECT COALESCE(NULL, 20, NULL) FROM DUAL; // 20
SELECT COALESCE(NULL, NULL, NULL, -100) FROM DUAL; // -100
- GROUP BY 구문 : 집계함수와 함께 사용함
SELECT USERNO AS "고객번호", SUM(PRICE) AS "주문합계" -- 3
FROM USER -- 1
GROUP BY USERNO -- 2
ORDER BY USERNO; -- 4
- HAVING : 그룹핑 된 내용에 조건식 주기, 항상 GROUP BY 와 함께 사용
SELECT USERNO AS "고객번호", SUM(PRICE) AS "주문합계" -- 4
FROM USER -- 1
GROUP BY USERNO -- 2
HAVING SUM(PRICE) >= 10000 -- 3
ORDER BY USERNO; -- 5
- COUNT() 함수 : 매개변수로 (*)는 전체 갯수, (컬럼명)은 NULL 값을 제외한 컬럼수 리턴
SELECT COUNT(*) FROM USER; // 10
SELECT COUNT(AGE) FROM USER; // 9, AGE가 NULL인 1명 존재할 것으로 유추
- 부서별(DEPTNO), 관리자(MGR)별 평균급여 계산, CEIL(소숫점 올림), FLOOR(소숫점 내림)
SELECT DEPTNO AS "부서", MGR AS "관리자", CEIL(AVG(SAL)) AS "평균급여"
FROM EMP
GROUP BY DEPTNO, MGR
ORDER BY DEPTNO;
- 직업별(JOB), 급여합계 중에서 5000 이상인 직업 추출
SELECT JOB AS "직업", SUM(SAL) AS "급여합계"
FROM EMP
-- WHERE SUM(SAL) >= 5000 // WHERE 절에 집계함수는 허가되지 않는다.
GROUP BY JOB
HAVING SUM(SAL) >= 5000
ORDER BY JOB;
- 사원번호가 1000 ~ 1003번까지 부서별 급여 합계
SELECT DEPTNO AS "부서코드", SUM(SAL) AS "급여합계"
FROM EMP
WHERE EMPNO BETWEEN 1000 AND 1003
GROUP BY DEPTNO
ORDER BY DEPTNO;
- SQL 실행순서
- SELECT문의 실행 순서는 FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY 순으로 실행된다.
SELECT name -- 5
FROM emp -- 1
WHERE empno=10 -- 2
GROUP BY name -- 3
HAVING COUNT(*) >= 1 -- 4
ORDER BY name; -- 6
명시적(Explicit) 형 변환과 암시적(Implicit) 형 변환
- 형변환 함수
TO_NUNBER(문자열) : 문자열을 숫자로 변환한다.
TO_CHAR(숫자 혹은 날짜, [FORMAT]) : 숫자 혹은 날짜를 지정된 FORMAT의 문자로 변환한다.
TO_DATE(문자열, FORMAT) : 문자열을 지정된 FORMAT의 날짜형으로 변환한다.
내장형 함수(Built-in Function)
- 내장형 함수
모든 데이터베이스는 SQL에서 사용할 수 있는 내장형 함수를 가지고 있다.
DBMS 벤더별로 약간의 차이가 있지만, 거의 비슷한 방법으로 사용 가능하다.
- DUAL 테이블
DUAL 테이블은 Oracle 데이터베이스에 의해서 자동으로 생성되는 테이블이다.
Oracle 사용자가 임시로 사용할 수 있는 테이블로 내장형 함수를 실행할 때도 사용할 수 있다.
Oracle 데이터베이스의 모든 사용자가 사용할 수 있다.
- 내장형 함수의 종류
DUAL 테이블에 문자형 내장형 함수를 사용하면 다음과 같다.
ASCII 함수는 문자에 대한 ASCII 코드 값을 알려준다. ASCII 코드는 대문자 A를 기준으로 A(65), B(66) 등의 값이다.
SUBSTR함수는 지정된 위치의 문자열을 자르는 함수이고 LENGTH 함수, LEN 함수는 문자열의 길이를 계산한다.
LTRIM 함수를 사용하면 문자열 왼쪽 공백을 제고할 수 있다.
또한 함수를 중첩해서 사용해도 된다.
SELECT ASCII('a'),
SUBSTR('ABC', 0, 2,
LENGTH('A BC'),
LTRIM(' ABC'),
LENGTH(LTRIM(' ABC'))
FROM DUAL;
[Oracle 기준 문자열 함수]
ASCII(문자)
CHR(ASCII 코드값) - ASCII 코드값을 문자로 변환
SUBSTR(문자열, m, n) - m번재 위치부터 n개 자르기, m번 위치는 1부터 시작
CONCAT(문자열1, 문자열2) - 문자 결합, Oracle은 '||', MS-SQL은 '+'를 사용할 수 있음
LOWER(문자열)
UPPER(문자열)
LENGTH 혹은 LEN(문자열)
LENGTHB(문자열) - 문자열의 바이트 크기 리턴, 영문 1바이트 / 한글은 3바이트
LTRIM(문자열, 지정문자) - 지정문자를 생략하면 공백 삭제
RTRIM(문자열, 지정문자)
TRIM(문자열, 지정문자)
SYSDATE - 오늘의 날짜를 날짜 타입으로 알려줌
EXTRACT('YEAR' | 'MONTH' | 'DAY' from dual) - 날짜에서 년, 월, 일을 조회함
SELECT SYSDATE,
EXTRACT(YEAR FROM SYSDATE),
TO_CHAR(SYSDATE, 'YYYY/MM/DD HH:MM:DD') -- AM 오후/오전 표시
FROM DUAL;
[숫자형 함수]
ABS(숫자) - 절댓값 리턴
SIGN(숫자) - 양수(1), 음수(-1), 0으로 구분해서 리턴
MOD(숫자1, 숫자2) - 숫자1을 숫자2로 나누어 나머지 계산. %를 사용해도 됨
CEIL/CEILING(숫자) - 숫자보다 크거나 같은 최소의 정수 리턴
FLOOR(숫자) - 숫자보다 작거나 같은 최대 정수 리턴
ROUND(숫자, m) - 소수점 m 자리에서 반올림, m의 기본값은 0
TRUNC(숫자, m) - 소수점 m 자리에서 절삭, m 기본값은 0
SELECT ABS(-1),
SIGN(-10),
MOD(4,2),
CEIL(10.9),
FLOOR(10.1),
ROUND(10.222, 1)
FROM DUAL;
DECODE와 CASE문
DECODE()
DECODE문으로 IF문을 구현할 수 있다. 즉, 특정 조건이 참이면 A, 거짓이면 B로 응답한다.
-- EMPNO == 1000 ? TRUE : FALSE
SELECT DECODE (EMPNO, 1000, 'TRUE', 'FALSE')
FROM EMP;CASE문
CASE문은 IF~THEN ~ELSE-END의 프로그래밍 언어처럼 조건문을 사용할 수 있다.
SELECT CASE
WHEN EMPNO = 1000 THEN 'A'
WHEN EMPNO = 1001 THEN 'B'
ELSE 'C'
END
FROM EMP;
ROWNUM과 ROWID
ROWNUM
ROWNUM은 ORACLE 데이터베이스의 SELECET문 결과에 대해서 논리적인 일련번호를 부여한다.
조회되는 행 수를 제한할 때 많이 사용된다.
화면에 데이터를 출력할 때 부여되는 논리적 순번이다. 만약 ROWNUM을 사용해서 페이지 단위 출력을 하기 위해서는 인라인 뷰(Inline view)를 사용해야 한다.
인라인 뷰는 SELECT문에서 FROM절이 사용되는 서브쿼리(Sub Query)를 의미한다.
SELECT * FROM -- MAIN QUERY
(SELECT * FROM EMP) LIST; -- SUB QUERY(Inline view)-- 특정 ROW 갯수 리턴
-- ORACLE 버전
SELECT *
FROM (SELECT ROWNUM list, ENAME FROM EMP)
WHERE list <= 10;
SELECT *
FROM (SELECT ROWNUM LIST, ENAME FROM EMP)
WHERE LIST BETWEEN 5 AND 10;
-- SQL Server 버전
SELECT TOP(10) FROM EMP;
-- MySQL 버전
SELECT * FROM EMP LIMIT 10;
ROWID
ROWID는 ORACLE 데이터베이스 내에서 데이터를 구분할 수 있는 유일한 값이다.
ROWID를 통해서 데이터가 어떤 데이터 파일, 어느 블록에 저장되어 있는지 알 수 있다.
SELECT ROWID, WINETYPENAME FROM WINETYPE;ROWID 구조는 오브젝트 번호, 상대 파일 번호, 블록 번호, 데이터 번호로 구성되어 있다.
WITH 구문
WITH 구문은 서브쿼리를 사용해서 임시 테이블이나 뷰처럼 사용할 수 있는 구문이다.
서브쿼리 블록에 별칭을 지정할 수 있다.
옵티마이저는 SQL을 인라인 뷰나 임시 테이블로 판단한다.
WITH viewData AS
(SELECT * FROM EMP
UNION ALL
SELECT * FROM EMP)
SELECT * FROM viewData WHERE EMPNO=1000;
-- UNION : 2개를 합해서 출력, 중복 제외
-- UNION ALL : 2개 합해서 출력, 중복 포함
WITH W_EMP AS
(SELECT * FROM EMP WHERE DEPTNO=30)
SELECT * FROM W_EMP;
DCL(Data Control Language) 구문
GRANT
GRANT문은 데이터베이스 사용자에게 권한을 부여한다.
GRANT privileges ON object TO user;
-- privileges는 권한을 의미하며 object는 테이블명
-- user는 Oracle 데이터베이스 사용자를 지정
GRANT SELECT, INSERT, UPDATE, DELETE
ON EMP
TO LIMBEST WITH GRANT OPTION;[privileges(권한)의 종류]
SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, ALL
WITH GRANT OPTION
| WIDTH GRANT OPTION | 특정 사용자에게 권한을 부여할 수 있는 권한을 부여한다. 권한을 A 가 B에게 부여하고, B가 다시 C에게 부여한 후, A가 B의 권한을 취소(REVOKE)하면 B,C 모두 모든 권한이 회수된다. |
| WITH ADMIN OPTION | 테이블에 대한 모든 권한을 부여한다. 위 WITH GRANT OPTION과 달리 취소시킨 해당 사용자의 권한만 취소된다. |
REVOKE
사용자에게 부여된 권한을 회수한다.
REVOKE privileges ON object FROM user;
REVOKE INSERT, UPDATE
ON EMP
FROM TEST;
TCL(Transaction Control Language) 구문
COMMIT, ROLLBACK, SAVEPOINT
TRANSACTION
| 원자성(Atomicity) | 트랜잭션은 데이터베이스 연산의 전부가 실행되거나 전혀 실행되지 않아야 한다. 즉, 트랜잭션의 처리가 완전히 끝나지 않았을 경우는 실행되지 않은 상태와 같아야 한다. |
| 일관성(Consistency) | 트랜잭션 실행 결과로 데이터베이스의 상태가 모순되지 않아야 한다. 트랜잭션 실행 후에도 일관성이 유지되어야 한다. |
| 고립성(Isolation) | 트랜잭션 실행 중에 생성하는 연산의 중간결과는 다른 트랜잭션이 접근할 수 없다. 즉, 부분적인 실행 결과를 다른 트랜잭션이 볼 수 없다. |
| 영속성, 지속성(Durability) | 트랜잭션이 그 실행을 성공적으로 완료하면 그 결과는 영구적 보장이 되어야 한다. |
COMMIT
INSERT, UPDATE, DELETE문으로 변경한 데이터를 물리적인 데이터베이스에 반영한다.
변경 이전 데이터는 삭제된다.
COMMIT이 완료되면 데이터베이스 변경으로 인한 LOCK이 해제(UNLOCK)된다.
COMMIT이 완료되면 다른 모든 데이터베이스 사용자는 변경된 데이터를 조작할 수 있다.
COMMIT을 실행하면 하나의 트랜잭션 과정을 종료한다.
ROLLBACK
데이터에 대한 변경 사용을 모두 취소하고 트랜잭션을 종료한다.
INSERT, UPDATE, DELETE문의 작업을 모두 취소하고 이전 커밋한 곳까지만 복구한다.
ROLLBACK을 실행하면 LOCK이 해제되고 다른 사용자도 데이터베이스행을 조작할 수 있다.
SAVEPOINT
트랜잭션을 작게 분할하여 관리하는 것으로 SAVEPOINT를 사용하면 지정된 위치 이후의 트랜잭션만 ROLLBACK 할 수 있다.
-- SAVEPOINT 지정
SAVEPOINT <SAVEPOINT명>
-- 지정된 SAVEPOINT까지만 데이터 변경을 취소하고 싶은 경우
ROLLBACT TO <SAVEPOINT명>
조인(Join)
EQUI(등가) 조인(교집합)
1) EQUI(등가) 조인
조인은 여러 개의 릴레이션을 사용해서 새로운 릴레이션을 만드는 과정이다.
두 개의 테이블 간에 일치하는 것을 조인한다.(교집합)
WHERE 절에 '=' 을 사용해 두 테이블을 연결한다. 추가 조건도 사용할 수 있다.
SELECT * FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND EMP.ENAME LIKE '임%'
ORDER BY ENAME;
2) INNER JOIN
ISO 표준 SQL이다. ON문을 사용해서 두 테이블을 연결한다.
SELECT * FROM EMP
INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO
AND EMP.ENAME LIKE '임%'
ORDER BY ENAME;
해시 조인(Hash Join)
해시 조인은 먼저 선행 테이블을 결정하고 선행 테이블에서 주어진 조건(WHERE구문)에 해당하는 행을 선택한다. 해당 행이 선택되면 조인 키를 기준으로 해시 함수를 사용해서 해시 테이블을 메인 메모리에 생성하고 후행 테이블에서 주어진 조건에 만족하는 행을 찾는다. 후행 테이블의 조인 키를 사용해서 해시함수를 적용하여 해당버킷을 검색한다.
해시 조인은 EQUI 조인만 사용 가능한 방법이다.
3) INTERSECT 연산
두 테이블에서 교집합을 조회한다. 즉 공통된 값을 조회한다.
두 테이블에 모두 존재하는 컬럼만 조회 가능하다.
SELECT DEPTNO FROM EMP
INTERSECT
SELECT DEPTNO FROM DEPT;
Non-EQUI(비등가) 조인
두 테이블 간에 조인하는 경우 "="을 사용하지 않고 ">", "<", ">=", "<=" 등을 사용한다.
즉 Non-EQUI 조인은 정확하게 일치하지 않는 것을 조인하는 것이다.
OUTER JOIN
두 테이블 간에 교집합을 조회하고 한 쪽 테이블에만 있는 데이터도 포함시켜서 조회한다.
왼쪽 테이블에만 있는 행도 포함하면 LEFT OUTER JOIN 이라고 하고, 반대는 RIGHT OUTER JOIN이라고 한다.
FULL OUTER JOIN은 둘 모두를 하는 것이다.
SELECT * FROM DEPT LEFT OUTER JOIN EMP
ON EMP.DEPTNO = DEPT.DEPTNO;ORACLE 데이터베이스에서는 OUTER JOIN을 할 때 "(+)" 기호를 사용해서 할 수 있다.
등호의 =(+) 오른쪽에 기호가 붙으면 오른쪽 테이블에 null을 허용하게 되어 결과적으로 LEFT OUTER JOIN이 된다.
SELECT * FROM DEPT, EMP
WHERE EMP.DEPTNO (+)= DEPT.DEPTNO; -- RIGHT OUTER JOIN
CROSS JOIN
조인 조건구문 없이 2개의 테이블을 하나로 조인한다.
조인구가 없기 때문에 카테시안 곱이 발생한다.
SELECT * FROM EMP CROSS JOIN DEPT;
SELECT * FROM EMP, DEPT;
UNION을 사용한 합집합 구현
UNION
두 테이블을 하나로 만드는 연산이다. 즉 합치는 것이다.
주의사항은 두 테이블의 컬럼 수, 데이터 형식 모두 일치해야 한다. 다르면 오류 발생.
중복된 데이터를 제거하면서 테이블을 합친다. 그래서 정렬(SORT) 과정을 발생시킨다.
SELECT U.USERID
FROM USERTBL U;
UNION
SELECT B.USERID
FROM BUYTBL B;
UNION ALL
두 테이블을 하나로 합치는 연산이지만, UNION처럼 중복을 제거하거나 정렬을 유발하지 않는다.
MINUS
두 테이블에서 차집합을 조회한다. 즉 먼저 쓴 SELECT문에는 있고 뒤에 쓴느 SELECT문에는 없는 집합을 조회하는 것이다.
MS-SQL에서는 EXCEPT가 있다. MYSQL에서는 지원하지 않는다.(NOT IN, NOT EXISTS 등 사용)
SELECT U.USERID
FROM USERTBL U
MINUS
SELECT B.USERID
FROM BUYTBL B;
계층형 조회(Connect by)
계층형 조회는 Oracle 데이터베이스에서 지원하는 것으로 계층형으로 데이터를 조회할 수 있다. 트리 형태의 구조를 위에서 아래로 탐색하면서 조회하는 것이다. 물론 역방향 조회도 가능하다. 자체조인(셀프조인)을 사용한다.
Connect by는 트리(Tree) 형태의 구조로 질의를 수행하는 것으로 START WITH구는 시작 조건을 의미하고 CONNECT BY PRIOR는 조인 조건이다. Root 노드로부터 하위 노드의 질의를 실행한다.
계층형 조회에서 MAX(LEVEL)을 사용해 최대 계층 수를 구할 수 있다. 즉, 계층형 구조에서 마지막 Leaf Node의 계층값을 구한다.
-- MGR 컬럼이 자신의 상사번호인 경우
SELECT LEVEL, EMPNO, MGR, ENAME
FROM AAAAA.EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;
- LPAD
Oracle 데이터베이스에서 계층형 조회 결과를 명확히 보기 위해 LPAD 함수를 사용할 수 있다.
LPAD(변수, 길이, 변형자)
SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || EMPNO, MGR, CONNECT_BY_ISLEAF
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;
- CONNECT_BY_ISLEAF
계층형 쿼리에서 해당하는 로우가 자식노드가 있는지 없는지 여부 체크.
자식노드가 있으면 0, 없으면 1.
- CONNECT_BY_ROOT
계층형 쿼리에서 최상위 노드를 찾고자 할 경우 사용.
- CONNECT BY 키워드
| LEVEL | 검색 항목의 깊이를 의미. 즉, 계층구조에서 가장 상위 레벨이 1이 된다. |
| CONNECT_BY_ROOT | 계층구조에서 가장 최상위 값을 표시. |
| CONNECT_BY_ISLEAF | 계층구조에서 가장 최하위를 표시. |
| SYS_CONNECT_BY_PATH | 계층구조의 전체 전개 경로를 표시. |
| NOCYCLE | 순환구조가 발생지점까지만 전개됨. |
| CONNECT_BY_ISCYCLE | 순환구조 발생 지점을 표시. |
서브쿼리(SUBQUERY)
Main query와 Subquery
Subquery는 SELECT문 내에 다시 SELECT문을 사용하는 SQL문이다.
- FROM 구에 SELECT문을 사용하는 건 인라인 뷰(Inline View).
- SELECT 문에 Subquery를 사용하는 건 스칼라 서브쿼리(Scala Subquery).
- WHERE 구에 SELECT문을 사용하는 걸 일반적으로 서브쿼리(Subquery)라고 함.
SELECT USERNAME, HEIGHT
FROM USERTBL
WHERE HEIGHT > (SELECT HEIGHT
FROM USERTBL
WHERE USERNAME = '홍길동'); -- 서브쿼리SELECT *
FROM (SELECT ROWNUM ID, PRODNAME
FROM BUYTBL) -- 인라인 뷰
WHERE ID < 5;
단일 행 서브쿼리
서브쿼리를 실행하면 그 결과는 반드시 한 행만 조회.
비교 연산자인 =, <, <=, >, >=, <>를 사용.
다중 행 서브쿼리
서브쿼리를 실행하면 그 결과는 여러 개의 행이 조회.
비교 연산자인 IN, ANY(SOME), ALL, EXISTS를 사용.
-- ANY(SOME)
SELECT USERNAME, HEIGHT
FROM USERTBL
WHERE HEIGHT > ANY (SELECT HEIGHT
FROM USERTBL
WHERE ADDR = '부산');
-- ALL
SELECT USERNAME, HEIGHT
FROM USERTBL
WHERE HEIGHT > ALL (SELECT HEIGHT
FROM USERTBL
WHERE ADDR = '부산');
-- IN
SELECT USERNAME, HEIGHT
FROM USERTBL
WHERE HEIGHT IN (SELECT HEIGHT
FROM USERTBL
WHERE ADDR = '부산');
-- EXISTS
SELECT U.USERID, U.USERNAME, U.ADDR,
CONCAT(U.MOBILE1, U.MOBILE2) AS "연락처"
FROM USERTBL U
WHERE EXISTS (SELECT *
FROM BUYTBL B
WHERE U.USERID = B.USERID);ANY의 의미는 OR의 개념과 비슷. 동일한 역할로 SOME도 있다.
ALL은 결과 값 모두를 만족하는 데이터만 출력. AND의 개념과 유사하다.
IN은 서브쿼리 결과 중 하나만 동일하면 참. OR조건과 유사.
EXISTS는 서브쿼리로 어떤 데이터 존재 여부 확인. BOOLEAN값으로 반환되어 참인 경우 출력. DISTINCT와 비슷한 역할.
4. 그룹함수(Group Function)
ROLLUP
GROUP BY의 칼럼에 대해서 Subtotal을 만들어 준다.
ROLLUP을 할 때 GROUP BY구에 칼럼이 두 개 이상 오면 순서에 따라 결과가 달라진다.
-- DEPTNO가 NULL이면 '전체합계' 문자열 출력
SELECT DECODE(DEPTNO, NULL, '전체합계', DEPTNO), SUM(SAL)
FROM EMP
-- ROLLUP을 사용하면 부서별 합계 및 전체합계가 계산됨
GROUP BY ROLLUP(DEPTNO);
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
-- 부서별, 직업별 합계를 구한다. ROLLUP은 SUBTOTAL을 구한다.
GROUP BY ROLLUP(DEPTNO, JOB);
GROUPING 함수
ROLLUP, CUBE, GROUPING SETS에서 생성되는 합계 값을 구분하기 위해 만들어진 함수.
예를 들어 소계, 합계 등이 계산되면 GROUPING 함수는 1을 반환하고 그렇지 않으면 0을 반환해서 합계 값을 식별할 수 있다.
-- ROLLUP, CUBE, GROUPING SETS으로 계산된 합계는 '1'로 표식된다.
SELECT DEPTNO, GROUPING(DEPTNO), JOB, GROUPING(JOB), SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);
-- DECODE를 사용하여 전체 합계와 부서 합계를 구하고 있다.
SELECT DEPTNO,
DECODE(GROUPING(DEPTNO), 1, '전체합계') TOT, JOB,
DECODE(GROUPING(JOB), 1, '부서합계') T_DETP,
SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);
GROUPING SETS 함수
GROUP BY에 나오는 칼럼의 순서와 관계 없이 다양한 소계를 만들 수 있다. 개별적으로 모두 처리한다.
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
-- JOB, DETPNO를 각각의 그룹으로 합계를 계산한다
-- 순서가 바뀌어도 결과는 동일하다
GROUP BY GROUPING SETS(JOB, DEPTNO);
결과 DETPNO 합계와 JOB 합계가 개별적으로 조회. 즉 서로 관계가 없다.
CUBE 함수
CUBE 함수에 제시한 칼럼에 대해서 결합 가능한 모든 집계를 계산한다.
다차원 집계를 제공하여 다양하게 데이터를 분석할 수 있다.
조합할 수 있는 경우의 수가 모두 조합되는 것이다.
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
-- CUBE는 모든 합계를 모두 계산하는 다차원 분석을 한다
GROUP BY CUBE(DEPTNO, JOB);
5. 윈도우 함수(Window Function)
윈도우 함수는 행과 행 간의 관계를 정의하기 위해 제공되는 함수이다.
윈도우 함수를 사용해서 순위, 합계, 평균, 행 위치 등을 조작할 수 있다.
SELECT WINDOW_FUNCTION(ARGUMENTS)
OVER(PARTITION BY 칼럼
ORDER BY WINDOWING절)
FROM 테이블명;
- 윈도우 함수 구조
| ARGUMENTS(인수) | 윈도우 함수에 따라서 0~N개의 인수를 설정한다. |
| PARTITION BY | 전제 집합을 기준에 의해 소그룹으로 나눈다. |
| ORDER BY | 어떤 항목에 대해 정렬한다. |
| WINDOWING | 행 기준의 범위를 정한다. ROWS는 물리적 결과의 행 수이고, RANGE는 논리적인 값에 의한 범위이다. |
- WINDOWING
| ROWS | 부분집합인 윈도우 크기를 물리적 단위로 행의 집합을 지정한다. |
| RANGE | 논리적인 주소에 의해 행 집합을 지정한다. |
| BETWEEN ~ AND | 윈도우의 시작과 끝의 위치를 지정한다. |
| UNBOUNDED PRECEDING | 윈도우의 시작 위치가 첫 번째 행임을 의미한다. |
| UNBOUNDED FOLLOWING | 윈도우 마지막 위치가 마지막 행임을 의미한다. |
| CURRENT ROW | 윈도우 시작 위치가 현재 행임을 의미한다. |
SELECT EMPNO, ENAME, SAL,
SUM(SAL) OVER (ORDER BY SAL
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) TOTSAL
FROM EMP;
위 TOTSAL을 차례로 누적시키려면,
SELECT EMPNO, ENAME, SAL
SUM(SAL) OVER(ORDER BY SAL
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) TOTSAL
FROM EMP;
순위함수(RANK Function)
특정 항목과 파티션에 대해서 순위를 계산할 수 있는 함수를 제공한다.
| RANK | 특정항목 및 파티션에 대해서 순위를 계산한다. 동일한 순위는 동일한 값이 부여된다. |
| DENSE_RANK | 동일한 순위를 하나의 건수로 계산한다. |
| ROW_NUMBER | 동일한 순위에 대해서 고유의 순위를 부여한다. |
SELECT ENAME, SAL, JOB,
-- 급여(SAL) 순위를 계산한다. 동일한 순위는 동일하게 조회한다.
RANK() OVER(ORDER BY SAL DESC) ALL_RANK,
-- 직업(JOB)별 순위를 계산한다.
RANK() OVER(PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;
SELECT ENAME, SAL,
RANK() OVER(ORDER BY SAL DESC) ALL_RANK,
DENSE_RANK() OVER(ORDER BY SAL DESC) DENSE_RANK,
ROW_NUMBER() OVER(ORDER BY SAL DESC) ROW_NUMBER
FROM EMP;
집계 함수(RANK Function)
| SUM | 파티션 별로 합계를 계산한다. |
| AVG | 파티션 별로 평균을 계산한다. |
| COUNT | 파티션 별로 행 수를 계산한다. |
| MAX와 MIN | 파티션 별로 최댓값과 최솟값을 계산한다. |
SELECT ENAME, MGR, SAL,
-- 같은 관리자(MGR)에 파티션을 만들고 합계(SUM)를 계산한다.
SUM(SAL) OVER(PARTITION BY MGR) SUMMGR
FROM EMP;
행 순서 관련 함수
| FIRST_VALUE | 파티션에서 가장 처음에 나오는 값을 구한다. MIN 함수를 사용해서 같은 결과를 구할 수 있다. |
| LAST_VALUE | 파티션에서 가장 나중에 나오는 값을 구한다. MAX 함수를 사용해서 같은 결과를 구할 수 있다. |
| LAG | 이전 행을 가지고 온다. |
| LEAD | 윈도우에서 특정 위치의 행을 가지고 온다. 기본값은 1이다. |
-- 부서 내에서 급여가 가장 많은 사람을 첫 번째 행으로 가져온다.
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 PRE_SAL
FROM EMP;
-- LEAD 함수는 지정된 위치의 행을 가져온다. 두번째 매개변수는 기본값이 1이며 음수값을 받지 않음.
SELECT DEPTNO, ENAME, SAL,
LEAD(SAL, 2) OVER(ORDER BY SAL DESC) AS PRE_SAL
FROM EMP;
비율 관련 함수
누적 백분율, 순서별 백분율, 파티션을 N분으로 분할한 결과 등을 조회할 수 있다.
| CUME_DIST | 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 조회한다. 누적 분포상에 위치를 0~1 사이의 값을 가진다. |
| PERCENT_RANK | 파티션에서 제일 먼저 나온 것을 0으로 제일 늦게 나온 것을 1로 하여 값이 아닌 행의 순서별 백분율을 조회한다. |
| NTILE | 파티션별로 전체 건수를 ARGUMENT 값으로 N 등분한 결과를 조회한다. |
| RATIO_TO_REPORT | 파티션 내에 전체 SUM(칼럼)에 대한 행 별 칼럼값의 백분율을 소수점까지 조회한다. |
-- CUME_DIST함수는 0으로 시작하지 않고 첫 번째 사람도 백분율에 적용시키고 누적 백분율을 표시한다.
-- 누적 백분율의 값은 0~1 사이 값을 가진다.
SELECT DEPTNO, ENAME, SAL, CUME_DIST()
OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS PERSENT_SAL
FROM EMP;
-- 같은 부서에서 자신의 급여 퍼센트(등수)를 구한다.
-- 제일 먼저 나온 걸 0으로, 제일 늦게 나온 걸 1로 하여 순서별 백분율로 표시한다.
SELECT DEPTNO, ENAME, SAL, PERCENT_RANK()
OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS PERCENT_SAL
FROM EMP;
-- 매개변수인 정수값으로 등분한다.
-- 나누어 떨어지지 않는 수는 오라클 엔진이 적절히 분할 시킨다.
SELECT DEPTNO, ENAME, SAL, NTILE(4)
OVER(ORDER BY SAL DESC) AS N_TILE
FROM EMP;
6. 테이블 파티션(Table Partition)
Partition 기능
파티션은 대용량의 테이블을 여러 개의 데이터 파일에 분리해서 저장한다.
이 경우 입력, 수정, 삭제, 조회 성능이 향상될 수 있고, 파티션 별로 독립적으로 관리될 수 있다.
파티션 별로 백업하고 복구가 가능하며 파티션 전용 인덱스 생성도 가능하다.
Oracle 데이터베이스의 논리적 관리 단위인 테이블 스페이스 간에 이동이 가능하다. 데이터를 조회할 때 데이터의 범위를 줄여서 성능을 향상시킨다.
*테이블 스페이스 : 데이터베이스 오브젝트 내 실제 데이터를 저장하는 공간이다. 이것은 데이터베이스의 물리적인 부분과 논리적인 부분으로 나뉜다.
- Range Partition
Range Partition은 테이블의 칼럼 중에서 값의 범위를 기준으로 여러 개의 파티션으로 데이터를 나누어 저장하는 것이다.
- List Partition 기능
특정 값을 기준으로 분할하는 방법이다.
- Hash Partition
데이터베이스 관리 시스템이 내부적으로 해시 함수를 사용해서 데이터를 분할한다.
결과적으로 데이터베이스 관리 시스템이 알아서 분할하고 관리하는 것이다.
- Composite Partition
여러 개의 파티션 기법을 조합해서 사용하는 것이다.
- 파티션 인덱스
4가지 유형의 인덱스를 제공한다.
파티션 키를 사용해서 인덱스를 만드는 Prefixed Index와 해당 파티션만 사용하는 Local Index 등으로 나누어진다.
Oracle 데이터베이스는 Global Non-Prefixed를 지원하지 않는다.
| Global Index | 여러 개의 파티션에서 하나의 인덱스를 사용한다. |
| Local Index | 해당 파티션 별로 각자의 인덱스를 사용한다. |
| Prefixed Index | 파티션 키와 인덱스 키가 동일하다. |
| Non Prefixed Index | 파티션 키와 인덱스 키가 다르다. |
-- 테이블 파티션 나누기
CREATE TABLE TEST(A NUMBER, B CHAR(3), C VARCHAR(10))
PARTITION BY RANGE(A) (
PARTITION P1 VALUES LESS THAN(1000),
PARTITION P2 VALUES LESS THAN(2000),
PARTITION P3 VALUES LESS THAN(MAXVALUE)
);
-- 인덱스 만들기
CREATE INDEX TEST_IDX
ON TEST(B) LOCAL; -- NON PREFIXED
-- 나눠진 파티션에 INSERT 하기
INSERT INTO TEST PARTITION(P1) VALUES(111, 'AAA', 'BBB');
INSERT INTO TEST PARTITION(P2) VALUES(222, 'BBB', 'CCC'); -- INSERT 실패. 파티션 제약
INSERT INTO TEST PARTITION(P2) VALUES(1011, 'BBB', 'CCC'); -- INSERT 성공
-- 파티션 테이블 키 컬럼 조회
SELECT * FROM ALL_PART_KEY_COLUMNS
WHERE NAME = 'TEST';
-- 파티션명 조회
SELECT * FROM ALL_TAB_PARTITIONS
WHERE TABLE_NAME = 'TEST';
7. 옵티마이저(Optimizer)와 실행 계획
옵티마이저(Optimizer)
SQL개발자가 SQL을 작성하여 실행할 때, 옵티마이저는 SQL을 어떻게 실행할 것인지를 계획하게 된다. 즉 실행 계획(Execution Plan)을 수립하고 SQL을 실행한다.
옵티마이저는 SQL의 실행 계획을 수립하고 SQL을 실행하는 데이터베이스 관리 시스템의 소프트웨어이다.
동일한 결과가 나오는 SQL도 어떻게 실행하냐에 따라 성능이 달라진다.(튜닝을 잘 해야...)
옵티마이저 특징
옵티마이저는 데이터 딕셔너리(Data Dictionary)에 있는 오브젝트 통계, 시스템 통계 등의 정보를 사용해서 예상되는 비용을 산정한다.
옵티마이저는 여러 개의 실행 계획 중에서 최저비용을 가지고 있는 계획을 선택해서 SQL을 실행한다.
옵티마지어 필요성
큰 집합과 작은 집합의 교집합을 구할 때 작은 집합을 먼저 읽고 큰 집합과 비교하는 게 더 효율적이다. 옵티마이저는 이렇게 더 효율적인 실행 계획을 수립하는 것이며, 만약 옵티마이저가 비효율적으로 실행 계획을 수립하면 이를 개선해야 한다. 이 때 옵티마이저에게 실행 계획을 변경하도록 요청할 수 있는데 힌트(HINT)를 사용한다.
옵티마이저 실행 계획 확인
옵티마이저는 SQL 실행 계획을 PLAN_TABLE에 저장한다.
옵티마이저 종류
옵티마이저의 실행 방법
개발자가 SQL을 실행하면 파싱(Parsing)을 실행해서 SQL의 문법 검사 및 구문분석을 수행한다.
구문분석이 완료되면 옵티마이저가 규칙 기반 혹은 비용 기반으로 실행 계획을 수립한다.
옵티마이저는 기본적으로 비용 기반 옵티마이저를 사용해서 실행 계획을 수립한다. 비용 기반 옵티마이저는 통계 정보를 활용해서 최적의 실행 계획을 수립하는 것이다.
실행 계획 수립이 완료되면 최종적으로 SQL을 실행하고 실행이 완료되면 데이터를 인출(FETCH)한다.
- 옵티마이저 엔진
| Query Transformer | SQL문을 효율적으로 실행하기 위해서 옵티마이저가 변환한다. SQL이 변환되어도 그 결과는 동일하다. |
| Estimator | 통계 정보를 사용해서 SQL 실행비용을 계산한다. 총 비용은 최적의 실행 계획을 수립하기 위해서이다. |
| Plan Generator | SQL을 실행할 실행 계획을 수립한다. |
규칙 기반 옵티마이저
규칙 기반 옵티마이저(Rule base Optimizer)는 실행 계획을 수립할 때 15개의 우선순위를 기준으로 실행 계획을 수립한다.
최신 Oracle 버전은 규칙 기반 옵티마이저보다 비용 기반 옵티마이저를 기본적으로 사용한다.
위 이미지에서는 '/*+ RULE */'를 사용해서 규칙 기반 옵티마이저로 실행하도록 알려주고 있다.
비용 기반 옵티마이저
비용 기반 옵티아미저(Cost base Optimizer)는 오브젝트 통계 및 시스템 통계를 사용해서 총 비용을 계산한다.
총 비용이라는 것은 SQL문을 실행하기 위해서 예상되는 소요시간 혹은 자원의 사용량을 의미한다.
총 비용이 적은 쪽으로 실행 계획을 수립한다.
단, 비용 기반 옵티마이저에서 통계정보가 부적절한 경우 성능 저하가 발생할 수 있다.
8. 인덱스(INDEX)
인덱스
인덱스는 데이터를 빠르게 검색할 수 있는 방법을 제공한다.
인덱스 키(ex. EMPNO)로 정렬(SORT)되어 있기 때문에 원하는 데이터를 빠르게 조회한다.
인덱스는 오름차순(ASCENDING) 및 내림차순(DESCENDING) 탐색이 가능하다.
한 테이블에 여러 개의 인덱스를 생성할 수 있고 하나의 인덱스는 여러 개의 칼럼으로 구성될 수 있다.
테이블을 생성할 때 기본기(PK)는 자동으로 인덱스가 만들어지고 인덱스의 이름은 SYSXXXX이다.
인덱스는 Root Blcok, Branch Block, Leaf Block으로 구성되고, Root Block은 인덱스 트리 가장 상위에 있는 노드이며 Branch Block은 다음 단계의 주소를 가지고 있는 포인터(Pointer)로 되어 있다.
Leaf Block은 INDEX KEY와 ROWID로 구성되고 인덱스키는 정렬되어 저장되어 있다.
Leaf Block은 Double Linked List 형태로 되어 있어서 양방향 검색이 가능하다.
인덱스 생성
한 개 이상의 칼럼을 사용해서 생성할 수 있다.
기본적으로 오름차순으로 정렬하고 'DESC' 구를 포함하면 내림차순으로 정렬한다.
-- ENAME 오름차순, SAL 내림차순 인덱스 생성
CREATE INDEX IND_EMP
ON EMP(ENAME ASC, SAL DESC); -- ASC 구는 생략 가능
인덱스 스캔
1) 인덱스 유일 스캔(INDEX UNIQUE SCAN)
INDEX UNIQUE SCAN은 인덱스의 키 값이 중복되지 않는 경우, 해당 인덱스를 사용할 때 발생된다.
SELECT *
FROM EMP
WHERE EMPNO=1000;
2) 인덱스 범위 스캔(INDEX RANGE SCAN)
INDEX RANGE SCAN은 SELECT문에서 특정 범위를 조회하는 WHERE 문을 사용할 경우 발생한다.
LIKE, BETWEEN이 대표적인 예이다. 물론 데이터 양이 적은 경우 인덱스 자체를 실행하지 않고 TABLE FULL SCAN이 될 수 있다.
INDEX RANGE SCAN은 인덱스의 LEAF BLOCK의 특정 범위를 스캔한 것이다.(관계 연산자 사용)
3) 인덱스 전체 스캔(INDEX FULL SCAN)
INDEX FULL SCAN은 인덱스에서 검색되는 인덱스 키가 많은 경우 LEAF BLOCK의 처음부터 끝까지 전체를 읽어 들인다.
9. 옵티마이저 조인(OPTIMIZER JOIN)
Nested Loop 조인
Nested Loop 조인은 하나의 테이블에서 데이터를 먼저 찾고 그 다음 테이블을 조인하는 방식으로 실행된다.
먼저 조회되는 테이블을 외부 테이블(Outer Table)이라고 하고 다음 조회되는 테이블을 내부 테이블(Inner Table)이라고 한다.
외부 테이블(선행 테이블)의 크기가 작은 것을 먼저 찾는 것이 중요하다. 스캔 범위가 줄어들기 때문이다.
Random Access가 발생하는데 많이 발생하면 성능지연이 생긴다. Random Access 양을 줄여야 성능이 향상된다.
Sort Merge 조인
두 개의 테이블을 SORT_AREA 메모리 공간에 모두 로딩하고 정렬을 수행한 후 테이블을 병합한다.
정렬이 발생하기 때문에 데이터 양이 많으면 성능이 떨어진다.
랜덤 엑세스로 NL Join에서 부담이 되던 넓은 범위의 데이터를 처리할 때 이용되던 조인 기법이다.
Hash 조인
두 테이블 중 작은 테이블을 Hash 메모리에 로딩하고 두 개의 테이블의 조인키를 사용해서 해시 테이블을 생성한다.
해시 함수를 사용해서 주소를 계산하고 해당 주소를 사용해서 테이블을 조인하기 때문에 CPU 연산을 많이 한다.
선행 테이블이 충분히 메모리에 로딩되는 크기여야 한다.
조인 칼럼의 인덱스를 사용하지 않기 때문에 조인 칼럼의 인덱스가 없을 경우에도 사용할 수 있다.
'='로 수행하는 조인, 즉 동등 조인에서만 사용할 수 있다.
[참고]
Inflean - SQL 개발자 (SQLD) 자격증 따기 (*기출문제는 일명 '노랭이' 책 추천)
항상 자주 쓰는 ORM 기술만 쓰다보니 DB 관련 지식도 휘발되고 기초가 부실해지는 것 같아 공부도 할 겸 겸사겸사 SQLD까지 같이 진행했다. 사소해도 목표가 있으면 좀 더 머리에 들어올 거 같아서.. 😁
'개발 > Etc' 카테고리의 다른 글
| 토이프로젝트 Plus Typo (0) | 2023.03.20 |
|---|---|
| git 간단 명령어 모음 (0) | 2023.01.25 |
| 웹 성능 개선 - 폰트편 (0) | 2023.01.12 |
| Cumulative Layout Shift(누적 레이아웃 이동, CLS) (0) | 2023.01.11 |
| npm trends _ 라이브러리 선택할 때 유용한 서비스 (2) | 2023.01.11 |