SQL

연산 순서

From -> Where

Group by -> Having

Select -> Order by

 

DISTINCT

- Select distinct A, B from table

-> {a,b} 둘 다 같아야 제거됨

 

Alias

- Select col1 as A from table 

- as 생략 가능

- 별칭은 where절에서 사용 불가

ex) select A+B as SUM from table where SUM > 1000     에러발생

 

Limit

* RANK() = 1,1,3,4    DENSE_RANK = 1,1,2,3     ROW_NUMBER = 1,2,3,4

 

Order by

- 느림 

- order by a2, a1 desc -> a2 오름차순 이후 a1 내림차순

- order by 2,1 desc

- 컬럼명, 별칭, 숫자 전부 혼용 가능

- 실행 순서는 select 이후 order by 이지만 전체 행을 메모리에 올리기에 select에 없는 속성으로 정렬이 가능하다.

 

 

Group by

- null도 하나의 그룹

- select엔 그룹화에 쓴 컬럼과 집계함수만 올 수 있음

ex) SELECT class, count(*) FROM student GROUP BY class

 

- group by 없이 having 사용 가능 (테이블 전체가 1개의 그룹인 경우)

ex) SELECT sum(cnt) FROM student HAVING max(cnt)>100

 

 

 

그룹함수

https://for-my-wealthy-life.tistory.com/44

SQL 집계함수 - ROLLUP, CUBE, GROUPING SETS

ROLLUP - 계층적 = 인수 순서 중요

GroupingSets - 인수 순서 상관 x (계층 구조 평등)

CUBE - 모든 경우의 수 = 부하 큼  // 인수 순서 상관 X

 

groupingSets(A,B) = group by A + group by B

rollup(A,B) = group by (A,B) +group by (A)    *인자 순서 중요!

cube(A,B) = group by A + B + AB + (테이블 그룹) = grouping sets (a, b, (a,b), ())

 

GROUPING - 집게 결과 선택에 사용

 

=========================================================================

 

Natural join

- 동일한 이름 + 호환되는 타입을 가진 컬림이 필요

- 컬럼을 명시하지 않아도 자동으로 조인

- 컬럼 앞에 테이블 명 안붙임

- select * from A natural join B

 

Using

- select * from A join B using (id)

 

Left join

- 왼쪽에 있는건 전부 출력, 겹치는게 있으면 오른쪽도 붙여서 출력

- select * from A left outer join B on A.id = B.id

- 오라클: select * from A,B where A.id = B.id(+)   

- and B.col(+) = 'n' and A.col = 'y'  => SQL문으로 치환시  B의 조건은 on절로, A의 조건은 where절에 작성된다.

- full outer join = left UNION right

 

* CROSS JOIN에 ON 조건절로 INNER JOIN처럼 쓸 수는 있다. 굳이 의미는 없다.

 

집합

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

- Union, Intersect(교집합), Minus(차집합) = 느림

- Union, Intersect, minus를 하면 기존에 존재하던 중복도 전부 제거된다

- 정렬(order by)는 무조건 집합 이후 실행

 

해시 조인

- 무조건 등가 join만 사용 (a = b)

- 선행 테이블이 작으면 유리

- 해시 처리를 위한 별도의 메모리 공간 필요

- 해시를 사용한다 = 정렬 사용 안함 = 대용량 작업에 유리 

- join 컬럼의 인덱스가 없어도 사용 가능

 

NestLoop

- 랜덤 액세스

 

 

Sort Merge Join

- 조인되는 N개의 테이블을 모두 정렬한 후 조인 수행

- join 컬럼의 인덱스가 없어도 사용 가능

 

*NL = OLTP  // 머지 & 해시 = DW

 

Like join

a = { smith, allen, scott }

b = { s%, %t%}

a.name LIKE b.rule 조인시

 

s% = smith, scott = 2

%t% = smith, scott = 2

count(*) = 4 가 된다

 

A left join B ~~ where A in (1,2) -> 조인 후 A가 1이나 2인 것만 남김

A left join B on A in(1,2) -> A가 1이나 2인 것만 조인함 = A가 1이나 2가 아닌것도 출력은 됨

=========================================================================

 

NULL

- 모르는 값, 정의되지 않은 값

- 공백이나 0과는 다름

- NULL 사칙연산은 무조건 NULL

