[프로그래머스 SQL 스터디 2기] 3주 차 TIL/후기

1. [프로그래머스 SQL 스터디 2기] 1주 차 TIL/후기

2. [프로그래머스 SQL 스터디 2기] 2주 차 TIL/후기​

3. [프로그래머스 SQL 스터디 2기] 3주 차 TIL/후기

4. [프로그래머스 SQL 스터디 2기] 4주 차 TIL/후기

5. [프로그래머스 SQL 스터디 2기] 5주 차 TIL/후기

6. [프로그래머스 SQL 스터디 2기] 6주 차 TIL/후기

 

3주 차는 그룹핑, 내장 함수, 갱신문에 대해서 집중적으로 학습했다.

 

2주 차 이후에 블로그를 꾸준히 작성을 하지 못하고 노션에 TIL만 작성을 하고 있었다.

 

마지막 주차에 코딩 테스트 전 복습 겸 하나씩 다시 정리를 시작한다.

 

그룹핑

SQL에서 그룹핑은 GROUP BY 절을 사용해서 행(튜플)을 그룹화하는 것을 말한다.

 

1. COUNT(*)와 COUNT(컬럼)의 차이점은?
2. GROUP BY절 SQL 표준과 MySQL에서 차이점은?
3. ORDER BY절 컬럼과 컬럼 위치(position) 어떤 것을 사용하는 게 더 좋을까?
4. LIMIT절과 ROW_NUMBER() 어떤 것을 사용하는 게 더 좋을까?

 

COUNT(*)와 COUNT(컬럼)의 차이점은?

COUNT()는 행의 개수를 세는 집단 함수이다. 

• COUNT(*) : PK 컬럼 값의 개수(전체 행의 개수)
  • PK는 제약 조건이 UNIQUE && NOT NULL이다. 
• COUNT(컬럼) : NULL값을 제외한 전체 행의 개수

 

간단하게 테스트를 할 때는 COUNT(*)라고 명시해주는 것도 괜찮지만, COUNT(기본키 컬럼)을 명시해주는 게 좋다

 

 

GROUP BY절 SQL 표준과 MySQL에서 차이점은?

GROUP BY 절은 기준 컬럼으로 같은 투플을 묶고, 테이블을 여러 그룹으로 나눈다.

각각의 그룹별로 하나의 투플이 생성된다.

 

• SQL 표준
  • GROUP BY절에서 SELECT절 컬럼 Alias의 사용이 불가능하다.
  • 그룹핑 기준 컬럼과 집단 함수 기준 컬럼만 SELECT절에서 사용 가능하다.
• MySQL
  • GROUP BY절에서 SELECT절 컬럼 Alias의 사용이 가능하다. 
  • 그룹핑 기준 컬럼과 집단 함수 기준 컬럼 이외의 컬럼도 SELECT절에서 사용 가능하다.

 

ORDER BY절 컬럼과 컬럼 위치(Column Position) 어떤 것을 사용하는 게 더 좋을까?

ORDER BY절에는 컬럼으로 정렬하는 방법과 컬럼 위치로 정렬하는 두 가지 방법이 있다.

 

컬럼 사용

SELECT절에 두 번째 컬럼인 name으로 오름차순(ASC)을 하는 예제이다.

ORDER BY절에 ASC | DESC을 생략하면 기본값은 ASC이다.

SELECT   customerId, name
FROM     customers
ORDER    BY name

 

 

컬럼 위치 사용

컬럼의 위치를 사용해서 컬럼의 위치 시작번호는 1이기에, 2를 정렬하면 name을 기준으로 정렬을 하는 것이다.

SELECT   customerId, name
FROM     customers
ORDER    BY 2

 

컬럼 위치를 사용하는 방법은 테스트를 할 때는 사용을 해도 괜찮다. 그렇지만 사용자가 ORDER BY 절을 보고 2번째 컬럼을 찾아야 하는 불편함이 있다. 

 

ORDER BY절에서는 컬럼으로 명확하게 명시해주는 것이 좋다!

 

LIMIT절과 ROW_NUMBER() 어떤 것을 사용하는 게 좋을까?

 

LIMIT

LIMIT 절은 MySQL에서만 사용 가능한 구문이다. LIMIT으로 페이징 처리를 하기도 하고 다양하게 사용할 수 있다.

 

 

학생 테이블에서 키가 가장 큰 5명을 출력하는 예제

-- LIMIT [offset, ] row_count ];
-- LIMIT 구문은 offset과 row_count 두 개의 인자가 있다.
-- offse은 필수값이 아니고, 기본값은 0이다.
-- row_count는 LIMIT을 사용하면 필수값이고, 출력할 행의 개수를 말한다.

SELECT   name, height
FROM     students
ORDER    BY height DESC
LIMIT    5  /* 0, 5 */

 

ROW_NUMBER()

ROW_NUMBER()은 윈도우 함수이다. 윈도우 함수는 투플 별로 통계치를 제공하는 방법이다.

그중에서도 LIMIT과 유사한 처리가 가능한 함수는 ROW_NUMBER()가 있다.

 

ROW_NUMBER()는 단어 그대로 행의 개수를 말한다.

LIMIT과는 다르게 서브 쿼리라는 방식으로 한 번 감싸서 출력이 가능하다.

 

SELECT name, height
FROM (
      SELECT   ROW_NUMBER() OVER(ORDER BY height DESC) AS rowNo
               name, height
      FROM     students
     )
WHERE   rowNo <= 5

 

LIMIT과 ROW_NUMBER() 둘 중에 어느 것을 쓰는 게 좋을까?

