💡 1과목 공부 꼭 해야하는 것

• 일반적으로 1과목 내용은 그냥 다 알아야함, 그럼에도 꼭 공부해야하는 것을 꼽자면 밑의 세가지가 있음

1. 엔티티
2. 속성 - 종류관련
3. 식별자 - 종류관련

💡 2과목 공부 꼭 해야하는 것 - SQL 문은 대부분 Oracle 기준으로 씀(Oracle 이나 SQL Server 기준으로 쿼리 알면 됨)

• DML, DDL, DCL, TCL
• ROLLBACK, SAVEPOINT - 문제 및 예제를 보기
• null 처리관련 연산자, 함수
  1. 연산자
     • IN , NOTIN 의 null 처리관련
  2. 함수
     • null처리함수(NVL, ISNULL, NULLIF, COALESCE)
     • 집계함수(SUM,AVG,COUNT 등)이 null에 대해 어떻게 처리하는지
• 윈도우함수(Window Function) - 윈도우함수로 될 수 있는 것 및 BETWEEN 절에 들어갈 수 있는 것 등등 전반적으로 공부
• 그룹함수 (ROLLUP, CUBE, GROUPING SETS)
• 조건관련 (CASE, DECODE)
• JOIN - 조인종류(natural join, inner join, cross join, ...)에 따른 특징
• 서브쿼리 - 서브쿼리 관련해서는 SQL 오류 관련으로 많이 나옴 (해당 서브쿼리를 썼을때 오류가 나지 않는지)
• 계층형 쿼리 - 무조건 많은 문제 및 예시를 보는게 답
• ORDER BY - order by 절에 올 수 있는 것들이 무엇이 있는지
• TOP(), ROWNUM
• Nested Loop Join, Sort Merge Join, Hash Join - 차이 및 개념정도

1과목. 데이터 모델의 이해

1-1. 데이터 모델링 이해

모델링

모델링 : 시스템 구현+ 업무,업무형상화 목적

• 특징
  • 추상화, 단순화, 정확화(명확화)
• 주의
  • 중복
    • 여러 장소에 같은 정보 저장 X
  • 비유연성
    • 데이터 정의와 사용프로세스를 분리해야함
    • 유지보수성관련
  • 비일관성
    • 데이터간에 상호 연관 관계 설명 명확히 해야함
• 데이터 모델링의 3가지 요소
  • Thing(저장이 되어 관리가 필요한 어떤 것), Attributes(속성), Relationship(관계)
• DB 스키마 구조
  • 개념적 데이터 모델링 : 통합 관점, 포괄적
  • 논리적 데이터 모델링 : 관계 명확히
    • 논리 모델링의 외래키는 반드시 구현되지는 않음
  • 물리적 데이터 모델링 : 이식, 성능, 저장등 고려 실제 물리적 고민

엔터티

• 이름짓기

  • 현업에서 사용하는 용어 사용
  • 약어 X
  • 단수 명사, 고유한 이름 사용

• 특징

  • 2개 이상의 인스턴스 집합

    인스턴스 : 엔터티 구현된거? (환자1,환자2)

  • 2개 이상의 속성 집합

  • 하나의 속성은 하나의 속성값만 가짐

• 종류

  • 유무형에 따른 분류
    • 유형 엔터티 : 물리적 엔터티 (사원, 물품, 강사)
    • 개념 엔터티 : 개념적 정보, 물리적인 형태가 아님 (보험상품, 조직)
    • 사건 엔터티 : 업무 수행시 발생, 통계자료 이용 (주문, 청구, 미납)
  • 발생시점에 따른 분류
    • 기본 엔터티 : 부모가 되는 엔터티, 주식별자를 상속받지 않고, 자신의 고유한 식별자를 가짐
      • 행위의 주체이자 제일 처음 고려대상
      • 예시 : 사원, 자재, 부서, 고객, 상품
    • 중심 엔터티 : 기본엔터티로부터 발생되고, 업무에 있어서 중심적인 역할함
      • 데이터 양 많음, 많은 행위 엔터티 발생시킴
      • 예시 : 매출, 계약, 주문
    • 행위 엔터티 : 2개 이상의 부모 엔터티로부터 발생됨, 내용자주 바뀜
      • 예시 : 장비점검목록(점검번호, 날짜), 주문번호, 사원변경이력

관계 (엔터티간의)

• 사용하는 개념
  • 관계명
  • 관계차수 (1:1, 1:M)
  • 관계 선택사양 : 필수관계, 선택관계
• 관계간에 중요한 것은 명사가 아닌 ‘동사’
• 데이터 모델링 vs. UML
  • 데이터 모델링 : 존재간 관계, 행위간 관계를 구분 안함
    • 존재에 의힌 관계 : 소속된다
    • 행위에 의한 관계 : 주문한다
  • UML : 연관관계(실선), 의존관계(점선) 구분함
    • 연관관계 : 존재적 관계
    • 의존관계 : 상대방 행위에 의한 관계

속성

• 정의
  • 의미상 더이상 분리되지 않는 최소의 데이터 단위
• ⭐ 도메인 : 속성이 가질 수 있는 값 범위
• 특징
  • 하나의 속성값만 가져야함
  • 속성의 명칭 : 전체 데이터 모델에서 유일성을 확보해야함 → 반정규화,통합등 작업시 혼란 방지할 수 있도록 ‘복합 명사' 사용해서 엔터티마다 비슷한 값을 가지더라도 명칭을 다 다르게 해야함
    • 1 엔터티의 name, 2 엔터티의 name : X (1_name, 2_name O)
• 종류
  • 기본속성
  • 파생속성 : 빠른성능 위함. 본래 속성값 계산해서 저장가능하게 만든 속성
  • 설계속성 : 업무를 규칙화하기 위해 새로만들거나 변형하거나 정의하는 속성 (ex : 일련번호)

식별자

• 종류
  • 대표성
    • 주식별자
      • 유일성, 최소성, 불변성, 존재성
    • 보조식별자
  • 엔터티 내에서 스스로 생겼는가
    • 내부식별자
    • 외부식별자 : 타 엔터티의 관계로 인해 타 엔터티에서 받아온 속성
  • 단일 속성으로 식별이 되는가
    • 단일식별자
    • 복합식별자 : 2개 이상의 속성으로 식별자 구성
  • 업무적으로 의미있는 식별자 대체한 식별자인가
    • 본질식별자
    • 인조식별자 : 본래 업무적으로 의미가 있던 식별자 속성 대체한 일련번호처럼 인위적으로 새롭게 만든 식별자
      • 시스템적으로 부여된 식별자
