changhozz

이번엔 SQL기법중 GROUP BY기법과 내부함수에 대하여 알아보자.

1.GROUP BY
GROUP BY란 특정 칼럼을 기준으로 같은 값을 가지는 행들을 그룹별로 모아 자료를 가져오는 것이다.
쉽게말해서, 중복된 여러개의 값들 중 대표선수 한명만 뽑겠다는 뜻으로 보면 될 것이다.

예시를 보면서 설명하도록 해보겠다.

위와 같이 사람들이 좋아하는 축구선수 목록 테이블인 tb_soccer_player가 있다고 할때,
방금 설명한 내용대로 축구선수의 name을 GROUP BY 해보도록 하겠다.

Query :  SELECT * FROM tb_soccer_player GROUP BY name;

result :

결과를 보게되면, 원래 테이블은 8개의 행이였는데 5개의 행으로 줄어든 것을 볼 수 있다.
이유는 C.Ronaldo, Kaka, Messi는 값이 2개씩 존재했기 때문에, 그룹화 하여 단 한개의 데이터로 취급한 것 이다.

그렇다면 축구선수의 num으로 GROUP BY를 해봤을 경우를 예상해보자.

Query :  SELECT * FROM tb_soccer_player GROUP BY num;

result : 

예상대로 num중 7,9,10만 출력되는 것을 볼 수 있다.
8개의 행들 중 num 칼럼은 모두 7,9,10들 뿐이었기 때문이다.
그런데 여기서 7번에는 ji-sung도 있고, 10번에는 Zidane도있는데 왜 C.Ronaldo와 Messi가 선택되었을까? 
우리가 따로 정렬에 대한 정보를 입력하지 않았다면 GROUP BY는 내부적으로 시컨스를 기준으로 정렬(sorting)이 이루어진다.  따라서 id값이 작은 순서대로(먼저 들어온 데이터 순) 정렬이 되었기 때문에,
뒤늦게 들어온 ji-sing과 Zidane은 밀려버린 것 이다.

2.HAVING

지금까지는 조건이 없는 GROUP BY문들이었다.
평소 어떤 쿼리를 작성할때 조건을 달고싶었다면 WHERE절을 이용하였는데, GROUP BY에서의 조건은
WHERE로 처리하지 않고 HAVING절을 사용한다. 

그럼 HAVING절을 이용하여 GROUP BY 해보도록 하겠다.
먼저 조건을 주기위해 tb_soccer_player테이블에 선수 몸값 칼럼을 추가해보겠다.

여기서,
축구선수들 중 몸 값이 400이상인 선수들의 목록을 그룹화 하여 보고싶다면,

Query : SELECT * FROM tb_soccer_player GROUP BY name HAVING price > 400; 

result : 

이러한 결과가 나오게 되는 것 이다.

이번엔 조금더 실질적으로 사용되는 예들을 들어보겠다.  
집계함수를 사용하는 것인데, 우선 집계함수에는 어떠한 것들이 있는지 알아보도록 하겠다.

3. 집계함수

집계란 이미 계산 된 것들을 모아서 계산하는 것을 말한다. 즉, 이미 계산된 결과를 어떤 기준으로
그룹화 하는 함수를 집계함수라고한다.

그렇다면 어떤 집계함수들이 있는지 확인해보자.

합수를 사용한 예를들기위해 새로운 테이블 tb_employee를 만들었다.
현재 tb_employee 칼럼으로는 dept(부서), position(직책), headcnt(인원수), salary(급여)가 존재한다.

ex.1) 각 부서당 몇개의 직책이 있는지 알고싶다.

Query : SELECT dept,count(position) position_cnt FROM tb_employee GROUP BY dept;

result : 

ex.2) 각 부서당 전체사원의 수를 알고싶다.

Query : SELECT dept, sum(headcnt) from tb_employee GROUP BY dept;

result :  

ex.3) 직책별로 평균 급여를 알고싶다.

