DB 생성하기 복습+procedure+view+transcation+function+trigger

포린키 설정

계속 next를 누르면

다이아그램이 완성된다.

use exam11;

#Create
desc members;
INSERT into members VALUES ('hong@gmail.com','12345');

#Read
select*from members;

#Update
UPDATE members SET email  = 'jang@gmail.com' WHERE email = 'hong@gmail.com';

#Delete
DELETE from members WHERE email = 'lim@gmail.com';

 

---

procedure

delimiter //
create procedure test()
	begin 
    select uuid();
    end //
delimiter ;

call test();

delimiter // 구분자

create procedure function 명( )
   begin
   function 정의
   end //

delimiter ; (세미콜론에 꼭 띄여쓰기 있어야 함)

call function 명( );

delimiter //
create procedure insert_product(category int, name varchar(45), price int, amount int)
	begin
		declare uuid varchar(255);
		set uuid = uuid();
        insert into products values (uuid, category, name, price, amount);
    end //
delimiter ;

call insert_product(100, '게토레이', 1500, 5);

select * from products;

 

---

 

desc purchases;
insert into purchases (member_email, product_id)
values ('jang@gmail.com', '2376c601-e3a8-11eb-b848-b42e99166e6e');
select*from purchases;

update purchases set date = now() where member_email = 'jang@gmail.com';
delete from purchases where member_email = 'jang@gmail.com';

---

Subquery

SELECT문의 결과를 마치 데이터 처럼 다루거나 수치처럼 취급해 조건 문에 이용할 수 있다.

use exam11;

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

select*from view_products;

해당 조건문에 해당되는 상품의 갯수를 센다.

delimiter //
create procedure select_products(target_price int)
	begin
		declare cnt int;
        #subquery
		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);

---

View 사용하기

테이블과 같은 데이터는 가지고 있지 않다. 테이블에 대한 SELECT를 가지고 있다. 테이블 대신 뷰를 사용하는 이점은 다음과 같다.

복잡한 SELECT 문을 일일이 매번 기술할 필요가 없다. 필요한 열과 행만 사용자에게 보여줄수 있고, 갱신 시에도 뷰 정의에 따른 갱신으로 한정할 수 있다. 데이터 저장 없이 실현할 수 있다 뷰를 제거 해도 참조하는 테이블은 영향을 받지 않는다.

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

두개의 테이블을 join 해서 정보 찾기

select 문이 길어져서 뷰로 테이블 보기

---

Transaction 사용하기

오토 커밋 체크 해제

 

select @@autocommit;

start transaction;
set autocommit = false; #오토커밋 해제
insert into members values ('jang@gmail.com','234234');

데이터가 들어와있는 것을 확인할 수 있다.

새로운 세션을 열고, 거기서 select를 보면 다른 세션에선 들어간 데이터가 보이지 않는다.

---

Function

Procedure와 비슷하지만 return 되는 값이 있다.

delimiter //
create function getCountOverPrice(targetPrice int) returns int
	begin
		declare cnt int;
        select count(*) into cnt from products 
        where price > targetPrice;
        return cnt;
    end//
delimiter ;

select getCountOverPrice(1000);

 

해당 오류가 뜨면

https://dzzienki.tistory.com/34

MYSQL 함수 생성시 ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration

위 블로그 참고

SET GLOBAL log_bin_trust_function_creators = 1;

SET GLOBAL log_bin_trust_function_creators = 1;
delimiter //
create function getCountOverPrice(targetPrice int) returns int
	begin
		declare cnt int;
        select count(*) into cnt from products 
        where price > targetPrice;
        return cnt;
    end//
delimiter ;

select getCountOverPrice(1000);
select*from products;

함수가 잘 호출된 것을 볼 수 있다.

---

Trigger

테이블에 대해 어떤 처리를 실행하면 이에 반응하여 설정해둔 명령이 자동으로 실행되는 구조.

트리거가 호출 되는 시점
BEFORE: 테이블에 어떠한 처리를 하기 직전에 호출
AFTER: 테이블에 어떠한 처리를 한 직후 호출
OLD.컬럼_이름 : 테이블에 어떠한 처리가 이뤄 지기 직전의 컬럼 값
NEW.컬럼_이름 : 테이블에 어떠한 처리가 이뤄 진 직후의 컬럼 값

테이블 새로 작성

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

select * from log_products;
select * from products;
insert into products  values (uuid(), 100, '바카스', 800, 3);
call insert_product (100, '커피우유', 1200, 1);

product에 데이터를 넣을 때 트리거로 log가 찍히도록 했다.