https://youtu.be/aiMSluMNzI8

USE market_db;
SELECT * FROM member;
SHOW INDEX FROM member; 
SHOW TABLE STATUS LIKE 'member';
(인덱스 하나도 없는 상태 확인.)
 
<인덱스 생성>
 
1) 인덱스 생성

CREATE INDEX idx_member_addr
ON member (addr);
    
SHOW INDEX FROM member;

SHOW TABLE STATUS LIKE 'member';
인덱스 만들고 다시 실행했는데 인덱스 정보가 안 보임.

---->
ANALYZE TABLE member;  
이걸 해야 인덱스가 적용된다.

SHOW TABLE STATUS LIKE 'member';
인덱스 생긴 거 확인 가능.
 
 
 
2) 유니크  인덱스 생성
 
CREATE UNIQUE INDEX idx_member_mem_name
ON member (mem_name);
유니크 인덱스는 중복값이 없어야 한다.

SHOW INDEX FROM member;

INSERT INTO member VALUES('MOO', '마마무', 2, '태국', '001', '12341234', 155, '2020.10.10');
ANALYZE TABLE member;
멤버 이름이 같은 데이터_ 유니크 인덱스가 있어서 삽입 불가능.
 
 
 
3) 인덱스 사용해서 검색

SELECT mem_id, mem_name, addr
FROM member
        WHERE mem_name = '에이핑크';
인덱스 사용해서 검색했음. 인덱스는 where절, 열 이름이 나와야 쓸 수 있음.


4) 인덱스를 사용하려면, WHERE 절에 뒤에 쓰는 열에 아무 가공을 하면 안된다.  
 
CREATE INDEX idx_member_mem_number
ON member (mem_number);
ANALYZE TABLE member;

SELECT mem_name, mem_number
FROM member
    WHERE mem_number >= 7;
    인덱스가 있든 없든 결과는 동일하게 나옴.
    execution plan 에 들어가서 결과 나오는 과정을 보면 
    index range scan --> query block #1 이런 식의 과정이 확인 됨.
   
SELECT mem_name, mem_number
FROM member
    WHERE mem_number >= 1;
    결과값으로 테이블의 모든 행이 출력됨.
    이 경우 execution plan 보면 인덱스 사용하지 않음. 
    어떤 방식이 더 효과적인지 자동으로 계산을 하는 것. 
   
SELECT mem_name, mem_number
FROM member
        WHERE mem_number*2 >= 14;
멤버 수가 7 이상인 거랑 똑같은 결과가 나오지만, 인덱스를 사용하지 않음.
 
SELECT mem_name, mem_number
FROM member
        WHERE mem_number >= 14/2;
        
이렇게 2를 뒤로 넘기면 인덱스 사용한다. 
--> WHERE절에 가공을 하지 않으면 더 효율적으로 정보를 불러올 수 있다.
 
 

5) 인덱스 제거

DROP INDEX idx_member_mem_name ON member;
DROP INDEX idx_member_addr ON member;
DROP INDEX idx_member_mem_number ON member;

 ALTER TABLE member
DROP PRIMARY KEY;
이건 PK, FK로 지금 buy 테이블과 연결되어 있기 때문에 안 지워짐.

SELECT table_name, constraint_name
 FROM information_schema.referential_constraints
     WHERE constraint_schema = 'market_db' ;
market_db의 외래키 이름 확인하는 구문. 

ALTER TABLE buy
DROP FOREIGN KEY buy_ibfk_1;
    
ALTER TABLE member
DROP PRIMARY KEY;
    
FK먼저 지우고 다시 mem 테이블의 PK 지우기 --> 잘 지워짐.

[SQL 기초 강의] 15강. 인덱스의 개념과 장단점, 클러스터형 인덱스와 보조 인덱스 - YouTube

USE market_db ;

CREATE TABLE table1 (
    col1 INT PRIMARY KEY,
    col2 INT,
    col3 INT 
);
SHOW INDEX FROM table1; 

키네임에 프라이머리 있으면 자동으로 클러스터형 인덱스가 만들어진다. 그 열에 입력된 값 기준으로 사전식 정렬.



CREATE TABLE table2 (
    col1 INT PRIMARY KEY,
    col2 INT UNIQUE,
    col3 INT UNIQUE
);
SHOW INDEX FROM table2;

여기서 프라이머리 키인 컬럼 1은 클러스터 인덱스, 컬럼2, 컬럼3는 보조 인덱스로 봄. 

둘 다 자동으로 생성되는 인덱스이다.
유니크 조건이 여러 열에 붙을 수 있듯, 보조 키도 여러개 만들 수 있음.
프라이머리키와 클러스터형 인덱스는 테이블 당 하나만 만들 수 있다. 

USE market_db;
DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
( mem_id CHAR(8),
  mem_name VARCHAR(10),
  mem_number INT,
  addr CHAR(2)
);

INSERT INTO member VALUES('TWC', '트와이스', 9, '서울');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기');
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울');

SELECT * FROM member;

새로운 멤버 테이블 만들었다. 인덱스 없이(PK 지정 안 하고) 무작위로 입력하면, 입력한 순서 그대로 테이블에서 보인다.

ALTER TABLE member
ADD CONSTRAINT
    PRIMARY KEY(mem_id);
