엑셀보다 쉬운 sql 4주차 & 문법 총 복습

정찬호·2023년 1월 31일
스파르타 코딩클럽엑셀보다 쉬운 sql 4주차

4주차 동안 작성한 코드

SELECT u.user_id, u.name, u.email  from orders o
inner join users u 
on u.user_id = o.user_id 
where o.payment_method = 'kakaopay'

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

select user_id  from orders o 
WHERE payment_method  = 'kakaopay'


SELECT c.checkin_id , c.user_id , c.likes FROM  checkins c 

SELECT c.checkin_id, 
	   c.user_id,
	   likes,
	   (
	   SELECT AVG(likes) FROM  checkins c2 
	   WHERE user_id = c.user_id 
	   )
	   as AVG_Likes_user
FROM checkins c 

SELECT AVG(likes)  FROM  checkins c 
WHERE user_id = '4b8a10e6'


SELECT user_id , round(AVG(likes),1) as avg_likes FROM checkins c 
GROUP BY user_id 

SELECT user_id , point FROM point_users pu 

SELECT pu.user_id , pu.point , a.avg_likes FROM point_users pu 
inner join (
	select user_id , round(avg(likes),1) as avg_likes from checkins c
	group by user_id 
) a on pu.user_id = a.user_id


SELECT * From point_users pu 
WHERE `point` > 5000

SELECT round(avg(point),1) from point_users pu 

SELECT * FROM point_users pu 
inner join (
	select round(avg(point)) as tot_avg_point from point_users pu2  
) a on a.tot_avg_point <= pu.point

SELECT * FROM point_users pu 
WHERE point > (
	select avg(point) from point_users pu2 
)


SELECT ROUND(AVG(pu.point))  FROM point_users pu 
inner join users u 
on u.user_id =pu.user_id 
where u.name = "이**"

SELECT * FROM point_users pu
WHERE  pu.user_id in (
	select user_id  from users u 
	where name = "이**"
)

SELECT * FROM point_users pu 
where pu.`point` > (
	select avg(point) from point_users pu2 
	inner join users u 
	on u.user_id = pu2.user_id 
	where u.name = "이**"
)

SELECT * FROM point_users pu 
WHERE pu.`point` > (
	select avg(point) from point_users pu2 
	where pu2.user_id in (
		select user_id from users u 
		WHERE name = "이**"
	)
)


SELECT * FROM checkins c 
inner join (
	select * , round(avg(likes),1) as cource_avg from checkins c2
	group by c2.course_id 
) a 
on c.course_id  = a.course_id

SELECT c.checkin_id ,
       c.course_id ,
       c.user_id ,
       c.likes ,
       a.cource_avg 
FROM checkins c 
inner join (
	select * , round(avg(likes),1) as cource_avg from checkins c2
	group by c2.course_id 
) a 
on c.course_id  = a.course_id

SELECT c.checkin_id ,
       c.course_id ,
       c.user_id ,
       c.likes ,
       (
       	  select round(avg(c2.likes),1) from checkins c2 
       	  WHERE c2.course_id = c.course_id 
       ) as course_avg 
FROM checkins c 


SELECT * FROM courses c 

SELECT c.checkin_id ,
       c.course_id ,
       c.user_id ,
       c.likes ,
       (
          select round(avg(c2.likes),1) from checkins c2 
          where c.course_id  = c2.course_id  
       ),
       a.title
FROM checkins c 
inner join (
	select * from courses c3 
) a
on a.course_id = c.course_id 

SELECT c.checkin_id ,
       c.course_id ,
       c.user_id ,
       c.likes ,
       (
          select round(avg(c2.likes),1) from checkins c2 
          where c.course_id  = c2.course_id  
       ),
       c3.title
FROM checkins c 
inner join courses c3 
on c3.course_id = c.course_id 


SELECT course_id , count(DISTINCT(user_id)) as cnt_checkins FROM checkins c 
group by course_id 

SELECT course_id , COUNT(DISTINCT(user_id)) FROM orders o
GROUP BY course_id 

SELECT a.course_id,a.cnt_checkins, b.cnt_tot FROM (
	select c.course_id, count(distinct(user_id)) as cnt_checkins from checkins c 
	group by c.course_id
) a
inner join (
	select o.course_id, count(distinct(user_id)) as cnt_tot from orders o 
	group by o.course_id
) b
on a.course_id = b.course_id


