090418 현재까지 쿼리문 작성된것 모음

use master
go
if DB_ID('cafe_manage') IS NOT NULL
DROP DATABASE cafe_manage
go
CREATE DATABASE cafe_manage
go
use cafe_manage
go

create table Member
(
    sn int identity(1,1) primary key,
    name nvarchar(5) not null,
    id varchar(20) not null,
    m_join_date datetime default getdate(),
    sex bit default(0),
    birthday char(10)
)

create table Category
(
    cate_id int identity(1,1) primary key,
    cate_name nvarchar(20) not null
)

create table Cafe
(
    sn int identity(1,1) primary key,
    name nvarchar(50) not null,
    make_date datetime default getdate(),
    fk_cate int foreign key references Category (cate_id)
)

create table Member_join
(
    fk_member int foreign key references Member(sn),
    fk_cafe int foreign key references Cafe(sn),
    join_date datetime default getdate(),
    visit_count int default(0),
)

create table Member_visit
(
    fk_member int foreign key references Member(sn),
    fk_cafe int foreign key references Cafe(sn),
    visit_count_day int default(0),
    visit_date_day char(10)
)

begin
insert into Category values ('지역')
insert into Category values ('동창/동문')
insert into Category values ('친목/모임')
insert into Category values ('팬카페')
insert into Category values ('게임')
insert into Category values ('만화/애니')
insert into Category values ('영화/비디오')
insert into Category values ('음악')
insert into Category values ('방송/연예')
insert into Category values ('취미')
insert into Category values ('문학/창작')
insert into Category values ('문화/예술')
insert into Category values ('인문/과학')
insert into Category values ('정치/사회')
insert into Category values ('경제/금융')
insert into Category values ('교육/외국어')
insert into Category values ('컴퓨터/인터넷')
insert into Category values ('종교/봉사')
insert into Category values ('생활/건강')
insert into Category values ('스포츠/레져')
end

begin
insert into Cafe(name, fk_cate) values ('프로방스 좋은방구하기 (원룸 오피스텔 룸메 인테리어 중고)', 1)
insert into Cafe(name, fk_cate) values ('서울, 서울 여행의 모든 것..!!', 1)
insert into Cafe(name, fk_cate) values ('러블리팡팡[연애가 필요한 싱글들의 모임]', 2)
insert into Cafe(name, fk_cate) values ('대학생, CEO 되다.',2)
insert into Cafe(name, fk_cate) values ('스펙UP데이트',2)
insert into Cafe(name, fk_cate) values ('교대피플',2)
insert into Cafe(name, fk_cate) values ('조선 해양 전문가들의 이야기',2)
insert into Cafe(name, fk_cate) values ('안녕하세요.방가워요..^^',2)
insert into Cafe(name, fk_cate) values ('한라대사회복지과',2)
insert into Cafe(name, fk_cate) values ('한국방송통신대학교 경영학과 돌탑스터디 동아리',2)
insert into Cafe(name, fk_cate) values ('복단대 커뮤니티',2)
insert into Cafe(name, fk_cate) values ('영진전문대학 ★ Y-story',2)
insert into Cafe(name, fk_cate) values ('오하이오 주립대 한인 카페',2)
insert into Cafe(name, fk_cate) values ('■ 방 . 사 ■ ',6)
insert into Cafe(name, fk_cate) values ('『초보자들의 만화 그리기 세상 ~♡』',6)
end

begin
insert into Member (name, id, sex, birthday) values ('홍길동', 'mshong', 0, '1980-02-03')
insert into Member (name, id, sex, birthday) values ('도우너', 'dunkin', 1, '1991-06-27')
insert into Member (name, id, sex, birthday) values ('김길동', 'kimkil', 0, '1980-02-03')
insert into Member (name, id, sex, birthday) values ('또오치', 'dochi', 1, '1982-08-15')
insert into Member (name, id, sex, birthday) values ('고길동', 'kokil', 1, '1989-03-14')
end

