33일차 - SQL index, FOREIGN KEY, INNER JOIN, SubQuery

Date : 2023-08-03

Topic : MySQL index, FOREIGN KEY, INNER JOIN, SubQuery

---

Note

INDEX

• 테이블 동작 속도를 높여주는 자료 구조
• 인덱스: 키-필드 구조 (나머지 세부 테이블 컬럼 정보는 가지고 있지 않음)
  • 클러스터형 인덱스: 영어 사전 같은 형태(데이터순 정렬) : 테이블에서 PK로 정의한 컬럼이 있을 경우 자동 생성
  • 보조 인덱스: 일반 책 뒤에 있는 <찾아보기> 같은 형태

 

- 실습: userTbl 테이블 생성

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
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- 생략 가능

• DEFAULT CHARSET=utf8 COLLATE=utf8_bin
  : 문자셋 인코딩(한글 데이터 정확한 처리)
  • Character set: 문자가 저장될 때 어떤 코드로 저장될지에 대한 규칙의 집합
  • Collation: 문자들을 서로 비교하기 위해 사용하는 규칙 집합(문자 비교 검색, 정렬 등)

인덱스 확인

SHOW INDEX FROM userTbl

index check

• Key_name이 P.K로 된 것은 클러스터형 인덱스를 의미

참고
- Table: The name of the table.
- Non_unique: 0 if the index cannot contain duplicates, 1 if it can.
- Key_name: The name of the index. If the index is the primary key, the name is always PRIMARY.
- Seq_in_index: The column sequence number in the index, starting with 1.
- Column_name: The column name.
- Collation: How the column is sorted in the index. This can have values A (ascending) or NULL (notsorted).
- Cardinality: An estimate of the number of unique values in the index.
- Index_type: The index method used (BTREE, FULLTEXT, HASH, RTREE).

 

• buyTbl 구조

CREATE TABLE buyTbl (
num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
userID CHAR(8) NOT NULL,
prodName CHAR(4),
groupName CHAR(4),
price INT NOT NULL,
amount SMALLINT NOT NULL,
FOREIGN KEY (userID) REFERENCES userTbl(userID)
);

• Key_name 이 PRIMARY 가 아닌 것은 보조 인덱스를 의미
• foreign key로 설정된 컬럼이 인덱스가 없다면, 인덱스를 자동 생성

- 참고: 테이블 변경

ALTER TABLE userTbl ADD CONSTRAINT TESTDate UNIQUE(mDate);
# 특정 컬럼에 duplicate값이 나오지 않도록 unique 제약조건 추가

UNIQUE 제약을 넣으면, 보조 테이블이 만들어짐

- 인덱스 생성 및 삭제 (필요에 따라)

CREATE INDEX idx_name ON userTbl (name); -- CREATE INDEX를 이용한 인덱스 추가
ALTER TABLE userTbl ADD INDEX idx_addr (addr); -- ALTER TABLE을 이용한 인덱스 추가

똑같은 추가 기능

- 테이블 생성 시 인덱스 추가

UNIQUE INDEX idx_userTbl_name (name), -- 항상 유일해야 함
INDEX idx_userTbl_addr (addr)

• UNIQUE INDEX idx_uerTbl_name (name) : name 컬럼에 대해 idx_userTbl_name 이름으로 인덱스 생성, name 은 UNIQUE 제약조건 필요
• INDEX idx_userTbl_addr (addr) : addr 컬럼에 대해 idx_userTbl_addr 이름으로 인덱스 생성

 

- 인덱스 삭제

ALTER TABLE userTbl DROP INDEX idx_userTbl_name -- idx_userTbl_name 인덱스 삭제

---

외래키(FOREIGN KEY)

buyTbl.userID 컬럼을, userTbl.userID에서 참조할 때

INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('STJ', '운동화', '의류', 30, 2);

userTbl에 userID가 STJ인 데이터가 없을 경우 입력이 안된다.

👉데이터 무결성 (두 테이블간, 데이터의 정확성을 보장하는 제약 조건)

(현업에서는 비즈니스 로직이 다양하므로 제약을 걸어놓을 경우 예외적인 비즈니스 로직 처리가 어렵기 때문에, 꼭 필요한 경우만 사용한다.)

---

HAVING

: 집계함수를 가지고 조건비교, GROUP BY절과 함께 사용

SELECT provider, COUNT(*)
FROM items
WHERE provider != '스마일배송'	-- 스마일배송은 제외
GROUP BY provider		-- 판매처별로 그룹
HAVING COUNT(*) >= 100		-- 베스트상품이 100개 이상 등록된 경우만 검색
ORDER BY COUNT(*) DESC;		-- 베스트상품 등록갯수 순으로 검색

---

JOIN

: 두 개 이상 테이블로부터 필요한 데이터를 연결해 하나의 포괄적인 구조로 결합

SELECT * FROM items
INNER JOIN ranking
ON ranking.item_code = items.item_code
WHERE ranking.main_category = "ALL";

/* [Alias]
SELECT * FROM items a
INNER JOIN ranking b
ON a.item_code = b.item_code
WHERE b.main_category = "ALL";
*/

- 실습 INNER JOIN

# 전체 베스트상품(ALL 메인 카테고리)에서 판매자별 베스트상품 개수 출력
SELECT provider, COUNT(provider) cnt_pvd
FROM items i
INNER JOIN ranking r
ON i.item_code = r.item_code
WHERE r.main_category = "ALL"
GROUP BY provider;

