数据库原理第三次实验报告

目录

• 场景描述
• 调研结果
• 数据库建模
  • ER 图
  • 关系数据模型
  • 数据库表关系图
• 模型验证
  • 插入随机数据
    • 随机函数
    • 触发器
    • 插入一些初始数据
    • 根据上述初始数据生成随机数据
  • 查询1
  • 查询2
  • 查询3
  • 查询4
  • 查询5
• 参考资料

场景描述

假设某银行要开发一个手机银行App，需要设计一个数据库Bank Finance。针对手机银行App银行的业务，需要管理的对象包括客户、银行卡、存款、理财产品和基金。客户可以办理银行卡，同时可以购买不同的银行产品。

对象之间存在如下关系：

1. 一个客户可以办理多张银行卡（包含储蓄卡和信用卡）；
2. 一个客户可以办理多笔存款，同一类存款可由多个客户办理；
3. 一个客户可以购买多个理财产品，同一类理财产品可由多个客户购买；
4. 一个客户可以购买多个基金，同一类基金可由多个客户购买。

调研结果

基于上述场景，调研银行业务，确定被管理的对象，包括客户、银行卡、存款、理财产品、保险和基金的属性以及对象之间的联系。

各对象的属性如下表所示：

对象	属性1	属性2	属性3	属性4	属性5	属性6	属性7	属性8
客户	姓名	身份证号	手机号	登录密码
银行卡	银行卡号	所有者	类型	密码	状态	余额
存款	存款利率	存款余额
理财产品	名称	编号	期限	起购金额	利率	持有数量
基金	名称	类型	经理	规模	风险等级	单位净值	本金数额	持仓数额

数据库建模

ER 图

关系数据模型

学习使用数据库辅助设计工具Sybase PowerDesigner，下载链接：https://www.cnblogs.com/huangting/p/12654057.html，使用PowerDesigner生成关系数据模型，并自动生成建表的 SQL 语句：

关系数据模型：

建表 SQL 语句：

/*==============================================================*/
/* DBMS name:      Sybase SQL Anywhere 12                       */
/* Created on:     2024/6/2 10:02:46                            */
/*==============================================================*/
if exists(select 1 from sys.sysforeignkey where role='FK_BANKCARD_REFERENCE_BANKCARD') then
    alter table BankCard
       delete foreign key FK_BANKCARD_REFERENCE_BANKCARD
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_BANKCARD_REFERENCE_CLIENT') then
    alter table BankCard
       delete foreign key FK_BANKCARD_REFERENCE_CLIENT
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_DEPOSITI_REFERENCE_PROPERTY') then
    alter table DepositInHand
       delete foreign key FK_DEPOSITI_REFERENCE_PROPERTY
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_DEPOSITI_REFERENCE_DEPOSIT') then
    alter table DepositInHand
       delete foreign key FK_DEPOSITI_REFERENCE_DEPOSIT
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_FINANCIA_REFERENCE_PROPERTY') then
    alter table FinancialProductInHand
       delete foreign key FK_FINANCIA_REFERENCE_PROPERTY
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_FINANCIA_REFERENCE_FINANCIA') then
    alter table FinancialProductInHand
       delete foreign key FK_FINANCIA_REFERENCE_FINANCIA
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_FUND_REFERENCE_FUNDTYPE') then
    alter table Fund
       delete foreign key FK_FUND_REFERENCE_FUNDTYPE
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_FUNDINHA_REFERENCE_PROPERTY') then
    alter table FundInHand
       delete foreign key FK_FUNDINHA_REFERENCE_PROPERTY
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_FUNDINHA_REFERENCE_FUND') then
    alter table FundInHand
       delete foreign key FK_FUNDINHA_REFERENCE_FUND
end if;

if exists(select 1 from sys.sysforeignkey where role='FK_PROPERTY_REFERENCE_CLIENT') then
    alter table Property
       delete foreign key FK_PROPERTY_REFERENCE_CLIENT
end if;

