[코테]SQL의 모든것

• 코딩 테스트에서 사용되는 SQL 문법의 모든 것을 요약

기본형

//테이블 자체를 출력
SELECT *
FROM (테이블);

select distinct (행) as (행이름)
from
where (행) like '%ABC%'
order by (행) asc/desc
limit X//상위 레코드 X개

행 함수(행 값 융합)

select count(행)
from (테이블)

select sum(행)
from (테이블)

select avg(행)
from (테이블)

select max(행)
from (테이블)

select min(행)
from (테이블)

//행의 모든 데이터를 하나의 값으로 concat 수행
select group_concat(행)
from (테이블)

행 함수(행 값 수정)

//if 함수
//행 자체에도 조건을 걸고, 즉석으로 값을 설정할 수 있음
//단 Null데이터는 is null or is not null을 통해 체크해야 함
select if(행 = X, YES, NO)
from (테이블)

//DATE_FORMAT 함수
//YYYY-MM-DD 형태 행에 적용 가능
//%Y: YYYY 형태 리턴
//%y: YY 형태 리턴
//%M: 영어(March) 형태 리턴
//%m: mm 숫자 형태 리턴
//%D: 1st 형태 리턴
//%d: dd 숫자 형태 리턴
select date_format((행), '%y-%m-%d')
from (테이블)

//반올림 함수  ROUND(행, N)
//특정 행과 반올림 자릿수를 입력

//N : 소숫점 아래 N째 자리**까지 반올림하여 표시
//-N : 반올림하여 10의 N승 자리**까지 표시
//0 : 반올림하여 1의 자리**까지 표시

select round(행, 2)//소수 둘재자리수까지 출력
from 테이블

//case when then end
//행에 따라 구체적인 값을 서정해주고 싶을 때 사용
select case
when (행 조건 1) then (설정값 1)
when (행 조건 2) then (설정값 2)...
else (설정값 3)
end
from (테이블)

//concat: 출력 값에 추가 값을 붙임
select concat(값(ex. max(행)), 'cm')
from 테이블명;

where or and in not between and exists like

select (행)
from (테이블)
where (행) is not null

where (행) = A or/and (행) = B// 기존 or and 효과

where (행) in (A, B)//행 값이 해당 범위 내에 존재할 때 리턴

//이런 방식으로 값을 (,,...,) 형식으로 체크할 수 있음
select a.id, b.fish_name, a.length
from fish_info as a, fish_name_info as b
where (a.fish_type, a.length) in (
        select
            fish_type,
            max(length)
        from fish_info
        group by fish_type
      )
and   a.fish_type = b.fish_type
order by id;

where not (행) = A//결과 반전

where (행) between A and B//A, B값 사이에 행 값이 존재하는 경우 리턴
where date between '2020-10-10' and '2024-12-28'

//서브 쿼리의 결과 존재 유무를 확인
where exists (select * from table2 where condition)

//와일드 카드 %
where (행) like `%ABC%`//ABC가 포함된 행 리턴
`ABC%`//ABC로 시작하는 행 리턴
`%ABC`//ABC로 끝나는 행 리턴

//와일드 카드 _: 빈칸이라 의미하면 쉬움
	%와 달리 정확히 해당 공간에만 빈 문자로 구성됨을 의미
where (행) like '_ABC_'//ABC가 [1, 4]영역에 존재하는 5글자 리턴

group by ... having
특정 (행)에 따라 그룹화
행 함수(행 값 융합)과 함께 자주 사용
having을 통해 그룹핑 조건을 설정할 수 있음
묶었을 때 기준 조건

SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID HAVING COUNT(*) >= 2
ORDER BY USER_ID ASC, PRODUCT_ID DESC;//행이 여러 개면, 모든 행 값이 같을 때 기준

order by
마지막에 적용
행1 기준 오름차순 -> 행2 기준 내림차순 순으로 정렬

select (행)
from (테이블)
order by 행1 asc, 행2 desc

distinct

//중복 제거 및 고유 값 리턴
select distinct (행1)
FROM Customers;

//고유 조합 리턴
select distinct (행1), (행2)
from (테이블);

JOIN

• join은 중요하기 때문에 별도로 뺌
• join: 두 테이블을 엮기

1. inner join: 두 테이블의 교집합(공통된) 행을 추출

