8. SQL 응용

RushBsite·2022년 7월 22일
0

정보처리기사

목록 보기
6/7

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

= DB 구축/ 수정에 사용, 번역한 결과가 데이터 사전 이라는 특별한 파일에 여러개의 테이블로 저장됨

CREATE : SCHEMA, DOMAIN, TABLE, VIEW, INDEX 정의
ALTER : TABLE 정의 변경
DROP : SCHEMA, DOMAIN, TABLE, VIEW, INDEX 를 삭제

CREATE

[] 는 생략 가능의 의미

CREATE SCHEMA

	CREATE SCHEMA 스키마명 AUTHORIZATION 사용자_ID;
    -> 사용자_ID 인 스키마명 스키마를 정의

CREATE DOMAIN

    CREATE DOMAIN 도메인명 [AS] 데이터_타입
    	   [DEFAULT 기본값]
           [CONSTRAINT 제약조건명 CHECK(범위값)]
    EX) 
    CREATE DOMAIN SEX CHAR(1) // 도메인 이름 'SEX' 데이터타입은 크기 1인 CHAR
    	   DEFAULT '남' //기본 속성값은 '남'
           CONSTRAINT VALID-SEX CHECK(VALUE IN('남', '여')) // SEX 속성에는 
'남', '여' 중 에서만 저장 가능하다는 VALID-SEX 라는 제약조건명 설정

CREATE TABLE

	CREATE TABLE 테이블명
    	   (속성명 데이터_타입 [DEFAULT 기본값] [NOT NULL], ...
           [, PRIMARY KEY(기본키_속성명,...)]
           [, UNIQUE(대체키_속성명)]
           [, FOREIGN KEY(외래키_속성명)]
           		REFERENCES 참조테이블(기본키_속성명,...)]
                [ON DELETE 옵션]
                [ON UPDATE 옵션]
           [, CONSTRAINT 제약조건명] [CHECK(조건식)])
           
   EX) 이름, 학번, 전공, 성별, 생년월일 로 구성된 <학생> 테이블.
   조건 : 이름은 NULL 불가, 학번 기본키, 전공은 <학과> 테이블의 '학과코드'참조하는 외래키
		<학과> 테이블에서 삭제가 일어나면 관련된 튜플의 전공값을 NULL<학과> 테이블에서 '학과코드'가 변경되면 전공값도 같은 값으로 변경
        '생년월일'1980-01-01 이후의 데이터만 저장
        제약조건의 이름은 '생년월일제약'
        각 속성의 데이터 타입은 적당하게, 단 성별은 도메인 'SEX' 사용
        
	CREATE TABLE 학생
    	   (이름 VARCHAR(15) NOT NULL, //VARCHAR는 데이터 크기만큼 기억 장소 확보
           학번 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문

EX)
CREATE VIEW 안산고객(성명, 전화번호)
AS SELECT 성명, 전화번호
FROM 고객
WHERE 주소 = '안산시'

CREATE INDEX

CREATE [UNIQUE] INDEX 인덱스명 // UNIQUE 사용경우 중복값이 없는 속성으로 인덱스를 생성
ON 테이블명(속성명 [ASC | DESC] [, 속성명 [ASC | DESC]]) //ASC : 오름차순 DESC : 내림차순
[CLUSTER] // 사용하면 인덱스가 클러스티드 인덱스로 설정됨

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

ALTER

ALTER TABLE

ALTER TABLE 테이블명 ADD 속성명 데이터_타입 [DEFAULT '기본값'] // 새로운 속성 추가
ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값'] // 특정 속성의 DEFAULT 값 변경
ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE] // 특정 속성 삭제

DROP

DROP SCHEMA 스키마명 [CASCADE | RESTRICT] // CASCADE : 제거할 요소를 참조하는 모든 개체 함께 제거
DROP DOMAIN 도메인명 [CASCADE | RESTRICT] // RESTRICT : 다른 개체가 제거할 요소를 참조중일때는 제거를 취소
DROP TABLE 테이블명 [CASCADE | RESTRICT]
DROP VIEW 뷰명 [CASCADE | RESTRICT]
DROP INDEX 인덱스명 [CASCADE | RESTRICT]
DROP CONSTRAINT 제약조건명

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

= 데이터의 보안, 무결성, 회복, 병행제어등을 정의하는 데 사용하는 언어 ,DBA(데이터베이스 관리자)가 데이터 관리를 목적으로 사용.

DCL의 종류

COMMIT : 트랜젝션이 완료되고 저장됨을 알림
ROLLBACK : 비정상 종료시, 원래 상태 복구
GRANT : 사용 권한 부여
REVOKE : 사용 권한 취소

GRANT / REVOKE

사용자 등급 지정 및 해제

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

//사용자 등급 : DBA(데이터베이스 관리자), RESOURCE(DB및 테이블 생성 가능자), CONNECT(단순사용자)

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

GRANT 권한_리스트 ON 개체 TO 사용자 [WITH GRANT OPTION]
REVOKE [GRANT OPTION FOR] 권한_리스트 ON 개체 FROM 사용자 [CASCADE]

//권한 종류 : ALL, SELECT, INSERT, DELETE, UPDATE
//WITH GRANT OPTION : 부여받은 권한을 다른사용자에게 다시 부여할 수 있는 권한을 부여
//GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있는 권한을 취소
//CASCADE 권한취소시 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소

EX) ID가 'NAVI'인 사람에게 <고객> 테이블의 모든 권한과 다른사람에게 권한 부여하는 권한까지 부여 
GRANT ALL ON 고객 TO NAVI WITH GRANT OPTION

ID 가 'STAR'인 사람에게 부여한 <고객> 테이블에 대한 권한 중 UPDATE 권한을 다른 사람에게 부여할 수있는 권한만 취소
REVOKE GRANT OPTION FOR UPDATE ON 고객 FROM STAR

COMMIT

= 트랜잭션 정상완료 후 트랜잭션이 수행한내용을 DB에 반영하는 명령

ROLLBACK

= 변경되었으나 아직 COMMIT 되지 않은 모든 내용들을 취소하고 DB를 이전 상태로 되돌리는 명령

SAVEPOINT

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

SAVEPOINT S1;
DELETE FROM 사원 WHERE 사원번호 = 20;
ROLLBACK TO S1; // DELETE 한것 ROLLBACK된다.
//COMMIT 이후에는 롤백해도 변경불가

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

저장 데이터를 실질적으로 관리하는데 사용되는 언어

SELECT 튜플 검색
INSERT 튜플 삽입
DELETE 튜플 삭제
UPDATE 튜플 갱신

삽입문 (INSERT INTO ~)

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

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

EX) 사원 테이블에 (이름-홍승헌, 부서-인터넷)을 삽입
INSERT INTO 사원(이름,부서)
VALUES('홍승헌','인터넷')

삭제문 (DELETE FROM ~)

DELETE
FROM 테이블명
[WHERE 조건] // 모든 레코드 삭제시 생략

갱신문 (UPDATE ~ SET ~)

UPDATE 테이블명
SET 속성명 = 데이터[, 속성명=데이터, ...]
[WHERE 조건]
profile
게임 기획/개발 지망생

0개의 댓글