1. SQL - DDL
DDL (Data Define Language, 데이터 정의어)
•
DB 구조, 데이터 형식, 접근 방식 등 DB를 구축하거나 수정할 목적으로 사용하는 언어
•
번역한 결과가 데이터 사전(Data Dictionary)이라는 특별한 파일에 여러개의 테이블로 저장된다.
•
DDL의 3가지 유형
◦
CREATE
▪
SCHEMA, DOMAIN, TABLE, VIEW, INDEX 를 정의함
◦
ALTER
▪
TABLE에 대한 정의를 변경하는 데 사용함
◦
DROP
▪
SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 삭제함
CREATE SCHEMA
•
스키마를 정의하는 명령문
◦
스키마 : 데이터베이스와 구조와 제약조건에 관한 전반적인 명세(Specification)를 기술(Description)한 것으로, 데이터 개체(Entity), 속성(Attribute), 관계(Relationship) 및 데이터 조작 시 데이터 값들이 갖는 제약 조건 등에 관해 전반적으로 정의한다.
•
예제 : 소유권자의 사용자 ID가 ‘홍길동’인 스키마 ‘대학교’를 정의하는 SQL문
create schema 대학교 authorization 홍길동;
SQL
복사
CREATE DOMAIN
•
도메인을 정의하는 명령문
◦
도메인
▪
하나의 속성이 취할 수 있는 동일한 유형의 원자값들의 집합
▪
특정 속성에서 사용할 데이터의 범위를 사용자가 정의하는 사용자 정의 데이터 타입
•
데이터 타입 : SQL에서 지원하는 데이터 타입
•
기본값 : 데이터를 입력하지 않았을 때, 자동으로 입력되는 값
•
예제 : ‘성별’을 ‘남’ 또는 ‘여’와 같이 정해진 1개의 문자로 표현하는 도메인 SEX를 정의하는 SQL문
create domain SEX CHAR(1)
default '남'
constraint VALID-SEX check(value in ('남', '여');
SQL
복사
CREATE TALBE
•
테이블을 정의하는 명령문
◦
테이블
▪
데이터베이스의 설계 단계에서는 테이블을 주로 릴레이션(Relation)이라 부르고, 조작이나 검색 시에는 테이블이라고 부른다
◦
PRIMARY KEY
▪
기본키를 사용할 속성을 지정함
◦
UNIQUE
▪
대체키로 사용할 속성을 지정함
▪
중복된 값을 가질 수 없다
◦
FOREIGN KEY ~ REFERENCES ~ : 외래키로 사용할 속성을 지정한다.
▪
ON DELETE 옵션
•
참조 테이블의 튜플이 삭제되었을 때, 기본 테이블에 취해야 할 사항을 지정한다.
▪
ON UPDATE 옵션
•
참조 테이블의 참조 속성 값이 변경되었을 때, 기본 테이블에 취해야 할 사항을 지정한다.
◦
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'));
SQL
복사
CREATE VIEW
•
뷰(View)를 정의하는 명령문
◦
뷰
▪
하나 이상의 기본 테이블로부터 유도되는 이름을 갖는 가상 테이블 (Virtual Table)
▪
테이블은 물리적으로 구현되어 실제로 데이터가 저장되지만, 뷰는 물리적으로 구현되지 않는다.
•
뷰를 생성하면 뷰 정의가 시스템 내에 저장되었다가 SQL 내에서 뷰 이름을 사용하면 실행 시간에 뷰 정의가 대체되어 수행된다.
•
예제 : <고객> 테이블에서 ‘주소’가 ‘안산시’인 고객들의 ‘성명’과 ‘전화번호’를 ‘안산고객’ 이라는 뷰로 정의하시오.
create view 안산고객(성명, 전화번호)
as select 성명, 전화번호
from 고객
where 주소 = '안산시';
SQL
복사
CREATE INDEX
•
인덱스를 정의하는 명령문
◦
인덱스 : 검색 시간으 단축시키기 위해 만든 보조적인 데이터 구조
•
UNIQUE
◦
사용된 경우 : 중복 값이 없는 속성으로 인덱스를 생성한다.
◦
생략된 경우 : 중복 값을 허용하는 속성으로 인덱스를 생성한다.
•
정렬 여부
◦
ASC : 오름차순 정렬
◦
DESC : 내림차순 정렬
◦
생략된 경우 : 오름차순 정렬
•
CLUSTER : 사용하면 인덱스가 클러스터드 인덱스로 설정됨
◦
클러스터드 인덱스 (Clustered Index)
▪
인덱스 키의 순서에 따라 데이터가 정렬되어 저장되는 방식
▪
실제 데이터가 순서대로 저장되어 있어 인덱스를 검색하지 않아도 원하는 데이터를 빠르게 찾을 수 있다.
▪
하지만, 데이터 삽입, 삭제 발생 시 순서를 유지하기 위해 데이터를 재정렬해야 한다.
◦
넌 클러스터드 인덱스 (Non-Clustered Index)
▪
인ㄷ게스의 키 값만 정렬되어 있을 뿐, 실제 데이터는 정렬되지 않는 방식
▪
데이터를 검색하기 위해서는 먼저 인덱스를 검색하여 실제 데이터의 위치를 확인해야 하므로, 클러스터드 인덱스에 비해 검색 속도가 떨어진다.
•
예제 : <고객> 테이블에서 UNIQUE한 특성을 갖는 ‘고객번호’ 속성에 대해 내림차순으로 정렬하여 ‘고객번호_idx’라는 이름으로 인덱스를 정의하시오.
create unique index 고객번호_idx
on 고객(고객번호 desc);
SQL
복사
ALTER TABLE
•
테이블에 대한 정의를 변경하는 명령문
•
표기형식
ALTER TABLE 테이블명 ADD 속성명 데이터_타입[DEFAULT '기본값'];
ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값'];
ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE];
SQL
복사
•
ADD : 새로운 속성(열)을 추가할 때 사용
•
ALTER : 특정 속성의 Default 값을 변경할 때 사용
•
DROP COLUMN : 특정 속성을 삭제할 때 사용
•
예제 : <학생> 테이블에 최대 3문자로 구성되는 ‘학년’ 속성을 추가하시오.
alter table 학생 add 학년 varchar(3);
SQL
복사
•
예제 : <학생> 테이블의 ‘학번’ 필드의 데이터 타입과 크기를 VARCHAR(10)으로 하고, NULL 값이 입력되지 않도록 변경하시오.
alter table 학생 alter 학번 varchar(10) not null;
SQL
복사
DROP
•
스키마, 도메인, 기본 테이블, 뷰 테이블, 인덱스, 계약 조건 등을 제거하는 명령문
•
CASCADE : 제거할 요소를 참조하는 다른 모든 개체를 함께 제거한다.
•
RESTRICT : 다른 개체가 제거할 요소를 참조 중일 때는 제거를 취소한다.
•
예제 : <학생> 테이블을 제거하되, <학생> 테이블을 참조하는 모든 데이터를 함께 제거하시오.
drop table 학생 cascade;
SQL
복사
2. SQL - DCL
DCL (Data Control Language, 데이터 제어어)
•
데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는 데 사용하는 언어
•
데이터베이스 관리자(DBA)가 데이터 관리를 목적으로 사용한다.
•
종류
◦
COMMIT
▪
명령에 의해 수행된 결과를 실제 물리적 디스크로 저장하고, 데이터베이스 조작 작업이 정상적으로 완료되었음을 관리자에게 알려준다.
◦
ROLLBACK
▪
데이터베이스 조작 작업이 비정상적으로 종료되었을 때, 원래의 상태로 복구된다.
◦
GRANT
▪
데이터베이스 사용자에게 사용 권한을 부여한다.
◦
REVOKE
▪
데이터베이스 사용자의 사용 권한을 취소한다.
GRANT / REVOKE
•
데이터베이스 관리자가 데이터베이스 사용자에게 권한을 부여하거나 취소하기 위한 명령어
•
GRANT : 권한 부여를 위한 명령어
•
REVOKE : 권한 취소를 위한 명령어
•
예제 : 사용자 ID가 “NABI”인 사람에게 데이터베이스 및 테이블을 생성할 수 있는 권한을 부여하는 SQL문
grant RESOURCE to NABI;
SQL
복사
•
예제 : 사용자 iD가 “STAR”인 사람에게 단순히 데이터베이스에 있는 정보를 검색할 수 있는 권한을 부여하는 SQL문
grant connect to STAR
SQL
복사
•
권한 종류
◦
ALL, SELECT, INSERT, DELETE, UPDATE, ALTER 등
•
WITH GRANT OPTION
◦
부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한을 부여한다.
•
GRANT OPTION FOR
◦
다른 사용자에게 권한을 부여할 수 있는 권한을 취소한다.
•
CASCADE
◦
권한 취소 시, 권한을 부여 받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소한다.
•
예제 : 사용자 ID가 “NABI”인 사람에게 <고객> 테이블에 대한 모든 권한과 다른 사람에게 권한을 부여할 수 있는 권한까지 부여하는 SQL문
grant all on 고객 to NABI with grant option
SQL
복사
•
예제 : 사용자 ID가 “STAR”인 사람에게 부여한 <고객> 테이블에 대한 권한 중, UPDATE 권한을 다른 사람에게 부여할 수 있는 권한만 취소하는 SQL문
revoke grant option for UPDATE on 고객 from STAR;
SQL
복사
COMMIT
•
트랜잭션 처리가 정상적으로 완료된 후, 트랜잭션이 수행한 내용을 데이터베이스에 반영하는 명령
•
COMMIT 명령을 실행하지 않아도 DML문이 성공적으로 완료되면 자동으로 COMMIT 되고, DML이 실패하면 자동으로 ROLLBACK이 되도록 Auto Commit 기능을 설정할 수 있다.
ROLLBACK
•
변경되었으나 아직 COMMIT 되지 않은 모든 내용들을 취소하고, 데이터베이스를 이전 상태로 되돌리는 명령어
•
트랜잭션 전체가 성공적으로 끝나지 못하면, 일부 변경된 내용만 데이터베이스에 반영되는 비일관성(Inconsistency) 상태가 될 수 있기 떄문에 일부분만 완료된 트랜잭션은 롤백되어야 한다.
SAVEPOINT
•
트랜잭션 내에 ROLLBACK 할 위치인 저장점을 지정하는 명령어
•
저장점을 지정할 때는 이름을 부여한다.
•
ROLLBACK 할 때, 지정된 저장점까지의 트랜잭션 처리 내용이 모두 취소된다.
•
예제 : <사원> 테이블에서 ‘사원번호’가 40인 사원의 정보를 삭제한 후, COMMIT을 수행하시오.
delete from 사원 where 사원번호 = 40;
commit;
SQL
복사
•
DELETE 명령을 수행한 후, COMMIT 명령을 수행하였으므로, DELETE 명령으로 삭제된 레코드는 이후 ROLLBACK 명령으로 되돌릴 수 없다.
•
예제 : <사원> 테이블에서 ‘사원번호’가 30인 사원의 정보를 삭제하시오.
delete from 사원 where 사원번호 = 30;
SQL
복사
•
DELETE 명령을 수행한 후, COMMIT 명령을 수행하지 않았으므로, DELETE 명령으로 삭제된 레코드는 이후 ROLLBACK 명령으로 되돌릴 수 있다.
•
예제 : SAVEPOINT ‘S1’을 설정하고, ‘사원번호’가 20인 사원의 정보를 삭제하시오.
savepoint S1;
delete from 사원 where 사원번호 = 20;
SQL
복사
•
예제 : SAVEPOINT ‘S2’를 설정하고, ‘사원번호’가 10인 사원의 정보를 삭제하시오.
savepoint S2;
delete from 사원 where 사원번호 = 10;
SQL
복사
•
예제 : SAVEPOINT ‘S2’ 까지 ROLLBACK을 수행하시오.
rollback to S2;
SQL
복사
•
예제 : SAVEPOINT ‘S1’ 까지 ROLLBACK을 수행하시오.
rollback to S1;
SQL
복사
•
예제 : SAVEPOINT 없이 ROLLBACK을 수행하시오.
rollback;
SQL
복사
TCL (Transaction Control Language)
•
COMMIT, ROLLBACK, SAVEPOINT는 트랜잭션을 제어하는 용도로 사용되므로 TCL (Transaction Control Language)로 분류하기도 한다.
•
하지만 기능을 제어하는 명령이라는 공통점으로 DCL의 일부로 분류하기도 한다.
3. SQL - DML
DML (Data Manipulation Language, 데이터 조작어)
•
데이터베이스 사용자가 저장된 데이터를 실질적으로 관리하는데 사용되는 언어
•
데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스를 제공한다.
•
유형
◦
SELECT
▪
테이블에서 튜플을 검색함
◦
INSERT
▪
테이블에서 새로운 튜플을 삽입함
◦
DELETE
▪
테이블에서 튜플을 삭제함
◦
UPDATE
▪
테이블에서 튜플의 내용을 갱신함
삽입문 (INSERT INTO ~ )
•
기본 테이블에 새로운 튜플을 삽입할 때 사용한다.
•
대응하는 속성과 데이터는 개수와 데이터 유형이 일치해야 한다.
•
기본 테이블의 모든 속성을 사용할 때는 속성명을 생략할 수 있다.
•
SELECT 문을 사용하여 다른 테이블의 검색 결과를 삽입할 수 있다.
•
예제 : <사원> 테이블에 (이름-홍승현, 부서-인터넷)을 삽입하시오.
insert into 사원(이름, 부서) values('홍승현', '인터넷');
SQL
복사
•
예제 : <사원> 테이블에 (’장보고’, ‘기획’, #05/03/73#, ‘홍제동’, 90)을 삽입하시오.
insert into 사원 values('장보고', '기획', #05/03/73#, '홍제동', 90);
SQL
복사
•
예제 : <사원> 테이블에 있는 편집부의 모든 튜플을 편집부원(이름, 생일, 주소, 기본급) 테이블에 삽입하시오.
insert into 편집부원(이름, 생일, 주소, 기본급)
select 이름, 생일, 주소, 기본급
from 사원
where 부서 = '편집';
SQL
복사
삭제 (DELETE FROM ~ )
•
기본 테이블에 있는 튜플들 중에서 특정 튜플(행)을 삭제할 때 사용한다.
•
모든 레코드를 삭제할 때는 WHERE 절을 생략한다.
•
모든 레코드를 삭제하더라도 테이블 구조는 남아있기 때문에 디스크에서 테이블을 완전히 제거하는 DROP 과는 다르다.
◦
DELETE 문은 테이블 구조나 테이블 자체는 그대로 남겨두고, 테이블 내의 튜플들만 삭제한다.
◦
테이블을 완전히 제거하기 위해서는 DROP 문을 사용해야 한다.
•
예제 : <사원> 테이블에서 “임꺽정”에 대한 튜플을 삭제하시오.
delete from 사원 where 이름 = "임꺽정";
SQL
복사
•
예제 : <사원> 테이블에서 “인터넷” 부서에 대한 모든 튜플을 삭제하시오.
delete from 사원 where 부서 = '인터넷';
SQL
복사
•
예제 : <사원> 테이블의 모든 레코드를 삭제하시오.
delete from 사원;
SQL
복사
갱신문 (UPDATE ~ SET ~ )
•
기본 테이블에 있는 튜플들 중에서 특정 튜플(행)의 내용을 변경할 때 사용한다.
•
예제 : <사원> 테이블에서 “홍길동”의 ‘주소’를 “수색동”으로 수정하시오.
update 사원 set 주소 = '수색동' where 이름 = '홍길동';
SQL
복사
•
예제 : <사원> 테이블에서 “황진이”의 ‘부서’를 “기획부”로 변경하고, ‘기본급’을 5만원 인상시키시오.
update 사원 set 부서 = '기획', 기본급 = 기본급 + 5 where 이름 = '황진이';
SQL
복사