2018-2 수업시간sql

181004

use db;

select * from sys.tables

select * from ebhPhoto$
create table ebhPhoto01(
id nvarchar(16) not null,
province nvarchar(8) null,
site nvarchar(40) null,
domain nvarchar(40) null,
domainType nvarchar(4) null,
photoUrl nvarchar(256) null,
iconUrl nvarchar(256) null,
caption nvarchar(256) null,
wikiFile nvarchar(80) null,
primary key(id)
)

select * from ebhPhoto$ where domainType = 3
--숫자는 인용부호가 없어도 됨. 

insert into ebhPhoto01
select * from ebhPhoto$ where domainType = 3
-- insert문을 이용하여 테이블 데이터 넣기
--각 개별칼럼별로 가져와서 넣으려면 into에도 column name을 지정.
-- 쓰리 콤보 , insert, update, delete
select * from ebhPhoto01

update ebhPhoto01 
set domainType = '3' where domainType is null

--보통은 입력 순서대로 들어가지만 id를 pk로 지정했기 때문에 pk기준으로 정렬됨

181018

use db
select * into institute from common.dbo.institute
select * into program from common.dbo.program
select * into person from common.dbo.person
select * into journal from common.dbo.journal
select * into library from common.dbo.library
select * into affiliation from common.dbo.affiliation

select * from institute order by zone
select institute_name, type, state_name from institute 
where type != 'EDU' order by type, state_name, institute_name

select institute_name, state_name, latitude from institute where latitude < 0

select * from institute where hasGis = 1

select * from institute where institute_name like 'University%'
select * from institute where institute_name like '%University'

select * from institute where state_name ='호주' or state_name = '뉴질랜드'
select * from institute where (state_name ='호주' or state_name = '뉴질랜드') and type = 'EDU'

select * from institute where state_name in ('중국', '일본', '베트남', '인도네시아') order by state

select * from institute where state_name = '미국' order by institute_name desc

select top 10 * from institute

select zone, state_name, count(*) from institute 
group by zone, state_name order by zone, state_name

select distinct state_name from institute
select distinct type from institute
select zone, count(zone) from institute group by zone

181025

use db;

select * from affiliation --person_name, institute_id, program_id
select * from institute 
select * from journal --institute_id
select * from library --institute_id, program_id
select * from person 
select * from program where program_name is null--institute_id
select * from program where institute_id is null--institute_id

--테이블을 한꺼번에 가져오면 생길수 있는 모든 경우의 수를 생성.

--한국학프로그램이 있는 곳의 프로그램, 나라, 대학, 학과, 프로그램 시작날짜
select program_name, state_name, institute_name, department, establishment 
from institute join program on institute.institute_id = program.institute_id

select program_name, state_name, institute_name, department, establishment 
from institute, program where institute.institute_id = program.institute_id
--2가지 방식으로 쓸 수 있지만 join문을 선호하는 것은 inner이외의 join도 쓸 수 있기 때문.

--해외한국학 대학중에 저널을 간행하는 곳이나 도서관이 있는 곳은 규모가 큼. 
--해외에 있는 한국학 도서관이나 저널을 보자
select library.institute_id, journal_name, library_name from library 
full outer join journal on library.institute_id = journal.institute_id
--dbo 말고 sys가 있음
-- full outer, left outer, right outer
--도서관이랑 저널이 두개 있는 곳과 도서관만 있는 곳을 전부 보겠음. 
select library.institute_id, journal_name, library_name from library 
right outer join journal on library.institute_id = journal.institute_id
--갯수가 늘어났다는 것은 대학에 2개이상이 있을 수 있다는 것.

--도서관은 있는데 저널은 없는 곳.
select library.institute_id, journal_name, library_name from library 
left outer join journal on library.institute_id = journal.institute_id
where journal_name is null
--저널은 있는데 도서관은 없는 곳.
select journal.institute_id, journal_name, library_name from library 
right outer join journal on library.institute_id = journal.institute_id
where library_name is null
--데이터가 없는 경우에 outer를 많이 씀. 조사한 것중에 빠진게 있는지.

select state_name, institute_name
from institute left outer join program on institute.institute_id = program.institute_id
where program.institute_id is null
--where program_name is null
--한쪽의 id값을 기준으로 null값을 찾는것이 좀더 정확함. 

181108

