MYSQL 기본 문법 총정리

아무래도 프로그래머스의 모든문제를 풀었지만 불안한것이 사람마음이다.

시험전 마지막 정리로 한번 흝어보자.

index

---

1. 기본적인 SQL 조회구조
2. 집계함수(GROUP BY와 세트)
3. String, Date
4. 연산함수
   번외. 그 외에 헷갈릴 만한 내용

0. 기본적인 SQL 조회구조

---

SELECT 칼럼
FROM 테이블
JOIN 조인
WHERE 조건
GROUP BY 그룹칼럼
HAVING 그룹조건
ORDER BY 정렬조건
LIMIT 출력개수
;

1. 집계함수(GROUP BY와 세트)

---

AVG() # 평균값 = SUM() / COUNT()
SUM() # 합
COUNT() # 그룹 내 데이터 개수

MAX() # 최대값
MIN() # 최소값

2. String, Date

---

STRING

문자열 LIKE '%조건%' # 문자열에 조건이 들어가는지 검사 이때 %를 앞이나 뒤에 붙여 다른 문자와 섞여있어도 체크가능

CONCAT(칼럼1, 칼럼2) # '칼럼1칼럼2' 이런식으로 문자열이나 칼럼을 하나의 문자열로 결합

SUBSTR(칼럼, 시작점, 개수) # 칼럼의 문자열에서 시작점으로부터 개수만큼 문자열을 잘라온다.

DATE

# 2024-01-15 와 같은 형태로 DATE 출력 형식 지정 대소문자를 구분하여 형식이 변화
DATE_FORMAT(날짜칼럼, '%Y-%m-%d')
YEAR(날짜칼럼) # 년도 출력 2024
MONTH(날짜칼럼) # 달 출력 1 위의 format과 달리 숫자타입으로 앞에 0이 안붙음
DAY(날짜칼럼) # 일 출력 마찬가지로 앞에 0 제외
HOUR(날짜칼럼) # 시간

3. 연산함수

---

소수점 연산

CEILING(변수) # 올림
ROUND(변수, 자리수) # 자리수에 맞춰 반올림
FLOOR(변수) # 내림

날짜 연산

# 날짜의 차이 계산
TIMESTAMPDIFF(형식, START_DATE, END_DATE)
# END_DATE - START_DATE를 구하되 형식에 맞게 구한다
# 형식으로는 DAY HOUR 등이 들어갈 수 있다
# 1일에서 31일을 뺴면 30일임에 주의하자 +1을 해줘야하는 경우가 많다

번외. 그 외에 헷갈릴 만한 내용

---

NULL

NULL 값인지 확인

칼럼 IS NULL, 칼럼 IS NOT NULL

NULL 값 대체

IFNULL(칼럼명, 대체값)

ORDER BY

ASC : 오름차순
DESC : 내림차순

WHERE VS HAVING

WHERE # GROUP BY의 위에 위치하며 그룹을 나누기 전 조건이 들어감
GROUP BY
HAVING # GROUP BY와 한 세트로 아래에 위치하며 그룹을 나눈 후 조건에 들어감

UNION

# 쿼리문에 따른 테이블 두개를 행으로 결합(중복행은 제거)
쿼리문1
UNION
쿼리문2;

# 쿼리문에 따른 테이블 두개를 행으로 결합(중복 포함)
쿼리문1
UNION ALL
쿼리문2;

UNION VS JOIN 차이점

• UNION: 행 결합
• JOIN: 열 결합

JOIN 종류

이미지 출처: https://velog.velcdn.com/images/solfe/post/a9da5b51-bdcd-4987-9902-d14f05559897

여기에 더해 교집합에 해당하는 'INNER JOIN' 이 존재한다. 가장 자주 사용된다.

TB1 INNER JOIN TB2 ON 조건 

BETWEEN A AND B

값의 범위 비교

