SQLD 학습 노트

https://wikidocs.net/170663

반정규화
수직수평분할 조인때매 중복칼럼추가

​

집합연산자
위에서 아래서

연산자	표준 SQL 지원	Oracle 지원	설명
UNION	O	O	중복 제거 합집합,정렬도됨
UNION ALL	O	O	중복 포함 합집합, 정렬도안됨
INTERSECT	O	O	교집합
EXCEPT	O	X	차집합 (표준), Oracle에서는 MINUS 사용
MINUS	X	O	차집합 (Oracle 전용)

계층형쿼리
where이실행안하고전개부터하고필터링함
prior : 뒤에있는 것이 기준이됨, 기준은즉 부모가된다는거임
prior가 자식쪽에 붙어있으면 순방향임
상위메뉴가 이전의 메뉴id와 같은걸 connect한다
nocycle : 순환발생시 CONNECT_BY_ISCYCLE,CONNECT_BY_ISLEAF로 확인가능 둘다 맞으면 1아니면 0
전개쭉하다가 알게없으면 order sibings by 메뉴id가적용됨
connectby는 전개조건을 알려주는것

• 부모 = PRIOR 자식 이면 순방향
• PRIOR 자식 = 부모 이면 순방향
• 자식 = PRIOR 부모 이면 역방향
• PRIOR 부모 = 자식 이면 역방향

이름	설명	예시 사용법
LEVEL	현재 행의 계층 깊이(레벨)를 반환. 루트 노드는 1, 하위로 갈수록 1씩 증가.	SELECT LEVEL, ... FROM ... CONNECT BY ...
CONNECT_BY_ROOT	현재 행의 최상위(루트) 부모 행의 컬럼 값을 반환.	SELECT CONNECT_BY_ROOT 컬럼명 FROM ... CONNECT BY ...
CONNECT_BY_ISLEAF	현재 행이 리프 노드(자식이 없는 최하위 노드)면 1, 아니면 0 반환.	SELECT CONNECT_BY_ISLEAF FROM ... CONNECT BY ...
SYS_CONNECT_BY_PATH	루트부터 현재 행까지의 경로를 지정한 구분자로 연결하여 반환.	SELECT SYS_CONNECT_BY_PATH(컬럼명, '/') FROM ... CONNECT BY ...
CONNECT_BY_ISCYCLE	현재 행이 순환 구조(루프)에 속하면 1, 아니면 0 반환. NOCYCLE 옵션과 함께 사용.	SELECT CONNECT_BY_ISCYCLE FROM ... CONNECT BY NOCYCLE ...

순서

내부적으로 and 외부적으로 or

이런값이 있는가,패 턴 : LIKE사용 or 정규식

• DDL : 테이블 정의(create alter drop truncate)
• DML : 데이터조작 (select insert등)
• DCL : 권환관련(grant revoke)
• tcl : 트랜젝션 제어언어( commit rollback savepoint)

distinct는 중복값 제거할때 사용

count(*) == count(1) == count(2)

DECODE(컬럼, 조건1, 결과1, 조건2, 결과2, ..., 기본값)
근데 애는 비교할ㄸ ㅐnull도 같은지 비교가됨

case when : if-else, switch 같은것. 예외는 null로 치환됨

searched case가 다른연산자 사용가능하기에 확장성이높음

자료형(n) : N자리수까지만 됨
varchar : 숫자넣어도 string으로 범위가 드감 ㅍ

round(10.24231,1) : 10.2

EXISTS 구문
보통 SELECT 1 SELECT * 사용 (1 존재여부확인)
SELECT 컬럼1, 컬럼2, ...
FROM 테이블A
WHERE EXISTS (
SELECT 1
FROM 테이블B
WHERE 테이블B.조건 = 테이블A.조건
AND (추가조건이있다면 추가조건)
);

부분 종속 :복합키일때 각 키마다 종속된 값들이 따로잇음

이행종속 : X > Y > Z 종속 보통 X가 키 Y,Z는 일반속성

일반속성끼리 종속

이상 : 정규화 수행하지않아서 생기는 현상
삽입,갱신삭제 이상이잇음

식별자

주,보조식별자 & 본직,인조 식별자
https://m.blog.naver.com/ilifo_book/223448187508?recommendTrackingCode=2

대표성 여부

구분	설명	예시
주식별자	엔터티를 대표하는 식별자. 타 엔터티와 참조 관계 연결에 사용	사원번호, 주문번호
보조식별자	엔터티 내에서 인스턴스를 구분할 수 있으나, 대표성이 없어 참조 관계에 주로 사용되지 않음	주민등록번호(사원 엔터티에서)

스스로 생성 여부

