Database/Mysql

sqld 요약

향식이 2023. 6. 3. 16:50

SQL 명령문

  1. 순서 정렬: From - where - group by - having - select - order by
  2. 종류 정렬:
    1. DML - select, insert ,delete, update
    2. DDL - alter, create, modify, drop
    3. TCL - rollback, commit
    4. DCL - grant, revoke

다음 중 DDL을 찾아라, 다음중 DML을 찾아라 .. 

SELECT

distinct: 집약 기능 -> 원하는 정보를 집약시켜줌

distinct deptno,mgr

group by deptno,mgr

 

AS ★ ★ ★ ★ ★ 

  • SELECT에서는.. 
    • as 생략가능
    • 컬럼명에 띄어쓰기 가능 (ex. "직업 번호")
  • FROM에서는..
    • as 사용불가

concat

  • 인수가 반드시 2개이여야 함
    • +: SQLs
    • ||: oracle (ex. '문자열 ' || '합치기' )

논리연산자

  • and: A and B: 둘 다 가짐
  • or: A or B: 둘 중 하나 가짐 
  • not: A, B: 둘 다 안 가짐

연산순위 

  1. NOT
  2. AND
  3. OR

ex. NOT <조건> AND <조건> NOT <조건> OR <조건> (나에오)

SQL 연산자

  • Between 1 and 2: 1 <= A <= 2
  • A IN (1,2,3): A=1 or A=2 or A=3
  • LIKE ★★★ 
    • _: 임의의 한 글자
    • %: 0개 이상의 글자 
    • escape: 와일드카드 인 _와 %를 문자로 취급 
      • ename like "%@_%" escape "@": escape 문자인 @의 다음 문자를 문자 그대로로 인식
  • Rownum (orcale) ★★★
    • where절까지 만족 시킨 자료에 1부터 순번이 붙음 (즉, 1인 값을 포함)
      • ROWNUM = 2이상의 경우는 데이터 추출 불가
    • SELECT empno, sal FROM emp WHERE rownum <=3 order by sal
      • order by sal 이 가장 마지막에 시행됨!!!!!! 즉, 정렬 전에 rownum에 의한 조건절 시행 
      • 3개의 행만 가지고 정렬됨
  • Top(n) <컬럼명> (sql server)
    • SELECT절에서 ORDER BY 후 상위 n개를 가져옴

NULL ★★★★★ (5문제 나옴)

  • null의 정의
    • 부재, 모르는 값
  • null의 산술연산
    • null + 2 = null (unknown)
    • null + null = null 
  • null의 비교연산
    • where 조건에 결과가 null이면 조건을 알 수 없기 때문에 조건이 맞지 않다고 판단하여 False와 동일
  • 정렬 상 의미
    • oracle: 무한대
    • sql server: 마이너스 무한대
  • NVL(컬럼, 0): 컬럼 값이 NULL인 경우 0으로 치환
  • NVL2(컬럼, '남', '여'): 컬럼 값이 NULL이 아닌 경우 '남', NULL인 경우 '여'
  • ISNULL(컬럼, 0): 컬럼 값이 NULL인 경우 0으로 치환
  • NULLIF(ex1, ex2): ex1 == ex2면 NULL, 다르면 ex1 치환
  • coalesce (ex1, ex2, ex3, ...): null이 아닌 최초의 표현식으로 치환

정렬 (ORDER BY)

  • 정렬의 특징 ★★ (4문제)
    • 가장 마지막에 실행
    • 성능이 느려질 가능성 있음
    • null값과의 관계 
  • 컬럼 번호(컬럼 순서) 정렬
    • 출력되는 컬럼의 수보다 큰 값은 불허 
  • 인수 두개 정렬
    • sal desc, ename asc: sal이 같으면 ename 오름차순
  • select ename order by sal: select문에 없어도 정렬 가능

숫자 함수

  • Round (n, i): n의 소수점 기준 (i+1)번째에서 반올림한 값을 반환
  • ceil(n): n과 같거나 큰 최소 정수 반환 - oracle
  • celing(n): n과 같거나 큰 최소 정수 반환 -SQLs

문자열 함수

  • UPPER, LOWER: 대소문자 변경
  • Lpad, Rpad, LTrim, RTrim, substr, nstr : 실습만 해봐~

