.Net Project & 재태크 재무 관리

Use tempdb
Go
--[0] 테스트용 임시테이블 작성
-- Drop Table FrontBoard1

Create Table dbo.FrontBoard1
(
 ItemID Int IDENTITY(1,1), -- Primary Key 및 Not null --넌 클러스터드 인덱스
 Title VarChar(150) Null --클러스터드 인덱스
)
Go

Create Table dbo.FrontBoard2
(
 ItemID Int IDENTITY(1,1),
 Title VarChar(150) Null,
 
 
)
Go

--[1]실행 속도 비교 : 아래 2개 구문을 블록 씌우고 Ctrl+L
Select *From FrontBoard1 Where Title Like '제목%'
Select *From FrontBoard2 Where Title Like '제목%'

--[2]데이터 입력
Declare @i Int
Set @i = 1
While @i <= 100000
Begin
 Insert FrontBoard1 Values('제목' + CAST(@i As VarChar))
    Insert FrontBoard2 Values('제목' + CAST(@i As VarChar))
    Set @i = @i + 1
End
Go    

--[3] FrontBoard의 Item필드에 idxFrontBoard란 넌 클러스터 인덱스 생성
Create Index IdxFrontBoard
On FrontBoard1(ItemID)

--[4] Sp_Helpindex의 시스템 저장 프로시저를 이용해서 인덱스 정보확인
Exec sp_helpindex FrontBoard1
Go

--[5] 인덱스 지우기
Drop Index FrontBoard1.idxFrontBoard
Go

--[6] Title필드에 유니크한 클러스트 인덱스 생성
CREATE UNIQUE ClUSTERED INDEX IdxFrontBoard1
ON FrontBoard1 (Title)
Go

--[7] sp_helpindex 시스템 저장 프로시저를 이용해서 인덱스 정보 확인
Exec sp_helpindex FrontBoard1

--[8] 인덱스 지우기
Drop Index FrontBoard.idxFrontBoard --인덱스 지우기
Go

 테이블 작성
--Drop table dbo.zip
 
Create Table dbo.Zip
(
 ZipCode NVarChar(8) Not Null, --우편번호
 Si NVarChar(150) Null, --시도
 Gu NVarChar(150) Null, --구군
 Dong NVarChar(255) Null, --동면
 PostEtc NVarChar(255) Null, --번지
 
)
Go

--6가지 예시문 작성

--[1] 입력
Insert Zip Values('404-230','인천시','서구','가정동','113번지')
Insert Zip Values('404-230','광명시','서구','가정동','113번지')
Insert Zip Values('404-230','서울시','서구','광명동','113번지')
Go

--[2] 출력
Select *From Zip Order By ZipCode Asc
Go

--[3] 상세
Select *From Zip Where Dong ='가정동'
Go

Select *From Zip Where Dong ='광명동'
Go

--[4] 수정
Begin Tran
 Update Zip
 Set PostEtc = '456번지' Where ZipCode = '404-203'
 
--RollBack Tran
Commit Tran

--[5] 삭제
Begin Tran
 Delete zip
 Where 1 = 1
 --RollBack Tran
Commit Tran

--[6] 검색  
Select *From Zip
Where Dong Like '%가정%'
Go

--[7] View 뷰 생성
Create View dbo.Onlysi
As
Select Distinct Si From Zip --시 정보 중복을 제거해서 출력
Go

--출력
Select *From Onlysi
Go
----------------------------------------------------------------------
--[1] 입력
Drop Procedure dbo.AddZip

Create Procedure dbo.AddZip
 @ZipCode NVarChar(8),
 @Si NVarChar(150),
 @Gu NVarChar(150),
 @Dong NVarChar(255),
 @PostEtc NVarChar(255)
As 
 Insert Zip Values('404-230','인천시','서구','가정동','113번지')
 Insert Zip Values('404-230','광명시','서구','가정동','113번지')
 Insert Zip Values('404-230','서울시','서구','광명동','113번지')
Go

--[2] 출력
Drop Procedure dbo.GetZip

