2.데이터의 검색...http://sqler.pe.kr/에서

[레인]

1. T-SQL SQLServer의 언어에 대한 간단한 소개

이구이구.. 날이 너무 더버서 정신이 가물가물할 정도네요..

밤에도 이리 더워 버리면 어케할지 걱정입니다. ^_^  -  더위 조심하세요.

체력이 힘입니다 힘!!!!

이번에 소개해드릴 내용은? 이제부터 배우실 SQL이라는 녀석 입니다.

SQL은 Structured Query Language 라고 불리우며 많은 분들이 SQL을 SELECT Query Language

라고 알고 있기도 하지요. ^_^ 전혀 상관없는 얘기지요?

앞의 DBMS를 왜 만들었는가를 얘기 드렸지요?

먼저 자료를 삽입하고 싶습니다.

그래서 코난이가 "야 SQL서버!!! 코난이는 잘생겼다를 너가 저장해"

그럼 SQL서버는 저장 안할겁니다. 거짓말이기 때문이지요. -_-;;;;;;;

더중요한건 SQL서버는 이런 구문 규칙을 알아듣지 못합니다. 

그래서 사람과 컴퓨터(SQL서버)가 통신하기위한 언어의 필요성이 생긴 거지요.

그게 바로 SQL이라는 겁니다.

SQL은 ANSI-92 ISO 표준 관계형 DBMS에 접근에 데이터를 처리하기위한 언어 입니다.

표준안이 이미 나와 있지요.

중요한건 저희가 배울 MSSQL서버 2000의 SQL입니다.

MS에서 만든 SQL서버는 T-SQL 이라는 SQL을 사용 합니다. 이 T-SQL이란 녀석이 어떤건지

감이 잡히실 거에요. 

바로 MS에서 ANSI 표준 SQL에 약간의 기능을 더 추가해 보완해 만든 녀석이 

바로 T-SQL이란 녀석 입니다.

가장 중요한 SQL서버를 제어하기 위한 언어 이지요.

많은 분들이 SQL서버의 엔터프라이즈 매니져(Enterprise manager)가 

많은 그래픽적인 요소로 액세스처럼 사용이 가능하기 때문에 

어렵고 프로그래밍 언어같은 T-SQL을 배우시기보다는 MSSQL서버의 엔터프라이즈 매니져만

사용해 MSSQL을 다루시는 분이 많이 계십니다.

좋지 않은 방법 입니다. 기초가 없는 작업이기 때문입니다.

아울러 다른 웹프로그래밍언어나 윈도우 어플리케이션에서 DB를 접근하기 위해서는

이 SQL을 이용해야 합니다. 엔터프라이즈 매니져로는 하실 수 없기 때문이지요.

혹시 이 글을 보시는 분들중 그런분이 계시다면? 꼭 시간을 내셔서 다시 SQL구문을

배우시길 권장합니다.

앞으로 한동안은 어쩌면 프로그래밍 언어에 생소하신 분들은 어렵고 힘든 부분이 될수도

있습니다. 반대로 약간의. 아주 약간의 프로그래밍 경력이 있으시면 쉽게 배우실 수 있구요.

T-SQL을 사용하시는 이유를 조금은 아시겠지요? ^_^

간단히 소개를 더 해드리자면. 

데이터를 검색하기 위한 데이터 검색어 

데이터를 조작하기 위한 데이터 조작어

데이터를 정의하기 위한 데이터 정의어로 보통 나눕니다.

구분	SQL 명령
데이터 제어어(Control Language)	GRANT, DENY
데이터 조작어(Manipulation Language)	SELECT, INSERT, DELETE, UPDATE
데이터 정의어(Definition Language)	CREATE, ALTER, DROP

SQL을 잘 모르시는 분도 아마 저 데이터 조작어 중의 SELECT 라는 문장은 눈에 익으실 겁니다.

물론 크게 상관 없는 이야기지요.

이상의 T-SQL에 대해서 살펴 보신다면

기존의 SQL구현 이상의 기능을 제공한다.

 - SELECT 리스트에 SELECT 문을 사용할 수 있는 보통 부질의(Sub-Query)사용 가능.

 - 확장된 기능을 사용한 질의 가능.

DB를 구축하는 가장 큰 이유인 데이터를 쌓아둔후 데이터를 쉽고 편하게 검색 하고자 할때

조금더 발전된 SELECT 구문을 이용하여 다양한 검색이 가능하게 하는 것이지요.

많은 시스템 함수를 제공한다.

 - 문자열 조작 함수들

 - 데이터형 변환 함수들

 - 날짜 데이터 조작 함수들

많은분들이 잘못 생각하시는 것중 하나 인데요. SQL서버 역시 여느 프로그래밍 언어의

내장 함수처럼 많은 데이터 처리를 위한 함수를 제공합니다.

거의 비주얼 베이직 만큼의 다양한 함수를 제공하지요. 

기타 특별한 연산자들이 제공된다.

 - CUBE, ROLLUP

 - 데이터웨어 하우징(data warehousing) 계산함수(aggregation)

데이터 웨어 하우징(추후에 배우실 겁니다. ^_^) 에 사용되는 다양한 계산함수(aggregation)

등의 추가역시 마찬가지이지요.  

T-SQL 프로그래밍 기능

 - 변수 선언기능 @@이라는 전역변수(시스템 함수)와 @이라는 지역변수가 있습니다.

 - 지역변수는 사용자가 자신의 연결동안만 사용하기 위해 만들어지는 변수이며

   전역변수는 이미 SQL서버에 내장된 값입니다.

- 자료형(data type) 제공을 합니다. 즉 int, float, varchar등의 자료형을 의미 하지요.

- 연산자(Operator) 산술연산자( +, -, *, /)와 비교연산자(=, <, >, <>) 논리연산자(and, or, not)

   사용이 가능 합니다.

- 흐름제어 기능 IF-ELSE와  WHILE, CASE-THEN

- 주석기능

   한줄 주석 -- 뒤의 내용은 주석.

   범위주석 /*   주석으로 둘 내용 여러줄도 가능 */ 

다음은 SQL서버의 질의 최적화 기능 입니다.

아주 놀랍게도 SQL서버는 해당하는 쿼리에 대해 자동으로 최적화를 시켜 이를 처리합니다.

사용자의 개입이 그다지 필요 없다는 의미 이지요.

비용기반 질의 최적화

 - 데이터를 접근하는 가장 좋은 방법을 결정한다.

 - 사용자는 질의가 어떻게 실행 되는지에 관계없이 원하는 질의를 정의할 수 있다.

보시는 바와 같이 예상 실행 계획을 쿼리를 판단해 생성하고 처리 합니다.

최적화 힌트의 제공

 - 최적화기의 동작을 조절할 수 있다.

데이터의 양과 분포에 대한 통계 데이터의 유지

 - 요청된 작업에 가장 적절한 것으로 판단되는 같은 계획을 평가하는데 사용한다.

SHOWPLAN

 - 선택된 실행 계획(execution plan)을 볼 수 있다.

이러한 어려운 DB관리자쪽 작업의 많은 부분을 SQL서버가 자동으로 처리해 주며 아울러

그래픽 적으로 이를 보여 줍니다. 아주 편리한 툴이지요.

저장 프로시져 이야기 입니다.

이는 7장에서 자세히 다룰 예정이니 이곳에서는 맛만 보실 겁니다.

길고 복잡한 SQL구문을 서버와 클라이언트가 통신을 하면서 전송을 합니다.

하지만 SQL구문이 길면 넷웍 부하 역시 만만치 않으며 서버에서 SQL구문을 자신이 처리가

가능하게 파싱(Parsing - SQL구문에 오류가 있는지 검사하는 작업) 할때 역시 부하가 커지지요.

그렇다면? 이 파싱된 SQL구문을 서버에 별명을 잡아 단지 클라이언트는 호출만해서

사용 가능하게 한다면? 이게 바로 저장 프로시져라는 녀석 입니다.

SQL서버 데이터베이스에 저장된 SQL문장들의 집합

- 사용자는 복잡한 질의와 트랜젝션을 저장된 절차 기능에 코드화하고 

   이를 응용 프로그램에서 직접 호출할 수 있다. - 캡슐화기능이 가능 합니다.

동적인  SQL Batch를 보다 빠르게 처리

 - 수행될 때 마다 파싱되고 최적화 되는 과정을 없앤다. - 이미 파싱된 바이너리 화일로 서버에

   존재합니다.

매개 변수 가능

 - 다른 입력에 대해 동일한 프로시져를 사용 할 수 있다. - 다양한 처리가 가능합니다.

변경 대상의 최소화

 - 프로그램을 개발할 때 하나의 프로시져에 논리를 포함함으로서 변경의 범위를 최소화한다.

   일종의 업무 규칙이지요.

보안 기능 제공

 - 데이터에 대한 직접적인 권한을 제공하지 않고 저장 프로시져를 통한 

 데이터를 접근할 수 있도록 할 수 있다. 데이터에 대한 보안성을 높일 수 있습니다.

다음은 확장된 저장 프로시져 입니다.

개발자들이 프로그래밍 기능을 확장하여 SQL서버 이외의 자원들을 

쉽게 접근할 수 있게 한다.

 - 메시지 통합, 보안통합, SQL 엔터프라이즈 매니져의 많은 기능들이 

   확장된 저장 프로시져를 사용하여 구현 되었다.

외부의 동적 연결 라이브러리(DLL)로서 확정된 저장 프로시져 기능을 작성한다.

Open Data Services(ODS)

 - 확장된 저장 프로시져를 구성할 수 있도록 하는 API

SQL서버 자체의 확장이 가능하다

 - 서버 엔진(Server Engine)을 직접 고치지 않고도 많은 기능들을 추가할 수 있다.

 

몇번 보셨을 만한 붉은 글씨 부분의 dir *.exe 라는 부분 입니다. ^_^ dir은 분명

명령 프롬프트의 명령어 이지요? 이런 다양한 처리도 가능하다는 의미 입니다.

자. 자세한 생성법과 구문은 7장에서 배우실 겁니다. ^_^

자 약간 서론이 길어 졌군요. 이제 많은 분들이 관심있어 하는 관심사를 말 드리고 싶군요.

"MS의 T-SQL은 다른 DBMS의 언어, 오라클등의 DBMS와 호환성이 없지 않나요?

그런거 배워도 나중에 회사에서 오라클을 쓰면 새로 배워야 하는거 아닌가요?"

대답을 먼저 드리면? 아닙니다.

위에서 말 드린대로 MS의 T-SQL은 ANSI - SQL의 표준을 따르며 약간만 수정된 것입니다.

오라클의 SQL PLUS(오라클의 SQL) 역시 마찬가지로 ANSI 표준에서 약간 수정해 만들어진

거지요.

말하고자 하는 바는 하나 입니다. 코난이도 MS의 T-SQL만 조금 알지 오라클의 SQL은 잘

모릅니다. 하지만 SQL문의 사용은 거의 비슷해서 강좌도 조금 제공하고 있는 중이지요 ^_^;;;

정말로 거의 유사 합니다!!!  MSSQL의 T-SQL을 배우시고 오라클의 SQL을 배우시려면?

그 차이점만 쉽게 알아가시면 된다는 것이지요. ^_^ 물론 다른 DBMS인 IBM의 UDB나

사이베이스, 인포믹스 역시 마찬가지 입니다. ^_^

자!  자신감을 가지시고 하나만 잘 알아두시면 두고두고 써먹는다는 것만 주지해 주세용~ ^_^

그럼 다음은 실제 데이터를 검색하는 SELECT 쿼리를 소개해 드리지요.

그럼 다음 강좌에서 뵙지요~~~  수고하셨습니다. 

****************************************************************************

2. SELECT 구문

자~ 이제부터는 가장 중요한 데이터의 검색 이야기를 드릴 차례군요.

살짝 말을 드렸지만 데이터를 다양한 방식으로 검색하는 것이 가장 중요한 DBMS 구축의

목적이기 때문에 이 SELECT 구문은 기초중의 기초가 되는 것이지요. ^_^

이 SELECT와 같은 쿼리를 실행하기위한 툴은? 쿼리 분석기라고 부른 답니다.

쿼리 분석기는 시작 -> 프로그램 -> MS SQL서버2000 -> 쿼리 분석기

를 실행하시면 

 

이런 화면이 뜹니다. 

SQL Server는 컴퓨터 이름을 말하며 저의 경우는 컴이름이 KONAN 입니다. 

아울러 Connect using은 두가지로 NT인증과 SQL서버 인증 두가지 방식이 있지요?

간단히 NT인증은 NT의 인증을 그대로 SQL서버의 로긴으로 사용한다는 의미이며

SQL Server 인증은 SQL서버고유의 인증을 사용한다는 의미 입니다.

저를 따라 SQL2000을 설치 하실때 분명 혼합인증으로 설치를 하셨을테니 이곳에서

SQL Server 인증이 사용 가능 하시겠지요? 아울러 sa계정만이 처음에 생성된

SQL서버 로긴이며 설치시 암호를 지정 하셨다면? 이곳에 암호를 적어 주심 됩니다.

계정을 생성하는 방법이나 수정하는 방법 아울러 암호를 변경하는 방법은 18장

SQL서버 관리와 모니터링에서 다룬 답니다.

확인을 누르시면?

이러한 쿼리 분석기가 실행된 화면이 보이고 커서만 깜빡 깜빡 하는 

앞이 깜깜한 상황이 보이실 겁니다.

자 이제 시작 하시는 겁니다. 앞으로 SQL서버2000을 배우시면서?

시간의 50% 이상은 바로 이 쿼리 분석기를 사용하게 되실 겁니다.

이 툴에 대한 사용 방법은? 이것저것 눌러 보세요.

이것저것 해보신다고 해서 망가지거나 고장나지는 않습니다.

간단하지요?

잠시 들어가시기 전에...  MSSQL서버 구조는

이렇게 MSSQL서버는 여러개의 데이터베이스로 이루어져 있으며

데이터베이스는 여러개의 테이블로 이루어져 있습니다.

아울러 이 테이블에 데이터가 들어 가는 것이지요.

테이블의 구조를 조금 살펴 본다면?

이런식으로 실제 데이터가 들어가 있겠지요?

이 테이블의 이름을 살짝 설명 드리자면?

라고 부른 답니다.

로우(Row)나 레코드(Record)중에서 관계형 데이터베이스는 대부분 로우라고 부르며

컬럼(Column) 또는 필드(Field)중에서는 컬럼을 많이 사용 한답니다.

이것 외에도 도메인(NT의 도메인이 아니라 관계형 DB용어 입니다.) 튜플 등의

용어가 있으나 이 설명은 그다지 신경 끄셔도 됩니다.

앞으로는 컬럼, 로우라고 코난이는 부르게 될 겁니다. ^_^

역시나 중요한건 데이터 입니다. 위의 표만해도 상당히 의미 심장한 데이터가

들어 있지요. ^_^;;;;

그럼 실제 SELECT를 알아 보지요.

주어진 테이블로부터 적절한 행들과 컬럼들을 선택한다. 바로 이것이 SELECT의

목적 입니다.

SELECT 문의 기본구성

 - SELECT : 검색할 컬럼들을 지정

 - FROM : 검색할 테이블을 지정

 - WHERE : 검색할 테이블 내의 행을 결정 WHERE절이 없는 경우에는 

    해당 테이블의 모든 행들을 검색한다.

 - GROUP BY : 결과 데이터를 그룹화 한다. HAVING 으로 그룹된 데이터를 결정한다.

 - ORDER BY : 데이터를 정렬한다.

의 식입니다. 아직 잘 모르시겠죠? 이제 많은 샘플을 보여드릴 겁니다.

의 식으로 저 허연 부분에 질의를 사용하시는 겁니다.

 

select @@servername, @@version

말 드렸듯이 위의 질의를 복사해서 붙이신후 실행해 보세요.

SQL서버는 SELECT와 같은 예약어에 대해 

SQL서버는 대문자, 소문자 구별을 하지 않습니다.

개인적으로는 저렇게 소문자로 적었지만 예약어의 경우 대문자로 표기하시면?

추후 100라인이 넘어가는 질의 등에 대해서 한눈에 보기 편하실 겁니다.

아울러 글자 색깔이 파랗게 빨갛게 슥슥 변하지요? SQL서버 쿼리 분석기의 인텔리 센스

기능으로 예약어에 대해 자동으로 변한답니다.

그리고 실행은 

처럼 제가 붉은 네모를 해둔 초록색 플레이 버튼을 누르시거나 

컨트롤 + E 키를 누르시거나 ALT + X 키 또는 펑션키 5번 F5 키를 누르셔도 됩니다.

개인적으로는 속편하게 F5키를 대부분 사용 한답니다.

그럼 위의 그림처럼 아래 부분에 KONAN, Microsoft SQL Server ..... 어쩌구 하는

결과가 보이실 겁니다 그렇지요? ^_^ (저와 다를수 있습니다.)

자 첫번째 질의는 바로 SELECT(가져와라) 서버이름과 SQL서버의 버젼 정보를

입니다. 저건 시스템 함수라고 불리우며 시스템에 저장된 데이터를 조회할때 사용 합니다.

우선은 그러려니 하시고 다음 질의는

 

SELECT 'Konan이는 잘생겼다.'

SELECT 12345

라고 해 보세요. 놀랍게도 SQL서버가 거짓말을 합니다. 그쵸? 코나니가 잘생겼다구.. T.T

