[MySQL] Alter, 서브쿼리, 트랜잭션, 뷰 개념 및 연습문제

SQL 심화 문법 가이드

이 문서는 ALTER, 서브쿼리(Subquery), 트랜잭션(Transaction), 뷰(View) 등 SQL 심화 개념을 다룹니다. 각 주제별로 개념 정리, 예제 코드, 그리고 이해를 돕기 위한 연습문제로 구성되어 있습니다.

---

1. ALTER : 테이블 구조 변경

ALTER는 이미 생성된 테이블의 구조를 변경하기 위해 사용하는 DDL(데이터 정의어)입니다.

1.1. 개념 정리

• ADD COLUMN: 새로운 열(필드)을 추가합니다.
• MODIFY COLUMN: 기존 열의 데이터 타입이나 제약 조건을 수정합니다.
• CHANGE COLUMN: 기존 열의 이름을 변경하면서 타입도 함께 수정할 수 있습니다.
• DROP COLUMN: 특정 열을 삭제합니다.
• ADD/DROP CONSTRAINT: 기본 키(PK), 외래 키(FK), UNIQUE 등 제약 조건을 추가하거나 삭제합니다.

1.2. 예제 코드 (1_alter.sql 기반)

-- 테이블 생성
CREATE TABLE employee(
    id INT PRIMARY KEY,
    name VARCHAR(50),
    dept VARCHAR(30)
);

-- [1] 열 추가 (ADD COLUMN)
-- age 열을 정수형, 기본값 10으로 추가
ALTER TABLE employee ADD COLUMN age INT DEFAULT 10;

-- [2] 열 타입 수정 (MODIFY COLUMN)
-- dept 열의 타입을 longtext로 변경
ALTER TABLE employee MODIFY COLUMN dept LONGTEXT;

-- [3] 열 이름 및 타입 변경 (CHANGE COLUMN)
-- name 열을 nickname으로 이름을 바꾸고 타입을 VARCHAR(100)으로 변경
ALTER TABLE employee CHANGE COLUMN name nickname VARCHAR(100);

-- [4] 열 삭제 (DROP COLUMN)
-- age 열 삭제
ALTER TABLE employee DROP COLUMN age;

-- [5] 제약 조건 추가 (ADD CONSTRAINT)
-- nickname 열에 UNIQUE 제약 조건 추가
ALTER TABLE employee ADD CONSTRAINT uq_nickname UNIQUE (nickname);

-- [6] 제약 조건 삭제 (DROP CONSTRAINT)
-- uq_nickname 제약 조건 삭제
ALTER TABLE employee DROP CONSTRAINT uq_nickname;
-- 참고: MySQL에서는 DROP INDEX uq_nickname; 으로도 가능

-- [7] 테이블 구조 확인
SHOW COLUMNS FROM employee;

1.3. 연습 문제

1. employee 테이블에 hire_date (입사일) 열을 DATE 타입으로 추가해보세요.
2. employee 테이블의 id 열에 AUTO_INCREMENT 속성을 추가해보세요. (힌트: MODIFY COLUMN 사용)
3. employee 테이블에 email 열을 VARCHAR(100)으로 추가한 뒤, NOT NULL 제약 조건을 걸어보세요.
4. 추가했던 email 열을 다시 삭제해보세요.

---

2. 서브쿼리 (Subquery)

서브쿼리는 하나의 SQL 문 안에 포함된 또 다른 SELECT 문입니다. 복잡한 질의를 단계적으로 해결하거나, 다른 테이블의 데이터를 조건으로 활용할 때 유용합니다.

2.1. 개념 정리

• 위치: SELECT, FROM, WHERE, HAVING 절 등 다양한 곳에서 사용될 수 있습니다.
• WHERE 절: 다른 쿼리의 결과를 조건으로 사용할 때 주로 사용됩니다. (예: 평균 점수보다 높은 학생 조회)
• FROM 절: 쿼리의 결과를 하나의 테이블처럼(인라인 뷰) 사용하여 더 복잡한 조회를 할 때 사용됩니다.
• SELECT 절: 스칼라 서브쿼리(하나의 값만 반환)를 사용하여 특정 레코드와 연관된 단일 값을 가져올 때 사용됩니다.

2.2. 예제 코드 (2_subquery.sql 기반)

-- 예제용 테이블 및 데이터
CREATE TABLE student (name VARCHAR(20), kor INT, math INT);
INSERT INTO student VALUES ('신동엽', 90, 85), ('유재석', 80, 95), ('강호동', 75, 70);