SELECT (
       		select title from courses c2 
       		where c2.course_id = a.course_id
       ) as title,
       a.course_id,
       a.cnt_checkins, 
       b.cnt_total,
       (a.cnt_checkins / b.cnt_total) as ratio 
FROM 
(
	select c.course_id, count(distinct(user_id)) as cnt_checkins from checkins c 
	group by c.course_id
) a
INNER JOIN (
	select o.course_id, count(*) as cnt_total from orders o 
	group by o.course_id
) b
ON a.course_id = b.course_id
ORDER BY title

SELECT c2.title,
	   a.course_id,
       a.cnt_checkins, 
       b.cnt_total,
       (a.cnt_checkins / b.cnt_total) as ratio 
FROM 
(
	select c.course_id, count(distinct(user_id)) as cnt_checkins from checkins c 
	group by c.course_id
) a
INNER JOIN (
	select o.course_id, count(*) as cnt_total from orders o 
	group by o.course_id
) b
ON a.course_id = b.course_id
INNER JOIN courses c2 on a.course_id = c2.course_id 
ORDER BY title


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

SELECT c2.title,
	   a.course_id,
       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 c2 on a.course_id = c2.course_id 
ORDER BY title


SELECT user_id ,
       email ,
       SUBSTRING_INDEX(email,'@', 1) as email_id ,
       SUBSTRING_INDEX(email,'@',-1) as email_domain 
FROM users u 

SELECT order_no , created_at , SUBSTRING(created_at,1,10) as date FROM orders o 

SELECT order_no , created_at , SUBSTRING(created_at,1,10) as date FROM orders o 
group by date

SELECT order_no , created_at , SUBSTRING(created_at,1,10) as date, COUNT(*)  FROM orders o 
group by date

SELECT created_at, SUBSTRING(created_at,12,8)  FROM orders o 

SELECT pu.user_id , pu.point ,
	   (case when pu.point > 10000 then '잘 하고 있어요!'
	   else '조금만 더 화이팅!' end) as msg
FROM point_users pu 

SELECT pu.user_id , pu.point ,
	   (
	      case 
		      when pu.point > 10000 then '1만 이상'
		      when pu.point > 5000 then '5천 이상'
	   		  else '5천 미만' end) as lv
FROM point_users pu 

SELECT a.lv, COUNT(*) FROM (
	select pu.user_id, pu.point,
	       (case 
		      when pu.point > 10000 then '1만 이상'
		      when pu.point > 5000 then '5천 이상'
	   		  else '5천 미만' end
	       ) as lv
	from point_users pu
) a
group by a.lv

with table1 as (
	select pu.user_id, pu.point,
	       (case 
		      when pu.point > 10000 then '1만 이상'
		      when pu.point > 5000 then '5천 이상'
	   		  else '5천 미만' end
	       ) as lv
	from point_users pu
)

SELECT a.lv, COUNT(*) FROM table1 a
group by a.lv


SELECT pu.point_user_id ,
       pu.`point` ,
       (
       case 
       	 when pu.`point` > (select SUM(pu2.`point`)/count(*)  from point_users pu2) then '잘 하고 있아요!'
       	 else '열심히 합시다!'
       end
       )as msg
FROM point_users pu 


SELECT pu.point_user_id ,
       pu.`point` ,
       (
       case 
       	 when pu.`point` > (select avg(point)  from point_users pu2) then '잘 하고 있아요!'
       	 else '열심히 합시다!'
       end
       )as msg
FROM point_users pu 

SELECT SUBSTRING_INDEX(email,'@',-1) as domain,
       COUNT(*) as cnt_domain  
FROM users u 
GROUP BY domain

SELECT domain, count(*) as cnt FROM (
	SELECT SUBSTRING_INDEX(email,'@',-1) as domain
	FROM users u 
) a
group by domain

SELECT * FROM checkins c 
WHERE comment like '%화이팅%'

SELECT * FROM (
	select * from checkins c 
	where comment like '%화이팅%'
) a


SELECT enrolled_id  , count(*)  as total_cnt FROM enrolleds_detail ed
GROUP BY enrolled_id 

SELECT enrolled_id , SUM(done)  From enrolleds_detail ed 
GROUP BY enrolled_id 

SELECT enrolled_id , SUM(done)  From enrolleds_detail ed 
WHERE ed.done = 1
GROUP BY enrolled_id 

