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);
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;
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;
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='工商银行';
-- 使用表连接查询的方式创建存储过程
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
-- ----------------------------
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');
-- ----------------------------
-- View structure for answer01
-- ----------------------------
DROP VIEW IF EXISTS `answer01`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `answer01` AS select `b`.`b_id` AS `b_id`,`b`.`bank_name` AS `bank_name`,sum(`d`.`amount`) AS `total` from (`deposite` `d` join `bank` `b` on((`b`.`b_id` = `d`.`b_id`))) where (`d`.`b_id` = `b`.`b_id`) group by `b`.`bank_name` ;
-- ----------------------------
-- View structure for answer02
-- ----------------------------
DROP VIEW IF EXISTS `answer02`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `answer02` AS select `c`.`name` AS `客户姓名`,`b`.`bank_name` AS `银行姓名`,`d`.`amount` AS `存款金额` from ((`deposite` `d` join `customer` `c`) join `bank` `b`) where ((`b`.`b_id` = `d`.`b_id`) and (`d`.`c_id` = `c`.`c_id`) and (`c`.`location` in ('广州','苏州','济南')) and (`d`.`amount` between 300000 and 500000)) ;
-- ----------------------------
-- View structure for answer03
-- ----------------------------
DROP VIEW IF EXISTS `answer03`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `answer03` AS select `c`.`name` AS `客户姓名`,`b`.`bank_name` AS `银行名称`,`d`.`amount` AS `存款金额` from ((`deposite` `d` join `customer` `c`) join `bank` `b`) where ((`b`.`b_id` = `d`.`b_id`) and (`b`.`bank_name` = '农业银行') and (`d`.`c_id` = `c`.`c_id`)) limit 2 ;
-- ----------------------------
-- View structure for answer05
-- ----------------------------
DROP VIEW IF EXISTS `answer05`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `answer05` AS select `c`.`c_id` AS `客户id`,`c`.`name` AS `客户姓名`,`b`.`b_id` AS `银行标识`,`b`.`bank_name` AS `银行名称`,`d`.`amount` AS `存款金额` from ((`deposite` `d` join `customer` `c`) join `bank` `b`) where ((`b`.`b_id` = `d`.`b_id`) and (`d`.`c_id` = `c`.`c_id`) and (`d`.`dep_date` >= '2011-04-05')) order by `d`.`amount` ;
-- ----------------------------
-- View structure for answer06
-- ----------------------------
DROP VIEW IF EXISTS `answer06`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `answer06` AS select `c`.`c_id` AS `客户id`,`c`.`name` AS `客户姓名`,`b`.`b_id` AS `银行标识`,`b`.`bank_name` AS `银行名称`,`d`.`dep_date` AS `存款日期`,`d`.`amount` AS `存款金额` from ((`deposite` `d` join `customer` `c`) join `bank` `b`) where ((`b`.`b_id` = `d`.`b_id`) and (`d`.`c_id` = `c`.`c_id`) and (`c`.`name` = '郭海') and (`b`.`bank_name` = '工商银行')) ;
-- ----------------------------
-- Procedure structure for answer07
-- ----------------------------
DROP PROCEDURE IF EXISTS `answer07`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` 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 ;