19일차 - INDEX, IN, EXISTS, VIEW

INDEX

■ 인덱스는 책의 색인처럼 데이터베이스에서 검색을 빠르게 해주기 위한 목록이다

■ PK 나 FK 가 이미 색인되어있는지 걸려있는지 확인하고, 인덱스를 추가하자

■ 데이터가 많을 경우, JOIN 이 많이 사용되는 경우

□ 데이터가 적을 경우, 삽입, 갱신, 삭제가 빈번하게 일어나는 경우 불필요하다

/* index */

-- 1) 고유 인덱스(UNIQUE INDEX)
-- ★ PK 나 UNIQUE KEY 를 걸어주면 자동으로 생성되는 인덱스이다
-- 인덱스 생성시 중복 데이터를 삽입/추가하면 에러를 발생시킨다
-- CREATE UNIQUE INDEX [인덱스의 이름] ON [테이블의 이름](컬럼)
create unique index emp_ename_idx on emp(ename);
-- SQL Error [1062] [23000]: (conn=17) Duplicate entry 'kim' for key 'emp_ename_idx'
-- 중복 데이터가 존재해서 에러를 발생시킨다 -> 비 고유 인덱스를 사용해야한다
select * from emp;

-- 2) 비고유 인덱스 (NON UNIQUE INDEX)
-- FK(외래키) 생성시 컬럼에 비고유 인덱스가 걸려있다
-- 중복 데이터가 있어도 인덱스 생성이 가능하다
-- CREATE INDEX [인덱스의 이름] ON [테이블의 이름](컬럼)
create index emp_ename_idx on emp(ename);
show index from emp;
-- deptno 는 FK 이기 때문에 비고유인덱스이고
-- ename 은 방금 비고유 인덱스로 설정해주었다

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

-- 4) 인덱스를 확인하는 방법
show index from emp;

-- 5) 인덱스를 삭제하는 방법
-- 인덱스는 테이블의 속성으로 취급해서 alter table 로 없애야한다
-- ALTER TABLE [테이블의 이름] DROP INDEX [인덱스의 이름]
alter table emp drop index emp_ename_idx; 
show index from emp;

ANY

■ 메인쿼리의 비교조건이 서브쿼리의 결과 중에 하나라도 일치하면 참을 반환한다

■ [대상 컬럼] > ANY (서브쿼리) : 대상 컬럼에서 서브쿼리의 결과보다 작은 값 행들을 반환한다

ALL

■ 메인쿼리의 비교조건이 서브쿼리의 결과 중에 모두 일치하면 참을 반환한다

■ [대상 컬럼] < ALL (서브쿼리) : 대상 컬럼에서 서브쿼리의 결과보다 큰 값 행들을 반환한다

IN

■ WHERE 절에 동일한 컬럼에 IN 뒤에 나열한 조건들 중 일치하는 행을 OR 조건으로 조회한다

■ IN 은 '=' 비고만 가능하다, >= > < <= 크고 작음을 비교할 수 없다

■서브쿼리가 먼저 실행되고 그 결과를 가지고 메인 쿼리를 실행한다

/* IN */
-- 문제 1) 부서가 LA 또는 BOSTON 에 있는 직원의 이름과 직책을 한번 출력해보자
select * from emp;
select deptno from dept where loc in ('LA', 'BOSTON');
select ename, job from emp where deptno in (select deptno from dept where loc in ('LA', 'BOSTON'));
select * from dept;

EXISTS

■ 메인쿼리가 먼저 실행되고 나중에 서브쿼리가 실행된다

■ 메인쿼리 실행 후 데이터를 exists 조건이 참이 되면 모든 내용을 보여주고, 참이 아니라면 아무것도 보여주지 않는다

/* exists */
-- dept 테이블에서 loc 컬럼에서 'LA' 또는 'BOSTON' 행이 있다면 참, 아니라면 거짓을 반환한다
select exists (select deptno from dept where loc = 'LA' or loc = 'BOSTON') as bool;
select ename, job, deptno from emp where 
	exists (select deptno from dept where loc = 'LA' or loc = 'BOSTON');

