정보처리기사 8장 SQL응용 정리

정보처리기사 실기 준비를 하면서 기록하면서 정리하기 위해 포스팅합니다.

 

1.DDL(Data Define Language 데이터 정의어)

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

종류: CREATE, ALTER, DROP

 

*CREATE

 

스키마 정의-CREATE SCHEMA

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

도메인 정의-CREATE DOMAIN

CREATE DOMAIN SEX CHAR(1)
DEFAULT '남'
CONSTRAINT VALID-SEX CHECK(VALUE IN('남','여'));

도메인의 이름은 SEX, 기본값은 남, 제약조건 이름은 VALID-SEX이고 남, 여 중 하나의 값만 지정 가능하다

 

테이블 정의-CREATE TABLE

CREATE TABLE instructor(
id CHAR(5) PRIMARY KEY,
name CHAR(15) NOT NULL,
dept CHAR(15),
FOREIGN KEY(dept) REFERENCES department(dept)
ON DELETE SET NULL 
ON UPDATE CASCADE
);

instructor 테이블 정의, id는 문자5에 기본키, name은 문자 15에 null이 올 수 없고

dept는 문자15 속성이며 department 테이블의 dept속성을 참조하는 외래키이다.

department테이블에서 튜플이 삭제되면 관련된 모든 튜플의 dept속성의 값은 null이 된다.

department테이블의 dept 속성이 변경되면 instructor테이블의 관련된 모든 속성 값도 같은 값으로 변경되어야 한다.

 

 

제약조건 설정

CONSTRAINT 생년월일제약 CHECK(생년월일>='1980-01-01');

'생년월일'THRTJDDPSMS 1980-01-01이후의 값만을 저장할 수 있고, 이 제약 조건의 이름은 생년월일제약 이다.

 

뷰 정의 - CREATE VIEW

인덱스 정의 -CREATE INDEX 

CREATE UNIQUE INDEX 고객번호_idx ON 고객(고객번호 DESC);

고객 테이블에서 중복되지 않는 특성을 가지는 '고객번호'속성에 대해 내림차순으로 정렬하여 '고객번호_idx'라는 이름으로 인덱스를 정의한다.

 

ALTER TABLE

테이블의 정의를 변경하는 명령문이다.

ALTER TABLE 테이블명 ADD 속성명 데이터_타입

ADD - 속성 추가

ALTER - 속성의 DEFAULT값을 변경할 때 사용

DROP COLUMN - 특정 속성 삭제

 

DROP

스키마, 도메인, 기본 테이블, 뷰 테이블, 인덱스, 제약 조건 등을 제거하는 명령문이다.

DROP TABLE 학생 CASCADE;

학생 테이블을 제거하되, 학생 테이블을 참조하는 모든 데이터를 함께 제거한다.

CASCADE: 제거할 요소를 참조하는 다른 모든 개체를 함께 제거한다.

RESTRICT: 다른 개체가 제거할 요소를 참조중일 때는 제거를 취소한다.

 

 

 

2.DCL(Data Control Language 데이터 제어어)

데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는데 사용하는 언어이다.

종류: COMMIT, ROLLBACK, GRANT, REVOKE

 

*GRANT / REVOKE

데이터베이스 관리자가 사용자에게 권한을 부여하거나 취소하기 위한 명령어이다.

#사용자 등급

DBA: 데이터베이스 관리자

RESOURCE: 데이터베이스 및 테이블 생성 가능자

CONNECT: 단순 사용자

 

*COMMIT

트랜잭션 처리가 정상적으로 완료된 후 트랜잭션이 수행한 내용을 데이터베이스에 반영하는 명령이다.

(트랜잭션: 데이터베이스에서 하나의 논리적 기능을 수행하기 위한 일련의 연산 집합, 작업의 단위)

 

*ROLLBACK

변경되었으나 아직 COMMIT되지 않은 모든 내용들을 취소하고 데이터베이스를 이전 상태로 되돌리는 명령어이다.

 

 

3.DML(Data Manifulation Language 데이터 조작어)

데이터베이스 사용자가 저장된 데이터를 실질적으로 관리하는데 사용되는 언어이다.

종류: SELECT, INSERT, DELETE, UPDATE

 

