[SQLD] 이것만 외우자 (SQLD정리)

각자 외우는 방식과 공부하는 방식이 다르지만 정리했던 내용을 공유합니다. 

스키마

---

외부	외형적 개념
개념	전체적인 관계를 표현
내부	데이터와 가장 근접

---

 

DML : SELECT, INSERT, UPDATE, DELETE → 데이터를 조작  #셀인업딜

DDL : CREATE, ALTER, DROP, RENAME → 데이터를 정의!  #크알드리

DCL : GRANT, REVOKE → 데이터를 컨트롤 할 수 있는 권한을 주는  #그리

TCL : COMMIT, ROLLBACK, SAVEPOINT → 트랜젝선을 제어하는 명령어  #커롤세

SQL 연산 순서 

---

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

Distinct

---

어떤 컬럼값들의 중복을 제거한 결과를 출력한다.

• select distinct col from table
• select distinct col1, col2 from table → col1과 col2의 값이 모두 같지 않은 경우 만 출력

Alias ⭐

---

select 절에 사용, where절에서는 사용 불가

select col as name from table → ok
select col name from table → ok

select a+b as sum from table where sum > 10 → fail

Concat

---

* 연산자가 2개 만 가능!!

select col1 + col2 + col3 from table; (sql server)
select col || col2 || col3 from table; (oracle)
select concat(col1, col2) from table; * 연산자가 2개 만 가능!!

SQL 연산자

---

A between B and C : B <= A <= C → A가 B와 C사이에 있다. (이상, 이하의 개념을 알아두기! 미만, 초과X)

A in (1, 2, 3) : A=1 or A=2 or A=3

A like ‘_ble*’ : A의 값 중 2, 3, 4번째 값이 ble인 모든 데이터 출력

Escape

---

select * from mail weher 1 = 1
and email like '@_%' escape '@'

rownum, top

---

• Oracle 에선 where절 옆에 rownum
• SQL server의 경우 select옆에 top

null⭐

---

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

• 산술연산에서 null이 들어가면 null이 출력
• 조건절에 null이 들어가면 false를 반환
• 집계함수 (sum, count, min, max…) 에서 null은 대상에서 제외
• 정렬시 Oracle에서는 가장 큰 값이되고, SQL server에서는 가장 작은 값이 된다

Nvl(col, 0) → col이 null이면 0반환, 아니면 col 반환

Nvl2(col, 1, 0) → col이 null이면 0반환, 아니면 1 반환

isnull(col, 0) → col이 null이면 0반환, 아니면 col 반환

Coalesce(col1, col2, col3) → null이 아닌 첫번째 값

정렬⭐

---

• 느려질 수 있다.
• 가장 마지막에 실행
• null이 어디에 오는지? (oracle은 가장 크게 sql server는 가장 작게)

컬럼명으로 정렬, 앞의 기준이 같을 때 그 다음 컬럼으로 정렬

asc 내림차순, desc 오름차순

order by col1, col2 desc -> # col1은 내림차순으로 정렬 후 col2는 오름차순으로 정렬하겠다

order by 2, 1 desc -> # select a, b -> b 내림차순  a 오름차순 정렬

숫자함수

---

• Round(222.45, 1) 소수점 둘째자리에서 반올림하여 첫째자리까지 출력
• Round(225.67, 0) 소수점 첫째자리에서 반올림하여 정수만 출력
  • 파라미터에 -1를 넘길경우 정수 1의 자리에서 반올림
• 버림
• ceil(Oracle) / ceiling (SQL Server) 올림함수, 사용법은 round와 같음
• truncate(Oracle) / Floor (SQL Server) : 버림함수, 사용법은 round와 같음

문자함수 ⭐

---

lower, upper : 소문자로, 대문자로

Trim, ltrim, rtrim : 공백제거

Lpad, Rpad : 특정 자리를 정하고, 왼쪽/오른쪽의 공백을 채워주는 함수

• select lpad(’A’, 5, ‘*’) from dual; → *****A,

Substr : select substr(’korea’, 2, 2) → or이 출력 index가 아님 - substr(문자, 위치, 개수)

