데이터베이스(SQL) 문법

rekv·2024년 11월 29일
SW CAMP 복습

BEYOND SW CAMP

목록 보기
9/30
일수일자교과목내용편성시간
824/11/29기반기술Database8

실습을 들어가기 전에

mysql에서 제공하는 sample데이터를 Reverse Engineer로 돌리면 ERD를 볼 수 있다.

샘플 데이터를 이용해 미리 주어진 관계를 가지고

SELECT

: 조회할 데이터 지정한다.

기본 SELECT 문법

SELECT 속성, 속성, 속성 FROM 테이블이름;
SELECT * FROM customers;
SELECT * FROM customers;
SELECT customerNumber, customerName, country FROM customers;

특정 조건에 맞는 데이터 조회

SELECT 속성, 속성, 속성, FROM 테이블이름 WHERE 조건;

응용

글자를 비교할 때는 글자를 무조건 ''로 묶어주고 숫자는 그냥 작성
SELECT customerNumber, customerName, country FROM customers WHERE country='USA';

DB에서 다르다는 NOT을 사용
SELECT customerNumber, customerName, country FROM customers WHERE NOT country='USA';

= 같은지 비교, > 큰지 비교, < 작은지 비교, <=, >= 등등이 있을 수 있다.
SELECT customerName, creditLimit FROM customers WHERE creditLimit >= 100000;

여러 개의 조건을 같이 사용하는 것도 가능
SELECT customerName, creditLimit FROM customers WHERE country='USA' AND creditLimit >= 100000;

일부 글자를 포함한 조회

SELECT customerNumber FROM customers WHERE country LIKE '단어';

응용

# a 뒤에 %를 붙이면 a로 시작하는 어떤 나라든 조회
SELECT customerNumber FROM customers WHERE country LIKE 'a%';
# 어디에든 a가 포함되기만 하면 된다면 앞뒤에 붙이기 (알파벳 a가 들어간 모든 나라의 고객 번호 조회)
SELECT customerNumber FROM customers WHERE country LIKE '%a%';

정렬

# ASC(오름차순)은 생략되어있다고 생각,  DESC 내림차순
SELECT customerNumber, country FROM customers ORDER BY country;

특정 수만큼만 조회

LIMIT 데이터의 수 OFFSET 몇 번째부터

응용

SELECT customerNumber, country FROM customers LIMIT 5;
SELECT customerNumber, country FROM customers LIMIT 3 OFFSET 3;

#다음과 같이 축약해서 쓸 수도 있다.
#LIMIT 몇 번째부터, 데이터의 수
SELECT customerNumber, country FROM customers LIMIT 3, 3;

JOIN

한 데이터베이스 내의 여러 테이블을 조합하여 하나의 열로 표현한 것

INNER JOIN [합칠 테이블] ON 조인이 될 조건
SELECT * FROM users
INNER JOIN orders ON users.user_id = orders.order_id

LEFT JOIN, RIGHT JOIN도 사용법이 같되, INNER 부분에 LEFT[RIGHT]를 넣어주면 된다.

SELECT * FROM customers INNER JOIN orders ON customers.customerNumber = orders.customerNumber;

처음부터 join을 생각하지 말고, 우선 테이블 목록을 뽑은 다음 조건을 뒤에 조금씩 붙이기

# customerNumber가 customers와 orders 테이블 둘 다에 있으므로 어느 테이블의 customerNumber인지 테이블이름.속성 으로 지정해주기
SELECT customers.customerNumber, customerName, orderNumber
FROM customers
INNER JOIN orders ON customers.customerNumber = orders.customerNumber;

응용

# 회원 테이블과 주문 테이블에서 주문 상태가 Disputed인 회원의 회원 번호, 이름, 주문 번호, 주문 상태를 조회
SELECT customers.customerNumber, customerName, orderNumber, status
FROM customers 
INNER JOIN orders ON customers.customerNumber = orders.customerNumber
WHERE status='Disputed';
# LEFT JOIN으로 붙이기
SELECT customers.customerNumber, customerName, orderNumber, status FROM customers 
LEFT JOIN orders ON customers.customerNumber = orders.customerNumber
WHERE status='Disputed';

실습

# 런던에서 주문한 고객들의 주문 번호와 고객 이름 조회
# customers의 city, orders의 ordernumber, customers의 customerName
SELECT orders.orderNumber, customers.customerName FROM customers
INNER JOIN orders ON customers.customerNumber = orders.customerNumber
where customers.city='London';

주문한 제품의 수량이 35개 이상인 주문의 주문 번호와 고객 이름
SELECT orders.orderNumber, customers.customerName from customers
INNER JOIN orders on customers.customerNumber = orders.customerNumber
INNER JOIN orderdetails on orderdetails.orderNumber = orderdetails.orderNumber
WHERE orderdetails.quantityOrdered >= 35;

