이번에는 게시판에 주로 사용되는 페이징 쿼리에 대해서 DBMS별로 포스팅해보도록 하겠습니다.

DBMS 종류는 MySQL,ORACLE,MSSQL 2005(+) 을 작성해보도록 하겠습니다.

페이징쿼리를 돌리기위한 테이블 + 데이터가 필요하겠죠?

기존에 데이터가 많이 쌓여있는 테이블로 테스트 하셔도 무관하나

 저같은 경우에는 예를들기위해 임의로 테이블 생성을 해보도록 하겠습니다.

테스트테이블 생성 및 데이터추가

create table board(
  idx int,
  title varchar(100)
);
insert into board
values(1,'제목1');
insert into board
values(2,'제목2');
insert into board
values(3,'제목3');
insert into board
values(4,'제목4');
insert into board
values(5,'제목5');
insert into board
values(6,'제목6');
insert into board
values(7,'제목7');
insert into board
values(8,'제목8');
insert into board
values(9,'제목9');
insert into board
values(10,'제목10');
insert into board
values(11,'제목11');
insert into board
values(12,'제목12');
insert into board
values(13,'제목13');
insert into board
values(14,'제목14');
insert into board
values(15,'제목15');
insert into board
values(16,'제목16');
insert into board
values(17,'제목17');
insert into board
values(18,'제목18');
insert into board
values(19,'제목19');
insert into board
values(20,'제목20');

테이블 생성 후 임의로 20개정도의 데이터 등록을 하였습니다.

생성된 데이터를 이용하여 DBMS별로 페이징 쿼리를 작성해보도록 하겠습니다.

MySQL 페이징쿼리

MySQL은 LIMIT 문법을 사용하여 페이징 쿼리를 만들 수 있습니다.

MySQL LIMIT 쿼리

SELECT 컬럼1,컬럼2, ... 컬럼 n
FROM 테이블명
LIMIT 시작INDEX, 뽑을데이터수

위 쿼리를 이용하여 페이징 쿼리를 만들 수 있습니다.

※ limit 다음 2개의 값을 작성해주게 되어있는데 하나만 작성 할 경우 뽑힌 data의 정의된 수 만큼 출력이 됩니다.

기존의 뽑힌 데이터를 가지고 페이지당 5개씩 출력해보도록 하겠습니다.

-- 1페이지
SELECT *
FROM board
LIMIT 0,5;
-- 2페이지
SELECT *
FROM board
LIMIT 5,5;
-- 3페이지
SELECT *
FROM board
LIMIT 10,5;
-- 4페이지
SELECT *
FROM board
LIMIT 15,5;

각 페이지별 페이징 쿼리를 작성 후 실행해 보았습니다.

MySQL 페이징쿼리 실행결과

게시판의 페이지라고 생각하고 페이지별로 데이터를 출력해보았습니다.

오라클 페이징 쿼리

오라클 경우 ROWNUM을 이용하여 페이징쿼리를 만들 수 있습니다.

ORACLE ROWNUM 쿼리

-- 1 페이지
SELECT *
  FROM (
        SELECT rownum rnum,idx,title
          FROM board
        ) board
 WHERE rnum BETWEEN 1 AND  5;
-- 2 페이지
SELECT *
  FROM (
        SELECT rownum rnum,idx,title
          FROM board
        ) board
 WHERE rnum BETWEEN 6 AND  10;
-- 3 페이지
SELECT *
  FROM (
        SELECT rownum rnum,idx,title
          FROM board
        ) board
 WHERE rnum BETWEEN 11 AND  15;
-- 4 페이지
SELECT *
  FROM (
        SELECT rownum rnum,idx,title
          FROM board
        ) board
 WHERE rnum BETWEEN 16 AND  20;

작성한 쿼리를 실행해보도록 하겠습니다.

오라클 페이징쿼리 실행 결과

MSSQL 2005버전 이상 페이징 쿼리

마지막으로 MSSQL 페이징 쿼리에 대하여 작성해보도록 하겠습니다.

MSSQL 2005부터 지원이 되는 ROW_NUMBER() 함수를 이용하여 페이징 처리를 하도록 하겠습니다.

