需求：通过查询分数表统计成绩，并将统计好的成绩插入另一张表。
分数表（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;-- 开始循环REPEATFETCH 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 = 1END 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;-- 开始循环REPEATFETCH cur1 INTO v_courseName,v_avg,v_max,v_min;if done <1 or done >1 thenSELECT 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 = 1END 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 */;