-- [1] WHERE 절 서브쿼리
-- 전체 학생의 평균 점수보다 높은 점수를 받은 학생의 이름 조회
SELECT name
FROM student
WHERE (kor + math) / 2 > (SELECT AVG((kor + math) / 2) FROM student);

-- [2] IN 연산자와 함께 사용
-- 국어 점수가 평균 이상인 학생들의 점수와 동일한 점수를 가진 학생 조회
SELECT name, kor
FROM student
WHERE kor IN (SELECT kor FROM student WHERE kor >= (SELECT AVG(kor) FROM student));

-- [3] FROM 절 서브쿼리 (인라인 뷰)
-- 각 학생의 이름과 평균 점수를 조회하고, 평균 점수 순으로 내림차순 정렬
SELECT name, average_score
FROM (SELECT name, (kor + math) / 2 AS average_score FROM student) AS student_avg
ORDER BY average_score DESC;

-- [4] SELECT 절 서브쿼리 (스칼라 서브쿼리)
-- 각 학생의 총점이 자신보다 높은 학생이 몇 명인지 조회
SELECT
    s1.name,
    (SELECT COUNT(*) FROM student s2 WHERE (s2.kor + s2.math) > (s1.kor + s1.math)) AS higher_rank_count
FROM student s1;

2.3. 연습 문제

1. student 테이블에서 수학 점수가 가장 높은 학생의 이름을 찾아보세요.
2. orders 테이블(아래 4. 뷰 예제 참고)에서, 평균 주문 금액보다 더 비싼 상품을 주문한 주문 정보를 모두 조회해보세요.
3. member 테이블에서 'VIP' 등급인 회원이 주문한 orders 내역을 서브쿼리를 사용하여 조회해보세요.

---

3. 트랜잭션 (Transaction)

트랜잭션은 데이터베이스의 상태를 변화시키기 위해 수행하는 하나의 작업 단위입니다. 여러 SQL 명령어를 하나의 묶음으로 처리하여, 모두 성공하면 COMMIT하여 데이터베이스에 영구 반영하고, 하나라도 실패하면 ROLLBACK하여 모든 변경 사항을 취소합니다.

3.1. 개념 정리

• COMMIT: 트랜잭션 내의 모든 작업이 성공적으로 완료되었을 때, 변경 사항을 데이터베이스에 영구적으로 저장합니다.
• ROLLBACK: 트랜잭션 내의 작업 중 하나라도 실패하면, 트랜잭션 시작 이전 상태로 모든 것을 되돌립니다.
• SAVEPOINT: 트랜잭션 내에 중간 저장 지점을 만들어, 전체 롤백이 아닌 특정 지점으로만 되돌아갈 수 있게 합니다.
• autocommit: SQL 문을 실행할 때마다 즉시 COMMIT을 수행하는 설정. 트랜잭션을 사용하려면 이 설정을 해제(SET autocommit = 0;)해야 합니다.
• 데이터 무결성: 트랜잭션은 계좌 이체와 같이 여러 단계로 이루어진 작업의 데이터 일관성과 무결성을 보장하는 핵심 기능입니다.

3.2. 예제 코드 (1_Transaction.sql 기반)

-- 예제용 테이블
CREATE TABLE trans (name VARCHAR(20), money INT);
INSERT INTO trans VALUES ('신동엽', 100000), ('서장훈', 150000);

-- 자동 커밋 비활성화 (워크벤치 세션용)
SET autocommit = 0;

-- [1] 기본 트랜잭션 (COMMIT / ROLLBACK)
START TRANSACTION; -- 트랜잭션 시작

-- 작업 1: 신동엽 계좌에서 30,000원 출금
UPDATE trans SET money = money - 30000 WHERE name = '신동엽';
-- 작업 2: 서장훈 계좌로 30,000원 입금
UPDATE trans SET money = money + 30000 WHERE name = '서장훈';

-- 확인 (아직 DB에 영구 반영되지 않은 상태)
SELECT * FROM trans;

-- 모든 작업이 성공했으므로 COMMIT
COMMIT;
-- 만약 중간에 실패했다면 ROLLBACK; 을 실행하여 모든 변경을 취소

-- [2] SAVEPOINT 사용 예제
START TRANSACTION;

UPDATE trans SET money = money - 10000 WHERE name = '신동엽';
SAVEPOINT step1; -- 저장 지점 1

