04. INDEX, EXISTS, VEIW

1. INDEX

 

 

index(색인) 은 검색을 빠르게 하기 위한 수단이다.

Primary key 와 Unique key 가 지정되면 따로 index 를 생성된다.

 

 

-- 색인 : 검색을 빠르게 해주기 위한 목록
-- 예) 가나다 순으로 각 단어의 위치를 알려주는 목록
-- 그렇다 보니 단어를 추가/삭제 할 때 인덱스도 추가/삭제 해주어야 한다.

-- 1) 고유 인덱스 (UNIQUE INDEX)
-- 중복되지 않는 데이터에 대해서만 설정되는 인덱스
-- PK 나 유니크 키를 걸어주면 자동으로 생성되는 인덱스
-- 인덱스 생성 시 중복데이터가 있으면 에러 발생
-- CREATE UNIQUE INDEX [인덱스 이름] ON [테이블 명](컬럼);
create unique index emp_ename_idx on emp(ename);
select * from emp;

-- 2) 비고유 인덱스 (NON UNIQUE INDEX)
-- FK 생성 시 이 인덱스가 걸려 있다.
-- 중복데이터가 있어도 인덱스 생성 가능
-- CREATE INDEX [인덱스 이름] ON [테이블 명](컬럼);
create index emp_ename_idx on emp(ename);


-- 3) 결합(복합) 인덱스
-- 복합키 처럼 여러 컬럼을 조합해서 인덱스 생성이 가능 (16개까지 가능)
-- CREATE [UNIQUE] INDEX [인덱스 이름] ON [테이블 명](컬럼, ...);
create unique index emp_combi_idx on emp(ename, job, deptno);


-- 4) 인덱스 확인 방법
-- SHOW INDEX FROM [테이블 명];
show index from emp;


-- 5) 인덱스 삭제
-- 인덱스는 테이블 속성으로 취급
-- ALTER TABLE [테이블 명] DROP INDEX [인덱스 이름];
alter table emp drop index emp_ename_idx;

 

 

 

 

 

2. EXISTS

 

 

 

/*IN & EXISTS*/
-- 부서가 LA 또는 BOSTON 에 있는 직원의 이름과 직책
select deptno from dept where loc in('la','boston');
select ename, job from emp where deptno in ('2','4');

-- 1. IN : '=' 비교만 가능(크다, 작다 비교 안됨), OR 조건의 검색 결과 내용을 가져온다.
-- 서브쿼리가 먼저 실행되고 그 결과를 가지고 메인 쿼리를 실행 한다.
select ename, job from emp where deptno in (select deptno from dept where loc in('la','boston'));

-- 2. EXISTS : 메인쿼리의 비교 조건이 서브쿼리의 결과 중에서 '만족하는 값이 하나라도 존재 한다면' 참(1)/거짓(0)으로 반환
-- 메인쿼리가 먼저 실행되고, 이후 서브쿼리가 실행 된다.
select exists (select deptno from dept where loc = 'LA' or loc = 'BOSTON') as bool;

-- 메인쿼리 실행 후 데이터를 exists 조건이 참이 되면 모든 내용을 보여주고
select ename,job,deptno from emp where 
	exists (select deptno from dept where loc = 'LA' or loc = 'BOSTON');

select * from emp where 1;

-- 참이 아니라면 아무것도 보여주지 않는다.
select ename,job,deptno from emp where 
	exists (select deptno from dept where loc = 'korea');

select * from emp where 0;


--  위 특성을 가지고 어떻게 써먹을 것인가?
-- 1) 메인쿼리가 실행되어 데이터를 확보하고
select ename, job, deptno from emp;

-- 2) 확보한 데이터로 부터 dept 와 연결 해 준다.
-- 양쪽 둘 다 있는 데이터만 가져옴
-- 아까 같이 왜 모든 데이터를 보여주지 않는가?(6번은 안보여 줬음)
-- 메인 쿼리와 서브쿼리 간에 공통된 컬럼을 조건으로 걸고 있어서
select ename, job, deptno 
	from emp e where exists (select deptno from dept d where e.deptno = d.deptno);

-- 3) 걸러낸 녀석들 중에서 dept 에서 loc 가 la 이거나 boston 인 녀석만 골라내면 된다.
select ename, job, deptno 
	from emp e where exists (
		select deptno from dept d where e.deptno = d.deptno and (d.loc = 'boston' or d.loc = 'la')
	);


select ename, job from emp where deptno in (select deptno from dept where loc in('la','boston'));

/* IN 이 EXISTS 보다 사용은 쉽지만 속도는 느리다.
 * SQL 에서는 데이터를 확보해 놓고 다음작업을 진행하는것이 빠르기 때문이다.
 * EXISTS 는 메인쿼리를 통해 데이터를 확보하고, 서브쿼리 내용으로 일치하는 내용을 보여주기 때문에 속도가 더 빠르다.
 * */


/*ANY & ALL*/
-- 3. ANY : 메인 쿼리의 비교조건이 서브쿼리의 결과중에 하나라도 일치하면 참
-- IN 처럼 쓸 수도 있고, 크다/작다를 활용 할 수 있다.
-- 1) = ANY : IN 과 같은 효과 
select ename, job from emp where deptno in (select deptno from dept where loc in('la','boston'));
select ename, job from emp where deptno = any (select deptno from dept where loc in('la','boston'));

