[혼공학습단 7기] 6주차 미션 : Chapter 07~08

혼공학습단/혼자 공부하는 SQL

[혼공학습단 7기] 6주차 미션 : Chapter 07~08

so0_0 2022. 2. 26. 09:00

혼공학습단 7기 마지막 미션,,,!!!

우선 한빛미디어와 혼공족장님께 감사드린다고 말씀드리고 싶어요!

한빛미디어에서 제공하는 유튜브 강의로부터 많은 도움을 받았어요!

혼공족장님께서 매주 응원을 해주셔서 포기하지 않고 끝까지 미션을 완주할 수 있었어요!

그럼 마지막 6주차 미션은 과연 무엇일까요??

[기본 미션]
p. 363 - market_db의 고객 테이블(member)에 입력된 회원의 정보가 변경될 때 변경한 사용자, 시간, 변경 전의 데이터 등을 기록하는 트리거 작성하고 인증샷
[선택 미션]
p. 402 - GUI 응용 프로그램 만들고 인증샷

[기본 미션]은 Chapter 07 스토어드 프로시저, [선택 미션]은 Chapter 08 SQL과 파이썬 연결에 대한 내용을 담고 있는 문제이다.

바로 미션에 대한 설명을 넘어가겠습니다!

[기본 미션]

[기본 미션]에 대한 인증샷!(p. 363~p. 366 트리거 활용)

✓ 트리거: 테이블에 입력/수정/삭제되는 정보를 백업하는 용도로 활용할 수 있음

[1단계] 회원 테이블의 아이디(mem_id), 이름(mem_name), 인원(mem_number), 주소(addr) 4개의 열로 구성된 가수 테이블(singer)로 복사함

▷ CREATE TABLE ~(SELECT ~) 문: 테이블을 복사해서 새로운 테이블을 만들어줌(단, 기본 키 정의는 복사되지 않음)

USE market_db;
CREATE TABLE singer (SELECT mem_id, mem_name, mem_number, addr FROM member);

SELECT * FROM singer;

[2단계] 가수 테이블(singer)에 INSERT나 UPDATE 작업이 일어나는 경우, 변경되기 전의 데이터를 저장할 백업 테이블(backup_singer)을 미리 생성함

CREATE TABLE backup_singer
( mem_id     CHAR(8) NOT NULL,
  mem_name   VARCHAR(10) NOT NULL,
  mem_number INT NOT NULL,
  addr       CHAR(2) NOT NULL,
  modType    CHAR(2), -- 변경된 타입, '수정' 또는 '삭제'
  modDate    DATE, -- 변경된 날짜
  modUser    VARCHAR(30) -- 변경한 사용자
);

SELECT * FROM backup_singer;

[3단계] 변경(UPDATE)과 삭제(DELETE)가 발생할 때 작동하는 트리거를 singer 테이블에 부착하기

[3-1단계] 변경(UPDATE)이 발생했을 때 작동하는 singer_updateTrg 트리거 만들기

▷ OLD 테이블: UPDATE나 DELETE가 수행될 때, 변경되기 전의 데이터가 잠깐 저장되는 임시 테이블

OLD 테이블에 UPDATE 문이 작동하면 이 행에 의해서 업데이트되기 전의 데이터가 백업 테이블(backup_singer)에 입력됨(즉, 원래 데이터 보존)

▷ CURDATE(): 현재 날짜, CURRENT_USER(): 현재 작업 중인 사용자

DROP TRIGGER IF EXISTS singer_updateTrg;
DELIMITER $$
CREATE TRIGGER singer_updateTrg -- 트리거 이름
    AFTER UPDATE -- 변경 후에 작동하도록 지정
    ON singer -- 트리거를 부착할 테이블
    FOR EACH ROW
BEGIN
    INSERT INTO backup_singer VALUES(OLD.mem_id, OLD.mem_name, 
    OLD.mem_number, OLD.addr, '수정', CURDATE(), CURRENT_USER());
END $$
DELIMITER ;

