SQL 기본문법

1. SELECT,   FROM

select : 데이터를 가져오는 기본 명령어로, 데이터를 조회하는 모든 Query 에 사용됨

from  : 데이터를 가져올 테이블을 특정해주는 문법

 

-> select에 *을 넣으면 전체 컬럼을 선택하고, 컬럼의 이름을 적으면 해당 컬럼만 선택하여 보여준다.

-> select와 from은 쿼리에 무조건 포함.

 

1-1) Aliases (별칭)

테이블이나 테이블의 컬럼에 임시로 별칭 지정이 가능 (더 간단히 읽고 쓰기 위해서)

방법1)  컬럼1 as 별명1
방법2)  컬럼2  별명2
-> as 는 써도 되고 안써도 된다.

구분	영문, 언더바, 숫자	특수문자, 한글
방법	별칭만 적음	"별명"  |  큰 따옴표 안에 적어준다.
예시	age10	"age 50", "나이"

 

 

2. WHERE

: 특정 조건으로 필터링할때 사용 (between, in, like)

2-1) 비교연산

비교연산자	의미
=	같다
<> , !=	같지 않다(다르다)
>	크다
>=	크거나 같다
<	작다
<=	작거나 같다

 

cf) 파이썬과 같다(==) 표기가 다름!

 

2-2) Between

:범위내에서 필터링

where column between a and b

 

-> a부터 b까지 범위내에 해당하는 것을 가져온다.

where column not between a and b

 

-> a~b 범위에 해당하지 않는 값을 선택

 

2-3) In

: 몇가지 종류를 필터링

where column in (A,B,C)

 

-> column의 값이 A or B or C 에 해당하는 값이면 선택한다.

where  'A'  in (column 1,2,3)

 

-> 컬럼 1 or 컬럼 2 or 컬럼 3의 값이  'A'인지 확인

 

 

 

where column not in (A,B,C)

 

-> A or B or C 가 아닌 값을 가져온다.

 

 

2-4) Like

: 특정한 문자를 포함할 때

where column like '%문자%'

 

2-4-1) 특정한 문자로 시작할 때 : '문자%'

2-4-2) 특정한 문자가 포함될 때 '%문자%'

2-4-3) 특정한 문자로끝날 때 '%문자' 

where column not like '%문자%'

 

-> 주어진 패턴과 일치하지 않는 행을 선택한다.

 

2-5) Null

null은 값이 없이 비어있는 것이라고 생각하면 된다.

is null : null값만 찾기

is not null : null값 제외하고 찾기

 

조건문 등에 null을 조건으로 넣을수도 있다.

 

 

2-6) Exists

: 서브쿼리의 결과 존재 여부를 기반으로 필터링을 수행

 

: EXISTS는 서브쿼리가 한 개 이상의 행을 반환할 경우 참(TRUE)을 반환하고, 그렇지 않을 경우 거짓(FALSE)을 반환

-> 서브쿼리 조건에 해당할 때, 외부 쿼리를 수행한다. 

SELECT column1, column2, ...
FROM table1
WHERE EXISTS (SELECT 1 FROM table2 WHERE condition)

 

: 서브쿼리의 조건 condition이 만족되면, 서브쿼리는 결과로 1을 반환하고,

(SELECT 1을 사용하는 이유는 내부의 내용은 필요없이, 서브쿼리의 결과가 존재하는지 아닌지만 중요하기 때문에 성능 최적화를 위해서이다.)

이는 외부쿼리의 EXISTS에 true로 반환되어, 외부 쿼리를 수행한다.

(이때 쿼리는 table1의 모든 행에 대해서 서브쿼리를 실행한다. 각 행마다 서브쿼리를 실행하지만 한번 일치하는 결과를 찾으면  true를 반환하며 해당 서브쿼리를 중지하고, 다음 행으로 넘어가서 다시 서브쿼리를 실행한다.)

 

 

SELECT column1, column2, ...
FROM table
WHERE NOT EXISTS (SELECT 1 FROM other_table WHERE condition);

 

: 서브쿼리의 조건을 만족하지 않는 경우 true를 반환하며, 외부 쿼리를 수행한다.

 

 

2-7) 논리연산

(여러 개의 조건으로 필터링)

논리연산자	의미
and	그리고
or	또는
not, !	아닌

 

 

주석

주석 처리하고 싶은 부분 앞에 # 혹은 -- 을 사용 ( --뒤에 한 칸 띄워줘야 함)

여러 줄을 동시에 주석처리하고 싶으면 /**/ 사용
/* ~~~~~
~~(내용)~~
~~~~*/ 
-> 이렇게 사용

 

 

3. 집계함수 ( Sum, Average, Min, Max, Count)