칼럼 BETWEEN A AND B # A이상 B이하 범위에 칼럼이 해당하는지 비교

'2022-10-16'
BETWEEN DATE_FORMAT(START_DATE, '%Y-%m-%d')
AND DATE_FORMAT(END_DATE, '%Y-%m-%d') 
# 날짜도 형식을 FORMAT으로 맞춰주면 비교 가능하다.

DISTINCT

중복 제거

DISTINCT 칼럼명 # 칼럼의 중복을 제외한다. COUNT와 응용해 중복값 제외 개수도 측정가능

CASE

칼럼의 값을 조건에 따라 바꾸는 방법

# IF 문의 형태
CASE IF 조건1 THEN 값1
	IF 조건2 THEN 값2
    ELSE 예외값
END 칼럼별명

# SWITCH 문의 형태
CASE IF 조건칼럼
	IF 조건칼럼값1 THEN 값1
	IF 조건칼럼값2 THEN 값2
    ELSE 예외값
END 칼럼별명

IN

같은지 비교하고 싶은 값이 많을때 사용

칼럼 IN ('값1', '값2') # 칼럼값이 값1이나 값2인지 비교

별명

편의를 위한 별명 설정

칼럼 AS 별명
칼럼 별명

테이블 AS 별명
테이블 별명
# 위 두가지 방법들로 테이블 칼럼 모두 별명화 가능

SET

변수를 선언하고 싶을때 사용

SET 변수명 = 초기화값;
@변수명 # 이걸로 가져다 사용

# 예시 문제: 입양 시각 구하기(2)
SET @HOUR = -1;

SELECT (@HOUR := @HOUR + 1) HOUR, (SELECT COUNT(HOUR(DATETIME))
                                   FROM ANIMAL_OUTS
                                   WHERE HOUR(DATETIME) = @HOUR) COUNT
FROM ANIMAL_OUTS
WHERE @HOUR < 23

 

 

문자열 함수

CONCAT(), CONCAT_WS() : 두 개 이상의 문자열을 결합합니다.

SELECT CONCAT('Hello', ' ', 'World'); -- 'Hello World'

# CONCAT_WS는 특정 구분자를 이용해 문자를 결합할 수 있음
SELECT CONCAT_WS('/', '2022', '10','22'); -- '2022/10/22'

 

SUBSTRING() : 문자열의 일부분을 추출합니다.

SELECT SUBSTRING('Hello World', 7); -- 'World'

 

SUBSTRING_INDEX(): 찾는 문자열이 n회 등장하면 그 이후 문자열을 버립니다. n이 마이너스면 오른쪽에서 시작합니다.

SELECT SUBSTRING_INDEX('test3.account.gmail.com', '.', 2) -- 'test3.account'
SELECT SUBSTRING_INDEX('test3.account.gmail.com', '.', -2) -- 'gmail.com'

 

LENGTH() : 문자열의 길이를 반환합니다.

SELECT LENGTH('Hello World'); -- 11

 

LOWER(), UPPDER() : 문자열을 모두 소문자, 대문자로 변환합니다.

SELECT LOWER('Hello World'); -- 'hello world'
SELECT UPPER('Hello World'); -- 'HELLO WORLD'

 

TRIM([LEADING, BOTH, TRAILING][rem_str] FROM str) : 문자열의 앞/뒤/양쪽에서 지정된 문자열을 제거합니다.

SELECT TRIM("     abc     "); -- 'abc'
SELECT TRIM(BOTH "a" FROM "aaaabcbbbaaa"); -- 'bcbbb'
SELECT TRIM(TRAILING "a" FROM "aaaabcbbbaaa"); -- 'aaaabcbb'
SELECT TRIM(LEADING "a" FROM "aaaabcbbbaaa"); -- 'bcbbbaaa'

 

REPLACE(str, old_str, new_str) : 문자열에서 old_str을 new_str로 대체합니다.

SELECT REPLACE("It's Banana", "Banana", "Apple"); -- It's Apple

 

