mysql存储过程 根据查询的结果集向表中插入数据

于 2019-10-26 23:00:26 发布
阅读量6k 收藏 14
点赞数 4
CC 4.0 BY-SA版权
分类专栏： mysql 文章标签： mysql存储过程
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/a_teacher_java/article/details/102761585
本文介绍如何使用MySQL存储过程来统计课程成绩的平均分、最低分、最高分及不及格人数，并将结果插入成绩表。通过创建游标和定义变量，实现对分数表数据的高效处理。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

需求：通过查询分数表统计成绩，并将统计好的成绩插入另一张表。

分数表（score）详细信息如下：

说明：分数表本应该引用课程id（course_id）作为外键，此处重点在于练习mysql的存储过程，为了能够直观的看到结果，所以此处使用课程名称作为分数表的列，也没有创建课程表（备注：测试数据只需保证课程名称不重复即可）。

DROP TABLE IF EXISTS `score`;

CREATE TABLE `score` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `course_name` varchar(32) DEFAULT NULL,
  `student_num` int(11) DEFAULT NULL,
  `score` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=131 DEFAULT CHARSET=utf8;

分数表的部分数据，这些数据为测试数据方便确定结果。
在这里插入图片描述

成绩（统计）表：

该表为结果表，具体字段如下

DROP TABLE IF EXISTS `grades`;

