[정처기 실기] (3) SQL 활용

 

DDL (데이터 정의어) 개념

 

 

1). DDL (Data Definition Language) 정의

 

• '데이터를 정의하는 언어'이자, 보다 엄밀하게 말하면 '데이터를 담는 그릇을 정의하는 언어'
• 이러한 그릇을 DBMS에서는 '오브젝트'라고 함
• DDL을 통해 정의할 수 있는 대상, 오브젝트의 유형

DDL 대상	설명
스키마 (Schema)	DBMS 특성과 구현 환경을 감안한 데이터 구조 직관적으로 하나의 데이터베이스로 이해 가능
도메인 (Domain)	속성의 데이터 타입과 크기, 제약조건 등을 지정한 정보 속성이 가질 수 있는 값의 범위로 이해 가능
테이블 (Table)	데이터 저장 공간
뷰 (View)	하나 이상의 물리 테이블에서 유도되는 가상 논리 테이블
인덱스 (Index)	검색을 빠르게 하기 위한 데이터 구조

 

 

2). DDL 유형

 

구분	DDL 명령어	내용
생성	CREATE	데이터베이스 오브젝트 생성
변경	ALTER	데이터베이스 오브젝트 변경
삭제	DROP	데이터베이스 오브젝트 삭제
	TRUNCATE	데이터베이스 오브젝트 내용 삭제, 테이블 구조는 유지

 

 

3). DDL(데이터 정의어)의 활용

 

• 테이블 생성

구분	문법
신규 생성	CREATE TABLE 테이블이름 ( 열이름 데이터 타입 [DEFAULT 값] [NOT NULL] [PRIMARY KEY (열 리스트)] {[FOREIGN KEY (열 리스트) REFERENCES 테이블이름 [(열이름)]              [ON DELETE 옵션]              [ON UPDATE 옵션] [CHECK (조건식) | UNIQUE(열이름)]);
다른 테이블 정보를 이용한 테이블 생성	CREATE TABLE 테이블이름 AS SELECT 문;

 

 

• 테이블 변경

구분	문법
열 추가	ALTER TABLE 테이블이름 ADD 열이름 데이터 타입 [DEFAULT 값]
열 데이터 타입 변경	ALTER TABLE 테이블이름 MODIFY 열이름 데이터 타입 [DEFAULT 값]
열 삭제	ALTER TABLE 테이블이름 DROP 열이름

 

 

• 테이블 삭제, 내용 삭제, 이름 변경

구분	문법
테이블 삭제	DROP TABLE 테이블이름
테이블 내용 삭제	TRUNCATE TABLE 테이블이름
테이블 이름 변경	RENAME TABLE 이전 테이블이름 TO 새로운 테이블이름
	ALTER TABLE 이전 테이블이름 RENAME 새로운 테이블이름

 

 

• 데이터 타입

유형	정의	유형	정의
CHAR	고정 길이 문자열 데이터 타입	FLOAT	소수형 데이터 타입
VARCHAR	가변 길이 문자열 데이터 타입	DATE	날짜에 사용되는 데이터 타입
INT	숫자에 사용되는 데이터 타입

 

 

4). 제약조건 적용

 

• 제약조건 유형

제약조건	설명
PRIMARY KEY	테이블의 기본키를 정의함 기본으로 NOT NULL, UNIQUE 제약이 포함됨
FOREIGN KEY	외래키를 정의함 참조 대상을 테이블 이름(열 이름)으로 명시해야 함 참조 무결성 위배 상황 시 처리 방법으로 옵션 지정 가능 NOT ACTION, SET DEFAULT, SET NULL, CASADE, RESTRICT
UNIQUE	테이블 내에서 옅은 유일한 값을 가져야 함 테이블 내에서 동일한 값을 가져서는 안 되는 항목에 지정함
NOT NULL	테이블 내에서 관련 열의 값은 NULL일 수 없음 필수 입력 항목에 대해 제약조건으로 설정함
CHECK	개발자가 정의하는 제약조건 상황에 따라 다양한 조건 설정 가

 

 

• 제약조건 변경

내용	SQL 명령문
제약조건 추가	ALTER TABLE 테이블이름 ADD [CONSTRAINT 제약조건이름] 제약조건(열이름)
제약조건 삭제	ALTER TABLE '주문테이블' DROP FOREGIN KEY [제약조건이름];
제약조건 활성화	ALTER TABLE 테이블이름 ENABLE CONSTRAINT 제약조건이름
제약조건 비활성화	ALTER TABLE 테이블이름 DISABLE CONSTRAINT 제약조건이

 

 

 

DML (데이터 조작어) 개념 

 

 

1). DML (Data Manipulation Language) 정의

 

