GSI

[MS-SQL] A 테이블에 B의 내용 중에서 일부 컬럼만 추가 하는 쿼리

DB&XML 2009. 4. 15. 14:39 |

A의 테이블에 idx라는 자동증가 컬럼이 있다고 한다면
모든 컬럼 내용을 다 복사 하지는 못한다.
그래서 아래와 같이 추가할 컬럼을 지정한 후에 복사를 해야 한다.

INSERT INTO userList
           ([uid]
           ,[passwd]
           ,[name]
           ,[phone]
           ,[email]
           ,[jumin1]
           ,[jumin2]
           ,[zipcode1]
           ,[zipcode2]
           ,[address1]
           ,[address2]
           ,[regdate]
           ,[level]
           ,[exp]
           ,[cash]
           ,[gem]
           ,[IsBlocked]
           ,[IsGameUser]
           ,[gameScore]
           ,[gameCash]
           ,[grade])
select a.uid, a.passwd, a.name, a.phone, a.email,
  a.jumin1, a.jumin2, a.zipcode1, a.zipcode2, a.address1, a.address2,
  a.regdate, a.level, a.exp, a.cash, a.gem, a.IsBlocked, a.IsGameUser,
  a.gameScore, a.gameCash, a.grade
from leave_userList a
where idx=3
Posted by gsi
:

테이블간 관계 추가해서 자동 삭제 처리 하기

DB&XML 2009. 3. 16. 04:35 |


두개의 테이블이 있다.

GalleryBoard2 에는 게시물이 저장된다.
그리고 GalleryBoard2File 에는 게시물에서 첨부이미지가 저장된다.
idx - imgIDX 를 통해서 해당 게시물의 이미지 파일을 연결한다.

이때 GalleryBoard2의 해당 게시물을 삭제 하게 될때
GalleryBoard2File의 내용도 같이 삭제를 해야 한다.
하지만, 두개의 테이블을 따로따로 삭제할 경우 좀 번거롭고 문제가 생기기 마련이다.
그래서 관계를 위의 이미지와 같이 형성하고 GalleryBoard2를 삭제 하면 GalleryBoard2File의 내용도 같이 포함된다.

간단하게 설명하면
1. MSSql 2005에서 데이터베이스 다이어그램을 새로 생성한다.
2. GalleryBoard2, BalleryBoard2File을 두개 추가한다.
3. 두개의 테이블에서 GalleryBoard2의 idx 와 GalleryBoard2File의 imgIDX를 드래그해서 연결한다.
4. 연결후에 속성을 아래와 같이 변경한다.
  - 기본으로 설정된 속성은 그대로 둔다.
  - INSERT 및 UPDATE 사양 에서 삭제 규칙 을 "계단식 배열"로 정한다.
    (사실 여기에 몇개 설정값이 있지만, 잘 모른다. 혹시 아신다면 코멘트 부탁해요)
5. 이게 정상적으로 되었다면 삭제처리를 해보기 바란다. 자동으로 삭제가 되니 코딩도 편해진다.
Posted by gsi
:

한국 지역별 소트 하기

DB&XML 2009. 3. 7. 06:26 |

작업 하다가 대리점별 지역 주소를 정렬하는 부분에 사용한 쿼리
지역 코드가 여러개의 혼합으로 되어 있어서 그걸로 안되고 해서
주소의 내용 일부를 order by 해서 사용하도록 했다.


SELECT * FROM dbo.Agency order by case left(ConAddr,2)
 when '서울' then 1
 when '경기' then 2
 when '인천' then 3
 when '강원' then 4
 when '대전' then 5
 when '충남' then 6
 when '충분' then 7
 when '부산' then 8
 when '울산' then 9
 when '경남' then 10
 when '대구' then 11
 when '경북' then 12
 when '광주' then 13
 when '전남' then 14
 when '전북' then 15
 when '제주' then 16
 end
Posted by gsi
:

10초에 한번씩 입력되는 데이터를 5분간 통계를 내는 저장 프로시저

