SQL 기초 1

학습 강의 - 엑셀보다 쉬운 SQL 1주차 & 2주차

 

SQL이 필요한 이유

• 많은 양의 데이터를 효과적으로 저장/수용/사용하기 위해서는 데이터 베이스가 필요함.
• 데이터 베이스가 사용자에게 주는 이점이 많음

 데이터 베이스의 기초 개념

• 여러 사람들이 같이 사용할 목적으로 데이터를 담는 통
• 통상적으로 모든 데이터베이스는 CRUD에 해당하는 기능을 지원
  • Create : 데이터의 생성을 의미
  • Read : 저장된 데이터를 읽어오는 것을 의미
  • Update : 저장된 데이터를 변경
  • Delete : 저장된 데이터를 삭제하는 것을 의미

데이터를 읽어오는 과정인 Read를 엄청 편하게 만들어 준다. 그리고, 데이터를 손쉽고 깔끔하게 정리/분석하는 기능도 지원

 

SQL(Structured Query Language) :데이터베이스에 요청(Query)을 날려서 원하는 데이터를 가져오는 것을 도와주는 언어

데이터를 가져오는 명령어를 작성하는 것을 SQL 쿼리를 작성한다라고 함.

 

 

Select 쿼리문

개념

Query(쿼리) : 질문을 의미, 데이터베이스에 명령을 내리는 것을 의미

Select 쿼리문은 데이터 베이스에서 '데이터를 선택해서 가져오겠다'는 의미

 

테이블과 필드

• 테이블: orders라는 엑셀 시트와 동일하게 데이터가 담긴 곳, 이런 형태의 값이 데이터베이스에 담기면, orders라는 이름의 테이블이 됨
• 필드: order_no, create_at, course_title, user_id, payment_method, email 각각이 필드

 

select 쿼리문 연습

데이터 베이스의 테이블 보기

show tables

 

orders 테이블의 데이터 가져오기 / 특정 필드만 가져오기

* : orders 모든 필드 선택
select * from orders

order_no, create_at, user_id, email 필드만 가져오기
select order_no, created_at, user_id, email from orders

 

 

특정 데이터만 가져오기

Where 절의 개념

• Where 절은, select 쿼리문으로 가져올 데이터에 조건을 걸어주는 것(조건문)

order 테이블의 payment 필드의 kakaopay인 값들만 가져오기
select * from orders 
where payment_method = 'kakaopay'

point_users 테이블의 point 필드의 5000점 이상의 값들만 가져오기
select * from point_users
where point >= 5000

orders 테이블의 course_title 필드의 '앱개발 종합반'이고 payment_method가 'CARD'인 값들 가져오기
select * from orders
where course_title = '앱개발 종합반' and payment_method = 'CARD'

 

select 쿼리문에 where 절 함께 사용하는 연습

• SQL을 잘하기 위해서는, 원하는 정보가 어느 테이블에 담겨 있는지 잘 찾는 것이 매우 중요

1. 포인트가 20000점보다 많은 유저만 뽑기
select * from point_users
where point >= 20000

2. 성이 황씨인 유저만 뽑아보기
select * from users
where name = '황**'

3. 웹개발 종합반이면서 결제수단이 CARD인 주문건만 뽑아보기
select * from orders
where course_title = '앱개발 종합반' and payment_method = 'CARD'

 

쿼리 작성 팁

1. show tables로 어떤 테이블이 있는지 확인
2. 원하는 정보가 있을 것 같은 테이블에 select * from 테이블명 쿼리 날려보기
3. 원하는 정보가 없으면 다른 테이블에도 2번 반복
4. 테이블을 찾으면 조건(where)을 걸 필드 찾기
5. select * from 테이블명 where 조건 - 쿼리 완성

 

Where 절과 자주 같이 쓰는 문법 써보기

1. 조건 : 같지 않음(!=)
2. 조건 : 범위(between)
3. 조건 : 포함(in)
4. 조건 : 패턴(like)

같지 않음 - 웹개발 종합반 제외하고 주문데이터 확인
select * from orders
where course_title != '웹개발 종합반'

범위 - 7월 13일, 7월 14일의 주문 데이터만 확인
select * from orders
where created_at between "2020-07-13" and "2020-07-15" 마지막 날짜는 포함 안됨(14일까지만 포함)

포함 - 1, 3주차 사람들의 '오늘의 다짐' 데이터 확인
select * from checkins
where week in (1, 3)

패턴 - 다음 이메일을 사용하는 유저만 확인
select * from customers
where email like '%daum.net'

 

like의 다양한 사용법

• 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로 끝나는 모든 데이터

연습

// 결제수단이 CARD가 아닌 데이터
select * from orders
where payment_method != 'CARD'

// 20000~30000 포인트를 보유한 유저 데이터
select * from point_users
where point between 20000 and 30000

//이메일이 s로 시작하고 com로 끝나는 유저 데이터
select * from users
where email like 's%com'

