Learning SQL 스터디 7,8,9장 정리

Learning SQL 7,8,9장 요약

문자열 데이터 처리

문자열 데이터를 처리할 때는 다음 자료형 중 하나를 사용함.

• char
  고정 길이 문자열. 지정한 크기보다 문자열이 작으면 나머지 공간을 공백으로 채운다.
  MySQL char는 최대 255자까지 허용한다.
• varchar
  가변 길이 문자열. 최대 65,535자를 허용한다.
• text
  가변 길이 문자열. 최대 4GB 크기 문서를 저장할 수 있다.
  MySQL에는 tinytext, textm mediumtext, longtext 등이 있다.

문자열 생성

문자열 길이가 최대 크기를 초과할 때 MySQL6.0 이후부터는 strict 모드로 동작하기 때문에 에러가 발생한다. 6.0 이전 버전은 경고만 발생하고 초과되는 문자열은 잘랐다.
다시 이 모드를 사용하려면 ANSI 모드로 변경하면 된다.

# 현재 모드 확인
SELECT @session.sql_mode;

# ANSI모드로 변경
mysql> SET sql_mode='ansi';

varchar 열에서 작업 시 가장 좋은 방법은 저장할 수 있는 가장 긴 문자열을 처리할 수 있을 만큼 충분히 높게 최댓값을 설정하는 것. 서버는 문자열을 저장하기에 충분한 공간을 할당하므로 varchar 컬럼에 최댓값을 설정하는 게 낭비가 아니다.

작은 따옴표 이스케이프