Query : SELECT position, CEILING(avg(salary)) salary_avg from tb_employee GROUP BY position;

result : 

(여기서 CEILING()은 내장함수로 소수점을 버려준다. )

앞으로 공부를 하면서 더 나은 예제가 있다면 또 다시 정리해보자.

SQL의 기법중 JOIN기법에 대하여 알아보도록하자.

참고, SQL이란?

Structed Query Language의 약자로써 해석하자면 '구조화된 질의어'란 뜻이다.

즉, 데이터베이스에서 데이터를 조회, 입력, 수정, 삭제, 저장하는데 사용되는 질의 언어이다.

구조화된 언어라는 말은 일정한 틀이나 패턴이 있다는 뜻이다.

일정한 틀과 패턴에 맞게 조건들을 나열하면 SQL은 원하는 결과를 가져다 준다.

패턴에 맞게 조건들을 선언한다는 의미에서 SQL을 선언적(declarative)언어라고도 한다.

*JOIN*

-JOIN이란 기법은 여러 테이블에 흩어져 있는 정보 중에서 사용자가 필요한 정보만 가져온뒤

가상의 테이블을 만들어 결과를 보여주는 기법이다.

-JOIN에는 오라클에서 사용하는 JOIN문과 모든 제품들에서서 공통적으로 사용 가능한 

표준(ANSI) JOIN 방법이 있다.

우리는 MySQL을 사용하므로 표준JOIN방법을 이용하면되겠다!.

그럼 우선 어떤  종류의 JOIN연산자가 있는지 알아보도록하자.

그럼 이제 실제로 테이블을 생성하여 직접 사용해보자.
오늘의 테스트를 위한 테이블 4 개를 생성해보겠다. 

지금부터 생성한 4개의 테이블을 이용하여 알아보도록 하자.

1. 내부 조인(INNER JOIN)

 - 조건을 사용하여 두 테이블의 레코드를 결합한다.

- 동등 조인, 비동등 조인, 자연 조인 등이 있다.

ex) Select somecolumns From table1 Inner Join table2 ON somecondition; 

1) 동등 조인(EQUI JOIN)

- 두 테이블 사이의 같은 행들을 반환한다.

ex) 각 boy가 어떤 toy를 가지고 있는지 조사한다. (매핑 확인)

SELECT tb_boys.boy, tb_toys.toy FROM tb_boys INNER JOIN tb_toys 
ON tb_boys.toy_id =  tb_toys.toy_id;

 2) 비동등 조인(NON-EQUI JOIN)

-두 테이블 사이의 같지 않은 모든 행들을 반환한다.

ex) SELECT tb_boys.boy, tb_toys.toy FROM tb_boys INNER JOIN tb_toys ON tb_boys.toy_id <> tb_toys.toy_id ORDER BY tb_boys.boy;

참고:여기서 <> ㅡ> 이것은 같지 않음을 나타냄.

3) 자연 조인(NATURAL JOIN)

- 두 테이블에 같은 이름의 열이 있을때만 동작한다.

- ON이 필요없다.

ex) SELECT tb_boys.boy, tb_toys.toy FROM tb_boys NATURAL JOIN tb_toys;

2. 외부 조인(OUTER JOIN)

- 내부 조인과 유사하며 일치하는 것이 없을 경우 NULL로 표시한다.

- 왼쪽 테이블은 FROM 바로 다음에 나오는 테이블이고, JOIN 뒤에 나오는 테이블이 오른쪽 테이블이다.

- 왼쪽 외부 조인(LEFT OUTER JOIN)과 오른쪽 외부 조인(RIGHT OUTER JOIN)이 있다.

- 왼쪽 외부 조인을 사용할 경우 왼쪽 테이블을 오른쪽 테이블에 비교한다. 오른쪽 외부 조인도 그 반대로 동작한다. 

유용한 예) girls가 어느 toys를 가지고 있는지 알아보도록 쿼리를 작성한다.