이렇게 문자열이나 숫자만 단순히 SELECT도 가능합니다.

다음은 주석 입니다.

 

--SELECT 'Konan이는 잘생겼다.'

SELECT 12345

--는 한라인 주석을 의미해서 글자가 초록색으로 변하면서 실행이 안됩니다.

 

/* 

SELECT 'Konan이는 잘생겼다.'

SELECT 12345

*/

/* 문자열들 */ 로 묶인 부분역시 실행이 안되며 주석처리 됩니다.

여러 라인에 주석을 사용하실때 편리 하지요.

다음은

원하시는 부분만 검색을 해 볼까요?

  

select @@servername, @@version

이 질의를 쿼리 어낼라이져에 붙이신후

식으로 쉬프트키와 커서 또는 마우스 드래그로 블럭을 잡으셔서 배경과 반전되게 하신후

실행을 해보세요.

결과는 선택된 부분만 나온다는 것을 아실 겁니다.

조금 쉽지요? 이제 실제 데이터를 가지고 처리를 해 보도록 합지요 ^_^

SQL서버를 설치하실때 설치 옵션중 샘플 데이터베이스를 설치를 모두 하셨을 겁니다.

그러면 시스템에 Northwind라는 데이터베이스와 Pubs라는 샘플데이터베이스가

설치 된답니다. 모두 설치 되셨을 겁니다. 

자 오른쪽 윗부분의 노란색 원기둥에 master라고 적혀 있을 겁니다.

쿼리 어낼라이져는 기본적으로 이렇게 SA 계정은 

master database를 사용하게 됩니다. 

저희가 다루어야할 데이터베이스는 바로 Pubs라는 데이터베이스 입니다.

이를 변경하시려면?

이렇게 USE Pubs 라고 적어주셔서 쿼리로 바꾸시거나 오른쪽 원기둥 부분을 클릭해

여러 데이터베이스들중 Pubs로 바꿔 주심 됩니다.

초보 분들이 많이 실수하는 부분이지요.

둘중 하나의 작업으로 사용 데이터베이스를 변경 하신후

 

SELECT * FROM titles

이렇게 이번엔 FROM이라는 키워드가 보이는 쿼리를 실행해 보지요.

아래부분에 쫘악~ 뭔가가 나오면서 결과가 보이실 겁니다.

이부분이 결과창 부분으로 이미 살짝 말 드렸지만 쿼리에 맞는 데이터를 보여주는

부분이랍니다.

쿼리를 설명 드리자면? 

titles라는 테이블에서(FROM) 모든것을(*) 가져와라(SELECT) 라는 의미 입니다. 

어떠세요? 쉽지요?

찬찬히 저 쿼리를 음미해 보세요. ^_^ 

에러가 나오셨다면? 더 좋은 상황입니다. 하나라도 더 고민해 보신 거니까요.

(코난이는 저 첫번째 SELECT를 하기위해 처음에 SQL6.5로 3일을 해맸습니다. 

저도 초보였습니다.)

자 첫번째 질의를 마치심을 축하 드립니다.

찬찬히 저 한줄의 SELECT 구문을 봐 보신후 다음 강좌에서 뵙도록 하지요

****************************************************************************

3. SELECT에서 컬럼의 선택

자 첫번째 질의를 잘 마치셨나요? 추카 드립니다. 시작이 반이라고 이제 반은~ 성공하신

겁니다.  자 이번에는 컬럼의 선택이라는 주제로 여러가지를 봐 보지요.

앞의 절에서 SELECT * FROM TITLES 라는 질의를 하셨을 겁니다. 글쵸?

여기서 *는 모든 컬럼을 가져 온다는 의미 였습니다. 어떠신지요?

이해가 되시나요?

저 질의를 실행 하시면?

결과 부분에

이러한 식으로 결과가 보이실 겁니다. 아울러 컬럼의 이름은 title_id , title, type 등의

컬럼 이름이 보이실 겁니다. 자 여기서 *로 아래의 모든 컬럼을 보는게 아니라 원하는 컬럼만

선택해 보는 것이 오늘의 목표 랍니다.

자 말드린  title_id , title, type 컬럼만 검색을 해 보지요.

 

SELECT title_id, title, type FROM TITLES

결과를 봐 보세요.

다음은 컬럼이름을 변경하는 샘플 입니다.

  

SELECT title_id AS 책ID, title AS 책제목, type AS 종류별 FROM TITLES

결과의 컬럼이름을 확인해 보세요. 위의 AS는 ANSI 표준 입니다.

 

SELECT 책ID=title_id, 책제목=title, 책종류=type FROM TITLES

이런식도 가능하나 ANSI 표준인 위의 방법을 사용하시면 추후 달리공부가 줄겠지요?

다음은 결과셋에 문자열 추가 입니다.

 

SELECT title_id, '책종류 : ', type FROM TITLES

그러면? 결과셋 부분에 '책종류' 라는 문자열이 추가 될 겁니다.

여기서 잠깐...

결과 부분이 표 형태로 선이 그려져 나오지요?

이를 바꾸셔서 텍스트만 나오게도 가능 합니다.

의 식으로 쿼리 메뉴의 텍스트로 결과표시를 하시면? 결과를 텍스트로 보실수 있습니다.

선택하신후 다시 결과를 보시면? 텍스트로 나올 겁니다.

이는 컨트롤 + T 키를 누르시면? 텍스트 형태가 되며 다시 표형식(그리드)으로 보시려면?

컨트롤 + D 키를 누르시면 됩니다.

웃차 이제 컬럼의 선택 절을 정리해 보면요..

컬럼의 선택

SELECT 키워드 다음의 select_list에 컬럼들을  열거하면 된다.

 - SELECT column_name[, column_name…]

그냥 컬럼 이름을 나열만 하심 된답니다.

컬럼 순서의 재배열

 - SELECT 키워드 다음에 열거하는 순서에 따라 결과로 나타나는 컬럼들의 순서가 결정된다.

 순서를 바꿔서 나열하면? 결과도 바뀌어서 나열 된답니다. 

Literal 의 사용

 SELECT column_name | ‘string literal’[, column_name | ‘string literal’…]

 - SELECT문의 결과에 문자열을 추가하여 읽기 쉽게한다.

문자열 추가 샘플 입니다. 

컬럼 제목의 변경

 SELECT column_heading = column_name[, column_name…] 또는

 SELECT column_name column_heading[. Column_name…]

 - 컬럼의 제목을 컬럼 이름이 아닌 다른 것으로 변경할 수 있다.

여기서 AS를 사용하심도 가능해 지지요.

자아 그럼 약간 다른 이야기를 해 드리지요.

조금더 깊은 이야기 입니다.

먼저 Pubs라는 데이터베이스 이야기 입니다.

이 데이터베이스에 어떤 테이블들이 존재 하는가 궁금 하시죠?

가장 쉬운 방법은 엔터프라이즈 매니져를 실행 하시고.

이렇게 서버를 선택 하시고 데이터베이스를 선택 하신후

테이블을 클릭 하시면? 오른쪽 화면에 테이블리스트가 보입니다.

물론 제가 언급 드린대로 쿼리 분석기로도 가능 하지요.

테이블 리스트를 보는 방법은 세가지 방법 정도가 있습니다.

먼저 첫번째 방법으로 저장 프로시져를 이용한 방법 입니다.

 

sp_tables

이것은 이미 생성되어 있는 저장 프로시져라고 불린답니다. 저장프로시져는

추후 7장에서 지겹게 보시게 될겁니다. -_-;;

우선은 사용하는 방식만 알아 두시길 바랍니다. ^_^

두번재 방법은 데이터베이스의 시스템 테이블을 이용하는 방식 입니다.

하나의 데이터베이스의 개체들(테이블 같은것)들은 모두 sys~로 시작하는 테이블에

저장이 되게 된답니다. 이 데이터베이스 객체들도 4장에서 많은 설명이 있을 겁니다.

바로 sysobjects라는 테이블에 저장이 되는 것이지요.

 

select * from sysobjects where type = 'U'

처음 보시는 where절이 보이시지요?

이것은 바로 뒤에 보실 내용입니다. 간단히 조건으로 type이 'U' 라고 된 녀석만 찾는거지요.

세번재 방법은 View를 이용하는 것입니다.

뷰는 일정의 거울이라고 생각 하세요.

테이블등에 여러 처리를 하기위해 일종의 거울을 만들고 사용자는 이 거울만 보게하며

실체를 보지는 못하게 막는다고 생각하시면 됩니다. 이 뷰역시 7장에서 지겹게 보실 겁니다.

 

select * from INFORMATION_SCHEMA.TABLES

select table_name from INFORMATION_SCHEMA.TABLES

인포메이션 스키마 뷰라는 이름만 긴 녀석에서 조회를 하는 방식 이지요.

위쪽 질의와 아래쪽 질의의 차이는? 컬럼을 선택해서 이름만 본다는 겁니다.

뭔가 많은 새로운 얘기가 나오지요?

아직 난해하다면 그러려니 하고 강좌를 계속 봐 나가세요. 한번 쭉 보심 아실 겁니다.

다음은 컬럼의 리스트를 보고 싶으실때 입니다.

역시 첫번째로 저장 프로시져를 이용한 방식으로 titles 테이블의 

컬럼 리스트를 보고 시프시다면?

 

sp_columns titles

두번째로 syscolumns 테이블에서 컬럼리스트를 검색하는 방법은

 

select * from syscolumns where id = object_id('titles')

역시 잘 모르시겠으면? 그냥 사용만 하셔도 문제 없답니다.

세번째로 인포메이션 스키마 뷰를 이용한 방식으로

 

select * from INFORMATION_SCHEMA.COLUMNS where table_name = 'titles'

select column_name from INFORMATION_SCHEMA.COLUMNS where table_name = 'titles'

이렇게 세가지 정도의 방식을 소개해 드립니다.

이제 테이블의 리스트가 궁금하시거나 어떤 테이블내 컬럼리스트가 궁금하시면?

위의 방법중 골라서 사용하심 되겠지용? ^_^

개인적으로는 세번재 방법 사용을 좋아한답니다. ^_^

자. 이렇게 여차직차 해서 대강의 말을 드렸군요.

조금 힘드셨을지도 모르겠네요. 자 아직 가야할 길이 머니 님들도 힘 내시구..

지두 힘 내겠숨다!!!!

다음강좌는 문자 변환, 수치변환, 날짜변환, 데이터 변환 함수, 시스템 함수 등에 대해새

자세히 알아보는 시간을 가지지요 ^_^

수고하셨습니다.

****************************************************************************

4. 문자 변환, 수치변환, 날짜변환, 데이터 변환 함수, 시스템 함수

이번에 소개해 드릴 내용은 데이터 타입과 함수 입니다.

데이터 타입이란?

테이블에 데이터를 저장 하지요? 이때 문자열형이란 어떤 문자가 들가는 것이고..

숫자형이란? 특정한 숫자가 들간다는 것을 아실 겁니다.

이렇게 문자, 숫자는 또한 여러가지 데이터 형으로 나뉘어 집니다.

이를 알아보는 자리가 되구요...

함수란? 어떤 특정한 처리를 위한 개체로 함수명(처리방식)을 주면?

그 함수의 규칙되로 결과가 나오는 일종의 상자 입니다.

이 마법의 상자들중 하나를 고르고 상자가 원하는 방식대로 입력을 주면?

우리가 원하는 결과를 이 상자가 보내주는 거지요. ^_^

SQL서버에도 함수가 있냐구요? ^_^;; 무쟈게 많습니다.

먼저 데이터 형입니다. 이는 실제 5장의 테이블의 생성 부분에서 자세히 배우실 겁니다.

그러니 이곳에서는 개념만 잡으시길 바랍니다.

Integers

bigint

sql2000부터 생긴 데이터 형으로

-2^63 (-9223372036854775808) 부터 2^63-1 (9223372036854775807) 까지의 정수를 지정 가능 합니다.

int

-2^31 (-2,147,483,648) 부터  2^31 - 1 (2,147,483,647)까지의 정수를 지정 가능 합니다.

smallint

2^15 (-32,768) 부터  2^15 - 1 (32,767)까지의 정수를 지정 가능 합니다.

tinyint

0 부터 255 까지의 정수를 지정 가능 합니다.

　

bit

bit

1 또는 0  이라는 데이터를 저장 가능하며 참, 거짓에 사용 됩니다.

　

decimal and numeric

decimal

 -10^38 -1  10^38 -1 까지의 수에 대해 정밀도와 크기를 사용자에 의해 지정 가능 합니다.

numeric

decimal 형과 같습니다.

　

money and smallmoney

money

돈과 관계되는 데이터 타입으로  -2^63 (-922,337,203,685,477.5808) 부터 

2^63 - 1 (+922,337,203,685,477.5807)까지의 수치를 사용가능 하며 

1000단위 컴마를 사용 가능 합니다. 천조 까지의 데이터를 처리 가능합니다.

smallmoney

 -214,748.3648 부터 +214,748.3647 까지 사용 가능하며 1000단위 컴마 지정이 가능 합니다.

　

Approximate Numerics

float

부동(정확하지 않은) 소수 데이터 형으로  -1.79E + 308 부터  1.79E + 308 까지의 데이터 

사용이 가능 합니다.

real

 -3.40E + 38 부터 3.40E + 38 까지의 부동 소수 데이터 형입니다.

datetime and smalldatetime

datetime

1월  1일, 1753년, 부터 12월 31일, 9999년, 까지의 데이터를 1000분의 일초 단위로 

지정 가능 합니다.

smalldatetime

1월 1일, 1900년, through 6월 6일, 2079년, 까지 1분 단위 정확도로 지정 가능 합니다.

　

Character Strings

char

8,000바이트(문자)까지 지정 가능한 유니코드가 아닌 고정길이 데이터 형입니다.

varchar

 8,000바이트 까지 지정 가능한 유니코드가 아닌 가변길이 데이터 형입니다.

text

 2^31 - 1 (2,147,483,647) 유니코드가 아닌 가변길이 데이터형으로 2기가 까지 지정 가능 합니다.

Unicode Character Strings

nchar

4,000바이트 까지 지정 가능한 유니코드 고정길이 데이터 형입니다.

nvarchar

4,000바이트 까지 지정 가능한 유니코드 가변길이 데이터 형입니다.

ntext

2^30 - 1 (1,073,741,823) 즉, 1기가 까지 지정 가능한 가변길이 유니코드 데이터 형입니다.

Binary Strings

binary

8,000 바이트 까지 이진 데이터를 지정 가능한 고정길이 데이터형입니다.

varbinary

8,000바이트 까지 이진 데이터를 지정 가능한 가변길이 데이터형입니다.

image

2기가까지 이진 데이터를 지정 가능한 가변길이 데이터형입니다.

　

Other Data Types

cursor

커서 지정에 사용하는 형입니다. 커서에서만 사용 합니다.

rowversion

sql2000에서 timestamp 형에서 변경된 데이터 형으로

데이터베이스에 걸쳐 유일하게 사용하는 고유 값을 지정 하는 

데이터 형입니다. 해당 로우가 변경될시 함께 변경됩니다. timestamp형이라 보통 불립니다.

sql_variant

sql2000부터 생긴 데이터 형으로 text, ntext, timestamp, 과 sql_variant 형을 제외한

 모든 데이터형을 저장 가능한 데이터 형입니다.

table

sql2000부터 생긴 데이터 형으로 테이블과 같은 2차원 데이터를 저장하기위한 데이터 형입니다.

uniqueidentifier

어떠한 경우라도 중복되지 않는 항상 고유한 값을 저장하는 데이터 형입니다.

새롭게 생긴 샘플만 알아 보도록 하지요. 먼저 많은 분들이 궁금해 하시는

table형 데이터 입니다.

 

DECLARE @T TABLE (id int, name varchar(8))
INSERT @T values(10,'Joe')
select * from @T

다음은 모든 데이터를 지정 가능한 sql_variant 형입니다.

VB의 데이터형과 비슷 하다고 생각하심 되겠지요?

 

DECLARE @T1 sql_variant, @T2 sql_variant
set @T1 = 12345
set @T2 = '문자열'
select @T1
select @T2

개인적으로는 이런 variant형은 그다지 필요 없을듯 하지만? 개발자 쪽에선 차라리

쉬울수도 있겠지요.

자세한 이야기는 테이블 생성 편에서 드릴 겁니다.

많이 질문 받는으로 var형과 var가 안 붙은 데이텨형의 차이 입니다.

varchar과 char의 차이를 보여 드리면...

1번 char(10)

2번 varchar(10)으로 컬럼을 잡습니다...

여기에

kimdaewoo를 넣는다고 해 보지요...

그럼 1번에는

'kimdaewoo '가 저장 됩니다.

즉 9자의 알파벳과 공백값이 하나가 들어간다 라는 의미 입니다.

항상 10개의 문자를 채워 저장이 되지요...

varchar는....

마찬가지로 'kimdaewoo'를 입력하면

'kimdaewoo' 로 딱 9자만 들어 갑니다...

'daewoo'를 2번에 넣으면

'daewoo' 라고 6자만 저장이 되는 거지요....

var가 붙으면? 저장시 뒷부분의 공백이 잘려 저장되어 저장 공간이(디스크)

낭비되지 않는다는 겁니다...

조금 이해가 되셨느지요. 그럼 다음은 함수 입니다.

---

함수의 설명 입니다.

아울러 이하 모든 함수 리스트의 링크들은? 영문 MS의 기술지원으로 링크가 됩니다.

