當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

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

發(fā)布時間：2024/1/8 数据库 31 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql数据库基础评分标准_《MYSQL高级查询与编程》综合机试试卷 - 云南农职美和易思... 小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

題目：銀行mysql數(shù)據(jù)庫系統(tǒng)管理

一、語言和環(huán)境

實(shí)現(xiàn)語言：sql。

開發(fā)環(huán)境：mySql,navicat。

二、題目(100分)：

功能需求：

a、創(chuàng)建數(shù)據(jù)庫，名稱為myBank；

b、創(chuàng)建數(shù)據(jù)表customer(客戶)、deposite(存款)、bank(銀行)，表結(jié)構(gòu)如下:

customer的表結(jié)構(gòu)

deposite的表結(jié)構(gòu)

bank的表結(jié)構(gòu)

c、錄入以下數(shù)據(jù)

customer的數(shù)據(jù)如下：

bank的數(shù)據(jù)如下：

deposite的數(shù)據(jù)如下：

要求：

對deposite表進(jìn)行統(tǒng)計(jì)，按銀行統(tǒng)計(jì)存款總數(shù)，顯示為b_id, bank_name,?total；(注：將結(jié)果保存為視圖，并將結(jié)果截圖，視圖和圖片命名為answer01);

對deposite, customer, bank進(jìn)行查詢，查詢條件為location在廣州、蘇州、濟(jì)南的客戶，存款在300000至500000之間的存款記錄，顯示客戶姓名name、銀行名稱bank name、存款金額amount；將結(jié)果保存在視圖中；(注：將結(jié)果保存為視圖，并將結(jié)果截圖，視圖和圖片命名為answer02)

查詢在農(nóng)業(yè)銀行存款前2名的客戶存款信息(顯示信息：客戶姓名，銀行名稱，存款金額)；(注：將結(jié)果保存為視圖，并將結(jié)果截圖，視圖和圖片命名為answer03);

更新customer表的salary屬性，將salary低于5000的客戶的salary?????變?yōu)樵瓉淼?倍(注：執(zhí)行你所編寫sql語句，檢查結(jié)果變化,不需要保存sql，將更新前的數(shù)據(jù)截圖保存為answer04(old)，更新后的數(shù)據(jù)截圖為answer04(new));

查詢?nèi)掌跒?011-04-05這一天到目前進(jìn)行過存款的客戶ID，客戶姓名，銀行名稱，存款金額，按存款金額降序排列；將結(jié)果保存在視圖中(注：將結(jié)果保存為視圖，并將結(jié)果截圖，視圖和圖片命名為answer05);

查詢郭海在工商銀行的存款信息(顯示信息：客戶id，客戶姓名，銀行標(biāo)識，銀行名稱，存款日期，存款金額)(注：將結(jié)果保存為視圖，并將結(jié)果截圖，視圖和圖片命名為answer06);

創(chuàng)建存儲過程，使用int類型傳入?yún)?shù)10000，并查詢工商銀行存款大于等于一萬的客戶姓名，金額，存款期限(查詢使用表鏈接和子查詢兩種方式實(shí)現(xiàn))(注：sql語句以 .txt文件保存，命名為answer07);

注：保存為視圖的2種方式：

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

二、使用工具創(chuàng)建視圖，保存；

推薦實(shí)現(xiàn)步驟

打開Navicat，創(chuàng)建數(shù)據(jù)庫myBank;

創(chuàng)建數(shù)據(jù)表customer(客戶)、deposite(存款)、bank(銀行)，建表時注意 ?主鍵、外健以及相關(guān)約束、類型和長度、并根據(jù)以上表格數(shù)據(jù)進(jìn)行數(shù)據(jù)的錄入(可用sql語句，也可使用navicat直接填入數(shù)據(jù))。

按要求完成查詢和修改將sql語句根據(jù)相應(yīng)的備注信息保存查詢或視圖；sql語句可以外聯(lián)或子查詢，方法不做限定；建立視圖可以直接用navicat視圖工具也可以寫代碼創(chuàng)建。

三、提交方式

文件以壓縮包提交, 壓縮包文件命名方式 :學(xué)號+中文名字.zip, 比如:193610202139張玉苗.zip，壓縮包必需在按規(guī)定的時間以內(nèi), 按監(jiān)考老師的要求提交.

四、評分標(biāo)準(zhǔn):

題目：銀行管理系統(tǒng)

