ASP.NET 메모, 주소, 게시판 구현을 위한 데이타베이스 생성
USE ASPNET;
--메모테이블
CREATE TABLE Memo
(
[seq] int identity(1, 1) not null,
[memo] varchar(4000) not null,
[category] varchar(4) not null,
[regDate] datetime default (getDate()) not null
);
--주소테이블
CREATE TABLE tblAddress
(
[seq] int identity(1,1) not null,
[name] varchar(20) not null,
[address] varchar(100),
[age] int not null,
[gender] char(1) not null,
[birth] datetime default(getdate()) not null
);
-- 기본형테이블
create table Board
(
[seq] [int] identity(1,1) not null,
[name] [nvarchar](20) not null,
[pwd] [varchar](20) not null,
[email] [varchar](50) null,
[subject] [nvarchar](100) not null,
[content] [nvarchar](3000) not null,
[tag] [char](1) not null,
[userip] [varchar](15) not null,
[readcount] [int] default(0) not null,
[regdate] [datetime] default(getdate()) not null
)
-- 파일업로드테이블
create table Board
(
[seq] [int] identity(1,1) not null,
[name] [nvarchar](20) not null,
[pwd] [varchar](20) not null,
[email] [varchar](50) null,
[subject] [nvarchar](100) not null,
[content] [nvarchar](3000) not null,
[tag] [char](1) not null,
[userip] [varchar](15) not null,
[readcount] [int] default(0) not null,
[regdate] [datetime] default(getdate()) not null,
[fileName] varchar(100) null, --첨부파일명
[downloadCount] int default(0) --다운로드횟수
)
select * from Board;
-- 댓글테이블
create table Board
(
[seq] [int] identity(1,1) not null,
[name] [nvarchar](20) not null,
[pwd] [varchar](20) not null,
[email] [varchar](50) null,
[subject] [nvarchar](100) not null,
[content] [nvarchar](3000) not null,
[tag] [char](1) not null,
[userip] [varchar](15) not null,
[readcount] [int] default(0) not null,
[regdate] [datetime] default(getdate()) not null,
[commentCount] int default(0) not null --댓글수
)
create table Comment
(
seq int identity(1,1) primary key, --댓글번호
bSeq int not null, --원본글번호(Board)
name nvarchar(20) not null,
pwd varchar(20) not null,
comment nvarchar(1000) not null, --댓글내용
regDate datetime default(getdate()) not null
)
-- 회원용테이블
create table Board
(
[seq] [int] identity(1,1) not null,
[id] varchar(20) references Member(id) not null,
[subject] [nvarchar](100) not null,
[content] [nvarchar](3000) not null,
[tag] [char](1) not null,
[userip] [varchar](15) not null,
[readcount] [int] default(0) not null,
[regdate] [datetime] default(getdate()) not null,
)
CREATE TABLE Member
(
[id] varchar(20) primary key,
[pwd] varchar(20) not null,
[email] varchar(50) null,
[name] varchar(20) not null
)
DROP TABLE Board;
select * from Board;
SELECT * FROM Comment;
SELECT * FROM Member;
--회원
INSERT INTO Member (id, pwd, email, name) VALUES ('hong', '111', 'hong@test.com', '홍길동');
INSERT INTO Member (id, pwd, email, name) VALUES ('sua', '123', 'sua@test.com', '홍수아');
INSERT INTO Member (id, pwd, email, name) VALUES ('park', '1234', 'park@test.com', '박하선');
--답변형게시판
create table Board
(
[seq] [int] identity(1,1) not null,
[name] nvarchar(20) not null,
[pwd] varchar(20) not null,
[email] varchar(50) null,
[subject] [nvarchar](100) not null,
[content] [nvarchar](3000) not null,
[tag] [char](1) not null,
[userip] [varchar](15) not null,
[readcount] [int] default(0) not null,
[regdate] [datetime] default(getdate()) not null,
[thread] int not null,
[depth] int not null
)
DROP TABLE Board;
SELECT *
FROM Board;
SELECT *
FROM Member
SELECT *
FROM Memo
SELECT *
FROM tblAddress;