• substr(’abcdedf’, -2, 2) → df가 출력

instr : select instr(’corporate floor’, ‘po’) → 4가 출력 - 찾은 자리의 위치값을 출력

날짜함수⭐

---

• To_char : 날짜형 데이터를 문자로 출력
  • select to_char(sysdate, ‘YYYY MM DD’) from dual;
• To_date : 문자형 데이터를 날짜형으로 출력
  • select to_date(’2022-09-22’) from dual;
• sysdate (oracle) / getdate (SQL Server)

조건문⭐

---

• Decode
  • select decode(col1,’A’,1,’B’,2,3) → col1이 A이면 1, B면 2, 아니면 3
• Case
• # 1 case when col = 'A' then 1 when col = 'B' then 2 else 3 end; # 2 case col when 'A' then 1 when 'B' then 2 else 3 end;

집계함수 ⭐

---

• Count, min, max 등
• null은 포함하지 않는다.
• (1, null, 2, 3, null) 기준
  • count : 3
  • sum : 6
  • avg : 2
  • min : 1
  • max : 3

그룹 (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 부서이름, LOCATION_ID 지역, CITY 도시
  FROM DEPARTMENTS
  JOIN LOCATIONS USING(LOCATION_ID);
  

• Left outer join
  • 왼쪽 테이블을 기준으로 전부 출력하고 오른쪽 테이블에서 같은 것 만 붙어서 출력
    • 같은 것 만 출력하고 싶으면 inner join → 이너조인은 같은 것 만 출력

  # SQL Server
  FROM TABLE A LEFT OUTER JOIN TABLE B
  ON A.COL = B.COL
  
  # Oracle
  FROM TABLE A, TABLE B
  WHERE A.COL = B.COL(+)
  

• join 순서
  • from a, b, c → a와 b가 join된 후 c와 join
  • 개수는 -1 하기

서브 쿼리 ⭐

• Select : 스칼라서브쿼리
• from : 인라인뷰(메인쿼리의 컬럼 사용 가능)
• where : 중첩서브쿼리
• group by : 사용불가
• having : 중첩서브쿼리
• order by : 스칼라서브쿼리
• in : 서브쿼리 출력값들 or 조건
• any / some : 서브쿼리 출력값들 중 가장 작거나 큰값과 비교
• all : any /some 과 반대 개념
• exists : 서브쿼리 내 select절엔 뭐가와도 상관 없다. row가 있으면 true, 없으면 false
  • 이거 한번 더 확인하기!!

집합연산자⭐ → ROW 베이스

---

• Union : 정렬 O, 중복제거 O, 느림
• Union ALL: 정렬 X, 중복제거 X, 빠르다
• Intersect : 정렬 O, 교집합, 느리다
• Minus(except) : 정렬 O, 차집합, 느리다

DDL⭐

---

• Truncate : drop & create, 테이블 구조는 남아있으나 데이터가 모두 삭제 됨
• drop : 테이블 자체가 사라짐 ↔ delete : 데이터만 삭제

Rollback, commit → DDL사용 시 AUTO COMMIT 됨 → (Oracle에서 )

DML⭐

---

• insert : 데이터 넣는 명령 → insert into 테이블(col1, col2 …) values (’11’, ‘22’, …)
• update : 특정 행 값을 변경 → update 테이블 set col=’조건’
• delete : 데이터의 특정 행을 삭제 → delete from 테이블 where col = ‘조건’;
• merge : 특정 데이터를 넣을 때 해당 테이블 키값을 기준으로 있으면 update, 없으면 insert

제약조건

---

• PK : not null + unipue
• not null : 해당 컬럼에 null이 올수 없음
• unique : 해당 컬럼에 중복값이 올 수 없음 → null은 올 수 있다

DCL

---

• Grant, Revoke 문법
  • GRANT 시스템권한명 [, 시스템권한명 | 롤명 ] TO 유저명 [, 유저명… | 롤명 … | PUBLIC | [WITH ADMIN OPTION]];
  • REVOKE { 권한명 [, 권한명…] ALL} ON 객체명 FROM {유저명 [,유저명…] | 롤명 | PUBLIC] [CASCADE CONSTRAINTS];
• Role ⇒ 권한의 집합체 같은 느낌

VIEW

---

독립성, 편의성, 보안성

SQL을 저장하는 개념

그룹함수

---

• ROLL UP → GROUP BY에 있는 컬럼들을 오른쪽에서 왼쪽순으로 그룹 생성
• CUBE → 나올 수 있는 모든 경우의 수
• GROUPINGSETS
• GROUPING

TCL

---

COMMIT, ROLLBACK

• Auto commit, begin transaction end → commit 잠시 끄기

윈도우함수

---

rows between and 값이 증가한다.

1. Unbounded preceding : 최종 출력될 값의 맨 처음 row의 값
2. Current row : 현재 row의 값
3. Unbounded following : 최종 출력될 값의 맨 마지막 row의 값

• Rank → 1, 1, 3, 4
• Dense_rank → 1, 1, 2, 3
• 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 관리자 = 사원;

PL/SQL

---

• exception(생략가능)
• procedule 반드시 반환값이 안나옴
• trigger 커밋, 롤백 안됨
  • before, after별로 insert, update, delete가 있음
• function 반드시 반환값이 있음

엔터티

---

• 관리해야 할 대상이 엔터티가 될 수 있다
• 인스턴스 2개 이상
• 업무에서 사용해야 함( 프로세스)
• 관계를 하나 이상 가져야 한다.
• 유형/ 개념 / 사건 엔터티
• 기본 / 중심 / 행위 엔터티

속성 ⭐ #기설파

---

• 기본 속성 / 설계 속성 / 파생 속성

도메인

---

• 데이터유형
• 크기
• 제약조건
  • check, primary key, foreign key, not null, unique…

관계

---

SQLD - IE/Barker 표기법 _ 이미지 출처 : https://lotuus.tistory.com/43

식별자 ⭐ → pk

---

• 유일성 : 유일하게 인스턴스를 구분
• 최소성 : 최소 컬럼으로
• 불변성 : 값이 바뀌지 않아야 함
• 존재성 : not null
  • 위 4개를 만족하면 후보키가 될 수 있으며, 그 중 하나, 대표하는 것이 기본키이다.

식별자 & 비식별자

---

• 식별자
  • 강한관계
  • pk가 많아진다.
  • sql이 복잡해짐
• 비식별자
  • 약한관계
  • sql이 느려짐

ERD

---

• 좌상에서 우하 방향
• 관계명은 반드시 표기하지 않아도 된다.
• UML은 객체지향에서만 쓰인다.

성능 데이터 모델링

---

• 아키텍처모델링
  • 테이블, 파티션, 컬럼 등의 정규화 및 반정규화
• SQL 튜닝
  • Join 수행 원리
• Hash join
  • 등가 join만 사용함
  • 선행테이블이 작다
  • Hash 처리를 위한 별도 공간 필요
• NL join
  • 랜덤엑세스
  • 대용량 sort 작업
  • 선행 테이블이 작을수록 유리
• Sort Merge
  • join키를 기준으로 정렬
  • 등가/비등가 join 가능
• Optimizer
  • CBO : 가장 경제적인 것을 정함
  • RBO : 규칙에 의해서 정함

실행 계획 읽는 법

뎁스가 가장 안에 있는 것 위에서 부터 첫번째

2 → 3 → 1

1 A

2	B
3	C

정규화 ⭐ #원부이결다조

---

제 1 정규화 원자성

제 2 정규화	부분함수종속성 제거
제 3정규화	이행함수종속성 제거
BCNF	결정자 함수이면서 후보키가 아닌 것 제거
제 4정규화	다중값 제거
제 5 정규화	조인에 의해서 종속성이 발생되는 경우 분해

SELECT 시 JOIN이 느려질 수 있다. → 테이블이 늘어나기 때문

INSERT, UPDATE는 빨라질 수 있다. → 테이블를 나눴기 때문

이상현상 #삽삭갱

반정규화 ⭐

---

• 데이터의 무결성을 해칠 수 있음
• 절차 ( 반정규화를 하기 전에 할 일)
  • 대량범위처리 빈도수 조사
  • 범위처리 빈도수
  • 통계처리 여부
• 종류
  • 테이블 병합 1:1/1:M, 슈퍼/서브타입 병합 , 부분테이블 분할, 통계테이블 분할, 중복테이블 분할, 부분테이블 분할, 이력 컬럼 추가 등

데이터에 따른 성능

---

• row migration
  • 행 이전
  1. update로 인해 행 길이가 증가했을 때, 저장공간이 부족한 경우 발생
  2. 원래 정보를 기존 블록에 남겨두고 실제 데이터는 다른 블록에 저장
     1. 검색 시, 원래 블록에서 주소를 먼저 읽고 다른 블록을 찾아야 하므로 성능 감소
  3. 해결책 : PCTFREE 영역을 출분히 할당한다.
     1. PCTFREE가 너무 큰 경우 데이터 저장공간 부족으로 공간 효율성 감소
• Chaing
  • 행 연결
  1. 데이터가 커서 여러 블록에 나누어 저장하는 방식
     1. 2개 이상의 데이터 블록을 검색하므로 성능 감소
  2. Intial Row Piece(행조각)와 Row Pointer로 블록 내에 저장
  3. 해결책 : DB_BLOCK_SIZE를 크게하여 최소화 가능
     1. 사이즈 변경이 어렵고, 무조건 크게 할 수 없음
• List partition
  • 특정값을 기준으로
  • 관리 쉬움
  • 데이터가 치우칠 수 있음
• Range partition
  • 특정값 범위
  • 관리 쉬움
  • 가장 많이 씀
• Hash partition
  • 관리 어려움

슈퍼/서브타입

---

• 1:1 타입 (one to one type)
• 슈퍼 + 서브타입 (plus type)
• all in one 타입 (single type)
• 특징
  • 트랜잭션은 항상 일괄로 처리하는데 테이블은 개별로 유지되어 union 연산에 의해 성능이 저하될 수 있다.
  • 트랜잭션은 항상 서브타입 개별로 처리하는데 테이블은 하나로 통합되어 있어 불필요하게 많은 양의 데이터 때문에 성능이 저하된다.
  • 트랜잭션은 항상 슈퍼 + 서브타입을 공통으로 처리하는데 개별로 유지되어 있거나 하나의 테이블로 집약되어있어 성능이 저하된다.

분산데이터베이스

---

분할 투명성 사용자가 입력한 전역 질의를 여러개의 단편 질의로 변환해 주기 때문에 사용자는 전역 스키마가 어떻게 분할되어있는지 알 필요가 없음

위치 투명성	어떤 작업을 수행하기 위해 분산 데이터베이스상에 존재하는 어떠한 데이터의 물리적인 위치도 알 필요가 없음
지역사상 투명성	지역 DBMS와 물리적 DB사이의 Mapping 보장, 각 지역 시스템 이름과 무관한 이름 사용가능
종목 투명성	어떤 데이터가 중복되었는지, 또는 어디에 중복 데이터를 보관하고 있는지 사용자가 알 필요없음
장애 투명성	분산되어있는 각 컴퓨터 시스템이나 네트워크 장애가 발생하더라도 데이터의 무결성이 보장됨
병행 투명성	다수 Transaction 동시 수행 시 결과의 일관성 유지, 잠금(Locking)과 타임 스탬프(Timestamp)의 두가지 방법을 주로 사용

단점 : 데이터무결성을 해칠 수 있다

인덱스 Index

---

• 사용 못하는 경우
  • 부정형 → where a not … 등등
  • like
  • 형변환(묵시적) → 문자가 들어가 있는데 숫자로 검색 시
• 악영향
  • DML 사용시 성능이 저하됨

 

---

밑에 있는 youtube 영상을 보며 따라 작성하여 아마 교재와 내용이 같을겁니다.

---

출처 : https://youtu.be/BQxAxMnHByU

 

교재 다운로드 : https://cafe.naver.com/joymarketing/1450