[Practice] 뷰, 트랜잭션

1. 뷰

1) 테이블 생성

DROP DATABASE IF EXISTS artist_db; 
CREATE DATABASE artist_db; 
USE artist_db;

DROP TABLE IF EXISTS art_member; 
CREATE TABLE `art_member` ( 
  u_no INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '일련 번호', 
  u_id VARCHAR(20) NOT NULL UNIQUE COMMENT '아이디', 
  u_pass VARCHAR(50) NOT NULL COMMENT '비밀번호', 
  u_name VARCHAR(20) NOT NULL COMMENT '이름', 
  u_age INT UNSIGNED NULL COMMENT '나이', 
  PRIMARY KEY(u_no) 
); 

DROP TABLE IF EXISTS art_board; 
CREATE TABLE art_board ( 
  b_no INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '일련 번호', 
  b_id VARCHAR(20) NOT NULL COMMENT '작성자 아이디', 
  b_subject VARCHAR(100) NOT NULL COMMENT '글 제목', 
  b_contents TEXT NOT NULL COMMENT '글 내용', 
  reg_date DATETIME NOT NULL COMMENT '작성 일시', 
  PRIMARY KEY (b_no) 
);

2) 데이터 삽입

INSERT INTO art_member VALUES ('1', 'bogummy', 'bogobogo', '박보검', 24); 
INSERT INTO art_member VALUES ('2', 'joongki', 's1234', '송중기', 32); 
INSERT INTO art_member VALUES ('3', 'hyekyo', 'hkhk', '송혜교', 36); 
INSERT INTO art_member VALUES ('4', 'parkbo', 'boyoung', '박보영', 27); 
INSERT INTO art_member VALUES ('5', 'parkhj', 'park', '박해진', 35); 
INSERT INTO art_member VALUES ('6', 'ksh', 'shkim', '김수현', 30); 
INSERT INTO art_member VALUES ('7', 'jihyun', 'jenjh', '전지현', 36); 
INSERT INTO art_member VALUES ('8', 'taehee', 'rainth', '김태희', 37); 

INSERT INTO art_board VALUES (1, 'bogummy', '핫뉴스~!!', '보검이왔어요~!!', now()); 
INSERT INTO art_board VALUES (2, 'ksh', '하이염~!', '수현이예요~~', now()); 
INSERT INTO art_board VALUES (3, 'parkhj', '안녕하세요', '반가워요~^^', now()); 
INSERT INTO art_board VALUES (4, 'haekyo', '혜교예요~', '혜교이뻐해주세요~', now()); 
INSERT INTO art_board VALUES (5, 'parkhj', '꼭 읽어주세요~!!', '나 좀 멋진듯~?', now()); 

 

3) 뷰

순서	내용
1	art_board 목록을 나타내는 art_board_list 뷰 생성
2	art_board_list의 구조 확인 및 조회
3	art_board_list 수정 및 조회
4	art_board 데이터 추가 및 art_board_list 조회
5	art_board_list 데이터 추가
6	art_board 데이터 수정 및 art_board_list 조회
7	art_board_list 데이터 수정 및 art_board 조회
8	art_board_list 뷰 삭제

- 실습 쿼리문

/* art_board 목록을 나타내는 art_board_list 뷰 생성 */
CREATE VIEW art_board_list AS
  SELECT b_subject, u_name, reg_date FROM art_board
  JOIN art_member ON b_id = u_id;

/* art_board_list의 구조 확인 */

SHOW TABLES;

DESC art_board_list;

SELECT * FROM art_board_list;

 

/* art_board_list 수정 및 조회 */

ALTER VIEW art_board_list AS

  SELECT b_no, b_subject, u_name, reg_date FROM art_board

  JOIN art_member ON b_id = u_id;

SELECT * FROM art_board_list;

 

/* art_board 데이터 추가 및 art_board_list 조회 */

INSERT INTO art_board VALUES ('', 'parkbo', '보영임당~', '겸둥이보영이에요~!', now());

SELECT * FROM art_board_list;

 

/* art_board_list 데이터 추가 */

INSERT INTO art_board_list VALUES ('', '보영임당~', '박보영', now()); // 문제 발생

 

/* art_board 데이터 수정 및 art_board_list 조회 */

UPDATE art_board SET b_subject='[끝올]혜교에요~' WHERE b_no=4;

SELECT * FROM art_board_list;

 

/* art_board_list 데이터 수정 및 art_board 조회 */

UPDATE art_board_list SET b_subject='읽지마세요!' WHERE b_no=5;

SELECT * FROM art_board;

 

/* art_board_list 뷰 삭제 */

DROP VIEW art_board_list;

 

 

 

2. 트랜잭션

순서	내용
1	트랜잭션 시작
2	데이터 삽입 및 조회
3	SAVEPOINT 지정
4	데이터 삽입 및 조회
5	SAVEPOINT 지점으로 ROLLBACK 후 조회
6	트랜잭션 시작지점으로 ROLLBACK

/* 트랜잭션 시작*/
START TRANSACTION;

 

/* 데이터 삽입 및 조회 */
INSERT INTO art_member VALUES ('9', 'jaesukee', 'jaejae', '유재석', 45);
SELECT * FROM art_member;

 

/* SAVEPOINT 지정 */

SAVEPOINT SP;

 

/* 데이터 삽입 및 조회 */
INSERT INTO art_member VALUES ('10', 'jaesukee2', 'jaejae2', '유재석2', 45);
SELECT * FROM art_member;

 

/* SAVEPOINT 지점으로 ROLLBACK 후 조회 */

ROLLBACK TO SP;

SELECT * FROM art_member;

 

/* 트랜잭션 시작지점으로 ROLLBACK */

ROLLBACK;