CREATE DATABASE playdata;
USE playdata;

CREATE TABLE member -- Playdata memeber 테이블
( num	INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 고유 순번(PK)
  name		CHAR(3) NOT NULL, -- 이름
  gender	ENUM('M', 'F') NOT NULL,
  mobile	char(13), -- 휴대폰 번호
  firstNum	CHAR(3), -- 휴대폰의 국번 010
  midNum 	CHAR(4), -- 휴대폰의 중간 4자리 번호
  lastNum 	CHAR(4), -- 휴대폰의 마지막 4자리 번호
  addr		CHAR(10), -- 주소
  addr1		CHAR(5), -- 시
  addr2		CHAR(5), -- 구
  email		CHAR(30), -- 이메일
  email1	char(10), -- 이메일 아이디
  email2	char(20), -- 계정 ex. naver.com
  major		char(10), -- 전공
  birthYear	INT -- 출생년도
);

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/playdata.csv'
INTO TABLE member
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(@num,@name,@gender,@mobile,@addr,@email,@major,@birthYear)
SET name = @name,
 gender = @gender,
 mobile = @mobile,
 firstNum = SUBSTRING_INDEX(@mobile, '-', 1),
 midNum = SUBSTRING(@mobile, 5, 4),
 lastNum = SUBSTRING_INDEX(@mobile, '-', -1),
 addr = @addr,
 addr1 = SUBSTRING_INDEX(@addr, ' ', 1),
 addr2 = SUBSTRING_INDEX(@addr, ' ', -1),
 email = @email,
 email1 = SUBSTRING_INDEX(@email, '@', 1),
 email2 = SUBSTRING_INDEX(@email, '@', -1),
 major = @major,
 birthYear = CONCAT(19, REPLACE(@birthYear, '년생', ''));

CREATE DATABASE playdata;
USE playdata;

-- palydata의 Tables 오른쪽 버튼 Table Data Import Wizard를 통해 csv파일 import하기

-- num을 1부터 시작할 수 있도록 (기존 데이터는 0부터 시작)
SET SQL_SAFE_UPDATES = 0; -- 일시적으로 safe_mode  해제하기
UPDATE member SET num = NULL;
ALTER TABLE member MODIFY COLUMN num INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY;

-- 전화번호 분리
ALTER TABLE member ADD COLUMN firstNum char(3);
UPDATE member SET firstNum = SUBSTRING_INDEX(mobile, "-" ,1);

ALTER TABLE member ADD COLUMN midNum char(4);
UPDATE member SET midNum = SUBSTRING(mobile, 5, 4);

ALTER TABLE member ADD COLUMN lastNum char(4);
ALTER TABLE member MODIFY COLUMN lastNum char(10); -- Data too long
UPDATE member SET lastNum = SUBSTRING_INDEX(mobile, "-" ,-1);

-- 이메일 분리
ALTER TABLE member ADD COLUMN email1 char(10);
ALTER TABLE member MODIFY COLUMN email1 char(20); -- Data too long
UPDATE member SET email1 = SUBSTRING_INDEX(email,"@",1);

ALTER TABLE member ADD COLUMN email2 char(20);
UPDATE member SET email2 = SUBSTRING_INDEX(email,"@",-1);

-- 주소 분리
ALTER TABLE member ADD COLUMN addr1 char(5);
UPDATE member SET addr1 = SUBSTRING_INDEX(addr," ",1);

ALTER TABLE member ADD COLUMN addr2 char(10);
UPDATE member SET addr2 = SUBSTRING_INDEX(addr," ",-1);

-- 년생 제거하고 19 앞에 붙이기 ex. 95년생 -> 1995
UPDATE member SET birthYear = CONCAT(19, REPLACE(birthYear, '년생', ''));
-- 년생 값이 없는 경우 null로 저장
UPDATE member SET birthYear = null WHERE name = '신동욱';

SELECT * FROM playdata.member;
SELECT num, name, gender, firstNum, midNum, lastNum, addr1, addr2, email1, email2, major, birthYear FROM playdata.member;

-- 서울
SELECT name, addr1 AS '지역' FROM member
   WHERE addr1 IN ('서울시') ;

-- 전공자
SELECT name, major AS '전공' FROM member
   WHERE major IN ('컴퓨터공학과', '빅데이터', '수학과', '경영학과') ;
-- 전공자 수
SELECT COUNT(*) AS '전공자 수' FROM member
   WHERE major IN ('컴퓨터공학과', '빅데이터', '수학과', '경영학과') ;

-- 비전공자
SELECT name, major AS '비전공' FROM member
   WHERE major NOT IN ('컴퓨터공학과', '빅데이터', '수학과', '경영학과') ;
-- 비전공자 수
SELECT COUNT(*) AS '비전공자 수' FROM member
   WHERE major NOT IN ('컴퓨터공학과', '빅데이터', '수학과', '경영학과') ;