• 식별자관계, 비식별자 관계
  • 식별자 관계 : 강한 연결관계 표현, 엔티티간 실선표현, 자식의 주식별자에 반드시 포함(종속관계있음)
  • 비식별자 관계 : 약한 연결관계 표현, 엔티티간 점선표현, 자식의 일반 속성에 포함됨
    • 복합식별자가 많은 경우 부모와 자식간의 조인시에 sql 문이 복잡해질 수 있음. 해당 부분은 비식별자로 할건지 고민 중 가장 최하위 우선순위를 가짐(가장 마지막에 고려한다는 것, 식별자관계여야하는데 단순 sql 문이 복잡해진다고 비식별자로 하면 안 됨)

1-2. 데이터 모델과 성능

정규화

1. 1차 정규형
   • 하나의 속성은 하나의 속성값만 가져야 함
     • 같은 속성을 가지는 속성값들은 동일 형식이어야 함(정수면 정수만)
     • 속성은 유일한 이름을 가져야함
2. 2차 정규형
   • 식별자가 아닌 속성들은 식별자에 완전 종속되어야 함
     • = PK 아닌 속성들은 PK에 종속되는 속성이어야 함
3. 3차 정규형
   • 식별자가 아닌 속성끼리는 종속관계가 있으면 안됨

반정규화

• 반정규화 대상
  • 조인으로 인한 성능저하
  • 디스크 I/O량이 많아 성능저하
  • 칼럼 계산량이 많아 성능 저하 → 계산된 칼럼을 새로 추가
  • 통계성 정보가 따로 필요할 때, 통계 테이블 만드는 것을 고려함
• 반정규화 대상이 아닌 것
  • Sorting, Order by가 많은 경우
• 반정규화 전에 고려해야하는 것
  • VIEW : 조인이 너무 많이 걸려있는 경우 조인을 한 View를 만들어서 활용
  • 인덱스 조정 / 클러스터링 적용 : 대량의 데이터처리에 의해 성능 저하되는 경우

대량 데이터 성능

• 많은 컬럼으로 인한 성능 저하
  • Row Chaining (신규 데이터 입력시 발생)
    • row 길이가 길어서 하나의 블록에 저장되지 못하고, 두 개 이상의 블록에 하나의 row가 저장되는 현상
    • chaining ? c++에서 포인터로 데이터 가리키듯이 너무 긴 수정데이터를 포인터 형식으로 가리켜서 그런 이름인 듯?
  • Row Migration (데이터 수정시 발생)
    • 데이터 블록에서 수정할 때, 수정된 데이터를 해당 블록에서 찾지 못하고 다른 블록의 빈 공간에서 찾아서 저장하는 방식

분산 DB

• 정의
  • DB가 물리적으로 여러곳에 분산되어 있음 하지만 하나의 논리적으로 동일한 가상 시스템
• 투명성
  • 위치 투명성 : 저장 장소 명시가 불필요
  • 지역 사상 투명성 : 지역 DBMS - 물리 DB 간의 맵핑 보장, 각 지역 시스템과 무관한 이름 사용 가능
  • 중복 투명성 : DB 객체가 여러 장소에 중복되어 있는지 알 필요 없는 성질
  • 장애 투명성 : 구성요소의 장애에 무관한 트랜잭션 원자성 유지
  • 병행 투명성 : 다수 트랜잭션 동시 수행시에 결과의 일관성 유지

2. SQL 기본 및 활용

RDBMS

• DML(데이터 조작어)

  • SELECT

  • INSERT, DELETE, UPDATE

    INSERT INTO 테이블명 (칼럼리스트) VALUES (칼럼 값);
    UPDATE 테이블명 SET 수정되어야할 칼럼명 = 값; DELETE FROM 테이블명;
    SELECT 칼럼리스트 FROM 테이블명;

• DDL(데이터 정의어)

  • DROP, CREATE, ALTER, RENAME, TRUNCATE

    ALTER TABLE DEPT ALTER COLUMN VARCHAR(30) NOT NULL;
    
    ALTER TABLE 테이블명 ADD 칼럼명 데이터 유형; - 여러개 컬럼 동시수정 불가(SQL Server)
    ALTER TABLE 테이블명 DROP COLUMN 칼럼명;
    ALTER TABLE 테이블명 MODIFY (칼럼명 데이터유형 DEFAULT식 NOT NULL); - 칼럼 데이터 유형, 조건 등 변경 / Oracle
    ALTER TABLE 테이블명 ALTER (칼럼명 데이터유형 DEFAULT식 NOT NULL); / SQL Server
    ALTER TABLE 테이블명 RENAME COLUMN 변경전칼 럼명 TO 뉴칼럼명; Ora
    ALTER TABLE 테이블명 DROP CONSTRAINT 조건명; 제약조건 삭제
    ALTER TABLE 테이블명 ADD CONSTRAINT 조건명 조건 (칼럼명); 조건 추가
    RENAME 변경전테이블명 TO 변경후테이블명; Ora 
    sp_rename ‘db0.TEAM’,‘TEAM_BACKUP’; SQL
    sp_rename 변경전칼럼명, 뉴칼럼명, ‘COLUMN’; SQ
    DROP TABLE 테이블명 [CASCADE CONSTRAINT] CASCADE CONSTRAINT : 참조되는 제약조건 삭제
    TRUNCATE TABLE 테이블명; 행 제거, 저장공간 재 사용

• DCL(데이터 제의어) - 유저생성 및 권한 제어

  • GRANT - 권한 부여
  • REVOKE - 권한 회수

• TCL(트랜잭션 제의어)

  • COMMIT, ROLLBACK, SAVEPOINT
  • DCL 로 표현하기도 함

Transaction (TCL관련)

