当前位置：首页 > 运维知识 > 数据库 >内容正文

数据库

mysql数据库基础评分标准_《MYSQL高级查询与编程》综合机试试卷 - 云南农职美和易思...

发布时间：2024/1/8 数据库 48 豆豆

生活随笔收集整理的这篇文章主要介绍了 mysql数据库基础评分标准_《MYSQL高级查询与编程》综合机试试卷 - 云南农职美和易思... 小编觉得挺不错的,现在分享给大家,帮大家做个参考.

题目：银行mysql数据库系统管理

一、语言和环境

实现语言：sql。

开发环境：mySql,navicat。

二、题目(100分)：

功能需求：

a、创建数据库，名称为myBank；

b、创建数据表customer(客户)、deposite(存款)、bank(银行)，表结构如下:

customer的表结构

deposite的表结构

bank的表结构

c、录入以下数据

customer的数据如下：

bank的数据如下：

deposite的数据如下：

要求：

对deposite表进行统计，按银行统计存款总数，显示为b_id, bank_name, total；(注：将结果保存为视图，并将结果截图，视图和图片命名为answer01);

对deposite, customer, bank进行查询，查询条件为location在广州、苏州、济南的客户，存款在300000至500000之间的存款记录，显示客户姓名name、银行名称bank name、存款金额amount；将结果保存在视图中；(注：将结果保存为视图，并将结果截图，视图和图片命名为answer02)

查询在农业银行存款前2名的客户存款信息(显示信息：客户姓名，银行名称，存款金额)；(注：将结果保存为视图，并将结果截图，视图和图片命名为answer03);

更新customer表的salary属性，将salary低于5000的客户的salary 变为原来的2倍(注：执行你所编写sql语句，检查结果变化,不需要保存sql，将更新前的数据截图保存为answer04(old)，更新后的数据截图为answer04(new));

查询日期为2011-04-05这一天到目前进行过存款的客户ID，客户姓名，银行名称，存款金额，按存款金额降序排列；将结果保存在视图中(注：将结果保存为视图，并将结果截图，视图和图片命名为answer05);

查询郭海在工商银行的存款信息(显示信息：客户id，客户姓名，银行标识，银行名称，存款日期，存款金额)(注：将结果保存为视图，并将结果截图，视图和图片命名为answer06);

创建存储过程，使用int类型传入参数10000，并查询工商银行存款大于等于一万的客户姓名，金额，存款期限(查询使用表链接和子查询两种方式实现)(注：sql语句以 .txt文件保存，命名为answer07);

注：保存为视图的2种方式：

一、create view view_name as select column_name from table_name where condition；

二、使用工具创建视图，保存；

推荐实现步骤

打开Navicat，创建数据库myBank;

创建数据表customer(客户)、deposite(存款)、bank(银行)，建表时注意主键、外健以及相关约束、类型和长度、并根据以上表格数据进行数据的录入(可用sql语句，也可使用navicat直接填入数据)。

按要求完成查询和修改将sql语句根据相应的备注信息保存查询或视图；sql语句可以外联或子查询，方法不做限定；建立视图可以直接用navicat视图工具也可以写代码创建。

三、提交方式

文件以压缩包提交, 压缩包文件命名方式 :学号+中文名字.zip, 比如:193610202139张玉苗.zip，压缩包必需在按规定的时间以内, 按监考老师的要求提交.

四、评分标准:

题目：银行管理系统

该程序评分标准如下：

数据库和表结构的正确创建，并根据提示正确录入数据

数据库的创建(包含命名)

正确创建表结构(包括表的命名和主键、外检以及非空约束)

正确录入数据

根据要求查询或修改并保存查询或建立视图

要求1，结果正确

要求2，结果正确

要求3，结果正确

要求4，结果正确

要求5，结果正确

要求6，结果正确

要求7，结果正确

总分

100分

五、实现代码：

创建表结构：

-- ----------------------------

-- Table structure for bank

-- ----------------------------

CREATE TABLE `bank` (

`b_id` char(5) NOT NULL,

`bank_name` char(30) NOT NULL,

PRIMARY KEY (`b_id`)

);

-- ----------------------------

-- Table structure for customer

-- ----------------------------

CREATE TABLE `customer` (

`c_id` char(6) NOT NULL,

`name` varchar(30) NOT NULL,

`location` varchar(30) DEFAULT NULL,

`salary` double(8,2) DEFAULT NULL,

PRIMARY KEY (`c_id`)

);