※ MSSQL 2000버전에는 ROW_NUMBER() 지원이 되지 않습니다. 2000버전은 TOP 절을 이용하여 페이징 처리를 해줍니다.

    MS-SQL 2000에 대한 페이징쿼리를 다음에 포스팅 하도록 하겠습니다.

MS-SQL 2005(+) ROW_NUMBER() OVER() 페이징 쿼리

-- 1 페이지
SELECT *
  FROM (
        SELECT row_number() over(order by idx) rnum,idx,title
          FROM board
        ) board
 WHERE rnum BETWEEN 1 AND  5;
-- 2 페이지
SELECT *
  FROM (
        SELECT row_number() over(order by idx) rnum,idx,title
          FROM board
        ) board
 WHERE rnum BETWEEN 6 AND  10;
-- 3 페이지
SELECT *
  FROM (
        SELECT row_number() over(order by idx) rnum,idx,title
          FROM board
        ) board
 WHERE rnum BETWEEN 11 AND  15;
-- 4 페이지
SELECT *
  FROM (
        SELECT row_number() over(order by idx) rnum,idx,title
          FROM board
        ) board
 WHERE rnum BETWEEN 16 AND  20;

MSSQL 2005 페이징쿼리 실행 결과는 ORACLE 페이징쿼리결과와 동일하므로 생략하였습니다.

by 개발로짜

DBMS별 DEPTH별로 트리목록을 출력하기위한 

재귀쿼리를 만들어보도록 하겠습니다.

재귀쿼리는 트리형 메뉴 혹은 부서 또는 직급별로 

각 레벨이 구분되어있고 단계별로 한방에 출력하고자 할때 

사용이 되는 방법(?)입니다.

이번 포스팅은 MS-SQL과 Oracle에 대하여 포스팅 하도록 하겠습니다.

MySQL에서는 재귀쿼리에 사용되는 문법이 제공되지 않아

 FUNCTION을 이용하여 만들어 사용하므로

 MySQL 재귀호출에 대해서는 제외하도록 하겠습니다.

우선적으로 MSSQL과 오라클에 재귀호출을 하기위해서 임의의 테이블 + 데이터가 필요하겠습니다.

DBMS 공통적으로 적용할수 있는 테이블을 생성 해보도록 하겠습니다.

테이블 생성 및 데이터 INSERT(ORACLE / MSSQL 동일)

create table parent_child(
  child int,
  parent int,
  name varchar(50)
);
insert into parent_child
values(1,0,'홍길동할아버지');
insert into parent_child
values(2,0,'이순신할아버지');
insert into parent_child
values(3,0,'개발로짜할아버지');
insert into parent_child
values(4,1,'홍길동아버지1');
insert into parent_child
values(5,1,'홍길동아버지2');
insert into parent_child
values(6,3,'개발로짜아버지');
insert into parent_child
values(7,4,'홍길동아버지1의아들');
insert into parent_child
values(8,4,'홍길동아버지1의딸');
insert into parent_child
values(9,6,'개발로짜아들');

위처럼 가족관계에 대한 샘플 데이터를 생성하였습니다.

제일먼저 다루어볼 재귀쿼리는 MSSQL과 오라클 공통적으로 사용이 가능한 문법입니다.

문법은 같으나 살짝 다른 부분이 있어서 두개쿼리 나눠서 작성을 해보았습니다.

MSSQL 재귀호출 쿼리

WITH recursive_query(child,parent,name,sort,dept_name) AS (
  SELECT  
         child
       , parent
       , name
       , convert(varchar(255), child) sort  
  	   , convert(varchar(255), name) dept_name
    FROM parent_child
    WHERE parent = 0
    UNION ALL 
    SELECT
          b.child
        , b.parent
        , b.name
        , convert(varchar(255), convert(nvarchar,c.sort) + ' > ' +  convert(varchar(255), b.child)) sort
        , convert(varchar(255), convert(nvarchar,c.dept_name) + ' > ' +  convert(varchar(255), b.name)) dept_name
    FROM  parent_child b, recursive_query c
    WHERE b.parent = c.child
) 
SELECT name, dept_name FROM recursive_query order by sort

ORACLE 재귀호출 쿼리(1)

