[SK플래닛] ASAC 빅데이터전문가 11기 | 15일차

15일차부터는 SQL이 단순 개념 설명을 넘어서 실제로 테이블을 만들고, 데이터를 넣고, 조건으로 조회하고, 여러 테이블을 연결하는 단계로 넘어갔다. 전날에는 DB, DBMS, RDBMS, DDL, DML 같은 기본 개념을 잡았다면, 이번에는 그 개념들이 실제 SQL 코드에서 어떻게 쓰이는지 확인한 날이었다.

전체 흐름은 꽤 많았다. create database, create table, insert into, select, where, group by, having, order by, limit, 그리고 join까지 이어졌다. 처음엔 문법이 많아 보였는데, 정리해보면 결국 SQL은 데이터를 만들고, 넣고, 원하는 조건으로 보고, 계산하고, 여러 테이블을 연결해서 보는 언어였다.

1. DB와 테이블을 직접 만들기

가장 먼저 한 것은 실습용 데이터베이스와 테이블을 만드는 작업이었다. 회원 정보를 저장하는 membertbl, 상품 정보를 저장하는 producttbl을 만들었다.

create database if not exists shopdb2;

use shopdb2;

create table membertbl (
    memberID char(8) not null primary key,
    memberName varchar(5) not null,
    memberAddress varchar(20)
);

여기서 create database if not exists shopdb2는 shopdb2라는 DB가 없을 때만 새로 만들겠다는 의미이고, use shopdb2는 앞으로 사용할 DB를 지정하는 명령어다. 그 다음 create table을 통해 회원 테이블을 만들었다.

회원 테이블에서 가장 중요한 컬럼은 memberID였다. memberID는 not null primary key로 지정했기 때문에 반드시 값이 있어야 하고, 각 회원을 구분하는 기준이 된다. 반면 memberAddress에는 not null을 붙이지 않았기 때문에 주소는 입력하지 않아도 되는 선택값이었다.

상품 테이블도 만들었다.

create table producttbl (
    productName char(4) not null primary key,
    cost int not null,
    makeDate date,
    company char(5),
    amount int not null
);

여기서는 productName이 상품을 구분하는 기본키이고, cost, amount는 숫자 데이터이므로 int를 사용했다. makeDate는 날짜이므로 date 타입을 사용했다. 테이블을 설계할 때 단순히 컬럼 이름만 정하는 게 아니라, 각 컬럼이 필수인지, 어떤 자료형인지, 어떤 값이 데이터를 구분하는 기준인지까지 같이 생각해야 했다.

2. char와 varchar 차이

테이블을 만들면서 char와 varchar 차이도 다시 정리했다. 둘 다 문자열을 저장하는 자료형이지만, 공간을 사용하는 방식이 다르다.

char(20)

char(20)은 무조건 20칸을 고정으로 확보한다. 입력값이 짧아도 공간은 고정으로 잡힌다. 반면 varchar(20)은 최대 20칸까지 저장할 수 있지만, 실제 입력된 글자 수에 따라 공간을 가변적으로 사용한다.

varchar(20)

그래서 길이가 거의 고정된 값에는 char, 길이가 제각각인 값에는 varchar를 쓰는 게 자연스럽다. 예를 들어 회원 ID처럼 길이가 어느 정도 정해진 값은 char(8)로 잡을 수 있고, 주소처럼 길이가 달라질 수 있는 값은 varchar(20)으로 잡는 식이다.

3. 데이터 입력: insert into

테이블을 만든 다음에는 데이터를 직접 넣었다. 회원 데이터는 insert into로 입력했다.

insert into membertbl
    (memberID, memberName, memberAddress)
values
    ('Dang', '당탕이', '경기 부천시 중동');

insert into membertbl
    (memberID, memberName, memberAddress)
values
    ('Jee', '지운이', '서울 은평구 증산동');

기본 구조는 다음과 같다.

insert into 테이블명 (컬럼명들)
values (값들);

여기서 중요한 건 컬럼 순서와 값의 순서가 맞아야 한다는 점이다. 문자 데이터는 따옴표로 감싸고, 숫자 데이터는 보통 따옴표 없이 작성한다.

상품 데이터도 같은 방식으로 넣었다.

insert into producttbl
    (productName, cost, makeDate, company, amount)
