7월24일 - SQL활용 4 : View & Index

VIEW & INDEX

 

Index(색인)
  - 데이터를 빠르게 찾을 수 있는 수단
  - 테이블에 대한 조회 속도를 높여 주는 자료구조
  - PK칼럼은 자동으로 인덱스 생성된다

인덱스생성 create index  인덱스이름
인덱스삭제 drop   index  인덱스이름
인덱스수정 alter   index  인덱스이름

[인덱스 방식]
- full scan
  처음부터 끝까지 일일이 검사하는 방법.
  전수조사

- index range scan
  이름이 여러개인 경우 목차를 찾아서 페이지를 찾아감.
  훨씬 빠름. 별도의 메모리가 있어야 함.

- index unique scan
  학번은 1개만 존재함. 유일한 값

 

인덱스 연습하기

 

1. c_emp 테이블 생성하기

 

drop table c_emp; create table c_emp(   id        number(5)       ,name     varchar2(25)   ,salary   number(7, 2)   ,phone    varchar2(15)   ,dept_id  number(7) ); insert into c_emp(id,name) values (10,'kim'); insert into c_emp(id,name) values (20,'park'); insert into c_emp(id,name) values (30,'hong');

 

2. 인덱스 생성하기 

  - Primary key, Unique제약조건을 만들면 해당 인덱스 페이지가 자동으로 생성된다

 

create index 인덱스명 on 테이블명(칼럼명); create index c_emp_name_idx on c_emp(name); --테이블 뒤에 붙이는 것을 만드는 것

 

***실행계획 보기 : F10 (커서위치 중요!)

 

3. 인덱스 삭제 및 목록보기

 

--인덱스 삭제  drop index c_emp_name_idx; --테이블 뒤에 붙이는 것을 만드는 것  --인덱스 목록보기 select * from user_indexes;

 

인덱스 목록보기

 

4. 제약조건 PK로 emp4 테이블 생성하기 

 

create table emp4(   no      number      primary key   ,name varchar2(10)   ,sal     number );

 

PL/SQL (Procedual Language) 프로시저
  

    : 절차적인 데이터베이스 프로그래밍 언어

 

예) 테스트용! 레코드 100만건 입력하기 

 

1. emp3테이블 생성하기

 

create table emp3(   no      number   ,name varchar2(10)   ,sal     number );

 

2. 선언문 생성하기

 

declare --선언문   1)변수선언   i        number      := 1;      --i변수에 1 대입(:=)연산자   name varchar(20)  := 'kim';   sal     number      := 0;   begin    2) T-SQL문(제어문) for문, while문      -> while문의 ()은 sql에서 loop~end loop; 로 쓴다      -> if문의 {}은 then, else if는 elsif 로 쓴다   while i<=1000000 loop     if i mod 2 = 0 then        name := 'kim' || to_char(i);       sal  := 300;    elsif i mod 3 = 0 then       name := 'park' || to_char(i);       sal  := 400;    elsif i mod 5 = 0 then       name := 'hong' || to_char(i);       sal  := 500;    else        name := 'shin' || to_char(i);       sal  := 250;    end if;        insert into emp3(no, name, sal)    values (i, name, sal);    i := i+1;  end loop; end;   3) 선언문 종결  /

 

loop를 돌렸을 때, 

loop 작업 완료 

 

3. 작업 결과 

 

select count(*) from emp3;

작업이 잘 되었다.

 

4. 예시

  1) 모조칼럼 : rowid (행의 주소값) / rownum (행번호)

 

select * from emp3 where rownum>-1 and rownum<=10;

 

  2) 인덱스를 사용하지 않은 경우

     - full scan, cost 893

select * from emp3 where name = 'kim1108';

 

문제) name 칼럼을 기준으로 인덱스 생성한 후 name 칼럼에서 조회하고 F10 계획결과 확인하세요.

 

  - 인덱스를 사용한 경우

 

create index emp3_name_idx on emp3(name); select * from emp3 where name = 'kim1108' ;

 

 

  - 인덱스 목록확인

 

select * from user_indexes;

 

  - 이름과 급여를 기준으로 인덱스 생성하기

 

create index empe_name_sal_index on emp3(name,sal);

 

--full scan, cost1786 select * from emp3 where no=1108;  --range scan, cost 3 --두개의 인덱스를 조회했을 시에 cost감소를 볼 수 있다 select * from emp3 where name='kim1108';  --full scan, cost 1788 select * from emp3 where sal>200;  --range scan, cost 3 select * from emp3 where name='kim1108' and sal>200;

 

 

CASE WHEN ~ THEN END

 

 : java의 switch~case구문과 비슷함

 

1. 형식

 

CASE WHEN 조건1 THEN 조건만족시 값1        WHEN 조건2 THEN 조건만족시 값2        WHEN 조건3 THEN 조건만족시 값3                ...        ELSE 값 END 결과컬럼명

 

문제) 국어점수에 따라 A, B, C, D, F 학점을 구하시오 

 

select uname, kor, CASE WHEN kor>=90 THEN 'A학점'                                 WHEN kor>=80 THEN 'B학점'                                 WHEN kor>=70 THEN 'C학점'                                 WHEN kor>=60 THEN 'D학점'                                 ELSE 'F학점'                          END as grade from sungjuk;

 

문제) addr칼럼의 주소를 한글로 조회하시오 

 

