[코드프레소] 웹개발 트랙 체험단 - 처음 시작하는 SQL 프로그래밍 2

05강. SQL 명령어를 이용한 데이터의 조회 Part 2

ORDER BY 명령어를 이용한 데이터의 정렬

데이터 정렬

특정 컬럼을 기준으로 내림차순 또는 오름차순으로 정렬 가능

ORDER BY 명령어 사용

1개 이상의 정렬 조건을 조합 가능하다.

ASC(오름차순,) DESC(내림차순) 명시 가능, 오름차순이 디폴트 값이다.

 

 

 

# 제품 데이터를 가격 오름차순으로 정렬
select *
from products
order by prod_price; # 디폴트 - 오름차순 정렬

# 제품 데이터를 가격 내림차순으로 정렬
select *
from products
order by prod_price DESC;

# 주문 아이템을 아이템 가격 내림차순으로 정렬
select *
from orderitems
order by item_price desc;

# 주문 아이템을 1 차 수량 기준 내림차순, 2차 아이템 기준 오름차순으로 정렬
# 수량 기준으로 먼저 정렬되고, 그 후에 아이템 기준으로 다시 정렬된다.
select *
from orderitems
order by quantity desc, order_item;

 

데이터 정렬과 필터링

조건을 명시하여 조회한 결과를 정렬

WHERE절과 ORDER BY 절을 결합하여 사용한다.

정렬하는 절은 테이블 안에만 존재하면 된다.

셀렉트에 포함되지 않아도 된다는 뜻!

 

# 주문번호가 20007 이상인 주문 데이터를 주문번호 내림차순 정렬
select *
from orders
where order_num >= 20007 #웨어절을 통해 데이터를 선택해서
order by order_num desc; #선택한 데이터를 정렬해준다.

# 수량이 50 이하인 주문아이템 데이터를 가격 오름차순으로 정렬
select *
from orderitems
where quantity <= 50
order by item_price;

# 제품 아이디가 'BNBG01'인 주문 아이템을 가격 내림차순으로 정렬
select *
from products
where prod_id = 'BNBG01'
order by prod_price desc;

 

LIMIT, OFFSET 명령어를 이용한 조회 데이터의 제한

Top N 데이터 조회 - LIMIT, OFFSET

LIMIT은 조회된 결과 값의 개수를 제한하는데 사용한다.

OFFSET은 LIMIT과 함께 사용되며 페이지 처리를 하는데 사용한다.

(페이징 처리 - 웹 상에서 데이터를 쪼개서 보여주는 것)

LIMIT은 주로 ORDER BY 절과 같이 사용, 정렬 후 상위 N개의 결과만을 확인할 수 있음

OFFSET으로 데이터가 선택되는 위치를 정할 수 있다.

 

### Top N 데이터 조회 연습
# 제품의 가격 순 하위 1개 제품 조회
select *
from products
order by prod_price 
limit 1;

# 제품의 가격 순 하위 4,5번째 제품 조회
select *
from products
order by prod_price 
limit 2 offset 3; #오프셋을 통해 리밋을 시작할 데이터 위치를 정할 수 있다.

# 주문아이템 수량 순 상위 5개 항목 조회 - 내림차순 정렬, 수량 많은 순
select *
from orderitems
order by quantity desc
limit 5;

# 가격이 3.49인 제품 1건 조회
select *
from products
where prod_price = 3.49 
limit 1;

# 가격이 6 이하인 주문아이템 중 수량 상위 2건 조회
select *
from orderitems
where item_price <= 6 
order by quantity desc #내림차순으로 정렬, 수량 많은 순
limit 2;

 

IN 연산자를 이용한 데이터 필터링

IN 연산자

다중 OR 연산자의 축약 버전

### IN 연산자 연습
# 벤더 아이디가 'BRS01'이거나 'DLL01'인 벤더 조회
select *
from vendors
where vend_id IN ('BRS01', 'DLL01');

# 이름이 'Village Toys' 이거나 'Fun4All' 이거나 'The Toy Store'인 고객 조회
select *
from customers
where cust_name IN ('Village Toys', 'Fun4All', 'The Toy Store');

 

BETWEEN 연산자를 이용한 데이터의 필터링

BETWEEN 연산자

