BBS > plan

account book

작성자 : 이창우 (x1wins) | 등록일 : 2011-05-12 | 목록
첨부 파일이 없습니다.
첨부 이미지가 없습니다.
create table cw_account_book_list
(
num integer not null auto_increment, 
reg_date timestamp not null default current_timestamp,
ip varchar(50) not null,									/*아이피*/
del_yn varchar(1) default 'n',								/*삭제여부*/
userid varchar(200) not null,
event varchar(200) not null default 'no event',
primary key(num)
);

create table cw_account_book
( 
num integer not null auto_increment, 
reg_date timestamp not null default current_timestamp,
ip varchar(50) not null,									/*아이피*/
del_yn varchar(1) default 'n',								/*삭제여부*/
account_book_list_num integer not null,
userid varchar(200) not null,
content text not null,
cost integer not null default 0,
status varchar(1) default '+', /*+ -*/
primary key(num) 
)
;

/*list*/
insert into cw_account_book_list(ip,userid,event) values('211.211.211.211','x1wins','test');
insert into cw_account_book(ip,account_book_list_num,userid,content,cost,status) values('211.211.211.211','1','x1wins','my money','+1000','+');
insert into cw_account_book(ip,account_book_list_num,userid,content,cost,status) values('211.211.211.211','1','x1wins','my money','1000','+');
insert into cw_account_book(ip,account_book_list_num,userid,content,cost,status) values('211.211.211.211','1','x1wins','my money','1000','+');
insert into cw_account_book(ip,account_book_list_num,userid,content,cost,status) values('211.211.211.211','1','x1wins','my money','1000','+');
insert into cw_account_book(ip,account_book_list_num,userid,content,cost,status) values('211.211.211.211','1','x1wins','my money','1000','+');
insert into cw_account_book(ip,account_book_list_num,userid,content,cost,status) values('211.211.211.211','1','x1wins','my money','1000','+');
insert into cw_account_book(ip,account_book_list_num,userid,content,cost,status) values('211.211.211.211','1','x1wins','my money','1000','+');
insert into cw_account_book(ip,account_book_list_num,userid,content,cost,status) values('211.211.211.211','1','x1wins','dinner','-5000','-');
insert into cw_account_book(ip,account_book_list_num,userid,content,cost,status) values('211.211.211.211','1','x1wins','lunch set','-6000','-');

/*list*/				select num, date(reg_date) as reg_date, userid, event from cw_account_book_list where del_yn='n' and userid='x1wins' order by num desc;
/*detail list*/			select num, date(reg_date) as reg_date, userid, content, cost, status from cw_account_book where del_yn='n' and account_book_list_num = '1' and userid='x1wins' order by num desc;
/*1 num sum*/			select sum(cost) as total_cost from cw_account_book where del_yn='n' and account_book_list_num='1' and userid='x1wins';
/*total sum*/			select sum(cost) as total_cost from cw_account_book where del_yn='n' and userid='x1wins';
/*year-month total sum*/select ifnull(sum(cost),0) as sum_cost from cw_account_book where del_yn='n' and account_book_list_num='1' and userid='x1wins' and year(reg_date) = year('2011-05-01') and month(reg_date) = month('2011-05-01');

select year('2001-05-01');
select year(date(2001-5-1))

/*list update*/
update cw_account_book_list set ip = '192.0.0.1' ,event = 'shit'where num = 1;
/*detail update*/
update cw_account_book set ip = '192.0.0.1' ,content = 'shit',cost='-1', status ='-' where num = 5;

/*list delete*/
update cw_account_book_list set del_yn = 'y' where num = 1;
update cw_account_book set del_yn = 'y' where account_book_list_num = 1;

/*detail delete*/
update cw_account_book set del_yn = 'y' where num = 5;
tired
로그인 하셔야 댓글을 사용하실수 있습니다.
댓글쓰기를 누르면 로그인 페이지로 이동 후 돌아옵니다.
총 댓글 겟수 0
번호 제목 등록일 작성자 조회수
520 free css design (2) 2012-04-23 이창우 2173
174 design (0) 2011-06-10 이창우 2441
-> account book (0) 2011-05-12 이창우 3256
86 event money list (0) 2011-05-11 이창우 2189
12 exam (0) 2011-04-26 이창우 3031
< 1 >
글쓰기 검색