정보처리기사 요약 정리 - 8장 SQL 응용

8장 SQL 응용(1/3/2)★★★★★

▶ DDL 데이터 정의어

DB를 구축하거나 수정할 목적으로 사용하는 언어

CREATE	SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의
ALTER	TABLE에 대한 정의를 변경
DROP	SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 삭제

 

▶ 스키마를 정의하는 명령문

CREATE SCHEMA 스키마명 AUTHORIZATION 사용자_ID;

 

▶ 도메인을 정의하는 명령문

CREATE DOMAIN 데이터 타입 DEFAULT 기본값

CONSTRAINT 제약조건명 CHECK (VALUE IN 범위값);

 

▶ 테이블을 정의하는 명령문

CREATE TABLE 테이블명(

id CHAR(5) PRIMARY KEY,

name CHAR(10),

sex CHAR(1),

CONSTRAINT sex_ck CHECK (sex='f' or sex='m'),

CONSTRAINT id_fk FOREIGIN KEY(id) REFERENCES doctor(doc_id)

);

→ sex 속성은 f 또는 m만 입력되어야하며 제약조건명은 sex_ck

id속성은 doctor테이블의 기본키인 doc_id를 참조하는 외래키이며,

이 제약 조건명은 id_fk

 

PRIMARY KEY : 기본키 사용 속성 지정

UNIQUE : 대체키 사용 속성, 중복된 값을 가질 수 없음

FOREIGIN KEY ~ REFERENCES ~ : 외래키로 사용할 속성을 지정함

ON DELETE 옵션	참조 테이블의 튜플이 삭제 되었을 때 기본 테이블에 취해야 할 사항을 지정함
ON UPDATE옵션	참조테이블의 참조 속성 값이 변경 되었을 때 기본 테이블에 취해야할 사항을 지정함
NO ACTION 조치 취하지 않음		CASCADE 관련 튜블 삭제나 업데이트	SET NULL 속성값 null	SET DEFAULT 기본값

CONSTRAINT : 약 조건의 이름 지정

CHECK : 성 값에 대한 제약 조건을 정의

 

▶ 뷰를 정의 하는 명령문

CREATE VIEW 뷰명(속성명) AS SELECT 컬럼 FROM 테이블 WHERE 조건;

 

▶ 인덱스를 정의하는 명령문

CREATE [ UNIQUE ]　INDEX 인덱스명 ON 테이블명 (속성명[ASC | DESC]) [ CLUSTER ];

 

UNIQUE

: 사용된 경우 - 중복값이 없는 속성으로 인덱스생성 / 생략된 경우 : 중복값 허용

CLUSTER : 사용하면 인덱스가 클러스터드 인덱스로 설정됨

▶ ALTER TABLE

테이블에 대한 정의를 변경하는 명령문

ALTER TABLE 테이블명 ADD 속성명 데이터_타입 [ DEFAULT '기본값' ];

ALTER TABLE 테이블명 ALTER 속성명 데이터_타입 [ DEFAULT '기본값' ];

ALTER TABLE 테이블명 DROP COLUMN 속성명 [ CASCADE ];

 

ADD 속성 추가 / ALTER 특정속성의 default값 변경 / DROP COLUMN 특정속성 삭제

 

▶ DROP

스키마, 도메인, 기본테이블, 뷰 테이블, 인덱스, 제약조건 등을 제거

DROP SCHEMA 스키마명 [ CASCADE | RESTRICT ];

DROP DOMAIN 도메인명 [ CASCADE | RESTRICT ];

DROP TABLE 테이블명 [ CASCADE | RESTRICT ];

DROP VIEW 뷰명 [ CASCADE | RESTRICT ];

DROP INDEX 인덱스명 [ CASCADE | RESTRICT ];

DROP CONSTRAINT 제약조건명;

CASCADE	제거할 요소를 참조하는 다른 모든 개체 함께 제거
RESTRICT	다른개체가 제거할 요소를 참조중 일때는 제거를 취소

 

▶ DCL 데이터 제어어

COMMIT	트랜잭션이 수행한 내용을 데이터베이스에 반영하는 명령
ROLLBACK	변경은 되었으나 아직 COMMIT되지 않은 모든 내용들을 취소하고 데이터베이스를 이전 상태로 되돌리는 명령
GRANT	데이터베이스 사용자에게 사용 권한을 부여함
REVOKE	데이터베이스 사용자의 사용 권한을 취소함

 

▶ GRANT / REVOKE

GRANT 권한리스트 ON 개체 TO 사용자 [ IDENTIFIED BY 암호 ];