//이메일이 s로 시작하고 com로 끝나면서 성이 이씨인 유저 데이터
select * from users
where email like 's%com' and name = '이**'

 

 

기타 유용한 문법

• 일부 데이터만 가져오기 : Limit
• 중복 데이터는 제외하고 가져오기 : Distinct
• 몇 개인지 숫자 세보기 : Count
• [응용] Distinct와 Count 같이 쓰기

// Limit : 결제정보가 kakaopay인 데이터를 5개만 가져오기
select * from orders
where payment_method = 'kakaopay'
limit 5

// Distinct : 결제정보에서 중복 데이터 제외하고 가져오기
select DISTINCT (payment_method) from orders

// Count : 데이터가 몇 개인지 숫자 세기
select count(*) from orders -> 전체 데이터 수
where payment_method = 'kakaopay' -> 결제 정보가 카카오 페이인 데이터 수

// Distinct & Count
select count(distinct(name)) from users -> 중복을 제외한 회원의 이름 수 확인

 

 

혼자서도 문제를 해결하려면

• 기초적인 SQL의 경우, 에러 메시지를 보고 원인을 파악하여 문제를 해결해 나가는 것

에러 메시지 해석

select * from orders
where payment_method = kakaopay
// 단어인 필드 값을 ''를 사용해서 감싸지 않아서 에러 발생

select * from users
where email like 's%net'
name = "이**"
// 조건을 여러 개 사용하는데 and or 등의 문구를 사용하지 않음

 

 

문제 풀기

1. 성이 남씨인 유저의 이메일만 추출

select email from users
where name = "남**"

 

2. Gmail을 사용하는 2020/07/12~13에 가입한 유저 추출

select * from users
where created_at between '2020-07-12' and '2020-07-14'
and email like '%gmail%'

 

3. Gmail을 사용하는 2020/07/12~13에 가입한 유저의 수를 세기

select count(*) from users
where created_at between '2020-07-12' and '2020-07-14'
and email like '%gmail%'

 

 

1주차 숙제

naver 이메일을 사용하면서, 웹개발 종합반을 신청했고, 결제는 kakaopay로 이뤄진 주문 데이터 추출

select * from orders
where email like '%naver%'
and course_title = '웹개발 종합반'
and payment_method = 'kakaopay'

 

 

 

통계

데이터 분석의 목적: 쌓여있는 날것의 데이터 → 의미를 갖는 '정보'로의 변환

• 데이터베이스의 테이블에 저장된 데이터: 쌓여있는 날것의 데이터
• 가장 많은 Like를 받은 사람의 이름, 전체 신청자수, 평균 연령: 의미있는 '정보'

범주(Category) 각각의 정보

• 과목별 신청자 평균 연령, 과목별 신청자 수, 성씨별 회원수 등

통계 구하기

기존 방법의 한계 - 구하려는 데이터 수가 많으면 쿼리를 여러 번 작성해야 한다(성씨별 회원수를 구하려는데 54개일 경우).

// 과목별 신청자 수 구하는 쿼리
select count(*) from orders
where course_title = '웹개발 종합반'

select count(*) from orders
where course_title = '앱개발 종합반'

 

통계에 유용한 쿼리문

• Group by (동일한 범주의 데이터를 묶어줌)
• Order by (깔끔하게 데이터를 정렬)

Group by 연습

// 성씨별 회원수를 가져오기
select name, count(*) from users
group by name

 

1. select * where users : users 테이블의 데이터 가져오기
2. group by name : name 필드에서 동일한 값을 갖는 데이터를 하나로 합치기
3. select name, count(*) : 이름과 count(*)를 출력 (count는 group by로 합쳐진 데이터 수를 세어 줌)

 

SQL 쿼리 실행 순서

작성법 순
Select -> Distinct -> From -> Where -> Group by -> Having -> Order by

실행순서
From -> Where -> Group by -> Having -> select -> Distinct -> Order by

 

연습

users 테이블 전체 가져오기
select * from users

users 테이블에서 '신'씨 성을 가진 데이터만 불러와서 개수 세기
select count(*) from users
where name = '신**'

group by를 사용해서 '신'씨 성을 가진 데이터가 몇 개인지 확인
select name, count(*) from users
group by name

 

 

Group by 기능

// 시작 전 테이블의 데이터 확인
select * from checkins
limit 10 -> 데이터가 많을 경우 가져올 데이터 수량 정하기

 

동일한 범주의 개수 구하기

// 주차별 개수 구하기
select week, count(*) from checkins
group by week

select 범주별로 세어주고 싶은 필드명, count(*) from 테이블명
group by 범주별로 세어주고 싶은 필드명

 

동일한 범주에서 최솟값 구하기

//주차별로 likes의 최솟값 구하기
select week, min(likes) from checkins
group by week

