[Day 23] Database 0427 - 내용 정리

🌞rownum

• 오라클에서 내부적으로 생성되는 가상 컬럼으로 sql 조회 결과의 순번을 나타낸다.
• select 결과에 행번호를 매겨주는 속성

예시) '대한민국'에 거주하는 고객의 이름과 주소를 출력합니다. 출력결과에 행 번호를 붙입니다.

select rownum, name, address 
from customer
where address like '대한민국%';

예시) '대한민국'에 거주하는 고객의 이름과 주소를 행번호를 붙여 앞에서 2명만 출력

select rownum no, name, address
from customer
where address like '대한민국%' and rownum <= 2;

예시) 고객번호로 내림차순으로 정렬하여 고객번호와 이름을 순번을 붙여 출력

select rownum, custid, name 
from (select custid, name 
			from customer 
			order by custid desc);

==> order by 한 쿼리문을 from절 안에 넣어주면 order by가 먼저 실행하게 된다.

🌞부속질의(subquery)

• 하나의 sql문 안에 포함되는 또 다른 sql문이 중첩된 질의를 말한다.
• 다른 테이블에서 가져온 데이터로 현재 테이블에 있는 정보를 찾거나 가공할 때 사용한다.
• 보통 데이터가 대량일 때 데이터를 모두 합쳐서 연산하는 조인보다 필요한 데이터만 찾아서 공급해주는 부속질의가 성능이 더 좋다.
• 주질의(main query, 외부질의)와 부속질의(sub query, 내부질의)로 구성된다.

🌈select절(스칼러 서브쿼리)

• select 절에 컬럼이름이 오는 곳에 사용되는 서브쿼리를 말한다.
• 단일행이 오도록 표현해야 한다.

예시) 고객이름 별로 판매액을 출력

select (select name 
			from customer c 
			where c.custid = o.custid) name, sum(saleprice) total
from orders o
group by custid;

==> 서브쿼리에 조건식이 메인쿼리와 조건식이 있어서 join이라고 생각할 수 있는데 이 경우 조인이라고 하지 않고 '상관 서브쿼리' 라고 한다.

🌈from절 (인라인 뷰)

• 서브쿼리가 from절에서 사용되는 부속질의
• 실제로 존재하지 않는 select한 결과를 가상의 테이블(View)라고 한다.

예시) 고객번호가 2 이하인 고객들에 대하여 고객 이름별로 총 판매액을 출력

select name, sum(saleprice)
from (select custid, name
		from customer 
		where custid <= 2) c, orders o
where c.custid = o.custid
group by name;

🌈where절 (중첩쿼리)

다중행 연산자에 대하여 설명하세요.
===> 서브쿼리가 where절에 사용될 때에 서브쿼리의 건수가 여러 건일 떄 사용하는 연산자이면 [in, not in, all, some, any] 등이 있다.

<단일 연산자>
예시) '대한민국'에 거주하는 고객에게 판매한 도서의 총 판매금액을 출력

select sum(saleprice)
from orders
where custid = (select custid 
					from customer
					where name = '박지성');

==> 이 때 서브쿼리의 건수는 1건이기 때문에 = 연산자를 사용할 수 있다.

<다중행 연산자>

select sum(saleprice)
from orders
where custid in (select custid 
					from customer
					where address like '%대한민국%');

==> 서브쿼리의 건수가 여러 건이기 때문에 = 연산자는 사용할 수 없고, in 연산자를 사용해야 한다.

🍁in 연산자

• 서브쿼리의 건수가 여러 건일 때 = 을 대신하여 사용

🍁all, any(some) 연산자

• 서브쿼리의 건수가 여러 건일 때 비교연산자 > < >= <= 을 표현할 때 같이 사용

예시)

select orderid, saleprice
from orders
where saleprice > all (select saleprice from orders where custid = 3);

🍁exists 연산자

• exists: 서브쿼리의 건수가 있으면 메인쿼리를 동작
• not exists: 서브쿼리의 건수가 없으면 메인쿼리를 동작

예시) exists 연산자를 이용하여 대한민국에 거주하는 고객에게 판매한 도서의 총 판매액을 출력

select sum(saleprice)
from orders o
where exists (select * from customer c
				where address like '%대한민국%' and c.custid = o.custid);

🌞뷰(View)

• 하나 이상의 테이블을 합하여 만든 가상의 테이블
• 실제로 존재하지 않는 가상의 테이블
• 자주 사용하는 복잡한 sql을 미리 뷰를 만들어 두어 편리하게 사용할 수 있다.
• 또, 사용자별로 접근할 수 있는 컬럼만 추려 뷰를 만들어 두면 보안유지상 중요한 정보를 보호할 수 있다.

<기본문법>

create view 뷰이름 [(열이름 [,...])]
as select문

예제) '축구' 관련 도서를 검색하는 뷰를 생성

create view vw_book
as select * from book where bookname like '%축구%';

• 뷰를 통해 추가, 수정, 삭제가 가능하다. 하지만, 뷰 생성 시에 설정된 조건과 맞지 않은 값으로 추가, 수정, 삭제하면 데이터는 뷰에 나타나지 않는다.

🌈with check option

• 뷰 생성 시에 사용한 조건식에 맞는 레코드만 추가, 수정하도록 하기 위한 옵션

<기본문법>

create view 뷰이름 as select절
조건식 with check option;

예시) with check option을 넣어 뷰 생성하기

create view vw_emp_20 as 
select eno, ename, dno, phone, addr
from emp
where dno = 20
with check option;

• with check option을 설정한 뷰의 테이블에서 뷰 생성 시에 설정된 조건과 맞지 않은 값으로 추가, 수정하게 되면 조건에 위배된다는 오류가 발생하게 된다.

🌈with read only

• 조회만 가능한 뷰의 생성

<기본문법>

create view 뷰이름 as select with read only;

• 읽기전용 뷰에서 DML 작업을 할 수 없다.