drop table if exists BankCard;
drop table if exists BankCardType;
drop index if exists Client.Client_PK;
drop table if exists Client;
drop table if exists Deposit;
drop table if exists DepositInHand;
drop table if exists FinancialProduct;
drop table if exists FinancialProductInHand;
drop table if exists Fund;
drop table if exists FundInHand;
drop table if exists FundType;
drop table if exists Property;
/*==============================================================*/
/* Table: BankCard                                              */
/*==============================================================*/
create table BankCard 
(
   cBankCardNo          varchar(19)                    not null,
   cClientNo            char(10)                       not null,
   cBankCardType        char(5)                        null,
   cBankPassword        varchar(20)                    null,
   cBankCardStatus      char(1)                        null,
   mBankCardBalance     numeric(8,2)                   null,
   constraint PK_BANKCARD primary key clustered (cBankCardNo)
);

/*==============================================================*/
/* Table: BankCardType                                          */
/*==============================================================*/
create table BankCardType 
(
   cBankCardTypeNo      char(5)                        not null,
   cBandCardTypeName    varchar(20)                    not null,
   constraint PK_BANKCARDTYPE primary key clustered (cBankCardTypeNo)
);

/*==============================================================*/
/* Table: Client                                                */
/*==============================================================*/
create table Client 
(
   cClientNo            char(10)                       not null,
   vClientName          varchar(10)                    not null,
   cIDNumber            char(18)                       not null,
   cClientPhoneNumber   char(11)                       not null,
   cClientPassword      varchar(20)                    not null,
   constraint PK_CLIENT primary key (cClientNo)
);

/*==============================================================*/
/* Index: Client_PK                                             */
/*==============================================================*/
create unique index Client_PK on Client (
cClientNo ASC
);

/*==============================================================*/
/* Table: Deposit                                               */
/*==============================================================*/
create table Deposit 
(
   cDepositNo           char(10)                       not null,
   vDepositName         varchar(10)                    null,
   decDepositRate       decimal(5,5)                   null,
   constraint PK_DEPOSIT primary key clustered (cDepositNo)
);

/*==============================================================*/
/* Table: DepositInHand                                         */
/*==============================================================*/
create table DepositInHand 
(
   cDepositInHandNo     char(10)                       not null,
   cPropertyNo          char(10)                       not null,
   cDepositNo           char(10)                       not null,
   mDepositBanlance     numeric(8,2)                   null,
   constraint PK_DEPOSITINHAND primary key clustered (cDepositInHandNo)
);

/*==============================================================*/
/* Table: FinancialProduct                                      */
/*==============================================================*/
create table FinancialProduct 
(
   cFinancialProductNo  char(10)                       not null,
   vFinancialProductName varchar(10)                    null,
   iFinancialProductDuration integer                        null,
   decFinanicalProductRate decimal(5,5)                   null,
   mFinancialProductMinimumAmount numeric(8,2)                   null,
   constraint PK_FINANCIALPRODUCT primary key clustered (cFinancialProductNo)
);

/*==============================================================*/
/* Table: FinancialProductInHand                                */
/*==============================================================*/
create table FinancialProductInHand 
(
   cFinancialProductInHandNo char(10)                       not null,
   cPropertyNo          char(10)                       not null,
   cFinancialProductNo  char(10)                       not null,
   iFinancialProductQty numeric(8,2)                   null,
   dFinancialProductDate datetime                       null,
   mFinancialProductPosition numeric(8,2)                   null,
   constraint PK_FINANCIALPRODUCTINHAND primary key clustered (cFinancialProductInHandNo)
);

/*==============================================================*/
/* Table: Fund                                                  */
/*==============================================================*/
create table Fund 
(
   cFundNo              char(10)                       not null,
   vFundName            varchar(10)                    null,
   vFundManagerName     varchar(10)                    null,
   cFundTypeNo          char(5)                        null,
   siFundRiskLevel      smallint                       null,
   mFundSize            numeric(8,2)                   null,
   mFundAverageNAV      numeric(8,2)                   null,
   constraint PK_FUND primary key clustered (cFundNo)
);

