学生运动会管理系统制作求解答

Question

如图是关于学生运动会相关的管理系统制作及其相关要求，请求各位帮助解答。

Answer 1

创建数据库与插入数据sql

/*
 Navicat Premium Data Transfer

 Source Server         : 翰和商城积分后台相关
 Source Server Type    : MySQL
 Source Server Version : 80012
 Source Host           : 47.92.64.19:3306
 Source Schema         : test20221026

 Target Server Type    : MySQL
 Target Server Version : 80012
 File Encoding         : 65001

 Date: 26/10/2022 19:25:19
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for banji
-- ----------------------------
DROP TABLE IF EXISTS `banji`;
CREATE TABLE `banji`  (
  `banNum` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '班号',
  `banName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '班级名称',
  `banMajor` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '专业',
  `banNumber` int(11) NULL DEFAULT NULL COMMENT '人数',
  INDEX `banNum`(`banNum`) USING BTREE,
  INDEX `banNum_2`(`banNum`) USING BTREE,
  INDEX `banNum_3`(`banNum`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of banji
-- ----------------------------
INSERT INTO `banji` VALUES ('11', '一年级一班', '法学', 30);
INSERT INTO `banji` VALUES ('12', '一年级二班', '法学', 31);
INSERT INTO `banji` VALUES ('21', '二年级一班', '法学', 32);
INSERT INTO `banji` VALUES ('22', '二年级二班', '法学', 30);
INSERT INTO `banji` VALUES ('31', '三年级一班', '法学', 28);
INSERT INTO `banji` VALUES ('32', '三年级二班', '法学', 35);

-- ----------------------------
-- Table structure for cj
-- ----------------------------
DROP TABLE IF EXISTS `cj`;
CREATE TABLE `cj`  (
  `ydyNum` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '运动员号',
  `xmNum` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '项目号',
  `cjNum` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '成绩',
  `cjRank` int(255) NULL DEFAULT NULL COMMENT '获奖名次',
  INDEX `ydy_P`(`ydyNum`) USING BTREE,
  INDEX `xm_P`(`xmNum`) USING BTREE,
  CONSTRAINT `xm_P` FOREIGN KEY (`xmNum`) REFERENCES `xm` (`xmnum`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `ydy_P` FOREIGN KEY (`ydyNum`) REFERENCES `ydy` (`ydyNum`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of cj
-- ----------------------------
INSERT INTO `cj` VALUES ('001', '1003', '98', 1);
INSERT INTO `cj` VALUES ('002', '1003', '97', 2);
INSERT INTO `cj` VALUES ('003', '1003', '95', 3);
INSERT INTO `cj` VALUES ('004', '1001', '100', 1);
INSERT INTO `cj` VALUES ('005', '1002', '99', 1);
INSERT INTO `cj` VALUES ('004', '1004', '98', 1);

-- ----------------------------
-- Table structure for xm
-- ----------------------------
DROP TABLE IF EXISTS `xm`;
CREATE TABLE `xm`  (
  `xmNum` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '项目号',
  `xmName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '项目名称',
  `xmPlace` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '项目比赛地点',
  INDEX `xmNum`(`xmNum`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of xm
-- ----------------------------
INSERT INTO `xm` VALUES ('1001', '跳高', '体育馆2楼');
INSERT INTO `xm` VALUES ('1002', '跳远', '体育馆2楼');
INSERT INTO `xm` VALUES ('1004', '200米', '操场');
INSERT INTO `xm` VALUES ('1005', '110跨栏', '操场');
INSERT INTO `xm` VALUES ('1006', '800米', '操场');
INSERT INTO `xm` VALUES ('1003', '100米', '操场');

-- ----------------------------
-- Table structure for ydy
-- ----------------------------
DROP TABLE IF EXISTS `ydy`;
CREATE TABLE `ydy`  (
  `ydyNum` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '运动员号',
  `ydyName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '运动员姓名',
  `ydySex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '运动员性别',
  `ydyAge` int(255) NULL DEFAULT NULL COMMENT '运动员年龄',
  `banNum` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '班号',
  INDEX `ydyNum`(`ydyNum`) USING BTREE,
  INDEX `bj_P`(`banNum`) USING BTREE,
  CONSTRAINT `bj_P` FOREIGN KEY (`banNum`) REFERENCES `banji` (`bannum`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of ydy
-- ----------------------------
INSERT INTO `ydy` VALUES ('001', '张三', '男', 18, '11');
INSERT INTO `ydy` VALUES ('002', '李四', '男', 17, '12');
INSERT INTO `ydy` VALUES ('003', '王五', '男', 18, '11');
INSERT INTO `ydy` VALUES ('004', '周一', '男', 18, '21');
INSERT INTO `ydy` VALUES ('005', '周二', '女', 17, '31');
INSERT INTO `ydy` VALUES ('006', '杨戬', '男', 17, '32');
INSERT INTO `ydy` VALUES ('007', '孙悟空', '男', 18, '22');

SET FOREIGN_KEY_CHECKS = 1;


/**
3.1查询项目表所有记录*/
select * from xm;

/**
3.2查询排名前五运动员信息及获奖项目*/
select b.ydyName,c.xmName,a.cjRank from cj  as a
inner join ydy as b on a.ydyNum = b.ydyNum
inner join xm as c on a.xmNum = c.xmNum
order by a.cjRank limit 5;

/**
3.3查询获奖运动员所在班级名称(前三名的为获奖人员，所以cjRank字段小于等于3)*/
select b.ydyName 获奖人员,c.banName 班级名称,d.xmName 项目名称 from cj  as a
inner join ydy as b on a.ydyNum = b.ydyNum
inner join banji as c on b.banNum = c.banNum
inner join xm as d on a.xmNum = d.xmNum
where a.cjRank<=3;

/**
3.4查询各班运动员人数*/
select b.banName 班级名称,count(1) 人数 from ydy as a 
inner join banji as b on a.banNum = b.banNum
group by a.banNum;

/**
4.1查询运动员成绩、名次、获奖项目名(前三名的为获奖人员，所以cjRank字段小于等于3)*/
select a.ydyName 获奖人员,b.cjNum 获奖成绩,b.cjRank 获奖名次,c.xmName 项目名称 from ydy as a 
inner join cj as b on a.ydyNum = b.ydyNum
inner join xm as c on b.xmNum = c.xmNum
where b.cjRank<=3;

/**
4.2查询项目运动员人数*/
select c.xmName 项目名称,count(1) 人数 from ydy as a 
inner join cj as b on a.ydyNum = b.ydyNum
inner join xm as c on b.xmNum = c.xmNum
group by c.xmName

/**
4.3查询统计参加项目没获奖的人数（名次3名开外的属于未获奖）*/
select count(1) 未获奖人数 from cj where cjRank>3

/**
4.4删除参加项目没获奖的运动员信息*/
delete from ydy where ydyNum in (select ydyNum from cj where cjRank>3)