부트캠프 개발일지(SQL 1, 2, 3주차)

부트 캠프 개발 일지 - SQL 1~3주차(23.05.10)

 

1. SQL 1주차

• DB : 데이터를 저장하고 사용하는 큰 저장 장치, CRUD 기능 지원 
• SQL : DataBase 내의 데이터를 내가 원하는 형태로 가져오기 위한 언어, 방대한 양의 데이터를 저장 가능하고 그 중 내가 원하는 정보 신속하게 가져올 수 있음 (SQL문 실행 : Ctrl + Enter)
• Select쿼리문 : DB에서 데이터를 선택해 가져오겠다(어떤 테이블에서 가져올지, 어떤 필드의 데이터를 가져올지)
• Table : 데이터가 담긴 전체 표 / Field : 데이터의 속성(Attribute)
• 별칭 : SQL 작성 도중 자동 완성 시 뒤에 붙는 알파벳
• 기본 검색 : select 필드명 from 테이블명 where 조건
• show table로 테이블 종류 확인 - 원하는 정보가 있을 것 같은 테이블에 select 날려보기(limit 10으로) - 없으면 반복 - 찾으면 범주를 나눌 필드를 찾기 - 범주 별 통계를 보고 싶은 필드 찾기 - sql쿼리 작성
• where절 : 조건문, 문자/문자열은 ' '/" "사용(숫자는 사용x) - 사용 하지 않는다면 컴퓨터가 테이블이나 필드명을 지칭하는 것이라 인식, and/or 응용 가능
• LIKE : 뒤에 조건에 해당하는 결과 반환(ex) '박%' -> 박으로 시작하는 컬럼)
• show table : 전체 테이블 검색(여기서 원하는 테이블도 전체 검색 후 필요한 필드명 확인 해 검색)
• between : 두 범위 내에 있는 자료 검색(ex) 5월 8~9일 정보 검색 시, between '2023-05-08' and '2023-05-10'(05-09로 하면 컴퓨터는 05-09 00시로 인식하므로 원하는 결과가 나오질 않음 따라서 05-10으로 해야 05-10 00시 전인 05-09를 검색) // 2000~3000 보유한 유저 추출 시, betweeb 2000 and 3000)
• in() : 범위 내의 정보 검색(ex) in(1,3) : 1이거나 3을 가진 자료 검색)
• limit : 일부 데이터만 호출(크기가 큰 테이블 조회 시 오래 걸려 전부 로딩하지 않고 지정 수 만큼만 오름차순으로 가져옴)
• distinct : 중복 제거(종류 확인 할때) / count() : 검색 된 자료 개수 출력 / count(distinct) 등 이중 사용 가능
  
  

2. SQL 2주차 

• sql에서의 ;(세미콜론)은 여러 쿼리문 사용 시, 한 쿼리 문장의 끝 표시(생략 가능)
• group by 필드명 : 필드의 공통부분을 묶기 (count개수 세기, 동일 개수 파악 등)
• order by 필드명 : 필드를 정렬하기 위해서 사용, defalut값은 asc(오름차순), desc사용 시 내림차순 정렬, group by 와 따로 사용 가능
• AS : Alias(알리아스), 즉 별칭으로 아래 문장의 c가 checkins의 alias가 됨(round로 감쌀 때는 round 밖에 작성)
• SELECT week, min(likes) FROM checkins c // 주차별로 좋아요 최소값 구하기
  group by week 
• min 대신 max(최대), avg(평균), sum(합계), count(개수) 등 사용 가능
• round(값, 소수점 자리수) : 값의 소수점 자리수까지 표시되는 함수
• 쿼리 작성 순서 : select * from 테이블명 -> where절 추가 -> group by field명 -> select절 수정 -> order by 절 사용
• SELECT payment_method , count(*) FROM orders o 
  WHERE email LIKE '%naver.com' and course_title = '앱개발 종합반'
  group by payment_method  //주문테이블에서 네이버이메일이고 앱개발 종합반인 사람들의 결제 수단별로 묶어 출력

 

