NULL : 빈칸, NULL과 숫자날짜를 더하면 NULL이 된다.
NULL 관련 함수
NVL : NULL이면 다른 값으로 바꿔라
NVL2 : NUL2(칼럼,1,0) 칼럼이 NULL이 아니면 1을 NULL이면 0을 반환
NULLIF : NULL(값1,값2) 값이 같으면 NULL 다르면 값1 반환
COALESCE : COALESCE(값1,값2,값3...) 값1이 NULL이 아니면 값1, NULL이면 값2...

PREIMARY KEY (PK) : 테이블에 저장된 레코드를 식별하는 칼럼, NOT NULL + UNIQUE
FOREIGN KEY(FK) : 두 테이블을 연결하는데 사용하는 KEY 반드시 uniqe나 pk여야 한다.
데이터 구조 중 INT와 BIGINT 차이 : INT(4바이트 할당) BIGINT(8바이트 할당)
AUTO_INCREMENT : 자동 생성
DATETIME INSERT 방법 : '2023-06-05 05:00:00' 방식으로 입력하면 자동으로 그렇게 설정함.

COUNT 함수
SELECT COUNT(* ) : row를 다 센다
SELECT COUNT(name) : name을 다 센다
SLECET COUNT(DISTINCT name) : 중복을 제거하고 name을 센다

삭제의 구분
ALTER TABLE 테이블명 DROP COLUMN 칼럼명; 칼럼 삭제
DROP TABLE 테이블명; 테이블 삭제
TRUNCATE TABLE 테이블명 ; 다 삭제
DELETE FROM 테이블명 WHERE 조건 : 행 삭제

ps. 데이터 모델링에서 사용할 때 카디널리티는 한 테이블이 다른 테이블과 가질 수 있는 관계를 나타냅니다. 다대다, 다대일/일대다 또는 일대일의 테이블 간의 관계를 카디널리티라고 합니다. SQL에서 사용할 때는 해당 열에 대한 테이블에 나타나는 고유한 값의 수를 나타냅니다

타입

1) 숫자 타입
정수 : tinyint(1),int(4),bigint(8) ; 1 = 8bit = 256 (2의 8승) =128~127
MYSQL은 INT를 사용하고, NUMBER는 소수점을 사용할 수 있다.
실수 : decimal(M,D) ; 소수점 위m,아래d (소수점이 있는 실수, 최대 65) 생략시 10.0
FLOAT(M,D)

2) 문자 타입
CHAR : 특정 문자열 개수를 지정할때 CHAR(10) 10자리 문자열, ~255까지 , 지정된 길이만큼을 사전에 차지함.
VARCHAR(M) : M은 최댓값. 가변길이의 문자열을 저장할 때 사용한다. 영어는 1바이트 지만, UTF-8은 기본적으로 4바이트를 할당한다.
VARCHAR(10): 가변적이라서 글 쓰는 만큼만 할당된다. 최대가 40바이트라는 소리지, 입력을 쫌쫌따리로 하면 쫌쫌 따리로만 바이트를 줌. 최대 65535까지 사용, 메모리를 적극적으로 사용하여 속도가 빠름, b-tree인덱스 가능
TEXT : 65,535까지 가능, 가변 데이터, 디스크에 저장되어 메모리(RAM)에 효율적, 최대크기를 지정하지 않는다. text는 index 사용 불가 (full-text는 인덱스 가능, b-tree는 불가)

3) 시간 타입
date : 날짜를 지정할 수 있는 타입 yyyy-nn-dd
datetime(m) : 날짜와 함께 시간까지 저장하며, m 지정 시 소수점
yyyy-mm-dd hh:mm:ss
datemine default current_timestamp; 현재 시간
select * from author where created_at > '2023-06-07'; 검색

4) 기타
BLOB : 다양한 크기의 바이너리 데이터를 저장할 수 있는 타입, 이미지 저장할 때 등
(이미지 DB 저장 방법1. 이미지는 디스크에 저장 2. 이미지 파일을 바이너리로(이진법) 체계로 변환해 저장)

BNUM : 특정한 문자열 종류만 지정하여 INSERT하게 함.
ALTER TABLE author Modify column role ENUM('user','admin');

CAST :숫자 값을 2020-02-01 등 DATE 타입으로 변환하는데 사용
CONVERT : 문자열을 날짜/시간으로 변환하는데 사용된다

INSERT INTO author (email, created_at) values ('jang@naver.com', cast(20220101030201 as datetime)); cast 뒤에 띄어쓰기하면 에러남 ㅜㅜㅋㅋㅋㅋ

1. SQL 기본

DDL(DATA DEFINITION LANGUAGE)

1. 테이블 생성

CREATE TABLE 테이블명 (칼럼1, 데이터 타입, 칼럼2, 데이터 타입, 기본 키);

1) 제약조건 사용

컬럼 NUMBER(10) DEFAULT 0; 기본값을 0으로 지정한다
컬럼 DATETIME DEFAULT SYSDATE ; 기본값을 오늘 날짜로 지정한다
created_at datetime DEFAULT CURRENT_TIMESTAMP

PRIMARY KEY(컬럼) ; 기본키를 지정한다
텍스트컬럼 FOREIGN KEY(현재 칼럼) REFERENCES 외부테이블명(외부 칼럼) ; 외래키를 지정한다
칼럼 VARCHAR(40) NOT NULL; 무조건 채워야함