날짜 함수

  • To char, To_date: 실습만 해봐~, 형변화 
  • sysdate(orcale), getdate (sql server)
  • 날짜 데이터 + 100: 100일 이후, 즉 default는 day값으로 인식

DECODE/CASE 

  • decode(A, B, '1', null): A가 B일 경우 '1'을, 아닐 경우 null(생략 가능) 반환
  • case
Case
	when than #1
    when than #2
else #가 존재하지 않음
end

-> 1번, 2번 모두 해당이 안 될 경우 null값으로 출력

집계 함수 ★★★ (2~3문제)

  • 값들의 집합을 계산해서 하나의 값을 리턴
  • COUNT(*)을 제외하고, 집계 함수는 NULL값을 무시
  • null과의 관계
    • sum(A) + sum(B) + sum(C) vs sum(A+B+C)

각 컬럼을 sum하고 더하는 것과 모든 컬럼을 더하고 sum하는 것은 다르다

GROUP BY

  • 집약 기능
  • 그룹 수준의 정보를 바꿈
  • Having: Group에 대한 조건식

JOIN

  • natural join
    • 동일한 타입과 이름을 가진 컬럼을 조인 조건으로 이용하는 조인을 간단히 표현하는 방법 
    • 중복된 컬럼이 하나만 출력
    • 중복된 컬럼이 제일 앞에 등장
    • alias 사용 안 됨
  • using
    • 같은 이름을 가진 칼럼들 중에서, using 으로 지정한 컬럼에 대해서만 선택적으로 join 진행
    • 중복된 컬럼이 제일 앞에 등장
    • alias 사용 안 됨
  • left outer join
    • ex. A left outer join B = A.col1 = B.col(+) : left 와 +가 같은 의미!! 그리고 반대임!!!!
  • 조인 순서
    • FROM A,B,C: A와 B를 조인한 후, 조인한 테이블과 C를 조인

서브쿼리 : 들어가는 위치에 따라 다름

  • select: 단일 행 서브쿼리 중 하나인 scalar(한 행, 한 컬럼만을 반환하는 서브쿼리)
  • from: inline view(메인쿼리에 컬럼 사용 가능, 테이블 명이 올 수 있는 곳에서 사용 가능)
  • where: 거의 모든 서브쿼리 다 들어감 = 중첩서브쿼리
  • group by: 서브쿼리 안 들어감!!
  • having: 거의 모든 서브쿼리 다 들어감 = 중첩서브쿼리
  • order by: scalar 서브쿼리만 쓸 수 있음

서브쿼리 다시보기

- in(): 메인 쿼리 한 데이터에 대해 서브쿼리를 다 실행 

- any/some(): 하나라도 만족하면 True

- all(): 모두 만족해야 True

- exist(ex. '1', 'x', 'a'): 존재하면 True, 0 rows면 False

집합연산자 - 잘은 안 나오지만 의미는 다 알고 있자

  • union: 정렬 작업 O, 느림
  • intersect: 정렬 작업 O, 느림
  • minus (except): 정렬 작업 O, 느림
  • union all: 중복 데이터 존재 가능, 정렬 작업 X, 빠름
  • union vs union all 중에 union all 이 더 빠름

DDL (vs TCL과 비교하며 공부하기)

  • Truncate: 구조만 남고 데이터 삭제, rollback, commit 불가능
  • Drop: 구조, 데이터 전부 삭제, rollback, commit 불가능
  • delete: rowe단위로 데이터 삭제, rollback, commit 가능
  • Truncate vs Drop
  • Truncate vs delete

DML

  • insert: TCL commit, rollback과 연관지어 나옴
  • update: TCL commit, rollback과 연관지어 나옴
  • delete: TCL commit, rollback과 연관지어 나옴
  • merge: 신유형 37회~

제약 조건 ★★★

  • PK: unique + Not null, 하나만 존재
  • FK: 참조하는 테이블에서 존재하는 값만 사용 가능
  • UNIQUE: 중복성 배제, 유일한 값으로 존재 
  • NOT NULL: 필수 입력 사항
  • CHECK: 주어진 조건에 해당하는 값만 입력 가능