values
    ('컴퓨터', 10, '2021-01-02', '삼성', 17);

insert into producttbl
    (productName, cost, makeDate, company, amount)
values
    ('세탁기', 20, '2022-09-03', 'LG', 3);

날짜는 '2021-01-02'처럼 문자열 형태로 넣지만, 컬럼 타입은 date로 지정되어 있다. 처음에는 그냥 값을 넣는 코드처럼 보였는데, 생각해보면 이 단계는 테이블 설계에서 정한 규칙에 맞춰 데이터를 채우는 과정이었다.

4. 테이블 구조와 데이터 확인

테이블을 만들고 데이터를 넣은 뒤에는 제대로 만들어졌는지 확인해야 했다.

show databases;
use shopdb2;
show tables;
desc membertbl;
select * from membertbl;

show databases는 MySQL 서버 안에 있는 DB 목록을 보여주고, show tables는 현재 선택한 DB 안에 있는 테이블 목록을 보여준다. desc membertbl은 테이블의 컬럼명, 자료형, null 가능 여부, key 여부를 확인할 때 사용했다.

select * from membertbl;
select * from producttbl;

select *는 테이블의 모든 컬럼을 조회한다. 아직은 데이터가 적으니까 select *로 전체를 보는 게 괜찮지만, 데이터가 많아지면 필요한 컬럼만 선택해서 보는 습관이 필요할 것 같다.

5. 인덱스: 검색을 빠르게 하기 위한 구조

인덱스도 간단히 실습했다. employees DB에서 일부 데이터를 가져와 indextbl을 만들고, first_name으로 검색했다.

use employees;

create table indextbl (
    first_name varchar(14),
    last_name varchar(16),
    hire_date date
);

기존 테이블에서 데이터를 가져와 새 테이블에 넣는 코드도 사용했다.

insert into indextbl
select first_name, last_name, hire_date
from employees
limit 500;

이 코드는 employees 테이블에서 first_name, last_name, hire_date를 가져와 indextbl에 500개만 넣는 구조였다. 그 다음 first_name = 'Mary'인 사람을 검색했다.

select *
from indextbl
where first_name = 'Mary';

인덱스가 없으면 데이터가 많을 때 전체 데이터를 훑어야 할 수 있다. 그래서 자주 검색하는 컬럼에는 인덱스를 만들 수 있다.

create index idx_firstname
on indextbl(first_name);

인덱스는 책 뒤쪽의 색인 같은 느낌이었다. 모든 페이지를 처음부터 넘기는 대신, 색인을 보고 원하는 단어가 있는 위치로 바로 가는 구조다. 데이터가 적을 때는 차이가 잘 안 느껴질 수 있지만, 데이터가 많아질수록 중요한 개념일 것 같다.

6. sqldb 생성과 회원/구매 테이블

그 다음에는 sqldb를 만들고, 회원 테이블 usertbl, 구매 테이블 buytbl을 만들었다.

drop database if exists sqldb;

create database sqldb;

use sqldb;

drop database if exists sqldb는 기존에 sqldb가 있으면 삭제한다는 의미다. 실습에서는 데이터를 초기화하고 다시 만들기 위해 사용했다.

회원 테이블은 다음과 같이 만들었다.

create table usertbl (
    userID char(8) not null primary key,
    name varchar(10) not null,
    birthYear int not null,
    addr char(2) not null,
    mobile1 char(3),
    mobile2 char(8),
    height smallint,
    mDate date
);

구매 테이블은 다음과 같이 만들었다.

create table buytbl (
    num int auto_increment not null primary key,
    userID char(8) not null,
    prodName char(6) not null,
    groupName char(4),
    price int not null,
    amount smallint not null,
    foreign key (userID) references usertbl(userID)
);

여기서 새로 중요하게 느껴진 건 auto_increment와 foreign key였다. num은 자동 증가하는 구매 번호라서 데이터를 넣을 때 null을 넣으면 MySQL이 알아서 번호를 부여한다. 그리고 foreign key (userID) references usertbl(userID)는 구매 테이블의 userID가 회원 테이블의 userID를 참조한다는 뜻이다.

즉, 구매 테이블에는 아무 회원 ID나 들어갈 수 있는 것이 아니라, 회원 테이블에 존재하는 회원 ID만 들어가야 한다. 여기서 RDBMS에서 말하는 “관계”가 실제 코드로 보였다.

