08.ASP.NET - 자료실 게시판(1) : DB설계

------------------------------

--자료실게시판응용프로그램

------------------------------

 

--[0] 자료실게시판(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 Int Default 0,                      --조회수

       Encoding VarChar(10) Not Null,                --인코딩(HTML/Text)

       Homepage VarChar(100) Null,                   --홈페이지

       ModifyDate DateTime Null,                     --수정일

       ModifyIP VarChar(15) Null,                    --수정IP

       ---

       FileName VarChar(255) Null,                   --파일명

       FileSize Int Default(0),                      --파일크기

       DownCount Int Default(0)                      --다운수

)

Go

 

-- 기본SQL문예시문6가지작성

--입력

Insert Into Upload(Name, Email, Title, PostDate, PostIP,

                   Content, Password, Encoding)

      

       Values('홍길동', 'h@com', '아바', GetDate(),

               '127.0.0.1', '원샷원샷', '123', 'Text')

              

Insert Into Upload(Name, Email, Title, PostDate, PostIP,

                   Content, Password, Encoding)

                  

       Values('백두산', 'b@.com', '스타크래프트', GetDate(),

              '127.0.0.1', '테란전', '123', 'Text')

Go

 

 --출력

Select * From Upload Order By Num Desc

Go

 

--상세

Select * From Upload Where Num = 1

Go

 

--수정

Update Upload Set Name = '홍길동', Email = 'h@com',

                  Title = '아바', ModifyDate = GetDate(),

                     ModifyIP = '127.0.0.1', Content = '원샷원킬',

                     Encoding = 'Text'

Where Num = 1

Go

 

--삭제

Delete From Upload Where Num = 1

Go

 

--검색

Select * From Upload Where Title Like '원%'

Go