MySQL 정리

kiki·2024년 2월 23일

mysql 정리

기본기

목록 보기

4/8

수정중에 있습니다. 참고 바랍니다

ORDER

내림차순
```
ORDER BY FLAVOR DESC
```
위와같이 컬럼명 뒤 DESC를 붙여 내림차순으로 정렬이 가능하다.

IN

IN
- 두 개 이상의 값에 해당하는 데이터만 조회하고싶다면 IN을 사용할 수 있다.
- MCDP_CD IN ('CS','GS')와 같이 사용한다면 MCDP_CD값이 CS이거나, GS인 데이터만 조회할 수 있다.
- 하나의 값과 비교하고 싶다면 = 연산자를 사용해라!
다중 컬럼 IN (OR로 묶기 대신)
```
WHERE 'Python'=SKILL_1 OR 'Python'=SKILL_2 OR 'Python'=SKILL_3
=> WHERE 'Python' IN (SKILL_1, SKILL_2, SKILL_3)
```
위와같이 여러 칼럼에 동일한 문자인 'Python'이 있는지 확인해야한다면, 번거롭게 같은 패턴의 코드를 반복해 OR로 묶을 필요 없이 in을 사용하면 된다.
NOT IN
- IN의 반대로 A NOT IN (b,c,d) A가 b,c,d 모두와 같지 않은 경우 true를 반환한다.
- IN은 or 연산으로 하나라도 포함되어있다면 true를 반환하지만, NOT IN은 and 연산이기 때문에 모든 데이터와 !=여야 true를 반환한다.
- 모두와 비교하기 때문에 우측에 null값이 포함되어있는 경우 무조건 false를 반환한다.
- 즉 우측 데이터에 null 값이 포함되어있는 경우는 null을 제외하고 NOT IN을 하길 바란다. 참고문제

DATEDIFF

두 날짜의 차이(day)를 구해주는 함수이다. 아래와 같이 사용
DATEDIFF(END_DATE, START_DATE) -> END_DATE - START_DATE 값(day)을 반환

만약 일수가 아닌 다른 단위로 DIFF를 구하고싶다면 TIMESTAMPDIFF를 사용하면 된다.
TIMESTAMPDIFF('MONTH',START_DATE,END_DATE) -> END_DATE - START_DATE 값(month)을 반환
?: 여기서 만약 두 날짜의 차이가 1달 반 이라면? -> 1이 반환된다. 즉 FLOOR로 값이 반환됨

DATEDIFF를 사용할 땐 주의해야할 것이 있다.
만약 렌트카 기록 데이터를 조회하는 상활을 가정해보자. DATEDIFF로 대여 일수를 구하고자 할 때, DATEDIFF만 쓰면 하루를 빌렸어도 0이 나온다. 그렇기에 DATEDIFF 값에 1을 더해줘야 원하는 값을 얻을 수 있다.

DATE_FORMAT

DATE_FORMAT(HIRE_YMD,'%Y-%m-%d')

날짜 포맷을 바꿔주는 기능이다.

대문자	소문자
%Y = 2024	%y = 24
%M = January	%m = 01
%D = 1st	%d = 01

특정 년/월의 데이터 뽑기
- DATE_FORMAT을 이용해 WHERE절에서 특정 년/월의 데이터만 뽑아낼 수 있다.
- DATE_FORMAT(PUBLISHED_DATE,'%Y')=2021

NULL

IFNULL(C, X)
- C 컬럼에서 NULL값인 데이터는 X로 대체해준다.
```
IFNULL(TLNO,'NONE')
```
ISNULL(C,)
- 값이 NULL인지 아닌지 체크
- NOT ISNULL(C)와 같이 사용해 C 컬럼의 값이 NULL 값이 아닌 데이터를 조회하기 위해 사용한다. 혹은 C IS NULL과 같이 작성할 수도 있다.
- C IS NOT NULL과 같이 조건식을 작성할 수도 있다.
NULLIF(A,B)
- A와 B의 값이 같으면 NULL을 반환하는 함수

JOIN

USING
- 원래 JOIN할 땐 JOIN B ON A.FLAVOR=B.FLAVOR와 같이 ON을 사용하는데, 이 경우 연결 컬럼이 FLAVOR로 동일하니 USING(FLAVOR)와 같이 사용해 코드를 줄일 수 있다.
- 동등 비교만 수행하기 때문에 다른 조건이 필요하다면 ON을 사용해야한다.
- USING을 사용하면 중복된 열 이름을 지정(예/A.FLAVOR)할 필요 없어 편리함