구분	설명	예시
내부식별자	엔터티 내부에서 스스로 만들어지는 식별자	사원번호, 주문번호
외부식별자	타 엔터티와의 관계를 통해 타 엔터티로부터 받아오는 식별자	사원 엔터티의 부서번호(부서 엔터티 참조)

속성의 수

구분	설명	예시
단일식별자	하나의 속성으로 구성된 식별자	사원번호, 주문번호
복합식별자	둘 이상의 속성으로 구성된 식별자	주문상세 엔터티의 주문번호+상세순번

대체 여부

구분	설명	예시
본질식별자	업무에 의해 자연스럽게 만들어지는 식별자	고객번호, 주문번호
인조식별자	원조식별자가 복잡해 인위적으로 만든 식별자(일련번호 등)	주문번호(업무적으로 만들지 않고 일련번호로 대체)

식별자 관계 여부

구분	설명	예시
식별자 관계	부모 엔터티의 주식별자를 자식 엔터티의 주식별자로 상속받는 경우	주문상세 엔터티가 주문 엔터티의 주문번호를 주식별자로 상속받는 경우
비식별자 관계	부모 엔터티의 주식별자를 자식 엔터티의 일반 속성으로만 사용하는 경우	사원 엔터티의 부서번호(부서 엔터티 참조, 주식별자가 아님)

IE 머시기 표현법 다시보기
https://velog.io/@kw78999/DB-%EB%B0%94%EC%BB%A4%EC%99%80-IE-%ED%91%9C%EA%B8%B0%EB%B2%95
https://dbguide.tistory.com/entry/%EB%AA%A8%EB%8D%B8%EB%A7%81-IE-%ED%91%9C%EA%B8%B0%EB%B2%95

• 비식별자에 대해 맞는것
  • 약한 연결 관계를 표현하고 있다.
  • 엔터티 간의 관계를 점선으로 표현한다.
  • 부모 쪽의 관계 참여가 선택적 관계이다.

null

• NULL과의 연산
  단일행 연산 : sum(a+b+c)
  ㄴ Null 값과의 연산 수행 결과를 Null
  ex) 100 + Null = Null
  다중행 연산 : sum(a)+sum(b)+sum(c)
  ㄴ Null 값인 행을 제외하고 연산 수행
  ex) 10, 20, Null, 30의 평균 = (10 + 20 + 30) / 3 = 20
• ==null 은 비교(= <= 같은..)가 안됨== is로 확인가능
• count 값은 null값이 제외된다
  • 이때 count가 3이됨

group by

• GROUP BY 절에 명시된 컬럼
  → SELECT 절에서 직접 사용 가능
• 집계 함수(COUNT, SUM, AVG, MIN, MAX 등)로 계산된 컬럼
  → SELECT 절에서 사용 가능
• GROUP BY에 없는 일반 컬럼
  → SELECT 절에서 사용 불가(오류 발생)

집계함수

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

권한부여 ,회수

옵션	설명
WITH GRANT OPTION	오브젝트 권한을 받은 사용자가 다른 사용자에게 권한을 줄 수 있음
WITH ADMIN OPTION	시스템 권한을 받은 사용자가 다른 사용자에게 권한을 줄 수 있음
PUBLIC	모든 사용자에게 권한을 부여할 때 사용
부여
GRANT CREATE SESSION TO user1;
GRANT CREATE TABLE TO user1 WITH ADMIN OPTION;
회수
REVOKE SELECT ON employees FROM user1;
REVOKE CREATE SESSION FROM user1;

• 시스템 권한 (System Privileges)
  • 데이터베이스 관리 및 운영에 필요한 권한
  • 예: CREATE USER, DROP USER, ALTER SYSTEM, CREATE TABLE, CREATE SESSION 등
• 오브젝트 권한 (Object Privileges)
  • 특정 데이터베이스 객체(테이블, 뷰, 시퀀스 등)에 대한 권한
  • 예: SELECT, INSERT, UPDATE, DELETE, EXECUTE 등

윈도우함수 정리

