数据库原理实验六——ODBC/JDBC数据库编程

实验目的

1. 熟练掌握ODBC数据库访问技术和编程方法。
2. 掌握除ODBC外的其他数据库访问编程技术。

实验内容

这个实验首先需要配置ODBC数据源，以下是配置的流程

首先在MySQL官网下载数据源驱动程序：下载地址

注意选择64bit的，下载后安装即可。

完成安装。

1. ODBC数据源配置以及程序调试。（20分）

(1) 配置一个ODBC数据源，要求数据源名称：student，其中包含s表（学生信息表）。

配置ODBC数据源，打开控制面板->管理工具->ODBC数据源（64位）–>系统DSN，单击添加：

完成如下配置，其中IP地址是127.0.0.1代表本地数据库。

完成这步后点击Test，测试一下是否成功。
(2) 理解ODBC编程，阅读并运行实验给出的例子程序（MFC或者CSharp代码），要求简单写出自己对这段程序的理解或者流程图，并且给出程序运行结果截图。

我这里演示一下MFC程序的运行结果，（主要是我不会C#，呜呜呜）

刚才创建的数据源名字叫student，这里要修改一下，然后密码也修改成自己的密码。然后生成解决方案，开始执行。

然后我在这里犯了一个非常愚蠢的错误，从代码可以看出他这里用的SNO是一个char类型的变量，但我在student里使用的是INT类型，这里把自己创建的student里的表删除，换成老师给的就好。

2. 参考以上的ODBC编程例子程序，使用ODBC编程技术，编写一个简单的程序，包含对数据库SPJ_MNG的连接，查询，插入，修改和删除。（30分）

首先创建一个MFC程序，具体可以参考这篇文章：MFC中创建一个基于对话框的应用程序

要实现MFC的GUI界面还需要使用CRecordset类的成员函数：
参考资料如下：
CRecordset类的常用成员函数
利用支持ODBC的CRecordset类实现对数据库的操作
3. 银行场景化综合应用实验。（50分）

(1) 身份证号为“610103123456781234”的人在C银行注册了一个新客户，并且申请办理了一张新的储蓄卡。在客户表和银行卡表中插入该客户记录。（2分）

INSERT 
INTO customer
VALUES(31,'刘一','610103123456781234','18815650031','gaussdb');

(2) 根据业务需要添加表的约束，约束添加成功后进行验证。（3分）
① 在银行卡表，理财产品购买表，保险购买表，基金购买表中，添加正确的外键约束：客户编号设置为外键，参照客户表的客户编号；理财产品编号，保险编号，基金编号分别参照对应的表中的编号；支付银行卡号参考银行卡表的卡号。
银行卡表的外键约束：

ALTER TABLE bank_card    
ADD CONSTRAINT bankcard_fk_cid    
  FOREIGN KEY (b_c_id)    
  REFERENCES customer (c_id)    
  ON DELETE NO ACTION    
  ON UPDATE NO ACTION;    

理财产品表的外键约束：

ALTER TABLE c_finances
ADD CONSTRAINT finances_fk_cid
  FOREIGN KEY (c_id)
  REFERENCES customer (c_id)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT finances_fk_fid
  FOREIGN KEY (p_id)
  REFERENCES finances_product (p_id)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

保险购买表的外键约束：

ALTER TABLE c_insurance  
ADD CONSTRAINT insurance_fk_cid  
  FOREIGN KEY (c_id)  
  REFERENCES customer (c_id)  
  ON DELETE NO ACTION  
  ON UPDATE NO ACTION,  
ADD CONSTRAINT insurance_fk_iid  
  FOREIGN KEY (i_id)  
  REFERENCES insurance (i_id)  
  ON DELETE NO ACTION  
  ON UPDATE NO ACTION; 

基金购买表的外键约束：

ALTER TABLE c_fund
ADD CONSTRAINT fund_fk_cid
  FOREIGN KEY (c_id)
  REFERENCES customer (c_id)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT fund_fk_fid
  FOREIGN KEY (f_id)
  REFERENCES fund (f_id)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