i) 왼쪽 테이블을 오른쪽 테이블에 비교하는 방법 

SELECT g.girl, t.toy FROM tb_girls g LEFT OUTER JOIN tb_toys t ON g.toy_id = t.toy_id;

 ii) 반대로 했을때

SELECT g.girl, t.toy FROM tb_toys t LEFT OUTER JOIN tb_girls g ON g.toy_id = t.toy_id;

3. 크로스 조인(CROSS JOIN)

- 한 테이블의 모든 행과 다른 테이블의 모든 행을 짝지워 반환한다.

ex) SELECT t.toy, b.boy FROM tb_toys AS t CROSS JOIN tb_boys AS b;

4. 셀프 조인(SELF JOIN)

- 자기 자신을 조인한다.

- 자기 자신을 하나씩 비교하기 위해 사용한다.

- 하나의 테이블로 같은 정보를 가진 테이블이 두 개 있는 것처럼 쿼리를 보낼 수 있다.

예) 각 사람의 이름(name)과 그 사람을 담당하는 보스(boss)의 이름을 가져온다. 

MVC 패턴.
- MVC이란 Model, View, Controller의 약자로 정말 많이 들어봤지만, 자세히는 모르던 개념.
정리하고가자.

1). 모델 1 구조.

- JSP로 구현한 기존 웹 어플리케이션은 모델 1 구조로 웹 브라우저의 요청을 JSP 페이지가 받아서 처리하는 구조이다.

- JSP 페이지에 비즈니스 로직을 처리하기 위한 코드와 웹 브라우저에 결과를 보여주기 위한 출력 관련 코드가 뒤섞여 있는 구조이다. 즉, 내가 만들었던 허접한 쇼핑몰 hoshop또한 모델 1 구조였던것이다.
즉, HoShop과 같이 웹 브라우저의 요청이 곧바로 JSP에 전달된다.
웹 브라우저의 요청을 받은 JSP는 자바빈이나 서비스 클래스를 사용해서 웹 브라우저가 요청한 작업을
처리하고 그 결과를 클라이언트에 출력해준다.
JSP 페이지에서 웹 브라우저가 요청한 것들을 처리한다는 것은 JSP 페이지에 비즈니스 로직을 처리하기 위한 코드와 웹 브라우저에 결과를 보여줄 출력 관련 코드가 섞인다는 것을 의미한다.
프로그래밍 수업시간에 다뤘던것처럼 하나의 JSP 페이지에서 서비스 클래스를 통해서 원하는 작업을
수행하고 그 결과를 출력하는 경우가 많았는데, 이것이 모델 1 구조의 전형적인 예이다.

- 이러한 구조는 JSP 페이지 안에서 모든 정보를 표현(View)하고 저장(Model)하고 처리(Control)되므로 재사용이 힘들고, 읽기도 힘들어 가독성이 떨어진다.

그럼 다음으로 모델 2 구조를 살펴보자.

2). 모델 2 구조.

모델 2 구조는 모델 1 구조와 달리 웹 브라우저의 요청을 하나의 서블릿이 받게 된다.
서블릿은 웹 브라우저의 요청을 알맞게 처리한 후 그 결과를 보여줄 JSP 페이지로 포워딩한다.
포워딩을 통해서 요청 흐름을 받은 JSP 페이지는 결과 화면을 클라이언트에 전송한다.
즉, 서블릿이 비즈니스 로직 부분을 처리하게 되는 것 이다.
모델 2 구조의 특징은 웹 브라우저의 모든 요청이 단일 진입점,
즉 하나의 서블릿에서 처리된다는 점이다.
하나의 서블릿이 웹 브라우저의 모든 요청을 받기 때문에,
서블릿은 웹 브라우저의 요청을 구분할 수 있는 방법을 필요로 하며,
서블릿은 웹 브라우저의 요청을 처리한 후 웹 브라우저에 보여줄 JSP를 선택하게 된다.
모델 2 구조의 이러한 특징 때문에 MVC(Model - View - Controller)패턴에 기반을 두어
웹 어플리케이션을 구현할 때는 모델 2 구조를 주로 사용한다. 
현재 우리 GROUPWARE 또한 모델 2 구조를 사용하고 있다.