SELECT a.enrolled_id , b.done_cnt , a.total_cnt  FROM (
	SELECT enrolled_id  , count(*)  as total_cnt FROM enrolleds_detail ed
	GROUP BY enrolled_id 
) a
INNER JOIN (
	SELECT enrolled_id , SUM(done) as done_cnt From enrolleds_detail ed 
	WHERE ed.done = 1
	GROUP BY enrolled_id
) b
ON a.enrolled_id = b.enrolled_id
ORDER BY a.enrolled_id

with table1 as (
 select enrolled_id , count(*) as total_cnt from enrolleds_detail ed 
 group by enrolled_id  
), table2 as (
  select ed2.enrolled_id , sum(ed2.done) as done_cnt from enrolleds_detail ed2 
  where ed2.done = 1
  group by ed2.enrolled_id 
)

SELECT a.enrolled_id, b.done_cnt, a.total_cnt FROM table1 a
INNER JOIN table2 b
ON a.enrolled_id = b.enrolled_id

with table1 as (
 select enrolled_id , count(*) as total_cnt from enrolleds_detail ed 
 group by enrolled_id  
), table2 as (
  select ed2.enrolled_id , sum(ed2.done) as done_cnt from enrolleds_detail ed2 
  where ed2.done = 1
  group by ed2.enrolled_id 
)

SELECT a.enrolled_id,
       b.done_cnt, a.total_cnt ,
       ROUND((b.done_cnt/a.total_cnt),2) as ratio  
FROM table1 a
INNER JOIN table2 b
ON a.enrolled_id = b.enrolled_id

4주차 학습 내용

subquery

subquery는 쿼리 안에 쿼리를 집어 넣는 것이다. 작은 쿼리(subquery)에서 나온 결과를 쿼리에서 사용하는 것으로 쿼리가 더욱 간단해 진다.
서브 쿼리는 ()안에 작성해주어야 한다.

예)

SELECT a.TYPE FROM (
	SELECT * FROM POKEMON
    WHERE ANIMAL = 'RAT'
) a

이러면 포켓몬 테이블에서 동물 컬럼 값이 'RAT'인 데이터만을 가져와 a라 이름 붙이고 그 a에서 TYPE 데이터를 가져오라는 요청이 된다.

with

with을 사용하면 서브쿼리를 밖으로 빼내어 쿼리문을 더 보기 좋게 만들 수 있다.

예)

with table1 as (
	SELECT * FROM POKEMON
    WHERE ANIMAL = 'RAT'
)

SELECT a.TYPE FROM table1 a

이때 주의할 점은 with ~ a까지 전체 코드를 함께 선택한 다음에 실행시키지 않으면 with이 실행 안 된다는 것이다.

테이블을 복수 선언하고 싶을 때는 with을 여러번 쓰는 것이 아닌 ,로 테이블을 연달아 선언해야 한다.

예)

with table1 as (
	SELECT * FROM POKEMON
    WHERE ANIMAL = 'RAT'
), table2 as (
	SELECT * FROM POKEMON
    WHERE ATK > 25
)

CASE

if else 문과 비슷하다. case와 end 사이에 when으로 조건을 걸고 조건을 충족시킬 경우 그 값은 then 이후의 것이 된다. 모든 when을 충족시키지 못할 경우 else 다음 값을 가지게 된다.
예)

SELECT USER_ID,
	   (
       	case
        when adven.level >250 then '충분한 레벨'
        else '아직 모자른 편'
        end
        ) as able_next
FROM MAPLESTROY_ADVENTURER adven

메이플스토리 모험가 테이블에서 유저 아이디와 유저 레벨에 따른 다음 스테이지 진입 가능 여부를 출력하는 코드이다.

전체 복습

데이터 베이스란?

여러 사람들과 함께 데이터를 꺼내 쓰기 쉽게 담아두는 통.
원하는 대로 저장하고 사용하기 위해 통상적으로 모든 데이터베이스는 CRUD에 해당하는 기능을 지원한다.
C(Create): 데이터 생성하는 것이다.
R(Read): 저장된 데이터 읽어오기, 즉 데이터 꺼내오는 것이다.
U(Update): 저장된 데이터를 변경, 즉 데이터 덮어쓰기는 것이다.
D(Delete): 저장된 데이터를 삭제하는 것이다.

SQL 이란?

Structured Query Language의 약자로 데이터베이스에 요청(Query)을 보내서 원하는 데이터를 가져오는 작업을 도와주는 언어라는 의미이다.

SQL이 필요한 이유?