외래키 지정예시)
ALTER TABLE EMP ADD CONSTRAINT FK_DEPT FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO); 책
create table post(id bigint AUTO_INCREMENT, title varchar(30), contents varchar(255), author_id bigint, primary key(id), foreign key(author_id) references author(id)); # 실습 author id를 가져와서 foreign key를 삼겠다.

제약조건
NOT NULL
PRIMARTY KEY(NOT NULL, UNIQUE)
FOREIGN KEY
UNIQUE
DEFAULT

PK, FK, UNIQUE 제약 조건을 걸게 되면 자동으로 INDEX가 생성된다.
자동으로 인덱스 카피본을 만든다. (WHERE 조건으로 자주 검색 할거니까) + 내가 별도로 만드는 index까지
인덱스는 우리눈에는 안보이고 컴퓨터가 자동으로 만든거임

create index my_index on author(name); 네임에 인덱스를 설정해
SHOW INDEX FROM author; 테이블에 인덱스 걸려있는거 보여줘

2) CASCADE 사용

컬럼 FOREIGN KEY(현재 칼럼) REFERENCES 외부테이블명(외부 칼럼) ON DELETE CASCADE; 참조관계(외래키)가 있을 때 참조되는 데이터를 자동으로 반영할 수 있다. 삭제할 때 같이 삭제된다.
ALTER TABLE post DROP FOREIGN KEY post_ibfk_1, DROP INDEX author_id; 삭제
ALTER TABLE post ADD CONSTRAINT post_author_fk FOREIGN KEY (author_id) REFERENCES author(id) ON DELETE CASCADE; 생성

CASCADE : 참조되는 테이블에서 삭제/수정되면 같이 됨
ON DELETE SET NULL : 삭제되면 FK값을 NULL로 변경
RESTRICT : CASCADE가 없으면 리스트릭트가 디폴트다 (삭제 불가)

3) 생성된 테이블 조회

DESC 테이블명; 생성한 테이블의 구조 확인(구조 정보 조회) DESCRIBE 테이블명으로도 조회됨

2. 테이블 변경

1) 테이블명 변경

ALTER TABLE 현재테이블명 RENAME TO 새테이블명 ;

2) 칼럼 추가

ALTER TABLE 테이블명 ADD (AGE NUMBER(2)) ; 칼럼을 추가한다.

3) 칼럼 데이터 타입 변경

ALTER TABLE 테이블명 MODIFY 변경할 칼럼명 변경할 데이터타입; 칼럼 구조 변경
ALTER TABLE 테이블명 MODIFY email varchar(20) not null uniqe; email칼럼에 unique 옵션을 걸기.
예시) alter table author MODIFY age varchar(4);

4) 칼럼 삭제

ALTER TABLE 테이블명 DROP COLUMN 칼럼명;

5) 칼럼명 변경

ALTER TABLE 테이블명 RENAME COLUMN 기존이름 TO 바꿀이름;

3. 테이블 삭제

DROP TABLE 테이블명;
DROP TABLE 테이블명 CASCADE CONSTRANINT ; 외래키로 참조한 테이블 제약사항 삭제

4. VIEW 생성과 삭제

: 뷰란 테이블로부터 유도된 가상의 테이블
뷰는 데이터 딕셔너리에 SQL문 형태로 저장하되 실행 시에 참조된다.
실제 데이터를 가지고 있지 않고 테이블을 참조해서 원하는 칼럼만을 조회한다.(보안 기능 강화)
뷰에 대한 입력/수정/삭제에는 제약이 있다. 데이터 관리가 간단하다.
보안성을 향상 시킨다. 뷰는 변경할 수 없고 변경을 원하면 삭제 후 재생성해야 한다.
ALTER로 변경 못한다. 연산 제약, 데이터 구조 변경 불가, 독자적인 인덱스 사용불가

CREATE VIEW 테이블별명 AS SELECT FROM 조회할 테이블 ; 뷰 생성
SELECT FROM 테이블별명 ; 뷰 조회
DROP VIEW 테이블별명 ; 뷰삭제

DML(DATA MANIPULATION LANGUAGE)

1. INSERT문

1) INSERT INTO

INSERT INTO 테이블명 (칼럼1,칼럼2..) VALUES (값1,값2...) #테이블에 데이터를 입력한다
(최종적으로 데이터를 저장하려면 COMMIT을 실행하야 한다. AUTO COMMIT인 경우 제외)

2) SELECT문으로 입력

SELECT INTO 입력할테이블 SELECT * FROM 테이블명
해당 테이블의 모든 데이터를 조회해서 입력할 테이블에 입력한다.

3) NOLOGGING 사용

ALTER TABLE 테이블명 NOLOGGING;
DB에 데이터를 입력하면 로그파일에 그 정보를 기록한다.
CHECK POINT라는 이벤트가 발생하면 로그파일의 데이터를 데이터 파일에 저장한다.
NOLOGGING 옵션은 로그파일의 기록을 최소화시켜서 입력 시 성능을 향항시키는 방법이다

2. UPDATE문