-- 직책이 manager 인 직원중 입사일이 가장 빠른 사람보다, 늦게 입사한 사람의 직책과 입사일
select min(hiredate) from emp where job = 'manager'; -- 2015-08-12
select job,hiredate from emp where hiredate > '2015-08-12' order by hiredate;

select job,hiredate from emp where hiredate > (select min(hiredate) from emp where job = 'manager') order by hiredate;
-- 2) > ANY : 최소값 보다 크면
select job,hiredate from emp where hiredate > any (select hiredate from emp where job = 'manager') order by hiredate;


-- 직책이 manager 인 직원중 입사일이 가장 늦은 사람보다, 빨리 입사한 사람의 직책과 입사일
select job,hiredate from emp where hiredate < (select max(hiredate) from emp where job = 'manager') order by hiredate;
-- 3) < ANY : 최대값 보다 작으면
select job,hiredate from emp where hiredate < any (select hiredate from emp where job = 'manager') order by hiredate;

-- 4. ALL : ANY 와 반대
-- 1) = ALL : = ANY 와 같지만 AND 조건 이다.
select * from dept;
select deptno from dept where loc = 'boston';
-- 서브쿼리 안의 값이 여러개가 나타날 경우 사용하면 안된다.
select ename, job from emp where deptno  = all(select deptno from dept where loc = 'boston');

-- 2) > ALL : 최대값 보다 크면
-- 직책이 manager 인 직원중 입사일이 가장 늦은 사람보다, 더 늦게 입사한 사람의 직책과 입사일
select max(hiredate) from emp where job = 'manager'; -- 2016-01-02
select job,hiredate from emp where hiredate > '2016-01-02';

select job,hiredate from emp where hiredate > (select max(hiredate) from emp where job = 'manager');

select job,hiredate from emp where hiredate > all (select hiredate from emp where job = 'manager');

-- 3) < ALL : 최소값보다 작으면
-- 직책이 manager 인 직원중 입사일이 가장 빠른 사람보다, 더 빨리 입사한 사람의 직책과 입사일
select min(hiredate) from emp where job = 'manager'; -- 2015-08-12
select job,hiredate from emp where hiredate < '2015-08-12';

select job,hiredate from emp where hiredate < (select min(hiredate) from emp where job = 'manager');

select job,hiredate from emp where hiredate < all (select hiredate from emp where job = 'manager');

 

 

 

 

 

3. VIEW

 

 

VIEW 는 하나의 가상 테이블이라 생각 하면 된다.

VIEW는 복잡한 Query를 통해 얻을 수 있는 결과를 간단한 Query로 얻을 수 있게 한다.

한 개의 뷰로 여러 테이블에 대한 데이터를 검색 할 수 있다.

 

 

-- 1) 뷰
-- 여러 테이블의 데이터를 모아서 만든 가상 테이블 이다.
-- 복잡한 서브쿼리나 조인이 필요한 쿼리문을 한번만 사용해서 뷰를 만들어 놓으면 이후 편리하게 사용 할 수 있다.
-- 뷰는 자신만의 인덱스를 가질 수 없다.
-- 뷰에 인덱스를 넣고 싶다면 컬럼의 원본 테이블에서 인덱스를 넣어야 한다.

-- 2) 뷰 생성 문법
-- CREATE [OR REPLACE] VIEW [뷰이름] AS [뷰를 생성할 쿼리]
-- OR REPLACE : 기존 뷰를 수정할때 사용 한다.
create view name_query as 
	select e.ename, d.deptname from emp e join dept d 
		on e.deptno = d.deptno;
	
-- 3) 뷰 확인
-- show create view [뷰이름]	
show create view name_query; -- 구성 쿼리도 보여줌
show full tables where table_type = 'VIEW'; -- 항목만 보여줌

-- 4) 뷰 사용 : 테이블 사용과 같음
select * from name_query;

-- 뷰에서 수정을 가하면 원본 테이블의 데이터도 수정 된다.
update name_query set ename = 'oh' where ename = 'kim';
select * from emp;
-- kim 이 남아이유는 6번의 경우 등가조인의 조건에 맞지 않아 뷰에 포함되지 않았기 때문에 뷰에서 수정 했을때 영향을 받지 않았다.

-- 5) 뷰 수정
-- ALTER로도 수정 가능 하지만 일반적으로 OR REPLACE 를 사용 한다.
-- 있으면 수정하고, 없으면 만들기 때문에
create or replace view name_query as
	select e.ename, d.deptname, d.loc from emp e join dept d on e.deptno = d.deptno;

select * from name_query;


-- 6) WITH CHECK OPTION : 뷰를 생성한 조건식을 만족하는 컬럼에 대해서는 수정을 막는 옵션
create view chk_option as select ename,job, deptno from emp where deptno = 1 with check option;
select * from chk_option;
-- 일반적인 수정은 모두 가능 하다.
update chk_option set job = 'manager' where ename = 'lee';

-- 뷰를 만든 조건인 deptno 는 수정 불가능 하다.
update chk_option set deptno = '2' where ename = 'lee';
-- 변경해 주고 싶다면 원본 테이블에서 수정해야 한다.
update emp set deptno ='2' where ename = 'lee' and job = 'manager';
select * from emp;

-- 7) 뷰 삭제
-- DROP VIEW [뷰이름]
drop view chk_option;
drop view name_query;

show full tables where table_type='VIEW';