MySQL 고급기능

“MySQL(또는 MariaDB) 의 외래키 사용한 Relationship, join, transaction, stored procedure, view, trigger 등 고급기능을 정리했습니다. 실습 위주의 설명으로 따라 하며 진행하면 쉽게 개념을 이해할 수 있습니다.


1. 샘플 DB 및 테이블 생성
 1) SERVER 라는 DB 에 서버 제원과 용도 IP 등을 관리하는 3개의 테이블을 생성합니다.
– hardward : 제조사, 서버모델, 스펙 테이블
– server : 서버의 OS, 서버명, 용도 테이블
– ip : 서버에 할당된 IP 관리하는 테이블
2) 쿼리


2. Relationship 준비사항
 1)  위 1에서는 테이블 구조만 생성된 상태입니다.
– 테이블간 relation을 hardware – server를 부모-자식 (1:n), server – ip를 부모 – 자식(1:n) 관계로 지정하겠습니다.

2) foreign key 추가 시 주의사항
– 부모 테이블과 자식 테이블은 InnoDB 여야 합니다.
– 자식 테이블이 참조하는 키는 부모 테이블의 키와 자료형이 같아야 합니다.
– 부모 테이블의 UNIQUE 한 키만 자식 테이블이 참조할 수 있습니다.
– 자식 테이블에 참조하는 키는 인덱스 설정되었거나 UNIQUE 해야합니다.

3) 4가지 모드
– RESTRICT : 개체를 변경/삭제할 때 다른 개체가 변경/삭제할 개체를 참조하고 있으면 변경/삭제가 취소됩니다.(제한)
– CASCADE : 개체를 변경/삭제할 때 다른 개체가 변경/삭제할 개체를 참조하고 있으면 함께 변경/삭제됩니다.
– NO ACTION : 개체를 변경/삭제할 때 다른 개체가 변경/삭제할 개체를 참조하고 있으면 변경/삭제할 개체만 변경/삭제되고 참조하고 있는 개체는 변동이 없습니다.
– SET NULL : 개체를 변경/삭제할 때 다른 개체가 변경/삭제할 개체를 참조하고 있으면 참조하고 있는 값은 NULL로 세팅됩니다.

3. Relationship 맺기
1) server 테이블의 hardware_id를 인덱스설정

2) server 테이블의 foreign key 지정하여 hardware 와 Relation

3) ip 테이블의 server_id 컬럼에 인덱스 설정

4) ip 테이블에 foreign key 지정하여  server 와 relation

5) phpmyadmin 의 디자이너 기능으로 ERD 를 보면 다음과 같습니다.

4. 데이타 입력 및 테스트
1) 쿼리 테스트를 위한 샘플 데이타를 입력합니다.

2) hardware 테이블 Delete 테스트

* RESTRICT 모드로 자식테이블 server 와 관계를 맺고있고 자식테이블에 데이타가 있기 때문에 1451 에러 발생시키며 삭제불가

3) server 테이블 Delete 테스트

* CASCADE 모드로 자식테이블 ip 와 관계를 맺고있어 server.id=2 에 해당하는 행과 ip.server_id=2 에 해당하는 행 모두 삭제됨

4) 쿼리 테스트를 위해 4-1) 에서 삭제된 행을 다시 입력합니다.

5. union, sub query, join
1) union
* union 은 2개 이상의 테이블에 대한 합집합 연산 입니다.
union 구문을 사용하기 위해서는 컬럼의 개수가 같아야 합니다.
union 만 사용하면 중복값은 출력되지 않으므로 모두 출력하려면 union all 을 사용해야 합니다.

2) intersect (교집합) 와 minus (차집합)

3) sub query

4) join

6. transaction
* 여러 단계의 처리를 하나의 처리처럼 다루는 기능을 트랜잭션(transection)이라고 합니다. 트랜잭션의 실행 결과를 데이터베이스에 반영하는 것을 커밋(commit)이라 하고, 반영하지 않고 원래 상태로 되돌리는 것을 롤백(rollback)이라고 합니다.
트랜잭션을 사용하려면 스토리지 엔진이 InnoDB 이며 자동 커밋 기능이 Off 되어 있어야 합니다.

7. stored procedure
* stored procedure 저장 프로시저란? 일련의 절차를 저장해 두었다는 의미로 정의된 쿼리문의 묶음 등을 CALL 하여 사용할 수 있습니다. 저장 프로시저를 사용하면 반복된 작업을 줄일 수 있고 개발 언어에 비의존적이며 프로시저별 권한 부여 등으로 보안성을 높일 수 있습니다.
1) 프로시저 생성

2) 프로시져 상태 및 상세보기


8. view
* view 뷰란? 사용자가 원하는 조건으로 원하는 컬럼을 모아 데이타를 추출할 수 있는 기능입니다.
1) 뷰 생성

2) 뷰 상세보기


9. trigger

* trigger 트리거란? 테이블의(ON 테이블명) 값이 변경되는 이벤트(INSERT, UPDATE, DELETE) 발생 전/후(BEFORE/AFTER) 에 미리 정의된 일련의 작업을 실행하는 것 입니다.

FOR EACH ROW 문은 FOR 구문으로 이해하면 되며 트리거가 적용된 테이블에 여러행의 작업이 일어날 경우

트리거가 적용된 테이블의 변경 전후값을 넘겨주고 싶으면 OLD.컬럼명 NEW.컬럼명을 사용합니다.

1) 로그 입력용 테이블 생성

2) ip 테이블에 DELETE 가 발생하면 event_log 에 로그 남기는 트리거

3) ip 테이블에서 row 를 삭제하면 tr_delete_ip 트리거에 의해 event_log 에 로그 기록 됩니다.

답글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다.