-- ----------------------------

-- Table structure for deposite

-- ----------------------------

CREATE TABLE `deposite` (

`d_id` int(11) NOT NULL,

`c_id` char(6) DEFAULT NULL,

`b_id` char(5) DEFAULT NULL,

`dep_date` date DEFAULT NULL,

`dep_type` int(11) DEFAULT NULL,

`amount` double(10,3) DEFAULT NULL,

PRIMARY KEY (`d_id`)

);

插入数据：

bank表：

INSERT INTO `bank` VALUES ('B0001', '工商银行');

INSERT INTO `bank` VALUES ('B0002', '建设银行');

INSERT INTO `bank` VALUES ('B0003', '中国银行');

INSERT INTO `bank` VALUES ('B0004', '农业银行');

customer表：

INSERT INTO `customer` VALUES ('101001', '孙杨', '广州', '1234.00');

INSERT INTO `customer` VALUES ('101002', '郭海', '南京', '3526.00');

INSERT INTO `customer` VALUES ('101003', '卢江', '苏州', '6892.00');

INSERT INTO `customer` VALUES ('101004', '郭惠', '济南', '3492.00');

deposite表：

INSERT INTO `deposite` VALUES ('1', '101001', 'B0001', '2011-04-05', '3', '42526.000');

INSERT INTO `deposite` VALUES ('2', '101002', 'B0003', '2012-07-15', '5', '66500.000');

INSERT INTO `deposite` VALUES ('3', '101003', 'B0002', '2010-11-24', '1', '42366.000');

INSERT INTO `deposite` VALUES ('4', '101004', 'B0004', '2008-03-31', '1', '62362.000');

INSERT INTO `deposite` VALUES ('5', '101001', 'B0003', '2002-02-07', '3', '56346.000');

INSERT INTO `deposite` VALUES ('6', '101002', 'B0001', '2004-09-23', '3', '353626.000');

INSERT INTO `deposite` VALUES ('7', '101003', 'B0004', '2003-12-14', '5', '36236.000');

INSERT INTO `deposite` VALUES ('8', '101004', 'B0002', '2007-04-21', '5', '26267.000');

INSERT INTO `deposite` VALUES ('9', '101001', 'B0002', '2011-02-11', '1', '435456.000');

INSERT INTO `deposite` VALUES ('10', '101002', 'B0004', '2012-05-13', '1', '234626.000');

INSERT INTO `deposite` VALUES ('11', '101003', 'B0003', '2001-01-24', '5', '26243.000');

INSERT INTO `deposite` VALUES ('12', '101004', 'B0001', '2009-08-23', '3', '45671.000');

1、对deposite表进行统计，按银行统计存款总数，显示为b_id, bank_name, total；(注：将结果保存为视图，并将结果截图，视图和图片命名为answer01);

CREATE VIEW answer01 AS

SELECT b.b_id b_id, b.bank_name bank_name,SUM(d.amount) total

FROM deposite d,bank b

WHERE d.b_id=b.b_id GROUP BY b.bank_name;

2、对deposite, customer, bank进行查询，查询条件为location在广州、苏州、济南的客户，存款在300000至500000之间的存款记录，显示客户姓名name、银行名称bank name、存款金额amount；将结果保存在视图中；(注：将结果保存为视图，并将结果截图，视图和图片命名为answer02)

CREATE VIEW answer02 AS

SELECT name 客户姓名,bank_name 银行姓名,amount 存款金额

FROM deposite d,customer c,bank b

WHERE b.b_id=d.b_id AND d.c_id=c.c_id

AND location IN ('广州','苏州','济南') AND (amount BETWEEN 300000 AND 500000);

3、查询在农业银行存款前2名的客户存款信息(显示信息：客户姓名，银行名称，存款金额)；(注：将结果保存为视图，并将结果截图，视图和图片命名为answer03);

CREATE VIEW answer03 AS

SELECT name 客户姓名,bank_name 银行名称,amount 存款金额

FROM deposite d,customer c,bank b

WHERE b.b_id=d.b_id AND bank_name='农业银行' AND d.c_id=c.c_id LIMIT 2;