• Transaction 속성

  • 원자성 : 트랜잭션에 정의된 속성은 모두 성공이던지 모두 실패던지 둘 중에 하나여야함
  • 일관성 : 트랜잭션이 실행되기 전 결과가 잘못되지 않았다면, 후에도 잘못되면 안됨
  • 지속성 : 트랜잭션이 성공적이면 그 결과는 영구적으로 저장됨
  • 고립성 : 다른 트랜잭션에 영향받아 현재 트랜잭션의 결과가 이상하게 되면 안됨

• Auto Commit

  • DDL이 일어나면 암묵적으로 commit 됨

      - 01 : CREATE TABLE TAB1 (N1 NUMBER, N2 NUMBER);
      - 02 : INSERT INTO TAB1 VALUES (1,1);
      - 03 : INSERT INTO TAB1 VALUES (2,2);
      - 04 : CREATE TABLE TAB2 (V1 VARCHAR2(10), V2 VARCHAR2(10));
      - 05 : ROLLBACK;
      - 06 : SELECT COUNT(*) FROM TAB1; // 2

권한 (DCL관련)

• ROLE : 권한 부여 쉽게 관리 가능한 권한 집합

• 권한 부여

  • DBA 권한, 시스템 권한 (SYS, SYSTEM) 가진 사람들이 권한 부여 가능

• 참고

  https://devdhjo.github.io/sqld/2019/11/28/database-sqld-024.html

SQL 실행순서

SELECT DEPTNO, COUNT(EMPNO) 
FROM SCOTT.EMP
WHERE SAL >= 500
GROUP BY DEPTNO
HAVING COUNT(EMPNO) > 2 
ORDER BY DEPTNO;

• FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

• 최대한 앞에서 거를 수 있으면 거르는게 좋음

  • WHERE에서 처리할 수 있는 일을 HAVING에서 처리하지 않기!
  • 참고 : https://myjamong.tistory.com/172

• 각 절 상세 설명

  1. FROM과 JOIN

     JOIN이 먼저 실행되어 데이터가 SET으로 모아지게 됌.여기에는 서브쿼리도 함께 포함되어 임시적인 테이블을 만들 수 있게 도와줌.

  2. WHERE

     데이터셋을 형성하게 되면 WHERE의 조건이 개별 행에 적용이 된다.

     이 WHERE절의 제약 조건은 FROM절로 가져온 테이블에 적용이 될 수 있다.

  3. GROUP BY

     WHERE의 조건 적용 후 나머지 행은 GROUP BY절에 지정된 열의 공통 값을 기준으로 그룹화된다.

     쿼리에 집계 기능이 있는 경우에만 이 기능을 사용해야 한다.

  4. HAVING

     GROUP BY 절이 쿼리에 있을 경우 HAVING 절의 제약조건이 그룹화된 행에 적용됌.

  5. SELECT

     SELECT에 표현된 식이 마지막으로 적용됌.

  6. DISTINCT

     표현된 행에서 중복된 행은 삭제됌

  7. ORDER BY

     지정된 데이터를 기준으로 오름차순, 내림차순을 지정

  8. LIMIT / OFFSET

     LIMIT와 OFFSET에서 벗어나는 행들이 제외되어서 출력됨

데이터 유형

• 문자
  • CHAR
    • 고정된 길이
    • 비교 : 길이가 다른 경우 스페이스(공백) 하나 추가해서 비교
      • 예를들어, CHAR(8)이고 ‘AA’ 가 저장된 경우, ‘AA’에 공백 6자리 붙인 ‘AA ‘로 타 문자와 비교
  • VARCHAR
    • 가변길이
    • 비교 : 공백또한 한 문자로 취급해서 비교, 길이 짧은거 끝나면 그대로 비교 종료함

INSERT

• Identity
  • SQL SERVER : IDENTITY [ ( seed , increment ) ]
    • seed : 시작
    • increment : 증가값
  • insert 문에 indentity인 컬럼에 값 넣으면 ERROR
• check
  • check 조건 만족못하면 ERROR, NULL은 무시

JOIN

• JOIN 종류

  • Natural Join

    • 두 테이블간 동일한 이름과 형식의 컬럼이 하나 있는 경우 사용가능

    • 식별자 가질 수 없음 ( EMP.DEPTNO 같은 owner 명 기재 불가)

        SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME FROM EMP NATURAL JOIN DEPT; // ERROR

  • Using

    • 두 테이블간 동일한 이름과 형식의 컬럼이 둘 이상인 경우 natural join 사용 불가. 이때 using 사용

      SELECT *
      FROM DEPT JOIN DEMPT_TEMP
      USING (DEPTNO);

Sub Query

SELECT col1, (SELECT ...)     -- 스칼라 서브쿼리(Scalar Sub Query): 하나의 컬럼처럼 사용 (표현 용도) FROM (SELECT ...)             -- 인라인 뷰(Inline View): 하나의 테이블처럼 사용 (테이블 대체 용도) WHERE col = (SELECT ...)    -- 일반 서브쿼리: 하나의 변수(상수)처럼 사용 (서브쿼리의 결과에 따라 달라지는 조건절)

• Inline View
  • from 절 내에서 쓰이는 서브쿼리
  • select 절을 마치 테이블처럼 쓰고 싶을 때 사용
  • 메인쿼리에서 서브쿼리 컬럼 사용 가능

여러 함수

• NULL 처리 함수

  • ⭐ NVL(v1,v2) : Null 이 컬럼에 포함된 경우 어떠한 값으로 치환하는 함수

    • v1 에 null 이 있다면 v2 로 치환 보통 문자면 ‘X’, 숫자면 0 으로 치환함

      SELECT COL1, COL2
      FROM TAB1
      WHERE NVL(COL1, 'X') <> 'A01';

    • COL1이 NULL 인 경우 ‘X’ 로 치환, 그 후 COL1값이 ‘A01’이 아니면 출력 → 보통 Null 행도 출력하기 위해 사용함(해당 함수를 사용하지 않는다면 Null인 행은 자동으로 배제되고 출력됨)

    • 실행결과

        <TAB1 테이블 구조>
        COL1 COL2
        A01 10
        A02 20
        A03 30 
        NULL 40 
        NULL 50
      
        <실행 결과>
        CO1 CO2 
        A02 20 
        A03 30 
        NULL 40 
        NULL 50

  • ISNULL(v1,v2) : NVL과 동일

  • COALESCE(v1,v2,...,vn) : 모든 v들이 null이면 null 반환하지만, 하나라도 아닌 경우 null이 아닌 최초의 표현식을 반환

  • NULLIF(v1,v2) : 특정값을 NULL로 대치하기 위한 함수

    • v1이 v2이라면 NULL로 치환

