| 2020 정보처리기사 (필기) | 3. 데이터베이스 구축 - SQL 응용

101. SQL의 개념

 

SQL (Structured Query Language)

 

• SQL의 분류

1. DDL(데이터 정의어)

명령어	기능
CREATE	SCHEMA, DOMAIN, TABLE, VIEW, INDEX 정의
ALTER	TABLE에 대한 정의 변경
DROP	SCHEMA, DOMAIN, TABLE, VIEW, INDEX 삭제

 

2. DML(데이터 조작어)

명령어	기능
SELECT	테이블에서 조건에 맞는 튜플 검색
INSERT	테이블에 새로운 튜플 삽입
DELETE	테이블에서 조건에 맞는 튜플 삭제
UPDATE	데이블에서 조건에 맞는 튜플의 내용 변경

 

3. DCL (데이터 제어어)

명령어	기능
COMMIT	명령에 의해 수행된 결과를 실제 물리적 디스크로 저장, 작업이 정상적으로 완료되었음을 알림
ROLLBACK	데이터베이스 조작 작업이 비정상적으로 종료되었을 때 원래의 상태로 복구
GRANT	데이터베이스 사용자에게 사용자 권한 부여
REVOKE	데이터베이스 사용자의 사용권한 취소

 

102. DDL

 

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

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

 

1. CREATE SCHEMA

CREATE SCHEMA 스키마_이름 AUTHORIZATION 사용자_id;

ex) 소유권자의 사용자 ID가 철수인 스키마 '대학교'의 정의

CREATE SCHEMA 대학교 AUTHORIZATION 철수;

 

2. CREATE DOMAIN

CREATE DOMAIN 도메인명 [AS] data_type
[DEFAULT 기본값]
[CONSTRAINT 제약조건명 CHECK(범위값)];

ex) '성별'을 '남'또는 '여'와 같이 정해진 1개의 문자로 표현되는 도메인  SEX를 정의

#정의된 도메인 이름은 'SEX', 문자형이고 크기는 1자
CREATE DOMAIN SEX CHAR(1)
#도메인 SEX를 지정한 속성의 기본값은 '남'
DEFAULT '남'
#SEX를 지정한 속성에는 '남', '여' 중 하나의 값만 지정할 수 있음
CONSTRAINT VALID-SEX CHECK (VALUE IN ('남', '여'));

 

SQL에서 지원하는 기본 data_type

- 정수(Integer): INT(4Byte 정수), SMALLINT(2Byte 정수)

- 실수(Float): FLOAT, REAL, DOUBLE PRECISION

- 형식화된 숫자: DEC(i, j) 단, i: 전체 자릿수, j: 소수부 자릿수

- 고정 길이 문자: CHAR(n) 단, n: 문자 수

- 가변 길이 문자: VARCHAR(n) 단, n: 최대 문자 수

- 고정 길이 비트 열(Bit String): BIT(n), 단, n: 비트 수

- 가변 길이 비트 열: VARBIT(n), 단, n: 최대 비트 수

- 날짜: DATE

- 시간: TIME

 

 

3. CREATE TABLE

CREATE TABLE 테이블명(
  속성명 data_type [DEFAULT 기본값] [NOT NULL], ...,
  [PRIMARY KEY (기본키_속성명)],
  [UNIQUE (대체키_속성명)],
  [FOREIGN KEY (외래키_속성명) REFERENCES 참조테이블(기본키_속성명)]
  [ON DELETE 옵션]
  [ON UPDATE 옵션],
  [CONSTRAINT 제약조건명] [CHECK (조건식)]
);

 

CASCADE : 참조 테이블의 튜플이 삭제되면 기본 테이블의 관련 튜플도 모두 삭제되고, 속성이 변경되면 관련 튜플의 속성 값도 모두 변경

 

 

4. CREATE INDEX

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

 

• 정렬 여부 지정

- ASC : 오름차순 정렬

- DESC : 내림차순 정렬

- 생략(Default)하면 오름차순으로 정렬

 

ex) <고객> 테이블에서 '고객번호' 속성에 대해 내림차순으로 정렬해 '고객번호_INX'라는 이름으로 인덱스 정의