SELECT *FROM member;

프라이머리 키 다시 지정한 후 불러오면 멤버 아이디가 알파벳 순으로 정렬되는 걸 확인할 수 있다.
클러스터형 인덱스가 자동으로 생성된 것.

ALTER TABLE member DROP PRIMARY KEY ; 
다시 기본 키를 제거하고
ALTER TABLE member
ADD CONSTRAINT
    PRIMARY KEY(mem_name);
새로운 기본 키를 멤버 이름 열에 부여하면
SELECT * FROM member;
멤버 이름 순으로 다시 정렬되는 걸 확인할 수 있다.

INSERT INTO member VALUES ('GRL', '소녀시대', 8, '서울');
이 상태에서 소녀시대 행을 새로 입력하면
SELECT * FROM member;
블핑, 여자친구 사이에 삽입이 되었다. ㄱㄴㄷ 순으로.

보조인덱스는 UNIQUE, 고유 키로 지정하면 생성된다. 
클러스터 형 인덱스를 사전 만들기에 비유한다면, 
보조 인덱스는 용어 사전이 아니라 줄글로 된 책의 인덱스를 만드는 것과 비슷하다. 
책의 내용을 다시 정렬하는 것이 아니라, 맨 뒤에 <찾아보기>를 추가하는 방식이다. 

USE market_db;
DROP TABLE IF EXISTS member;
CREATE TABLE member
( mem_id CHAR(8),
  mem_name VARCHAR(10),
  mem_number INT,
  addr CHAR(2)
);

INSERT INTO member VALUES('TWC', '트와이스', 9, '서울');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기');
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울');

SELECT * FROM member;

인덱스 없이 새로운 테이블 만들기.

ALTER TABLE member
ADD CONSTRAINT
    UNIQUE (mem_id);
SELECT * FROM member;

유니크 키를 추가한 뒤 멤버 테이블을 조회해도, 내용, 순서가 바뀌지 않았다. 보조 인덱스가 생성된 것이다.

ALTER TABLE member
ADD CONSTRAINT
    UNIQUE (mem_name);
SELECT * FROM member;

두번째 유니크 키를 지정함. 책 뒤에 찾아보기는 여러 버전으로 만들 수 있으므로.

INSERT INTO member VALUES ('GRL', '소녀시대' ,8, '서울');
SELECT * FROM member;

새로운 행은 그냥 맨 뒤에 추가된다.

<1>

DROP PROCEDURE IF EXISTS caseProc;
DELIMITER $$
CREATE PROCEDURE caseProc()
BEGIN
    DECLARE point INT ;
    DECLARE credit CHAR(1);
    SET point = 88;
    
    CASE
         WHEN point >= 90 THEN
         SET credit = 'A';
         WHEN point >= 80 THEN
         SET credit = 'B';
         WHEN point >= 70 THEN
         SET credit = 'C';
         WHEN point = 60 THEN
         SET credit = 'D';
         ELSE
         SET credit = 'F';
   END CASE;
   SELECT CONCAT('취득점수==>', point), CONCAT('학점==>', credit);
END $$
DELIMITER ;
CALL caseProc();

<2>

SELECT M.mem_id, M.mem_name, SUM(price*amount) "총구매액",
     CASE
            WHEN (SUM(price*amount) >= 1500) THEN '최우수고객'
            WHEN (SUM(price*amount) >= 1000) THEN '우수고객'
            WHEN (SUM(price*amount) >= 1 ) THEN '일반고객'
            ELSE '유령고객'
     END "회원등급"
FROM buy B 
        RIGHT OUTER JOIN member M
        ON B.mem_id = M.mem_id
GROUP BY M.mem_id
ORDER BY SUM(price*amount) DESC ; 

<1> 

DROP PROCEDURE IF EXISTS ifProc2;
DELIMITER $$
CREATE PROCEDURE ifProc2()
BEGIN
  DECLARE myNum INT;
  SET myNum = 200;
  IF myNum = 100 THEN
SELECT '100입니다,';
  ELSE
SELECT '100이 아닙니다.';
  END IF;
END $$
DELIMITER ; 
CALL ifProc2();

<2>

DROP PROCEDURE IF EXISTS ifProc3;
DELIMITER $$
CREATE PROCEDURE ifProc3()
BEGIN 
DECLARE debutDate DATE;
    DECLARE curDate DATE;
    DECLARE days INT;
    
    SELECT debut_date INTO debutDate
FROM market_db.member
        WHERE mem_id = 'APN';
        
SET curDATE = CURRENT_DATE();
    SET days = DATEDIFF(curDATE, debutDate);
    
    IF (days/365) >= 5 THEN
SELECT CONCAT('데뷔한지', days, '일이나 지났습니다. 핑순이들 축하합니다!');
ELSE 
SELECT '데뷔한지' + days + '일밖에 안 되었네요. 핑순이들 화이팅~' ; 
END IF;
END $$
DELIMITER ; 
CALL ifProc3();

굵은 글씨는 SQL의 스토어드 프로시저이고

그 가운데 if문 넣는 연습을 했다. 

[SQL 기초 강의] 11강. SQL 프로그래밍(IF문, CASE문, WHILE문, 동적SQL) - YouTube

좋은 강의 감사드립니다.