• 데이터를 조작하는 명령어를 의미
• 여기서 조작이란, 데이터 관점에서 생명 주기를 제어하는 것

 

 

2) DML 유형

 

구분	DML 명령어	내용
데이터 조회	SELECT	테이블의 내용을 조회
데이터 삭제	DELETE	테이벌의 내용을 삭제
데이터 변경	UPDATE	테이블의 내용을 변경
데이터 추가	INSERT	테이블의 내용을 추가

 

 

 

DML(데이터 조작어)의 활용

 

 

1) 데이터 조회

 

• 데이터의 내용을 조회할 때 사용하는 명령어
• 가장 많이 사용되는 SQL 명령어로서, 다른 DML 명령어와 같이 사용되어 SQL의 활용을 풍부하게 함
• SELECT 명령어의 기본 형식

 

• SELECT 문에서 사용되는 요소

요소	요소값	내용
OPTION	ALL	중복을 포함한 조회 결과 출력
	DISTINCT	중복을 제외한 조회 결과 출력
columns	컬럼명 목록	SELECT를 통해 조회할 컬럼명 지정
	와일드카드	모두 또는 전체를 의미하는 '*'

 

 

2) 데이터 삭제

 

• 레코드를 삭제할 때 다음과 같은 형태의 DELETE 명령문을 사용
• 조건절 없이 DELETE를 사용하는 경우, 테이블 전체가 한 번에 삭제됨

 

 

3) 데이터 변경

 

• 데이터를 변경, 수정할 때 다음과 같은 형태의 UPDATE 명령문을 사용
• UPDATE 명령문은 보통 WHERE 절을 통해 어떤 조건이 만족할 경우에만 특정 컬럼의 값을 수정하는 용도로 많이 사용됨

 

 

4) 데이터 추가

 

• 데이터를 삽입하기 위한 명령어로 다음과 같은 두 가지 형태의 명령문 형식을 제공
• 이때 데이터 삽입 결과로 하나의 레코드가 추가됨. 따라서 삽입에 사용되는 정보는 하나의 레코드를 충분히 묘사해야 함

 

 

 

DCL(데이터 제의어)의 개념

 

 

1). DCL(Data Control Language) 정의

 

• 데이터베이스에서 데이터 이외의 오브젝트에 대해 조작할 필요가 있을 때 사용하는 SQL 명령
• DCL의 조작 대상, 오브젝트 유형

오브젝트	목적	내용
사용자 권한	접근 통제	사용자를 등록하고, 사용자에게 특정 데이터베이스를 사용할 수 있는 권리를 부여하는 작업
트랜잭션	안전한 거래 보장	동시에 다수의 작업을 독립적으로 안전하게 처리하기 위한 상호작용 단위

 

 

2). DCL 유형

 

유형	명령어	용도
DCL	GRANT	데이터베이스 사용자 권한 부여
	REVOKE	데이터베이스 사용자 권한 회수
TCL	COMMIT	트랜잭션 확정
	ROLLBACK	트랜잭션 취소
	CHECKPOINT	복귀지점 설

 

 

 

DCL(데이터 제의어)의 활용

 

 

1) 사용자 권한 부여

 

권한	명령어 문법
시스템 권한 부여	GRANT 권한1, 권한2 TO 사용자계정
객체 권한 부여	GRANT 권한1, 권한2 ON 객체명 TO 사용자계정

 

 

2). 시스템 권한과 객체 권한의 종류

 

 

 

3). 사용자 권한 회수

 

GRANT에 대응하는 권한 회수 명령 : REVOKE

권한	명령어 문법
시스템 권한 회수	REVOKE 권한1, 권한2 FROM 사용자 계정
객체 권한 회수	REVOKE 권한1, 권한2 ON 객체명 FROM 사용자계

 

 

접근 통제

 

1). 접근 통제 개념

 