-- 참이 아니라면 아무것도 보여주지 않는다
select * from emp where 0;

-- a) exists 를 서브쿼리와 함께 활용해보자
-- 1) 메인쿼리로 직원들의 필요한 정보를 먼저 확보해둔다
select ename, job, deptno from emp;
-- 2) 서브쿼리와 메인쿼리 간에 공통된 컬럼으로 조건을 가지고 있어, 등가조인에 맞는 행을 반환한다
select ename, job, deptno 
	from emp e where exists (select deptno from dept d where e.deptno = d.deptno);
-- 3) 부서 위치 컬럼에서 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')
);

VIEW

■ 여러 테이블의 테이터들을 모아서 만든 가상의 테이블이다 

■ 복잡한 서브쿼리나 조인을 통해 원하는 정보를 확인했었다면, 뷰를 만들어두면 편리하게 사용할 수 있다

■ 뷰는 자신만의 인덱스를 가질 수 없다 원본 테이블에서 인덱스를 넣어줘야 가질 수 있다

/* VIEW */
-- 1) 뷰 생성 문법
-- CREATE [OR REPLACE] VIEW [뷰의 이름] AS [뷰를 생성할 쿼리]
-- OR REPLACE 는 기존 뷰를 수정하기 위해 사용한다
create view name_query as select e.ename, d.deptname 
	from emp e, dept d where e.deptno = d.deptno;
    
-- 2) 뷰 확인
-- show create view [뷰 이름]
-- 어떤 구조로 뷰를 만들었는지 확인할 수 있다
show create view name_query;
-- 어떤 뷰가 있는지 항목만 보여준다
show full tables where table_type = 'VIEW';

-- 3) 뷰 사용
select * from name_query;
-- 뷰에서 수정을 가하면 원본 테이블의 데이터도 수정된다
update name_query set ename = 'oh' where ename = 'kim';
-- 원본 테이블에서 확인
select * from emp;
select * from dept;

-- 4) 뷰 수정
-- 가상 테이블이기 때문에 alter 로도 수정가능하다 일반적으로 OR RELACE 를 사용한다
-- 있으면 수정하고, 없으면 만들기 때문에 목적을 분명히 하는게 좋다!
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;

-- 5) WITH CHECK OPTION 
-- 뷰를 생성한 조건식을 만족하는 컬럼에 대해서는 수정을 막는 옵션이다
create or replace view chk_option as select ename, job, deptno from emp where deptno = 1 with check option;
select * from chk_option;
-- 부서번호가 1번인 직원들을 보여주는 뷰 테이블이다
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';

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

 

AUTO_INCREMENT

■ 중복을 허용하지 않고, 반드시 값이 삽입된 그리고 테이블 종속적이므로 PK 가 될 수 있다

■ 행이 추가될때마다 1씩 증가하여 고유의 번호를 부여한다

LIMIT & OFFSET

■ 전체 데이터를 가져오지 않고, 가져올 갯수와 대상을 정해서 일부분만 가져오는 작업을 페이징이라고 한다

■ 리미트는 페이징 처리 작업을 할 때 주로 사용된다

/* limit */
-- limit n : n개 보여주기
select * from employees limit 5; 
-- limit n, m : n번 부터 m개
select * from employees limit 0, 5;
select * from employees limit 5, 5;
select * from employees limit 10, 5;
-- offset n : n번 부터 
select * from employees limit 5 offset 0;
select * from employees limit 5 offset 5;
select * from employees limit 5 offset 10

 

FUNCTION

■ COUNT(컬럼의 이름) : 컬럼을 기준으로 검색된 행의 갯수를 반환한다

■ MAX(컬럼의 이름) : 가장 큰 값을 반환한다

■ MIN(컬럼의 이름) : 가장 작은 값을 반환한다

■ AVG(컬럼의 이름) : 행들의 값들을 모두 더해 평균을 반환한다

■ SUM(컬럼의 이름) : 행들의 값들을 모두 더한 값을 반환한다 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

-- 메일 또는 구두로 통해서 내려오는 지시사항
-- 특히나 수정이나 삭제의 지시사항을 메일로 한번만 보내달라고 해야한다