CREATE INDEX 고객번호_INX
ON 고객(고객번호 DESC);

 

5. ALTER TABLE

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

ALTER TABLE 테이블명 ADD 속성명 data_type [DEFAULT '기본값'];

ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값'];

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

- ADD : 새로운 속성(열) 추가

- ALTER : 속성의 기본값(Default) 변경

- DROP : 속성 삭제

 

ex) <학생> 테이블에 최대 3문자로 구성되는 '학년' 속성을 추가하라.

ALTER TABLE 학생 ADD 학년 VARCHAR(3);

 

 

6. DROP

스키마, 도메인, 기본 테이블, 뷰 테이블, 인덱스를 삭제하는 명령문

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

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

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

DROP VIEW 뷰명 [CASCADE | RESTRICT];

DROP INDEX 인덱스명;

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

                  즉 Main Table의 데이터 삭제 시 각 외래키와 관계를 맺고 있는 모든 데이터를 제거

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

 

ex) <학생> 테이블을 삭제하되, <학생> 테이블을 참조하는 모든 테이블을 함께 삭제하라.

DROP TABLE 학생 CASCADE;

 

103. DCL

 

DCL (Data Control Language, 데이터 제어어)

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

 

1. GRANT / REVOKE

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

 

• 사용자등급 지정 및 해제

# 권한 부여
GRANT 사용자 등급 TO 사용자_ID_리스트;
# 권한 취소
REVOKE 사용자 등급 FROM 사용자_ID_리스트;

 

• 사용자 등급 종류

DBA : 데이터 베이스 관리자

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

CONNECT : 단순 사용자

 

ex) 사용자 ID가 "NABI"인 사람에게 데이터베이스 및 테이블을 생성할 수 있는 권한 부여

GRANT RESOURCE TO NABI;

GRANT RESOURCE TO NABI;

 

• 테이블 및 속성에 대한 권한 부여 및 취소

GRANT 권한_리스트 ON 테이블명 TO 사용자 [WITH GRANT OPTION];

REVOKE [GRANT OPTION FOR] 권한_리스트 ON 테이블명 FROM 사용자;

GRANT 권한_리스트 ON 테이블명 TO 사용자 [WITH GRANT OPTION];
REVOKE [GRANT OPTION FOR] 권한_리스트 ON 테이블명 FROM 사용자;

- 권한_리스트 : ALL, SELECT, INSERT, DELETE, UPDATE, ALTER 등

- WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한 부여

- GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있는 권한을 취소함

 

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

GRANT ALL ON 고객 TO NABI WITH GRANT OPTION;

 

2. COMMIT

트랜잭션이 성공적으로 끝나면 데이터베이스가 새로운 일관성 상태를 가지기 위해 

변경된 모든 내용을 데이터베이스에 반영하여야 하는데, 이때 사용하는 명령어

 

 

3. ROLLBACK

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

 

 

4. SAVEPOINT

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

 

104. DML

 

DML (Data Manipulation Language, 데이터 조작어)

데이터베이스 사용자가 응용 프로그램이나 질의어를 통해 저장된 데이터를 실질적으로 관리하는데 사용되는 언어

 

 

• DML 명령문

1. 삽입문(INSERT INTO~)

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

 

ex1) <사원>테이블에 (이름-홍길동, 부서-인터넷)을 삽입

INSERT INTO 사원(이름, 부서) 
VALUES('홍길동', '인터넷');

 

ex2) <사원>테이블에 (장보고, 기획, 05/03/73, 옥계동, 90)을 삽입

INSERT INTO 사원 
VALUES('장보고', '기획', #05/03/73#, '옥계동', 90);

 

ex3) <사원>테이블에 있는 편집부의 모든 튜플을 <편집부원(이름, 생일, 주소, 기본급)> 테이블에 삽입

INSERT INTO 편집부원(이름, 생일, 주소, 기본급) 
SELECT 이름, 생일, 주소, 기본급 
FROM 사원 
WHERE 부서 = '편집';

 

 

2. 삭제문(DELETE FROM~)

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

- 모든 레코드 삭제 시 WHERE절 생략

☞ DELETE문은 테이블 내의 튜플들만 삭제, DROP문은 테이블 자체를 삭제

 

