정보처리기사 3과목 데이터베이스 구축 - 3장 SQL 응용 요점 정리

1. SQL의 개념

- 1974년 IBM 연구소에서 개발한 SEQUEL에서 유래

- 국제 표준 데이터베이스 언어, 많은 회사에서 RDB를 지원하는 언어로 채택

- 관계대수 관계해석 기초, 질의어지만 질의 기능뿐 아닌 데이터 구조 정의, 데이터 조작, 제어 기능 모두 갖춤

● DDL : SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의하거나 변경 또는 삭제 시 사용하는 언어

-논리적 데이터 구조와 물리적 데이터 구조의 사상을 정의

- DB 관리자나 DB 설계자가 사용

- CREAT : SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의

- ALTER : TABLE에 대한 정의 변경

- DROP : SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 삭제

● DML : DB 사용자가 응용 프로그램이나 질의어 통해 저장된 데이터를 실질적으로 처리하는 데 사용되는 언어

- DB 사용자와 DB 관리 시스템 간의 인터페이스 제공

- SELECT : 튜플 검색

- INSERT : 새로운 튜플 삽입

- DELETE : 조건에 맞는 튜플 삭제

- UPDATE: 조건에 맞는 튜플의 내용 변경

● DCL : 보안, 무결성, 회복, 병행 수행 제어 등을 정의하는데 사용되는 언어

- DB 관리자가 데이터 관리 목적으로 사용

- COMMIT : 명령 결과 실제 물리적 디스크에 저장, DB 작업이 정상적으로 완료되었음을 관리자에게 알려줌

- ROLLBACK : 데이터 조작 작업이 비정상적으로 종료 시 원래 상태로 복구

- GRANT : DB 사용자에게 사용 권한을 부여

- REVOKE : DB 사용자의 사용 권한 취소

​

2. DDL

- DB 구조, 데이터 형식, 접근 방식 등 DB를 구축하거나 수정할 목적으로 사용하는 언어

- 번역한 결과 데이터 사전이라는 특별한 파일에 여러 개의 테이블로서 저장

● CREAT SCHEMA : 스키마 정의하는 명령어

● CREAT DOMAIN : 도메인 정의하는 명령어, 값의 범위가 SQL의 일부분일 때 그 값의 범위를 도메인으로 정의 가능

● CREAT TABLE : 테이블 정의하는 명령어

- 속성의 NOT NULL 제약 조건, 속성의 초기값 지정, CHECK 제약 조건의 정의

● CREAT VIEW : 뷰를 정의하는 명령문

● CREAT INDEX : 인덱스 정의하는 명령문

● ALTER TABLE : 테이블에 대한 정의 변경하는 명령어

● DROP : 스키마, 도메인, 기본 테이블, 뷰 테이블, 인덱스, 제약 조건 등을 제거하는 명령어, 테이블 자체 제거 명령어

- CASCADE : 제거할 요소를 참조하는 다른 모든 개체를 함께 제거, 즉 주 테이블의 데이터 제거 시 각 외래 키와 관계를 맺고 있는 모든 데이터를 제거하는 참조 무결성 제약 조건을 설정하기 위해 사용

- RESTRICTED : 다른 개체가 제거할 요소를 참조 중 일 때는 제거를 취소

- TUPLE, INDEX, VIEW 제거, 테이블은 CASCADE 옵션일 때 제거

​

3. DML

- DB 사용자가 응용 프로그램이나 질의어를 통해 저장된 데이터를 관리하는 데 사용하는 언어

● 삽입문 : INTSERT INTO 테이블명(속성명1, 속성명2, ···) VALUE (데이터1, 데이터2,···)

- 테이블에 새로운 튜플 삽입, 대응하는 속성과 데이터의 개수와 데이터 유형 일치해야 함

- 기본 테이블의 모든 속성 사용 시 속성명을 생략 가능

- SELECT문 사용하여 다른 테이블의 검색 결과 삽입 가능

● 삭제문 : DELETE FROM 테이블명 WHERE 조건

- 특정 튜플 삭제 시 사용, 모든 레코드 삭제 시 WHERE절 생략, 모든 레코드 삭제해도 테이블은 남아있음(DROP과 대조)

● 갱신문 : UPDATE 테이블명 SET 속성명 = 데이터 WHERE 조건

- 특정 튜플 내용 변경 시 사용

​

4. DCL

- 보안, 무결성, 회복, 병행 수행 제어 등을 정의하는데 사용되는 언어

● GRANT : DB 사용자에게 사용 권한을 부여

● REVOKE : DB 사용자의 사용 권한 취소

