TIL 2023-01-27 (금) 중급 SQL

요즘 SQL 관해서 강의를 듣고 있기 때문에 포스트를 하나 남겨보도록 한다.
아주 기본적인 데이터베이스 구성이나 테이블 생성 및 변경 등에 대해서는 따로 포스트를 안남기려고 한다.

1. 인덱스

테이블에 대한 동작의 속도를 높여주는 자료 구조이다.
키-필드만으로 컬럼 정보를 가져올 수 있도록 해주는 역할이다.
종류로는 클러스터형과 보조 인덱스가 있다.

1. 클러스터형 : 사전처럼 데이터가 순서대로 정렬되는 형태이다. 테이블에 PRIMARY KEY가 있다면 자동으로 생성이 된다.
2. 보조 인덱스 : 일반적인 책 뒷 부분에 따로 있는 색인과 비슷한 역할로 볼 수 있다.

인덱스 확인

SHOW INDEX FROM TABLE_NAME;

인덱스 생성

CREATE INDEX INDEX_NAME ON TABLE_NAME (Column);

ALTER TABLE TABLE_NAME ADD INDEX INDEX_NAME (Column);

또는 테이블을 생성할 때 인덱스를 함께 생성할 수 도 있다.

CREATE TABLE userTBL{
userID CHAER(8) NOT NULL PRIMARY KEY,
name VARCHAR(20) UNIQUE NOT NULL,
birthday INT NOT NULL,
UNIQUE INDEX idx_userTBL_name (name),
INDEX idx_userTBL_birthday (birthday)
}

인덱스 삭제

ALTER TABLE TABLE_NAME DROP INDEX INDEX_NAME;

2. GROUP BY, HAVING

HAVING은 집계함수를 통해서 조건비교를 하는 경우에 사용한다.
GROUP BY는 결과값을 특정 컬럼이나 기준에 따라서 정렬을 할 때 사용한다.

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

3. JOIN

JOIN은 두 개 이상의 테이블로 부터 필요한 데이터를 연결해서 하나의 구조로 결합시키는 연산이다.

1. INNER JOIN
   두 테이블의 컬럼값이 매칭되는 경우에만 가져옴

   EX)
   SELECT * FROM items 
   INNER JOIN ranking 
   ON ranking.item_code = items.item_code
   WHERE ranking.main_category = "ALL"
   
   items의 item_code와 ranking의 item_code를 비교한 후 category가 ALL로 입력되어 있는 데이터들을 SELECT하는 것이다.
   

2. OUTER JOIN
   LEFT OUTER JOIN: 왼쪽 테이블에서는 모든 컬럼을, 오른쪽 테이블에서는 왼쪽 테이블의 컬럼과 매칭되는 컬럼만 가져옴

   EX)
   SELECT * FROM customer_table C
   LEFT OUTER JOIN order_table O ON C.customer_id = O.customer_id
   
   cutomer_table과 order_table에서 각각 customer_id를 비교한 후 
   customer_table의 컬럼에다가 order_table에서 컬럼을 가져와서 붙인다.

   RIGHT OUTER JOIN: 반대로 오른쪽 테이블에서는 모든 컬럼을, 왼쪽 테이블에서는 오른쪽 테이블의 컬럼과 매칭되는 컬럼만 가져옴

   SELECT * FROM customer_table C 
   RIGHT OUTER JOIN order_table O ON C.customer_id = O.customer_id
   
   cutomer_table과 order_table에서 각각 customer_id를 비교한 후 order_table의 컬럼에다가 customer_table의 컬럼값들을 가져와서 붙인다.

4. 서브쿼리

SQL문 안에 SQL문을 작성한다고 보면 된다.
대부분의 서브쿼리는 JOIN문으로 처리가 가능하기 때문에 상황에 따라서 편한 것을 사용해도 무방하다.

EX)
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 = '여성신발')

어느 것이 더 낫다고 표현할 수는 없지만 편한 것을 사용하는 것이 좋다고 생각한다.