INSTR(str, substr) : 문자열에서 substr이 처음 나타나는 위치를 반환합니다.

SELECT INSTR('foobarbar', 'bar'); -- 4

# SQL에서 시작점은 1이기 때문에 0이면 존재하지않는다는 의미이다.
SELECT INSTR('xbar', 'foobar'); -- 0

 

LPAD, RPAD(str, len, pad_str) : 문자열을 왼쪽, 오른쪽으로 패딩합니다.

SELECT LPAD("aaa", 5, "0"); -- 00aaa
SELECT RPAD("aaa", 5, "0"); -- aa000

 

LEFT, RIGHT(str, len) : 문자열에서 왼쪽 또는 오른쪽에서 길이만큼 추출합니다.

SELECT LEFT("abcde", 3); -- "abc"
SELECT RIGHT("abcde", 3); -- "cde"

 

MID(str, pos, len) : 문자열에서 특정 시작점에서의 길이만큼을 추출합니다.

SELECT MID("abcdefghi", 5, 2); -- "ef"

 

BIN, OCT, HEX : 각각 2진수, 8진수, 16진수 값을 반환합니다.

SELECT BIN(31); -- 11111
SELECT OCT(31); -- 37
SELECT HEX(31); -- 1F

 

REVERSE(str) : 주어진 문자열을 거꾸로 반환합니다.

SELECT REVERSE("123456789"); -- 987654321

 

SPACE(len) : 길이만큼의 공백을 반환합니다.

SELECT CONCAT("Hello", SPACE(5), "World"); -- "Hello     World"

 

REPEAT(str, len) : 문자열을 주어진 횟수만큼 반복합니다.

SELECT REPEAT("abc", 3); -- "abcabcabc"

 

LOCATE(substr, str, [pos]) : 첫번째로 발견한 문자열의 위치를 반환합니다. (POSITION과 동일한 함수이며, INSTR와는 파라미터 순서만 다릅니다.)

SELECT LOCATE("abc", "abcdefabc"); -- 1
SELECT POISITION("abc" IN "abcdefabc"); -- 1
SELECT INSTR("abcdefabc", "abc");

# 시작지점을 정하는 것은 Locate에서만 가능합니다.
SELECT LOCATE("abc", "abcdefabc", 3); -- 7

 

FORMAT(x, d) : 세자리 수 마다 콤마를 넣고 주어진 길이만큼 소수점을 소수점을 남깁니다.

SELECT FORMAT(12332.1,4); -- '12,332.1000'
SELECT FORMAT(12332.2,0); -- '12,332'

날짜 및 시간 관련 함수

CURDATE(), CURTIME(), NOW(), SYSDATE() : 현재 날짜 또는 시간을 반환합니다.

# 현재 날짜를 '년-월-일'로 반환
SELECT CURDATE(); -- '2023-02-27'

# 현재 시간을 HH:MM:SS로 반환
SELECT CURTIME(); -- '04:45:10'

# 현재 날짜 및 시간을 반환
SELECT NOW(); -- '2023-02-27 04:45:00'
SELECT SYSDATE(); -- '2023-02-27 04:45:00'

 

YEAR(), MONTH(), DAYOFMONTH(), HOUR(), MINUTE(), SECOND(): 특정 날짜나 시간 단위를 반환합니다.

# 날짜에 대한 "연도"를 반환
SELECT YEAR('2023-02-27'); -- 2023

# 날짜에 대한 "월"을 반환
SELECT MONTH('2023-02-27'); -- 2

# 날짜에 대한 "일"을 반환
SELECT DAYOFMONTH('2023-02-28'); -- 28

# 시간을 반환
SELECT HOUR('2023-02-27 15:32:00'); -- 15

# 분을 반환
SELECT MINUTE('2023-02-27 15:32:00'); -- 32

# 초를 반환
SELECT SECOND('2023-02-27 15:32:00'); -- 0

 