여러 행의 데이터를 하나의 결과값으로 요약하는 함수.

-> 얘네들은 select에서만 사용할 수 있다. (where에서 안됨) -> 집계함수를 조건으로 걸고 싶으면 having 사용

 

집계함수는 전체 데이터를 대상으로 사용하거나, 특정 컬럼을 기준으로 사용할 수 있다.

전체 데이터를 기준으로 하면 -> 전체 행을 1개의 결과값으로 나타내고 (ex: 데이터 전체의 사용금액의 합)
특정 컬럼을 기준(ex: 날짜별)으로 하면 -> 반드시 group by 절을 사용해 주어야 한다. (ex: 날짜별 사용금액의 합)

 

1) sum(column) : 해당 column의 값을 모두 합침

2) avg(column) : 해당 column 값의 평균을 구함

3) min(column) : 해당 column의 최소값을 찾음

4) max(column) : 해당 column의 최댓값을 찾음

5) count() : 모든 행의 개수를 계산

-> count(1 or *) : 전체 행의 총 개수를 알려줌(null값 포함)
-> count(column): 해당 column 행의 총 개수를 알려줌(null값 제외)

 

 

3-1) DISTINCT

: 중복 제거

distinct column : column에서 중복값을 제거하여, 중복 없이 값을 보여준다.

 

 

4. GROUP BY

: 그룹별로 나누기

group by column : 결과를 column의 고유한 data별로(중복x) 그룹화한다.

group by column1, column2 : 2개 이상의 열을 선택하면, 열들의 고유한 조합에 따라 결과를 그룹화한다. 

 

SQL은 group by 절에 나열되지 않은 열을 선택하는 것을 허용하지 않는다.

-> 즉, select에서 선택한 열은 다 group by에 나열해야 된다! 

 

 

앞서 언급한, 집계 함수에서 특정 칼럼을 기준으로 할 때 group by를 사용해야 하는 이유는

-> 집계함수는 1개의 행을 반환한다. 그런데 이때 select에서 선택한 기준으로 할 칼럼은 n+α개의 값을 반환할 것이기 때문에 오류가 뜨게 되는 것이다. 따라서 group by를 사용하여 선택한 칼럼의 고유한 n개의 값에 각각 집계함수를 적용하여, n개의 값이 각각 1개씩의 집계함수 결과를 가지도록 해주어야 한다. 

 

 

 

4-1) Having 

:  group by에 의한 결과를 필터링 -> 집계 함수 필터링 가능 

(집계 함수로 한 계산을 조건으로 걸 수 있다.) 

 

having은 group by가 앞서 선행되어야 한다!

+) group by 절이 없어도 모든 행을 하나의 그룹으로 간주한다면 having만 사용이 가능하다. (하지만 그러면 굳이 having을 쓸 이유가..)

 

having  vs  where

: where은 단일 조건 (기존의 테이블 형태) 으로만 필터링을 걸고 
: having은 group별 결과값 (ex: 집계함수 연산 값)으로 필터링 한다.

 

GROUP BY와 집계 함수의 관계

SQL에서는 GROUP BY가 사용되면, 결과 집합은 그룹 단위로 요약됩니다. 이때 사용되는 GROUP_CONCAT(), SUM(), AVG(), COUNT() 같은 집계 함수(Aggregate Function)는 각 그룹 내부의 데이터에 대해서만 동작합니다.
즉, 각 그룹에 대해서 집계 함수가 적용된다.

 

#ex) column1의 data를 기준으로 column2의 값을 합쳤다. 이때 범주별로 합쳐진 column2에 조건을 거는 것
select column1, sum(column2)
from table
group by column1
having sum(column2) > 4

 

cf) SQL의 작동 순서
FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY
이걸 보면 WHERE이 GROUP BY보다 먼저 작동하기 때문에, 그룹화 된 결과를 필터링 할 수 없다는 걸 알 수 있다.
따라서 GROUP BY 다음에 실행되는 HAVING을 통해 그룹별로 필터링을 걸 수 있다.

 

 

5. ORDER BY

: 순서대로 정렬하기

1) order by column (asc) : column을 기준으로 오름차순 정렬 (오름차순이 기본이라 asc 생략가능)

2) order by column desc : column을 기준으로 내림차순 정렬

3) order by column1, column2 : column1을 기준으로 정렬한 뒤 같은 값끼리는 column2로 정렬 

 

 

5-1) Limit

: 쿼리 결과의 갯수를 제한

limit은 보통 order by로 정렬한 뒤 원하는 범위의 개수를 가져온다.

limit n : 결과를 (맨처음) n개만 반환 