주어진 범위에 대한 조건에 해당하는 데이터 조회

숫자, 날짜, 문자형 데이터에 모두 사용 가능

주어진 범위까지 모두 포함

 

### BETWEEN 연산자 연습
# 가격이 3$ ~ 6$인 제품 조회
select *
from products
where prod_price between 3 and 6;

# 수량이 50 ~ 300인 주문 아이템 조회
select *
from orderitems
where quantity between 50 and 300;

# 고객 아이디가 '1000000002' ~ '1000000004'인 고객 조회
# 문자열을 비교할 때에는 앞의 문자열부터 큰지 작은지 비교해가면서 조회된다.
# cust_id는 문자열로 구성된 데이터이다.
select *
from customers
where cust_id between '1000000002' and '1000000004';

 

LIKE 연산자를 이용한 데이터의 필터링

LIKE 연산자

문자열 안에서 특정 패턴을 검색하기 위해 사용

= 연산자가 문자열이 완전히 일치하는 조건인 반면,

LIKE 연산자는 문자열이 부분적으로 일치하는 조건을 검색할 때 사용한다.

 

### LIKE 연산자 연습
# cust_contact가 알파벳 'J'로 시작하는 고객 조회
select *
from customers
where cust_contact like 'J%';

# 이름이 'Inc.'로 끝나는 벤더 조회
select *
from vendors
where vend_name like '%Inc.';

# 이름에 'teddy'가 포함된 제품 조회
select *
from products
where prod_name like '%teddy%';

# 이름이 'Bird'로 시작하고 'toy'로 끝나는 제품 조회
select *
from products
where prod_name like 'Bird%toy';

# 제품 설명에 'inch'가 포함된 제품 조회
select *
from products
where prod_desc like '%inch%';

# 제품 설명에 'inch'와 'bear'가 포함된 제품 조회
select *
from products
where prod_desc like '%inch%'
and prod_desc like '%bear%'; #두개의 조건을 따로 적어주기 위해 and를 사용한다.

 

DISTINCT 명령어를 이용한 중복 데이터 제거

SELECT DISTINCT 문

특정 컬럼의 유니크한 값들을 조회할 때 사용

엑셀의 중복 값 제거 기능 실행후 결과와 동일

다수의 컬럼을 명시할 수 있지만 자주 사용되지 않는다.

 

### DISTINCT 문 연습
# 제품의 unique 한 가격 목록 조회
select distinct prod_price
from products; #중복된 가격을 제외하고 유니크한 값을 찾아서 조회한다.

# 주문 아이템 중 어떤 제품이 포함되어 있는지 유니크한 제품 id 조회
select distinct prod_id
from orderitems;

# 주문 아이템 중 아이템 가격의 유니크한 목록 조회
select distinct item_price
from orderitems;

 

누락(Null Value) 데이터의 처리

Null Value 처리

널 벨류란 아무것도 값이 없는 상태를 의미

IS NULL, IS NOT NULL 연산자로 null이거나 null이 아닌 데이터만 조회할 수 있다.

 

### Null 데이터 처리 연습
# 고객 데이터 중 이메일 정보가 null인 고객 조회
select *
from customers
where cust_email is null;

# 고객 데이터 중 이메일 정보가 null이 아닌 고객 조회
select *
from customers
where cust_email is not null;

# 벤더 데이터 중 vend_city 정보가 'London'이고 vent_state 정보가 null인 벤더 조회
select *
from vendors
where vend_city = 'London'
and vend_state is null;

 

AS 명령어를 이용한 데이터의 별칭

AS

Alias 별칭을 의미

컬럼 또는 테이블에 별칭을 부여해서 조회 가능하다.

컬럼 명을 이해하기 쉽게 만들기 위해서 사용한다.

테이블 명을 짧게 만들기 위해 사용

테이블이 여러개라면 어떤 테이블에서 컬럼이 온건지 명시해야 하지 때문에 테이블 명을 명시해주어야 한다.

 

### AS 명령어 연습
# vend_name을 VendorName으로 변경하여 벤더의 이름 정보 조회
select vend_name as VendorName #컬럼의 이름이 변경되어 조회된다.
from vendors;

