Database 실습 3 - Database 툴을 활용한 여러가지 함수 및 프로시저

1. alias

: 사용하기 쉽게 하기위한 별명.

select m.name as 이름
	, m.mobile_n as 번호
	, m.email as 메일
	, m.password as 비밀번호
from zerobase_member as m
;

---

2. join

: 2개 이상의 테이블을 연결하여 데이터를 출력하는 것. 실무에서 가장 많이 쓰이는 쿼리문의 종류이며, 관계형 DB의 가장 큰 장점이자 핵심기능.

일반적인 경우 PK(프라이머리 키) 값을 연관해 조인하며, 2개 이상 join을 할 경우 1 + 1 먼저 하고나서 다음 테이블을 join함(순차적으로). 이 때 순서는 DBMS가 정함.

• 각 테이블 데이터

  
  

• (Inner) Join (Default)

  select *
  from zerobase_member zm 
  	join zerobase_member_detail zmd 
  		on zm.name = zmd.name and zm.email = zmd.email 
  ;
  

  

• Left Join

  -- LEFT JOIN
  select *
  from zerobase_member zm 
  	left join zerobase_member_detail zmd 
  		on zm.name = zmd.name and zm.email = zmd.email 
  ;
    
  -- LEFT JOIN / 중복 Column 제거
  select zm.*, zmd.live_town, zmd.birthday, zmd.school_type
  from zerobase_member zm 
  	left join zerobase_member_detail zmd 
  		on zm.name = zmd.name and zm.email = zmd.email 
  ;
    
  -- LEFT JOIN / 중복 Column 제거 / Column 순서 내가 원하는대로
  select zm.name, zm.mobile_n, zmd.school_type, zmd.live_town, 
  		zmd.birthday, zm.email, zm.register_date, zm.marketing_yn
  from zerobase_member zm 
  	left join zerobase_member_detail zmd 
  		on zm.name = zmd.name and zm.email = zmd.email 
  ;
  

  
  
  

• Full Join

  -- FULL JOIN // 데이터가 그냥 다 섞여버리네....
  select *
  from zerobase_member zm 
  	join zerobase_member_detail zmd 
  		-- on zm.name = zmd.name and zm.email = zmd.email 
  ;
  

  

• 내장함수

  1. 문자열 함수
  -- 문자열 함수 1
  select zm.name, zm.password 
  	, concat(substring(password, 1, 2), '**') as password_mask
  	, length(password) as password_length
  from zerobase_member zm 
  ;
    
  -- 문자열 함수 2
  select zm.name, zm.password 
  	,
  	case 
  		when length(password) > 2 then concat(substring(password, 1, 2), '**')
  		else ''
  	end as password_mask
  from zerobase_member zm 
  ;
  

  
  

  2. 날짜 함수
  -- 날짜 함수 1
  select zm.name, zm.register_date 
  	, date_format(register_date, '%Y.%m.%d') as dt_format
  from zerobase_member zm 
  ;
    
  -- 날짜 함수 2
  select '20220701' as dt_String
  	, str_to_date('20220701', '%Y%m%d') as dt_date
  	, date_add(str_to_date('20220701', '%Y%m%d'), interval 1 month) as dt_date2
  from dual
  ;
  

  
  

• 사용자 함수

  -- function 생성
  create function sf_password_mask(password varchar(255))
  	returns varchar(255)
  begin
    	
  	return 
  		case
  			when length(password) > 2 then
  				concat(substring(password, 1, 2), '**')
  			else
  				concat('****')
  		end;
    	
  end;
    
  -- function 삭제
  drop function sf_password_mask;
    
  -- function 사용
  select password, sf_password_mask(password) as password_mask
  from zerobase_member zm 
  ;
  

  

---

3. 페이징 처리

: 페이징 처리. 데이터를 한꺼번에 모두 볼 수 없기 때문에 페이징 처리를 함.

-- 페이징 처리 / limit a, b : a부터 b개씩
select *
from zerobase_member zm
limit 1, 1
;

---

4. View 테이블

: 쉽게, select만 가능한 테이블이라고 생각할 수 있음.

• 독립성(뷰테이블을 사용하면 응용프로그램을 변경하지 않아도 됨)
• 편리성(복잡한 쿼리를 뷰테이블을 사용하여 간단하게 작성할 수 있음)
• 보안성(표시하지 않아야 하는 컬럼의 경우 숨기기 용이함)

-- View table
create view v_member as

select zm.*, zmd.live_town, zmd.birthday , zmd.school_type 
from zerobase_member zm 
	join zerobase_member_detail zmd on zm.name = zmd.name and zm.email = zmd.email 
;

select *
from v_member vm 
;

---

5. 프로시저(procedure)

: 함수와는 다르게 리턴값이 있기 보다는 그저 어떠한 작업을 하게 하는 것.

-- procedure 생성
create procedure sp_select_member()
begin
	
	select *
	from zerobase_member zm ;

end;

-- procedure 삭제
drop procedure sp_select_member;

-- procedure 사용
call sp_select_member();

---

6. 트리거(trigger)

: 특정 조건이 만족하면 자동으로 실행되는 일종의 프로시저. 트리거는 '테이블'에 장치를 해둘 수 있음.

• insert 작업: 어떤 것이 진행된 후에 트리거 동작 가능.
• update 작업: 어떤 것이 진행되기 전, 후에 모두 트리거 동작 가능.
• delete 작업: 어떤 것이 진행되기 전에만 트리거 동작 가능.

-- trigger 생성
-- zerobase_member이 변경될 때 업데이트 되는 트리거
create trigger tg_member_mobile_no_history
	before update on zerobase_member
	for each row 
	begin 
		insert into zerobase_member_history 
		(
			name,
			email,
			mobile_n,
			update_date
		)
		values
		(
			old.name,
			old.email,
			old.mobile_n,
			now()
		);
	end;
	

select *
from zerobase_member zm 
;

select *
from zerobase_member_history zmh 
;

update zerobase_member 
set
	mobile_n = '01098761234'
where name = '홍길동'
	and email = 'test2@naver.com'
;

---

마지막 수정일시: 2022-07-12 00:26