use db

select * from sys.tables
select * from sys.views
--sys 컴퓨터가 만드는 테이블

select * from dbo.ebhPhoto

--캡션을 다 채워넣는게 미션
select id, domain, caption, wikiFile from ebhPhoto
where caption is null

--delete 하려면 where 조건 넣어야 함
--db는 백업데이터를 항상 만들어두는 것이 좋음.

--1) 백업데이터 부터 만들기
select * into ebhPhoto2 from ebhPhoto

--2) 원본 작업할 것만 빼고 제외
select * from ebhPhoto where domain != '남원_실상사'

--3) 남기고 지우기
delete ebhPhoto where domain != '남원_실상사'

--4) 확인
select * from ebhPhoto

--지우고 다시 복구 중... 껍데기도 지우느냐, 다시 삽입하냐
--1) 껍데기 살리고 데이터 가져오기
insert into ebhPhoto
select * from ebhPhoto2

select * from ebhPhoto

--2) 내것만 추출해서 다른 db로
select * into myPhoto from ebhPhoto where domain = '남원_실상사'

select id, domain, caption, wikiFile from myPhoto where caption is null

--캡션 바꾸는 방법
select * from myPhoto

--insert into의 위치. 맨 앞이면 기존에 추가하는거. select 뒤에 넣으면 새로 테이블 만들기.
update myPhoto
set caption = '남원 실상사 증각 스틸' where id = 'EBJL145'

181122

use VH2018;

/*자기가 직접 데이터 업로드*/

--자기가 만든 데이터 확인
select * from 자기디비.dbo.myPhoto2
select * from 자기디비.dbo.myPhoto

--교수님께서 새로 생성하신 통합 테이블
select * from ebhPhotoNew

--새 테이블에 내 데이터 넣기(이미 다 들어감)
INSERT INTO ebhPhotoNew
select * from 자기디비.dbo.myPhoto2

--데이터가 제대로 들어갔는지 확인하기. (빠진 목록 확인하기)
select * from ebhPhoto left join ebhPhotoNew 
on ebhPhoto.id = ebhPhotoNew.id
where ebhPhotoNew.id is null

--캡션만 추가하는 방법(실행하지 마세요)
insert into ebhPhotoNew
select ebhPhoto.id, ebhPhoto.province, ebhPhoto.site, ebhPhoto.domain,
ebhPhoto.domainType, ebhPhoto.photoUrl, ebhPhoto.iconUrl, 자기디비.dbo.myPhoto2.caption,
ebhPhoto.wikiFile from ebhPhoto join 자기디비.dbo.myPhoto2 on ebhPhoto.id = 자기디비.dbo.myPhoto2.id
--다른 데이터베이스에서 update하는 것은 프로시저를 짜야함
--가장 간단한 방법은 새로 파일을 만들어서 insert하면 됨.
--2개의 테이블에서 가져와야 하므로, join해서 가져올 것.

--중복 데이터 제거하기(중복된 경우에만 지우고 다시 insert)
--delete ebhPhotoNew 
--where domain in ('영주_부석사', '양평_용문사', '영월_법흥사', '영주_비로사')

181129

use db;

--
declare @term nvarchar(30)
set @term=N'견마기(肩亇只)';
select @term

--
SELECT id, xmltxt FROM tour2018s

--xmltxt에서 대표명칭만 뽑아서 보기
--query 메쏘드는 xpath를 인수로 받음.
SELECT id, xmltxt.query('/항목/명칭/대표명칭') FROM tour2018s
SELECT id, xmltxt.query('/항목/명칭') FROM tour2018s

--인명 태그를 모두 뽑기 but query를 쓰면 태그의 위치를 계층적으로 지정해줘야함.
SELECT id, xmltxt.query('/항목/데이터/해설/문단/건물명') FROM tour2018s
--계층 상관없이 모든 위치의 요소 뽑기
SELECT id, xmltxt.query('//인명') FROM tour2018s
SELECT id, xmltxt.query('//지명') FROM tour2018s

--지명 색인 만들기
CREATE VIEW tour2018s_place AS
  SELECT id, node.value('.', 'nchar(80)' ) AS 지명
  FROM  tour2018s CROSS APPLY xmltxt.nodes('//지명') AS R(node) 

SELECT * FROM tour2018s_place

