1. SQL의 개념
1.1. SQL 개요
SQL(Structured Query Language)은 1974년 IBM 연구소에서 개발한 SEQUEL에서 유래되었다. 국제 표준 데이터베이스 언어이며, 관계대수와 관계해석을 기초로 한 혼합 데이터 언어이다.
SQL은 쿼리문(Query:질문)이지만 질의 기능만 하는 것이 아니라, 데이터 베이스를 관리하는 전반적인 기능을 구사할 수 있다. 즉, 데이터베이스 관리에 필요한 데이터 구조 정의, 데이터 조작, 데이터 제어 기능도 수행한다는 것이다.
1.2. SQL의 분류
◍ DDL(Data Define Language, 데이터 정의어)
◍ DML(Data Manipulation Language, 데이터 조작어)
◍ DCL(Data Control Language, 데이터 제어어)
1) DDL(Data Define Language, 데이터 정의어)
DDL은 데이터 정의어라고 하며 SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의/변경/삭제할 때 사용하는 언어이다. 주요 기능은 논리적 데이터 구조와 물리적 데이터 구조의 사상을 정의하는 것이다. 주로 데이터베이스 관리자나 데이터베이스 설계자가 사용한다.
|
명령어 |
기능 |
|
CREATE |
SCHEMA, DOMAIN, TABLE, VIEW, INDEX 정의 |
|
ALTER |
TABLE에 대한 정의 변경 |
|
DROP |
SCHEMA, DOMAIN, TABLE, VIEW, INDEX 삭제 |
2) DML(Data Manipulation Language, 데이터 조작어)
DML은 데이터 조작을 위해 사용하는 명령문이다. 데이터베이스 사용자가 응용 프로그램이나 질의어를 통해 데이터 베이스에 저장한 데이터를 실질적으로 처리하는 데 사용한다. 데이터베이스 사용자와 DBMS 간의 인터페이스를 제공하는 언어이다.
|
명령어 |
기능 |
|
SELECT |
테이블에서 조건에 맞는 튜플 검색 |
|
INSERT |
테이블에 새로운 튜플 삽입 |
|
DELETE |
테이블에서 조건에 맞는 튜플 삭제 |
|
UPDATE |
데이블에서 조건에 맞는 튜플의 내용 변경 |
3) DCL(Data Control Language, 데이터 제어어)
DCL은 데이터 제어어라고 하며, 데이터의 보안, 무결성, 회복, 병행 수행 제어 등을 정의하는데 사용한다. 데이터베이스 관리자가 데이터 관리를 목적으로 사용한다.
|
명령어 |
기능 |
|
COMMIT |
명령에 의해 수행된 결과를 실제 물리적 디스크로 저장, 작업이 정상적으로 완료되었음을 알림 |
|
ROLLBACK |
데이터베이스 조작 작업이 비정상적으로 종료되었을 때 원래의 상태로 복구 |
|
GRANT |
데이터베이스 사용자에게 사용자 권한 부여 |
|
REVOKE |
데이터베이스 사용자의 사용권한 취소 |
2. DDL(Data Define Language, 데이터 정의어)
DDL(Data Define Language, 데이터 정의어)은 데이터베이스의 구조, 데이터 형식, 접근 방식 등 데이터베이스를 구축하거나 수정할 목적으로 사용하는 언어이다. DDL 번역 결과는 데이터 사전(Data Dictionary) 파일에 여러 테이블로서 저장된다.
2.1. CREATE SCHEMA
CREATE SCHEMA는 스키마를 정의하는 명령문이다. SCHEMA는 데이터베이스의 구조와 제약 조건에 대한 전반적인 명세를 기술한 것으로, 개체(entity), 속성(attribute), 관계(relationship) 및 데이터 조작 시에 데이터 값들이 갖는 제약 조건을 정의하는 것이다.
스키마 식별을 위해 스키마 이름, 소유권자, 허가권자를 정의한다.
표기 형식: CREATE SCHEMA [스키마명] AUTHORIZATION [사용자id];
|
예) |
소규원자의 사용자 id가 '홍길동'인 스키마 '대학교'를 정의하는 SQL 문 CREATE SCHEMA 대학교 AUTHORIZATION 홍길동; |
2.2. CREATE DOMAIN
CREATE DOMAIN은 도메인을 정의하는 명령문이다. DOMAIN이란 하나의 속성이 취할 수 있는 동일한 유형의 원자 값들의 집합을 말하는데, 특정 속성에서 사용할 데이터의 범위를 사용자가 정의하는 ‘사용자 정의 데이터 타입’이다.
임의의 속성에서 취할 수 있는 값의 범위가 SQL에서 지원하는 전체 데이터 타입의 값이 아니고 일부분인 경우, 사용자는 그 값의 범위를 DOMAIN으로서 정의할 수 있다. 정의된 도메인명은 일반적인 데이터 타입처럼 사용한다
|
표기 형식: |
여기서 데이터 타입은 SQL이 지원하는 데이터 타입이고, 기본값은 데이터를 입력하지 않았을 때 자동으로 입력되는 값이다.
|
예) |
성별을 '남'/'여' 1개의 문자열로 표현되는 도메인 GENDER를 정의하는 SQL문 CREATE DOMAIN GENDER CHAR(1) DEFAULT '남' CONSTRATINT VALID-GENDER CHECK(VALUE IN ('남', '여')); |
▶ SQL에서 지원하는 기본 data_type
◍ 정수(Integer): INT(4Byte 정수), SMALLINT(2 Byte 정수)
◍ 실수(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
2.3. CREATE TABLE
CREATE TABLE은 테이블을 정의하는 명령어이다. TABLE이란 데이터가 저장된 표를 말하는데, 데이터베이스의 설계 단계에서는 릴레이션이라 부르고, 조직이나 검색 시에는 테이블이라고 부른다. 두 용어는 사실상 구분 없이 사용한다.
|
표기 형식: |
테이블에 포함될 모든 속성에 대해 속성명과 그 속성의 데이터 타입, 기본 값, NOT NULL 여부를 지정한다.
▶ 주로 사용하는 기본 용어
◍ PK: 기본 키로 사용할 속성 또는 속성의 집합을 지정한다.
◍ UNIQUE: 대체 키로 사용할 속성 또는 속성의 집합을 지정하는 것으로, 이 속성은 중복된 값을 가질 수 없다.
◍ FK + REFERENCE: 참조할 다른 테이블과 그 테이블을 참조할 때 사용할 외래 키 속성을 지정한다. 외래 키가 지정되면 참조 무결성의 CASCADE 법칙이 적용된다.
◍ ON DELETE: 참조 테이블의 튜플이 삭제됐을 때, 기본 테이블에 취해 줘야 할 사항을 지정한다.
◍ ON UPDATE: 참조 테이블의 참조 속성 값이 변경됐을 때, 기본 테이블에 취해줘야 할 사항을 지정한다.
◍ CONSTRAINT: 제약 조건의 이름을 지정한다.
◍ CHECK: 속성 값에 대한 제약 조건을 정의한다.
|
예) |
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란?
참조 테이블의 튜플이 삭제되면 기본 테이블의 관련 튜플도 모두 삭제되고, 속성이 변경되면 관련 튜플의 속성 값도 모두 변경
2.4. CREATE VIEW
CREATE VIEW는 뷰를 정의하는 명령어이다. VIEW는 하나 이상의 기본 테이블로부터 유도되는 이름을 갖는 가상 테이블을 말한다.
|
표기 형식: |
◍ SELECT문을 서브 쿼리로 사용해 SELECT문의 결과로서 뷰를 생성한다.
◍ 서브 쿼리인 SELECT문에서는 UNION이나 ORDER BY절을 사용할 수 없다.
◍ 속성명을 기술하지 않으면 SELECT문의 속성명이 자동으로 사용된다.
|
예) |
<고객> 테이블에서 '주소'가 '안산시'인 고객의 '성명'과 '전화번호'를 '안산고객'이라는 뷰로 정의하는 SQL문) CREATE VIEW 안산고객(성명, 전화번호) AS SELECT 성명, 전화번호 FROM 고객 WHERE 주소 = '안산시'; |
2.5. CREATE INDEX
CREATE INDEX는 인덱스를 정의하는 명령어이다. INDEX는 검색 시간을 단축시키기 위해 만든 보조적인 데이터 구조를 말한다.
|
표기 형식: |
◍ UNIQUE: 사용하면 중복 값이 없는 속성으로 인덱스를 생성하고, 생략하면 중복 값을 허용하는 속성으로 인덱스를 생성한다.
◍ 정렬: ASC는 오름차순, DESC는 내림차순으로 정렬, 기본은 오름차순이다.
◍ CLUSTER: 인덱스가 클러스터드 인덱스(인덱스 키의 순서에 따라 데이터 정렬)로 설정된다.
|
예) |
<고객> 테이블에서 UNIQUE한 특성을 갖는 '고객번호' 속성에 대해 내림차순으로 정렬하여 '고객번호_idx'라는 이름의 인덱스를 정의하는 SQL문 CREATE UNIQUE INDEX 고객번호_idx ON 고객(고객번호, DESC); |
|
예) |
<고객> 테이블에서 '고객번호' 속성에 대해 내림차순으로 정렬해 '고객번호_INX'라는 이름으로 인덱스 정의 CREATE INDEX 고객번호_INX ON 고객(고객번호 DESC); |
2.6. ALTER TABLE
ALTER TABLE은 테이블에 대한 정의를 변경하는 명령어이다.
|
표기 형식: |
|
예) |
<학생> 테이블에 최대 3문자로 구성되는 '학년' 속성을 추가하는 SQL문 ALTER TABLE 학생 ADD 학년 VARCHAR(3); |
|
예) |
<학생> 테이블에 '학번' 필드의 데이터 타입과 크기를 VARCHAR(10)으로 변경하고, NULL 값 허용하지 않도록 변경하는 SQL문 ALTER TABLE 학생 ALTER 학번 VARCHAR(10) NOT NULL; |
2.7. DROP
DROP은 스키마, 도메인, 테이블, 뷰, 인덱스, 제약 조건 등을 제거하는 명령어이다.
|
표기 형식: |
◍ CASCADE: 제거할 요소를 참조하는 다른 모든 개체를 함께 제거한다.
◍ RESTRICTED: 다른 개체가 제거할 요소를 참조 중이면 제거를 취소한다.
|
예) |
<학생> 테이블을 제거하되, <학생> 테이블을 참조하는 모든 데이터를 함께 제거하는 SQL문 DROP TABLE 학생 CASCADE; |
3. DCL (Data Control Language, 데이터 제어어)
3.1. DCL (Data Control Language, 데이터 제어어)의 개념
DCL(Data Control Language, 데이터 제어어)은 데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는 데 사용하는 언어이다. 데이터베이스 관리자(DB Administrator)가 데이터 관리를 목적으로 사용한다.
3.2. GRANT / REVOKE
데이터베이스 관리자가 데이터베이스 사용자에게 권한을 부여하거나 취소하기 위한 명령어이다. GRANT는 권한 부여를 위한 명령어이고, REVOKE는 권한 취소를 위한 명령어이다.
|
표기 형식: |
|
예) |
사용자 ID 'NABI'에게 DB 및 테이블을 생성할 수 있는 권한을 부여하는 SQL문 GRANT RESOURCE TO NABI; |
|
예) |
사용자 ID 'STAR'에게 DB 정보 검색만 가능한 권한을 부여하는 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; |
|
예) |
사용자 ID 'STAR'에게 부여한 <고객> 테이블에 대한 권한 중 UPDATE 권한을 다른 사람에게 부여할 수 있는 권한을 취소하는 SQL문 REVOKE GRANT OPTION FOR UPDATE ON 고객 FROM STAR; |
3.3. COMMIT
트랜잭션이 성공적으로 끝나면 데이터베이스가 새로운 일관성 상태를 갖기 위해 변경된 모든 내용을 데이터베이스에 반영한다. 이 때, COMMIT 명령어를 사용한다.
COMMIT 명령을 실행하지 않아도, DML문이 성공적으로 완료되면 자동으로 커밋되고, DML이 실패하면 자동으로 롤백되도록, Auto Commit 기능을 설정할 수 있다.
3.4. ROLLBACK
아직 COMMIT 되지 않은 변경된 모든 내용들을 취소하고, DB를 이전 상태로 되돌리는 명령어이다. 트랜잭션 전체가 성공적으로 끝나지 못하면 일부 변경된 내용만 DB에 반영되는 비일관성인 상태를 가질 위험이 있기 때문에, 일부분만 완료된 트랜잭션 또한 롤백되어야 한다.
3.5. SAVEPOINT
트랜잭션 내에 ROLLBACK 할 위치인 저장 지점을 지정하는 명령어이다. 저장점을 지정할 때는 이름을 부여하며, ROLLBACK 시 지정된 저장점까지의 트랜잭션 처리 내용이 취소된다.
4. DML(Data Manipulation Language, 데이터 조작어)
4.1. DML(Data Manipulation Language, 데이터 조작어)의 개념
DML은 데이터베이스 사용자가 응용 프로그램이나 질의어를 통해 저장된 데이터를 실질적으로 관리하는 데 사용하는 언어이다. DML은 데이터베이스 사용자와 DBMS 간의 인터페이스를 제공한다.
4.2. 삽입문(INSERT INTO~)
삽입문은 기본 테이블에 새로운 튜플을 삽입할 때 사용한다.
|
표기 형식: |
◍ 대응하는 속성과 데이터는 개수와 데이터 유형이 일치해야 한다.
◍ 기본 테이블의 모든 속성을 사용할 때는 속성명을 생략할 수 있다.
◍ SELECT 구문을 사용해 다른 테이블의 검색 결과를 삽입할 수 있다.
|
예) |
<사원>테이블에 (이름-홍길동, 부서-인터넷)을 삽입 INSERT INTO 사원(이름, 부서) VALUES('홍길동', '인터넷'); |
|
예) |
<사원>테이블에 (장보고, 기획, 05/03/73, 옥계동, 90)을 삽입 INSERT INTO 사원 VALUES('장보고', '기획', #05/03/73#, '옥계동', 90); |
|
예) |
<사원>테이블에 있는 편집부의 모든 튜플을 <편집부원(이름, 생일, 주소, 기본급)> 테이블에 삽입 INSERT INTO 편집부원(이름, 생일, 주소, 기본급) SELECT 이름, 생일, 주소, 기본급 FROM 사원 WHERE 부서 = '편집'; |
4.3. 삭제문(DELETE FROM~)
삭제문은 기본 테이블에 있는 튜플 중 특정 튜플(행)을 삭제할 때 사용한다.
|
표기 형식: |
◍ 모든 레코드를 삭제할 때는 WHERE 절을 생략한다.
◍ 모든 레코드를 삭제하더라도 테이블 구조는 남아 있기 때문에 디스크에서 테이블을 완전히 삭제하는 DROP과는 다르다.
|
예) |
<사원>테이블에서 "황진이"에 대한 튜플을 삭제 DELETE FROM 사원 WHERE 이름 = '황진이'; |
|
예) |
<사원>테이블의 모든 레코드 삭제 DELETE FROM 사원; |
4.4. 갱신문(UPDATE~ SET~)
갱신문은 기본 테이블에 있는 튜플 중 특정 튜플의 내용을 변경할 때 사용한다.
|
표기 형식: |
|
예) |
<사원>테이블에서 "홍길동"의 '주소'를 "수색동"으로 수정 UPDATE 사원 SET 주소='수색동' WHERE 이름='홍길동'; |
|
예) |
<사원>테이블에서 "홍길동"의 '부서'를 "기획부"로 변경하고 '기본급'을 5만원으로 인상시키시오 UPDATE 사원 SET 부서='기획', 기본급=기본급+5 WHERE 이름='홍길동'; |
▶ 데이터 조작어 4가지 유형
◍ SELECT (검색): SELECT~ FROM~ WHERE~
◍ INSERT (삽입): INSERT INTO~ VALUES~
◍ DELETE (삭제): DELETE~ FROM~ WHERE~
◍ UPDATE (변경): UPDATE~ SET~ WHERE~
5. DML -SELECT-1
5.1. 일반 형식
|
SELECT [PREDICATE] [테이블명.] 속성명 [AS 별칭]... [, 그룹함수(속성명) [AS 별칭]] [, Window + OVER ...] FROM 테이블명1, 테이블2,... [WHERE 조건] [GROUP BY 속성명 1, 속성명2,...] [HAVING 조건] [ORDER BY 속성명 [ASC | DESC]]; |
1) SELECT절
▶ PREDICATE: 불러올 튜플 수를 제한할 명령어를 기술한다.
◍ ALL: 모든 튜플 검색, 디폴트 설정이기 때문에 주로 생략
◍ DISTINCT: 중복된 튜플이 있으면 그 중 첫 번째 한 개만 검색
◍ DISTINCTROW: 중복된 튜플을 제거하고 한 개만 검색하지만, 선택된 속성의 값이 아닌, 튜플 전체를 대상으로 한다.
▶ 속성명: 검색하여 불러올 속성(열) 또는 속성을 이용한 수식 지정
◍ 기본 테이블을 구성하는 모든 속성을 지정할 때 ' * ' 사용
◍ 두 개 이상의 테이블을 대상으로 검색할 때, '테이블명.속성명'으로 표현
▶ AS: 속성 및 연산의 이름을 다른 제목으로 표시하기 위해 사용
2) FROM절: 질의에 의해 검색될 데이터들을 포함하는 테이블명을 기술한다.
|
예) |
쿼리에 의해 검색될 데이터들을 포함하는 테이블 명 FROM 테이블명1, 테이블명2, … |
3) WHERE절: 검색할 조건을 기술한다.
|
예) |
검색할 조건을 기술한다. [WHERE 조건] [GROUP BY 속성명1, 속성명2, ...] [HAVING 조건] |
4) ORDER BY절: 특정 속성을 기준으로 정렬하여 검색할 때 사용한다.
|
예) |
[ORDER BY 속성명 [ASC | DESC]]; |
◍ 속성명: 정렬의 기준이 되는 속성명 기술한다.
◍ ASC/DESC: 정렬 방식, ASC는 오름차순(디폴트), DESC는 내림차순
▶ ALL: 모든 튜플 검색, 주로 생략
▶ DISTINCT: 중복된 튜플이 있으면 그중 첫 번째 한 개만 검색
▶ LIKE 연산자
|
대표문자 |
% |
_ |
# |
|
의미 |
모든 문자를 대표 |
문자 하나를 대표 |
숫자 하나를 대표 |
5.2. 기본 검색
SELECT 절에 원하는 속성을 지정하여 검색한다.
|
예) |
<사원> 테이블의 모든 튜플을 검색하시오. SELECT * FROM 사원; SELECT 사원.* FROM 사원; SELECT 이름, 부서, 생일, 주소, 기본급 FROM 사원; SELECT 사원.이름, 사원.부서, 사원.생일, 사원.주소, 사원.기본급 FROM 사원; |
|
예) |
<사원> 테이블에서 '주소'만 검색하되, 같은 '주소'는 한 번만 출력하시오. SELECT DISTINCT 주소 FROM 사원; |
|
예) |
<사원> 테이블에서 '기본급'에 특별수당 10을 더한 월급을 "XX부서의 XXX의 월급 XXX" 형태로 출력하시오. SELECT 부서+'부서의' AS 부서2, 이름+'의 월급' AS 이름2, 기본급+10 AS 기본급2 FROM 사원; |
5.3. 조건 지정 검색
|
예) |
<사원> 테이블에서 '기획'부서의 모든 튜플을 검색하시오. SELECT * FROM 사원 WHERE 부서='기획'; |
|
예) |
<사원> 테이블에서 '기획'부서에 근무하면서 "대흥동"에 사는 사람의 튜플을 검색하시오. SELECT * FROM 사원 WHERE 부서='기획' AND 주소='대흥동'; |
|
예) |
<사원> 테이블에서 '부서'가 '기획'이거나 '인터넷'인 튜플 검색하시오. SELECT * FROM 사원 WHERE 부서='기획' OR 부서='인터넷'; |
|
예) |
<사원> 테이블에서 성이 '김'인 사람의 튜플 검색하시오. SELECT * FROM 사원 WHERE 이름 LIKE "김%"; |
|
예) |
<사원> 테이블에서 '생일'이 '01/01/69'에서 '12/31/73' 사이인 튜플을 검색하시오. SELECT * FROM 사원 WHERE 생일 BETWEEN #01/01/69# AND #12/31/73#; 날짜 데이터는 숫자로 취급하지만, '' 또는 ##로 묶는다. |
|
예) |
<사원> 테이블에서 '주소'가 NULL인 튜플을 검색하시오. SELECT * FROM 사원 WHERE 주소 IS NULL; |
5.4. 정렬 검색
|
예) |
<사원> 테이블에서 '주소'를 기준으로 내림차순으로 정렬해서 상위 2개 튜플만 검색하시오. SELECT TOP 2 * FROM 사원 ORDER BY 주소 DESC; |
|
예) |
<사원> 테이블에서 '부서'를 기준으로 오름차순으로 정렬하고, 같은 '부서'에 대해서는 '이름'을 기준으로 내림차순 정렬시켜 검색하시오. SELECT * FROM 사원 ORDER BY 부서 ASC, 이름 DESC; |
5.5. 하위 질의
|
예) |
‘취미'가 '나이트댄스'인 사원의 '이름'과 '주소'를 검색하시오. SELECT 이름, 주소 FROM 사원 WHERE 이름 = ( SELECT 이름 FROM 여가활동 WHERE 취미 = '나이트댄스' ); |
|
예) |
취미활동을 하지 않는 사원들을 검색하시오. SELECT * FROM 사원 WHERE 이름 NOT IN ( SELECT 이름 FROM 여가활동 |
5.6. 복수 테이블 검색
|
예) |
'경력'이 10년 이상인 사원의 '이름', '부서', '취미', '경력'을 검색하시오. SELECT 사원.이름 사원.부서, 여가활동.취미, 여가활동.경력 FROM 사원, 여가활동 WHERE 여가활동.경력 >= 10 AND 사원.이름 = 여가활동.이름; |
6. DML-SELECT-2
6.1. 일반 형식
| SELECT [PREDICATE] [테이블명.] 속성명 [AS 별칭]... [, 그룹함수(속성명) [AS 별칭]] [, Window + OVER ...] FROM 테이블명1, 테이블2,... [WHERE 조건] [GROUP BY 속성명 1, 속성명2,...] [HAVING 조건] [ORDER BY 속성명 [ASC | DESC]]; |
1) 그룹 함수: GROUP BY 절에 지정된 그룹 별로 속성 값을 집계할 함수를 기술한다.
◍ COUNT(속성명): 그룹별 튜플 수를 구하는 함수
◍ SUM(속성명): 그룹별 합계를 구하는 함수
◍ AVG(속성명): 그룹별 평균을 구하는 함수
◍ MAX/MIN(속성명): 그룹별 최대/최소 값을 구하는 함수
◍ STDDEV(속성명): 그룹별 표준편차를 구하는 함수
◍ VARIANCE(속성명: 그룹별 분산을 구하는 함수
◍ ROLLUP(속성명1, 속성명2, ...): 인수로 주어진 속성을 대상으로 그룹별 소계를 구하는 함수, 속성의 개수가 n개이면 n+1까지, 하위레벨에서 상위레벨 순으로 데이터 집계
◍ CUBE(속성명1, 속성명2, ...): 인수로 주어진 속성을 대상으로 모든 조합의 그룹별 소계를 구하는 함수, 속성 n개에 대해 n^2 레벨까지, 상위레벨에서 하위레벨 순으로 데이터 집계
2) WINDOW 함수: GROUP BY 절을 사용하지 않고 속성 값을 집계할 함수를 기술한다.
함수의 인수로 지정한 속성이 대상 레코드 범위가되는데, 이를 윈도우(window)라고 부른다.
◍ ROW_NUMBER(): 윈도우 별 각 레코드에 대한 일련 번호 반환
◍ RANK(): 윈도우 별로 순위 반환, 공동 순위 반영
◍ DENSE_RANK(): 윈도우별로 순위 반환, 공동 순위는 무시하고 순위 부여
◍ PARTITION BY: WINDOW 함수가 적용될 범위로 사용할 속성 지정
◍ ORDER BY: PARTITION 안에서 정렬 기준으로 사용할 속성 지정
3) GROUP BY 절: 특정 속성을 기준으로 그룹화하여 검색할 때 사용한다. 일반적으로, GROUP BY 절은 그룹 함수와 함께 사용된다.
4) HAVING 절: GROUP BY 절과 함께 사용되며, 그룹에 대한 조건을 지정한다.
6.2. WINDOW 함수 이용 검색
GROUP BY 절을 사용하지 않고 속성 값을 집계할 함수를 기술한다.
|
예) |
<상여금> 테이블에서 '상여내역'별로 '상여금'에 대한 일련 번호를 구하시오.(단, 순서는 내림차순이며, 속성명으 'NO'로 할 것) SELECT 상여내역, 상여금 ROW_NUMBER() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS NO FROM 상여금; |
|
예) |
<상여금> 테이블에서 '상여내역'별로 '상여금'에 대한 순위를 구하시오.(단, 순서는 내림차순이며, 속성명은 '상여금순위'로 하고, RANK() 함수를 이용할 것) SELECT 상여내역, 상여금 RANK() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS 상여금순위 FROM 상여금; |
6.3. 그룹 지정 검색
|
예) |
<상여금> 테이블에서 '부서'별 '상여금' 평균을 구하시오. SELECT 부서, AVG(상여금) AS 평균 FROM 상여금 GROUP BY 부서; |
|
예) |
<상여금> 테이블에서 부서별 튜플 수를 검색하시오. SELECT 상여내역, 상여금 RANK() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS 상여금순위 FROM 상여금; |
|
예) |
<상여금> 테이블에서 '상여금'이 100 이상인 사원이 2명 이상인 '부서'의 튜플 수를 구하시오. SELECT 부서, COUNT(*) AS 사원수 FROM 상여금 WHERE 상여금 >= 100 GROUP BY 부서 HAVING COUNT(*) >= 2; |
|
예) |
<상여금> 테이블의 '부서', '상여내역', '상여금'에 대해 부서별 상여내역별 소계와 전체 합계를 검색하시오.(단, 속성명은 '상여금합계'로 하고, ROLLUP 함수를 사용할 것) SELECT 부서, 상여내역, SUM(상여금) AS 상여급합계 FROM 상여금 GROUP BY ROLLUP(부서, 상여내역) |
|
예) |
<상여금> 테이블의 '부서', '상여내역', '상여금'에 대해 부서별 상여내역별 소계와 전체 합계를 검색하시오.(단, 속성명은 '상여급합계'로 하고, CUBE 함수를 사용할 것) 합계를 검색하시오.(단, 속성명은 '상여급합계'로 하고, CUBE 함수를 사용할 것) SELECT 부서, 상여내역, SUM(상여금) AS 상여급합계 FROM 상여금 GROUP BY CUBE(부서, 상여내역); |
6.4. 집합 연산자를 이용한 통합 질의
집합연산자는 2개 이상의 테이블의 데이터를 하나로 통합하는 기능을 한다. 두 개의 SELECT문에 기술한 속성들은 개수와 데이터 유형이 서로 동일해야 한다.
▶ 집합 연산자
◍ UNION: 합집합, 두 SELECT 문의 조회 결과를 통합하여 모두 출력하되, 중복 행은 한번만 출력한다.
◍ UNION ALL: 합집합, 두 SELECT 문의 조회 결과를 통합하여 모두 출력하되, 중복행도 그대로 출력한다.
◍ INTERSECT: 교집합, 두 SELECT 문의 조회 결과 중 공통된 행만 출력한다.
◍ EXCEPT: 차집합, 첫 SELECT 문의 조회 결과에서 두번째 SELECT 문의 조회 결과를 제외한 행을 출력한다.
|
예) |
<사원> 테이블과 <직원> 테이블을 통합하는 질의문을 작성하시오.(단, 같은 레코드가 중복되어 나오지 않게 하시오.) SELECT * FROM 사원 UNION SELECT * FROM 직원 |
|
예) |
<사원> 테이블과 <직원> 테이블에 공통으로 존재하는 레코드만 통합하는 질의문을 작성하시오. SELECT * FROM 사원 INTERSECT SELECT * FROM 직원 |
7. DML-JOIN
7.1. JOIN의 개념
조인은 2개의 테이블에 대해 연관된 튜플들을 결합해 하나의 새로운 릴레이션을 반환한다. JOIN은 일반적으로 FROM 절에 기술하지만, 릴레이션이 사용되는 곳 어디에서나 사용될 수 있다. 조인은 크게 INNER JOIN과 OUTER JOIN으로 구분된다.
7.2. INNER JOIN
일반적으로 EQUI JOIN과 NON-EQUI JOIN으로 구분된다. 조건이 없는 INNER JOIN을 수행하면 CROSS JOIN과 동일한 결과를 얻을 수 있다.
◍ CROSS JOIN: 교차 조인은 두 테이블에 있는 튜플들의 순서쌍을 결과로 반환한다, 반환된 테이블의 행의 수는 두 테이블의 행 수를 곱한 것과 같다.
1) EQUI JOIN
◍ JOIN 대상 테이블에서 공통 속성을 기준으로 '=' 비교에 의해 같은 값을 갖는 행을 연결하여 결과를 생성하는 JOIN 방법이다.
◍ JOIN 조건이 '='일 때, 동일한 속성이 두 번 나타나게 되는데, 중복된 속성을 제거하여 같은 속성을 한 번만 표기하는 방법을 NATURAL JOIN이라고 한다.
◍ 연결고리가 되는 공통 속성을 JOIN 속성이라고 한다.
|
WHERE 절을 이용한 EQUI JOIN 표기 형식 |
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ... FROM 테이블명1, 테이블명2, ... WHERE 테이블명1.속성명 = 테이블명2.속성명; |
|
NATURAL JOIN 절을 이용한 EQUI JOIN 표기 형식 |
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ... FROM 테이블명1 NATURAL JOIN 테이블명2; |
|
JOIN~USING 절을 이용한 EQUI JOIN 표기 형식 |
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ... FROM 테이블명1 JOIN 테이블명2 USING 속성명; |
2) NON-EQUI JOIN
◍ JOIN 조건에 '='이 아닌 나머지 비교 연산자(>, <, <>, >=, <=)를 이용하는 방법
|
표기 형식: SELECT [테이블명1.]속성명, [테이블명2.]속성명, ... FROM 테이블명1, 테이블명2, … WHERE (NON-EQUI JOIN 조건);
|
7.3. OUTER JOIN
OUTER JOIN은 릴레이션에서 JOIN 조건에 만족하지 않는 튜플 또한 결과로 출력하기 위해 사용하는 JOIN 방법으로 LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN이 있다.
▶ LEFT OUTER JOIN
INNER JOIN 결과를 구한 후, 우측 항 릴레이션의 어떤 튜플과도 맞지 않는 좌측 항의 릴레이션에 있는 튜플 들에 NULL 값을 붙여 INNER JOIN 결과에 추가한다.
|
표기 형식: |
▶ RIGHT OUTER JOIN
INNER JOIN 결과를 구한 후, 좌측 항 릴레이션의 어떤 튜플과도 맞지 않는 우측 항의 릴레이션에 있는 튜플들에 NULL 값을 붙여서 INNER JOIN 결과에 추가한다.
|
표기 형식: |
▶ FULL OUTER JOIN
LEFT OUTER JOIN과 RIGHT OUTER JOIN을 합쳐 놓은 것이다. INNER JOIN 결과를 구한 후, 좌측 항의 릴레이션의 튜플들에 대해 우측항의 릴레이션의 어떤 퓨틀과도 맞지 않는 튜플들에 NULL 값을 붙여서 INNER JOIN의 결과에 추가한다. 그리고 우측 항의 릴레이션의 튜플들에 대해 좌측 항의 릴레이션의 어떤 튜플과도 맞지 않는 튜플들에 NULL 값을 붙여서 INNER JOIN의 결과에 추가한다.
|
표기 형식: |
7.4. SELF JOIN
같은 테이블에서 2개의 속성을 연결해 EQUI JOIN을 하는 JOIN 방법
|
표기 형식: |
'정보처리기사 > 3과목 데이터베이스 구축' 카테고리의 다른 글
| [정보처리기사] Chapter 05. 데이터베이스 구축: 데이터전환 (0) | 2021.04.26 |
|---|---|
| [정보처리기사] Chapter 04. 데이터베이스 구축: SQL 활용 (0) | 2021.04.25 |
| [정보처리기사] Chapter 02. 데이터베이스 구축: 물리 데이터베이스 설계 (0) | 2021.04.25 |
| [정보처리기사] Chapter 01. 데이터베이스 구축: 논리 데이터베이스 설계 (0) | 2021.04.25 |
