音乐播放器
My Brain
 
文章 标签
8
首页 首页
归档 归档
标签 标签
关于 关于
友人帐 友人帐
Powered by Gridea | Theme: Fog
载入天数...
载入时分秒...
阅
2022-11-13
91 min read
mysql java

数据库系统实验

数据库实验

一、实训1 数据库、表与完整性约束的定义(Create)

1.1 创建数据库

创建2022年北京冬奥会信息系统数据库

-- 本关我们强烈建议你点击本编辑窗口上方的“命令行”菜单打开Linux的命令行，
-- 并通过mysql命令行工具连接并登录到服务器，
-- 然后在mysql的命令行工具里通过恰当的语句完成任务。
-- 在以后的关卡中你可以通过命令行调试或测试语句，再将正确的语句粘贴到文件编辑窗口。

-- 你也可以选择在以下空白行填写完成目标任务的SQL语句：
-- 创建2022年北京冬奥会信息系统数据库
CREATE DATABASE beijing2022;
-- end

1.2 创建表及表的主码约束

建数据库TestDb，在TestDb下创建表t_emp，表结构如下：

字段名称 数据类型 备注
id INT 员工编号，主码
name VARCHAR(32) 员工名称
deptId INT 所在部门标号
salary FLOAT 工资 
# 请在以下适当的空白处填写SQL语句，完成任务书的要求。空白行可通过回车换行添加。 
USE TestDb;
CREATE TABLE t_emp(
    id INT PRIMARY KEY,
    name VARCHAR(32),
    deptId INT,
    salary FLOAT
);
/* *********** 结束 ************* */

1.3 创建外码约束(foreign key)

设有以下两张表：

dept(部门)

字段名称 数据类型 备注
deptNo INT 部门号，主键
deptName VARCHAR(32) 部门名称

staff(职工)

字段名称 数据类型 备注
staffNo INT 职工号，主键
staffName VARCHAR(32) 职工姓名
gender CHAR(1) 性别，F-女，M-男
dob date 出生日期
salary numeric(8,2) 工资
deptNo INT 部门号,外键

请创建上述两个表，为表定义主键，并给表staff创建外键，这个外键约束的名称为FK_staff_deptNo。在创建表之前你可能需要先创建数据库：MyDb，并且将两张表创建在MyDb数据库中。不需考虑关于性别的约束。（注意：如果你在实验1之后接着作本实验，数据库MyDb可能已经存在）

# 请在以下空白处填写完成任务的语句，空白行可通过回车换行添加。
# 你也可以在命令行窗口完成任务，不过，在命令行键入的语句不会被保存。
CREATE DATABASE MyDb;
USE MyDb;
CREATE TABLE dept(
    deptNo INT PRIMARY KEY,
    deptName VARCHAR(32)
);
CREATE TABLE staff(
    staffNo INT PRIMARY KEY,
    staffName VARCHAR(32),
    gender CHAR(1),
    dob date,
    salary numeric(8,2),
    deptNo INT,
    CONSTRAINT FK_staff_deptNo FOREIGN KEY(deptNo) REFERENCES dept(deptNo)
);
# 结束

1.4 CHECK约束

表products的结构如下：

字段名称 数据类型 备注
pid char(10) 产品户ID,主码
name varchar(32) 产品名称
brand char(10) 品牌，只能是('A','B')中的某一个
price int 价格，必须>0

请在数据库MyDb中创建表products，并分别实现对品牌和价格的约束，两个CHECK约束的名称分别为CK_products_brand和CK_products_price，主码约束不要显示命名。（提示：如果数据库MyDb不存在，请首先创建它，并将它作为工作数据库。）

# 请在以下空白处填写完成任务的语句，空白行可通过回车换行添加。
# 你也可以在命令行窗口完成任务，不过，在命令行键入的语句不会被保存。
# CREATE DATABASE MyDb;
USE MyDb;
CREATE TABLE products(
    pid char(10) PRIMARY KEY,
    name VARCHAR(32),
    brand CHAR(10) CONSTRAINT CK_products_brand CHECK(brand in ('A','B')),
    price INT CONSTRAINT CK_products_price CHECK(price>0)
);
# 结束

1.5 DEFAULT约束

表hr的结构如下：

字段名称 数据类型 备注
id char(10) 工号,主码
name varchar(32) 姓名,不允许为空值
mz char(16) 民族, 缺省值为“汉族”

请在数据库MyDb中创建表hr，并实现name列的NOT NULL约束和mz列的Default约束（别忘了主码约束）

# 请在以下空白处填写完成任务的语句，空白行可通过回车换行添加。
# 你也可以在命令行窗口完成任务，不过，在命令行键入的语句不会被保存。
USE MyDb;
CREATE TABLE hr(
    id CHAR(10) PRIMARY KEY,
    name VARCHAR(32) NOT NULL,
    mz CHAR(16) DEFAULT "汉族"
);
# 结束

1.6 UNIQUE约束

表s的结构如下：

字段名称 数据类型 备注
sno char(10) 学号,主码
name varchar(32) 姓名,不允许为空值
ID char(18) 身份证号, 不允许有两个相同的身份证号

请在数据库MyDb中创建表s，并实现相关约束（主码，NOT NULL和Unique约束）。 注意表名s是小写的，列名ID是全大写的。如果没有数据库MyDb,你需要创建它，并使其成为当前工作数据库。

# 请在以下空白处填写完成任务的语句，空白行可通过回车换行添加。
# 你也可以在命令行窗口完成任务，不过，在命令行键入的语句不会被保存。
USE MyDb;
CREATE TABLE s(
    sno CHAR(10) PRIMARY KEY,
    name VARCHAR(32) NOT NULL,
    ID CHAR(18) UNIQUE
);
# 结束

二、实训2 表结构与完整性的修改(ALTER)

2.1 修改表名

数据库TestDb1中有表your_table，请根据提示，在右侧代码文件编辑窗中添加恰当的语句，将表名your_table更改为my_table。

USE TestDb1;

#请在以下空白处添加恰当的语句，将表名your_table更改为my_table:
ALTER TABLE your_table rename my_table;

2.2 添加与删除字段

假设数据库MyDb中有表order(订单)和orderDetail(订单明细) 等表，两表的结构分别如下：

order表

字段名称 数据类型 备注
orderNo char(12) 订单号，主码
orderDate date 订购日期
customerNo char(12) 客户编号，外码，与customer.customerNo对应
employeeNo char(12) 雇员工号，外码，与employee.employeeNo对应

orderDetail表

字段名称 数据类型 备注
orderNo char(12) 订单号，主属性，外码，与order.orderNo对应
productNo char(12) 产品编号，主属性，外码，与product.productNo对应
quantityOrdered int 订购数量
orderDate date 订购日期

注：表orderDetail的主码由(orderNo,productNo)组成

编程的任务是对orderDetail表进行修改：

  1. orderDetail表的orderDate列明显多余，因为同一订单中的每一笔交易都发生在同一天，这个日期在订单主体表order中已有记录，请删除列orderDate。
  2. 产品的单价是订单明细需要记录的内容，请在orderDetail中添加列unitPrice以记录产品的单价:
字段名称 数据类型 备注
unitPrice numeric(10,2) 产品的成交单价

请根据提示，在右侧代码文件编辑窗中添加恰当的语句，实现上述编程任务。

use MyDb;

#请在以下空白处添加适当的SQL代码，实现编程要求
#语句1：删除表orderDetail中的列orderDate
ALTER TABLE orderDetail DROP orderDate;
#语句2：添加列unitPrice
ALTER TABLE orderDetail ADD unitPrice numeric(10,2);

2.3 修改字段

数据库MyDb中有表addressBook(通信录)，结构如下：

字段名称 数据类型 备注
serialNo int 自动编号，主码
name char(32) 姓名
company char(32) 工作单位
position char(10) 职位
workPhone char(16) 办公电话
mobile char(11) 手机
QQ int QQ号
weixin char(12) 微信号

当初创建表的语句如下：

create table addressBook(
    serialNo int auto_increment primary key,
    name char(32),
    company char(32),
    position char(10),
    workPhone char(16),
    mobile char(11),
    QQ int,
    weixin char(12)
);

你的编程任务是对表addressBook作以下修改：

  1. 将QQ号的数据类型改为char(12);
  2. 将列名weixin改为wechat。

请根据提示，在右侧代码文件test3.sql编辑窗中添加恰当的语句，实现上述编程任务。

use MyDb;

#请在以下空白处添加适当的SQL语句，实现编程要求
ALTER TABLE addressBook MODIFY QQ CHAR(12);
ALTER TABLE addressBook RENAME COLUMN weixin TO wechat;

2.4 添加、删除与修改约束

数据库MyDb中有以下两表：

Dept(部门)

字段名称 数据类型 备注
deptNo INT 部门号，主键
deptName VARCHAR(32) 部门名称，不同部门不允许重名
tel char(11) 部门电话
mgrStaffNo int 部门经理的工号，外码

Staff(职工)

字段名称 数据类型 备注
staffNo INT 工号，主键
staffName VARCHAR(32) 职工姓名
gender CHAR(1) 性别，取值范围：F-女，M-男
dob date 出生日期
Salary numeric(8,2) 工资
dept INT 部门号,外键

现通过以下语句，完成了两表的基础创建工作(部分约束没有实现):

create table Dept(
    deptNo int primary key,
    deptName varchar(32),
    tel char(11),
    mgrStaffNo int
);
create table Staff(
    staffNo int,
    staffName varchar(32),
    gender char(1),
    dob date,
    salary numeric(8,2),
    dept int
);

请在右侧代码编辑窗对应位置写出适当的语句，完成以下工作： (1) 为表Staff添加主码; (2) Dept.mgrStaffNo是外码，对应的主码是Staff.staffNo,请添加这个外码，名字为FK_Dept_mgrStaffNo; (3) Staff.dept是外码，对应的主码是Dept.deptNo. 请添加这个外码，名字为FK_Staff_dept; (4) 为表Staff添加check约束，规则为：gender的值只能为F或M；约束名为CK_Staff_gender; (5) 为表Dept添加unique约束：deptName不允许重复。约束名为UN_Dept_deptName.

use MyDb;
#请在以下空白处填写适当的诘句，实现编程要求。
#(1) 为表Staff添加主码
ALTER TABLE Staff ADD PRIMARY KEY(staffNo);
#(2) Dept.mgrStaffNo是外码，对应的主码是Staff.staffNo,请添加这个外码，名字为FK_Dept_mgrStaffNo:
ALTER TABLE Dept ADD CONSTRAINT FK_Dept_mgrStaffNo FOREIGN KEY(mgrStaffNo) REFERENCES Staff(staffNo);
#(3) Staff.dept是外码，对应的主码是Dept.deptNo. 请添加这个外码，名字为FK_Staff_dept:
ALTER TABLE Staff ADD CONSTRAINT FK_Staff_dept FOREIGN KEY(dept) REFERENCES Dept(deptNo);
#(4) 为表Staff添加check约束，规则为：gender的值只能为F或M；约束名为CK_Staff_gender:
ALTER TABLE Staff ADD CONSTRAINT CK_Staff_gender CHECK(gender in('F','M'));
#(5) 为表Dept添加unique约束：deptName不允许重复。约束名为UN_Dept_deptName：
ALTER TABLE Dept ADD CONSTRAINT UN_Dept_deptName UNIQUE(deptName);

三、实训3 数据查询(Select)之一

3.1 查询客户主要信息

本实训采用的是某银行的一个金融场景应用的模拟数据库，数据库中表，表结构以及所有字段的说明如下：

表1 client(客户表)

字段名称 数据类型 约束 说明
c_id INTEGER PRIMARY KEY 客户编号
c_name VARCHAR(100) NOT NULL 客户名称
c_mail CHAR(30) UNIQUE 客户邮箱
c_id_card CHAR(20) UNIQUE NOT NULL 客户身份证
c_phone CHAR(20) UNIQUE NOT NULL 客户手机号
c_password CHAR(20) NOT NULL 客户登录密码

表2 bank_card(银行卡)

字段名称 数据类型 约束 说明
b_number CHAR(30) PRIMARY KEY 银行卡号
b_type CHAR(20) 无 银行卡类型(储蓄卡/信用卡)
b_c_id INTEGER NOT NULL FOREIGN KEY 所属客户编号,引用自client表的c_id字段。
b_balance NUMERIC(10,2) NOT NULL 余额,信用卡余额系指已透支的金额

表3 finances_product(理财产品表)

字段名称 数据类型 约束 说明
p_name VARCHAR(100) NOT NULL 产品名称
p_id INTEGER PRIMARY KEY 产品编号
p_description VARCHAR(4000) 无 产品描述
p_amount INTEGER 无 购买金额
p_year INTEGER 无 理财年限

表4 insurance(保险表)

字段名称 数据类型 约束 说明
i_name VARCHAR(100) NOT NULL 保险名称
i_id INTEGER PRIMARY KEY 保险编号
i_amount INTEGER 无 保险金额
i_person CHAR(20) 无 适用人群
i_year INTEGER 无 保险年限
i_project VARCHAR(200) 无 保障项目

表5 fund(基金表)

字段名称 数据类型 约束 说明
f_name VARCHAR(100) NOT NULL 基金名称
f_id INTEGER PRIMARY KEY 基金编号
f_type CHAR(20) 无 基金类型
f_amount INTEGER 无 基金金额
risk_level CHAR(20) NOT NULL 风险等级
f_manager INTEGER NOT NULL 基金管理者