• 문자열 관련 함수

  • 문자열 이어붙이기 : +, ||(연산자), CONCAT,
  • 공백제거 : trim

• length(), len()

  • length() : ORACLE
  • len() : SQL SERVER

• 집계함수

  • AVG

    • 해당 칼럼값이 NULL 인 것 제외해서 결과 반환

  • SUM

    • 해당 칼럼값이 NULL 인 것 제외해서 결과 반환

  • COUNT

    • 아무 결과도 반환안하면 0을 반환함

      • 이외의 다른 집계함수는 아무 결과가 없다면 NULL → NVL함수 필요

        SELECT nvl(count(*), 9999) from table where 1=2 // 0

    • COUNT(*) : NULL 포함해서 결과 반환

    • COUNT(column) : 해당 컬럼값이 NULL 인것 제외해서 결과 반환

• SQL의 IF-ELSE

  • DECODE(칼럼,조건1,결과1,조건2,결과2,...)
    • 칼럼 == 조건1 → 결과1 반환
    • 해당 조건이 없는경우 NULL 반환
  • CASE : 최근 권장
    • ELSE 없고 해당값이 아니면 NULL 반환

• WINDOW FUNCTION

  • 윈도우 함수 개념 정리

    • https://velog.io/@yewon-july/Window-Function

    SELECT WINDOW_FUNCTION(ARGUMENTS)
    OVER (PARTITION BY 칼럼
              ORDER BY WINDOWING절
              RANGE BETWEEN start_point AND end_point)
    FROM 테이블명;

  • WINDOW_FUNCTION 가능한 것

    • 집계함수 ( COUNT, AVG, ... )
    • 순위함수
      • RANK() : 4,5,5,5,8 (동일 순위에 대해 하나의 등수로 매기지만, 그 다음 등수는 중복값 개수 쳐서 진행)
      • RANK_DENSE() : 4,5,5,5,6 (동일 등수에 대해 하나의 등수로 매기고, 그 다음 등수는 해당 등수 다음)
      • ROW_NUMBER() : 4,5,6,7,8 (동일 등수에 대해서도 순위 매김)
      • PERCENT_RANK() : 값이 아닌 백분율로 순위 매김 (0~1 사이값 반환)

  • PARTITION_BY : group by 과 비슷

  • RANGE BETWEEN start_point AND end_point : 누적 평균 낼 수 있음

    • default : RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW : 누적평균

    • UNBOUNDED PRECEDING : start_point만 들어갈 수 있으며, 파티션의 first row

    • UNBOUNDED FOLLOWING : end_point만 들어갈 수 있으며, 파티션의 last row

    • CURRENT ROW : start, end_point 둘다 가능. 윈도우는 CUREENT ROW에서 start하거나 end 함

    • 누적평균 예제

        SELECT COL1, COL2,
        ROUND ( AVG (COL3) 
            OVER( PARTITION BY COL1 ORDER BY COL3
                        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) , 0)
            AS 누적평균
        FROM TAB1 
        ORDER BY COL1, COL3;
      

      • COL1 로 파티션되어서 파티션 내의 누적평균

• 그룹 함수

  • ROLLUP

    • 인수 순서 상관O
      • 1번째 인수 중심

  • CUBE

    • 인수 순서 상관X
      • 인수에 따라 나올 수 있는 모든 경우의 수 출력해줌

  • GROUPING SETS

    https://velog.io/@dongchyeon/오라클Oracle-그룹-함수-ROLLUP-CUBE-GROUPING-등

연산자

• IN

  • COL1 IN(NULL, 'A') : COL1 = Null or COL1 = ‘A’ 로 치환됨. 결과는 null인 행은 출력안함
  • COL1 NOT IN(NULL, 'A') : NOT IN의 조건절에 NULL 이 하나라도 포함되면 아무행도 출력안함
  • IN, ANY, ALL
    • IN(10,20,30) : 10,20,30 값 중 하나
    • ANY(10,20,30) : 10,20,30 어떤 값보다 - 비교연산자 사용
    • ALL(10,20,30) : 10,20,30 모두보다 - 비교연산자 사용
  • IN, EXISTS
    • IN : 컬럼 값 비교할 때 좋음, 서브쿼리는 안넣는게 좋음
      • 일치하지 않더라도 서브쿼리를 모두 수행하기 때문에 별로 안좋음
      • SELECT * FROM A WHERE A.v1 IN (SELECT B.v1 FROM B) : not good
    • EXISTS : 서브쿼리 넣어서 비교할 때 좋음(성능상)
      • 한 건이라도 만족하면 true
      • 일치하지 않다면 더이상 서브쿼리를 실행하지 않는다. - 뭔말인지 이해안감
      • SELECT * FROM A WHERE EXISTS (SELECT 'X' FROM B WHERE A.v1 = B.v1) : good
        • 서브쿼리절의 컬럼은 불필요함 - 의미없는 값(’X’) 넣음

• =

  • COL1=NULL : NULL 을 비교시 항상 False를 함. 따라서 실제 Null인 행이 있어도 출력 암것도 안함

• IS, IS NOT

  • 유일하게 NULL 을 잡아낼 수 있는 연산자

• SUM

  • SUM(NULL, 1) : 1

• SET 연산자

  • INTERSECT

  • UNION

    • UNION ALL : 중복 제거하기 위한 정렬 안함

      • 문제

          [데이터]
          TABLE A 
          COL1 COL2 
          -----------
             1    2
             1    2
             1    3
          TABLE B 
          COL1 COL2 
          -----------
             1    2 
             1    4 
             1    5
          [SQL]
          SELECT DISTINCT COL1, COL2 FROM TAB_A 
          UNION ALL
          SELECT COL1, COL2 FROM TAB_B;
        
          // 결과 : 5

Having

• HAVING COUNT(*)

    SELECT 주문일자, 고객명, COUNT(*) 
    FROM TAB1
    GROUP BY 주문일자,고객명
    HAVING COUNT(*) >= 2;

  • 2건 이상 주문하는 고객리스트

Order by

