finace.sql

一个用MFC编写的家庭财务管理系统.采用了Sqlserver数据库.压缩包中已包括了所需的数据库文件.可直接附加到SQL服务器.

create database FinaceDB;
use FinaceDB;



--收入类型表
create table in_type_tab (
	id int primary key,
	name varchar(24) unique not null
);

--支出类型表
create table exp_type_tab (
	id int primary key,
	name varchar(24) unique not null
);

--收支信息表
create table in_exp_info_tab(
	ix_id integer primary key,
	ix_date datetime not null,
	ix_type int check (ix_type in (0,1)),
	ix_name varchar(24),
	money float null,
	description varchar(1000) null
);

--添加收支信息表日期的索引
create index in_exp_dateindex
	on in_exp_info_tab(ix_date);

--帐户信息
create table account_info_tab (
	acc_num varchar(24) primary key,
	name varchar(24) not null,
	card_num varchar(24) null,
	bank varchar(24) null,
	opendate datetime not null,
	balance float null,
	description varchar(1000) null
);

--交易信息表
create table exchange_info_tab (
	ex_id int primary key,
	acc_num varchar(24) foreign key(acc_num) references account_info_tab,
	ex_date datetime not null,
	ex_type int check (ex_type in(0,1)),
	money float null
);

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

--删除关联的收支类型数据
delete from in_exp_info_tab;

--添加收入类型数据
delete from in_type_tab;
insert into in_type_tab(id,name) values(1,'工资');
insert into in_type_tab(id,name) values(2,'奖励');
insert into in_type_tab(id,name) values(3,'红利');
insert into in_type_tab(id,name) values(4,'利息');
insert into in_type_tab(id,name) values(5,'津贴');
insert into in_type_tab(id,name) values(6,'其他');

--添加支出类型数据
delete from exp_type_tab;
insert into exp_type_tab (id,name) values(1,'生活');
insert into exp_type_tab (id,name) values(2,'服装');
insert into exp_type_tab (id,name) values(3,'休闲娱乐');
insert into exp_type_tab (id,name) values(4,'教育');
insert into exp_type_tab (id,name) values(5,'度假');
insert into exp_type_tab (id,name) values(6,'交通');
insert into exp_type_tab (id,name) values(7,'汽车');
insert into exp_type_tab (id,name) values(8,'电话');
insert into exp_type_tab (id,name) values(9,'上网');
insert into exp_type_tab (id,name) values(10,'报刊');
insert into exp_type_tab (id,name) values(11,'健康');
insert into exp_type_tab (id,name) values(12,'有线电视');
insert into exp_type_tab (id,name) values(13,'其他');