[Hanaro] 47일차 / SQL (DML, DDL, 제약조건, 데이터타입, 윈도우함수, 데이터모델링)

07 DML 이해 및 실습

• 데이터 조작어(Data Manipulation Language, DML)
  • 데이터 추가(INSERT), 변경(UPDATE), 삭제(DELETE)할 때 사용
• INSERT
  • 테이블에 새로운 행 삽입
  • INSERT INTO 테이블명 VALUES(값1, 값2, …);
    • 컬럼명 생략 가능하나, 삽입할 값이 테이블의 컬럼 수와 순서도 동일해야 한다.
  • INSERT INTO 테이블명(컬럼1, 컬럼2, …) VALUES (값1, 값2, …);
• UPDATE
  • 기존 행에 있는 데이터 값 변경
  • UPDATE 문에 WHERE 절이 없으면 모든 행의 값이 변경되므로 주의!
  • UPDATE 테이블명 SET 컬럼1=값1, 컬럼2=값2 [WHERE 조건];
• JOIN을 사용한 UPDATE
  • 하위 쿼리에서 메인 쿼리의 대상 테이블을 참조하면 오류 발생하므로 서브쿼리 대신 조인을 사용해야 한다!
  • UPDATE 테이블명 뒤에 JOIN 사용
  • JOIN 예시

    • UPDATE 고객 AS c
      JOIN (SELECT ROUND(AVG(마일리지)) AS 평균마일리지
      		FROM 고객
      		WHERE 담당자직위='대표 이사'
      		GROUP BY 담당자직위) AS 평균 
      SET c.마일리지= 평균.평균마일리지
      WHERE c.고객번호='ZZZAA';
      SELECT * FROM 고객;

• DELETE
  • 기존에 있는 행을 삭제
  • DELETE 문에 WHERE 절이 없으면 모든 행이 삭제되므로 주의!
  • DELETE FROM 테이블명 [WHERE 조건];
• JOIN 사용한 DELETE
  • 하위 쿼리에서 메인 쿼리의 대상 테이블을 참조하면 오류 발생하므로 서브쿼리 대신 조인을 사용해야 한다!
  • 두 테이블에서 조건 충족하는 행 삭제 (INNER JOIN)

    • DELETE 테이블명1, 테이블명2
      FROM 테이블명1
      INNER JOIN 테이블명2
      ON 테이블명1.컬럼명 = 테이블명2.컬럼명
      WHERE 테이블명1.컬럼명 = 값;

  • 한 테이블에서 조건 충족하는 행 삭제 (LEFT JOIN)

    • DELETE 부서
      FROM 부서
      LEFT JOIN 사원 ON 부서.부서번호 = 사원.부서번호
      WHERE 사원.사원번호 IS NULL;

• INSERT ON DUPLICATE KEY UPDATE
  • 레코드가 없다면 새롭게 추가하고, 이미 있다면 데이터 변경
  • 형식

    • INSERT INTO 테이블명(컬럼1, 컬럼2, ...)
      VALUES(값1, 값2, ...)
      ON DUPLICATE KEY UPDATE
      컬럼1 = 값1, 컬럼2 = 값2, ...;​

    • ON DUPLICATE KEY UPDATE 사용하려면 INSERT INTO 절에서 지정한 컬럼 중에 기본키 컬럼이 존재해야 한다. 

 

 

08 DDL 이해 및 실습

• 데이터 정의어 (Data Definition Language, DDL)
  • 테이블, 인덱스, 뷰 등의 객체를 생성(CREATE), 수정(ALTER), 삭제(DROP)
• CREATE
  • 데이터베이스, 테이블, 뷰, 인덱스 등 객체 생성
  • 데이터베이스 생성
    • CREATE DATABASE [IF NOT EXISTS] 데이터베이스명;
  • 테이블 생성
    

    • CREATE TABLE 테이블명 (
      컬럼1 데이터타입,
      컬럼2 데이터타입,
      ...
      )

  • 테이블 구조 복사
    • CREATE TABLE 테이블명 AS SELECT문;
• ALTER
  • 컬럼 추가
    • ALTER TABLE 테이블명 ADD COLUMN 새로운컬럼명 데이터타입;
  • 기존 컬럼 연산하여 자동으로 값까지 추가되는 컬럼 추가
    • ALTER TABLE 테이블명 ADD COLUMN 새로운컬럼명 데이터타입 GENERATED ALWAYS AS (컬럼 이용한 수식) STORED;
    • 예시

      • ALTER TABLE 제품 
        ADD COLUMN 재고금액 INT 
        GENERATED ALWAYS AS (단가 * 재고) STORED;

  • 컬럼 데이터 타입 변경
    • ALTER TABLE 테이블명 MODIFY COLUMN 컬럼명 새로운데이터타입;
  • 컬럼명 변경
    • ALTER TABLE 테이블명 CHANGE COLUMN 새로운컬럼명 데이터타입;
  • 컬럼 삭제
    • ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
  • 테이블명 변경
    • ALTER TABLE 테이블명 RENAME 새로운테이블명;