MS영문 사이트의 링크가 변환되면 링크가 안될 수 있습니다.

SQL서버의 함수의 종류를 간단히 설명 드리면.

함수 종류	설명
Aggregate Functions	테이블의 로우들에 대해 결과집합을 생성 합니다.
Configuration Functions	현재 시스템의 설정 정보를 보여 줍니다.
Cursor Functions	커서에 대한 정보를 보여 줍니다.
Date and Time Functions	날짜와 시간에 대한 함수
Mathematical Functions	수치 계산에 관한 함수
Metadata Functions	데이터베이스와 데이터베이스 개체에 대한 함수
Security Functions	사용자와 롤(권한)에 대한 함수
String Functions	문자열 처리에 대한 함수
System Functions	개체 세팅등의 SQL서버에 대한 함수
System Statistical Functions	시스템 통계치에 대한 함수
Text and Image Functions	텍스트와 이미지에 대한 함수

자 함수들이 무쟈게 많이 있지요? ^_^

이것덜중 많이 사용하게 되는 함수인 

Aggregate Functions, Date and Time Functions, Mathematical Functions, String Functions 들에 

대한 것을 설명을 드리구요. 

아울러 나머지 함수들중 많이 사용되는 함수를 코난이의 임의 판단하에 설명을

드리지요. ^_^

Aggregate Functions 입니다.

이는 일반적으로 테이블들의 여러 행들에 걸치는 작업을 하게 됩니다.

가장 많이 사용하실 테이블의 로우들에 대해 처리해 결과 또는 결과 집합을 생성 합니다.

AVG	SUM
COUNT	STDEV
GROUPING	STDEVP
MAX	VAR
MIN	VARP

AVG 함수 - 수치형으로 된 컬럼의 로우들에 대해 평균을 리턴 합니다.

　

SELECT AVG(advance)

FROM titles

SUM 함수 - 수치형으로 된 컬럼의 로우들에 대해 수치데이터의 총합값을 계산합니다.

 

SELECT SUM(price), SUM(advance)

FROM titles

COUNT 함수 - 컬럼의 로우 갯수를 리턴합니다. 

이는 *로 잡으셔도 되며 컬럼 1개를 잡으셔도 같은 결과 입니다.

 

SELECT COUNT(*)

FROM titles

SELECT COUNT(title)

FROM titles

MAX함수 - 수치형으로 된 컬럼의 로우들중 최대값을 찾아 리턴 합니다.

 

SELECT MAX(ytd_sales)

FROM titles

GO

MIN 함수 - 수치형으로 된 컬럼의 로우들중 최소값을 찾아 리턴 합니다.

 

SELECT min(ytd_sales) FROM titles 

GO

다음은 Date and Time Function 함수 

DATEADD	DAY
DATEDIFF	GETDATE
DATENAME	MONTH
DATEPART	YEAR

GETDATE() : 현재 날짜와 시간을 반환

 

USE pubs
GO
SELECT GETDATE()
GO

DATEADD : 기존 날짜 데이터에 대해 날짜, 월, 년 등의 날짜 데이터를 더하는 함수

 

USE pubs
GO
SELECT DATEADD(day, 21, pubdate) AS timeframe
FROM titles
GO

DATEDIFF : 날짜 데이터부터 지정일까지의 년,월,일 시간을 반환

 

USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO

DATEPART : 지정년도의 년, 월, 일 등의 특정 부분만을 반환

 

SELECT DATEPART(month, GETDATE()) AS 'Month Number'
GO

웃샤.. 양이 점점 많아 지는군요. 그래도 코난이는 꿋꿋하게 해나갈랍니다. ^_^

웃으며 해야지요. ^_^

다음은 Mathematical Functions 수치 계산에 관한 함수 입니다.

수학과 관련된 부분이지만? 몇몇개는 종종 사용 한답니다. ^_^

ABS	LOG10
ACOS	PI
ASIN	POWER
ATAN	RADIANS
ATN2	RAND
CEILING	ROUND
COS	SIGN
COT	SIN
DEGREES	SQUARE
EXP	SQRT
FLOOR	TAN
LOG

ABS : 절대값을 반환.

 

SELECT ABS(-1.0), ABS(0.0), ABS(1.0)

RAND : 0~1 까지의 소수로된 난수를 발행시킴.

 

SELECT RAND(1) AS Random_Number

ROUND : 지정위치에서 반올림을 함. - 많이 사용 됩니다.

 

SELECT ROUND(123.9994, 3), ROUND(123.9995, 3) 

주의 하셔야 할 부분 입니다. ROUND는 종종 사용할 경우가 있답니다.

좀더 샘플을 보여 드리면?

예제	결과
ROUND(748.58, -1)	750.00
ROUND(748.58, -2)	700.00
ROUND(748.58, -3)	1000.00
SELECT ROUND(123.4545, 2)	123.4500
SELECT ROUND(123.45, -2)	100.00
SELECT ROUND(150.75, 0)	151.00
SELECT ROUND(150.75, 0, 1)	150.00
ROUND(748.58, -4)	0

다음은 String Functions 즉 문자열 처리에 대한 함수 입니다.

이거 많이 사용하게 되실 겁니다. 잘 알아두시면 나중에 유용합니다.

ASCII	REPLICATE
CHAR	REVERSE
CHARINDEX	RIGHT
DIFFERENCE	RTRIM
LEFT	SOUNDEX
LEN	SPACE
LOWER	STR
LTRIM	STUFF
NCHAR	SUBSTRING
PATINDEX	UNICODE
REPLACE	UPPER
QUOTENAME

LEFT - 해당 컬럼의 문자열중 해당하는 문자열 까지만 문자열을 리턴

 

SELECT LEFT(title, 5) 

FROM titles

LEN - 해당 컬럼의 문자열의 길이를 수치로 리턴

 

SELECT LEN(title) 

FROM titles

LOWER - 해당 컬럼의 문자열(영문)을 모두 소문자로 변경

 

SELECT LOWER(title) AS 소문자

FROM titles

LTRIM - 문자열 왼쪽의 공백을 제거함

 

--@konan_string 이라는 변수를 VARCHAR(100) 이라는 

--100자의 문자열로 선언

DECLARE @konan_string varchar(100)

--@konan_string에 아래의 땀표부터 땀표까지 문자열 지정

SET @konan_string = ' 냐햐~ 코난이는 잘생겼다.'

--STRING에 LTRIM 적용으로 왼쪽의 빈 문자열 잘라냄

SELECT LTRIM(@konan_string)

GO

조금 어려우신가요? 변수를 생성하고 문자열을 변수에 대입후 처리하는 샘플 입니다.

나중에 T-SQL 프로그래밍 등에서 종종 보실테니 그러려니하시고 어려우시면 지금은 그냥

실행만 해 보세요. ^_^

PATINDEX - 해당 컬럼의 문자열중 원하는 패턴이 처음 발생한 위치(수치위치)을 리턴

 

SELECT PATINDEX('%the%', title) as 문자열이첨발생한곳

FROM titles

REPLACE - 문자열중 일정 문자열 또는 문자를 다른 문자열로 변환 

 

SELECT REPLACE('코난이는 잘 생겼다','잘','못')

REVERSE - 문자열의 위치를 바꿈 (맨첫자는 맨 뒤로 식으로 문자열의 순서를 바꿈)

 

SELECT REVERSE(au_fname) 

FROM authors

RIGHT - 문자열중 오른쪽부터 원하는 수치만큼의 문자열만 가져옴

 

SELECT RIGHT(au_fname, 5) 

FROM authors

RTRIM - 문자열중 오른쪽부터의 공백을 제거 (LTRIM 참고)

STUFF - 문자열중 원하는 위치부터 원하는 길이까지 원하는 문자열로 변환

 

SELECT STUFF('코난이는 자알~ 생겼다.', 6, 3, '못')

SUBSTRING - 문자열중 원하는 위치부터 원하는 길이만큼 문자열을 가져옴

 

SELECT au_fname, SUBSTRING(au_fname, 3, 2) as 세번째위치부터두글자

FROM authors

UPPER - 모두 대문자로

 

SELECT UPPER(au_lname) AS 모두대문자로

FROM authors

웃차 이정도면? 많이 사용되는 네가지

Aggregate Functions, Date and Time Functions, Mathematical Functions, String Functions

들에대해서 약간 알아 보았구요. 이외의 것들은 제경우 많이 사용하진 않았던 함수들이군요

그래도 이외에도 수없이 많은 함수들이 있으니 꼬옥~ 공부 하셔야 할겁니다. ^_^

코난이도 이 많은 다른 함수들을 다 기억하진 못합니다. - 제가 천잽니까 -_-;;

항상 북스 온라인을 보고 예제를 보고 사용하는 것이지요.

항상 북스 온라인을 자주 보시길 바랍니다.

북스 온라인의 샘플은 대주제로 위처럼 

Aggregate Functions, Date and Time Functions, Mathematical Functions, String Functions

등으로 나뉘어 지며 자신이 하고자 하는게 함수로 있을까 의문이라면?

이분류에서 찾아 보심 되겠지요? ^_^ 

즉, 문자열처리가 필요한데.. 하시면? String 함수에서

날짜와 관계된 함수가 필요한데.. 하시면? Date Time 함수에서 차자 보심 되겠지요?

물론 함수를 생성도 가능하니 없다면? 생성하고 사용하셔도 된답니다. ^_^

예를들면? TRIM이라는 함수가 필요하다고 하지요.

이는 문자열 왼쪽 또는 오른쪽 또는 문자열내에 공백이 있다면? 이 공백을 없애는 함수를

쓰고 싶은데 SQL에는 없는 겁니다. 어케함 될까요? -_-;;

바로!!!  다른 String 함수 등을 이용해 만드시는 겁니다.

코난이는 일케 했습니다.

 

SELECT replace(' 코난이는 아주 잘 생겼다. ', ' ', '')

이런 식이죵. 물론 이를 어플리케이션의 함수로 보내도 되지 않는가?

물론 됩니다. 하지만 코난이는 개발자의 부담(저는 ASP개발자와 일합니다.)

을 덜어주기 위해 상의후 가능함 DB쪽에서 처리하기 위해 노력 한답니다.

아울러 방법이 없네.. 내가 찾는 함수가 없어..  T.T 

항상 방법은 있다고 생각 하세요. 생각하시면 나옵니다. 

아울러~~ 이거 내가 만든건데 증말 쓸만한 함수네~~ 하심? 

이곳 Tip 게시판에 꼬옥~ 적어 주세용~ 정보의 공유는 더 큰 자기 발전 입니다.

감사합니다~

****************************************************************************

5. WHERE절의 조건에 의한 행의 검색 

 자 이번시간은 WHERE절의 조건에 의한 행의 검색을 할 시간 입니다.

앞에서 where절은 조건에 사용한다고 맛보기만 살짝 보여 드렸지요?

많은 처리가 어떻게 이루어지는지 공부할 시간 입니다.

 이런것을 생각해 볼까요?

sales 테이블에서 데이터를 모두 검색한 값이 이런 식이라고 생각을 해 보세요.

그런대 코난이의 직속 상관이 배가짱 팀장님이 요구를 하는 겁니다.

"음. 코난군?! 우리 PUBS 책회사는 

 qty 즉 팔린수량이 10권 이상인 책만 필요 하다네

 오늘중으로 이 10권 이상 팔린책만 골라내게!!!! 못하면 집에 못가네!!!"

허거덩.. DB를 공부한지 보름도 안됬는디.. 이런 날벼락이 떨어 진겁니다.

그래서 공부를 하기위해 책을 찾던중...  바로 WHERE 이라는 조건절의 지정이 가능한

것을 알게 되었습니다. 

---

WHERE 절에 탐색 조건을 근거하여 어떤 행을 검색할 지를 지정한다.

  SELECT select_list

  FROM table_list

  WHERE search_conditions

 주의사항

  - WHERE 절에는 가능하면 NOT은 사용하지 않는 것이 좋다.

    - NOT을 사용하면 색인의 도움을 받을 수 없다.

  - 연산자 앞에는 가능하면 컬럼 이름이 오도록 한다.

탐색 조건에 포함할 수 있는 것들

  - 비교 연산자                 =.  >,  <,  >=,  <=,  <>,  !=,  !,  !>

  - 범위                        BETWEEN, NOT BETWEEN

  - 리스트              IN, NOT IN

  - 일치하는 문자열     LIKE,  NOT LIKE

  - 알 수 없는 값               IS NULL, IS NOT NULL

  - 결합                        AND,  OR

  - 부정                        NOT

의 식이 된답니다. 이제 자 팔린수가 10권 이상인 책만 보여드릴 시간이지요?

 

select * from sales where qty > 10

웃차 코난이는 한방에 끝내고 지베가게 되었답니다.

(아마 코난일 지베 자주 못가게하는 배가짱팀장님(실닉네임) 땜시 이런 무의식중의

사태가 벌어지는지도...)

또 다른 샘플 입니다.

authors 테이블에서 zip 컬럼이 9000 보다 큰 녀석을 골라 볼까요?

 

SELECT * FROM authors WHERE zip > 90000

authors 테이블에서 state가 CA인 녀석들만 골라내 보지요.

 

SELECT * FROM authors WHERE state = 'CA'

자 위의 비교와 뭔가 다른게 검색어에 붙어 있지요?

문자열의 비교는 반드시 ' ' 로 작은 땀표를 쓰셔야 합니다.

왜냐구요? ^_^

  1235 라는 값과 '1235' 라는 값을 비교하기 위함이랍니다.

저게 뭐냐구요? 앞의 것은 수치형 데이터, 뒤의것은 문자열 데이터 이지요! ^_^

다음은 titles 테이블에서 price가 NULL인 녀석을 골라내 보지요.

 

SELECT title FROM titles WHERE price IS NULL

여기서 NULL 이란 녀석에 대해 좀더 알아 볼까요?

많은 분들이 NULL이 뭔지 잘 개념을 못 잡으시고 NULL = 빈 문자열 식으로 생각하고 계십니다.

아닙니다.

NULL은 값이 지정되지 않았다!!! 라는 의미 입니다. 

' ' (빈문자열) 이 녀석은? 빈 문자열이 지정되어 있다는 의미이며.

NULL은? 값이 어떤 값도 지정되지 않았다는 의미 입니다.

추후 NULL에 대한 이야기가 나와도 해깔리지 마시길 바랍니다.

다음은 titles테이블에서 ytd_sales가 4095 보다 크고 12000 보다 작은 녀석들만

뽑아서 봐 볼까요?

 

SELECT title_id, ytd_sales 
FROM titles 
WHERE ytd_sales > 4095 AND ytd_sales < 12000

자 비슷한 처리가 between 이라는 범위 검색이 있습니다.

 

SELECT title_id, ytd_sales 
FROM titles 
WHERE ytd_sales between 4095 AND 12000

거의 비슷한듯 하지요? 하지만 바로 위의 AND 검색과 결과가 틀립니다?

실행해 보시면 아시겠지요. 바로 ~이상 ~초과 라는 측정값에 대한 부분이 다릅니다.

다음은 titles테이블에서 type이 mod_cook이거나 trad_cook 인 녀석들만 골라 보져.

 

SELECT title, type FROM titles 
WHERE type IN ('mod_cook', 'trad_cook')

위의 샘플과 약간만 달리 NOT을 사용하는 방식 입니다.

결과는 필이 오시죠?

 

SELECT title, type FROM titles 
WHERE type NOT IN ('mod_cook', 'trad_cook')

음.. 이번엔? type이 mod_cook이거나 trad_cook 인 녀석들을 골라내 보죠.

위의 IN을 사용한 방식과는 또다른 OR을 사용한 방식입니다.

 

SELECT title, type FROM titles 
WHERE type = 'mod_cook' OR type = 'trad_cook'

자 몇몇 샘플을 알아 보셨는데요.

중요한 이야기를 드리지요.

위에서 문자열을 비교시 예를들면

WHERE type = 'mod_cook' OR type = 'trad_cook'

이런 식일 경우에요. type 이란 컬럼은 문자열 컬럼 입니다.

그냥 = 연산자를 사용해 비교를 했습니다. 글치요?

하지만 SQL서버는 다양한 문자열 조건 처리를 위해 like 사용을 권장하고 있습니다. 

---

LIKE 키워드

        SELECT select_list

        FROM table_list

        WHERE expression‎ [NOT] LIKE “string”

  - 정규 표현식(regular expression‎)형식의 와일드카드 사용

와일드 카드(Wildcard)

  % : 임의의 0개 이상의 문자열

  _  :  임의의 한 글자

  [ ] :  지정된 범위 또는 집합 안의 한 문자

  [^] :  지정된 범위 또는 집합에 없는 한 문자

이런 식이지요.

먼저 샘플로 stores 테이블에서 stor_name이 앞 글자가 뭘로 시작하건 상관 없고

끝글자가 뭘로 시작하건 상관 없고 문자열상에 Books라는 문자열만 존재하는지

알고 싶을때 사용하는 LIKE 구문을 알아보면?

 

SELECT stor_name FROM stores 
WHERE stor_name LIKE '%Books%'

다음으로 stores 테이블에서 stor_name이 처음은 Bo로 시작하고 

끝은 어때도 상관없는 샘플을 검색해 볼까요?

 

SELECT stor_name FROM stores
WHERE stor_name LIKE 'Bo%'

이번엔 약간 복잡한듯 하지만? 한글자 치환인 _ (언더바라고 부르지요)

