사용 DB : MySQL (5.7 / 8.0인 것은 따로 명시)

String Functions

12.8 String Functions and Operators

LENGTH()

# 가장 긴 문자열 1개만 찾기 (길이가 같다면 사전 순으로 앞서는 것)
SELECT CITY,LENGTH(CITY)
FROM STATION 
WHERE LENGTH(CITY)  = (
    SELECT MAX(LENGTH(CITY))
    FROM STATION 
)
ORDER BY CITY
LIMIT 1;

조건문

13.6.5 Flow Control Statements

CASE문

• CASE문은 MySQL에서 조건문을 표현하기 가장 쉬운 방법.
  IF() function은 else if가 없고
  IF statement는 더 어렵기 때문.

# binary search tree의 노드 종류 구분하기 문제
SELECT DISTINCT BST1.N AS node,
    CASE 
        WHEN BST1.P IS NULL 
            THEN 'Root'
        WHEN BST2.P IS NOT NULL
            THEN 'Inner'
        ELSE 'Leaf'
    END
    AS type
FROM BST BST1 
LEFT JOIN BST BST2 ON BST1.N = BST2.P
ORDER BY node;

변수 사용하기

9.4 user defined variables

• User variables are written as @var_name
• User-defined variables are session specific.
• integer, decimal, floating-point, binary or nonbinary string, or NULL value 할당 가능

# iterative한 중앙값(median) 찾기
SET @idx = -1;

SELECT ROUND(LAT_N,4) AS medianLatitude
FROM (
    SELECT @idx := @idx+1 AS rowIdx,
            LAT_N 
    FROM STATION
    ORDER BY LAT_N
) AS subQuery
WHERE subQuery.rowIdx = floor(@idx/2);

조인

# between 조건으로 조인
SELECT IF(grd.Grade < 8 , NULL , stu.name),
grd.Grade,
stu.Marks
FROM Students stu 
JOIN Grades grd ON stu.Marks BETWEEN grd.Min_Mark AND grd.Max_Mark
ORDER BY grd.Grade DESC,
stu.name,
stu.Marks;

aggregate 관련

12.20.3 MySQL Handling of GROUP BY

WHERE,HAVING

• where vs having 의미를 잘 구분해서 조건을 위치시키기
• join 조건 의미를 잘 생각하기
  처음에 sub.hacker_id = chal.hacker_id로 sub와 chal을 조인해줬는데 그렇게 하면 hacker의 submission이 어떤 challenge의 것인지 구분할 수 없게 된다.

SELECT hack.hacker_id AS id, 
hack.name AS name
FROM Hackers hack 
    JOIN Submissions sub ON hack.hacker_id = sub.hacker_id
    JOIN Challenges chal ON sub.challenge_id = chal.challenge_id
    JOIN Difficulty diff ON diff.difficulty_level = chal.difficulty_level
WHERE chal.difficulty_level = diff.difficulty_level
AND sub.score = diff.score
GROUP BY id,
name
HAVING COUNT(sub.hacker_id) > 1
ORDER BY COUNT(sub.hacker_id) DESC,
id;

MySQL 8.0's new DML

• mysql 8.0이상에서만 사용할 수 있는 데이터 조작 기능들.

with [Recursive]

13.2.15 WITH (Common Table Expressions)

# 테이블 없이 N 이하 모든 소수 구하기
SET @num := 2;

WITH RECURSIVE cte AS
(
    SELECT @num AS num
    UNION
    SELECT @num := @num + 1
    FROM cte
    WHERE @num < 100
)

SELECT cte1.num
FROM cte cte1
JOIN cte cte2 ON cte1.num > cte2.num
GROUP BY cte1.num
HAVING SUM(
    IF((cte1.num % cte2.num) = 0, 1,0)
) = 0
ORDER BY cte1.num;