• 데이터베이스의 보안을 구현하는 방법으로 접근 통제방법을 사용
• 접근 통제 : 보안 정책에 따라 접근 객체(시스템 자원, 통신 자원 등)에 대한 접근 주체(사용자, 프로세스 등)의 접근 권한 확인 및 이를 기반으로 한 접근 제어를 통해 자원에 대한 비인가된 사용을 방지하는 정보 보호 기능

 

2). 접근 통제 유형

 

유형	정의	의미
임의 접근 통제 (DAC : Discretionary Access Control)	시스템 객체에 대한 접근을 사용자 개인 또는 그룹의 식별자를 기반으로 제한하는 방법 임의적이라는 말은 어떤 종류의 접근 권한을 갖는 사용자는 다른 사용자에게 자신의 판단에 의해서 권한을 줄 수 있다는 것 주체와 객체의 신분 및 임의적 접근을 통제 규칙에 기초하여 객체에 대한 주체의 접근을 통제하는 기능	통제 권한이 주체에게 있음 주체가 임의적으로 접근 통제 권한을 배분하여 제어할 수 있음
강제 접근 통제 (MAC : Mandatory Access Control)	정보 시스템 내에서 어떤 주체가 특정 객체에 접근하려 할 때  양쪽의 보안 레이블에 기초하여 높은 보안 수준을 요구하는 정보(객체)가 낮은 보안 수준의 주체에게 노출되지 않도록 제한하는 통제 방법	통제 권한이 제3자에게 있음 주체는 접근 통제 권한과 무관함

 

 

3) 접근 통제와 DCL의 관계

 

• 강제 접근 통제의 경우, 제 3자의 종류에 따라 보다 세분화된 정책이 존재
• 데이터베이스 관리 시스템(DBMS)에서 채택한 접근 통제 정책은 두 가지 중 임의 접근 통제(DAC) 방식
• 데이터베이스 관리, 특히 접근 통제 용도로 SQL에서 사용하는 명령어가 바로 DCL

 

 

TCL 활용 방법

 

 

1) 트랜잭션 개념

 

트랜잭션은 '일 처리 단위'를 의미하며, 보다 다양한 관점에서의 트랜잭션은 다음과 같다

 

 

2) 트랜잭션 제어

 

• 트랜잭션을 제어한다는 것은 흐름의 구조를 바꾼다는 것이 아니라 트랜잭션의 결과를 수용하거나 취소하는 것을 의미
• 이러한 작업을 수행하는 TCL 관련 명령어

 

명령어	내용	비고
COMMIT	트랜잭션을 확정함
ROLLBACK	트랜잭션을 취소함
CHECKPOINT	저장점 설정	ROLLBACK할 위치를 지정함

 

 

 

데이터 사전 검색

 

 

1). 데이터 사전(Data Dictionary)의 개념

 

• 데이터 사전에는 데이터베이스의 데이터를 제외한 모든 정보가 존재
• 데이터를 제외한(데이터를 구성하는) 모든 정보라는것은 데이터의 데이터를 의미
• 따라서 데이터 사전은 메타 데이터(Meta Data)로 구성되어 있다고 할 수 있음
• 데이터 사전의 내용을 변경하는 권한은 시스템이 가지며, 사용자에게는 읽기 전용 테이블 형태로 제공되므로 단순 조회만 가능

 

2). 데이터 사전의 내용

 

• 데이터 사전 안에 존재하는 메타 데이터의 유형
• 사용자 정보 (아이디, 패스워드 및 권한 등)
• 데이터베이스 객체 정보 (테이블, 뷰, 인덱스 등)
• 무결성 제약 정보
• 함수, 프로시저 및 트리거 등
• 데이터 사전 내용이 메타 데이터라는 것은 모든 DBMS 제품의 공통점이나, 데이터 사전을 구현하는 방법, 관리하는 방법 등의 차이로 메타 데이터의 구체적인 내용은 제품마다 다름

 

3). 데이터 사전의 용도

 

• 사용자에게 데이터 사전은 단순 조회의 대상
• 데이터베이스 엔진을 이루는 컴파일러, 옵티마이저 등과 같은 구성요소에게 데이터 사전은 작업을 수행하는 데 필요한 참조 정보일 뿐만 아니라 작업은 대상이기도 함