[2월 4주차-2/25(1)]MySQL 데이터 그룹화, 테이블 조작, 외래키 설정 및 데이터 유형 정리

오늘은 MySQL에서 GROUP BY와 HAVING 절을 이용한 데이터 그룹화, AUTO_INCREMENT를 활용한 테이블 조작, 조회 결과를 테이블에 입력하는 방법, 외래키 설정 및 삭제, 그리고 데이터 유형에 대해 배웠습니다. 주요 내용을 정리해보겠습니다.

---

1. GROUP BY와 HAVING을 활용한 데이터 그룹화

GROUP BY 절을 사용할 때는 SELECT 문에서 사용한 열을 반드시 포함해야 합니다. 특정 조건을 만족하는 그룹만 필터링하려면 HAVING 절을 활용할 수 있습니다.

SELECT special_features, rating, COUNT(*) AS cnt 
FROM film
GROUP BY special_features, rating
HAVING rating = 'R' AND cnt > 8
ORDER BY cnt DESC;

• GROUP BY special_features, rating: special_features와 rating을 기준으로 그룹화
• HAVING rating = 'R' AND cnt > 8: 그룹화된 데이터 중에서 rating이 'R'이고, 개수가 8보다 큰 그룹만 조회
• ORDER BY cnt DESC: 결과를 개수 기준 내림차순 정렬

---

2. AUTO_INCREMENT를 활용한 테이블 생성 및 조작

MySQL에서 AUTO_INCREMENT를 사용하면 자동으로 증가하는 기본 키 값을 설정할 수 있습니다.

2.1 테이블 생성 및 기본 데이터 삽입

CREATE TABLE doit_increment (
    col_1 INT AUTO_INCREMENT PRIMARY KEY,
    col_2 VARCHAR(50),
    col_3 INT
);

INSERT INTO doit_increment (col_2, col_3) VALUES ('1 auto input', 1);
INSERT INTO doit_increment (col_2, col_3) VALUES ('2 auto input', 2);
INSERT INTO doit_increment (col_1, col_2, col_3) VALUES (3, '3 input', 3); -- 가능
INSERT INTO doit_increment (col_1, col_2, col_3) VALUES (5, '4 skip and input', 5); -- 가능
INSERT INTO doit_increment (col_2, col_3) VALUES (' auto input', 0); -- 6으로 입력됨

• AUTO_INCREMENT는 자동으로 값을 증가시키지만, 직접 값을 지정하는 것도 가능함
• 자동 증가는 마지막 값에서 이어서 증가함.

2.2 AUTO_INCREMENT 값 변경

ALTER TABLE doit_increment AUTO_INCREMENT = 100;
INSERT INTO doit_increment (col_2, col_3) VALUES ('has it changed?', 0); -- 100으로 입력됨

• AUTO_INCREMENT 초기값을 변경하여 다음 입력될 값이 100이 되도록 설정

2.3 증가 단위 변경

SET @@auto_increment_increment = 5;
INSERT INTO doit_increment (col_2, col_3) VALUES ('Will it increase by 5?', 0); -- 105로 입력됨

• AUTO_INCREMENT의 증가 단위를 5로 설정하여 100 → 105로 증가

---

3. 조회 결과를 새로운 테이블에 입력하기

기존 테이블 데이터를 복사하여 새로운 테이블을 만들거나, 기존 데이터를 활용하여 새로운 테이블에 데이터를 삽입할 수 있습니다.

3.1 기존 테이블 생성 및 데이터 삽입

CREATE TABLE doit_insert_select_from (
    col_1 INT,
    col_2 VARCHAR(10)
);

INSERT INTO doit_insert_select_from VALUES (1, 'Do');
INSERT INTO doit_insert_select_from VALUES (2, 'It');
INSERT INTO doit_insert_select_from VALUES (3, 'MySQL');

3.2 INSERT INTO를 활용한 테이블 복사

CREATE TABLE copy_table1 (
    col_1 INT,
    col_2 VARCHAR(10)
);

INSERT INTO copy_table1
SELECT * FROM doit_insert_select_from;

• copy_table1을 생성하고 doit_insert_select_from 데이터를 복사

3.3 CREATE TABLE ... AS 활용

CREATE TABLE copy_table2 AS
(SELECT * FROM doit_insert_select_from);

• copy_table2를 생성하면서 doit_insert_select_from 데이터를 바로 복사

---

4. 외래키(Foreign Key) 설정 및 삭제

외래키를 사용하면 테이블 간의 관계를 유지할 수 있으며, 부모 테이블의 데이터가 존재해야 자식 테이블에서 데이터를 삽입할 수 있습니다.

4.1 외래키 설정

CREATE TABLE parent (
    col_1 INT PRIMARY KEY
);

CREATE TABLE child (
    col_1 INT
);

ALTER TABLE child ADD FOREIGN KEY (col_1) REFERENCES parent(col_1);

• child 테이블의 col_1을 parent 테이블의 col_1과 연결

4.2 부모-자식 테이블 데이터 삽입 및 삭제 순서

INSERT INTO parent VALUES(1);
INSERT INTO child VALUES(1);

DROP TABLE child;  -- 자식 테이블 먼저 삭제
DROP TABLE parent; -- 부모 테이블 삭제

• 부모 테이블 데이터가 먼저 있어야 자식 테이블에서 참조 가능
• 자식 테이블을 먼저 삭제한 후 부모 테이블을 삭제해야 오류가 발생하지 않음

4.3 외래키 제약 조건 삭제

ALTER TABLE child DROP CONSTRAINT child_ibfk_1;

• 외래키 제약 조건을 제거하여 부모 테이블 없이도 데이터를 삽입 가능하도록 변경

---

5. MySQL 데이터 유형 정리

MySQL에서 데이터를 저장할 때, 데이터 유형을 올바르게 선택하는 것이 중요합니다.

5.1 정수형 데이터

• INT, BIGINT 등

5.2 실수형 데이터

• FLOAT, DOUBLE → 부동소수점 방식으로 저장되므로 금융 데이터에는 부적합
• DECIMAL, NUMERIC → 정확한 소수점 연산이 필요할 때 사용

5.3 형 변환

SELECT CAST('123' AS SIGNED);
SELECT CONVERT('123.45', DECIMAL(5,2));

• CAST와 CONVERT를 사용하여 데이터 유형을 변환할 수 있음

5.4 문자형 데이터

• CHAR(N), VARCHAR(N) → CHAR는 고정 길이, VARCHAR는 가변 길이

5.5 날짜 및 시간 데이터

• DATETIME → 문자형으로 저장됨, 특정한 날짜와 시간을 저장
• TIMESTAMP → 숫자형으로 저장됨, UTC 기반 자동 업데이트 가능

---

🔥 정리

오늘 배운 MySQL 개념을 간단히 정리하면 다음과 같습니다.

✅ GROUP BY와 HAVING을 사용하여 그룹화된 데이터 필터링
✅ AUTO_INCREMENT를 활용하여 자동 증가하는 기본 키 관리
✅ 기존 데이터를 복사하여 새로운 테이블 생성 (INSERT ... SELECT, CREATE TABLE AS)
✅ FOREIGN KEY를 사용하여 테이블 간 관계 설정 및 삭제 순서 이해
✅ INT, FLOAT, DECIMAL, DATETIME 등의 데이터 유형과 형 변환 활용