를 사용해 첫글자는 E로 시작하고 한글자는 모르며 다음에 i자가 나오며

아울러 뒷글자는 뭐이어도 상관없는 문자열을 검색 한다면?

 

SELECT stor_name FROM stores
WHERE stor_name LIKE 'E_i%'

다음으로 첫자는 B로 시작하며 다음에 a자가 없고 아울러 뒷글자가 뭐라도 상관없는

문자열을 검색할 경우는?

 

SELECT stor_name FROM stores
WHERE stor_name LIKE 'B[^a]%'

의 식이지요.

여기서 짚으셔야 할 것은?

가능하시면 문자열의 비교에는 like를 사용하셔서 처리 하시면 좋다는 것 입니다.

아울러 문자열을 비교한 수치 데이터형에는 = 을 사용하시거나 <  또는 > 의 비교 이구요.

끝으로 복습 차원에서 약간더 복잡한 결과의 선택 입니다.

 

SELECT title_id, title, pub_id, price, pubdate
FROM titles
WHERE (title LIKE 'T%' OR pub_id = '0877') AND
(price > $16.00)

SELECT title_id, title, pub_id, price, pubdate
FROM titles
WHERE (title LIKE 'T%') OR (pub_id = '0877' AND price > $16.00)

SELECT title_id, title, pub_id, price, pubdate
FROM titles
WHERE title LIKE 'T%' OR pub_id = '0877' AND
(price > $16.00)

결과의 실행순서를 먼저 잘 생각해 보세요.

아울러 문자열에 대한 검색과 수치형의 혼합 검색 조건역시 입니다.

찬찬히 판단해 보시면? 느낌이 오실 겁니다.

이번은 많이 사용하는 조건을 이용한 데이터 검색을 보셨습니다.

수고하셨습니다.

****************************************************************************

6. DISTINCT 문을 이용한 중복의 제거

웃챠 오늘은 새벽 두시 이군요. 어김없이 오늘도 하나의 강좌글을 쓰겠다는 제 자신과의

약속으로 졸린 가운데 키보드를 두들 깁니다.

이번에 소개해드릴 내용은 중복의 제거 입니다.

이 키워드는 바로 distinct 입니다.　

 

SELECT au_id
FROM titleauthor

이 질의를 실행해 보세요.

25개의 데이터가 들어 있을 겁니다.

문제는? 중복되는 녀석이 존재 한다는 거지요.

이 녀석들에 대해 중복을 제거해 보자는 겁니다.

이때 사용하는 키워드는 중복의 제거인 DISTINCT 입니다.

 

SELECT DISTINCT au_id
FROM titleauthor

자 그렇다면? 

하나의 컬럼으로 보면 여러개의 로우가 중복되어 있는데..여러개의 컬럼에서 

본다면? 로우의 값들이 고유 합니다. 그럼 어떻게 될까요?

돌려 보면?

 

SELECT *
FROM titleauthor

질의를 수행 하시면? 여러개의 컬럼들이 리턴 될겁니다.

로우들의 데이터를 죽 보시면? 중복되는 것이 하나도 없지요. 바로...

 

SELECT DISTINCT *
FROM titleauthor

이렇게 수행 하셔도 같은 결과가 된다는 의미 입니다.

왜인지는 아실 겁니다.

모든 컬럼의 로우들을 비교하면? 중복값이 없기 때문이지요.

이 사용에 대해 주의 하셔야 하며..

두번재로 text데이터 형에서는 dsitinct가 불가 합니다.

 

use northwind
go

select description from Categories

northwind라는 샘플 데이터베이스의 categories 테이블은? 데이터형이 text형으로 되어

있습니다. 이곳에 대해

 

use northwind
go

select distinct description from Categories

하시면?

결과-----------------------------------------------------------------

서버: 메시지 8163, 수준 16, 상태 3, 줄 2
text, ntext 또는 image 데이터 형식은 DISTINCT로 선택할 수 없습니다.

라는 메세지가 뜨게 됩니다.

이해가 되시는지요...

세번째로  NULL 이라는 값이 지정되는 않을 경우는 여러개의 NULL이 있더라도?

하나의 NULL만 dsitinct의 결과에 나타나게 됩니다.

그럼 이정도로 distinct의 이야기는 접도록 하지요.

이는 종종 실제 업무에서 사용을 한 경험이 있습니다.

코난이는 종종 다른회사에서 주는 데이터를 가지고 가공 처리해 솔루션을 구축하는

외주 업무를 주로 많이 했었는데...

업체에서 넘겨주는 데이터가 엉망일 경우가 많습니다.

이럴때 가공 처리를 위해 종종 distinct를 사용 하지요....

간단하지만 몇몇 제약이 있는 distinct 잘 이해 되셨으리라 생각 합니다.

그럼 이만.

****************************************************************************

7. ORDER BY 문을 이용한 결과의 정렬

　이번에 소개해 드릴 내용은 order by를 사용한 결과의 정렬 입니다.

 먼저 정렬이란게 몬지는 아마 알고 계시겠지만 환기시켜 드리는 의미에서

살짝 소개를 해 드리지요.

C A B 라는 식의 데이터가 있을때 이를

A B C 의 순서로 되게 하는 것이 정렬이라고 보통 말을 하지요?

정확히 이를 오름차순이라고 하구요.

C B A 순으로 바꾼다면? 

이를 내림차순 정렬이라고 보통 말을 하지요.

한글에 대한 정렬역시 가능 합니다.

이를 확인 하시려면? 

설치시 옵션중 949 Korean 즉 한글 완성형으로 설치 하셨다면?

가능 합니다.

아래 저장 프로시져는 서버의 sort 방식을 리턴 합니다.

 

sp_helpsort

결과 --------

Server default collation 
---------------------------------------------------------------------------------------------

Korean-Wansung, case-insensitive, 

accent-sensitive, kanatype-insensitive, width-insensitive

이렇게 Korean-Wansung 으로 되어 있다면? 사용 가능 하지요.

SQL2000에서는 이렇게 바뀌었으며 아울러 이전 SQL7 에서는?

결과 -------

Unicode data sorting 
---------------------- 
Locale ID = 1042 

case insensitive, kana type insensitive, width insensitive


Sort Order Description 
---------------------------------------------------------------------

Character Set = 12, cp949
Code page 949
Sort Order = 195, nls_cp949
NLS Case-Insensitive Sort Order for the Code Page 949 Character Set

이런 식으로 Code page 949 로 찍히게 됩니다.

SQL2000 부터는 위의 결과처럼 바뀌게 되었지요.

그럼 ORDER BY의 구문 정보 입니다.

---

구문정보

SELECT column_name[, column_name…]

FROM table_list

[ORDER BY column_name | select_list_number | expression‎

        [ASC | DESC][,column_name | select_list_number | expression‎

        [ASC | desc]…]

WHERE search_conditions

고려사항

  - ORDER BY 절에 컬럼 명이 아닌 상대적인 컬럼 번호를 사용할 수 있다.

  - 오름차순(ASC)또는 내림차순(DESC)이 지정되지 않으면 오름차순으로 가정한다.

  - 최고 16 컬럼까지 정렬할 수 있다.

  - 정렬의 대상이 된 열이 select_list에 반드시 나타날 필요는 없다.

먼저 사용해볼 쿼리 입니다.

 

SELECT city, au_fname, au_lname FROM authors

그럼 결과가 보이실 겁니다. 그렇지요?

여기서 결과를 city 순으로 함 정렬 시켜 볼까요? 

 

SELECT city, au_fname, au_lname FROM authors
ORDER BY city

그럼 결과는 아마도.

city 컬럼이 A 의 순으로 해서 나오게 될겁니다.

바로 내림차순 정렬이 되는 거지요.

이처럼 SQL서버는 기본적으로 내림차순 정렬을 하게 됩니다.

이를 정확히 명시 한다면?

 

SELECT city, au_fname, au_lname FROM authors
ORDER BY city ASC

이렇게 ASC 가 사용 되는 것이지요.

이를 바꿔서 오름차순으로 정렬해 본다면?

 

SELECT city, au_fname, au_lname FROM authors
ORDER BY city DESC

바뀐것은 단하나.. 바로 DESC 부분이지요. 이렇게 실행이 되는 것입니다.

그렇다면? 두개의 컬럼으로 정렬을 시킬 순 없을가요?

지금은 city 하나로 정렬 된것이지만 city 컬럼과 au_fname으로 또한 정렬을 시키고 싶다면?

위의 쿼리의 결과를 살펴보면

city 의 경우 여러 데이터가 있습니다. au_fname도 정렬해 살펴 보지요

 

SELECT city, au_fname, au_lname FROM authors
ORDER BY city ASC, au_fname ASC

먼저 city에 대해서 정렬하고 그 결과셋중 city가 중복되는 녀석이 있다면?

이녀석중 au_fname을 정렬한다는 의미 입니다.

이렇게 두개의 컬럼을 동시에 정렬도 가능 합니다.

바꾸어서 city는 내림차순으로 아울러 au_fname은 오름차순으로 정렬 하려 한다면?

 

SELECT city, au_fname, au_lname FROM authors
ORDER BY city ASC, au_fname DESC

이렇게 사용하시면 되겠지요. ^_^

이때 주의 하실 점은..

text형이나 image 데이터 형의 경우는 order by 절에 사용이 불가 합니다.

하지만 이런 로우에 대한 계산 함수의 사용은 가능 합니다.

 

SELECT type, sum(ytd_sales) AS sales_total
FROM titles
GROUP BY type
ORDER BY sales_total

즉, 그룹화된 계산된 컬럼(sum(), avg(), max() 등)의 결과셋의 사용은 가능하다는 의미 입니다.

GROUP BY 의 경우는 아직 나올때가 아닌데 나왔지요?

잘 모르시더라도 바로 뒤에서 배우니 그러려니 하고 그냥 보시길 바랍니다.

끝으로...

3개 이상의 컬럼이 있더라도 사용이 가능하겠지요?

수고하셨습니다.

****************************************************************************

8. GROUP BY, COMPUTE BY, ROLLUP, CUBE를 이용한 

    요약 데이터 작성

이번에 소개해 드릴 내용은 요약 데이터를 작성하자 라는 의미 입니다.

먼저 이곳을 보시기 전에 설명 드린 함수의

Aggregate 함수 부분을 찬찬히 다시 함 봐 보시길 바랍니다.

이를 보통 계산 함수라 부르는데요...

이 계산함수와 group by가 함께 사용이 된답니다.

먼저 간단한 쿼리를 살펴 보도록 하지요.

코난이를 책회사의 사장이라고 생각해 보세요... ^_^

책회사가 생긴지는 어언 1년이 다 되어 갑니다.....

그동안 만든 책도 많았습니다....

요리책.., 컴퓨터 책...., 비즈니스에 대한 책...... , 정신세계에 대한책....

흐흐흐.... 근데.. 최근들어 의문이 생긴 겁니다....

도대체 내가 어떤책을 얼마나많은 양을 얼마를 주고 판거지????

저자와. 로열티와.. 팔린수.... 흠흠흠... 책의 가격은.....

궁금하구먼......

평소 MSSQL서버에 관심이 많던 코난이가 컴앞에 앉아 질의를 만듭니다..

흠흠흠.... 일케 하면???

 

USE pubs

select pub_id 저자, type 책종류, royalty 로열티, ytd_sales 팔린수 from titles

자 이런 쿼리를 돌려 보시면?

아마도 결과는..

저자 책종류 로열티 팔린수 
---- ------------ ----------- ----------- 
1389 business 10 4095
1389 business 10 3876
0736 business 24 18722
1389 business 10 4095
0877 mod_cook 12 2032
0877 mod_cook 24 22246
0877 UNDECIDED NULL NULL
1389 popular_comp 16 8780
1389 popular_comp 10 4095
1389 popular_comp NULL NULL
0877 psychology 10 375
0736 psychology 12 2045
0736 psychology 10 111
0736 psychology 10 4072
0736 psychology 10 3336
0877 trad_cook 10 375
0877 trad_cook 14 15096
0877 trad_cook 10 4095

(18개 행 적용됨)

흠흠흠... 그러쿠먼... 흠흠흠........

음..........

이걸 그룹핑을 시켜서 보면 좀더 '정리된 자룔 볼 수 있지 않을강?'

흠흠흠....

저자명과 책종류와 로열티와 팔린수 평균값의 순으로 그룹핑을 시킴 보기 좋겠구먼...

 

select pub_id 저자, type 책종류, royalty 로열티, ytd_sales 팔린수, AVG(price) 평균값
from titles
GROUP BY pub_id, type, royalty, ytd_sales

저자 책종류 로열티 팔린수 평균값 
---- ------------ ----------- ----------- --------------------- 
0736 business 24 18722 2.9900
0736 psychology 10 111 7.0000
0736 psychology 10 3336 7.9900
0736 psychology 10 4072 19.9900
0736 psychology 12 2045 10.9500
0877 mod_cook 12 2032 19.9900
0877 mod_cook 24 22246 2.9900
0877 psychology 10 375 21.5900
0877 trad_cook 10 375 20.9500
0877 trad_cook 10 4095 14.9900
0877 trad_cook 14 15096 11.9500
0877 UNDECIDED NULL NULL NULL
1389 business 10 3876 11.9500
1389 business 10 4095 19.9900
1389 popular_comp NULL NULL NULL
1389 popular_comp 10 4095 20.0000
1389 popular_comp 16 8780 22.9500

(17개 행 적용됨)

경고: 집계 또는 다른 집합 연산에 의해 Null 값이 제거되었습니다.

 자 어떠세요? 이런 경우가 있겠지요? 조금 쉽게 더 생각해 보도록 하지요.

 

select pub_id 저자, type 책종류, price 평균값
from titles

저자 책종류 평균값 
---- ------------ --------------------- 
1389 business 19.9900
1389 business 11.9500
0736 business 2.9900
1389 business 19.9900
0877 mod_cook 19.9900
0877 mod_cook 2.9900
0877 UNDECIDED NULL
1389 popular_comp 22.9500
1389 popular_comp 20.0000
1389 popular_comp NULL
0877 psychology 21.5900
0736 psychology 10.9500
0736 psychology 7.0000
0736 psychology 19.9900
0736 psychology 7.9900
0877 trad_cook 20.9500
0877 trad_cook 11.9500
0877 trad_cook 14.9900

(18개 행 적용됨)

이런 데이터가 있다고 할때요...

책 종류별로 팔린 SUM 가격 값을 알고 싶다면? 어떻게 할까요?

중요한 포인트는 책 종류별 입니다.

 

select type 책종류, sum(price) 평균값
from titles
group by type

책종류 평균값 
------------ --------------------- 
business 54.9200
mod_cook 22.9800
popular_comp 42.9500
psychology 67.5200
trad_cook 47.8900
UNDECIDED NULL

(6개 행 적용됨)

경고: 집계 또는 다른 집합 연산에 의해 Null 값이 제거되었습니다.

맨 아래의 경고: 집계 또는 다른 집합 연산에 의해 Null 값이 제거되었습니다.는?

그렇군.!!!! 책값중에 아직 책의 가격이 책정 안된게(가격이 NULL인게) 있군!!!!

이것을 무시하고 계산한다.. 라는 의미구먼..... ^_^

이런식이 되는 것이지요? 조금 보이시나요? 보시는 바와 같이 

책 종류별로 어떠한 집계 결과(지금은 SUM에 의한 총합)를 얻게 되었습니다.

이 GROUP BY에 대해 조금더 깊이 생각해 볼까요?

GROUP BY / HAVING

계산 함수와 함께 각 그룹에 대해 하나의 행과 하나의 요약 정보를 생성한다.

        SELECT select_list

        FROM table_name

        WHERE search_conditions

        GROUP BY [ALL] aggregate_free_expression‎

                                [, aggregate_free_expression‎…]]

        [HAVING search conditions]

        - GROUP BY

          - 선택된 테이블의 각 그룹에 대한 요약 정보를 생성한다.

        - HAVING

          - 결과 행들에 대해 제약을 가한다.

          - 조건에 맞지 않는 그룹들을 제외한다.

ANSI 표준 SQL에서의 요구사항

        - select_list에 있는 모든 컬럼들은 반드시 GROUP BY 절에 나타나야 한다.

        - HAVING 절에 있는 컬럼은 반드시 하나의 값을 반환해야 한다.

        - HAVING절을 포함한 질의는 반드시 GROUP BY 절을 포함해야 한다.

Transact - SQL에서는 ANSI 표준 SQL보다 많은 옵션을 제공한다.

        - GROUP BY 절은 수식을 포함할 수 있다.

        - GROUP BY ALL은 WHERE절에서 제외된 그룹까지도 포함한다.

        - ALL은 SELECT 문이 WHERE 절을 포함할 때만 의미가 있다.

몇개의 샘플을 더 보여 드리면?

 

SELECT title_id, copies_sold = SUM(qty)
FROM sales
GROUP BY title_id

이 샘플은? title_id 별로 팔린수라는 값을 보여주는 샘플 이겠지요?

여기서 HAVING 이라는 말이 나오는데 이는 뭘까요?

 

SELECT title_id, copies_sold = SUM(qty)
FROM sales
GROUP BY title_id
HAVING SUM(qty) > 20

이렇게 HAVING은? 그룹화된 데이터들에 대해 조건을 줄 수 있다는 것입니다.

말로 풀어 본다면? SUM(qty) 값이 20 보다 큰 그룹화된 녀석들만 보여줘!!