表6 property(资产表)

字段名称 数据类型 约束 说明
pro_id INTEGER PRIMARY KEY 资产编号
pro_c_id INTEGER NOT NULL FOREIGN KEY
pro_pif_id INTEGER NOT NULL 业务约束
pro_type INTEGER NOT NULL 商品类型:1表示理财产品;2表示保险;3表示基金
pro_status CHAR(20) 无 商品状态:'可用','冻结'
pro_quantity INTEGER 无 商品数量
pro_income INTEGER 无 商品收益
pro_purchase_time DATE 无 购买时间

请用一条SQL语句完成以下查询任务： 查询所有客户的名称、手机号和邮箱信息。查询结果按照客户编号排序。

-- 1) 查询所有客户的名称、手机号和邮箱信息。查询结果按照客户编号排序。
--    请用一条SQL语句实现该查询：
SELECT c_name,c_phone,c_mail
FROM client
ORDER BY c_id;
/*  end  of  your code  */

3.2 邮箱为null的客户

请用一条SQL语句完成以下查询任务：
查询客户表(client)中没有填写邮箱信息(邮箱字段值为null)的客户的编号、名称、身份证号、手机号。

-- 2) 查询客户表(client)中邮箱信息为null的客户的编号、名称、身份证号、手机号。
--    请用一条SQL语句实现该查询：

SELECT c_id,c_name,c_id_card,c_phone
FROM client
WHERE c_mail IS NULL;
/*  end  of  your code  */

3.3 既买了保险又买了基金的客户

请用一条SQL语句完成以下查询任务： 查询既买了保险又买了基金的客户的名称、邮箱和电话,结果依客户编号排序。

-- 3) 查询既买了保险又买了基金的客户的名称、邮箱和电话。结果依c_id排序
-- 请用一条SQL语句实现该查询：
SELECT c_name,c_mail,c_phone
FROM client
WHERE c_id IN(SELECT pro_c_id FROM property WHERE pro_type=2)
AND c_id IN(SELECT pro_c_id FROM property WHERE pro_type=3)
ORDER BY c_id;
/*  end  of  your code  */

3.4 办理了储蓄卡的客户信息

请用一条SQL语句完成以下查询任务： 查询办理了储蓄卡的客户名称、手机号和银行卡号。注意一个客户在本行可能不止一张储蓄卡，应全列出。查询结果结果依客户编号排序。

-- 4) 查询办理了储蓄卡的客户名称、手机号、银行卡号。 查询结果结果依客户编号排序。
--    请用一条SQL语句实现该查询：
SELECT c_name,c_phone,b_number
FROM client,bank_card
WHERE c_id=b_c_id AND b_type="储蓄卡"
ORDER BY c_id;
/*  end  of  your code  */

3.5 每份金额在30000～50000之间的理财产品

请用一条SQL语句完成以下查询任务： 查询理财产品中每份金额在30000～50000之间的理财产品的编号,每份金额，理财年限，并按金额升序排序，金额相同的按照理财年限降序排序。

-- 5) 查询理财产品中每份金额在30000～50000之间的理财产品的编号,每份金额，理财年限，并按照金额升序排序，金额相同的按照理财年限降序排序。
--    请用一条SQL语句实现该查询：
SELECT p_id,p_amount,p_year
FROM finances_product
WHERE p_amount BETWEEN 30000 AND 50000
ORDER BY p_amount,p_year DESC;
/*  end  of  your code  */

3.6 商品收益的众数

请用一条SQL语句完成以下查询任务： 众数是一组数据中出现次数最多的数值，有时众数在一组数中会有好几个。查询资产表中所有资产记录里商品收益的众数和它出现的次数，出现的次数命名为presence。

-- 6) 查询资产表中所有资产记录里商品收益的众数和它出现的次数。
--    请用一条SQL语句实现该查询：
SELECT A.pro_income,COUNT(*) presence
FROM property A
GROUP BY A.pro_income
HAVING presence>=ALL(SELECT COUNT(*)
                    FROM property B
                    GROUP BY B.pro_income);
/*  end  of  your code  */

3.7 未购买任何理财产品的武汉居民

请用一条SQL语句完成以下查询任务： 已知身份证前6位表示居民地区，其中4201开头表示湖北省武汉市。查询身份证隶属武汉市没有买过任何理财产品的客户的名称、电话号、邮箱。依客户编号排序

-- 7) 查询身份证隶属武汉市没有买过任何理财产品的客户的名称、电话号、邮箱。
--    请用一条SQL语句实现该查询：
SELECT c_name,c_phone,c_mail
FROM client
WHERE c_id_card LIKE '4201%' 
AND c_id NOT IN(SELECT pro_c_id
                FROM property B
                WHERE B.pro_type=1)
ORDER BY c_id;
/*  end  of  your code  */

3.8 持有两张信用卡的用户

请用一条SQL语句完成以下查询任务： 查询持有两张(含）以上信用卡的用户的名称、身份证号、手机号。查询结果依客户编号排序

-- 8) 查询持有两张(含）以上信用卡的用户的名称、身份证号、手机号。
--    请用一条SQL语句实现该查询：
SELECT c_name,c_id_card,c_phone
FROM client,bank_card
WHERE c_id=b_c_id AND b_type="信用卡"
GROUP BY c_id
HAVING COUNT(*)>=2
ORDER BY c_id;
/*  end  of  your code  */

3.9 购买了货币型基金的客户信息

请用一条SQL语句完成以下查询任务： 查询购买了货币型(f_type='货币型')基金的用户的名称、电话号、邮箱。依客户编号排序

-- 9) 查询购买了货币型(f_type='货币型')基金的用户的名称、电话号、邮箱。
--   请用一条SQL语句实现该查询：
SELECT c_name,c_phone,c_mail
FROM client
WHERE EXISTS(
    SELECT *
    FROM property
    WHERE c_id=pro_c_id AND pro_type=3 AND EXISTS(
        SELECT *
        FROM fund
        WHERE f_type='货币型' AND f_id=pro_pif_id
    )
)
ORDER BY c_id;
/*  end  of  your code  */

3.10 投资总收益前三名的客户

请用一条SQL语句完成以下查询任务： 查询当前总的可用资产收益(被冻结的资产除外)前三名的客户的名称、身份证号及其总收益，按收益降序输出，总收益命名为total_income。不考虑并列排名情形。

-- 10) 查询当前总的可用资产收益(被冻结的资产除外)前三名的客户的名称、身份证号及其总收益，按收益降序输出，总收益命名为total_income。不考虑并列排名情形。
--    请用一条SQL语句实现该查询：
SELECT c_name,c_id_card,S1.total_income
FROM client,(SELECT pro_c_id,SUM(pro_income) total_income
             FROM property
             WHERE pro_status="可用"
             GROUP BY pro_c_id) S1
WHERE c_id=S1.pro_c_id
ORDER BY S1.total_income DESC
LIMIT 3;
/*  end  of  your code  */

3.11 黄姓客户持卡数量

请用一条SQL语句完成以下查询任务： 给出黄姓用户的编号、名称、办理的银行卡的数量(没有办卡的卡数量计为0). 按办理银行卡数量降序输出,持卡数量相同的,依客户编号排序。

-- 11) 给出黄姓用户的编号、名称、办理的银行卡的数量(没有办卡的卡数量计为0),持卡数量命名为number_of_cards,
--     按办理银行卡数量降序输出,持卡数量相同的,依客户编号排序。
-- 请用一条SQL语句实现该查询：
SELECT c_id,c_name,COUNT(b_number) number_of_cards
FROM client LEFT OUTER JOIN bank_card ON(client.c_id=bank_card.b_c_id)
WHERE c_name LIKE '黄%'
GROUP BY c_id
ORDER BY number_of_cards DESC,c_id;
/*  end  of  your code  */

3.12 客户理财、保险与基金投资总额

请用一条SQL语句完成以下查询任务： 综合客户表(client)、资产表(property)、理财产品表(finances_product)、保险表(insurance)和基金表(fund)，列出客户的名称、身份证号以及投资总金额（即投资本金，每笔投资金额=商品数量*该产品每份金额)，注意投资金额按类型需查询不同的表， 投资总金额是客户购买的各类(理财,保险,基金)资产投资金额的总和，总金额命名为total_amount。查询结果按总金额降序排序。

 -- 12) 综合客户表(client)、资产表(property)、理财产品表(finances_product)、保险表(insurance)和
 --     基金表(fund)，列出客户的名称、身份证号以及投资总金额（即投资本金，
 --     每笔投资金额=商品数量*该产品每份金额)，注意投资金额按类型需要查询不同的表，
 --     投资总金额是客户购买的各类资产(理财,保险,基金)投资金额的总和，总金额命名为total_amount。
 --     查询结果按总金额降序排序。
 -- 请用一条SQL语句实现该查询：
SELECT c_name,c_id_card,coalesce(SUM(final.amount_pif),0) total_amount
FROM client C1
LEFT OUTER JOIN
((SELECT C2.c_id c_id_p,SUM(pro_quantity*p_amount) amount_p
 FROM client C2,finances_product,property
 WHERE pro_c_id=C2.c_id AND p_id=pro_pif_id AND pro_type=1
 GROUP BY c_id_p)
 UNION ALL
(SELECT C3.c_id c_id_i,SUM(pro_quantity*i_amount) amount_i
 FROM client C3,insurance,property
 WHERE pro_c_id=C3.c_id AND i_id=pro_pif_id AND pro_type=2
 GROUP BY c_id_i)
 UNION ALL
(SELECT C4.c_id c_id_f,SUM(pro_quantity*f_amount) amount_f
 FROM client C4,fund,property
 WHERE pro_c_id=C4.c_id AND f_id=pro_pif_id AND pro_type=3
 GROUP BY c_id_f)) AS final(c_id_pif,amount_pif)
ON(C1.c_id=final.c_id_pif)
GROUP BY C1.c_id
ORDER BY total_amount DESC;
/*  end  of  your code  */

3.13 客户总资产

请用一条SQL语句完成以下查询任务：
综合客户表(client)、资产表(property)、理财产品表(finances_product)、保险表(insurance)、基金表(fund)，列出所有客户的编号、名称和总资产，总资产命名为total_property。总资产为储蓄卡总余额，投资总额，投资总收益的和，再扣除信用卡透支的总金额(信用卡余额即为透支金额)。客户总资产包括被冻结的资产。

-- 13) 综合客户表(client)、资产表(property)、理财产品表(finances_product)、
--     保险表(insurance)、基金表(fund)和投资资产表(property)，
--     列出所有客户的编号、名称和总资产，总资产命名为total_property。
--     总资产为储蓄卡余额，投资总额，投资总收益的和，再扣除信用卡透支的金额
--     (信用卡余额即为透支金额)。客户总资产包括被冻结的资产。
--    请用一条SQL语句实现该查询：
SELECT C0.c_id,C0.c_name,coalesce(debit_balance+total_amount+total_income-credit_balance,0) total_property
FROM client C0,
(
    SELECT c_id,coalesce(SUM(final.amount_pif),0) total_amount
    FROM client C1
    LEFT OUTER JOIN
    ((SELECT C2.c_id c_id_p,SUM(pro_quantity*p_amount) amount_p
    FROM client C2,finances_product,property
    WHERE pro_c_id=C2.c_id AND p_id=pro_pif_id AND pro_type=1
    GROUP BY c_id_p)
    UNION ALL
    (SELECT C3.c_id c_id_i,SUM(pro_quantity*i_amount) amount_i
    FROM client C3,insurance,property
    WHERE pro_c_id=C3.c_id AND i_id=pro_pif_id AND pro_type=2
    GROUP BY c_id_i)
    UNION ALL
    (SELECT C4.c_id c_id_f,SUM(pro_quantity*f_amount) amount_f
    FROM client C4,fund,property
    WHERE pro_c_id=C4.c_id AND f_id=pro_pif_id AND pro_type=3
    GROUP BY c_id_f)) AS final(c_id_pif,amount_pif)
    ON(C1.c_id=final.c_id_pif)
    GROUP BY C1.c_id
) AS Amount(c_id,total_amount),
(
    SELECT c_id,coalesce(SUM(b_balance),0) debit_balance
    FROM client LEFT OUTER JOIN
    (
        SELECT b_c_id,b_balance
        FROM bank_card
        WHERE b_type='储蓄卡'
    ) AS balance0(b_c_id,b_balance)
    ON(c_id=b_c_id)
    GROUP BY c_id
) AS Debit(c_id,debit_balance),
(
    SELECT c_id,coalesce(SUM(b_balance),0) credit_balance
    FROM client LEFT OUTER JOIN
    (
        SELECT b_c_id,b_balance
        FROM bank_card
        WHERE b_type='信用卡'
    ) AS balance1(b_c_id,b_balance)
    ON(c_id=b_c_id)
    GROUP BY c_id
) AS Credit(c_id,credit_balance),
(
    SELECT c_id,coalesce(SUM(pro_income),0) total_income
    FROM client LEFT OUTER JOIN property ON(c_id=pro_c_id)
    GROUP BY c_id
) AS Income(c_id,total_income)
WHERE C0.c_id=Amount.c_id AND C0.c_id=Debit.c_id AND C0.c_id=Credit.c_id AND C0.c_id=Income.c_id
ORDER BY c_id
/*  end  of  your code  */

3.14 第N高问题