ADDDATE(date, diff), SUBDATE(date,diff) : 날짜를 기준으로 차이를 더하거나 뺍니다. (DATE_ADD와 DATE_SUB와 동일)

SELECT ADDDATE('2022-12-31', INTERVAL 15 DAY); -- '2022-01-15'
SELECT ADDDATE('2022-12-31', INTERVAL 1 MONTH); -- '2022-01-31'

SELECT SUBDATE('2022-12-31', INTERVAL 15 DAY); -- '2022-12-16'
SELECT SUBDATE('2022-12-31', INTERVAL 1 MONTH); -- '2022-11-30'

 

DATEDIFF(date1, date2), TIMEDIFF(time1, time2) : 날짜 차이 혹은 시간 차이를 반환합니다.

SELECT DATEDIFF('2022-12-31', '2022-10-29'); -- 63
SELECT TIMEDIFF('14:50:00', '08:40:50'); -- 06:09:10

 

DATEOFWEEK(day) : 해당 날짜의 요일을 숫자로 반환합니다.(일:1, 월:2..)

SELECT DAYOFWEEK(NOW()); -- 2

 

MONTHNAME(month) : 해당 월의 영어 이름을 반환합니다.

SELECT MONTHNAME(NOW()); -- February

 

DAYOFYEAR(day) : 1월 1일 기준으로 몇 일이 지났는 지를 반환합니다.

SELECT DAYOFYEAR(NOW()); -- 58

 

LASTDAY(date) : 주어진 월의 마지막 날을 반환합니다.

SELECT LAST_DAY('2012-02-01'); -- '2012-02-29

 

TIME_TO_SEC(time) : 시간을 초 단위로 반환니다.

SELECT TIME_TO_SEC('12:00:00'); -- 43200

 

**DATE_FORMAT(date, condition) : 조건에 맞는 형식으로 변환된 date를 반환합니다.

SELECT DATE_FORMAT(now(), '%Y-%M-%D'); -- '2023-February-27th'

MySQL DATE_FORMAT 옵션값 참고

숫자 관련 함수

SUM(column) : 숫자 열의 합계를 계산합니다.

SELECT SUM(sales) FROM orders; -> 10000

 

AVG(column) : 숫자 열의 평균을 계산합니다.

SELECT AVG(sales) FROM orders; -> 500

 

MAX(column) : 숫자 열의 최댓값을 찾습니다.

SELECT MAX(sales) FROM orders; -> 1000

 

MIN(column) : 숫자 열의 최솟값을 찾습니다.

SELECT MIN(sales) FROM orders; -> 100

 

COUNT(column) : 열의 레코드 수를 반환합니다.

SELECT COUNT(*) FROM orders; -> 10

 

ABS(number) : 숫자의 절대값을 출력합니다.

SELECT ABS(100-150);  -- 50

 

MOD(분자, 분모) : 분자를 분모로 나눈 나머지를 구합니다. (%와 동일)

SELECT MOD(100, 3); -- 1

 

CEILING(number) : 소수점을 올림한 값을 반환합니다.

SELECT CEILING(3301.3123); -- 3302

 

FLOOR(number) : 소수점을 내림한 값을 반환합니다.

SELECT FLOOR(1523.5779); -- 1523

 

TRUNCATE(number, pos): 숫자를 소수점 이하 자리수에서 버린 값을 반환합니다.

SELECT TRUNCATE(1523.5779, 2); -- 1523.57
SELECT TRUNCATE(1523.5779, -2); -- 1500

 

ROUND(number, pos)숫자를 소수점 이하 자리수에서 반올림한 값을 반환합니다.

SELECT ROUND(1523.5779, 3); -- 1523.578

 

SQRT(number): 숫자를 제곱근 값을 반환합니다.

SELECT SQRT(100); -- 10

 

POW(number, n): 숫자를 n 제곱한 값을 반환합니다.