begin
insert into Member_join(fk_member, fk_cafe, join_date, visit_count) values (1,1,getdate(),56)
insert into Member_join(fk_member, fk_cafe, join_date, visit_count) values (2,2,getdate(),21)
insert into Member_join(fk_member, fk_cafe, join_date, visit_count) values (3,3,getdate(),54)
insert into Member_join(fk_member, fk_cafe, join_date, visit_count) values (4,4,getdate(),58)
insert into Member_join(fk_member, fk_cafe, join_date, visit_count) values (5,5,getdate(),21)
insert into Member_join(fk_member, fk_cafe, join_date, visit_count) values (1,5,getdate(),54)
insert into Member_join(fk_member, fk_cafe, join_date, visit_count) values (2,4,getdate(),65)
insert into Member_join(fk_member, fk_cafe, join_date, visit_count) values (3,3,getdate(),97)
insert into Member_join(fk_member, fk_cafe, join_date, visit_count) values (4,2,getdate(),16)
insert into Member_join(fk_member, fk_cafe, join_date, visit_count) values (5,1,getdate(),73)
insert into Member_join(fk_member, fk_cafe, join_date, visit_count) values (1,2,getdate(),54)
insert into Member_join(fk_member, fk_cafe, join_date, visit_count) values (2,3,getdate(),65)
insert into Member_join(fk_member, fk_cafe, join_date, visit_count) values (3,4,getdate(),87)
insert into Member_join(fk_member, fk_cafe, join_date, visit_count) values (4,5,getdate(),21)
insert into Member_join(fk_member, fk_cafe, join_date, visit_count) values (5,6,getdate(),54)
insert into Member_join(fk_member, fk_cafe, join_date, visit_count) values (1,3,getdate(),95)
insert into Member_join(fk_member, fk_cafe, join_date, visit_count) values (2,4,getdate(),21)
insert into Member_join(fk_member, fk_cafe, join_date, visit_count) values (3,5,getdate(),32)
insert into Member_join(fk_member, fk_cafe, join_date, visit_count) values (4,6,getdate(),54)
insert into Member_join(fk_member, fk_cafe, join_date, visit_count) values (5,7,getdate(),81)
end
go

create function memNum(@mID varchar(20))
returns int
as
begin
declare @memNum int
set @memNum = (select sn from member where id = @mID)
return @memNum
end
go

create function memChk(@memNum int, @cafeNum int)
returns bit
begin
 declare @returns bit
 set @returns = 0
 declare @cnt int
 set @cnt = (select count(*) from member_join where fk_member = @memNum and fk_cafe = @cafeNum)
 if(@cnt > 0)
 begin
  set @returns = 1
 end
 return @returns
end
go

create proc temp_list1
as
begin
select name from cafe order by sn
end
go

create proc temp_list2
as
begin
select id from member
end
go

create proc UpCount(@mID varchar(20), @cafeNum int)
as
begin
 declare @memNum int
 declare @cnt int
 declare @mChk bit

 set @memNum = cafe_manage.dbo.memNum(@mID)

 --1. 해당 카페의 회원인지 여부 확인
 set @mChk = cafe_manage.dbo.memChk(@memNum, @cafeNum)

 --카페에 등록된 회원일 경우에만 업데이트를 실행한다.
 if(@mChk = 1)
 begin
  update Member_join set visit_count = visit_count + 1 where fk_member = @memNum and fk_cafe = @cafeNum
 end

 --2. 오늘 접속한 적이 있는지 체크
 set @cnt = (
 select count(*) from member_visit
 where visit_date_day = convert(char(10), getdate(), 120) and fk_member = @memNum and fk_cafe = @cafeNum
 )

  --오늘 접속한 적이 있을 경우에만 업데이트를 실행하고 처음 접속일 경우에는 insert를 실행한다.
 if(@cnt = 0)
 begin
  insert into member_visit values (@memNum, @cafeNum, 1, convert(char(10), getdate(), 120))
 end
 else
 begin
  update member_visit set visit_count_day = visit_count_day + 1
  where visit_date_day = convert(char(10), getdate(), 120) and fk_member = @memNum and fk_cafe = @cafeNum
 end
end
go