--[1] 한줄 메모장 관련 테이블 설계
--Drop Table dbo.Memos
Create Table dbo.Memos
(
Num Int Identity(1, 1) Primary Key Not Null,
[Name] VarChar(25) Not Null,
Email VarChar(50) Null,
Title VarChar(150) Not Null,
PostDate SmallDateTime Default(GetDate())
)
Go
--[2] 기본형 게시판 관련 테이블 설계
-- 테이블 설계
-- Drop Table dbo.Basic
Create Table dbo.Basic
(
Num Int Identity(1, 1) Not Null Primary Key, --번호
[Name] VarChar(25) Not Null, --이름
Email VarChar(100) Null, --이메일
Title VarChar(150) Not Null, --제목
PostDate DateTime Default(GetDate()) Not Null, --작성일
PostIP VarChar(15) Not Null, --작성IP
Content Text Not Null, --내용
[Password] VarChar(20) Not Null, --비밀번호
ReadCount SmallInt Default 0, --조회수
Encoding VarChar(10) Not Null, --인코딩(HTML/Text)
HomePage VarChar(100) Null, --홈페이지
ModifyDate DateTime Null, --수정일
ModifyIP VarChar(15) Null --수정IP
)
Go
--Insert SP문
Create Proc dbo.WriteBasic
(
@Name VarChar(25),
@Email VarChar(20),
@Title VarChar(150),
@PostIP VarChar(15),
@Content Text,
@Password VarChar(20),
@Encoding VarChar(10),
@HomePage VarChar(100)
)
As
Insert Basic
(
Name, Email, Title, PostIP,
Content, Password, Encoding, HomePage
)
Values
(
@Name, @Email, @Title, @PostIP,
@Content, @Password, @Encoding, @HomePage
)
Go
--(1)입력 : BoardWrite.aspx : 게시판 글 쓰기
Insert Basic
(
Name, Email, Title,
PostDate, PostIP,
Content, Password,
Encoding, HomePage
)
Values
(
'홍길동', 'h@h.com', '홍길동입니다.',
GetDate(), '127.0.0.1',
'안녕하세요''반갑습니다.', '1234',
'Text', ''
)
--(2)출력 : BoardList.aspx : 게시판 리스트
--View 생성
--Drop View vwBoardList
Create View vwBoardList
As
Select
Num, Name, Email, Title,
PostDate, ReadCount
From Basic
Go
--View로부터 데이터 읽어오기
Select * From vwBoardList
Order By Num Desc
--(3)상세 : BoardView.aspx : 게시판 상세 보기 페이지
--Declare @Num Int
--Set @Num = 2
Begin Tran
Update Basic
Set ReadCount = ReadCount + 1
Where Num = 2--@Num
--RollBack Tran
Commit Tran
-- 조회수 증가 후 Select
Select
Num, Name, Email, Title, PostDate,
Content, ReadCount, PostIP,
ModifyDate, ModifyIP,
Encoding, HomePage
From
Basic
Where
Num = 2--@Num
--(4)수정 : BoardModify.aspx : 게시판 내용 수정 페이지
Begin Transaction
Update Basic
Set
Name = '백두산',--@Name
Email = 'b@b.com',--@Email
HomePage = '',--@HomePage
Title = '바뀌제목',--@Title
Content = '내용바뀜',--@Content
Encoding = 'HTML',--@Encoding
ModifyDate = GetDate(),
ModifyIP = '127.0.0.1'--@ModifyIP
Where
Num = 2--@Num
--RollBack Trannsaction
Commit Transaction
--(5)삭제 : BoardDelete.aspx : 게시판 해당 글 삭제
Select Password
From Basic
Where
Password = '12345'--@Password
And
Num = 2--@Num
--
Begin Tran
Delete Basic
Where Num = 2--@Num
--RollBack Tran
Commit Tran
--(6)검색 : BoardSearch.aspx : 게시판 글 검색
Select *
From Basic
Where
Name Like '%홍길동%'--@SerachQuery
Or
Title Like '%제목%'--@SerachQuery
Or
Content Like '%내용%'--@SerachQuery
--[3] 자료실 게시판 테이블 생성
-- 테이블 설계
-- Drop Table dbo.Upload
Create Table dbo.Upload
(
Num Int Identity(1, 1) Not Null Primary Key, --번호
[Name] VarChar(25) Not Null, --이름
Email VarChar(100) Null, --이메일
Title VarChar(150) Not Null, --제목
PostDate DateTime Default(GetDate()) Not Null, --작성일
PostIP VarChar(15) Not Null, --작성IP
Content Text Not Null, --내용
[Password] VarChar(20) Not Null, --비밀번호
ReadCount SmallInt Default 0, --조회수
Encoding VarChar(10) Not Null
Check
(
Encoding In('Text','HTML','Mixed')
), --인코딩(HTML/Text)
HomePage VarChar(100) Null, --홈페이지
ModifyDate DateTime Null, --수정일
ModifyIP VarChar(15) Null, --수정IP
FileName NVarChar(255) Null,--파일명
FileSize Int Null, --사이즈
DownCount Int Default(0) --다운횟수
)
Go
--(1)입력 : BoardWrite.aspx : 게시판 글 쓰기
Insert Upload
(
Name, Email, Title,
PostDate, PostIP,
Content, Password,
Encoding, HomePage,
FileName, FileSize
)
Values
(
'홍길동', 'h@h.com', '홍길동입니다.',
GetDate(), '127.0.0.1',
'안녕하세요''반갑습니다.', '1234',
'Text', '',
'test.gif', '1234'
)
===========================================
--(2)출력 : BoardList.aspx : 게시판 리스트
===========================================
--걍 SQL문
Select
Num, Name, Email,
Title, PostDate, ReadCount,
FileName, FileSize
From Upload
Order By Num Desc
===========================================
--View를 통한 SQL문
Create View vwUploadBoardList
As
Select
Num, Name, Email,
Title, PostDate, ReadCount,
FileName, FileSize
From Upload
Go
Select * From vwUploadBoardList
Order By Num Desc
===========================================
--Stored Procedure를 통한 SQL문
Create Procedure spUploadBoardList
As
Select
Num, Name, Email,
Title, PostDate, ReadCount,
FileName, FileSize
From Upload
Order By Num Desc
Go
spUploadBoardList --실행방식 3가지
Exec spUploadBoardList
Execute spUploadBoardList
===========================================
--(3)상세 : BoardView.aspx : 게시판 상세 보기 페이지
--Declare @Num Int
--Set @Num = 2
Begin Tran
Update Upload
Set ReadCount = ReadCount + 1
Where Num = 2--@Num
--RollBack Tran
Commit Tran
-- 조회수 증가 후 Select
--처음
Select
Num, Name, Email, Title, PostDate,
Content, ReadCount, PostIP,
ModifyDate, ModifyIP,
Encoding, HomePage,
FileName, FileSize, DownCount
From
Upload
Where
Num = 2--@Num
--변경
Create View vwUploadBoardView
As
Select
Num, Name, Email, Title, PostDate,
Content, ReadCount, PostIP,
ModifyDate, ModifyIP,
Encoding, HomePage,
FileName, FileSize, DownCount
From
Upload
Go
--실제 페이지(ASP.NET)
Select * From vwUploadBoardView
Where Num = 2
--(4)수정 : BoardModify.aspx : 게시판 내용 수정 페이지
Begin Transaction
Update Upload
Set
Name = '백두산',--@Name
Email = 'b@b.com',--@Email
HomePage = '',--@HomePage
Title = '바뀌제목',--@Title
Content = '내용바뀜',--@Content
Encoding = 'HTML',--@Encoding
ModifyDate = GetDate(),
ModifyIP = '127.0.0.1',--@ModifyIP
FileName = 'test.jpg',
FileSize = '1048576'
Where
Num = 2--@Num
--RollBack Trannsaction
Commit Transaction
--(5)삭제 : BoardDelete.aspx : 게시판 해당 글 삭제
Select Password
From Upload
Where
Password = '12345'--@Password
And
Num = 2--@Num
--
Begin Tran
Delete Upload
Where Num = 2--@Num
--RollBack Tran
Commit Tran
--프로그램 언어에서 이미 업로드된 파일 삭제
--(6)검색 : BoardSearch.aspx : 게시판 글 검색
Select *
From Basic
Where
Name Like '%홍길동%'--@SerachQuery
Or
Title Like '%제목%'--@SerachQuery
Or
Content Like '%내용%'--@SerachQuery
==========================================
--(7)다운 : BoardDown.aspx에서 사용
==========================================
--처음
==========================================
Begin Tran
Update Upload
Set
DownCount = DownCount + 1
Where
Num = 2
--RollBack Tran
Commit Tran
==========================================
--튜닝
==========================================
Create Proc spUploadBoardDown
@Num Int
As
Begin Tran
Update Upload
Set
DownCount = DownCount + 1
Where
Num = @Num
Select * From Upload
--RollBack Tran
Commit Tran
Go
Exec spUploadBoardDown 2
==========================================
--[4] 답변형(계층형) 게시판
-- 기본형게시판과 대부분 똑같고,
-- 테이블구조(3개필드추가)와 리스트, 답변페이지만 다름
--(1) 테이블 설계 : Reply.sql
-- Drop Table dbo.Reply
Create Table dbo.Reply
(
Num Int Identity(1, 1) Not Null Primary Key, --번호
[Name] VarChar(25) Not Null, --이름
Email VarChar(100) Null, --이메일
Title VarChar(150) Not Null, --제목
PostDate DateTime Default(GetDate()) Not Null, --작성일
PostIP VarChar(15) Not Null, --작성IP
Content Text Not Null, --내용
[Password] VarChar(20) Not Null, --비밀번호
ReadCount SmallInt Default 0, --조회수
Encoding VarChar(10) Not Null
Check
(
Encoding In('Text','HTML','Mixed')
), --인코딩(HTML/Text)
HomePage VarChar(100) Null, --홈페이지
ModifyDate DateTime Null, --수정일
ModifyIP VarChar(15) Null, --수정IP
Ref Int Default(0) Not Null, --참조(부모글)
Step Int Default(0) Not Null, --들여쓰기(Re)
RefOrder Int Default(0) Not Null--그룹내순서
)
Go
--(1)입력 : BoardWrite.aspx : 게시판 글 쓰기
Insert Reply
(
Name, Email, Title,
PostDate, PostIP,
Content, Password,
Encoding, HomePage
)
Values
(
'홍길동', 'h@h.com', '홍길동입니다.',
GetDate(), '127.0.0.1',
'안녕하세요''반갑습니다.', '1234',
'Text', ''
)
--(1) 입력 저장 프로시저
Create Proc dbo.procReplyBoardWrite
@Name VarChar(25),
@Email VarChar(50),
@Title VarChar(150),
@PostDate DateTime,
@PostIP VarChar(15),
@Content Text,
@Password VarChar(20),
@Encoding VarChar(10),
@HomePage VarChar(100)
As
Insert Reply
(
Name, Email, Title,
PostDate, PostIP,
Content, Password,
Encoding, HomePage
)
Values
(
@Name, @Email, @Title,
@PostDate, @PostIP,
@Content, @Password,
@Encoding, @HomePage
)
Go
--Exec procReplyBoardWrite
--(2)출력 : BoardList.aspx : 게시판 리스트
Select * From Reply
Order By Ref Desc, RefOrder Asc
--(2)출력에 대한 뷰(View) 정의
Create View dbo.vwReplyBoardList
As
Select * From Reply
Go
--뷰 실행
Select * From vwReplyBoardList
Order By Ref Desc, RefOrder Asc
--(2)출력에 대한 저장프로시저(Stored Procedure) 정의
Create Procedure dbo.ReplyBoardList
As
Select * From Reply
Order By Ref Desc, RefOrder Asc
Go
--저장 프로시저 실행
Execute ReplyBoardList
--(3)상세 : BoardView.aspx : 게시판 상세 보기 페이지
--Declare @Num Int
--Set @Num = 2
Begin Tran
Update Reply
Set ReadCount = ReadCount + 1
Where Num = 2--@Num
--RollBack Tran
Commit Tran
-- 조회수 증가 후 Select
Select
Num, Name, Email, Title, PostDate,
Content, ReadCount, PostIP,
ModifyDate, ModifyIP,
Encoding, HomePage
From
Reply
Where
Num = 2--@Num
--(4)수정 : BoardModify.aspx : 게시판 내용 수정 페이지
Begin Transaction
Update Reply
Set
Name = '백두산',--@Name
Email = 'b@b.com',--@Email
HomePage = '',--@HomePage
Title = '바뀌제목',--@Title
Content = '내용바뀜',--@Content
Encoding = 'HTML',--@Encoding
ModifyDate = GetDate(),
ModifyIP = '127.0.0.1'--@ModifyIP
Where
Num = 2--@Num
--RollBack Trannsaction
Commit Transaction
--(5)삭제 : BoardDelete.aspx : 게시판 해당 글 삭제
Select Password
From Reply
Where
Password = '12345'--@Password
And
Num = 2--@Num
--
Begin Tran
Delete Reply
Where Num = 2--@Num
--RollBack Tran
Commit Tran
--(5)삭제 관련 저장 프로시저
Create Proc dbo.ReplyBoardDelete
@Num Int,
@Password VarChar(20)
As
Select Password
From Reply
Where
Password = @Password
And
Num = @Num
Begin Tran
Delete Reply
Where Num = @Num
Commit Tran
Go
--(6)검색 : BoardSearch.aspx : 게시판 글 검색
Select *
From Reply
Where
Name Like '%홍길동%'--@SerachQuery
Or
Title Like '%제목%'--@SerachQuery
Or
Content Like '%내용%'--@SerachQuery
--(8) 답변 : BoardReply.aspx