→ GRANT ALL ON 고객 TO NABI WITH GRANT OPTION;

사용자ID가 NABI인 사람에게 고객테이블의 모든권한과 다른사람에게 권한을 부여할수 있는 권한까지 부여

REVOKE [ GRANT OPTION FOR ] 권한리스트 ON 개체 FROM 사용자 [ CASCADE ];

권한 종류 : ALL, SELECT, INSERT, DELETE, UPDATE등

→ REVOKE GRANT OPTION FOR UPDATE ON 고객 FROM STAR;

사용자ID가 STAR인 사람에게 부여한 고객테이블에 대한 권한 중 UPDATE권한을 다른사람에게 부여할 수 있는 권한만 취소

WITH GRANT OPTION	부여받은 권한을 다른 사용자에게 다시 부여할 수 있는권한을 부여
GRANT OPTION FOR	다른 사용자에게 권한을 부여할 수 있는 권한을 취소
CASCADE	권한 취소시 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소함

 

 

▶ SAVEPOINT

트랜잭션 내에 ROLLBACK할 위치인 저장점을 지정하는 명령어

SAVEPOINT S2; → ROLLBACK TO S2;

▶ DML 데이터 조작어

저장된 데이터를 실질적으로 관리하는데 사용되는 언어

SELECT	테이블에서 튜플을 검색함
INSERT	테이블에서 새로운 튜블을 삽입함
DELETE	테이블에서 튜플을 삭제함
UPDATE	테이블에서 튜플의 내용을 갱신함

 

▶ 삽입문 ( INSERT INTO～）

INSERT INTO 테이블명(속성1, 속성2) VALUES (데이터1, 데이터 2);

 

▶ 삭제문 (DELETE FROM~)

DELETE FROM 테이블명 [WHERE 조건];

 

▶ 갱신문 ( UPDATE~SET~)

UPDATE 테이블명 SET 속성명 = 데이터 [WHERE 조건];

 

▶ SELECT

SELECT DISTINCT 컬럼 FROM 테이블명

WHERE 조건

GROUP BY 속성명

HAVING 조건

ORDER BY 정렬조건(기본ASC)

 

DISTINCT : 중복된 튜플이 있으면 그 중 첫 번째 한 개만 표시

GROUP BY : 특정 속성을 기준으로 그룹화하여 검색할 때 사용

HAVING : GROUP BY와 함께 사용되며, 그룹에 대한 조건을 지정함

 

▶ 조건 연산자

비교연산자	<> 같지 않다 >= 크거나 같다 <= 작거나 같다
논리연산자	NOT, AND, OR
LIKE연산자	% 모든문자 _문자하나 #숫자하나

 

▶그룹함수 GROUP BY

COUNT	그룹별 튜플 수
SUM	그룹별 합계
AVG	그룹별 평균
MAX	그룹별 최대값
MIN	그룹별 최소값
STDDEV	그룹별 표준편하
VARIANCE	그룹별 분산
ROLLUP	그룹별 소계구하는 함수 / 하위레벨에서 상위레벨순 / 하위→상위 순
CUBE	그룹별 소계구하는 함수 / 상위레벨에서 하위레벨순 / 상위→하위 순

 

▶WINDOW함수

ROW_NUMBER()	윈도우별로 각 레코드에 대한 일련번호를 반환
RANK()	윈도우별로 순위를 반환하며, 공동 순위 반영
DENSE_RANK()	윈도우별로 순위를 반환하여 공동 순위 무시

 

 

▶집합연산자

UNION	합집합	두 SELECT문의 조회 결과를 통합하여 모두 출력 중복 행은 한번만 출력
UNION ALL	합집합	두 SELECT문의 조회 결과를 통합하여 모두 출력 중복 행도 모두 출력
INTERSECT	교집합	두 SELECT문의 조회 결과중 공통된 행만 출력
EXCEPT	차집합	첫 번째 SELECT문의 조회 결과에서 두 번째 SELECT문의 조회 결과를 제외한 행을 출력함

 

▶ 프로시저 PROCEDURE

일련의 쿼리들을 한 함수처럼 실행위한 쿼리집합
DECLARE (필수) BEGIN (필수) CONTROL SQL EXCEPTION TRENSACTION END (필수)	1) DECLARE 선언부: 프로시저 명칭, 변수, 인수, 데이터타입정의 2) BEGIN/END 시작/종료부: 다수 실행을 제어하는 기본적 단위, 논리적 프로세스를 구성 3) CONTROL 제어부: 기본적으로는 순차적 처리. 조건문과 반복문 이용해 문장 처리. 4) SQL: dml을 주로 사용. DDL 중 TRUNCATE사용 5) EXCEPTION 예외부: 예외 처리 방법 정의 6) TRANSACTION 실행부: 프로시저에서 수행된 DML 수행내역의 DBMS 적용 또는 취소 여부 결정하는 처리부