7. where: 원래 있던 데이터 기준 필터링

조회에서 가장 많이 쓰인 것은 where였다. where는 원본 테이블에 이미 존재하는 값을 기준으로 필터링할 때 사용한다.

select *
from usertbl
where name = '김범수';

이 코드는 이름이 정확히 김범수인 회원만 조회한다. 문자열을 비교할 때는 따옴표를 써야 한다.

select *
from usertbl
where name = `김범수`;

이런 식으로 백틱을 쓰면 안 된다. 백틱은 문자열이 아니라 DB명, 테이블명, 컬럼명 같은 이름을 감쌀 때 사용한다.

서울에 사는 회원도 조회했다.

select *
from usertbl
where addr = '서울';

출생년도처럼 숫자 컬럼은 숫자로 비교하는 게 좋다.

select *
from usertbl
where birthYear = 1973;

MySQL에서는 '1973'처럼 문자열로 써도 동작할 수 있지만, 자료형에 맞게 숫자는 숫자로 비교하는 습관이 필요하다.

8. null은 =로 비교하지 않는다

null 값 조회도 중요했다. 처음에 헷갈리기 쉬운 부분은 null을 =로 비교하면 안 된다는 점이다.

잘못된 방식은 다음과 같다.

select *
from usertbl
where mobile1 = null;

올바른 방식은 is null이다.

select *
from usertbl
where mobile1 is null;

반대로 값이 있는 회원을 찾으려면 is not null을 사용한다.

select *
from usertbl
where mobile1 is not null;

이 부분은 SQL에서 꼭 기억해야 할 것 같다. null은 값이 없는 상태라서 일반적인 값처럼 =로 비교하는 대상이 아니다.

9. 조건을 다양하게 쓰는 방법: and, between, in, like

여러 조건을 동시에 만족해야 할 때는 and를 사용했다.

select name, userID
from usertbl
where birthYear >= 1970
  and height >= 182;

범위 조건은 between으로도 표현할 수 있다.

select name, height
from usertbl
where height between 180 and 183;

between A and B는 A 이상 B 이하이다. 즉, 180과 183도 포함된다. 초과/미만 조건이 필요하면 부등식을 직접 써야 한다.

select name, height
from usertbl
where height > 180
  and height < 183;

여러 값 중 하나를 찾을 때는 in이 깔끔했다.

select *
from usertbl
where addr in ('서울', '경기');

이건 아래 코드와 같은 의미다.

select *
from usertbl
where addr = '서울'
   or addr = '경기';

문자열 유사 검색은 like를 사용했다.

select name, addr
from usertbl
where name like '김%';

%는 글자가 0개 이상 와도 된다는 의미다. 김%는 김으로 시작하는 모든 이름을 찾는다. 반면 _는 정확히 한 글자를 의미한다.

select name, addr
from usertbl
where name like '김_';

이 코드는 김으로 시작하는 두 글자 이름만 찾는다. like는 문자열 패턴 검색에서 자주 쓰일 것 같다.

10. concat()과 별칭

휴대폰 앞자리와 뒷자리를 합쳐서 보여줄 때는 concat()을 사용했다.

select
    userID,
    name,
    concat(mobile1, mobile2) as Mobile
from usertbl
where addr = '서울'
  and mobile1 = '011';

concat(mobile1, mobile2)는 두 컬럼을 이어 붙인다. 결과 컬럼명을 보기 좋게 바꾸고 싶으면 as로 별칭을 붙인다.

select
    userID,
    name,
    concat(mobile1, mobile2) as `연락처`
from usertbl
where addr = '서울'
  and mobile1 = '011';

한글 별칭은 백틱으로 감싸면 안전하다. 여기서 백틱은 문자열용이 아니라 컬럼명 또는 별칭 같은 이름을 감쌀 때 쓴다는 걸 다시 확인했다.

11. 서브쿼리: 조건값을 데이터에서 가져오기

서브쿼리는 쿼리 안에 또 다른 쿼리를 넣는 방식이다. 예를 들어 김경호의 키보다 크거나 같은 회원을 찾고 싶다면 먼저 김경호의 키를 확인해야 한다.

select height
from usertbl
where name = '김경호';

결과가 177이라면 직접 이렇게 쓸 수 있다.