• DROP
  • 데이터베이스, 테이블 등 객체 삭제
  • 데이터베이스 삭제
    • DROP DATABASE 데이터베이스명;
  • 테이블 삭제
    • DROP TABLE 테이블명;
• 제약조건
  • 테이블에 제약조건을 설정하여 데이터 무결성을 유지할 수 있다.
  • CREATE 문으로 테이블 생성할 때나 ALTER 문으로 테이블 구조 변경시 지정 가능
  • 제약조건은 고유한 이름을 붙여서 식별할 수 있다.
  • 한 컬럼의 여러 개의 제약조건 설정 가능
  • 형식

    • CREATE TABLE 테이블명 (
      컬럼1 데이터타입 제약조건,
      컬럼2 데이터타입,
      제약조건(컬럼2)
      );​

    • 컬럼의 데이터타입 바로 다음에 기술 또는
    • 컬럼의 정의를 끝낸 후 제약조건을 별도로 지정으로 가능
• 제약조건 종류
  • PRIMARY KEY
    • 기본키 설정
    • 기본키는 테이블 당 한개만 가능
    • 기본키는 NOT NULL과 유일한 값 (UNIQUE)을 가져야 한다.
    • 기본키 생성시 자동으로 인덱스 생성
  • NOT NULL
  • UNIQUE
    • 유일한 값을 넣어야 한다.
    • 자동 인덱스 생성
  • CHECK
    • 설정된 조건에 맞는 값만 넣어야 한다.
    • 조건으로는 특정 값이나 범위, 특정 패턴의 숫자나 문자 설정 가능
  • DEFAULT
    • 값을 넣지 않으면 지정한 값이 자동으로 들어간다.
  • FOREIGN KEY
    • 외래키 설정
    • 한 테이블의 외래키는 참조하는 테이블의 기본키이거나 NULL이어야 하고, 데이터 타입과 크기가 동일해야 한다.
• 제약조건의 추가/삭제/지정
  • 추가
    • ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건(컬럼명이나 조건);
    • 예시

      • ALTER TABLE 평점관리 ADD CONSTRAINT q08_8 FOREIGN KEY (영화번호)
            REFERENCES 영화(영화번호)
            ON DELETE CASCADE;

  • 삭제
    • ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건;
  • 제약조건명의 지정 (고유 이름)
    • 컬럼 레벨
      • 컬럼명 데이터타입 [CONSTRAINT 제약조건명] 제약조건
    • 테이블 레벨
      • [CONSTRAINT 제약조건명] 제약조건
• 외래키 제약조건의 옵션
  • CASCADE
    • ON DELETE CASCADE
      • 부모 레코드 삭제 시 자식 레코드도 연쇄적으로 삭제
    • ON UPDATE CASCADE
      • 부모 레코드의 기본키 수정시 자식 레코드의 외래키 값도 연쇄적으로 수정
  • SET NULL
    • ON DELETE
      • 부모 레코드 삭제시 자식 레코드의 외래키 값이 NULL로 변경
    • ON UPDATE
      • 부모 레코드의 기본키 수정시 자식 레코드의 외래키 값이 NULL로 변경
  • SET DEFAULT
    • ON DELETE
      • 부모 레코드 삭제시 자식 레코드의 외래키 값이 기본값으로 변경
    • ON UPDATE
      • 부모 레코드의 기본키 수정시 자식 레코드의 외래키값이 기본값으로 변경
  • NO ACTION (기본값)
    • ON DELETE
      • 자식 레코드가 있으면 부모 레코드를 삭제할 수 없다.
    • ON UPDATE
      • 자식 레코드가 있으면 부모 레코드의 기본키 값을 수정할 수 없다.

 

데이터 타입

• 문자형 데이터 타입
  • CHAR
    • 고정길이 문자형
    • 지정한 길이보다 데이터 길이가 작으면 빈칸만큼 공백이 들어간다.
  • VARCHAR
    • 가변길이 문자형
    • 데이터의 길이만큼의 메모리 차지
  • TEXT
    • 길이를 지정하지 않는다.
    • 컬럼의 최대 길이를 모를 때 사용
    • TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT
  • JSON
    • JSON 문자열 데이터타입
• 숫자형 데이터 타입
  • 정수형
    • INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT
  • 실수형
    • FLOAT(소수점 아래7자리), DOUBLE(소수점 아래15자리), DECIMAL(고정 소수점)
• 날짜시간형 데이터 타입
  • 날짜
    • DATE
  • 시간
    • TIME
  • 날짜와 시간
    • DATETIME, TIMESTAMP
      • TIMESTAMP는 시간대 적용시켜서 보여주므로 글로벌 서비스에서 사용
• 이진형 데이터 타입
  • BINARY, BLOB
    • 이미지, 사운드, PDF 파일, 멀티미디어 및 기타 바이너리 데이터 저장
    • BLOB은 크기에 따라 TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB 사용
  • BINARY, BYTE
    • CHAR 형태의 이진형 데이터 타입
  • VARBINARY
    • VARCHAR 형태의 이진형 데이터 타입
• 공간형 데이터 타입
  • 공간 데이터를 저장하기 위한 데이터 타입
  • GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCPLLECTION

 

 

