DBMS(6)- 서브 쿼리(Sub query)와 뷰(View)

*이 글을 읽기전에 작성자 개인의견이 있으니, 다른 블로그와 교차로 읽는것을 권장합니다.*

1. 서브 쿼리(Sub Query)

서브 쿼리는 다른 쿼리 내부에 포함되어있는 select문을 의미합니다. 서브쿼리를 감싸고 있는 쿼리를 외부쿼리, 서브쿼리는 내부쿼리라고도 불립니다. 서브쿼리는 괄호()를 사용해서 표현합니다. 그밖에 select문에 where, from, having 절에서 사용할 수 있습니다.

# 상품코드가 '00001'의 가격보다 크거나 같은 가격을 가지고 있는 상품의 모든 정보를 출력 -> where중첩 사용
select * from product where price >= (select price from product where code='00001');

# product 테이블에서 모든 상품의 code, name, price, 가장비싼가격 을 모두 출력
select code, name, price '가장비싼가격' from product;
select code, name, price, (select max(price) from product roduct) as max_price from product;

where조건절안에 서브쿼리를 사용, code열의 value값이 00001일때 해당되는 price열만 출력

product table에서 code, price, (price열에서 가장 비싼가격)을 열로 출력

create table orders(
	no int auto_increment primary key,
    userid varchar(20) not null,
    product_code varchar(5) not null,
    cnt int default 0,
    regdate datetime default now(),
    foreign key(userid) references member(userid)	#references는 기본키-참조키 제약조건 걸때 사용하는 키워드
);

insert into orders values (1,'apple', '00005', 1, null);
insert into orders values (10,'banana', '00004', 1, null);
-- insert into orders values (10 ,'apple', '00003', 2, null); # 중복된 no
insert into orders (userid, product_code, cnt) values ('apple', '00003',2);
select * from orders;

insert into orders (userid, product_code, cnt) values ('banana', '00002',1);
insert into orders (userid, product_code, cnt) values ('orange', '00004',1);
insert into orders (userid, product_code, cnt) values ('avocado', '00003',1);
insert into orders (userid, product_code, cnt) values ('cherry', '00001',1);

# 상품을 최소 2번이상 구입한 횟수가 있는 회원의 아이디와 이름, 성별을 출력
select userid, name, gender from member where userid in 
(select userid from orders group by userid having count(no) >=2);

userid, name, gender열을 출력합니다. where조건절에 orders table에서 no열의 value값이 2이상인 userid열만 그룹화하여 출력한 userid에서만 출력하는것으로 조건을 걸었습니다.

# 상품을 최소 2번이상 구입한 아이디의 앞 2글자와 이름, 상품 구입 횟수를 출력
# join
select left(m.userid,2), m.name, count(o.no) as cnt from member as m inner join orders as o 
on m.userid = o.userid group by m.userid having cnt >= 2;

# 서브쿼리
select left(m.userid,2), m.name, sub.ocnt from member as m inner join 
(select userid, count(no) as ocnt from orders group by userid having count(no) >=2) as sub
on m.userid = sub.userid;

 

cnt열의 value값 2이상인 userid열을 그룹화하였습니다. member table은 m으로, orders table은 o로 alias시켰으며, 그룹화한 자료에서 해당되는 member table의 userid열의 value값을 왼쪽에서 2자리 글자만 열로 뽑고, name열, no열은 해당되는 숫자만 int형으로 출력합니다.

create table orders_new(
	no int auto_increment primary key,
    userid varchar(20) not null,
    product_code varchar(5) not null,
    cnt int default 0,
    regdate datetime default now(),
    foreign key(userid) references member(userid)
);

select * from orders_new;
insert into orders_new(select * from orders);	#value값에 orders 대입

create table orders_new_new (select*from orders);
select *from orders_new_new;

2. 뷰(View)

뷰(View)는 원하는 데이터를 선택하여 가상의 테이블처럼 보여주는 기능입니다. 실제 테이블처럼 행렬을 갖고 있지만, 데이터를 직접 저장하고 있지 않습니다. 주로 SQL코드 간결화, 데이터 삽입, 삭제, 수정 작업에 대한 제한 사항을 가질 때, 내부 데이터를 전체 공개하고 싶지 않을 때 사용합니다.

사용형태: create view view_이름 as 쿼리문;

View를 사용하는 이유는 웹에서 데이터시트를 가져와서 가상의 테이블처럼 보여줍니다.

select * from member;
select userid, name, hp, gender from member;
create view vw_member as select userid, name, hp, gender from member;
select * from vw_member;

위 코드로 실행된 vw_member view table은 member table로부터 userid, name, hp, gender 열을 가져와 생성됩니다.

# member 테이블의 userid, name, hp와 profile 테이블의 mbti를 가지는 가상 테이블(뷰)를 만들어보자
# vw_member_profile
create view vw_member_profile as select m.userid, m.name, m.hp, p.mbti from member as m
left join profile as p on m.userid = p.userid;
select*from vw_member_profile;

member table에서 userid, name, hp, profile table에서 mbti 열을 가져와 vw_member_profile view table을 만듭니다. inner join 조건문을 사용했습니다.

# 뷰 수정하기
# alter view 뷰이름 as 쿼리문 ...
alter view vw_member_profile as select m.userid, m.name, m.hp, p.mbti from member as m
inner join profile as p on m.userid = p.userid;
select*from vw_member_profile;

Alter을 사용해서 테이블 구조를 변경합니다. 

# 뷰 삭제하기
drop view vw_member_profile;

삭제 확인