UPDATE 테이블 SET 칼럼2 = '바꿀내용' WHERE 칼럼1 = 1 ;
테이블에서 칼럼1이 1인걸 조회하여, 칼럼2를 바꿀내용으로 바꾼다.
조건절이 없거나, 조건절이 중복되면 모든 데이터가 변경된다.

3. DELETE문

DELETE FROM 테이블명 WHERE 조건; 조건에 맞는 행을 삭제한다. 조건이 없으면 모두 삭제된다
DELETE문은 삭제는 되지만, 용량이 초기화되지 않는다.
TRUNCATE TABLE 테이블명;으로 삭제하면 테이블의 용량도 초기화된다.

4. SELECT문

1) SELECT문 사용

SELECT * FROM 테이블명 WHERE 조건 ; 조건에 맞는 모든 데이터를 조회한다.
SELECT 칼럼1,칼럼2 FROM 테이블명 ; 칼럼1,칼럼2 데이터를 조회한다.
SELECT 칼럼||'님 FROM 테이블명 : 칼럼+님이라는 문자로 출력한다.

2) ORDER BY 정렬

SELECT * FROM 테이블명 ORDER BY 칼럼 DESC; 칼럼명의 내림차순 정렬
ASC : 오름차순 1 ~ 특별한 지정이 없으면 오름차순
DESC : 내림차순 100 ~

예시)
select * from author order by name asc, book desc;
여러가지 기준으로 정렬하기 위해서는 ,로 나눠줘야 한다. 맨 처음걸 기준으로 정렬하고 중복이 되는 경우 그 다음 컬럼으로 정렬한다.

3) INDEX를 사용한 정렬 회피

SELECT / +INDEX_DESC(A) / FROM 테이블명 A; 정렬은 db에 부하를 주므로 인덱스(pk)를 사용해서 order by~를 회피할 수있다. pk 자동으로 오름차순 인덱스가 생성된다.

4) DISTINCT와 ALIAS (엘리어스)

SELECT DISTINCT 칼럼 FROM 테이블; 칼럼 값이 중복되지 않는다.
SELECT 칼럼 AS '별명' FROM 테이블 테이블별명; WHERE 테이블별명.칼럼 = 100; 칼럼명을 별명으로 출력되게 한다. 테이블별명을 테이블처럼 사용한다.

예시) select distinct book from author;
null도 1개로 체크되어서 나온다. 중복이 제거된다.

예시) select name as N from author; 혹은 select name N from author;
이러면 결과 컬럼명에 N으로 나옴 as를 쓰지 않는 경우 name으로 나옴

5) WHERE문 사용

!=, ^=, <>, NOT칼럼명= : 같지 않은 것
NOT칼럼명 > : 크지 않은 것

LIKE '%비교문자열%' : %는 모든 값을 의미한다. '조%'조로 시작하는 모든 것 %A% A가 중간에 있는 모든 것 : 한개인 단일 문자로 LIKE 'TEST' 면 TEST로 시작하고 하나의 글자가 더 있는 것을 조회한다 TEST1,TEST2...
select * from author where name like 'kim%';

BETWEEN A AND B : A와 B 사이의 값
IN : OR을 의미하며 값중에 하나만 일치해도 조회된다.
SELECT * FROM 테이블명 WHERE (직업,이름) IN ((의사,경찰),(JON,LEE))
NOT IN : LIST와 불일치한 것을 조회한다.
IS NULL : NULL값 조회
IS NOT NULL : NULL값이 아닌 것을 조회한다.

6) GROUP BY

GROUP BY는 테이블에서 소규모행을 그룹화하여 합계, 평균, 최대값, 최솟값등을 계산할 수 있다. 조건문은 HAVING으로 시작한다. ORDER BY로 정렬할 수 있다.

SELECT 부서번호, SUM(급여) FROM 테이블명 GROUP BY 부서번호 ; 부서번호로 그룹을 만들고 그룹별 급여 합계를 계산해라

SELECT 부서번호, SUM(급여) FROM 테이블명 GROUP BY 부서번호 HAVING SUM(급여) > 10000;
GROUP BY 결과에서 급여 합계가 10000 이상만 조회한다.

집계 함수 : COUNT(행수), SUM, AVG, MAX, MIN, STDDEV(표준편차), VARIANCE(분산)
COUNT함수는 행 수를 계산하고 NULL값이 제외된다. NULL을 포함하고 싶으면 COUNT(* )

예시) 사원번호 1000~1003번의 부서별 급여 합계
SELECT 사원번호, SUM(급여) FROM 회사테이블 WHERE 사원번호 1000 AND 1003 GROUP BY 사원번호;
예시2) 직업별 급여합계 중에 급여 합계가 1000이상 조회
SELECT 직업, SUM(급여) FROM 회사테이블 GROUP BY 직업 HAVING SUM(급여) > 1000;

7) SELECT문 실행순서

FROM > WHERE > GROUP BY > HAVING < SELECT < ORDER BY

8) 명시적 형변환과 암시적 형변환

형변환 : 두 개의 데이터의 데이터 타입이 일치하도록 변환하는 것
명시적 형변환 : 형변환 함수를 사용해서 개발자가 SQL 사용할 때 함
TO_NUMBER(문자열) : 문자열을 숫자로 변환
TO_CHAR(숫자,날짜, [FORMAT]): 숫자,날짜를 지정된 FORMAT문자로 변환
TO_DATE(문자열,FORMAT) : 문자열을 지정된 FORMAT의 날짜형으로 변환
암시적 형변환 : 데이터베이스 관리 시스템이 자동으로 형변환 하는 것