select name, height
from usertbl
where height >= 177;

하지만 이 방식은 값을 직접 확인해서 넣어야 한다. 서브쿼리를 쓰면 조건값을 데이터에서 바로 가져올 수 있다.

select name, height
from usertbl
where height >= (
    select height
    from usertbl
    where name = '김경호'
);

이때 안쪽 쿼리는 비교에 필요한 값 하나만 반환해야 한다.

select name, height
from usertbl
where height >= (
    select name, height
    from usertbl
    where name = '김경호'
);

이렇게 안쪽 쿼리에서 name, height 두 컬럼을 반환하면 에러가 난다. height >= (...)의 오른쪽에는 비교 가능한 값 하나가 와야 하기 때문이다.

서브쿼리 결과가 여러 개일 때는 any, all을 사용해야 했다.

select name, height
from usertbl
where height >= any (
    select height
    from usertbl
    where addr = '경남'
);

any는 여러 결과 중 하나라도 만족하면 된다. 반면 all은 모든 결과를 만족해야 한다.

select name, height
from usertbl
where height >= all (
    select height
    from usertbl
    where addr = '경남'
);

이 부분은 처음엔 조금 헷갈렸지만, 예를 들어 경남 회원의 키가 170, 173이라면 any는 170 이상이면 되고, all은 173 이상이어야 한다고 생각하면 이해가 됐다.

12. 집계 함수와 group by

후반부에서는 집계 함수와 group by를 다뤘다. 집계 함수는 여러 데이터를 계산해서 요약할 때 사용한다.

함수 의미

sum()	합계
avg()	평균
min()	최솟값
max()	최댓값
count()	개수
count(distinct 컬럼)	중복 제거 후 개수

예를 들어 고객별 총 구매 수량을 구하려면 userid별로 묶고, amount를 합산해야 한다.

select userid, sum(amount)
from buytbl
group by userid;

여기서 중요한 점은 group by만 한다고 자동으로 합계가 나오는 게 아니라는 것이다.

select userid, amount
from buytbl
group by userid;

이 코드는 고객별 총 구매 수량이 아니다. amount에 sum() 같은 집계 함수가 없기 때문에 의도한 결과가 아닐 수 있다. 고객별 합계가 필요하면 반드시 sum(amount)를 써야 한다.

별칭을 붙이면 결과가 더 읽기 좋아진다.

select userid, sum(amount) as `총 구매수량`
from buytbl
group by userid;

13. order by, limit으로 정렬과 개수 제한

집계 결과를 정렬할 때는 order by를 사용한다.

select userid, sum(amount) as `총 구매수량`
from buytbl
group by userid
order by `총 구매수량` desc;

이 코드는 고객별 총 구매 수량을 계산한 뒤, 구매 수량이 많은 순서로 정렬한다. desc는 내림차순, asc는 오름차순이다.

상위 몇 개만 보고 싶으면 limit을 붙인다.

select userid, sum(amount) as `총 구매수량`
from buytbl
group by userid
order by `총 구매수량` desc
limit 3;

이렇게 하면 구매 수량 기준 상위 3명만 조회할 수 있다.

14. 고객별 총 구매 금액 계산

구매 금액은 price * amount로 계산할 수 있다.

select userid, price * amount
from buytbl;

고객별 총 구매 금액은 이 값을 sum()으로 묶으면 된다.

select userid, sum(price * amount) as `총구매금액`
from buytbl
group by userid;

정렬까지 하면 우량 고객을 찾을 수 있다.

select userid, sum(price * amount) as `총구매금액`
from buytbl
group by userid
order by `총구매금액` desc;

이 부분이 좋았던 이유는 SQL이 단순 조회만 하는 게 아니라, 계산한 값을 기준으로 고객을 분류하고 정렬할 수 있다는 점이 보였기 때문이다.

15. where와 having 차이

이날 가장 중요하게 남은 개념 중 하나는 where와 having 차이였다.

where  = 원래 테이블에 있던 값을 기준으로 필터링
having = group by 이후 새로 계산한 결과를 기준으로 필터링

예를 들어 price, amount는 원래 buytbl에 있는 컬럼이다. 하지만 sum(price * amount)로 만든 누적금액은 원래 있던 컬럼이 아니라 집계해서 새로 만든 값이다. 그래서 누적금액을 기준으로 필터링하려면 where가 아니라 having을 사용해야 한다.

