SQL 문법 정리

 

SQL (Structured Query Language)

• 데이터베이스에서 데이터를 읽어오는 과정인 Read 과정을 편하게 해주는 목적으로 사용된다.
• 데이터를 손쉽고 깔끔하게 정리/분석하는 기능을 지원한다.
• 데이터베이스에 요청(Query)을 날려서 원하는 데이터를 가져오는 것을 도와준다.

 다시 말해, SQL을 이용하는 목적은 데이터를 정의하고, 조작하고, 제어하여 데이터를 손쉽게 관리하는 것입니다.

 

 이에 따라 SQL의 문법은 데이터 정의어, 데이터 조작어, 데이터 제어어 크게 세 가지로 나뉘게 되는데 이 글에서 각 문법을 하나로 정리해 볼 예정입니다!

 

( 아래 자료들은 스파르타 코딩클럽의 엑셀보다 쉬운 SQL 강의를 참조했으며, DBeaver를 사용했습니다 )

 

---

 

- Ctrl + Enter : 기본적으로 명령을 실행시키는 단축키입니다.

 

- 테이블 확인하기

Show tables;

 위 데이터베이스에서는 8개의 테이블이 존재한다는 것을 알 수 있습니다!

 

 

- 특정 테이블의 데이터 가져오기 (SELECT 함수 이용)

Select * from orders;

 Select 함수를 통해 특정 테이블의 필드를 가져올 수 있으며, *는 all을 뜻하기에 모든 필드를 가져온 것입니다.

 

 모든 필드 대신 특정 필드를 가져오고 싶다면, * 대신 (필드명)을 입력하면 됩니다.

아래와 같이 입력하면 select와 from 사이에 있는 필드들을 가져올 수 있습니다.

Select created_at, course_title, payment_method, email from orders;

 

- 조건을 이용하여 테이블의 필드 가져오기 (WHERE 절 이용)

Select * from orders
Where payment_method = 'kakaopay';

 위와 같이 실행하게 되면 orders 테이블에서 payment_method가 kakaopay인 필드만 가져오게 됩니다!

 

 만약 'kakaopay'가 아니라 kakaopay라고 작성하게 되면 에러가 나니 주의해야 합니다.

(kakaopay는 column이 아닌 문자열이기 때문에 따옴표를 붙여줘야 합니다!)

 

 

- WHERE 절과 자주 같이 쓰이는 문법

 

• '같지 않음' 조건 (!=)

 아래와 같이 입력하게 되면, orders 테이블의 course_title이 웹개발 종합반이 아닌 필드를 가져오게 됩니다.

Select * from orders
where course_title != '웹개발 종합반';

 

• 범위 조건 (Between ~ and ~)

 아래와 같이 입력하게 되면, orders 테이블의 created_at이 2020년 7월 13일, 7월 14일인 데이터만 불러옵니다.

Select * from orders
where created_at between '2020-07-13' and '2020-07-15';

 

• 포함 조건 (in)

 아래와 같이 입력하게 되면 checkins 테이블의 week가 1주차, 3주차인 사람들의 정보만 불러옵니다.

Select * from checkins
where week in (1, 3);

 

• 패턴 조건 (Like)

 아래와 같이 입력하게 되면 users 테이블의 email이 'daum.net'으로 끝나는 데이터들을 불러옵니다.

Select * from users
where email like '%daum.net' ;

 

 Like는 패턴을 거는 문법으로 다음과 같은 사용법을 가지고 있습니다.

1. where email like 'a%'  :  email 필드값이 a로 시작하는 모든 데이터
2. where email like '%a'  : email 필드값이 a로 끝나는 모든 데이터
3. where email like '%co%'  : email 필드값에 co를 포함하는 모든 데이터
4. where email like 'a%o'  :  email 필드값이 a로 시작하고 o로 끝나는 모든 데이터

 

• 일부 데이터만 가져오기 (Limit)

 파이썬의 head() 와 비슷한 함수입니다. 데이터의 수가 너무 많을 경우 위에서부터 일부 데이터만 추출하는 함수입니다.

아래와 같이 입력하게 되면 조건에 맞는 데이터들 중 위에서부터 5개의 데이터만 추출합니다.

Select * from orders
where payment_method = 'kakaopay'
limit 5 ;

 