#territory가 apac에서 근무하는 직원이 담당하는 고객의 이름과 고객 번호 조회
SELECT customers.customerName, customers.customerNumber from customers
INNER JOIN employees on employees.employeeNumber = customers.salesRepEmployeeNumber
INNER JOIN offices on employees.officeCode = offices.officeCode
WHERE offices.territory='APAC';
![](https://velog.velcdn.com/images/bdt6246/post/7b3ad495-6220-41d1-9d17-4c691eae5c85/image.png)

#주문 상태가 Shipped인 주문에 포함된 제품의 이름과 주문한 고객의 이름 조회 
SELECT products.productName, customers.customerName from customers
INNER JOIN orders on customers.customerNumber = orders.customerNumber
INNER JOIN orderdetails on orders.orderNumber = orderdetails.orderNumber
INNER JOIN products on products.productCode = orderdetails.productCode
where orders.status='Shipped';

UNION

여러 개의 SELECT문의 결과를 한 번에 표헌할 때 사용
각각의 SELECT문으로 선택된 속성의 개수와 타입, 순서가 모두 같아야 한다.
중복된 값은 제거하고 보여준다.

UNION ALL
중복된 값까지 모두 보려면 UNION ALL을 사용

SELECT A AS one, B AS two 
FROM TABLE_A
UNION (또는 UNION ALL)
SELECT C AS one, D AS two 
FROM TABLE_B

JOIN과의 차이점

JOIN : 새로운 열로 결합한다. (수평결합)
UNION : 새로운 행으로 결합한다. (수직결합)

join 결과 (예시는 inner join)

union 결과

위와 같이, join은 옆에 붙는다면 union은 아래에 붙는다.

서브쿼리

SELECT, INSERT, UPDATE, DELETE, SET, DO 문을 사용할 때 해당 쿼리 내부에 또다른 쿼리를 사용하는 것

SELECT a, b FROM T1 WHERE a IN (SELECT a FROM T2 WHERE b=2)

*가급적 서브쿼리는 안 쓰는 게 좋다.

내장함수

일정한 기능을 수행하는 내용을 미리 만들어 둔 것
사용자는 일정한 기능을 수행하고 싶을 때 함수를 사용하기만 하면 된다.

  • 집계 함수 : SUM, COUNT, AVG
  • 문자 처리 내장 함수 : RIGHT, SUBSTRING_INDEX, UPPER
  • 날짜 및 시간 처리 내장 함수 : NOW, CURDATE, CURTIME

응용

SELECT SUM(quantityOrdered) FROM orderdetails;

# 총 주문받은 횟수
SELECT COUNT(orderNumber) FROM orders;

group by

#도시 별 고객의 수
SELECT city, COUNT(customerNumber) FROM customers
GROUP BY city;

중복 제거 DISTINCT

도시별로 주문한 고객들의 수와 도시 이름을 나타내려고 한다.

SELECT customers.city, count(customers.customerNumber) from customers
inner join orders
on orders.customerNumber = customers.customerNumber
group by customers.city;
SELECT customers.city, count(distinct customers.customerNumber) from customers
inner join orders
on orders.customerNumber = customers.customerNumber
group by customers.city;

이때, 첫 번째와 같이 코드를 짠다면 같은 제품을 여러 번 구매한 고객의 수가 중복되어 집계된다. 이때 중복되는 값을 distinct 함수를 통해 제거할 수 있다.

distinct 하나로 값이 달라진 것을 볼 수 있다.

그 외에도 다양한 내장함수가 존재한다.

실습

# 주문한 제품별 제품의 이름과 고객의 수
select products.productName, count(distinct customers.customerNumber) from products
inner join orderdetails on products.productCode = orderdetails.productCode
inner join orders on orders.orderNumber = orderdetails.orderNumber
inner join customers on customers.customerNumber = orders.customerNumber
group by products.productName;

# territory별 근무하는 직원이 담당하는 고객의 수
SELECT territory, count(customers.customerNumber) from customers
INNER JOIN employees on employees.employeeNumber = customers.salesRepEmployeeNumber
INNER JOIN offices on employees.officeCode = offices.officeCode
group by offices.territory;

HAVING

집계함수로 뽑은 결과에 조건을 넣고싶을 때 사용
having은 늘 group by와 함께 한다.

# territory별 근무하는 직원 중 담당하는 고객의 수가 30명인 직원
SELECT territory, count(customers.customerNumber) from customers
INNER JOIN employees on employees.employeeNumber = customers.salesRepEmployeeNumber
INNER JOIN offices on employees.officeCode = offices.officeCode
group by offices.territory
HAVING COUNT(customers.customerNumber) > 30;

코딩테스트 문제 풀면서 실습하기

조건에 부합하는 중고거래 댓글 조회
https://school.programmers.co.kr/learn/courses/30/lessons/164673

오랜 기간 보호한 동물
https://school.programmers.co.kr/learn/courses/30/lessons/59044

보호소에서 중성화한 동물
https://school.programmers.co.kr/learn/courses/30/lessons/59045

있었는데요 없었습니다
https://school.programmers.co.kr/learn/courses/30/lessons/59043

없어진 기록 찾기
https://school.programmers.co.kr/learn/courses/30/lessons/59042

카테고리 별 도서 판매량 집계하기 - 나중에 다시 풀어볼 것
https://school.programmers.co.kr/learn/courses/30/lessons/144855

마지막 문제는 잠깐 짚고 넘어가자

문제 설명
다음은 어느 한 서점에서 판매중인 도서들의 도서 정보(BOOK), 판매 정보(BOOK_SALES) 테이블입니다.

BOOK 테이블은 각 도서의 정보를 담은 테이블로 아래와 같은 구조로 되어있습니다.

Column nameTypeNullableDescription
BOOK_IDINTEGERFALSE도서 ID
CATEGORYVARCHAR(N)FALSE카테고리 (경제, 인문, 소설, 생활, 기술)
AUTHOR_IDINTEGERFALSE저자 ID
PRICEINTEGERFALSE판매가 (원)
PUBLISHED_DATEDATEFALSE출판일

BOOK_SALES 테이블은 각 도서의 날짜 별 판매량 정보를 담은 테이블로 아래와 같은 구조로 되어있습니다.

Column nameTypeNullableDescription
BOOK_IDINTEGERFALSE도서 ID
SALES_DATEDATEFALSE판매일
SALESINTEGERFALSE판매량

문제
2022년 1월의 카테고리 별 도서 판매량을 합산하고, 카테고리(CATEGORY), 총 판매량(TOTAL_SALES) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 카테고리명을 기준으로 오름차순 정렬해주세요.

예시
예를 들어 BOOK 테이블과 BOOK_SALES 테이블이 다음과 같다면

BOOK_IDCATEGORYAUTHOR_IDPRICEPUBLISHED_DATE
1인문1100002020-01-01
2경제190002021-02-05
3경제290002021-03-11
BOOK_IDSALES_DATESALES
12022-01-012
22022-01-023
12022-01-051
22022-01-205
22022-01-216
32022-01-222
22022-02-113

2022년 1월의 도서 별 총 판매량은 도서 ID 가 1 인 도서가 총 3권, 도서 ID 가 2 인 도서가 총 14권 이고, 도서 ID 가 3 인 도서가 총 2권 입니다.

카테고리 별로 판매량을 집계한 결과는 다음과 같습니다.

CATEGORYTOTAL_SALES
인문3
경제16

카테고리명을 오름차순으로 정렬하면 다음과 같이 나와야 합니다.

CATEGORYTOTAL_SALES
경제16
인문3

풀이
한 번에 풀려고 하기보다는 조금씩 정보를 쪼개서 완성해 나간다.

  1. USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 우선 뽑기
SELECT * FROM USER_INFO
INNER JOIN ONLINE_SALE ON USER_INFO.USER_ID = ONLINE_SALE.USER_INFO
WHERE JOINED LIKE '2021-%'
  1. SELECT 문에서 *을 뽑아줄 결과로 바꾸고, 그룹 바이로 묶어주기
SELECT
    DATE_FORMAT(ONLINE_SALE.SALES_DATE, '%Y') AS 'YEAR',
    DATE_FORMAT(ONLINE_SALE.SALES_DATE, '%m') AS 'MONTH',
    #WHERE절로 2021년에 가입한 회원만 뽑았음을 생각하자
    COUNT(DISTINCT U.USER_ID) AS PURCHASED_USERS, #2021년에 가입한 회원 중 상품을 구매한 회원 수

    
FROM USER_INFO
INNER JOIN ONLINE_SALE ON USER_INFO.USER_ID = ONLINE_SALE.USER_ID
WHERE JOINED LIKE '2021-%'
GROUP BY YEAR, MONTH
  1. (구매한 사람 수 / 전체 사람 수)
    새로운 쿼리 만들어서 SELECT문에 넣어준다.
# 2021년에 가입한 전체 회원의 수 
SELECT COUNT(USER_INFO.USER_ID)
FROM USER_INFO WHERE JOINED LIKE '2021-%'
  1. 상품을 구매한 회원의 비율은 소수점 두 번째 자리에서 반올림을 하라고 했으므로 ROUND()함수로 계산할 값을 감싼다.
    또한 전체 결과는 년과 월을 기준으로 오름차순으로 정렬하라고 했으므로 ORDER BY를 추가해서 마무리한다.
SELECT
    DATE_FORMAT(O.SALES_DATE, '%Y') AS YEAR,
    DATE_FORMAT(O.SALES_DATE, '%c') AS MONTH,
    COUNT(DISTINCT U.USER_ID) AS PURCHASED_USERS,
    ROUND((COUNT(DISTINCT U.USER_ID))/(SELECT COUNT(USER_INFO.USER_ID) FROM USER_INFO WHERE JOINED LIKE '2021%'),1) AS PUCHASED_RATIO
FROM USER_INFO AS U
INNER JOIN ONLINE_SALE AS O
ON U.USER_ID = O.USER_ID
WHERE JOINED LIKE '2021-%'
GROUP BY YEAR, MONTH
ORDER BY YEAR ASC, DATE_FORMAT(O.SALES_DATE, '%m')
profile
rekv
이전 포스트

데이터베이스 서버와 WordPress

다음 포스트

BEYOND SW 캠프 12기 2주차 회고

0개의 댓글