CRUD 중 R(Read)을 편하게 하기 위해서 필요하다. 데이터를 정리/분석하는 기능도 지원한다고 한다.
데이터 베이스(이하 DB)는 만들기 시작(C: Create)할 때부터 관리하는 것(U: Update)까지 전문적으로 설계를 해야 하기 때문에 실제 업무에서 주로 하는 것은 R이다.
실수로 D(Delete)를 하면 중대한 문제가 발생할 수 있으니 전문적으로 관리하는 인력이 따로 존재한다고 한다.

SQL 문법

테이블 목록 보기

SHOW tables

존재하는 테이블의 목록을 호출한다.

SELECT, FROM

SELECT 테이블 컬럼명(찾는 대상) FROM 테이블 이름

데이터 베이스에 보내는 질의문으로 테이블에서 지정한 데이터를 가져온다.
테이블로부터 지정한 컬럼명에 해당 하는 데이터를 가져온다.
FROM을 데이터를 가져올 테이블를 지정, SELECT는 테이블에서 가져올 데이터를 지정한다.

  • 은 전체 컬럼을 의미한다.

WHERE

Where 조건

SELECT문을 작성한 다음에 작성하며, select로 가져올 데이터에 조건을 부여한다.
예시코드

SELECT * FROM POKEMON
WHERE TYPE='electronic'

포켓몬 테이블에서 타입이 전기인 데이터만을 가져오라는 요청이 된다.

참고로 AND나 OR을 사용해 여러 개를 함께 지정하는 것이 가능하다.

WHERE과 함께 쓰는 문법

같지 않음(!=) 조건 걸기
지정된 조건과 일치하지 않는 데이터를 가져오도록 요청한다.

SELECT * FROM POKEMON
WHERE TYPE !='electronic'

포켓몬 테이블에서 타입이 전기가 아닌 데이터만을 가져오라는 요청이 된다.

범위 조건(between) 걸기
가져올 데이터의 범위를 지정한다.

SELECT * FROM POKEMON
WHERE ATK BETWEEN 100 AND 200

이렇게 하면 포켓몬 테이블에서 공격력이 100에서 200 사이인 데이터를 가져오라는 요청이 된다.

포함 조건(in (,,,)) 걸기
가져올 데이터에 해당 조건이 포함되도록 요청한다.

SELECT * FROM POKEMON
WHERE TYPE IN ('electronic','fairy','dragon')

이렇게 하면 포켓몬 테이블에서 타입이 전기, 페어리 혹은 드래곤인 데이터들을 가져오라는 요청이 된다.

패턴(문자열 규칙) 조건(like '%') 걸기
특정 문자를 포함한 문자열을 가지고 있을 것을 조건으로 건다.

SELECT * FROM POKEMON
WHERE name LIKE '%츄'

이렇게 하면 이름이 츄로 끝나는 모든 데이터를 가져오라는 요청이 된다.

그 외 유용한 문법

일부 데이터만 가져오기(Limit)
가져올 데이터가 방대할 때 그 중 일부만 가져오기 위해 사용한다.

SELECT * FROM POKEMON
WHERE TYPE IN ('electronic','fairy','dragon')
LIMIT 10

이렇게 하면 포켓몬 테이블에서 타입이 전기, 페어리 혹은 드래곤인 데이터들 중 10개만을 가져오라는 요청이 된다.

중복 데이터는 제외하고 가져오기(Distinct)
가져올 데이터에서 중복을 제거하도록 요청한다.

SELECT DISTINCT(TYPE) FROM POKEMON

이렇게 하면 포켓몬 테이블에서 TYPE에 해당하는 데이터를 중복을 제외하고 가져오도록 요청한다.

몇 개인지 숫자 세기(Count)
데이터의 갯수를 세도록 요청한다,

SELECT COUNT(DISTINCT(TYPE)) FROM POKEMON

이렇게 하면 포켓몬 테이블에서 TYPE에 해당하는 데이터들의 중복을 제외한 갯수를 가져오도록 요청한다.

GROP BY

동일한 범주를 갖는 데이터를 하나로 묶어서, 범주별 통계를 내주는 것

select type,count(*) from pokemon
group by type

위 코드를 실행시키면 pokemon으로 부터 데이터를 가져와 type으로 묶은 뒤 type과 그 갯수를 출력해준다.

ORDER BY

데이터를 오름차순, 내림차순으로 정렬해주는 것
desc은 내림차순, ASC는 오름차순이며 기본적으로 오름차순이다.

select type,count(*) from pokemon
group by type
order by count(*) desc

위 코드를 실행시키면 GROUP BY 예시코드의 결과를 내림차순으로 정렬하여 출력해준다.

