본문 바로가기

혼자공부하는 SQL

3. SQL 기본 문법-2

0. 학습 목표

  • 테이블에서 데이터를 추출하는 SELECT 문을 완벽히 이해
  • 여러 건의 데이터를 그룹으로 묶는 방법을 이해
  • 데이터를 입력, 수정, 삭제하는 방법을 익히고 활용

0. 요약

  • USE 문은 데이터베이스를 선택하는 구문
  • SELECT~FROM~WHERE는 기본적인 SQL
  • 관계연산자는 WHERE절에서 크다/작다/같다(<, <=, =, >=, >)등이 있음
  • 논리연산자는 관계 연산자가 2개이상 나오면 AND, OR 등으로 참/거짓을 판별
  • LIKE는 문자열 비교  모두 허용할 때는 %, 하나로 지정할 때는 _을 사용
  • ORDER BY는 결과가 출력되는 순서를 조절, 오름차순은 ASC, 내림차순은 DESC
  • LIMIT는 출력하는 개수를 조절, ORDER BY와 함께 사용
  • DISTINCT는 조회된 결과에서 중복된 항목을 제거
  • GROUP BY는 데이터를 그룹으로 묶어주는 기능
  • HAVING은 집계 함수와 관련된 조건을 제한하며 GROUP BY 뒤에 옴
  • INSERT문은 테이블에 데이터를 입력하는 명령
  • AUTO_INCREMENT는 1부터 증가하는 값을 자동으로 입력, 주의할 점은 PRIMARY KEY로 지정해야 함
  • INSERT INTO ~ SELECT는 다른 테이블의 데이터를 가져와서 한 번에 대량으로 입력
  • UPDATE는 기존에 입력되어 있는 값을 수정하며 주로 WHERE와 함께 사용
  • DELETE는 행 단위로 삭제하며 WHERE절이 없으면 전체 행이 삭제
    용어 약자 설명
    NULL   빈 값
    PRIMARY KEY PK 기본 키
    ALTER TABLE   테이블의 구조를 변형하는 SQL
    시스템 변수   MySQL이 가지고 있는 변수
    @@auto_increment_increment   AUTO_INCREMENT의 증가값을 지정하는 시스템 변수
    DESCRIBE DESC 테이블의 구조를 확인하는 SQL
    TRUNCATE   DELETE와 비슷한 기능이지만 전체 행을 삭제할 때 사용

2. 좀 더 깊게 알아보는 SELECT 문

※핵심 키워드:  ORDER BY, LIMIT, DISTINCT, GROUP BY, HAVING


2-0. 기본 미션

ORDER BY

평균 키(height) 순서대로 정렬해 보자.

SELECT * FROM member ORDER BY height;

# mem_id, mem_name, mem_number, addr, phone1, phone2, height, debut_date
'OMY', '오마이걸', '7', '서울', NULL, NULL, '160', '2015-04-21'
'RED', '레드벨벳', '4', '경북', '054', '55555555', '161', '2014-08-01'
'SPC', '우주소녀', '13', '서울', '02', '88888888', '162', '2016-02-25'
'BLK', '블랙핑크', '4', '경남', '055', '22222222', '163', '2016-08-08'
'APN', '에이핑크', '6', '경기', '031', '77777777', '164', '2011-02-10'
'MMU', '마마무', '4', '전남', '061', '99999999', '165', '2014-06-19'
'WMN', '여자친구', '6', '경기', '031', '33333333', '166', '2015-01-15'
'ITZ', '잇지', '5', '경남', NULL, NULL, '167', '2019-02-12'
'TWC', '트와이스', '9', '서울', '02', '11111111', '167', '2015-10-19'
'GRL', '소녀시대', '8', '서울', '02', '44444444', '168', '2007-08-02'

LIMIT

LIMIT를 통해 5번째부터 2건만 조회해 보자.

SELECT * FROM member LIMIT 5, 2;

# mem_id, mem_name, mem_number, addr, phone1, phone2, height, debut_date
'OMY', '오마이걸', '7', '서울', NULL, NULL, '160', '2015-04-21'
'RED', '레드벨벳', '4', '경북', '054', '55555555', '161', '2014-08-01'

DISTINCT

지역번호(phone1)이 중복된 건을 제거 해보자. 우선 전체 값을 확인하자.

SELECT phone1 FROM member;

# phone1
'031'
'055'
'02'
NULL
'061'
NULL
'054'
'02'
'02'
'031'

중복 제거를 해보자.

SELECT DISTINCT phone1 FROM member;

# phone1
'031'
'055'
'02'
NULL
'061'
'054'

2-1. ORDER BY 절

SELECT 절의 형식은 아래와 같다. 이 중에서 아직 다루지 않은 ORDER BY와 LIMIT에 대해 알아 보자.

SELECT 열_이름
	FROM 테이블_이름
    WHERE 조건식
    GROUP BY 열_이름
    HAVING 조건식
    ORDER BY 열_이름
    LIMIT 숫자

ORDER BY 절은 결과의 값이나 개수에 대해서는 영향을 미치지 않지만, 결과가 출력되는 순서를 조절한다.