• order by 절에는 숫자 가능
  • 0 : 해당 column 을 가장 위에 오게 함 (CASE 와 함께 쓰임 order by case ROWNUM when 3 then 0)
  • 1,2,3,... : select a,b,c 일때 a 가 1, b가 2, c가 3, 만약 여기서 order by 4 하면 오류남(4에 해당하는 칼럼이 없으므로)
• group by 가 있는 경우 order by 에 집계함수 사용 가능
  • group by col1 order by count(*) : col1에 따라 개수 셈
• select 절에서 alias 로 명명지은 이름 사용가능
  • select user_name as name from dept order by name
  • SQL 실행순서와 관련있음 (select 이후에 order by를 행하므로 select 시에 alias 로 지은 이름을 사용할 수 있는 것)
  • 비교 : group by 절 내에서는 alias 로 명명지은 이름 사용 불가 (group by 는 select 이전에 실행되기 때문)

SQL Server ↔ Oracle

• TOP(SQL Server), ROWNUM : Oracle

  • oracle

      SELECT ENAME, SAL
      FROM (SELECT ENAME, SAL
                  FROM SCOTT.EMP
                  ORDER BY SAL DESC) 
      WHERE ROWNUM < 4 ;

  • SQL server

      SELECT TOP(3) ENAME,SAL 
      FROM SCOTT.EMP
      ORDER BY SAL DESC

ANSI SQL 표준

• 모든 DBMS에 통하는 SQL문

• Inner Join 표준

  • 오라클

      SELECT *
      FROM SCOTT.EMP A,
      SCOTT.DEPT B
      WHERE A.DEPTNO = B.DEPTNO AND B.DNAME = 'SALES'

  • ANSI 표준

      SELECT *
      FROM SCOTT.EMP A INNER JOIN SCOTT.DEPT B
      ON A.DEPTNO = B.DEPTNO 
      WHERE 1=1
      AND B.DNAME ='SALES';

VIEW(뷰)

• 개념

  • 사용자에게 제한적으로 자료를 보여주기 위한 가상 테이블

    • 필요한 데이터만 뷰로 정의 가능

  • 물리적으로 존재X, 임시작업(데이터 보정, 처리과정)위한 용도

  • 뷰정의 변경 불가(ALTER VIEW 안됨)

    • 변경이 필요하다면 삭제후에 정의 다시해야함

  • 뷰로 구성된 내용삽입,제거,연산,갱신 제약O

  • 예제

    • 정의문

        --문법--
        CREATE VIEW 뷰이름[(속성이름[,속성이름])]AS SELECT문;
      
        --고객 테이블에서 주소가 서울시인 고객들의 성명과 전화번호를 서울고객이라는 뷰로 만들어라--
        CREATE VIEW 서울고객(성명, 전화번호)
        AS SELECT 성명 전화번호
        FROM 고객
        WHERE 주소 = '서울시';

    • 삭제문

        --문법--
        DROP VIEW 뷰이름 RESTRICT or CASCADE
      
        --서울고객이라는 뷰를 삭제해라--
        DROP VIEW 서울고객 RESTRICT;

      RESTRICT : 뷰를 다른곳에서 참조하고 있으면 삭제가 취소된다. CASCADE : 뷰를 참조하는 다른 뷰나 제약 조건까지 모두 삭제된다.

계층형 질의 (계층형 SQL)

• 쉽게 설명

  https://mjn5027.tistory.com/55

• 계층형 쿼리 관련

  https://devdhjo.github.io/sqld/2019/11/26/database-sqld-020.html

• MAX(LEVEL) : 계층형 쿼리에서 최대 계층 수 구함

    SELECT MAX(LEVEL)
    FROM SCOTT.EMP
    START WITH MGR IS NULL
    CONNECT BY PRIOR EMPNO = MGR;

  • 매니저~사원까지 결제 단계가 가장 많은 레벨을 구함

Trigger

• 특정 테이블에서 DML(select,insert,update,delete)이 일어나게 된다면 자동적으로 데이터베이스에서 수행되도록 만들어진 프로그램
  • 결국 해당 DML과는 트랜잭션 관계
  • begin,end 절 내에서는 TCL(COMMIT, ROLLBACK) 사용 불가
• :OLD : 기존 데이터
• :NEW : 변경될 데이터

최적화

• Optimizer (옵티마이저)

  • 비용 기반 옵티마이저 : 쿼리 수행시 소요되는 시간 및 일량 기준으로 최적화 수행

• 실행계획

• INDEX(인덱스)

  • 효율적으로 숫자 인덱스를 사용하는 방법?

    • = 으로 찾기 (WHERE COL1 = 10)

    • 비효율적인 방법

      WHERE COL1 LIKE '2%' : LIKE - 문자로 변환해서 인덱스 사용 못함 WHERE COL1 IS NOT NULL : 인덱스 풀 조회하지만, 효율 떨어짐 WHERE COL1 <> 10 : 부정형 비교(<>, !=, ^=, NOT)는 인덱스 사용 불가

• JOIN

  • ⭐Nested Loop Join, Sort Merge Join, Hash Join
    • Nested Loop Join
      • C++의 중첩된 반복문처럼 조인 수행
      • INDEX 사용
      • 데이터 읽는 방식 : 랜덤 액세스
        • ex ) Index scan
      • 대량의 데이터에 적합 X
      • 빨리 조인 결과를 보여줘야 하는 경우 굳
    • Sort Merge Join
      • 먼저 조인할 두 테이블의 SORT 부터 수행 후 merge 해서 join
        • sort 로 인해 disk 를 사용할 수 있어 성능이 그렇게 좋지 않음(느리다)
      • INDEX 사용 안함
      • Non Equal Join 가능
      • 대량의 데이터는 성능상 Hash Join 이 유리함
    • Hash Join
      • NL Join(랜덤 액세스), Sort Merge Join(선 sort로 인한 느림) 문제점 해결
      • INDEX 사용 안함
      • Non Equal Join 불가능
      • 대량의 데이터 처리시 굳! (CPU 작업 위주로 처리하기 때문)
    • https://hoon93.tistory.com/46

이 visited를 초기화할 때 밑과 같은 방법으로 했더니 잘 동작하지 않았다.

visited = [[False]*3]*4

visited[1][2] = True

이때, visited를 출력해보면, 밑과 같다.