SELECT POW(5, 3); -- 125

 

RAND() : 0 ~ 1 사이의 랜덤값을 반환합니다.

SELECT FLOOR(RAND() * 100)+ 1; -- 1 ~ 100 사이 랜덤한 값 반환

조건함수

IF(expr, true_value, false_value) : 조건에 따라 다른 값을 반환합니다.

SELECT IF(sales > 500, 'High', 'Low') FROM orders; -- 'Low'

 

IFNULL(column, value) : 컬럼이 NULL이라면 대체할 값을 출력합니다.

SELECT IFNULL(data, '자료없음') FROM library; -- '자료없음'

 

CASE expression WHEN value1 THEN result1 WHEN value2 THEN result2 ... ELSE else_result END : 다중 조건에 따라 다른 값을 반환합니다.

SELECT CASE 
		WHEN sales > 500 
        THEN 'High' 
        WHEN sales > 100 
        THEN 'Medium' 
        ELSE 'Low' END 
   FROM orders; -- 'Medium'

 

COALESCE(value1, value2, ...) : NULL이 아닌 첫 번째 인수를 반환합니다.

SELECT COALESCE(NULL, NULL, 'apple', 'banana'); -- 'apple'

정규식

매칭

• . : 문자 하나, ex) ... - 문자열의 길이가 세 글자 이상인 것
• | : 또는 (OR) |로 구분된 문자에 해당하는 문자열을 찾음 ex) a|b라면 a 또는 b에 해당하는 문자열을 찾음
• []: []안에 나열된 패턴에 해당하는 문자열을 찾음
• ^: 시작하는 문자열을 찾음
• $: 끝나는 문자열을 찾음

수의 제한

• *: 0회 이상 나타나는 문자
• +: 1회 이상 나타나는 문자
• {m, n}: m회 이상 n회 이하 나타나는 문자
• ?: 0 또는 1회 나타나는 문자

문자 그룹

• [A-z]또는 [:alpha:] 또는 \a: 알파벳 대문자 또는 소문자인 문자열을 찾음
• [0-9] 또는 [:digit:] 또는 \d :숫자인 문자열을 찾음

부정

• [^문자]: 괄호 안의 문자를 포함하지 않은 문자열을 찾음

예시

# (1) apple 또는 banana로 시작하는 문자열을 찾고 싶을 때

SELECT *
FROM tb
WHERE data REGEXP ('^apple | ^banana')


(2) 길이 7글자인 문자열 중 2번째 자리부터 abc를 포함하는 문자열을 찾고 싶을 때

SELECT * 
FROM tb
WHERE data REGEXP ('^.abc...$')

(3) 텍스트와 숫자가 섞여있는 문자열에서 숫자로만 이루어진 문자열을 찾고 싶을 때

SELECT *
FROM tb
WHERE data REGEXP (^[:digit:]+$)

 

 

참고한, 그리고 참고할 블로그:

https://velog.io/@solfe/MYSQL-%EC%BD%94%EB%94%A9%ED%85%8C%EC%8A%A4%ED%8A%B8

MYSQL 코딩테스트

https://velog.io/@ekfvnddl99/%ED%94%84%EB%A1%9C%EA%B7%B8%EB%9E%98%EB%A8%B8%EC%8A%A4-SQL-%EC%B4%9D%EC%A0%95%EB%A6%AC

코딩테스트 SQL 모음집

https://velog.io/@coddingyun/SQL-%ED%95%A8%EC%88%98-%EC%A0%95%EB%A6%AC

[SQL] 함수 정리

https://velog.io/@wngud4950/MySQL-%EB%82%B4%EC%9E%A5%ED%95%A8%EC%88%98-%EC%A0%95%EB%A6%AC

MySQL 내장함수 정리

 

 

추가)정규표현식

https://yurimkoo.github.io/analytics/2019/10/26/regular_expression.html

유림's Blog