CODO Day35_DB(In&Exists/Any&All/Index/View)

In&Exists

• IN : '=' 비교만 가능, OR 조건의 검색결과 내용을 가져옴

select ename, job from emp e where deptno in(2,4);

-- 서브쿼리가 먼저 동작된 후 메인쿼리가 동작
select ename, job from emp e where deptno 
	in(select deptno from dept where loc = 'LA' or loc = 'boston');

 

• EXISTS : 메인쿼리 실행 후 서브쿼리 조건을 비교
  • 해당 결과가 있는지 1과 0을 반환
  • 결과값 1 = 데이터를 보여줌 / 결과값 0 = 보여주지 않음 / 결과값 0&1 = 1인 데이터만 보여줌
  • 메인쿼리가 먼저 실행된 후 서브쿼리 실행
  • EXISTS가 IN 보다 사용은 복잡하지만 속도가 빠름
  • IN은 조건을 추출하고 그 조건으로 전체 데이터를 검색하는 반면
  • EXISTS는 메인쿼리를 통해 데이터를 확보해놓고, 서브쿼리의 조건으로 걸러내기 때문

select exists(select deptno from dept where loc = 'LA' or loc = 'boston') as yn;

 

-- exists의 결과 값이 1이면 데이터를 보여줌
select ename, job, deptno from emp where 1;

 

-- 결과값이 1인 데이터만 보여줌
select ename, job, deptno from emp where exists (select deptno from dept where loc = 'LA' or loc = 'boston');

 

-- exists의 결과 값이 0이면 데이터를 보여주지 않음
select ename, job, deptno from emp where 0;
select ename, job, deptno from emp where exists (select deptno from dept where loc = 'korea');

 

• 사용방법

-- 1) 메인쿼리가 먼저 실행 
select ename, job, deptno from emp;
-- 2) 가져온 데이터가 조건에 맞는지 하나씩 비교
-- dept와 emp의 공통되는 컬럼이 있으므로 이 둘을 하나씩 맞춰가는 것임
select ename, job, deptno from emp e where exists (select deptno from dept d where e.deptno = d.deptno);
-- 3) 결과적으로 확보한 데이터에 loc = 'LA' or loc='boston' 조건을 추가해줌
select ename, job, deptno from emp e where exists (select deptno from dept d where e.deptno = d.deptno 
and (loc = 'LA' or loc = 'boston'));

 

Any&All

• = ANY : IN과 비슷(OR 조건 사용) + 부등호 사용 가능

select ename, job from emp e where deptno = any (select deptno from dept where loc = 'LA' or loc = 'boston');

 

• > ANY : 최솟값보다 크면

-- 직책이 manager인 직원의 입사일이 가장 빠른 사람보다 늦은 직원의 이름, 직책, 입사일
select ename, job, hiredate from emp 
	where hiredate > any (select hiredate from emp e where job = 'manager') 
order by hiredate;

 

• < ANY : 최대값보다 작으면

-- 직책이 manager인 직원의 입사일이 가장 늦은 사람보다 빠른 직원의 이름, 직책, 입사일
select ename, job, hiredate from emp 
	where hiredate < any (select hiredate from emp e where job = 'manager') 
order by hiredate;

 

• ALL : ANY와 비슷(부등호를 쓸 수 있음)
  • = ALL : AND 조건 사용(같은 조건의 값이 복수개로 오면 사용할 수 없음)

-- loc가 'newyork'인 사람 중 deptno가 1이고 3인 사람이 존재할 수 없기 때문에 값x
-- 조건의 값이 하나로 가능
select ename, job from emp where deptno 
= all(select  deptno from dept where loc = 'newyork');
-- 조건의 값이 복수개로 불가능
select ename, job from emp where deptno 
= all(select  deptno from dept where loc = 'newyork' or loc = 'LA');

 

-- 조건을 복수개로 주는 것은 가능
select ename, job from emp where deptno = all(
select deptno from dept where loc = 'newyork' and deptname = 'sales');

 

• > ALL : 최대값보다 크면

-- 직책이 manager인 직원의 입사일이 가장 늦은 사람보다 더 늦은 직원의 이름, 직책, 입사일
select ename, job, hiredate from emp 
	where hiredate > all (select hiredate from emp e where job = 'manager') 
order by hiredate;

 

• < ALL : 최솟값보다 작으면

-- 직책이 manager인 직원의 입사일이 가장 빠른 사람보다 더 빠른 직원의 이름, 직책, 입사일
select ename, job, hiredate from emp 
	where hiredate < all (select hiredate from emp e where job = 'manager') 
