바로바로의 중얼중얼

바로 : 기본적으로는 2학기 김현 교수님의 SQL 수업을 대비한 사전공부이기에, 자유참가에 참가비용도 없습니다.(굳이 참가비를 내고 싶으시면, 저한테 밥을 사주시면?!;;;) IT에 대해서 완전히 무지한 인문학도를 기본 교육 대상으로 규정하고, 기본적인 데이터의 정의부터 시작해서, 기본적인 MSSQL에 대한 내용으로 진행할 예정입니다. 다만 단기합숙의 개념으로 3일간 오전부터 저녁까지 달릴 예정입니다.^^::: 

일시 : 7월 8일 ~ 8월 26일, 매주 화요일 오전 9시~12시

** 단! 7월 8일(화)만 오후 3시!!!!

장소 : 한국학중앙연구원 한국학대학원 103호

대상 : 디지털인문학에 관심이 있는 모든 사람

강의자 : 김바로

내용 : 인문학자를 위한 MSSQL 입문 강의

교재 : 뇌를 자극하는 SQL Server 2012 1.기본편(없어도 무방-0-)

한국학중앙연구원 한국학대학원 인문정보학 전공의 2학기 필수수업인 김현 교수님의 "인문정보 데이터베이스"을 준비하기 위한 입문 수업입니다. "인문정보 데이터베이스"가 기본적인 데이터베이스 지식이 없으면 힘들 수도 있는 수업이기에 전통적으로 박사생이 2학기 시작전에 준비 수업을 진행합니다. (당장 내일부터 시작인데 이제야 올리는 군요. 요즘 정신이 없어서...죄송합니다.-_)

기본적인 강의계획은 다음과 같습니다.

1주차. MSSQL 설치와 데이터베이스 기본 개념

2주차. SQL select문(where 조건절)

3주차. SQL select문(함수 사용)

4주차. SQL join 문(조인기본)

5주차. SQL join 문(조인응용)

6주차. sql create, view, drop

7주차. SQL insert, updatel, delete

8주차. SQL xml

+ XML 기반 관계형 데이터베이스 설계의 3원칙^[각주:2]

 1. 단위 노드의 XML 문서는 더 이상 분해하지 않고, 하나의 XML 컬럼에 적재한다.

단위 노드는 일반적으로 고유한 ID 값을 가지는 항목이면서 독립적인 관리의 대상이다. 

예를 들어서 종이사전의 하나하나의 명사("계절", "가구" 등)가 하나의 "단위 노드"가 된다. 물론 A로 시작하는 모든 명사("air, att....")를 "단위 노드"로 설정해도 무방하나, 실무에서 원고위탁등의 행위을 할 때 일반적으로 하나의 명사 단위로 관리를 하기 때문에 하나하나의 명사가 "단위 노드"가 되는 것이 관리하기 편하다.

 2. 주요 메타데이터 요소는 별도 테이블의 독립 컬럼 데이터로  관리한다.

일반적으로 자주 쓰이는 메타데이터 요소(ID, 이름 등)은 작업의 편이성을 위해서 독립적인 테이블로 만드는 것이 편하다. 또한 특정 서비스(갤러리, 지리정보서비스)의 구현을 위해서 필요한 데이터만을 추출하여 독립적인 테이블로 만드는 것도 작업의 편이성을 보장한다. 

다만 3번 항목을 충족시키기 위하여 View 테이블을 사용하는 것을 강력하게 권장한다.

 3. XML 본문 컬럼과 메타데이터 컬럼의 데이터는 항상 일치되도록 한다. 

XML 데이터에서 자주 사용되는 데이터를 View 테이블이 아닌 일반 테이블로 만들 경우 XML 데이터와 서비스되는 데이터 간의 상이성이 발생한다. 데이터 무결성은 DB 관리의 기본이지만, 기본이 지켜지지 않는 경우가 왕왕 발생하기에 다시 한번 강조된다. 

정말 왠만하면 View 테이블을 사용하라. 물론 View가 아닌 트리거를 사용할 수도 있다. 그러나 시스템부하나 일반적인 서비스환경을 생각하면 역시 View 기능을 사용하는 것이 비교적 합리적이다.

+ XML 데이터 입력

