210714 _ 데이터 프로그래밍 2 (복습 및 시험)

남은 거

 

뷰 작성 (5)

내부 결합 사용 (5)

트랜잭션 사용 (5)

저장함수 사용 (10)

트리거 사용 (10)

 

를 이어서 하겠음

 

---

 

1. 뷰 작성

 

 

 

새로운 쿼리를 만들어서 use를 해준다.

 

뷰는 셀렉트를 가지고있는 가상테이블임

복잡한 SELECT문을 일일이 기술할 필요가 없어진다.

 

 

예를 들어 내가 원하는 것 만

 

 

 

 

셀렉트 해서 볼 수있는데

 

뷰를 써주면 일일이 저렇게 쓸 필요가 없어진다.

 

create view view_products as 
select name, price, amount 
from products;

 

view_products 는 이름

셀렉트  보일 컬럼

프롬 테이블 이름

 

이렇게 해주면

 

이렇게 가상 테이블이 옆에 생기게된다.

 

 

 

이렇게 가상 테이블를 셀렉트해서 볼 수있다.

 

 

use exam11;

#view 생성
create view view_products as 
select name, price, amount 
from products;

#select view
SELECT * FROM exam11.view_products;

조인과 함께

셀렉트 문이 복잡해지면 

뷰로 확인하는 경우가 많다.

 

조인과 같이 사용하면 강력해짐

 

실질적으로 뷰에서는 삽입이 불가능하다

 

-----

 

서브쿼리

 

셀렉트문을 통해서나오는 데이터를 테이블 형식이 아니라 단일 데이터로 다룰 수 있는 것

 

서브쿼리 카운터 - 현재 특정 같이 얼마 있는지 확인하는 것

 

 

카운터 테스트 

데이터를 좀 추가해주엇따.

 

select count(*) from products where amount > 1;

 

 

어마운트가 1 이상인 캉누터를 찾을 수있다 .

 

 

 

select count(*) as upper_1000_count from products where amount > 1;

 

as 이름

 

을 붙여주면 내가 원하는 이름으로 열을 정해줄수있다.

 

이 카운터를 변수에 넣어서 쓸 수 있다. (이게 좋은 이유)

 

 

delimiter //
create procedure select_products(target_price int)
	begin
		declare cnt int;
        #서브쿼리 
		select count(*) into cnt
        from products where price > target_price;    
        #분기 
        IF cnt > 0 THEN
			select name, price, amount from products
            where price > target_price;
        END IF;
    end//
    delimiter ;
    
    
call select_products(1000);

 

cnt 변수를 만들고

터겟 프라이스도 보다 많은 프라이스 가진 숫자를 cnt에 넣어주고

 

만약 cnt  갯수가 0 이상이면 (존재한다면) 

select ' name과 price와 amount 를 ' from 'products 의 테이블에서 '

타겟 가격보다 높은 가격을 꺼내서 보여준다.

 

 

프로시져로 만들어서

 

안에 서브쿼리를 사용해서 target _ price 이상 것들을 표시하게 한다.

 

 

 

 

----

 

결합

 

2개의 테이블에서 필요한 열을 가지고 올 때 행을 결합하기 위한 조건을 ON으로 지정한다.

 

 

 

purchases 와 products 를 조인해서 출력할 것이다.

 

두 개가 다 합쳐진걸로보인다.

 

일단 select 부분에 * 이라서 지금은 전체가 나온다..

 

purchases.member_email 과

 

products.name 과

 

purchases.date 만 출력하게 할 수도있다.

 

이제 이거를 뷰로 보면 되게 효과적이다. 매번 입력 안해도되서

 

 

 

위에 create view 이름 as 를 달아주면 

 

생성이 되고 

 

 

이제 뷰만 보면 저 위에를 다 치지 않아도 어디서든 볼수가 있다.

 

뷰와 조인

create view view_purchases as
select purchases.member_email, products.name, purchases.date
from products
inner join purchases
on products.id = purchases.product_id;



select * from view_purchases;

 

------

 

트랜젝션

 

복수 쿼리를 한 단위로 묶는 것이 트랜젝션이라고ㅑ함

셀렉트하고 인서트하고 따로따로 있는걸 하나로 묶어서 사용하는 것

 

 

커밋

트랜잭션의 샐행결과를 데이터 베이스에 반영하는것

롤백

데이터베이스에 반영하지않고 원래 상태로 되돌리는것

 

원자성

데이터의 변경(인서트, 딜리트,업데이트)을 수반하는

일련의 데이터 조작이 전부 성공할지 전부 실패 할지 보증하는 구조

(항공원, 영화관 등등 예약 시스템에 사용 되는 구조)

- 즉 결제가 되고 나서야 한번에 데이터가 저장되는것

 

트랜젝션 특징

 

일관성

일련의 데이터 조작 전후에 그 상태를 유지하는 것을 보증하는 것

(사용자에게 임의로 사용자 번호를 주어질때 유니크하게 주어지는 것)

 

고립성

격리성이라고도한다

일련의 데이터 조작을 복수 사용자가 동시에 실행해도

