devbox
花生_TL007
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySQL中 DQL语句实训_查询参加计算机基础课程考试的学生成绩高于课程平均分的学生信息的查询代码

作者：花生_TL007 | 2024-06-11 01:32:40

赞

踩

查询参加计算机基础课程考试的学生成绩高于课程平均分的学生信息的查询代码

学习目标

        创建xscjgl数据库，对xscjgl数据库中的xs,kc,xs_kc表进行查询。

背景铺设：创建数据库并填入数据

  1. create database xscjgl;
  2. use xscjgl;
  3. /*
  4.  Navicat Premium Data Transfer
  6.  Source Server         : phonesy
  7.  Source Server Type    : MySQL
  8.  Source Server Version : 50731
  9.  Source Host           : localhost:3306
  10.  Source Schema         : xscjgl
  12.  Target Server Type    : MySQL
  13.  Target Server Version : 50731
  14.  File Encoding         : 65001
  16.  Date: 18/04/2023 09:12:32
  17. */
  18.  
  19. SET NAMES utf8mb4;
  20. SET FOREIGN_KEY_CHECKS = 0;
  21.  
  22. -- ----------------------------
  23. -- Table structure for kc
  24. -- ----------------------------
  25. DROP TABLE IF EXISTS `kc`;
  26. CREATE TABLE `kc`  (
  27.   `课程号` char(3) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  28.   `课程名` char(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  29.   `开课学期` tinyint(1) NOT NULL DEFAULT 1 COMMENT '只能为1-8',
  30.   `学时` tinyint(1) NOT NULL,
  31.   `学分` tinyint(1) NULL DEFAULT NULL,
  32.   PRIMARY KEY (`课程号`) USING BTREE
  33. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
  34.  
  35. -- ----------------------------
  36. -- Records of kc
  37. -- ----------------------------
  38. INSERT INTO `kc` VALUES ('101', '计算机基础', 1, 80, 5);
  39. INSERT INTO `kc` VALUES ('102', '程序设计与语言', 2, 68, 4);
  40. INSERT INTO `kc` VALUES ('206', '离散数学', 4, 68, 4);
  41. INSERT INTO `kc` VALUES ('208', '数据结构', 5, 68, 4);
  42. INSERT INTO `kc` VALUES ('209', '操作系统', 6, 68, 4);
  43. INSERT INTO `kc` VALUES ('210', '计算机原理', 5, 85, 5);
  44. INSERT INTO `kc` VALUES ('212', '数据库原理', 7, 68, 4);
  45. INSERT INTO `kc` VALUES ('301', '计算机网络', 7, 51, 3);
  46. INSERT INTO `kc` VALUES ('302', '软件工程', 7, 51, 3);
  47.  
  48. -- ----------------------------
  49. -- Table structure for xs
  50. -- ----------------------------
  51. DROP TABLE IF EXISTS `xs`;
  52. CREATE TABLE `xs`  (
  53.   `学号` char(6) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  54.   `姓名` char(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  55.   `专业名` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  56.   `性别` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1为男 0为女',
  57.   `出生时间` date NOT NULL,
  58.   `总学分` tinyint(1) NULL DEFAULT NULL,
  59.   `照片` blob NULL,
  60.   `备注` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  61.   PRIMARY KEY (`学号`) USING BTREE
  62. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
  63.  
  64. -- ----------------------------
  65. -- Records of xs
  66. -- ----------------------------
  67. INSERT INTO `xs` VALUES ('081101', '王林', '计算机', 1, '1990-02-22', 50, NULL, NULL);
  68. INSERT INTO `xs` VALUES ('081102', '程明', '计算机', 1, '1991-02-01', 50, NULL, NULL);
  69. INSERT INTO `xs` VALUES ('081103', '王燕', '计算机', 0, '1989-10-06', 50, NULL, NULL);
  70. INSERT INTO `xs` VALUES ('081104', '韦严平', '计算机', 1, '1990-08-26', 50, NULL, NULL);
  71. INSERT INTO `xs` VALUES ('081106', '李方方', '计算机', 1, '1990-11-20', 50, NULL, NULL);
  72. INSERT INTO `xs` VALUES ('081107', '李明', '计算机', 1, '1990-05-01', 54, NULL, '提前修完《数据结构》，并获学分');
  73. INSERT INTO `xs` VALUES ('081108', '林一帆', '计算机', 1, '1989-08-05', 52, NULL, '已提前修完一门课');
  74. INSERT INTO `xs` VALUES ('081109', '张强民', '计算机', 1, '1989-08-11', 50, NULL, NULL);
  75. INSERT INTO `xs` VALUES ('081110', '张蔚', '计算机', 0, '1991-07-22', 50, NULL, '三好生');
  76. INSERT INTO `xs` VALUES ('081111', '赵琳', '计算机', 0, '1990-03-18', 50, NULL, NULL);
  77. INSERT INTO `xs` VALUES ('081113', '严红', '计算机', 0, '1989-08-11', 48, NULL, '有一门功课不及格，待补考');
  78. INSERT INTO `xs` VALUES ('081201', '王敏', '通信工程', 1, '1989-06-10', 42, NULL, NULL);
  79. INSERT INTO `xs` VALUES ('081202', '王林', '通信工程', 1, '1989-01-29', 40, NULL, '有一门课不及格，待补考');
  80. INSERT INTO `xs` VALUES ('081203', '王玉民', '通信工程', 1, '1990-03-26', 42, NULL, NULL);
  81. INSERT INTO `xs` VALUES ('081204', '马琳琳', '通信工程', 0, '1989-02-10', 42, NULL, NULL);
  82. INSERT INTO `xs` VALUES ('081206', '李计', '通信工程', 1, '1989-09-20', 42, NULL, NULL);
  83. INSERT INTO `xs` VALUES ('081210', '李红庆', '通信工程', 1, '1989-05-01', 44, NULL, '已提前修完一门课，并获得学分');
  84. INSERT INTO `xs` VALUES ('081216', '孙祥欣', '通信工程', 1, '1989-03-09', 42, NULL, NULL);
  85. INSERT INTO `xs` VALUES ('081218', '孙研', '通信工程', 1, '1990-10-09', 42, NULL, NULL);
  86. INSERT INTO `xs` VALUES ('081220', '吴薇华', '通信工程', 0, '1990-03-18', 42, NULL, NULL);
  87. INSERT INTO `xs` VALUES ('081221', '刘燕敏', '通信工程', 0, '1989-11-12', 42, NULL, NULL);
  88. INSERT INTO `xs` VALUES ('081241', '罗林琳', '通信工程', 0, '1990-01-30', 50, NULL, '转专业学习');
  89.  
  90. -- ----------------------------
  91. -- Table structure for xs_kc
  92. -- ----------------------------
  93. DROP TABLE IF EXISTS `xs_kc`;
  94. CREATE TABLE `xs_kc`  (
  95.   `学号` char(6) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  96.   `课程号` char(3) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  97.   `成绩` tinyint(1) NULL DEFAULT NULL,
  98.   `学分` tinyint(1) NULL DEFAULT NULL,
  99.   PRIMARY KEY (`学号`, `课程号`) USING BTREE
  100. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
  101.  
  102. -- ----------------------------
  103. -- Records of xs_kc
  104. -- ----------------------------
  105. INSERT INTO `xs_kc` VALUES ('081101', '101', 80, 5);
  106. INSERT INTO `xs_kc` VALUES ('081101', '102', 78, 4);
  107. INSERT INTO `xs_kc` VALUES ('081101', '206', 76, 4);
  108. INSERT INTO `xs_kc` VALUES ('081102', '102', 78, 4);
  109. INSERT INTO `xs_kc` VALUES ('081102', '206', 78, 4);
  110. INSERT INTO `xs_kc` VALUES ('081103', '101', 62, 5);
  111. INSERT INTO `xs_kc` VALUES ('081103', '102', 70, 4);
  112. INSERT INTO `xs_kc` VALUES ('081103', '206', 81, 4);
  113. INSERT INTO `xs_kc` VALUES ('081104', '101', 90, 5);
  114. INSERT INTO `xs_kc` VALUES ('081104', '102', 84, 4);
  115. INSERT INTO `xs_kc` VALUES ('081104', '206', 65, 4);
  116. INSERT INTO `xs_kc` VALUES ('081106', '101', 65, 5);
  117. INSERT INTO `xs_kc` VALUES ('081106', '102', 71, 4);
  118. INSERT INTO `xs_kc` VALUES ('081106', '206', 81, 4);
  119. INSERT INTO `xs_kc` VALUES ('081107', '101', 78, 5);
  120. INSERT INTO `xs_kc` VALUES ('081107', '102', 80, 4);
  121. INSERT INTO `xs_kc` VALUES ('081107', '206', 68, 4);
  122. INSERT INTO `xs_kc` VALUES ('081108', '101', 85, 5);
  123. INSERT INTO `xs_kc` VALUES ('081108', '102', 64, 4);
  124. INSERT INTO `xs_kc` VALUES ('081108', '206', 87, 4);
  125. INSERT INTO `xs_kc` VALUES ('081109', '101', 66, 5);
  126. INSERT INTO `xs_kc` VALUES ('081109', '102', 83, 4);
  127. INSERT INTO `xs_kc` VALUES ('081109', '206', 70, 4);
  128. INSERT INTO `xs_kc` VALUES ('081110', '101', 95, 5);
  129. INSERT INTO `xs_kc` VALUES ('081110', '102', 90, 4);
  130. INSERT INTO `xs_kc` VALUES ('081110', '206', 89, 4);
  131. INSERT INTO `xs_kc` VALUES ('081111', '101', 91, 5);
  132. INSERT INTO `xs_kc` VALUES ('081111', '102', 70, 4);
  133. INSERT INTO `xs_kc` VALUES ('081111', '206', 76, 4);
  134. INSERT INTO `xs_kc` VALUES ('081113', '101', 63, 5);
  135. INSERT INTO `xs_kc` VALUES ('081113', '102', 79, 4);
  136. INSERT INTO `xs_kc` VALUES ('081113', '206', 60, 4);
  137. INSERT INTO `xs_kc` VALUES ('081201', '101', 80, 5);
  138. INSERT INTO `xs_kc` VALUES ('081202', '101', 65, 5);
  139. INSERT INTO `xs_kc` VALUES ('081203', '101', 87, 5);
  140. INSERT INTO `xs_kc` VALUES ('081204', '101', 91, 5);
  141. INSERT INTO `xs_kc` VALUES ('081210', '101', 76, 5);
  142. INSERT INTO `xs_kc` VALUES ('081216', '101', 81, 5);
  143. INSERT INTO `xs_kc` VALUES ('081218', '101', 70, 5);
  144. INSERT INTO `xs_kc` VALUES ('081220', '101', 82, 5);
  145. INSERT INTO `xs_kc` VALUES ('081221', '101', 76, 5);
  146. INSERT INTO `xs_kc` VALUES ('081241', '101', 90, 5);
  147.  
  148. SET FOREIGN_KEY_CHECKS = 1;

习题：

(1)查询学生的学号，姓名，出生日期

  1. use xscjgl;
  2. select 学号,姓名,出生时间 from xs;

(2)查询1990年以后出生的学生的学号，姓名，出生日期

select 学号,姓名,出生时间 from xs where 出生时间>="1990-1-1";

(3)按姓名升序显示学生的学号，姓名。

 select 学号,姓名 from xs order by 姓名;

 

(4)按专业名统计各专业的学生人数。

select 专业名,count(*) 各专业人数 from xs group by 专业名;

(5)查询姓“王”的学生的学号，姓名信息

select 学号,姓名 from xs where 姓名 like "王%";

 (6)查询学生的学号，姓名，课程名，成绩信息。

SELECT b.`学号`,b.`姓名`,c.`课程名`,a.`成绩` FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.`课程号`=c.`课程号`;

(7)统计学生的成绩总分并按总分由高到低排名。

SELECT b.*,sum(成绩) 总分 FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.`课程号`=c.`课程号` group by a.学号 order by 总分 desc;

(8)查询学生成绩总分高于200分的学生信息。

SELECT b.*,sum(成绩) 总分 FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.`课程号`=c.`课程号` group by a.学号  having 总分>200;

(9)统计没有参加考试的学生名单。

select * from xs where 学号 not in(SELECT b.学号 FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.`课程号`=c.`课程号` group by a.学号);

(10)显示总分前三名的学生信息。

SELECT b.*,sum(成绩) 总分 FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.`课程号`=c.`课程号` group by a.学号 order by 总分 desc limit 3;

(11)查询参加计算机基础课程考试的学生成绩高于课程平均分的学生信息。

  1. SELECT b.* ,a.`成绩`FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.`课程号`=c.`课程号` AND c.课程名='计算机基础' AND a.成绩>=(
  2. SELECT AVG(a.成绩) FROM xs_kc a,kc b WHERE a.课程号=b.课程号  AND b.课程名='计算机基础');

(12)查询程序设计与语言课程最高分的学生的学号和姓名

  1. SELECT b.学号,姓名,MAX(成绩) FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.课程号=c.课程号 AND c.课程名='程序设计与语言' AND a.`成绩`=(
  2. SELECT MAX(成绩) FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.课程号=c.课程号 AND c.课程名='程序设计与语言');

(13)查询计算机基础，程序设计与语言和离散数学三门课程中最高分学生的学号，

  1. SELECT b.学号,姓名,MAX(成绩) FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.课程号=c.课程号 AND c.课程名='计算机基础' AND a.`成绩`=(
  2. SELECT MAX(成绩) FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.课程号=c.课程号 AND c.课程名='计算机基础')
  3. UNION ALL
  4. SELECT b.学号,姓名,MAX(成绩) FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.课程号=c.课程号 AND c.课程名='程序设计与语言' AND a.`成绩`=(
  5. SELECT MAX(成绩) FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.课程号=c.课程号 AND c.课程名='程序设计与语言')
  6. UNION ALL
  7. SELECT b.学号,姓名,MAX(成绩) FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.课程号=c.课程号 AND c.课程名='离散数学' AND a.`成绩`=(
  8. SELECT MAX(成绩) FROM xs_kc a,xs b,kc c WHERE a.`学号`=b.`学号` AND a.课程号=c.课程号 AND c.课程名='离散数学');

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/花生_TL007/article/detail/701149
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号