[[False, False, True], 
[False, False, True], 
[False, False, True], 
[False, False, True]]

바꾸려고 했던 (1,2)의 값만 바뀌지 않고, 모두 바뀌는 것이었다.

제대로 동작하도록 수정해보면 다음과 같다.

visited_ = [ [False]*3 for l in range(4) ]

visited_[1][2] = True
print(visited_)

이때, visited_를 출력해보면 원하는 대로 (1,2)만 수정된 것을 볼 수 있다.

[[False, False, False], 
[False, False, True], 
[False, False, False], 
[False, False, False]]

결론

리스트를 포함하는 리스트에 곱하기 연산자를 사용하면, 얕은 복사가 되어, 값 하나를 변경시 모두 변경된다. 따라서 초기화할 때에는 반드시 밑처럼 사용하자.

visited = [ [False]*3 for _ in range(4) ]

• includes 사용

  currentLocation = 'local';
  ['local','dev'].includes(currentLocation)

실수

실수 코드

• || 사용해서 해결하려고 함

  currentLocation = 'local';
  curretLocation === ('local' || 'dev')

실수 상황

현재 빌드한 상황이 로컬인지 개발기인지 운영기인지 비교하기 위해서 해당 코드를 작성했다. 저 코드를 작성하고 로컬에서 잘 돌아가네 싶어서 올렸는데 개발기에서 동작하지 않는 것이었다..

다시 살펴보니 || 연산자는 값을 리턴하는 '연산자'였던 것이었다.. 'local' || 'dev' 하면 당연히 해당 연산에서는 local을 값으로 리턴한다. 어쩌다 이렇게 바보같은 코드를 짜고 돌아간다고 생각한건지 모르겠다.

여하튼 어떠한 값이 여러개 값 중 하나인지 검사할 때에는 includes를 쓰자!

• 문제

  • 상황 : mount하는 시점에 dispatch실행
  • mount, action은 한번씩만 불렸지만, reducer과 saga는 여러번 호출됨

• 관련 코드

  • Component (Layout.js)

      import React, {Component} from 'react';
      import { connect } from 'react-redux';
      import '../assets/css/Contacts.css';
      import {selectedContactAction} from '../Store/Actions/selectedContactAction'
      import {setContacts} from '../Store/Sagas/contactsSaga';
    
      class Layout extends Component {
    
          async componentDidMount() {
              this.props.setContacts();
          }
    
          render() {
    
              return (
                  <div className={'contacts'}>
                        {
                                          this.props.contacts
                                  }  
                  </div>
              );
          }
      }
    
      const mapStateToProps = (state) => ({
          contacts : state.contacts
      });
    
      const mapDispatchToProps = (Dispatch) => {
          return {
              setContacts : () => Dispatch(contactsSetAction())
          }
      }
    
      export default connect(mapStateToProps,mapDispatchToProps)(Layout);

  • Action Types (types.js)

      export const contactsType = {
          CONTACTS_SET : 'CONTACTS_SET'
      }

  • Action (contactsAction.js)

      export const contactsSetAction = () => {
          return { type: contactsType.CONTACTS_SET }
      };

  • Saga (contactsSaga.js)

      export function* setContacts(){
          try{
              const response = yield call(fetchApi().getAll);
              yield put({type: contactsType.CONTACTS_SET, contacts : response.map(symbolizeObjectId)});
          } catch (error) {
              yield put({type: contactsType.CONTACTS_SET, contacts : {}})
          } finally {
          }
      }
    
      const saga = [
          takeLatest(contactsType.CONTACTS_SET, contactsSaga.setContacts),
      ]
      export default saga;

  • Reducer (contactReducer.js)

      import { contactsType } from '../Actions/types';
    
      const contactsReducer = (state = [], action) => {
          switch (action.type) {
              case contactsType.CONTACTS_SET:
                  return action.contacts ? action.contacts : state;
              default:
                  return state;
          }
      };
    
      export default contactsReducer;

Solve

• Action, Saga 용 type과 reducer용 type을 나누기

  • Action, Saga 용 : CONTACTS_SET
  • reducer 용 : CONTACTS_SET_RESULT

• 변경한 코드 (CONTACTS_SET_RESULT 추가)

  • Action Types (types.js)

      export const contactsType = {
          CONTACTS_SET : 'CONTACTS_SET', //action, saga용
          CONTACTS_SET_RESULT : 'CONTACTS_SET_RESULT' // reducer 용
      }

  • Saga (contactsSaga.js)

      export function* setContacts(){
          try{
              const response = yield call(fetchApi().getAll);
              yield put({type: contactsType.CONTACTS_SET_RESULT, contacts : response.map(symbolizeObjectId)});
          } catch (error) {
              yield put({type: contactsType.CONTACTS_SET_RESULT, contacts : {}})
          } finally {
          }
      }
    
      const saga = [
          takeLatest(contactsType.CONTACTS_SET, contactsSaga.setContacts),
      ]
      export default saga;

  • Reducer (contactReducer.js)

      import { contactsType } from '../Actions/types';
    
      const contactsReducer = (state = [], action) => {
          switch (action.type) {
              case contactsType.CONTACTS_SET_RESULT:
                  return action.contacts ? action.contacts : state;
              default:
                  return state;
          }
      };
    
      export default contactsReducer;

핵심

무한히 saga를 호출하지 않게 하기 위해서 takeLatest에서 지켜보는 type과 put하는 type를 같게 하면 안된다.

• error 주요 코드

    export function* setContacts(){
        try{
            const response = yield call(fetchApi().getAll);
            yield put({type: contactsType.CONTACTS_SET, contacts : response.map(symbolizeObjectId)});
        } catch (error) {
            yield put({type: contactsType.CONTACTS_SET, contacts : {}})
        } finally {
        }
    }
  
    // Action이랑 Saga 연결
    const saga = [
        takeLatest(contactsType.CONTACTS_SET, contactsSaga.setContacts)
    ];
    export default saga;

  • put과 takeLatest type 같음

• good 주요 코드

    export function* setContacts(){
        try{
            const response = yield call(fetchApi().getAll);
            yield put({type: contactsType.CONTACTS_SET_RESULT, contacts : response.map(symbolizeObjectId)});
        } catch (error) {
            yield put({type: contactsType.CONTACTS_SET_RESULT, contacts : {}})
        } finally {
        }
    }
  
    // Action이랑 Saga 연결
    const saga = [
        takeLatest(contactsType.CONTACTS_SET, contactsSaga.setContacts)
    ];
    export default saga;

  • put과 takeLatest type 다름