Create Procedure dbo.GetZip
As
 Select *From Zip Order By ZipCode Asc
Go

--[3] 상세
--Drop Procedure GetZipByDong
Drop Procedure dbo.GetZipByDong

Create Procedure dbo.GetZipByDong
           @Dong NVarChar(255)
As
 Select *From Zip Where Dong =@Dong
Go

--[4] 수정
Drop Proc dbo.UpdateZip

Create Proc dbo.UpdateZip
 @PostEtc NVarChar(255),
 @ZipCode NVarChar(8)
As
 Begin Tran
  Update Zip
  Set PostEtc = @PostEtc Where ZipCode = @ZipCode
 
  --RollBack Tran
 Commit Tran
Go

--[5] 삭제
Drop Proc dbo.DeleteZip

Create Proc dbo.DeleteZip
As
 Delete zip Where 1 = 1
  --RollBack Tran
 Commit Tran
Go
--[6] 검색
Drop Proc dbo.SearchZip

Create Proc dbo.SearchZip
 @SearchQuery VarChar(50)
As
 --Declare @SearchQuery VarChar(50)
 --Set @SearchQuery = '가정'
 Declare @sql VarChar(500)
 Set @sql = 'Select *From Zip Where Dong Like ''' + @SearchQuery +
                  '%''
                 ' 
 Print @sql
 Exec(@sql)
Go

--인덱스 생성 : 테이블에 딱한개(클러스터드인덱스), 넌클러스터드인덱스(254)
Create Clustered Index idxZip On Zip(Dong)
Go

 Create Table dbo.Zip
(
 ZipCode NVarChar(8) Not Null, --우편번호
 Sido NVarChar(150) Null, --시도
 GuGum NVarChar(150) Null, --구군
 Dong NVarChar(255) Null, --동면
 Bunji NVarChar(255) Null --번지
) 
select *from Zip

Select COUNT(*) From Zip 
Go
--가져온 zipCode 확인
Select *From Zip Where Dong Like '%역삼%'
Go

--[2] CTRL + L : 예상 실행 계획
Select *From Zip Where Dong Like '%역삼'
Go

                                      (예상실획계획표시)

--[3] SQL Server에서 최고의 성능 향상 : 인덱스
-- Dong 필드는 자주 검색에 사용되더라 ... 그러면 인텍스
-- 기본 : NonClustered Index : 책의 찾아보기(뒷부분)
Create Clustered Index idxZip On Zip(Dong)
Go
--Drop Index idxZip On Zip
--Go

--[4] CTRL+L : 예상 실행 계획
Select *From Zip Where Dong Like '&역삼&' --60
Go
Select *From Zip Where Dong Like '역삼&' --40
Go  
                                       (예상실획계획표시)

--[5] 인덱스 삭제
Drop Index idxZip On Zip
Go

--[6] Clustered Index로 다시 생성
Create Clustered Index idxZip On Zip(Dong)

--[7] CTRL + L : 예상 실행 계획
Select *From Zip Where Dong Like '&역삼&' --99
Go
Select *From Zip Where Dong Like '역삼&' --1
Go 
                                        (예상실획계획표시)

--Drop table dbo.AdminMemos
--select *from AdminMemos
Create Table dbo.AdminMemos
(
 Num Int Identity(1,1) Not Null Primary Key, --일련번호
 [Content] Text Not Null
)
Go
--[2] 입력 예시문 : 글쓰기, 회원가입
Insert AdminMemos Values('저장 프로시저 연습')
Go

--[3] 출력 예시문 : 리스트, 회원리스트, 상품리스트
Select *From AdminMemos Order By Num Desc
Go

--[4]상세 예시문 : 회원상세보기, 주문 상세내역, 게시물 보기
Select Num, Content From AdminMemos Where Num =1
Go

--[5]수정 예시문 : 회원정보수정, 게시물 수정
Begin Tran
 Update AdminMemos
 Set Content = '저장 프로시저 안습'
 Where Num = 1
 --RollBack Tran
Commit Tran