9) 내장형 함수

DUAL 테이블 : 오라클 데이터 베이스에 의해서 자동으로 생성되는 테이블, 내장형 함수를 실행할 때도 사용할 수 있다. DESC DUAL 입력

문자열 함수
ASC||(문자) : 문자, 숫자를 아스키 코드값으로 변환
CHR/CHAR(코드값) : 아스키 코드값을 문자로 변환
SUBSTA(문자열,M,N) : 문자열에서 m번째 위치부터 n개를 자른다
CONCAT(문자열1,문자열2) : 문자열1,2를 결합
LOWER(문자열), UPPER(문자열) : 소문자/대문자 변환
LENGTH, LEN(문자열) 공백을 포함한 문자열의 길이
LTRIM(문자열, 지정문자) : 왼쪽에서 지정된 문자/없으면 공백 삭제
RTRIM(문자열, 지정문자) : 오른쪽에서 지정된 문자/없으면 공백 삭제
TRIM(문자열,지정문자) : 왼쪽 및 오른쪽에서 지정된 문자/없으면 공백 삭제
SYSDATE 'YYYYMMDD' :오늘의 날짜를 타입으로 알려준다.
EXTRACT('YEAR'|'MONTH'|'DAY' FROM DUAL) : 날짜에서 년,월,일 조회
ABS(숫자) : 절대값
SIGN(숫자) : 양수, 음수, 0 구별
MOD(숫자1,숫자2) : 나머지
CEIL/CEILING(숫자) : 숫자보다 크거나 같은 최소의 정수
FLOOR(숫자) : 숫자보다 작거나 같은 최소의 정수
ROUND(숫자,M) : 소수점 M의 자리에서 반올림 (기본값 0)
TRUNC(숫자,M) : 소수점 M의 자리에서 절삭 (기본값 0)

10) DECODE와 CASE

DECODE (칼럼, 1,'TRUE','FALSE') ; IF문저럼, 칼럼1이 1이면 TRUE, 아니면 FALSE를 반환한다.
CASE WHEN 조건 THEN 결과1 WHEN 조건2 THEN 결과2 ELSE 결과3 END
조건이 참이면 THEN 거짓이면 ELSE 가 실행된다.

예시) SELECT CASE WHEN ID = 1 THEN 1 WHEN ID = 2 THEN 2 ELSE 3
END FROM 테이블명;

IFNULL(A,B)
A가 NULL이 아니면 A반환, NULL이면 B반환

select name, if(book IS NULL,'s','a') from author; if앞에 , 안찍으면 에러남

11) ROWNUM과 ROWID

SELECT FROM (SELECT ROWNUM LIST, NAME FROM 테이블명) WHERE LIST BETWEEN 5 AND 10; (5~10 행만 조회하기)
ROWNUM은 결과에 대하여 논리적인 일련번호를 부여한다. 웹페이지 조회 등 조회되는 행 수를 제한할 때 사용한다. mysql은 LIMIT구를 사용한다.
select from 테이블명 limit 10;

ROWID은 오라클 데이터베이스 내에서 데이터를 구분할 수 있는 유일한 값이다. ROWID를 통해서 데이터가 어떤 데이터 파일, 어느 블록에 저장되어 있는지 알 수 있다. 자동으로 생성되는 값이다.

12) WITH 구문

WITH 구문은 서브쿼리를 사용해서 임시 테이블이나 뷰처럼 사용할 수 있는 구문으로 스버쿼리 블록에 별칭을 지정할 수 잇고, 옵티마이저는 SQL을 인라인 뷰나 임시 테이블로 판단한다.

예시) 직원 테이블에서 WITH구문을 사용해서 부서번호가 30인 것의 임시 테이블을 만들고 조회하시오
WITH 직원테이블 AS (SELECT FROM 직원테이블 WHERE 부서번호 = 30) SELECT FROM 직원테이블;

DCL (DATA CONTRAL LANGUGE)

1) GRANT

GRANT 권한 ON 테이블명 To 유저 ;
데이터 베이스 사용자에게 권한을 부여한다.
WITH GRATN OPTION : 특정 사용자에게 권할을 부여할 수 있는 권한 부여
WITH ADMIN OPTION : 테이블에 대한 모든 권한 부여
예시)
GRANT SELECT, INSERT, UPDATE ON 테이블명 TO USER WITH GRANT OPTION;

2) REVOKE

REVOKE 권한 ON 테이블명 FROM 유저;
데이터베이스사용자에게 부여된 권한을 회수한다.

TCL (TRANSACTION CONTROL LANGUAGE)

1) COMMIT

COMMIT은 변경된 데이터를 데이터베이스에 반영한다. COMMIT이 완료되면 데이터베이스 변경으로 인한 LOCK이 해제된다. 완료되야지 변경된 데이터를 조작할 수 있다. COMMIT을 실행하면 하나의 트랜잭션 과정을 종료한다.
DDL, DCL을 사용하는 경우 자동 COMMIT 된다. SET AUTOCOMMIT ON;을 실행하면 자동 COMMIT된다.