라는 질의가 되겠지요. ^_^

그렇다면? GROUP BY ALL 은 무엇 일까요?

 

SELECT title_id, copies_sold = SUM(qty)
FROM sales
WHERE ord_date BETWEEN '1/1/1994' AND '12/31/1994'
GROUP BY ALL title_id

결과를 실행해 보시면? 조건에 맞지 않는 title_id라도 우선은 보여 준다는

의미 입니다. 집계 결과는 NULL로 표기 하지만 그룹의 리스트는 보여 준다는

의미 이지요. 

다음으로 ROLLUP / CUBE에 대해서 조금더 알려 드릴 시간 이군요.

위의 코난이가 책가게 사장이라는 상황의 계속 입니다.

흠.....

헌데.. 몬가 부족한 듯 하구먼.............

흠흠흠....

흠흠...

저자별로..... 거기에 각각의 잭종류 별로 로열티 별로 볼 수는 없을가...

흠흠흠.. 저 0376같은 저자를 보니... 사업과 정신세계에 대한 책을 썼는데...

각각에대해 좀더 자세한 값을 보고 싶구먼.. 흠흠흠....

 

select pub_id 저자, type 책종류, royalty 로열티, ytd_sales 팔린수, AVG(price) 평균값
from titles
GROUP BY pub_id, type, royalty, ytd_sales
WITH ROLLUP

저자 책종류 로열티 팔린수 평균값 
---- ------------ ----------- ----------- --------------------- 
0736 business 24 18722 2.9900
0736 business 24 NULL 2.9900
0736 business NULL NULL 2.9900
0736 psychology 10 111 7.0000
0736 psychology 10 3336 7.9900
0736 psychology 10 4072 19.9900
0736 psychology 10 NULL 11.6600
0736 psychology 12 2045 10.9500
0736 psychology 12 NULL 10.9500
0736 psychology NULL NULL 11.4825
0736 NULL NULL NULL 9.7840
0877 mod_cook 12 2032 19.9900
0877 mod_cook 12 NULL 19.9900
0877 mod_cook 24 22246 2.9900
0877 mod_cook 24 NULL 2.9900
0877 mod_cook NULL NULL 11.4900
0877 psychology 10 375 21.5900
0877 psychology 10 NULL 21.5900
0877 psychology NULL NULL 21.5900
0877 trad_cook 10 375 20.9500
0877 trad_cook 10 4095 14.9900
0877 trad_cook 10 NULL 17.9700
0877 trad_cook 14 15096 11.9500
0877 trad_cook 14 NULL 11.9500
0877 trad_cook NULL NULL 15.9633
0877 UNDECIDED NULL NULL NULL
0877 UNDECIDED NULL NULL NULL
0877 UNDECIDED NULL NULL NULL
0877 NULL NULL NULL 15.4100
1389 business 10 3876 11.9500
1389 business 10 4095 19.9900
1389 business 10 NULL 17.3100
1389 business NULL NULL 17.3100
1389 popular_comp NULL NULL NULL
1389 popular_comp NULL NULL NULL
1389 popular_comp 10 4095 20.0000
1389 popular_comp 10 NULL 20.0000
1389 popular_comp 16 8780 22.9500
1389 popular_comp 16 NULL 22.9500
1389 popular_comp NULL NULL 21.4750
1389 NULL NULL NULL 18.9760
NULL NULL NULL NULL 14.7662

(42개 행 적용됨)

경고: 집계 또는 다른 집합 연산에 의해 Null 값이 제거되었습니다.

흠... 저 결과의 두 번째줄의 NULL과 같은 저 값은 무얼가????

아항~~~ 바로 총합일 경우구먼!!!!!!

그럼 결과 맨 밑줄의 다 널인놈은?

아하~~~ 모든책 저자, 종류에 대한~~~  총 평균을 말하는군!!!!

 흠흠흠 (뿌듯~~~) 좋구먼... ^_^ 흠~~~~~~~

각각의 저자별로 책종류 별로... 로열티 별로.. 팔린수 별로 아주 자세히 보이는 구먼...

근데...

각각의 저자별이 아닌 책종류가 중요한데...

그렇쿠먼!!! 일케함 되겠구먼!!!!!!

 

select type 책종류,pub_id 저자, royalty 로열티, ytd_sales 팔린수, AVG(price) 평균값
from titles
GROUP BY type,pub_id, royalty, ytd_sales
WITH ROLLUP

책종류 저자 로열티 팔린수 평균값 
------------ ---- ----------- ----------- --------------------- 
business 0736 24 18722 2.9900
business 0736 24 NULL 2.9900
business 0736 NULL NULL 2.9900
business 1389 10 3876 11.9500
business 1389 10 4095 19.9900
business 1389 10 NULL 17.3100
business 1389 NULL NULL 17.3100
business NULL NULL NULL 13.7300
mod_cook 0877 12 2032 19.9900
mod_cook 0877 12 NULL 19.9900
mod_cook 0877 24 22246 2.9900
mod_cook 0877 24 NULL 2.9900
mod_cook 0877 NULL NULL 11.4900
mod_cook NULL NULL NULL 11.4900
popular_comp 1389 NULL NULL NULL
popular_comp 1389 NULL NULL NULL
popular_comp 1389 10 4095 20.0000
popular_comp 1389 10 NULL 20.0000
popular_comp 1389 16 8780 22.9500
popular_comp 1389 16 NULL 22.9500
popular_comp 1389 NULL NULL 21.4750
popular_comp NULL NULL NULL 21.4750
psychology 0736 10 111 7.0000
psychology 0736 10 3336 7.9900
psychology 0736 10 4072 19.9900
psychology 0736 10 NULL 11.6600
psychology 0736 12 2045 10.9500
psychology 0736 12 NULL 10.9500
psychology 0736 NULL NULL 11.4825
psychology 0877 10 375 21.5900
psychology 0877 10 NULL 21.5900
psychology 0877 NULL NULL 21.5900
psychology NULL NULL NULL 13.5040
trad_cook 0877 10 375 20.9500
trad_cook 0877 10 4095 14.9900
trad_cook 0877 10 NULL 17.9700
trad_cook 0877 14 15096 11.9500
trad_cook 0877 14 NULL 11.9500
trad_cook 0877 NULL NULL 15.9633
trad_cook NULL NULL NULL 15.9633
UNDECIDED 0877 NULL NULL NULL
UNDECIDED 0877 NULL NULL NULL
UNDECIDED 0877 NULL NULL NULL
UNDECIDED NULL NULL NULL NULL
NULL NULL NULL NULL 14.7662

(45개 행 적용됨)

경고: 집계 또는 다른 집합 연산에 의해 Null 값이 제거되었습니다.

흠하하하~~~ 아주아주 좋은 자료군~~~ 이렇게 조은 명령이 있어 세세히

모든 자료를 들여다 볼 수 있으니.. 호오~~~ 죽이는구먼~~~~ 핫핫핫~~~~

그런데... 지금처럼... 저자별 각각의 책의 종류와....

종류별 각각의 책의 저자의 평균값을 동시에 볼 수는 없을가???????

조금 길어질 것도 같지만...

음.. 이게 좋겠군....

 

SELECT pub_id 저자, type 책종류, royalty 로열티, ytd_sales 팔린수, AVG(price) 평균값
FROM titles
GROUP BY pub_id, type, royalty, ytd_sales
WITH CUBE

저자 책종류 로열티 팔린수 평균값 
---- ------------ ----------- ----------- --------------------- 
0736 business 24 18722 2.9900
0736 business 24 NULL 2.9900
0736 business NULL NULL 2.9900
0736 psychology 10 111 7.0000
0736 psychology 10 3336 7.9900
0736 psychology 10 4072 19.9900
0736 psychology 10 NULL 11.6600
0736 psychology 12 2045 10.9500
0736 psychology 12 NULL 10.9500
0736 psychology NULL NULL 11.4825
0736 NULL NULL NULL 9.7840
0877 mod_cook 12 2032 19.9900
0877 mod_cook 12 NULL 19.9900
0877 mod_cook 24 22246 2.9900
0877 mod_cook 24 NULL 2.9900
0877 mod_cook NULL NULL 11.4900
0877 psychology 10 375 21.5900
0877 psychology 10 NULL 21.5900
0877 psychology NULL NULL 21.5900
0877 trad_cook 10 375 20.9500
0877 trad_cook 10 4095 14.9900
0877 trad_cook 10 NULL 17.9700
0877 trad_cook 14 15096 11.9500
0877 trad_cook 14 NULL 11.9500
0877 trad_cook NULL NULL 15.9633
0877 UNDECIDED NULL NULL NULL
0877 UNDECIDED NULL NULL NULL
0877 UNDECIDED NULL NULL NULL
0877 NULL NULL NULL 15.4100
1389 business 10 3876 11.9500
1389 business 10 4095 19.9900
1389 business 10 NULL 17.3100
1389 business NULL NULL 17.3100
1389 popular_comp NULL NULL NULL
1389 popular_comp NULL NULL NULL
1389 popular_comp 10 4095 20.0000
1389 popular_comp 10 NULL 20.0000
1389 popular_comp 16 8780 22.9500
1389 popular_comp 16 NULL 22.9500
1389 popular_comp NULL NULL 21.4750
1389 NULL NULL NULL 18.9760
NULL NULL NULL NULL 14.7662
NULL business 10 3876 11.9500
NULL business 10 4095 19.9900
NULL business 10 NULL 17.3100
NULL business 24 18722 2.9900
NULL business 24 NULL 2.9900
NULL business NULL NULL 13.7300
NULL mod_cook 12 2032 19.9900
NULL mod_cook 12 NULL 19.9900
NULL mod_cook 24 22246 2.9900
NULL mod_cook 24 NULL 2.9900
NULL mod_cook NULL NULL 11.4900
NULL popular_comp NULL NULL NULL
NULL popular_comp NULL NULL NULL
NULL popular_comp 10 4095 20.0000
NULL popular_comp 10 NULL 20.0000
NULL popular_comp 16 8780 22.9500
NULL popular_comp 16 NULL 22.9500
NULL popular_comp NULL NULL 21.4750
NULL psychology 10 111 7.0000
NULL psychology 10 375 21.5900
NULL psychology 10 3336 7.9900
NULL psychology 10 4072 19.9900
NULL psychology 10 NULL 14.1425
NULL psychology 12 2045 10.9500
NULL psychology 12 NULL 10.9500
NULL psychology NULL NULL 13.5040
NULL trad_cook 10 375 20.9500
NULL trad_cook 10 4095 14.9900
NULL trad_cook 10 NULL 17.9700
NULL trad_cook 14 15096 11.9500
NULL trad_cook 14 NULL 11.9500
NULL trad_cook NULL NULL 15.9633
NULL UNDECIDED NULL NULL NULL
NULL UNDECIDED NULL NULL NULL
NULL UNDECIDED NULL NULL NULL
0877 NULL NULL NULL NULL
1389 NULL NULL NULL NULL
NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL
0736 NULL 10 111 7.0000
NULL NULL 10 111 7.0000
0877 NULL 10 375 21.2700
NULL NULL 10 375 21.2700
0736 NULL 10 3336 7.9900
NULL NULL 10 3336 7.9900
1389 NULL 10 3876 11.9500
NULL NULL 10 3876 11.9500
0736 NULL 10 4072 19.9900
NULL NULL 10 4072 19.9900
0877 NULL 10 4095 14.9900
1389 NULL 10 4095 19.9933
NULL NULL 10 4095 18.7425
NULL NULL 10 NULL 16.4440
0877 NULL 12 2032 19.9900
NULL NULL 12 2032 19.9900
0736 NULL 12 2045 10.9500
NULL NULL 12 2045 10.9500
NULL NULL 12 NULL 15.4700
0877 NULL 14 15096 11.9500
NULL NULL 14 15096 11.9500
NULL NULL 14 NULL 11.9500
1389 NULL 16 8780 22.9500
NULL NULL 16 8780 22.9500
NULL NULL 16 NULL 22.9500
0736 NULL 24 18722 2.9900
NULL NULL 24 18722 2.9900
0877 NULL 24 22246 2.9900
NULL NULL 24 22246 2.9900
NULL NULL 24 NULL 2.9900
0877 UNDECIDED NULL NULL NULL
0877 NULL NULL NULL NULL
1389 popular_comp NULL NULL NULL
1389 NULL NULL NULL NULL
NULL NULL NULL NULL NULL
0736 psychology NULL 111 7.0000
0736 NULL NULL 111 7.0000
NULL NULL NULL 111 7.0000
0877 psychology NULL 375 21.5900
0877 trad_cook NULL 375 20.9500
0877 NULL NULL 375 21.2700
NULL NULL NULL 375 21.2700
0877 mod_cook NULL 2032 19.9900
0877 NULL NULL 2032 19.9900
NULL NULL NULL 2032 19.9900
0736 psychology NULL 2045 10.9500
0736 NULL NULL 2045 10.9500
NULL NULL NULL 2045 10.9500
0736 psychology NULL 3336 7.9900
0736 NULL NULL 3336 7.9900
NULL NULL NULL 3336 7.9900
1389 business NULL 3876 11.9500
1389 NULL NULL 3876 11.9500
NULL NULL NULL 3876 11.9500
0736 psychology NULL 4072 19.9900
0736 NULL NULL 4072 19.9900
NULL NULL NULL 4072 19.9900
0877 trad_cook NULL 4095 14.9900
0877 NULL NULL 4095 14.9900
1389 business NULL 4095 19.9900
1389 popular_comp NULL 4095 20.0000
1389 NULL NULL 4095 19.9933
NULL NULL NULL 4095 18.7425
1389 popular_comp NULL 8780 22.9500
1389 NULL NULL 8780 22.9500
NULL NULL NULL 8780 22.9500
0877 trad_cook NULL 15096 11.9500
0877 NULL NULL 15096 11.9500
NULL NULL NULL 15096 11.9500
0736 business NULL 18722 2.9900
0736 NULL NULL 18722 2.9900
NULL NULL NULL 18722 2.9900
0877 mod_cook NULL 22246 2.9900
0877 NULL NULL 22246 2.9900
NULL NULL NULL 22246 2.9900
0877 NULL NULL NULL NULL
1389 NULL NULL NULL NULL
0736 NULL 10 NULL 11.6600
0877 NULL 10 NULL 19.1766
1389 NULL 10 NULL 17.9825
0736 NULL 12 NULL 10.9500
0877 NULL 12 NULL 19.9900
0877 NULL 14 NULL 11.9500
1389 NULL 16 NULL 22.9500
0736 NULL 24 NULL 2.9900
0877 NULL 24 NULL 2.9900
NULL popular_comp NULL NULL NULL
NULL UNDECIDED NULL NULL NULL
NULL psychology NULL 111 7.0000
NULL psychology NULL 375 21.5900
NULL trad_cook NULL 375 20.9500
NULL mod_cook NULL 2032 19.9900
NULL psychology NULL 2045 10.9500
NULL psychology NULL 3336 7.9900
NULL business NULL 3876 11.9500
NULL psychology NULL 4072 19.9900
NULL business NULL 4095 19.9900
NULL popular_comp NULL 4095 20.0000
NULL trad_cook NULL 4095 14.9900
NULL popular_comp NULL 8780 22.9500
NULL trad_cook NULL 15096 11.9500
NULL business NULL 18722 2.9900
NULL mod_cook NULL 22246 2.9900

(184개 행 적용됨)

경고: 집계 또는 다른 집합 연산에 의해 Null 값이 제거되었습니다.

자 이렇게 지금까지 보신 내용이? ROLLUP과 CUBE에 대한 샘플 이었습니다.

조금더 내부적으로 알아 볼까요?

ROLLUP / CUBE 연산자

지정된 컬럼들에 대한 다양한 조합을 고려하는 데이타 요약의 필요성이 나타나게 되었다.

        - data warehousing

        - DSS(decision support system)

        - OLAP(online analytic processing)…

계산함수와 함께 사용되어 결과에 추가적인 행을 만든다.

        GROUP BY [ALL] aggregate_free_expression‎

                        [, aggregate_free_expression‎…]

                        [WITH{CUBE | ROLLUP}]

ROLLUP 연산자

- GROUP BY 절에 있는 컬럼들의 오른쪽에서 왼쪽의 차례로 그룹들을 생성하고 

   각 그룹에 계산 함수를 적용한다.

- GROUP BY 절의 결과는 누적 계산 결과이다.

CUBE 연산자

- GROUP BY 절에 있는 모든 컬럼들에 대한 가능한 모든 조합을 그룹으로 생성한다.

고려사항

        - 최대 10 그룹의 표현식이 지정될 수 있다

        - 일반적인 GROUP BY 의 경우는 16 그룹의 표현식을 허용한다.

        - 컬럼이나 표현식을 명시적으로 GROUP BY에 지정할 수 없다.

        - GROUP BY 절에 나타나는 컬럼들의 최대 크기는 900바이트 이다.

다음은 GROUPING 함수에 대한 이야깁니다.

 

SELECT royalty, SUM(advance) 'total advance', 
GROUPING(royalty) 'grp'
FROM titles
GROUP BY royalty WITH ROLLUP

크게 중요한 내용은 아닙니다만..

GROUPING 함수

각 결과 행이 CUBE, ROLLUP 연산자들에 의해 계산된 것인지를 알기 위해 사용된다

GROUPING(column_name)

  - 각 행에 결과 값을 위한 새로운 컬름을 추가하여 어느 것에 의해 계산 되었는지를 나타낸다.

  - GROUP BY 절에 나타나는 컬럼에 적용된다.

