[중급 쿼리] INDEX, VIEW, WITH, 함수 생성

INDEX

인덱스를 설정하면 쿼리 실행 속도가 빨라진다. > 생산성 향상

 

• 클러스터형 인덱스

영어 사전과 같은 형태로 데이터를 실제로 재정렬하여 저장

PRIMARY KEY 컬럼이 있을 경우, 자동 생성

 

• 보조 인덱스

데이터를 디스크에 재정렬하지 않고, 보조 인덱스를 설정하여 각 데이터의 위치만 빠르게 조회 인덱스는 키-필드만 갖고 있고, 나머지 세부 테이블 컬럼 정보는 가지고 있지 않음

 

• INDEX 확인방법

SHOW INDEX FROM 테이블명; 을 실행했을 때 key_name = PRIMARY KEY라면 클러스터형 인덱스

 

• 테이블 변경

FOREIGN KEY도 INDEX가 생긴다! (보조 인덱스)

테이블에 특정 컬럼에 duplicate 값이 나오지 않도록 제약조건을 추가하기

-- ALTER TABLE 테이블이름 ADD [CONSTRAINT 제약조건명] UNIQUE(컬럼명)
ALTER TABLE userTbl ADD [CONSTRAINT TESTDate] UNIQUE(mDate);

 

• INDEX 추가

1. CREATE INDEX 인덱스명 ON 테이블명 ( column 1, column 2, ... );
2. ALTER TABLE 테이블명 ADD INDEX 인덱스명 ( column 1, column 2, ... );

CREATE INDEX groupName ON userTbl(name);
SHOW INDEX FROM userTbl;

CREATE INDEX prodName ON buyTbl(prodName);
SHOW INDEX FROM buyTbl;

UNIQUE가 설정된 column에 대해서만 UNIQUE INDEX를 설정할 수 있다. (그 외에는 그냥 INDEX)

*동일한 column에 여러 INDEX를 설정할 필요는 사실상 없지만, 가능은 하다.

 

• INDEX 삭제

2개 이상의 INDEX를 한번에 삭제하는 쿼리는 데이터 관리에 위험하기도 하고 없음. → 하나씩 삭제하기를 추천!

ALTER TABLE 테이블명 DROP INDEX 인덱스이름;

 

---

VIEW

실제 테이블을 기반으로 가상테이블 만들기

데이터의 특정 부분에만 접근하도록 하여 원본을 보존하고 원하는 데이터를 확인하는 방법

1. 생성

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

1. 수정

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

1. 삭제

DROP VIEW view_name;

 

 

---

WITH

WITH cte_name AS (
-- SQL 쿼리
)
-- CTE를 사용하는 메인 쿼리

✔️VIEW 와 WITH의 차이점

WITH는 임시테이블 : WITH를 포함한 전체쿼리가 실행될 때만 유효하다.

쿼리가 실행됨과 동시에 해당 데이터는 사라진다. (어디에도 저장되지 않음)

 

 

 

---

직접 함수 생성하기

MySQL에서 함수를 생성할 때 진한 회색 부분을 반드시 같이 실행해주어야 한다.

/*
MISSION
공격력과 방어력의 합이 120보다 크면 ‘very strong’, 90보다 크면 ‘strong’,
모두 해당 되지 않으면 ‘not strong’를 반환하는 함수 ‘isStrong’을 만들고 사용해주세요.
조건1: attack과 defense를 입력값으로 사용하세요.
조건2: 결과값 데이터 타입은 VARCHAR(20)로 해주세요.
*/

DROP FUNCTION IF EXISTS isStrong; -- 동일한 함수가 있다면 삭제

SET GLOBAL log_bin_trust_function_creators=1; -- 함수 생성위해 실행
DELIMITER // -- DELIMITER와 // 사이에 띄어쓰기 한칸 필수

CREATE FUNCTION isStrong(attack INT, defense INT) -- 함수이름 및 x값 정의
	RETURNS VARCHAR(20) -- isStrong 함수 결과값 형식
BEGIN -- 함수 시작
	DECLARE a INT; -- DECLARE로 빈 값 선언
    DECLARE b INT;
    DECLARE isStrong varchar(20);
	SET a = attack; -- 선언한 빈 값 채워주기
	SET b = defense;
    SELECT CASE -- 'SELECT 쿼리 INTO 결과반환' 구조, 쿼리에 CASE 조건문 삽입
		WHEN a+b >= 120 THEN 'very strong'
        WHEN a+b >= 90 THEN 'strong'
		ELSE 'not strong'
		END INTO isStrong; -- 해당 쿼리 결과를 선언한 isStrong에 집어넣음
	RETURN isStrong; -- isStrong 반환
END

//
DELIMITER ; -- DELIMITER와 ; 사이에 띄어쓰기 한칸 필수

#### 만든 함수 조회하기 ####
SELECT name, isSTrong(attack, defense) AS isStrong
FROM mypokemon;