Unhandled Rejection (Error): When called with an action of type "CONTACTS_SET", the slice reducer for key "contacts" returned undefined. To ignore an action, you must explicitly return the previous state. If you want this reducer to hold no value, you can return null instead of undefined.

reducer 규칙

• state 값으로 null, undefined 넘겨줄 수 없다.
  • 이전 state을 넘기거나 action에서 받아온 payload등을 넘겨야 함

수정

• error

  case contactsType.CONTACTS_SET:
    return action.contacts;

• good

  case contactsType.CONTACTS_SET:
   return action.contacts ? action.contacts : state;

참고

• reducer 규칙

한글이 깨져 이상한 문자로 보였다.

해결

UTF8로 변경한다. 이때, 데이터베이스의 character set뿐만 아니라, 데이터베이스 내의 테이블의 character set도 변경한다.

• Database character set 변경

  alter database [DB명] default character set UTF8;

• Table character set 변경

  alter table [table명] convert to character set UTF8;

• 해결한 코드 ```sql
• • character set 확인 show variables like 'c%';

-- database character set 변경 alter database covengers default character set UTF8;

-- table character set 변경 alter table room convert to character set UTF8; alter table chat convert to character set UTF8; alter table room convert to character set UTF8; alter table room convert to character set UTF8; alter table room convert to character set UTF8;

-- character set 확인 show variables like 'c%'; ```

Failed to compile.

./src/assets/scss/Index.scss (./node_modules/css-loader/dist/cjs.js??ref--6-oneOf-5-1!./node_modules/postcss-loader/src??postcss!./node_modules/resolve-url-loader??ref--6-oneOf-5-3!./node_modules/sass-loader/dist/cjs.js??ref--6-oneOf-5-4!./src/assets/scss/Index.scss)
Error: Missing binding E:\project\covengers\hungry-chat-react\node_modules\node-sass\vendor\win32-x64-57\binding.node
Node Sass could not find a binding for your current environment: Windows 64-bit with Node.js 8.x   

Found bindings for the following environments:
  - Windows 64-bit with Node.js 14.x

npm install을 하고 모듈 설치를 한 후 npm start를 했을 때 이러한 오류가 뜨며 리액트가 실행되지 않았다.

해결

scss 문제는 대개 버전 관련 문제라고 한다. 오래 전 업데이트를 한 후 이후로 업데이트가 없어서 최신 버전의 Java 나 Node를 사용할 경우 해당 오류가 발생하게 된다.

• Java version 8~11
• Node version 14
  • Node 16 이나 8 이면 Node 제거하고 재설치

• Chatting app 개발 (socket.io,redis 이용 구현)

• 개발 환경

  • backend : node
  • frontend : React

• 문제 상황

  • server(node)쪽에서 client(React)쪽으로 메세지를 보내줄 때마다 메세지를 보낼 때마다 메세지 받고 출력하는 이벤트가 급수적으로 추가적으로 불러져서 동일한 메세지가 여러개 생성되었습니다.

문제

React에서 아무 장소에서나 socket 이벤트 호출(socket.on('message',function))하는 코드를 작성하니 rendering 될 때마다 socket이 하나씩 추가되어 이벤트도 그에 따라 여러번 호출되는 현상이 발생하였습니다.

즉, 한 번 rendering될 때마다 socket이 하나씩 추가되고, 하나의 이벤트가 발생할 것이라고 예상한 것이 실제로는 생성된 socket수 만큼 이벤트를 발생하였기에 rendering한 수가 2번이면 socket 수가 2개이고, 이벤트도 2번 호출되었습니다.

해결

찾아보니 rendering될 때마다 socket은 계속 생성되는 것이 맞습니다. 다만, 문제는 생성된 모든 socket 객체가 event를 실행하는 데에 있었습니다.

이 문제는 react component life-cycle과 관련이 있습니다. socket 호출(socket.on)부분은 component가 한 번 mount된 후(즉, 처음 socket이 생성되었을 때)만 호출되어야 합니다. component update될 때마다 socket 호출이 있게되면 생성된 소켓 수만큼 호출되게 됩니다. 따라서 한 번 mount된 직후에만 호출될 수 있도록(socket이 처음 생성된 후에만) 만드시면 됩니다.

따라서, class component인 경우, 밑처럼 componentDidMount내에서만 socket.on을 하시면 됩니다.

componentDidMount() {
  socket.on("comment", (info) => {
    this.textAreaAdd(info.name, info.room, info.message)
  }) 
}

function component인 경우, 밑처럼 hook함수인 useEffect를 이용해서 하시면 됩니다.

