[MySQL] 문법 총정리

[jeeyeonLIM]

목적

• Mysql은 평소에 쓰던 문법과 다를 수 있어서 한번 비교 정리가 필요함.
• Impala SQL 정리했던 링크. 참고해서 비교 정리할 것.

목차

• date 형식 다루기
• RANK 함수
• 정규식 - 문자열 다루기
• 기타 함수

[jeeyeonLIM]

date 형식 다루기

• 가능한 date 연산 단위 정리

  YEAR : 연
  QUARTER : 분기
  MONTH : 월
  WEEK : 주
  DAY : 일
  HOUR : 시
  MINUTE : 분
  SECOND : 초

• 단일 날짜 연산

  
  ## PLUS 
  DATE_ADD(날짜, INTERVAL 1 YEAR) 
  DATE_ADD(날짜, INTERVAL 1 MONTH)
  DATE_ADD(날짜, INTERVAL 1 DAY)
  DATE_ADD(날짜, INTERVAL 1 HOUR) 
  DATE_ADD(날짜, INTERVAL 1 MINUTE)  
  DATE_ADD(날짜, INTERVAL 1 SECOND) 

MINUS

DATE_SUB(날짜, INTERVAL 2 YEAR)

- 두 날짜간 연산 
```sql
DATEDIFF(날짜2, 날짜1) # 날짜1 < 날짜2 
TIMESTAMPDIFF(WEEK, 날짜1, 날짜2)  #DAY, MONTH, YEAR,... 변환 가능!

https://programmers.co.kr/learn/courses/30/lessons/59411

• 요일 계산

  • dayname('2019-01-01')
  • 요일 산출 함수, 결과는 Monday, Tuesday ,...

• 예제 : 프로그래머스 오랜기간 보호한 동물(2)

  
  SELECT A.ANIMAL_ID, A.NAME, A.DATETIME AS A_DATETIME, B.DATETIME AS B_DATETIME,
  DATE_ADD( B.DATETIME, INTERVAL 1 YEAR ) AS DATE_ADD,
  DATEDIFF( B.DATETIME, A.DATETIME) AS DATEDIFF,
  TIMESTAMPDIFF(SECOND, A.DATETIME, B.DATETIME) AS TIMESTAMPDIFF,
  DAYNAME(A.DATETIME) AS DAYNAME

FROM ANIMAL_INS A
LEFT OUTER JOIN (SELECT ANIMAL_ID, DATETIME FROM ANIMAL_OUTS) AS B ON A.ANIMAL_ID = B.ANIMAL_ID ORDER BY DATEDIFF( B.DATETIME, A.DATETIME) DESC # 날짜 차이 기준으로 내림차순 정렬

![image](https://user-images.githubusercontent.com/45617225/83777035-e4761980-a6c3-11ea-8a3d-4bbb146140d3.png)

### 일부 추출 
- year 만 추출 
- ```date_part('year',concat_ws('-',substr(dt,1,4),substr(dt,5,2),substr(dt,7,2))) as years```
- ```from_timestamp(date_trunc('week',concat_ws('-',substr(dt,1,4),substr(dt,5,2),substr(dt,7,2))),'yyyy-MM-dd' ) as stdt```
   - week 단위로 끊어서 한 주 시작일자만을 남기고 싶을 때 사용
```sql

SELECT A.DATETIME,
    YEAR(A.DATETIME), MONTH(A.DATETIME), DAY(A.DATETIME), 
    HOUR(A.DATETIME), MINUTE(A.DATETIME), SECOND(A.DATETIME)

FROM ANIMAL_INS A   
LEFT OUTER JOIN (SELECT ANIMAL_ID, DATETIME FROM ANIMAL_OUTS) AS B
ON A.ANIMAL_ID = B.ANIMAL_ID
ORDER BY DATEDIFF( B.DATETIME, A.DATETIME) DESC # 날짜 차이 기준으로 내림차순 정렬 

형 변환

• DATE_FORMAT
• CONCAT_WS + SUBSTR

  
  SELECT DATETIME,
  DATE_FORMAT(DATETIME, '%Y-%m-%d') AS DATE_FORMAT,
  CONCAT_WS('-',SUBSTR(DATETIME,1,4),SUBSTR(DATETIME,6,2),SUBSTR(DATETIME,9,2)) AS CONCAT_WS,
  CONCAT(SUBSTR(DATETIME,1,4),'-',SUBSTR(DATETIME,6,2),'-',SUBSTR(DATETIME,9,2)) AS CONCAT

