[Database/Theory] 15. WHERE 1=1


1
2
select * from tst_tbl
where 1=1
cs

조건문에 있는 1=1은 있어도 되고 없어도 되는 구문이다. 그러나 이와 같이 1=1을 사용하는 이유는 간단하다.

차후 조건문을 용이하게 작성하기 위한것 뿐이다.

1
2
3
4
5
6
7
8
9
StringBuffer sql = new StringBuffer();
 
// sql에 쿼리문 가져온 것으로 가정.
 
if(true){
    sql.append("and tbl.aaa=1");
}else{
    sql.append("and tbl.aaa=2");
}
cs


위와 같은 경우를 사용할 때 주의할 점은 저 조건으로 인하여 의도치 않는 결과가 나오지 못하게 작성하도록 한다.

MyBatis는 xml파일에서 내부적으로 처리가 가능.

[Database/Theory] 14. view

테이블과 동일하게 사용되는 개체다. 사용자 입장에서 테이블과 동일하다.

1
2
create view v_users
as select id, name from users;
cs


 - 보안성이 좋다.

뷰에 있는 내용만 보여지게 되고, 테이블에 있는 실제 데이터들이 노출되지 않는다.

그냥 테이블을 분리하면 된다 생각되지만 일관성이 떨어진다거나 관리가 어려워진다.


- 복잡한 쿼리의 단순화가 된다.

1
2
3
4
5
6
7
8
9
select   e1.last_name 사원이름, d1.department_name 부서이름, j1.급여 급여
from     employees e1, departments d1, (select   department_id 부서아이디, min(salary) 급여 
                                        from     employees 
                                        group by department_id) j1
where    d1.department_id = e1.department_id and 
         j1.부서아이디 = e1.department_id and 
         j1.급여 = e1.salary
order by d1.department_name, e1.last_name
 
cs

위와 같은 쿼리가 있다면 view로 만들어서 편리하게 사용하면 된다.

([Database/Practice]02. Oracle hr account table(2) 참조)


- create, alter, drop문으로 생성, 수정이 가능하다.

update, insert도 가능하다.

테이블과 마찬가지로 view를 insert나 update 할 경우 

NULL이 허용되지 않는것에 주의하도록한다.


그러나 집계함수[AVG(), MIN(), MAX(), COUNT(), COUNT_BIG(), STDEV()], UNION ALL,

Cross Join, group by를 사용한 View는 수정이 불가능하다.


 - View의 종류

표준뷰, 분할뷰, Indexed 뷰가 있다.


- 표준뷰

1개 이상의 테이블을 이용하여 만든 뷰다. 사용빈도가 가장 높다.


- 분할뷰(Partitioned View)

1대 이상의 서버에 있는 여러 테이블들을 join하여 1개의 테이블터럼 보이도록 된 뷰


- indexed view

복잡한 쿼리를 뷰로 만들어 사용하지만 실제로는 원래 테이블에 접근하는 것이기 때문에,

원래의 쿼리와 비유하면 속도는 높아지지 않는다.


이렇듯이 자주 사용되는 뷰에 실제 데이터를 대입하여 테이블까지 접근하지 않고 뷰까지만

접근하므로 쿼리의 성능이 높아진다.


즉 실제 데이터가 존재하고 고유의 clustered index에 의해 정렬되어 있는 뷰다.


계산하는데 사용되는 집계함수를 사용하거나 join하는 경우에 사용된다.


실제 테이블의 변경에 의하여 indexed view의 변경도 수행해야되는 번거로움과 시스템 부하라는

단점이 있기 때문에 변동이 드문 테이블에 사용되는 것이 좋다.


1
2
3
4
5
create view v_users with schemabinding
as select id, name, (math_score - science_score) from users;
 
create unique clustered index v_users_idx
in v_users(id);
cs

위와 같은 방법으로 생성이 된다.


만약에 뷰를 생성하는 구문에서 where절에 범위를 지정한다면 table scan을 할 것이다.


그리하여 계산식이 포함된 indexed view를 이용하여 범위를 이용한 쿼리가 효과적이다.

그러나 생성되는 index의 양은 증가하게 된다.

1
select id from v_users where (math_score - science_score) > 50;
cs














13. index(3) - 생성 시기

index는 열단위로 생성된다. 또한 2개 이상의 열을 조합하고 생성할 수 있다.


 - where절에서 사용되는 열을 index로 만든다.

그 만큼 자주 사용할 가치가 있는 index로 만든다. 

select문보다 insert문이 자주 사용되면 그만큼 페이지 분할이 될 것이고 시스템의 성능이

저하될 것이다.


 - 중복이 많은 열은 index의 가치가 없다.

non-clustered index일지라도, 중복률이 테이블의 1% ~ 3% 이상이면 사용되지 않는다.


 - 외래키나 Join에 사용되는 열을 사용하는 것이 좋다.

그만큼 설계해야 될 중요성이 크다. 제약조건에 의하여 생성된 index는 삭제가 불가능하고

대량의 데이터의 변동이 발생된다.


 - insert, update, delete의 빈도수를 고려한다.

index는 읽는 것에 대하여 DB의 성능을 높히지만 변경작업은 그 반대의 영향을 끼친다.


- clustered index는 하나만 생성할 수 있다.

clustered index는 데이터 페이지를 최소로 읽기 때문에, 

조건절에서 가장 많이 사용되는 열에 생성하는 것이 바람직하다.