请用一条SQL语句实现本询要求： 查询每份保险金额第4高保险产品的编号和保险金额。在数字序列8000,8000,7000,7000,6000中，两个8000均为第1高，两个7000均为第2高,6000为第3高。

-- 14) 查询每份保险金额第4高保险产品的编号和保险金额。
--     在数字序列8000,8000,7000,7000,6000中，
--     两个8000均为第1高，两个7000均为第2高,6000为第3高。
-- 请用一条SQL语句实现该查询：
SELECT i_id,i_amount
FROM insurance
WHERE i_amount = (
    SELECT DISTINCT i_amount
    FROM insurance
    ORDER BY i_amount DESC
    LIMIT 1 OFFSET 3
)
ORDER BY i_id;
/*  end  of  your code  */

3.15 基金收益两种方式排名

排名问题：
排名是数据库应用中的一类经典问题，实际又根据具体需求细分为3种场景：

  • 连续排名，同数不同名次，无并列名次。例如300、200、200、150、150、100的排名结果为1, 2, 3, 4, 5, 6。这种排名类似于编号；
  • 同数同名次，总排名不连续。例如300、200、200、150、150、100的排名结果为1, 2, 2, 4, 4, 6；
  • 同数同名次，总排名连续。例如300、200、200、150、150、100的排名结果为1, 2, 2, 3, 3, 4。

不同的需求对应着不同的查询策略。本关的任务是实现后两种方式的排名。

编程要求：
请分别用两条SQL语句实现下述两个任务： 查询资产表中客户编号，客户基金投资总收益,基金投资总收益的排名(从高到低排名)。总收益相同时名次亦相同(即并列名次)。总收益命名为total_revenue, 名次命名为rank。第一条SQL语句实现全局名次不连续的排名，第二条SQL语句实现全局名次连续的排名。不管哪种方式排名，收益相同时,客户编号小的排在前

-- 15) 查询资产表中客户编号，客户基金投资总收益,基金投资总收益的排名(从高到低排名)。
--     总收益相同时名次亦相同(即并列名次)。总收益命名为total_revenue, 名次命名为rank。
--     第一条SQL语句实现全局名次不连续的排名，
--     第二条SQL语句实现全局名次连续的排名。

-- (1) 基金总收益排名(名次不连续)
SELECT pro_c_id,total_revenue,rank0 AS 'rank'
FROM(
    SELECT pro_c_id,total_revenue,if(total_revenue=@income,@curRank,@curRank:=@num) AS rank0,@income:=total_revenue,@num:=@num+1
    FROM (
        SELECT pro_c_id,SUM(pro_income) total_revenue
        FROM property
        WHERE pro_type=3
        GROUP BY pro_c_id
        ORDER BY total_revenue DESC,pro_c_id
    ) AS Income,(SELECT @curRank:=0,@income:=NULL,@num:=1) r
) AS r1;
-- (2) 基金总收益排名(名次连续)
SELECT pro_c_id,total_revenue,rank0 AS 'rank'
FROM(
    SELECT pro_c_id,total_revenue,if(total_revenue=@income,@curRank,@curRank:=@curRank+1) AS rank0,@income:=total_revenue
    FROM (
        SELECT pro_c_id,SUM(pro_income) total_revenue
        FROM property
        WHERE pro_type=3
        GROUP BY pro_c_id
        ORDER BY total_revenue DESC,pro_c_id
    ) AS Income,(SELECT @curRank:=0,@income:=NULL) r
) AS r1;
/*  end  of  your code  */

3.16 持有完全相同基金组合的客户

请用一条SQL语句实现查询任务： 查询持有相同基金组合的客户对，如编号为A的客户持有的基金，编号为B的客户也持有，反过来，编号为B的客户持有的基金，编号为A的客户也持有，则(A,B)即为持有相同基金组合的二元组，请列出这样的客户对。为避免过多的重复，如果(1,2)为满足条件的元组，则不必显示(2,1)，即只显示 编号小者在前的那一对，这一组客户编号分别命名为c_id1,c_id2

-- 16) 查询持有相同基金组合的客户对，如编号为A的客户持有的基金，编号为B的客户也持有，反过来，编号为B的客户持有的基金，编号为A的客户也持有，则(A,B)即为持有相同基金组合的二元组，请列出这样的客户对。为避免过多的重复，如果(1,2)为满足条件的元组，则不必显示(2,1)，即只显示编号小者在前的那一对，这一组客户编号分别命名为c_id1,c_id2。

-- 请用一条SQL语句实现该查询：
SELECT c_id1,c_id2
FROM (
    SELECT pro_c_id,GROUP_CONCAT(
        pro_pif_id
        ORDER BY pro_pif_id
        SEPARATOR ' ') as pifset
    FROM property
    WHERE pro_type=3
    GROUP BY pro_c_id
) AS c1(c_id1,pifset1),
(
    SELECT pro_c_id,GROUP_CONCAT(
        pro_pif_id
        ORDER BY pro_pif_id
        SEPARATOR ' ') as pifset
    FROM property
    WHERE pro_type=3
    GROUP BY pro_c_id
)AS c2(c_id2,pifset2)
WHERE pifset1=pifset2 AND c_id1<c_id2;
/*  end  of  your code  */

3.17 购买基金的高峰期

请用一条SQL语句实现查询任务： 查询2022年2月购买基金的高峰期。至少连续三个交易日，所有投资者购买基金的总金额超过100万(含)，则称这段连续交易日为投资者购买基金的高峰期。只有交易日才能购买基金,但不能保证每个交易日都有投资者购买基金。2022年春节假期之后的第1个交易日为2月7日,周六和周日是非交易日，其余均为交易日。请列出高峰时段的日期和当日基金的总购买金额，按日期顺序排序。总购买金额命名为total_amount。

（破防！切身感受到了课上讲的和实际要你做的之间的天壤之别）

-- 17 查询2022年2月购买基金的高峰期。至少连续三个交易日，所有投资者购买基金的总金额超过100万(含)，则称这段连续交易日为投资者购买基金的高峰期。只有交易日才能购买基金,但不能保证每个交易日都有投资者购买基金。2022年春节假期之后的第1个交易日为2月7日,周六和周日是非交易日，其余均为交易日。请列出高峰时段的日期和当日基金的总购买金额，按日期顺序排序。总购买金额命名为total_amount。
--    请用一条SQL语句实现该查询：
/*交易日:2.7-2.11,2.14-2.18,2.21-2.25,2.28共16天*/
/*遍历全部3个连续交易日,需要遍历14次*/
SELECT pro_purchase_time,total_amount
FROM(
    SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
    FROM property,fund
    WHERE pro_type=3 AND pro_pif_id=f_id AND
    pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
    GROUP BY pro_purchase_time
    ORDER BY pro_purchase_time
    LIMIT 3 OFFSET 0
) AS amount0(pro_purchase_time,total_amount)
WHERE 3 IN (SELECT `rank` FROM(
    SELECT pro_purchase_time,total_amount,@curRank0:=@curRank0+1 AS `rank`
    FROM(
        SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
        FROM property,fund
        WHERE pro_type=3 AND pro_pif_id=f_id AND
        pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
        GROUP BY pro_purchase_time
        ORDER BY pro_purchase_time
        LIMIT 3 OFFSET 0
    ) AS amount(pro_purchase_time,total_amount),(SELECT @curRank0:=0) AS r
    WHERE total_amount>=1000000
) AS amount1(pro_purchase_time,total_amount,`rank`))

UNION

SELECT pro_purchase_time,total_amount
FROM(
    SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
    FROM property,fund
    WHERE pro_type=3 AND pro_pif_id=f_id AND
    pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
    GROUP BY pro_purchase_time
    ORDER BY pro_purchase_time
    LIMIT 3 OFFSET 1
) AS amount0(pro_purchase_time,total_amount)
WHERE 3 IN (SELECT `rank` FROM(
    SELECT pro_purchase_time,total_amount,@curRank1:=@curRank1+1 AS `rank`
    FROM(
        SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
        FROM property,fund
        WHERE pro_type=3 AND pro_pif_id=f_id AND
        pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
        GROUP BY pro_purchase_time
        ORDER BY pro_purchase_time
        LIMIT 3 OFFSET 1
    ) AS amount(pro_purchase_time,total_amount),(SELECT @curRank1:=0) AS r
    WHERE total_amount>=1000000
) AS amount1(pro_purchase_time,total_amount,`rank`))

UNION

SELECT pro_purchase_time,total_amount
FROM(
    SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
    FROM property,fund
    WHERE pro_type=3 AND pro_pif_id=f_id AND
    pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
    GROUP BY pro_purchase_time
    ORDER BY pro_purchase_time
    LIMIT 3 OFFSET 2
) AS amount0(pro_purchase_time,total_amount)
WHERE 3 IN (SELECT `rank` FROM(
    SELECT pro_purchase_time,total_amount,@curRank2:=@curRank2+1 AS `rank`
    FROM(
        SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
        FROM property,fund
        WHERE pro_type=3 AND pro_pif_id=f_id AND
        pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
        GROUP BY pro_purchase_time
        ORDER BY pro_purchase_time
        LIMIT 3 OFFSET 2
    ) AS amount(pro_purchase_time,total_amount),(SELECT @curRank2:=0) AS r
    WHERE total_amount>=1000000
) AS amount1(pro_purchase_time,total_amount,`rank`))

UNION

SELECT pro_purchase_time,total_amount
FROM(
    SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
    FROM property,fund
    WHERE pro_type=3 AND pro_pif_id=f_id AND
    pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
    GROUP BY pro_purchase_time
    ORDER BY pro_purchase_time
    LIMIT 3 OFFSET 3
) AS amount0(pro_purchase_time,total_amount)
WHERE 3 IN (SELECT `rank` FROM(
    SELECT pro_purchase_time,total_amount,@curRank3:=@curRank3+1 AS `rank`
    FROM(
        SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
        FROM property,fund
        WHERE pro_type=3 AND pro_pif_id=f_id AND
        pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
        GROUP BY pro_purchase_time
        ORDER BY pro_purchase_time
        LIMIT 3 OFFSET 3
    ) AS amount(pro_purchase_time,total_amount),(SELECT @curRank3:=0) AS r
    WHERE total_amount>=1000000
) AS amount1(pro_purchase_time,total_amount,`rank`))

UNION

SELECT pro_purchase_time,total_amount
FROM(
    SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
    FROM property,fund
    WHERE pro_type=3 AND pro_pif_id=f_id AND
    pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
    GROUP BY pro_purchase_time
    ORDER BY pro_purchase_time
    LIMIT 3 OFFSET 4
) AS amount0(pro_purchase_time,total_amount)
WHERE 3 IN (SELECT `rank` FROM(
    SELECT pro_purchase_time,total_amount,@curRank4:=@curRank4+1 AS `rank`
    FROM(
        SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
        FROM property,fund
        WHERE pro_type=3 AND pro_pif_id=f_id AND
        pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
        GROUP BY pro_purchase_time
        ORDER BY pro_purchase_time
        LIMIT 3 OFFSET 4
    ) AS amount(pro_purchase_time,total_amount),(SELECT @curRank4:=0) AS r
    WHERE total_amount>=1000000
) AS amount1(pro_purchase_time,total_amount,`rank`))

UNION

SELECT pro_purchase_time,total_amount
FROM(
    SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
    FROM property,fund
    WHERE pro_type=3 AND pro_pif_id=f_id AND
    pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
    GROUP BY pro_purchase_time
    ORDER BY pro_purchase_time
    LIMIT 3 OFFSET 5
) AS amount0(pro_purchase_time,total_amount)
WHERE 3 IN (SELECT `rank` FROM(
    SELECT pro_purchase_time,total_amount,@curRank5:=@curRank5+1 AS `rank`
    FROM(
        SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
        FROM property,fund
        WHERE pro_type=3 AND pro_pif_id=f_id AND
        pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
        GROUP BY pro_purchase_time
        ORDER BY pro_purchase_time
        LIMIT 3 OFFSET 5
    ) AS amount(pro_purchase_time,total_amount),(SELECT @curRank5:=0) AS r
    WHERE total_amount>=1000000
) AS amount1(pro_purchase_time,total_amount,`rank`))

UNION

SELECT pro_purchase_time,total_amount
FROM(
    SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
    FROM property,fund
    WHERE pro_type=3 AND pro_pif_id=f_id AND
    pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
    GROUP BY pro_purchase_time
    ORDER BY pro_purchase_time
    LIMIT 3 OFFSET 6
) AS amount0(pro_purchase_time,total_amount)
WHERE 3 IN (SELECT `rank` FROM(
    SELECT pro_purchase_time,total_amount,@curRank6:=@curRank6+1 AS `rank`
    FROM(
        SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
        FROM property,fund
        WHERE pro_type=3 AND pro_pif_id=f_id AND
        pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
        GROUP BY pro_purchase_time
        ORDER BY pro_purchase_time
        LIMIT 3 OFFSET 6
    ) AS amount(pro_purchase_time,total_amount),(SELECT @curRank6:=0) AS r
    WHERE total_amount>=1000000
) AS amount1(pro_purchase_time,total_amount,`rank`))

UNION