/*==============================================================*/
/* Table: FundInHand                                            */
/*==============================================================*/
create table FundInHand 
(
   cFundInHandNo        char(10)                       not null,
   cPropertyNo          char(10)                       not null,
   cFundNo              char(10)                       not null,
   mFundCapital         numeric(8,2)                   null,
   mFundPosition        numeric(8,2)                   null,
   constraint PK_FUNDINHAND primary key clustered (cFundInHandNo)
);

/*==============================================================*/
/* Table: FundType                                              */
/*==============================================================*/
create table FundType 
(
   cFundTypeNo          char(5)                        not null,
   vFundTypeName        varchar(20)                    not null,
   constraint PK_FUNDTYPE primary key clustered (cFundTypeNo)
);

/*==============================================================*/
/* Table: Property                                              */
/*==============================================================*/
create table Property 
(
   cPropertyNo          char(10)                       not null,
   cClientNo            char(10)                       not null,
   mPropertyCapital     numeric(8,2)                   null,
   mPropertyPosition    numeric(8,2)                   null,
   constraint PK_PROPERTY primary key clustered (cPropertyNo)
);

alter table BankCard
   add constraint FK_BANKCARD_REFERENCE_BANKCARD foreign key (cBankCardType)
      references BankCardType (cBankCardTypeNo)
      on update restrict
      on delete restrict;

alter table BankCard
   add constraint FK_BANKCARD_REFERENCE_CLIENT foreign key (cClientNo)
      references Client (cClientNo)
      on update restrict
      on delete restrict;

alter table DepositInHand
   add constraint FK_DEPOSITI_REFERENCE_PROPERTY foreign key (cPropertyNo)
      references Property (cPropertyNo)
      on update restrict
      on delete restrict;

alter table DepositInHand
   add constraint FK_DEPOSITI_REFERENCE_DEPOSIT foreign key (cDepositNo)
      references Deposit (cDepositNo)
      on update restrict
      on delete restrict;

alter table FinancialProductInHand
   add constraint FK_FINANCIA_REFERENCE_PROPERTY foreign key (cPropertyNo)
      references Property (cPropertyNo)
      on update restrict
      on delete restrict;

alter table FinancialProductInHand
   add constraint FK_FINANCIA_REFERENCE_FINANCIA foreign key (cFinancialProductNo)
      references FinancialProduct (cFinancialProductNo)
      on update restrict
      on delete restrict;

alter table Fund
   add constraint FK_FUND_REFERENCE_FUNDTYPE foreign key (cFundTypeNo)
      references FundType (cFundTypeNo)
      on update restrict
      on delete restrict;

alter table FundInHand
   add constraint FK_FUNDINHA_REFERENCE_PROPERTY foreign key (cPropertyNo)
      references Property (cPropertyNo)
      on update restrict
      on delete restrict;

alter table FundInHand
   add constraint FK_FUNDINHA_REFERENCE_FUND foreign key (cFundNo)
      references Fund (cFundNo)
      on update restrict
      on delete restrict;

alter table Property
   add constraint FK_PROPERTY_REFERENCE_CLIENT foreign key (cClientNo)
      references Client (cClientNo)
      on update restrict
      on delete restrict;

数据库表关系图

将自动连接到数据库服务器上，生成数据库BankFinance，创建数据库表关系图。

模型验证

在数据库表中生成若干模拟数据，验证模型是否支持给定查询。

插入随机数据

随机函数

自定义用于生成随机数据的函数：

create view v_rand
as
select rand() as val;
go

create or alter function getRandNum(@n int)
    returns int
as begin
    select @n = @n * val from v_rand
    return floor(@n)
end;
go

create or alter function getRandomString(
    @num int,
    @chars varchar(1024) = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
) returns varchar(1024)
as begin
    declare @res_str VARCHAR(1024) = ''
    declare @i int=0
    while (@i < @num) begin
        set @res_str = @res_str + substring(@chars, dbo.getRandNum(len(@chars))+ 1, 1)
        set @i = @i + 1
    end
    return @res_str
end;
go

