3과목 데이터베이스 구축 SQL 응용 107 ~ 113
107. SQL의 개념
: 관계대수와 관계해석을 기초로 한 혼합 데이터 언어.
1) 분류
- DDL 데이터 정의어 : 논리적 데이터 구조와 물리적 데이터 구조의 사상을 정의. (DB관리자, 설계자 사용) > CREATE, ALTER, DROP
- DML 데이터 조작어 : DB 사용자가 응용 프로그램이나 질의어를 통하여 저장된 데이터를 실질적으로 처리하는 데 사용되는 언어. > SELECT, INSERT, DELETE, UPDATE
- DCL 데이터 제어어 : 데이터의 보안, 무결성, 회복, 병행 수행 제어 등을 정의하는데 사용되는 언어. > COMMIT, ROLLBACK, GRANT, REVOKE
108. DDL
: DB 구조, 데이터 형식, 접근 방식 등 DB를 구축하거나 수정할 목적으로 사용하는 언어.
> 번역한 결과가 데이터 사전이라는 파일에 여러 개의 테이블로서 저장된다.
1) CREATE SCHEMA : 스키마 정의. 스키마 이름과 소유권자나 허가권자를 정의.
CREATE SCHEMA <스키마명> AUTHORIZATION <사용자_id>;
2) CREATE DOMAIN : 도메인 정의.
- 도메인 : 하나의 속성이 취할 수 있는 동일한 유형의 원자값들의 집합을 의미.
- 임의의 속성에서 취할 수 있는 값의 범위가 일부분일 때 그 값의 범위를 도메인으로 정할 수 있다.
- 정의된 도메인명은 일반적인 데이터 타입처럼 사용.
CREATE DOMAIN <도메인명> [AS] <데이터_타입>
[DEFAULT <기본값>]
[CONSTRAINT <제약조건명> CHECK (범위값)];
CREATE DOMAIN GENDER CHAR(1) // 정의된 도메인은 이름이 'GENDER'이며, 문자형이고 크기는 1자이다.
DEFAULT '여' //도메인 GENDER를 지정한 속성의 기본값은 '여'
CONSTRAINT VALID-GENDER CHECK(VALUE IN ('여', '남')); //GETNDER를 지정한 속성에는 '여', '남' 중 하나의 값만 저장가능.
3) CREATE TABLE : 테이블 정의
- ON DELETE 옵션 : 참조 테이블의 튜플이 삭제되었을 때 기본 테이블에 취해야 할 사항을 지정.
- NO ACTION(아무런 조치X), CASCADE(관련 튜플 모두 삭제/수정), SET NULL(관련 튜플 속성값을 NULL로), SET DEFAULT(관련 튜플 속성값을 기본값으로 변경)
- ON UPDATE 옵션 : 참조 테이블의 참조 속성 값이 변경되었을 때 기본 테이블에 취해야 할 사항을 지정.
- NO ACTION(아무런 조치X), CASCADE(관련 튜플 모두 삭제/수정), SET NULL(관련 튜플 속성값을 NULL로), SET DEFAULT(관련 튜플 속성값을 기본값으로 변경)
CREATE TABLE <테이블명>
(<속성명> <데이터_타입> [DEFAULT <기본값>] [NOT NULL], ...
[, PRIMARY KEY(기본키_속성명, ...)]
[, UNIQUE(대체키_속성명, ...)]
[, FOREIGN KEY(외래키_속성명, ...)]
[REFERENCES 참조테이블(기본키_속성명,...)]
[ON DELETE 옵션]
[ON UPDATE 옵션]
[, CONSTRAINT 제약조건명] [CHECK(조건식)]);
CREATE TABLE 학생
(이름 VARCHAR(15) NOT NULL,
학번 CHAR(8),
전공 CHAR(5),
성별 GENDER,
생년월일 DATE,
PRIMARY KEY(학번),
FOREIGN KEY(전공) REFERENCES 학과(학과코드) //전공 속성은 학과 테이블의 학과코드 속성을 참조하는 외래키
ON DELETE SET NULL
ON UPDATE CASCADE,
CONSTRAINT 생년월일제약
CHECK(생년월일 >= '1980-01-01'));
4) CREATE VIEW : 뷰를 정의
- SELECT문을 서브 쿼리로 사용하여 SELECT문의 결과로서 뷰를 생성한다.
- 서브쿼리에서는 UNION이나 ORDER BY절을 사용 불가.
- 속성명을 기술하지 않으면 SELECT문의 속성명이 자동으로 사용됨.
CREATE VIEW <뷰명>[(속성명[, 속성명, ...])]
AS SELECT문;
CREATE VIEW 안산고객(성명, 전화번호)
AS SELECT 성명, 전화번호
FROM 고객
WHERE 주소 = '안산시';
5) CREATE INDEX : 인덱스 정의
- UNIQUE : 중복 값이 없는 속성으로 인덱스 생성
- CLUSTER : 사용하면 인덱스가 클러스터드 인덱스로 설정됨.
CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명(속성명 [ASC|DESC] [, 속성명 [ASC|DESC]])
[CLUSTER];
CREATE UNIQUE INDEX 고객번호_idx //고객 테이블에서 UNIQUE한 특성을 갖는 고객번호 속성에 대해
ON 고객(고객번호 DESC); //내림차순으로 정렬하여 고객번호_idx라는 이름의 인덱스 정의
6) ALTER TABLE : 테이블 정의 변경
ALTER TABLE 테이블명 ADD 속성명 데이터_타입 [DEFAULT '기본값'];
ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값'];
ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE];
ALTER TABLE 학생 ADD 학년 VARCHAR(3);
ALTER TABLE 학생 ALTER 학번 VARCHAR(10) NOT NULL;
7) DROP : 스키마, 도메인, 테이블, 뷰 테이블, 인덱스, 제약 조건 등을 제거
- CASCADE : 제거할 요소를 참조하는 다른 모든 개체를 함께 제거
- RESTRICT : 다른 개체가 제거할 요소를 참조중일 때는 제거를 취
DROP SCHEMA 스키마명 [CASCADE | RESTRICT];
DROP DOMAIN 도메인명 [CASCADE | RESTRICT];
DROP TABLE 테이블명 [CASCADE | RESTRICT];
DROP VIEW 뷰명 [CASCADE | RESTRICT];
DROP INDEX 인덱스명 [CASCADE | RESTRICT];
DROP CONSTRAINT 제약조건명;
109. DCL
: DBA가 데이터 관리를 목적으로 사용.
1) GRANT/REVOKE
- GRANT : 권한 부여
- REVOKE : 권한 취소
- 사용자 등급 : DBA(데이터베이스 관리자), RESOURCE (데이터베이스 및 테이블 생성 가능자), CONNECT (단순 사용자)
- 권한 종류 : ALL, SELECT, INSERT, DELETE, UPDATE, ALTER 등
- WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한을 부여함.
- GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있는 권한을 취소
- CASCADE : 권한 취소 시 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소.
// 사용자 등급 지정 및 해제
GRANT 사용자등급 TO 사용자_id_리스트 [IDENTIFIED BY 암호];
REVOKE 사용자등급 FROM 사용자_id_리스트;
// 테이블 및 속성에 대한 권한 부여 및 취소
GRANT 권한_리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];
REVOKE [GRANT OPTION FOR] 권한_리스트 ON 개체 FROM 사용자 [CASCADE];
2) COMMIT : 트랜잭션이 성공적으로 끝나면 데이터베이스가 새로운 일관성 상태를 가지기 위해 변경된 모든 내용을 데이터베이스에 반영해야 한다.
3) ROLLBACK : 아직 COMMIT되지 않은 변경된 모든 내용들을 취소.
4) SAVEPOINT : 트랜잭션 내에 ROLLBACK 할 위치인 저장점을 지정하는 명령어. > 이름 부여
110. DML
: 데이터베이스 사용자가 응용 프로그램이나 질의어를 통해 저장된 데이터를 실질적으로 관리하는데 사용되는 언어.
1) INSERT
INSERT INTO 테이블명 ([속성명1, 속성명2, ...])
VALUES (데이터1, 데이터2, ...);
INSERT INTO 편집부원(이름, 생일, 주소, 기본급)
SELECT 이름, 생일, 주소, 기본급
FROM 사원
WHERE 부서 = '편집';2) DELETE
DELETE
FROM 테이블명
[WHERE 조건];
DELETE
FROM 사원
WHERE 부서='인터넷';3) UPDATE
UPDATE 테이블명
SET 속성명 = 데이터[, 속성명 = 데이터, ...]
[WHERE 조건];
UPDATE 사원
SET 주소 = '수색동'
WHERE 이름 = '홍길동';
111. DML - SELECT-1
1) 일반 형식
- PREDICATE : 불러올 튜플 수를 제한할 명령어를 기술
- ALL : 모든 튜플을 검색할 때 지정하는 것 (주로 생략)
- DISTINCT : 중복된 튜플이 있으면 첫번째 한개만 검색
- DISTINCTROW : 중복된 튜플을 제거하고 한 개만 검색하지만 선택된 속성의 값이 아닌 튜플 전체를 대상으로 함.
SELECT [PREDICATE] [테이블명.]속성명 [AS 별칭][, [테이블명.]속성명, ...]
FROM 테이블명[, 테이블명, ...]
[WHERE 조건]
[ORDER BY 속성명 [ASC|DESC]];
SELECT * FROM 사원;
SELECT DISTINCT 주소 FROM 사원;
SELECT 부서 + '부서의' AS 부서2, 이름+'의 월급' AS 이름2, 기본급+10 AS 기본급2 FROM 사원;
SELECT * FROM 사원 WHERE 생일 BETWEEN #01/01/69# AND #12/31/73#;
SELECT 이름, 주소 FROM 사원 WHERE 이름 = (SELECT 이름 FROM 여가활동 WHERE 취미='댄스');
112. DML - SELECT-2
- 그룹함수 : GROUP BY절에 지정된 그룹별로 속성의 값을 집계할 함수를 기술
- COUNT
- SUM
- AVG
- MAX
- MIN
- STDDEV : 그룹별 표준편차
- VARIANCE : 그룹별 분산
- ROLLUP(속성명, 속성명,...) : 인수로 주어진 속성을 대상으로 그룹별 소계를 구하는 함수.
- CUBE(속성명, 속성명,...) : 인수로 주어진 속성을 대상으로 모든 조합의 그룹별 소계를 구하는 함수.
- WINDOW 함수 : GROUP BY절을 이용하지 않고 속성의 값을 집계할 함수를 기술
- PARTITION BY : WINDOW 함수가 적용될 범위로 사용할 속성을 지정
- ORDER BY : PARTITION 안에서 정렬 기준으로 사용할 속성을 지정.
- ROW_NUMBER() : 윈도우별로 각 레코드에 대한 일련 번호를 반환
- RANK() : 윈도우별로 순위를 반환하며 공동순위 반영
- DENSE_RANK() : 윈도우별로 순위 반환하며, 공동 순위를 무시하고 순위를 부여
- GROUP BY절 : 특정 속성을 기준으로 그룹화하여 검색할 때 사용. > 일반적으로 그룹함수와 함께 사용
- HAVING절 : GROUP BY와 함께 사용되며, 그룹에 대한 조건을 지정
SELECT [PREDICATE] [테이블명.]속성명 [AS 별칭][, [테이블명.]속성명, ...]
[, 그룹함수(속성명) [AS 별칭]]
[, WINDOW함수 OVER(PARTITION BY 속성명1, 속성명2, ...
ORDER BY 속성명3, 속성명4, ...) [AS 별칭]]
FROM 테이블명[, 테이블명, ...]
[WHERE 조건]
[GROUP BY 속성명, 속성명, ...]
[HAVING 조건]
[ORDER BY 속성명 [ASC|DESC]];
SELECT 상여내역, 상여금,
ROW_NUMBER() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS NO
FROM 상여금;
SELECT 상여내역, 상여금,
RANK() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS 상여금순위
FROM 상여금;
SELECT 부서, AVG(상여금) AS 평균
FROM 상여금
GROUP BY 부서;
SELECT 부서, SUM(상여금) AS 상여금합계
FROM 상여금
GROUP BY ROLLUP(부서, 상여내역); // 부서별로 묶고, 부서 내에서 상여내역별로 묶고, 그 함계를 낸다.
// 그리고 부서별 상여금 합계까지 낸다.
// 1레벨 : 전체 상여금의 합계
// 2레벨 : 부서별 상여금의 합계
// 3레벨 : 부서별, 상여내역별 상여금의 합계
SELECT 부서, 상여내역, SUM(상여금) AS 상여금합계
FROM 상여금
GROUP BY CUBE(부서, 상여내역);
// 1레벨 : 전체 상여금의 합계
// 2레벨 : 상여내역별 상여금의 합계
// 3레벨 : 부서별 상여금의 합계
// 4레벨 : 부서별, 상여내역별 상여금의 합계
- 집합 연산자를 이용한 통합 질의
- UNION : 두 SELECT문의 조회 결과를 통합하여 모두 출력. (중복된 행은 한번만 출력)
- UNION ALL : 위와 같지만 중복된 행 모두 그대로 출력.
- INTERSECT : 두 SELECT문의 조회 결과 중 공통된 행만 출력
- EXCEPT : 첫번째 조회 결과에서 두번째 조회 결과를 제외한 행을 출력 (차집합)
SELECT 속성명1, 속성명2, ...
FROM 테이블명
UNION | UNION ALL | INTERSECT | EXCEPT
SELECT 속성명1, 속성명2, ...
FROM 테이블명
[ORDER BY 속성명 [ASC|DESC]];
113. DML - JOIN
: 2개의 테이블에 대해 연관된 튜플들을 결합하여, 하나의 새로운 릴레이션을 반환.
1) INNER JOIN
- EQUI JOIN : JOIN 대상 테이블에서 공통 속성을 기준으로 =비교에 의해 같은 값을 가지는 행을 연결하여 결과 생성.
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2, ...
WHERE 테이블명1.속성명 = 테이블명2.속성명;
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 NATURAL JOIN 테이블명2;
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 JOIN 테이블명2 USING(속성명);- NON-EQUI JOIN : JOIN 조건에 =조건이 아닌 나머지 비교연산자 (>, >=, <, <=) 사용하는 조인.
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2, ...
WHERE (NON-EQUI JOIN 조건);
2) OUTER JOIN : 릴레이션에서 JOIN 조건에 만족하지 않는 튜플도 결과로 출력하기 위한 JOIN 방법.
- LEFT OUTER JOIN : 좌측항이 기준!!! 좌측 릴레이션에 있는 튜플 모두 표시, 우측 튜플에 NULL값 존재
- RIGHT OUTER JOIN : 우측항이 기준!!! 우측 릴레이션에 있는 튜플 모두 표시, 좌측 튜플에 NULL값 존재.
- FULL OUTER JOIN : 모두 나옴.
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 LEFT OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 RIGHT OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 FULL OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;
3) SELF JOIN : 같은 테이블에서 2개의 속성을 연결하여 EQUI JOIN하는 방법.
SELECT [별칭1.]속성명, [별칭1]속성명, ...
FROM 테이블명1 AS 별칭1 JOIN 테이블명1 AS 별칭2
ON 별칭1.속성명 = 별칭2.속성명;