데뷔 일자(debut_date)가 빠른 순서대로 출력해 보자.

SELECT mem_id, mem_name, debut_date FROM member ORDER BY debut_date;

# mem_id, mem_name, debut_date
'GRL', '소녀시대', '2007-08-02'
'APN', '에이핑크', '2011-02-10'
'MMU', '마마무', '2014-06-19'
'RED', '레드벨벳', '2014-08-01'
'WMN', '여자친구', '2015-01-15'
'OMY', '오마이걸', '2015-04-21'
'TWC', '트와이스', '2015-10-19'
'SPC', '우주소녀', '2016-02-25'
'BLK', '블랙핑크', '2016-08-08'
'ITZ', '잇지', '2019-02-12'

데뷔 일자(debut_date)를 역순으로 하려면 기본값인 ASC(Ascending)을 DESC(Descending)인 내림차순으로 변경해 주면 된다.

SELECT mem_id, mem_name, debut_date FROM member ORDER BY debut_date DESC;

# mem_id, mem_name, debut_date
'ITZ', '잇지', '2019-02-12'
'BLK', '블랙핑크', '2016-08-08'
'SPC', '우주소녀', '2016-02-25'
'TWC', '트와이스', '2015-10-19'
'OMY', '오마이걸', '2015-04-21'
'WMN', '여자친구', '2015-01-15'
'RED', '레드벨벳', '2014-08-01'
'MMU', '마마무', '2014-06-19'
'APN', '에이핑크', '2011-02-10'
'GRL', '소녀시대', '2007-08-02'

이번에는 평균 키(height)가 164이상인 회원들을 조회해 보자. 조심할 점은 WHERE 절 뒤에 ORDER BY가 와야 한다는 것이다.

SELECT mem_id, mem_name, debut_date, height FROM member WHERE height >= 164 ORDER BY height DESC;

# mem_id, mem_name, debut_date, height
'GRL', '소녀시대', '2007-08-02', '168'
'ITZ', '잇지', '2019-02-12', '167'
'TWC', '트와이스', '2015-10-19', '167'
'WMN', '여자친구', '2015-01-15', '166'
'MMU', '마마무', '2014-06-19', '165'
'APN', '에이핑크', '2011-02-10', '164'

여기서 보면 데뷔 날짜가 정렬되지 않았다 평균키(height)를 기준으로 정렬했기 때문이다. 만약 평균 키는 내림차순, 데뷔일자는 오름차순으로 기준 열을 2개로 정하면 된다.

SELECT mem_id, mem_name, debut_date, height FROM member WHERE height >= 164 ORDER BY height DESC, debut_date ASC;

# mem_id, mem_name, debut_date, height
'GRL', '소녀시대', '2007-08-02', '168'
'TWC', '트와이스', '2015-10-19', '167'
'ITZ', '잇지', '2019-02-12', '167'
'WMN', '여자친구', '2015-01-15', '166'
'MMU', '마마무', '2014-06-19', '165'
'APN', '에이핑크', '2011-02-10', '164'

출력의 개수를 제한: LIMIT

LIMIT는 출력하는 개수를 제한한다. 예를 들어 회원 테이블(member)을 조회하는데 전체 중에서 앞의 3건만 조회해 보자.

SELECT * FROM member LIMIT 3;

# mem_id, mem_name, mem_number, addr, phone1, phone2, height, debut_date
'APN', '에이핑크', '6', '경기', '031', '77777777', '164', '2011-02-10'
'BLK', '블랙핑크', '4', '경남', '055', '22222222', '163', '2016-08-08'
'GRL', '소녀시대', '8', '서울', '02', '44444444', '168', '2007-08-02'

아무런 기준없이 3건만 뽑는 경우는 거의 없으므로 이번에는 데뷔 일자(debut_date)가 빠른 회원 3건만 추출하자. 참고로 LIMIT 형식은 시작, 개수 이다. LIMIT 3의 의미는 LIMIT 0, 3이다.

SELECT mem_name, debut_date FROM member ORDER BY debut_date LIMIT 3;

# mem_name, debut_date
'소녀시대', '2007-08-02'
'에이핑크', '2011-02-10'
'마마무', '2014-06-19'

평균키가 큰 순으로 정렬하되, 3번째 부터 2건만 조회해 보자.

SELECT mem_name, height FROM member ORDER BY height DESC LIMIT 3, 2;

# mem_name, height
'여자친구', '166'
'마마무', '165'

중복된 결과를 제거: DISTICT

DISTICT는 조회된 결과에서 중복된 데이터를 1개만 남긴다. 회원들의 지역을 출력해 보면 중복된 지역이 있는 것을 확인할 수 있다.

SELECT addr FROM member;

# addr
'경기'
'경남'
'서울'
'경남'
'전남'
'서울'
'경북'
'서울'
'서울'
'경기'

순서대로 정리해 보면 중복된 데이터가 좀 더 쉽게 확인 된다.

SELECT addr FROM member ORDER BY addr;

# addr
'경기'
'경기'
'경남'
'경남'
'경북'
'서울'
'서울'
'서울'
'서울'
'전남'