create or alter function getRandomDigitString(
    @num int,
    @chars varchar(1024) = '0123456789'
) returns varchar(1024)
as begin
    declare @res_str VARCHAR(1024) = ''
    declare @i int=0
    while (@i < @num) begin
        set @res_str = @res_str + substring(@chars, dbo.getRandNum(len(@chars))+ 1, 1)
        set @i = @i + 1
    end
    return @res_str
end;
go

触发器

用于在更新客户的存款、理财产品与基金时，更新 Property 表中总成本与总仓位数据的触发器。

/*==============================================================*/
/* Table: DepositInHand                                         */
/*==============================================================*/
create trigger updatePropertyAfterInsertDepositInHand on DepositInHand
after insert as
    update Property
    set mPropertyCapital = mPropertyCapital + inserted.mDepositBanlance,
        mPropertyPosition = mPropertyPosition + inserted.mDepositBanlance
    from inserted
    where Property.cPropertyNo = inserted.cPropertyNo
go

create trigger updatePropertyAfterDeleteDepositInHand on DepositInHand
    after delete as
    update Property
    set mPropertyCapital = mPropertyCapital - deleted.mDepositBanlance,
        mPropertyPosition = mPropertyPosition - deleted.mDepositBanlance
    from deleted
    where Property.cPropertyNo = deleted.cPropertyNo
go

create trigger updatePropertyAfterUpdateDepositInHand on DepositInHand
after update as
    update Property
    set mPropertyCapital = mPropertyCapital - deleted.mDepositBanlance,
        mPropertyPosition = mPropertyPosition - deleted.mDepositBanlance
    from deleted
    where Property.cPropertyNo = deleted.cPropertyNo

    update Property
    set mPropertyCapital = mPropertyCapital + inserted.mDepositBanlance,
        mPropertyPosition = mPropertyPosition + inserted.mDepositBanlance
    from inserted
    where Property.cPropertyNo = inserted.cPropertyNo
go

/*==============================================================*/
/* Table: FinancialProductInHand                                */
/*==============================================================*/
create trigger updatePropertyAfterInsertFinancialProductInHand on FinancialProductInHand
after insert as
    update Property
    set mPropertyCapital = mPropertyCapital + inserted.mFinancialProductPosition,
        mPropertyPosition = mPropertyPosition + inserted.mFinancialProductPosition
    from inserted
    where Property.cPropertyNo = inserted.cPropertyNo
go

create trigger updatePropertyAfterDeleteFinancialProductInHand on FinancialProductInHand
after delete as
    declare @financialProductNo char(10)
    select @financialProductNo = deleted.cFinancialProductNo
    from deleted

    declare @minAmount money
    select @minAmount = FP.mFinancialProductMinimumAmount
    from FinancialProduct FP
    where @financialProductNo = FP.cFinancialProductNo

    update Property
    set mPropertyCapital = mPropertyCapital - @minAmount * deleted.iFinancialProductQty,
        mPropertyPosition = mPropertyPosition - deleted.mFinancialProductPosition
    from deleted
    where Property.cPropertyNo = deleted.cPropertyNo
go

create trigger updatePropertyAfterUpdateFinancialProductInHand on FinancialProductInHand
after update as
    update Property
    set mPropertyPosition = mPropertyPosition - deleted.mFinancialProductPosition
    from deleted
    where Property.cPropertyNo = deleted.cPropertyNo

    update Property
    set mPropertyPosition = mPropertyPosition + inserted.mFinancialProductPosition
    from inserted
    where Property.cPropertyNo = inserted.cPropertyNo
go

/*==============================================================*/
/* Table: FundInHand                                            */
/*==============================================================*/
create trigger updatePropertyAfterInsertFundInHand on FundInHand
after insert as
    update Property
    set mPropertyCapital = mPropertyCapital + inserted.mFundCapital,
        mPropertyPosition = mPropertyPosition + inserted.mFundPosition
    from inserted
    where Property.cPropertyNo = inserted.cPropertyNo
go