limit n1, n2 : n1 인덱스부터 시작(0부터 시작함)해서, n2개의 값을 반환

 

 

(4) group by & (5) order by는 select에서 조건을 넣어 연산한 칼럼(ex : sum, count ...) 을 기준으로
할 수도 있음.   -> 조건을 그대로 다시 넣거나 select의 칼럼 순서대로 번호(1,2,3) 사용도 가능
(where, having은 이렇게 안됨)

 

 

순서 중요! 순서 틀리면 에러남
select
from
where
group by
having
order by
limit

 

 

 

6.  Replace

: 특정 문자를 다른 문자로 바꾸기

replace(바꿀 컬럼, 현재 값, 바꿀 값)

 

 

 

7.  Substring(substr)

: 원하는 문자만 남기기

substr(조회 할 컬럼, 시작 위치(1부터 인덱싱), 추출할 문자의 총 길이)

 

 

 

8.  Concat

: 여러 컬럼의 문자를 합치기

concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, .....)

 

붙일 수 있는 문자의 종류 : 컬럼, 한글, 영어, 숫자, 기타 특수문자

#예시
concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"

 

8-1) GROUP_CONCAT()

: 그룹 안에 있는 여러 문자열 값을 하나의 문자열로 합쳐주는 역할

GROUP_CONCAT()은 MySQL에서만 제공되는 문자열 집계 함수.

GROUP_CONCAT(column1 ORDER BY column2 SEPARATOR '|')

 

ORDER BY

그룹 내 정렬 기준 설정

SEPARATOR 'x'

값들 사이에 넣을 구분자 설정 (기본값은 ',')

 

 

 

9.  Date_format

: 날짜를 지정된 형식으로 포맷

date_format(date column, format)

포맷 지정자
%a	요일의 약어 (일요일부터 토요일까지)
%b	월의 약어 (1월부터 12월까지)
%c	월의 숫자 (0부터 12)
%D	일자를 숫자로 표시하고 접미사를 붙 (1st, 2nd, 3rd, …)
%d	일자를 숫자로 표시 (01부터 31)
%e	일자를 숫자로 표시 (0부터 31)
%H	시간 (00부터 23)
%i	분 (00부터 59)
%M	월의 전체 이름 (January부터 December까지)
%m	두 자리 월 (01부터 12까지)
%Y	연도를 4자리 숫자로 표시

 

#예시
SELECT DATE_FORMAT("2022-09-05", "%Y-%c")
-- 출력: 2022-9

 

 

9-1) DATEDIFF

: 날짜 차이 구하기

DATEDIFF(날짜1, 날짜2);

 

날짜1 - 날짜2

 

 

9-2) TIMESTAMPDIFF

: 특정 단위로 시간/날짜 차이 구하기

TIMESTAMPDIFF(단위, 날짜1, 날짜2);

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

 

: 단위에 해당하는 '날짜1 - 날짜2' 차이

 

 

10.  If

: 조건에 따라 연산하기 (조건1개)

if (조건, 조건을 충족할 때, 조건을 충족하지 못할 때)

#예시
if(addr like '%평택군%', replace(addr, '문곡리', '문가리'), addr) "바뀐 주소"

 

 

 

11.  Case

: 여러 조건을 주고 싶을 때

case  when 조건1 then 값(수식)1
          when 조건2 then 값(수식)2
          else 값(수식)3
          end

 

꼭 end로 마쳐줘야 함!

else는 필요 없으면 안넣고 end로 바로 끝내도 됨

 

 

 

12.  Subquery

: 여러 번의 연산을 한 번의 SQL문으로

서브 쿼리는 여러번의 연산을 수행하거나, 조건문에 연산 결과를 사용하거나, 조건에 쿼리 결과를 사용하고 싶을 때 등등 쿼리의 결과값을 가지고 추가 연산을 할 때 사용하기 좋다.

join, union 동작 수행을 대체 할 수 있는 방법 제공

 

 

1. 인라인 뷰 (상위 쿼리가 하위 쿼리를 테이블로 사용) → 가장 많이 사용

하위 쿼리는 ( ) 안에 작성.
( ) 밖에 하위 쿼리를 명명할 약어가 필요하다. 보통 a,b,c,,,,x,y,, 등등. 하위 쿼리는 여러개 가능

상위 쿼리는 하위 쿼리를 테이블로써 사용하므로, 하위 쿼리의 결과로 나오는 칼럼만 다시 선택할 수 있다.
(예시에서 상위 쿼리가 column3 불러오기 불가능)

상위 쿼리는 똑같이 select from 사용하며, from에 테이블 대신 하위 쿼리가 들어간다.
이후 where절이나 group by 등등은 똑같이 from절이 끝난 다음에 사용한다.

 

