제1장 : SQL 기본
제 1 절 : 관계형 데이터베이스 개요
- 데이터베이스
- SQL
제 2 절 : DDL
- 데이터유형
1) CHAR(s)
a) 고정길이 : 할당변수가 s보다 작을경우 나머지는 공백으로 채움
b) 비교방법 : 공백을 채워 비교 'AA' == 'AA '
2) VARCHAR(s)
a) 가변길이 : 할당변수 길이만큼(단,s가 최대)
b) 비교방법 : 있는 그대로 'AA' != 'AA '
2. CREATE TABLE
1) 구문형식
CREATE TABLE 테이블이름(
칼럼명 데이터타입 [DEFAULT] [NOT NULL]
..............
CONSTRAINT 제약조건명 제약조건
);테이블이름은 숫자로 시작하면안되고, '-'는 허용
2) 제약조건
a) PK, Unique Key
CONSTRAINT 제약조건명 PK/UK (기본키)b) FK
CONSTRAINT 제약조건명 FK (외래키) REFERENCES 다른테이블(참조키)cf) CTAS
CREATE TABLE 테이블명 AS SELECT * FROM 다른테이블- NOT NULL을 제외한 모든 제약조건은 복제되지 않음
3. ALTER TABLE
1) ADD COLUMN
ALTER TABLE 테이블명 ADD (칼럼명 데이터타입);추가된 칼럼은 마지막에 위치하고 위치지정은 안된다.
2) DROP COLUMN
ALTER TABLE 테이블명 DROP COLUMN 컬럼명데이터 유무와 상관없으며, 한번에 하나만 삭제가능하다. 복구 불가능.
3) MODIFY COLUMN
ALTER TABLE 테이블명
MODIFY (컬럼명 데이터타입 [DEFAULT] [NOT NULL] ...);- 칼럼의 크기는 늘릴수 있지만 줄일 수 없다.
- 데이터타입을 바꿀수 없다.
- 단, 모두 NULL값이거나 테이블에 행이없으면 칼럼의 크기를 늘리거나 데이터타입을 바꿀 수 있다.
- DEFAULT값 변경은 변경 후 삽입되는 행에만 적용
- NULL값이 없을 경우에만 NOT NULL 추가 가능
4) RENAME COLUMN
ALTER TABLE 테이블명 RENAME COLUMN 이전칼럼명 TO 새로운 칼럼명;5) ADD CONSTRAINT
ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건;6) DROP CONSTRAINT
ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;
4. RENAME TABLE
RENAME 이전테이블명 TO 새로운테이블명;
5. DROP TABLE
DROP TABLE 테이블명 [CASCADE CONSTRAINT];
6. TRUNCATE TABLE
TRUNCATE TABLE 테이블명;테이블 삭제가 아니라 테이블 안에 있는 행들이 삭제되어 저장공간을 재사용 가능하도록 해제한다. 테이블 삭제는 DROP
DELETE와 다르게 복구 불가능 BUT 시스템 부하가 적다.
제 3 절 : DML
- INSERT
INSERT INTO 테이블명 (컬럼 리스트) VALUES (컬럼 리스트에 넣을 값);
2. UPDATE
UPDATE 테이블명 SET 칼럼명 = 값;
3. DELETE
DELETE FROM 테이블명;
4. SELECT
SELECT [DISTINCT] FROM 테이블명 [WHERE ....]- 문자||문자
- CONCAT(문자,문자)
SELECT PLAYER_NAME|| '선수,'||HEIGHT|| 'cm,'||WEIGHT|| 'kg' 체격정보
제 4 절 : TCL
- 개요
1) 트랜잭선 : 분리될 수 없는 한개이상의 데이터베이스 조작(DML)
2) 특징
원자성 : 트랜잭션에서 정의된 연산은 모두 성공하던지 아니면 전혀 실행되지 않아야 한다.(all or nothing)
일관성 : 트랜잭션이 실행되기 전 데이터베이스에 이상이 없다면 실행 후에도 이상이 없어야 한다.
고립성 : 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아선 안된다.
지속성 : 트랜잭션이 성공적으로 수행되면 영구적으로 저장된다.
2. COMMIT & ROLLBACK
1) COMMIT, ROLLBACK 이전 데이터 상태
- 단지 메모리 BUFFER에만 영향
- 현재 사용자는 SELECT로 결과 확인 가능
- 다른 사용자는 SELECT로 결과 확인 불가능
- 변경된 행은 LOCKING되고 다른 사용자가 변경 불가능
2) 이후 데이터 상태
- DB에 반영
- 이전데이터는 영원히 삭제
- 모든 사용자가 결과 확인 가능
- LOCKING가 해제되고 다른 사용자가 행 조작 가능
3) 효과
ㄱ) 데이터 무결성 보장
ㄴ) 영구적 데이터 변경 전 데이터 변경사항 확인 가능
ㄷ) 논리적으로 연관된 작업을 그룹핑하며 처리
4) 정리
ㄱ) DDL전후로 자동 COMMIT
ㄴ) DB정상종료 → 자동 COMMIT
ㄷ) 어플리케이션 이상종료 → 트렌잭션 자동 ROLLBACK
5) 트랜잭션 구동방식
ㄱ) AUTO COMMIT(DML, DDL)
- 명령어가 성공하면 DBMS가 자동 COMMIT
- 명령어가 실패하면 DBMS가 자동 ROLLBACK
ㄴ) 암시적 트랜잭션
- 트랜잭션의 시작 - DBMS가 처리
- 트랜잭션의 끝 - 사용자가 명시적으로 COMMIT, ROLLBACK
ㄷ) 명시적 트랜잭션
- 트랜잭션의 시작 - BEGIN TRAN
- 트랜잭션의 끝 - COMMIT[TRANSACTION], ROLLBACK[TRAINSACTION]
3. SAVEPOINT
- 트랜잭션의 일부만 ROLLBACK
SAVEPOINT 포인트명;
ROLLBACK TO 포인트명;
제 5 절 : WHERE
- 연산자
2. ROWNUM, TOP
1) ROWNUM : 1건의 행만 가지고 오고 싶을때
2) TOP : 출력되는 행의 수 제한
SELECT TOP(1) : 맨위 한건만
SELECT TOP(N) : 맨위 N건만
SELECT TOP(N) PERCENT : 맨위 N%만
SELECT TOP(N) PERCENT WITH TIES ... ORDER BY : N%마지막 행과 값이 같은 행을 추가적으로 같이 출력
제 6 절 : 함수
- 문자형 함수
2. 숫자형 함수
3. 날짜형 함수
날짜 +- 숫자 ⇒ 숫자만큼의 날수를 날짜에서 더하거나 뺌
날짜1 - 날짜2 ⇒ 일수
날짜 +- 숫자/24 ⇒ 시간을 날짜에 더함
4. 변환형 함수
1) 명시적 데이터유형 변환 : 데이터 변환형 함수로 데이터 유형을 변환하도록 명시
2) 암시적 데이터유형 변환 : 데이터베이스가 자동으로 데이터 유형 변환, 성능저하나 에러 발생가능성이 있기 때문에 명시적 데이터유형 변환을 사용하는것 추천
5. CASE
SELECT CASE 칼럼명
WHEN 조건1 THEN 조건2
............
ELSE 결과 N
FROM 테이블명;
6. NULL관련 함수
1) NVL/ISNULL (칼럼명, 결과)
2) NULLIF (표현식, 표현식2)
- 표현식1 = 표현식2 → NULL RETURN
- 표현식1 ≠ 표현식2 → 표현식 1 RETRUN
3) COALESCE(표현식1, 2, 3...)
NULL이 아닌 최초의 표현식 리턴, 모두 NULL이면 NULL리턴
4) NULL함수와 공집합
cf) 결과가 공집합일 때 무엇인가 출력하고 싶으면 집계함수 이용
SELECT NVL(MAX(칼럼명),'출력')
FROM...
제 7 절 : GROUP BY, HAVING 절
- 집계함수
집계함수명([DISTINCT | ALL] 칼럼명)
1) COUNT(*) : NULL 포함 COUNT(칼럼) : NULL 제외
2) SUM(칼럼) : NULL 제외
3) AVG(칼럼) : NULL 제외
4) MAX(칼럼) : NULL 제외 MIN(칼럼) : NULL 제외
5) STDDEV(칼럼) : 표준편차
6) VARIAN(칼럼) : 분산
- WHERE 절 뒤에 집계함수 사용
- GROUP BY, HAVING
SELECT DISTINCT 칼럼명 ALIAS명 (GROUP BY에서 쓴 칼럼만 쓸 수 있음) FROM 테이블명 WHERE 조건식 GROUP BY 칼럼(Column)이나 표현식 HAVING 그룹조건식 ;
- 집계함수와 NULL
- 다중행 함수에 NVL함수를 사용하면 부하 발생 → 굳이 쓸 필요 없음
- SUM(NVL(SAL,0)) → X
- NVL(SUM(SAL),0) → O
제 8 절 : ORDER BY
SELECT 칼럼명 ALIAS명
FROM 테이블명
WHERE 조건식
GROUP BY 칼럼(Column)이나 표현식
HAVING 그룹조건식
ORDER BY 칼럼(Column)이나 표현식 [ASC 또는 DESC] ;
ASC(Ascending) : 조회한 데이터를 오름차순으로 정렬한다.(기본 값이므로 생략 가능)
DESC(Descending) : 조회한 데이터를 내림차순으로 정렬한다ORDER BY 절에 칼럼(Column)명 대신 SELECT 절에서 사용한 ALIAS 명이나 칼럼 순서를 나타내는 정수도 사용 가능
제 9 절 : JOIN
- EQUI JOIN : 두개의 테이블 간에 칼럼 값들이 정확하게 일치하는 경우(INNER JOIN)
- NON EQUI JOIN
SELECT EMPNO
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
SELECT EMPNO
FROM EMP, INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO
SELECT EMPNO
FROM EMP JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO3. TABLE JOIN
SELECT P.PLAYER_NAME 선수명,
P.POSITION 포지션,
T.REGION_NAME 연고지,
T.TEAM_NAME 팀명,
S.STADIUM_NAME 구장명
FROM PLAYER P, TEAM T, STADIUM S
WHERE P.TEAM_ID = T.TEAM_ID
AND T.STADIUM_ID = S.STADIUM_ID
ORDER BY 선수명;SELECT P.PLAYER_NAME 선수명,
P.POSITION 포지션,
T.REGION_NAME 연고지,
T.TEAM_NAME 팀명,
S.STADIUM_NAME 구장명
FROM PLAYER P INNER JOIN TEAM T ON P.TEAM_ID = T.TEAM_ID INNER JOIN STADIUM S ON T.STADIUM_ID = S.STADIUM_ID
ORDER BY 선수명;
4. NATURAL INNER JOIN
- 테이블간 동일한 칼럼명을 갖는 모든 칼럼들을 EQUI JOIN
- 기준이 되는 칼럼을 하나로 처리하고 첫번째로 출력
사용불가 예
SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME
FROM EMP NATURAL JOIN DEPT; SELECT ...
FROM ... NATURAL JOIN...
ON | USINGSELECT DEPTNO DEP
FROM ... NATURAL JOIN...
WHERE 조인조건절
5. CROSS JOIN
- 양쪽집합의 N*N건의 데이터 조합발생
cf) NATURAL JOIN과 다르게 WHERE절에 조인가능 그러나 이는 INNER JOIN과 같기 때문에 CROSS JOIN을 사용하는 의미가 없다.
6. OUTER JOIN
1) LEFT JOIN
2) RIGHT JOIN
3) FULL(OUTER) JOIN
SELECT L.DEPTNO, L.DNAME, L.LOC, R.DEPTNO, R.DNAME, R.LOC
FROM DEPT L LEFT OUTER JOIN DEPT_TEMP R
ON L.DEPTNO = R.DEPTNO
UNION
SELECT L.DEPTNO, L.DNAME, L.LOC, R.DEPTNO, R.DNAME, R.LOC
FROM DEPT L RIGHT OUTER JOIN DEPT_TEMP R
ON L.DEPTNO = R.DEPTNO;
7. USING ON
1) USING(칼럼)
- 기준이 되는 칼럼을 하나로 처리, 첫번째로 출력
2) ON(조건식)
- 괄호는 선택사항