2-4《学生食堂信息管理系统》饭卡管理模块

Create Database  CardDB
on Primary
(name='CardDB',
filename='D:\boss\ CardDB.mdf')
log on
(name='ProductDB_log',
filename='D:\boss\ CardDB_log.ldf')

use CardDB
go

Create table T_card
(
card_id int identity(1,1),
student_id int,
student_name varchar(50),
curr_money money,
register_date datetime,
primary key(card_id),
)

Create table T_add_money
(
add_id int identity(1011,1),
card_id int,
the_money money,
register_date datetime,
primary key(add_id)
)

Create table T_consume_money
(
consume_id int identity(1111,1),
card_id int,
the_money money,
register_date datetime,
primary key(consume_id)
)

alter table T_add_money
add constraint FK_T_add_money_card_id foreign key(card_id ) references T_card(card_id )

alter table T_consume_money
add constraint FK_T_consume_money_card_id foreign key(card_id ) references T_card(card_id )
--充值金额列（the_money）只能输入 50-200 之间的数；--
alter table  T_add_money
add constraint CK_T_add_money_the_money  check(the_money>=50 and the_money<=200)
--录入时间列(register_date)默认值为当前录入时间（三个表均设置）--
alter table T_card
add constraint GE_T_card_register_date default getdate() for register_date

alter table T_add_money
add constraint GE_T_add_money_register_date default getdate() for register_date

alter table T_consume_money
add constraint GE_T_consume_money_register_date default getdate() for register_date

--在每个表中插入 1 条测试数据（样本数据包含下面题目中使用的数据）；--
insert into T_card values('1234','XXX','3.5','2012-3-4')

insert into T_add_money values('1','60','2011-6-2')

insert into T_consume_money values('1','30','2011-8-2')
--查询所有饭卡信息；--
select*from [dbo].[T_card]
--查询所有饭卡的余额之和；--
select SUM(the_money) from [dbo].[T_consume_money]
--查询某日所有饭卡的充值金额之和(比如 2011 年 6 月 2 号总共充值金额是多少元)；--
select [register_date],SUM(the_money) from [dbo].[T_add_money]
group by [register_date]

--创建存储过程，根据某个学生的饭卡 ID 上的余额，判断该饭卡是否能进行一定金额的消--
--费.(比如饭卡 ID 号为 1 的饭卡余额只有 3.5 元，则该饭卡不能购买 5.5 元的中餐存--
--储过程输入参数为饭卡 ID 号，本次消费金额，返回值为 true 或者false。--
CREATE PROC consume_money
(
@stu_Id int
,@the_money money
)
as
    declare @stumoney money; 
    select  @stumoney = [the_money]
    from    [dbo].[T_consume_money]
    where   [card_id] = @stu_Id; 
    if @stumoney < @the_money
        begin 
            return 0;
        end;
    if @stumoney >= @the_money
        begin 
            return 1;
        end;
 go   
declare @return varchar(10)	 
exec @return=consume_money '2',5.5
print @return