[엘리스 AI트랙] 06-04 SQL 2

1. 데이터 그룹 짓기 - GROUP BY

SELECT user_id, COUNT(*)
FROM rental
GROUP BY user_id; # 그룹의 기준 컬럼

• 보통 limit, order by, group by가 SQL문의 맨 끝에 옴

 

---

2. 데이터 그룹(GROUP BY절)에 조건 적용하기 - HAVING

# rental 테이블에서 user_id가 같은 2개 이상의 데이터가 몇 개 있는지 검색

SELECT user_id, COUNT(*) # GROUP BY의 기준 컬럼 및 검색할 컬럼
FROM rental
GROUP BY user_id # 그룹의 기준 컬럼
HAVING COUNT(user_id) > 1; # 조건

 

---

3. 여러 테이블의 정보를 한번에 조회하기 - JOIN

 

1) INNER JOIN문의 기본 문법

SELECT *
FROM rental
INNER JOIN user; # 연결할 테이블, rental table + user table

 

2) JOIN에 조건을 적용하고 싶다면?

SELECT *
FROM rental
INNER JOIN user
ON user.id = rental.user_id
# user table의 id 컬럼과 rental 테이블의 user_id 컬럼이 같다면 연결

 

3) LEFT JOIN의 기본 문법

SELECT *
FROM user # 두 테이블 중 더 중요한 쪽
LEFT JOIN rental
ON user.id = rental.user_id
# user 테이블은 모두 출력됨
# 모든 user 테이블의 user_id와 rental 테이블의 id가 겹치도록 JOIN

 

4) RIGHT JOIN문의 기본 문법

SELECT *
FROM user
RIGHT JOIN rental # 두 테이블 중 더 중요한 쪽
ON user.id = rental.user_id

 

 

---

3. 서브쿼리

• 하나의 쿼리 안에 포함된 또 하나의 쿼리
• 메인 쿼리가 서브쿼리를 포함하는 종속적인 관계
• 한 문장에서 여러번 사용 가능

 

서브쿼리를 사용할 때의 주의사항

• 서브쿼리는 괄호와 함께 사용되어야 함
• 서브쿼리 안에서 ORDER BY 절은 사용할 수 없음
• 서브쿼리는 연산자의 오른쪽에 사용되어야 함
• 서브쿼리는 오로지 SELECT문으로만 작성할 수 있음

기본 문법

SELECT * FROM employee
WHERE 급여 > (SELECT 급여 FROM employee WHERE 이름='elice')

 

단일 행 서브쿼리

• 결과가 한 행만 나오는 서브쿼리
• 서브쿼리가 결과를 1개의 값만 반환하고, 이 결과를 메인쿼리로 전달하는 쿼리
• 사용되는 연산자: = , <>(같지 않다), >, >=, <, <= 

SELECT * FROM employee
WHERE 급여 > (SELECT 급여 FROM employee WHERE 사원번호 = 1)
# 이 경우 사원번호는 기본적으로 한 개만 있으므로 1개의 행만 반환함 = 단일 행

 

다중 행 서브 쿼리

• 서브쿼리가 결과를 2개 이상 반환하고, 이 결과를 메인쿼리로 전달하는 쿼리

SELECT * FROM employee
WHERE 급여 IN ( #IN -> 다중 행 연산자
SELECT max(급여) FROM employee GROUP BY 부서번호);

기호	뜻
IN	하나라도 만족하면 반환
ANY	하나라도 만족하면 반환 비교 연산 가능 내부적으로 모두 or 연산을 함
ALL	모두 만족하면 반환 비교 연산 가능 내부적으로 모두 and 연산을 함

 

예시

1 in (1, 2, 3, 4) # True
10 < any (1, 2, 3, 4) # False
99 >= all (99, 100, 101) # False

 

스칼라 서브쿼리

• SELECT 절에서 사용하는 서브쿼리
• 오직 한 행만 반환
• JOIN을 사용한 것과 같은 결과를 얻을 수 있음
• 데이터가 많을 때에는 JOIN을 사용하는 것보다 스칼라 서브쿼리를 사용하는 것이 더 빠르게 결과 반환 가능

예시

SELECT students.name, (
	SELECT math
    FROM middle_test as m
    WHERE m.student_id = students.student_id
) AS middle_avg
FROM students;

 

---

4. 데이터베이스

