[sqld] sqld 정리 2과목
과목2 SQL 기본 및 활용
1장 SQL 기본
1절 관계형 DB
1. DB
- 특정 기업이나 조직 또는 개인이 필요에 의해 데이터를 일정한 형태로 저장해 놓은 것
- DBMS : 효율적인 데이터 관리 뿐 아니라 예기치 못한 사건으로 인한 데이터의 손상을 피하고 필요시 데이터를 복구하기 위한 강력한 기능의 소프트웨어
2. 관계형 DB
- 정규화를 통해 이상현상 및 중복 데이터 제거
- 동시성 관리와 병행제어를 통한 데이터 동시 조작 가능
3. SQL : RDB에서 사용하는 언어, 데이터 조회 및 신규 데이터 입력/수정/삭제 기능 제공
♢ 종류
1) DML - SELECT, INSERT, UPDATE, DELETE (데이터 조작어)
2) DDL - CREATE,ALTER,DROP (데이터 정의어) - 데이터 구조 관련 명령어
3) DCL - GRANT, REVOKE (데이터 제어어) - 데이터베이스 접근 권한 부여 및 회수 명령어
4) TCL - COMMIT, ROLLBACK (트랜잭션 제어어) - DML로 조작한 결과를 논리적인 작업단위 별로 제어
2절 DDL (데이터 정의어)
1. 데이터 타입
♢ CHAR(L) : 고정길이 문자열
♢ VARCHAR(L). VARCHAR2(L) : 가변길이 문자열
♢ NUMBER(L,0) : 숫자형
- SQL Server : NUMBERIC, DECIMAL, FLOAT, REAL ..
♢ DATE, DATETIME : 날짜형, 데이터 크기 정의 x
2. CREATE TABLE
♢ SQL > CREATE TABLE 테이블명 (컬럼명 데이터타입 제약조건, ..)
♢ 테이블 및 칼럼 명명규칙
- 알파벳, 숫자, '_', '$', '#' 사용
- 대소문자 구분 x
- 테이블명은 단수형 권고
♢ 제약조건 : 데이터 무결성 유지가 목적, 복제 테이블에는 기존 테이블 제약조건 중 not null만 적용
- primary key : 테이블 당 하나의 기본키만 정의 가능, 기본키 생성시 DBMS가 자동으로 인덱스 생성, not null
- foreign key : 다른 테이블의 기본키를 외래키로 지정, 참조 무결성 제약조건, null, 여러개 존재 가능
- unique key : 고유키 정의, null 가능
- check : 입력값 범위 제한
- not null
* DESC 테이블명 / DESCRIBE 테이블명 : 테이블 정보 확인
CREATE TABLE 테이블명
(
컬럼명 데이터타입 제약조건
, ....
, CONSTRAINT 제약조건명 제약조건(컬럼)
);* DELETE(MODIFY) ACTION
1) CASCADE : master 삭제 시 child 같이 삭제
2) SET NULL : master 삭제 시 child 필드 null
3) SET DEFAULT : master 삭제 시 child 필드 default 값으로 설정
4) RESTRICT : child 테이블에 PK값이 없는 경우 master 삭제 허용
5) NO ACTION : 참조무결성을 위반하는 삭제/수정 액션을 취하지 않음
* INSERT ACTION
1) AUTOMATIC : master 테이블에 PK가 없는 경우 master PK 생성 후 child 입력
2) SET NULL : master 테이블에 PK가 없는 경우 child 외부키를 null으로 처리
3) SET DEFAULT : master 테이블에 PK가 없는 경우 child 외부키를 기본값으로 처리
4) DEPENDENT : master 테이블에 PK가 존재할 떄만 child 입력 허용
5) NO ACTION : 참조무결성을 위반하는 입력 액션을 취하지 않음
3. ALTER TABLE
♢ 칼럼 추가 : ALTER TABLE 테이블명 ADD (칼럼명 데이터타입);
♢ 칼럼 삭제 : ALTER TABLE 테이블명 DROP COLUMN 칼럼명;
♢ 칼럼 설정 변경 : ALTER TABLE 테이블명 MODIFY (칼럼명 데이터타입 default 제약조건);
- 칼럼 크기 축소는 null만 있거나 행이 없는 경우만 가능
- null만 있는 경우 데이터타입 변경 가능
♢ 칼럼명 변경 : ALTER TABLE 테이블명 RENAME COLUMN 원칼럼명 TO 새칼렴명;
♢ 제약조건 추가 : ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건;
♢ 제약조건 제거 : ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건;
4. RENAME TABLE
- RENAME 변경전테이블명 TO 변경테이블명; (ANSI 표준)
- ALTER TABLE 테이블명 RENAME TO 테이블명;
5. DROP TABLE
- DROP TABLE 테이블명 (CASCADE CONSTRAINT);
- 테이블의 데이터와 구조 삭제, 복구 불가
- CASCADE CONSTRAINT : 관련 테이블의 참조 제약조건도 삭제하여 참조 무결성 준수
6. TRUNCATE TABLE
- TRUNCATE TABLE 테이블명;
- 테이블 데이터 전체 삭제, 로그를 기록하지 않기 때문에 rollback 불가
3절 DML (데이터 조작어)
1. INSERT INTO 테이블명 (a1, a2, ..) VALUE (b1, b2, ..);
2. UPDATE 테이블명 SET a1=b1, a2=b2, ... WHERE 조건;
3. DELETE FROM 테이블명 WHERE 조건;
4. SELECT 칼럼명 FROM 테이블명 WHERE 조건; (SELECT DISTINCT 칼럼명 .. : 데이터 중복없이 조회/ ALIAS : SELECT 칼럼명 AS '별명')
와일드카드 : '*' (모두), '%' (0개 이상의 문자), '_'(한 글자)
5. 문자열 합성 연산자
- CONCAT()
- ORACLE : ' || ', SQL SERVER : ' + '
4절 TCL (트랜잭션 제어어)
1. 트랜잭션 : DB의 논리적 연산 단위, 하나 이상의 sql문을 포함
♢ 트랜잭션의 특성 'ACID'
- 원자성(atomicity) : all or nothing
- 일관성(consistency) : 트랜잭션으로 인한 데이터베이스 상태의 모순이 없음
- 고립성(isolation) : 부분적인 실행결과에 다른 트랜잭션 접근 불가, locking으로 고립성
- 영속성(durability) : 트랜잭션의 결과는 영구적으로 저장됨
* 트랜잭션에 대한 격리성이 낮은 경우
1) dirty read : 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽는 것
2) non-repeatable read : 한 트랜잭션 내에 같은 쿼리를 두번 수행했는데, 그 사이 다른 값을 수정 또는 삭제하는 바람에 두 쿼리의 결과가 다르게 나타나는 현상
3) phantom read : 한 트랜잭션 내에 같은 쿼리를 두 번 수행했는데. 첫번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상
2. TCL
- 데이터 무결성 보장을 목적으로 함
- 영구 변경 전 확인과 연관 작업을 동시 처리 가능
- oracle
1) sql문장 실행하면 트랜잭션 시작, TCL를 실행하면 트랜잭션 종료
2) DDL 실행 시 자동 커밋
3) DB 정상 종료 시 자동커밋, DB 접속 단절로 종료 시 자동 롤백
3. COMMIT
- 데이터를 DB에 영구적으로 반영하는 명령어
- 커밋 시 트랜잭션이 완료되어 LOCKING 해제
- sql server는 기본적으로 자동 커밋
4 ROLLBACK
- 트랜잭션 시작 이전의 상태로 되돌림
- ROLLBACK TO SAVEPOINT1; (oracle)
- ROLLBACK TRAN SAVEPOINT1; (sql server)
5. SAVEPOINT
- 트랜잭션 저장 시점
- 트랜잭션 일부만 롤백할 수 있도록 중간 상태를 저장하는 명령어
- SAVEPOINT SVPT1; (ORACLE)
- SAVE TRAN SVPT1; (SQL SERVER)
5절 WHERE
1.연산자
♢ 종류
1) 비교연산자
- =, >, >=, <, <=
2) 부정비교연산자
- not 컬럼명 비교연산자
- !=, ^=, <>
3) sql 연산자
- BETWEEN A AND B
- IN (리스트)
- LIKE '문자열'
- IS NULL
- NOT BETWEEN A AND B / NOT IN (리스트) / IS NOT NULL
4) 논리연산자
- AND, OR, NOT
♢ 우선순위
- 부정 > 비교 > 논리
- ( ) > NOT > 비교/SQL > AND > OR
2. 부분 범위 처리
♢ ROWNUM
- ORACLE 처리 결과 집합의 각 행에 임시로 부여되는 번호
- 조건절 내에서 행의 개수를 제한하는 목적으로 사용
select *
from (select rownum as rnum
from tbl01
order by seq)
where rnum between 1 and 10; --seq 기준으로 정렬한 데이터를 1부터 10개 출력6절 함수
1. 단일행 함수
♢ 문자형 함수
- LOWER, UPPER, LENGTH
- CONCAT(A,B) : 문자열 결합
- SUBSTR('문자열', '시작위치', '길이') : 문자열 부분 추출
- LTRIM, RTRIM, TRIM : 공백제거
- ASCII : 아스키코드값 출력
♢ 숫자형 함수
- ABS(절대값), SIGN(부호)
- MOD : 나머지, 연산 '%'
- ROUND(반올림), CEIL(올림), FLOOR(버림) : 함수(a,b) 소수점 이후 n번째 자리까지 출력
- TRUNC : 절사 (ex. TRUNC(38.5432) > 38, TRUNC(38.5432.3333, 3) > 38.333
♢ 날짜형 함수
- SYSDATE / GETDATE()
- EXTRACT : 날짜형 부분추출 (sql server : datepart)
- NEXT_DAT(기준일, 'sun') : 기준일에서 가장 가까운 다음 일요일
♢ 변환형 함수
- TO_NUMBER, TO_CHAR, TO_DATE
- SQL SERVER : CAST, CONVERT
♢ NULL 관련 함수
- NVL(C, D) : C가 널이면 D로 변환
- NVL2(C, D1, D2) : C가 널이면 D1, 아니면 D2
- NULLIF(D1, D2) : D1=D2이면 널, 다르면 D1 출력
- COALESCE(D1, D2, ..) : 널이 아닌 첫 값 출력
- ISNULL(C, D) : 널이면 D, 아니면 C
** null값이 포함된 연산은 무조건 null
2. 데이터 변환
- 명시적 형변환, 암시적 형변환(자동 데이터타입 변환)
3. 조건문
- CASE WHEN 조건절 THEN 출력값 ELSE 기본값 END
case when 컬럼 = 'a' then 1 else 0
= case 컬럼 when 'a' then 1 else 0- DECODE(COL, 기준값1, 출력값1, .. , 기본값)
7절 GROUP BY, HAVING
1.집계함수(null값 제외하고 연산, *count(*)는 null 포함)
- ALL, DISTINCT
- SUM, AVG, MAX, MIN, VARIAN, STDDEV
- COUNT
2. GROUP BY : 그룹 기준 설정, alias 사용불가
3. HAVING : GROUP BY절에 의한 집계 데이터에 출력 조건을 줌
8절 ORDER BY
1. ORDER BY
- 특정 칼럼을 기준으로 정렬
- default : asc(오름차순)
- 내림차순 : desc
*** oracle 에서는 null값을 가장 큰 값으로 취급, sql server는 null값을 가장 작은 값으로 취급하여 정렬
2. SELECT 실행 순서
select 컬럼명 as 'abc' --5
from 테이블명 --1
where 조건식 --2
group by 칼럼/표현식 --3
having 조건식 --4
order by 칼럼/표현식 --6 (마지막)
9절 JOIN
1. 조인
- 여러 테이블을 연결 또는 결합하여 데이터를 출력
- 일반적으로 PK와 FK의 연관성에 의해 성립
2. 등가조인(equi join) - 두 테이블의 칼럼 값이 정확히 일치하는 경우 대부분 PK와 FK 관계 기반
3. 비등가 조인(non equi join) - 두 테이블의 칼럼 값이 정확하게 일치하지 않는 경우, 부등호나 between 연산자를 통해 조인
2장 SQL 활용
1절 표준조인
1. 일반 집합 연산자
UNION - 합집합
INTERSECT - 교집합
EXCEPT(MINUS) - 차집합
CROSS JOIN - 곱집합(생길 수 있는 모든 데이터 조합)
2. 순수관계 연산자 : 관계형 DB를 새롭게 구현
1) select 연산은 where절로 구현
2) project 연산은 select절로 구현
3) (natural) join 연산은 다양한 Join 으로 구현
4) divide 연산은 현재 사용x
3. FROM절 JOIN 형태
- INNER JOIN
- NATURAL JOIN
- USING 조건절
- ON 조건절
- CROSS JOIN
- OUTER JOIN
4. NATURAL JOIN
- 같은 이름을 가진 칼럼 전체에 대한 등가 조인
- using, on 사용 불가
- 같은 데이터 유형의 칼럼만 조인 가능
- alias, 테이블명 사용 불가
- sql >> select c1, c2, .. from tbl1 natural join tbl2;
5. INNER JOIN
- 행에 동일한 값이 있는 칼럼 조인
- join의 default 옵션
- using, on 조건절 필수
- cross join , outer join 동시 사용 불가
- 두 테이블에 동일 이름의 칼럼이 있을 경우 select절에 alias 필수
* using 조건절 : 같은 이름을 가진 칼럼명 중 등가 조인 대상 칼럼 선택, sql server 지원안함, 조건절에 alias, 테이블명 불가
* on 조건절 : 다른 이름을 가진 칼럼간 조인 가능(alias, 테이블명 필수)
6. CROSS JOIN
- 가능한 모든 조합으로 조인 (곱집합)
- M*N건의 데이터 조합 발생
7. OUTER JOIN
- 조인 조건에서 행에 동일한 값이 없는 칼럼 조인
- using, on 조건절 필수
1) LEFT OUTER JOIN
- 좌측 테이블 데이터 조회 후 우측 테이블 조인 대상 데이터 조회
select c1
from t1 a, t2 b
where a.c1 = b.c1(+);
select c1
from t1 a left outer join t2 b
on (a.c1=b.c1);2) RIGHT OUTER JOIN
3) FULL OUTER JOIN
- 조인 시 좌측, 우측 테이블의 모든 데이터를 읽어 join, 중복 데이터 삭제
2절 집합 연산자
1. 집합 연산자 : 조인 없이 여러 테이블의 관련 데이터를 조회하는 연산자
2. UNION(합집합)
- 칼럼 수와 데이터 타입이 모두 동일한 테이블 간 연산만 가능, 중복제거, 정렬o
- UNION ALL : 중복된 행도 전부 출력, 정렬x
3. INTERSECT(교집합)
4. MINUS, EXCEPT(차집합)
*3절 계층형 쿼리와 셀프 조인
1. 계층형 쿼리(hierarchical query) - oracle지원
* TOP DOWN
SELECT LEVEL AS LV --항목의 깊이
, HGRNK_DPT_CD
, DPT_CD
, LPAD(' ', 5*(LEVEL-1) || DPT_NM AS DPT_NM --LPAD(왼쪽에 빈칸을 LV만큼 삽입)
, CONNECT_BY_ROOT DPT_NM AS ROOT_NM --최상위 노드 데이터
, CONNECT_BY_ISLEAF AS LEAF --하위노드 유무(유:0, 무:1)
, SYS_CONNECT_BY_PATH(DPT_NM, '/') AS PATH --경로
, CONNECT_BY_ISCYCLE AS ISLOOP --루프가 발생하는 노드(유:1, 무:0) connect by절 nocycle 필수
FROM VW_DPT
START WITH HGRNK_DPT_CD IS NULL --최상위 노드 조건
CONNECT BY NOCYCLE PRIOR DPT_CD = HGRANK_DPT_CD --prior 하위 = 상위 (탑다운) *connect by, select, where에 사용* BOTTOM UP
SELECT LEVEL
, HGRANK_DPT_CD
, DPT_CD
, LPAD(' ', 5*(LEVEL-1) || DPT_NM AS DPT_NM
FROM VW_DPT
START WITH DPT_NM LIKE '%' || '팀'
CONNECT BY DPT_CD = PRIOR HGRANK_DPT_CD2. 셀프조인
- 한 테이블 내에서 두 칼럼이 연관관계가 있는 경우
- table alias 필수
4절 서브쿼리
1. 종류
♢ 동작 방식에 따른 분류
- 비연관 서브쿼리
- 연관 서브쿼리
♢ 반환 데이터 형태에 따른 분류
- 단일 행 서브쿼리 : 실행결과 1건 이하
- 다중 행 서브쿼리 : 실행결과가 여러개
* 다중 행 비교 연산자
- IN : 서브쿼리 결과 중 하나의 값이라도 동일하다는 조건
- ANY : 서브쿼리 결과 중 하나의 값이라도 만족한다는 조건
- ALL : 서브쿼리 모든결과값을 만족한다는 조건
- EXISTS : 서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건
2. 스칼라 서브쿼리
- 값 하나를 반환하는 서브쿼리
- select 절에 사용
3. 뷰 : 가상테이블. from절에 사용하는 뷰는 인라인 뷰라고 함
♢ 장점
- 독립성 : 테이블 구조 변경 자동 반영
- 편리성 : 쿼리를 단순하게 작성할 수 있음, 자주 사용하는 sql문 형태를 뷰로 생성하여 사용 가능
- 보안성 : 뷰를 생성할 때 칼럼을 제외할 수 있음
5절 그룹함수
1. ROLLUP
- GROUP BY로 묶인 칼럼의 소계 계산, 계층 구조로 GROUP BY의 칼럼 순서가 바뀌면 결과 값 바뀜
- GROUP BY ROLLUP(E1, E2) : E1와 E2별 소계 / E1별 소계 / 총 합계
>> SELECT POSITION, SUM(BONUS) AS SUM
FROM TBL01
GROUP BY ROLLUP(POSITION); --마지막에 [POSITION]NULL [SUM]TOTAL 데이터 출력
2. CUBE
- 조합 가능한 모든 값에 대해 다차원 집계
- GROUP BY CUBE(A,B) : A와 B별 소계 / A별 소계 / B별 소계/ 총 합계
3. GROUPING SETS
- 특정 항목에 대한 소계 계산, group by의 칼럼 순서와 무관하게 개별적으로 처리
- group by grouping sets(a,b) : a별 소계 / b별 소계
6절 윈도우 함수
1. 윈도우 함수 : 여러 행 간의 관계 정의하거나 행과 행간을 비교, 중첩 불가
♢ 순위 함수
1) RANK : 중복 순위 포함(중간 순위 비움)
2) DENSE_RANK :중복 순위 무시(중간 순위 비우지 않음)
3) ROW_NUMBER : 단순히 행 번호 표시. 값에 무관하게 고유한 순위 부여
♢ 일반 집계 함수
- sum, max, min, avg, count
♢ 행 순서 함수
1) FIRST_VALUE / LAST_VALUE
2) LAG / LEAD : 이전 행 / 이후 행(oracle)
* LEAD(E,A) - E에서 A번째 행의 값을 호출하는 형태 (A기본값은 1)
♢ 비율 관련 함수
1) PERCENT_RANK() : 백분율 순서
2) CUME_DIST() : 현재 행 이하 값을 포함한 누적 백분율
3) NTILE(A) : 전체 데이터 A등분
4) RATIO_TO_REPORT : 총 합계에 대한 값의 백분율
2. 문법
> SELECT WINDFN(A) OVER (PARTITION BY 칼럼 ORDER BY 칼럼 윈도잉절) FROM TBL;
1) partition by : 그룹핑 기준
2) order by : 순위지정 기준
3) 윈도잉절 : 함수의 대상이 되는 행 범위 지정
7절 DCL
1. DCL : 유저 생성 및 권한 제어 명령어
♢ GRANT : 권한 부여 ( GRANT 권한 ON 오브젝트 TO 유저명; )
♢ REVOKE : 권한 회수 ( REVOKE 권한 ON 오브젝트 TO 유저명;)
2. 권한(privileges)
- DML 관련 권한 : select , insert, update, delete, alter, all
- REFERENCES : 지정된 테이블을 참조하는 제약조건을 생성하는 권한
- INDEX : 지정된 테이블에서 인덱스를 생성하는 권한
3. oracle user
- scott : 테스트용 샘플 유저
- sys : dba 권한이 부여된 최상위 유저
- system : db의 모든 시스템 권한이 부여된 dba
4. ROLE : 권한의 집합, 권한을 일일이 부여하지 않고 ROLE로 여러 권한 부여
- CONNECT : CREATE SESSION
- RESOURCE : CREATE CLUSTER / TRIGGER / PROCEDURE / OPERATOR / TYPE / TABLE / SEQUENCE / INDEXTYPE
8절 절차형 SQL
1. 절차형 SQL
- 일반적인 개발 언어처럼 절차 지향적인 프로그램을 작성할 수 있도록 제공하는 기능
- sql문의 연속적인 실행 및 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈 생성
** PL/SQL
- 블록 구조 : 블록 내에 DML, 쿼리, IF, LOOP 등 사용 가능
- declare : 선언부, 블록에서 사용할 변수나 인수에 대한 정의
- begin : 실행부, 처리할 sql문 정의
- exception : 예외 처리부, 블록에서 발생한 에러 처리 로직 정의, 선택 항목
2. 프로시저
3. 사용자 정의 함수
- 절차형 sql을 로직과 함께 DB 내 저장해 놓은 명령문 집합
- return을 통해 반드시 하나의 값을 반환(*프로시저와 다른 점)
4. 트리거
- DML문이 수행되었을 때 자동으로 동작하는 프로그램 (*프로시저는 execute로 실행)
- DCL과 TCL 실행 불가 (*프로시저는 사용 가능)
- DB에 로그인하는 작업에도 정의 가능