또한 조건절에서 Between과 같은 범위나 집계함수를 사용하는 경우가 좋다.


order by절에 자주 사용되는 열을 clustered index로 사용되는 것이 좋다.

clustered index의 leaf는 이미 정렬되어 있기 때문이다.


clustered index는 테이블에 하나의 열에서만 생성이 가능한데,

만약에 또 하나의 열을 범위로 조회 한다면 

포괄열이 있는 인덱스(index with included columns)를 생성하도록 한다.


 - clustered index가 아예 없는 경우도 좋다.

대용량의 데이터들이 무작위로 입력되다 보면 페이지 분할이 자주 발생될 수 있다.

차라리 non-clustered index로 지정해도 나쁘지 않다.


 - 사용하지 않는 index는 제거한다.

where 조건에서 사용되지 않는 열의 index는 제거할 필요가 있다.

DB공간의 확보 뿐만 아니라 데이터 삽입에 발생하는 부하도 많이 줄일수 있다.


 - 계산 열에서도 index를 활용할 수 있다.

예시를 들어본다.

1
2
3
4
5
6
7
8
9
10
11
create table tbl (
    int1 int,
    int2 int,
    sum  as int1 + int2 persisted
);
 
insert into tbl values(100100);
insert into tbl values(200200);
insert into tbl values(300300);
 
select * from tbl;
cs

int1    int2     sum

-----------------

100    100     200

200    200    400

300    300    300


sum이라는 열을 index를 사용하려면 아래와 같은 구문을 사용하면 된다.

1
create clustered index idx_tbl_sum on tbl(sum);
cs


- 포괄열이 있는 non-clustered index를 활용하면 

   쿼리 성능이 높아진다.

포괄열이 있는 인덱스(index with included column)는 non-clustered index의

leaf page에 데이터를 포함하고 있는 열을 의미한다. 


[Database/Theory] 12. index(2) - 내부적 동작에 의하면,

clustered index의 속도는 non-clustered index보다 빠르다.

추가로 where절에 범위를 입력할 경우, clustered index는 그 기준을 찾아서 탐색하지만

non-clustered index는 전체 테이블을 검색하게 된다. clustered index는 leaf page가

곧 데이터이고 정렬되어 있기 때문이다. 


그러나 non-clustered index는 leaf page를 거치고 다시 데이터 페이지에서 검색한다.

만약에 leaf page와 데이터가 공존한다면 굳이 데이터 페이지를 찾지 않아도

non-clusered index에서 모든 작업이 종료되므로 clustered index와 같은 효과가 나타난다.


예시를 위해 기존에 작성한 테이블을 다르게 작성해 보겠다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create table users(
   number int,  
   id     varchar,
   name   varchar,
   phone  varchar
);
 
insert into users values(9'KYY''김윤영''011');
insert into users values(6'KEH''김은혜''016');
insert into users values(3'HKM''홍경모''011');
insert into users values(11'YHH''유형환''017');
insert into users values(12'YMH''유민호''016');
insert into users values(10'LTG''이태검''017');
insert into users values(2'BSH''백승현''011');
insert into users values(8'KKC''김경철''018');
insert into users values(4'JHJ''장희정''016');
insert into users values(5'JHM''정희민''011');
insert into users values(7'KHS''김희선''018');
insert into users values(1'BCE''배찬익''016');
cs


여기서 index with included column을 생성한다.

1
2
3
create      nonclustered index idx_users_include
    on      users(number)
    include (id, name); 
cs


아래와 같은 구조가 될 것이다.



이러한 구조에서 다음과 같은 쿼리를 수행할 경우, root와 leaf page에서

해당된 데이터만 읽게 된다.

1
select number, id, name from users where number < 10;
cs

즉 leaf page에 100번과 101번만 읽게 되면 select문에 있는 number, id, name이

모두 있게되므로 clustered index와 같은 효과를 낼 수 있다.


그러나 다음과 같은 쿼리를 수행할 경우 모든 data page까지 읽게되는

table scan을 하게 된다.

1
select number, id, name, phone from users where number < 10;
cs

이유인 즉슨 select문에 phone은 leaf page에 phone이 없기 때문에,

RID를 참조하여 data page에서 탐색할 수밖에 없다.


- 결론

포괄열이 있는 index는 non-clustered index에만 생성할 수 있다. 

clustered index에는 생성할 수 없을 뿐만 아니라 생성해도

clustered index에는 leaf page 자체가 data이기 때문에 생성의 의미가 없다.


포괄열이 있는 index는 index의 크기가 커지는 단점이 있다.


포괄열이 있는 index를 생성하면 쿼리의 성능이 급격히 향상된다.


포괄열이 있는 index가 있어도 select의 열이 그 포괄열에 포함되지 않으면

index는 사용되지 않는다.