• 공유하여 사용할 목적으로 체계화하여 관리하는 데이터의 집합

관계형 데이터베이스(RDB)	비관계형 데이터베이스(NoSQL)
각 테이블 간 관계 O	각 테이블 간 관계 X
스키마 O	스키마 X
구조적으로 안정적이나 유연하지 못함	유연성을 가지고 있으나 구조 결정이 어려움

 

데이터 모델링이란?

• 효율적으로 데이터베이스를 구축하기 위해 데이터베이스의 뼈대를 세우는 작업

 

데이터 모델링 순서

1) 요구 사항 정리
데이터가 어떠한 형태, 어떤 목적을 가지고 있는지 정리
2) 개념적 데이터 모델 설계
핵심 개체를 찾고, 각 개체 간의 관계를 정리
3) 논리적 데이터 모델 설계
실제 데이터베이스로 구현하기 위한 모델링
4) 물리적 데이터 모델 설계
데이터가 어떻게 컴퓨터에 저장될 지 설계

 

데이터베이스 네이밍 규칙

유지보수와 의미의 명확성을 전달하기 위해 네이밍 규칙이 있어야함

• 줄임말 사용은 최소화
  • 단, 사용할 경우 범용적으로 사용되는 줄임말을 사용 (ex: info)
• 테이블 명을 작성할 때는 파스칼 표기법(첫 단어 시작부터 대문자로 시작)
• 테이블, 뷰(View)임을 한 눈에 확인 할 수 있도록 테이블 명 앞에는 'tb_', 'v_'를 붙임

 

속성 네이밍 규칙

• 속성 명을 작성할 때는 스네이크 표기법 활용
• 유일키, 외래키 등은 한 눈에 알아 볼 수 있도록 속성명 앞에 'pk_', 'fk_'를 붙임
  • 중복으로 설정되어 있는 경우 외래키 > 유일키 

 

---

5. 키(Key)

• 데이터베이스에서 튜플(레코드, 행) 간 구분을 위한 값(속성)
  • 유일키, 복합키, 외래키 ...
  • 테이블 명, 속성 명, 유일키로 사용할 속성 명을 감싸줄 때는 `(백틱) 활용

 

1) 유일키(Primary Key)

• 테이블에서 튜플(레코드, 행)을 구분지어주는 고유 식별 값(속성)
• 유일키 값은 중복될 수 없으며, NULL값을 가질 수 없음

 

유일키(Primary Key) 선언하기

CREATE TABLE '테이블 명' {
    `속성명` 타입,
    `속성명2` 타입,
    ...
    PRIMARY KEY(`유일키로 사용할 속성명`)
}

 

2) 복합키(Composite Key)

• 두 개 이상의 속성을 활용하여 튜플(레코드, 행)을 구분지어 주는 방법

 

복합키(Composite Key) 선언하기

CREATE TABLE '테이블 명' {
    `속성명` 타입,
    `속성명2` 타입,
    ...
    PRIMARY KEY(`복합키로 사용할 속성명`, `복합키로 사용할 속성명`)
}

 

3) 외래키(Foreign Key)

• 다른 테이블의 키와 연결을 해주는 키
• 참조의 무결성^[각주:1]을 유지하기 위해 활용

 

외래키(Foreign Key) 선언하기

CREATE TABLE '테이블 명' {
    `속성명` 타입,
    `속성명2` 타입,
    ...
    FOREIGN KEY(`외래키로 사용할 속성명`) REFERENCES `참조하는 속성이 있는 테이블`(`참조할 속성명`)
}

 

---

6. View (뷰)

• 하나 이상의 테이블에서 여러 정보를 토대로 만들어지는 가상의 테이블
• 필요한 정보만 추출해서 제공함으로써 보안성과 속도를 높일 수 있음
• 실제 존재하지 않는 가상의 테이블이기 때문에 View를 삭제하더라도 원본 테이블에는 영향 X

 

뷰(View) 생성 방법

CREATE VIEW '테이블 명' AS
SELECT 가져오고자 할 속성명, 속성명2, ...
FROM 가져오고자 할 속성이 있는 테이블, 테이블2 ...
(WHERE 등 사용 가능)

 

뷰(View) 삭제 방법

DROP VIEW 삭제하고자 하는 View명

 

1. 외래키 값이 참조 중인 테이블의 값과 일관성을 가지는 것을 의미 [본문으로]