MySQL 기초(trigger)- AWS 풀스택 과정 35일차

오늘은 trigger에 대해서 배워보겠습니다.

 

목차
1.	join 복습
2.	trigger
3.	느낀 점

 

1. join 복습

DB - day05

DB 생성 = college 생성
root에서 DB 생성 / 권한 부여 (myUser@localhost)

ERD Cloud 사이트에서 ERD 그리기

테이블 생성

1. root 계정으로 접속
mysql -uroot -p비밀번호

2. create database college; => db 생성
create database school;

3. myUser 권한 부여 => 권한 확정
grant all privileges on college. * to 'myUser'@'localhost' with grant option;
flush privileges;

4. myUser 계정으로 접속
mysql -umyUser -pMySQL
use college;
exit;

5. 테이블 생성 및 외래키 지정
start transaction;

create table professor(
pr_num int not null,
pr_name varchar(20) not null,
pr_age int not null,
pr_room varchar(45) not null,
pr_state varchar(40) default '재직',
pr_position varchar(40) default '조교수',
primary key(pr_num));

create table subject(
su_num int,
su_code varchar(20) not null,
su_title varchar(45) not null,
su_point int not null default 0,
su_time int not null default 0,
primary key(su_num));

create table student(
st_num int,
st_name varchar(20) not null,
st_term int not null default 1,
st_point int not null default 0,
primary key(st_num));

create table course(
co_num int not null,
co_pr_num int not null,
co_su_num int not null,
co_term int default 1,
co_year int,
co_timetable varchar(100),
primary key(co_num),
foreign key(co_pr_num) references professor(pr_num),
foreign key(co_su_num) references subject(su_num));

create table guide(
gu_num int not null,
gu_pr_num int not null,
gu_st_num int not null,
gu_year int,
primary key(gu_num),
foreign key(gu_pr_num) references professor (pr_num),
foreign key(gu_st_num) references student(st_num));

create table attend(
at_num int not null auto_increment,
at_st_num int not null,
at_co_num int not null,
at_mid int default 0,
at_final int default 0,
at_hw int default 0,
at_attend int default 0,
at_score varchar(10),
at_pass varchar(1) default 'f',
at_repetition varchar(1) default 'n',
primary key(at_num),
foreign key(at_st_num) references student(st_num),
foreign key(at_co_num) references course(co_num));


-- 외래키 연결이 되어있는 테이블 삭제
1. 외래키 삭제
alter table 테이블명 drop foreign key 외래키명
1-1. 외래키 명 확인 
select * from information_schema.table_constraints where table_name = '테이블명';
2. 테이블 제거
drop table 테이블명
3. 테이블 재생성
create table...
4. 외래키 생성
alter table professor add constraint [제약조건명] foreign key (칼럼명)
references [부모테이블명] (칼럼명)

-- 외래키 연결이 되어있는 테이블 수정
alter table professor add pr_room varchar(45) not null after pr_age;

6. DB 자료
insert into student(st_num, st_name,st_term) values
(2020123001,'김영철',1),
(2020123002,'나영희',1),
(2020160001,'강철수',2),
(2020160002,'박철수',2),
(2020456001,'강군',2);

insert into subject values
(1,'msc001','대학수학',3,3),
(2,'com001','컴퓨터개론',2,2),
(3,'com002','운영체제',3,3),
(4,'abc001','디자인이론',2,2),
(5,'abc002','생활영어',2,3);

insert into professor values
(2005789001, '홍길동',60,'B동302호','재직','정교수'),
(2006456001, '박영실',65,'B동301호','안식년','정교수'),
(2010160001, '강길동',55,'A동202호','재직','조교수'),
(2010160002, '김길순',50,'A동301호','재직','조교수'),
(2011123001, '이순신',55,'A동203호','재직','정교수');

insert into course(co_num, co_pr_num, co_su_num, co_term, co_year, co_timetable) values
(1, 2005789001,1,1,2002,'월2a/2b/3a/3b/4a/4b'),
(2, 2010160002,1,2,2002,'월2a/2b/3a/3b/4a/4b'),
(3, 2010160001,2,1,2002,'화1a/1b/2a/2b'),
(4, 2010160001,3,1,2002,'목2a/2b/3a/3b/4a/4b'),
(5, 2011123001,4,2,2002,'화1a/1b/2a/2b'),
(6, 2011123001,5,2,2002,'수1a/1b/2a/2b');