4、更新customer表的salary属性，将salary低于5000的客户的salary 变为原来的2倍(注：执行你所编写sql语句，检查结果变化,不需要保存sql，将更新前的数据截图保存为answer04(old)，更新后的数据截图为answer04(new));

SELECT * FROM customer WHERE salary<5000;

UPDATE customer SET salary=(salary*2) WHERE salary<5000;

SELECT * FROM customer WHERE salary;

5、查询日期为2011-04-05这一天到目前进行过存款的客户ID，客户姓名，银行名称，存款金额，按存款金额降序排列；将结果保存在视图中(注：将结果保存为视图，并将结果截图，视图和图片命名为answer05);

CREATE VIEW answer05 AS

SELECT c.c_id 客户id,name 客户姓名,b.b_id 银行标识,b.bank_name 银行名称,amount 存款金额

FROM deposite d,customer c,bank b

WHERE b.b_id=d.b_id AND d.c_id=c.c_id

AND dep_date>='2011-04-05' ORDER BY amount;

6、查询郭海在工商银行的存款信息(显示信息：客户id，客户姓名，银行标识，银行名称，存款日期，存款金额)(注：将结果保存为视图，并将结果截图，视图和图片命名为answer06);

CREATE VIEW answer06 AS

SELECT c.c_id 客户id,name 客户姓名,b.b_id 银行标识,b.bank_name 银行名称,dep_date 存款日期,amount 存款金额

FROM deposite d,customer c,bank b

WHERE b.b_id=d.b_id AND d.c_id=c.c_id

AND `name`='郭海' AND bank_name='工商银行';

7、创建存储过程，使用int类型传入参数10000，并查询工商银行存款大于等于一万的客户姓名，金额，存款期限(查询使用表链接和子查询两种方式实现)(注：sql语句以 .txt文件保存，命名为answer07);

-- 使用表连接查询的方式创建存储过程

delimiter $$

create procedure answer07(

in num INT

)

begin

SELECT name 客户姓名,amount 金额,dep_type 存款期限

FROM deposite d,customer c,bank b

WHERE b.b_id=d.b_id AND d.c_id=c.c_id

AND bank_name='工商银行' AND amount>=num;

end

delimiter ;

-- 实现存储过程

SET @num=10000;

CALL answer07(@num);

数据库完整结构和代码：

Navicat MySQL Data Transfer

Source Server : test

Source Server Version : 50646

Source Host : localhost:3306

Source Database : mybank

Target Server Type : MYSQL

Target Server Version : 50646

File Encoding : 65001

Date: 2021-01-11 22:39:04

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for bank

-- ----------------------------

DROP TABLE IF EXISTS `bank`;

CREATE TABLE `bank` (

`b_id` char(5) NOT NULL,

`bank_name` char(30) NOT NULL,

PRIMARY KEY (`b_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of bank

-- ----------------------------

INSERT INTO `bank` VALUES ('B0001', '工商银行');

INSERT INTO `bank` VALUES ('B0002', '建设银行');

INSERT INTO `bank` VALUES ('B0003', '中国银行');

INSERT INTO `bank` VALUES ('B0004', '农业银行');

-- ----------------------------

-- Table structure for customer

-- ----------------------------

DROP TABLE IF EXISTS `customer`;

CREATE TABLE `customer` (

`c_id` char(6) NOT NULL,

`name` varchar(30) NOT NULL,

`location` varchar(30) DEFAULT NULL,

`salary` double(8,2) DEFAULT NULL,

PRIMARY KEY (`c_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of customer

-- ----------------------------

INSERT INTO `customer` VALUES ('101001', '孙杨', '广州', '1234.00');

INSERT INTO `customer` VALUES ('101002', '郭海', '南京', '3526.00');

INSERT INTO `customer` VALUES ('101003', '卢江', '苏州', '6892.00');

INSERT INTO `customer` VALUES ('101004', '郭惠', '济南', '3492.00');

-- ----------------------------

-- Table structure for deposite

-- ----------------------------

DROP TABLE IF EXISTS `deposite`;

CREATE TABLE `deposite` (

`d_id` int(11) NOT NULL,

`c_id` char(6) DEFAULT NULL,

`b_id` char(5) DEFAULT NULL,

`dep_date` date DEFAULT NULL,

`dep_type` int(11) DEFAULT NULL,

`amount` double(10,3) DEFAULT NULL,

PRIMARY KEY (`d_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of deposite