MYSQL 기초 핵심 정리

# 데이터 모델러

  도메인 지식(Domain Knowledge) + 데이터 모델링 지식 + 데이터베이스 지식

4.2.2 데이터베이스 모델링 실습

 

# 데이터베이스 모델링 단계

  1) 개념적 모델링  ⇒  2) 논리적 모델링       ⇒ 3) 물리적 모델링

     (업무 분석)  (업무분석 후반부, 설계 전반부)  (설계 후반부, 구현/구축/빌드)

     도메인 지식            ERD                  데이터베이스 개체 (SQL사용)

# 1:1, 1:N, M:N ⇒ M:1 1:N 

 

 M:N ⇒ 고객 : 물건 ⇒ 고객 : 주문 : 물건

 

# 정규화(Normalization)

  - 데이터 중복을 최소화하고 데이터 무결성을 확보하기 위한 모델링 방법

  - 컬럼이 있어야 할 위치를 찾아가는 과정

 

1) 제1정규화

  : Single Valued Field ⇒ 다중값 제거

  : 다중값이 있는 컬럼들을 모아서 새로운 테이블로 보냄

  ⇒ 제1정규형(1st Normal Form)

     : 데이터 중복이 없는 테이블

 

2) 제2정규화

  : 모든 행은 PK에 의해 식별되어야 한다.

    모든 컬럼은 PK에 종속되어야 한다.

  : PK에 의해 식별되지 않는 컬럼을 모아서 분리

  ⇒ 제2정규형

     : 데이터 중복이 없으면서 PK에 의해 행이 식별되는 테이블

3) 제3정규화

  : PK가 아닌 컬럼에 의해 식별되는 컬럼이 있어서는 안된다.

    일반 컬럼에 의해 식별되는 컬럼을 찾음

  : 일반 컬럼에 식별되는 컬럼과 해당 일반 컬럼을 분리

  ⇒ 제3정규형

    : 데이터 중복이 없고 PK에 의해서만 행이 식별되는 테이블

  * 더 많은 정규형(BCNF, 제4정규형 등)가 있지만 일반적으로 모든 테이블이 제3정규형이면 정규화가 완성되었다고 판단

# 대부분의 RDBMS가 기본적으로 제공하는 제약 조건 5 가지

  1) PK

  2) UK

  3) FK

  4) CK

  5) NN

# 기본 5 가지 제약 조건으로 해결할 수 없는 제약 조건이 있다면 트리거를 이용

  ⇒ 트리거의 또다른 용도: 사용자 정의 제약 조건

# ERD(Entity-Relationship Diagram)

  - 데이터 모델링의 결과물, 설계도

  - ERD를 기반으로 데이터베이스 빌드/구축

# Forward Engineering

  : 설계도 → 제품

 

# Reverse Engineering (역공학)

  : 제품 → 설계도

# 서브쿼리의 종류

  * 실행순서에 따라

    1) 중첩서브쿼리(Nested SQ)

       - 서브쿼리가 먼저 한 번 수행

       - 서브쿼리의 실행 결과를 메인 쿼리에서 활용

       ⇒ 서브쿼리는 단 한 번만 실행

    2) 상호관련서브쿼리(상관서브쿼리, Correlated SQ)

       - 메인쿼리에서 후보행 선택

       - 서브쿼리에서 후보행의 데이터를 이용하여 실행

       - 서브쿼리의 실행 결과를 메인 쿼리에서 활용

       ⇒ 서브쿼리가 후보행 개수만큼 실행

    * 중첩서브쿼리와 상호관련서브쿼리의 구별

      ⇒ 서브쿼리의 WHERE 절에 조인조건이 있으면 상호관련서브쿼리

    

    * 조인조건인지 판별방법

      ⇒ 서브쿼리의 WHERE 절에서 내부 테이블과 외부 테이블의 컬럼들이 비교될 때

 

  * 단일행 서브쿼리 vs. 다중행 서브쿼리

     ⇒ 이에 따라 연산자가 달라지기 때문에 중요

  * 단일 컬럼 서브쿼리 vs. 다중 컬럼 서브쿼리

  * 스칼라 서브쿼리

  * 인라인 뷰

<2021/08/20>

 

# ORDER BY

  * 오름차순, 내림차순

  * 정렬 기준이 여러개면 콤마로 구분

  * 정수 → column position in select-list

  * NULL 값을 뒤로: 컬럼 IS NULL, 컬럼

# Top-N 문제

 

# CREATE TABLE … SELECT

  - AS 예약어 생략 가능

  - 서브 쿼리를 감싸는 괄호 생략 가능

  - 서브 쿼리의 항상 거짓인 WHERE 절 → 어떤 경우에 사용?

  - 컬럼 이름을 바꾸고 싶을 때: ????

  - 새로운 컬럼을 추가: ???

6.1.3 GROUP BY 및 HAVING 그리고 집계 함수

 