별칭

쿼리가 길어지면서 생기는 혼동을 줄이기 위해 테이블명이나 데이터 필드명에 약어(같은 것)을 부여하는 것
as를 붙여서 필드명에 별칭을 추가할 수 있다. from에서 지정하는 데이블명에는 as가 생략되어도 문제가 없는 것 같다.

SELECT payment_method ,count(*) as cnt from orders o 
WHERE  o.course_title = '앱개발 종합반' and email like '%naver.com'
group by payment_method

위 코드의 count() as cnt는 출력될 count()에 cnt라는 별칭을 붙여 cnt로 출력되게 변경해준다.
orders o는 orders 테이블을 o라는 단어로 접근할 수 있게 해준다.

Join

두 테이블의 공통된 정보를 기준으로 테이블을 연결해서 한 테이블처럼 보는 것
inner (교집합), left(처음 나오는 테이블에 합치는 방식), outer(거의 안씀)의 3가지 방식이 존재한다.
inner로 사용할 경우 일치하는 데이터가 있는 것만 합쳐서 가져오고, left일 경우에는 일치하는 것이 없을 경우 없는 자동으로 데이터를 NULL로 채워서 가져온다,.

SELECT * FROM checkins c 
inner join users u 
on c.user_id = u.user_id 

select u.name, COUNT(*) as cnt 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 u.name

NULL

데이터가 없는 비어있음을 의미한다.
IS NULL - NULL인지 확인
IS NOT NULL - NULL이 아닌지 확인
COUNT는 NULL을 세지 않는다.

select u.name, COUNT(*) as cnt 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 u.name

UNION

데이터 명과 종류가 모두 동일할 때 사용할 수 있다.
서로 다른 테이블을 합쳐서 보여준다.

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

subquery

subquery는 쿼리 안에 쿼리를 집어 넣는 것이다. 작은 쿼리(subquery)에서 나온 결과를 쿼리에서 사용하는 것으로 쿼리가 더욱 간단해 진다.
서브 쿼리는 ()안에 작성해주어야 한다.
select, from, where에서 유용하게 사용되는 편
예)

SELECT a.TYPE FROM (
	SELECT * FROM POKEMON
    WHERE ANIMAL = 'RAT'
) a

이러면 포켓몬 테이블에서 동물 컬럼 값이 'RAT'인 데이터만을 가져와 a라 이름 붙이고 그 a에서 TYPE 데이터를 가져오라는 요청이 된다.

with

with을 사용하면 서브쿼리를 밖으로 빼내어 쿼리문을 더 보기 좋게 만들 수 있다.

예)

with table1 as (
	SELECT * FROM POKEMON
    WHERE ANIMAL = 'RAT'
)

SELECT a.TYPE FROM table1 a

이때 주의할 점은 with ~ a까지 전체 코드를 함께 선택한 다음에 실행시키지 않으면 with이 실행 안 된다는 것이다.

테이블을 복수 선언하고 싶을 때는 with을 여러번 쓰는 것이 아닌 ,로 테이블을 연달아 선언해야 한다.

예)

with table1 as (
	SELECT * FROM POKEMON
    WHERE ANIMAL = 'RAT'
), table2 as (
	SELECT * FROM POKEMON
    WHERE ATK > 25
)

CASE

if else 문과 비슷하다. case와 end 사이에 when으로 조건을 걸고 조건을 충족시킬 경우 그 값은 then 이후의 것이 된다. 모든 when을 충족시키지 못할 경우 else 다음 값을 가지게 된다.
예)

SELECT USER_ID,
	   (
       	case
        when adven.level >250 then '충분한 레벨'
        else '아직 모자른 편'
        end
        ) as able_next
FROM MAPLESTROY_ADVENTURER adven

메이플스토리 모험가 테이블에서 유저 아이디와 유저 레벨에 따른 다음 스테이지 진입 가능 여부를 출력하는 코드이다.

sql문 쿼리 실행 순서

from(데이터 가져오기) -> JOIN(데이터 합치기) -> where(특정 데이터만 가져오기) -> group by(데이터 묶기) -> select(해당하는 데이터 추출하기) -> order by(데이터 정렬하기)
subquery가 포함되어 있는 부분에서는 subquery를 진행 후 해당 부분을 진행한다,

profile
정찬호
게임 개발 지망생입니다.
이전 포스트

엑셀보다 쉬운 sql 3주차 개발 노트

다음 포스트

c# ?. ?[] ?? 연산자

0개의 댓글