SQL Server 강의실

시삽: 레드플러스 님 
게시판 이동:
 제목 : RedPlus.sql
글번호: 26
작성자: 레드플러스 (  )
작성일: 2005/05/26 오후 5:46:00
조회수: 5110
--[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
 
이전 글   다음 글 삭제 수정 답변 글쓰기 리스트

(댓글을 남기려면 로그인이 필요합니다.)

관련 아티클 리스트
  제       목 파일 작성자 작성일 조회
이전글 UDL 파일 설정법 - 레드플러스 2008-06-24 4128
  20. 웹 사이트 DB 설계 미니 프로젝트 - 레드플러스 2003-12-05 5861
현재글 RedPlus.sql - 레드플러스 2005-05-26 5110
  ShoppingDB.sql - 레드플러스 2005-05-30 4413
  ShoppingDB 데이터베이스 다이어그램 다운로드 권한이 없습니다. 레드플러스 2005-05-31 4214
  SQL Server 2005에서 반드시 알아야 할 사항(개발자) 총정리 - 레드플러스 2007-04-19 5592
  SQL Server 2005에서 반드시 알아야 할 사항(개발자) 총정리 쿼리문 - 레드플러스 2007-04-19 4756
다음글 19. 백업과 복원 - 레드플러스 2003-12-05 4684
 



손님 사용자 Anonymous (손님)
로그인 Home