TIL 240831 (SQL 기초다지기2)

1. 특정 문자를 다른 문자로 바꾸기 (replace)

 

replace(컬럼, 현재 값, 변경 값) 

select 컬럼 "현 문자",
           replace(컬럼, 현재 문자, 변경문자) "새 컬럼명" 
from 테이블
where restaurant_name like '%문자%'         //  %바꾸고 싶은 문자%

 

2. 원하는 문자만 남기기 (substr)

substr(컬럼, 시작 위치, 글자 수)

select 컬럼,
           substr(컬럼, 1, 2) " 새 컬럼명"    // 이건 0부터 시작 x
from 테이블
where 컬럼 like '%문자%'    // %바꾸고 싶은 문자%  

 

3. 여러개의 컬럼 문자 합치기 (concat)

concat(원하는 값1, 원하는 값 2, 원하는 값 3, .....)

 

select 컬럼,
           컬럼2
           concat('[', substring(컬럼1, 1, 2), '] ', 컬럼2) "바뀐 이름"
from 테이블
where addr like '%붙이고 싶은값%'

 

붙일 수 있는 문자의 종류

• 컬럼
• 한글
• 영어
• 숫자
• 특수문자

4. 조건에 따라 특정 형식으로 변환하거나 표시 하는법 (IF, CASE)

4 - 1   IF

보통 한가지 조건을 이용할때 쓴다.

if(조건, 조건 충족할때, 조건 불충족할때)

 

select 컬럼 "컬럼 이름 변경"
//  "" 안에 있는건 필수는 아니지만 바뀐 내용과 원래 내용을 구분하기 위해 추가함
if(컬럼 = 'apple', '사과', '과일') "바뀐 컬럼"        // 컬럼이 = ' ' 안에 있는거면 사과,  ' ' 에 있는 문자가 아니면 과일
from 테이블

 

 

4 - 2   CASE

보통 두가지 이상의 조건을 이용할 때 쓴다.

 

case when 조건1 then 값1
     when 조건2 then 값2
     else 값3
     end 새컬럼 이름 // 이 뒤에 새 컬럼 이름을 지어주면 된다.

 

5. 문자와 숫자 계산할때 오류 나는 이유

 

이 사진에 rating이라는 컬럼은 숫자로 되어있어 숫자인걸로 보이지만 사실은 문자형이다.

왜나하면 사진을 보면 각 컬럼 옆에 A-Z와 123이 있는데 이는 문자형과 숫자를 나타내는 것이다.

그래서 rating 컬럼이 계산이 안되는 것이다.

 

cast  라는 함수는 데이터베이스가 문자라고 인지하는것을 숫자라고 알려주고

숫자라고 알고있는것을 문자라고 알려주는 역할을 한다.

• 숫자형으로 변경해줄때

// 기본 구조
select cast(컬럼 as int)
from 테이블

 

// 컬럼에 문자가 있을때 문자형을 숫자형으로 바꿔주고 숫자 컬럼으로 바꿀때
select cast(if(컬럼 = '조건', '바꿀 값', 컬럼) as decimal)  // decimal = 숫자데이터 형식으로 변환
from 테이블

 

• 문자형으로 변경해줄때

// 기본 구조
select cast (컬럼 as char)
from 테이블

// 숫자형 컬럼을 문자형 컬럼으로 바꿔주고 문자형 컬럼과 결합할때
select concat(문자형 컬럼, '-', cast(숫자형 컬럼 as char)) '결합 컬럼'
from 테이블

 

 

 

6. 여러 번의 연산을 한 번에 끝내기 (Subquery)

subquery가 필요한 경우

• 여러번의 연산을 수행해야 할 때
• 조건문에 연산 결과를 사용해야 할 때
• 조건에 Query 결과를 사용하고 싶을 때

기본 구조

// 서브쿼리문에서 SQL문을 한번 실행하고 외부쿼리를 실행하는 구조같다.

// 여러번의 연산이 필요할때 한번에 계산하지 않고 한단계씩 계산 하는 구조이다.

select 컬럼, 컬럼2, 컬럼3
from 테이블
(
select 컬럼, 컬럼2, 컬럼3
from 테이블
) a

 

7. 필요한 데이터가 다른 테이블에 있을 때 조회하기 (JOIN)

JOIN 기본 구조

 

- LEFT JOIN (공통 컬럼을 기준으로 모든 데이터를 조회)
select 조회 할 컬럼, 조회 할 컬럼, 조회 할 컬럼
from 테이블1 a left join 테이블2 b on a.공통컬럼명=b.공통컬럼명

 

- INNER JOIN (공통 컬럼을 기준으로 두 테이블에 있는 값만 조회)
select 조회 할 컬럼, 조회 할 컬럼, 조회 할 컬럼       //  공통된 컬럼은 1번만 적기
from 테이블1 a inner join 테이블2 b on a.공통컬럼명=b.공통컬럼명

 

8. 조회한 데이터에 아무 값이 없을때

기본 join 구조에 where 컬럼 is not null 로 값이 있는 데이터만 조회하게 할수있다.

 

기본적인 구조

select 조회 할 컬럼, 조회 할 컬럼, 조회 할 컬럼
from 테이블1 a
left join 테이블2 b 
on a.공통컬럼명=b.공통컬럼명
where 컬럼 is not null

 

 

또는 조건문(if 조건, 컬럼, 대체값) 이나 coalesce(컬럼, 대체값)을 추가해 다른 값으로 대체할 수 있다.

 

9. 조회한 데이터에 예상하지 못한 값이 있을때

 

SELECT 컬럼
CASE
WHEN 범위 THEN 대체값
WHEN 범위 THEN 대체값
ELSE 컬럼
END AS "대체값의 컬럼"
FROM 테이블

 

이렇게 범위를 지정해 대체 값으로 조회되게 한다.

 

10. 윈도우 함수 (Window Funtion - RANK, SUM 등)

기본 구조

selcet 에 들어간다.

window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)

 

window_function : sum, avg 같은 기능명이 있다.

argument : 함수에 따라 생략 할 수 있다.

paritition by : 그룹을 나눌때 필요 (group by  절과 비슷하다.

order by : 정렬 할때 필요

 

11. 데이터를 날짜 형식 으로 바꾸기

기본 구조

// 년,월,일 동시에
select date(컬럼) 날짜
from 테이블

 

날짜 포맷을 통해 년, 월, 일, 요일 별로 구분이 된다.

// 컬럼 마다 년, 월, 일, 요일 구분 가능
select date(date),
           date_format(date(컬럼), '%Y') "년",
           date_format(date(컬럼), '%m') "월",
           date_format(date(컬럼), '%d') "일",
           date_format(date(컬럼), '%w') "요일"
from 테이블