order by hiredate;

 

 

 

INDEX

• index(색인)은 검색시간을 단축시킴
• Primary key와 Unique index로 설정하면 자동으로 index가 생성됨
• 장점 : 검색 속도가 빨라짐, 시스템의 부하를 줄여 성능이 향상됨
• 단점 : index 생성에 시간과 공간 소요, 삽입/갱신/삭제가 빈번할 경우 성능 저하
• index가 있으면 좋은 경우 : 데이터가 많을 경우(찾기 쉬움), JOIN이 많이 사용된 경우
• index가 필요 없는 경우 : 데이터가 적을 경우, 삽입/갱신/삭제가 빈번한 경우

 

• UNIQUE INDEX
  • 중복되지 않은 데이터에 INDEX를 걸어줌(중복값 넣으면 에러남)
  • Primary Key, Unique Index를 설정하면 자동으로 index 생성(중복 허용하지 않기 때문)
  • CREATE UNIQUE INDEX [index name] ON [table](column)

select * from emp;  -- 중복값 보유
-- Error : Duplicate entry 'kim' for key 'emp_ename_idx'
create unique index emp_ename_idx on emp(ename);

 

• NON-UNIQUE INDEX
  • 중복된 데이터에 INDEX를 걸어줌

create index emp_ename_idx on emp(ename);

 

• 결합 INDEX : 여러개의 컬럼에 index 설정
  • CREATE [UNIQUE] INDEX [index name] ON [table](column,...)

create index emp_combi_idx on emp(ename,job,deptno); 

-- 인덱스 확인(인덱스 데이터 사전)
show index from emp;

 

• 인덱스 삭제(속성 취급)
  • ALTER TABLE [table name] DROP INDEX [index name];

alter table emp drop index emp_combi_idx;
alter table emp drop index emp_ename_idx;

 

 

 

VIEW

• 여러 테이블의 데이터를 모아서 만든 하나의 가상 테이블
• 복잡한 Query를 통해 얻을 수 있는 결과를 간단한 Query로 얻을 수 있게 함
• 복잡한 Join이나 Sub Query를 이용한 데이터를 뷰로 만들어두면 편리하게 볼 수 있음
• 한 개의 뷰로 여러 테이블에 대한 데이터를 검색 할 수 있음
• 보안성이 좋음(원본 노출X)
• 통계 낼 때 많이 사용
• 자신만의 인덱스를 가질 수 없음

 

• 뷰 생성 문법
  • CREATE [OR REPLACE] VIEW [view name] AS [query];
  • OR REPLACE : 기존 뷰를 수정할 때 사용 / create 할 때 같이 작성하기도 함

create view vw_name as select e.ename, d.deptname from emp e join dept d on e.deptno = d.deptno;

 

• 뷰 사용(일반 테이블과 같음)

select * from vw_name;

 

• 뷰 수정
  • 뷰에서 수정하면 원본 테이블의 데이터도 수정됨

update vw_name set ename = 'oh' where ename = 'kim';
-- but 등가조인에 의해서 뷰를 생성할 때 deptno 6인 kim은 해당되지 않아서 update 안됨

 

• create or replace : 해당 뷰가 있으면 수정, 없으면 생성
  
  • CREATE [OR REPLACE] VIEW [view name] AS [query];
  • ALTER로도 수정 가능하나 Join을 사용하여 복잡해지므로 create or replace 사용

create or replace view vw_name as
	select e.ename, d.deptname, d.loc from emp e join dept d on e.deptno = d.deptno;

 

• with check option : 뷰를 생성한 조건식을 만족하는 컬럼은 update 할 수 없도록 하는 옵션
  • 절대 바뀌지 않는 내용을 조건으로 걸 때 사용

create view vw_chk as 
	select ename,job,deptno from emp where deptno = 1 with check option;

-- job 수정 가능
update vw_chk set job = 'manager' where ename = 'lee';

-- deptno 수정 불가능 : 뷰 생성시 조건으로 걸었기 때문
-- with check option이 없었다면 deptno가 2로 변경되면 'lee'는 뷰에서 빠짐
-- CHECK OPTION failed `mydb`.`vw_chk`
update vw_chk set deptno = 2 where ename = 'lee';

 

• 생성된 뷰 정보 조회

show full tables where table_type = 'VIEW';  -- 내가 만든 뷰가 얼마나 있나
show create view vw_name;  -- 특정 뷰에 대한 정보(뷰를 생성한 쿼리문)

 

• 뷰 삭제

drop view vw_name;
drop view vw_chk;