- 모델 1은 컨트롤러가 모델이나 뷰를 포함한 MV 형태를 띄고 있고, 모델 2는 완전한 MVC 형태를 적용하고 있다고 볼 수 있다.

모델 2 를 설명하면서 MVC패턴이란 무엇인지까지 설명이 되버린것같다.
그래도 다시한번 MVC패턴이란 무엇인가를 알아보자.

 3). MVC 패턴

모델(Model) : 페이지의 이동간에 파라미터나 값들을 넘겨줄때 
이러한 값들을 담은 인스턴스를 의미한다.

뷰(View) : 비즈니스 영역에 대한 프레젠테이션 뷰(즉, 사용자가 보게 될 결과 화면)를 담당한다.

컨트롤러(Controller) : 사용자의 입력 및 흐름 제어를 담당한다.

사용자는 원하는 기능을 처리하기 위한 모든 요청을 단일 컨트롤러에 보낸다.
모델은 비즈니스와 관련된 상태 정보 및 관련 기능을 제공하는데,
컨트롤러는 이 모델을 통해서 사용자의 요청을 처리한다.
모델을 사용하여 알맞은 비즈니스 로직을 수행한 후 컨트롤러는 사용자에게 보여줄 뷰를 선택하며,
선택된 뷰는 사용자에게 알맞은 결과 화면을 보여준다. 
뷰가 사용자에게 결과 화면을 보여줄 때에는 결과 정보가 필요한데,
컨트롤러가 이 정보를 뷰에 전달해 준다.

MVC 패턴의 핵심은 다음과 같다.
-비즈니스 로직을 처리하는 모델과 결과 화면을 보여주는 뷰가 분리되어 있다.
-어플리케이션의 흐름 제어나 사용자의 처리 요청은 컨트롤러에 집중된다.

즉, 모델은 오직 비즈니스와 관련된 부분을 처리하면 될 뿐 사용자에게 보여줄 화면이나 흐름 제어에 
대해서는 처리할 필요가 없다.
반대로 뷰는 사용자에게 알맞은 화면을 보여주는 역할만 수행하면 될 뿐, 비즈니스 로직이나 흐름 제어 등을 신경 쓸 필요가 없다.
이렇게 모델과 뷰가 분리되어 있기 때문에 모델의 내부 로직이 변경된다 하더라도 뷰는 영향을 받지 않으며, 뷰와 모델이 결합되어 있지 않기 때문에 뷰를 어렵지 않게 변경할 수 있다.

또한, 컨트롤러는 사용자의 요청에 대해서 알맞은 모델을 사용하고 사용자에게 보여줄 뷰를 선택하기만 하면 된다. 
만약 컨트롤러나 모델에 상관없이 새로운 뷰를 추가해 주면 된다. 즉
, MVC 패턴을 사용함으로써 유지 보수 작업이 간단해지고 어플리케이션을 쉽게 확장할 수 있게 되는 것이다.

정리를하자면, 모델 2 구조에서 서블릿은 MVC 패턴의 컨트롤러 역할을 한다.

서블릿은 웹 브라우저의 요청과 웹 어플리케이션의 전체적인 흐름을 제어하게 된다.
또한 모델 2 구조에서 JSP는 뷰의 역할을 담당한다. 비즈니스 로직과 관련된 코드가 없는 점을
제외하면 일반 JSP와 거의 동일한 형태를 취한다는 것이다.

cygwin 설치가 끝났다면, tomcat과 ant를 설정해보도록한다.
우선 빠른 설치를 위하여 먼저 다운을 받도록하자.

 tomcat : http://tomcat.apache.org/download-60.cgi