INSERT INTO  (테이블명)((컬럼명)) VALUES(

CONVERT(xml, -- XML이라고 명시 

N' -- 유니코드 명시

(XML 데이터)

', 1) -- data_type 설정(data_type 참고)

)

* CONVERT xml data_type(http://msdn.microsoft.com/ko-kr/library/ms187928.aspx)

0(기본값) : 불필요한 공백을 삭제하고 내부 DTD 하위 집합을 허용하지 않는 기본 구문 분석 동작을 사용합니다.

1 : 불필요한 공백을 유지합니다. 이 스타일 설정에 따라 xml:space="preserve"가 대신 지정된 경우와 동일하게 동작하도록 기본 xml:space 처리가 설정됩니다.

2 : 제한된 내부 DTD 하위 집합 처리를 설정합니다

3 : 불필요한 공백을 유지하고 제한된 내부 DTD 하위 집합 처리를 설정합니다.

+ XML 스키마 컬렉션

--스키마 만들기

CREATE XML SCHEMA COLLECTION 스키마명 AS '

XML스키마내용

'

-- 스키마 사용

CREATE TABLE 테이블명 (

              컬럼명 속성값 IDENTITY PRIMARY KEY,

              컬럼명 xml 스키마명 -- XML 스키마 컬렉션 지정

)

+ XML 데이터 조작 메소드

query() : XML 인스턴스에 대해 쿼리 : 검색된 XML 인스턴스 

value() : XML 인스턴스에서 값을 검색 : 검색된 XML 인스턴스의 값 

exist() : NULL이 아닌 결과를 반환하는지 여부를 확인 : TRUE/FALSE 

modify() : XML 데이터의 업데이트를 수행하도록 DML 문을 지정 : 반환값 없음 

nodes() : 여러개의 XML 인스턴스를 여러개의 행으로 반환 :노드별로 추출된 XML 인스턴스를 담은 행 집합(테이블) 

1. 기본적인 query()

select 칼럼명.query('/') -- 루트노드의 XML값 반환

select 칼럼명.query('/노드A') -- 자식노드 A의 XML값 반환

select 칼럼명.query('/노드A/노드B') -- 자식노드 B의 XML값 반환

select 칼럼명.query('/노드A/@속성B') -- 자식노드 A의 속성 B의 XML값 반환

2. 상대경로 지정 query()

select 칼럼명.query('/노드A/노드B/.') -- 자식노드 B 자체 XML값 반환

select 칼럼명.query('/노드A/노드B/..') -- 자식노드 B의 상위노드(A노드)의 XML값 반환

3. 재귀적내림 연산자 query()

select 칼럼명.query('//노드A') -- 모든 A 노드의 XML값 반환

select 칼럼명.query('//노드A[1]') -- 문서내 1번째 A 노드의 XML값 반환

select 칼럼명.query('//노드A[2]') -- 문서내 2번째 A 노드의 XML값 반환

select 칼럼명.query('//@속성A') -- 문서내 모든 속성A의 XML값 반환

4. 필터 사용 query()

select 칼럼명.query('//노드A[노드B]') -- 노드 B를 포함한 모든 노드 A의 XML값 반환

select 칼럼명.query('//노드A[@속성B]') -- 속성 B를 포함한 모든 노드 A의 XML값 반환

select 칼럼명.query('/노드A/노드B[노드C]') -- 노드 C를 포함한 노드 A의 자식노드 B의 XML값 반환

select 칼럼명.query('//노드A[.="키워드B"]') -- 노드값이 B인 모든 노드 A의 XML값 반환

select 칼럼명.query('//노드A[@속성B="키워드C"]') -- 속성 B의 값이 C인 모든 노드 A의 XML값 반환

select 칼럼명.query('//노드A[노드B="키워드C"]') -- 노드B의 값이 C인 모든 노드 A의 XML값 반환

5. Xpath 함수사용 query()

select 칼럼명.query('/노드A/node()') -- 노드 A의 XML값을 반환

select 칼럼명.query('/노드A/text()') -- 노드 A의 모든 하위 노드를 제외한 XML값을 반환

select 칼럼명.query('/노드A[last()]') -- 마지막 노드 A의 XML 값을 반환

select 칼럼명.query('count(/노드A)') -- 노드 A의 출현횟수를 count한 XML 값을 반환

select 칼럼명.query('sum(노드A/@속성B)') -- 노드 A의 모든 속성B을 sum 한 XML 값을 반환

6. 기본적인 value() 

select 칼럼명.value('(/노드A)[1]', 'nvarchar(100)') -- 1번째 자식노드 A의 값을 nvarchar(100) 형식으로 반환

select 칼럼명.value('(/노드A)[1]', 'real') -- 1번째 자식노드 A의 값을 real형식으로 반환

select 칼럼명.value('(/@속성A)[1]', 'real') -- 1번째 속성 A의 값을 real형식으로 반환

7. 여러개의 값 반환 value() + nodes()

SELECT 지정노드_임시명.value('/노드B', 'nvarchar(100)') 

-- "지정노드_임시명"의 노드A의 위치의 모든 노드 B의 값을 nvarchar(100) 형식으로 반환

FROM  테이블명 CROSS APPLY 칼럼명.nodes('/노드A') AS R(지정노드_임시명) 

- 노드A의 위치를 "지정노드_임시명"으로 저장

* 추천

text데이터(한글+한자) = nvarchar

geo(위경도)데이터 = real

8. 기본적인 exist()

-- exist()는 where 조건절에 쓰여서 특정값을 가져올 때 쓰인다

SELECT 칼럼명 FROM DB명 WHERE 칼럼명.exist('노드A') = (0 or 1)

9. 기본적인 modify()

9.1. replace

UPDATE DB명 

SET 칼럼명.modify('replace value of (노드A())[1] with "입력값" ') 

WHERE 조건값

9.2. insert

UPDATE DB명 

SET 칼럼명.modify ('insert 입력값 after (노드A)[1]') 

WHERE 조건값

9.3. delete

UPDATE DB명 

SET 칼럼명.modify('delete 대상위치값[1]') 

WHERE 조건값

% value()도 더 자세히 해야겠고 exist()나 modify()도 넣어야겠네.....나중에...언젠가?!

+ 실무작업 예시^[각주:3]

-- [Create Table] 기본테이블 세팅

create table jeju_xml (

id nvarchar(10) NOT NULL, -- ID 컬럼, 기본키

xmltxt xml NOT NULL, -- XML 항목 2G까지 입력가능.

primary key(id)

)

-- [Trigger] XML 데이터를 입력했을 때 해당 XML 데이터에서 자동으로 ID값 추출

CREATE TRIGGER jeju_getid ON jeju_xml INSTEAD OF INSERT

AS

INSERT INTO jeju_xml(id, xmltxt)

SELECT

t.xmltxt.value('(/항목/@ID)[1]', 'nvarchar(10)') AS id,

t.xmltxt AS xmltxt

FROM inserted t

-- [Data Input] 예시데이터 입력

INSERT INTO jeju_xml(xmltxt) VALUES(

CONVERT(xml,

N'<항목 ID="2011-5-01">

<항목명>용연(龍淵)</항목명>

<메타데이터>

<일시>2011. 10. 10. 09:30</일시>

<주소>제주특별자치도 제주시 용담동</주소>

<위치>

<경도>126.5144225</경도>

<위도>33.51488194</위도>

<고도>26.1</고도>

</위치>

</메타데이터>

<본문>

첫 방문지. <지명>용연(龍淵)</지명>은 <지명>한천(漢川)</지명>이라는 이름의 하천이 바다와 만나는 지점. 양안이 절벽인 하천 하구에 바닷물이 들어 연못처럼 보인다. 옛날에는 <지명>제주</지명>에 부임한 관리들이 달밤에 이곳에서 배를 띄우고 놀았다고.... 지금은 연인들이 즐겨 찾는 여행 코스인 듯. 다리 난간 와이어에 이른바 「사랑의 자물쇠」를 채우는 일이 빈번해지자 시에서는 별도의 자물쇠 걸이를 설치하였다.

<삽도 url="L1080486m.jpg">용연(龍淵) 안내판</삽도>

<삽도 url="L1080477m.jpg">용연(龍淵)</삽도>

<삽도 url="IMG_9073.jpg">용연(龍淵)</삽도>

<삽도 url="IMG_9077.JPG">용연(龍淵) 석벽에는 목사 심원택이라 새겨져 있다.</삽도>

<삽도 url="L1080485m.jpg">시에서 설치한 자물쇠 걸이.</삽도>

</본문>

</항목>', 1)

)

select * from jeju_xml -- 입력 확인용

-- [Meta View] 기본정보 제공을 위한 view 테이블 생성

create view jeju_meta as

select

id,

xmltxt.value( '(/항목/항목명)[1]', 'nchar(80)' ) as 항목명,

xmltxt.value( '(/항목/메타데이터/일시)[1]', 'nchar(20)' ) as 일시,

xmltxt.value( '(/항목/메타데이터/주소)[1]', 'nchar(40)' ) as 주소

from jeju_xml

select * from jeju_meta -- 추출내용 확인용

-- [GIS View] GIS 서비스를 위한 view 테이블 생성

create view jeju_gis as

select

id,

xmltxt.value( '(/항목/메타데이터/위치/경도)[1]', 'real' ) as 경도,

xmltxt.value( '(/항목/메타데이터/위치/위도)[1]', 'real' ) as 위도,

xmltxt.value( '(/항목/메타데이터/위치/고도)[1]', 'real' ) as 고도

from jeju_xml

select * from jeju_gis -- 추출내용 확인용

-- [Photo View] 갤러리 서비스를 위한 view 테이블 생성

create view jeju_photo as

SELECT id as txt_id,

photo.value('./@url', 'nchar(40)' ) as 파일,

photo.value('.', 'nchar(100)' ) as 캡션

FROM jeju_xml CROSS APPLY xmltxt.nodes('/항목/본문/삽도') AS R(photo)

select * from jeju_photo -- 추출내용 확인용

-- [Keyword Index] 인덱스 서비스를 위한 인덱스 생성

-- 지명추출

create view jeju_place as

SELECT name.value('.', 'nchar(40)' ) as 지명, id as txt_id

FROM jeju_xml CROSS APPLY xmltxt.nodes('/항목/본문/지명') AS R(name)

-- 인덱스 추출 

create view jeju_pndx as

select distinct 지명, count(*) as 빈도 from jeju_place group by 지명

select * from jeju_pndx order by 지명 -- 인덱스 정보 확인용