DCL - 특징 알고 있자

  • GRANT
    • 데이터베이스 사용자에게 객체에 대한 권한을 부여할 때 사용
    • GRANT _ ON _ TO _ (그온투)
  • REVOKE
    • 데이터베이스 사용자에게 객체에 대한 권한을 회수할 때 사용
    • REVOKE _ ON _ TO _(리온투)
  • ROLE
    • 사용자와 권한 사이에 중개 역할 수행(사용자에게 허가 할 수 있는 권한들의 집합)
    • 명령어 아니고 객체(object)의 하나임 특징 다섯개 알아두자
    • 권한 부여와 회수를 쉽게 할 수 있음
    • CREATE ROLE: 권한을 가진 user에 의해 생성
    • 한명의 사용자가 여러 개의 role을 접근 할 수 있고, 여러 사용자에게 같은 role 부여 가능
    • 시스템 권한을 부여하고 취소할 때 동일한 명령을 사용해서 사용자에게 부여 및 취소 가능
    • 사용자는 role에게 role 부여 가능

VIEW - (독편보)

  • 독립성: 기존 테이블의 구조가 변경되면 뷰의 구조도 같이 변경됨
  • 편리성: 테이블을 조작할 필요 없음, 쓸 때 마다 나옴
  • 보안성: 원하는 정보만 가지고 만들 수 있음 (숨길 수 있음)

* 기존 테이블보다는 용량이 적게 필요함

 

그룹함수 ★★★★ - 각 비교

  • ROLLUP
    • 소그룹 간의 소계를 계산 
    • roll up (A,B) != roll up (B,A)
  • CUBE
    • GROUPBY 항목들 간 다차원적인 소계를 계산할 수 있음 (모든 그룹케이스를 계산함)
    • cube (A,B) == cube (B,A)
  • grouping sets
    • 특정 항목에 대한 소계를 계산할 수 있음
    • 특정 항목을 그룹한 다수의 SQL들을 UNION한 것과 같음

기출문제로 다시 확인

* 빵꾸 뚫어서 어떤 걸 써야 하는지.. 행의 수가 적어보이면 roll up, 행의 수가 많아보이면 cube

 

TCL

  • commit
  • rollback
  • auto commit off 와 begin transaction commit 기능 없앤 것

다시 보기

 

윈도우 함수 - 무조건 문제 많이 풀기!!!

  • Rows vs. RANGE: 결과값 차이점, 즉 같은 값이 있는가(범위가 같기 때문에 같은 값이 있을 수 있음)
  • RANK vs. DENSE RANK: RANK는 중복 건너 뜀, DENSE RANK는 건너 뛰지 않음
  • Partition by vs. order by

계층형 질의 ★

  • 계층형 데이터: 동일한 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터(ex. 사원테이블의 직급)
  • prior 자식 데이터 = 부모 데이터 (프자부)
    • '부모데이터 = Prior 자식데이터' 와 같은 의미
    • 부모에서 자식으로 가는 경우 순방향 (부자순)

실습 부분은 이렇게 풀어야 잘 풀 수 있음 다시 보기

절차형 PL/SQL

  • Exception 은 생략 가능
  • procedure, trigger, userdefined function 차이점

데이터 모델링

 

엔터티

  • 업무에 필요하고 유용한 정보를 저장하고 관리하기 위한 집합 (관리하고자 하는 대상)
  • 엔터티 특징
    • 한 개의 엔터티는 두 개 이상의 인스턴스 집합이여야 함
    • 한 개의 엔터티는 두 개 이상의 속성을 가짐
    • 한 개의 속성은 한 개의 속성값을 가짐
    • 관계는 한 개 이상 가짐
  • 엔터티 분류
    • 유형, 개념, 사건 엔터티 (유개사)
    • 기본, 중심, 행동 엔터티 (기중행)

속성

  • 관리하고자 하는 대상인 인스턴스의 특징, 고유한 성질
  • 분류 정의!!!!! (기설파)
    • 기본 속성: 기본속성은 업무로부터 추출한 모든 속성이 해당되며, 가장 일반적이고 많은 속성을 차지함
    • 설계 속성: 업무상 필요한 데이터 이외에 데이터 모델링을 위해, 업무를 규칙화 하기 위해 속성을 새로 만들거나 변형하여 정의함
    • 파생 속성: 다른 속성에 영향을 받아 발생하는 속성으로, 보통 계산된 값들이 이에 해당됨

도메인 ★★★

  • 각 속성이 가질 수 있는 값의 범위
  • 데이터 유형, 크기, 제약 조건
  • check, primary key 등등 값의 범위..

관계

  • IE 표기법
  • Bar ker