Core에 있는 tar.gz를 다운 받는다.
(리눅스 기반인 cygwin은 윈도우용 압축파일인 zip 대신 gz을 사용한다.)
이때 Core와 Deployer의 차이를 알고갈 필요가있다.
Core란 실행파일만 가지고 있는 배포파일이고 Deployer는 소스파일까지 가지고 있는 배포파일이다.
그렇다면  실행파일만 있으면될것을 소스파일까지 받을 이유가 뭐가있을까?
답은 이용자가 자신의 사용 방법과 기호에 맞추어 소스파일을 설정하거나 기능을 변경하기 쉽도록 해주는 customize 때문이다.
 

ant :  http://ant.apache.org/bindownload.cgi

다운이 완료되었다면 cygwin을 통하여 다운 받은 해당경로로  이동한다.
이동하였다면 이처럼 방금 다운받은 ant와 tomcat이 존재할 것 이다.

이제 이곳에 압축해제 명령어를 사용하여 두개의 gz을 풀어줘야하는데
풀어주기전에 명령어부터 확인해보도록 하자.

1. 압축하기

아래 명령어를 실행하여 압축 파일을 만들 수 있다. source_files에는 압축할 파일이나 디렉터리 목록을 적는다.

$ tar cvzf filename.tar.gz source_files

실제 예는 아래와 같다

$ tar cvzf image.tar.gz image.jpg
$ tar cvzf images.tar.gz image1.jpg image2.jpg
$ tar cvzf imagedir.tar.gz images

2. 압축풀기

$ tar xvzf filename.tar.gz

실제 예는 아래와 같다.

$ tar xvzf image.tar.gz
$ tar xvzf images.tar.gz
$ tar xvzf imagedir.tar.gz

3. 옵션

tar 명령에 뒤에 따라오는 옵션에 대한 설명은 아래와 같다.

• c(Create): 파일 생성
• x(eXtract): 파일 해제
• v(Verbose): 작업 진행상황을 화면에 출력
• z(gZip): gzip으로 압축 또는 해제
• f  (File)  : 파일명을 다음과 같이 지정한다는 의미
  (다음에 반드시 파일명이 따라와야 함)
  
  예를들어 위의 명령중, tar xvzf image.tar.gz를 보자면,
  image란 파일명으로 압축파일 gzip을 해제하겠다는 뜻이다.
   

여기서 잠깐. 
tar 는 압축 명령어가 아닌 여러개의 파일을 하나로 묶는 명령어이다. 즉, 여러개의 파일을 하나로 묶어주는 일종의 유틸리티 라고 할 수 있다. 따라서 위의 예제나 실제로 받은 tomcat, ant 또한 개별의 파일들이 tar라는 명령어에 의하여 묶인상태로 gzip으로 압축이 된 것 이다. 

ant부터 압축을 풀어준 상태이다.

다음은 tomcat 압축해제상태이다.

이제 압축을 모두 해제했으므로, 환경변수를 세팅해보자.
필자는 .configure에 환경변수 설정을 할 것이다.

.configure에 들어왔다면 자신이 설치한 JDK,tomcat,ant 위치로 환경 변수를 설정해주어야 한다.

(Tip. 위의 설정처럼 환경변수명을 JRE_HOME, CATALINA_HOME 등으로 설정한 이유.
톰캣하위경로에 JDK경로를 설정해주기 위한 JRE_HOME 이라는 환경변수가 선언되어있다.
따라서 환경변수명을 다르게 지정해주면 안되고, 이미 지정되어있는 JRE_HOME이라는 변수에
경로 설정을 해주어야 하는 것이다.
이처럼 tomcat/bin경로에서 catalina.sh에 들어가보면 이미 CATALINA_HOME, JRE_HOME등의 변수가
지정되어있는 것을 볼 수 있다.)