12. index(2) - 내부적 동작

 - 내부적 동작

   * B-Tree(Balanced Tree)라는 구조에 의해 동작한다.

   * 자료구조의 Tree와 같은 구조이며 Root, Leaf와 같은 지칭이 있다.

   * 자료구조에서는 자료를 가진 것을 node라 부르지만 DB의 B-Tree에서는

     Page라 부른다.

   * 한 Page에 8Kbyte의 공간을 차지한다.

   * 특정 Data를 검색할 때, Root page를 시점으로 연결된 Page를 찾아나아간다.

   * table scan보다 확연한 탐색 속도의 차이가 있다.

   * 그러나 Data의 변경작업이(insert, delete, update => CUD라 부르겠다.) 잦으면      역효과가 나타난다.

   * 페이지 분할 작업이 발생되기 때문이다.

   * 가령 특정 페이지에 데이터가 삽입되면 index 특성상 정렬이 먼저 수행되고

     페이지에 공간이 부족하면 새로운 페이지가 생성되어 분할된다.

   * 그렇게 되면 tree의 구조가 크게 변경될 수 있다. 이에 따라 탐색이 기존보다

     복잡해질 수 있다.

   * 그러니까 결론은 분할이 많이 발생되면 시스템의 성능이 떨어진다.


   예시



  이 구조에서 iii와 ggg를 삽입하게 되면 페이지들이 분할된다. 




 - Clustered Index

    * 예시로 users라는 테이블을 생성해서 아래와 같은 데이터들을 삽입하겠다.

    * 데이터를 입력한 순서에 유의한다.

    * 참고로 입력하는 데이터들은 페이지 용량에 맞춰진다. 이러한 페이지들을

      Data Page 혹은 Heap area라 부른다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table users(
   id   varchar primary key,
   name varchar
);
 
insert into users values('KYY''김윤영');
insert into users values('KEH''김은혜');
insert into users values('HKM''홍경모');
insert into users values('YHH''유형환');
insert into users values('YMH''유민호');
insert into users values('LTG''이태검');
insert into users values('BSH''백승현');
insert into users values('KKC''김경철');
insert into users values('JHJ''장희정');
insert into users values('JHM''정희민');
insert into users values('KHS''김희선');
insert into users values('BCE''배찬익');
cs



    * Clustered index는 영단어 사전과 유사하다.

    * heap area를 보면 입력한 데이터의 순서가 아닌 index로 지정된 열에 따라

      정렬이 된 후에 위와 같은 구조를 표현하고 있다.

    * 11. index(1) - 개요, 장단점, 종류 에 설명된 바에 의하면 clustered index는

      데이터가 순차적으로 정렬되어 있다는 성질이 있다.

    * Root, Leaf의 구조를 가지고 있으며 Leaf page 자체가 Data임을 알 수 있다.

    * non-clustered index보다 비교적으로 검색속도가 빠르지만 변경작업은 느리다.


 - Non-Clustered Index

    * clustered index와 같은 테이블로 예시를 들어본다.

    * 다시 위로 올리기 귀찮아 할까봐 다시 적어본다.

    * 역시 입력한 데이터의 순서에 유의한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table users(
   id   varchar primary key,
   name varchar
);
 
insert into users values('KYY''김윤영');
insert into users values('KEH''김은혜');
insert into users values('HKM''홍경모');
insert into users values('YHH''유형환');
insert into users values('YMH''유민호');
insert into users values('LTG''이태검');
insert into users values('BSH''백승현');
insert into users values('KKC''김경철');
insert into users values('JHJ''장희정');
insert into users values('JHM''정희민');
insert into users values('KHS''김희선');
insert into users values('BCE''배찬익');
cs



   * clustered index와 달리 입력한 순서대로 Data page가 구성된다.

   * non-clustered index는 Data page를 건드리지 않고 

     별도의 index page를 생성한다.

   * data page를 참조하여 non-clustered index page의 leaf에서 index로 설정한

     열에 따라 데이터들이 정렬된다.

   * leaf에 1000 + #2와 같은 표기는 RID(Row ID, 데이터 위치 포인터)라 부른다.

     페이지 번호와 offset에 따라 실제 data로 이동된다.

   * clustered index와 비교하면, 데이터를 탐색할 때 

     거치는 페이지 수는 non-clusetered index가 1회가량 더 거치게 된다.

   * 물론 간단한 예시이지만, 데이터들이 방대해지면 큰 차이가 일어날 것이다.

   * 고로 Clustered index가 더 탐색속도가 빠르다.



 - 데이터의 추가(Clustered Index,  Non-Clustered Index)

   * 역시 위로 올리기 귀찮으니 한번 더 예시의 테이블을 작성한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table users(
   id   varchar primary key,
   name varchar
);
 
insert into users values('KYY''김윤영');
insert into users values('KEH''김은혜');
insert into users values('HKM''홍경모');
insert into users values('YHH''유형환');
insert into users values('YMH''유민호');
insert into users values('LTG''이태검');
insert into users values('BSH''백승현');
insert into users values('KKC''김경철');
insert into users values('JHJ''장희정');
insert into users values('JHM''정희민');
insert into users values('KHS''김희선');
insert into users values('BCE''배찬익');
cs

   * 여기에서 두 개의 데이터를 추가한다.

1
2
insert into users values('SMK''서민기');
insert into users values('KJH''김주현');
cs


   * Clustered Index와 Non-Clustered Index의 B-Tree 구조를 본다.





   * 다시 한번 더 정리한다.

   * Clustered Index는 생성될 때 데이터 페이지 전체를 다시 정렬한다.

   * 대용량의 데이터가 있다면 시스템 부하가 발생될 수 있다.

   * Leaf가 곧 데이터다 따라서 인덱스 자체에 데이터가 포함된 것이라 볼 수 있다.

   * Non-Clustered Index와 비교할 때 검색속도는 빠르지만 변경작업은

     페이지 분할에 의하여 느리다.

   * 테이블 한개에 Clustered Index 하나만 생성이 가능하므로,

     어느 열에 Clustered Index를 지정하느냐에 따라 시스템 성능이 좌우된다.


   * Non-Clustered Index는 생성될 때 페이지를 그대로 둔 채,

      Index를 구성한다.

   * Leaf가 RID다.

   * Clustered Index와 비교할 때 검색속도는 느리지만 변경작업은 빠르다.

     (Heap Area뒤에 추가만 하면 되고 Leaf에서는 약간의 변동만 하면 되니까)

   * Non-Clustered Index는 한 테이블에 여러개를 생성할 수 있지만 

     지속적인 남용은 시스템의 성능이 저하되므로 필요한 것만 지정하도록 한다.



 - Clustered Index,  Non-Clustered Index의 혼합

   * 한 가지 추가한 예시로 설명을 본다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create table users(
   id    varchar primary key,
   name  varchar unique,
   phone varchar
);
 