잘못된 코드:

select userid, sum(price * amount) as `누적금액`
from buytbl
group by userid
where `누적금액` >= 1000;

올바른 코드:

select userid, sum(price * amount) as `누적금액`
from buytbl
group by userid
having `누적금액` >= 1000;

또는 계산식을 그대로 써도 된다.

select userid, sum(price * amount) as `누적금액`
from buytbl
group by userid
having sum(price * amount) >= 1000;

정렬까지 하면 다음과 같다.

select userid, sum(price * amount) as `누적금액`
from buytbl
group by userid
having sum(price * amount) >= 1000
order by `누적금액` asc;

이 부분은 한 줄로 기억하면 될 것 같다.

where는 원래 있던 데이터 필터,
having은 내가 집계해서 만들어낸 결과 필터.

16. SQL 작성 순서와 실행 흐름

SQL은 작성 순서와 실제 실행 흐름이 조금 다르게 느껴졌다. 문법상 작성 순서는 다음과 같다.

select 컬럼명
from 테이블명
where 원본데이터조건
group by 그룹기준
having 그룹결과조건
order by 정렬기준
limit 개수;

실행 흐름으로 보면 대략 이렇게 이해할 수 있다.

from
→ where
→ group by
→ having
→ select
→ order by
→ limit

이 흐름을 이해하니까 where와 having의 차이도 더 잘 보였다. 먼저 from으로 테이블을 잡고, where로 원본 데이터를 필터링한 뒤, group by로 묶고, having으로 집계 결과를 필터링한다. 그 다음 최종적으로 보여줄 컬럼을 선택하고, 정렬하고, 개수를 제한한다.

17. Join 실습용 테이블 만들기

마지막 큰 파트는 Join이었다. test_join DB를 만들고, members, movies 두 테이블을 만들었다.

create database if not exists test_join
default character set utf8mb4
collate utf8mb4_0900_ai_ci;

use test_join;

회원 또는 대여 정보 테이블인 members를 만들었다.

create table members (
    id int not null,
    first_name varchar(45),
    last_name varchar(45),
    movie_id int,
    primary key (id)
);

영화 정보 테이블인 movies도 만들었다.

create table movies (
    id int not null,
    title varchar(45),
    category varchar(45),
    primary key (id)
);

두 테이블의 연결 기준은 다음이었다.

members.movie_id = movies.id

members.movie_id는 회원이 빌린 영화 ID이고, movies.id는 영화 테이블의 영화 ID다. 즉, 두 값을 연결하면 누가 어떤 영화를 빌렸는지 알 수 있다.

18. Cross Join: 모든 조합 만들기

cross join은 두 테이블의 모든 행을 전부 조합한다.

select *
from movies cross join members;

영화가 10개, 회원 데이터가 6개라면 결과는 60개 행이 된다.

10 × 6 = 60

연결 조건이 없기 때문에 가능한 모든 조합이 만들어진다. 실제 분석에서 의도 없이 쓰면 결과가 엄청 커질 수 있어서 조심해야 할 것 같다.

아래처럼 쉼표로 테이블을 나열해도 cross join처럼 동작할 수 있다.

select *
from movies, members;

하지만 조인의 의도를 명확하게 쓰려면 cross join이라고 적는 것이 더 좋아 보인다.

19. Inner Join: 양쪽에 모두 연결되는 데이터만 보기

inner join은 두 테이블에 모두 연결되는 데이터만 보여준다.

select *
from members ME inner join movies M
    on ME.movie_id = M.id;

여기서 on ME.movie_id = M.id가 연결 기준이다. 회원이 빌린 영화 ID와 영화 테이블의 ID가 같은 데이터만 결과로 나온다.

필요한 컬럼만 골라서 볼 수도 있다.

select
    M.title,
    ME.first_name,
    ME.last_name
from members ME inner join movies M
    on ME.movie_id = M.id;

이렇게 하면 영화 제목과 빌린 사람 이름만 확인할 수 있다. 조인에서는 테이블 별칭을 붙이는 습관이 중요했다. members를 ME, movies를 M으로 줄이면 코드가 훨씬 읽기 좋아진다.

20. 컬럼명이 겹치면 테이블명을 붙여야 한다