select uname, addr, CASE WHEN addr='Seoul' THEN '서울'                                   WHEN addr='Jeju' THEN '제주'                                   WHEN addr='Busan' THEN '부산'                                   ELSE '수원'                            END as address from sungjuk;

 

문제) 부서코드 10 경리팀, 20 연구팀, 30 총무팀, 40 전산팀

 

select deptno, ename, CASE WHEN deptno=10 THEN '경리팀'                                      WHEN deptno=20 THEN '연구팀'                                      WHEN deptno=30 THEN '총무팀'                                      WHEN deptno=40 THEN '전산팀'                               END as deptname from emp;

 

문제) 커미션 5이상 '5%' , 4이상 '4%' , 3이상 '3%' , 2이상 '2%' , 나머지 '없음'

 

1) 내가 푼 방법 select comm, ename, CASE WHEN comm>=5 THEN '5%'                                     WHEN comm>=4 THEN '4%'                                     WHEN comm>=3 THEN '3%'                                     WHEN comm>=2 THEN '2%'                                     ELSE '없음'                            END as COMMITION from emp;          2) 선생님이 푼 방법 (추천) select ename,comm, CASE WHEN nvl(comm,0)>=5 THEN '5%'                                    WHEN nvl(comm,0)>=4 THEN '4%'                                    WHEN nvl(comm,0)>=3 THEN '3%'                                    WHEN nvl(comm,0)>=2 THEN '2%'                                    ELSE '없음'                            END as BONUS from emp;

 

decode() 함수

 

1. 의미

   : 값을 비교하여 해당하는 값을 돌려주는 함수. 단, 비교시에는 정확히 같은 값(=)만 비교가 가능함 

 

2. 형식

    : decode(A, B, 같을때의 값, 다를때의 값)

 

예시)

 

1. emp 테이블 생성

 

select ename, deptno,          decode(deptno, 10, '경리팀'                             , 20, '연구팀'                             , 30, '총무팀') 부서 from emp;

 

2. sungjuk 테이블 조회하기 - 성적 등급 나누기

 

select uname, trunc(((kor+eng+mat)/3)/10) as aver from sungjuk;   select uname, trunc(((kor+eng+mat)/3)/10),          decode(trunc(((kor+eng+mat)/3)/10) ,10,'A'                                                         , 9,'A'                                                         , 8,'B'                                                         , 7,'C'                                                         , 6,'D'                                                         , 'F') grade  from sungjuk;

 

 

JOIN

 

1. 의미

  : 두개 이상의 테이블을 결합하여 테이터를 추출하는 기법.

    두 테이블의 공통값을 이용하여 컬럼을 조합하는 수단

 

2. 형식 

 

 - 직원 & 부서테이블 만들기 

 

drop table emp; drop table dept; --직원테이블 create table emp(  empno     number(4)    primary key  ,ename    varchar2(10)    ,job      varchar2(9)    ,mgr      number(4)      ,hiredate date           ,sal      number(7,2)    ,comm     number(7,2)    ,deptno   number(2)    references dept(deptno) ); --부서테이블 create table dept(   deptno number(7) primary key   ,dname varchar(20)   ,loc varchar(10) );

 

 - 직원 & 부서테이블에 행추가하기

 

--부서테이블 행 추가 insert into dept values(10,'경리팀','부산'); insert into dept values(20,'연구팀','대전'); insert into dept values(30,'총무팀','대구'); insert into dept values(40,'전산팀','인천'); --직원테이블 행 추가 insert into emp(empno, ename, job, mgr, hiredate, sal, deptno) values(7369, '개나리', '사원', 7902, '2000-12-17', 200, 20); insert into emp(empno, ename, job, mgr, hiredate, sal, deptno) values(7499, '진달래', '주임', 7698, '2001-12-15', 360, 20); insert into emp(empno, ename, job, mgr, hiredate, sal, deptno) values(7521, '라일락', '주임', 7698, '2001-02-17', 355, 30); insert into emp(empno, ename, job, mgr, hiredate, sal, deptno) values(7654, '손흥민', '과장', 7839, '2002-01-11', 400, 30); insert into emp(empno, ename, job, mgr, hiredate, sal, deptno) values(7698, '박지성', '주임', 7698, '2000-07-12', 325, 20); insert into emp(empno, ename, job, mgr, hiredate, sal, deptno) values(7782, '김연아', '사원', 7698, '2001-12-17', 225, 10); insert into emp(empno, ename, job, mgr, hiredate, sal, deptno) values(7778, '무궁화', '사원', 7839, '2005-11-14', 200, 10); insert into emp(empno, ename, job, mgr, hiredate, sal, deptno) values(7839, '홍길동', '부장', 7566, '2006-06-17', 450, 20); insert into emp(empno, ename, job, mgr, hiredate, sal, deptno) values(7844, '송강호', '과장', 7566, '2018-09-17', 400, 30); insert into emp(empno, ename, job, mgr, hiredate, sal, deptno) values(7876, '정우성', '대표', 7839, '2004-09-09', 500, 30); insert into emp(empno, ename, job, mgr, hiredate, sal, deptno) values(7900, '김혜수', '사원', 7902, '2001-12-03', 200, 20);

 

3. 표준 (ANSI) SQL문 - 공통

   각 DB만의 SQL문

 

select * from emp join dept on emp.deptno=dept.deptno;

 

select * from emp, dept on emp.deptno=dept.deptno;