SQL 이론

DDL : CREATE, ALTER, DROP

SCHEMA, DOMAIN, TABLE, INDEX, VIEW를 정의하거나 변경 또는 삭제
 
CREATE
1. CREATE TABLE
PRIMARY KEY : 기본키로 사용할 속성 지정
UNIQUE : 대체키로 사용할 속성을 지정, 중복 값 불가
FOREIGN KEY ~ REFERENCES ~ : 외래키로 사용할 속성을 지정
- ON DELETE : 참조 테이블 튜플이 삭제되면 기본 테이블이 취해야 할 사항
- ON UPDATE : 참조 테이블 참조 속성 값이 변경되었을 때 취해야 할 사항
 
2. CREATE INDEX
CREATE(UNIQUE) INDEX 인덱스명
ON 테이블명(속성명 ASC/DESC);
*ex) <고객> 테이블에서 UNIQUE한 '고객번호' 속성에 대해 내림차순으로 정렬하여 '고객번호_idx'라는 인덱스 정의
=> CREATE UNIQUE INDEX 고객번호_idx
ON 고객(고객번호 DESC);
 
ALTER
ALTER TABLE 테이블명 ADD 속성명 데이터_타입; : 새로운 속성(열) 추가
ALTER TABLE 테이블명 ALTER 속성명; : 특정 속성의 Default 값 변경
ALTER TABLE 테이블명 DROP COLUMN 속성명; : 특정 속성 삭제
*ex) <학생> 테이블에 최대 3문자로 구성되는 '학년' 속성 추가
ALTER TABLE 학생 ADD 학년 VARCHAR(3);
 
DROP
DROP SCHEMA 스키마명 CASCADE/RESTRICT;
DROP TABLE 테이블명 CASCADE/RESTRICT;
DROP INDEX 인덱스명 CASCADE/RESTRICT;
- CASCADE : 제거할 요소를 참조하는 다른 모든 개체를 함께 제거
- RESTRICT : 다른 개체가 제거할 요소를 참조중일 때는 제거를 취소
*ex) <학생> 테이블을 제거하되, <학생> 테이블을 참조하는 모든 데이터를 함께 제거
=> DROP TABLE 학생 CASCADE;

---

DCL : COMMIT, ROLLBACK, GRANT, REVOKE

데이터베이스에 접근하거나 객체에 권한을 주는 등의 역할

GRANT/REVOKE
1. GRANT : 권한 부여
GRANT 사용자등급 TO 사용자_ID_리스트;
GRANT 권한_리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];
- WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한을 부여
*ex) 'NABI'에게 모든 권한을 부여
=> GRANT RESOURCE TO NABI;
'NABI'에게 <고객> 테이블에 대한 모든 권한과 다른 사람에게 권한을 부여할 수 있는 권한 부여
=> GRANT ALL ON 고객 TO NABI WITH GRANT OPTION;
 
2. REVOKE : 권한 취소
REVOKE 사용자등급 FROM 사용자_ID_리스트; 
REVOKE [GRANT OPTION FOR] 권한_리스트 ON 개체 FROM 사용자 [CASCADE];
- GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있는 권한을 취소
*ex) 'STAR'에게 부여된 <고객> 테이블에 대한 권한 중 UPDATE 권한을 다른 사람에게 부여할 수 있는 권한만 취소
REVOKE GRANT OPTION FOR UPDATE ON 고객 FROM STAR;
 
COMMIT
트랜잭션이 수행한 내용을 데이터베이스에 반영
 
ROLLBACK
변경되었으나 아직 COMMIT되지 않은 모든 내용들을 취소하고 데이터베이스를 이전 상태로 되돌리는 명령어

---

DML : SELECT, INSERT, DELETE, UPDATE, JOIN

저장된 데이터를 실질적으로 처리

INSERT
INSERT INTO 테이블명(속성명1, 속성명2, ...)
VALUES (데이터1, 데이터2, ...);
*ex) <사원> 테이블에 (이름 : 김철수, 부서 : 인터넷) 삽입
=> INSERT INTO 사원(이름, 부서)
VALUES ('김철수', '인터넷');
 
DELETE
DELETE
FROM 테이블명
WHERE 조건;
*ex) <사원> 테이블에서 '김철수'에 대한 튜플을 삭제
=> DELETE
FROM 사원
WHERE 이름 = '김철수';
 
UPDATE
UPDATE 테이블명
SET 속성명1 = 데이터1, 속성명2 = 데이터2, ...
WHERE 조건;
*ex) <사원> 테이블에서 '김철수'의 '부서'를 '기획'으로 변경하고 '기본급'을 5만원 인상
=> UPDATE 사원
SET 부서 = '기획', 기본급 = 기본급 + 5
WHERE 이름 = '김철수';
 
SELECT -1
1. 일반 형식
SELECT 튜플수
FROM 테이블명
WHERE 조건
ORDER BY ASC/DESC;
 
2. 하위 질의 : 조건절에 주어진 질의를 먼저 수행하여 그 검색 결과를 조건절의 피연산자로 사용
*ex) 여가활동에서 취미가 '나이트댄스'인 사원의 '이름'과 '주소'를 검색
=> SELECT 이름, 주소
FROM 사원
WHERE 이름 = (SELECT 이름 FROM 여가활동 WHERE 취미 = '나이트댄스');
 
3. 복수 테이블 검색
*ex) 경력이 10년 이상인 사원의 '이름', '부서', '취미', '경력'을 검색
=> SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동,경력
FROM 사원.여가활동
WHERE 여가활동.경력 >= 10 AND 사원.이름 = 여가활동.이름;
 