조인에서 주의할 점은 컬럼명이 겹칠 수 있다는 것이다. members에도 id가 있고, movies에도 id가 있다.

select id, title, first_name, last_name
from members ME inner join movies M
    on ME.movie_id = M.id;

이렇게 쓰면 SQL 입장에서는 어떤 테이블의 id를 말하는지 알 수 없다. 그래서 반드시 별칭을 붙여야 한다.

select
    M.id,
    M.title,
    ME.first_name,
    ME.last_name
from members ME inner join movies M
    on ME.movie_id = M.id;

또는 회원 쪽 ID를 보고 싶으면 이렇게 쓴다.

select
    ME.id,
    M.title,
    ME.first_name,
    ME.last_name
from members ME inner join movies M
    on ME.movie_id = M.id;

조인에서는 테이블별칭.컬럼명 형태를 습관처럼 쓰는 게 좋아 보였다.

21. Left Join과 Right Join

left join은 왼쪽 테이블을 기준으로 전체 데이터를 보여준다.

select
    M.id,
    M.title,
    ME.first_name,
    ME.last_name
from movies M left join members ME
    on M.id = ME.movie_id;

이 코드는 movies를 기준으로 모든 영화를 보여준다. 빌린 사람이 있으면 회원 정보가 같이 나오고, 빌린 사람이 없으면 회원 정보 부분이 null로 나온다.

반대로 right join은 오른쪽 테이블을 기준으로 전체 데이터를 보여준다.

select
    M.id,
    M.title,
    ME.first_name,
    ME.last_name
from members ME right join movies M
    on M.id = ME.movie_id;

이 코드도 기준 테이블은 movies다. movies가 오른쪽에 있기 때문에 오른쪽 테이블 전체를 기준으로 보여준다. 결국 아래 두 코드는 같은 목적을 가질 수 있다.

from movies M left join members ME

from members ME right join movies M

기준이 되는 테이블이 어디에 있느냐에 따라 left 또는 right를 선택하는 구조였다. 개인적으로는 left join이 더 직관적으로 느껴졌다. 기준 테이블을 왼쪽에 두고 생각하면 훨씬 덜 헷갈린다.

22. Join 정리

Join은 여러 테이블에 나뉘어 있는 데이터를 연결해서 보는 방법이다.

Join 종류 의미

cross join	두 테이블의 모든 조합을 만든다
inner join	양쪽 테이블에 모두 연결되는 데이터만 조회한다
left join	왼쪽 테이블을 기준으로 전체 조회한다
right join	오른쪽 테이블을 기준으로 전체 조회한다

이번 Join에서 가장 중요한 기준은 이 코드였다.

on ME.movie_id = M.id

이 조건이 있어야 members와 movies가 의미 있게 연결된다. Join은 그냥 테이블을 붙이는 것이 아니라, 어떤 컬럼을 기준으로 연결할 것인지 정하는 것이 핵심이었다.

마무리

15일차는 SQL을 실제로 많이 만진 날이었다. 테이블을 만들고, 데이터를 넣고, 조건으로 조회하고, 집계하고, 정렬하고, 조인까지 이어졌다. 처음에는 문법이 너무 많아 보였지만, 다시 정리해보면 흐름은 꽤 명확했다.

DB/테이블 생성
→ 데이터 입력
→ 조건 조회
→ 집계와 정렬
→ 여러 테이블 조인

이번에 특히 크게 남은 건 세 가지다. 첫째, where는 원본 데이터 필터이고 having은 집계 결과 필터라는 점. 둘째, group by를 쓸 때는 단순히 묶는 게 아니라 sum, avg, count 같은 집계 함수와 같이 써야 의미가 생긴다는 점. 셋째, Join은 여러 테이블을 연결하는 것이고, 이때 on 조건이 연결의 핵심이라는 점이다.

SQL은 처음엔 문법 암기처럼 느껴졌는데, 실제로는 데이터를 원하는 형태로 꺼내기 위한 사고 방식에 가깝다는 생각이 들었다. 앞으로는 쿼리를 볼 때 단순히 “이 문법이 뭐였지?”보다, 원본 데이터에서 무엇을 필터링하고, 어떤 기준으로 묶고, 어떤 테이블을 연결하려는지를 먼저 봐야겠다.