insert into guide(gu_num, gu_pr_num, gu_st_num, gu_year) values
(1, 2010160001,2020160001,2023),
(2, 2010160001,2020160002,2023),
(3, 2011123001,2020123001,2023),
(4, 2011123001,2020123002,2023),
(5, 2010160002,2020456001,2023);

insert into attend(at_st_num, at_co_num) values
(2020123001,1),
(2020123001,2),
(2020123001,3),
(2020123001,4),
(2020123001,5),
(2020123001,6),
(2020123002,1),
(2020123002,2),
(2020123002,3),
(2020123002,4),
(2020123002,5),
(2020160001,1),
(2020160001,2),
(2020160001,3),
(2020160001,4),
(2020160001,5),
(2020160001,6),
(2020160002,1),
(2020160002,2),
(2020160002,3),
(2020160002,4),
(2020160002,5),
(2020160002,6),
(2020456001,1),
(2020456001,2),
(2020456001,3),
(2020456001,4),
(2020456001,5);

commit;
--------------------------------------------------
1. 김영철이 수강하는 과목명을 출력
st_name, su_title => 이름(student st), 과목명(subject su)

select u.su_title as '김영철의 수강 과목' from student s
inner join attend a on s.st_num = a.at_st_num
inner join course c on a.at_co_num = c.co_num
inner join subject u on c.co_su_num = u.su_num
where s.st_name = '김영철';
--------------------------------------
-- 서브 쿼리 형태(SQLD 시험에 자주 나오는 형태)
SELECT
st_name,
(
  select su_title from subject s
  where s.su_num = c.co_su_num
) as su_title
FROM
  student st
  join attend a on a.at_st_num = st.st_num
  join course c on c.co_num = a.at_co_num
WHERE
  st.st_name = '김영철';

2. 강길동 교수가 지도하는 학생명 출력
select s.st_name from professor p
inner join guide g on p.pr_num = g.gu_pr_num
inner join student s on g.gu_st_num = s.st_num
where p.pr_name = '강길동';
----------------------------------------
-- 서브 쿼리 활용
select
(
  select st_name from student st
  where st_num = g.gu_st_num
) as '강길동교수 지도학생'
from
  guide g
  join professor p on p.pr_num = g.gu_pr_num
where
  p.pr_name = '강길동';

3. 대학수학 과목을 수강하는 수강자 명단 출력(중복제거)
select distinct s.st_name as '대학수학 수강자 명단' from subject u
inner join course c on u.su_num = c.co_su_num
inner join attend a on c.co_num = a.at_co_num
inner join student s on a.at_st_num = s.st_num
where u.su_title = '대학수학';
------------------------------------
-- 서브 쿼리 활용
select distinct
(
  select st_name from student st
  where st_num = a.at_st_num
) as'대학수학 수강자 명단'
from
  attend a
  join course c on c.co_num = a.at_co_num
  join subject u on u.su_num = c.co_su_num
where
  u.su_title = '대학수학';

----------------------------------------------------
1. at_mid, at_final, at_attend, at_hw 값 업데이트

update attend set at_mid=40, at_final=40, at_attend=10, at_hw=9
where at_num in (1,9,15);
update attend set at_mid=38, at_final=35, at_attend=8, at_hw=7
where at_num in (2,4,10,5,6);
update attend set at_mid=30, at_final=28, at_attend=5, at_hw=7
where at_num in (3,11,20, 25, 27);
update attend set at_mid=28, at_final=25, at_attend=6, at_hw=10
where at_num in (12,21,19);
update attend set at_mid=12, at_final=20, at_attend=2, at_hw=6
where at_num in (7,16,24);
update attend set at_mid=25, at_final=19, at_attend=7, at_hw=5
where at_num in (8,13,22);
update attend set at_mid=38, at_final=35, at_attend=7, at_hw=6
where at_num in(23, 26, 14, 17, 18);
update attend set at_mid=9, at_final=3, at_attend=5, at_hw=8
where at_num = 28;

2. at_mid + at_final + at_attend + at_hw 합계 값을 이용하여 at_score 업데이트
>= 90 A / >= 80 B / >=70 C / >= 60 D / F
start transaction;

update attend
set at_score = case
when at_mid + at_final + at_attend + at_hw >= 90 then 'A'
when at_mid + at_final + at_attend + at_hw >= 80 then 'B'
when at_mid + at_final + at_attend + at_hw >= 70 then 'C'
when at_mid + at_final + at_attend + at_hw >= 60 then 'D'
else 'F'
end;