SELECT 지명, count(*) as 빈도
  FROM  tour2018s_place
  GROUP BY 지명

181206

use db;

select * from tour2018s

--우리가 하고자 하는것은 2가지
select xmltxt.query('/항목/명칭/대표명칭') from tour2018s
select xmltxt.query('/항목/명칭/한자명칭') from tour2018s
select xmltxt.query('/항목/명칭/영문명칭') from tour2018s

select xmltxt.query('/항목/메타데이터/기본정보/유물형태') from tour2018s

--이런 방식이 불편하므로 별도의 메타데이터를 만드는 것이 필요.
select xmltxt.value('(/항목/명칭/대표명칭)[1]', 'nchar(80)') from tour2018s
--.query()는 XML요소를 반환. .value()는 인수로 xpath와 datatype이 필요. 게다가 순서 지정도 필요.

--query로 모든 요소를 검색할 수 있지만, xpath를 쓰는게 불편.
--따라서 필요한 데이터를 가져와서 별도의 테이블을 만들어둠.
--별도의 테이블은 항상 연동되어야 함. 따라서 view로 만듦

SELECT
	xmltxt.query('/항목/명칭/대표명칭'),
	xmltxt.query('/항목/명칭/한자명칭'),
	xmltxt.query('/항목/명칭/영문명칭'),
	xmltxt.query('/항목/메타데이터/기본정보/유물형태'),
	xmltxt.query('/항목/메타데이터/기본정보/소재지'),
	xmltxt.query('/항목/메타데이터/기본정보/위도'),
	xmltxt.query('/항목/메타데이터/기본정보/경도') 
FROM tour2018s

CREATE VIEW tour2018s_meta AS
SELECT id, 
	xmltxt.value('(/항목/명칭/대표명칭)[1]', 'nchar(40)') AS 대표명칭,
	xmltxt.value('(/항목/명칭/한자명칭)[1]', 'nchar(40)') AS 한자명칭,
	xmltxt.value('(/항목/명칭/영문명칭)[1]', 'nchar(80)') AS 영문명칭,
	xmltxt.value('(/항목/메타데이터/기본정보/유물형태)[1]', 'nchar(40)') AS 유물형태,
	xmltxt.value('(/항목/메타데이터/기본정보/소재지)[1]', 'nchar(80)') AS 소재지,
	xmltxt.value('(/항목/메타데이터/기본정보/위도)[1]', 'nchar(20)') AS 위도, 
	xmltxt.value('(/항목/메타데이터/기본정보/경도)[1]', 'nchar(20)') AS 경도 
FROM tour2018s

select * from tour2018s_meta where 소재지 like '%군산시%'
select * from tour2018s_meta where 위도 > '36'

select tour2018s.id, 대표명칭, xmltxt from tour2018s_meta join tour2018s 
on tour2018s_meta.id=tour2018s.id 
where 대표명칭 = '곰소염전'
--id를 별도의 칼럼으로 두는 건 join을 위해서임.
--xml 테이블을 만들때 id 칼럼을 별도로 만들어 둘 것. 

select * from tour2018s 
where xmltxt.value('(/항목/명칭/대표명칭)[1]', 'nchar(40)') = '곰소염전'

--id, 대표명칭, 주소, 위도, 경도
select id, 대표명칭, 소재지, 위도, 경도 from tour2018s_meta

181213

use s_seonae;

select * from tour2018s_meta

select * from tour2018s

select xmltxt.query('//인명') from tour2018s
--재귀적 내림 연산자 // recursive descendant operator 
--모든 계층적 태그를 다 살펴본다는 것. 
--xmltxt.nodes('//인명')와 의미적으로 같음.
--but query는 시각적으로 보여주고(결과테이블) nodes는 보여주지 않음.(메모리에만 저장)

create view tour2018s_person as
	select id, node.value('.', 'nchar(80)') as 인명
	from tour2018s cross apply xmltxt.nodes('//인명') as R(node)
--cross apply : 1건의 레코드를 안에 나오는 갯수만큼 개별 레코드로 따로 저장시키는 것.
--일종의 join과 비슷. 
--node.value에서 '.'는 그 자체를 의미.(this)
--R(node) 테이블(컬럼)

select 인명, count(*) as 빈도
from tour2018s_person group by 인명 order by 빈도 desc;

select * from tour2018s_person