/* 메인 카테고리가 패션의류인 서브 카테고리 포함, 패션의류 전체 베스트상품에서
판매자별 베스트 상품 갯수가 5이상인 판매자와 베스트상품 개수 출력 */
SELECT provider, COUNT(*) cnt_pvd
FROM items i
INNER JOIN ranking r
ON i.item_code = r.item_code
WHERE main_category='패션의류'
GROUP BY provider
HAVING cnt_pvd >= 5;

/* 메인 카테고리가 신발/잡화인 서브 카테고리 포함, 전체 베스트상품에서 
판매자별 베스트상품 갯수가 10이상인 판매자와 베스트상품 갯수를 베스트상품 개수 순으로 출력 */
SELECT provider, COUNT(*) cnt_pvd
FROM items i
INNER JOIN ranking r
ON i.item_code = r.item_code
WHERE main_category='신발/잡화'
GROUP BY provider
HAVING cnt_pvd >= 10
ORDER BY cnt_pvd;

/* 메인 카테고리가 화장품/헤어인 서브 카테고리 포함, 전체 베스트상품의 평균, 최대, 최소 할인 가격 출력 */
SELECT avg(dis_price) avg_pvd,
	   max(dis_price) max_pvd,
	   min(dis_price) min_pvd
FROM items i
INNER JOIN ranking r
ON i.item_code = r.item_code
WHERE main_category='화장품/헤어'; -- GROUP BY 할 필요 없음

- 실습 OUTER JOIN

• OUTER JOIN은 ON절 조건 중 한쪽 데이터를 모두 가져옴.
• LEFT, RIGHT으로 기준이 되는 데이터 설정

/* sakila 데이터베이스에서 address_id가 address 테이블에는 있지만,
customer 테이블에는 없는 데이터의 갯수 출력 */

USE sakila; -- 사용할 데이터베이스 변경해야 하니까 이전에 사용하던 다른 데이터가 있다면 새로 작성 후 실행

SELECT COUNT(*) AS cust_addr_null
FROM address addr
LEFT OUTER JOIN customer cust
ON addr.address_id = cust.address_id
WHERE cust.address_id IS NULL; -- customer 테이블엔 없어야 하니까 WHERE절에 is null 조건

---

SubQuery

: SQL문 안에 () 괄호를 사용해 포함된 SQL문. 테이블과 테이블간 검색 시,
(테이블 중 필요한 부분만 먼저 가져오도록) 검색 범위를 좁히는 기능에 주로 사용

• JOIN은 출력 결과에 여러 테이블의 열이 필요한 경우 유용
• 대부분 서브쿼리는 JOIN문으로 처리 가능

(✅강사님피셜 : 현업에서는 JOIN을 쓰는 경우가 조금 더 많다)

 

EX1) 서브 카테고리가 '여성신발'인 상품 타이틀만 가져오기

- JOIN | SubQuery 비교

# JOIN 사용
SELECT title
FROM items
INNER JOIN ranking ON items.item_code = ranking.item_code
WHERE ranking.sub_category = '여성신발';

# 서브쿼리 사용
SELECT title
FROM items
WHERE item_code IN
	(SELECT item_code FROM ranking WHERE sub_category = '여성신발')

 

- 실습

1. 메인 카테고리별로 할인 가격이 10만원 이상인 상품이 몇개 있는지를 출력해보기

# JOIN 사용
SELECT main_category, 
       COUNT(*) AS tot_dis10
FROM items i
INNER JOIN ranking r
ON i.item_code = r.item_code
WHERE dis_price >= 100000
GROUP BY main_category;

# 서브쿼리 사용
SELECT main_category, 
       COUNT(*) AS tot_dis10
FROM ranking
WHERE item_code IN (
	SELECT item_code FROM items
	WHERE dis_price >= 100000
	)
GROUP BY main_category;

2. 'items' 테이블에서 'dis_price'가 200000 이상인 아이템들 중, 각 'sub_category'별 아이템 수 출력

# JOIN 사용
SELECT sub_category,
	COUNT(*) AS cnt_sub
FROM items i
INNER JOIN ranking r 
ON i.item_code=r.item_code
WHERE dis_price >= 200000
GROUP BY sub_category;

# 서브쿼리 사용
SELECT sub_category,
	COUNT(*) AS cnt_sub
FROM ranking
WHERE item_code IN (
	SELECT item_code 
	FROM items i
	WHERE dis_price >= 200000
	)
GROUP BY sub_category;

3. 메인 카테고리, 서브 카테고리에 대해, 평균할인가격과 평균할인율을 출력

SELECT main_category, 
	sub_category, 
	AVG(dis_price) AS avg_dis,
	AVG(discount_percent) AS avg_dis_per
FROM ranking r
INNER JOIN items i
ON r.item_code=i.item_code
GROUP BY main_category, sub_category; -- 여러 개 그룹핑 가능(,콤마 구분)

4. 판매자별, 베스트상품 갯수, 평균할인가격, 평균할인율을 베스트상품 갯수가 높은 순으로 출력

SELECT provider,
	COUNT(*) AS tot_cnt,
	AVG(dis_price) AS avg_dis,
	AVG(discount_percent) AS avg_dis_per
FROM items
GROUP BY provider
ORDER BY tot_cnt DESC;

---

SQL Query Test