[8주차 학습일지] 패스트캠퍼스 박두진 강사님 SQL 강의 (2)

데이터베이스 모델링

• 데이터베이스 테이블의 구조와 관계를 만들어 주는 방법
• 절차 : 개념적 → 논리적 → 물리적 모델링
• EER 다이어그램 : 물리적 모델링 구현 → 데이터베이스(테이블) 생성
  
  • 테이블 사이의 관계를 확인 → JOIN을 이용하여 데이터의 관계로 새로운 인사이트를 얻을 때

FOREIGN KEY (외래키)

• FOREIGN KEY : 외래키 : 제약조건
  • 테이블 사이의 관계를 형성할 때 외래키(FOREIGN KEY)를 설정하면 데이터의 무결성(원하지 않는 데이터가 들어가지 않도록함)을 지킬수 있음
  • 컬럼 단위로 설정할수 있으며 UNIQUE나 PRIMARY 제약조건이 있어야 설정 가능
• 외래키(FOREIGN KEY) 설정
  • SET UPDATE, DELETE 설정시 무결성이 깨지는 경우에 동작을 정의
  • 외래키는 참조하는 컬럼과 참조되는 컬럼 사이에 다양한 설정들을 하여 데이터를 처리할수 있습니다.
  • CASCADE : 외래키로 설정된 두 컬럼의 데이터를 동기화
  • SET NULL : 참조 되는 컬럼을 삭제, 수정하면 참조 하는 컬럼을 NULL 데이터로 변경(데이터의 유실을 인지하기 위해)
  • SET DEFAULT : 결측데이터 → 참조 되는 컬럼을 삭제, 수정하면 참조 하는 컬럼을 DEFAULT 데이터로 변경
  • RESTRICT : (기본설정) : 외래키로 설정된 두 컬럼의 데이터를 삭제 수정하면 에러가 발생됨
• UPDATE → CASCADE
  • 뜻 : 부모 테이블의 기본 키 값이 변경되면, 자식 테이블의 외래 키 값도 자동으로 같이 변경
  • 사용 이유 : 참조 무결성을 유지하면서, 키 값 변경 시 수동으로 자식 테이블을 업데이트할 필요가 없음
  • 주의 사항 : CASCADE는 데이터가 연쇄적으로 변경되므로, 불필요한 데이터 변동을 방지하려면 신중하게 써야 함
• DELETE → SET NULL
  • 뜻 : 부모 테이블의 행이 삭제되면, 자식 테이블의 해당 외래 키 값을 NULL로 설정
  • 사용 이유 : 부모 데이터가 없어졌을 때, 자식 데이터를 아예 지우지 않고 "연결 없음" 상태로 두고 싶을 때
  • 주의 사항 : SET NULL을 쓰려면, 외래 키 컬럼이 NULL 허용이어야 함

FUNCTION (함수)

• 미리 만들어 놓은 특별한 기능을 사용하는 방법
• 단일행 함수 : 특별한 기능을 하나의 데이터에 적용하여 출력
  • ex) ROUND( ), CEIL( ), TRUNCATE( ), CONCAT( ), DATE_FORMAT( ) …
  • 소수 2번째 자리에서 올림하여 소수 1번째 자리까지 출력
    • CEIL( ) 자리수 설정 불가
    • SELECT CEIL(12.345 * 10) / 10;
  • ROUDN() : 자리수를 설정해도되고 안해도 됨
  • TRUNCATE() : 반드시 자리수를 설정해야 함
  • CONCAT() : 문자열을 결합하여 결과 출력
  • DATE_FORMAT( )
    • 날짜 데이터의 포맷을 변환할 때 사용
    • https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format
• 다중행(결합, 집계) 함수 : 특별한 기능을 여러개의 데이터에 적용하여 출력
  • ex) SUM( ), AVG( ), COUNT( ), MIN( ), MAX( ), VAR( ), MEDIAN( ) …
  • COUNT( ) : 결측 데이터는 제외하고 데이터의 갯수 출력