• 중복 데이터는 제외하고 가져오기 (Distinct)

 아래와 같이 입력하게 되면, 조건에 맞는 필드의 중복 데이터는 제거하기 때문에 payment_method에서 kakaopay, CARD, TAXBILL, MONEY 네개의 데이터만 추출됩니다.

Select distinct(payment_method) from orders;

 

• 몇 개인지 숫자 세보기 (Count)

 아래와 같이 입력하면, orders 테이블이 몇 개의 데이터로 이루어져 있는지 알 수 있습니다.

Select count(*) from orders;

 

---

 

- 동일한 범주의 데이터를 묶어주기 (Group by절 이용)

 

Select name, count(*) from users
group by name;

 위와 같은 쿼리를 실행하게 되면 아래와 같이 동일한 범주의 데이터로 묶은 결과물이 나옵니다.

(보통 ~~별 이라는 말이 조건에 붙게 되면 group by 절을 이용하여 나타내면 됩니다.)

 

 

- 데이터 정렬하기 (Order by절 이용)

 

 위 group by를 통해 동일한 범주를 묶어준 다음, Order by절을 추가로 삽입하면 name 필드를 정렬하여 나타냅니다.

Select name, count(*) from users
group by name
order by name;

• default값은 오름차순(asc)이며, 내림차순으로 하고자 한다면 order by ~~ desc; 라고 작성합니다.

 

- 별칭 기능 적용하기 (Alias)

 

Select payment_method, count(*) as cnt from orders o
where o.course_title = '웹개발 종합반'
group by payment_method;

위에서 count(*)에 cnt라는 별칭을 주었습니다. 위 코드를 입력하면 아래와 같은 결과값이 나옵니다.

 

 

---

 

 다음 내용에서는 한 테이블이 아닌 여러 테이블을 다룰 때 적용하는 문법에 대해 다루겠습니다.

 

 

- 두 테이블의 공통된 부분을 출력하기 (Join함수 이용)

 

 Join 함수의 종류에는 Left Join과 Inner Join 두 가지가 있습니다.

 

• Left Join

Select * from users u
left join point_users p
on u.user_id = p.user_id;

 users 테이블을 A, point_users를 B라고 했을 때 위와 같이 코드를 작성하면,

A와 B가 겹치는 부분에 대한 공통 필드에 대해서 A 테이블의 내용만 출력하게 됩니다.

 

• Inner Join

Select * from users u
inner join point_users p
on u.user_id = p.user_id;

 Left Join과 반대로 Inner Join을 적용하게 되면 A테이블과 B테이블의 공통된 필드의 내용만 출력합니다.

그러나 실무에서는 Left Join보다는 Inner Join을 위주로 많이 사용한다고 합니다 :)

 

 

- select를 두번이 아닌 한번에 나누어 입력하고자 할 때 (Union 함수)

(
 select '7월' as month, c.title, c2.week, count(*) as cnt from checkins c2
 inner join courses c on c2.course_id = c.course_id
 inner join orders o on o.user_id = c2.user_id
 where o.created_at < '2020-08-01'
 group by c2.course_id, c2.week
 order by c2.course_id, c2.week
)
union all
(
 select '8월' as month, c.title, c2.week, count(*) as cnt from checkins c2
 inner join courses c on c2.course_id = c.course_id
 inner join orders o on o.user_id = c2.user_id
 where o.created_at > '2020-08-01'
 group by c2.course_id, c2.week
 order by c2.course_id, c2.week
)

 코드가 길어 조금은 복잡해 보이지만, 사실 간단한 코드입니다.

 

 

 

 첫번째 select 문단을 출력하면 다음과 같이 나오고,

 

 

 

 

 두번째 select 문단을 출력하면 다음과 같이 나옵니다.

 

 

 

 이 둘을 Union 함수를 이용하여 위 코드와 같이 작성하면

 

 

 다음과 같은 결과값이 출력되는 것을 볼 수 있습니다.

 

 위와 같이 Union 함수는 일일이 Select문을 하여 출력하는 것이 아니라 한번에 모아서 보고 싶은 경우 사용하는 함수입니다.

 

 

 

 (그러나 Union을 사용하면 내부 정렬이 먹지 않습니다. 아래에서 설명할 SubQuery를 이용하면 이를 해결할 수 있습니다.)

 

 