문자열은 작은 따옴표로 구분한다. 따라서 사용하려는 문자열에 어퍼스트로피(')가 포함되어 있다면 문자열의 종료로 인식할 수 있다. 따라서 이것을 종료 문자가 아닌 문자열에 포함된 문자라는 이스케이프를 추가해야 한다. mysql은 어퍼스트로피를 연속해서 두 번 쓰면 종료 따옴표가 아닌 문자열에 포함되는 따옴표라고 인식한다.

UPDATE string_tbl
SET text_fld = 'This string didn''t work, but it does now';

SELECT text_fld
FROM string_tbl;

This string didn't work, but it does now

# 이스케이프를 함께 출력해야 한다면 quote()
SELECT quote(text_fld)
FROM string_tbl;

문자열 조작

숫자를 반환하는 문자열 함수

가장 일반적으로 사용하는 것은 length()다.

SELECT LENGTH(char_fld)

char 는 컬럼에 저장하는 문자열은 빈 공간을 공백으로 채운다고 했지만, length를 조회할 때는 공백을 제거하고 실제 문자열의 길이만큼만 반환한다.

substring의 위치를 찾는 내장 함수인 position() 도 있다.

SELECT POSITION('characters' IN vchar_fld)
FROM string_tbl;

위 쿼리는 vchar_fld 컬럼에서 문자열 characters 가 나타나는 위치를 찾는다.
substring이 존재하지 않는다면 0을 반환한다.

프로그래밍 언어에서 첫 번째 요소의 위치는 0이지만 데이터베이스 서버에서 첫 번쨰 요소의 위치는 1이다.

그리고 strcmp() 함수가 있다. strcmp() 는 두 개의 문자열을 인수로 받아서 다음 중 하나를 반환한다.

• 정렬 순서에서 첫 번째 문자열이 두 번째 문자열 앞에 오는 경우 -1
• 정렬 순서에서 첫 번째 문자열이 두 번째 문자열 뒤에 오는 경우 1
• 문자열이 동일한 경우 0

like와 regexp 연산자로 select 절의 문자열을 비교할 수도 있다. 이 비교는 1 or 0을 산출한다.

SELECT 절에서 LIKE 연산

mysql> SELECT name, name LIKE '%y' ends_in_y
     -> FROM category;

SELECT 절에서 REGEXP 연산

SELECT name, name REGEXP 'y$' ends_in_y
FROM category;

name의 값이 정규식과 일치하면 1을 일치하지 않으면 0을 반환한다.

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

• concat()
  문자열 이어 붙일 때 사용. 문자열 중간에 문자를 추가하거나, 해당 문자를 교체해야 할 때도 쓸 수 있다.
• insert()
insert() 는 기존 문자열, 시작 위치, 대체할 문자 개수, 대체 문자열 네 개의 인수를 가지는 함수다. 세 번째 인수 값에 따라 문자열에 문자를 삽입하거나 바꿀 수 있다. 세 번째 인수 값이 0이면 대체 문자열이 삽입되며 다음처럼 처리 된다.

  SELECT INSERT('goodbye world', 9, 0, 'cruel ') string;
  

+---------------------+
| string |
+---------------------+
| goodbye cruel world |
+---------------------+
1 row in set (0.00 sec)

세 번째 인수가 0보다 크면 다음처럼 해당 문자 수의 문자열이 대체 문자열로 바뀐다.
```sql
SELECT INSERT('goodbye world', 1, 7, 'hello') string;

• replace()

  SELECT REPLACE('goodbye world', 'goodbye', 'hello')
  FROM dual;

  goodbye를 hello로 대체하는 쿼리

• stuff()
insert() 와 유사하다.

  SELECT STUFF('hello world', 1, 5, 'goodbye cruel')

  실행하면 첫 번째 위치부터 5개 문자가 되거되며 시작 위치에 'goodbye cruel' 문자열이 삽입되어 'goodbye cruel world'가 된다.

• substring()
  지정한 위치에서 시작하여 지정한 개수의 문자를 추출한다.

  SELECT SUBSTRING('goodbye cruel world', 9, 5);
  # 결과: 'curel'

숫자 데이터 처리

• MOD(x, y)
  x / y의 나머지를 구한다.
• POW(x,y)
  x의 y제곱
• CEIL()
  올림
• FLOOR()
  내림
• ROUND()
  반올림. ROUND() 는 두 번째 인수를 선택적으로 사용할 수 있다. 반올림할 소수점 이하 자릿수를 지정할 수 있다.
  ROUND(72.0909, 3) 하면 소수점 세 번째 자리까지 반올림하기 때문에 72.091이 된다.
• TRUNCATE()
  이 함수도 두 번째 인수를 이용해서 소수점 오른쪽 자릿수를 지정하도록 허용한다. 하지만 이것은 반올림하는 대신 원하지 않는 숫자를 버린다.
  TRUNCATE(72.0909, 3) 의 결과는 72.090이다.

ROUND() 와 TRUNCATE() 모두 두 번째 인수로 음수를 허용한다. 즉, 소수점 왼쪽에 있는 숫자가 잘리거나 반올림될 수 있다.

Signed 데이터 처리

• SIGN(x) : x가 음수이면 -1 양수이면 1을 반환
• ABS(x) : x의 절댓값을 반환

시간 데이터 처리

시간대 처리

세계는 시간대time zone라는 24개의 가상 영역으로 분할된다. 특정 시간대에 있는 모든 사람은 현재 시간에 동의하지만, 다른 시간대의 사람들은 동의하지 않는다.
간단한 이야기 같지만 일부 지역에서는 1년에 두 차례에 걸쳐 1시간씩 시간을 옮긴다. (서머 타임)
지구에서 두 지점 간의 시차는 반년 동안에는 4시간이 되지만, 나머지 반년 동안에는 5시간일 수 있다. 동일 시간대를 사용하는 서로 다른 지역의 경우에도 서머타임을 준수하는지 여부에 따라 1년 중 반년은 시간은 같지만 나머지 반년은 1시간씩 차이가 난다.

시간 기록의 공통적인 참조 기준을 위해 15세기 항해사들은 시계를 영국 그리니치의 시간대로 설정했다. 그리니치 표준시 또는 GMT로 알려진 시간대다. 다른 모든 시간대는 GMT와의 시간 차이로 나타날 수 있다. 예를 들어 동부 표준시로 알려진 미국 동부 시간대는 GMT -5:00. 또는 GMT보다 빠른 시간으로 나타낼 수 있다.

오늘날 우리는 국제원자시(TAI)에 기반한 협정 세계표준시Coordinated Universal Time(UTC)라는 변형된 GMT를 사용한다.
SQL 서버와 MySQL 모두 현재 UTC 타임스탬프를 반환하는 함수 utc_timestamp() 를 제공한다.
대부분 데이터베이스 서버는 기본적으로 서버가 있는 곳의 표준 시간대로 설정되지만, 필요하다면 시간대를 수정할 수 있다.
일반적으로 UTC를 사용하도록 구성되지만, 서버의 표준 시간대를 변경할 수도 있다.

국제원자시: 전 세계 50개국 실험실에 있는 400개 이상의 원자 시계 시간의 가중 평균치

시간 데이터 생성

다음 방법 중 하나로 시간 데이터 생성 가능하다.

• date, datetime 또는 time 컬럼에서 데이터 복사
• date, datetime 또는 time을 반환하는 내장 함수 실행
• 서버에서 확인된 시간 데이터를 문자열로 표현

마지막 방법을 사용하려면 날짜 포맷 지정에 사용하는 구성요소를 알아야 한다.

시간 데이터 문자열 표시

• YYYY: 연도, 세기 포함 1000~9999
• MM: 월 01(January)부터 12(December)
• DD: 일 01~31
• HH: 시간 00~23
• HHH: 시간 경과 -838 ~ 838
• MI: 분 00~59
• SS: 초 00~59

datetime 컬럼을 채우려면 다음과 같은 문자열을 만들어야 한다.

'2022-06-06 00:00:00'

문자열을 날짜로 변환

서버가 datetime 값이라고 판단할 수 없거나 기본 형식이 아닌 형식을 사용해서 datetime을 표시하려는 경우 서버는 문자열을 datetime으로 변환해야 한다.

cast() 함수로 datetime 값을 반환하는 쿼리

SELECT CAST('2022-06-06 00:00:00' AS DATETIME);

7장 실습

8장 그룹화와 집계

데이터를 그룹화하고 집계하는 방법을 설명하는 장.

그룹화 개념

가장 많이 대여한 사람을 집계하기 위해 rental 테이블에서 customer_id 를 조회한다고 해보자.
이렇게 하면 모든 대여내역을 불러오기 때문에 집계하기가 쉽지 않다. 이때 사용할 수 있는 절이 group by 다.

SELECT customer_id, count(*)
FROM rental
GROUP BY customer_id;
ORDER BY 2 DESC;

group by 절을 사용해서 customer_id 로 그룹화한 뒤에, count() 함수로 몇 회 렌탈했는지 집계를 내는 쿼리다. 이때 order by 절으로 많이 조회한 순으로 정렬해서 데이터를 출력한다.
이때 40회 이상 조회된 절만 조회하고 싶다면 having 절을 쓰면 된다.

where 절은 group by 절이 실행되기 전에 실행된다. 따라서 where 절이 실행되는 시점에는 아직 그룹화가 되기 전이므로 where 절을 쓸 수 없다.

SELECT customer_id, count(*)
FROM rental
GROUP BY customer_id;
HAVING count(*) >= 40;

집계 함수

집계함수는 그룹의 모든 행에 대해 특정 연산을 수행한다.
대부분 데이터베이스 서버에서 가지고 있는 공통 집계 함수는 다음과 같다.

• max()
• min()
• avg()
• sum()
• count()

명시적 그룹과 암시적 그룹

group by 로 생성된 그룹은 명시적 그룹, 그렇지 않은 그룹은 암시적 그룹이다.

모든 데이터가 아닌 각 고객에 대해 집계 함수를 실행하려고 하면 어떻게 될까?

SELECT customer_id
 MAX(amount) max_amt,
 MIN(amount) min)amt,
    ...
 COUNT(*) num_payments
FROM payment;

이 쿼리는 에러가 발생한다. 각 고객에게 집계 함수를 적용하려는 것은 분명하지만, 데이터가 그룹화되는 방법을 명시적으로 지정하지 않았기 때문이다.

고유한 값 계산

count() 함수를 사용할 때 모든 customer_id 수를 계산(중복 포함)할지 아니면 그룹의 모든 customer_id 중 고유한 값에 대해서만 계산할지 선택할 수 있다.

SELECT COUNT(customer_id) num_rows,
            COUNT(DISTINCT customer_id) num_customers
FROM payment;

group by 절은 쿼리에서 데이터를 그룹화하는 메커니즘.

그룹 생성

• 단일 컬럼 그룹화
  하나의 컬럼을 기준으로 그룹을 만드는 가장 단순하면서 자주 사용되는 그룹화 종류
• 다중 컬럼 그룹화
  하나 이상의 컬럼을 이용해서 그룹을 생성.

각 배우(기준1)의 영화 등급(기준2)에 대한 총 영화 수를 찾는 쿼리

SELECT fa.actor_id, f.rating, count(*)
FROM film_actor fa
    INNER JOIN film f
    ON fa.film_id = f.flim_id
GROUP BY fa.actor_id, f.rating # 각 배우와 영화 등급으로 그룹화
ORDER BY 1, 2;

위 쿼리의 결과는 각 배우가 각 등급 영화마다 몇 번 출연했는지 여부를 반환한다.

• 표현식으로 그룹화 가능하다.
  연도별로 대여를 그룹화하는 쿼리다.

SELECT extract(YEAR FROM rental_date) year,
    COUNT(*) how_many
FROM rental
GROUP BY extract(YEAR FROM rental_date);

이 쿼리는 날짜 연도 부분만 반환하는 extract() 함수를 사용해서 rental 테이블 행을 그룹화하는 간단한 표현식을 사용한다.

• 롤업
  롤업은 group by 결과로 출력된 항목들의 합계를 나타내는 방법이다.

SELECT fa.actor_id, f.rating, count(*)
FROM film_actor fa
    INNER JOIN film f
    ON fa.film_id = f.film_id
GROUP BY fa.actor_id, f.rating WITH ROLLUP
ORDER BY 1,2; # 이 order by가 안 먹음..

그룹 필터

where 절은 그룹화가 되기 전에 적용되고, having 은 그룹화 이후에 실행된다.

8장 실습

9장 서브쿼리

서브쿼리란?

- 다른 SQL 구문에 포함된 쿼리.
- 서브쿼리는 항상 괄호 안에 들어가며, 일반적으로 포함구문보다 먼저 실행됨.
- 다음과 같은 요소를 포함한 결과를 반환함
    - 단일 컬럼을 가진 단일 행
    - 단일 컬럼을 가진 다중 행
    - 다중 컬럼을 가진 다중 행
- 서브쿼리는 임시 테이블처럼 동작하고, 포함쿼리가 실행을 완료하면 메모리를 해제함.

서브쿼리 유형

비상관noncorrelated 서브쿼리

단독으로 실행될 수 있고, 포함구문에서 아무것도 참조하지 않는다.

서브쿼리가 집합을 반환할 때 포함구문이 사용할 수 있는 연산자
- in
- not in
- all
all 연산자는 한 집합의 모든 값과 하나의 값을 비교할 수 있다. all 연산자는 단독으로 쓸 수 없고 비교 연산자와 함께 사용해야 한다.

무료 영화를 대여한 적이 없는 모든 고객을 찾는 쿼리

SELECT first_name, last_name
FROM customer
WHERE customer_id <> ALL 
        (SELECT customer_id
        FROM payment
        WHERE amount = 0);

위 서브쿼리는 영화 대여료로 0달러를 지불한 고객의 ID 집합을 반환하고, 포함 쿼리는 서브쿼리에서 반환된 집합 ID가 없는 모든 고객의 이름을 반환한다. all 연산자는 잘 사용되지 않는다. not in으로 동일한 결과를 만들 수 있고 not in을 사용하는 편이 더 이해하기 쉽기 때문.

- any
any 연산자를 사용하는 조건은 다수의 비굣값 중 하나라도 만족하면 true를 반환한다.

상관correlated 서브쿼리

• 상관 서브쿼리는 포함 구문에 의존적이다.
• 상관 서브쿼리는 포함 구문을 실행하기 전에 실행되지 않는다.
• 대신 상관 서브쿼리는 각 후보 행(최종 결과에 포함될 수 있는 행)에 대해 한 번씩 실행된다.
  예를 들어 다음 쿼리는 상관 서브쿼리로 각 고객의 영화 대여 횟수를 계산한 다음, 정확히 20편의 영화를 대여한 고객을 검색한다.

SELECT c.first_name, c.last_name
FROM customer c
WHERE 20 = (
        SELECT count(*) FROM rental r
        WHERE r.customer_id = c.customer_id
    );

서브쿼리 맨 끝에 있는 c.customer_id 에 대한 참조는 서브쿼리를 상호 연관시킨다. 포함 쿼리는 서브 쿼리를 실행하려면 c.customer_id 값을 제공해야 한다. 이 경우 포함 쿼리는 customer 테이블에서 599행을 모두 검색한 다음, 각 고객에 대해 한 번씩 서브쿼리를 실행해서 해당하는 고객 ID를 전달한다.

주의할 점은 역시 성능이다. 포함 쿼리의 각 행에 대해 한 번씩 실행되므로 포함 쿼리가 많은 행을 반환하는 경우 성능 문제가 발생할 수 있다.

상관 서브쿼리로 조건을 작성할 때 가장 많이 사용되는 연산은 exists 다. 수량에 관계없이 관계가 존재하는지 확인하고자 exists 연산자를 사용한다.

총 몇 편의 영화를 대여했는지 상관없이 2005년 5월 25일 이전에 한 편 이상의 영화를 대여한 모든 고객을 찾는 쿼리

SELECT c.first_name, c.last_name
FROM customer c
WHERE EXISTS
    (SELECT 1 FROM rental r
        WHERE r.cutomer_id = c.customer_id
            AND date(r.rental_date) < '2005-05-25');

exsits 연산을 사용하면 서브쿼리는 0개, 1개 또는 여러 개 행을 반환할 수 있다. 조건으로는 단순히 서브 쿼리가 하나 이상의 행을 반환했는지 여부만 확인한다.
exists 연산을 사용할 때는 select * 또는 select 1 을 정의하는 게 규칙이다.