• inner join = join

SELECT <열 목록>
FROM <첫 번째 테이블>
    (INNER) JOIN <두 번째 테이블>
    ON <조인 조건>
[WHERE 검색 조건]

#INNER JOIN을 JOIN이라고만 써도 INNER JOIN으로 인식합니다.

2. outer join: 두 테이블의 합집합 행을 추출

• left (outer) join [*방향 자체로도 outer이므로 생략 가능]
• right (outer) join
• full (outer) join

SELECT <열 목록>
FROM <첫 번째 테이블(LEFT 테이블)>
    <LEFT | RIGHT | FULL> OUTER JOIN <두 번째 테이블(RIGHT 테이블)>
     ON <조인 조건>
[WHERE 검색 조건]

3. cross join: 두 테이블의 각 행의 조합을 하나의 값으로 만들어 조인
   A(1, 2, 3) B(a, b, c)
   (1, a) (1, b) (1, c)
   (2, a) (2, b) (2, c)
   (3, a) (3, b) (3, c) = 3 X 3 = 9개

4. self join: 자기 자신과 조인(별도 문법X)

5. on: 조인에서 조건을 줄 때 사용

//사용 예시
SELECT a.FLAVOR 
FROM FIRST_HALF AS a LEFT JOIN ICECREAM_INFO AS b 
ON a.FLAVOR = b.FLAVOR
WHERE a.TOTAL_ORDER > 3000 AND b.INGREDIENT_TYPE LIKE 'fruit_based'
ORDER BY a.TOTAL_ORDER DESC;

날자 계산

• datediff(종료, 시작)

SELECT HISTORY_ID, CAR_ID, 
	   DATE_FORMAT (START_DATE, "%Y-%m-%d") AS START_DATE, 
	   DATE_FORMAT (END_DATE, "%Y-%m-%d") AS END_DATE,
CASE WHEN DATEDIFF(END_DATE, START_DATE) < 29 then '단기 대여'
            ELSE '장기 대여' 
            END AS  RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE LIKE '2022-09-%'
ORDER BY HISTORY_ID DESC;

• timestampdiff(단위, 종료, 시작)
• SECOND : 초
• MINUTE : 분
• HOUR : 시
• DAY : 일
• WEEK : 주
• MONTH : 월
• QUARTER : 분기
• YEAR : 연

SELECT TIMESTAMPDIFF(DAY,   '2021-07-13', '2021-06-12') AS [일차이]
     , TIMESTAMPDIFF(MONTH, '2021-07-13', '2021-06-12') AS [개월차이] 
     , TIMESTAMPDIFF(YEAR,  '2021-07-13', '2021-06-12') AS [년차이]

SELECT HISTORY_ID,
        CAR_ID,
        DATE_FORMAT(START_DATE, '%Y-%m-%d') AS START_DATE,
        DATE_FORMAT(END_DATE, '%Y-%m-%d') AS END_DATE,
IF(TIMESTAMPDIFF(DAY, END_DATE, START_DATE) >= 29, '장기 대여', '단기 대여') AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE MONTH(START_DATE) = 9
ORDER BY HISTORY_ID DESC;

테이블 결합
테이블 간 결합 연산을 의미
union: 중복된 행을 제외한 두 테이블의 합 테이블을 출력(합집합)
union all: 중복된 행을 포함한 두 테이블의 합 테이블을 출력
intersect: 두 테이블에서 공통된 행만을 테이블로 출력(교집합)
except: 첫번째 쿼리 결과에서 두번째 쿼리 결과 행을 제외한 행을 반환(차집합)

비트 연산[★]
&, ^, |을 통해 비트 단위 데이터 연산 및 저장을 수행함
select 문을 통해 해당 행 데이터를 뽑고, 이를 연산에 적용

SELECT d.ID, d.EMAIL, d.FIRST_NAME, d.LAST_NAME
FROM DEVELOPERS AS d
WHERE d.SKILL_CODE &
(
SELECT s.CODE
FROM SKILLCODES AS s
WHERE s.NAME = 'Python'
)
OR d.SKILL_CODE &
(
SELECT s.CODE
FROM SKILLCODES AS s
WHERE s.NAME = 'C#'
)
ORDER BY d.ID ASC;

with
임시 테이블을 형성하는 키워드