프로시저 문법	프로시저 호출문 작성
CREATE [OR REPLACE] PROCEDURE 프로시저명 (파라미터명 [IN|OUT|INPUT] 데이터타입, ... ) IS 변수선언 BEGEN 명령어; [COMMIT|ROLLBACK] END;	SQL TOOL을 활용해 직접 실행시키는 경우 EXECUTE 또는 EXEC 명령어를 이용해 프로시저를 실행함. 프로시저에 입출력 변수가 존재하는 경우 변수를 입력하여 실행해야 함. EXECUTE 프로시저명 (파라1, 파라2, ...) ※ DROP PROCEDURE 프로시저명;

구성	설명
[OR REPLACE]	기존 프로시저 존재시 현재 컴파일하는 내용으로 덮어씀.
모드[IN|OUT|INOUT]	IN: OS에서 프로시저로 값전달OUT: 프로시저에서 처리된결과 OS로 전달INOUT: IN+OUT 동시 수행
BEGIN	프로시저 시작
COMMIT	트잭이 끝나고 DB일관성있는 상태에서 하나 트잭 끝낫을 때 사용하는 연산
ROLLBACK	재시작하거나 부분연산취소
END	프로시저 끝

 

▶ 트리거 TRIGGER ※ DCL X

DECLARE (필수) EVENT (필수) BEGIN (필수) CONTROL SQL EXCEPTION END(필수)	이벤트가 발생할 때 관련 작업이 자동으로 수행되게 하는 절차형 SQL DCL을 사용할 수 없으며, DCL이 포함된 프로시저나 함수를 호출하는 경우 오류 발생 EVENT 트리거가 실행되는 조건을 명시 SQL : DML문이 사용
FOR EACH ROW : 각 튜플마다 트리거를 적용한다는 의미

 

▶사용자 정의 함수

종료시 처리 결과로 단일 값만을 반환하는 절차형 SQL
DECLARE (필수) BEGIN (필수) CONTROL SQL EXCEPTION RETURN (필수) END (필수)	CREATE FUNCTION SQL : DML문이 사용 RETURN : 호출 프로그램에 반환할 값이나 변수를 정의 단일값을 반환하며, 출력 파라미터가 없다.
IN : 호출 프로그램이 사용자 정의 함수에게 전달할 때 지정

 

▶ 커서 CURSOR

쿼리문의 처리 결과가 저장되어 있는 메모리 공간을 가리키는 포인터

묵시적커서 (내부에서 자동으로 생성되어 사용하는 커서)

명시적커서 (사용자가 직접 정의해서 사용하는 커서)

 

9장 소프트웨어 개발 보안 구축(1/1/3) ★★★★★

▶소프트웨어 개발 보안 3대 요소[기무가인부] ★★★★★

기밀성	인가되지 않은 사용자와 애플리케이션의 접근에 따른 정보 노출 차단
무결성	완전성, 일관성. 데이터 훼손 및 파손이 되지 않아야 함.
가용성	지속적인 서비스를 유지하며, 인가받은 사용자는 언제나 시스템 정보와 자원을 사용할 수 있음.
인증	자원을 사용하려는 사용자를 확인하는 모든 행위.
부인 방지	데이터를 송수신한 자가 송수신 사실을 부인할 수 없도록 송수신 증거를 제공.

 

▶시큐어 코딩

구현 단계에서 발생할 수 있는 보안 취약점들을 최소화 하기 위해 보안 요소들을 고려해 코딩하는 것

 

▶ 입력 데이터 검증 및 표현 ★★★

SQL 삽입	웹 응용 프로그램에 SQL을 삽입해 내부 데이터베이스 서버의 데이터를 유출 및 변조, 관리자 인증을 우회하는 보안 약점
크로스사이트 스크립팅(XSS)	웹 페이지에 악의적인 스크립트를 삽입해 방문자들의 정보를 탈취하거나, 비정상적인 기능 수행을 유발하는 보안 약점
메모리 버퍼 오버플로우	연속된 메모리 공간을 사용하는 프로그램에서 할당된 메모리의 범위를 넘어선 위치에 자료를 읽거나 쓰려고할때 발생하는 보안 약점. 적절한 버퍼의 크기를 설정하고, 설정된 범위의 메모리 내에서 올바르게 읽고 쓰도록 함으로 예방.