어떠신지요? 조금은 복잡한 내용 이었지요?

지금까지의 내용은 GROUP BY와 ROLLUP CUBE에 대한 내용이었습니다.

이제 보실 내용은?

COMPUTE BY라는 다른 녀석 입니다.

 

SELECT type, price FROM titles
WHERE type LIKE '%cook'
ORDER BY type, price
COMPUTE SUM(price)

이녀석은 큰 설명이 필요 없습니다. 실행해 보시면? 어떤 녀석인지 감이

팍 잡히실 겁니다.

바로 요약 데이터를 작성하는 녀석 이지요 결과는

type price 
------------ --------------------- 
mod_cook 2.9900
mod_cook 19.9900
trad_cook 11.9500
trad_cook 14.9900
trad_cook 20.9500

sum
===================
70.8700


(6개 행 적용됨)

이런 식이 되실 겁니다 글치요?

다음은? COMPUTE BY 샘플 입니다.

 

SELECT type, price FROM titles
WHERE type LIKE '%cook'
ORDER BY type, price
COMPUTE SUM(price) BY type

해당하는 질의와 다른점은?

type price 
------------ --------------------- 
mod_cook 2.9900
mod_cook 19.9900

sum
===================
22.9800


type price 
------------ --------------------- 
trad_cook 11.9500
trad_cook 14.9900
trad_cook 20.9500

sum
===================
47.8900


(7개 행 적용됨)

모가 다른듯 하신가요?

바로 type별로 나뉘어진 요약 결과라는 점이지요.

조금더 재미있는 샘플을 알아 본다면?

 

SELECT type, price FROM titles
WHERE type LIKE '%cook'
ORDER BY type, price
COMPUTE SUM(price) BY type
COMPUTE SUM(price)

이런 질의를 날려 볼까요?

결과는?

type price 
------------ --------------------- 
mod_cook 2.9900
mod_cook 19.9900

sum
===================
22.9800


type price 
------------ --------------------- 
trad_cook 11.9500
trad_cook 14.9900
trad_cook 20.9500

sum
===================
47.8900


sum
===================
70.8700


(8개 행 적용됨)

이런 식이 되실 겁니다.

하나는 COMPUTE BY 이고.. 또하나는 COMPUTE 인데 맨 마지막에

결과가 하나 추가 되지요? 그럼 제대로 보신 겁니다.

자..  좀더 상세히 이 COMPUTE BY를 알아 보도록 하지요.

COMPUTE / COMPUTE BY

계산 함수를 이용하여 질의 결과로 부터 요약 행을 생성한다.

COMPUTE row_aggregate(column_name)

               [, row_aggregate(column_name)…]

    [BY column_name [,column_name]…]

- COMPUTE BY

- 그룹별로 요약 정보를 생성한다.

- 상세 행과  요약행을 한꺼번에 작성할 수 있다.

- 일반적으로 기본키와 참조키 값에 의해 정렬된 행들에 대해 

   요약 보고서를 작성하는데 사용된다.

제약 사항

    - DISTINCT 키워드는 행 계산 함수와 함께 사용할 수 없다.

    - COMPUTE 절에 있는 컬럼은 반드시 select_list에 나타나야 한다.

    - SELECT INTO는 COMPUTE절과 함께 사용할 수 없다.

      - COMPUTE를 포함하고 있는 문장은 Non-relational 결과를 생성하므로

    - COMPUTE BY 절에 컬럼 이름이나 수식을 사용해야 한다.

    - COMPUTE BY 는 반드시 ORDER BY와 함께 사용해야 한다.

      - COMPUTE키워드는 ORDER BY없이 자체적으로 사용될 수 없다.

    - COMPUTE BY 절에 나타나는 컬럼들은 ORDER BY 절에 나타나는 컬럼들과 같거나 

        그 일부분 이어야 한다.

      - 이때 컬럼들의 순서도 일치해야 하며 ORDER BY 절에 나타나는 표현식은 

        반드시 나타나야 한다.

    - 텍스트나 이미지 자료형은 COMPUTE 절에 포함 시킬 수 없다.

ORDER BY와 COMPUTE BY

        ORDER BY a,  b,  c

        - 올바르게 작성된 COMPUTE BY 절

          COMPUTE row_aggregate (column_name) BY a, b, c

          COMPUTE row_aggregate (column_name) BY a, b

          COMPUTE row_aggregate (column_name) BY a

        - 잘못 작성된 COMPUTE BY절

          COMPUTE row_aggregate (column_name) BY b, c

          COMPUTE row_aggregate (column_name) BY a, c

          COMPUTE row_aggregate (column_name) BY c

          COMPUTE row_aggregate (column_name) BY b

 

--잘못된 COMPUTE BY 절

SELECT type, price FROM titles
WHERE type LIKE '%cook'
ORDER BY type, price
COMPUTE SUM(price), SUM(advance) BY type
COMPUTE SUM(price), SUM(advance)

결과 :

서버: 메시지 411, 수준 16, 상태 2, 줄 1
COMPUTE 절 #1, 집계 식 #2이(가) SELECT 목록에 없습니다.
서버: 메시지 411, 수준 16, 상태 1, 줄 1
COMPUTE 절 #2, 집계 식 #2이(가) SELECT 목록에 없습니다.

자 끝으로요..

조금 복잡 하지요? 특히나 ROLLUP , CUBE요..   ^_^;;

그렇다면... "그눔의 복잡 그지없는 ROLLUP / CUBE는 왜 있냐?"

"전들 압니가?" 라고 말한다면 강좌를 하는 저에게 돌을 떤지실분 무지 많겠죠.. -.-;;

최근의 생활과 인터넷이라는 매체를 생각해 보죠....

첨 R-DB가 개발 된게 70년 중반이랍니다...

그리고 20년도 넘게 흘러서....

DB에 이 복잡 그지없는 "생활" , "인터넷상의 떠도는 모든 이미지, 동영상, ..."

게다가.... 데이터웨어 하우징, 전사적 자원관리, 지식 관리 시스템..... 등등등....

단 한줄의 자료... 예를 들면......

"코난이는 다리도 길고 잘생겼다.." (-_-+ 거기 그냥돌도 아닌 짱돌 던진분 누구신가요... -.-+)

라는 한줄의 자료로.......   실로 책한권 분량의 자료를 분석해내야 할 때가 된거지요..

코난이의 다리는 기니까.. 키는 180이상이고... 잘생겼다는걸 보니... 왕자병인지... 사실인지..

알 도리가 없으며..... ......................................................................."

라는 방식이 필요해진겁니다.

그래서 희한한? ROLLUPP / CUBE 라는 연산자가 생기는 거지요....

이 ROLLUP과 CUBE는 SQL2000의 analysis 서비스를 사용하기 위한 기본적인

질의 입니다. 추후 데이터 웨어 하우징과 데이터 마이닝을 사용하기위한

초석이 되는 T-SQL 연산자라는 의미 이지요.

이개념을 잘 잡으시면? 추후의 analysis 서비스 강좌에서 큰 무리 없으실 겁니다.

수고 하셨습니다.

************************************************************************

9. JOIN을 이용한 테이블의 연결

안녕하세요. 코난 입니다. 이번에 소개해 드릴 내용은?

JOIN을 사용한 테이블의 연결 입니다.  

원리를 잘 생각해 보신다면? 쉽게 해결이 가능한 부분이니 찬찬히 음미하면서 

보시길 바랍니다.

먼저 조인을 보시기 전에....

저희들의 샘플 데이터베이스인 Pubs 데이터베이스를 좀더 깊이 들여다 보도록

하지요.

시작 -> 프로그램 -> Microsoft SQL Server -> 엔터프라이즈 관리자 를 실행 합니다.

이미 살짝 말 드렸지요?

MSSQL서버를 총체적으로 관리하는 툴 입니다.

아울러 많이 사용하시게 되실 거구요.

이렇게  Pubs 데이터베이스 까지 내려 가신후..

다이어 그램에서 마우스 우 버튼을 누르신후 새 데이터베이스 다이어그램을 선택 하세요.

앞으로 자주 보시게될 위저드라는 아주 편리한 MS의 인터페이스 입니다.

다음을 누르세요.

Authors 테이블과 titleauthor 테이블, titles 테이블을 추가 버튼을 눌러 고르신후

다음을 누르세요.

완료 화면입니다. 여기서 이제 마침을 누르세요.

그럼 이렇게 테이블 3개의 구조와 뭔지는 잘 모르겠지만 선으로 연결된 "관계"가 보이실

겁니다. 글치요?

이렇게 찬찬히 우선 저 그림을 보시면서 이것저것 눌러도 보시고 해보세요.

중요한건 저희가 사용하는 샘플 데이터베이스인 Pubs 데이터베이스도 저렇게

테이블과 테이블이 어떤 관계로 얽혀 있다는 점입니다.

왜 테이블을 분리해 두지? 하나로 두면 안되나?

자 이제 찬찬히 코난이의 이야기를 잠깐 드리도록 하지요.

ID	성별
KONAN	남
BAEZZANG	남
오양	여
DEVIL	남
BLACK	남
...	...

이러한 식의 데이터가 들어가 있다고 생각해 보세요.

별 문제 없지요? 데이터는 저런식으로 한 100개 정도만 들어가 있다고 생각해 보죠.

자.. 그런데 이제 문제가 생겼습니다.

성별이 남 또는 여로 되어 있는 것을 회사가 다국적 기업으로 커 나가면서..

남 -> MALE      여 -> FEMALE 로 바꿔야 할 상황이 되었습니다.

그럼 어떻게 바꿔야 할까요?

100개 정도 우습지 뭐~~~ 그냥 손가락 노가다 시켜 뿌리자~~~~

하실 건가요?

만약 데이터의 수가 10만개라면 어케 하실 건가요? 손가락이 노가다로 견디다 못해

파스값이 월급보다 더 나올수 있겠죠?

다른 상황으로.. ID, 직급 이라는 식이어서.. 남, 여 가 아니라..

과장 -> 실장    부장 -> 차장   대리 -> 사원  ....

식으로 상당히 많은 문제가 발생 한다면? 어케 하실 건가요?

자 우선 문제를 해결해 보도록 하지요... 바로..!!!!

ID - 성별ID 테이블

ID	성별ID
KONAN	1
BAEZZANG	1
오양	2
DEVIL	1
BLACK	1
...	...

성별ID - 성별 테이블

ID	성별
1	남
2	여

이렇게 처음부터 두개의 테이블로 나누어서 테이블 설계를 했다고 생각해 보죠.

아울러 윗쪽의 ID-성별ID 테이블의 성별 ID컬럼의 1, 2는?

아래쪽 성별ID-성별 테이블을 "참조한다" 라고 생각 하시고.

그럼?

남 -> MALE 로 바꾸어야 할 상황이 되며.. 여 -> FEMALE로 바꿀 상황이 되었습니다.

이때는?

ID	성별
1	MALE
2	FEMALE

로 바꾸기만 한다면?

어떠세요? 

다른건 건드릴 필요가 없겠지요?

지금은 이런 간단한 상황이지만.. 회사의 직급을 생각해 보세요..

직급에 대한 테이블을 따로 빼 두고.. 직급명에 대해 변경이 일어날시는? 저렇게

직급에 대한 정보가 있는 테이블만 바꾸면 더 쉽겠지요. 이해가 되시는지요?

추후 데이터를 합쳐서 보고 싶을 때는 바로 오늘 배우실 내용인 JOIN문을 사용하여

ID	성별
KONAN	남
BAEZZANG	남
오양	여
DEVIL	남
BLACK	남
...	...

이렇게 테이블을 다시 생성이 가능해 집니다.(실제 생기진 않고 논리적으로 생성되는 것입니다.)

이러한 테이블을 쪼갤 수 있는데 까지 쪼개는 과정을 보통 정규화 과정 이라고 말을 합니다.

대부분의 DB책등에서 이 DB설계와 정규화의 이야기를 보통 맨 앞에 두어

가뜩이나 어려운 DB를 더 어렵게 하고 있지요...

이 이야기는 8장 관계형 데이터베이스 이해 부분에서 좀더 상세히 얘기 드릴 겁니다.

그냥 저런게 정규화구나.. 정도만 알아 두세요.

자!!! 그럼 우리의 Pubs 데이터 베이스로 다시 돌아가 보지요!!!

위에서 생성해 보신 테이블 3개가 연결되어 있는 그림을? 데이터베이스 다이어그램

이라고 합니다. 테이블과 테이블의 연관성을 한눈에 보게 해 주는 것이지요.

조인에 대해 이제 찬찬히 이야기를 드려 보지요.

Join은 2개 이상의 테이블에서 1개의 테이블 집합을 생성해 질의 결과를 얻게 해주는 방법.

RDBMS는 정규화 과정을 거쳐 테이블을 나누어 설계 하게 됨.

이때 여러개의 테이블중 논리적인 테이블을 만들어 결과를 생성하게 하는 방법을 의미함.

 조인의 종류는 5가지로 나뉘어 집니다.

1. INNER JOIN

2. OUTER JOIN

3. CROSS JOIN

4. FULL OUTER JOIN

5. SELF JOIN

조인의 방식은 3가지로 나뉘어 집니다.

1. Nested loop Join

2. Hash Join

3. Merge Join

종류에 대한 이야기니 그렇구나 하시면 되구요..

주로 사용되는 조인은? 맨 처음의 INNER JOIN 이라는 녀석 입니다.

위의 ID-성별 처럼 두개의 테이블을 합칠때 사용되는 녀석 이지요.

먼저 INNER JOIN에 대해 말을 드리지요.

1. INNER JOIN

두개의 관련된 키가 있는 테이블에서 Column의 값을 비교 후

Join 조건에 맞는 행만 검색합니다. 

SQL서버의 기본 조인 방식 입니다.

 

SELECT * from authors

SELECT * FROM publishers

SELECT *
FROM authors AS a INNER JOIN publishers AS p
ON a.city = p.city

자 위의 조인문이 중요한 것은 아닙니다. 우선은 샘플만 보여 드린 거지요.

위의 두개 SELECT 구문은 데이터를 먼저 찬찬히 봐 보시라는 의미 이며..

세번째 쿼리가 JOIN 쿼리 입니다.

이제 INNER JOIN의 좀더 다른 샘플을 보도록 하지요.

ID-이름   테이블

ID	이름
KONAN	김대우
BAEZZANG	이동혁
DEVIL	은정범

이런 테이블이 있구요.

아울러.

ID - 정보   테이블

ID	나이	성별
KONAN	18	남
BAEZZANG	45	남

의 식이라고 생각해 보세요 아시겠지요?

여기서!!! 가능한 모든 조합을 한번 뽑아 보도록 하지요.

ID-이름의 이름	ID-이름의 ID	ID-정보의 ID	ID-정보의 나이	ID-정보의 성별
김대우	KONAN	KONAN	18	남
김대우	KONAN	BAEZZANG	45	남
김대우	KONAN	NULL	NULL	NULL
이동혁	BAEZZANG	KONAN	18	남
이동혁	BAEZZANG	BAEZZANG	45	남
이동혁	BAEZZANG	NULL	NULL	NULL
은정범	DEVIL	KONAN	18	남
은정범	DEVIL	BAEZZANG	45	남
은정범	DEVIL	NULL	NULL	NULL

이런 식이 될겁니다.

그렇지요?

여기서!!!

결과들중!!! 바로 파란색으로 된 녀석들!!!!

ID-이름 테이블의 ID컬럼과 ID-정보 테이블의 ID 컬럼이 같은 녀석들이 의미 있는

값이라는 것이지요!!!!  다른 값들은 바로 쓰레기 값이다!!! 라는 의미 입니다.

나온 결과중 의미 있는 결과는!!!

ID-이름의 이름	ID-이름의 ID	ID-정보의 ID	ID-정보의 나이	ID-정보의 성별
김대우	KONAN	KONAN	18	남
이동혁	BAEZZANG	BAEZZANG	45	남

이런 식이 될겁니다.

이런식으로 생각해 보시면 조금 빨리 이해가 되시지요?

이렇게 여러 조합중 조건에 맞는 즉! 조건 컬럼이 같은 녀석을 뽑아 내는 것이 바로

JOIN  입니다.

따라서 이런 결과과 되지요.

이를 SQL문으로 생각해 본다면?

 

--실행 안됩니다. 

SELECT id-이름.이름, id-이름.ID, id-정보.id, id-정보.나이,id-정보.성별

FROM id-이름 INNER JOIN id-정보

ON id-이름.ID = id-정보.ID

이런 식이 되겠지요!! 조건은 바로 맨 아래줄의 ON 키워드 라는 것입니다.

자 상당히 많은 내용을 배우셨네요.

다음 내용은? INNER JOIN의 문법과 간단한 샘플들 입니다.

조인의 구현

각 테이블에서 하나의 컬럼을 사용하여 두개의 테이블을 연결하는 것

- 고려사항

- 연결하려는 컬럼은 조인에 포함된 각 테이블에 있는 데이터를 쉽게 일치시키거나 비교할 수 있어야 한다.

- ANSI SQL 문법 또는 SQL 서버 문법을 사용할 수 있다.

- 하나의 SELECT 문에서 동시에 ANSI SQL문법과 SQL서버 문법을 사용 할 수 없다.

- 두 테이블 모두에 존재하는 컬럼 이름을 참조하는 경우에는 반드시 table_name.column_name 형식을 따라야 한다.

