"2018-2 수업시간sql"의 두 판 사이의 차이
seonae's wiki
(→181213) |
|||
322번째 줄: | 322번째 줄: | ||
select * from tour2018s_person | select * from tour2018s_person | ||
</pre> | </pre> | ||
+ | |||
+ | [[#top]] |
2018년 12월 18일 (화) 21:09 판
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