*INSERT INTO

삽입문은 기본 테이블에 새로운 튜플을 삽입할 때 사용한다.

INSERT INTO 사원(이름,부서) VALUES('홍길동','영업');

사원 테이블에 이름-홍길동 부서-영업을 삽입한다.

 

 

*DELETE FROM

삭제문은 기본 테이블에 있는 튜플들 중에서 특정 튜플을 삭제할 때 사용한다.

모든 레코드를 삭제하더라도 테이블 구조는 남아 있어서 DROP과는 다르다.

DELETE FROM 사원 WHERE 이름='홍길동';

 사원 테이블에서 이름이 홍길동인 튜플을 삭제한다.

 

*UPDATE~SET

갱신문은 기본 테이블에 있는 튜플들 중에서 특정 튜플의 내용을 변경할 때 사용한다.

UPDATE 사원 SET 부서='기획', 기본급=기본급+5
WHERE 이름='홍길동';

사원 테이블에서 홍길동의 부서를 기획으로, 기본급을 5만원 인상시킨다.

 

*SELECT

SELECT * FROM 사원 WHERE 부서 ='기획' AND 주소 ='서울';

사원 테이블에서 기획 부서에 근무하면서 서울에 사는 사람의 튜플을 검색한다.

 

#정렬검색

ORDER BY절에 특정 속성을 지정하여 지정된 속성으로 자료를 정렬하여 검색한다.

SELECT TOP 2 * FROM 사원 ORDER BY 주소 DESC;

사원 테이블에서 주소를 기준으로 내림차순 정렬시켜 상위 2개 튜플만 검색한다.

 

#그룹함수 : GROUP BY절에 지정된 그룹별로 속성의 값을 집계할 함수를 기술한다.

WINDOW 함수 : GROUP BY절을 이용하지 않고 속성의 값을 집계할 함수를 기술한다.

 -PARTITION BY : WINDOW함수의 적용 범위가 될 속성을 지정한다.

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

 -ROW_NUMBER(): 윈도우별로 각 레코드에 대한 일련번호를 반환한다.

 -RANK(): 윈도우별로 순위를 반환하며, 공동 순위를 반영한다.

 -DENSE_RANK(): 윈도우별로 순위를 반환하며, 공동 순위를 무시한다.

 

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

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

SELECT 과목이름, MIN(점수) AS 최소점수, MAX(점수) AS 최대점수 
FROM 성적 GROUP BY 과목이름 HAVING AVG(점수)>=90;

성적 테이블에서 과목별 점수 평균이 90점 이상인 과목이름, 최소점수, 최대점수를 검색한다.

 

 

#집합 연산자

UNION: 두 SELECT문의 조회 결과를 통합하여 모두 출력한다. 중복된 행은 한번만 출력한다.(합집합)

UNION ALL: 중복된 행도 그대로 출력한다.(합집합)

INTERSECT:  두 SELECT문의 조회 결과 중 공통된 행만 출력한다(교집합)

EXCEPT: 첫 번째 SELECT문의 조회 결과에서 두 번째 SELECT문의 조회 결과를 제외한 행을 출력한다.(차집합)

 

SELECT * FROM 사원 UNION SELECT * FROM 직원;

사원 테이블과 직원 테이블을 통합하는 질의문이다. (단, 중복된 행은 출력하지 않음)

 

*JOIN

2개의 릴레이션에서 연관된 튜플들을 결합하여, 하나의 새로운 릴레이션을 반환한다.

-INNER JOIN

-OUTER JOIN : JOIN 조건에 만족하지 않는 튜플도 결과로 출력하기 위한 방법

  -LEFT OUTER JOIN : INNER JOIN의 결과를 구한 후 우측 항 릴레이션의 어떤 튜플과도 맞지 않는 좌측 항의 릴레이션에 있는 튜플들에 NULL값을 붙여 표기한다.

  -RIGHT OUTER JOIN: INNER JOIN의 결과를 구한 후 좌측 항 릴레이션의 어떤 튜플과도 맞지 않는 우측 항의 릴레이션에 있는 튜플들에 NULL값을 붙여 표기한다.

  -FULL OUTER JOIN : LEFT+RIGHT JOIN