SQL 기본 문법 정리

쿼리란?

데이터베이스에 명령을 내리는 것

 

select 쿼리문

데이터베이스에서 데이터를 선택해서 가져오겠다는 것.

select * from orders 이런 식으로 쓰인다.

*은 필드 전체를 말하며, order 테이블의 전체 필드를 가져오라는 뜻.

 

select created_at, course_title, payment_method, email from orders;

order 테이블에서 created_at 과 같은 특정 필드들을 가져오라는 뜻.

 

where절

where절은 쿼리문으로 가져올 데이터에 조건을 걸어주는 것.

 

select * from orders
where payment_method = "kakaopay";

orders 테이블에서 payment_method가 kakaopay인 것만 가져와줘!

카카오페이를 큰따옴표(혹은 작은 따옴표)로 쓰는 이유는 필드명이나 테이블명이 아니라 문자열로 인식시키기 위해!

 

select * from point_users
where point > 20000;

이런 식으로 범위를 지정해 줄 수도 있음!

 

select * from users

where name = "황**";

요런 것도 가능하다~

 

여러 조건은 and를 활용하면 된다.

select * from orders
where course_title = "웹개발 종합반" and payment_method = "CARD";

이렇게!

 

같지 않음은 !=

 

select * from orders
where course_title != "웹개발 종합반";

이러면 웹개발 종합반이 아닌 사람들만 나온다.

 

범위 조건은 between을 사용

 

예를 들어 7월 13일부터 14일까지의 데이터를 보고싶다!

select * from orders
where created_at between "2020-07-13" and "2020-07-15";

 

왜 07-15까지 쓰냐면, 07-14로만 쓰면 2020-07-14 00:00:00여기까지의 데이터만 잡히니까.

2020-07-14 23:59:59까지 찾아야 하니까!

 

포함 조건은 in

 

select * from checkins

where week in (1, 3);

이러면 1,3주차 사람들만 잡히게 된다.

 

패턴은 like로!

 

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

이러면 이메일이 다음으로 끝나는 사람만 잡힌다.

%는 앞에 뭐가 오든 상관없다!

 

하기 예시.

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

 

일부 데이터만 가져오기 limit

 

select * from orders 
where payment_method = "kakaopay"
limit 5;

 

이러면 다섯개까지만 나온다. 

실제론 데이터가 방대할텐데 전부 다 찍어서 보려면 시간이 오래걸리니까 이런식으로 테이블의 구조를 먼저 확인하자!

 

중복 데이터 제외하고 가져오기 Distinct

 

select distinct(payment_method) from orders;

이러면 결제 수단의 종류가 어떤 것이 있는지 바로 파악이 가능하다.

 

몇 개인지 세어보기 count

select count(*) from orders

 

distinct와 같이 활용하면

 

select distinct(name) from users;

요걸로 어떤 성씨들이 있는 지 확인할 수 있고,

 

SELECT count(distinct(name)) from users

이런 쿼리를 통해 이 테이블에 총 몇 개의 성씨가 있는지 알 수 있다.

 

group by

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

 

여기에서 group by name은

name이라는 필드에서 동일한 값을 갖는 데이터를 하나로 합쳐줍니다

 

데이터들을 정렬할때 order by

 

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

이렇게 하면 이름이라는 결과의 개수를 오름차순으로 정렬한다.

 

select name, count(*) from users
group by name

order by count(*) desc;

desc를 붙이면 내림차순.

 

별칭 기능: Alias

 

select * from orders o

where o.course_title = '앱개발 종합반'

 

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

 

요런식으로 as나 테이블명 뒤에 한 칸 띄워주고 정말 간단한 알파벳으로 구분하여 alias를 잡아준다.

 

 

join이라는 기능을 사용하면 두 개의 테이블을 연결할 수 있음.

 

Join의 종류: Left Join, Inner Join

 

~left join

select * from users u

left join point_users p on u.user_id = p.user_id;

 

이런 경우 어떤 데이터는 모든 필드가 채워져있지만, 어떤 데이터는 비어있는 필드가 있습니다.

꽉찬 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재해서 연결한 경우

비어있는 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재하지 않는 경우

 

~inner join

select * from users u

inner join point_users p on u.user_id = p.user_id;

 

inner join에는 비어있는 필드가 없음.

그 이유는, 같은 user_id를 두 테이블에서 모두 가지고 있는 데이터만 출력했기 때문.

 

NULL 값의 표현

NULL은 대문자로 써줘야 함.

 

select name, count(*) from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is NULL
group by name

 

select name, count(*) from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is not NULL
group by name

 

Select를 두 번 할 게 아니라, 한번에 모아서 보고싶은 경우

Union사용

단 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

)

 

 

where 절에 들어가는 서브쿼리

select * from point_users pu 
where pu.point > (select avg(pu2.point) from point_users pu2);

 

select절에 들어가는 서브쿼리

select checkin_id,

             course_id,

             user_id,

             likes, 
             (select avg(c2.likes)

from checkins c2
where c.course_id = c2.course_id) 
from checkins c;

 

 

From 절에 들어가는 Subquery 연습해보기

 

테이블 두 개를 만든다 생각하고 필요한 내용이 담긴 쿼리 두 개를 만든다.

이후 두 개의 쿼리를 아래와 같은 형식으로 붙인다.

 

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

 

먼저 두 개의 쿼리 중 하나를 기준으로 잡아 a,b를 만들어 주고, 둘 다 course_id라는 동일한 필드가 존재하니 

이를 inner join으로 묶어 원하는 값을 찾는다!

 

from 절에 들어가는 서브쿼리는 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

 

with절은 전체 선택 후 control + enter로 실행시키거나

괄호 바로 밑에 select 절을 붙여서 실행하면 된다

 

이메일에서 아이디만 가져오거나 할때 substring index 활용

select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users

이메일 필드에서 @기준으로 나누었을때, 첫번째 덩어리를 사용하겠다는 뜻.

(보통 프로그래밍에서 0이 첫번째인데 여기선 1이 첫번째였음)

 

select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users

@를 기준으로 텍스트를 쪼개고, 그 중 마지막 조각을 가져오라는 뜻!

 

case 경우에 따라 원하는 값을 새 필드에 출력

 

select pu.point_user_id, pu.point,

case when pu.point > 10000 then '잘 하고 있어요!'

          else '조금 더 달려주세요!' END as '구분'

from point_users pu;

 

이런 식으로 나옴