조인의 구현 - ANSI SQL  문법

SELECT table_name.column_name [, table_name.column_name…]

        FROM {table_name[join_type] JOIN table_name ON search_conditions}

        WHERE [search_condition…]

        - WHERE 절을 사용한 행의 선택에서 연결된 테이블을 구성한다.

형식

        SELECT table_name.column_name[, table_name.column_name…]

        FROM {table_name, table_name}

        WHERE table_name.column_name join_operator table_name.column_name

- 컬럼 들의 값을 한 행씩 비교하여 비교 결과가 참일때 그 행을 나열한다.

- FROM절에 조인에 관련되는 모든 테이블을 나열하고 WHERE절에 어떤 행동들이 결과에 포함되어야 하는지를 명시한다.

WHERE 절에 사용할 수 있는 연산자들

        =,  >,  <,  >=,  <=,  <>

Inner 조인

두 테이블을 연결 조건에 맞는 행들만 포함하는 세번째 테이블로 연결한다.

내부 연결의 일반적 유형

        - Equijoin

                - 비교되는 컬럼의 값이 같을 경우에 연결이 이루어진다.

                - 중복된 컬럼 정보를 만들게 된다.

        - Natural join

                - Equijoin 이 만들어 내는 결과 집합에서 중복된 컬럼의 데이터를 제거한다.

 

SELECT pub_name, title

FROM titles INNER JOIN publishers

ON titles.pub_id = publishers.pub_id

 

/* ANSI 조인 */

USE pubs

        SELECT authors.au_lname, authors.state, publishers.*

        FROM publishers INNER JOIN authors

                ON publishers.city = authors.city

 

/*T-SQL 조인*/

USE pubs

        SELECT authors.au_lname, authors.state, publishers.*

        FROM publishers, authors

        WHERE publishers.city = authors.city

북스 온라인 상에서는 T-SQL조인을 사용하기 보다는 ANSI 조인의 사용을 권하고 있습니다.

T-SQL조인은 사실 약간 모호할 가능성이 있기 때문 입니다.

추후 스터디를 위해서라도 가능하심 ANSI - SQL로 배우시길 바랍니다.

다음은 두번째의 OUTER JOIN 입니다.

LEFT 또는 RIGHT OUTER JOIN.

두 테이블에서 지정된 쪽인 LEFT 또는 RIGHT 쪽의

모든 결과를 보여준후 반대쪽에 대해는 매칭값이 없어도

보여주는 JOIN을 의미

조금 난해 하지요?

역시나 샘플을 보시면? 감이 빡빡 오실 겁니다.

위의 샘플과 마찬 가지로...

ID-이름   테이블

ID	이름
KONAN	김대우
BAEZZANG	이동혁
DEVIL	은정범

이런 테이블이 있구요.

아울러.

ID - 정보   테이블

ID	나이	성별
KONAN	18	남
BAEZZANG	45	남

다시 가능한 모든 조합입니다.

ID-이름의 이름	ID-이름의 ID	ID-정보의 ID	ID-정보의 나이	ID-정보의 성별
김대우	KONAN	KONAN	18	남
김대우	KONAN	BAEZZANG	45	남
김대우	KONAN	NULL	NULL	NULL
이동혁	BAEZZANG	KONAN	18	남
이동혁	BAEZZANG	BAEZZANG	45	남
이동혁	BAEZZANG	NULL	NULL	NULL
은정범	DEVIL	KONAN	18	남
은정범	DEVIL	BAEZZANG	45	남
은정범	DEVIL	NULL	NULL	NULL

이때 INNER JOIN과 다른점은?

다시 가능한 모든 조합입니다.

ID-이름의 이름	ID-이름의 ID	ID-정보의 ID	ID-정보의 나이	ID-정보의 성별
김대우	KONAN	KONAN	18	남
이동혁	BAEZZANG	KONAN	18	남
이동혁	BAEZZANG	BAEZZANG	45	남
은정범	DEVIL	NULL	NULL	NULL

따라서 이런 결과과 됩니다.

즉!!!  

FROM id-이름 LEFT OUTER JOIN id-정보

ON id-이름.ID = id-정보.ID

이런 LEFT OUTER JOIN 키워드가 있을 경우 JOIN의 왼쪽에 표시된 테이블인

id-이름 테이블은 오른쪽에 매칭 되는 결과가 없어도!!! 왼쪽 테이블을 우선 보여 준다는

의미 입니다.

그래서 ID-이름 테이블의  은정범 : DEVIL 은 매칭되는 ID-정보 테이블의 로우가 없어도

우선은 보여 준다는 의미 이지요.

이를 SQL문으로 생각해 본다면?

 

SELECT id-이름.이름, id-이름.ID, id-정보.id, id-정보.나이,id-정보.성별

FROM id-이름 LEFT OUTER JOIN id-정보

ON id-이름.ID = id-정보.ID

의 식이 된다. 이것이 LEFT OUTER JOIN 이다.

RIGHT OUTER JOIN 역시 같습니다. 오른쪽 테이블은 무조건 보이고 왼쪽은 NULL로

표기 한다는 의미 입니다. LEFT 와 같지요? 

코난이의 경우 대부분 LEFT를 많은 사람이 사용하는 경우를 보았지..

RIGHT는 쓰시는 분이 거의 없더군요..

그렇다면!!! 이 LEFT OUTER JOIN은 주로 언제 사용하는가!!!

코난이의 경우 몇번 전자 상거래 구축시 사용한 경험이 있습니다. 

바로 제품 - 판매량을 볼때 이지요.

특정 제품들의 리스트를 보기위해 아래의 표와 같은 데이터를 보려 합니다.

이런 테이블을 INNER JOIN해 볼때는? C 제품의 경우..

즉!! 하나도 팔리지 않은 제품도 분명히 있을 겁니다.

제품	판매량
A	10
B	20
C	NULL

이때..!

C제품도 우선은 보이게 하고 판매량은 NULL로 보이더라도 우선은 보이고 싶을때

입니다. 그래서 LEFT OUTER JOIN을 사용하게 되는 것이지요..

비슷한 케이스가 상당히 많으니.. 이 LEFT OUTER JOIN도 알아두심 많은 도움 되실 겁니다.

이러한 OUTER JOIN의 구문 정보와 샘플 입니다.

Outer 조인

한 테이블에 있는 행에는 제한 조건을 가하지 않는 

반면에 다른 테이블에 대해서는 행에 제한을 한다.

고려사항

        - 관계된 테이블에서 일치하지 않는 outer 테이블의 모든 행을 보여준다.

        - 두 테이블간에만 이루어질 수 있다.

        - 기본키와 참조키가 동기화 되지 않았는지 등을 알아보는데 유용하게 사용된다.

 

SELECT title, stor_id, ord_num, qty, ord_date
FROM titles LEFT OUTER JOIN sales
ON titles.title_id = sales.title_id

다음은 CORSS JOIN 입니다.

CROSS JOIN

연관된 두개의 테이블에서 가능한 모든 조합을 찾는다.

 

SELECT au_fname, au_lname, pub_name
FROM authors CROSS JOIN publishers 

자 역시 찬찬히 풀어서 말을 드릴 차례 이지요~~

위의 샘플과 마찬 가지로...

ID-이름   테이블

ID	이름
KONAN	김대우
BAEZZANG	이동혁
DEVIL	은정범

이런 테이블이 있구요.

아울러.

ID - 정보   테이블

ID	나이	성별
KONAN	18	남
BAEZZANG	45	남

다시 가능한 모든 조합입니다.

ID-이름의 이름	ID-이름의 ID	ID-정보의 ID	ID-정보의 나이	ID-정보의 성별
김대우	KONAN	KONAN	18	남
김대우	KONAN	BAEZZANG	45	남
김대우	KONAN	NULL	NULL	NULL
이동혁	BAEZZANG	KONAN	18	남
이동혁	BAEZZANG	BAEZZANG	45	남
이동혁	BAEZZANG	NULL	NULL	NULL
은정범	DEVIL	KONAN	18	남
은정범	DEVIL	BAEZZANG	45	남
은정범	DEVIL	NULL	NULL	NULL

지금 보고 계신 이 모든 가능한 조합이? 바로 CORSS JOIN 의 결과 입니다.

자 여기엔 쓰레기 값이 상당히 많습니다.

LRFT OUTER JOIN에서 사용되는 맨 아래 줄을 제외 하더라도..

6개의 노란 셀로 표시된 로우는 모두 쓰레기 값이라는 것이지요.

그래도 CORSS JOIN에서는? 모두 보여 줍니다.

코난이의 경우 실무에서는 한번도 사용해 본적 없지만..

이런게 있다는 것은 알아 두시길 바랍니다.

다음은 FULL OUTER JOIN 입니다.

FULL OUTER JOIN

LEFT OUTER JOIN의 결과와 RIGHT OUTER JOIN의

결과를 표시한후 한번 중복되는 값(INNER JOIN의 값)의 중복을 제거한 값을 표시한다.

 

SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a FULL OUTER JOIN publishers p
ON a.city = p.city

이는? LEFT OUTER JOIN과 RIGHT OUTER JOIN을 실행 해 보심 아실 수 있습니다.

두 OUTER JOIN을 실행한후..

겹치는 부분인 INNER JOIN의 결과 부분이 두번 나오겠지요?

이 INNER JOIN의 결과를 한번 제외한 결과 라고 생각 하심 빠릅니다.

LEFT , RIGHT 조인을 실행 한다고 생각하심 빠르다는 의미 입니다.

코나니는 실무에서 사용해본 경험이 없습니다. - 거의 사용 안한다는 말이지요.

다음은 SELF JOIN 입니다.

SELF JOIN

사용하는 경우 : 

1. 계층적인 구조를 테이블화 할 경우.

2. 한 테이블에서 일치하는 값을 찾고자 하는 경우.

아울러 반드시 테이블 Alias 명을 사용해 질의해야 한다.

판매 라는 테이블이 있다고 생각해 보세요..

우선 판매 라는 테이블을 별명으로 판매 a 라고 잡겠습니다.

판매a

구매자 ID	물품
KONAN	디아블로2
KONAN	스타크래프트
BAEZZANG	니드포스피드
DEVIL	프리셀
DEVIL	디아블로2

아울러 판매 b 라는 테이블이 또 있다고 생각 하지요.. 바로

구매자 ID	물품
KONAN	디아블로2
KONAN	스타크래프트
BAEZZANG	니드포스피드
DEVIL	프리셀
DEVIL	디아블로2

이렇게 같은 테이블 입니다.

구매자 ID	물품	구매자 ID
KONAN	디아블로2	DEVIL

이런 식으로 찾기를 원한다고 생각해 보세요..

바로!!! 같은 물품을 구매한 다른 사람을 찾고 싶을때 입니다.

 

SELECT au_fname, au_lname, zip, city
FROM authors
WHERE city = 'Oakland' 

이 쿼리를 먼저 실행해서 결과를 확인한후 Self-Join 의 쿼리를 실행해 보지요.

결과를 찬찬히 봐 보세요.. ZIP 코드가 같은 작가들이 있습니다.

이때 이를 찾을때 어떻게 찾으면 될까요?

 

SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname, au1.zip
FROM authors au1 INNER JOIN authors au2 
ON au1.zip = au2.zip
WHERE au1.city = 'Oakland' 
and au1.au_id <> au2.au_id
ORDER BY au1.au_fname ASC, au1.au_lname ASC

이 쿼리는 오클랜드 지역에 사는 작가들중 zip 코드가 같은 사람이 있는가 하는 쿼리 이다. 

즉, 오클랜드 지역에 같은 zip코드에  거주하는 사람이 있는가 하는 쿼리 이다.

코나니가 책회사 사장 이라면? 같은 지역에 거주하는 작가들 끼리 서로의 정보를

공유해 더 좋은 책을 쓰게 함 좋겠죠? 이럴 경우 사용 가능한 질의 입니다.

이제 SELF JOIN의 구문정보 입니다.

Self 조인

테이블의 행을 같은 테이블 안에 있는 다른 행과 연관시킨다.

- 비교되는 컬럼은 같은 자료형이어야 하고 여러 방법에 대해 비교 가능해야 한다.

- 같은 테이블을 조인하기 위해서는 하나의 테이블을 두개의 다른 논리적인 테이블로 

    참조 할 수 있도록 별명을 할당해야 한다.

ANSI SQL 문법

        SELECT column_name, column_name [, column_name…]

        FROM table_name alias [join_type] JOIN table_name alias

                ON search_conditions

T - SQL 서버 문법

        SELECT column_name, column_name [, column_name…]

        FROM table_name alias, table_name alias [, table_name…]

        WHERE alias.column_name join_operator alias.column_name

코나니의 경우 SELF 조인을 가끔 사용한 경험이 있습니다.

아울러 최근 많은 이슈가 되고 있는 CRM(Customer Relationship Management)

에서 종종 사용될 경우가 있으니.. 주의해 보심 많은 도움 되실 겁니다.

다음은 둘 이상의 테이블을 조인할 경우 입니다.

둘 이상의 테이블 조인

ASNI SQL 문법

SELECT table_name.column_name [, table_name.column_name…]

FROM table_name[join_type] 

JOIN table_name ON search_conditions…[join_type]JOIN table_name ON search_conditions

WHERE [search_condition…]

T - SQL문법

SELECT table_name.column_name[, table_name.column_name…]

FROM table_name, table_name[, table_name…]

WHERE table_name.column_name join_operator

                table_name.column_name

        [AND table_name.column_name join_operator

                table_name.column_name…]

ANSI 표준을 가능하면 보시고.. 간단히 ON 키워드에 추가추가 하심 됩니다.

 

/*ASNI 조인*/

        SELECT stor_name, qty, title

        FROM titles INNER JOIN sales

                                ON titles.title_id = sales.title_id

                        INNER JOIN stores

                                ON stores.stor_id = sales.stor_id

/*T - SQL 조인*/

        SELECT stor_name, qty, title

        FROM titles, sales, stores

        WHERE titles.title_id = sales.title_id AND

                stores.stor_id = sales.stor_id

끝으로 조인의 방식에 대한 이야기 입니다.

조인의 방식은 3가지로 나뉘어 집니다.

1. Nested loop Join

2. Hash Join

3. Merge Join

의 식입니다.

이는 조인이 내부적으로 사용하는 알고리즘에 대한 이야기 입니다.

이에 대한 내용은 알고리즘 이야기 인데..

코난이가 화일 시스템 시간에 배웠던 HASH의 이야기완 조금 틀리고..

MERGE 조인의 경우 제가 알고있는 MERGE에 대한 내용과 역시나 틀리 더군요.

아울러 Nested Loop는 루프를 돌며 조건 찾기로 아마 이해가 쉬우실 겁니다.

이에 대한 자료를 sqler의 Tip 게시판에 올려 두었으니 참고 하시길 바랍니다.

많은걸 배우 셨네요...

테이블 다이어 그램 생성하기....

간단한 정규화와 테이블에 대한 짧은 이야기...

조인의 다양한 방식들...

수고하셨네요...

그럼 다음 이야기인.. Sub Query에서 뵙지요.

************************************************************************

10. SUB QUERY 부질의의 사용

흐허~~~ 무지막지 하게 더운 날들 입니다. 글치요?

이렇게 더버도.. 코나니는 강좌를 쓴답니다... ~~~

오늘 소개해 드릴 내용은 

부질의 = 서브질의 = Sub Query 로 여러가지 식으로 불립니다.

큰 어려운 내용은 없으니 부담 없이 보셔도 되실 겁니다.

코나니는 위 세개를 혼용해 쓰니 위의 세가지 말을 잘 봐 두세요...

저 부질의 라는 말을 들으시면 대강 어떤 것인가 감이 잡히지 않으시나요?

간단히 소개를 드리자면..

SELECT 컬럼 FROM 테이블1 WHERE 컬럼1 IN (SELECT 컬럼 FROM 테이블2)

이런 식이 랍니다. 어떠신지요? 느낌이 조금 오시져?

먼저 샘플을 보시겠습니다.

 

SELECT * FROM titles
WHERE title_id IN (SELECT title_id FROM sales)

자 먼저 두번째 줄의 괄호 안을 봐 보세요.

 

SELECT title_id FROM sales

이 녀석의 결과 값을 찬찬히 봐 보시고..

첫번째 쿼리를 찬찬히 봐 보세요. 느낌이 오시죠?

먼저 괄호 안의 질의를 수행한후.....

괄호안의 질의의 리턴값을 상의 질의의 WHERE절에 사용한다는 의미 이지요.

다음 질의와 비교해 보세요

 

SELECT * FROM titles
WHERE title_id = (SELECT title_id FROM sales WHERE title_id like 'PS2106')

뒤에 WHERE절이 하위 질위에 붙어 있지만.. 

중요한건 그게 아니라..

IN 연산자 대신 = 연산자를 사용한 부분 입니다.

부질의의 리턴값이 1개 일경우는 = 연산자를 사용해도 되지만..

부질의의 리턴값이 여러개일 경우는? 반드시 IN 연산자를 사용해야 원하는 결과를

얻으시는 겁니다.

그럼 서브질의의 구문입니다.

서브질의란?

서브 질의

  - SELECT,  INSERT,  UPDATE,  DELETE 문에 중첩된 내부 SELECT 문

  - 가장 먼저 평가된다.

  - 단일 값을 반환하는 서브질의

      - 단일 값이 사용될 수 있는 곳이면 어느 곳이든 사용될수 있다.

  - 단일 컬럼을 반환하는 서브질의

      - WHERE절에서만 사용 될 수 있다.

