[정보처리기사 필기] 3-3. SQL 응용
2020 시나공 정보처리기사 필기책 참고
주의! 중요도가 낮은 항목(C)은 일부 제외
1. SQL의 개념 – B
- SQL의 개요
- 국제 표준 DB 언어. 많은 회사에서 RDB를 지원하는 언어로 채택
- 관계대수와 관계해석을 기초로 한 혼합 데이터 언어
- 질의어지만 질의 기능 뿐만 아니라 데이터 구조 정의, 데이터 조작, 제어 기능을 모두 갖춤
- SQL의 분류
- DDL(Data Define Language, 데이터 정의어)
- SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의하거나 변경 또는 삭제할 때 사용
- 논리적 데이터 구조와 물리적 데이터 구조의 사상을 정의
- DB 관리자나 DB 설계자가 사용
- CREATE : SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의
- ALTER : TABLE에 대한 정의를 변경하는 데 사용
- DROP : SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 삭제
- DML(Data Manipulation Language, 데이터 조작어)
- 저장된 데이터를 실질적으로 처리하는 데 사용
- DB 사용자와 DB 관리 시스템 간의 인터페이스 제공
- SELECT : 튜플 검색
- INSERT : 튜플 삽입
- DELETE : 튜플 삭제
- UPDATE : 튜플 내용 변경
- DCL(Data Control Language, 데이터 제어어)
- 데이터의 보안, 무결성, 회복, 병행 수행 제어 등을 정의하는데 사용
- DB 관리자가 데이터 관리를 목적으로 사용
- COMMIT : 결과를 물리적 디스크로 저장. 작업이 완료되었음을 관리자에게 알려줌
- ROLLBACK : 비정상적으로 종료되었을 때 원래의 상태로 복구
- GRANT : 사용자에게 사용 권한을 부여
- REVOKE : 사용자의 사용 권한을 취소
2. DDL – C
- DDL 개념
- DB를 구축하거나 수정 할 목적으로 사용하는 언어
- 데이터 사전에 여러개의 테이블로서 저장됨
- CREATE SCHEMA
- 스키마를 정의하는 명령문
- 스키마 이름과 소유권자나 허가권자를 정의
CREATE SCHEMA 스키마명 AUTHORIZATION 사용자_id;
// ID가 홍길동인 스키마 대학교를 정의하는 SQL문
CREATE SCHEMA 대학교 AUTHORIZATION 홍길동;
- CREATE DOMAIN
- 도메인을 정의하는 명령문
- SQL 에서 지원하는 전체 테이터 타입의 값이 아니고 일부분일 때, 그 값의 범위를 도메인으로 정의
- 일반적인 데이터 타입처럼 사용
CREATE DOMAIN 도메인명 [AS] 데이터_타입
[DEFAULT 기본값]
[CONSTRAINT 제약조건명 CHECK (범위값)];
// '성별'을 '남' 또는 '여'와 같이 정해진 1개의 문자로 표현되는 도메인 SEX를 정의하는 SQL문
CREATE DOMAIN SEX CHAR(1)
DEFAULT '남'
CONSTRAINT VALID-SEX CHECK(VALUE IN('남', '여'));
- CREATE TABLE
- 테이블을 정의하는 명령문
CREATE TABLE 테이블명
(속성명 데이터_타입 [DEFAULT 기본값] [NOT NULL], ...
[, PRIMARY KEY(기본키_속성명, ...)]
[, UNIQUE(대체키_속성명, ...)]
[, FOREIGN(외래키_속성명, ...)]
[REFERENCES 참조테이블(기본키_속성명, ...)]
[ON DELETE 옵션]
[ON UPDATE 옵션]
[, CONSTRAINT 제약조건명] [CHECK (조건식)]);
- UNIQUE : 대체키로 사용할 속성. 중복된 값 가질 수 없음
- FOREIGN KEY ~ REFERENCES ~
- 참조할 다른 테이블과 참조할 때 사용할 외래키 속성 지정
- 외래키가 지정되면 참조 무결성의 CASCADE 법칙이 적용됨
- ON DELETE 옵션 : 튜플이 삭제되었을 때 옵션. NO ACTION, CASCADE, SET NULL, SET DEFAULT
- ON UPDATE 옵션 : 참조 속성 값이 변경되었을 때 옵션. NO ACTION, CASCADE, SET NULL, SET DEFAULT
- NO ACTION : 기본 테이블에 아무런 조취를 취하지 않음
- CASCADE : 참조 테이블의 튜플이 삭제, 수정되면 기본 테이블 튜플 삭제, 수정됨
- SET NULL : 참조 테이블에 변화가 있으면 기본 테이블의 관련 튜플 속성 값을 NULL로 변경
- SET DEFAULT : 참조 테이블에 변화가 있으면 기본 테이블의 관련 튜플 속성 값을 기본값으로 변경
- CONSTRAINT : 제약 조건의 이름을 지정.
- CHECK : 속성 값에 대한 제약 조건 정의
CREATE TABLE 학생
(이름 VARCHAR(15) NOT NULL,
학번 CHAR(8),
전공 CHAR(5),
성별 SEX,
생년월일 DATE,
PRIMARY KEY(학번),
FOREIGN KEY(전공) REFERENCES 학과(학과코드)
ON DELETE SET NULL
ON UPDATE CASCADE,
CONSTRAINT 생년월일제약
CHECK(생년월일>='1980-01-01'));
- CREATE VIEW
- 뷰를 정의하는 명령문
CREATE VIEW 뷰명[(속성명[, 속성명, ...])]
AS SELECT문;
- SELECT문은 서브 쿼리로 사용하여 SELECT문의 결과로서 뷰를 생성
- SELECT문에는 UNION이나 ORDER BY절을 사용할 수 없음
- 속성명을 기술하지 않으면 SELECT문의 속성명이 자동으로 사용됨
// <고객> 테이블에서 '주소'가 '안산시'인 고객들의
// '성명'과 '전화번호'를 '안산고객'이라는 뷰로 정의
CREATE VIEW 안산고객(성명, 전화번호)
AS SELECT 성명, 전화번호
FROM 고객
WHERE 주소 = '안산시';
- CREATE INDEX
- 인데스를 정의하는 명령문
CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명(속성명 [ASC | DESC] [,속성명 [ASC | DESC]])
[CLUSTER];
- UNIQUE : 사용된 경우 중복값 없는 속성으로 인덱스 생성. 생략된 경우 중복 값 허용
- 정렬
- ASC : 오름차순
- DESC : 내림차순
- 생략시 : 오름차순
- CLUSTER : 사용하면 인덱스가 클러스터드 인덱스로 설정됨
// <고객> 테이블에서 UNIQUE한 특성을 갖는 '고객번호' 속성에 대해
// 내림차순으로 정렬하여 '고객번호_idx'라는 이름으로 인덱스를 정의
CREATE UNIQUE INDEX 고객번호_idx
ON 고객(고객번호 DESC);
- ALTER TABLE
- 테이블에 대한 정의를 변경하는 명령문
// ADD : 새로운 속성을 추가할 때 사용
ALTER TABLE 테이블명 ADD 속성명 데이터_타입 [DEFAULT '기본값'];
// ALTER : 특정 속성의 Default 값을 변경할 때 사용
ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값'];
// DROP COLUMN 특정 속성을 삭제할 때 사용
ALTER TABLE 테이블명 DROP COLUMN 속성명[CASCADE];
// <학생> 테이블에 최대 3문자로 구성되는 '학년' 속성 추가
ALTER TABLE 학생 ADD 학년 VARCHAR(3);
// <학생> 테이블의 '학번' 필드의 데이터 타입과 크기를 VARCHAR(10)으로
// 하고 NULL 값이 입력되지 않도록 변경
ALTER TABLE 학생 ALTER 학번 VARCHAR(10) NOT NULL;
- DROP
- 스키마, 도메인, 테이블, 뷰, 인덱스, 제약 조건 등을 제거하는 명령문
DROP SCHEMA 스키마명 [CASCADE | RESTRICT];
DROP DOMAIN 도메인명 [CASCADE | RESTRICT];
DROP TABLE 테이플명 [CASCADE | RESTRICT];
DROP VIEW 뷰명 [CASCADE | RESTRICT];
DROP INDEX 인덱스명 [CASCADE | RESTRICT];
DROP CONSTRAINT 제약조건명;
- CASCADE : 참조하는 다른 모든 개체를 함께 제거. 참조 무결성 제약 조건을 설정하기 위해 사용
- RESTRICTED : 다른 개체가 제거할 요소를 참조중일 때는 제거를 취소
3. DCL – C
- DCL 개념
- 데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는 데 사용하는 언어
- DB 관리자(DBA)가 데이터 관리를 목적으로 사용
- GRANT / REVOKE
- 사용자에게 권한을 부여하거나 취소하기 위한 명령어
// 사용자등급 지정 및 해제
GRANT 사용자등급 TO 사용자_ID_리스트 [IDENTIFIED BY 암호];
REVOKE 사용자등급 FROM 사용자_ID_리스트
// ID가 "NABI"인 사람에게 DB 및 테이블을 생성할 수 있는 권한 부여
GRANT RESOURCE TO NABI;
// ID가 "STAR"인 사람에게 DB 정보를 검색할 수 있는 권한을 부여
GRANT CONNECT TO STAR;
// 테이블 및 속성에 대한 권한 부여 및 취소
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 : 권한 취소 시 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 취소
// ID가 "NABI"인 사람에게 <고객> 테이블에 대한 모든 권한과 다른 사람
// 에게 권한을 부여할 수 있는 권한까지 부여
GRANT ALL ON 고객 TO NABI WITH GRANT OPTION;
// ID가 "STAR"인 사람에게 부여한 <고객> 테이블에 대한 권한 중 UPDATE
// 권한을 다른 사람에게 부여할 수 있는 권한만 취소
REVOKE GRANT OPTION FOR UPDATE ON 고객 FROM STAR;
- COMMIT
- 트랜잭션이 성공적으로 끝나면 DB가 새로운 일관성 상태를 가지기 위해 변경된 모든 내용을 반영
- COMMIT 명령을 실행하지 않아도 DML문이 성공적으로 완료되면 자동으로 COMMIT 됨
- DML이 실패하면 자동으로 ROLLBACK이 되도록 Auto Commit 기능을 설정할 수 있음
- ROLLBACK
- 아직 COMMIT되지 않은 변경된 모든 내용들을 취소하고 DB를 이전 상태로 되돌리는 명령어
- 일부분만 완료된 트랜잭션은 ROLLBACK 되어야 함
- SAVEPOINT
- 트랜잭션 내에 ROLLBACK 할 위치인 저장점을 지정하는 명령어
- ROLLBACK 시 지정된 SAVEPOINT까지 트랜잭션 처리 내용이 취소됨
4. DML – B
- DML의 개념
- DB 사용자가 응용 프로그램이나 질의어를 통해 데이터를 실질적으로 관리하는데 사용되는 언어
- 사용자와 DBMS 간의 인터페이스를 제공
- 삽입문(INSERT INTO ~ )
- 기본 테이블에 새로운 튜플을 삽입할 때 사용
INSERT INTO 테이블명([속성명1, 속성명2, ...])
VALUES (데이터1, 데이터2, ...)
- 대응하는 속성과 데이터는 개수와 데이터 유형이 일치해야 함
- 기본 테이블의 모든 속성을 사용할 때는 속성명을 생략할 수 있음
- SELECT문을 사용하여 다른 테이블의 검색 결과를 삽입할 수 있음
// <사원> 테이블에 (이름 - 홍승현, 부서 - 인터넷)을 삽입
INSERT INTO 사원(이름, 부서) VALUES ('홍승현', '인터넷');
// <사원> 테이블에 (장보고, 기획, 05/03/73, 홍제동, 90)을 삽입
INSERT INTO 사원 VALUES('장보고', '기획', #05/03/73#, '홍제동', 90);
// <사원> 테이블에 있는 편집부의 모든 튜플을 편집부원(이름, 생일, 주소, 기본급)
// 테이블에 삽입
INSERT INTO 편집부원(이름, 생일, 주소, 기본급)
SELECT 이름, 생일, 주소, 기본급
FROM 사원
WHERE 부서 = '편집';
- 삭제문(DELETE FROM ~ )
- 기본 테이블에 있는 튜플들 중에서 특정 튜플을 삭제할 때 사용
DELETE
FROM 테이블명
[WHERE 조건];
- 모든 레코드를 삭제할 때는 WHERE절을 생략
- 모든 레코드를 삭제하더라도 테이블 구조는 남아있기 때문에 DROP과는 다름
// <사원> 테이블에서 "임꺽정"에 대한 튜플을 삭제
DELETE
FROM 사원
WHERE 이름 = '임꺽정';
// <사원> 테이블에서 "인터넷" 부서에 대한 모든 튜플을 삭제
DELETE
FROM 사원
WHERE 부서 = '인터넷';
// <사원> 테이블의 모든 레코드를 삭제
DELETE
FROM 사원;
- 갱신문(UPDATE ~ SET ~)
- 기본 테이블에 있는 튜플들 중에서 특정 튜플의 내용을 변경할 때 사용
UPDATE 테이블명
SET 속성명 = 데이터[, 속성명=데이터, ...]
[WHERE 조건];
// <사원> 테이블에서 "홍길동"의 '주소'를 "수색동"으로 수정
UPDATE 사원
SET 주소 = '수색동'
WHERE 이름 = '홍길동'
// <사원> 테이블에서 "황진이"의 '부서'를 "기획부"로 변경하고
// '기본급'을 5만원 인상
UPDATE 사원
SET 부서 = '기획', 기본급 = 기본급 + 5
WHERE 이름 = '황진이';
- DML의 4가지 유형
- SELECT~ FROM~ WHERE~
- INSERT INTO~ VALUES~
- DELETE~ FROM~ WHERE~
- UPDATE~ SET~ WHERE~
5. DML - SELECT-1 – A
- 일반 형식
SELECT [PREDICATE] [테이블명.]속성명 [AS 별칭][, [테이블명.]속성명, ...]
FROM 테이블명[, 테이블명, ...]
[WHERE 조건]
[ORDER BY 속성명 [ASC | DESC]];
- SELECT절
- PREDICATE : 불러올 튜플 수를 제한할 명령어를 기술
- ALL : 모든 튜플을 검색할 때 지정. 주로 생략
- DISTINCT : 중복된 튜플이 있으면 그 중 첫 번째 한 개만 검색
- DISTINCTROW : 중복된 튜플을 제거하고 한 개만 검색. 튜플 전체를 대상으로 함
- 속성명 : 검색하여 불러올 속성(열) 또는 속성을 이용한 수식을 지정
- 모든 속성을 지정할 때는 ‘*‘를 기술
- 두 개 이상의 테이블을 대상으로 검색할 때는 ‘테이블명.속성명’으로 표현
- AS : 속성 및 연산의 이름을 다른 제목으로 표시하기 위해 사용
- PREDICATE : 불러올 튜플 수를 제한할 명령어를 기술
- FROM절 : 검색될 데이터들을 포함하는 테이블명 기술
- WHERE절 : 검색할 조건을 기술
- ORDER BY절 : 특정 속성을 기준으로 정렬하여 검색할 때 사용
- ASC : 오름차순
- DESC : 내림차순
- 생략 : 오름차순
- 조건 연산자 / 연산자 우선순위
- 조건 연산자
- 비교 연산자 : =, <>, >, <, >=, <=
- 논리 연산자 : NOT, AND, OR
- LIKE 연산자 : %(모든 문자 대표), -(문자 하나 대표), #(숫자 하나 대표)
- 연산자 우선순위
- 산술 연산자 : x, /, +, - -> 왼쪽에서 오른쪽으로 갈수록 낮아짐
- 관계 연산자 : =, <>, >, >=, <, <= -> 모두 같음
- 논리 연산자 : NOT, AND, OR -> 왼쪽에서 오른쪽으로 갈수록 낮아짐
- 산술 > 관계 > 논리
6. DML - SELECT-2 – A
- 일반 형식
SELECT [PREDICATE] [테이블명.]속성명 [AS 별칭][, [테이블명.]속성명, ...]
[, 그룹함수(속성명) [AS 별칭]]
[, WINDOW함수 OVER (PARTITION BY 속성명1, 속성명2, ...
ORDER BY 속성명3, 속성명4, ...) [AS 별칭]]
FROM 테이블명[, 테이블명, ...]
[WHERE 조건]
[GROUP BY 속성명, 속성명, ...]
[HAVING 조건]
[ORDER BY 속성명 [ASC | DESC]];
- 그룹함수 : GROUP BY절에 지정된 그룹별로 속성의 값을 집계할 함수를 기술
- WINDOW 함수 : GROUP BY절을 이용하지 않고 속성의 값을 집계할 함수를 기술
- PARTITION BY : WINDOW 함수가 적용될 범위로 사용할 속성을 지정
- ORDER BY : PARTITION 안에서 정렬 기준으로 사용할 속성을 지정
- GROUP BY절 : 특정 속성을 기준으로 그룹화하여 검색할 때 사용. 그룹 함수와 함께 사용
- HAVING절 : GROUP BY와 함꼐 사용되며, 그룹에 대한 조건을 지정
- 집합 연산자를 이용한 통합 질의
- 집합 연산자를 사용하여 2개 이상의 테이블의 데이터를 하나로 통합
SELECT 속성명1, 속성명2, ...
FROM 테이블명
UNION | UNION ALL | INTERSECT | EXCEPT
SELECT 속성명1, 속성명2, ...
FROM 테이블명
[ORDER BY 속성명 [ASC | DESC]];
7. DML - JOIN – B
- JOIN의 개념
- 2개의 테이블에 대해 연관된 튜플들을 결합하여 하나의 새로운 릴레이션을 반환
- 일반적으로 FROM절에 기술하지만, 어느 곳에서나 사용할 수 있음
- INNER JOIN
- 조건이 없는 INNER JOIN을 수행하면 CROSS JOIN과 동일한 결과를 얻음
- EQUI JOIN
- JOIN 대상 테이블에서 공통 속성을 기준으로 ‘=’ 비교에 의해 같은 값을 가지는 행을 연결
- JOIN 조건이 ‘=’일 때 동일한 속성이 두 번 나타남, 중복된 속성을 제거한 것을 NATURAL JOIN
- EQUI JOIN에서 연결 고리가 되는 공통 속성을 JOIN 속성이라고 함
// WHERE절을 이용한 EQUI JOIN의 표기 형식
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2, ...
WHERE 테이블명1.속성명 = 테이블명2.속성명;
// NATURAL JOIN절을 이용한 EQUI JOIN의 표기 형식
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 NATURAL JOIN 테이블명2;
// JOIN ~ USING절을 이용한 EQUI JOIN 표기 형식
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 JOIN 테이블명2 USING(속성명);
- NON-EQUI JOIN
- JOIN 조건에 ‘=’ 조건이 아닌 나머지 비교 연산자를 사용하는 JOIN 방법
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2, ...
WHERE (NON-EQUI JOIN 조건);
- OUTER JOIN
- JOIN 조건에 만족하지 않는 튜플도 결과로 출력하기 위한 방법
// LEFT OUTER JOIN
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 LEFT OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2
WHERE 테이블명1.속성명 = 테이블명2.속성명(+);
// RIGHT OUTER JOIN
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 RIGHT OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2
WHERE 테이블명1.속성명(+) = 테이블명2.속성명;
// FULL OUTER JOIN
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 FULL OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;
- SELF JOIN
- 같은 테이블에서 2개의 속성을 연결하여 EQUI JOIN
SELECT [별칭1.]속성명, [별칭1.]속성명, ...
FROM 테이블명1 [AS] 별칭1 JOIN 테이블명1 [AS] 별칭2
ON 별칭1.속성명 = 별칭2.속성명;
댓글남기기