SELECT pro_purchase_time,total_amount
FROM(
    SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
    FROM property,fund
    WHERE pro_type=3 AND pro_pif_id=f_id AND
    pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
    GROUP BY pro_purchase_time
    ORDER BY pro_purchase_time
    LIMIT 3 OFFSET 7
) AS amount0(pro_purchase_time,total_amount)
WHERE 3 IN (SELECT `rank` FROM(
    SELECT pro_purchase_time,total_amount,@curRank7:=@curRank7+1 AS `rank`
    FROM(
        SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
        FROM property,fund
        WHERE pro_type=3 AND pro_pif_id=f_id AND
        pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
        GROUP BY pro_purchase_time
        ORDER BY pro_purchase_time
        LIMIT 3 OFFSET 7
    ) AS amount(pro_purchase_time,total_amount),(SELECT @curRank7:=0) AS r
    WHERE total_amount>=1000000
) AS amount1(pro_purchase_time,total_amount,`rank`))

UNION

SELECT pro_purchase_time,total_amount
FROM(
    SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
    FROM property,fund
    WHERE pro_type=3 AND pro_pif_id=f_id AND
    pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
    GROUP BY pro_purchase_time
    ORDER BY pro_purchase_time
    LIMIT 3 OFFSET 8
) AS amount0(pro_purchase_time,total_amount)
WHERE 3 IN (SELECT `rank` FROM(
    SELECT pro_purchase_time,total_amount,@curRank8:=@curRank8+1 AS `rank`
    FROM(
        SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
        FROM property,fund
        WHERE pro_type=3 AND pro_pif_id=f_id AND
        pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
        GROUP BY pro_purchase_time
        ORDER BY pro_purchase_time
        LIMIT 3 OFFSET 8
    ) AS amount(pro_purchase_time,total_amount),(SELECT @curRank8:=0) AS r
    WHERE total_amount>=1000000
) AS amount1(pro_purchase_time,total_amount,`rank`))

UNION

SELECT pro_purchase_time,total_amount
FROM(
    SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
    FROM property,fund
    WHERE pro_type=3 AND pro_pif_id=f_id AND
    pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
    GROUP BY pro_purchase_time
    ORDER BY pro_purchase_time
    LIMIT 3 OFFSET 9
) AS amount0(pro_purchase_time,total_amount)
WHERE 3 IN (SELECT `rank` FROM(
    SELECT pro_purchase_time,total_amount,@curRank9:=@curRank9+1 AS `rank`
    FROM(
        SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
        FROM property,fund
        WHERE pro_type=3 AND pro_pif_id=f_id AND
        pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
        GROUP BY pro_purchase_time
        ORDER BY pro_purchase_time
        LIMIT 3 OFFSET 9
    ) AS amount(pro_purchase_time,total_amount),(SELECT @curRank9:=0) AS r
    WHERE total_amount>=1000000
) AS amount1(pro_purchase_time,total_amount,`rank`))

UNION

SELECT pro_purchase_time,total_amount
FROM(
    SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
    FROM property,fund
    WHERE pro_type=3 AND pro_pif_id=f_id AND
    pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
    GROUP BY pro_purchase_time
    ORDER BY pro_purchase_time
    LIMIT 3 OFFSET 10
) AS amount0(pro_purchase_time,total_amount)
WHERE 3 IN (SELECT `rank` FROM(
    SELECT pro_purchase_time,total_amount,@curRank10:=@curRank10+1 AS `rank`
    FROM(
        SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
        FROM property,fund
        WHERE pro_type=3 AND pro_pif_id=f_id AND
        pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
        GROUP BY pro_purchase_time
        ORDER BY pro_purchase_time
        LIMIT 3 OFFSET 10
    ) AS amount(pro_purchase_time,total_amount),(SELECT @curRank10:=0) AS r
    WHERE total_amount>=1000000
) AS amount1(pro_purchase_time,total_amount,`rank`))

UNION

SELECT pro_purchase_time,total_amount
FROM(
    SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
    FROM property,fund
    WHERE pro_type=3 AND pro_pif_id=f_id AND
    pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
    GROUP BY pro_purchase_time
    ORDER BY pro_purchase_time
    LIMIT 3 OFFSET 11
) AS amount0(pro_purchase_time,total_amount)
WHERE 3 IN (SELECT `rank` FROM(
    SELECT pro_purchase_time,total_amount,@curRank11:=@curRank11+1 AS `rank`
    FROM(
        SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
        FROM property,fund
        WHERE pro_type=3 AND pro_pif_id=f_id AND
        pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
        GROUP BY pro_purchase_time
        ORDER BY pro_purchase_time
        LIMIT 3 OFFSET 11
    ) AS amount(pro_purchase_time,total_amount),(SELECT @curRank11:=0) AS r
    WHERE total_amount>=1000000
) AS amount1(pro_purchase_time,total_amount,`rank`))

UNION

SELECT pro_purchase_time,total_amount
FROM(
    SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
    FROM property,fund
    WHERE pro_type=3 AND pro_pif_id=f_id AND
    pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
    GROUP BY pro_purchase_time
    ORDER BY pro_purchase_time
    LIMIT 3 OFFSET 12
) AS amount0(pro_purchase_time,total_amount)
WHERE 3 IN (SELECT `rank` FROM(
    SELECT pro_purchase_time,total_amount,@curRank12:=@curRank12+1 AS `rank`
    FROM(
        SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
        FROM property,fund
        WHERE pro_type=3 AND pro_pif_id=f_id AND
        pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
        GROUP BY pro_purchase_time
        ORDER BY pro_purchase_time
        LIMIT 3 OFFSET 12
    ) AS amount(pro_purchase_time,total_amount),(SELECT @curRank12:=0) AS r
    WHERE total_amount>=1000000
) AS amount1(pro_purchase_time,total_amount,`rank`))

UNION

SELECT pro_purchase_time,total_amount
FROM(
    SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
    FROM property,fund
    WHERE pro_type=3 AND pro_pif_id=f_id AND
    pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
    GROUP BY pro_purchase_time
    ORDER BY pro_purchase_time
    LIMIT 3 OFFSET 13
) AS amount0(pro_purchase_time,total_amount)
WHERE 3 IN (SELECT `rank` FROM(
    SELECT pro_purchase_time,total_amount,@curRank13:=@curRank13+1 AS `rank`
    FROM(
        SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
        FROM property,fund
        WHERE pro_type=3 AND pro_pif_id=f_id AND
        pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
        GROUP BY pro_purchase_time
        ORDER BY pro_purchase_time
        LIMIT 3 OFFSET 13
    ) AS amount(pro_purchase_time,total_amount),(SELECT @curRank13:=0) AS r
    WHERE total_amount>=1000000
) AS amount1(pro_purchase_time,total_amount,`rank`))
/*  end  of  your code  */

3.18 至少有一张信用卡余额超过5000元的客户信息

请用一条SQL语句实现以下查询要求：
查询至少有一张信用卡余额超过5000元的客户编号，以及该客户持有的信用卡总余额，总余额命名为credit_card_amount。查询结果依客户编号排序。

（第17关快把人整死，第18关就这？）

 -- 18) 查询至少有一张信用卡余额超过5000元的客户编号，以及该客户持有的信用卡总余额，总余额命名为credit_card_amount。
--    请用一条SQL语句实现该查询：
SELECT A.b_c_id,SUM(b_balance) credit_card_amount
FROM bank_card A
WHERE b_type='信用卡' AND EXISTS(
    SELECT *
    FROM bank_card B
    WHERE A.b_c_id=B.b_c_id AND b_type='信用卡' AND b_balance>5000
)
GROUP BY A.b_c_id
ORDER BY A.b_c_id;
/*  end  of  your code  */

3.19 以日历表格式显示每日基金购买总金额

请用一条SQL语句完成以下查询任务： 以日历表格式列出2022年2月每周每个交易日基金购买总金额，输出格式如下：

week_of_trading Monday Tuesday Wednesday Thursday Friday
1
2
3
4

列表中第1列为周次，2022年2月7日(星期一)为当月的第1个交易日，这一周记为第1周次。注意显示结果并不需要画表格线，只需按这个格式输出结果即可了。

-- 19) 以日历表格式列出2022年2月每周每日基金购买总金额，输出格式如下：
-- week_of_trading Monday Tuesday Wednesday Thursday Friday
--               1
--               2    
--               3
--               4
--   请用一条SQL语句实现该查询：
SELECT week_of_trading,
    SUM(if(wd=2,total_amount,NULL)) Monday,
    SUM(if(wd=3,total_amount,NULL)) Tuesday,
    SUM(if(wd=4,total_amount,NULL)) Wednesday,
    SUM(if(wd=5,total_amount,NULL)) Thursday,
    SUM(if(wd=6,total_amount,NULL)) Friday
FROM(
        SELECT week(pro_purchase_time)-5 week_of_trading,dayofweek(pro_purchase_time) wd,total_amount
    FROM(
        SELECT pro_purchase_time,SUM(pro_quantity*f_amount) total_amount
        FROM property,fund
        WHERE pro_type=3 AND pro_pif_id=f_id AND
        pro_purchase_time>='2022-02-07' AND pro_purchase_time<='2022-02-28'
        GROUP BY pro_purchase_time
        ORDER BY pro_purchase_time
    ) R1(pro_purchase_time,total_amount)
) R2(week_of_trading,wd,total_amount)
GROUP BY week_of_trading;
/*  end  of  your code  */

四、实训4 数据查询(Select)之二

4.1 查询销售总额前三的理财产品

请用一条SQL语句完成以下查询任务：
查询2010年和2011年这两年每年销售总额前3名（如果有并列排名，则后续排名号跳过之前的并列排名个数，例如1、1、3）的统计年份（pyear）、销售总额排名值(rk)、理财产品编号(p_id)、销售总额(sumamount)。
注意结果输出要求：(1)按照年份升序排列，同一年份按照销售总额的排名值升序排列，如遇到并列排名则按照理财产品编号升序排列;(2)属性显示：统计年份（pyear）、销售总额排名值(rk)、理财产品编号(p_id)、销售总额(sumamount)（3）结果显示顺序：先按照统计年份（pyear）升序排,同一年份按照销售总额排名值（rk）升序排,同一排名值的按照理财产品编号（p_id ）升序排。

 -- 1) 查询销售总额前三的理财产品
--   请用一条SQL语句实现该查询：
SELECT pyear,rk,p_id,sumamount
FROM(
    SELECT pyear,rank()over(partition by pyear order by sumamount DESC) AS rk,p_id,sumamount
    FROM(
        SELECT p_id,SUM(pro_quantity*p_amount) sumamount,2010 pyear
        FROM property,finances_product
        WHERE pro_pif_id=p_id AND pro_type=1 AND year(pro_purchase_time)=2010
        GROUP BY p_id
        UNION
        SELECT p_id,SUM(pro_quantity*p_amount) sumamount,2011 pyear
        FROM property,finances_product
        WHERE pro_pif_id=p_id AND pro_type=1 AND year(pro_purchase_time)=2011
        GROUP BY p_id
    ) AS R1
) AS R2
WHERE rk<=3;
/*  end  of  your code  */

4.2 投资积极且偏好理财类产品的客户

请用一条SQL语句完成以下查询任务：
购买了3种（同一编号的理财产品记为一种）以上理财产品的客户被认为投资积极的客户，若该客户持有基金产品种类数（同一基金编号记为相同的基金产品种类）小于其持有的理财产品种类数，则认为该客户为投资积极且偏好理财产品的客户。查询所有此类客户的编号(pro_c_id)。 注意结果输出要求：按照客户编号的升序排列，且去除重复结果

  -- 2) 投资积极且偏好理财类产品的客户
--   请用一条SQL语句实现该查询：
SELECT P.pro_c_id
FROM(
  SELECT pro_c_id,COUNT(pro_pif_id) pnum
  FROM property
  WHERE pro_type=1
  GROUP BY pro_c_id
) P,(
  SELECT pro_c_id,COUNT(pro_pif_id) fnum
  FROM property
  WHERE pro_type=3
  GROUP BY pro_c_id
) F
WHERE P.pro_c_id=F.pro_c_id AND pnum>=3 AND pnum>fnum;
/*  end  of  your code  */

4.3 查询购买了所有畅销理财产品的客户

请用一条SQL语句完成以下查询任务：
若定义持有人数超过2的理财产品称为畅销理财产品。查询购买了所有畅销理财产品的客户编号(pro_c_id)。 注意结果输出要求：按照客户编号的升序排列，且去除重复结果。

（实训3做出阴影了，感觉实训4都还算简单）

   -- 3) 查询购买了所有畅销理财产品的客户
--   请用一条SQL语句实现该查询：
SELECT pro_c_id
FROM property A
WHERE NOT EXISTS(
   SELECT *
   FROM property B
   WHERE A.pro_c_id=B.pro_c_id AND B.pro_type=1 AND A.pro_pif_id NOT IN(
      SELECT pro_pif_id
      FROM property
      WHERE pro_type=1
      GROUP BY pro_pif_id
      HAVING COUNT(pro_c_id)>2
   )
) AND pro_type=1
GROUP BY pro_c_id
HAVING COUNT(pro_pif_id)=(SELECT COUNT(*) FROM(
   SELECT pro_pif_id
   FROM property
   WHERE pro_type=1
   GROUP BY pro_pif_id
   HAVING COUNT(pro_c_id)>2
) AS R1)
ORDER BY pro_c_id;
/*  end  of  your code  */

4.4 查找相似的理财产品