UNION

쿼리의 결과를 수직으로 합친다. 동일한 열 수와 유사한 데이터 타입을 가져야함

SELECT * FROM A
UNION
SELECT * FROM B

UNION ALL은 중복도 포함하며, UNION ALL은 중복 row를 제외한다.

String

LIKE
- 데이터에 특정 문자의 포함 여부를 확인해준다.
- ADDRESS LIKE "%강원도%"
- 혹은 NAME LIKE "김%"과 같이 사용해 김으로 시작하는 이름을 갖는 데이터만 뽑아볼 수 있다.
- ADDRESS LIKE "[ABC]" 대괄호를 사용하면 첫번째 문자만 확인할 수 있나보다. 이 경우는 첫번째 문자가 A, B, C 중 하나인지 확인하는 코드다.
- NAME LIKE "[^김]"과 같이 ^를 사용하면 김씨가 아닌 이름을 가진 데이터를 모두 뽑을 수 있다.
- like 적용시 대소문자는 구분되지 않고 조회된다.
left, right, SUBSTRING
- sql의 슬라이싱으로 볼 수 있겠다. left는 왼쪽에서부터 몇 글자를 가져올 것인지, right는 오른쪽에서부터 몇 글자 가져올 것인지
- SUBSTRING(STR, 4,3)와 같이 작성하면, STR이라는 문자열의 4 인덱스부터 3개의 글자를 가져오겠다는 이야기. 참고로 mysql에서 인덱스는 1부터 시작함

함수

AVG(컬럼명)
- 평균값을 구해준다.
ROUND(값, 인자)
- 인자가 없다면 반올림 값을 구해 정수를 반환한다.
- 만약 인자가 1이라면 소수점 두번째의 자릿수를 반올림해준다, 즉 소수점 아래 한자리까지 표현한다.
TRUNCATE(값, 인자)
- 소수점 이하 자리를 버리는 함수
- 인자가 음수라면 소수점 이하는 버리고, 정수를 지정된 자릿수까지 0으로 표시
CONCAT(인자1, 인자2, ...)
- 인자들을 CONCAT해준다.
- CONCAT에는 인자로 숫자가 들어와도 자동 타입 CAST가 되는데, 그렇기 때문에 반환값은 더이상 숫자가 아닌 문자가 된다.
- 하지만 NULL이 들어오면 결과는 NULL
CAST(값 AS [TYPE])
- TYPE을 변환해주는 함수이다. 주로 CHAR을 INT형으로 변환할 때 쓰곤하는데, CAST(~ AS SIGNED)와 같이 사용한다.

LIMIT

조회 데이터 갯수 제한
- LIMIT 1과 같이 쓰면 조회할 데이터의 갯수를 하나로 제한할 수 있다.

BETWEEN

AGE BETWEEN 20 AND 29
위와 같이 사용하며, 이 때 AGE가 20 이상, 29 이하인 데이터가 조회된다. 즉 BETWEEN은 양 끝 값을 포함한다.

GROUP BY

그룹화할 때 사용한다. 책의 장르별로 그룹화할 수도 있고, 상품별로 그룹화할 수도 있다.
GROUP BY C1, C2와 같이 사용한다. 하나의 속성만 사용해도 되고, 두 개 이상도 가능하다. WHERE 다음에 사용한다.
HAVING
- 그룹화 후 조건을 쓰고싶을 때 사용한다. GROUP BY의 WHERE절이라고 생각하면 된다.
- 보통 집합 함수(COUNT, SUM, AVG 등)와 함께 사용한다.
GROUP BY 사용시, SELECT 절에 올 컬럼으로는 그룹화 대상 컬럼 혹은 집계 함수만 가능하다. 이 때, 집계함수 내에서는 그룹화하지 않았을 때처럼 연산 가능하다. (이게 뭔소리,, 이 문제 참고)

EXISTS

서브쿼리가 반환하는 값이 있는지 확인
EX) WHERE EXISTS(SELECT 1 FROM ~ WHERE ~~)
WHERE EXISTS(서브쿼리)와 같은 식으로 사용하며, 서브쿼리가 반환하는 값이 있는 데이터를 조회 (???)
반대로 조건에 맞지 않는 데이터만 조회하고 싶다면 NOT EXISTS(서브쿼리)사용
성능 면에서 IN보다 좋다.
이 문제에서 처음 봤다! 너무 어려운 문제

