2020 정보처리기사 필기 - 3.3 SQL 응용(1)
▶ 101 SQL의 개념
SQL(Structured Query Language)
- 데이터베이스 언어
SQL의 분류
- DDL(Data Define Language, 데이터 정의어)
- DML(Data Manipulation Language, 데이터 조작어)
- DCL(Data Control Language, 데이터 제어어)
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를 정의
CREATE DOMAIN SEX CHAR(1) 정의된 도메인 이름은 'SEX', 문자형이고 크기는 1자
DEFAULT '남' 도메인 SEX를 지정한 속성의 기본값은 '남'
CONSTRAINT VALID-SEX CHECK (VALUE IN ('남', '여')); SEX를 지정한 속성에는 '남', '여' 중 하나의 값만 지정할 수 있음
☞ 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 (조건식)]
);
ex)
CREATE TABLE 학생( <학생>테이블 생성
이름 VARCHAR(15) NOT NULL, '이름'속성은 최대 문자 15자, NULL값 갖지 않음
학번 CHAR(8) , '학번'속성은 문자 8자
전공 CHAR(5), '전공'속성은 문자 5자
성별 SEX,
생년월일 DATE
PRIMARY KEY(학번), '학번'을 기본키로 정의
FOREIGN KEY(전공) REFERENCES 학과(학과코드) '전공'속성은 <학과>테이블의 '학과코드'속성 참조하는 외래키
ON DELETE SET NULL <학과> 테이블에서 튜플이 삭제되면 관련된 모든 튜플의 '전공;속성 값 NULL로 변경
ON UPDATE CASCADE, <학과>테이블에서 '학과코드' 변경되면 관련된 모든 튜플의 '전공'속성 값도 같은 값으로 변경
CONSTRAINT 생년월일제약 CHECK(생년월일>='1980-01-01'); '생년월일' 속성에는 1980-01-01 이후의 값만 저장
CONSTRAINT VALID-SEX CHECK (VALUE IN ('남', '여')); SEX를 지정한 속성에는 '남', '여' 중 하나의 값만 지정할 수 있음
☞ CASCADE란?
참조 테이블의 튜플이 삭제되면 기본 테이블의 관련 튜플도 모두 삭제되고,
속성이 변경되면 관련 튜플의 속성 값도 모두 변경
4. CREATE INDEX
CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명(속성명 [ASC | DESC]);
[CLUSTER];
• 정렬 여부 지정
- ASC: 오름차순 정렬, DESC: 내림차순 정렬
- 생략하면 오름차순으로 정렬
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_리스트;
- GRANT : 권한 부여
- REVOKE : 권한 취소
☞ 사용자 등급
DBA : 데이터 베이스 관리자
RESOURCE : 데이터베이스 및 테이블 생성 가능자
CONNECT : 단순 사용자
ex) 사용자 ID가 "NABI"인 사람에게 데이터베이스 및 테이블을 생성할 수 있는 권한 부여
GRANT RESOURCE TO NABI;
테이블 및 속성에 대한 권한 부여 및 취소
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 할 위치인 저장점을 지정하는 명령어