SQL 기본
01 Oct 2021 | SQL목차
이번 포스팅에서는 SQL 기본 구문에 대해 정리해본다. 평소에 자주 사용하지만 정확히 숙지하지 못한 부분들을 정리하는 데에 목적이 있으며 아주 기초적인 부분은 생략한다.
SQL 기본
1. Basic
SELECT문의 기본적인 쓰임은 아래와 같다. 순서를 지켜야 한다.
/* 블록 주석 */
--한줄주석
SELECT select_expr
FROM table_reference
WHERE where_condition
GROUP BY column_name/expression/position
HAVING where_condition
ORDER BY column_name/expression/position
WHERE 절 뒤에는 조건/관계 연산자가 오게 된다. 조건 연산자에는 =, <, >, <=, >=, != 가 있고, 관계 연산자에는 or, and, not, between A and B, in, not in 가 있다.
ORDER BY는 정렬을 위해 사용되며, 큰 테이블에 직접적으로 사용하는 것은 많은 부하를 유발하게 된다.
--ORDER BY height ASC
--ORDER BY height DESC
SELECT name
FROM table
ORDER BY height asc, weight desc
위와 같이 2개 이상의 칼럼으로 정렬할 때는 왼쪽부터 우선순위를 갖는다.
LIMIT 5 OFFSET 3
위 구문은 3번째 row부터 5개를 반환한다는 뜻을 갖는다.
GROUP BY는 기준을 세워 집계하는 용도로 사용한다. 집계 함수에는 count, sum, avg, min, max 등이 있다. 예시는 아래와 같다.
SELECT user_id, sum(amount*price)
FROM table
GROUP BY user_id
HAVING sum(amount * price) > 1500;
GROUP BY에서 where 조건 절을 추가하고 싶다면 위와 같이 HAVING을 이용하면 된다.
NULL을 처리하는 방법을 정리한다. IS NULL은 null 여부를 조건식으로 나타낸다.
--IS (NOT) NULL
SELECT * FROM table WHERE name IS NULL
-- SELECT * FROM table WHERE name IS NOT NULL
IF NULL의 경우 특정 칼럼이 null값이 아니면 그 값 그대로, null이면 2번째 인자 값으로 채워주는 기능을 갖는다.
--IFNULL (혹은 NVL, ISNULL)
SELECT animal_type, IFNULL(NAME, ‘no name’) as name
FROM table
--→ null이면 ‘no_name’ 아니면 NAME 그대로
COALESCE는 정의된 열 중 null이 아닌 1번째 값을 출력한다.
SELECT animal_type, COALESCE(NAME, TAG, ‘nothing’) as KEY
FROM table
--→ NAME에 값이 있으면 NAME의 값을, NAME에 값이 없지만 TAG에 값이 있으면 TAG의 값을 출력함
IF는 말 그대로 조건식을 나타내는데, True일 경우 2번째 인자 값을 False일 경우 3번째 인자 값을 반환한다.
SELECT name, IF(price > 100, ‘expensive’, ‘normal’) as judgement
FROM table
LIKE의 몇 가지 사용법은 아래와 같다.
WHERE user_id like ‘a%’
- ‘a%’: a로 시작하는 모든 것
- ‘_a%’: 2번재 자리에 a가 들어가는 모든 것
- ‘a_%_%’: a로 시작하고, 길이가 최소 3이상인 것
서브쿼리는 쿼리 속의 쿼리이다. 서브쿼리의 결과물은 여러 값일 경우 이를 조건으로 사용하기 힘들 때가 있다. 이 때 ANY, ALL, SOME을 이용할 수 있다.
- ANY(SOME): 서브쿼리의 결과 중 하나라도 해당되면 OK (or의 역할)
- ALL: 서브쿼리의 결과 모두에 해당해야 함 (and의 역할)
2. Case
조건을 쓰고 이를 만족했을 때와 만족하지 않았을 때의 반환 값을 지정하여 하나의 칼럼으로 생성하는 구문이다. ELSE를 생략하면 Null 값이 반환될 수 있다.
SELECT user_id,
CASE
WHEN (age BETWEEN 0 and 40) THEN 'YOUNG'
WHEN (age BETWEEN 41 and 70) THEN
CASE
WHEN (mind = 'good') THEN 'not young but good mind'
ELSE 'not young and bad mind'
END
ELSE '?'
END AS condition
FROM table
위 예시와 같이 기본적으로 CASE ~ END라는 큰 틀 안에 조건식을 채워넣는 개념이며, 조건식은 WHEN ~ THEN ~ + ELSE로 짜여지게 된다.
3. With
WITH는 특정 질의 결과에 이름을 부여하여 재사용을 가능하게 함과 동시에 가독성을 높여준다. 또한 WITH 구문을 사용할 경우 반복적으로 실행 계획을 수립하는 것이 아니기 대문에 쿼리의 성능 또한 향상된다.
WITH 구문은 여러 번 연속해서 쓸 수도 있다. 아래 예시를 참고하자.
WITH ex1 AS (
query
),
ex2 AS (
query
)
반복적으로 쿼리를 수행하는 WITH RECURSIVE 구문의 좋은 예시는 아래와 같다.
WITH RECURSIVE TIMETABLE AS (
SELECT 0 AS HOUR FROM DUAL
UNION ALL
SELECT HOUR + 1 FROM TIMETABLE
WHERE HOUR < 23
)
SELECT T.HOUR, IFNULL(COUNT, 0) as COUNT
FROM TIMETABLE as T
LEFT JOIN (
SELECT HOUR(datetime) as HOUR, COUNT(animal_id) as COUNT
FROM animal_outs
GROUP BY HOUR
HAVING HOUR BETWEEN 0 and 23
ORDER BY HOUR
) AS animal
ON T.HOUR = animal.HOUR
4. Join
INNER JOIN의 틀은 다음과 같다.
SELECT col
FROM table1
INNER JOIN table2
ON table1.key = table2.key
WHERE condition
GROUP BY
HAVING
ORDER BY
OUTER JOIN의 틀은 다음과 같다.
SELECT col
FROM table1
<LEFT/RIGHT/FULL> JOIN table2
ON table1.key = table2.key
WHERE condition
GROUP BY
HAVING
ORDER BY
5. 문자열 함수
SUBSTRING은 문자열을 시작위치로부터 자르는 함수이다.
SELECT SUBSTRING('1234', 2);
-- 12
SELECT SUBSTRING('abc', -2);
-- cb
SELECT SUBSTRING('12345', 2, 2);
-- 23
SUBSTRING_INDEX는 문자열을 자른 후 주어진 INDEX까지의 문자를 추출한다.
SELECT SUBSTRING_INDEX('이름,나이,시간', ',', 2)
-- 이름, 나이
위의 두 함수를 조합하면 split & get element를 구현할 수 있다.
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('이름,나이,시간', ',', 2), ',', -1)
-- 나이
CHAR_LENGTH는 string의 길이를 문자 단위로 반환한다. 이 함수의 경우 bytes도 문자 수로 반환하는데, LENGTH 함수는 바이트는 바이트 수로 반환하는 차이점이 있다.
CONCAT은 여러 값을 하나의 결과로 연결한다.
SELECT CONCAT('Summer', ' ', 1923) as release_date
-- Summer 1923
CONTAINS_SUBSTR은 대소문자를 구분하지 않는 정규화된 검색을 수행하여 값이 표현식에 있는지 확인한다. 값이 있으면 True, 없으면 False를 반환한다.
SELECT CONTAINS_SUBSTR('the blue house', 'Blue house') AS result
-- True
SELECT * FROM Recipes WHERE CONTAINS_SUBSTR(Recipes, 'toast')
+-------------------+-------------------------+------------------+
| Breakfast | Lunch | Dinner |
+-------------------+-------------------------+------------------+
| Potato pancakes | Toasted cheese sandwich | Beef stroganoff |
| Avocado toast | Tomato soup | Blueberry samon |
+-------------------+-------------------------+------------------+
2번째 값이 1번째 값의 suffix이면 ENDS_WITH 함수는 True를 반환한다. STARTS_WITH는 당연히 반대로 생각하면 된다.
WITH items AS
(SELECT 'apple' as item
UNION ALL
SELECT 'banana' as item
UNION ALL
SELECT 'orange' as item)
SELECT
ENDS_WITH(item, 'e') as example
FROM items;
+---------+
| example |
+---------+
| True |
| False |
| True |
+---------+
LEFT, RIGHT 함수는 아래와 같은 쓰임새를 갖는다.
WITH examples AS
(SELECT 'apple' as example
UNION ALL
SELECT 'banana' as example
)
SELECT example, LEFT(example, 3) AS left_example
FROM examples;
+---------+--------------+
| example | left_example |
+---------+--------------+
| apple | app |
| banana | ban |
+---------+--------------+
LTRIM, RTRIM, TRIM은 공백을 제거할 때 사용된다.
REPLACE 함수는 문자열 값을 바꾸는 함수이다.
WITH desserts AS
(SELECT 'apple pie' as dessert
UNION ALL
SELECT 'cherry pie' as dessert)
SELECT REPLACE(dessert, 'pie', 'cobbler') as example
FROM desserts;
+--------------------+
| example |
+--------------------+
| apple cobbler |
| cherry cobbler |
+--------------------+
TRANSLATE 함수는 REPLACE와 비슷하지만 차이가 있다. REPLACE 함수는 위 문자열을 하나로 취급한다. 즉, 위 예시에 나온 pie라는 문자열이 정확히 존재해야만 변환이 일어난다. 그러나 TRANSLATE 함수는 각 문자 하나 하나에 대한 변환을 수행한다.
SELECT TRANSLATE('abcde', 'ce', 'XY') FROM DUAL
-- abXdY
REPEAT은 지정한 수 만큼 문자열을 반복 반환한다.
SELECT REPEAT('abc', 3) as REPEAT
-- abcabcabc
REVERSE는 문자열을 역으로 반환한다.
SPLIT 함수는 delimiter 인수를 사용하여 value를 분할하여 array로 반환한다.
WITH letters AS
(SELECT 'b c d' as letter_group)
SELECT SPLIT(letter_group, ' ') as example
FROM letters;
+----------------------+
| example |
+----------------------+
| [b, c, d] |
+----------------------+
6. 집합 연산자
Union은 복수의 쿼리 결과를 합친다. 중복을 제거하기 때문에 만약 중복을 그대로 유지하고 싶다면 Union All을 사용하면 된다.
SELECT *
FROM jan
UNION ALL
SELECT *
FROM mar
INTERSECT와 MINUS는 각각 교집합, 차집합 결과를 반환한다.
7. 날짜 함수
DATE_FORMAT 함수는 DATE_FORMAT(DATETIME, ‘%Y-%m-%d’)와 같이 사용하며, datetime 칼럼을 원하는 형식에 맞게 변환할 수 있다.
DATE_DIFF 함수는 두 datetime 사이의 차이를 구하는 함수인데, 차이의 기준을 지정할 수 있다.
-- end_date - start_date 구조임
SELECT DATE_DIFF(HOUR, start_date, end_date) FROM DUAL
YEAR, MONTH, DAY, WEEK, HOUR, MINUTE 등을 사용할 수 있다.
SYSDATE 함수는 현재 일자와 시간을 date 형식으로 반환하는 함수이다. 이를 원하는 형식으로 바꾸기 위해서 다음과 같은 작업을 수행할 수 있다.
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') FROM DUAL
8. 정규 표현식 함수
REGEXP_CONTAINS(value, regexp)는 value가 정규표현식 regexp와 부분적으로 일치하면 True를 반환한다.
REGEXP_EXTRACT(value, regexp, position, occurrence)은 REGEXP_SUBSTR와 같은 함수로, value에서 정규표현식 regexp와 일치하는 첫 번째 하위 문자열을 반환하고, 일치하는 항목이 없으면 Null을 반환한다. position은 optional 인자로, position이 지정되면 검색은 value의 이 위치에서 시작한다. position은 양의 정수여야 한다.
REGEXP_EXTRACT_ALL은 위 함수와 같은 효과이나 일치하는 value의 모든 하위 문자열 배열을 반환한다는 차이가 있다.
WITH email_addresses AS
(SELECT 'foo@example.com' as email
UNION ALL
SELECT 'bar@example.org' as email
UNION ALL
SELECT 'baz@example.net' as email)
SELECT
REGEXP_EXTRACT(email, r'^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.([a-zA-Z0-9-.]+$)')
AS top_level_domain
FROM email_addresses;
+------------------+
| top_level_domain |
+------------------+
| com |
| org |
| net |
+------------------+
REGEXP_REPLACE(value, regexp, replacement)는 regexp와 일치하는 모든 value 하위 문자열이 replacement로 바뀐 string을 반환한다.
WITH markdown AS
(SELECT '# Heading' as heading
UNION ALL
SELECT '# Another heading' as heading)
SELECT
REGEXP_REPLACE(heading, r'^# ([a-zA-Z0-9\s]+$)', '<h1>\\1</h1>')
AS html
FROM markdown;
+--------------------------+
| html |
+--------------------------+
| <h1>Heading</h1> |
| <h1>Another heading</h1> |
+--------------------------+
References
1) 프로그래머스 SQL 고득점 Kit
2) 참고블로그1
3) 참고블로그2
4) BigQuery문서