FROM ANIMAL_INS

![image](https://user-images.githubusercontent.com/45617225/83780888-ba732600-a6c8-11ea-9c91-8e3e2e2754e8.png)

### 비교 
- DATETIME 형태를 DATE_FORMAT 함수 이용해서 '%Y-%m-%d' 형태로 바꿔준 뒤 비교 ~
```sql
SELECT A.ANIMAL_ID, A.DATETIME, B.DATETIME, DATE_FORMAT(A.DATETIME,'%Y-%m-%d'),
    IF(DATE_FORMAT(A.DATETIME,'%Y-%m-%d')<='2016-04-21',1,0)
FROM ANIMAL_INS A
LEFT OUTER JOIN ANIMAL_OUTS B
ON A.ANIMAL_ID = B.ANIMAL_ID 
WHERE A.DATETIME > B.DATETIME

[jeeyeonLIM]

RANK 함수

• 내가 알던 랭크랑 같음.

  
  1. RANK() OVER(partition by 변수명, order by)

• 동순위 같은 RANK, 최종 랭크는 최종 행 수가 되는 방식
• Ex) RANK : 1 2 2 4

2. DENSE_RANK() OVER(partition by 변수명, order by)

   • 동순위 같은 RANK, 순위를 차례로 모두 사용해 나타냄
   • Ex) RANK : 1 2 2 3

3. ROW_NUMBER() OVER(partition by 변수명, order by)

   • 동순위 랜덤 RANK부여, 동순위 내에서는 랜덤으로 순위 부여됨
   • Ex) RANK : 1 2 3 4

   • RANK() OVER (PARTITION BY user_id ORDER BY count(distinct product_id) desc) as rk
   • 유저 id 내에서 가장 많이 구입한 product_id 의 rank 를 생성할 수 있음
   • 주로 중복 허용할 때는 rank(), 중복 허용하지 않을 때는 row_number() 를 사용함.
   • 직관적 설명 참고 ; https://doorbw.tistory.com/221

[jeeyeonLIM]

정규식(string)

• 하나의 문자열을 검사하고 싶을 때는 그냥 like 써서 비교
• 2개 이상의 문자열을 비교하고 싶을 때는 👇

  where 변수명 regexp '검사할문자1|검사할문자2
  where (변수명 like '%검사할문자1') or (변수명 like '%검사할문자2)

• 예제 쿼리 -두방법 비교

  
  select animal_id, name,
  #if((sex_upon_intake like '%Neutered%')or( sex_upon_intake like'%Spayed%'),'O','X') as 중성화
  if( sex_upon_intake regexp 'Neutered|Spayed' ,'O','X') as 중성화

from animal_ins order by animal_id

### regexp 사용 시 정규식
- **`^`** : st로 시작하는 row찾기
```sql
SELECT 변수 FROM 테이블 WHERE name REGEXP '^st';

• $ : end로 끝나는 row 찾기

  SELECT 변수 FROM 테이블 WHERE name REGEXP 'end$';

• '' : jeeyeon이 포함된 row 찾기

  SELECT 변수 FROM 테이블 WHERE name REGEXP 'jeeyeon';

[jeeyeonLIM]

기타 함수

형변환

cast(문자변수 as unsigned) # 문자변수 -> 숫자변환
cast(문자변수 as char) # 숫자변수 -> 문자변환

join ㅡ null

• coalesce(변수명, null대체값)

LEAD LAG

SELECT ANIMAL_ID,ANIMAL_TYPE, lag(ANIMAL_ID,1) over(partition by ANIMAL_TYPE)
FROM ANIMAL_INS

• 요렇게 내려갈 수 있음 lead 는 앞으로

ROLL UP

• WITH ROLLUP

  SELECT ANIMAL_TYPE,SEX_UPON_INTAKE, COUNT(*) AS COUNT
  FROM ANIMAL_INS
  GROUP BY ANIMAL_TYPE,SEX_UPON_INTAKE WITH ROLLUP

CUBE

PARTITION BY