CREATE TABLE `grades` (
  `course_name` varchar(32) NOT NULL,
  `avg` float DEFAULT NULL,
  `min` float DEFAULT NULL,
  `max` float DEFAULT NULL,
  `lose` int(11) DEFAULT NULL,
  PRIMARY KEY (`course_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

该表初始状态下为一张空表，用于保存存储过程执行后的数据，下图为存储过程执行完成后的结果图。

成绩表统计了课程的平均分（avg）、最低分（min）、最高分（max）以及不及格人数（lose）在这里插入图片描述

(重点)创建存储过程

明确目的：通过创建存储过程，查询出各科目的平均分（avg）、最低分（min）、最高分（max）以及不及格人数（lose），并且插入到成绩表（grades）中。

-- 如果存在重名的存储过程 先删除后 再创建
DROP PROCEDURE IF EXISTS pro_test;

DELIMITER $
-- 创建存储过程
CREATE PROCEDURE pro_test()
BEGIN
	-- 需要定义接收游标数据的变量 
	DECLARE done BOOLEAN DEFAULT 0;

	DECLARE v_avg FLOAT;-- 平均分
	DECLARE v_max INT;-- 最高分
	DECLARE v_min INT; -- 最低分
	DECLARE v_lose INT; -- 考试不及格的人数
	DECLARE v_courseName VARCHAR(32); -- 课程名称
	
 -- 定义游标
	DECLARE cur1 CURSOR FOR SELECT course_name, AVG(score) v_avg, MAX(score) v_max, MIN(score) v_min FROM score GROUP BY course_name;
	
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
	
	-- 使用任意一种方式定义都可以
	-- DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
	
 -- 打开游标
	OPEN cur1;
 -- 开始循环
	REPEAT
		FETCH cur1 INTO v_courseName,v_avg,v_max,v_min;
		
		-- 判断是否读到游标末尾 如果不加这个条件 插入的数据会增多1条
		IF done <1 OR done >1 THEN
					
			-- 查询每个学科不及格人数		
			SELECT COUNT(1) INTO v_lose FROM score WHERE v_courseName = course_name AND score<60;	
			
			INSERT INTO grades VALUES(v_courseName,v_avg,v_max,v_min,v_lose);
		END IF;
		
		UNTIL done = 1

	END REPEAT;
-- 循环结束

 -- 关闭游标
  CLOSE cur1 ;
END $

-- 清空结果表数据
TRUNCATE TABLE grades;

-- 执行存储过程
CALL pro_test();

SELECT * FROM grades;

SELECT course_name, AVG(score) 'avg', MIN(score) 'min', MAX(score) 'max' FROM score GROUP BY course_name;

最后贴上全部sql

/*
SQLyog Ultimate v11.27 (32 bit)
MySQL - 5.7.28-log : Database - test
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `test`;

/*Table structure for table `grades` */

DROP TABLE IF EXISTS `grades`;

CREATE TABLE `grades` (
  `course_name` varchar(32) NOT NULL,
  `avg` float DEFAULT NULL,
  `min` float DEFAULT NULL,
  `max` float DEFAULT NULL,
  `lose` int(11) DEFAULT NULL,
  PRIMARY KEY (`course_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `grades` */

/*Table structure for table `score` */

DROP TABLE IF EXISTS `score`;

CREATE TABLE `score` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `course_name` varchar(32) DEFAULT NULL,
  `student_num` int(11) DEFAULT NULL,
  `score` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=131 DEFAULT CHARSET=utf8;

/*Data for the table `score` */

insert  into `score`(`id`,`course_name`,`student_num`,`score`) values (1,'H5前端',1,84),(2,'java基础',1,56),(3,'需求分析',1,42),(4,'大学英语',1,68),(5,'操作系统',1,56),(6,'编译原理',1,65),(7,'软件测试',1,34),(8,'Android',1,23),(9,'spring',1,78),(10,'mysql',1,96),(11,'vue.js',1,45),(12,'linux',1,96),(13,'hadoop',1,75),(14,'H5前端',2,52),(15,'java基础',2,54),(16,'需求分析',2,45),(17,'大学英语',2,68),(18,'操作系统',2,79),(19,'编译原理',2,35),(20,'软件测试',2,89),(21,'Android',2,55),(22,'spring',2,66),(23,'mysql',2,89),(24,'vue.js',2,87),(25,'linux',2,43),(26,'hadoop',2,90),(27,'H5前端',3,24),(28,'java基础',3,58),(29,'需求分析',3,30),(30,'大学英语',3,64),(31,'操作系统',3,89),(32,'编译原理',3,21),(33,'软件测试',3,47),(34,'Android',3,86),(35,'spring',3,35),(36,'mysql',3,88),(37,'vue.js',3,74),(38,'linux',3,38),(39,'hadoop',3,96),(40,'H5前端',4,55),(41,'java基础',4,78),(42,'需求分析',4,94),(43,'大学英语',4,81),(44,'操作系统',4,68),(45,'编译原理',4,70),(46,'软件测试',4,56),(47,'Android',4,30),(48,'spring',4,79),(49,'mysql',4,99),(50,'vue.js',4,75),(51,'linux',4,32),(52,'hadoop',4,45),(53,'H5前端',5,84),(54,'java基础',5,56),(55,'需求分析',5,42),(56,'大学英语',5,68),(57,'操作系统',5,47),(58,'编译原理',5,65),(59,'软件测试',5,34),(60,'Android',5,23),(61,'spring',5,78),(62,'mysql',5,96),(63,'vue.js',5,45),(64,'linux',5,96),(65,'hadoop',5,75),(66,'H5前端',6,84),(67,'java基础',6,56),(68,'需求分析',6,42),(69,'大学英语',6,68),(70,'操作系统',6,47),(71,'编译原理',6,65),(72,'软件测试',6,34),(73,'Android',6,23),(74,'spring',6,78),(75,'mysql',6,96),(76,'vue.js',6,45),(77,'linux',6,96),(78,'hadoop',6,75),(79,'H5前端',7,84),(80,'java基础',7,56),(81,'需求分析',7,42),(82,'大学英语',7,68),(83,'操作系统',7,47),(84,'编译原理',7,65),(85,'软件测试',7,38),(86,'Android',7,56),(87,'spring',7,45),(88,'mysql',7,82),(89,'vue.js',7,50),(90,'linux',7,96),(91,'hadoop',7,100),(92,'H5前端',8,45),(93,'java基础',8,65),(94,'需求分析',8,35),(95,'大学英语',8,85),(96,'操作系统',8,69),(97,'编译原理',8,56),(98,'软件测试',8,78),(99,'Android',8,54),(100,'spring',8,56),(101,'mysql',8,52),(102,'vue.js',8,99),(103,'linux',8,100),(104,'hadoop',8,96),(105,'H5前端',9,78),(106,'java基础',9,45),(107,'需求分析',9,86),(108,'大学英语',9,75),(109,'操作系统',9,12),(110,'编译原理',9,53),(111,'软件测试',9,68),(112,'Android',9,78),(113,'spring',9,24),(114,'mysql',9,78),(115,'vue.js',9,15),(116,'linux',9,96),(117,'hadoop',9,23),(118,'H5前端',10,98),(119,'java基础',10,99),(120,'需求分析',10,96),(121,'大学英语',10,68),(122,'操作系统',10,65),(123,'编译原理',10,78),(124,'软件测试',10,89),(125,'Android',10,87),(126,'spring',10,78),(127,'mysql',10,35),(128,'vue.js',10,87),(129,'linux',10,78),(130,'hadoop',10,56);

/* Procedure structure for procedure `pro_test` */

/*!50003 DROP PROCEDURE IF EXISTS  `pro_test` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_test`()
BEGIN
	-- 需要定义接收游标数据的变量 
	DECLARE done boolean DEFAULT 0;
	DECLARE v_avg float;-- 平均分
	DECLARE v_max INT;-- 最高分
	DECLARE v_min INT; -- 最低分
	DECLARE v_lose INT; -- 考试不及格的人数
	DECLARE v_courseName varchar(32); -- 课程名称
	
 -- 定义游标
	DECLARE cur1 CURSOR FOR SELECT course_name, avg(score) v_avg, MAX(score) v_max, MIN(score) v_min FROM score GROUP BY course_name;
	
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
	
	-- DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
	
 -- 打开游标
	OPEN cur1;
 -- 开始循环
	REPEAT
		FETCH cur1 INTO v_courseName,v_avg,v_max,v_min;
		
		if done <1 or done >1 then
					
			SELECT COUNT(1) into v_lose FROM score WHERE v_courseName = course_name and score<60;	
			
			INSERT INTO grades VALUES(v_courseName,v_avg,v_max,v_min,v_lose);
		end if;
		
		UNTIL done = 1
	END REPEAT;
-- 循环结束
 -- 关闭游标
  CLOSE cur1 ;
END */$$
DELIMITER ;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值