ex1) <사원>테이블에서 "황진이"에 대한 튜플을 삭제

DELETE FROM 사원 WHERE 이름 = '황진이';

 

ex2) <사원>테이블의 모든 레코드 삭제

DELETE FROM 사원;

 

 

3. 갱신문(UPDATE~ SET~)

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

 

ex1) <사원>테이블에서 "홍길동"의 '주소'를 "수색동"으로 수정

UPDATE 사원 
SET 주소='수색동' 
WHERE 이름='홍길동';

 

ex2) <사원>테이블에서 "홍길동"의 '부서'를 "기획부"로 변경하고 '기본급'을 5만원으로 인상시키시오

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

 

• 데이터 조작어 4가지 유형

SELECT~ FROM~ WHERE~

INSERT INTO~ VALUES~

DELETE~ FROM~ WHERE~

UPDATE~ SET~ WHERE~

 

 

105. DML - SELECT(1)

 

검색문 (SELECT~ FROM~)

SELECT  [PREDICATE] [테이블명.]속성명1, [테이블명.]속성명2, ......,
[그룹함수(속성명)],
[WINDOW함수 OVER(PARTITION BY 속성명1, 속성명2, .... ORDER BY 속성명 3, 속성명4,...)]
FROM 테이블명1, 테이블명2, ...
[WHERE 조건]
[GROUP BY 속성명1, 속성명2, ...]
[HAVING 조건]
[ORDER BY 속성명 [ASC | DESC]];

- PREDICATE : 불러올 튜플 수를 제한할 명령어 기술

> ALL : 모든 튜플 검색, 주로 생략

> DISTINCT : 중복된 튜플이 있으면 그중 첫 번째 한 개만 검색

 

• LIKE 연산자

대표문자	%	_	#
의미	모든 문자를 대표	문자 하나를 대표	숫자 하나를 대표

 

 

1. 기본 검색

ex1) <사원>테이블의 모든 튜플 검색

SELECT * FROM 사원;

SELECT 사원.* FROM 사원;

 

ex2) <사원>테이블에서 '주소'만 검색하되 같은 '주소'는 한 번만 출력하시오.

SELECT DISTINCT 주소 FROM 사원;

 

ex3) <사원>테이블의 모든 튜플 검색

SELECT * FROM 사원;

SELECT 사원.* FROM 사원;

 

 

2. 조건 지정 검색

ex1) <사원>테이블에서 "기획"부서의 모든 튜플 검색

SELECT * FROM 사원 WHERE 부서='기획';

 

ex2) <사원>테이블에서 "기획"부서에 근무하면서 "대흥동"에 사는 사람의 튜플 검색

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

 

ex3) <사원>테이블에서 '부서'가 "기획"이거나 "인터넷"인 튜플 검색

SELECT * FROM 사원 WHERE 부서='기획' OR 부서='인터넷';

 

ex4) <사원>테이블에서 성이 "김"인 사람의 튜플 검색.

SELECT * FROM 사원 WHERE 이름 LIKE "김%";

 

ex5) <사원>테이블에서 '생일'이 '01/01/69'에서 '12/31/73' 사이인 튜플 검색

SELECT * FROM 사원 WHERE 생일 BETWEEN #01/01/69# AND #12/31/73#;

 

ex6) <사원>테이블에서 '주소'가 NULL인 튜플 검색

SELECT * FROM 사원 WHERE 주소 IS NULL;

 

 

3. 정렬 검색

ex1) <사원>테이블에서 '주소'를 기준으로 내림차순 정렬시켜 검색

SELECT * FROM 사원 ORDER BY 주소 DESC;

 

ex2) <사원>테이블에서 '부서'를 기준으로 오름차순 정렬, 같은 '부서'에 대해서는 '이름'을 기준으로 내림차순 정렬시켜 검색

SELECT * FROM 사원 ORDER BY 부서 ASC, 이름 DESC;

 

 

4. 하위 질의

ex1) '취미'가 "나이트댄스"인 사원의 '이름'과 '주소' 검색

SELECT 이름, 주소 FROM 사원 WHERE 이름=(SELECT 이름 FROM 여가활동 WHERE 취미='나이트댄스'); 

 