식별자

  • 하나의 엔터티에 구성되어 있는 여러 개의 속성 중에서 엔터티를 대표할 수 있는 속성을 의미함
  • 하나의 엔터티는 반드시 하나의 유일한 식별자가 존재해야 함
  • 식별/비식별자 한계 (ERD)
    • -------: 비식별자
    • -------(실선): 식별자
  • 주식별자의 특징 (유최불존) ★ 
    • 유일성: 주식별자에 의해 엔터티 내에 모든 인스턴스들은 유일하고 구분함
    • 최소성: 주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수가 되어야 함
    • 불변성: 주식별자가 한 번 특정 엔터티에 지정되면 그 식별자의 값은 변하지 않아야 함
    • 존재성: 주식별자가 지정되면 반드시 데이터 값이 존재해야 함 (NULL값 안 됨)

식별자 관계, 비식별자 관계

  • 식별자 관계: 자식엔티티의 주식별자로 부모의 주식별자가 상속이 되는 경우
  • 비식별자 관계: 부모엔티티로부터 속성을 받았지만 자식엔티티의 주식별자로 사용하지 않고 일반적인 속성으로만 사용하는 경우

식별자 관계: 1. 강한 관계, 2. 단점- SQL 구문 복잡, PK 속성수 증가

비식별자 관계: 약한 관계, 단점- 조인이 많아 느려짐

3. ERD

ERD 서술 규칙

  • 시선 좌상단 -> 우하단
  • 관계명 반드시 표기 안 해도 됨
  • UML은 객체지향 모델에서만 쓰임

성능 데이터 모델링

  • 성능 향상을 목적으로 설계 단계의 데이터 모델링 때부터 성능 관련된 사항이 데이터 모델링에 반영될 수 있도록 함 
  1. 아키텍쳐를 바꾸는 방법: 테이블을 자르거나 파티셔닝 등등..
  2. SQL의 명령문
    1. 조인 수행 원리 ★★★★
    2. optimizer
    3. 실행 계획

정규화 ★★★★★★★★★

  • 방법
    • 1차 정규화: 테이블의 컬럼이 원자값(하나의 값)을 갖도록 테이블을 분해하는 것
    • 2차 정규화: 1정규화를 진행한 테이블에 대해 완전 함수 종속을 만족하도록 테이블 분해하는 것
    • 3차 정규화: 2정규화를 진행한 테이블에 대해 이행적 종속을 없애도록 테이블을 분해하는 것
    • BCNF 정규화: 3정규화를 진행한 테이블에 대해 모든 결정자가 후보키가 되도록 테이블을 분해하는 것
    • 4차 정규화: 다중 값 종속성을 제거하는 것
    • 5차 정규화: 조인 종속성을 제거하는 것
  • 이상 현상 예시 봐둬!
    • 삽입 이상: 원하지 않는 데이터가 삽입되는 현상
    • 삭제 이상: 하나의 자료를 삭제하고 싶지만, 그 자료가 포함된 튜플 전체가 삭제되는 현상
    • 갱신 이상: 일부만 변경하여 데이터가 불일치하는 현상
  • 성능 : select에서 성능이 안 좋아지고(join 때문에), insert, update에서는 성능이 좋아짐

