<코드 프레소 웹 개발 트랙> 처음 시작하는 SQL 프로그래밍 [3]

 

데이터의 조작 ,집계 그리고 테이블 결합 

 

데이터의 변경 

[1] UPDATE 

데이터를 수정하는 SQL 명령어 

• 기존의 입력되어있는 데이터를 수정할 때 사용된다.
• WHERE 절에 명시한 조건에 해당하는 데이터를 변경한다.
  • 명시하지 않으면 모든 ROW의 값이 변경 될 수 있다.

 

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    ...
WHERE condtion;

사용자 데이터 중 ID가 '100000002'인 사용자의 이메일 주소를 변경해라

UPDATE customers SET cust_email = 'support@kidsplace.com' WHERE cust_id = '100000002';

이름이 'Village Toys'인 고객의 우편번호를 '12345'로 변경해라

주문 번호가 20007이고 제품 ID가 'BNBG01'인 주문 아이템의 주문 수량을 50으로 변경

 

 

 

[2] DELETE  

데이터 삭제를 수행하는 SQL명령어

• 기존 데이터를 삭제 할 수 있다.
• WHERE 절에 명시한 조건에 해당하는 데이터를 삭제한다.
  • 명시하지 않으면 해당 테이블의 모든 데이터가 삭제 될 수 있음.
• 지워진 데이터는 복구 어려움 
  • 삭제하기 전 데이터를 백업하는게 중요하다 ( 복구가 어려우니까 ! )
  • 삭제하기 전 동일한 where 조건으로 조회하여 먼저 삭제되는 데이터를 확인한다.

 

DELETE FROM table_name
WHERE condition;

주문 아이템 테이블에서 order_num이 20005이고 order_item이 2인 데이터를 삭제해라

DELETE FROM Orderitems WHERE order_num = 20005 AND order_item = 2;

주문 아이템 테이블에서 order_num이 20005인 데이터 삭제

DELETE FROM Orderitems WHERE order_num = 20005;

 

 

 

데이터의 집계

 

[1] COUNT 함수 

 - 조회 된 데이터의 개수를 계산하는 함수 

 - WHERE 절을 통해 필터링 된 데이터 갯수를 계산하는게 가능하고 Null 값은 카운트 되지 않는다.

 

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

제품의 총 갯수 조회

SELECT COUNT(*) FROM Products;

제품테이블 내 제품 가격 컬럼의 총 데이터 개수 조회

SELECT COUNT(prod_price) FROM Products;

미국에 있는 벤더 개수 조회

SELECT Count(vend_city) FROM Vendors;

이름에 'inch'가 포함된 제품 개수 조회

SELECT Count(prod_name) FROM Products WHERE prod_name LIKE '%inch%';

 

 

[2] SUM 함수

 - 조회 된 데이터의 합을 계산하는 함수

 - WHERE 절을 통해 필터링 된 데이터 합을 계산하고 (*)를 사용할 수 없어 특정 컬럼 명을 명시해야함

 

SELECT SUM(column_name) 
FROM table_name 
WHERE condition;

주문 아이템 데이터에서 전체 구매 수량 계산

SELECT SUM(quantity) FROM OrderItems;

구매 수량이 50에서 100사이의 주문 아이템의 총 가격 계산

SELECT Sum(item_price) From OrderItems WHERE quantity Between 50 and 100

이름이 'doll'로 끝나는 제품의 주문 총 가격 계산

SELECT Sum(prod_price) From Products WHERE prod_name LIKE '%doll';

 

 

 

[3] MIN, MAX 함수

 - MIN : 조회된 데이터에서 특정 컬럼의 "최소값"을 계산하는 함수

 - MAX : 조회된 데이터에서 특정 컬럼의 "최대값"을 계산하는 함수

 

SELECT MIN(column_name)
FROM table_name
WHERE condtion;

주문 아이템 수량의 최대 값 게산

SELECT MAX(quantity) FROM OrderItems;

제품의 ID가 'BNBG02'인 주문 아이템의 최소 수량 계산

수량이 100개 이상인 주문 아이템 중 최대 가격 계산

 

 

 

[4] AVG 함수

 - 조회된 데이터에서 특정 컬럼의 평균 값을 구하는 함수

 

SELECT AVG(column_name)
FROM table_name
WHERE condition;

주문 아이템 수량의 평균을 계산

SELECT AVG(quantity) FROM OrderItems ;

제품 중 이름에 'bean bag'이 들어간 제품들의 평균 가격을 계산

 

 

 

[5] 통계 함수

 - VARIANCE : 조회된 데이터에서 특정 컬럼의 분산 계산

 - STDDEV : 조회된 데이터에서 특정 컬럼의 표준 편차 게산 

 

SELECT VARIANCE[STDDEV] (column_name)
FROM table_name
WHERE condtion;

주문 아이템 수량의 분산을 계산

SELECT VARIANCE(quantity) FROM OrderItems;

주문 아이템 수량의 표준편차를 계산

SELECT STDDEV(quantity) FROM OrderItems;

 

 

[6] GROUP BY 

             : 그룹별 데이터 집계

 - 특정 컬럼들을 기준으로 데이터를 한 개 이상의 그룹으로 분석한다.

 - 집계함수들과 함께 자주 사용한다.

 

SELECT colum_name1, column_name2 ... ,
		Aggregation Functions[집계함수] ...
FROM table_name
GROUP BY column_name1, column_name2 ... ;

주문 아이템을 주문번호로 그룹화 = "DISTINCT와 결과 같음"

SELECT order_num FROM OrderItems GROUP BY order_num;

주문 아이템을 주문번호로 그룹화하여 그룹 별 데이터의 개수 게산

SELECT order_num, COUNT(order_num) FROM OrderItems GROUP BY order_num;