2) ROLLBACK

ROLLBACK을 실행하면 데이터에 대한 변경 사용을 모두 취소하고 트랜잭션을 종료한다. 이전 COMMIT한 곳까지만 복구한다. ROLLBACK을 실행하면 LOCK이 해제되고 다른 사용자도 조작이 가능하다.

3) SAVEPOINT <저장명>

SAVEPOINT를 사용하면 지정된 위치 이후의 트랜잭션만 롤백할 수 있다. ROLLBACK TO <SAVEPOINT명>을 실행한다.

2. SQL 활용

1.EQUI JOIN

1) EQUI JOIN (교집합)

조인의 기본은 교집합 , 두 테이블 간의 일치하는 것을 조인한다.
SELECT * FROM 테이블1,테이블2 WHERE 테이블1.칼럼 = 테이블2.칼럼; '='로 두개의 테이블을 연결한다.

2) INNER JOIN

SELECT * FROM 테이블1 INNER JOIN 테이블2 ON 테이블1.칼럼 = 테이블2.칼럼;

해시 조인 : EQUI JOIN만 가능한 방법으로 선행 테이블을 결정하고 선행 테이블에서 WHERE에 해당하는 행을 선택하고, 조인 키를 기준으로 해시 함수를 사용해서 해시 테이블을 메인 메모리에 생성하고 후행 테이블에서 주어진 조건에 만족하는 행을 찾는다. 후행 테이블의 조인 키를 사용해서 해시 함수를 적용하여 해당 버킷을 검색한다.

3) INTERSECT 연산

두 개의 테이블에서 교집합을 조회한다. (공통된 값)
SELECT 칼럼1 FROM 테이블1 INERSECT SELECT 칼럼2 FROM 테이블2;

2. NON-EQUI(비등가) JOIN

두 개의 테이블 간에 조인하는 경우 '='가 아니라 >,<,>= 등을 사요하여 정확하게 일치하지 않는 것을 조인하는 형식이다.

3. OUTER JOIN

테이블 간의 교집합을 조회하고 한쪽 테이블에만 있는 데이터도 포함시켜서 조회한다.
LEFT OUTER JOIN : 두 개의 테이블에서 같은 것을 조회하고 왼쪽 테이블에 있는 행 포함
FIGHT OUTER JOIN : 오른쪽 테이블에 있는 행도 포함
FULL OUTER JOIN : 모두 포함

1) LEFT OUTER JOIN, RIGHT OUTER JOIN

SELECT FROM 테이블1 LEFT OUTER JOIN 테이블2 ON 테이블1.칼럼 = 테이블2.칼럼;
SELECT FROM 테이블1 RIGHT OUTER JOIN 테이블2 ON 테이블1.칼럼 = 테이블2.칼럼;

4. CROSS JOIN (카테시안곱 발생)

조인 조건구 없이 2개의 테이블을 하나로 조인한다. 카테시안 곱이 발생한다.
SELECT FROM 테이블1 CROSS JOIN 테이블2 ; 테이블1 테이블2
SELECT * FROM 테이블1,테이블2;

5. UNION (합집합)

1) UNION : 중복제거 + 정렬

두 개의 테이블을 하나로 만드는 연산, 테이블의 칼럼 수, 칼럼의 데이터 형식 모두가 일치해야 한다. UNION 연산은 중복을 제거하고, 정렬 과정을 발생시킨다.
SELECT 칼럼1 FROM 테이블1 UNION SELECT 칼럼2 FROM 테이블2;

2) UNION ALL

두 개의 테이블을 하나로 합치는 것으로 중복제거나 정렬을 유발하지 않는다.
SELECT 칼럼1 FROM 테이블1 UNION ALL SELECT 칼럼2 FROM 테이블2;

6. MINUS (차집합)

두 개의 테이블에서 차집합을 조회한다. 먼저 쓴 SELECT문에는 있고 뒤에 쓰는 SELECT문에는 없는 집합을 조회한다. (MS-SQL에서는 EXCEPT)
SELECT 칼럼1 FROM 테이블1 MINUS SELECT 칼럼2 FROM 테이블2
테이블1에 있고 테이블2에 없는 집합을 조회한다.

2. 계층형 조회(CONNECT BY)

오라클 데이터베이스에서 지원하는 것으로 계층형으로 데이터를 조회할 수 있다. EX) 부장차장과장대리사원순.. 트리 형태의 구조를 위->아래로 탐색하면서 조회 가능

START WITH는 시작 조건, CONNECT BY PRIOR는 조인 조건 Root 노드로부터 하위 노드의 질의를 실행한다. 계층형 max(level)을 사용하여 최대 계층 수를 구할 수 있다. 계층형 구조에서 마지막 leaf node의 계층 값을 구한다.
PRIOR 키워드 : 바로 직전에 출력된 행을 의미한다.

CONNECT BY 키워드
LEVEL : 검색 항목의 깊이를 의미한다. 계층 구조에서 가장 상위 레벨이 1
CONNECT_BY_ROOT : 계층 구조에서 가장 최상위 값
CONNECT_NY_ISLEAF : 계층 구조에서 가장 최하위 값
SYS_CONNECT_BY_PATH : 계층 구조의 전체 전개 경로
NOCYCLE : 순환 구조가 발생 지점까지만 전개
CONNECT_BY_ISCYCLE : 순환 구조 발생 지점을 표시