함수	설명	예시 결과 (동점 존재 시)
RANK	동일 값은 같은 순위로, 다음 값은 중복 개수만큼 건너뛰어 순위 부여	1, 2, 2, 4, 5, 5, 7
DENSE_RANK	동일 값은 같은 순위로, 다음 값은 순차적으로 순위 부여	1, 2, 2, 3, 4, 4, 5
ROW_NUMBER	동일 값도 고유한 순위 부여(중복 없음, 정렬 기준에 따라 순서 결정)	1, 2, 3, 4, 5, 6, 7
NTILE	전체 데이터를 지정한 그룹(분위) 수로 균등 분할하여 그룹 번호 부여	1, 1, 2, 2, 3, 3, 4 (4개 그룹 예시
over() : 어떤기준으로 동작할건지
UNBOUNDED PRECEDING이 두번 사용할 . 수없다.

윈도우함수(컬럼) OVER(
    [PARTITION BY 컬럼]
    [ORDER BY 컬럼]
    [ROWS/RANGE BETWEEN 범위]
)

• PARTITION : 그룹별로 순위가 부여된돠

  • 각각의 파티션영역안에서만 밖을침범하지않음

• ROWS/RANGE BETWEEN

  • rows : 물리정 행수를 기준
    • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
      → 현재 행의 바로 앞 1행, 현재 행, 현재 행의 바로 뒤 1행(총 3행) 범위
  • unbounded를 많이 사용함
  • UNBOUNDED PRECEDING : 범위 시작(첫행)
  • UNBOUNDED FOLLOWING : 범위 끝(막행3 )
  • range 끝을 안맺어도됨. 자동으로. end current row가들어감

• top

  • with ties를 쓸수 있음(동률)
    • 이때 orderby가 필수
    • 4개뽑아라해도 5개나올수도잇음(동일한값잇으면)
  • rownum < n 으로 대체가능

join

조인 종류	특징/설명
INNER JOIN	두 테이블에 모두 있는 데이터만 결과로 나옴
LEFT OUTER JOIN	왼쪽 테이블은 모두, 오른쪽은 조건 맞으면 붙음
RIGHT OUTER JOIN	오른쪽 테이블은 모두, 왼쪽은 조건 맞으면 붙음
FULL OUTER JOIN	양쪽 테이블 모두 다 나옴(조건 안 맞으면 NULL)
CROSS JOIN	모든 행을 곱해서 결과로 내보냄
SELF JOIN	같은 테이블을 두 번 조인
NATURAL JOIN	같은 이름의 컬럼으로 자동 조인
crossjoin은 on안씀

오라클

outer join하는곳에 +를 둠
표준 <-> 오라클
on <-> where (조인기준)
where <-> where(일반조건)

+없는방향 join
표준
SELECT * FROM A LEFT OUTER JOIN B ON A.a = B.a;

오라클
SELECT *
FROM A, B
WHERE A.a = B.a(+);

키 종류

키 종류	유일성	최소성	특징/설명
슈퍼키	O	X	불필요한 속성 포함 가능
후보키	O	O	불필요한 속성 없음, 후보가 됨
기본키	O	O	후보키 중 하나, 대표 키
복합키	O	O	2개 이상 컬럼 조합, 후보키가 될 수 있음
대체키	O	O	후보키 중 기본키 아닌 것

파티셔닝

종류	설명	예시/특징
수평 파티셔닝	행(row) 단위로 데이터를 분할. 각 파티션은 같은 스키마를 가짐.	고객ID, 날짜 등 기준으로 분할36
수직 파티셔닝	컬럼(column) 단위로 데이터를 분할. 자주 사용하는 컬럼을 따로 분리해 성능 향상.	보안/성능 목적, 스키마 분리36

수평파티셔닝

기법	설명	예시/특징
범위 기반(Range)	특정 범위(예: 날짜, 숫자)를 기준으로 파티션 분할.	2023년, 2024년 등 연도별 분할4
리스트 기반(List)	특정 값(예: 국가, 지역)을 기준으로 파티션 분할.	국가별, 지역별 분할1
해시 기반(Hash)	해시 함수를 적용해 파티션 키의 값에 따라 무작위로 분할. 데이터가 골고루 분산됨.	고객ID 해시값으로 4개 파티션 분할
리스트는 관리자가 직접지정한걸로 주기에 따라 쉽게 삭제가 불가

엔티티 분류

모델링

개념적 >논리적 > 물리적

• 개념적 : 엔티티 , 속성 도출, 전사적차원
  • 높은추상화래밸
• 논리적 : 정규화 ,식별자도출관계저으이
  • 재사용성 높음
  • 특정 db에 종속적
• 물리적 : db구현가능하게

스키마

• 외부 : 다중사용자뷰
• 개념 : 통합뷰, 설계자관점
  • 논리적 데이터 독립성보장
• 내부 : 개발자관점 , 디스크 저장구조
  • 물리적동립성 보장

피벗

https://m.blog.naver.com/PostView.naver?blogId=ilifo_book&logNo=223461068051&navType=by

프로시저

sql(비절차적임) 을 절차적으로 사용하고 싶다. ==>> 프로시저

Declare : 변수선언
Cursor : 메ㅔ모리 저장
Fetch : 한줄씩 꺼내쓰겟다
Close: 끝나고 해제 시켜줘야함