형식

  (SELECT [ALL | DISTINCT] subquery_select_list

      [FROM {table_name | view_name} [ optimizer_hints]

         [[, {table_name2 | view_name2} [ optimizer_hints]

         […,{table_name16 | view_name16} [ optimizer_hints]]]

         [WHERE clause]

         [GROUP BY clause]

        [HAVING clause])

 - 항상 괄호로 묶여야 한다.

제약사항

 - 비교 연산자로 시작하는 서브질의의 select_list 에는 하나의 표현식 

    또는 컬럼 이름만이 포함된다.

 - 단일 값을 반환한다면 표현식이 허용되는 모든 곳에서 사용 가능

 - 텍스트와 이미지 자료형의 컬럼은 허용되지 않는다.

 - 서브질의에서 생성된 뷰는 갱신될 수 없다.

 - EXISTS를 포함하는 경우에 select_list는 *로 구성되어야 한다

 - 중첩의 수준에 대한 실질적인 제한은 없다.

 

SELECT title FROM titles

WHERE title_id IN (SELECT title_id FROM sales)



SELECT title FROM titles

WHERE title_id IN

(SELECT DISTINCT title_id FROM sales)

다음은 상관 관계가 있는 부질의 입니다.

간단히 하위의 질의와 상의의 질의가 관계가 있다.. 라는 의미 이지요.

일반 부질의와의 차이점은?

일반 부질의는 하위 질의와 상의 질의 따로따로 실행이 우선 가능 합니다.

하지만 상관관계 부질의는 상위와 하위 질의 따로따로 실행이 불가하며 반드시

같이 실행을 해야만 합니다.

먼저 샘플을 보시지요.

 

SELECT title_id, au_id, royaltyper

        FROM titleauthor ta

        WHERE royaltyper =

                ( SELECT MAX(royaltyper)

                        FROM titleauthor

                        WHERE title_id = ta.title_id)

상관 관계가 있는 서브질의

WHERE 절이 외부 질의의 FROM 절에 있는 테이블을 참조 하는 것

 - 서브질의가 외부 질의에 의해 참조되는 테이블의 각 행에 대해 평가 된다

 - 외부 질의에 의해 선택 될 수 있는 각 행들에 대해 반복적으로 수행된다.  -> 반복질의

 - 상관관계가 있는 서브 질의를 포함하는 질의에서는 서브질의의 값이 외부 질의에 의존한다.

어떠세요? 그다지 어려운 내용은 아니지요?

그럼 약간더 깊은 이야기 입니다.

이 부질의와 JOIN에서 뭔가 공통점을 느끼시나요?

아래 샘플을 함 봐 보실까요?

 

use northwind

go

/* 부질의를 사용한 쿼리 */
SELECT ProductName
FROM Northwind.dbo.Products
WHERE UnitPrice =
(SELECT UnitPrice
FROM Northwind.dbo.Products
WHERE ProductName = 'Sir Rodney''s Scones')

/* 조인을 사용해 같은 결과 집합을 생성하는 쿼리 */
SELECT Prd1.ProductName
FROM Northwind.dbo.Products AS Prd1
JOIN Northwind.dbo.Products AS Prd2
ON (Prd1.UnitPrice = Prd2.UnitPrice)
WHERE Prd2.ProductName = 'Sir Rodney''s Scones'

어떠세요? 조금 느낌이 오시죠?

부질의 역시 JOIN처럼 사용이 분명히 가능하답니다.

SQL서버 북스 온라인 상에서도 부질의 = 조인 이라는 공식을 보여주고 있습니다.

하지만 중요한것은? 어느 녀석이 더 효과적인가 하는 질문 입니다.

몇번 이런 질문을 누군가가 하신적이 있는데요...

많은 분들이 부질의가 빠를것이다 라고 답을 하실듯 합니다.

하지만 실제는 그렇지 않습니다. 

부질의와 JOIN 중 JOIN이 더 빠르고 부하도 적습니다.

이유인 즉슨....

부질의는 사용자가 쿼리의 순서를 임의로 지정하는 방식 입니다.

아울러 JOIN은 실행이 될때..

SQL서버의 쿼리 최적화기(쿼리를 수행하는 녀석)이 JOIN구문을 먼저 분석후

최적화된 실행 계획을 세워 수행하므로 느릴 듯 하지만 이게 더 빠른 속도와

퍼포먼스를 가진다고 합니다. 가능하심 조인을 잘 알아 두심 좋겠지요...

도움 되셨길 바라구요..

그럼 이정도로 부질의에 대한 이야기는 접을까? 합니다~~~

더운 하루 조심 하시구요..

이만..

************************************************************************

11. SELECT INTO를 사용한 테이블 복사

이번에 소개해 드릴 내용은? SELECT INTO를 사용한 테이블 복사 입니다.

사실 SELECT INTO를 사용한 결과셋 복사가 더 정확한 의미 겠지만..

이렇게 그냥 주제를 잡았습니다.

간단합니다.

먼저...

 

SELECT * FROM titles

그럼 결과가 쭈욱~~~ 보일 겁니다.

이 결과를 그대로 하나의 테이블로 생성 하려면?

여러가지 방법이 있겠지요? 추후 배우실 데이터 트랜스퍼 서비스(DTS)를 쓰시거나..

BCP로 복사 하시거나.. 등등등의 방법이 있을 겁니다.

그런 방법이 있다는 것만 알아 두시고...

이곳에선? SELECT INTO 를 사용한 복사를 배우실 겁니다.

 

SELECT * INTO konan_titles_test FROM titles

그럼 결과로  (18개 행 적용됨) 이런 결과가 보이실 겁니다.

그리고 이어서..

 

SELECT * FROM konan_titles_test

해보시면?  어떠세요? titles 테이블과 완전히 같은 데이터가 들어 있는

konan_titles_test 테이블이 보이시나요? 

이것이 바로 SELECT INTO의 방식 입니다.

지금 여러분이 테스트 하는 데이터베이스인 Pubs 데이터베이스는 조금 특별한 데이터

베이스 입니다. 

이 SELECT INTO 구문을 기본적으로 사용 가능으로 되어 있다는 의미 입니다.

 

sp_dboption pubs

결과 부분을 보시면?

The following options are set: 
----------------------------------- 
select into/bulkcopy
trunc. log on chkpt.
torn page detection
auto create statistics
auto update statistics

이렇게 되어 있을 겁니다.

여러분이 앞으로 DB를 생성하게 되면 위의 Pubs데이터베이스에 걸린 옵션과

다를 겁니다. 이때 처리를 위해...

 

-- 사용 방식은 sp_dboption '디비명', '사용할 옵션명', '활성화 비활성화 옵션'

sp_dboption 'pubs', 'select into/bulkcopy', 'true'

이런 식으로 Pubs 데이터베이스에 대해서 SELECT INTO와 BULK COPY 옵션을

TRUE로 한후 SELECT INTO를 사용 하겠다 라고 명시한후 사용 하셔야 하며..

SELECT INTO 작업을 마치신 후에는...

 

sp_dboption ' pubs', 'select into/bulkcopy', 'false'

로 FALSE 시키시는 연습을 꼬옥 하시길 바랍니다.

아울러.. DB_OPTION에 대해서는? 추후 얘기를 또 드리니..

그때 상세히 얘기 드릴 겁니다.

SELECT INTO의 구문 정보 입니다.

SELECT INTO

일반적인 데이터 정의 절차를 거치지 않고 테이블을 정의하고 

데이터를 넣는 것을 허용한다.

 SELECT select_list

 INTO new_table_name

 FROM table_list

 WHERE search_conditions

질의 결과를 바탕으로 새로운 테이블을 생성한다.

  - select_list 에서 지정한 컬럼, FROM 절의 테이블, WHERE 절에서 선택 한 행들에 

    바탕을 두고 생성된다.

고려사항

 - select into / bulkcopy 데이터 베이스 옵션이 설정되어 있을 때만, 

    새로운 영구 테이블을 생성한다.

 - 임시 테이블에 대해서는 select into / bulkcopy 옵션이 설정되어 있지 않은 경우에도 

    SELECT INTO 문을 수행할 수 있다.

 - 이미 존재하는 테이블로 행을 보내여려면 반드시 INSERT 를 사용해야 한다.

 - select_list 에 있는 컬럼이 이름이 없는 경우에는 생성된 테이블에서 해당 컬럼에는 

    이름이 지정되지 않는다.

 - “SELECT * FROM tablename”에 의해서만 참조될 수 있다.

 - 삽입된 행들은 로그에 기록되지 않는다.

 

--특정 결과셋만 konan_test2 테이블로 생성

SELECT title_id, title, type INTO konan_test2 FROM titles where price > 10

코나니의 경우 SELECT INTO를 종종 사용 합니다.

테이블에 대한 어떤 처리를 하고 싶을때... 주로 다른 업체의 데이터 가공 처리시..

SELECT INTO를 사용해 여러가지를 가공한 데이터를 테이블로 만들어

처리할 경우 입니다... 종종 사용할 일이 있으실 테니..

도움 되시길 바랍니다.

************************************************************************

12. UNION을 사용한 테이블의 연결

이번에 소개해 드릴 내용은? 특정 형태의 테이블을 합치는 또하나의 방법

UNION 이라는 부분 입니다.

간단히니 그다지 어려워 하실 필요는 없답니다.

Table1			Table2
ColumnA	ColumnB		ColumnC	ColumnD
varchar(4)	int		varchar(4)	int
-------	---		-------	---
가	1		다	3
나	2		라	4
다	3		마	5

이러한 데이터가 있다고 생각해 보지요..

실제 데이터는?

 

--table1 생성
create table table1(
columnA varchar(4)
,columnB int
)

--table2 생성
create table table2(
columnC varchar(4)
,columnD int
)

--table1에 데이터 삽입
insert into table1 values('가',1)
insert into table1 values('나',2)
insert into table1 values('다',3)

--table2에 데이터 삽입
insert into table2 values('다',3)
insert into table2 values('라',4)
insert into table2 values('마',5)

이렇게 들어 있다고 생각 하심 됩니다.

테이블 생성 구문과 insert 구문은? 

추후에 배우시게 되니 우선은 저 구문을 그냥 돌리기만 하세요...

 

SELECT * FROM table1

SELECT * FROM table2

결과 는 이런식일 겁니다.

columnA columnB 
------- ----------- 
가 1
나 2
다 3

(3개 행 적용됨)

columnC columnD 
------- ----------- 
다 3
라 4
마 5

(3개 행 적용됨)

어떠세요? 깔끔 하지요. 이제 저희가 배울 UNION을 사용해 보도록 하지요..

 

SELECT * FROM table1
UNION
SELECT * FROM table2

결과를 주의해서 봐 보세요...

두개의 테이블에 6개의 데이터인데.. 5개만 나왔습니다.

중복된 데이터는 제거 되었다는 의미 이지요..

중복 데이터 역시 보이게 하시려면?

 

SELECT * FROM table1
UNION ALL
SELECT * FROM table2

이렇게 하시면? 결과에 6개가 모두 나옵니다. 유니언이 어떤 녀석인지 조금 감이 오시나요?

바로 테이블의 로우들을 합쳐서 하나의 테이블을 만들어 처리 한다는 방식 입니다.

중요한건? 테이블의 형이 비슷해야 가능하다는 것이지요..

다음으로 유니언의 구문 정보 입니다.

UNION 연산자

둘 이상의 질의 결과를 하나의 결과로 조작할 수 있게 한다.

   SELECT select_list…

   UNION [ALL]

   SELECT select_list

고려사항

  - UNION의 대상이 되는 테이블들의 컬럼 구조가 일치해야 하며 자료형 이 호환 되어야 한다.

  - 결과에서 중복된 행은 제거한다.

  - ALL옵션을 사용하면 모든 행을 포함한다.

  - 모든 select_list는 컬럼의 수가 같아야 하고, 비슷한(?)자료형이어야 하고 

     같은 순서 이어야 한다.

  - ORDER BY 절을 포함하고 있으면 전체 결과가 정렬된다.

  - 컬럼 이름은 첫번째 SELECT 문을 따른다.

어떠세요? 조인이나 다른것에 비해 조금 간단하지요?

코나니의 경우 UNION을 종종 사용한 경험이 있습니다.

게시판등의 구축을 위해 하나의 게시판 = 하나의 테이블로 구축한 경우에..

테이블을 합치고 싶을때에.. 이런 UNION의 사용이 가능하답니다.

코나니의 프로젝 강좌 부분의 웹 신문사 만들기때 사용한 방식 이지요..

가끔 사용할 경우가 있으니 알아 두심 역시나 도움 되실 겁니다.

그럼 이만.

************************************************************************

13. SQL 질의를 자동으로 생성하는 Query Designer사용

자 기나긴 첫번째 난관 , SELECT 를 무사히 마치심을 추카 드립니다..

이제 여러분에게 쿼리를 자동으로 만들어 주는 마법의 도구를 소개해 드리고자 합니다.

이름도 멋진 쿼리-디자이너 입니다.

자동으로 쿼리를 만들어 준다는 말에 주목해 주세요..

앞에서 보신 조인과 같은 자주 사용하시게 될 그런 쿼리의 경우 상당히 많은 쿼리 라인과

조건의 처리에 상당히 애를 먹으실 수 있답니다.

이럴 경우에 이 쿼리 디자이너를 사용 하시면? 많은 효과를 보실 겁니다.

이 쿼리 디자이너는 SQL6.5 부터 꾸준히 발전해 왔습니다.

SQL7도 되며 당연히 SQL2000도 사용 가능하니 도움 되시길 바랍니다.

그럼 쿼리 디자이너를 돌려 자동을 쿼리를 생성해 볼까엽~~~

SQL서버 엔터프라이즈 관리자를 실행 합니다. (어떤건지 이젠 아시겠져~~~)

글구 나서 위처럼 Pubs 데이터베이스 -> 테이블을 누르시면?

오른쪽 화면에 테이블 리스트가 쭈욱 보입니다.

유형이 시스템인 녀석들은 추후 배우실 거구요...

유형이 사용자 인 녀석에게만 관심을 가지시면 됩니다.

여기서 authors를 택하시고 마우스 오른쪽 버튼을 누르신 다음에..

테이블 열기 -> 모든행 반환을 누르세요.

그후 위의 붉은 선이 친 부분의 왼쪽 네모 두개가 그려진 녀석과

SQL이라고 쓰인 버튼을 누르세요.. 그러면?

이런 그림으로 짠~~ 변할 겁니다.

여기서~~~

이런 식으로 세개의 창중에서 맨 위쪽 창에서 마우스 우측 버튼을 누르신후

테이블 추가를 누르세요..

이렇게 두개의 titleauthor 테이블과 titles 테이블을 추가 해 보도록 하지요.

그리고 나서...

나온 화면에서? 이렇게... 체크박스를 추가 해 보세요..

바로!!! 해당 테이블의 원하는 컬럼에서 가져오고픈 컬럼을 체크 하는 겁니다.

그럼 결과는? 중간 부분에 쿼리문이 생성 되어 있지요? 것두 JOIN 구문과..

컬럼 리스트가 정확히요..

이제 중간 부분의 쿼리를 약간 손 봐야 합니다.

맨 앞 부분의 *, 를 지우세요..

좀더 세세히 보고 싶으시다면? 중간부분의 쿼리 부분을 쿼리 분석기로 복사해서

붙이신후 이곳에서 찬찬히 들여다 보세요.. WHERE절의 처리 등을 쿼리 분석기에서

사용하심 더 좋겠지요?

어떠신지요? 쓸만한 툴인가요? ^_^

저렇게 테이블과의 관계를 자동으로 조사해 조인 구문을 생성하며..

간단히 마우스 따각 거리기로 쿼리가 완성(미완성이지만) 된답니다.

코나니의 경우 긴 조인 구문의 경우 이 쿼리 디자이너로 생성하고요..

쿼리 분석기로 옮긴 다음에 코난이가 편집해 사용 한답니다.

보통 3개 이상의 테이블 조인이면 상당히 길어져서.. 이렇게 쓰시면 더 빠른 작업이 

가능 하답니다.

WHERE절의 처리... 아울러.. GROUP BY 도 사용 가능 하지만..

코난이의 경험상.. JOIN등에만 유용하지 다른데는 크게 장점을 못 느끼겠더라구요..

차라리 수작업으로 치는게 더 빠를때가 많았습니다...

이 쿼리 툴을 왜 미리 안 갈캬 줬냐구요?

기초가 없는 공부는 공부가 아니라 편법일 뿐입니다.

SQL구문을 기본부터 찬찬히 보지 않으신다면? 위의 쿼리 디자이너는 님들을

바보로 만들기 딱 좋은 툴이라는 말입니다.

이 쿼리 툴에만 의존하지 마시고 꼭!!! 반드시!!!!

SQL기본 구문에 대한 이해를 가지시길 바랍니다.

참고로 이 툴은 MS의 Visual Interdev 에도 있으며 사용 가능 합니다.

참고하시길 바라구요..

그럼 이렇게 SELECT 이야기를 마치심을 추카 드립니다.

다음은~~~ 데이터의 수정 입니다.

이 데이터의 수정은 SELECT에 비하면? 암것도 아니며 아주 쉽게 슥슥 보실 수 있으니

전혀 부담 안 가지셔도 되실 겁니다.

수고하셨습니다.