WHERE

SELECT * 
FROM FOOD_PRODUCT
WHERE PRICE = MAX(PRICE);

이 코드에서 Invalid use of group function 오류가 나는 걸로 보아 WHERE 절의 컬럼명은 그 컬럼의 하나의 데이터로 인식되나보다

WHERE절은 각 행에 대해 적용되기 때문에 집계함수를 사용할 수 없다!!! 이 문제는 서브쿼리를 써서 해결하면 됨. (CTE를 쓰면 두 번 조회해야함. CTE에서 한 번, 서브쿼리에서 한 번. 그러니까 그냥 서브쿼리 써라)

COUNT

COUNT(컬럼)과 같이 사용한다. (중복 포함)
COUNT(DISTINCT 컬럼)과 같이 사용하면 중복을 제외하고 세어준다.
COUNT(컬럼) 시 NULL 값은 포함되지 않는다. 만약 NULL값을 포함해 테이블의 데이터 갯수를 세고싶다면 COUNT(*)를 사용하면 된다.

연산

몫 구하기
- DIV를 사용하거나 / 후 FLOOR를 사용하는 방법이 있다.
- 4 DIV 2 혹은 FLOOR(4/2)

Replace

Replace(content, '%', '')와 같이 특정 문자를 대체할 수 있다.

IF

IF를 이용해서 조건문이 TRUE일때, FALSE일때 반환할 값을 설정할 수 있다.
EX) IF(SUM(~~), '대여중', '대여 가능')

그 외의 자잘한 팁

count하고 백분율 구하는 법
```
SELECT cnt, COUNT(1) as freq, COUNT(1)/SUM(COUNT(1))*100 OVER() as freq_ratio
FROM order_amount
GROUP BY 1
ORDER BY 2 DESC;
```
- count 값을 sum(count(1)) over()로 나눠줌으로써 백분율을 구할 수 있음
- over이 굉장히... 막강한 함수같다
SELECT DISTINCT CAR_ID와 같이 작성하면 중복되지 않은 car_id 값들을 가져올 수 있다.
조건문에서 UNKOWN은 FALSE로 간주된다고 함. 그래서 NOT IN에서 우측에 NULL이 포함되는 경우 무조건 FALSE가 반환되는 것임.

궁금증

언제 R.ID와 같이 써줘야하는지
- 여기서는 두 테이블에 flavor가 있는데 왜 a.flavor로 안써줘도 잘 되는지.
  - 왜냐면,,,, USING을 써줬기 때문이었다. ON으로 작성하면 a.flavor로 써줘야 오류 안남
  - USING을 사용하는 게 편리하긴 하지만 동등 비교만을 수행하기 때문에 다른 조건이 필요할 땐 ON을 써줘야한다.
DATE_FORMAT의 반환값은 STRING인지 INT인지 뭔지
- chat gpt왈 문자열이라 함
- 근데! 이 문제에서 DATE_FORMAT(PUBLISHED_DATE,'%Y')='2021'도 되고, DATE_FORMAT(PUBLISHED_DATE,'%Y')=2021도 되는데 이게 뭘까?
왜 코드를 대문자로 쓰는지는 -> 참고
이 문제는 좀 아리까리한 게 많다. 일단 like를 "%서울%"로 하면 틀리고, join을 inner로 하던, left로 하던 문제가 없다는 점이다. 아니 뭔가 써놓고보니까 이상하지 않은데, 나중에 sql 더 공부하고 한 번 더 보면 좋을 것 같아서 기록해둔다.
exists 사용이 잘 이해가 가지 않는다. 나중에 보자.

sql 외의 정리

2의 제곱들의 합은 비트 연산(a & b = b)으로 특정 값이 포함되어있는지 아닌지 확인할 수 있다. 즉, a에 b가 더해져있는지 아닌지 확인하려면 위의 & 연산(AND 쓰면 안됨!!)을 진행하면 된다. 참고
실제로 현업에서 비트로 데이터를 저장하는지는 잘 모르겠으나, 프로그래머스 문제엔 비트 연산을 해야하는 문제가 종종있다.
위에서 처럼 A & B = B로 확인할 수도 있지만, 조건으로 쓸 때는 그냥 A & B만 써도 A에 B 비트가 포함되는지(?) 조건을 걸 수 있다. 근데 이건 한 비트의 중복 여부를 확인할 때만 쓸 수 있겠군.

kiki

이전 포스트

프로그래머스 level 0 정리

다음 포스트