SELECT - 2
1. 일반 형식
SELECT 테이블명.속성명 AS 별칭, ...
FROM 테이블명
GROUP BY(속성명1, 속성명2, ...)
HAVING 조건;
 
2. 그룹 지정 검색
*ex) <상여금> 테이블에서 '부서'별 '상여금'의 평균
=> SELECT 부서, AVG(상여금) AS 평균
FROM 상여금
GROUP BY 부서;
<상여금> 테이블에서 '상여금'이 100 이상인 사원이 2명 이상인 '부서'의 튜플 수
SELECT '부서', COUNT(*) AS 사원수
FROM 상여금
WHERE 상여금 >= 100
GROUP BY 부서
HAVING COUNT(*) >= 2;
 
3. 집합 연산자
UNION : 두 SELECT문의 조회 결과를 통합하여 모두 출력, 중복된 행은 한 번만 출력 (합집합)
UNION ALL : 두 SELECT문의 조회 결과를 통합하여 모두 출력, 중복된 행도 그대로 출력 (합집합)
INTERSECT : 두 SELECT문의 조회 결과 중 공통된 행만 출력 (교집합)
EXCEPT : 첫 번째 SELECT문의 조회 결과에서 두 번째 SELECT문의 조회 결과를 제외한 행을 출력 (차집합)
SELECT 속성명1, 속성명2, ...
FROM 테이블명
UNION/UNION ALL/INTERSECT/EXCEPT
SELECT 속성명1, 속성명2, ...
FROM 테이블명
ORDER BY 속성명 ASC/DESC;
*ex) <사원> 테이블과 <직원> 테이블을 같은 레코드가 중복되지 않도록 통합
SELECT *
FROM 사원
UNION
SELECT *
FROM 직원;
<사원> 테이블과 <직원> 테이블에 공통으로 존재하는 레코드만 통합
SELECT *
FROM 사원
INTERSECT
SELECT *
FROM 직원;
 
JOIN : 2개의 릴레이션에서 연관된 튜플들을 결합하여 하나의 새로운 릴레이션으로 변환
INNER JOIN
1. EQUI JOIN : =(equal) 비교에 의해 같은 값을 가지는 행을 연결하여 결과를 생성
1-1. WHERE절 사용
SELECT 테이블명1.속성명, 테이블명2.속성명, ...
FROM 테이블명1, 속성명2
WHERE 테이블명1.속성명 = 테이블명2.속성명;
1-2. NATURAL JOIN절 사용
SELECT 테이블명1.속성명, 테이블명2.속성명, ...
FROM 테이블명1 NATURAL JOIN 테이블명2;
1-3. JOIN ~ USING절 사용 : 두 테이블에는 이름과 도메인이 같은 속성이 반드시 존재
SELECT 테이블명1.속성명, 테이블명2.속성명, ..
FROM 테이블명1 JOIN 테이블명2 USING(속성명)
*ex) <학생> 테이블과 <학과> 테이블에서 '학과코드' 값이 같은 튜플을 JOIN하여 '학번', '이름', '학과코드', '학과명' 출력
WHERE절
SELECT 학번, 이름, 학생.학과코드, 학과명
FROM 학생.학과
WHERE 학생.학과코드 = 학과.학과코드;
NATURAL JOIN절
SELECT 학번, 이름, 학생.학과코드, 학과명
FROM 학생 NATURAL JOIN 학과;
JOIN ~ USING절
SELECT 학번, 이름, 학생.학과코드, 학과명
FROM 학생 JOIN 학과 USING(학과코드);
 
2. NON-EQUI JOIN : '='이 아닌 나머지 비교 연산자 '>', '<', ' <>', '>=', '<=' 연산자를 사용
SELECT 테이블명1.속성명, 테이블명2.속성명
FROM 테이블명1, 테이블명2
WHERE 조건;
*ex) <학생> 테이블과 <성적등급> 테이블을 JOIN하여 각 학생의 '학번', '이름', '성적', '등급'을 출력
SELECT 학번, 이름, 성적, 등급
FROM 학생, 성적등급
WHERE 학생.성적 BETWEEN 성적등급.최저 AND 성적등급.최고;
 
OUTER JOIN : JOIN 조건에 만족하지 않는 튜플도 결과로 출력
1. LEFT OUTER JOIN : INNER JOIN의 결과를 구한 후 우측 항 릴레이션의 조건에 맞지 않는
좌측 항 릴레이션에 있는 튜플들에 NULL 값을 붙여서 INNER JOIN 결과에 추가
1-1. LEFT OUTER JOIN절 사용
SELECT 테이블명1.속성명, 테이블명2.속성명
FROM 테이블명1 LEFT OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;
1-2. WHERE절 사용
SELECT 테이블명1.속성명, 테이블명2.속성명
FROM 테이블명1, 테이블명2
WHERE 테이블명1.속성명 = 테이블명2.속성명(+);
 
2. RIGHT OUTER JOIN : 우측 항 릴레이션에 있는 튜플들에 NULL 값을 붙여서 INNER JOIN 결과에 추가
1-1. RIGHT OUTER JOIN절 사용
SELECT 테이블명1.속성명, 테이블명2.속성명
FROM 테이블명1 RIGHT OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;
1-2. WHERE절 사용
SELECT 테이블명1.속성명, 테이블명2.속성명
FROM 테이블명1, 테이블명2
WHERE 테이블명1.속성명(+) = 테이블명2.속성명;
 
3. FULL OUTER JOIN : LEFT + RIGHT
SELECT 테이블명1.속성명, 테이블명2.속성명
FROM 테이블명1 FULL OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;