MYSQL 란? select Query 를 통해 저장된 데이터를 가져온다
C(create,생성) R(read,저장된 데이터를 꺼내는) U(updete,수정) D(delete,삭제)
테이블과 필드로 이루어져 있음
(orders 의 앱개발 종합반을 듣는데 카드로 결제한 데이터를 불러온것 )
select from 의 은 지정 테이블의 모든 정보를 가져 오는것
where 절을 통해 지정을 할 수 있다
and 는 or
문법
!= 를 사용해 값이 같지 않은 값을 찾아온다
BETWEEN 을 사용해서 ~부터 ~까지 값을 가지고옴 사진은 20-07-13 ~ 14일 까지의 값을 찾는데
14일 23시59분59초 까지는 14일이기 때문에 위에 코드는 15일로 설정함
week 가 1, 3인 값을 가져오려는데
값을 포함할때 in () 일치하는 값을 넣으면 된다
특정 문자를 통하거나
어떠한 패턴으로 값을 찾아올때 네이버 메일을 사용한 유저만 찾아온 것이다
like 를 통해 가져오는데 % 는 앞에 뭐가 있든 제외하고 % 뒤에 값을 가져온다
또 하나의 예시 a로 시작해서 t 로 끝나는 값을 가져오려면 a%t 이런식으로 사용가능
예시로 어떤 코멘트에 화이팅이 들어가는 모든 것을 가져올때
comment like '%화이팅%' 이런식으로 해주면 앞 뒤에 뭐가 붙던 화이팅이 들어간 코멘트는 다 출력할 수 있다.
LIMIT 을 사용해 원하는 데이어 양만 가져오기
큰 테이블을 사용할때 다 불러오게 되면 로딩이 걸릴 수 있어 limit 을 사용해서 방지한다
중복 되는 값들을 제외하고 가져올때 DISTINCT 를 사용한다
사진은 orders 의 페이먼트 메서드를 중복제거 하고 보여준것
count 를 사용해 필드 값들이 총 몇개인지 셀 수 있다
사진은 orders 가 얼마나 있는지 카운트 한것
where payment_method = 'kakaopay'
이런식으로 페이먼트 메서드에서 카카오페이로 결제한 것만 카운트 할 수도 있음
DISTINCT, COUNT 응용하기
위 사진은 users 의 name 들을 중복제거하고 카운트 한것
group by(동일한 범주의 데이터를 묶어줌)
order by(데이터 정렬 예:오름차,내림차 등)
-----------------------------------------------------------------------------------------------------
group by 로 user name 을 묶어서 카운트를 한것 (같은 성씨의 수)
최소값은 min 최대값은 max 합계 sum 사진은 최대값을 구한것 값을 볼때 * 이라니라 보고싶은 테이블 명을 써야함
평균값은 avg 로 구하는데 소수점 까지 다 나오기 때문에 반올림을 위해 round 로 감쌀 수 있다
round(avg(likes),?) , 콤마 뒤에 들어가는 수에 따라 소수점 몇까지 출력 가능 0은 평균
order by 는 정렬이기 때문에 제일 마지막에 사용한다
아래 사진은 그룹바이로 이름을 정렬하고 오더바이로 정렬을 한것 기본적으로
*로 하면 오름차순 이 된다 숫자만이 아니라 문자열, 시간별도 정렬가능
order by count(*) desc 내림차순
order by name
order by created_at
where 절과 그룹바이 오더바이 같이 쓰기
사진은 웹개발 종합반의 결제수단별 주문건수를 출력한것이다
오더스의 코스_타이틀을 웹개발 종합반 테이블을 짜르고 그룹바이로 결제수단은 묶고 카운드를 통해 보여준다
마지막 오더바이로 오름차순 정렬한 것이다
테이블의 별칭 (alias)
별칭은 보통 짧게 알파벳 1~2 사용
사진은 orders 테이블을 이제 o 라고 칭하고
where 절에 o.course_title 은 order 테이블의 course_title 을 부른것이다
Join
조인이란? 두 테이블의 공통된 정보를 (KEY값) 기준으로 테이블을 연결해 한 테이블 처럼 보이게 하는것
예시
user_id 필드를 기준으로 user 테이블과 orders 테이블을 연결해 한 눈에 보게한다
Join 의 종류로 Left Join, Inner Join 이 있다
먼저 Left Join 예시이다 레프트 조인은 왼쪽에 있는 테이블을 기준으로 조인 한다는 뜻이다
값이 없으면 NULL 로 표시가 된다
사진은 각자 별칭(alias)을 줬고 users 테이블을 기준으로 point_users 에 있는 user_id를
조인한 것이다
Inner Join 은 사진에서 Left를 지우고 inner 로만 바꾸면 된다
실행 결과는 포인트가 있는 user_id 만 가지고 온다 Inner Join은 교집합 이라고 생각하면 된다
두 테이블을 조인해 과목별 통계치를 찾은 사진이다
GROUP BY 를 통해 과목별로 묶는다 이때 어떤 테이블의 course_id 를 찝어줘야 인식을 하기 때문에
사진 처럼 ck.course_id 로 정해줬고 c.title을 통해서 필드값도 가져오고
COUNT 로 센다 결과값 필드를 AS를 사용해 cnt 로 바꾸어 보았다
위 사진은 point_users 테이블과 users 테이블을 조인하여
유저ID, 이름, 이메일과 함께 포인트를 내림차 순으로 정렬한 것이다
orders 테이블과 users 테이블을 조인하여 네이버 이메일을 쓰는 성씨별 주문건수를 가져온 것이다
사진은 결제수단의 평균 포인트를 구한 것이다
사진은 is_registered = 0 인 사람들의 성씨별 통계를낸것이다
웹,앱 개발 종합반의 week 별 체크인 수를 정렬 해놓은것 그룹바이,오더바이에 and 를 쓰지 않고 ,로 2개이상 묶을 수 있다
추가로 08-01 이후 구매한 사람들을 추가적으로 뽑아 보았다
SELECT c.title, ck.week, o.created_at, COUNT(*) FROM courses c
INNER JOIN checkins ck ON c.course_id = ck.course_id
INNER JOIN orders o ON ck.user_id = o.user_id
WHERE o.created_at >= '2020-08-01'
GROUP BY c.title, ck.week
ORDER BY c.title, ck.week
체크인스 테이블에 오더스 테이블을 이너조인 한번 더 하고 WHERE 절을 통해 목표시간대보다 크거나 같은 >= 넣어 주었다
left join
left join은 어디에 → 뭐를 붙일건지 순서가 중요하다
한쪽 테이블에 있는데 다른쪽에는 없는 필드값을 조인하는 것이다
예시로 users 테이블에는 없는데 point_users 에는 있는 포인트 값을 users로 붙인다
레프트 조인을 하면 값이 없는 필드는 NULL 나오게 된다
아래 사진은 강의 시작을 하지 않아 포인트가 없는 사람들을 뽑아온 것이다
group by name 으로 많은 성씨들을 카운트한 것이다
NULL 값이 아닌 포인트가 있는 유저들을 가져오려면 IS NOT NULL 을 하면 된다
사진은 정해진 기간내에 포인트가 있는 유저의 수, 전체 유저의 수, 둘의 비율을 구한 것이다
point_user_id 와 users_id 를 / 통해 나눴다
줄정렬도 신경써서 하자
UNION
유니온이란 예를 들어 7월의 표와 8월의 표가 있는데 이를 이어서 붙혀서 값을 뽑고 싶을때 쓰는 것이다
사진 처럼 () () 사이에 UNION ALL 을 하면 이러진다
하나 재밌는건 사진을 보면 ORDER BY 가 깨져있다
UNION 은 합치기 전 ORDER BY가 먹지 않아 사진처럼 합친 후 ORDER BY를 해줘야 한다
사진은 enrolleds 테이블과 enrolleds_detlal 테이블을 inner join 으로 합쳐서
enrolleds_detlal 테이블의 done 필드 값이 = 1 인 유저를 찾아 강의 완료한 강의가 많은 순으로
정렬한 것이다
SubQuery
서브쿼리란
큰 쿼리문 안에() 들어가는 쿼리문을 서브쿼리라 부른다
실행순서는 가장 안에 있는 쿼리문부터 실행되어 결과를 만든다 (사진에는 ()안에 들어가 있는 쿼리문을 뜻함)
사진에 보여진 것은 WHERE 절에 있는 서브쿼리를 쓴것이다
코드해석: 괄호안에 있는 값들이 user_id 안에 있으면 그것을 뽑아주는 것이다
SELECT 안에 서브쿼리가 들어가면 SELECT 될 때마다 각각 하나하나씩 서브쿼리가 실행된다
user_id 가 한줄씩 나올때마다 서브쿼리문이 실행되어 그 유저의 평군 좋아요 수를 뽑아 필드 옆에 붙인다
from 절에 서브쿼리 사용하기 (현업에서 제일 많이 쓴다고함)
() 안에 있는 서브쿼리의 테이블을 a 지정하고 a의 user_id 와 pu의 user_id 를 조인해서
서브쿼리를 하나의 테이블로 만들어 사용이 가능하다
내가 만든 SELECT 문을 원래 있던 테이블 처럼 만들어 from 절을 이용해 사용이 가능하다
문제: 이씨 성을 사진 유저들의 평균 포인트보다 더 많은 포인트를 가지고 있는 데이터를 추출하기
1.INNER JOIN 으로 point_users pu 테이블과 users u 테이블을 조인한다
2.WHERE 절로 users 테이블 안에 있는 이름중 이씨만 추출한다
3.SELECT avg(point) 로 포인트의 평균 값을 구한다
4.1 ~ 3 에서 만든 조인한 테이블을 서브쿼리로 사용해 () 안에 넣는다
문제
만들었던 서브쿼리에 INNER JOIN 을 해서 title 필드값 넣기
알게된 점 as 와 from 사이에 있는 c2.* 은 조인했던 테이블의 값들을 다 보여달라는 뜻이다
저렇게 확인하고 목표했던 title 필드값이 들어와 있던걸 확인했기 때문에 지우고
c.course_id 를 c2.title 로 바꿔주면 원하는 답을 추출할 수 있다 ( , 조심!)
INNER JOIN 2번하기
- checkins 테이블에서 course_id 로 GROUP BY 하고 유저아이디를 중복 없이 카운트 한다 카운트한 필드 명을 바꾼다
- orders 테이블에서 course_id 로 GROUP BY 하고 course_id 별로 수를 센다 카운트한 필드명을 바꾼다
- 위에 두 테이블을 INNER JOIN 하고 checkins 테이블은 a, orders 테이블은 b 라 칭한뒤 select 에서
필요한 필드명들을 적고 checkins 와 total 값을 / 나눈다 - title 필드는 courses 테이블에 있기 때문에 checkins 테이블의 course_id 와 courses 테이블의 course_id 를
INNER JOIN 한다 후에 select 에서 title 필드를 불러온다
with
위 처럼 서브커리들을 쓰면 조금 코드가 길어지는? 수 밖에 없다
그래서 with 절로 서브쿼리들을 알리아스 처럼 하나의 테이블에 넣어 사용할 수 있다
첫번째 서브쿼리를 table1 로 넣고 두번째 서브쿼리를 table2 로 넣어서
from 절에 칭했던 테이블을 넣으면 값은 같지만 코드의 가독성을 훨씬 좋게 뽑을 수 있다
실전에서 사용하는 SQL 문법들
문자열 쪼개기(substfing_index)
이메일 주소에서 @앞의 아이디만 가져오거나, @뒤의 이메일 도메인을 가져 오기!
substfing_index 를 사용해 이메일의 @ 를 기점으로 짤라서 값을 가져오는 방법이다
1을 넣었을때는 사진과 같이 @ 기점으로 앞에 문자(아이디)들이 나오고 -1 을 하게되면 @ 기점으로 뒤에 문자(도메인)들을 가져올 수 있다
문자열 일부만 출력하기(substfing)
orders 테이블에서 created_at을 날짜까지만 출력하게 하기!
substfing 의 시작 포인트1과 마지막 포인트10 설정해
(첫번째부터 10번째 자리를 따오는 것)
created_at의 문자를 원하는 만큼만 가져온 것이다
더 나아가 일별로 몇 개씩 주문이 일어났는지도 출력해보기
data 를 group by 하고 count(*) 하게 되면 그날 주문건 수를 볼 수 있다
경우에 따라 원하는 값을 새 필드에 출력하기(case)조건문
10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있네', 평균보다 낮으면 '조만 더 힘내' 라고 표시해 주려면 어떻게 해야할까?
when:어떤경우에 then: 그때는
case 를 걸고 point가 10000이 넘을때는 '잘하고 있네', 그렇지 않을때는 '조금 더 힘내' 를 새 필드에 나오게 하고
end 로 끝을 내줘야한다
더 나아가 서브쿼리까지 이용해 하나의 테이블로 만들고 통계까지 내보기
위 point_users 테이블을 서브쿼리로 만들고 point 값을 group by 해서 통계치를 냈다
with절 까지 사용하면?
이제 저 값들은 table1 안에 넣어놨기 때문에 계속 꺼내서 사용이 가능하다
배운것들 사용해서 문제풀기
1. 평균 이상 포인트를 가지고 있으면 '잘 하고 있어요' / 낮으면 '열심히 합시다!' 표시하기!
평균 값을 구한avg 값은 () 안에 넣어줘야 숫자를 넣어을때 역할을 해준다
2.이메일 도메인별 유저의 수 세어보기
서브스트링_인덱스로 이메일을 나눠 도메인만 뽑은 뒤
서브쿼리를 만들고 그룹바이로 도메인을 카운트 하였다
3. 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기
-
enrolled_detail 테이블을 where 절로 done이 1인 값을 출력 후
group by 를 사용해 enrolled_id 별로 묶고 count 한다 (1쿼리) -
위 1번 쿼리에서 done 값만 빼고 그대로 가져온뒤 done 카운트한 필드를 total로 바꾼다
-
이제 두 값을 서브쿼리로 만들고 inner join 한 뒤 두 쿼리를 enrolled_id로 엮는다
SELECT a.enrolleds_id, a.done_cnt, b.total_cnt FROM (
SELECT enrolled_id, COUNT() AS done_cnt FROM enrolleds_detail
WHERE done = 1
GROUP BY enrolled_id
) a
INNER JOIN (
SELECT enrolled_id, COUNT() AS total_cnt FROM enrolleds_detail
GROUP BY enrolled_id
) b ON a.enrolled_id = b.enrolled_id -
with 절까지 사용해 코드의 가시성을 살린다
-
done 값과 total 값을 나누기로 진도율을 구하고 round로 소수점 2자리 까지만 출력한다
코드를 더 줄일 수 있는 방법
select enrolled_id,
sum(done) as cnt_done,
count(*) as cnt_total
from enrolleds_detail ed
group by enrolled_id
굳이 서브쿼리들을 생성하지 않아도 sum으로 done 값을 구하면 코드가 훨씬 간편해진다 쿼리를 다르게 짰기 때문에 값순서는 다르지만 결과는 똑같다!
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
설정 파일 안에서 키:
c (수정하기)
수정 후
esc :wq(저장) :q(나가기) :p!(강제 나가기)