주문 아이템을 주문번호로 그룹화하여, 다양한 통계량 한 눈에 보기

SELECT           order_num,           COUNT(order_num) AS "총 카운트",           SUM(quantity) AS "수량 합계",           AVG(item_price) AS "가격 평균",           MAX(item_price) AS "가격 최대",           MIN(item_price) AS "가격 최소" FROM OrderItems  GROUP BY order_num;

 

 

[7] HAVING

             : 그루핑하여 집계된 결과를 필터링

 - 그룹화 전 필터링은 WHERE , 그룹화 후 필터링은 HAVING ! 

 

SELECT column_name1 column_name2 ... ,
	Aggregation Functions ...
FROM table_name;
GROUP BY column_name1, column_name2 ... ,
HAVING condition;

주문 아이템을 주문번호로 그룹화하여, 그룹 별 카운트 게산 후 카운트가 3이상인 결과만 필터링

SELECT order_num,               COUNT(order_num) FROM OrderItems GROUP BY order_num HAVING COUNT(order_num) >= 3;

제품 데이터를 벤더 아이디 별로 그룹화하여 그룹 별 카운트 게산 후 카운트가 3이상인 데이터만 필터링

 

 

COUNT	SUM	MIN, MAX	AVG
VARIANCE , STDDEV	GROUP BY	HAVING

 

 

 

 

 

 

 

 

 

 

 

SQL 테이블 결합 

SQL JOIN

• 두 개 이상의 테이블을 결합하고 결합하는 방향은 Column을 기반으로 결합함 
• JOIN의 종류로는 다양한 JOIN이 있음
  • INNER , LEFT , RIGHT , FULL OUTER , CROSS , SELF ... 

 

[1] INEER JOIN 

- 두개의 테이블에 모두 존재하는 데이터만 결합하여 조회한다.

- ON 명령어 뒤에 정의한 Column 정보를 기준으로 존재 여부를 체크한다. 

 

SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

INEER JOIN

 

 

사용자

구매정보

사용자ID

사용자 이름

이메일

구매ID

구매제품

사용자ID

00001

코드

a@naver.com

aaaa

java

00001

00002

프레소

b@naver.com

aaab

python

00001

00003

Code

c@naver.com

aaac

c++

00001

INNER JOIN

00001

코드

a@naver.com

aaaa

java

00001

00001

코드

a@naver.com

aaab

python

00001

00001

코드

a@naver.com

aaac

c++

00001

 

고객 테이블과 주문 테이블을 cust_id 값으로 JOIN한다.

 

제품 테이블과 벤더 테이블을 vend_id로 INNER JOIN

 

 

 

 

 

 

 

 

OUTER JOIN 

 

[1] LEFT (OUTER) JOIN

left join

 

• 왼쪽에 위치한  테이블을 기준을 기준으로 오른쪽의 테이블의 데이터를 붙임
  
  • 왼쪽 테이블의 데이터는 "모두 조회됨"
  • 왼쪽 테이블에는 있지만 오른쪽 테이블에 없는 데이터는 NULL

 

SELECT column_name(s)
FROM table1
LEFT JOIN tavble2 ON table1.column_name = table2.column_name;

 

 

 

 LEFT JOIN Example 

사용자					구매정보
사용자ID	사용자 이름	이메일			구매ID	구매제품	사용자ID
00001	코드	a@naver.com			aaaa	java	00001
00002	프레소	b@naver.com			aaab	python	00001
00003	Code	c@naver.com			aaac	c++	00001
LEFT OUTER JOIN
FROM 사용자 LEFT JOIN 구매정보 ON 사용자.사용자ID = 구매정보.사용자ID
사용자별 구매정보
	사용자 ID	사용자이름	이메일		구매ID	구매제품	사용자ID
	00001	코드	a@naver.com		aaaa	java	00001
	00001	코드	a@naver.com		aaab	python	00001
	00001	코드	a@naver.com		aaac	C++	00001
	00002	프레소	b@naver.com		NULL	NULL	NULL
	00003	code	c@naver.com		NULL	NULL	NULL

 

 

 

고객 테이블과 주문 테이블의 cust_id 값으로 JOIN , 단 주문 정보가 존재하지 않는 고객 정보도 함께 조회 

 

벤더 테이블을 기준으로 제품 테이블을 LEFT JOIN 

 

고객 테이블을 기준으로 주문 테이블을 LEFT JOIN

 

주문 아이템 테이블을 기준으로 제품 테이블을 LEFT JOIN

 

 

 

 

 

 

[2] RIGHT (OUTER) JOIN

RIGHT JOIN

 

• LEFT JOIN과 동일하고 기준이 되는 테이블의 방향만 반대
• LEFT JOIN의 기준 테이블을 왼쪽으로 할지, 오른쪽으로 할지 차이이다

 

SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

 

 RIGHT JOIN Example 

 

사용자					구매정보
사용자ID	사용자 이름	이메일			구매ID	구매제품	사용자ID
00001	코드	a@naver.com			aaaa	java	00001
					aaab	python	00002
					aaac	c++	00003
RIGHT OUTER JOIN
FROM 사용자 RIGHT JOIN 구매정보 ON 사용자.사용자ID = 구매정보.사용자ID
사용자별 구매정보
	사용자 ID	사용자이름	이메일		구매ID	구매제품	사용자ID
	00001	코드	a@coldepresso.kr		aaaa	java	00001
	NULL	NULL	NULL		aaab	python	00002
	NULL	NULL	NULL		aaac	c++	00003

 

 

 

 

 

 

---

- 코드프레소  Java  웹  개발  체험단  활동  중    
- 코드프레소  웹개발 트랙의 "처음 시작하는 SQL 프로그래밍" 내용입니다.
- 코드프레소  URL: https://www.codepresso.kr/