• FUNCTION은 데이터(스칼라, 벡터, 매트릭스, 텐서)를 입력받아 변환·계산하는 ‘수학적 규칙’ 또는 프로그래밍 함수
  • 선형대수용어 : 스칼라, 백터, 매트릭스, 텐서용어 설명 예시

    용어	설명	예시
    스칼라(Scalar)	하나의 숫자 값 (크기만 있고 방향 없음)	5, -3.2
    벡터(Vector)	1차원 데이터 (크기 + 방향)	[1, 2, 3]
    매트릭스(Matrix)	2차원 데이터 (행 × 열)	[ 1 2   3 4 ]
    텐서(Tensor)	3차원 이상 다차원 데이터	이미지 데이터 (채널×높이×너비)

  • 같은 모양 : 같은 위치의 데이터끼리 연산
  • 다른 모양 : 브로드 캐스트하게 연산

중복 제거

• UNION : 두개의 쿼리 실행 결과를 결합후 중복을 제거해서 출력
• UNION ALL : 두개의 쿼리 실행 결과를 결합후 중복을 제거하지 않고 출력
• DISTINCT : SELECT 구문에서 사용 중복제거하여 출력 (여러개의 컬럼 중복제거도 가능)
  

  구분	UNION	DISTINCT
  적용 대상	두 개 이상 SELECT 결과	한 개 SELECT 결과
  중복 제거	기본적으로 함	함
  조건	컬럼 개수·데이터 타입 동일해야 함	컬럼 개수·타입 제약 없음
  변형 옵션	UNION ALL로 중복 유지 가능	없음
  사용 목적	여러 쿼리 결과 합치기	하나의 쿼리에서 고유 행 추출

 

CONDITION (조건문)

• 특정 조건에 따라 출력되는 결과를 다르게
• 기준이 되는 조건이 1개인 경우 : IF(condition, true, false)
• 기준이 되는 조건이 2개 이상인 경우 : CASE WHEN <condition1> THEN <data1> ELSE <data2> END
• 결측 데이터 처리하는 경우 : IFNULL(column, data)
• ORDER BY 원하는 순서대로 출력하고 싶을 때
  • CASE WHEN 사용

ORDER BY CASE WHEN embarked = 'S' THEN 1 WHEN embarked = 'C' THEN 2 ELSE 3 END;

WHERE embarked IS NOT NULL ORDER BY 
CASE embarked WHEN 'S' THEN 1 
              WHEN 'C' THEN 2
              WHEN 'Q' THEN 3 
              END ASC;

 

 

백업 테이블 생성

# 백업테이블 생성
CREATE TABLE survived(
	passengerid INT PRIMARY KEY
    , pclass INT NOT NULL
    , age INT
    , embarked CHAR(1)
);
DESC survived;

# 데이터 백업
INSERT INTO survived
SELECT passengerid, pclass, age, embarked
FROM titanic
WHERE survived = 1;
# 데이터 백업 확인
SELECT * FROM survived;

 

GROUP BY (데이터 그룹핑)

• 특정 컬럼을 기준으로 중복되는 데이터를 결합하여 통계치를 출력하는 방법
• 중복결합, 결합함수결합(다중행 함수)
• 쿼리 작성 순서 : SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY > LIMIT
• 쿼리 실행 순서 : FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT
  • MySQL에서는 코드의 가독성, 유지보수 향상을 위해 SELECT 구문에서 생성된 컬럼을 GROUP BY, HAVING에서 사용 가능하도록 되어 있음(Oracle에서는 안됨)
  • WHERE에서 먼저 필터링하고 그룹핑하는게 HAVING에서 필터링하는 것보다 훨씬 빠름(그룹핑할 데이터가 줄어듦)

JOIN : 데이터 결합

• 두개의 테이블을 특정 컬럼을 기준으로 결합하여 데이터 출력
• JOIN, ON
• T1(C1, C2) + T2(C1, C3) = C1, C2, C3
• INNER, LEFT, RIGHT, OUTER, SELF, CROSS
• JOIN을 잘못 사용하면 시스템에 과부화를 줄수 있음
  • 문제해결방법
    • 서브쿼리를 이용하여 결합 데이터의 양을 줄임
    • INDEX 사용하여 쿼리 조회 속도를 빠르게 함
    • 서버 컴퓨터의 성능을 높임(돈이 필요)