DB&XML 2009. 2. 19. 17:02 |

-- 최초 입력 데이터
idx         hostName                                           value       regDate                 flag
----------- -------------------------------------------------- ----------- ----------------------- ----
15934       P6901756001C                                       4           2009-02-19 16:25:36.150 0
15935       P6901756001C                                       5           2009-02-19 16:25:46.150 0
15936       P6901756001C                                       1           2009-02-19 16:25:56.167 0
15937       P6901756001C                                       3           2009-02-19 16:26:06.150 0
15938       P6901756001C                                       3           2009-02-19 16:26:16.150 0
15939       P6901756001C                                       3           2009-02-19 16:26:26.150 0
15940       P6901756001C                                       2           2009-02-19 16:26:36.150 0
15941       P6901756001C                                       2           2009-02-19 16:26:46.150 0
15942       P6901756001C                                       1           2009-02-19 16:26:56.150 0
15943       P6901756001C                                       2           2009-02-19 16:27:06.150 0
15944       P6901756001C                                       1           2009-02-19 16:27:16.150 0

-- 변경후 데이터
idx         hostName                                           value       valueSum    valueCount  regDate                 flag
----------- -------------------------------------------------- ----------- ----------- ----------- ----------------------- ----
5           P6901756001C                                       0           50          20          2009-02-19 15:45:00.000 0
6           P6901756001C                                       0           99          30          2009-02-19 15:50:00.000 0
7           P6901756001C                                       0           97          30          2009-02-19 15:55:00.000 0
8           P6901756001C                                       0           287         30          2009-02-19 16:00:00.000 0
9           P6901756001C                                       0           218         30          2009-02-19 16:05:00.000 0
10          P6901756001C                                       0           362         30          2009-02-19 16:10:00.000 0
11          P6901756001C                                       0           131         30          2009-02-19 16:15:00.000 0
12          P6901756001C                                       0           163         30          2009-02-19 16:20:00.000 0
13          P6901756001C                                       0           23          3           2009-02-19 16:25:00.000 0

-- 구성 내용
10초에 한번씩 입력 되는 해당 데이터의 내용을 5분 단위로 끊어서 새로운 테이블로 통계를 낸다.
값이 일정하지도 않을수 있고, 값의 주기가 조금씩 틀릴수도 있다.
우선 테스트를 통해서 더 보완 해야 할듯 하다. (커서를 몰라서 이렇게 걍 작성 했다.)

-- 배울점
1. 시간 값의 구간을 5분 단위로 구분

Posted by gsi
:

문자열을 DATETIME로 변경해 주는 함수

DB&XML 2009. 2. 19. 16:58 |


USE [GPMS]
GO
/****** 개체:  UserDefinedFunction [dbo].[CHANGEDATETIME]    스크립트 날짜: 02/19/2009 16:57:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 날짜변환하는부분을함수로작성
CREATE  FUNCTION [dbo].[CHANGEDATETIME] (
       @sdate varchar(30)
       )RETURNS DATETIME
AS
BEGIN
       DECLARE @RETURNVALUE DATETIME
       BEGIN
             SELECT  @RETURNVALUE=CASE WHEN  CHARINDEX('오후',@sdate)>0
                    THEN DATEADD(HH,12,CONVERT (DATETIME, REPLACE(@sdate,'오후','')))
                    ELSE CONVERT (DATETIME,REPLACE(@sdate,'오전','') )
                    END
       END
       RETURN(@RETURNVALUE)
END

-- 결과
문자열을 시간 값으로 변경해 준다.
하지만 문자열이 시간값과 같은 포멧으로 구성되어 있어야 한다.
ex) 2009-02-19 14:25:00

Posted by gsi
:

DATETIME 값을 5분 단위로 잘라서 문자열로 반환하는 함수#2

DB&XML 2009. 2. 19. 16:57 |


USE [GPMS]
GO
/****** 개체:  UserDefinedFunction [dbo].[GETSTIMEPER5MINTODATETIME]    스크립트 날짜: 02/19/2009 16:55:38 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

/*
 GETDATETIMEPERMIN : 2006-09-19 BY J.Y. PARK,
 DESC: 입력받은 DATETIME의 초,밀리세컨드를 0으로 수정하고 5분단위 시간으로 반환
 */