create trigger updatePropertyAfterDeleteFundInHand on FundInHand
after delete as
    update Property
    set mPropertyCapital = mPropertyCapital - deleted.mFundCapital,
        mPropertyPosition = mPropertyPosition - deleted.mFundPosition
    from deleted
    where Property.cPropertyNo = deleted.cPropertyNo
go

create trigger updatePropertyAfterUpdateFundInHand on FundInHand
after update as
    update Property
    set mPropertyCapital = mPropertyCapital - deleted.mFundCapital,
        mPropertyPosition = mPropertyPosition - deleted.mFundPosition
    from deleted
    where Property.cPropertyNo = deleted.cPropertyNo

    update Property
    set mPropertyCapital = mPropertyCapital + inserted.mFundCapital,
        mPropertyPosition = mPropertyPosition + inserted.mFundPosition
    from inserted
    where Property.cPropertyNo = inserted.cPropertyNo
go

create trigger InitPropertyAfterInsertClient on Client
    after insert as
    declare @maxNo int
    declare @clientNo char(10)

    set @maxNo = 1
    if exists(select * from Property) begin
        select @maxNo = max(cPropertyNo) + 1 from Property
    end
    select @clientNo = inserted.cClientNo from inserted

--     print @maxNo
--     print right('0000000000' + cast(@maxNo AS VARCHAR), 10)

    insert into Property values(right('0000000000' + cast(@maxNo AS VARCHAR), 10),
                                @clientNo, 0, 0)
go


create trigger DeletePropertyAfterDeleteClient on Client
    after delete as

    delete from Property
    where Property.cClientNo in (
        select cClientNo
        from deleted
    )
go

插入一些初始数据

insert into Deposit values('0000000001', 'Deposit1', 0.01)
insert into Deposit values('0000000002', 'Deposit2', 0.02)
insert into Deposit values('0000000003', 'Deposit3', 0.03)

insert into FinancialProduct values('0000000001', 'FProduct1', 6, 0.01, 10000)
insert into FinancialProduct values('0000000002', 'FProduct2', 12, 0.02, 20000)
insert into FinancialProduct values('0000000003', 'FProduct3', 24, 0.03, 30000)

insert into FundType values('00001', 'Millennium')
insert into FundType values('00002', 'Trinity')
insert into FundType values('00003', 'Casino!')

insert into Fund values('0000000001', 'Fund1', 'Yukka', '00001', 0, 100000.0, 5.0)
insert into Fund values('0000000002', 'Fund2', 'Nagisa', '00002', 1, 100.0, 2.0)
insert into Fund values('0000000003', 'Fund3', 'Koyuki', '00003', 1000, 0.01, 0.001)

insert into Client values('0000000001', 'Shiroko', '1145141919810-0001', '114514-0001', '1')
insert into Client values('0000000002', 'Hoshino', '1145141919810-0002', '114514-0002', '2')
insert into Client values('0000000003', 'Nonomi', '1145141919810-0003', '114514-0003', '3')
insert into Client values('0000000004', 'Serika', '1145141919810-0004', '114514-0004', '4')
insert into Client values('0000000005', 'Ayane', '1145141919810-0005', '114514-0005', '5')

insert into BankCardType values('00001', 'BankCardType1')
insert into BankCardType values('00002', 'BankCardType2')
insert into BankCardType values('00003', 'BankCardType3!')

根据上述初始数据生成随机数据

/*==============================================================*/
/* Table: BankCard                                              */
/*==============================================================*/
declare @i int
declare @dataNumber int
set @i = 0
set @dataNumber = 10

while @i < @dataNumber begin
    declare @clientNo char(10)
    select top 1 @clientNo = cClientNo
    from Client
    order by newid()

    declare @bankCardNo varchar(9)
    set @bankCardNo = dbo.getRandomDigitString(19, DEFAULT)
    while @bankCardNo in (select cBankCardNo from BankCard) begin
        set @bankCardNo = dbo.getRandomDigitString(19, DEFAULT)
    end

    declare @bankCardTypeNo char(5)
    select top 1 @bankCardTypeNo = cBankCardTypeNo
    from BankCardType
    order by newid()


    insert into BankCard values(@bankCardNo, @clientNo, @bankCardTypeNo,
                                dbo.getRandomString(20, DEFAULT),
                                dbo.getRandomString(1, 'YN'),
                                dbo.getRandNum(1000));
    set @i = @i + 1