09 윈도우 함수 이해 및 실습

• 윈도우 함수 (Window Function)
  • 데이터베이스 쿼리나 데이터 분석에서 주로 사용되는 함수
  • 데이터셋을 그룹화하거나 정렬한 후에 특정 연산 적용하는데 사용
  • 분석 함수 또는 순위 함수라고도 한다.
  • 여기서 윈도우란 연산이 수행되는 데이터셋의 부분집합으로 지정된 분할 기준에 의해서 정의되며 함수가 적용되는 범위를 지정하는 역할을 한다.
• 윈도우 함수 사용
  • 반드시 OVER 절이 필요하다.
  • OVER 절은 윈도우 함수가 작동하는 윈도우를 정의하며 파티션과 순서를 지정할 수 있다.
  • 윈도우 함수의 종류에 따라서 0~n개의 인수(arguments)가 지정될 수 있다.
  • 형식

    • SELECT 윈도우함수명(인수)
      	OVER([PARTITION BY 컬럼] [ORDER BY 컬럼] [WINDOWING절])
      FROM 테이블명;

• 집계 윈도우 함수
  • OVER절을 사용하여 정의하는 집계 윈도우 함수는 PARTITION BY절을 사용하여 그룹화
  • 행의 데이터와 집계된 값을 함께 볼 수 있다.
  • OVER 절
    • 결과를 생성하기 위해 입력으로 고려해야 하는 행의 일부를 OVER절에서 정의
    • 예시
      • AVG(컬럼) OVER() AS 평균
  • PARTITION BY 절
    • 전체 집합을 특정 기준에 의해 소그룹으로 나누고자 할 때 OVER 절 내에서 PARTITION BY 절 사용
    • 예시
      • 컬럼 - AVG(컬럼) OVER(PARTITION BY 컬럼2) AS 차이
  • ORDER BY 절
    • OVER 절에 정렬기준 지정
    • 예시
      • SUM(컬럼) OVER(ORDER BY 컬럼2) AS 누적합
• 순위 윈도우 함수
  • 행 간의 값을 비교하여 순위를 매기는 함수
  • 종류
    • RANK()
      • 순위 반환, 동일 순위에 동일한 숫자 값 반환 후 그 다음 순위는 해당 동일 수만큼 더해짐
    • DENSE_RANK()
      • 순위 반환, 동일 순서에 동일한 숫자 값 반환 후 그 다음 순위는 + 1
    • ROW_NUMBER()
      • 순위 반환, 모든 행에 순차적으로 번호 부여
    • PERCENT_RANK()
      • 백분율 순위 값, 0~1 사이의 값 부여
    • CUME_DIST()
      • 누적 분포 계산 (백분율)
    • NTILE(n)
      • 지정된 수의 그룹으로 수로 나눠서 해당 행이 속한 그룹 번호 반환
• 행 순서 윈도우 함수
  • 특정 행의 위치에 있는 값을 가져올 때 사용
  • 종류
    • FIRST_VALUE()
      • 정렬된 값 집합의 첫번째 값 반환
    • LAST_VALUE()
      • 정렬된 값 집합의 마지막 값 반환
    • LAG()
      • 명시된 값을 기준으로 이전 행 값 반환
    • LEAD()
      • 명시된 값을 기준으로 이후 행 값 반환
    • NTH_VALUE(n)
      • 윈도우의 n번째 행 값 반환

 

 

10 데이터 모델링 이해 및 실습

• 데이터 모델
  • 현실 세계를 추상화하여 데이터 구조와 관계를 시각적으로 표현한 것
  • 데이터베이스 시스템에서 사용되는 데이터의 논리적 구조 정의
• 데이터 모델링
  • 현실 세계의 데이터를 추상화하여 데이터 모델로 변환하는 작업
• 데이터 모델링 프로세스
  1. 요구사항 분석
  2. 개념적 데이터 모델링
  3. 논리적 데이터 모델링
  4. 물리적 데이터 모델링
  5. 구현 및 유지보수
  6. 검증 및 최적화
  7. 문서화
• 데이터 모델의 종류
  • 계층적, 네트워크, 개체-관계형, 관계형, 차원, 객체지향, 그래프
• 속성 (Attribute)
  • 엔티티 집합에 포함되는 최소의 데이터 단위
  • 각각 속성에 대해 단일 속성값을 가져야 한다.
• 식별자 (Identifier)
  • 하나의 엔티티 집합에서 각 엔티티를 구분할 수 있는 속성이나 속성의 집합
  • 엔티티 집합은 반드시 식별자를 가져야 한다.
• 관계 (Relationship)
  • 엔티티 간의 관련성을 의미 하는 것으로 존재 관계와 행위 관계로 구분 가능
  • 존재 관계
    • 두 엔티티간의 소속이나 소유의 관계
  • 행위 관계
    • 두 엔티티간의 상호작용이나 동작의 관계
• 관계 도출
  • 관계의 카디널리티
    • 일대일, 일대다, 다대일, 다대다 관계
  • 관계 이름
    • 객체 간의 관계가 맺어지는 형태
  • 관계 선택 사양
    • 선택적 참여 관계, 필수적 참여 관계