SQL 스터디 노트 - 심화

Backup

Backup 파일 생성

데이터베이스 혹은 테이블을 백업

[사용 예시]
mysqldump -u username -p database > backup_filename.sql

• database를 backup_filename.sql로 백업

mysqldump -d -u username -p table > backup_filename.sql

• Scheme backup, 테이블을 백업

# 백업 결과물

Backup 파일 불러오기 (Restore)

[사용 예시]
source backup_filename.sql

• 한글 깨짐 현상이 발생하는 경우, mysql 실행 시 --default-character-set=utf8mb4 옵션을 추가 후 실행
• mysql --default-character-set=utf8mb4 -h "엔드포인트" -P "포트" -u "유저명" -p

# 백업 파일을 아마존 RDS에 적용한 결과

Primary Key

• 테이블의 각 레코드를 식별
• 중복되지 않은 고유값을 포함
• NULL 값을 포함할 수 없음 (KEY로 지정할 column 생성 시 not null 옵션을 지정해주어야함)
• 테이블 당 하나의 기본 키를 가짐

Primary Key 생성

Table 생성 단계에서 Primary Key를 지정

[사용 예시]
CREATE TABLE tablename (columnname columntype PRIMARY KEY)
CREATE TABLE tablename (column1 type, column2 type, ... PRIMARY KEY (column1))

Primary Key 추가

이미 생성된 Table을 수정, Primary Key를 새로 지정

[사용 예시]
ALTER TABLE tablename ADD PRIMARY KEY (column1, column2, ...)

Primary Key 제거

[사용 예시]
ALTER TABLE tablename DROP PRIMARY KEY

Foreign Key

• 한 테이블을 다른 테이블과 연결해주는 역할, 해당 테이블의 기본키를 참조
• 참조되는 테이블의 항목은 그 테이블의 기본키 (혹은 단일값)
• 여러 테이블을 참조할 수 있으므로 여러개 존재 가능

Foreign Key 생성

생성 시 Constraint 명령어를 통해 Foreign Key의 이름을 지정 가능하며
생략할 경우 자동으로 Foreign Key의 이름이 부여됨 (SHOW CREATE TABLE 명령어로 Foreign Key 이름 확인 가능)

[사용 예시]
FOREIGN KEY (column) REFERENCE ref_tablename(ref_column)

Foreign Key 삭제

Foreign Key는 여러 개 존재할 수 있으므로 삭제할 이름을 지정해주어야 함

[사용 예시]
ALTER TABLE tablename DROP FOREIGN KEY foreignkeyname

Aggregate Functions (집계 함수)

여러 컬럼 혹은 테이블 전체 컬럼으로부터 하나의 결과값을 반환하는 함수

COUNT

해당하는 값의 개수를 반환

[사용 예시]
SELECT COUNT(column) FROM tablename

SUM

전체 데이터의 합계를 반환

[사용 예시]
SELECT SUM(column) FROM tablename

AVG

전체 데이터의 평균값을 반환

[사용 예시]
SELECT AVG(column) FROM tablename

MIN

가장 작은 데이터 값을 반환

[사용 예시]
SELECT MIN(column) FROM tablename

MAX

가장 큰 데이터 값을 반환

[사용 예시]
SELECT MAX(column) FROM tablename

Group By

그룹화하여 데이터 조회
Distinct와 유사하나, Order by 명령어를 사용할 수 없음

[사용 예시]
SELECT column1, column2, ... FROM tablename GROUP BY column1, column2, ...

Having

조건에 집계함수가 포함되는 경우 WHERE 대신 사용하는 구문

[사용 예시]
SELECT FROM tablename WHERE HAVING condition (Aggregate Functions)

Scalar Functions

입력값을 기준으로 단일 값을 반환하는 함수

UCASE, LCASE

영문을 대문자/소문자로 변환하는 함수

[사용 예시]
SELECT UCASE(string) FROM tablename
SELECT UCASE(string) FROM tablename

MID

문자열 부분을 반환하는 함수

[사용 예시]
SELECT MID(string, start_position, length) FROM tablename

• MID(원본문자열, 문자열 반환 시작 위치, 반환할 문자열 길이) # 문자열 반환 시작 위치를 음수로 줄 경우, 뒤에서부터 셈

LENGTH

문자열의 길이를 반환하는 함수, Python의 len() 함수와 동일

[사용 예시]
SELECT LENGTH(string) FROM tablename

• 공백도 문자이므로 길이에 포함

ROUND

지정한 자리에서 숫자를 반올림하는 함수 (0이 소수점 첫째 짜리)

[사용 예시]
SELECT ROUND(number, decimals_place(반올림할 소수점 위치)) FROM tablename

• 소수점 위치를 지정하지 않을 경우 소수점 첫번째 자리(0번)에서 반올림, 음수의 경우 일/십/백단위로 증가

NOW

현재 날짜 및 시간을 반환하는 함수

[사용 예시]
SELECT NOW()

FORMAT

숫자를 천단위 콤마가 있는 형식으로 반환하는 함수

[사용 예시]
SELECT FORMAT(number, decimal_place) FROM tablename

• 표시할 소수점 위치 지정, 디폴트 0 (소수점 두자리 표기할 경우 2)

SQL Subquery

하나의 SQL 문에 포함되어 있는 또다른 SQL문, 메인쿼리가 서브쿼리를 포함하는 종속적인 관계
서브쿼리는 메인쿼리의 칼럼을 사용 가능하나, 메인 쿼리는 서브 쿼리의 칼럼을 사용 불가

Subquery는 괄호로 묶어서 사용
단일 행 혹은 복수 행 비교 연산자와 함꼐 사용 가능하나, order by를 사용 불가

Scalar Subquery

SELECT 절에 사용하는 Subquery, 결과값이 반드시 하나의 Column이어야 함

[사용 예시]
SELECT column1, (SELECT column FROM tablename HERE condition) ...

Inline Subquery

FROM 절에 사용하는 Subquery, 결과값이 복수의 Column(테이블) 형태여야함

[사용 예시]
SELECT column1, column2 FROM (SELECT column FROM tablename WHERE condition) ...

Nested Subquery

WHERE 절에 사용하는 Subquery, 단일열/복수열/복수행을 반환 가능

IN, EXISTS

결과물이 목록에 있는 경우 TRUE를 반환

ANY

비교연산자와 함께 사용, 목록의 값 중 어느 하나와 일치할 경우 TRUE를 반환

ALL

주어진 조건을 모두 만족하는 경우 TRUE를 반환