select x.나이, x.직업, y.결제금액 
from(	select 나이, 직업, 이름
		  from basic.theglory
		  where 나이>=33
	  )as x, theglory2 y
WHERE x.이름 = y.이름;

+) from 뒤에 저렇게 테이블 여러개를 둘 수도 있다. 하지만 저러면 y의 행 1개마다 x 테이블 전체가 연결돼서 데이터가 엄청 중복된다. 굉장히 비효율적이라서 비추. join을 사용하는게 좋을듯하다.

 

 

2. 중첩(일반) 서브쿼리

-> 상위 쿼리의 where절에서 하위쿼리를 조건으로 사용

(이때는 약어 필요 없음)

# 예시
select *
from basic.theglory 
where 나이 > (select 나이 from basic.theglory where 이름='마동석')

 

 

3. 스칼라 서브쿼리

-> 상위 쿼리의 select절에서 하위쿼리를 컬럼으로 사용

(약어 대신 별칭 사용, 없어도 되기는 하지만 가독성이 똥이라 있는게 좋을 듯 하다)

-- 예시
select 이름
, 나이
, (select count(*) from theglory2 where theglory2.이름=theglory.이름) as same_name_cnt
, (select sum(결제금액) from theglory2 where theglory2.이름=theglory.이름)as same_name_sumamount
from basic.theglory

 

 

 

13. Union

: 테이블의 수직 결합

※ 테이블 간 컬럼의 순서가 같고, 데이터의 형식이 같아야 한다.

union : 중복되는 행을 하나로 표기 (중복 제거)
union all : 모든 행을 표기 (중복 제거하지 x)

 

select 컬럼1, 컬럼2, 컬럼3
from 테이블명1

union (all) 

select 컬럼1, 컬럼2, 컬럼3
from 테이블명 2

 

 

14.  Join

: 서로 다른 테이블을 합칠 때 (수평)

 

Join 종류

 

※ join은 테이블 간 같은 값을 공유하는 칼럼이 있어야 한다. (공통컬럼 찾기)

조인 종류	특징
INNER JOIN	두 테이블에서 일치하는 값을 가진 행을 출력 (교집합)
LEFT JOIN	왼쪽 테이블의 모든 행과 그에 일치하는 오른쪽 테이블의 행을 반환. 왼쪽과 일치하는 오른쪽 항목이 없으면 NULL이 출력 (왼쪽에는 없고 오른쪽에만 존재하는 값은 출력되지 않는다.)
RIGHT JOIN	오른쪽 테이블의 모든 행과 그에 일치하는 왼쪽 테이블의 행을 반환. 오른쪽과 일치하는 왼쪽 항목이 없으면 NULL이 출력 (오른쪽에는 없고 왼쪽에만 존재하는 값은 출력되지 않는다.)

-> inner join과 left join이 주로  많이 사용된다.

 

join에 where를 이용해서 조건에 맞는것만 조인할 수도 있으며,
서브 쿼리를 함께 사용해서 서브 쿼리 테이블과 다른 테이블을 조합할 수도 있다!

 

select  *
from 테이블1  a   left / inner / right  join 테이블2  b   on   a.공통컬럼명 = b.공통컬럼명 

 

테이블1 의 약어 (a), 테이블2의 약어(b)를 설정하고, on 뒤에  두 테이블에서 공통되는 칼럼을 가져온다.

-> 공통 칼럼을 기준으로 2개의 테이블이 합쳐진다.

 

+) 공통컬럼의 이름이 같을 필요는 없으며, 1개 이상이어도 된다. 

[ on  a.공통컬럼=b.공통컬럼   and   a.공통컬럼2  =  b.공통컬럼2  ] 이런식으로 작성할 수도 있다.

 

 

 

on 대신 using도 사용 가능하다.

select *
from 테이블1 JOIN 테이블2 USING (common_column)

 

두 테이블 간 공통된 열을 기준으로 조인할 때 사용

: 테이블 간 공통 열의 이름이 같을 때만 사용가능하다.

열 이름이 동일할 때 간결하게 쿼리를 작성할 수 있지만, 조인 조건이 복잡하거나 열 이름이 다른 경우에는 ' JOIN ON' 을 사용해야 한다.

( 앞에 기준 없이 JOIN만 작성하면 기본적으로 INNER JOIN으로 실행된다.)

 

 

 

 

---

 

5주차 강의인 Pivot Table과 Window Function은 기초 문법 아닌거 같아서 따로 포스팅 할 예정.

+) 새롭게 알게 되는 기본 개념이나, 기존의 개념을 더 자세히 알게 되면 추가하면서 수정중