WITH recursive_query(child,parent,name,sort,dept_name) AS (
  SELECT  
         child
       , parent
       , name
       , ''||child sort  
  	   , ''||name dept_name
    FROM parent_child
    WHERE parent = 0
    UNION ALL 
    SELECT
          b.child
        , b.parent
        , b.name
        , c.sort || ' > ' ||  b.child sort
        , c.dept_name || ' > ' ||  b.name dept_name
    FROM  parent_child b, recursive_query c
    WHERE b.parent = c.child
) 
SELECT name, dept_name FROM recursive_query order by sort

위처럼 MSSQL과 Oracle의 문법은 동일하지만 

문자열 표시의 합치는 부분의 차이로 인해서 쿼리를 두개로 나누었습니다.

또다른 차이점이라고 하면 차이일수 있겠지만

 WITH recrusive_query다음에 괄호부분의 컬럼지정하는 문법은

 Oracle에서는 필수지만 MSSQL에서는 제외하셔도 무방합니다.

오라클에는 추가로 재귀쿼리를 지원합니다 

ORACLE 재귀호출쿼리(2)

-- name 컬럼만 출력해도 되지만 연결점을 확인하기 위한 LTRIM , SYS_CONNECT_BY_PATH를 사용해보았음
SELECT name, LTRIM (SYS_CONNECT_BY_PATH (name, ' > '), ' > ') AS dept_name
FROM parent_child 
-- 시작지점
START WITH parent = 0
-- 트리구조로 연결할 부모와 자식간의 아이디값
CONNECT BY PRIOR child=parent

오라클의 경우 호출쿼리(2) 쿼리가 간단하니 

START WITH CONNECT BY문을 사용하시는게 나으실듯..

재귀쿼리 실행결과

3가지 재귀쿼리코드를 작성했었는데

결과는 모두 동일할것입니다 ^^

by 개발로짜

이번에는 각 dbms별로 문자열을 합치는 코드를 작성해보도록 하겠습니다.

비교할 DBMS들은 Oracle,MSSQL,MySQL 이 3가지를 비교해볼까 합니다.

기존테이블 기준으로 설명을 드리겠습니다.

오라클 테이블확인

상단처럼 title 컬럼과 idx 컬럼이 존재하는 A테이블의 데이터를 합쳐보도록 하겠습니다.

오라클의 경우 문자열을 합치고자 하려면 일명 OR 표시로 문자열을 붙여줍니다.

오라클 문자열합치기 쿼리

SELECT TITLE,IDX,TITLE||'-'||IDX TOTAL
  FROM A

TITLE,IDX 컬럼에 추가로 TITLE + - + IDX  문자열을 합쳐서 

TOTAL이라는 ALIAS를 정해준 쿼리결과를 확인해보도록 하겠습니다.

오라클 문자열합치기 실행결과

다음은 MySQL 컬럼의 문자열을 합쳐보도록 하겠습니다.

MySQL 테이블확인

역시 기존 존재하는 테이블의 테이블 확인 후 문자열을 합쳐보도록 하겠습니다.

title,content 컬럼의 문자열을 합쳐보도록 하겠습니다.

 MySQL의 경우 concat이라는 함수를 이용하여 문자열 합치는것이 가능합니다.

MySQL 문자열합치기 쿼리

SELECT TITLE,CONTENT,CONCAT(TITLE,'-',CONTENT)  TOTAL
FROM BOARD_NEW

MySQL 문자열합치기 쿼리결과

Oracle과 마찬가지로 동일하게 문자열이 합쳐졌습니다. 

마지막으로 MS-SQL의 문자열합치기를 해보겠습니다.

MSSQL 테이블확인

위처럼 Survey 테이블 컬럼의 answer_1_1/answer_1_2 컬럼의 문자열을 합쳐보도록 하겠습니다.

MSSQL은 플러그기호(+)를 이용하여 문자열합치기가 가능합니다.

MSSQL 문자열합치기 쿼리

select answer_1_1,answer_1_2, answer_1_1+'-'+answer_1_2 TOTAL
from Survey

MSSQL 문자열합치기 쿼리결과

역시 정상적으로 문자열이 합쳐졌습니다.

IBATIS/MYBATIS에도 동일하게 적용이 가능하니 잘 사용하시길...

by 개발로짜