--[6] 삭제 예시문 : 회원탈퇴, 게시물삭제
Begin Tran
 Delete AdminMemos
 Where Num = 1
 --RollBack Tran
Commit Tran  

--[7] 검색 예시문 : 회원검색, 상품검색
Select Num, Content
From AdminMemos
Where [Content] Like '%프로시저%'
Go

--[8] 1부터 100까지 짝수 일때에는 Contentdp '안녕'을 입력,
 --홀수 일때는 '방가'  출력
Declare @i Int
Set @i = 1
While @i <= 100
Begin
 If @i % 2 = 0
 Begin 
  Insert AdminMemos Values('안녕')
 End
 Else
 Begin
  Insert Into AdminMemos Values('방가')
 End
 Set @i = @i + 1
End
Go  

--[9]View 뷰 : 전체레크도를 조회
Create View dbo.GetAdminMemos
As
 Select Num, Content From AdminMemos
Go

--View 뷰로 부터 조회
Select *From GetAdminMemos Order By Num Desc
Go

--[10] 입력 예시문 - > 프로그래밍화(매개변수화) -> 입력 저장 프로시저
Create Procedure dbo.AddAdminMemo
As
 Insert AdminMemos Values('저장 프로시저 연습')
Go

--[11] 저장 프로시저 실행 : Execute, Exec, 저장프로시저명
Excute AddAdminMemo
Go

Exec AddAdminMemo
Go

AddAdminMemo
Go

--[12] 저장 프로시저 수정
Alter Procedure dbo.AddAdminMemo
--실제는 테이블형태로 생성하였지만 현재 VarChar 사용
 @Content VarChar(8000) --Text?
As
 Insert AdminMemos Values (@Content)
Go

--[13] 매개변수가 있는 저장 프로시저 실행
Exec AddAdminMemo '안녕'
Go

Exec AddAdminMemo '방가'
Go  
--[14]출력 : 긴 SQL문을 짧은 하나의 명령어(저장프로시저명)으로 변환
Create Procedure dbo.ListAdminMemos
As
 Select *From AdminMemos Order by Num Desc
Go

--[15] 매개변수가 없는 저장 프로시저 테스트
Exec ListAdminMemos
Go

--[16] 상세 : 넘겨주는 값에 따른 결과 생성
Create Proc dbo.ViewAdminMemos
 @Num Int
As
 Select Num, Content From AdminMemos Where Num = @Num
Go

--[17] 매개변수가 있는 저장 프로시저 실행
Exec ViewAdminMemos 100
Go
Exec ViewAdminMemos 5
Go

--[18] 넘겨져 온 번호에 해당하는 내용을 넘겨져온 값으로 수정
--Drop Proc ModifyAdminMemos
Create Proc dbo.ModifyAdminMemos
 @Content VarChar(8000),
 @Num Int
As
 Begin Tran
  Update AdminMemos
  Set Content = @Content
  Where Num = @Num
  --RollBack Tran
 Commit Tran
Go

--[19] 위 저장 프로시저 실행 예시
Exec ModifyAdminMemos '반갑습니다', 100
Go
Exec ModifyAdminMemos 100
Go    

--[20] 삭제 저장 프로시저 생성
Create Proc dbo.DeleteAdminMemos
 @Num Int
As
 Begin Tran
  Delete AdminMemos
  Where Num = @Num
  --RollBack Tran
 Commit Tran
 --Select *From AdminMemos Where Num = @Num
Go

--[21] 실행 : 100번 레코드 삭제
DeleteAdminMemos 100
Go

--[22] 검색 저장 프로시저 : 저장 프로시저보다는 동적 쿼리문이라고 부름.
Create Proc dbo.SearchAdminMemos
 @Content VarChar(50)
As
 Declare @strSql VarChar(255)
 Set @strSql = '
  Select Num, Content
  From AdminMemos
  Where [Content] Like ''%' + @Content + '%'' '
 Exec(@strSql)  
 Go
 
 --[23]동적 쿼리문으로 검색
 SearchAdminMemos '안녕'
 Go
 SearchAdminMemos '방가'
 Go