계층형 조회
STAR WITH 조건 : 계층 전개의 시작 위치 지정 (계층형 조회에서 첫 시작점)
PRIOR 자식 = 부모 : 부모에서 자식 방향으로 검색 수행
PRIOR 부모 = 자식 : 자식에서 부모 방향으로 검색 수행
NOCYCLE : 사이클이 발생되지 않게 한다(이미 조회된 데이터를 다시 조회하면 사이클)
ORDER SIBLINGS BY 칼럼명 : 동일한 level인 형제 노드 사이에서 정렬 수행

3. 서브쿼리

1) 메인쿼리와 서브쿼리

서브쿼리는 select문에서 다시 select문을 사용하는 sql이다. FROM구에 SELECT문을 사용하는 인라인뷰와 SELECT문에 서브쿼리를 사용하는 스칼라 서브쿼리가 있다. FROM구에 SELECT문을 사용한 것이 인라인뷰다.
SELECT* FROM 테이블명1 WHERE 칼럼1 = (SELECT 칼럼2 FROM 테이블명2 WHERE 칼럼1 = 10);

2) 단일행 서브쿼리와 다중행 서브쿼리

반환하는 행 수가 한 개, 반환하는 행 수가 여러개로 분류된다.
단일행 서브쿼리 : 결과는 반드시 한 행만 조회된다. 비교 연산자인 =, >, <= 등을 사용한다.
다중행 서브쿼리 : 결과는 여러행이 조회된다. IN, ANY,EXISTS를 사용한다

3) 다중행

다중행 서브쿼리는 서브쿼리 결과가 여러개의 행을 반환한다.
SELECT 이름1,이름2,급여 FROM 테이블1,테이블2 WHERE 테이블1.ID = 테이블2.ID AND 테이블1.ID_2 IN (SELECT ID_3 FROM 테이블1 WHERE 급여>2000);
테이블1에서 급여가 2000원 초과인 ID를 반환하고 반환된 ID를 메인쿼리에 있는 ID와 비교해서 같은 것을 조회한다.

IN : 메인쿼리의 비교 조건이 서브쿼리의 결과 중 하나만 동일하면 참
ALL: 메인쿼리와 서브쿼리의 결과가 모두 동일하면 참 >ALL 최대값 반환, < ALL 최소값반환
ANY : 메인쿼리의 비교 조건이 서브쿼리의 결과 중 하나 이상 동일하면 참 < ANY 하나라도 크게 되면 참, > ANY 하나라도 작게 되면 참
EXISTS : 메인쿼리와 서브쿼리의 결과가 하나라도 존재하면 참(어떤 데이터 존재 여부 확인)
4) 스칼라 서브쿼리 : 한 행과 한 칼럼만 반환하는 서브 쿼리
5) 연관 서브쿼리 : 서브쿼리에서 메인 쿼리내의칼럼을 사용한다.

4. 그룹함수

1) ROLLUP
ROLLUP은 GROUP BY의 칼럼에 대해 SUBTOTAL을 만들어준다.
ROLLUP을 할때 GROUP BY구에 칼럼이 두 개 이상 오면 순서에 따라서 결과가 달라진다.

SELECT DECODE(NO,NULL,'전체합계'NO),SUM(급여) FROM 테이블 GROUP BY ROLLUP(NO);

ROLLUP을 사용하면 부서별 합계 및 전체 합계가 계산된다.
DECODE문은 전체합계를 조회할 때 전체합계라는 문자를 출력하기 위해서 사용된다.

2) GROUPING 함수
ROLLUP, CUBE, GROUPING SETS에서 생성되는 합계값을 구분하기 위한 함수.

SELECT NO,DECODE(GROUP(NO),1,'전체합계') JOB, DECODE(GRUOPING(JOB),1,'부서합계),SUM(급여) FROM 테이블1

DECODE함수를 사용해서 CROUPING 함수 결과가 1이면 전체합계, 부서합계를 출력하고 그렇지 않으면 NULL을 반환한다.

3) GROUPING SETS 함수
GRUOP BY에 나오는 칼럼의 순서와 관계없이 다양한 소계를 만들고, 개별적으로 처리한다.
SELECT ID,JOB,SUM(급여) FROM 테이블 GROUP BY GROUP SETS(ID, JKB); ID과 JOB을 각각의 그룹으로 합계를 계산한다.

4) CUBE 함수
CUBE함수에 제시한 칼럼에 대하여 결합 가능한 모드 ㄴ집계를 계산한다. 부서 * 직업 -> 부서별 합계, 직업별 합계, 부서별 직업별 합계, 전체 합계 조회

5. 윈도우 함수

1) 윈도우 함수
행과 행간의 관계를 정의하기 위해서 제공되는 함수, 순위, 합계, 평균, 행 위치 등을 조작할 수 있다.
윈도우 함수 구조
ARGUMENT(인수) : 0~N개의 인수를 설정한다
PARTITION BY : 전체집합을 기준에 의해 소그룹으로 나눈다다
ORDER BY : 정렬
WINDOWING : 행 기준의 범위를 정한다. ROWS는 물리적 결과의 행 수이고 RANGE는 논리적인 값에 의한 범위다.