DISTINCT를 이용하여 중복된 값을 제거해 보자.

SELECT DISTINCT addr FROM member;

# addr
'경기'
'경남'
'서울'
'전남'
'경북'

2-2. GROUP BY 절

GROUP BY 절은 그룹으로 묶어주는 역할을 한다. 예를 들어 market_db의 구매 테이블(buy)에서 회원(mem_id)이 구매한 물품의 개수를 조회해 보자.

SELECT mem_id, amount FROM buy ORDER BY mem_id;

# mem_id, amount
'APN', '1'
'APN', '2'
'APN', '1'
'APN', '1'
'BLK', '2'
'BLK', '1'
'BLK', '3'
'GRL', '5'
'MMU', '5'
'MMU', '10'
'MMU', '1'
'MMU', '4'

회원 별(mem_id)로 여러 건의 물건 구매가 있었고 이에 대한 합을 구하는 경우 집계 함수를 사용하면 된다. 집계 함수는 보통 GROUP BY 절과 함께 쓰며 데이터를 그룹화 해주는 기능을 한다.


집계 함수

GROUP BY와 함께 주로 사용되는 집계 함수는 아래 표와 같다.

함수명 설명
SUM() 합계
AVG() 평균
MIN() 최소값
MAX() 최대값
COUNT() 행의 개수
COUNT(DISTINCT) 중복을 제거한 행의 개수

우선 회원 ID(mem_id)별 구매한 개수(amount)를 합쳐서 출력해 보자.

SELECT mem_id, SUM(amount) FROM buy GROUP BY mem_id;

# mem_id, SUM(amount)
'APN', '5'
'BLK', '6'
'GRL', '5'
'MMU', '20'

별칭으로 조금 더 보기 좋게 만들어 보자.

SELECT mem_id "회원_ID", SUM(amount) "총 구매 개수" FROM buy GROUP BY mem_id;

# 회원_ID, 총 구매 개수
'APN', '5'
'BLK', '6'
'GRL', '5'
'MMU', '20'

이번에는 회원이 구매한 금액의 총합을 출력해 보자.

SELECT mem_id "회원_ID", SUM(amount * price) "총 구매 금액" FROM buy GROUP BY mem_id;

# 회원_ID, 총 구매 금액
'APN', '295'
'BLK', '1210'
'GRL', '75'
'MMU', '1950'

이번에는 전체 회원이 구매한 물품 개수(amount)의 평균을 구해 보자.

SELECT AVG(amount) "평균 구매 개수" FROM buy;

# 평균 구매 개수
'3.0000'

이번에는 각 회원이 한 번 구매 시 평균 몇 개를 구매했는지 알아보자.

SELECT mem_id "회원_ID", AVG(amount) FROM buy GROUP BY mem_id;

# 회원_ID, AVG(amount)
'APN', '1.2500'
'BLK', '2.0000'
'GRL', '5.0000'
'MMU', '5.0000'

이번에는 회원 테이블(member)에서 연락처가 있는 회원의 수를 카운트 해보기 위해 우선 전체 행을 카운트 하자.

SELECT COUNT(*) FROM member;

# COUNT(*)
'10'

이제 연락처리 있는 회원의 수만 카운트 해보자.

SELECT COUNT(phone1) "연락처가 있는 회원 수" FROM member;

# 연락처가 있는 회원 수
'8'

HAVING 절

HAVING은 WHERE 절과 비슷하지만 집계 함수에 대해서 조건을 제한하는 것이다.

이에 대한 예제를 위해 우선 앞에서 살펴보았던 회원(mem_id) 별 총 구매액을 구해 보자.

SELECT mem_id "회원_ID", SUM(amount*price) "총 구매 금액" FROM buy GROUP BY mem_id;

# 회원_ID, 총 구매 금액
'APN', '295'
'BLK', '1210'
'GRL', '75'
'MMU', '1950'

그런데 총 구매액이 1000이상인 회원만 구해 보자. 이럴 때 WHERE 절은 집계 함수에 사용할 수 없으므로 HAVING 절을 사용한다.

SELECT mem_id "회원_ID", SUM(amount*price) FROM buy GROUP BY mem_id HAVING SUM(amount*price) >= 1000;

# 회원_ID, SUM(amount*price)
'BLK', '1210'
'MMU', '1950'

구매한 전체 금액이 큰 사용자 부터 나타내려면 ORDER BY를 사용하면 된다.

SELECT mem_id "회원_ID", SUM(amount*price) FROM buy GROUP BY mem_id HAVING SUM(amount*price) >= 1000 ORDER BY SUM(amount*price) DESC;

# 회원_ID, SUM(amount*price)
'MMU', '1950'
'BLK', '1210'

 

'혼자공부하는 SQL' 카테고리의 다른 글

4. SQL 고급 문법-1  (0) 2022.01.24
3. SQL 기본 문법-3  (0) 2022.01.21
3. SQL 기본 문법-1  (0) 2022.01.17
2. 실전용 SQL 미리 맛보기-2  (0) 2022.01.15
2. 실전용 SQL 미리 맛보기-1  (0) 2022.01.14