# cust_zip을 ZipCodeOfCustomer로 변경하여 고객의 zip code 정보 조회
select cust_zip as ZipCodeOfCustomer
from customers;

# vend_city, vend_state를 각각 VendorCity, VendorState로 변경하여 벤더의 도시 정보와 주 정보 조회
select vend_city as VendorCity, vend_state as VendorState
from vendors;

 

 

---

 

06강. SQL 명령어를 이용한 데이터의 변경

UPDATE 명령어를 이용한 데이터 수정

UPDATE

기존의 데이터를 수정시 사용

WHERE절에 명시한 조건에 해당하는 데이터를 변경한다.

• 웨어절에 명시하지 않으면 모든 ROW의 값이 변경될 수 있음

### UPDATE 연습
# 데이터를 수정하고 변경하는 작업은 민감한 작업이다.
# 데이터를 변경하면 바로 변경되지 않고, 메모리에 가지고 있다가 커밋이 되면 처리를 한다.
# 변경 -> 메모리 -> 파일로 저장(commit) 의 단계를 거친다.
# 워크벤치는 메모리를 변경하면 바로 커밋이 된다. - auto commit 설정되어 있음

select @@autocommit; #오토 커밋의 값 조회 
# 1로 설정되어있으면 On, 0이면 Off 상태이다.
set @@autocommit=0; #실습을 위해 0으로 만들어두고, rollback을 진행하자.

# ID가 'BNBG03'인 상품의 판매단가를 4.0$로 변경
update products
set prod_price = 4.0
where prod_id = 'BNBG03';

rollback; #업데이트 된 내용을 취소하기 위한 롤백 

select *
from products
where prod_id = 'BNBG03'; #업데이트가 잘 됐는지 확인하기 위한 셀렉트문

# 고객 ID가 '100000001'인 고객의 우편번호를 '12345'로 변경
update customers
set cust_zip = 12345
where cust_id = '1000000001';

select *
from customers
where cust_id = '1000000001';

# 주문 번호가 20007이고 제품 ID가 'BNBG01'인 주문 아이템의 주문 수량을 50으로 변경
update orderitems
set quantity = 50
where order_num = 20007 and prod_id = 'BNBG01';

select *
from orderitems
where order_num = 20007 and prod_id = 'BNBG01';

 

 

DELETE 명령어를 이용한 데이터 삭제

DELETE

기존 데이터를 삭제할 수 있음

WHERE 절에 명시한 조건에 해당하는 데이터를 삭제

*명시하지 않으면 해당 테이블의 모든 데이터가 삭제될 수 있음

지워진 데이터는 복구 어려움!

 

DELETE Tip

삭제 전 데이터를 백업

삭제 전 동일한 where 조건으로 조회해서 삭제되는 데이터 확인하기

 

### DELETE 연습
# 원활한 실습을 위해 오토커밋을 껐다가 롤백해주자

# 주문 아이템 테이블에서 order_num이 20005이고 order_item이 1인 데이터 삭제
delete from orderitems
where order_num = 20005 and order_item = 1;

select *
from orderitems
where order_num = 20005 and order_item = 1; #처리 여부를 확인하기 위한 조회절

rollback;

# 주문 테이블에서 order_num이 20005인 데이터 삭제
select *
from orders
where order_num = 20005;

delete from orders
where order_num = 20005;

# 외래키 제약조건에 위배된다고 하면서 에러가 나면서 데이터가 삭제되지 않음!
# 이유 : orders와 orderitmes는 서로 관계있는 테이블이다.
# 구매는 하나이지만 여러개의 물건을 구매 할 수 도 있음.
# 해당 데이터는 orderitems에 들어있다.
# 즉, order 의 order_num과 orderitems 의 order_num은 같은 데이터이다.
# 이는 orderitem에서 order의 데이터를 참조하는 형태이다. (Foreign Key)
# 이러한 관계성이 있기 때문에 데이터를 삭제할 수 없는 것이다.

# 이 구문을 실행하기 위해서는 자식 테이블인 orderitems 테이블에서 order_num = 20005인 데이터를 삭제해주어야 한다.
# 그 다음에 실행하면 잘 지워지는 걸 확인할 수 있음

rollback;

 

 

---

 

07강. SQL 명령어를 이용한 데이터의 집계