CREATE   FUNCTION [dbo].[GETSTIMEPER5MINTODATETIME]
 ( @IDATETYPE DATETIME)
RETURNS VARCHAR(20)
AS
 BEGIN
 DECLARE @SNEWTIME VARCHAR(20)
 DECLARE @SMIN VARCHAR(5)

 SET @SNEWTIME = DATENAME(YY,@IDATETYPE)

 SET  @SNEWTIME =  @SNEWTIME + '-' + DATENAME(MM,@IDATETYPE)

 IF DATEPART(DD,@IDATETYPE) < 10
  SET  @SNEWTIME =  @SNEWTIME + '0'
 SET  @SNEWTIME =  @SNEWTIME + '-' + DATENAME(DD,@IDATETYPE) + ' '

 IF DATEPART(HH,@IDATETYPE) < 10
  SET  @SNEWTIME =  @SNEWTIME + '0'
 SET  @SNEWTIME =  @SNEWTIME + DATENAME(HH,@IDATETYPE)

 IF DATEPART(MI,@IDATETYPE) < 10
  SET  @SMIN =  '0' + DATENAME(MI,@IDATETYPE)
 ELSE
  SET @SMIN = DATENAME(MI,@IDATETYPE)
 IF (DATEPART(MI,@IDATETYPE) % 10) >= 5
  SET @SNEWTIME = @SNEWTIME + ':' + LEFT(@SMIN,1) + '5'
 ELSE
  SET @SNEWTIME = @SNEWTIME + ':' + LEFT(@SMIN,1) + '0'

 RETURN @SNEWTIME
 END

-- 결과
GETSTIMEPER5MIN 와 비슷하지만 이건 이 문자열을 DateTime로 변환이 가능한
문자열 형태로 반환한다.

Posted by gsi
:

DATETIME 값을 5분 단위로 잘라서 문자열로 반환하는 함수

DB&XML 2009. 2. 19. 16:55 |


USE [GPMS]
GO
/****** 개체:  UserDefinedFunction [dbo].[GETSTIMEPER5MIN]    스크립트 날짜: 02/19/2009 16:54:01 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

/*
 GETDATETIMEPERMIN : 2006-09-19 BY J.Y. PARK,
 DESC: 입력받은 DATETIME의 초,밀리세컨드를 0으로 수정하고 5분단위 시간으로 반환
 */
CREATE   FUNCTION [dbo].[GETSTIMEPER5MIN]
 ( @IDATETYPE DATETIME)
RETURNS VARCHAR(20)
AS
 BEGIN
 DECLARE @SNEWTIME VARCHAR(20)
 DECLARE @SMIN VARCHAR(5)

 SET @SNEWTIME = DATENAME(YY,@IDATETYPE)

 SET  @SNEWTIME =  @SNEWTIME + DATENAME(MM,@IDATETYPE)

 IF DATEPART(DD,@IDATETYPE) < 10
  SET  @SNEWTIME =  @SNEWTIME + '0'
 SET  @SNEWTIME =  @SNEWTIME + DATENAME(DD,@IDATETYPE)

 IF DATEPART(HH,@IDATETYPE) < 10
  SET  @SNEWTIME =  @SNEWTIME + '0'
 SET  @SNEWTIME =  @SNEWTIME + DATENAME(HH,@IDATETYPE)

 IF DATEPART(MI,@IDATETYPE) < 10
  SET  @SMIN =  '0' + DATENAME(MI,@IDATETYPE)
 ELSE
  SET @SMIN = DATENAME(MI,@IDATETYPE)
 IF (DATEPART(MI,@IDATETYPE) % 10) >= 5
  SET @SNEWTIME = @SNEWTIME + LEFT(@SMIN,1) + '5'
 ELSE
  SET @SNEWTIME = @SNEWTIME  +LEFT(@SMIN,1) + '0'

 RETURN @SNEWTIME
 END

 

