SQL
cf. 구문에서 [] 는 생략가능을 의미
DDL(Data Define Language)
논리적 데이터 구조와 물리적 데이터 구조의 사상을 정의
DDL이 번역된 결과가 Data Dictionary 에 여러개의 테이블로서 저장됨
임시테이블이 튜플 하나인 경우 값으로서 비교 대입이 가능하니 유의
구문
CREATE
CREATE SCHEMA 스키마명 AUTHORIZATION 사용자ID;
CREATE DOMAIN 도메인명 [AS] 데이터타입 [DEFAULT 기본값]
[CONSTRAINT 제약조건명 CHECK 범위값]
CREATE TABLE (
속성명 데이터타입 [DEFAULT 기본값] [, NOT NULL] ...
[, PRIMARY KEY(기본키속성명)]
[, UNIQUE(대체키속성명)]
[, FOREIGN KEY(외래키속성명)]
[ REFERENCES 참조테이블(기본키속성명)]
[ ON DELETE 옵션] [ ON UPDATE 옵션]
NO ACTION, CASCADE, SET NULL, SET DEFAULT 등의 옵션
CASCADE 는 TABLE 이 아니라 데이터 값을 삭제하는 것임
[, CONSTRAINT 제약조건명] [ CHECK (제약조건식)]);
CREATE TABLE 신규테이블명 AS SELECT ... FROM ... ;
속성, NOT NULL, DEFAULT 는 적용되는데 제약조건은 ALTER TABLE 필요
CREATE VIEW 뷰명[(속성명, ...)] AS SELECT ... FROM ... ;
속성명은 서브쿼리문의 속성중 가져올걸 말함. 없으면 있는거 다 들고옴
서브쿼리에 UNION, ORDER BY 가 안댐
CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명(속성명[ASC | DESC] ... )
[CLUSTER];
여기서 말하는 클러스터는 동일 인덱스 값을 갖는 튜플들을 묶는것
ALTER
ALTER TABLE 테이블명 ADD 속성명 데이터타입
[DEFAULT 기본값] [ NOT NULL];
ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT 기본값];
ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE];
DROP
DROP SCHEMA 스키마명 [CASCADE | RESTRICTED];
DOMAIN, TABLE, VIEW, INDEX, CONSTRAINT 다 가능
CASCADE 는 참조하는 모든 개체를 제거, RESTRICTED 는 참조시 제거 취소
DML(Data Manipulation Language)
데이터베이스 사용자와 관리자 간의 인터페이스
구문
SELECT
SELECT [PREDICATE] [테이블명.]속성명 [AS 별칭] ...
PREDICATE - ALL, DISTINCT, DISTINCTROW
속성명 대신 그룹함수를 쓸 수 있음
- COUNT, STEDEV, VARANCE,
- ROLLUP , 주어진 속성이 n 개면 n+1 까지 레벨별 그룹별 소계
- CUBE , 주어진 속성이 n 개면 2^n 인 모든경우 그룹별 소계
[WINDOW함수 OVER (PARTITION BY 속성명 ... ORDER BY 속성명] [AS 별칭]
WINDOW 함수
ROW_NUMBER() - 파티션 된 속성의 값에 대해서 일련번호를 붙임
RANK() - 윈도우별로 순위를 반환하며 공동순위 반영
DENSE_RANK() - 윈도우별로 순위를 반환하며 공동순위 미반영
FROM 테이블명 ...
INNER JOIN
CROSS JOIN - 조건없이 테이블명 병행, 데카르트 곱
EQUI JOIN
테이블명 병행 + WHERE 로 속성명 같음 조건. 제일 잘 쓰임
FROM t1 JOIN t2 USING(속성명) -> 속성명 중복
FROM t1 NATURAL JOIN t2 -> 속성명 중복 제거
NON EQUI JOIN
테이블명 병행 + WHERE 로 비교연산 사용
OUTER JOIN 속성명 ON 조건
INNER JOIN 의 결과에 ON 조건이 적용되고,
조건 적용되지 않은 행은 다른 테이블 속성 NULL 로 채워서 밑에 추가.
SELF JOIN
[WHERE 조건]
WHERE t1.name LIKE "%#_"
%는 문자들, #는 숫자, _는 문자하나
WHERE t1.date BETWEEN #01/01/97# AND #12/31/99#
쌍따옴표 없는 #으로 묶이면 날짜를 말함. 달/일/년
WHERE t1.age BETWEEN 19 AND 21
19 <= t1.age <= 21 임
WHERE t1.name IS NULL / IS NOT NULL;
[GROUP BY 속성명 ... ]
[HAVING 조건]
[ORDER BY 속성명 [ASC | DESC]] ...;
ASC 가 디폴트고 속명별로 ASC | DESC 따로 적용해야함.
ORDER BY a1, a2 DESC 면 a1 는 ASC a2 는 DESC 임
INSERT
INSERT INTO 테이블명([속성명 ...]) VALUES (데이터 ...);
기존데이터의 모든 속성을 사용할 때는 속성명을 생략가능
INSERT INTO 테이블명([속성명 ...]) SELECT ... FROM ... ;
DELETE
DELETE FROM 테이블명 [WHERE 조건];
UPDATE
UPDATE 테이블명 SET 속성명 = 데이터값 ... [WHERE 조건];
속성명 = 데이터값 이것만 반복하면서 변경함
DCL(Data Control Language)
데이터의 보안, 무결성, 회복, 병행 수행 제어 등을 정의하는데 사용됨
구문
GRANT REVOKE
DAC(Discretionary Access Control)
GRANT 사용자등급 TO 사용자ID [IDENTIFIED BY 암호];
REVOKE 사용자등급 FROM 사용자ID
사용자등급 - RESOURCE(테이블 생성), CONNECT(읽기), DBA(생성, 읽쓰기)
MAC(Mandatory Access Control)
GRANT 권한리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];
REVOKE [GRANT OPTION FOR] 권한리스트 ON 개체 FROM 사용자
[CASCADE];
권한리스트 - ALL, SELECT, INSERT, DELETE, UPDATE, ALTER ...
WITH GRANT OPTION - 받은 권한을 타유저에 부여할 수 있는 권한도 추가
GRANT OPTION FOR - 있으면 권한리스트에 대한 권한부여권한만 취소함.
CASCADE - WITH GRANT OPTION 권한으로 부여받은 권한 연쇄적 삭제
TCL(Transaction Control Language)
COMMIT
트랜잭션 끝난 후 데이터베이스에 변경내용 반영하는 문. 주기억->보조기억
DML 끝난후 결과에 따라 자동으로 auto commit 하는 기능이 있음
ROLLBACK
ROLLBACK; - SAVEPOINT 랑 관계없이 제일 최근 COMMIT 으로 이동
ROLLBACK TO 세이브포인트이름;
SAVEPOINT
SAVEPOINT 세이브포인트이름;
절차형 SQL( Procedure, Trigger, 사용자 정의 함수)
특징
트랜잭션 언어
DB를 조작하고 트랜잭션을 처리하는 언어. SQL과 TCL, 절차형 SQL 이 속함
디버그 시 Show 로 상세 오류 메시지
주석 가능
PROCEDURE
DB에 저장해서 호출해서 쓸 수 있고, 그래서 Stored Procedure 라고 불림
구문
정의
CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터)
DECLARE 부분이다.
CREATE OR REPLACE PROCEDURE emp_change_s(i_data IN INT) 이렇게
IN 은 호출시 전달. OUT 는 반환, INOUT 은 둘다
IS [지역변수 선언]
선언 안하면 예약어인 IS 만 사용
Begin - End 형식의 Block
CONTROL 부 - 조건문, 반복문
SQL 부 - DML, DCL 이 들어감
EXCEPTION - 예약어로 이거 적고 아래에 구문을 적음
WHEN 에러명 THEN 이하구문 이렇게.
TRANSACTION 부 - COMMIT, ROLLBACK 등이 적힘
실행
EXECUTE / EXEC / CALL 프로시저명;
제거
DROP PROCEDURE 프로시저명;
TRIGGER
삽입, 갱신, 삭제 등 이벤트가 발생할 때마다 관련작업을 자동으로 수행
DB 에 저장되며 무결성유지, 로그 메시지 출력을 위해 사용
구문
생성
CREATE [OR REPLACE] TRIGGER 트리거명 [시기옵] [동작옵] ON 테이블명
동작시기옵션엔 ALTER, BEFORE 가 있음
동작옵션엔 INSERT, DELETE, UPDATE 가 있음
REFERENCING [NEW | OLD] AS 테이블명
NEW 는 INSERT 나 UPDATE 시 새로 추가될 데이터에 별명을 붙임
OLD 는 DELETE 나 UPDATE 시 기존의 데이터에 별명을 붙임
FOR EACH ROW
[WHEN 조건식]
BEGIN - END;
CONTROL
SQL - DML 만 가능하고 DCL 안댐
EXCEPTION - 예약어로 ...
제거
DROP TRIGGER 트리거명;
사용자정의 함수
프로시저처럼 디비에 저장되며 SELECT 만 가능하고 RETURN 으로 값을 하나 반환
BEGIN-END 엔 다른 사용자정의 함수는 가능한데 프로시저는 안댐
구문
생성
CREATE [OR REPLACE] FUNCTION 함수명(파라미터)
CREATE FUNCTION GET_S_NAME(i_성별코드 IN INT)
IN 만 있음에 주의
RETURN 리턴자료형
IS [지역변수선언]
BEGIN - END;
CONTROL - IF a = 10 THEN ~~
SQL - SELECT 문만 가능
EXCEPTION - 예약어
RETURN 반환값; 이 필수로 끝에 와야함
사용
SELECT my_function(a1) FROM t1;
INSERT INTO t1(a1) VALUES my_function(v);
DELETE FROM t1 WHERE a1 = my_function(v);
UPDATE t1 SET a1 = my_function(v);
제거
DROP FUNCTION 함수명;
SQL API
DBMS 에 접근하기 위해 사용하는 API 또는 프레임워크
JDBC(Java Database Connectivity)
1997 썬 마이크로시스템에서 출시
Java SE(Standard Edition) 에 포함되어 있고 JDBC 클래스는 java.sql, javax.sql에 있음
DBMS 에 대한 드라이버 필요
OBDC(Open DataBase Connectivity)
표준 개방형 API. 개발언어 관계 없음
DBMS 에 맞게 연결해주므로 드라이버만 있으면 DBMS 종류 몰라도 됨
1992년 마이크로소프트에서 출시
MyBatis
JDBC 코드를 단순화하여 사용하는 SQL Mapping 기반 오픈 접속 프레임워크
SQL 문을 그대로 사용할 수 있어 국내에 적합
SQL 문장 -> XML 파일 -> DB 연결
SQL Mapping - SQL로 호출되는 테이블이나 열 대이터를 개발언어의 객체로 변환
ORM(Object Relational Mapping)
객체지향 프로그래밍의 객체와 관계형 데이터베이스의 데이터를 Mapping 함
ORM이 생성한 가상의 객체는 DB 와 객체 모두와 독립적임
SQL 코드를 직접 입력하지 않고 ORM 이 자동으로 SQL을 작성해서 편함
제대로 번역됐는지 확인과, ORM은 염두에 두지 않은 DB는 연동이 힘든 단점.
ORM Framework
Django(파이썬), ODB(C++), JAVA(JPA, EclipseLink), Hibernate
동적 API
NVL 함수 필요 없음
기존의 Cursor 로 정적으로 처리하지 않고 문자열 변수에 SQL을 담아 동적으로 처리
동적처리라 사전에 검사 불가능할 뿐더러 프리컴파일도 안댐
정적 SQL 보다 느림
SQL 디버깅
단문 SQL 테스트
DESC 개체명 => 테이블의 경우 필드, 자료형, Nullable, Key, Default value 등을 봄
Oracle - SELECT * FROM DBA_ROLE_PRIVES WHERE GRANTEE = 사용자;
MySql - SHOW GRANTS FOR 사용자@호스트;
절차형 SQL 테스트
SHOW ERRORS; -> 오류 라인, 에러 상세 내용이 표시
Oracle - DBMS_OUTPUT.ENABLE; 후 DBMS_OUTPUT.PUT_LINE(결과값); 로 확인
MySql - SELECT 결과값; 으로 확인
쿼리성능 최적화
APM(Application Performance Management)
쿼리성능 측정도구
리소스 방식 - Nagios
End to End 방식 - VisualMM, 제니퍼, 스카우터
Optimizer
질의어인 SQL이 효율적으로 실행되도록 최적의 경로를 찾아주는 도구
RBO(Rule Based Optimizer)
개발자의 SQL 숙련도 (휴리스틱을 쓰니까) 가 성능 기준이 됨. 계산 쉬움.
CBO(Cost Based Optimizer)
옵티마이저의 예측 알고리즘 성능이 성능 기준이 됨. 계산 빡셈. 잘쓰임
EXPLAIN
실행 계획 확인 명령어
쿼리 재구성
IN 은 전체를 돌고 EXISTS 를 쓰면 존재확인 후 끝나니까 후자가 좋을 때가 많음
실행계획이 맘에 안들면 Hint 를 활용하면 변경 가능
인덱스 재구성
단일 인덱스로 읽기만 하는거면 IOT(Index-Organized Table) 을 씀
인덱스에 값이 들어가 있는 테이블 정렬방식임
DB 관리
데이터 전환 (ETL, Extract Transform Loading) 데이터 이행(Data Migration)
기존 정보 시스템에 축적된 데이터를 Extract 해서
새로 개발할 정보 시스템에서 운영 가능하도록 Transform 하고 Loading 하는 과정
데이터 전환 계획서
개요 - 간단하게 적음
대상 및 범위 - 분류, 전환대상, 테이블 수, 용량
환경 구성 - 원천시스템 구성도, 목적시스템 구성도, 전환 단계별 DISK 용량 산정
조직 및 역할 - 최대한 자세히
일정 - 상세히 일정을 적음
전환 방안 - 데이터 전환 규칙, 데이터 전환 절차, 데이터 전환 방법
정비 방안, 비상 계획 ..
데이터 검증
추출(Extract) : 로그 검증
-> 전환(Transform) : 로그 검증
-> DB 적재 (Load) : 로그 검증
-> DB 적재 후 : 기본 항목 검증(정합성을 검증하는 것)
-> 전환 완료 : 응용 프로그램 검증, 응용 데이터 검증
로그 검증, 기본항목검증, 응용 프로그램 검증, 응용 데이터 검증, 값 검증 이 있음.
오류데이터 측정 및 정제
데이터 품질 측정
시스템 데이터의 정합성 여부 등을 확인해 오류 데이터 확인
원천 시스템 데이터와 목적 시스템 데이터의 정합성 목록이 다름
-> 오류 데이터 측정
오류관리목록
정상 데이터와 오류 데이터의 수를 측정해 만듬
정상 데이터는 업무 영역 등 범위를 나눠서 측정
오류 데이터도 업무 영역 등 범위를 나눠서 위치 및 유형을 확인해 수량 측정
-> 오류 데이터 정제
데이터 정제요청서
원천 데이터의 정제와 전환 프로그램의 수정 전에 만드는 문서
데이터 정제 요건 목록
정제 유형
완전성(누락), 유효성, 일치성(모순된값), 유일성(ID중복 등), 기타
정제 방법
원천(원천DB), 전환(전환프로그램), 모두(원천 + 전환)
오류 상태
Open - 보고만 됨
Assigned - 개발자에게 오류 보고
Fixed - 개발자가 오류 수정
Closed - 테스트 후 오류 발생 안함
Deferred - 연기
Classified - 개발자가 이거 오류 아니라고 확인한 상황
데이터 정제보고서
정제요청서로 수행한 정제의 결과를 정제 ID 별로 문서로 작성
육안으로 정제 요청 데이터와 정제된 데이터 항목을 비교
오류 데이터의 원인, 실제 데이터 정제 건수, 향후 대안 방안 등
댓글 없음:
댓글 쓰기