該程序評分標(biāo)準(zhǔn)如下：

數(shù)據(jù)庫和表結(jié)構(gòu)的正確創(chuàng)建，并根據(jù)提示正確錄入數(shù)據(jù)

數(shù)據(jù)庫的創(chuàng)建(包含命名)

正確創(chuàng)建表結(jié)構(gòu)(包括表的命名和主鍵、外檢以及非空約束)

正確錄入數(shù)據(jù)

根據(jù)要求查詢或修改并保存查詢或建立視圖

要求1，結(jié)果正確

要求2，結(jié)果正確

要求3，結(jié)果正確

要求4，結(jié)果正確

要求5，結(jié)果正確

要求6，結(jié)果正確

要求7，結(jié)果正確

總分

100分

五、實(shí)現(xiàn)代碼：

創(chuàng)建表結(jié)構(gòu)：

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

-- 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`)

);

插入數(shù)據(jù)：

bank表：

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

INSERT INTO `bank` VALUES ('B0002', '建設(shè)銀行');

INSERT INTO `bank` VALUES ('B0003', '中國銀行');

INSERT INTO `bank` VALUES ('B0004', '農(nóng)業(yè)銀行');

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', '郭惠', '濟(jì)南', '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表進(jìn)行統(tǒng)計(jì)，按銀行統(tǒng)計(jì)存款總數(shù)，顯示為b_id, bank_name,?total；(注：將結(jié)果保存為視圖，并將結(jié)果截圖，視圖和圖片命名為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進(jìn)行查詢，查詢條件為location在廣州、蘇州、濟(jì)南的客戶，存款在300000至500000之間的存款記錄，顯示客戶姓名name、銀行名稱bank name、存款金額amount；將結(jié)果保存在視圖中；(注：將結(jié)果保存為視圖，并將結(jié)果截圖，視圖和圖片命名為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 ('廣州','蘇州','濟(jì)南') AND (amount BETWEEN 300000 AND 500000);

3、查詢在農(nóng)業(yè)銀行存款前2名的客戶存款信息(顯示信息：客戶姓名，銀行名稱，存款金額)；(注：將結(jié)果保存為視圖，并將結(jié)果截圖，視圖和圖片命名為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='農(nóng)業(yè)銀行' AND d.c_id=c.c_id LIMIT 2;

4、更新customer表的salary屬性，將salary低于5000的客戶的salary?????變?yōu)樵瓉淼?倍(注：執(zhí)行你所編寫sql語句，檢查結(jié)果變化,不需要保存sql，將更新前的數(shù)據(jù)截圖保存為answer04(old)，更新后的數(shù)據(jù)截圖為answer04(new));

SELECT * FROM customer WHERE salary<5000;

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

SELECT * FROM customer WHERE salary;

5、查詢?nèi)掌跒?011-04-05這一天到目前進(jìn)行過存款的客戶ID，客戶姓名，銀行名稱，存款金額，按存款金額降序排列；將結(jié)果保存在視圖中(注：將結(jié)果保存為視圖，并將結(jié)果截圖，視圖和圖片命名為answer05);

CREATE VIEW answer05 AS

SELECT c.c_id 客戶id,name 客戶姓名,b.b_id 銀行標(biāo)識,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，客戶姓名，銀行標(biāo)識，銀行名稱，存款日期，存款金額)(注：將結(jié)果保存為視圖，并將結(jié)果截圖，視圖和圖片命名為answer06);

CREATE VIEW answer06 AS

SELECT c.c_id 客戶id,name 客戶姓名,b.b_id 銀行標(biāo)識,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、創(chuàng)建存儲過程，使用int類型傳入?yún)?shù)10000，并查詢工商銀行存款大于等于一萬的客戶姓名，金額，存款期限(查詢使用表鏈接和子查詢兩種方式實(shí)現(xiàn))(注：sql語句以 .txt文件保存，命名為answer07);

-- 使用表連接查詢的方式創(chuàng)建存儲過程

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 ;

-- 實(shí)現(xiàn)存儲過程

SET @num=10000;

CALL answer07(@num);

數(shù)據(jù)庫完整結(jié)構(gòu)和代碼：

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', '建設(shè)銀行');

INSERT INTO `bank` VALUES ('B0003', '中國銀行');

INSERT INTO `bank` VALUES ('B0004', '農(nóng)業(yè)銀行');

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

-- 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', '郭惠', '濟(jì)南', '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