1. SQL과 쿼리문이란?
1) SQL이란
SQL은 데이터베이스에서 자료 및 관리를 위해서 설계된 구조적(Structured) 쿼리(Query) 문(Language)이다.
2) 쿼리(Query)문이란
쿼리(Query)문이란 데이터베이스에 명령을 내리는 것을 의미한다.
ex) Select 쿼리문= 데이터를 '선택'해서 가져오겠다는 명령을 의미
2. SQL의 용어
데이터베이스에서 원하는 데이터를 추출하려면 목적에 맞는 명령을 내려야 한다.
데이터베이스에서 원하는 요구가 다양한 만큼 SQL에서 사용되는 용어 또한 다양하다.
1) Select, where
Select와 where은 SQL에서 사용되는 기본적인 용어다.
Select는 데이터를 선택해서 가져오고 where은 Select로 가져올 데이터에 조건을 걸어준다.
① Select는 어떤 테이블에서 어떤 필드의 데이터를 가져올지로 구성된다. 아래의 사진은 users의 테이블에서
user_id, created_at... 과 같은 필드의 데이터를 추출한 모습이다.
①-1 특정 테이블의 데이터를 가져오기 위해서는 Select 필드명 from 테이블명 과 같은 문법을 짜야한다.
①-2 Select * from users 는 users라는 테이블의 모든 필드 데이터를 선택해서 가져오겠다는 뜻 이다. 특정 필드를 가져오기 위해서는 * 대신 필드명을 적으면 된다. 각 필드명 마다 ' , '를 넣어서 구분해야 한다.
② where은 Select로 가져올 데이터에 조건을 걸어준다.
단적인 예로
'특정 데이터를 포함하는 데이터만 ', '특정 수치 이상의 데이터만', '특정한 문자열을 포함하는 데이터만' 등의 조건을 건다.
특정 필드의 선택된 데이터만 가져오는 조건을 걸기 위해서는
where 필드명 (부등호) "문자열", 숫자 와 같은 문법을 짜야한다.
where name = "이**" 은 name 필드에 "이**"을 포함하는 데이터만 보여준다.
숫자만을 표현할 때는 " " 를 넣지 않는다. 그러나 특수문자가 섞여있으면 넣어야 한다.
②-1 where에 자주 쓰는 문법
where에 여러 조건을 걸기 위한 and, 같지 않음의 !=, 범위설정의 between "A" and "B", 포함하는 in
패턴 설정의 like가 있다.
②-2 예를 들면
where name = "이**" and email like "%naver.com" => 성이 '이'씨인 사람과 naver 이메일을 사용하는 사람의 데이터
where name != "이**' => 성이 '이'씨가 아닌 사람들의 데이터
where craeted_at between "2020-07-13" and "2020-07-15" => 7월13일부터 7월14일까지의 데이터
※본문의 데이터는 '2020-07-12 19:21:46' 처럼 년, 월, 일, 시, 분, 초 로 구성되어 있다. between에서는 '2020-07-13'을
'2020-07-13 00:00:00'으로 인식하기 때문에 and '2020-07-15' 로 조건을 설정하면 15일0시0분 부터 23:59:59까지의 데이터를 얻을 수 없다.
where name in ("이**","김**") => 성이 '이'씨와 '김'씨인 사람들의 데이터
where email like "%naver.com" => email의 데이터가 'naver.com' 으로 끝나는 사람들의 데이터
where email like "abc%" => email의 데이터가 'abc'로 시작하는 사람들의 데이터
where email like "%abc%" => email의 데이터가 'abc'를 포함하는 사람들의 데이터
③
④
⑤
⑥
2. 데이터의 통계와 정렬
데이터베이스에 있는 데이터에 통계라는 의미를 부여하려면 의미에 맞는 명령을 내려야한다.
EX) 데이터베이스에 저장되어있는 사람들의 평균연령, 성씨별 회원수, 가장많은 성씨, 가장적은 성씨...등
'통계를 낸다.' 는 어떤 결과를 한눈에 알아보기 위해 일정한 체계를 거쳐 수치로 표현한다는 뜻이다.
SQL을 이용하면 특정 데이터가 몇 개인지, 최소 값은 몇인지, 평균은 몇인지 등을 알 수 있다.
1) Group by
Group by는 동일한 범주를 갖는 데이터를 하나로 묶어서, 범주별 통계를 내주는 것을 의미한다.
Group by name 은 name 필드에 동일한 범주를 갖는 데이터를 하나로 묶었다는 뜻이다.
2) 개수/최소/최대/평균/합계
Group by로 묶은 데이터의 범주를 분석 및 원하는 통계 값을 얻을 수 있는 용어다.
count(개수)/ 최소(min)/ 최대(max)/ 평균(avg)
select 범주가 담긴 필드명, count(개수를 알고 싶은 필드명) from 테이블명
group by 범주별가 담긴 필드명
범주별로 세어주고 싶은 필드명이 name 이라면 먼저 Group by로 묶는다. 그 다음 Select문에 범주별로 세어주고 싶은
필드명을 넣고, count에 개수를 알고싶은 필드명을 넣으면 된다.
min/ max/ avg/ sum를 count 자리에만 넣어주면 같은 과정을 거쳐서 결과가 나온다.
3) Order by
Order by는 통계과정을 거친 결과값을 정렬시켜준다.
count 사진의 결과처럼 수치가 중구난방하게 나열되어 있다.
이를
Select name, count(name) from users
Group by name
Order by count(name) 로 정렬시키면 수치가 오름차순으로 정렬이 된다.
내림차순으로 정렬하고 싶다면 Order by count(name) desc 로 정렬이 된다.
4) Alias
Alias는 별칭기능이다. 예를 들어 Select문의 테이블 명 뒤에 from orders o 라고 기입하면 o. 필드명은
orders 테이블의 필드를 지칭한다. 이 기능은 여러가지 테이블 가져와서 데이터를 출력해야 하거나 SQL의 쿼리문이 길어질 경우 사용한다.
테이블 뿐만 아니라 필드에도 별칭을 넣을 수 있다. Order by의 사진을 보면 성씨별 개수를 나타내는 필드명이 count(name)으로 되어 있다. 이를 count(name) as cnt를 넣으면 필드명이 cnt로 바뀐다.
select name, count(name) as cnt from users
group by name
order by count(name) desc
3. 테이블을 연결하기
한 테이블에 너무 많은 데이터가 있으면 이해하기 어렵다. 그래서 보기에 좋고 이해하기 쉽도록 테이블의 성질에 따라서
데이터를 분류를 해놓는다. 하지만 각 테이블의 데이터를 같이 봐야하는 경우가 생긴다면 그때는 각각의 테이블을 연결시켜서 데이터를 출력시킬 수 있다.
1) join
join은 두 테이블의 공통된 정보 (key값)를 기준으로 테이블을 연결해서 한 테이블처럼 보는 것을 의미한다.
예를 들어 users 테이블의 필드와 orders 테이블의 필드에 공통의 필드가 존재한다면 그 필드를 기준으로
users와 orders를 join으로 이어줄 수 있다.
①inner join
inner join은 두 테이블이 같이 공유하는 데이터들만 추출한다.
users 테이블과 orders 테이블은 user_id라는 필드를 공통으로 가지고 있다. 하지만 users에는 존재하는 user_id의 데이터가 orders 테이블에는 존재하지 않는다면 그 데이터는 출력 되지 않는다.
join문은 연결의 기준이 되고싶은 테이블을 from 절에, 붙이고 싶은 테이블을 Join 절에 놓는다. users와 orders의 연결은
Select * from users u
inner join orders o on u.user_id = o.user_id 로 표현할 수 있다.
②left join
left join은 기준 테이블의 모든 데이터와 연결되는 테이블의 겹치는 데이터를 추가한다.
붉은 상자는 users 테이블의 데이터다. 사진과 같이 모든 데이터가 누락없이 기입되었다.
파란 상자는 orders 테이블의 데이터다. users 테이블을 기준으로 orders 테이블에 없는 데이터는 누락되어있다.
orders 테이블에만 존재하는 user_id의 데이터는 inner join과 left join에도 추출되지 않는다. 그래서 join으로 두 테이블을 연결할 때는 기준이 되는 (from절의 테이블) 테이블이 중요하다.
③left join과 null 데이터
left join을 하면 기준 테이블에는 존재하지만 연결 테이블에는 존재하지 않는 데이터에 null 값이 기입된다.
SQL의 null 값은 여러 특성을 가지고 있다. 첫번째로 null은 count가 세지 않는다. users 테이블의 필드의 개수와 orders 테이블의 null이 있는 필드의 개수에서 차이가 난다.
두번째는 Where 절로 null이 있는 데이터만 추출하거나 null이 없는 데이터만 추출하는 것이 가능하다.
Where o.order_no is null 은 order.no의 null값이 포함된 데이터만 추출한다.
Where o.order_no is not null 은 반대로 null값이 없는 데이터만을 추출한다.
2) union
union은 Select로 끝난 쿼리문의 결과를 합치는 역할을 한다. 예를들어 8월 9일 이전의 주차별 좋아요 개수의 통계와
8월9일 이후의 주차별 좋아요 개수의 통계를 합치려면 각 쿼리문을 괄호로 닫아주고 가운데에 union all 을 넣어야한다.
(Select 'Bf08-09' as day, week, count(likes) from checkins c
where created_at < '2020-08-09'
group by week)
union all
(Select 'Af08-09' as day, week, count(likes) from checkins c
where created_at >= '2020-08-09'
group by week)
두개의 쿼리문이 합쳐진 결과가 나온다.
4. Subquery
Subquery란 하나의 쿼리 안에 또 다른 쿼리 문이 존재한는 뜻이다. 좀 더 간략하고 정확한 데이터를 얻을 때 필요하다.
Subquery는 where, select, from 절에 각각 적용 할 수 있다.
1) Where절 Subquery
Where은 조건문이다. Subquery의 결과를 조건에 활용하는 방식으로 where 필드명 in (subquery)의 형태다.
예를 들어 kakaopay로 결제한 유저들을 출력하고 싶은 경우
select * from users u
where u.user_id in (select o.user_id from orders o
where o.payment_method = 'kakaopay')
from 실행: users 데이터를 가져와줌
Subquery 실행: 'kakaopay'가 결제수단인 user_id의 명단을 뽑아줌
where .. in 절에서 subquery의 결과에 해당되는 'user_id의 명단' 조건으로 필터링 해줌
조건에 맞는 결과 출력
2) Select절 Subquery
Select는 결과를 출력해주는 부분이다. 기존 테이블과 함께 보고싶은 통계 데이터를 붙이는 방식으로
select 필드명, 필드명, (subquery) from의 형태다. 예를 들어 평소 본인이 받았던 좋아요의 수와 오늘 받은 좋아요의 수를 비교하는 데이터를 얻고 싶은 경우
select c.checkin_id, c.user_id, c.likes,
(select avg(likes) from checkins c2
where c2.user_id = c.user_id) as avg_like_user
from checkins c
checkins c 데이터를 한줄한줄 출력하는 과정이 진행된다.
그 과정에서 select 안의 subquery가 매 데이터 한줄마다 실행이 된다.
subquery의 내용은 checkins의 user_id의 좋아요 평균값이다.
Select c.user_id가 c2.user_id와 같은 값을 가지기 때문에 c.user_id의 데이터의 평균 좋아요 값을 subquery에서 계산해서
함께 출력해준다.
3) From절 Subquery
From은 만든 Select와 이미 있는 테이블을 Join하고 싶을 때 사용한다.
select user_id, round(avg(likes),1) as avg_like from checkins
group by user_id
먼저 checkins 테이블의 user_id 별 좋아요 평균 개수의 데이터를 구해놓았다.
select pu.user_id, pu.point from point_users pu
그리고 point_users 테이블의 user_id와 point의 데이터를 구해놓았다.
위의 두개의 데이터를 join해서 한 화면에 같이 보게 하기 위해서는
select pu.user_id, a.avg_like, pu.point from point_users pu
inner join (
select user_id, round(avg(likes),1) as avg_like from checkins
group by user_id
) a on pu.user_id = a.user_id
point_users 테이블에 inner join으로 서브쿼리를 괄호로 묶고 Alias 'a' 로 지칭한다.
그리고 공통의 필드로 inner join을 완성시킨다.
기존의 point_users의 필드와 미리 구해놓았던 커스텀한 데이터가 join된 모습이다.
4) With절
with절은 너무 긴 쿼리문을 보기 쉽게하기 위해서 서브 쿼리를 하나의 용어로 압축시키는 역할을 한다.
select c.title,
a.cnt_checkins,
b.cnt_total,
(a.cnt_checkins/b.cnt_total) as ratio
from
(
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
) a
inner join
(
select course_id, count(*) as cnt_total from orders
group by course_id
) b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id
한 쿼리문에 Select 절이 3개나 들어가 있어 길고 복잡해 보인다.
with절은 with 서브쿼리의 별칭 as (subquery) 의 문법이다.
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절을 적용한 쿼리문이다. with절이 없는 쿼리문과 똑같은 결과를 보여준다.
with절을 적용해도 길어보이지만 결과를 보여주는 쿼리문은
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
분량이 굉장히 단축된다.
'SQL & 웹 개발 숙제' 카테고리의 다른 글
| 웹개발 4주차 숙제코딩 (0) | 2023.05.01 |
|---|---|
| 웹개발 3주차 숙제코딩 (0) | 2023.04.26 |
| SQL 3주차 숙제 코딩 (0) | 2023.04.25 |
| 웹개발 2주차 숙제코딩 (0) | 2023.04.21 |
| SQL 2주차 숙제 코딩 (0) | 2023.04.20 |