请用一条SQL语句完成以下查询任务： 在某些推荐方法中，需要查找某款理财产品相似的其他理财产品，不妨设其定义为：对于某款理财产品A，可找到持有A数量最多的“3”个（包括所有持有相同数量的客户，因此如有3个并列第一、1个第二、一个第三，则排列结果是1,1,1,2,3）客户，然后对于这“3”个客户持有的所有理财产品（不包含产品A自身），每款产品被全体客户持有总人数被认为是和产品A的相似度，若有相似度相同的理财产品，则为了便于后续处理的确定性，则这些相似度相同的理财产品间按照产品编号的升序排列。按照和产品A的相似度，最多的“3”款（同上理，前3名允许并列的情况，例如排列结果是1,2,2,2,3）理财产品，就是产品A的相似的理财产品。 请查找产品14的相似理财产品编号（不包含14自身）（pro_pif_id）、该编号的理财产品的客购买客户总人数（cc）以及该理财产品对于14 号理财产品的相似度排名值（prank）。 注意结果输出要求：按照相似度值降序排列，相同相似度的理财产品之间则按照产品编号的升序排列。

    -- 4) 	查找相似的理财产品

--   请用一条SQL语句实现该查询：
SELECT R2.pro_pif_id,cc,dense_rank() over(ORDER BY cc DESC) AS prank
FROM(
    SELECT pro_pif_id
    FROM property A,(
        SELECT pro_c_id
        FROM(
            SELECT pro_c_id,dense_rank() over(ORDER BY pro_quantity DESC) AS rk
            FROM property
            WHERE pro_type=1 AND pro_pif_id=14
        ) AS R1(pro_c_id,rk)
        WHERE R1.rk<=3
    ) R1
    WHERE R1.pro_c_id=A.pro_c_id AND A.pro_type=1 AND pro_pif_id<>14
    GROUP BY pro_pif_id
) AS R2,(
    SELECT pro_pif_id,COUNT(pro_c_id) cc
    FROM property
    WHERE pro_type=1
    GROUP BY pro_pif_id
) AS R3
WHERE R2.pro_pif_id=R3.pro_pif_id
ORDER BY cc DESC,pro_pif_id
/*  end  of  your code  */

4.5 查询任意两个客户的相同理财产品数

请用一条SQL语句完成以下查询任务：
查询任意两个客户之间持有的相同理财产品种数，并且结果仅保留相同理财产品数至少2种的用户对。 注意结果输出要求：第一列和第二列输出客户编号(pro_c_id,pro_c_id)，第三列输出他们持有的相同理财产品数(total_count)，按照第一列的客户编号的升序排列。

-- 5) 查询任意两个客户的相同理财产品数
--   请用一条SQL语句实现该查询：
SELECT R1.pro_c_id,R2.pro_c_id,COUNT(*) total_count
FROM(
    SELECT pro_c_id,pro_pif_id
    FROM property
    WHERE pro_type=1
) AS R1,(
    SELECT pro_c_id,pro_pif_id
    FROM property
    WHERE pro_type=1
)AS R2
WHERE R1.pro_pif_id=R2.pro_pif_id AND R1.pro_c_id<>R2.pro_c_id
GROUP BY R1.pro_c_id,R2.pro_c_id
HAVING total_count>=2
ORDER BY R1.pro_c_id
/*  end  of  your code  */

4.6 查找相似的理财客户

请用一条SQL语句完成以下查询任务：
在某些推荐方法中，需要查找某位客户在理财行为上相似的其他客户，不妨设其定义为：对于A客户，其购买的理财产品集合为{P}，另所有买过{P}中至少一款产品的其他客户集合为{B}，则{B}中每位用户购买的{P}中产品的数量为他与A客户的相似度值。将{B}中客户按照相似度值降序排列，得到A客户的相同相似度值则按照客户编号升序排列，这样取前两位客户即为A客户的相似理财客户列表。 查询每位客户(列名：pac)的相似度排名值小于3的相似客户(列名：pbc)列表，以及该每位客户和他的每位相似客户的共同持有的理财产品数(列名：common)、相似度排名值(列名：crank)。 注意结果输出要求：要求结果先按照左边客户编号(pac)升序排列，同一个客户的相似客户则按照客户相似度排名值（crank）顺序排列。

 -- 6) 查找相似的理财客户
--   请用一条SQL语句实现该查询：
SELECT pac,pbc,common,crank
FROM(
    SELECT pac,pbc,common,rank() over(partition by pac order by common desc,pbc) as crank
    FROM(
        SELECT R1.pro_c_id pac,R2.pro_c_id pbc,COUNT(*) common
        FROM(
            SELECT pro_c_id,pro_pif_id
            FROM property
            WHERE pro_type=1
        ) AS R1,(
            SELECT pro_c_id,pro_pif_id
            FROM property
            WHERE pro_type=1
        )AS R2
        WHERE R1.pro_pif_id=R2.pro_pif_id AND R1.pro_c_id<>R2.pro_c_id
        GROUP BY R1.pro_c_id,R2.pro_c_id
        ORDER BY R1.pro_c_id
    ) AS R1(pac,pbc,common)
) AS R2(pac,pbc,common,crank)
WHERE R2.crank<3
/*  end  of  your code  */

五、实训5 数据的插入、修改与删除(Insert,Update,Delete)

5.1 插入多条完整的客户信息

重温上一实训项目中金融应用场景数据库中的客户表结构：

表1 client(客户表)

字段名称 数据类型 约束 说明
c_id INTEGER PRIMARY KEY 客户编号
c_name VARCHAR(100) NOT NULL 客户名称
c_mail CHAR(30) UNIQUE 客户邮箱
c_id_card CHAR(20) UNIQUE NOT NULL 客户身份证
c_phone CHAR(20) UNIQUE NOT NULL 客户手机号
c_password CHAR(20) NOT NULL 客户登录密码

向客户表插入以下3条数据:

c_id c_name c_mail c_id_card c_phone c_password
1 林惠雯 960323053@qq.com 411014196712130323 15609032348 Mop5UPkl
2 吴婉瑜 1613230826@gmail.com 420152196802131323 17605132307 QUTPhxgVNlXtMxN
3 蔡贞仪 252323341@foxmail.com 160347199005222323 17763232321 Bwe3gyhEErJ7

用一条insert语句，或用三条insert语句完成，都可以。

use finance1;
-- 用insert语句向客户表(client)插入任务要求的3条数据:
INSERT
INTO client(c_id,c_name,c_mail,c_id_card,c_phone,c_password)
VALUES(1,'林惠雯','960323053@qq.com','411014196712130323','15609032348','Mop5UPkl');

INSERT
INTO client(c_id,c_name,c_mail,c_id_card,c_phone,c_password)
VALUES(2,'吴婉瑜','1613230826@gmail.com','420152196802131323','17605132307','QUTPhxgVNlXtMxN');

INSERT
INTO client(c_id,c_name,c_mail,c_id_card,c_phone,c_password)
VALUES(3,'蔡贞仪','252323341@foxmail.com','160347199005222323','17763232321','Bwe3gyhEErJ7');
/* end of you code */

5.2 插入不完整的客户信息

已知33号客户部分信息如下:
c_id(编号):33
c_name(名称):蔡依婷
c_phone(电话):18820762130
c_id_card(身份证号):350972199204227621
c_password(密码):MKwEuc1sc6
请用一条SQL语句将这名客户的信息插入到客户表(client)

use finance1;
-- 已知33号客户部分信息如下:
-- c_id(编号):33
-- c_name(名称):蔡依婷
-- c_phone(电话):18820762130
-- c_id_card(身份证号):350972199204227621
-- c_password(密码):MKwEuc1sc6

-- 请用一条SQL语句将这名客户的信息插入到客户表(client):
INSERT
INTO client(c_id,c_name,c_phone,c_id_card,c_password)
VALUES(33,'蔡依婷','18820762130','350972199204227621','MKwEuc1sc6');
/* the end of your code */

5.3 批量插入数据

已知表new_client保存了一批新客户信息，该表与client表结构完全相同。
请用一条SQL语句将new_client表的全部客户信息插入到客户表(client)

 use finance1;
 -- 已知表new_client保存了一批新客户信息，该表与client表结构完全相同。请用一条SQL语句将new_client表的全部客户信息插入到客户表(client):
INSERT
INTO client
SELECT * FROM new_client;
/* the end of your code */

5.4 删除没有银行卡的客户信息

请用一条SQL语句删除client表中没有银行卡的客户信息。 注意：MySQL的delete语句中from关键词不能省略。

use finance1;
-- 请用一条SQL语句删除client表中没有银行卡的客户信息：
DELETE
FROM client
WHERE c_id NOT IN(SELECT b_c_id FROM bank_card);
/* the end of your code */

5.5 冻结客户资产

请用一条update语句将手机号码为“13686431238”这位客户的投资资产(理财、保险与基金)的状态置为“冻结”。

use finance1;
-- 请用一条update语句将手机号码为“13686431238”的这位客户的投资资产(理财、保险与基金)的状态置为“冻结”。：
UPDATE property
SET pro_status='冻结'
WHERE EXISTS(
    SELECT *
    FROM client
    WHERE c_id=pro_c_id AND c_phone='13686431238'
);
/* the end of your code */

5.6 连接更新

在金融应用场景数据库中，已在表property(资产表)中添加了客户身份证列，列名为pro_id_card，类型为char(18)，该列目前全部留空(null)。请用一条update语句，根据client表中提供的身份证号(c_id_card)，填写property表中对应的身份证号信息(pro_id_card)

use finance1;
-- 在金融应用场景数据库中，已在表property(资产表)中添加了客户身份证列，列名为pro_id_card，类型为char(18)，该列目前全部留空(null)。

-- 请用一条update语句，根据client表中提供的身份证号(c_id_card)，填写property表中对应的身份证号信息(pro_id_card)。
UPDATE property
SET pro_id_card=(
    SELECT c_id_card
    FROM client
    WHERE pro_c_id=c_id
);
/* the end of your code */

六、视图

6.1 创建所有保险资产的详细记录视图

根据提示，在右侧代码编辑窗口填写1条SQL语句，完成以下任务： 创建包含所有保险资产记录的详细信息的视图v_insurance_detail，包括购买客户的名称、客户的身份证号、保险名称、保障项目、商品状态、商品数量、保险金额、保险年限、商品收益和购买时间

use finance1;
-- 创建包含所有保险资产记录的详细信息的视图v_insurance_detail，包括购买客户的名称、客户的身份证号、保险名称、保障项目、商品状态、商品数量、保险金额、保险年限、商品收益和购买时间。
-- 请用1条SQL语句完成上述任务：
CREATE VIEW v_insurance_detail
AS
SELECT c_name,c_id_card,i_name,i_project,pro_status,pro_quantity,i_amount,i_year,pro_income,pro_purchase_time
FROM client,insurance,property
WHERE c_id=pro_c_id AND pro_pif_id=i_id AND pro_type=2
/*   end  of your code  */

6.2 基于视图的查询

基于上一关创建的视图v_insurance_detail进行分组统计查询，列出每位客户的姓名，身份证号，保险投资总额（insurance_total_amount）和保险投资总收益（insurance_total_revenue）,结果依保险投资总额降序排列

 -- 基于上一关创建的视图v_insurance_detail进行分组统计查询，列出每位客户的姓名，身份证号，保险投资总额(insurance_total_amount)和保险投资总收益(insurance_total_revenue),结果依保险投资总额降序排列。
--  请用一条SQL语句实现该查询：
SELECT c_name,c_id_card,SUM(pro_quantity*i_amount) insurance_total_amount,SUM(pro_income) insurance_total_revenue
FROM v_insurance_detail
GROUP BY c_id_card
ORDER BY insurance_total_amount DESC;
/*  end  of  your code  */

七、存储过程与事务

7.1 使用流程控制语句的存储过程

数据库中有表fibonacci，用来储存斐波拉契数列的前n项：

列名 类型 说明
n int 斐波拉契数列的第n项,主码
fib(n) bigint 第n项的值

斐波拉契数列的前5项为:

n fib(n)
0 0
1 1
2 1
3 2
4 3

推导公式为：fib(n) = fib(n-1) + fib(n-2)。

请根据提示，在右侧代码文件编辑器补充代码，创建存储过程sp_fibonacci(in m int)，向表fibonacci插入斐波拉契数列的前m项，及其对应的斐波拉契数。fibonacci表初始值为一张空表。请保证你的存储过程可以多次运行而不出错。

use fib;

-- 创建存储过程`sp_fibonacci(in m int)`，向表fibonacci插入斐波拉契数列的前m项，及其对应的斐波拉契数。fibonacci表初始值为一张空表。请保证你的存储过程可以多次运行而不出错。

drop procedure if exists sp_fibonacci;
delimiter $$
create procedure sp_fibonacci(in m int)
begin
######## 请补充代码完成存储过程体 ########
DECLARE fib0,fib1,fibn,n int;
set fib0=0,fib1=1,n=2;
if m>=1 then
    insert into fibonacci values(0,0);
end if;
if m>=2 then
    insert into fibonacci values(1,1);
end if;
while m>n do
    set fibn=fib0+fib1;
    insert into fibonacci values(n,fibn);
    set fib0=fib1;
    set fib1=fibn;
    set n=n+1;
end while;
end $$

delimiter ;

7.2 使用游标的存储过程

