[SQLD] 핵심요약 (2)

조건문

Decode

- select decode(col1,'A',1,'B',2,3) from dual; (col이 A면 1, B면 2, 아니면 3)

 

case

- case when col = 'A' then 1

           when col = 'B' then 2

           else 3 end;

- case col when 'A' then 1

                 when 'B' then 2

                 else 3 end;

---

집계함수

- Count, min sum, max 등

- null은 포함되지 않음

 

- (1, null, 2, 3, null) 의 데이터를 기준으로 결과는 다음과 같음

- Count() : 3

- Sum() : 6

- Avg() : 2

- Min() : 1

- Max() : 3

 

Col1	Col2	Col3
null	null	1
2	3	2
1	null	null

- Select sum(col1 + col2 + col3) from dual; => null + 7 + null = 7 출력

- sum(col1) + sum(col2) + sum(col3) => 3 + 3 + 3 = 9 출력

---

그룹바이 group by

- 집약기능 가짐 (다수의 행을 하나로 합침)

- Group by 절에 온 컬럼만 select 절에 올 수 있음

---

join

Natural join

- 반드시 두 테이블 간 동일한 이름, 타입 가진 컬럼 필요

- 조인에 이용되는 컬럼은 명시하지 않아도 자동으로 조인에 사용

- 동일한 이름 갖는 컬럼 있지만 데이터 타입 다르면 에러 발생

- 조인하는 테이블 간 동일 컬럼이 SELECT 절에 기술되도 테이블 이름 생략해야 함

- select department_id 부서, department_name 부서이름, location_id 지역번호, city 도시

  from departments

  natural join locations

  where city = 'Seattle';

 

Using

- USING 절은 조인에 사용될 컬럼 지정함

- NATURAL 절과 USING 절은 함께 사용할 수 없음

- 조인에 이용되지 않은 동일 이름 가진 컬럼은 컬럼명 앞에 테이블명 기술

- 조인 컬럼은 괄호로 묶어서 기술

- select department_id 부서번호, department_name 부서, loaction_id 지역번호, city 도시

  from departments

  join loactions using (location_id);

 

left outer join

- from table a left outer join table b

  on a.col = b.col

- from table a, table b

  where a.col = b.col(+) (오라클 sql 문법)

 

join 순서

- from a,b,c (a와 b가 join되고, 그리고 c와 join됨)

---

서브쿼리

- Select : 스칼라 서브쿼리

- From : 인라인뷰 (메인 쿼리 컬럼 사용 가능)

- where : 중첩 서브쿼리

- Group by : 사용 불가

- Having : 중첩 서브쿼리

- Order by : 스칼라 서브쿼리

 

- In : 서브쿼리 출력값들 or 조건

- Any/some : 서브쿼리 출력값들 중 가장 작거나 큰 값과 비교

- All : any/some 과 반대 개념

- Exists : 서브쿼리내 select 절엔 뭐가 와도 상관 없음. Row 있으면 true, 없으면 false

---

집합연산자

- Union : 정렬O, 중복제거O, 느림

- Intersect : 정렬O, 교집합, 느림

- Minus (except) : 정렬O, 차집합, 느림

- Union all : 정렬X, 중복제거X, 빠름

---

DDL

- Truncate : drop & create, 테이블 내부 구조는 남아 있으나 데이터가 모두 삭제됨

- Drop : 테이블 자체가 없어짐 (데이터도 없어짐)

- Delete : 데이터만 삭제

- Rollback : commit이랑 항상 같이 나옴

---

DML

- Insert : 데이터 넣는 명령, insert into 테이블 (col1, col2, col3...) values ('11', '22', '33'...);

               vlaues 기준으로 좌우 괄호 속 개수 맞아야 함

- update : 데이터 특정 행의 값을 변경 (delete & insert)

                 update 테이블 set col = '값' where col1 = '조건';

- delete : 데이터 특정 행 삭제

               delete from 테이블 where col = '조건';

- merge : 특정 데이터 넣을 때 해당 테이블 키값을 기준으로 있으면 update, 없으면 insert 함\

---

제약조건

- PK : not null + unique

          테이블 당 하나의 PK 가질 수 있음 (복합키 가능)

- Not null : 해당 컬럼에 null 올 수 없음

- Unique : 해당 컬럼에 중복값 올 수 없음

---

DCL

- GRANT 시스템 권한명 [, 시스템 권한명 .... | 롤명] TO 유저명 [, 유저명... | 롤명... | PUBLIC | [WITH ADMIN OPTION];

- REVOKE { 권한명 [, 권한명...] ALL} ON 객체명 FROM {유저명 [, 유저명...] | 롤명(ROLL) | PUBLIC} [CASCADE CONSTRAINTS];

- Role은 객체

---

VIEW

- 독립성, 편의성, 보안성

- SQL 저장하는 개념

---

그룹함수

- roll up (순서 중요)

- cube (순서 상관 X)

- groupingsets

- grouping

---

ROLLUP

- GROUP BY에 있는 컬럼들을 오른쪽에서 왼쪽으로 그룹 생성

- a, b로 묶이는 그룹의 값

- a로 묶이는 그룹 소계

- 전체 합계

CUBE

- 나올 수 있는 모든 경우의 수로 그룹 생성

- a, b로 묶이는 그룹의 값

- a로 묶이는 그룹의 값

- b로 묶이는 그룹의 소계

- 전체 합계

 

rollup(A,B) != rollup(B,A), cube(A,B) = cube(B,A)

---

TCL

- Commit, rollback

- Auto commit, begin transaction (commit 기능 잠시 끄기) end

---

윈도우 함수

- rows between and 값 증가

- rows between UNBOUNDED PRECEDING and CURRENT ROW) as "직업별 합계";

- rows between 1 PRECEDING and 1 FOLLOWING) as "위아래합계";

 

- range between and 값 동일

1. UNBOUNDED PRECEDING : 최종 출력될 값의 맨 처음 row 값 (Partition by 고려)

2. CURRENT ROW : 현재 row 값

3. UNBOUNDED FOLLOWING : 최종 출력될 값의 맨 마지막 row 값 (Partition by 고려)

 

- Rank : 1, 1, 3, 4... (동률있을 시 건너뜀)

- Dense_rank : 1, 1, 2, 3... (건너뛰는것 없음)

 

- Partition by, order by

  Row_number() over (partition by col1 order by col2)...

---

계층형 함수

- prior 자식데이터 = 부모데이터

- 부모데이터에서 자식데이터로 가면 순방향

- 순방향 : SELECT LEVEL,

                               LPAD(' ', 4 * (LEVEL - 1)) || 사원 사원,

                               관리자,

                                CONNECT_BY_ISLEAF ISLEAF

                        FROM 사원

                         START WITH 관리자 IS NULL

                CONNECT BY PRIOR 사원 = 관리자;

- 역방향 :SELECT LEVEL,

                               LPAD(' ', 4 * (LEVEL - 1)) || 사원 사원,

                               관리자,

                                CONNECT_BY_ISLEAF ISLEAF

                        FROM 사원

                         START WITH 사원 = 'D'

                CONNECT BY PRIOR 관리자 = 사원;