COUNT 함수를 이용한 데이터 개수 집계

count 함수

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

WHERE절을 통해 필터링 된 데이터의 개수를 계산 가능

null value는 카운트 되지 않는다.

 

 

SUM 함수를 이용한 데이터의 합 집계

SUM 함수

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

WHERE절을 통해 필터링 된 데이터의 합을 계산 가능

*를 사용할 수 없고, 특정 컬럼 명을 명시해줘야 한다.

(특정 컬럼의 합을 계산하기 때문에)

 

 

MIN, MAX 함수를 이용한 최대값, 최소값 집계

MIN, MAX 함수

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

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

WHERE절을 통해 필터링 된 데이터의 최대갑, 최소값을 계산 할 수 있다.

 

 

AVG 함수를 이용한 평균값 집계

AVG 함수

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

WHERE절을 통해 필터링 된 데이터에서 특정 컬럼의 평균값을 계산 가능

 

 

VARIANCE, STDDEV 함수를 이용한 분산, 표준편차 집계

VARIANCE, STDDEV 함수

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

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

분산과 표준편차는 데이터들이 평균에서 벗어나 있는 정도를 측정하는 지표이다.

 

 

GROUP BY 명령어를 이용한 그룹별 데이터 집계

GROUP BY

특정 컬럼들을 기준으로 데이터를 그룹 지어 분석한다.

한 개 이상의 컬럼의로 그룹화 가능.

대부분 집계 함수들과 함께 자주 사용된다.

 

그룹바이 뒤에 그룹으로 묶을 여러개의 컬럼을 명시한다.

오더아이템즈의 오더_num 은 여러개의 값일 수 있다.

그룹바이를 쓰면 중복된 데이터를 삭제하고 유니크한 값만 남게 가능

 

하나의 오더 넘버에 3건의 데이터가 있다면, count로 그 값을 세 수 있고

오더 넘을 그룹바이로 묶어서 사용할 수 있다.

 

그룹바이를 통해 고유한 오더 넘버만 조회된다.

그룹을 통해서 다양한 집계 함수를 묶어놓은 하나의 그룹에 적용할 수 있게 된다.

 

 

HAVING 명령어를 이용한 집계 데이터 필터링

HAVING

그룹화 한 결과를 필터링

그룹화 전 필터링은 WHERE, 그룹화 후 필터링은 HAVING을 사용한다.

(여러건의 집계된 데이터를 피터링 할 때)

 

 

08강. SQL 명령어를 이용한 데이터의 결합

INNER JOIN 명령어를 이용한 데이터의 Column 결합

SQL JOIN

두 개 이상의 테이블을 결합하여 컬럼을 확장.

 

SQL JOIN

INNER JOIN

LEFT (OUTER) JOIN

RIGHT (OUTER) JOIN

FULL OUTER JOIN

CROSS JOIN

SELF JOIN

등이 있다

 

INNER JOIN

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

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

 

OUTER JOIN 명령어를 이용한 데이터의 Column 결합

LEFT (OUTER) JOIN

왼쪽에 위치한 테이블을 기준으로 오른쪽 테이블의 데이터를 붙인다.

왼쪽 테이블의 데이터는 모두 조회된다.

왼쪽 테이블에는 있지만 오른쪽 테이블에 없는 데이터는 NULL 처리

( FROM 뒤에 적히는 테이블이 왼쪽이 되고, LEFT JOIN 뒤에 적히는 테이블이 오른쪽 테이블이 된다. 즉, JOIN 명령어를 기준으로 왼쪽 오른쪽이 나뉜다! )

왼쪽에만 데이터가 있고, 오른쪽에는 데이터가 없어도 NULL로 표현이 되어 나온다.

다만 오른쪽 테이블에는 있지만 왼쪽테이블에는 없다면 조회되지 않는다.

 

RIGHT (OUTER) JOIN

테이블의 순서만 바뀜.

LEFT JOIN과 동일하고 기준이 되는 테이블의 방향만 반대가 된다.

 

 

---

 

https://www.codepresso.kr/

노션 정리본 : https://rapid-run-744.notion.site/Java-6dca205b818c4dc5ae28160dbd7b6d65

코드프레소 : Java 웹 개발 트랙 체험단