end

/*==============================================================*/
/* Table: DepositInHand                                         */
/*==============================================================*/
declare @i int
declare @dataNumber int
set @i = 0
set @dataNumber = 10

while @i < @dataNumber begin
    declare @maxNo int
    set @maxNo = 1
    if exists(select * from DepositInHand) begin
        select @maxNo = max(cDepositInHandNo) + 1 from DepositInHand
    end
    declare @depositInHandNo char(10)
    set @depositInHandNo = right('0000000000' + cast(@maxNo AS VARCHAR), 10)

    declare @propertyNo char(10)
    select top 1 @propertyNo = cPropertyNo
    from Property
    order by newid()

    declare @depositNo char(10)
    select top 1 @depositNo = cDepositNo
    from Deposit
    order by newid()

    insert into DepositInHand values(@depositInHandNo, @propertyNo, @depositNo,
                                     dbo.getRandNum(1000));
    set @i = @i + 1
end

/*==============================================================*/
/* Table: FinancialProductInHand                                */
/*==============================================================*/
declare @i int
declare @dataNumber int
set @i = 0
set @dataNumber = 10

while @i < @dataNumber begin
    declare @maxNo int
    set @maxNo = 1
    if exists(select * from FinancialProductInHand) begin
        select @maxNo = max(cFinancialProductInHandNo) + 1 from FinancialProductInHand
    end
    declare @financialProductInHandNo char(10)
    set @financialProductInHandNo = right('0000000000' + cast(@maxNo AS VARCHAR), 10)

    declare @propertyNo char(10)
    select top 1 @propertyNo = cPropertyNo
    from Property
    order by newid()

    declare @financialProductNo char(10)
    select top 1 @financialProductNo = cFinancialProductNo
    from FinancialProduct
    order by newid()

    declare @minAmount money
    select @minAmount = mFinancialProductMinimumAmount
    from FinancialProduct
    where @financialProductNo = cFinancialProductNo
    declare @financialProductQty integer
    set @financialProductQty = dbo.getRandNum(5)

    insert into FinancialProductInHand values(@financialProductInHandNo, @propertyNo, @financialProductNo,
                                              @financialProductQty, sysdatetime(),
                                              @minAmount * @financialProductQty);
    set @i = @i + 1
end

/*==============================================================*/
/* Table: FundInHand                                            */
/*==============================================================*/
declare @i int
declare @dataNumber int
set @i = 0
set @dataNumber = 10
while @i < @dataNumber begin
    declare @maxNo int
    set @maxNo = 1
    if exists(select * from FundInHand) begin
        select @maxNo = max(cFundInHandNo) + 1 from FundInHand
    end
    declare @fundInHandNo char(10)
    set @fundInHandNo = right('0000000000' + cast(@maxNo AS VARCHAR), 10)

    declare @propertyNo char(10)
    select top 1 @propertyNo = cPropertyNo
    from Property
    order by newid()

    declare @fundNo char(10)
    select top 1 @fundNo = cFundNo
    from Fund
    order by newid()

    declare @fundCapital money
    set @fundCapital = dbo.getRandNum(1000)

    insert into FundInHand values(@fundInHandNo, @propertyNo, @fundNo, @fundCapital, @fundCapital);

    set @i = @i + 1
end

查询1

查询某客户的总资产。

declare @clientName varchar(10)
set @clientName = 'Shiroko'

select vClientName, mPropertyPosition
from Property
left join Client
on Property.cClientNo = Client.cClientNo
where vClientName = @clientName

查询2

查询某客户的总负债。

declare @clientName varchar(10)
set @clientName = 'Shiroko'

select vClientName, mPropertyPosition - mPropertyCapital
from Property
left join Client
on Property.cClientNo = Client.cClientNo
where vClientName = @clientName

查询3

查询某客户购买的基金的总持仓收益和总持仓收益率。