3. SQL 3주차

• Join : 테이블과 테이블을 연결해 한 테이블처럼 보는 것(두 테이블의 공통된 정보(키 값)을 기준으로 연결 -> 엑셀의 vlookup)
• 테이블을 나누는 이유 : 테이블은 한 목적에 맞는 것만 모아두는 것이 가장 좋음(웹 서비스 동작에 있어 간편하고 수정 시 문제가 없음)
• Join 종류 : Left Join, Inner Join, Outer Join(거의 사용x)
• Left Join : 왼쪽의 테이블을 기준으로 연결(왼쪽에 테이블에 오른쪽 테이블을 붙이기 -> 없으면 NULL로 보여주고), 순서가 중요!, 주로 한 쪽에는 있고 한 쪽에는 없는 것의 통계를 집계하고 싶을 때 사용(ex) 강의를 신청했지만 시작하지 않은 사람들 보고 싶을 때 -> NULL인것만 취합해서 쿼리작성)
• Inner Join : 두 테이블의 서로 교집합(필드에서 정보를 가지고 있는 것들만) 형태로 연결(테이블 순서 상관x)

* Inner Join 예제)

 // 결제하고 시작하지 않은 유저들을 성씨별로 세기(셀 때 내림차순 정렬)

SELECT u.name, count(*) as cnt_name FROM enrolleds e

inner join users u

on e.user_id = u.user_id

where e.is_registered = 0

group by u.name

order by cnt_name desc    

• 예제 쿼리 실행 순서 : from -> join -> where -> group by -> select -> order by

* Inner Join 응용 예제)

// 앱개발, 웹개발 종합반의 week별 체크인 수 중 8월 1일 이후 구매한 사람만 보기 좋게 정리

SELECT c.title, c2.week, count(*) as cnt FROM courses c

inner join checkins c2 on c.course_id = c2.course_id

inner join orders o on c2.user_id = o.user_id // 구매한 내역은 orders 테이블에 있으므로 조인을 한번 더 함

where o.created_at > '2020-08-01'  // 8월 1일 이후 구매한 사람 조건

GROUP BY c.title, c2.week  //콤마를 이용해 title과 week 두 개로 묶기

order by c.title, c2.week  //보기 좋게 정리(c.title먼저 정렬 / c2.week 그다음 정렬)

 

* Left Join 예제) (count()는 NULL 을 세지 않는다를 이용!, 비어 있지 않는 것만 개수를 찾아 숫자 출력)

// 7월 10일부터 19일까지 가입한 사람포인트를 가진 고객 숫자, 전체 숫자, 그리고 비율 구하기!

SELECT count(pu.point_user_id) as pnt_user_cnt, // 요거를 count하면 NULL뺴고 카운트함 따라서 82값 출력

               count(u.user_id) as user_cnt, // 202출력

               round((count(pu.point_user_id)/count(u.user_id)), 2) as ratio FROM users u // 비율구할때는 count 한 두개 값 나누기!! 82/202 => 0.405~~인데 round로 2자리까지 출력이므로 0.41 출력

left join point_users pu on u.user_id = pu.user_id

where u.created_at BETWEEN '2020-07-10' and '2020-07-20'

 

• Union : 필드가 전부 동일 할 때 가능(ex) 위의 Inner Join 응용 예제는 8월의 결과만 보여주는 데 7월의 결과도 합쳐서 보고 싶다 할 때 사용)

* Union 예제)

(

select '7월' as month, c1.title, c2.week, count(*) as cnt from courses c1 // 7월달의 정보

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 // 8월달의 정보

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 시 ()내부에 있는 order by 절은 깨진다(먹히지 않는다) 따라서 두개를 유니온 후 밖에서 order를 하는 방법이 있음(서브쿼리 활용 : 이거는 4주차 때 공부)

 

중간 중간 이해는 되었지만 응용 부분에서 부족한 부분이 많았다. 여러 예제를 다시 돌려보며 익숙해져 보려 한다.