반정규화 ★★

  • 데이터 무결성이 해침!!!
  • 대량 범위, 범위 처리, 통계처리 (대범한 통조림)
  • 응용 시스템 변경, 클러스터링, 뷰, 인덱스 (응클뷰인) -> 를 해보고 안 되면 반정규화 시행
  • 테이블(병합, 분할) , 속성(파생, 오류, 이력컬럼, PK->일반속성, 중복속성), 관계(중복관계 추가) (태속관) ★★★ 정의, 특징 암기
  • 테이블 반정규화
    • 테이블 병합
      • 1:1 관계 테이블 병합: 1:1 테이블을 병합하여 성능 향상
      • 1:M 관계 테이블 병합: 1:M 테이블을 통합하여 성능 향상
      • 슈퍼/서브 테이블 병합: 슈퍼/서브 관계를 통합하여 성능 향상
    • 테이블 분할
      • 수직 분할: 컬럼 단위의 테이블을 1:1로 분리하여 성능 향상
      • 수평 분할: row 단위로 집중 발생되는 트랜잭션을 분석하여 테이블을 분할
    • 테이블추가
      • 중복테이블 추가: 다른 업무 또는 서버가 틀린경우 동일한 테이블 추가 (원격 조인 제거)
      • 통계테이블 추가: 집계함수 등을 미리 수행하여 계산해둔 테이블을 추가 (쿼리 수행시에 계산하지 않음)
      • 이력테이블 추가: 마스터 테이블에서 자주 조회되는 레코를 중복하여 테이블 추가 (범위처리 최소화)
      • 부분테이블 추가: 자주 이용하는 집중화된 칼럼이 있는경우 디스크 I/O를 줄이기위해 별도의 컬럼만 테이블 추가
  • 속성(컬럼) 반정규화
    • 중복칼럼 추가: 조인시 성능저하를 예방하기위해, 중복되는 컬럼을 위치시킴
    • 파생컬럼 추가: 트랜잭션이 처리되는 시점에 계산에 의해 발생되는 성능저하를 예방하기 위해 미리 계산하여 컬럼에 보관
      • 미리 계산한 컬럼 sum , sumtoValue
    • 이력테이블 컬럼 추가: 대량의 이력데이터 처리시 기능성컬럼(최근값, 시작일자, 종료일자) 를 추가
  • 관계 반정규화
    • 중복관계 추가: 여러경로를 거쳐 조인 할 수 있지만, 성능저하를 예방하기위해 추가적인 관계를 맺는 방법(중복 FK)

* 테이블,컬럼 반정규화는 데이터 무결성에 영향을 끼치지만, 관계의 반정규화는 데이터의 무결성을 깨뜨리지 않는다

대량 데이터에 따른 성능 

  • Row Migration: 수정된 데이터를 해당 데이터 블록에서 저장하지 못 하고 다른 블록의 빈 공간을 찾아 저장 
  • Row Chaining: row 길이가 너무 길어서 하나의 블록에 저장할 수 없어 여러 블록에 걸쳐 저장
  • 해결 방법: partitioning
    • 논리적으로는 하나의 테이블이지만 물리적으로는 여러개의 테이블로 분리하는 것
    • list partitioning: 특정 값을 기준으로 분할
    • range partitioning: 테이블의 컬럼 중에서 값의 범위를 기준으로 여러 개의 파티션으로 데이터를 나누어 저장
      • 가장 많이 쓰이고 관리하기 편함
    • hash partitioning: 내부적으로 해시 함수를 사용해 데이터를 분할
      • 관리하기 어려움

슈퍼/서브타입 - 다시

  • 용량 별로 처리 가능 
    • 큰 경우: 트랜잭션 유형으로 분류 (아래 참고)
    • 작은 경우: one to one, 트랜잭션이 개별로 들어감 
  • 트랜잭션 유형에 따라 
    • 공통/차이
    • 전체 통합

표로 공부하자
그림으로 선택할 수 있어야 함 다시보기

분산 데이터베이스

  • 데이터베이스를 여러 개의 서버로 뜯어둠
  • 데이터 무결성을 해칠 수 있음

조인 수행 원리 - 문제 많이 풀어 4문제 정도 나옴 ★★★★

  • NL join
    • 프로그래밍에서 사용하는 중첩된 반복문과 유사한 방식으로 조인 수행
    • 랜덤 방식으로 데이터 액세스
    • 대용량 sort 작업 시 유리
    • 조인 성공 시 조인 결과를 바로 보여줌
  • sort merge join
    • 조인키 기준으로 정렬, 동기 비동기
    • 스캔 방식으로 진행
    • hash join과 달리 동등 조인 뿐만 아니라 비동등 조인에 대해서도 조인 작업 가능
  • hash join
    • 등가 조인에서만 사용 가능
    • 선행 테이블 작음
    • hash처리 때문에 별도 공간 필요함

인덱스 

  • 원하는 데이터를 쉽게 찾을 수 있또록 돕는 책의 찾아보기와 유사한 개념
  • 언제 사용되는지
    • 부정형, LIKE, 묵시적 형변환 일 때 안 쓰임
  • 인덱스 사용시 성능 느려짐
    • DML(INSERT, UPDATE, DELETE) 성능 나빠짐 

실행 계획 ★★

  • 사용자가 SQL을 실행하여 데이터를 추출하려고 할 때 옵티마이저가 수립하는 작업절차를 뜻함
  • 실행 순서: 들여쓰기 ★★★★★

정규화, pivot, unpivot, merge

반응형
저작자표시 비영리 변경금지