[3-2단계] 삭제(DELETE)가 발생했을 때 작동하는 singer_deleteTrg 트리거 생성

DROP TRIGGER IF EXISTS singer_deleteTrg;
DELIMITER $$
CREATE TRIGGER singer_deleteTrg -- 트리거 이름
    AFTER DELETE -- 삭제 후에 작동하도록 지정
    ON singer -- 트리거를 부착할 테이블
    FOR EACH ROW
BEGIN
    INSERT INTO backup_singer VALUES(OLD.mem_id, OLD.mem_name, 
    OLD.mem_number, OLD.addr, '삭제', CURDATE(), CURRENT_USER());
END $$
DELIMITER ;

차이점	트리거 지정	변경된 타입
singer_updateTrg 트리거	UPDATE 트리거로 지정	'수정'으로 입력
singer_deleteTrg 트리거	DELETE 트리거로 지정	'삭제'로 입력

[4단계] 데이터 변경하기

[4-1단계] 한 건의 데이터 업데이트하기(UPDATE), 여러 건 삭제하기(DELETE)

▷ UPDATE 문: 테이블의 행 데이터 수정

▷ DELETE 문: 테이블의 행 데이터 삭제

UPDATE singer SET addr = '영국' WHERE mem_id = 'BLK'; -- 수정
DELETE FROM singer WHERE mem_number >= 7; -- 삭제

✓ 백업 테이블(backup_singer) 조회 - 1건 수정, 4건 삭제

SELECT * FROM backup_singer;

[4-2단계] 테이블의 모든 행 데이터 삭제

▷ TRUNCATE TABLE 테이블_이름 문= DELETE FROM 테이블_이름 문: 모든 행 데이터를 삭제함

TRUNCATE TABLE singer;

✓ 백업 테이블(backup_singer) 조회 - 백업 테이블에 삭제된 내용이 들어가지 않음(이유: TRUNCATE TABLE로 삭제 시에는 트리거가 작동하지 않음, DELETE 트리거는 오직 DELETE 문에만 작동함!)

SELECT * FROM backup_singer;

[선택 미션]

[선택 미션]에 대한 인증샷!(p. 402~p. 404 완전한 형태의 GUI 응용 프로그래밍)

✓ GUI: 윈도를 제공함으로써 사용자가 편리하게 데이터베이스에 접근하도록 도와주는 환경(윈도에 그래픽 환경으로 제공되는 화면)

tkinter	파이썬에서 GUI를 만들기 위해 제공되는 라이브러리
라벨(Label)	윈도에 문자를 표현할 수 있는 위젯 - Label(부모윈도, 옵션...) 형식
버튼(Button)	마우스로 클릭하면 지정한 작업이 실행되도록 사용되는 위젯 - Button(부모윈도, 옵션...) 형식
프레임(Frame)	화면을 여러 구역으로 나누는 기능
엔트리(Entry)	입력 상자를 표현하는 기능
리스트 박스(Listbox)	여러 건의 목록을 표현하는 기능

[1단계] 08-1 파이썬 개발 환경 준비: 파이썬 설치하기

[2단계] 08-2 파이썬과 MySQL의 연동: MySQL 워크벤치를 실행해서 '혼공 쇼핑몰 DB (soloDB)' 생성

DROP DATABASE IF EXISTS soloDB;
CREATE DATABASE soloDB;

[3단계] 08-2 파이썬과 MySQL의 연동

[파이썬] 파이썬에서 데이터 입력 및 코드실행

파이썬에서 데이터 입력
→ 1. MySQL 연결하기: 연결자=pymysql.connect(연결 옵션)
→ 2. 커서 생성하기: 커서이름=연결자.cursor()
→ 3. 테이블 만들기: 커서이름.execute("CREATE TABLE 문장")
→ 4. 데이터 입력하기(반복): 커서이름.execute("INSERT 문장")
→ 5. 입력한 데이터 저장하기: 연결자.commit()
→ 6. MySQL 연결 종료하기: 연결자.close()