WINDOWING
ROWS : 부분집합인 윈도우 크기를 무리적 단위로 행의 집합을 지정한다.
RAGNE : 논리적인 주소에 의해 행 집합을 지정한다
BETWEEN~AND : 윈도우의 시작과 끝의 위치를 지정한다
UNBOUNDED PRECEDING : 윈도우의 시작 위치가 첫번째 행
UNBOUNDED FOLLOWING : 윈도우 마지막 위치가 마지막 행
CURRENT ROW : 윈도우 시작 위치가 현재 행

SELECT NO,NAME,SAL,SUM(SAL) OVER(ORDER BY CAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING TOTSAL FROM EMP;
TOTSAL에 처음부터 마지막까지의 합계SUM(SAL)을 계산한 것

2) 순위 함수
SELECT NAME,SAL, RANK() OVER (ORDER BY CAL SECS) ALL RANK, RANK() OVER(PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK ; 급여 순위를 계산한다. 동일한 순위는 동일하게 조회되며 직업별 순위를 계산한다. SAL로 등수를 계산하고 내림차순으로 조회, JOB으로 파티션을 만들고 JOB별 순위를 조회하게 한다.

RANK : 특정항목 및 파티션에 대해서 순위를 계산한다, 동일한 순위에는 동일한 값이 부여된다 1,2,2,4..
DENSE_RANK : 동일한 순위를 하나의 건수로 계산 1,2,2,3
ROW_NUMBER : 동일한 순위에 대해서 고유의 순위를 부여한다1,2,3,4

3) 집계 함수 : SUN, AVG, COUNT, MAX, MIN

4) 행 순서 관련 함수
상위 행의 값을 하위에 출력하거나 하위 행의 값을 상위 행에 출력할 수 있다. 특정 위치의 행을 출력할 수 도 있다.
FIRST_VALUE : 파티션에서 가장 처음에 나오는 값, MIN
LAST_VALUE : 가장 나중에 나오는 값, MAX
LAG : 이전 행
LEAD : 특정위치의 행 기본값은 1 (첫번째 행의 값)
SELECT NO, NAME, SAL, LEAD(SAL,2) OVER(ORDER BY SAL DESC) AS PRE_SAL FROM EMP; LEAD는 지정된 위치의 행을 가져온다 SAL에서 2번째 행위 값을 가지고 온다.

5) 비율 관련 함수
누적 백분율, 순서별 백분율, 파티션을 n으로 분할한 결과 등을 조회할 수 있다.
CUME_DIST : 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 조회한다. 누적 분포상에 위치를 0~1사이의 값을 가진다.
PERCENT_RANK : 파티션에서 제일 먼저 나온 것을 0으로 제일 늦게 나온 것을 1로 하여 값이 아닌 행의 순서별 백ㅜㄴ율을 조회한다.
NTILE : 파티션별로 전체 건수를 ARGUMENT값으로 N등분한 결과를 조회한다.
RATIO_TO_REPORT : 파티션 내에 전체 SUM(칼럼)에 대한 행 별 칼럼 값의 백분율을 소수점까지 조회한다.

SELECT NO, NAME, SAL, NTILE(4) OVER(ORDER BY SAL DESC) AS n-TILE FROM EMP; 4개로 등분하여 분류한다, 급여가 높은 순으로 4개로 등분한다.

6. 테이블 파티션

1) 파티션 기능
파티션은 대용량의 테이블을 여러개의 데이터 파일에 분리해서 저장한다. 입력/수정/삭제 성능이 향상된다. 독립적으로 관리 가능, 전용 인덱스 생성 가능, 테이블 스페이스간에 이동이 가능, 조회할 때 데이터의 범위를 줄여서 성능 향상시킴

2) RANGE PARTITION
테이블의 칼럼 중에서 값의 범위를 기준으로 여러개의 파티션으로 데이터를 나누어 저장

3) LIST PARTITION
특정 값을 기준으로 분할하는 방법

4) HASH PARTITION
데이터베이스 관리 시스템이 내부적으로 해시 함수를 사용해서 데이터를 분할한다. (관리 시스템이 알아서 분할 관리)

5) 파티션 인덱스
Global index : 여러개의 파티션에서 하나의 인덱스를 사용한다
Local index : 해당 파티션 별로 각자의 인덱스를 사용한다
Prefixed index : 파티션 키와 인덱스 키가 동일하다
Non Prefixed index : 파티션 키와 인덱스 키가 다르다

3. SQL 최적화의 원리

1. 옵티마이저와 실행계획

1) 옵티마이저

SQL의 실행 계획을 수립하고 SQL을 실행하는 데이터베이스 관리 시스템의 소프트웨어다.
SQL 개발자가 SQL을 작성해서 실행할 때 옵티마이저는 SQL을 어떻게 실행할 것인지 실행 계획을 수립하고 SQL을 실행한다.

2) 옵티마이저 특징

데이터 딕셔너리에 있는 오브젝트 통계, 시스템 통계 등의 정보를 사용해서 예상되는 비용을 산정하고 최저비용을 가지고 있는 계획을 선택하여 실행한다.

3) 옵티마이저 필요성

AND 조건으로 데이터를 읽을 때 작은 집합을 먼저 읽는 등.. 실행 계획을 변경할 수 있는 것은 HINT를 사용한다.