• CROSS JOIN
  • CROSS JOIN은 모든 행을 곱집합(Cartesian product) 으로 결합
    → 조건 없이 조인할 때 사용

SELECT *
FROM user
JOIN addr;

• INNER JOIN
  • SQL에서 JOIN만 쓰면 기본값이 INNER JOIN

# INNER
SELECT user.ui, user.un, addr.an
FROM user
INNER JOIN addr on user.ui = addr.ui;
# 간결하게 코드작성 (INNER JOIN에서만 가능)
SELECT u.ui, u.un, a.an
FROM user u, addr a
WHERE u.ui = a.ui;

• 작성순서 : SELECT > FROM > JOIN > WHERE(ON) > GROUP BY > HAVING > ORDER BY > LIMIT
• 실행순서 : FROM > JOIN > WHERE(ON) > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT

 

• GROUP_CONCAT(여러개의 문자를 ,로 결합해줌)

SELECT f.film_id, f.title
	 , GROUP_CONCAT(CONCAT(a.first_name, " ", a.last_name)) AS full_name
FROM film f
JOIN film_actor fa
ON f.film_id = fa.film_id
JOIN actor a
ON fa.actor_id = a.actor_id
GROUP BY f.film_id;

 

SUB QUERY (서브 쿼리)

• 쿼리 안에 쿼리를 작성하는 방법
• 문제 : SQL 코드의 복잡도 증가 → 복잡한 쿼리를 조금 단순화 하는 방법 : VIEW, WITH
• SELECT, FROM(JOIN), WHERE(ON, HAVING)

SELECT 
(SELECT COUNT(*) FROM country) AS tco,
(SELECT COUNT(*) FROM city) AS tci,
(SELECT COUNT(DISTINCT language) FROM countrylanguage) AS tl
FROM DUAL;

• FROM 구문 서브쿼리
  • 쿼리 실행 결과를 다시 쿼리 실행하여 출력할때
  • 쿼리의 실행순서를 변경하여 쿼리 실행
• UNION을 사용할때는 ORDER BY, LIMIT 명령어 아래에 사용 X

SELECT countrycode, name, population 
FROM (
	SELECT countrycode, name, population
	FROM city
	ORDER BY population DESC
	LIMIT 3) AS sub1
UNION
SELECT countrycode, name, population
FROM (
	SELECT countrycode, name, population
	FROM city
	ORDER BY population ASC
	LIMIT 3) AS sub2;

• 서브쿼리 작성 > 쿼리의 복잡성 증가
  1. VIEW : 가상의 테이블 사용
  2. WITH : 중복되는 특정 쿼리를 위에 빼서 사용

VIEW (가상 테이블)

• 실제 데이터를 저장하지 않고 주소값만 저장
• 데이터의 추가, 수정, 삭제를 할수 X
  • READ만 가능 : CREATE, UPDATE, DELETE 불가
• 다른 팀에 READ 권한만 부여하고 싶을때도 사용가능
• VIEW 생성
  • CREATE VIEW <view_name> AS ( <SQL code> )

CREATE VIEW city800 AS 
SELECT * 
FROM city 
WHERE population >= 800*10000;

 

WITH

• 쿼리 실행전에 미리 실행할 임시쿼리를 작성하여 원래 쿼리를 간결하게 작성하는 방법
• 중복 서브쿼리나 복잡한 서브쿼리를 쿼리 시작부분에서 설정하여 쿼리를 간단하게 만드는 방법
• MySQL은 8.0 버전부터 사용 가능
• 여러개의 WITH 설정 가능
• WITH 예시
  • WITH <with_name> AS (<SQL code>)

WITH city_cte AS (
	SELECT * 
    FROM city 
    WHERE population >= 800*10000
)
SELECT co.code as co_code, co.name as co_name
	 , ci.name as ci_name
     , ci.population
FROM  country co
JOIN city_cte as ci
ON co.code = ci.countrycode;