UPDATE trans SET money = money - 10000 WHERE name = '서장훈';
SAVEPOINT step2; -- 저장 지점 2

-- 확인
SELECT * FROM trans;

-- step1 시점으로 롤백 (서장훈의 변경 내역만 취소됨)
ROLLBACK TO step1;

-- 확인 (신동엽의 변경 내역만 남아있음)
SELECT * FROM trans;

COMMIT;

3.3. 연습 문제

1. products 테이블(상품명, 재고량)과 orders 테이블(주문번호, 상품명, 주문수량)이 있다고 가정합니다.
2. '노트북' 2개를 주문하는 트랜잭션을 작성해보세요. 이 트랜잭션은 다음 두 작업으로 구성됩니다.
   • orders 테이블에 새로운 주문 내역을 INSERT.
   • products 테이블에서 '노트북'의 재고량을 2만큼 UPDATE (감소).
3. 만약 재고량이 부족하여 UPDATE가 실패할 경우, INSERT된 주문 내역도 함께 취소(ROLLBACK)되어야 합니다. 성공 시에는 COMMIT합니다.

---

4. 뷰 (View)

뷰는 하나 이상의 기본 테이블에서 유도된 가상의 테이블입니다. 실제 데이터를 저장하지 않고, 뷰를 정의하는 SELECT 쿼리만 저장했다가 뷰에 접근할 때마다 쿼리를 실행하여 결과를 보여줍니다.

4.1. 개념 정리

• 목적:
  1. 보안: 사용자에게 테이블의 특정 열이나 행만 노출시켜 민감한 데이터를 숨길 수 있습니다.
  2. 단순성: 복잡한 JOIN이나 집계 함수가 포함된 쿼리를 뷰로 만들어두면, 사용자는 간단한 SELECT 문으로 결과를 조회할 수 있습니다.
  3. 재사용성: 자주 사용되는 쿼리를 뷰로 저장하여 재사용성을 높입니다.
• 종류:
  • 수정 가능한 뷰: 단일 테이블 기반의 단순한 뷰. INSERT, UPDATE, DELETE가 가능할 수 있습니다.
  • 읽기 전용 뷰: JOIN, 집계 함수(GROUP BY, COUNT 등), DISTINCT 등을 포함한 뷰. 대부분 수정이 불가능하고 조회만 가능합니다.

4.2. 예제 코드 (2_View.sql 기반)

-- 예제용 테이블
CREATE TABLE member (mno INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), grade VARCHAR(20));
CREATE TABLE orders (ono INT AUTO_INCREMENT PRIMARY KEY, mno INT, product VARCHAR(50), price INT, FOREIGN KEY (mno) REFERENCES member(mno));
INSERT INTO member (name, grade) VALUES ('유재석', 'VIP'), ('강호동', 'GOLD'), ('신동엽', 'SILVER');
INSERT INTO orders (mno, product, price) VALUES (1, '노트북', 1500000), (1, '마우스', 30000), (2, '키보드', 50000);

-- [1] 뷰 생성 (CREATE VIEW)
-- VIP 등급 회원만 보여주는 뷰
CREATE OR REPLACE VIEW vip_members AS
SELECT mno, name
FROM member
WHERE grade = 'VIP';

-- 뷰 조회 (일반 테이블처럼 사용)
SELECT * FROM vip_members;

-- [2] JOIN을 사용한 뷰 생성
-- 회원 이름과 주문한 상품, 가격을 함께 보여주는 뷰
CREATE OR REPLACE VIEW member_orders AS
SELECT m.name, m.grade, o.product, o.price
FROM member m
JOIN orders o ON m.mno = o.mno;

-- 복잡한 JOIN 쿼리 대신 간단히 뷰 조회
SELECT * FROM member_orders WHERE grade = 'VIP';

-- [3] 뷰 목록 확인
SHOW FULL TABLES WHERE TABLE_TYPE = 'VIEW';

-- [4] 뷰 삭제 (DROP VIEW)
DROP VIEW IF EXISTS vip_members;

4.3. 연습 문제

1. orders 테이블에서 가격이 100,000원 이상인 주문만 보여주는 expensive_orders 뷰를 만들어보세요.
2. member와 orders 테이블을 JOIN하여, 각 회원의 이름과 그 회원의 총 주문 금액(SUM)을 보여주는 member_total_spending 뷰를 만들어보세요. (힌트: GROUP BY 사용)
3. 생성한 member_total_spending 뷰를 조회하여 총 주문 금액이 가장 높은 회원을 찾아보세요.