【课程设计】“个人记账系统”——记熬夜奋战的大学二年级数据库课程设计作品

实现效果

实现效果：导航栏

实现效果：资产展示页面

实现效果：动账管理

实现效果：预算管理

实现效果：消费情况

实现效果：可视化分析

展示视频

“个人记账系统”-记熬夜奋战的大学二年级数据库课程设计作品

数据库创建关系模式

CREATE TABLE `assettable` (
  `aId` int(11) NOT NULL AUTO_INCREMENT,
  `aName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `aMount` double NOT NULL,
  `aType` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `uId` int(11) DEFAULT NULL,
  PRIMARY KEY (`aId`),
  KEY `uId` (`uId`),
  CONSTRAINT `assettable_ibfk_1` FOREIGN KEY (`uId`) REFERENCES `usertable` (`uId`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=97 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `budgettable` (
  `bId` int(11) NOT NULL AUTO_INCREMENT,
  `bName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `bMount` double NOT NULL,
  `bRemain` double NOT NULL,
  `uId` int(11) DEFAULT NULL,
  PRIMARY KEY (`bId`),
  KEY `uId` (`uId`),
  CONSTRAINT `budgettable_ibfk_1` FOREIGN KEY (`uId`) REFERENCES `usertable` (`uId`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `costtable` (
  `cId` int(11) NOT NULL AUTO_INCREMENT,
  `cDate` date NOT NULL,
  `cContent` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `cMount` double NOT NULL,
  PRIMARY KEY (`cId`)
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `logstable` (
  `uId` int(11) DEFAULT NULL,
  `cId` int(11) NOT NULL,
  `aId` int(11) DEFAULT NULL,
  `b2Id` int(11) DEFAULT NULL,
  PRIMARY KEY (`cId`),
  KEY `uId` (`uId`),
  KEY `aId` (`aId`),
  KEY `b2Id` (`b2Id`),
  CONSTRAINT `logstable_ibfk_1` FOREIGN KEY (`uId`) REFERENCES `usertable` (`uId`) ON DELETE CASCADE,
  CONSTRAINT `logstable_ibfk_2` FOREIGN KEY (`cId`) REFERENCES `costtable` (`cId`) ON DELETE CASCADE,
  CONSTRAINT `logstable_ibfk_3` FOREIGN KEY (`aId`) REFERENCES `assettable` (`aId`) ON DELETE SET NULL,
  CONSTRAINT `logstable_ibfk_4` FOREIGN KEY (`b2Id`) REFERENCES `budget2table` (`b2Id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `usertable` (
  `uId` int(11) NOT NULL AUTO_INCREMENT,
  `uPw` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `uName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`uId`),
  UNIQUE KEY `usertable_un` (`uName`)
) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

建立视图

VassetTable、Vbudget2Table、VbudgetTable 、VcostTable、VlogsTable、VuserTable

create view VassetTable as
select aId, aName, aMount, aType, uId from assetTable;

create view Vbudget2Table as
select b2Id, b2Name, b2Mount, b2Remain, bId, uId from budget2Table;

create view VbudgetTable as
select bId, bName, bMount, bRemain, uId from budgetTable;

create view VcostTable as
select cId, cDate, cContent, cMount from costTable;

create view VlogsTable  as
select uId, cId, aId, b2Id from logsTable;

create view VuserTable  as
select uId, uPw, uName from userTable;

建立触发器

deleteCostBudgetAsset： 用户删除消费记录，需要从金融账户和预算表中分别返还钱，对于负债和储蓄要分别操作：负债账户的金额将减少、储蓄账户的金额将增加，对应预算中的金额将增加

CREATE DEFINER=`root`@`localhost` TRIGGER deleteCostBudgetAsset
BEFORE delete ON costTable
FOR EACH ROW
begin
	declare Mount real;

	set Mount = old.cMount;

	if (select aType from assettable where aId=(
	select aId from logsTable where cId=old.cId))='save' then
	UPDATE assettable SET aMount=aMount+Mount where aid=(
	select aId from logsTable where cId=old.cId);
	UPDATE budgettable SET bRemain=bRemain+Mount where bid=(
		select bid from budget2table where b2id=(
		select b2id from logsTable where cId=old.cId)
	);	
	UPDATE budget2table SET b2Remain=b2Remain+Mount where b2id=(
	select b2id from logsTable where cId=old.cId);

	elseif (select aType from assettable where aId=(
	select aId from logsTable where cId=old.cId))='debt' then
	
	UPDATE assettable SET aMount=aMount-Mount where aid=(
	select aId from logsTable where cId=old.cId);
	UPDATE budgettable SET bRemain=bRemain+Mount where bid=(
		select bid from budget2table where b2id=(
		select b2id from logsTable where cId=old.cId)
	);	
	UPDATE budget2table SET b2Remain=b2Remain+Mount where b2id=(
	select b2id from logsTable where cId=old.cId);

	end if;

paymentBudgetAsset： 用户记录消费后将对储蓄金融账户的金额减少，负债金融账户的金额增加，预算金额将减少

CREATE DEFINER=`root`@`localhost` TRIGGER paymentBudgetAsset
AFTER INSERT ON logsTable
FOR EACH ROW
begin
	declare Mount real;

	set Mount = (select cMount from costtable where cId=new.cId);

	if (select aType from assettable where aId=new.aId and uId=new.uId)='save' then
	UPDATE assettable SET aMount=aMount-Mount where aid=new.aid;
	UPDATE budgettable SET bRemain=bRemain-Mount where bid=(
		select bid from budget2table where b2id=new.b2id
	);	
	UPDATE budget2table SET b2Remain=b2Remain-Mount where b2id=new.b2id;

	elseif (select aType from assettable where aId=new.aId and uId=new.uId)='debt' then
	
	UPDATE assettable SET aMount=aMount+Mount where aid=new.aid;
	UPDATE budgettable SET bRemain=bRemain-Mount where bid=(
		select bid from budget2table where b2id=new.b2id
	);	
	UPDATE budget2table SET b2Remain=b2Remain-Mount where b2id=new.b2id;

	end if;

建立存储过程

budgetCostAnalysis： 取近7天的消费金额总和对应一级预算名称，进行周的预算-消费分析

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`budgetCostAnalysis`(
fuId int
)
begin
	select sum(cMount), bName from VcostTable natural join Vlogstable natural join Vbudget2Table natural join VbudgetTable
 	where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(cDate) and uid=fuid
 	group by bName order by sum(cMount) desc limit 7;
end

CostAnalysis： 取近7天的消费总和和日期，进行周消费分析

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`CostAnalysis`(
fuId int
)
begin
	select sum(cMount), cDate from 
	VcostTable natural join Vlogstable
 	where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(cDate) and uid=fuid
 	group by cDate order by cDate;
end

deleteAsset： 删除资产表

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`deleteAsset`(in daId varchar(100),duId int)
BEGIN
delete from VassetTable WHERE aId = daId and uid=duid;
end

deleteBudget： 删除一级预算

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`deleteBudget`(in dbId int,duId int)
BEGIN
delete from VbudgetTable WHERE bId = dbId and uid=duid;
end

deleteBudget2： 删除二级预算

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`deleteBudget2`(in db2Id varchar(100),duId int)
BEGIN
delete from Vbudget2Table WHERE b2Id = db2Id and uid=duid;
end

deleteCost： 删除消费记录

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`deleteCost`(in dcId int)
BEGIN
delete from VcostTable WHERE cId = dcId;
end

findaId： 查找对应的金融账户id

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`findaId`(
in faName varchar(100), 
fuId int,
out RaId int)
begin
	select aId 
	from assetTable 
	where aName=faName and uId=fuId into RaId;
end

findAssetDebAll： 查找所有的负债账户信息

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`findAssetDebAll`(in fuId int)
BEGIN
SELECT aName, aMount, aId, uId
FROM Vassettable
WHERE aType='debt' and uId=fuId;
End

findAssetDebSum： 查找所有负债账户负债金额的总和

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`findAssetDebSum`(in fuId int)
BEGIN
SELECT SUM(aMount) as assetSum
FROM Vassettable
where aType='debt' and uId=fuId;
end

findAssetPosAll： 查找所有储蓄账户信息

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`findAssetPosAll`(in fuId int)
BEGIN
SELECT aName, aMount, aId, uId
FROM Vassettable
WHERE aType='save' and uId=fuId;
End

findAssetPosSum： 查找所有储蓄账户储蓄金额的总和

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`findAssetPosSum`(in fuId int)
BEGIN
SELECT SUM(aMount) as assetSum
FROM Vassettable
where aType='save' and uId=fuId;
end

findb2Id： 查找二级预算的预算id

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`findb2Id`(
in fb2Name varchar(100),
fuId int,
out Rb2Id int)
begin
	select b2Id 
	from Vbudget2Table 
	where b2Name=fb2Name and uId=fuId into Rb2Id;
end

findBudget2： 通过一级预算id查找所有的二级预算

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`findBudget2`(in fbId int)
BEGIN
select b2Name, b2Mount, b2Remain, b2Id
from Vbudget2table
where bId=fbId;
end

findBudget2All： 通过用户id查找所有的二级预算

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`findBudget2All`( in fuId int)
BEGIN
select b2id, b2Name, b2Mount, b2Remain
from Vbudget2Table
where uId=fuId;
end

findBudgetAll： 通过用户id查找所有的一级预算

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`findBudgetAll`(in fuId int)
BEGIN
select bId, bName, bMount, bRemain 
from VbudgetTable
where uId=fuId;
end

findCostAllBeforeLastMonth： 查找上个月之前的所有消费记录

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`findCostAllBeforeLastMonth`(in fuid int)
BEGIN
select cId, cDate, cMount, cContent, aName, b2Name
from VcostTable natural join VlogsTable natural join VassetTable natural join Vbudget2Table 
where period_diff(date_format(now(),'%Y%m'), date_format(cDate,'%Y%m')) >1 and uid=fuid;
end

findCostAllLastMonth： 查找上个月的所有消费记录

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`findCostAllLastMonth`(in fuid int)
BEGIN
select cId,cDate, cMount, cContent, aName, b2Name
from VcostTable natural join VlogsTable natural join VassetTable natural join Vbudget2Table 
where period_diff(date_format(now(),'%Y%m'), date_format(cDate,'%Y%m')) =1 and uid=fuid;
end

findCostAllThisMonth： 查找本月所有的消费记录

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`findCostAllThisMonth`(in fuid int)
BEGIN
select tl.cId as cId,cDate, cMount, cContent, aName, b2Name
from VlogsTable tl
left join VcostTable tc on tc.cid=tl.cid 
left join Vbudget2Table  tb on tb.b2Id=tl.b2Id 
left join VassetTable ta on ta.aid=tl.aid
where tl.uid=fuid and date_format( cDate, '%Y%m' ) = date_format(curdate( ) , '%Y%m' )
order by tc.cDate desc;
end

findCostFromDiffDay： 通过日期差查找所有的消费金额的和（比如查找和今天差一天的消费金额的和），用以统计分析

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`findCostFromDiffDay`(in fuId int, diffday int)
BEGIN
select sum(cmount)
from Vcosttable natural join Vlogstable natural join Vusertable
where uId=fuId and TIMESTAMPDIFF(day,cDate,NOW())=diffday;
end

findCostSumLastMonth： 查找上个月的消费总和

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`findCostSumLastMonth`(in fuid int)
BEGIN
select SUM(cMount) as CostSumLastMonth
from VcostTable natural join VlogsTable 
where period_diff(date_format(now(),'%Y%m'), date_format(cDate,'%Y%m')) =1 and uid=fuid;
end

findCostSumThisMonth： 查找本月的消费金额总和

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`findCostSumThisMonth`(in fuid int)
BEGIN
select SUM(cMount) as CostSumThisMonth
from VcostTable natural join VlogsTable
where date_format( cDate, '%Y%m' ) = date_format(curdate( ) , '%Y%m' ) and uId=fuid;
end

income： 记录收入

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`income`(
in uaName varchar(100),
uuId int,
uaMount real
)
begin
	update Vassettable set aMount=aMount+uaMount where aName=uaName and uId=uuId;
end

insertAsset： 创建资产账户

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`insertAsset`(in ianame varchar(100), iamount int, iatype varchar(100), iuId int)
BEGIN
INSERT INTO VassetTable(aName, aMount, aType, uId) Values(ianame, iamount, iatype,iuId);
End

insertBudget： 创建一级预算

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`insertBudget`(in ibName varchar(100), ibMount real, iuId int)
BEGIN
insert into Vbudgettable(bName, bMount, bRemain, uId)
values(ibName, ibMount, ibMount, iuId);
end

insertBudget2： 创建二级预算

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`insertBudget2`(in ib2Name varchar(100), ib2Mount real, ibId int, iuId int)
BEGIN
insert into Vbudget2table(b2Name, b2Mount, b2Remain, bId, uId)
values(ib2Name, ib2Mount, ib2Mount, ibId, iuId);
end

insertCost： 创建消费记录

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`insertCost`(in
icDate date,
icContent varchar(500), 
icMount real,
out RcId int)
BEGIN
insert into Vcosttable (cDate, cContent, cMount)
values(icDate, icContent, icMount);
SELECT LAST_INSERT_ID() into RcId;
End

insertCostLogs： 通过cost表的插入信息创建logs表信息，一个中间触发器

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`insertCostLogs`(
in icDate date, 
icContent varchar(100), 
icMount int, 
fb2Name varchar(100), 
faName varchar(100), 
iuId int
)
begin
	call insertCost(icDate, icContent, icMount, @RcId);
	call findaId(faName, iuId, @RaId);
	call findb2Id(fb2Name, iuId, @Rb2Id);
	call insertLogs(iuId, @RcId, @RaId, @Rb2Id);
end

insertLogs： 把信息插入logs表内

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`insertLogs`(in 
iuId int, 
icId int, 
iaId int, 
ib2Id int)
begin
	insert into Vlogstable(uId, cId, aId, b2Id)
	values(iuId, icId, iaId, ib2Id);
end

login： 登录触发器

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`login`(
in fuName varchar(100), 
fupw varchar(100),
out fuId int
)
begin
	select uId from VuserTable where uName=fuName and upw=fupw into fuId;
end

logup： 注册触发器

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`logup`(
in iuName varchar(100), 
iupw varchar(100)
)
begin
	insert into Vusertable(uName, uPw)
	values(iuName, iupw);
end

transfer： 转账触发器

CREATE DEFINER=`root`@`localhost` PROCEDURE `assetmanagementsys`.`transfer`(
in faName varchar(100),
taName varchar(100),
uuId int,
taMount real
)
begin
	set @Type=(select aType from assettable where aName=taName and uId=uuId);
	if @Type='save' then
	update Vassettable set aMount=aMount-taMount where aName=faName and uId=uuId;
	update Vassettable set aMount=aMount+taMount where aName=taName and uId=uuId;
	else
	update Vassettable set aMount=aMount-taMount where aName=faName and uId=uuId;
	update Vassettable set aMount=aMount-taMount where aName=taName and uId=uuId;
	end if;
end