LIMIT은 MySQL에서만 사용이 가능하지만, ROW_NUMBER() 표준 함수이다.

MySQL에서만 사용이 가능한 방법보다는, 표준을 사용하는 것이 좋다. MySQL DBMS 제품만 사용하지 않고, 추후에 Oracle과 다른 DBMS의 호환성을 맞추기 위해서는 표준을 지향해야 한다.

 

 

TIL : 그룹핑

3주차 TIL : 그룹핑

 

내장 함수

SQL의 내장 함수는 단일행 내장 함수와 다중행 내장 함수로 크게 나눠진다.

해당 챕터에서 많은 함수들이 있지만 날짜형 함수인 INTERVAL, TIMESTAMPDIFF()와 REGEXP_*로 시작하는 정규식 함수들에 깊게 학습할 수 있었다. 하단과 같은 다양한 함수가 있었다.

• 단일행 내장 함수
  • 문자형, 숫자형, ..., 날짜형, 정규식 등등
• 다중행 내장 함수
  • 집단 함수    : 그룹 별 통계치
  • 윈도우 함수 : 투플 별 통계치
  • 그룹 함수    : 레벨 별 통계치

 

1. NULL처리 함수는 어떤 것을 쓰는 게 좋을까?

 

NULL 처리 함수는 어떤 것을 쓰는 게 좋을까?

NULL 처리 함수의 표준으로는 COALESCE(expr1, expr2, expr3...)가 있다.

COALESCE 함수는 임의 개수 expr에서 NULL 값이 아닌 값을 리턴하는 NULL 처리 함수이다.

SELECT   COALESCE(NULL, 'Hello') /* Hello */
SELECT   COALESCE(NULL, NULL, 'Hello World'); /* Hello World */

 

DBMS 제품마다 NULL 처리하는 함수는 정말 다양하다.

MySQL, Oracle 역시 NULL을 처리하는 각각의 함수가 있고, 다른 DBMS도 NULL을 처리하는 함수가 다르다.

각각의 DBMS의 제품의 함수를 쓰는 것보다는 표준 함수인 COALESCE를 쓰는 게 DBMS의 제품을 변경할 때 유연하게 대처가 가능하다.

 

• MySQL 
  • IFNULL(expr1, expr2)
• Oracle
  • NVL(expr1, expr2)

 

 

갱신문

출력문과는 다르게 갱신 문에서는 INSERT, UPDATE, DELETE문을 다뤘다.

DDL과 DML이 어떤 차이가 있는지 Safe-Updates Mode란 무엇인지 확인해본다.

 

1. DROP, TRUNCATE(DDL)와 DELETE(DML)의 차이점은?
2. Safe-Updates Mode(MySQL)이란?

 

DROP, TRUNCATE(DDL)와 DELETE(DML)의 차이점은?

DROP, TRUNCATE와 같은 DDL 명령어는 하드 디스크에 저장된 테이블에 직접 적용을 한다.

DDL 명령이 실행되면 자동 커밋(AUTO COMMIT)이 이뤄지고, 트랜젝션 로그의 기록이 남지 않는다.

 

DELETE(DML)은 DDL과는 다르게 테이블을 메모리에 로딩해서 적용한다.

DML 명령이 실행되면 메모리에서 실행돼서 수동으로 COMMIT 명령을 통해서 메모리에 적용한 명령을 하드디스크의 테이블에 실제로 반영한다. 트랜젝션 로그의 기록이 남는다. 

 

Safe-Updates Mode(MySQL)이란?

MySQL에서는 갱신문 처리 시(INSERT/UPDATE/DELETE) Safe-Updates Mode가 적용된다.

많은 수의 투플을 실수로 삭제하거나 수정하는 상황을 미리 방지하는 기능이다.

 

Ex) 고객 테이블에 103번인 고객의 우편번호(postalCode)를 10000으로 수정을 하려고 했는데, 실수로 모두 수정을 하면 MySQL은 Safe-Updates Mode가 기본 true이기에 실행이 거절된다.

 

하단과 같이 실행하면 safe-mode가 설정되어 있기에 실행되지 않는다.

UPDATE   customers
SET      postalCode = '10000';

/*
0	6	22:53:44	UPDATE customers
 SET   postalCode = '10000'	Error Code: 1175. 
 You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 
 To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.	0.016 sec
*/

 

safe-mode를 해제

sql_safe_updates를 0으로 하면 safe-mode가 해제된다.

-- safe 모드 해제
SET   sql_safe_updates = 0;

 

다시 UPDATE문을 실행

정상적으로 실행이 되는 것을 볼 수 있다.

UPDATE   customers
SET      postalCode = '10000';

/*
3	8	22:56:18	UPDATE customers
 SET   postalCode = '10000'	122 row(s) affected
 Rows matched: 122  Changed: 122  Warnings: 0	0.000 sec
*/

 

safe-mode 설정

다시 safe-mode를 설정하고 싶으면 sql_safe_updates를 1로 설정하면 된다.

 

-- safe mode 설정
SET   sql_safe_updates = 1;

 

 

TIL : 내장 함수 및 갱신문

3주차 TIL : 내장 함수

 

후기

3주차에는 그룹핑에서 대해서 더 확실하게 알 게 되었고, 내장 함수에 대해서도 조금 더 익숙해졌다.

 

여기서는 다루지 않았지만, 추후에 정규식 함수는 따로 포스팅을 할 것이다. 

 

코딩 테스트의 난이도가 점점 증가하고 있지만, 아직 까지는 풀만하다!

 

4주 차는 집합과 조인에 대해서 학습한다.