3. at_pass 값 업데이트 (p/f)
at_score가 A 또는 B이면 p(pass) 아니면 f(fail)

update attend
set at_pass = case
when at_score = 'A' or at_score = 'B' then 'p'
else 'f'
end;

4. at_repetition 값 업데이트 (y/n)
at_score가 F이거나, at_attend가 3이하인 자료는 y 아니면 n

update attend
set at_repetition = case
when at_score = 'F' then 'y'
when at_attend <= 3  then 'y'
else 'n'
end;

------------------------------------------------------
1. 학점별 학생 수 출력(2002년 1학기 자료만 사용)
A B C D E F G 의 학생수
at_score, count(st_score)
조건 co_year = 2002, co_term = 1

SELECT a.at_score as '학점', count(a.at_score) as '인원수'
FROM attend a
join course c
on c.co_num = a.at_co_num
where c.co_year = 2002 and c.co_term = 1
group by a.at_score
order by a.at_score;
-------------------------------------
SELECT a.at_score as '학점', count(a.at_score) as '인원수'
FROM attend a
join
(select co_num from course where co_year=2002 and co_term=1) as c
on c.co_num = a.at_co_num
group by a.at_score
order by a.at_score;

2. 김영철이 수강하는 과목 출력
대학수학(1) => su_title(co_term)
대학수학(2) => concat
attend, student, subject, course

select concat(su_title, '(', co_term,')') as '김영철 수강과목'
from attend a 
join student on at_st_num = st_num
join course on at_co_num = co_num
join subject on co_su_num = su_num
where st_name = '김영철';

-----------------------------------------------------
index : 인덱스
데이터 베이스에서 조회할 때 결과를 빠르게 추출하도록 도와주는 키
기본키가 아닌 일반 키에 대해 조회를 많이 하면
인덱스 추가를 고려해볼 수 있음
- 추가적인 DB 공간이 필요(10%)
- 변경작업(insert, update, delete)이
  빈번히 일어난다면 성능 저하를 불러올 수 있음

-------------------------------------------------
student 테이블에 st_name에 인덱스 추가
create index 인덱스명 on 테이블명(칼럼명);

create index idx_name on student(st_name);

alter table student drop index idx_name;

--------------------------------------------------
view 생성
가상 테이블
create view view_name as
(서브쿼리로 뷰 내용 검색)

1. 1학기 수업을 듣는 학생명단 => 1_term_view
학번, 이름 조건 1학기 수강자 co_term = 1
중복제거

create view 1_term_view as
select distinct st_num, st_name
from student s
join attend a on a.at_st_num = s.st_num
join course c on a.at_co_num =c.co_num
where c.co_term =1;

view 확인
select * from 1_term_view;

show tables; => 내 db의 테이블 확인
show full tables; => 테이블 종류 확인 가능

=> view 내용 변경 (변경x)

create or replace view 1_term_view as
select distinct st_num, st_name
from student s
join attend a on a.at_st_num = s.st_num
join course c on a.at_co_num =c.co_num
where c.co_term =1;

---------------------------------------------
school DB에서 view 생성

1. 컴퓨터공학과(std_major) 뷰 생성 => com_view
 - 학번, 이름, 학과

create view com_view as
select  std_num, std_name
from student
where std_major ='컴퓨터공학과';

2. A학점인 친구들 뷰 생성 a_view
 - 학번, 이름, 학점
create view a_view as
select s.std_num, s.std_name, a.at_score from student s
inner join attend a on s.std_num = a.at_std_num
where a.at_score = 'A';

select * from a_view
where std_num like '2020%';

-----------------------------------------------
left join
-- 수강하지 않는 학생 검색 => 휴학생

student 테이블에 학생을 추가

insert into student values('2023160003','김영이','디자인',2,25);
insert into student values('2023160004','최영이','디자인',2,25);

=> student 테이블에는 존재하지만 attend 테이블에 없는 자료를 추출
select s.* from student s
left join attend a
on std_num = at_std_num;
where a.at_num is null;

 

- ERD

ERD (1)

 

ERD (2)

 

ERD Cloud

 

▷ 출력

출력 (1)

 

출력 (2)

 

출력 (3)

 

출력 (4)

 

출력 (5)

 

출력 (6)

 

