- DDL (Data Define Language, 데이터 정의어)
- DB를 구축하거나 수정할 목적으로 사용하는 언어
- 번역한 결과가 데이터 사전 파일에 여러 개의 테이블로 저장
- CREATE, ALTER, DROP
- CREATE : 정의
- CREATE SCHEMA
CREATE SCHEMA 스키마 명 AUTHORIZATION 사용자ID;
-- 사용자 ID가 홍길동인 스키마 '대학교'를 정의
CREATE SCHEMA 대학교 AUTHORIZATION 홍길동;
CREATE DOMAIN 도메인 명 [AS] 데이터 타입
[DEFAULT 기본값]
[CONSTRAINT 제약조건명 CHECK (범위값)];
-- '성별'을 '남' 또는 '여'와 같이 정해진 1개의 문자로 표현되는 도메인 SEX를 정의하는 명령문
CREATE DOMAIN SEX CHAR(1) --크기가 1인 SEX 도메인
DEFAULT '남' -- 기본값 = '남'
CONSTRAINT VALID-SEX CHECK (VALUE IN ('남', '여'));
-- SEX를 지정한 속성에는 '남', '여' 중 하나의 값만을 저장 가능
CREATE TABLE 테이블 명 (
속성명 데이터 타입 [DEFAULT 기본값] [NOT NULL]
.
.
.
);
--예
CREATE TABLE 학생 (
이름 CHAR(15) NOT NULL, --크기가 15인 이름 속성, NULL값을 가질 수 없음
학번 CHAR(6), -- 크기가 6인 학번 속성
전공 CHAR(5), -- 크기가 5인 전공 속성
PRIMARY KEY(학번), -- 학번을 기본키로 정의
FOREIGN KEY(전공) REFERENCES 학과(학번코드) --'전공' 속성은 <학과> 테이블의 '학번코드'를 참조하는 외래키
ON DELETE SET NULL -- <학과>테이블에서 튜플이 삭제되면 관련된 모든 튜플의 '전공' 속성값을 NULL로 변경
ON UPDATE CASCADE, -- <학과>테이블에서 '학번코드'가 변경되면 관련된 모든 튜플의 '전공'속성값도 변경
CONSTRAINT 학번제약 CHECK(학번 >= '100')
--학번에는 100이상의 값만 저장할 수 있으며, 제약 조건의 이름은 학번제약
);
--PRIMARY KEY : 기본키로 사용할 속성 지정
--UNIQUE : 대체키로 사용할 속성 지정, 중복된 값을 가질 수 없음
--FOREIGN KEY ~ REFERENCES ~ : 외래키로 사용할 속성 지정
--ON DELETE 옵션 : 참조 테이블의 튜플이 삭제되었을 때 기본 테이블에 취해야 할 사항 지정
--ON UPDATE 옵션 : 참조 테이블의 참조 속성 값이 변경되었을 때 취해야 할 사항 지정
--CONSTRAINT : 제약조건의 이름을 지정
--CHECK : 속성값에 대한 제약 조건 정의
CREATE VIEW 뷰이름 [(속성명,...)]
AS SELECT문;
-- <고객> 테이블에서 '주소'가 '안산시'인 고객들의 '성명'과 '전화번호'를 '안산고객'이라는 뷰로 정의
CREATE VIEW 안산고객(성명, 전화번호)
AS SELECT 성명, 전화번호
FROM 고객
WHERE 주소 = '안산시';
CREATE [UNIQUE] INDEX 인덱스이름
ON 테이블이름 (속성명 [DESC | ASC] [,속성명 [DESC | ASC]])
[CLUSTER];
-- <고객> 테이블에서 UNIQUE한 특성을 갖는 '고객번호'속성에 대해 내림차순으로 정렬하여
-- '고객번호_idx'라는 이름으로 인덱스 정의
CREATE UNIQUE INDEX 고객번호_idx
ON 고객(고객번호 DESC);
--UNIQUE
--사용 : 중복 값이 없는 속성으로 인덱스 생성
--생략 : 중복 값을 허용하는 속성으로 인덱스 생성
-- 정렬 여부
--ASC : 오름차순
--DESC : 내림차순
--CLUSTER
-- 인덱스를 클러스터드 인덱스로 설정
- ALTER : 정의 변경
- ALTER TABLE
-- 테이블에 새로운 속성 추가
ALTER TABLE 테이블이름 ADD 속성명 데이터타입 [DEFAULT '기본값'];
-- 특정 속성의 DEFAULT 값 변경
ALTER TABLE 테이블이름 ALTER 속성명 [SET DEFAULT '기본값'];
-- 특정 속성을 삭제
ALTER TABLE 테이블이름 DROP COLUMN 속성명 [CASCADE];
-- <학생> 테이블에 최대 3문자로 구성되는 '학년'속성을 추가
ALTER TABLE 학생 ADD 학년 VARCHAR(3);
-- <학생>테이블의 '학번'필드의 데이터 타입과 크기를 VARCHAR(10)으로 하고 NULL값이 입력되지않도록 변경
ALTER TABLE 학생 ALTER 학번 VARCHAR(10) NOT NULL;
- DROP : 삭제
- 스키마, 도메인, 기본 테이블, 뷰, 인덱스 등을 제거하는 명령문
DROP SCHEMA 스키마이름 [CASCADE | RESTRICT];
DROP DOMAIN 도메인이름 [CASCADE | RESTRICT];
DROP TABLE 테이블이름 [CASCADE | RESTRICT];
DROP VIEW 뷰이름 [CASCADE | RESTRICT];
DROP INDEX 인덱스이름 [CASCADE | RESTRICT];
DROP CONSTRAINT 스키마이름;
-- <학생> 테이블을 제거하되, <학생>테이블을 참조하는 모든 데이터를 함께 제거
DROP TABLE 학생 CASCADE;
--CASCADE : 제거할 요소를 참조하는 다른 모든 개체를 함께 제거
--RESTRICTED : 다른 개체가 제거할 요소를 참조중일 때는 제거 취소
- DCL (Data Control Language, 데이터 제어어)
- 데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는데 사용하는 언어
- 데이터베이스 관리자가 데이터 관리를 목적으로 사용
- COMMIT, ROLLBACK, GRANT, REVOKE
- GRANT / REVOKE
- GRANT : 권한 부여를 위한 명령어
- REVOKE : 권한 취소를 위한 명령어
GRANT 사용자등급 TO 사용자ID [IDENTIFIED BY 암호];
REVOKE 사용자등급 FROM 사용자ID;
--사용자 등급
-- DBA : 데이터베이스 관리자
-- RESOURCE : 데이터베이스 및 테이블 생성 가능자
-- CONNECT : 단순 사용자
--사용자 NABI에게 데이터베이스 및 테이블을 생성할 수 있는 권한을 부여
GRANT RESOURCE TO NABI
--사용자 STAR에게 단순히 데이터베이스에있는 정보를 검색할 수 있는 권한을 부여
GRANT CONNECT TO STAR;
GRANT 권한리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];
REVOKE [GRANT OPTION FOR] 권한리스트 ON 개체 FROM 사용자 [CASCADE];
--권한
--ALL
--SELECT
--INSERT
--DELECTE
--UPDATE
-- WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한
-- GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있는 권한 취소
-- CASCADE : 권한 취소 시 부여할 수 있는 권한과 권한을 부여받은 사용자의 권한도 연쇄적으로 취소
-- 사용자 NABI에게 <고객>테이블에 대한 모든 권한과 다른 사람에게 권한을 부여할 수 있는 권한 부여
GRANT ALL ON 고객 TO NABI WITH GRANT OPTION;
-- 사용자 STAR에게 부여한 <고객>테이블에 대한 권한 중 UDPATE 권한을 다른 사람에게 부여할 수 있는 권한 취소
REVOKE GRANT OPTION FOR UPDATE ON 고객 FROM STAR;
- COMMIT
- 트랜잭션이 정상적으로 완료된 후 트랜잭션이 수행한 내용을 데이터베이스에 반영하는 명령어
- Auto Commit 기능 설정 가능
- COMMIT을 실행하지않아도 DML이 성공적으로 완료되면 자동으로 COMMIT되고, 실패하면 자동으로 ROLLBACK
- ROLLBACK
- 트랜잭션이 비정상적으로 종료되어 원래의 상태로 복구하는 명령어
- SAVE POINT
- 트랜잭션 내에 ROLLBACK 할 위치인 저장점을 지정하는 명령어
- SAVEPOINT S1;
- DML (Date Manipulation Language, 데이터 조작어)
- 사용자가 저장된 데이터를 실질적으로 관리하는데 사용되는 언어
- SELECT, INSERT, DELETE, UPDATE
- INSERT
INSERT INTO 테이블이름 ([속성명1, 속성명2, ..])
VALUES (데이터1, 데이터2, ..);
-- 사원 테이블
-- 이름, 부서, 생일, 주소, 기본급
--<사원>테이블에 이름이 홍승헌, 부서가 인터넷인 데이터를 삽입
INSERT INTO 사원(이름, 부서) VALUES ('홍승헌', '인터넷');
--<사원> 테이블에 (장보고, 기획, 05/07/73, '홍제동' 90)을 삽입
INSERT INTO 사원 VALUES ('장보고', '기획', '05/07/73', '홍제동', 90);
--<사원>테이블에 있는 편집부의 모든 튜플을 편집부원 (이름, 생일, 주소, 기본급) 테이블에 삽입
INSERT INTO 편집부원(이름, 생일, 주소, 기본급)
SELECT 이름, 생일, 주소, 기본급
FROM 사원
WHERE 부서 = '편집';
- DELETE
- 기본 테이블에 있는 특정 튜플을 삭제
- 레코드만 삭제 -> 테이블의 구조는 남아있음 (DROP과는 다름)
DELETE FROM 테이블이름 [WHERE 조건];
--<사원> 테이블에서 "임꺽정"에 대한 튜플 삭제
DELETE FROM 사원 WHERE 이름 = '임꺽정';
--<사원> 테이블에서 "인터넷" 부서에 대한 모든 튜플 삭제
DELETE FROM 사원 WHERE 부서 = '인터넷';
--<사원> 테이블의 모든 레코드 삭제
DELETE FROM 사원;
UPDATE 테이블이름 SET 속성명 = 데이터[, 속성명 = 데이터, ...] [WHERE 조건];
--<사원> 테이블에서 "홍길동"의 '주소'를 "수색동"으로 수정
UPDATE 사원 SET 주소 = '수색동' WHERE 이름 = '홍길동';
--<사원> 테이블에서 "황진이"의 '부서'를 "기획부"로 변경하고 '기본급'을 5만원 인상
UPDATE 사원 SET 부서 = '기획부', 기본급 = 기본급 + WHERE 이름 = '황진이';
- SELECT
- 튜플을 검색하는 명령문
- SELECT 절
- PREDICATE : 검색할 튜플 수를 제한하는 명령어
- DISTINCT : 중복된 튜플이 있으면 중복을 제거하여 표시
- AS : 속성이나 연산의 이름을 다른 이름으로 표시
- FROM 절
- WHERE 절
- ORDER BY 절
- NULL 값
- 조건 연산자
- 비교 연산자
- =, <> (같지않다), >, <, >=, <=
- 논리 연산자
- LIKE 연산자
- 대표 문자를 이용해 지정된 속성의 값이 문자 패턴과 일치하는 튜플을 검색하기 위해 사용
- % : 모든 문자를 대표
- _ : 문자 하나를 대표
- # : 숫자 하나를 대표
- SELECT 기본
--<사원> 테이블의 모든 튜플 검색
SELECT * FROM 사원;
--<사원> 테이블에서 '주소'만 검색하되, 중복을 제거
SELECT DISTINCT 주소 FROM 사원;
--<사원> 테이블에서 '기본급'에 10을 더한 월급을 "XX부서의 XX의 월급XX" 형태로 출력
SELECT 부서+'부서의' AS 부서2, 이름 + '의 월급' AS 이름2, 기본급 + 10 AS 기본급2
FROM 사원;
--<사원> 테이블에서 "기획"부의 모든 튜플 검색
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;
--<사원> 테이블에서 '주소'를 기준으로 내림차순 정렬시켜 상위 2개의 튜플만 검색
SELECT TOP 2* FROM 사원 ORDER BY 주소 DESC;
--<사원> 테이블에서 '부서'를 기준으로 오름차순 정렬하고 같은 '부서'에 대해서는 '이름'을 기준으로 내림차순
SELECT * FROM 사원 ORDER BY 부서 ASC, 이름 DESC;
- 하위 질의
- 조건절에 주어진 질의를 먼저 수행하여 그 검색 결과를 조건절의 피연산자로 사용
-- <여가활동> 테이블 (이름, 취미, 경력)
--'취미'가 "나이트댄스"인 사원의 '이름'과 '주소' 검색
SELECT 이름, 주소 FROM 사원 WHERE 이름
= (SELECT 이름 FROM 여가활동 WHERE 취미 = '나이트댄스');
--취미활동을 하지 않는 사원 검색
SELECT * FROM 사원 WHERE 이름 NOT IN (SELECT 이름 FROM 여가활동);
-- NOT IN : 포함되지 않는 데이터
--취미활동을 하는 사원들의 부서 검색
SELECT 부서 FROM 사원 WHERE EXISTS (SELECT 이름 FROM 여가활동 WHERE 여가활동.이름 = 사원.이름);
--EXISTS : 하위 질의로 검색된 결과가 존재하는지 확인
-- '경력'이 10년 이상인 사원의 '이름', '부서', '취미', '경력'을 검색
SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력
FROM 사원
WHERE 여가활동.경력 >= 10 AND 사원.이름 = 여가활동.이름;
- GROUP BY 절
- HAVING
- 그룹에 대한 조건 지정, GROUP BY와 함께 사용
- 그룹 함수
- COUNT(속성명) : 그룹별 튜플의 수를 구하는 함수
- SUM(속성명) : 그룹별 합계
- AVG(속성명) : 그룹별 평균
- MAX(속성명) : 그룹별 최대값
- MIN(속성명) : 그룹별 최소값
- STDDEV(속성명) : 그룹별 표준편차
- VARIANCE(속성명) : 그룹별 분산
- ROLLUP(속성명, ..) : 주어진 속성을 대상으로 그룹별 소계
- CUBE(속성명, ..) : 주어진 속성을 대상으로 모든 조합의 그룹별 소계
-
-- <상여금> 테이블 (부서, 이름, 상여내역, 상여금)
--<상여금> 테이블에서 '부서'별 '상여금'의 평균을 구하시오
SELECT 부서, AVG(상여금) AS 평균
FROM 상여금
GROUP BY 부서;
--<상여금> 테이블에서 부서별 튜플 수를 검색
SELECT 부서, COUNT(*) AS 사원수
FROM 상여금
GROUP BY 부서;
--<상여금> 테이블에서 '상여금'이 100 이상인 사원이 2명 이상인 '부서'의 튜플 수
SELECT 부서, COUNT(*) AS 사원수
FROM 상여금
WHERE 상여금 >= 100
GROUP BY 부서
HAVING COUNT(*) >= 2;
--<상여금> 테이블의 '부서', '싱여내역', '상여금'에 대해 부서별 상여내역별 소계와 전체 합계
-- 속성명은 '상여금합계', ROLLUP 함수 사용
SELECT 부서, 상여내역, SUM(상여금) AS 상여금합계
FROM 상여금
GROUP BY ROLLUP(부서, 상여내역);
--<상여금> 테이블의 '부서', '상여내역', '상여금'에 대해 부서별 상여내역별 소계와 전체 합계
--속성명은 '상여금합계', CUBE 함수 사용
SELECT 부서, 상여내역, SUM(상여금) AS 상여금합계
FROM 상여금
GROUP BY CUBE(부서, 상여내역);
- WINDOW 함수
- GROUP BY를 사용하지않고 함수의 인수로 지정한 속성의 값을 집계
- PARTITION BY : 함수의 적용 범위가 될 속성 지정
- WINDOW 함수
- ROW_NUMBER() : 각 레코드에 대한 일련번호 반환
- RANK() : 윈도우별로 순위를 반환, 공동 순위 반영
- DENSE_RANK() : 순위 반환, 공동 순위 무시
--<상여금> 테이블 (부서, 이름, 상여내역, 상여금)
--<상여금> 테이블에서 '상여내역'별로 '상여금'에 대한 일련번호를 구하시오
--순서는 내림차순, 속성명은 'NO'
SELECT 상여내역, 상여금,
ROW-NUMBER() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS NO
FROM 상여금;
--<상여금> 테이블에서 '상여내역'별로 '상여금'에 대한 순위
--순서는 내림차순, 속성명은 '상여금순위'
SELECT 상여내역, 상여금,
RANK() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS 상여금순위
FROM 상여금;
- 집합 연산자
- UNION : 합집합, 중복된 행은 한번만 출력
- UNION ALL : 합집합, 중복된 행도 그대로 출력
- INTERSECT : 교집합
- EXCEPT : 차집합
-- <사원> 테이블 (사원, 직급)
-- <직원> 테이블 (사원, 직급)
--<사원> 테이블과 <직원>테이블을 통합 (단, 같은 레코드는 중복을 제거)
SELECT * FROM 사원 UNION SELECT * FROM 직원;
--<사원> 테이블과 <직원>테이블에 공통으로 존재하는 레코드만 통합
SELECT * FROM 사원 INTERSECT SELECT * FROM 직원;
- JOIN
- 2개의 릴레이션에서 연관된 튜플들을 결합하여, 하나의 새로운 릴레이션을 반환
- 일반적으로 FROM 절에 기술
- INNER JOIN
- CROSS JOIN과 동일한 결과를 얻음
- CROSS JOIN(교차 조인)
- 조인하는 두 테이블에 있는 순서 쌍을 결과로 반환
- 결과로 반환되는 행의 수는 두 테이블의 행의 수를 곱한 것과 같음
- EQUI JOIN
- 대상 테이블에서 공통 속성을 기준으로 '=' 비교에 의해 같은 값을 가지는 행을 연결
- NATURAL JOIN : 중복된 속성을 제거하여 같은 속성을 한번만 표기하는 방법
- NON-EQUI JOIN
- '='조건이 아닌 나머지 비교연산자를 사용하는 방법
- OUTER JOIN
- JOIN 조건에 만족하지 않는 튜플들도 결과로 출력하는 방법
- LEFT OUTER JOIN
- 우측 항 릴레이션의 어떤 튜플과도 맞지 않는 좌측 항의 릴레이션에 있는 튜플들을 NULL 값으로 INNER JOIN에 출력
- RIGHT OUTER JOIN
- 좌측 항 릴레이션의 어떤 튜플과도 맞지 않는 우측 항의 릴레이션에 있는 튜플들을 NULL 값으로 INNER JOIN에 출력
- FULL OUTER JOIN