医院的某科室有科室主任1名(亦为医生)，医生若干(至少2名，不含主任)，护士若干(至少4人)，现在需要编写一存储过程，自动安排某个连续期间的大夜班(即每天00:00-8:00时间段)的值班表，排班规则为：
1.每个夜班安排1名医生，2名护士；
2.值班顺序依工号顺序循环轮流安排(即排至最后1名后再从第1名接着排)；
3.科室主任参与轮值夜班，但不安排周末(星期六和星期天)的夜班，当周末轮至科主任时，主任的夜班调至周一，由排在主任后面的医生依次递补值周末的夜班。

存储过程的名字为sp_night_shift_arrange,它带两个输入参数：start_date, end_date，分别指排班的起始时间和结束时间。排班结果直接写入表night_shift_schedule，其结构如下：

表night_shift_schedule(夜班值班安排表)

列 类型 说明
n_date date 日期, primary key
n_doctor_name char(30) 医生姓名
n_nurse1_name char(30) 护士1姓名
n_nurse2_name char(30) 护士2姓名

假定该科室没有同名的医生和同名的护士。
科室参与值班的医护人员存储在表employee中，其结构为：

表employee(医护人员表)

列 类型 说明
e_id int 编号, primary key
e_name char(30) 姓名
e_type int 类别：1-主任,医生;2-医生;3-护士

不用考虑其它信息(比如科室之类的)，在生产环境中，只需在where短语中施加条件限制即可明确选出所需科室的医护人员。这里，且把表中全部人员视为该科室人员。