useEffect(() => {
  socket.on("comment", (info) => {
    this.textAreaAdd(info.name, info.room, info.message)
}, []);

• windows 10

  문제

  MySQL Workbench에서 3306 포트로 접속한 DB와 Node에서 개발하기 위해 Sequelize에서 3306 포트로 접속한 DB의 내용이 다른 것을 보고 MySQL이 2개가 깔려있고, 포트가 서로 충돌이 일어나서 일정하지 않게 프로그램에 따라 다른 DB로 연결되는 것을 알게 되었습니다.

문제를 확인 하기 위해 작업 관리자를 틀어보니 실제로 예전에 깔았던 bitnami에서 함께 깔았던 MySQL과 Oracle에서 깐 MySQL이 함께 있는 것을 볼 수 있었습니다.

• 작업 관리자 화면
  • mysqld가 두 개가 있는 것을 볼 수 있습니다.
  • 하나는 bitnami를 통해 깐 MySQL이고, 다른 하나는 일반적으로 MySQL을 설치하는 방법인 Oracle 통한 MySQL 설치로 만들어진 MySQL server입니다.

해결

저는 제가 관리하기 쉬운 MySQL의 포트를 변경하기로 하였습니다. 따라서, 예전에 사용했던 bitnami말고 현재 사용하는 Oracle 홈페이지에서 설치한 MySQL의 포트를 변경합니다.

DB의 설정파일은 my.ini파일에 있습니다. 따라서 윈도우 cmd창에서 my.ini파일을 찾아주었습니다. C:\경로에서 dir /s my.ini를 하시면 my.ini파일이 있는 것을 보실 수 있습니다.

전 여기에서 MySQL Server 8.0에 있는 my.ini파일의 포트를 변경해줄 것이기에 해당 경로 (C:\ProgramData\MySQL\MySQL Server 8.0\my.ini)의 my.ini파일을 틀고 port번호를 3306에서 3307로 변경해주었습니다. 두 군데에 있으니 두 군데 모두 변경해주시면 됩니다.

변경 전	변경 후

그 이후, 변경한 MySQL의 서비스를 중지한 후, 다시 실행해야 합니다. 윈도우 버튼을 눌러 "서비스"를 검색하셔서 어플을 실행시켜주신 후, MySQL80의 서비스를 중지한 후 다시 실행을 하시면 됩니다.

예를 들어, participant, chat table이 있을 때, participant : chat 의 관계가 1 : N 이라 가정하겠습니다. chat table은 participant의 primary key인 no를 participantNo라는 foreign key를 가집니다.

파일에 대해 설명드리자면, models라는 폴더 내에 index.js, Chat.js, Participant.js가 존재합니다. Chat.js, Participant.js는 sequelize.define 함수로chat, participant table을 정의하는 파일이고, index.js는 sequelize 생성(DB연결) 및 Chat과 Participant등등 table들의 관계( relation)를 명시하는 파일입니다.

이때, chat sequelize define에는 foreign key를 명시하지 않고, index.js에서 chat과 participant의 관계를 명시하면 됩니다. 즉, 밑처럼 관계를 index.js에서 밑 부분만 추가하시면 됩니다.

models/index.js

...
Participant.hasMany(Chat, {
    foreignKey: 'participantNo',
    allowNull: false,
    constraints: true,
    onDelete: 'cascade'
});
Chat.belongsTo(Participant, {
    foreignKey: 'participantNo'
});
...

밑은 전체 코드입니다.

models/index.js

const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize(
    process.env.DB_NAME,
    process.env.DB_USER,
    process.env.DB_PASSWORD,{
        host: process.env.DB_HOST,
        port: process.env.DB_PORT,
        timezone: "+09:00",
        dialect: 'mysql'
    }
);

const Chat = require('./Chat')(sequelize);
const Participant = require('./Participant')(sequelize);

Participant.hasMany(Chat, {
    foreignKey: 'participantNo',
    allowNull: false,
    constraints: true,
    onDelete: 'cascade'
});
Chat.belongsTo(Participant, {
    foreignKey: 'participantNo'
});

Chat.sync({
    force: process.env.TABLE_CREATE_ALWAYS === 'true', // true : (drop) table 데이터 없어질 수 있음
    alter: process.env.TABLE_ALTER_SYNC === 'true'     // 개발 끝나면 false로 하기
})
Participant.sync({
    force: process.env.TABLE_CREATE_ALWAYS === 'true', // true : (drop) table 데이터 없어질 수 있음
    alter: process.env.TABLE_ALTER_SYNC === 'true'     // 개발 끝나면 false로 하기
})

module.exports = {sequelize, Chat, Participant};

models/Participant.js

const { Sequelize, DataTypes } = require('sequelize');
const models = require('../models');

module.exports = function(sequelize){
    // foreign Key : (Room)room_no, (User)user_no
    const participant = sequelize.define('Participant', {
        no: {
            field: 'no',
            type: DataTypes.INTEGER,
            primaryKey: true,
            autoIncrement: true,
            allowNull: false
        },
        role: {
            field: 'role',
            type: DataTypes.STRING(45),
            allowNull: false
        },
        status: {
            field: 'status',
            type: DataTypes.ENUM('true', 'false') ,
            allowNull: false
        },
        createdAt: {
            field: 'createdAt',
            type: DataTypes.DATE,
            allowNull: false
        },
        lastReadAt: {
            field: 'lastReadAt',
            type: DataTypes.DATE,
            allowNull: false
        }
    }, {
        underscored: false, // updateAt -> updateAt (underscored: update_at)
        freezeTableName: true,
        timestamps: true,
        createdAt: false,
        updatedAt: false,
        tableName: 'participant'
    });
    return participant;
}

models/chat.js

const { Sequelize, DataTypes } = require('sequelize');

module.exports = function(sequelize){
    // foreign Key : (Participant)participant_no
    return sequelize.define('Chat', {
        no: {
            field: 'no',
            type: DataTypes.INTEGER,
            primaryKey: true,
            autoIncrement: true,
            allowNull: false
        },
        type: {
            field: 'type',
            type: DataTypes.STRING(45),
            allowNull: false
        },
        createdAt: {
            field: 'createdAt',
            type: DataTypes.DATE,
            allowNull: false
        },
        contents: {
            field: 'contents',
            type: DataTypes.TEXT,
            allowNull: false
        },
        read: {
            field: 'read',
            type: DataTypes.INTEGER,
            allowNull: false
        }
    }, {
        underscored: false, // updateAt -> updateAt (underscored: update_at)
        freezeTableName: true,
        timestamps: true,
        createdAt: false,
        updatedAt: false,
        tableName: 'chat'
    });
}

결과

서버를 실행시키셨을 때, 밑처럼 실행된다면 성공입니다.

Executing (default): CREATE TABLE IF NOT EXISTS `chat` (`no` INTEGER NOT NULL auto_increment , `type` VARCHAR(45) NOT NULL, `createdAt` DA
TETIME NOT NULL, `contents` TEXT NOT NULL, `read` INTEGER NOT NULL, `participantNo` INTEGER, PRIMARY KEY (`no`), FOREIGN KEY (`participant
No`) REFERENCES `participant` (`no`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

Executing (default): CREATE TABLE IF NOT EXISTS `participant` (`no` INTEGER NOT NULL auto_increment , `role` VARCHAR(45) NOT NULL, `status
` ENUM('true', 'false') NOT NULL, `createdAt` DATETIME NOT NULL, `lastReadAt` DATETIME NOT NULL, `roomNo` INTEGER, `userNo` INTEGER, PRIMA
RY KEY (`no`), FOREIGN KEY (`roomNo`) REFERENCES `room` (`no`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`userNo`) REFERENCES `use
r` (`no`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;