# 집계 함수(Aggregate function, Group function, Multiple-Row function)

  - 여러 데이터를 모아서 한 건 반환(a result per group) 

     <-> 단일행 함수 (a result per row)

  - NULL 값은 무시

    예외) COUNT(*)

 

CREATE TABLE test

    SELECT 1000 AS col1

    UNION ALL SELECT 2000

    UNION ALL SELECT NULL

    UNION ALL SELECT NULL

    UNION ALL SELECT 2000

    UNION ALL SELECT 1000;

    

SELECT * FROM test;

6.1.4 SQL의 분류

 

  # DML

  # DDL
  # DCL

6.2 데이터의 변경을 위한 SQL 문

 

6.2.1 INSERT

 

6.2.2 UPDATE

 

6.2.3 DELETE

 

# 대용량의 테이블 삭제

                             시간       테이블존치   

  방법 1) DELETE FROM       오~~~래         O          

  방법 2) DROP TABLE          빨리          X          

  방법 3) TRUNCATE TABLE      빨리          O     

6.2.4 조건부 데이터 입력, 변경

chapter 07 SQL 고급

7.1 MySQL의 데이터 형식

 

7.1.1 MySQL에서 지원하는 데이터 형식의 종류

 

# 숫자 데이터 형식

 

# 문자 데이터 형식

 

# 날짜와 시간 데이터 형식

7.1.2 변수의 사용

7.1.3 데이터 형식과 형 변환

 

# 암시적 형 변환

  : Implicit vs. Explicit

    암시적   vs. 명시적

  ⇒ Automatic vs. Manual

7.1.4 MySQL 내장 함수

 

# CASE expr. ⇒ 값

  1) Simple CASE expr.

  2) Searched CASE expr.

 

# CASE 표현식의 활용 예

SELECT name, height, addr

FROM usertbl

ORDER BY CASE addr WHEN '서울' THEN 1

            WHEN '경기' THEN 2

                   WHEN '경북' THEN 3

                   WHEN '경남' THEN 4

          ELSE 10

        END;

7.2 조인

 

# 둘 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어내는 것

 

# 둘 이상의 재료집합으로부터 새로운 재료집합을 만드는 연산

 

# 조인 조건

  : WHERE 절 또는 ON 절

  : Predicate

 

# 조인의 종류

  * 조인 메커니즘에 따라

    1) (내부) 조인(Inner Join)

      : 정리하세요~~

    2) 외부 조인(Outer Join)

      : 정리하세요~~

    3) 무조건 조인(Cross Join)

  * 자기 자신과의 조인

    - 셀프 조인(Self Join)

      → 테이블 별칭이 반드시 필요

  * 동등 조인 조건

    - 동등 조인(Equi-Join)

 

# EXISTS, NOT EXISTS 표현식    WHERE EXISTS (상호관련SQ)

  - 오른편에 반드시 상호관련서브쿼리

 

  - 조인 조건을 만족하는 결과가 나오면 

    ⇒ EXISTS: TRUE를 리턴하고 다음 후보행으로

    ⇒ NOT EXISTS: FALSE를 리턴하고 다음 후보행으로

  - 서브쿼리의 모든 행에 대해서 조인 조건을 만족하는 행이 하나도 없다면

    ⇒ EXISTS: FALSE를 리턴하고 다음 후보행으로

    ⇒ NOT EXISTS: TRUE를 리턴하고 다음 후보행으로

 

  - EXISTS: Semi-Join

  - NOT EXISTS: Anti-Join

# MySQL의 Transaction

  : https://dev.mysql.com/doc/refman/8.0/en/commit.html
  * 기본적으로 MySQL은 자동 커밋 모드가 활성화

    ⇒ SQL 문장 하나 하나가 트랜잭션

 

# MySQL에서 Autocommit 비활성화

  1) 자동 비활성화

   - START TRANSACTION 문

  2) @@autocommit 속성

-- 자동 커밋 설정 확인

SELECT @@autocommit;

 

-- 자동 커밋 비활성화

SET autocommit = FALSE;

 

-- 자동 커밋 활성화

SET autocommit = TRUE;

 

chapter 15 파이썬과 MySQL 응용 프로그래밍

15.2.8. 윈도 프로그래밍

 

  # 내장 모듈 tkinter

 

  # 외부 패키지: PyQT

15.3 파이썬과 MySQL 연동

 

15.3.1 파이썬에 MySQL 활용

 

# pymysql 패키지 설치

 

# MySQL에 데이터 입력을 위한 파이썬 코딩 순서

  * p.648 그림 15-40 참조

  1) 데이터베이스 연결

  2) 커서 생성

  3) 테이블 만들기

  4) 데이터 입력

  5) 입력한 데이터 저장

  6) MySQL 연결 종료

 

# MySQL의 데이터 조회를 위한 파이썬 코딩 순서

  * p.652 그림 15-41 참조

  1)