출력 (7)

 

출력 (8)

 

출력 (9)

 

출력 (10)

 

출력 (12)

 

출력 (13)

 

출력 (14)

 

출력 (15)

 

출력 (16)

 

출력 (17)

 

출력 (18)

 

출력 (19)

 

출력 (20)

 

출력 (21)

 

2. trigger

------------------------------------------
trigger : 트리거 (연쇄반응)
테이블에 대한 이벤트(insert, update, delete)에 반응하여
자동으로 실행되는 작업
이벤트가 발생했을 때 데이터의 무결성(일관성)을 지키기 위해 사용

use textdb;
product / buy 테이블 이용

트리거 구문
drop trigger if exists 트리거명
delimiter $$ => // 문장의 끝을 수정하는 키워드 ; => $$ //

create trigger 트리거명 after | before 이벤트 on 테이블명
for each row
begin
[트리거 시 실행될 구문 작성]
-- 변수 사용가능. [선언 방법]
declare 변수명 자료형 기본값; _ @ 특별한 기호를 이용하여 변수임을 알림
set 변수명 = 값;

-- new : 이벤트가 발생한 행의 최신 데이터
-- old : 이벤트가 발생하기 전의 데이터 

end $$

delimiter ; => 원래대로 문장의 끝 기호를 수정

---------------------------------------------------------
- product 테이블에 amount(재고량) = 100, sale_amount(판매량) = 0
update product set
amount = 100, sale_amount = 0;

-- buy 테이블에 insert가 되면 buy.amount만큼
product 테이블의 amount - , sale_amount +

drop trigger if exists insert_buy;
delimiter $$
create trigger insert_buy after insert on buy
for each row
begin

  update product set
  amount = amount - new.amount,
  sale_amount = sale_amount + new.amount
  where name = new.product_name;

end $$
delimiter ;

--------------------------------
안정성이 검증된 유저만 트리거, 프로시저, 함수를 사용할 수 있게 off
log_bin_trust_function_creators = off

show global variables like 'log_bin_trust_function_creators';
-- root 계정에서 set
set global log_bin_trust_function_creators = on;

---------------------------------
insert into buy(customer, product_name, price, amount)
values('홍길동', '폴라 티셔츠', 15000, 10);

insert into buy(customer, product_name, price, amount)
values('박순이', '피트니스상의', 30000, 7);

-- trigger 적용 이후 실행 안 됨. error
insert into buy(customer, product_name, price, amount)
select '박순이', name price, 6 from product
where num = 11;

----------------------------------
buy 테이블에 데이터를 삭제하면 amount, sale_amount 변경
트리거 생성
delete 삭제 후 확인

-- 변수 사용
declare 변수명 자료형 기본값;
set 변수명 = 값;

-- 선생님 풀이과정
drop trigger if exists delete_buy;
delimiter $$
create trigger delete_buy after delete on buy
for each row
begin
  declare_amount int default 0;
  set _amount = old.amount;

  update product set
  amount = amount + _amount;
  sale_amount = sale_amount - _amount
  where name = old.product_name;

end $$
delimiter ;

delete from buy where num = 1;
--

--  내 풀이 과정
drop trigger if exists delete_buy;
delimiter $$
create trigger delete_buy after delete on buy
for each row
begin

  update product set
  amount = amount + old.amount,
  sale_amount = sale_amount - old.amount
  where name = old.product_name;

end $$
delimiter ;

delete from buy where customer = 15;
-----------------------------------------------
buy 테이블의 데이터를 업데이트 하면
amount와 sale_amount 값 변경하는 트리거 작성

drop trigger if exists update_buy;
delimiter $$
create trigger update_buy after update on buy
for each row
begin

  update product set
  amount = amount + old.amount - new.amount,
  sale_amount = sale_amount - old.amount + new.amount
  where name = old.product_name;

end $$
delimiter ;

update buy
set amount = 1
where num = 14;

※ trigger

: delimiter (띄워쓰기) 키워드

 

▷ 출력

출력 (1)

 

출력 (2)

 

출력 (3)

 

출력 (4)

 

출력 (5)

 

출력 (6)

 

출력 (7)

 

3. 느낀 점

concat과 같은 명령어를 까먹어서 복습을 꼭 해야겠다고 생각하게 되었다. 자꾸 헷갈리거나 까먹으면 수업에 집중하기가 힘들어지는 것 같았다.