위와 같이 설정을 끝냈다면
. .configure를 입력하여 적용을 시켜준다.
(여기서 . .configure의 의미는?
우선 파일명앞의 ' . '은 숨김파일을 의미한다. 그러므로 .configure는 숨김파일이란 뜻이다.
그렇다면 또 그앞의 ' . ' 은 무엇을 의미할까?
이것은 source라는 명령어와 동일한 기능을 갖는 .(점)이다.!
soure 명령어는 script를 읽어서 실행하게 하는 명령어이다.
즉,
. .configure는 source .configure 와 같은 의미인것이다.)
그러나 매번 cygwin을 접속할때마다 쳐주기엔 참 귀찮은일이다.
따라서 cygwin 실행시 사용자의 별 다른 명령어 입력이 없더라도 자동적으로 실행되는
bash_profile 에 초기에 실행되길 원하는 명령어 . .configure를 적용시켜준다.

(Tip. 위처럼 bash_profile에만 . .configure와 같이 적용을 시켜줘야하는가?
 위에서 말했듯이, bash_profile은 cygwin 접속시에 사용자의 별다른 명령어없이 자동적으로 실행이되는
쉘이기 때문에 이 곳에 . .configure와 같은 설정을 해주었다. 그러나사용자의 별다른 명령어없이
자동적으로 실행되는 쉘은 bash_profile뿐만은 아니다.
먼저 .bash_profile이 있으면 이파일을 찾고, 이파일이 없으면 .bash_login을 찾고, 이것도 없으면 .profile을 찾아 실행한다. 만약 .bash_profile이 있을땐, .bashrc파일도 있으면 이 파일도 읽어들인다.
따라서 방금 말한 쉘들에만 적용하면 되는 것 이다.)

이제 마지막으로 내가 설정한 환경변수를 확인해보고 설치를 마치도록한다. 
(env - 환경변수의 목록을 보여준다.)

cygwin 설치방법.

www.cygwin.com에서
Install Cygwin ㅡ> setup.exe를 클릭하여 다운로드를 받는다.

다운받은 setup.exe를 설치를 시작해보자.
다음과 같은 화면처럼 별다른 설정이 필요없는 경우 <다음>을 클릭한다.

Choose A Download Site화면이 나오게되면 선택되어있는 것 처럼
 ftp://ftp.jaist.ac.jp를 클릭하여 <다음>을 클릭한다. 
국내에서도 KAIST 등 학교나 기관에서 서버를 제공하고 있지만,
일본의 JAIST 속도가 빨라서 여기서 받는 것이다.
왜 이러한 site를 클릭하여 다운을 받는지 궁금하다면 미러링의 개념이 필요하다.
(mirroring :  특정 지역의 사용자들이 더욱 빠르게 액세스할 수 있고,
원래 사이트에 몰리던 부하를 분산시키기 위해, 한 사이트에 있는 파일의 일부 또는 전부를 복사하여
다른 웹사이트에 저장, 운영하는 것이다. )

다음으로 Select Package 화면에서 무엇을 설치할지는 본인 선택이지만, 
나같은 경우엔
Editor ㅡ> vim (윈도우로 치면 메모장과 같은 기능임.)
web ㅡ>wget,wput 을 선택 설치하였다.
(wget ㅡ>ftp 서버 등에 파일을 다운로드할 때 간단하게 사용할 수 있는 프로그램)
(wput ㅡ>ftp 서버 등에 파일을 업로드할 때 간단하게 사용할 수 있는 프로그램)
사용법. wget http://changhozz.tistory.com/download/뭐뭐파일.tar.gz
         

설치가 완료되었다면 이제 cygwin에 tomcat과 ant를 설정해보도록 하자.

 쉘의 위치

 환경 설정 파일 목록

 매직넘버와 주석

 변수 설정 및 해제

 전역 변수 설정 및 해제

 지역 변수 설정 및 해제

 set 명령어를 이용한 배열 선언.

 산술 계산

 조건문 예제

 조건문 실행 결과

 루프문 예제

루프문 실행 결과 

함수 예제

 함수 실행 결과

산술 계산 예제