각각의 처리가 모슨 없이 실행되는 것을 보증

(A 사용자와 B 사용자가 동시에 숙박 예약을 하게 되면 생기는 문제

내가 먼저 사용할거야하면, 다른 사용자가 손을 못대게 해야한다)

 

지속성

일련의 데이터 조작을 완료하고 완료 통지를 받는 시점에서

그 조작이 영구적이 되어 그 결과를 잃지 않는것

 

기본적으로 mySQL에서 오토 커밋이 달려있따.

이걸 꺼야한다.

 

 

 

edit - 프리퍼런스 - SQL 익스튜션에서

 

new connection ---

이걸 해체해준다.

그리고 다시 껐다 켜준다.

 

 

 

그리고

 

트랜지션을 걸고 인서트 해준다.

 

일단은 들어갔다.

 

근데 이건 나만 확인 가능한 것 (아직 커밋을 안해서 DB에 안들어감)

 

 

테스트로 다른 곳에서 엷어보게음

 

홈에서 localhost를 또 눌러주면 새로운 섹션이 만들어진다.

 

새로운 섹션에서 맴버를 찍어준다.

 

 

그럼 아까 트랜젝션으로 넣은 값은 보이지 않는다. (왜냐면 아직 커밋을 하지 않았기 때문에!)

 

그럼 이전 꺼에서 

 

트랜지션 후 커밋을 해준다.

 

전체 실행해야지 커밋하면 다른 섹션에서 보임

 

---

 

 

테스트 테이블 만들기 

 

cmd 에서

 

접속하기

 

 

 

select * from test; 까지 cmd에서 해준다.

 

 

 

이 섹션에서는 인서트하면

 

들어가있는데

 

 

cmd 에서는 안나온다. 이건 오토 커밋을 꺼서그럼

 

그럼 commit를 해보깔?

 

 

 

커밋하면 됌

 

일단 지워보고 cmd에서 인서트를 하려고한다. 그러면 락이 걸릴듯 

 

 

 

 

먼저 프리퍼런스에서 세이프 업데이트 체크를 풀어야함

 

 

트랜지션으로 test를 삭제를 하면

 

cmd에서 

 

인서트 하려고하면 lock 이 걸린걸 확인할수있음.

 

커밋하지말고 다시 롤배ㅑㄱ을 해준다.

 

 

롤백하면 다시 인서트 제대로 된다.

 

다른것도 테스트

 

이번에는 수정도 해본다.

 

a 섹션에서 트랜지션으로 1아이디를 minquu로 바꿔준다.

 

커밋을 안해서 cmd에서 바꿔줘도 락이 걸려있을 것이다.

 

수정하려하면 락이 걸려있는 걸 확인할 수있다.

 

테스트 했으면 rollback 를 해준다.

 

이렇게 트래지션을 걸면 그 섹션만 먹힌다.

 

트랜지션 사용 안할떄는 오토커밋을 해놓은다.

 

set autocommit = 1;

 

입력해주자.

 

트랜잭션으로 롤백 할 수 있는 경우

DROP DATABASE

DROP TABLE

DROP 

 

드롭은 머리속에서 지워라 ㅋㅋ

 

 

교착상태 deadlock

트랜잭션 A 의 테이블에서 권한을 가지고 있으면

풀리기 까지 다른 트랜잭션에서 접근을 못한다. 

이런 상황이 교착 상태이다.

근데 이게 서로 사이클이 되어버리면 서로 풀리기 까지 계속 대기하는 상태를 교착상태라고한다.

 

----

 

저장 함수

프로시저와 비슷하지면 값을 반환 할 수 있다

 

델리미터 해주고

펑션을 정하고

반환을 무얼 할지 정한다.

 

리턴으로 int 값을 반환하고

서브쿼리로 타겟프라이스보다 높은 프라이스를 cnt에 넣어서 

리턴해준다.

 

반환되는걸 확인 할 수있음

 

 

 

 

-----

 

트리거 

mysql 5.x 이상만 사용가능

테이블에 대해 어떤 처리를 실행하면 이에 반응하여 처리 되는 구조

 

BEFORE : 테이블에 어떠한 처리를 하기 직전에 호출

AFTER : 테이블에 어떠한 처리를 한 직후 호출

 

구조

 

상품등록시 등록된 시간과 상품 ID를 로그로 남기는 테이블 만들어줌

 

 

 

트리거는  프로덕트가 새로 인서트 될때 로기가 남겨지게 하는 것

 

 

 

트리거 확인하는 것

 

두 개 를 넣어본다.

 

 

 

 

넣을때 로그가 들어가는 걸 확인 함

 

 

delimiter //
	create trigger logDateInsertAfter
		after insert on products for each row 
			begin
				insert into log_products (product_id) values (new.id);
            end //
delimiter ;

show triggers;

desc products;

insert into products values (uuid(), 100, '박카스', 810, 3);
call insert_product (100, '커피우유', 1200, 1);


select * from log_products;
select * from products;

 

 

 

-----

 

덤프 파일 익스폴트

 

 

 

 

 

덤프 그냥 이름 그대로하고 제출하기