4) 옵티마이저 실행 계획 확인

옵티마이저는 SQL 실행 계획을 PLAN_TABLE에 저장한다.
DESC PLAN_TABLE ; 실행 계획 확인
TOAD에서 버튼 눌러서도 확인 가능

2. 옵티마이저 종류

1) 옵티마이저의 실행방법

SQL 실행 -> 파싱(구문 분석) -> 규칙/비용 기반 옵티마이저 -> 실행계획 -> SQL 실행
구문 분석이 완료되면 옵티마이저가 규칙 기반, 비용 기반으로 실행 계획 수립

2) 옵티마이저 엔진

QUERY TRANSFORMER : SQL문을 실행하기 위해 옵티마이저가 변환
ESTIMATOR : 통계 비용 사용하여 SQL 실행 비용 계산
PLAN GENERATOR : SQL을 실행할 실행 계획 수립
1~15개의 우선 순위가 있는데 우선순위 1은 ROWID를 사용한 단일 행인 경우이다. 최신 오라클은 비용 기반 옵티마이저를 기본으로 사용한다.
SELECT / +RULE/* FROM 테이블 WHERE ROWID='AA'
옵티마이저에서 실행방법을 RULE 을 사용해서 알려주는 것이 HINT다. 테이블을 ROWID로 조회한것.

3) 비용기반 옵티마이저

총 비용 : SQL문을 실행하기 위해서 예상되는 소요시간, 자원의 사용량을 의미

3. 인덱스

1) 인덱스

인덱스는 인덱스 키로 정렬되어 있기 때문에 데이터를 빠르게 조회할 수 있다. 하나의 테이블에 여러개의 인덱스를 생성할 수 있고, 정렬이 가능하다. 테이블을 생성할 때 PK는 자동으로 인덱스가 만들어지고 인덱스의 이름은 SYSXXXX이다. 인덱스의 구조는 Root Block(가장 상위 노드), Branch Block(다음 단계의 주소를 가지고 있는 포인터), Leaf Block(인덱스 키와 rowid)으로 구성되어 있다.

• index : 검색의 효율성 때문에 사용함 , B-tree 자료구조. where에서 계층 구조를 만든다. 조회 횟수를 최소화 한다.
  예시) 823을 그냥 조회하면 823번 조회, b-tree로 하면 8번->2번->3번해서 13번에 조회

2) 인덱스 생성

CREATE INDEX IND 테이블명 ON 테이블명(칼럼);
IND는 인덱스의 별명
파티션 인덱스
Global Index : 여러개의 파티션에서 하나의 인덱스를 사용
Local Index : 해당 파티션 별로 각자의 인덱스를 사용
Prefixed Index : 파티션 키와 인덱스 키가 동일
Non Prefixed Index : 파티션 키와 인덱스 키가 다름

3) 인덱스 스캔

인덱스 유일 스캔(INDEX UNIQUE SCAN) : 인덱스 키 값이 중복되지 않는 경우 해당 인덱스를 사용할 때 발생한다. 가장 검색 속도가 빠르다.
인덱스 범위 스캔(INDEX RANGE SCAN) : SELECT문에서 WHERE을 사용할 때 발생한다.
인덱스 전체 스캔(INDEX FULL SCAN) : 인덱스 키가 많은 경우에 LEAF BLOCK의 처음부터 끝까지 전체를 읽는다. 테이블의 데이터를 모두 읽은 것을 의미한다. HIGH WATERMARK는 테이블에 데이터가 저장된 블록에서 최상위 위치를 의미하고 데이터가 삭제되면 HIGH WATERMARK(테이블에서 데이터가 저장되어 있는 최상위 위치)가 변경된다.

4. 실행 계획

ALL_ROWS는 비용 기반 옵티마이저
SQL문의 실행 계획을 읽는 방법은 쉬에 있는 순서대로 읽는다.

5. 옵티마이저 조인

ORDERED : FROM 절에 기술한 테이블을 순서대로 조인을 하는 힌트

1) NESTED LOOP 조인

하나의 테이블에서 데이터를 먼저 찾고 그 다음 테이블을 조인하는 방식으로 실행된다. 먼저 조회되는 테이블을 OUTER TABLE, 그 다음이 INNER TABLE이다. OUTER TABLE(선행 테이블) 크기가 작은 것을 먼저 찾는 것이 중요하다. RANDOM ASCCESS 양을 줄여야 성능이 향상된다. ORDERED USE_NL()

2) SORT MERGE 조인

두 개의 테이블을 SORT_AREA라는 메모리 공간에 모두 로딩하고 SORT를 수행한다. SORT가 완료되면 테이블을 병합한다. 정렬이 발생하므로 데이터 양이 많아지면 임시 영역(디스크)에서 수행되고 느려진다. ORDERED USE_MERGE()

3) HASH 조인

두 개의 테이블 중에서 작은 테이블을 HASH 메모리에 로딩하고 두 개의 테이블의 조인 키를 사용해서 해시 테이블을 생성한다. 해당 주소를 사용하여 테이블을 조인하기 떄문에 CPU연산을 많이 하며, 선행 테이블이 충분히 메모리에 로딩되는 크기여야 한다. ORDERED USE_HASH()