declare @clientName varchar(10)
set @clientName = 'Shiroko'

declare @PropertyNo char(10)
select @PropertyNo = cPropertyNo
from Property
left join Client
on Property.cClientNo = Client.cClientNo
where vClientName = @clientName

select @clientName as Name,
       sum(mFundCapital) as Capital,
       sum(mFundPosition) as Position,
       (sum(mFundCapital) / sum(mFundPosition)) as ProfitRate
from FundInHand
where cPropertyNo = @PropertyNo

查询4

查询某客户持有的所有银行卡的卡号、类型、状态、余额。

declare @clientName varchar(10)
set @clientName = 'Shiroko'

declare @clientNo char(10)
select @clientNo = cClientNo
from Client
where vClientName = @clientName

select cBankCardNo, cBankCardType, cBankCardStatus, mBankCardBalance
from BankCard
where cClientNo = @clientNo

查询5

查询手机银行目前在售的所有理财产品的名称、编号、产品期限、起购金额。

select vFinancialProductName as Name,
       cFinancialProductNo as No,
       iFinancialProductDuration as Duration,
       mFinancialProductMinimumAmount as MinimumAmount
from FinancialProduct

参考资料

主要参考：数据库原理及应用教程 第4版︱微课版 （陈志泊 许福 韩慧 崔晓晖 路贺俊 阮豫红编著）

设计参考：

• 主要参考：【数据库原理实验(openGauss)】金融场景化实验_opengauss金融场景化实验-CSDN博客
• MySQL关于多种类型商品，商品拥有不同属性的表结构设计-CSDN博客
• 数据库设计（物品属性需要差异化）_数据库商品属性设计-CSDN博客
• 数据库 - 一个项目中多种订单的表设计 - SegmentFault 思否
• mysql - 数据库表中，两个表互为外键参考如何解决 - SegmentFault 思否
• SQL中两个表相互为外键关联，如何进行更新？-CSDN社区
• YY淘宝商品数据库设计 - 活雷锋 - 博客园

PowerDesigner 使用参考：

• PowerDesigner 安装+汉化+破解+使用过程 - 沦陷 - 博客园
• PowerDesigner 入门使用教程_powerdesigner使用教程-CSDN博客
• PowerDesigner为数据库物理模型字段添加主键、外键、非空、默认值、唯一等约束(持续更新ing) - Creasybeard - 博客园
• powerdesigner如何创建外键_powerdesigner 外键-CSDN博客

语言实现参考：

• SQL INSERT INTO 语句 _ 菜鸟教程
• SQL UPDATE 语句 _ 菜鸟教程
• Microsoft SQL Server 生成随机数字、字符串、日期、验证码以及 UUID_sql server 验证码-CSDN博客
• SQL 在SQL Server中使用前导零来格式化数字_极客教程
• 日期和时间数据类型及函数 - SQL Server (Transact-SQL) _ Microsoft Learn
• SQL Server YEAR() 函数使用指南
• SQL Server Year()函数 - SQL Server教程
• Sql语句在日期中添加或减去指定的时间_sql 时间增减-CSDN博客
• SQL 年份和月份使用哪种数据类型_极客教程
• Sql Server 对数据表循环插入多条数据（使用循环方法给数据表插入数据）_给sql server数据库持续写入数据的方法-CSDN博客
• mysql 用户变量怎么删除_mob649e8157aaee的技术博客_51CTO博客
• 变量 (Transact-SQL) - SQL Server _ Microsoft Learn
• 细说varchar与char有哪些区别？_char和varchar区别-CSDN博客
• SQL 查询从表中提取随机行_极客教程
• SQL 在SQL Server表中如何获取第n行_极客教程
• SQL SERVER中使用print如何一起输出数值和字符串_sql server print-CSDN博客

捉虫参考：

• SQL Server错误443：在函数内对带副作用的运算符 'rand' 的使用无效 - HandsomeFa - 博客园
• sql语句报错：“不是可以识别的内置函数名称”_sql不是可以识别的内置函数名称-CSDN博客