- Query 내부에 Query를 작성하고자 할 때 (SubQuery)

 

 위 테이블에서 kakaopay로 결제한 유저들의 정보를 보기 위해서는 users 필드와 orders 필드를 inner join하고 where절을 추가하여 코드를 작성했습니다.

 

 그러나 더욱 복잡한 문제에서는 코드가 매우 길어질 수 있기 때문에, 이를 간단하게 나타낼 수 있는 방법에 대해 생각해 보아야 합니다.

 

 두 필드를 join하여 나타내는 것이 아닌, where 절에 또 다른 Query를 추가하여 나타낼 수도 있습니다.

 

select u.user_id, u.name, u.email from users u
where u.user_id in (
     select user_id from orders
     where payment_method = 'kakaopay'
)

 SubQuery는 단순히 Where절에만 들어가는 것이 아닌, from이나 select 내부 또는 join 내부에도 들어갈 수 있습니다.

이를 잘 활용한다면 굉장히 복잡한 문제를 간단한 코드로 해결할 수 있습니다.

 

 

- SubQuery를 더욱 간단하게 나타내기 (With절 이용)

 

 with절을 이용하면, 긴 SubQuery를 반복하여 작성해야 하는 경우 이를 간단하게 나타낼 수 있습니다.

아래 코드는 SubQuery를 이용하여 코스제목별 like 개수, 전체, 비율을 출력하는 코드입니다.

select c.title,
        a.cnt_checkins,
        b.cnt_total,
        (a.cnt_checkins/b.cnt_total) as ratio
from
(
   select course_id, count(distinct(user_id)) as cnt_checkins from checkins
   group by course_id
) a
inner join
(
   select course_id, count(*) as cnt_total from orders
   group by course_id
) b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id;

이를 with절로 간단하게 나타내면,

with table1 as (
   select course_id, count(distinct(user_id)) as cnt_checkins from checkins
   group by course_id
), table2 as (
   select course_id, count(*) as cnt_total from orders
   group by course_id
)

select c.title,
        a.cnt_checkins,
        b.cnt_total,
        (a.cnt_checkins/b.cnt_total) as ratio
from table1 a inner join table2 b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id;

다음과 같이 나타낼 수 있습니다.

 

 

- 문자열 쪼개기 (SUBSTRING_INDEX 이용)

 

 이메일 주소에서 @ 앞의 아이디만 가져오거나, @ 뒤의 이메일 도메인만을 가져오고 싶다면 SUBSTRING_INDEX 함수를 이용하여 코드를 작성합니다.

Select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users;

 이는 @를 기준으로 텍스트를 쪼개고, 그 중 첫 번째 조각을 가져오라는 의미이며, 두 번째(마지막) 조각을 가져오고 싶다면 1 대신 2 또는 -1를 입력하면 됩니다.

 

 또는 출력하고자 하는 위치를 지정하고 싶다면,

SUBSTRING(문자열, 출력을 하고 싶은 첫 글자의 위치, 출력하고자 하는 글자의 개수) 를 입력합니다.

 

 

- 원하는 값을 새 필드에 출력하기 (Case 이용)

 

 point_users 필드에 있는 값이 10,000보다 높으면 '1만 이상', 5,000보다 높으면 '5천 이상', 그 아래를 '5천 이하'라고 입력하고 싶으면 아래와 같이 작성합니다.

Select pu.point_user_id, pu.point,
case
when pu.point > 10000 then '1만 이상'
when pu.point > 5000 then '5천 이상'
else '5천 이하'
END as '구분'
from point_users pu;

 

 

---

 

 스파르타 코딩클럽의 '엑셀보다 쉬운 SQL' 강의를 들으며, 학교 강의로 들었던 데이터베이스 강의를 복습함과 동시에 SQL 문법과 적용을 더욱 쉽게 배울 수 있었습니다!

 

 위 문법들을 아는 것도 중요하지만 복잡하게 얽힌 문제를 여러 문법을 적용하여 간단하게 나타내는 것이 중요할 것 같습니다!

 

 

 

 첫 블로그 게시글이자 스파르타 코딩클럽 4주차 과제 끝 !