정보처리기사 02. SQL 응용-1 (DDL, DCL, DML)

 

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

 

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

- 번역한 결과가 데이터 사전(Data Dictionary)이라는 특별한 파일에 여러 개의 테이블로 저장된다.

 

 

DDL의 3가지 유형

명령어	기능
CREATE	SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의함
ALTER	TABLE에 대한 정의를 변경하는 데 사용함
DROP	SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 삭제함

 

 

CREATE TABLE

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

 

- 기본 테이블에 포함될 모든 속성에 대하여 속성명과 그 속성의 데이터 타입, 기본값, NOT NULL 여부를 지정한다.

- PRIMARY KEY: 기본키로 사용할 속성을 지정함

- UNIQUE: 대체키로 사용할 속성을 지정함, 중복된 값을 가질 수 없음

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

- CONSTRAINT: 제약 조건의 이름을 지정함

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

 

 

예제) '이름', '학번' '전공', '성별', '생년월일'로 구성된 <학생> 테이블을 정의하는 SQL문을 작성하시오.

제약 조건

- '이름'을 NULL이 올 수 없고, '학번'은 기본키이다.

- '전공'은 <학과> 테이블의 '학과코드'를 참조하는 외래키로 사용된다.

- <학과> 테이블에서 삭제가 일어나면 관련된 튜플의 전공 값을 NULL로 만든다.

- <학과> 테이블에서 '학과코드'가 변경되면 전공 값도 같은 값으로 변경한다. 

- '생년월일'은 1980-01-01 이후의 데이터만 저장할 수 있다.

- 제약 조건의 이름은 '생년월일제약'으로 한다.

- 각 속성의 데이터 타입은 적당하게 지정한다. 단 '성별'은 도메인 'SEX'를 사용한다.

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 INDEX

CREATE VIEW 뷰명[(속성명[, 속성명, ...])]
AS SELECT문;

 

 

CREATE INDEX는 인덱스를 정의하는 명령문이다.

- UNIQUE: 사용된 경우에는 중복 값이 없는 속성으로 인덱스를 생성, 생략된 경우 중복 값을 허용하는 속성으로 인덱스를 생성한다.

정렬여부지정

- ASC: 오름차순 정렬 (Default)

- DESC: 내림차순 정렬

- CLUSTER: 사용하면 인덱스가 클러스터드 인덱스로 설정됨(인덱스 키의 순서에 따라 데이터가 정렬되어 저장되는 방식)

 

예제) <고객> 테이블에서 UNIQUE한 특성을 갖는 '고객번호' 속성에 대해 내림차순으로 정렬하여 '고객번호_idx'라는 이름으로 인덱스를 정의하시오.

CREATE INDEX 고객번호_idx

ON 고객(고객번호 DESC);

 

 

ALTER TABLE

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

 

ALTER TABLE은 테이블에 대한 정의를 변경하는 명령문이다.

- ADD: 새로운 속성(열)을 추가할 때 사용한다.

- ALTER: 특정 속성의 Default 값을 변경할 때 사용한다.

- DROP CLUMN: 특정 속성을 삭제할 때 사용한다.

 

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

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

예제) <학생> 테이블의 '학번' 필드의 데이터 타입과 크기를 VARCHAR(10)으로 하고 NULL 값이 입력되지 않도록 변경하시오.

ALTER TALBE 학생 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 제약조건명;

 

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

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

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

 

예제) <학생> 테이블을 제거하되, <학생> 테이블을 참조하는 모든 데이터를 함께 제거하시오.

DROP TABLE 학생 CASCADE;

 

 

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

 

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

- DCL은 데이터베이스 관리자(DBA)가 데이터 관리를 목적으로 사용한다.

 

DCL의 종류

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

 

GRANT/REVOKE

GRANT 사용자등급 TO 사용자_ID_리스트 [IDENTIFIED BY 암호];
REVOKE 사용자등급 FROM 사용자_ID_리스트;

 

- GRANK: 권한 부여를 위한 명령어

- REVOKE: 권한 취소를 위한 명령어

 

예제) 사용자 ID가 'NABI'인 사람에게 데이터베이스 및 테이블을 생성할 수 있는 권한을 부여하는 SQL문을 작성하시오.

GRANT RESOURCE TO NABI;

예제) 사용자 ID가 'STAR'인 사람에게 단순히 데이터베이스에 있는 정보를 검색할 수 있는 권한을 부여하는 SQL문을 작성하시오.

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"인 사람에게 <고객> 테이블에 대한 모든 권한과 다른 사람에게 권한을 부여할 수 있는 권한까지 부여하는 SQL문을 작성하시오.

GRANT ALL ON 고객 TO NABI WITH GRANT OPTION;

 

COMMIT

 

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

- COMMIT 명령을 실행하지 않아도 DML문이 성공적으로 완료되면 자동으로 COMMIT되고, DML이 실패하면 자동으로 ROLLBACK이 되도록 Auto Commit 기능을 설정할 수 있다.

 

ROLLBACK

 

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

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

 

SAVEPOINT

 

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

- 저장점을 지정할 때는 이름을 부여한다.

- ROLLBACK 할 때 지정된 저장점까지의 트랜잭션 처리 내용이 모두 취소된다.

 

 

03 SQL - DML(Data Manipulation Language, 데이터 제어어)

 

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

- DML은 데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스를 제공한다.

 

 DML의 유형

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

 

삽입문(INSERT INTO~)

 

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

 

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

1. 대응하는 속성과 데이터는 개수와 데이터 유형이 일치해야 한다.
2. 기본 테이블의 모든 속성을 사용할 때는 속성명을 생략할 수 있다.
3. SELECT문을 사용하여 다른 테이블의 검색 결과를 삽입할 수 있다

예제) <사원> 테이블에 (이름 - 홍승현, 부서 - 인터넷)을 삽입하시오

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

 

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

INSERT INTO 편집부원(이름, 생일, 주소, 기본급)

SELECT 이름, 생일, 주소, 기본급

FROM 사원

WHERE 부서 = '편집';

 

삭제문(DELETE FROM~)

 

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

 

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

1. 모든 레코드를 삭제할 때는 WHERE절을 생략한다.
2. 모든 레코드를 삭제하더라도 테이블 구조는 남아 있기 때문에 디스크에서 테이블을 완전히 제거하는 DROP과는 다르다

 

예제) <사원> 테이블에서 "임꺽정"에 대한 튜플을 삭제하시오

DELETE

FROM 사원

WHERE NAME = '임꺽정';

 

갱신문(UPDATE~ SET~)

 

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

 

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

 

예제) <사원> 테이블에서 '홍길동'의 '주소'를 '수색동'으로 수정하시오

UPDATE 사원

SET 주소 = '수색동'

WHERE 이름 = '홍길동';