insert into users values('KYY''김윤영''011');
insert into users values('KEH''김은혜''016');
insert into users values('HKM''홍경모''011');
insert into users values('YHH''유형환''017');
insert into users values('YMH''유민호''016');
insert into users values('LTG''이태검''017');
insert into users values('BSH''백승현''011');
insert into users values('KKC''김경철''018');
insert into users values('JHJ''장희정''016');
insert into users values('JHM''정희민''011');
insert into users values('KHS''김희선''018');
insert into users values('BCE''배찬익''016');
cs

   * 구조는 아래와 같은 그림으로 형성되고 빨간색 글자를 보면

     데이터를 탐색하는 과정을 볼 수 있다.

   * Non-Clustered Index를 탐색하고 Clustered의 Root Page로 찾아가

     찾고자 하는 데이터를 탐색하는 순서로 되어있다.

   * 기존에 있는 Non-Clustered Index의 Leaf는 RID로 되어 있었지만

     혼합이 되면서 Clustered Index의 Key Value로 되어있다.

   * 이유 : 혼합하지 않고 분리되어 RID를 그대로 사용된다면 검색하는데 있어

     더 빠르겠지만, 

     새로운 데이터가 추가되면 Clustered Index의 Data page가 재구성되고

     RID의 표기가 모두 변경된다. 

     그 결과로 Non-Clustered Index 전체가 재구성되면서 

     엄청난 시스템 부하가 발생될 소지가 있다.

   * 그러니까 결론은 Non-Clustered Index, Clustered Index가 혼합되면 

     RID표기는 삼가한다.

   * 검색의 손해는 있지만 CUD의 손해보다는 확연히 작기 때문에 감안할 수 있다.


 - 용어

   * table scan : 테이블의 모든 데이터를 처음부터 끝까지 탐색하는 것.

   * index seek : Non-Clustered Index에서 데이터를 찾는다는 의미.

   * Clustered Index Seek : Clustered Index에서 데이터를 찾는다는 의미.

   * Clustered Index Scan : table scan과 동일한 의미. 

                                   Clustered Index의 Leaf가 Data Page이기 때문이다.

   ( Seek과 Scan은 다른 의미다.

     인덱스 검색을 위해 where절에 해당 인덱스의 이름을 작성하도록 한다.)


11. index(1) - 개요, 장단점, 종류


 - 인덱스의 개요

DB의 성능을 향상시키는 것이 목적.

적절한 사용으로 데이터에 빠르게 접근한다.

DB 튜닝의 큰 효과를 볼 수 있다.

과다한 사용은 성능이 떨어진다.

필요없는 인덱스가 많아지면 DB에 차지되는 공간이 많아져 table scan보다 속도가 느려질 수 있다.

(table scan : 인덱스를 사용하지 않고 DB를 처음부터 끝까지 탐색하는 것)


- 장점과 단점

무조건은 아니지만, 데이터 검색속도가 향상된다.

시스템 부하가 줄어들고 시스템의 전체 성능이 높아진다.


DB의 10%정도 공간이 요구된다.

인덱스를 생성하는 시간이 크게 요구된다.

DB의 변경작업(Insert, Update, Delete)이 잦으면 성능이 저하된다.


- Index의 종류

  Clustered Index

   사전처럼 데이터가 순차적으로 정렬되어 있다.

   테이블당 한 개만 생성이 가능하다.

   행 데이터를 인덱스로 지정한 열에 맞춰 자동으로 정렬한다.

   테이블 생성시 제약조건이 없다면 인덱스를 만들 수 없다.

   기본키를 생성시 기본키에 Clustered Index가 된다.


   Non-Clustered Index

   테이블당 여러개를 생성 할 수 있다.


 - 예제를 통해 확인하는 Index의 특징

기본키로 지정하면 기본키에 Clustered Index가 생성된다.