- NULL 비교연산은 무조건 False (null= null   //   null+2 < null+4   //  null in (null,1,2))

- null, 1 in (null, 1) -> 1만 뽑힘

- a, b not in (null) -> 무조건 아무것도 안뽑힘

 

- 집계 함수에선 제외됨 AVG(1,2,3,NULL) = 2

- SUM에 NULL 있으면 알아서 빠짐!!

- COUNT(A)에선 NULL이 제외되지만 COUNT(*)에선 세진다

- 정렬시 오라클에선 가장 큰 값, SQL에선 가장 작은 값 취급 *min max는 정렬이 아닌 집계

-> ORDER BY col DESC NULLS LAST / FIRST 

- group by로 나눌때 null도 하나의 그룹.

 

- IsNULL(A,B) A가 null이면 B, 아니면 A 반환

- NVL(A,B) A가 null이면 B, 아니면 A 반환

 

- NVL2(A,B,C) A가 null이면 C, 아니면 B 반환

- NULLIF(A,B) A==B면 NULL, 아니면 A 반환        

- COALESCE(A,B,C...) Null이 아닌 첫번째 값 반환

 

 

Math

- Round(222.45, 1) -> 소수 첫째 자리까지 출력 = 둘째 자리에서 반올림

- Trunc (222.45, 1) -> 소수 첫째 자리까지 출력 = 둘째 자리에서 버림

* -1이면 일의 자리에서 반올림 = 0이 1개

 

- 올림 = ceil (오라클)  //  ceiling (SQL)

- 내림 = floor

 

String

- lpad('A',5,'K') = KKKKA

- Substr('korea',2,3) = 2번째 글자 포함 3개 = ore 

- Insrt('aaabbb', 'ab') = 3

 

Concat

- SQL : a+b+c

- Oracle : a || b || c

- concat(a,b) *인자 2개?

 

조건문

- Decode( col1, 'a', 1, 'b', 2, 3)

- case when col1 = 'a' then 1

           when col1 = 'b' then 2     *col2로 바꿀 수 있음

           else 3 end;

- case col1 when 'a' then 1

                  when 'b' then 2

                  else 3 end;

 

 

윈도우 함수

[SQL][SQLD][SQLP] 윈도우 함수

- Select 사원명, 매출액, Rank() over ( order by 매출액 DESC) as 순위    from 사원 order by 순위

- Select 부서명, 매출액, Rank() over ( partition by 부서번호 order by 매출액 DESC) as 순위    from 사원 order by 순위

- Select 부서명, 매출액, Sum() over ( order by 부서번호 ROWS UNBOUNDED PRECEDING) as 매출액합계    from 사원

- Select 부서명, 매출액, Sum() over ( order by 부서번호 ROWS 1 PRECEDING) as 매출액합계    from 사원

 

ROWS between UNBOUNDED PRECEDING and current row -> 누적 합계

ROWS between 1 preceding and 1 following -> 위아래 합계

* partition by로 나뉘면 해당 파티션의 가장 위/아래를 따진다

* RANK() = 1,1,3,4    DENSE_RANK = 1,1,2,3     ROW_NUMBER = 1,2,3,4

SELECT DEPT_ID, SALARY
FROM ( SELECT ROW_NUMBER()
         OVER(PARTITION BY DEPT_ID
         ORDER BY SALARY DESC) RN,
       DEPT_ID, SALARY
       FROM SQLD_34_27
     )
WHERE RN = 1;

SELECT DEPT_ID, MAX(SALARY) AS SALARY FROM SQLD_34_27
GROUP BY DEPT_ID

부서명으로 파티션해 연봉 내림차순으로 정렬  후 순위 매김 = 부서 명으로 GROUP BY해 MAX값

* 일반적으로 group by 와 위도우 함수 병행 불가, 

-> group by로 묶은 다음 그에대한 sum이나 avg 값에 윈도우 함수는 사용 가능

 

* 집계 윈도우 함수(sum,max,min)을 윈동

SUM(amount) OVER (ORDER BY sale_date) AS running_total

* 집계 윈도우 함수(sum,max,min)을 window 절과 함꼐 사용시 레코드 범위 지정 가능

* 윈도우 함수를 사용해도 결과 건수는 절대로 줄지 않는다

 

 

절차적 SQL

* 트리거는 커밋, 롤백 불가 (중요)

* 둘다 CREATE로 생성하지만 프로시저만 EXECUTE로 실행.

 

계층 SQL

Start With -> 시작점. 루트 = 레벨 1

conenct by -> 조건문.    1의 자식 2,3 -> 2의 자식 4      

order siblings -> 같은 레벨끼리 정렬. A의 자식 B와 C중 3이 크므로 3이 앞에 온다 

만약 A의 자식으로 B와 C가 있고 C의 자식으로 D가 있었다면 A C D B 순으로 정렬된다. (자식이 같이 이동)

 

이후 where절의 조건에 해당 안되는게 나오면 바로 stop

*no cycle = 중복이 생기면 루프 stop

=========================================================================

 

SQL

DML = select insert update delete + MERGE

DDL = create alter drop + RENAME

DCL = grant revoke

TCL = COMMIT ROLLBACK

DROP과 TRUNCATE는 사용한 스토리지를 Realease하지만 delete는 아님.

 

DML

SELECT [컬럼명] FROM [테이블] WHERE [조건]

UPDATE [테이블명] SET [컬럼 = 변경값] WHERE [조건]

INSERT INTO [테이블명] (컬럼1, 컬럼2, 컬럼3) VALUES (값1, 값2, 값3)

DELETE FROM [테이블명] WHERE [조건]

컬럼이 4개인데 3개 INSERT = 가능

단 나머지 하나엔 Null이 들어감 = 해당 컬럼이 not null이면 에러

 

컬럼 지정 없이 INSERT 한다면 무조건 4개 다 넣어야 함

DEFAULT 있어도 무조건 전부!

 

* delete랑 from 사이에 뭐 절대로 없음

* delete [테이블] 가능

* merge: input의 키 값이 이미 존재하면 update, 없으면 insert

 

* 오라클은 DDL 이후 자동 커밋, SQL은 X

 

DML

- alter table A add column COL varchar(10)

- alter table A add ( COL1 varchar(10), COL2 varchar(10) )

- alter table A drop column COL 

- atler table A modify (COL varchar(10))

- alter table A add/drop CONSAINT ~  *제약조건

 

DCL

- with grant option: 해당 권한을 부여할 수 있는 권한

- cascade: grant option으로 추가 부여된 권환도 회수 

 

순수 관계 연산자

- SELECT, PROJECT, JOIN, DIVIDE

 

 

 

 

 

외래키 참조 동작

1) Delege or Modify