② 在以上基本表中，存在金额或者价格相关的6个属性。在现实生活中，金额或者价格不会存在负数。因此针对这些属性，添加其值大于0的约束条件。注意，对于银行卡是“信用卡”的情况余额可以为负，所以不要设定余额大于0的约束。

我是真没找到6个是哪6个

ALTER TABLE bank_card
ADD CONSTRAINT b_chk
CHECK((b_balance >= 0 AND b_type != '信用卡') OR (b_type = '信用卡'));

ALTER TABLE finances_product
ADD CONSTRAINT f_chk
CHECK(p_price >= 0);

ALTER TABLE fund
ADD CONSTRAINT fund_chk
CHECK(f_price >= 0);

ALTER TABLE insurance
ADD CONSTRAINT i_chk
CHECK(i_price >= 0);

ALTER TABLE c_finances  
ADD CONSTRAINT cf_chk  
CHECK(p_purchase_money >= 0);

ALTER TABLE c_fund  
ADD CONSTRAINT cfund_chk  
CHECK(f_purchase_money >= 0);

ALTER TABLE c_insurance  
ADD CONSTRAINT ci_chk  
CHECK(i_purchase_money >= 0);

(3) 模拟以下的业务写出SQL查询语句进行查询。（10分）
① 查询C银行所有银行卡的卡号和类型信息。

SELECT b_number,b_type
FROM bank_card;

② 查询C银行拥有的客户数量。

SELECT COUNT(*)
FROM customer;

③ 查询拥银行卡的所有客户编号，姓名和身份证号。

SELECT c_id,c_name,c_id_card
FROM customer,bank_card
WHERE c_id = b_c_id
GROUP BY c_id
;

④ 统计所有的银行卡中，储蓄卡和信用卡的各自数量。

SELECT b_type,COUNT(b_type)
FROM bank_card
GROUP BY b_type;

⑤ 查询保险表中，保险价格的平均值。

SELECT AVG(i_price)
FROM insurance;

⑥ 查询保险表中保险价格的最大值和最小值所对应的险种和价格。

SELECT i_name,i_price
FROM insurance
GROUP BY i_id
HAVING i_price = (SELECT MAX(i_price) FROM insurance) or i_price = (SELECT MIN(i_price) FROM insurance);

⑦ 某人捡到一张卡，希望查询该银行卡号是’6222021302020000006’的客户编号，姓名和联系电话。

SELECT c_id,c_name,c_phone
FROM customer
WHERE c_id_card = '6222021302020000006';

⑧ 查询保险产品中保险价格大于平均值的保险名称和适用人群。

SELECT i_name,i_person,i_price
FROM insurance
GROUP BY i_id
HAVING i_price > (SELECT AVG(i_price) FROM insurance);

⑨ 查询C银行发布的理财产品总数，按照p_year分组。

SELECT p_year,count(*)
FROM finances_product
GROUP BY p_year;

⑩ 查询适用于老人的保险编号，保险名称，保险年限。

SELECT i_id,i_name,i_year
FROM insurance
WHERE i_person = '老人';

(4) 模拟以下的业务创建视图并基于视图进行查询。（2分）
① 创建一个视图，包含拥有银行卡的所有客户编号，姓名, 身份证号, 拥有的银行卡个数。

CREATE VIEW V_BANK_CARD
AS
	SELECT c_id,c_name,c_id_card,count(*)
	FROM customer,bank_card
	WHERE c_id = b_c_id
	GROUP BY c_id;

② 修改视图：在原有视图的基础上，仅包含拥有信用卡的用户。

ALTER VIEW V_BANK_CARD
AS
	SELECT c_id,c_name,c_id_card,count(*)
	FROM customer,bank_card
	WHERE c_id = b_c_id AND b_type = '信用卡'
	GROUP BY c_id;

(5) 模拟业务变化，人们对基金查询的需求大幅度增加。在基金购买表上创建复合索引：c_id ASC, f_id ASC, f_quantity DESC。（3分）

ALTER TABLE c_fund
ADD INDEX composite_index (c_id ASC, f_id ASC, f_quantity DESC) VISIBLE;