1
2
3
4
create table users
( id   varchar not null primary key,
  name varchar,
 .....
cs

     

* 기본키를 지정하면서 Non-Clustered Index를 생성할 수 있다.

  그 결과 Clustered Index의 여분이 있데 된다.

1
2
3
4
create table users
( id   varchar not null primary key nonclusterd,
  name varchar,
 .....
cs


 * unique로 지정한 행마다 Non-Clustered Index가 생성된다.

   (한 테이블에 여러개의 Non-Clustered Index가 생성될 수 있다.)

1
2
3
4
5
6
create table users
( id      varchar not null primary key,
  name    varchar unique,
  digit   varchar unique,
  address varchar
)
cs


  * 강제로 Clustered Index와 Non-Clustered Index를 지정할 수 있다.

    (기본키로 무조건  Clustered Index를 지정되는 것이 아니다. 단지 Default로 지정될 뿐이다.)

1
2
3
4
5
6
create table users
( id      varchar not null primary key nonclustered,
  name    varchar unique clustered,
  digit   varchar unique,
  address varchar
)


cs

    

        * Clustered Index로 지정된 열에 따라 테이블이 정렬된다.

1
2
3
4
5
6
7
8
9
10
11
create table users(
  id   varchar primary key,
  name varchar
);
 
insert into users('ccc''111');
insert into users('ddd''222');
insert into users('bbb''333');
insert into users('aaa''444');
 
select * from users;
cs

      결과

      id        name

      ----------------

      aaa      444

      bbb     333

      ccc      111

      ddd     222

      입력된 순서대로 보여질거라 생각했지만 Clustered Index에 의해 정렬되어 표현한다.

10. Join(1)

2개 이상의 테이블이 하나로 묶여 집합을 만들어내는것.


 - Inner Join

일반적인 Join

서로 관계가 맺어진 테이블에서 각자의 속성들을 추출하는것.

Join하는 조건에 일치한 테이블들이 결합된다.

select 뒤에는 어떤 테이블의 속성을 추출할 것인지 명시해야된다.

Join의 조건하에, 양쪽 테이블에 모두 존재하는것만 추출된다.

Join의 조건은 순차적으로 검색된다.


- Outter Join

Inner Join의 결과와 한쪽 테이블에만 존재하는 데이터도 추출하는 것.

Join 조건에 만족하지 아니한 행도 추출된다.

Left, Right의 경우 각 측에 있는 테이블의 데이터를 추출한다는 의미다.

Full Outter는 Left와 Right를 모두 수행한다는 의미다.


- Self Join

한 테이블에 같은 데이터들이 존재하되, 서로 다른 의미로 표현되는 경우 사용된다.


09. SQL syntax(7) - Rownum

     select rownum from ~~~~ order by -> rownum먼저 수행되므로 order by하면 rownum은 섞일 수 있다.

     where -> group by -> having -> order by 순서로 진행

     rownum은 항상 추출된  1부터 부여된다.


     where rownum >= 3 (X) 조건절에 만족하지 아니하면 

     매번 1부터 넘버링이되고 매번 조건에 만족하지 않으므로 결과가 나오지 않는다

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
/* Ex 1 : */
select   rownum, id, name, salary 
from     emp_test 
order by salary desc nulls last;
 
/* Ex 2 : */
select   rownum, id, name, salary 
from     emp_test 
where    rownum <=7 
order by salary desc nulls last; 
/* (쓰레기, order by가 나중에 적용) */
 
/* Ex 3 : */
select rownum, vt.rnum, vt.id, vt.name, vt.salary
from (select   rownum rnum, id, name, salary 
      from     emp_test 
      order by salary desc nulls last) vt 
where rownum <= 7;
 
/* Ex 4 : */
select rownum, vt.rnum, vt.id, vt.name, vt.salary
from (select rownum rnum, id, name, salary 
      from emp_test 
      order by salary desc nulls last) vt 
where rownum > 7;
/* (X) */
 
cs

     rownum은 항상 추출된 1부터 부여된다. 조건절에 만족하지 아니하면 

     매번 첫행부터 1씩 넘버링이되고 매번 조건에 만족하지 않으므로 결과가 나오지 않는다

     ROWNUM은 FROM WHERE절에 대한 순번으로서 1부터 시작해야된다. 

     다음 ROW가 추출 될 때마다 ROWNUM의 값을 순차적으로 증가, 

     그런데 1이 존재하지 않는다면 다시 1부터 숫자가 부여된다.

     다음 순번을 증가부여 할 수 없으므로 아무런 데이터가 추출되지 않는다.


1
2
3
4
5
6
7
select vt1.rrnum, vt1.rnum, vt1.id, vt1.name, vt1.salary
from (select rownum rrnum, vt.rnum vt.id, vt.name, vt.salary 
      from(select   Rownum rnum, id, name, salary 
           from     emp_test 
           order by salary desc nulls last) vt
      ) vvt 
where rownum > 7;
cs


08. SQL syntax(6) - Sub Query

         Main Query안에 확장된 또 다른 Query문

         DML, Query과 CREATE TABLE, VIEW에서 이용가능

         

   1. Single Row Sub Query : 

           한 가지의 값을 전달하는 Query

1
2
3
4
5
select id, name
from c_emp
where dept_id=select dept_id
                from   c_emp
                where  name='이순신');
cs
1
2
3
4
select id, name, salary
from c_emp
where salary < ( select avg(salary)
                 from c_emp);
cs


  

         

   2. Multiple Row Sub Query : 

           한 가지 이상의 값들을 전달하는 Query

1
2
3
4
5
select name, dept_id
from c_emp
where dept_id in (select id
                  from s_dept
                  where dept_name in('총무부''영업부') );
cs
1
2
3
4
5
select name, dept_id
from c_emp
where dept_id in (select id
                  from s_dept
                  where dept_name in('총무부''영업부') );
cs

            * 필요성

              해당되는 값이 1개 이상인 것을 요구할때



   3. Multiple Column Sub Query : 

           한 가지의 값을 전달하는 Query

1
2
3
4
5
select name, salary, dept_id
from c_emp
where (dept_id, salary) in (select
                            dept_id, min(salary) 
                            from c_emp group by dept_id);
cs

            * 필요성

              Sub Query는 해당 값들만을 이용하여 Query를 처리한다.

              이에 대하여 해당되는 Column의 해당되는 값을 맞춰야 된다.

1
2
3
4
5
select name, salary, dept_id
from c_emp
where salary in (select min(salary) 
                 from c_emp
                 group by dept_id);
cs

1
select min(salary) from c_emp group by dept_id;
cs

           


   4. Inline View : 

            From에 적용하는 Sub Query. Virtual Table을 작성한다.

1
2
3
4
5
select c.name, c.salary, c.dept_id
from c_emp c, (select dept_id, min(salary) min
               from c_emp
               group by dept_id) vt
where c.dept_id=vt.dept_id and c.salary = vt.min;
cs


07. SQL syntax(5) - Join

   하나 이상의 테이블로 부터 연관된 Data 조회

   보통 둘 이상의 Column들의 공통된 값 Primary Key 및 Foreign Key 값을 사용하여 조인 한다.

   4가지로 분류 : EQUI JOIN / NON-EQUI JOIN / SELF JOIN / OUTER JOIN


   이해를 돕기위하여 테이블들을 열거한다.

     c_emp;

     이름                                            널?      유형

      ----------------------------------------- -------- ------------

      ID                                        NOT NULL NUMBER(5)

      NAME                                   NOT NULL CHAR(15)

      SALARY                                                NUMBER(7,2)

      PHONE                                                CHAR(15)

      DEPT_ID                                               NUMBER(7)


     s_dept;

      이름                                      널?      유형

      ----------------------------------------- -------- ------------

      ID                                        NOT NULL NUMBER(7)

      DEPT_NAME                                          VARCHAR2(12)


     sal_grade;

      이름                                      널?      유형

      ----------------------------------------- -------- ---------

      GRADE                                     NOT NULL CHAR(1)

      LOSAL                                              NUMBER(4)

      HISAL                                              NUMBER(4)


   1. EQUI JOIN : 

        서로 관계를 가진 Table들을 이용한 Data 조회.

        PK 와 FK로 관계의 무결성 보장 필요.

        Equi join의 성능을 높이기 위해서는 Index 기능을 사용하는 것이 좋다.


     * Cartesian Product : 테이블 모든 행이 다른 테이블의 모든행을 연결. 쓰레기값.

1
2
3
select
c_emp.name, c_emp.dept_id, s_dept.id, s_dept.dept_name
from c_emp, s_dept;
cs

       NAME                              DEPT_ID         ID DEPT_NAME

       ------------------------------ ---------- ---------- ------------------------

       김사장                                  1          1 총무부

       홍길동                                  1          1 총무부

       홍길순                                  1          1 총무부

       이순신                                  2          1 총무부

       강감찬                                  2          1 총무부

       류시원                                  2          1 총무부

       이문세                                  3          1 총무부

       강호동                                  3          1 총무부

       강문영                                  3          1 총무부

       추현재                                  4          1 총무부

       박상민                                  4          1 총무부

       박상면                                  4          1 총무부

       유재석                                  5          1 총무부

       노무현                                  5          1 총무부

       전두환                                  5          1 총무부

       김일성                                  6          1 총무부

       김정일                                  6          1 총무부

       .....


       => WHERE로 조건 해결

1
2
3
4
select
c_emp.name, c_emp.dept_id, s_dept.id, s_dept.dept_name
from c_emp, s_dept
where c_emp.dept_id = s_dept.id;
cs

 

     * 실습 예제

       -  김씨성 직원의  이름 / 부서명 /연봉  조회

           ==> 연봉:  \250,000 형식 , 연봉높은순 

1
2
3
4
select 
c.name 이름, s.dept_name 부서명, to_char(c.salary*12,'L999,999') 연봉
from c_emp c, s_dept s
where c.dept_id=s.id and c.name like '김%';
cs

       이름                           부서명                   연봉

       ------------------------------ ------------------------ ------------------------------------

       김사장                         총무부                            ₩12,000

       김일성                         회계부                            ₩24,000

       김정일                         회계부                            ₩36,000




       -  월급이 2000이상 직원 부서명 / 이름 /일당 조회

             ==> 부서별 :: 오름차순

             ==> 이    름 :: 오름차순

             ==> 일    당 :: 30일기준, 소수점 0자리 버림, \250,000형식, 높은순 

1
2
3
4
5
select
s.dept_name 부서명, c.name 이름, to_char(trunc(c.salary/30,0),'L999,999') 일당
from c_emp c, s_dept s
where c.dept_id=s.id and c.salary >= 2000
order by s.id, c.name asc, c.salary desc;
cs

       부서명                   이름                           일당

       ------------------------ ------------------------------ ------------------------------------

       총무부                   홍길동                                      ₩66

       총무부                   홍길순                                     ₩100

       개발부                   강감찬                                     ₩166

       개발부                   이순신                                     ₩133

       인사부                   박상면                                     ₩116

       인사부                   박상민                                      ₩83

       자재부                   노무현                                     ₩183

       자재부                   유재석                                     ₩150

       회계부                   김일성                                      ₩66

       회계부                   김정일                                     ₩100

       

       

       

       

       - 부서별 평균 월급  부서번호/부서명/평균월급 조회

            ==> 부서번호 :: 오름차순

1
2
3
4
5
6
select
s.id 부서번호, s.dept_name 부서명, avg(c.salary) 평균월급
from c_emp c, s_dept s
where c.dept_id=s.id
group by s.id, s.dept_name
order by s.id;
cs

         부서번호 부서명                     평균월급

       ---------- ------------------------ ----------

                1 총무부                         2000

                2 개발부                   3366.66667

                3 영업부                         1300

                4 인사부                         2500

                5 자재부                   3666.66667

                6 회계부                         2500

       

       

       - 총무부 평균 월급  부서번호/부서명/평균월급 조회

       

       =>Where가 효율적

     #1.

1
2
3
4
5
select
s.id 부서번호, s.dept_name 부서명, avg(c.salary) 평균월급
from c_emp c, s_dept s
where c.dept_id = s.id and s.dept_name='총무부'
group by s.id, s.dept_name;
cs

       

         부서번호 부서명                     평균월급

       ---------- ------------------------ ----------

                1 총무부                         2000 


     #2. 

1
2
3
4
5
6
select
s.id 부서번호, s.dept_name 부서명, avg(c.salary) 평균월급
from c_emp c, s_dept s
where c.dept_id = s.id
group by s.id, s.dept_name
having s.dept_name='총무부';
cs

       

         부서번호 부서명                     평균월급

       ---------- ------------------------ ----------

         1 총무부                         2000



   2. NON - EQUI JOIN 

           관계가 없는 Table들을 이용한 Data 조회.

           테이블의 어떤 column도 Join할 테이블의 column에 일치하지 않을 때 사용하고, 

           조인조건은 동등( = )이외의 연산자를 갖는다.

           => EQUI JOIN 은 = 연산자를 사용

     => NON-EQUI JOIN 은 =연산자를 제외한 비교 연산자 사용

                (BETWEEN AND, IS NULL, IS NOT NULL, IN, NOT IN)


     * 예제

1
2
3
4
select
c.name 이름, c.salary 월급, sg.grade 월급등급
from c_emp c, sal_grade sg
where c.salary between sg.losal and sg.hisal;
cs

       이름                                 월급 월

       ------------------------------ ---------- --

       김사장                               1000 D

       전두환                               1000 D

       류시원                               1100 D

       이문세                               1200 D

       강호동                               1300 D

       강문영                               1400 D

       추현재                               1500 D

       홍길동                               2000 C

       김일성                               2000 C

       박상민                               2500 C

       홍길순                               3000 B

       김정일                               3000 B

       박상면                               3500 B

       이순신                               4000 A

       유재석                               4500 A

       강감찬                               5000 A

       노무현                               5500 A


     * NON - EQUI JOIN & EQUI JOIN 응용 실습

       - 개발부 직원과 영업부직원 이름 , 월급 , 월급등급 , 부서명 조회

           => 등급 높은순  ( NON-EQUI JOIN /  EQUI JOIN 같이 사용)

1
2
3
4
5
select
c.name 이름, c.salary 월급, sg.grade 월급등급, s.dept_name 부서명
from c_emp c, sal_grade sg, s_dept s
where s.id=c.dept_id and s.dept_name in ('개발부''영업부')
      and c.salary between sg.losal and sg.hisal;
cs

       이름                                 월급 월 부서명

       ------------------------------ ---------- -- ------------------------

       류시원                               1100 D  개발부

       이문세                               1200 D  영업부

       강호동                               1300 D  영업부

       강문영                               1400 D  영업부

       이순신                               4000 A  개발부

       강감찬                               5000 A  개발부


       - sal_grade가 A  인원이 2명이상인 부서명 , 인원수 , 등급 

            ( NON-EQUI JOIN /  EQUI JOIN 같이 사용)

1
2
3
4
5
6
7
8
select
s.dept_name 부서명, count(c.id) 인원수, sg.grade 등급
from c_emp c, sal_grade sg, s_dept s
where s.id=c.dept_id
      and c.salary between sg.losal and sg.hisal
      and sg.grade = 'A'
group by s.dept_name, sg.grade
having count(c.id)>=2;
cs


       부서명                       인원수 등

       ------------------------ ---------- --

       자재부                            2 A

       개발부                            2 A


   3. OUTER JOIN : 

            NULL의 비교는 FALSE, NULL의 연산은 NULL이라는 특징을 이용한 JOIN

            NULL값에 의하여, 조건문에 배제된 값을 출력할 때 사용.

1
2
3
4
select
c.name 이름, s.dept_name 부서명
from c_emp c, s_dept s
where c.dept_id = s.id;
cs

        이름                           부서명

        ------------------------------ ------------------------

        김사장                         총무부

        홍길동                         총무부

        홍길순                         총무부

        이순신                         개발부

        강감찬                         개발부

        류시원                         개발부

        이문세                         영업부

        강호동                         영업부

        강문영                         영업부

        추현재                         인사부

        박상민                         인사부

        박상면                         인사부

        유재석                         자재부

        노무현                         자재부

        전두환                         자재부

        김일성                         회계부

        김정일                         회계부


        17 개의 행이 선택되었습니다.



        OUTER JOIN 적용(+)

1
2
3
4
select
c.name 이름, s.dept_name 부서명
from c_emp c, s_dept s
where c.dept_id = s.id(+);
cs


        이름                           부서명

        ------------------------------ ------------------------

        홍길순                         총무부

        홍길동                         총무부

        김사장                         총무부

        류시원                         개발부

        강감찬                         개발부

        이순신                         개발부

        강문영                         영업부

        강호동                         영업부

        이문세                         영업부

        박상면                         인사부

        박상민                         인사부

        추현재                         인사부

        전두환                         자재부

        노무현                         자재부

        유재석                         자재부

        김정일                         회계부

        김일성                         회계부

        신입1

        신입2


        19 개의 행이 선택되었습니다.



   4. SELF JOIN

           한 Table에 대하여, 두 개의 Alias를 사용하여

           FROM절에 두 개의 테이블을 사용하는 것 처럼 조인한다.

1
2
3
4
5
select
e1.empno 사번, e1.ename 이름, e2.empno 매니저사번, e2.ename 매니저이름
from emp e1, emp e2
where e1.mgr=e2.empno
order by e1.empno;
cs

              사번 이름                 매니저사번 매니저이름

        ---------- -------------------- ---------- --------------------

              7369 SMITH                      7902 FORD

              7499 ALLEN                      7698 BLAKE

              7521 WARD                       7698 BLAKE

              7566 JONES                      7839 KING

              7654 MARTIN                     7698 BLAKE

              7698 BLAKE                      7839 KING

              7782 CLARK                      7839 KING

              7788 SCOTT                      7566 JONES

              7844 TURNER                     7698 BLAKE

              7876 ADAMS                      7788 SCOTT

              7900 JAMES                      7698 BLAKE

              7902 FORD                       7566 JONES

              7934 MILLER                     7782 CLARK

        

        OUTER JOIN 적용(+)   

1
2
3
4
5
select
e1.empno 사번, e1.ename 이름, e2.empno 매니저사번, e2.ename 매니저이름
from emp e1, emp e2
where e1.mgr=e2.empno(+)
order by e1.empno;
cs


              사번 이름                 매니저사번 매니저이름

        ---------- -------------------- ---------- --------------------

              7369 SMITH                      7902 FORD

              7499 ALLEN                      7698 BLAKE

              7521 WARD                       7698 BLAKE

              7566 JONES                      7839 KING

              7654 MARTIN                     7698 BLAKE

              7698 BLAKE                      7839 KING

              7782 CLARK                      7839 KING

              7788 SCOTT                      7566 JONES

              7839 KING

              7844 TURNER                     7698 BLAKE

              7876 ADAMS                      7788 SCOTT

              7900 JAMES                      7698 BLAKE

              7902 FORD                       7566 JONES

              7934 MILLER                     7782 CLARK



06. SQL syntax(4) - Constraint, rest

- Constraint

   Table을 만들기 전제 규정하는 것을 제약조건(Constraint)라고 한다.

   제약조건은 결함이 없다는 뜻을 가진 무결성을 특징으로 표현한다.

   그 무결성은 두가지로 분류된다.

   

   * 개체 무결성 : Table의 한 Record에 대하여, 대표하는 값이 부재상태가 있을수 없고 고유하지 아니하여야 되는 특징.

   * 참조 무결성 : Table끼리 관계를 가진 특징. 

                       다른 Table의 Column을 참조하는 Table의 Record에 대하여, 

                       참조되는 Column의 값이 들어갈 수 없다는 특징.

   

   * 제약조건 Key Word

     PRIMARY KEY(PK) : 개체 무결성을 가진 키. Record를 대표하는 Column. NOT NULL과 UNIQUE의 성격이 있다. 

     FOREIGN KEY(FK) : 참조 무결성을 가진 키. 참조되는 Column의 관계를 적용하고 설정.

     CHECK(CK)     : TRUE값을 지키는 조건을 설정.


   Ex : 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
CREATE TABLE emp_test01(
    /*제약 조건의 이름을 걸어준다*/
    id            NUMBER(3)        CONSTRAINT    emp_test01_id_pk        PRIMARY KEY,
    name        VARCHAR2(12)    CONSTRAINT    emp_test01_name_nn        NOT NULL,
    salary        NUMBER(7,2)        CONSTRAINT    emp_test01_salary_ch    CHECK (SALARY >=1000),
    title        VARCHAR2(15)    DEFAULT '사원',
    in_date        DATE            DEFAULT SYSDATE,
    dept_name    VARCHAR2(15)    
);
 
 
 
INSERT
INTO emp_test01
VALUES(1 , '홍길동' , 2000 , '부장' , SYSDATE , '총무부' );
 
/*
  1행에 오류:
  ORA-00001: 무결성 제약 조건(SCOTT.EMP_TEST01_ID_PK)에 위배됩니다
*/
 
 
INSERT
INTO emp_test01
VALUES2 , NULL , 3000'사원' , SYSDATE , '총무부' );
 
 
/*
  3행에 오류:
  ORA-01400: NULL을 ("SCOTT"."EMP_TEST01"."NAME") 안에 삽입할 수 없습니다
*/
 
 
INSERT
INTO emp_test01
VALUES(2,'홍길동',500,'사원',SYSDATE,'총무부');
 
 
/*
  1행에 오류:
  ORA-02290: 체크 제약조건(SCOTT.EMP_TEST01_SALARY_CH)이 위배되었습니다
*/


- rest


   * set linesize : 출력할 Table 너비 설정

   * set pagesize : 출력할 Table 폭   설정

   * COMMIT : 현재까지 입력된 Table의 내용을 Persistant 저장

     ROLLBACK : COMMIT이후에 입력된 모든 명령어들 취소

   * Default가 설정된 DataType은 NULL, null, ''로 대입이 가능하다.

   * USER: 사용자의 정보

     Ex 

1
 INSERT INTO table VALUES(USER);
cs


+ Recent posts