31st Jan 2023
SQL 스터디를 시작했다.
목표는 한달안에 프로그래머스 SQL 고득점 Kit을 푸는 것이다.
풀면서 배운 내용을 여기에 정리해보려고 한다.
24th Feb 2023
목표대로 프로그래머스 SQL 고득점 Kit을 다 풀었다.
확실히 스터디를 진행하니까 좋은 자극을 받고 덕분에 끝까지 열심히 풀 수 있었던 것 같다. 스터디 같이 하자고 먼저 제안해준 분께 매우 감사하다.
Reference: https://www.sqlstyle.guide/
SELECT *
FROM TABLE
WHERE CONDITION1
GROUP BY COLUMN1
HAVING CONDITION2
ORDER BY COLUMN2
LIMIT n;
IN - check if a value matches any value in a subquery (or a list)
EXISTS - check if a subquery returns at least one row (TRUE or FALSE)
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
vs. JOIN
vs. selecting from multiple tables
Single JOIN
SELECT a.COLUMN1, b.COLUMN1
FROM TABLE1 AS a JOIN TABLE2 AS b
ON a.key=b.key;
Multiple JOINS
SELECT *
FROM TABLE1 a
JOIN TABLE2 b
ON a.key=b.key
JOIN TABLE3 c
ON b.key=c.key
We can use USING subclause instead if the join columns have identical names
In MySQL, JOIN, CROSS JOIN and INNER JOIN are syntactic equivalents.
returns records that have matching values in both tables
Sometimes, we use INNER JOIN to write self join queries.
SELECT e1.name, e2.name
FROM Employee e1
JOIN Employee e2
ON e1.id!=e2.id AND e1.dob=e2.dob;
returns all records from the left table, and the matched records from the right table
SELECT a.COLUMN1, b.COLUMN1
FROM TABLE1 a
LEFT JOIN TABLE2 b
ON a.key=b.key;
SELECT a.COLUMN1, b.COLUMN1
FROM TABLE1 a
LEFT JOIN TABLE2 b
ON a.key=b.key
WHERE b.key IS NULL;
returns all records from the right table, and the matched records from the left table
SELECT a.COLUMN1, b.COLUMN1
FROM TABLE1 a
RIGHT JOIN TABLE2 b
ON a.key=b.key;
SELECT a.COLUMN1, b.COLUMN1
FROM TABLE1 a
RIGHT JOIN TABLE2 b
ON a.key=b.key
WHERE a.key IS NULL;
returns all records from both tables
SELECT a.COLUMN1, b.COLUMN1
FROM TABLE1 a
FULL JOIN TABLE2 b
ON a.key=b.key;
SELECT a.COLUMN1, b.COLUMN1
FROM TABLE1 a
FULL JOIN TABLE2 b
ON a.key=b.key
WHERE a.key IS NULL OR b.key IS NULL;
produces the Cartesian product of the named tables, producing every permutation of their rows
creates an implicit join clause based on the common columns (identical names and data types)
정렬의 기본값은 ASC(오름차순)이다.
SELECT * FROM TABLE ORDER BY COLUMN1;
SELECT * FROM TABLE ORDER BY COLUMN1 ASC;
즉, 위 statement들은 동일하다.
내림차순, 역순 정렬은 DESC이다.
조회한 열과 정렬하는 열이 같을 경우 잡기술이 있다.
SELECT COLUMN1, COLUMN2 FROM TABLE ORDER BY COLUMN1, COLUMN2 DESC;
SELECT COLUMN1, COLUMN2 FROM TABLE ORDER BY 1, 2 DESC;
위 statement들은 동일하다. COLUMN1의 경우 기본값인 오름차순 정렬이다.
In MySQL, NULL values are sorted first in ascending order (and last in descending order) - NULL values are considered "lower" than any non-NULL values.
COUNT(), MAX(), MIN(), SUM(), AVG()
MIN(), MAX()
SELECT COLUMN1, COUNT(COLUMN2) AS count
FROM TABLE
GROUP BY 1
HAVING count>=2
ORDER BY 1;
GROUP BY도 ORDER BY와 동일한 잡기술이 적용된다.
HAVING vs. WHERE
Rollups can be generated on multicolumn groups using the WITH ROLLUP clause after the GROUP BY clause.
Under WHERE clause
INSTR(toBeSearched, toSearch)
SUBSTRING(string, start, length)
LEFT(string, length)
RIGHT(string, length)
LPAD(string, length, padding)
CONCAT(string1, string2, ...) AS newString
날짜 데이터에서 일부만 추출하기
날짜 형식 맞추기
DATE_FORMAT(COLUMN, '%Y-%m-%d')
DATEDIFF(날짜1, 날짜2)
Under WHERE clause
NULL 대체
중복 제거 DISTINCT
SELECT DISTINCT COLUMN FROM TABLE;
BETWEEN value1 and value2
LIMIT
SELECT * FROM TABLE LIMIT n;
SELECT * FROM TABLE LIMIT a, b;
ROUND(number, dp)
TRUNCATE(number, dp)
CAST(value AS datatype)
COLUMN1 값을 기반으로 새로운 COLUMN 생성/조회하기
SELECT if(condition, true_value, false_value) COLUMN FROM TABLE;
CASE
CASE
WHEN CONDITION1 THEN RESULT1
WHEN CONDITION2 THEN RESULT3
WHEN CONDITION3 THEN RESULT3
ELSE 'NONE'
END AS COLUMN_NAME
UNION & UNION ALL
UNION - combine the result of 2+ SELECT statements (must have the same number of columns)
(
SELECT * FROM TABLE1
)
UNION
(
SELECT * FROM TABLE2
)
ORDER BY 1;
UNION ALL selects duplicate values as well (as opposed to UNION)
INTERSECT: the result is all the row values common to both tables
EXCEPT: the result is all the row values in the 1st table but not in the 2nd table
Virtual Table
with newT as (
SELECT *
FROM TABLEA
WHERE condition;
)
SELECT * FROM newT;
Virtual Table + RECURSIVE
with RECURSIVE 테이블 as (
SELECT 초기값 AS COLUMN
UNION ALL
SELECT COLUMN1 계산식 FROM 테이블 WHERE 조건문
)
SELECT * FROM 테이블;
예를 들자면,
with RECURSIVE t as (
SELECT 1 AS h
UNION ALL
SELECT h+1 FROM t WHERE h<5
)
SELECT * FROM t;
를 실행하면 아래와 같은 테이블을 조회한다.
h |
---|
1 |
2 |
3 |
4 |
5 |
로컬 변수
SET @HOUR:=-1;
SELECT (@HOUR:=@HOUR+1) AS HOUR,
(SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME)=@HOUR) AS COUNT
FROM ANIMAL_OUTS
WHERE @HOUR<23;
https://www.geeksforgeeks.org/mysql-regular-expressions-regexp/
https://steemit.com/mysql/@seobangnim/mysql-regexp
추후 정리 필