● COMMIT : 트랜잭션이 성공적으로 끝나면 DB가 새로운 일관성 상태를 가지기 위해 변경된 모든 내용을 DB에 반영해야 하는데 이때 사용하는 명령어

● ROLLBACK : 아직 COMMIT 되지 않은 변경된 모든 내용들을 취소하고 DB를 이전 상태로 되돌리는 명령어

- 트랜잭션 전체가 성공적으로 끝나지 못하면 일부 변경된 내용만을 DB에 반영되는 비일관성인 상태 가질 수 있기 때문에 일부분만 완료된 트랜잭션은 롤백 되어야 한다

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

​

5. DML - SELECT-1​

● 기본 검색 : 사원 테이블에 있는 모든 튜플을 검색하는 SQL문

- SELECT * FROM 사원;

- DISTICNT : 중복을 제거하여 첫 번째 한 개만 검색

● 조건 지정 검색 : 사원 테이블에서 부서가 개발인 튜플 검색 SQL문

- SELECT * FROM 사원 WHERE 부서 = "개발";

- 날짜 데이터 : 숫자로 취급, ' ' or # #으로 묶어 줌

- NULL이 아닌 값 검색 : IS NOT NULL

● 정렬 검색 : ORDER BY 절에 특정 속성 지정 ASC : 오름차순, DESC : 내림차순

● 하위 질의 : 조건절에 다시 SELECT문을 넣어 그 결과를 조건으로 검색

- Not In ( ) : 포함되지 않는 데이터 의미

● 복수 테이블 검색 : 여러 테이블을 대상으로 검색을 수행

​

6. DML - SELECT-2

● 그룹 함수 : GROUP BY 절에 지정된 그룹별로 속성의 값을 집게 할 함수를 기술

- GROUP BY : 그룹 지정 검색, 일반적으로 GROUP BY 절은 그룹 함수와 함께 사용

- 사원 테이블에서 부서별 경력의 평균을 구하는 SQL문 => SELECT 부서, AVG(경력)AS 평균 FROM 상여금 GROUP BY 부서;

● WINDOW 함수 : GROUP BY 절을 이용하지 않고 속성의 갑을 집계할 함수를 기술

- PARTITION BY : WINDOW 함수가 적용될 범위로 사용할 속성을 지정

- ORDER BY : PARTITION 안에서 정렬 기준으로 사용할 속성을 지정

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

● 집합 연산자를 이용한 통합 질의 : 집합 연산자를 사용하여 2개 이상의 테이블의 데이터를 하나로 통합

- 두 개의 SELECT문에 기술한 속성들은 개수와 데이터 유형이 서로 동일해야 함

- UNION : 두 SELECT문의 결과를 통합하고 중복된 행 한 번만 출력

- UNION ALL : 두 SELECT문의 결과를 통합하고 중복된 행도 그대로 출력

- INTERSECT : 두 SELECT문의 결과 중 공통된 행만 출력

- EXCEPT : 첫 번째 SELECT문의 결과에서 두 번째 SELECT문의 결과를 제외한 행 출력

​

7. DML - JOIN

- 2개의 테이블에 대해 연관된 튜플들을 결합하여, 하나의 새로운 릴레이션을 반환

- JOIN은 일반적으로 FROM절에 기술, 릴레이션이 사용되는 어느 곳에서나 사용 가능

● INNER JOIN : 조건이 없는 INNER JOIN 수행 시 CROSS JOIN과 동일 결과

- EQUI JOIN : JOIN 대상 테이블에서 공통 속성 기준으로 '=' 비교에 의해 같은 값을 가지는 행을 연결하여 결과 생성

조건 '='일 때 중복 => 중복 제거 한 번만 표기 방법 = NATURAL JOIN, 값이 일치하는 레코드만 조인

- NON-EQUI JOIN : JOIN 조건에 '=' 조건이 아닌 나머지 비교 연산자(>,<,<>,<=,>=)를 사용하는 JOIN 방법

● OUTER JOIN

- LEFT OUTER JOIN : 좌측 릴레이션에 있는 튜플 모두 표시, 우측 릴레이션에는 관련 있는 튜플만 표시

- RIGHT OUTER JOIN : 우측 릴레이션에 있는 튜플 모두 표시, 좌측 릴레이션에는 관련 있는 튜플만 표시

- FULL OUTER J0IN : LEFT, RIGHT OUTER JOIN 합쳐 놓은 것

● SELF JOIN : 같은 테이블에서 2개의 속성을 연결하여 EQUI JOIN을 하는 JOIN 방법

● CROSS JOIN : 첫 번째 테이블의 행수를 두 번째 테이블의 행수로 곱한 것만큼의 행을 반환