select 범주가 담긴 필드명, min(최솟값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명

 

동일한 범주에서의 최댓값

// 주차별 likes 최댓값 구하기
select week, max(likes) from checkins
group by week

select 범주가 담긴 필드명, max(최댓값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명,

 

동일한 범주의 평균 구하기

// 주차별 likes의 평균 구하기
select week, avg(likes) from checkins
group by week

select 범주가 담긴 필드명, avg(평균값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명

round(avg(likes), 2) -> likes의 평균값 소숫점 2자리까지만 표시
round(avg(평균값을 알고 싶은 필드명),표시할 소숫점 자리 수) -> 0은 반올림(소숫점 없음)

 

동일한 범주의 합계 구하기

// 주차별 likes의 합계 구하기
select week, sum(likes) from checkins
group by week

select 범주가 담긴 필드명, sum(합계를 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명

 

 

Order by

Group by로 묶은 데이터를 정렬하기

// group by로 얻은 성씨별 회원수 데이터
select name, count(*) from users
group by name

// 오름차순 정렬
select name, count(*) from users
group by name
order by count(*)

// 내림차순 정렬
select name, count(*) from users
group by name
order by count(*) desc

 

Order by 연습

order by는 group by 없이 단독으로도 사용할 수 있음

// users 테이블의 likes 필드의 값 내림차순으로 정렬
select * from checkins
order by likes desc

 

쿼리 실행 순서 복기

작성법 순
Select -> Distinct -> From -> Where -> Group by -> Having -> Order by

실행순서
From -> Where -> Group by -> Having -> select -> Distinct -> Order by

 

order by 적용 쿼리 실행 순서

1. from users : users 테이블 전체의 데이터 가져오기
2. group by name : users 테이블의 같은 name 데이터를 그룹화
3. select name, count(*) : group by에 따라 합쳐진 데이터가 각각 몇 개인지
4. order by count(*) : 합쳐진 데이터의 수에 따라 오름차순 정렬

 

Where와 Group by, Order by 같이 사용하기

문제 - 웹개발 종합반의 결제수단 별 주문 건수 세기

select payment_method, count(*) from orders
where course_title = '웹개발 종합반'
group by payment_method
// 정렬 추가할 경우
order by count(*) -> 오름차순
order by count(*) desc -> 내림차순

 

중요: SQL 쿼리가 실행되는 순서를 알고 작성해야 에러가 발생하지 않는 쿼리문을 작성할 수 있다.

 

 

에러& 문제 해석해보기

select * from orders
group by payment_method

• 위 코드에서 통계치가 나오지 않는 이유는 group by로 payment_method를 그룹화 한 후 어떤 통계치를 출력할지 명령문을 작성하지 않았기 때문에 group by의 결과만 나온다.
• payment_method에 대한 각각의 개수에 대한 통계를 보고 싶을 경우 select payment_method, count(*) from orders로 첫번째 줄을 수정하면 된다.

 

쿼리 연습하기(Group by, Order by)

Order by

• 문자열 기준으로 정렬
• 시간 기준으로 정렬

// 문자열 기준 오름차순 정렬
select * from orders
order by email -> 내림차순 시 email 뒤에 desc 추가

select * from users
order by name

// 시간 기준 내림차순 정렬
select * from orders
order by created_at desc

 

Group by

• 앱개발 종합반의 결제 수단별 주문 건수 세기
• Gmail을 사용하는 성씨별 회원 수 세기
• course_id 별 '오늘의 다짐'에 달린 평균 like 개수 구해보기

// 1번 문제
select payment_method, count(*) from orders
where course_title = '앱개발 종합반'
group by payment_method
order by count(*) -> 오름차순 정렬

// 2번 문제
select name, count(*) from users
where email like '%gmail%'
group by name
order by count(*) desc -> 내림차순 정렬

// 3번 문제
select course_id, round(avg(likes), 2) from checkins
group by course_id

 

 

유용한 문법 Alias(별칭 기능)

쿼리가 점점 길어지면 종종 헷갈리는 일이 생길 수 있어서 SQL은 Alias라는 별칭 기능을 지원함.

select * from orders o -> alias 기능으로 orders 테이블의 별칭을 'o'로 지정
where o.course_title = '앱개발 종합반' -> o.course_title : orders.course_title 이라는 의미

select payment_method, count(*) as cnt from orders o 
where o.course_title = '앱개발 종합반'
group by payment_method
// count(*) as cnt : group by로 그룹화한 payment_method의 count(*)을 cnt로 표기(Alias 기능)

 

 

2주차 숙제

네이버 이메일을 사용하여 앱개발 종합반을 신청한 주문의 결제수단별 주문 건수

select payment_method, count(*) from orders
where email like '%naver%' and course_title = '앱개발 종합반'
group by payment_method

 

 

 

중요

• SQL 쿼리가 실행되는 순서를 알고 작성해야 에러가 발생하지 않는 쿼리문을 작성할 수 있다.
• 기초적인 SQL의 경우, 에러 메시지를 보고 원인을 파악하여 문제를 해결하기

쿼리 작성 순서 및 실행 순서

작성법 순
Select -> Distinct -> From -> Where -> Group by -> Having -> Order by

실행순서
From -> Where -> Group by -> Having -> select -> Distinct -> Order by