ex2) 취미활동을 하지 않는 사원들 검색

SELECT * FROM 사원 WHERE 이름 NOT IN(SELECT 이름 FROM 여가활동); 

 

 

5. 복수 테이블 검색

ex1) '경력'이 10년 이상인 사원의 '이름', '부서', '취미', '경력' 검색

SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력 FROM 사원, 여가활동 

WHERE 여가활동.경력>=10 AND 사원.이름=여가활동.이름; 

 

 

107. DML - SELECT(2)

 

검색문 (SELECT~ FROM~)

SELECT [PREDICATE] [테이블명.]속성명1, [테이블명.]속성명2, ......,
[그룹함수(속성명)],
[WINDOW함수 OVER(PARTITION BY 속성명1, 속성명2, .... ORDER BY 속성명 3, 속성명4,...)]
FROM 테이블명1, 테이블명2, ...
[WHERE 조건]
[GROUP BY 속성명1, 속성명2, ...]
[HAVING 조건]
[ORDER BY 속성명 [ASC | DESC]];

- 그룹함수 : GROUP BY절에 지정된 그룹별로 속성의 값 집계

> COUNT(속성명) : 그룹별 튜플 수

> AVG(속성명) : 그룹별 평균

> SUM(속성명) : 그룹별 합계

- WINDOW 함수 : GROUP BY절을 이용하지 않고 속성의 값 집계

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

> RANK( ) : 윈도우별로 순위 반환, 공동 순위 반영

> DENSE_RANK( ) : 윈도우별로 순위 반환, 공동 순위 무시하고 순위 부여

- GROUP BY절 : 특정 속성을 기준으로 그룹화하여 검색할 때 허용. 그룹합수와 함께 사용

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

 

 

1. WINDOW 함수 이용 검색

ex1) <상여금>테이블에서 '상여내역'별로 '상여금'에 대한 일련 번호를 구하시오. (단, 순서는 내림차순, 속성명은 'NO'로 할것)

SELECT 상여내역, 상여금

ROW_NUMBER( ) OVER(PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS NO

 FROM 상여금 

 

ex2) <상여금>테이블에서 '상여내역'별로 '상여금'에 대한 순위를 구하시오. (단, 순서는 내림차순, 속성명은 '순위'로 할것)

SELECT 상여내역, 상여금 

RANK( ) OVER(PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS 순위

FROM 사원 WHERE 부서='기획' AND 주소='대흥동';

 

 

2. 그룹 지정 검색

ex1) <상여금>테이블에서 '부서'별 '상여금'의 평균을 구하시오.

SELECT 부서, AVG(상여금) AS 평균 FROM 상여금 

GROUP BY 부서;

 

ex2) <상여금>테이블에서 부서별 튜플 수 검색

SELECT 부서, COUNT(*) AS 사원수 FROM 상여금 

GROUP BY 부서;

 

ex3) <상여금>테이블에서 '상여금'이 100이상인 사원이 2명 이상인 '부서'의 튜플 수를 구하시오

SELECT 부서, COUNT(*) AS 사원수 FROM 상여금 

WHERE 상여금>=100

GROUP BY 부서

HAVING COUNT(*)>=2;

 

 

검색문 (SELECT~ FROM~)

SELECT 속성명1, 속성명2, ......,
FROM 테이블명1, 테이블명2, ...
UNION | UNION ALL | INTERSECT | EXCEPT
SELECT 속성명1, 속성명2, ......
FROM 테이블명1, 테이블명2, ...
[ORDER BY 속성명 [ASC | DESC]];

- 집합 연산자의 종류

> UNION : 두 SELECT문의 조회 결과를 통합하여 모두 출력, 중복된 행 한번만 출력

> UNION ALL :  두 SELECT문의 조회 결과를 통합하여 모두 출력, 중복된 행도 그대로 출력

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

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

 

 

3. 집합 연산자를 이용한 통합 질의

ex1) <사원>테이블과 <직원>테이블을 통합하는 질의문을 작성하시오 (단, 같은 레코드 중복 X)

SELECT * FROM 사원 

UNION

SELECT * FROM 직원;