-- 결과는 아래와 같이 나옵니다.
2009-02-19 14:23:41 -> 20090219142340

 


 

Posted by gsi
:

[MS-SQL] DateTime에서 시간, 분 값을 String로 변환

DB&XML 2009. 2. 12. 15:23 |


select top 100 value, CONVERT(VARCHAR, datepart(hh,regDate))+':'+CONVERT(VARCHAR, datepart(mi,regDate)) as tick
from TBL_REP_CPU_INPUT order by regDate desc

-- 출력결과 --
value       tick
----------- -------------------------------------------------------------
3           14:49
2           14:49
4           14:49

Tip : 8시간 후의 날짜와 시간을 10분 단위로 반환

Posted by gsi
:

Asp 쇼핑몰 구축 프로젝트 모듈 기반 쇼핑몰 개발 실무 - Shop DB 테이블 내용

DB&XML 2008. 12. 19. 10:20 |

<설명>
1. ASP 쇼핑몰 구축 프로젝트 의 DB 스크립트 입니다.
2. www.hanb.co.kr/exam/1390 에 소스 자료가 있습니다.
3. 책 제목은 "Asp 쇼핑몰 구축 프로젝트 모듈 기반 쇼핑몰 개발 실무"

 Table
 Member

 Product

 Category

 Cart

 Btable

Porder
Posted by gsi
:

MS SQL : 샘플 테이블 (학생 테이블 + 동아리 테이블 + 학생동아리 테이블)

DB&XML 2008. 11. 15. 17:17 |

조인 테이블 테스트시 필요한 쿼리문
학생, 동아리 테이블은 기본으로 생성됨
학생동아리 테이블은 FK로 구성되어짐
-------------------------------------------------------------------------------------------
use sqlDB
create table stdTbl
(
 stdName nvarchar(10) not null primary key,
 addr nchar(4) not null
)
go

create table clubTbl
(
 clubName nvarchar(10) not null primary key,
 roomNo nchar(4) not null
)
go

create table stdclubTbl
(
 num int identity not null primary key,
 stdName nvarchar(10) not null
  foreign key references stdTbl(stdName),
 clubName nvarchar(10) not null
  foreign key references clubTbl(clubName)
)
go

insert into stdTbl values('박지성', '서울')
insert into stdTbl values('박주영', '경기')
insert into stdTbl values('조재진', '충북')
insert into stdTbl values('이천수', '인천')
insert into stdTbl values('안정환', '강원')
go
insert into clubTbl values('수영', '101호')
insert into clubTbl values('바둑', '102호')
insert into clubTbl values('축구', '103호')
insert into clubTbl values('봉사', '104호')
go
insert into stdclubTbl values('박지성', '바둑')
insert into stdclubTbl values('박지성', '축구')
insert into stdclubTbl values('조재진', '축구')
insert into stdclubTbl values('이천수', '축구')
insert into stdclubTbl values('이천수', '봉사')
insert into stdclubTbl values('안정환', '봉사')
go

Posted by gsi
:

Adventureworks DB - Sample Files

DB&XML 2008. 11. 14. 01:11 |


http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004


AdventureWorksLT.msi 를 설치 하시고 DB를 연결하시면 됩니다.
Posted by gsi
:

MS-SQL 2005 테이블 컬럼 점검

DB&XML 2008. 11. 12. 10:17 |

--확인방법
USE oms25_20070801
GO
-- 사전에 sysindexes에 있는 정보를 최신상태로 업데이트
DBCC UPDATEUSAGE(0)
GO