- No action 

- Set Null / Dafault

- Cascade : Master 삭제시 Child 같이 삭제

- Restrict : Child에 없을때만 삭제 허용

 

2) Insert

- No action 

- Set Null / Dafault

- Automatic : Master에 PK가 없으면 Master에 PK 자동 생성 후 Child에 입력

- Dependent : Master에 PK가 존재하면 Child에 입력 허용

 

*Set Null과 not Null이 겹치면 실행되지 않음

 

 

트랜잭션 특성

- 원자성

- 고립성 (서로 영향 x)

- 일관성 (실행 전에 잘못이 없다면 실행 후에도 잘못이 없음)

- 지속성 (영구히 저장)

 

트랜잭션 문제점

- Dirty Read : 커밋되기 전 정보를 읽음

- Non Repeatable Read : 1 트랜잭션 내 여러번의 쿼리 사이에 데이터 값이 바뀜

- Phantom Read : 1 트랜잭션 내 여러번의 쿼리 사이에 없던 값이 추가됨

 

 

서브 쿼리

- SELECT, FROM, HAING, ORDER에서 사용 가능 

- select와 order by는 단일/스칼라,

- from과 having은 다중/중첩 서브쿼리

- group by에선 사용 불가

- from에선 인라인/동적 뷰를 사용

 

- 단일 행 연산자(부등호)는 단일 행에만 사용 가능,

- 복수 행 연산자(IN, ALL)은 단일 행, 복수 행 상관없이 사용 가능

 

- 메인 쿼리의 결과가 서브 쿼리로 제공될 수 있고, 반대도 가능하다

- 서브쿼리 내부에서 order by 사용 불가

 

- 상호 연관 (correlated) 서브 쿼리는 서브 쿼리가 메인 쿼리 컬럼을 포함하고 있는 형태 (성능 안좋음)

ex) SELECT a.name FROM a WHERE a.no = (select 1 from b where a.no = b.no)

- 비 연관 서브 쿼리는 메인 쿼리에 값을 제공하기 위해 사용된다 (연관 아님!)

 

 

뷰

- 독립성 : 테이블 구조가 변경되도 뷰를 사용하는 응용 프로그램은 변경 x

- 편리성, 보안성

- 뷰는 물리적으로 존재 x

 

PL/SQL

- 커서 define -> open -> fetch -> close   -> exit

(커서 = 오라클 서버에서 할당한 전용 메모리 영역에 대한 포인터)

 

*변수와 상수 등을 사용하여 일반 SQL 문장을 실행할 때 WHERE 절의 조건 등으로 대입할 수 있다

* 프로시저 내부 절차적 코드는 PL/SQL 엔진이, 일반적인 SQL 문장은 SQL 실행기가 처리한다

* DECLARE, BEGIN~ END의 구조는 필수 // EXCEPTION은 필수 아님

 

=========================================================================

 

* 유니크 키 = 중복 불가, Null 가능

 

* 괄호 > NOT > 비교 연산자 > AND > OR

* 비교 연산자 = 부등호, Between, In, Like, IS NULL

* <>  != ^=

 

* 비절차적 DML = What // 절차적 DML = How

 

* 오라클은 insert('')을 null로 인식한다. sql은 공백인듯?

 

*오라클 1 = 하루   1/24 = 1시간   1/24/60 = 1분    1/24/(60/10) = 1시간을 6등분 = 10분

* to_date(char, 서식)

* to_char(sysdate, 서식)

 

*권한을 한꺼번에 부여 = ROLE

 

* 'abc' == 'abc  '

varchar면 서로 다른 값으로, char면 같은 값으로 취급

* varchar = 가변 길이 문자형, char = 고정 길이 문자형

 

* SELECT * FROM a WHERE name LIKE '%@_%' ESCAPE '@'

-> _가 모든 글자를 나타내는게 아닌 '_' 그 자체를 나타냄

 

( 3 -> (54) -> 2) -> (7 -> 6)  -> 1 -> 0