请根据提示，在右侧代码文件编辑器补充代码，完成该存储过程。（这部分太长了，感觉有点复杂，没有自己完成，以下部分为学长的代码）[HUST-CS-Database-system-principle/第2关 使用游标的存储过程.txt at main · fly-lovest/HUST-CS-Database-system-principle (github.com)](https://github.com/fly-lovest/HUST-CS-Database-system-principle/blob/main/数据库系统原理实验/实验code及报告/源代码/实训6 存储过程与事务/第2关 使用游标的存储过程.txt)

use hms1;

-- 编写一存储过程，自动安排某个连续期间的大夜班的值班表:

delimiter $$
create procedure sp_night_shift_arrange(in start_date date, in end_date date)
begin
declare now_date date;
declare not_found_flag INT default FALSE;
declare weekend_doctor_flag INT default FALSE;
declare d_name,n_name1,n_name2 char(30);
declare temp_name char(30);
declare d_type int default(0);

declare doctor_cursor cursor for select e_name,e_type from employee where e_type <> 3;
declare nurse_cursor cursor for select e_name from employee where e_type = 3;

declare continue handler for NOT FOUND set not_found_flag = TRUE;

open doctor_cursor;
open nurse_cursor;

fetch doctor_cursor into d_name,d_type;
fetch nurse_cursor into n_name1;
fetch nurse_cursor into n_name2;

set now_date = start_date;
while now_date <= end_date do
	if d_type = 1 and (dayofweek(now_date)=1 or dayofweek(now_date)=7) then
		set temp_name = d_name;
		set weekend_doctor_flag = TRUE;
		fetch doctor_cursor into d_name,d_type;
		if not_found_flag = TRUE then
			set not_found_flag = FALSE;
			close doctor_cursor;
			open doctor_cursor;
			fetch doctor_cursor into d_name,d_type;
		end if;
		insert into night_shift_schedule values(now_date,d_name,n_name1,n_name2);
		set now_date = date_sub(now_date,interval -1 day);
		fetch doctor_cursor into d_name,d_type;
		if not_found_flag = TRUE then
			set not_found_flag = FALSE;
			close doctor_cursor;
			open doctor_cursor;
			fetch doctor_cursor into d_name,d_type;
		end if;
		fetch nurse_cursor into n_name1;
		if not_found_flag = TRUE then
			set not_found_flag = FALSE;
			close nurse_cursor;
			open nurse_cursor;
			fetch nurse_cursor into n_name1;
		end if;
		fetch nurse_cursor into n_name2;
		if not_found_flag = TRUE then
			set not_found_flag = FALSE;
			close nurse_cursor;
			open nurse_cursor;
			fetch nurse_cursor into n_name2;
		end if;	
	elseif dayofweek(now_date)=2 and weekend_doctor_flag = TRUE then
		set weekend_doctor_flag = FALSE;
		insert into night_shift_schedule values(now_date,temp_name,n_name1,n_name2);
		set now_date = date_sub(now_date,interval -1 day);
		fetch nurse_cursor into n_name1;
		if not_found_flag = TRUE then
			set not_found_flag = FALSE;
			close nurse_cursor;
			open nurse_cursor;
			fetch nurse_cursor into n_name1;
		end if;
		fetch nurse_cursor into n_name2;
		if not_found_flag = TRUE then
			set not_found_flag = FALSE;
			close nurse_cursor;
			open nurse_cursor;
			fetch nurse_cursor into n_name2;
		end if;
	else
		insert into night_shift_schedule values(now_date,d_name,n_name1,n_name2);
		set now_date = date_sub(now_date,interval -1 day);
		fetch doctor_cursor into d_name,d_type;
		if not_found_flag = TRUE then
			set not_found_flag = FALSE;
			close doctor_cursor;
			open doctor_cursor;
			fetch doctor_cursor into d_name,d_type;
		end if;
		fetch nurse_cursor into n_name1;
		if not_found_flag = TRUE then
			set not_found_flag = FALSE;
			close nurse_cursor;
			open nurse_cursor;
			fetch nurse_cursor into n_name1;
		end if;
		fetch nurse_cursor into n_name2;
		if not_found_flag = TRUE then
			set not_found_flag = FALSE;
			close nurse_cursor;
			open nurse_cursor;
			fetch nurse_cursor into n_name2;
		end if;
	end if;
end while;
close doctor_cursor;
close nurse_cursor;
end$$

delimiter ;

/*  end  of  your code  */

7.3 使用事务的存储过程

在金融应用场景数据库中，编程实现一个转账操作的存储过程sp_transfer，实现从一个帐户向另一个帐户转账。该过程有5个输入参数：

applicant_id 付款人编号
source_card_id 付款卡号
receiver_card_id 收款人编号
dest_card_id 收款卡号
amount 转账金额
return_code 1：正常转账；0:转账不成功

转账操作涉及对表bank_card的操作(在生产环境中，至少还要记录转账操作本身相关的信息至转账表，在实验环境中没有设计这样的表，从略；另外，生产环境中，当银行卡被冻结，或被卡主挂失后，都不能进行转账，在实验环境中，没有设计相应的字段 ，故也从略)。

注意事项：

仅当转款人是转出卡的持有人时，才可转出；
仅当收款人是收款卡的持有人时，才可转入；
储蓄卡之间可以相互转账；
允许储蓄卡向信用卡转账，称为信用卡还款(允许替它人还款)，还款可以超过信用卡余额，此时，信用卡余额为负数；
信用卡不能向储蓄卡转账；
转账金额不能超过储蓄卡余额；

附上 bank_card(银行卡)表结构：

字段名称 数据类型 约束 说明
b_number CHAR(30) PRIMARY KEY 银行卡号
b_type CHAR(20) 无 银行卡类型(储蓄卡/信用卡)
b_c_id INTEGER NOT NULL FOREIGN KEY 所属客户编号,引用自client表的c_id字段。
b_balance NUMERIC(10,2) NOT NULL 余额,信用卡余额系指已透支的金额

请根据上述要求，在右侧代码文件编辑器补充代码，完成存储过程sp_transfer的编程。

use finance1;

-- 在金融应用场景数据库中，编程实现一个转账操作的存储过程sp_transfer_balance，实现从一个帐户向另一个帐户转账。
-- 请补充代码完成该过程：
delimiter $$
create procedure sp_transfer(
	                 IN applicant_id int,      
                     IN source_card_id char(30),
					 IN receiver_id int, 
                     IN dest_card_id char(30),
					 IN	amount numeric(10,2),
					 OUT return_code int)
BEGIN
SET autocommit=OFF;
START transaction;

UPDATE bank_card
SET b_balance=b_balance-amount
WHERE b_number=source_card_id AND b_c_id=applicant_id AND b_type='储蓄卡';

UPDATE bank_card
SET b_balance=b_balance+amount
WHERE b_number=dest_card_id AND b_c_id=receiver_id AND b_type='储蓄卡';

UPDATE bank_card
SET b_balance=b_balance-amount
WHERE b_number=dest_card_id AND b_c_id=receiver_id AND b_type='信用卡';

IF NOT EXISTS(
    SELECT *
    FROM bank_card 
    WHERE b_number=source_card_id 
    AND b_c_id=applicant_id 
    AND b_type='储蓄卡' 
    AND b_balance>=0
) THEN
SET return_code=0;
rollback;

ELSEIF NOT EXISTS(
    SELECT *
    FROM bank_card
    WHERE b_number=dest_card_id AND b_c_id=receiver_id
) THEN
SET return_code=0;
rollback;

ELSE
SET return_code=1;
commit;

END IF;

END$$

delimiter ;

/*  end  of  your code  */

八、触发器

8.1 为投资表property实现业务约束规则-根据投资类别分别引用不同表的主码

在右侧代码文件编辑器里补充代码，实现本任务所要求的完整性业务规则。当插入的数据不符合要求时，拒绝数据的插入，并反馈出错信息：
(1) pro_type数据不合法时，显示:
type x is illegal!
这里，x系指试图插入的pro_type值。
(2) pro_type = 1,但pro_pif_id不是finances_product表中的某个主码值，显示:
finances product #x not found!
这里,x系指试图插入的pro_pif_di的值。
(3) pro_type = 2,但pro_pif_id不是insurance表中的某个主码值，显示:
insurance #x not found!
这里,x系指试图插入的pro_pif_id的值。
(3) pro_type = 3,但pro_pif_id不是fund表中的某个主码值，显示:
fund #x not found!
这里,x系指试图插入的pro_pif_id的值。

提示：
(1) 查阅MySQL的字符串函数，构造出错信息；
(2) 当数据不合法时，用signal sqlstate 语句抛出异常，并设置出错信息：
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
其中，通用SQLSTATE '45000'意指用户定义的待处理异常，msg需替换成你想要显示的提示信息(不超过128个字符)。

use finance1;
drop trigger if exists before_property_inserted;
-- 请在适当的地方补充代码，完成任务要求：
delimiter $$
CREATE TRIGGER before_property_inserted BEFORE INSERT ON property
FOR EACH ROW 
BEGIN

DECLARE msg varchar(40);
IF new.pro_type<>1 AND new.pro_type<>2 AND new.pro_type<>3 THEN
    SET msg=concat('type ',new.pro_type,' is illegal!');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
END IF;

IF new.pro_type=1 AND NOT EXISTS(SELECT * FROM finances_product WHERE p_id=new.pro_pif_id) THEN
    SET msg=concat('finances product #',new.pro_pif_id,' not found!');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
END IF;

IF new.pro_type=2 AND NOT EXISTS(SELECT * FROM insurance WHERE i_id=new.pro_pif_id) THEN
    SET msg=concat('insurance #',new.pro_pif_id,' not found!');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
END IF;

IF new.pro_type=3 AND NOT EXISTS(SELECT * FROM fund WHERE f_id=new.pro_pif_id) THEN
    SET msg=concat('fund #',new.pro_pif_id,' not found!');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
END IF;

END$$
 
delimiter ;

九、用户自定义函数

9.1 创建函数并在语句中使用它

主在右侧代文件编辑器补充代码，完成以下编程任务：
(1) 用create function语句创建符合以下要求的函数：
依据客户编号计算其所有储蓄卡余额的总和
函数名为：get_deposit
(2) 利用创建的函数，仅用一条SQL语句查询存款总额在100万(含)以上的客户身份证号，姓名和存款总额(total_deposit)，结果依存储总额从高到低排序

use finance1;
set global log_bin_trust_function_creators=1;
drop function IF EXISTS get_deposit;
/*
   用create function语句创建符合以下要求的函数：
   依据客户编号计算该客户所有储蓄卡的存款总额。
   函数名为：get_Records。函数的参数名可以自己命名:*/

delimiter $$
create function get_deposit(client_id int)
returns numeric(10,2) 
begin
	DECLARE deposit numeric(10,2);
    SELECT SUM(b_balance) INTO deposit
    FROM bank_card
    WHERE b_c_id=client_id AND b_type='储蓄卡'
    GROUP BY b_c_id;
    return deposit;
end$$
delimiter ;

/*  应用该函数查询存款总额在100万以上的客户身份证号，姓名和存储总额(total_deposit)，
    结果依存款总额从高到代排序  */
SELECT c_id_card,c_name,get_deposit(c_id) AS total_deposit
FROM client
WHERE get_deposit(c_id)>=1000000
ORDER BY get_deposit(c_id) DESC;
/*  代码文件结束     */

十、数据库设计与实现

10.1 从概念模型到MySQL实现

应用背景介绍
这是一个机票订票系统，系统需要考虑以下实体：
（1）用户(user)：
用户分两类，普通用户可以订票，管理用户有权限维护和管理整个系统的运营。为简单起见，两类用户合并，用admin_tag标记区分。用户的属性(包括业务约束)有：

● 用户编号: user_id int 主码,自动增加
● 名字: firstname varchar(50) 不可为空
● 姓氏: lastname varchar(50) 不可为空
● 生日: dob date 不可为空
● 性别: sex char(1) 不可为空
● 邮箱: email varchar(50)
● 联系电话: phone varchar(30)
● 用户名: username varchar(20) 不可空,不可有重
● 密码: password char(32) 不可空
● 管理员标志: admin_tag tinyint 缺省值0(非管理员),不能空

（2）旅客(passenger)：
用户登录系统不一定是替自己买票，所以用户和旅客信息是分开存储的。属性有：

● 旅客编号: passenger_id int 自增，主码
● 证件号码: id char(18) 不可空 不可重
● 名字：firstname varchar(50) 不可空
● 姓氏: lastname varchar(50) 不可空
● 邮箱：mail varchar(50)
● 电话: phone varchar(20) 不可空
● 性别: sex char(1) 不可空
● 生日: dob date

（3）机场(airport)：
有以下属性：

● 编号: airport_id int 自增，主码
● 国际民航组织编码：iata char(3) 不可空，全球唯一
● 国际航运协会编码: icao char(4) 不可空，全球唯一
● 机场名称: name varchar(50) 不可空，普通索引
● 所在城市: city varchar(50)
● 所在国家: country varchar(50)
● 纬度: latitude decimal(11,8)
● 经度: longitude decimal(11,8)
● 全球每个机场都有唯一IATA编码和ICAO编码，IATA为3个字符，ICAO为4个字符。例如首都机场的(IATA,ICAO)分别为(PEK,ZBAA)，大兴机场为(PKX,ZBAD),天河机机场为(WUH,ZHHH)。在飞机登记牌上出发地和到达地均用IATA表示。为能在地图上显示机场位置，需要记录经纬度信息。

（4）航空公司(airline)：
有以下属性:

● 编号：airline_id int 自增，主码
● 名称：name varchar(30) 不可空
● 国际民航组织编码: iata char(2) 不可空，具全球唯一性
● 航空公司的IATA编码为2位，如东航为MU，国航为CA,南航为CZ等，航班号一般以所属航空公司的IATA码为前缀。

（5）民航飞机(airplane)：
有属性：

● 编号：airplane_id int 自增，主码
● 机型：type varchar(50) 不可空，如B737-300,A320-500等
● 座位: capacity smallint 不可空
● 标识: identifier varchar(50) 不可空

（6）航班常规调度表(flightschedule)：
舤班一般以周次安排，例如：每周两个班次，周一和周五。有属性：

● 航班号: flight_no char(8) 主码
● 起飞时间: departure time 非空
● 到达时间: arrival time 非空
● 飞行时长: duration smalint 非空，飞行时长一般以分种计。
● 周一：monday tinyint 缺省0
● 周二：tuesday tinyint 缺省0
● 周三：wednesday tinyint 缺省0
● 周四：thursday tinyint 缺省0
● 周五：friday tinyint 缺省0
● 周六：saturday tinyint 缺省0
● 周日：sunday tinyint 缺省0

（7）航班表(flight)：
航班依航班常规调度表为基准安排。但调度表不是一成不变，也不是每个既定的航都实际起飞，也不总是按既定的时间起飞，所以实飞航班必须单独安排并记录。要记录的信息有：

● 飞行编号：flight_id int 自增，主码
● 起飞时间: departure datetime 非空
● 到达时间: arrival datetime 非空
● 飞行时长：duration smallint 非空

有的系统还会实时显示航班经纬度和高度位置，这里我们作了简化，去掉了实时飞行信息。

（8）机票(ticket)：
用户可替自己或其亲友购买某个航班的机票。机票的属性有：

● 票号：ticket_id int 自增，主码
● 坐位号：seat char(4)
● 价格：price decimal(10,2) 不能空

（9）实体间的联系：
实体间的联系都清楚地标注在ER图中：

每个航空公司都有一个母港(机场)，又叫基地。大的机场可能会是多家公司的基地，小型机场可能不是任何航空公司的基地。
每个航班属于一家航空公司，航空公司可以很多航班。
任何一驾民航飞机属于一家航空公司，航空公司可以有多驾飞机。
每驾飞机可以执飞多个航班，一个飞行航班由一架飞机执飞。
一个航班根据执飞机型可以售出若干机票。一张机票是某个特定航班的机票。
用户可以多次订票，旅客可以多次乘坐飞机。一张机票肯定是某个用户为某个特定的旅客购买的特定航班的机票。即机票信息不仅跟乘坐人有关，同时记录购买人信息(虽然两者有时是同一人)。为简单起见，订购时间没有考虑。
无论常规计划的航班，还是实际飞行航班，都是从某个机场出发，到达另一个机场。但一个机场可以是很多个航班的出发地，也是很多航班的到达地。
请根据上述信息和所给ER图，给出在MySQL实现flight_booking的语句，包括建库，建表，创建主码，外码，索引，指定缺省，不能为空等约束。所有索引采用BTREE。所有约束的名字不作要求。所有的外码与主码同名。但有两处例外: 计划航班和飞行航班都涉及出发机场和到达机场，外码与主码同名会导致同一表有两个同名列。故这两处外码例外处理：出发机场命名为from，到达机场命名为to。
注意：所有的表名字和列名，都没有大写字母。列名存在与关键字同名的情形，请妥善处理。你有可能不能一次通过评测，请考虑你的代码要反复修改再运行的情形。

 # 请将你实现flight_booking数据库的语句写在下方：
CREATE DATABASE flight_booking;
USE flight_booking;
CREATE TABLE user(
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    firstname VARCHAR(50) NOT NULL,
    lastname VARCHAR(50) NOT NULL,
    dob DATE NOT NULL,
    sex CHAR(1) NOT NULL,
    email VARCHAR(50),
    phone VARCHAR(30),
    username VARCHAR(20) NOT NULL UNIQUE,
    password CHAR(32) NOT NULL,
    admin_tag TINYINT NOT NULL DEFAULT 0
);

CREATE TABLE passenger(
    passenger_id INT PRIMARY KEY AUTO_INCREMENT,
    id CHAR(18) NOT NULL UNIQUE,
    firstname VARCHAR(50) NOT NULL,
    lastname VARCHAR(50) NOT NULL,
    mail VARCHAR(50),
    phone VARCHAR(20) NOT NULL,
    sex CHAR(1) NOT NULL,
    dob DATE
);

CREATE TABLE airport(
    airport_id INT PRIMARY KEY AUTO_INCREMENT,
    iata CHAR(3) NOT NULL UNIQUE,
    icao CHAR(4) NOT NULL UNIQUE,
    name VARCHAR(50) NOT NULL,
    city VARCHAR(50),
    country VARCHAR(50),
    latitude DECIMAL(11,8),
    longitude DECIMAL(11,8),
    index(name)
);

CREATE TABLE airline(
    airline_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL,
    iata CHAR(2) NOT NULL UNIQUE,
    airport_id INT NOT NULL,
    CONSTRAINT FK_airline_airport FOREIGN KEY(airport_id) REFERENCES airport(airport_id)
);

CREATE TABLE airplane(
    airplane_id INT PRIMARY KEY AUTO_INCREMENT,
    type VARCHAR(50) NOT NULL,
    capacity SMALLINT NOT NULL,
    identifier VARCHAR(50) NOT NULL,
    airline_id INT NOT NULL,
    CONSTRAINT FK_airplane_airline FOREIGN KEY(airline_id) REFERENCES airline(airline_id)
);

CREATE TABLE flightschedule(
    flight_no CHAR(8) PRIMARY KEY,
    departure TIME NOT NULL,
    arrival TIME NOT NULL,
    duration SMALLINT NOT NULL,
    monday TINYINT DEFAULT 0,
    tuesday TINYINT DEFAULT 0,
    wednesday TINYINT DEFAULT 0,
    thursday TINYINT DEFAULT 0,
    friday TINYINT DEFAULT 0,
    saturday TINYINT DEFAULT 0,
    sunday TINYINT DEFAULT 0,
    airline_id INT NOT NULL,
    `from` INT NOT NULL,
    `to` INT NOT NULL,
    CONSTRAINT FK_flightschedule_airline FOREIGN KEY(airline_id) REFERENCES airline(airline_id),
	CONSTRAINT FK_flightschedule_airport1 FOREIGN KEY(`from`) REFERENCES airport(airport_id),
	CONSTRAINT FK_flightschedule_airport2 FOREIGN KEY(`to`) REFERENCES airport(airport_id)
);

CREATE TABLE flight(
    flight_id INT PRIMARY KEY AUTO_INCREMENT,
    departure DATETIME NOT NULL,
    arrival DATETIME NOT NULL,
    duration SMALLINT NOT NULL,
    airline_id INT NOT NULL,
	airplane_id INT NOT NULL,
	flight_no CHAR(8) NOT NULL,
	`from` INT NOT NULL,
	`to` INT NOT NULL,
    CONSTRAINT FK_flight_airline FOREIGN KEY (airline_id) REFERENCES airline(airline_id),
	CONSTRAINT FK_flight_airplane FOREIGN KEY (airplane_id) REFERENCES airplane(airplane_id),
	CONSTRAINT FK_flight_flightschedule FOREIGN KEY (flight_no) REFERENCES flightschedule(flight_no),
	CONSTRAINT FK_flight_airport1 FOREIGN KEY (`from`) REFERENCES airport(airport_id),
	CONSTRAINT FK_flight_airport2 FOREIGN KEY (`to`) REFERENCES airport(airport_id)
);

CREATE TABLE ticket(
    ticket_id INT PRIMARY KEY AUTO_INCREMENT,
    seat CHAR(4),
    price DECIMAL(10,2) NOT NULL,
    flight_id INT NOT NULL,
	passenger_id INT NOT NULL,
	user_id INT NOT NULL,
	CONSTRAINT FK_ticket_flight FOREIGN KEY (flight_id) REFERENCES flight(flight_id),
	CONSTRAINT FK_ticket_passenger FOREIGN KEY (passenger_id) REFERENCES passenger(passenger_id),
	CONSTRAINT FK_ticket_user FOREIGN KEY (user_id) REFERENCES user(user_id)
);

十一、数据库应用开发(JAVA篇)

11.1 JDBC体系结构和简单的查询

正确使用JDBC，查询金融应用场景数据库finance的client表(客户表)中邮箱不为空的客户信息，列出客户姓名，邮箱和电话.一个展示结果的示例如下(字体颜色是平台自动加的，不是编程要求)：

姓名	邮箱				电话
夏雅惠	57433144004@qq.com		18962433158
钟庭玮	59434199077@163.com		18110434192

注：标题以及字段值之间用制表符隔开。第1列和第2列间用一个制表符，第2列和第3列间，标题用4个制表符，字段值用两个制表符隔开。

JDBC驱动程序(mysql-connector-java-8.0.23.jar)由平台提供，直接使用即可，不用特别设置。

请在右侧代码文件编辑器适当的位置补充代码，实现上述编程要求。

附client表的结构: 表1 client(客户表)

字段名称 数据类型 约束 说明
c_id INTEGER PRIMARY KEY 客户编号
c_name VARCHAR(100) NOT NULL 客户名称
c_mail CHAR(30) UNIQUE 客户邮箱
c_id_card CHAR(20) UNIQUE NOT NULL 客户身份证
c_phone CHAR(20) UNIQUE NOT NULL 客户手机号
c_password CHAR(20) NOT NULL 客户登录密码 
/* 请在适当的位置补充代码，完成指定的任务 
   提示：
      try {


      } catch
    之间补充代码  
*/
import java.sql.*;

public class Client {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            //注册驱动程序
            Class.forName("com.mysql.cj.jdbc.Driver");
            //创建数据库连接对象
            String URL="jdbc:mysql://127.0.0.1:3306/finance?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=UTC";
            String USER="root";
            String PASS="123123";
            connection=DriverManager.getConnection(URL, USER, PASS);
            //创建执行对象
            statement = connection.createStatement();
            //执行SQL语句
            String SQL = "select * from client where c_mail is not null;";
            resultSet = statement.executeQuery(SQL);
            //输出查询结果
            System.out.println("姓名\t邮箱\t\t\t\t电话");
            while (resultSet.next()) {
                System.out.print(resultSet.getString("c_name")+'\t');
                System.out.print(resultSet.getString("c_mail")+"\t\t");
                System.out.println(resultSet.getString("c_phone"));
            }
 
         } catch (ClassNotFoundException e) {
            System.out.println("Sorry,can`t find the JDBC Driver!"); 
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }

                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

11.2 用户登录

编程体验客户登录功能.程序先后提示客户输用户名和密码: 请输入用户名： 请输入密码： 客户的邮箱(c_mail)充当用户名,而不是编号(c_id).通常邮箱更容易记住. 根据客户的输入,输出以下两类信息之一:

  • 登录成功。

  • 用户名或密码错误！

    如果用户名和密码匹配成功,输出前者,其它情况输出后者(包括该用户不存在).通常被恶意用户试探出用户名,也会带来不良后果.

请在右侧代码文件编辑窗补充代码，完成编程任务。

import java.sql.*;
import java.util.Scanner;

public class Login {
    public static void main(String[] args) {
        Connection connection = null;
        //申明下文中的resultSet, statement
        Statement statement = null;
        ResultSet resultSet = null;


        Scanner input = new Scanner(System.in);

        System.out.print("请输入用户名：");
        String loginName = input.nextLine();
        System.out.print("请输入密码：");
        String loginPass = input.nextLine();

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            String userName = "root";
            String passWord = "123123";
            String url = "jdbc:mysql://127.0.0.1:3306/finance?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            // 补充实现代码:
            statement = connection.createStatement();
            String SQL="SELECT * FROM client WHERE c_mail='"+loginName+"';";
            resultSet = statement.executeQuery(SQL); 
            if(resultSet.next()){//有查询结果
                if(resultSet.getString("c_password").equals(loginPass)){
                    System.out.print("登录成功。\n");
                }
                else{
                    System.out.print("用户名或密码错误！\n");
                }
            }
            else{
                System.out.print("用户名或密码错误！\n");
            }

            

         } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }

                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

11.3 添加新客户

根据提示，在右侧代码文件编辑器补充代码，实现向client表插入客户信息的方法：

public static int insertClient(Connection connection,int c_id, String c_name, String c_mail,           String c_id_card, String c_phone,String c_password){
    
}

insertClient()方法的每个参数都在注释中进行了详细的说明。 insertClient()方法返回插入的行数，尽管main()方法在调时并没有检查该返回值(评测程序能检查出是否插入是否成功)。 假定输入的客户信息没有错误需要处理(比如客户编号不是整数，信息项缺失等异常情况)。

请不要修改main()方法。

import java.sql.*;
import java.util.Scanner;

public class AddClient {
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/finance?allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=UTC";
    static final String USER = "root";
    static final String PASS = "123123";
    /**
     * 向Client表中插入数据
     *
     * @param connection 数据库连接对象
     * @param c_id 客户编号
     * @param c_name 客户名称
     * @param c_mail 客户邮箱
     * @param c_id_card 客户身份证
     * @param c_phone 客户手机号
     * @param c_password 客户登录密码
     */
    public static int insertClient(Connection connection,
                                   int c_id, String c_name, String c_mail,
                                   String c_id_card, String c_phone, 
                                   String c_password){
        int n=0;
        PreparedStatement pps = null;
        try{
            String SQL="INSERT INTO client VALUES(?,?,?,?,?,?);";
            pps=connection.prepareStatement(SQL);
            pps.setInt(1,c_id);
            pps.setString(2,c_name);
            pps.setString(3,c_mail);
            pps.setString(4,c_id_card);
            pps.setString(5,c_phone);
            pps.setString(6,c_password);
            n=pps.executeUpdate();
        }catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                if (pps != null) {
                    pps.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return n;
    }

    // 不要修改main() 
    public static void main(String[] args) throws Exception {

        Scanner sc = new Scanner(System.in);
        Class.forName(JDBC_DRIVER);

        Connection connection = DriverManager.getConnection(DB_URL, USER, PASS);

        while(sc.hasNext())
        {
            String input = sc.nextLine();
            if(input.equals(""))
                break;

            String[]commands = input.split(" ");
            if(commands.length ==0)
                break;
            int id = Integer.parseInt(commands[0]);
            String name = commands[1];
            String mail = commands[2];
            String idCard = commands[3];
            String phone = commands[4];
            String password = commands[5];

            insertClient(connection, id, name, mail, idCard, phone, password);
        }
    }

}

11.4 银行卡销户

根据提示，在右侧代码文件编辑器补充代码，实现向银行卡销号的方法，只要客户编号和银行卡号匹配，即从bank_card表中删除该银行卡。至于卡内余额或信用卡的欠款，由客户在柜台当面结清，程序不用考虑其它事项。

public static int removeBankCard(Connection connection,int b_c_id, String b_number){
}

removeBankCard()方法的每个参数都在注释中进行了详细的说明。 removeBankCard()方法返回被删除的行数。 假定输入的客户信息没有错误需要处理(比如客户编号不是整数，信息项缺失等异常情况)。

请不要修改main()方法。

import java.sql.*;
import java.util.Scanner;

public class RemoveCard {
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/finance?allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=UTC";
    static final String USER = "root";
    static final String PASS = "123123";
    /**
     * 删除bank_card表中数据
     *
     * @param connection 数据库连接对象
     * @param b_c_id 客户编号
     * @param c_number 银行卡号
     */
    public static int removeBankCard(Connection connection,
                                   int b_c_id, String b_number){
        int n=0;
        PreparedStatement pps = null;
        try{
            String SQL="DELETE FROM bank_card WHERE b_c_id=? AND b_number=? ;";
            pps=connection.prepareStatement(SQL);
            pps.setInt(1,b_c_id);
            pps.setString(2,b_number);
            n=pps.executeUpdate();
        }catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                if (pps != null) {
                    pps.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return n;
    }

    // 不要修改main() 
    public static void main(String[] args) throws Exception {

        Scanner sc = new Scanner(System.in);
        Class.forName(JDBC_DRIVER);

        Connection connection = DriverManager.getConnection(DB_URL, USER, PASS);

        while(sc.hasNext())
        {
            String input = sc.nextLine();
            if(input.equals(""))
                break;

            String[]commands = input.split(" ");
            if(commands.length ==0)
                break;
            int id = Integer.parseInt(commands[0]);
            String carNumber = commands[1];
            
            int n = removeBankCard(connection, id, carNumber);
            if (n > 0) {
               System.out.println("已销卡数：" + n);
            } else {
               System.out.println("销户失败，请检查客户编号或银行卡号！" );
            }
        }
    }

}

11.5 客户修改密码

根据提示，在右侧代码文件编辑器补充代码，实现修改密码的方法passwd()。客户修改密码通常需要确认客户身份，即客户需提供用户名(以邮箱为用户名)和密码，同时还需要输两次新密码，以免客户实际输入的密码与心中想的不一致，只有当所有条件(合法的客户，两次密码输入一致)时才修改密码。main()方法在调用passwd()之前，会先检查新设密码的两次输入是否一致，只有在两次输入一致的情形下才会调用passwd();

public static int insertClient(Connection connection,String mail,String password,String newPass){
    
}

passwd()方法的每个参数都在注释中进行了详细的说明。 passwd()方法返回一个整数：
1 - 密码修改成功
2 - 用户不存在
3 - 密码不正确
-1 - 程序异常(如没能连接到数据库等）

请不要修改main()方法。

import java.sql.*;
import java.util.Scanner;

public class ChangePass {
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/finance?allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=UTC";
    static final String USER = "root";
    static final String PASS = "123123";
    /**
     * 修改客户密码
     *
     * @param connection 数据库连接对象
     * @param mail 客户邮箱,也是登录名
     * @param password 客户登录密码
     * @param newPass  新密码
     * @return
     *   1 - 密码修改成功
     *   2 - 用户不存在
     *   3 - 密码不正确
     *  -1 - 程序异常(如没能连接到数据库等）
     */
    public static int passwd(Connection connection,
                             String mail,
                             String password, 
                             String newPass){
        int n=0;
        PreparedStatement pps = null;
        ResultSet resultSet = null;
        try{
            //首先确认用户是否存在
            String SQL="SELECT * FROM client WHERE c_mail=?;";
            pps=connection.prepareStatement(SQL);
            pps.setString(1,mail);
            resultSet = pps.executeQuery();
            if(resultSet.next()){//查询到了用户
                SQL="SELECT * FROM client WHERE c_mail=? AND c_password=? ;";
                pps=connection.prepareStatement(SQL);
                pps.setString(1,mail);
                pps.setString(2,password);
                resultSet = pps.executeQuery();
                if(resultSet.next()){//密码正确
                    SQL="UPDATE client SET c_password = ? where c_mail = ?;";
                    pps=connection.prepareStatement(SQL);
                    pps.setString(1,newPass);
                    pps.setString(2,mail);
                    n = pps.executeUpdate();
                }
                else{//密码错误
                    n=3;
                }
            }
            else{//未查询到用户
                n=2;
            }
        }catch (SQLException throwables) {
            throwables.printStackTrace();
            n = -1;
        } finally {
            try {
                if (pps != null) {
                    pps.close();
                }
                if (resultSet != null) {
                    resultSet.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                n = -1;
            }
        }
        return n;
    }

    // 不要修改main() 
    public static void main(String[] args) throws Exception {

        Scanner sc = new Scanner(System.in);
        Class.forName(JDBC_DRIVER);

        Connection connection = DriverManager.getConnection(DB_URL, USER, PASS);

        while(sc.hasNext())
        {
            String input = sc.nextLine();
            if(input.equals(""))
                break;

            String[]commands = input.split(" ");
            if(commands.length ==0)
                break;
            String email = commands[0];
            String pass = commands[1];
            String pwd1 = commands[2];
            String pwd2 = commands[3];
            if (pwd1.equals(pwd2)) {
              int n = passwd(connection, email, pass, pwd1);  
              System.out.println("return: " + n);
            } else {
              System.out.println("两次输入的密码不一样!");
            }
        }
    }

}

11.6 事务与转账操作

编写一个银行卡转账的方法transferBalance()。

transferBalance()在被调用前，柜台已经确认过转出帐号持有者身份，所以转帐方法只接受转出卡号，转入卡号和转账金额三个参数。由调用者保证转账金额为正数。 transferBalance()方法的每个参数都在注释中进行了详细的说明。 transferBalance()返回boolean值，true表示转帐成功，false表示转账失败，并不需要细分或解释失败的原因。 下列任一情形都不可转账(转账失败的原因)： 转出或转入帐号不存在 转出账号是信用卡 转出帐号余额不足

提示：
1.本方法需开启手工事务,并正确使用commit和rollback；
2.当转入卡是信用卡时，意指信用卡还款，还款可以超过透支款项；
3.对事务的隔离级别不作要求。

请不要修改main()方法。

附:bank_card(银行卡)的表结构：

字段名称 数据类型 约束 说明
b_number CHAR(30) PRIMARY KEY 银行卡号
b_type CHAR(20) 无 银行卡类型(储蓄卡/信用卡)
b_c_id INTEGER NOT NULL FOREIGN KEY 所属客户编号,引用自client表的c_id字段。
b_balance NUMERIC(10,2) NOT NULL 余额,信用卡余额系指已透支 
import java.sql.*;
import java.util.Scanner;

public class Transfer {
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/finance?allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=UTC";
    static final String USER = "root";
    static final String PASS = "123123";
    /**
     * 转账操作
     *
     * @param connection 数据库连接对象
     * @param sourceCard 转出账号
     * @param destCard 转入账号
     * @param amount  转账金额
     * @return boolean
     *   true  - 转账成功
     *   false - 转账失败
     */
    public static boolean transferBalance(Connection connection,
                             String sourceCard,
                             String destCard, 
                             double amount){
        PreparedStatement pps = null;
        ResultSet resultSet = null;
        boolean n = true;
        try{
            connection.setAutoCommit(false);//禁止自动提交
            
            String SQL="UPDATE bank_card SET b_balance = b_balance - ? WHERE b_number = ? AND b_type='储蓄卡';";
            pps=connection.prepareStatement(SQL);
            pps.setDouble(1,amount);
            pps.setString(2,sourceCard);
            pps.executeUpdate();
            
            SQL="UPDATE bank_card SET b_balance = b_balance + ? WHERE b_number = ? AND b_type='储蓄卡' ;";
            pps=connection.prepareStatement(SQL);
            pps.setDouble(1,amount);
            pps.setString(2,destCard);
            pps.executeUpdate();

            SQL="UPDATE bank_card SET b_balance = b_balance - ? WHERE b_number = ? AND b_type='信用卡' ;";
            pps=connection.prepareStatement(SQL);
            pps.setDouble(1,amount);
            pps.setString(2,destCard);
            pps.executeUpdate();

            SQL="SELECT * FROM bank_card WHERE b_number=? AND b_type='储蓄卡' ;";
            pps=connection.prepareStatement(SQL);
            pps.setString(1,sourceCard);
            resultSet = pps.executeQuery();
            if(!resultSet.next()){//不存在转出卡
                n=false;
                connection.rollback();
            }
            else{//存在转出卡
                if(resultSet.getDouble("b_balance")<0){//当前b_balance为转出的状态
                    n=false;
                    connection.rollback();
                }
                else{
                    //判断转出卡是否存在
                    SQL="SELECT * FROM bank_card WHERE b_number=? ;";
                    pps=connection.prepareStatement(SQL);
                    pps.setString(1,destCard);
                    resultSet = pps.executeQuery();
                    if(!resultSet.next()){
                        n=false;
                        connection.rollback();
                    }
                    else connection.commit();
                }
            }
        }catch (SQLException throwables) {
            throwables.printStackTrace();
            n = false;
        } finally {
            try {
                if (pps != null) {
                    pps.close();
                }
                if (resultSet != null) {
                    resultSet.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                n = false;
            }
        }
        return n;
    }

    // 不要修改main() 
    public static void main(String[] args) throws Exception {

        Scanner sc = new Scanner(System.in);
        Class.forName(JDBC_DRIVER);

        Connection connection = DriverManager.getConnection(DB_URL, USER, PASS);

        while(sc.hasNext())
        {
            String input = sc.nextLine();
            if(input.equals(""))
                break;

            String[]commands = input.split(" ");
            if(commands.length ==0)
                break;
            String payerCard = commands[0];
            String  payeeCard = commands[1];
            double  amount = Double.parseDouble(commands[2]);
            if (transferBalance(connection, payerCard, payeeCard, amount)) {
              System.out.println("转账成功。" );
            } else {
              System.out.println("转账失败,请核对卡号，卡类型及卡余额!");
            }
        }
    }

}
  • 文章作者：w_CS
  • 原文链接：https://2006wzt.github.io/post/数据库系统概论实验/
  • 版权声明：本博客所有文章除特别声明外，均采用BY-NC-SA 许可协议。转载请注明出处！