-- 결과 내용 (몇가지 최적화가 수행된다면, 아래와 같이 나온다.)
DBCC UPDATEUSAGE: 테이블 'TBL_BIZOBJ_STATUS'의 업데이트된 수(인덱스 'IX_TBL_BIZOBJ_STATUS_00', 파티션 1):
        DATA 페이지 (In-row Data): (12328)에서 (12377)(으)로 변경되었습니다.
        USED 페이지 (In-row Data): 12427개 페이지에서 12476개 페이지로 변경되었습니다.
        RSVD 페이지 (In-row Data): 12432개 페이지에서 12481개 페이지로 변경되었습니다.
DBCC UPDATEUSAGE: 테이블 'TBL_BIZOBJ_STATUS'의 업데이트된 수(인덱스 'IX_TBL_BIZOBJ_STATUS_01', 파티션 1):
        RSVD 페이지 (In-row Data): 1320개 페이지에서 1321개 페이지로 변경되었습니다.
...
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

-- 결과 내용 (아무 이상이 없다면 아래와 같다.)
DBCC 실행이 완료되었습니다.
Posted by gsi
:

MS-SQL 2005 파일 사이즈 점검

DB&XML 2008. 11. 12. 10:09 |

DB이름을 기준으로 아래의 쿼리를 실행 합니다.

-- 확인 방법
USE [DB 이름]
GO
DBCC SHOWFILESTATS
GO

--실행 결과


여기 나온 정보를 사용해서 아래의 쿼리를 만들어서 다시 실행합니다.

-- 확인 방법
select 513*8*8/1024. as Reserved, 400*8*8/1024. as Used, (400*8*8/1024.)/(513*8*8/1024.)*100.

-- 실행 결과

현재 파일 사이즈를 보면, 32MB가 총 할당된 데이터 파일 사이즈이고, 이 중 25MB가 실제 사용되고 있는 것을 확인할 수 있다.
할당된 영역 중 77% 정도 사용되고 있는 상태라고 보면 된다.

파일의 크기가 자동증가라고 본다면 증가 폭을 늘리던지 아니면 파일의 크기를 미리 늘려 놓는 것이 좋다.
Posted by gsi
:

MS-SQL 2005 - Top을 사용한 페이지 읽어 오기

DB&XML 2008. 11. 10. 00:45 |

GridView 컨트롤이 존재 하지만 여러개의 데이터를 읽어 올때
데이터가 많아 지면 속도가 느려 지기 마련인데요.
top을 이용하면 해당 개수 만큼 값을 가져 올수 있어서 편한듯 합니다.

.net 3.5 부터는 page를 컨트롤 하는 컨트롤이 존재 하기 때문에
이 것과 연동하면 더 좋은 효과를 기대할 수 있다고 보여 집니다.

기본 코드는 아래와 같습니다.

select top * 5 from test
where key_col not in
(
select top 0 key_col
from test
order by key_col desc
)
order by key_col desc

- top * 5 라는 것은 5개의 데이터를 가져 오도록 하는 구문입니다.
- 0 이곳은 0페이지 부터.. 즉 (현재 페이지 * 보여줄 항목개수, 0 * 5)
  가 되는 것입니다.

위의 쿼리를 실행하면 5개의 데이터를 가져옵니다.
Posted by gsi
:

MS-SQL 2005 - 쿼리 결과를 상세하게 표시(Data 페이지, 등등..)

DB&XML 2008. 11. 9. 00:27 |

악성 쿼리를 찾는데 도움이 될듯 합니다.
현재 쿼리 문이 io를 어떻게 사용하고 있는지를 알려 줍니다.

select * from tb_double

이 쿼리를 날리게 되면 메시지 창에 (1000개 행 적용됨)
이라는 구문을 보실수 있습니다.

하지만 더 디테일 하게 보게 되면
select 을 하기 위해서 전체 몇 페이지를 읽고 있으며,
어떤 부하가 걸리고 있는지를 볼 수 있습니다.

set statistics io on
select * from tb_double

 



이상..

Posted by gsi
:

티스토리툴바