04. [SQL] 테이블 외의 데이터베이스 개체의 활용

인덱스(Index)

• 데이터베이스 '색인'의 개념
• 쿼리에 응답하는 시간을 단축시켜 데이터베이스의 성능 향상
• 데이터의 양이 많을수록 효과적으로 작용
• 테이블의 열 단위에 생성

참고) 기본 키(PK) 열에는 명시적으로 인덱스를 생성하지 않아도 자동 생성됨.

Q. first_name이 Mary인 사람의 정보를 indextbl에서 찾아라.

select * from indextbl where first_name = 'Mary';​

>> Query cost : 50.75

Q. first_name 열에 인덱스를 생성한 후 위의 쿼리문을 재실행하라.

create index idx_indextbl_firstname on indextbl(first_name);
select * from indextbl where first_name = 'Mary';​

>> Query cost : 0.35 (같은 쿼리문이지만 속도면에서 확실한 개선이 나타남)

뷰(View)

• 실제 행 데이터를 가지고 있지 않은 가상의 테이블
• 실제 테이블에 링크(Link)된 개념
• 뷰를 조회하면 진짜 테이블의 데이터를 조회하는 것과 동일한 결과가 나타남.
• 테이블 중 일부 데이터만 조회할 수 있는 권한을 부여하려고 할 때 사용됨.

Q. membertbl에서 mamberName 과 memberAddress 데이터에만 접근할 수 있는 View를 생성하라.

create view uv_membertbl
	as select memberName, memberAddress
	from membertbl;

select * from uv_membertbl;​

 

저장 프로시저(Stored Procedure)

• MySQL에서 제공해주는 프로그래밍 기능
• 쿼리문을 하나로 묶어 편리하게 사용하는 기능
• 자주 사용하는 쿼리문을 스토어드 프로시저로 만들어 놓으면 쿼리문을 매번 하나하나 수행하는 번거로움을 해결할 수 있음.

Q. membertbl에서 memberName 이 '당탕이'인 사람의 정보를 보여주고 producttbl에서 productName이 냉장고인 제품의 정보를 보여주는 쿼리문을 담은 스토어드 프로시저를 생성하고 호출하라.

DELIMITER //
create procedure myProc()
begin
	select * from membertbl where memberName = '당탕이';
    select * from producttbl where productName = '냉장고';
end //
DELIMITER ;

call myProc();

* 주의) 마지막 DELIMITER와 ; 사이에는 공백이 하나 존재해야 함.

 

트리거(Trigger)

• 테이블에 부착되어 테이블에 INSERT/UPDATE/DELETE 작업이 발생하면 실행되는 코드

Q. membertbl에서 데이터가 삭제되었을 때 삭제된 정보를 저장하는 트리거를 생성하고 실행하라.

create table deletedmembertbl(
	memberID CHAR(8),
    memberName CHAR(5),
    memberAddress CHAR(20),
    deletedDate DATE);
    
DELIMITER //
	create trigger trg_deletedmembertbl
	after delete
	on membertbl
	for each row
begin
	insert into deletedmembertbl
    values (OLD.memberID, OLD.memberName, OLD.memberAddress, CURDATE());
end //
DELIMITER ;

delete from membertbl where memberName = '당탕이';
select * from deletedmembertbl;

 

---

Reference : 이것이 MySQL이다!