22. 查询练习之xscj

一、准备数据

/*
Navicat MySQL Data Transfer

Source Server         : lzl
Source Server Version : 50540
Source Host           : localhost:3306
Source Database       : xscj

Target Server Type    : MYSQL
Target Server Version : 50540
File Encoding         : 65001

Date: 2023-04-20 11:09:08
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for kc
-- ----------------------------
DROP TABLE IF EXISTS `kc`;
CREATE TABLE `kc` (
  `courseno` char(3) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `semester` int(11) DEFAULT NULL,
  `hour` int(11) DEFAULT NULL,
  `credit` int(11) DEFAULT NULL,
  PRIMARY KEY (`courseno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of kc
-- ----------------------------
INSERT INTO `kc` VALUES ('101', '计算机基础', '1', '80', '5');
INSERT INTO `kc` VALUES ('102', '程序设计与语言', '2', '68', '4');
INSERT INTO `kc` VALUES ('206', '离散数学', '4', '68', '4');
INSERT INTO `kc` VALUES ('208', '数据结构', '5', '68', '4');
INSERT INTO `kc` VALUES ('209', '操作系统', '6', '68', '4');
INSERT INTO `kc` VALUES ('210', '计算机原理', '5', '85', '5');
INSERT INTO `kc` VALUES ('212', '数据库原理', '7', '68', '4');
INSERT INTO `kc` VALUES ('301', '计算机网络', '7', '51', '3');
INSERT INTO `kc` VALUES ('302', '软件工程', '7', '51', '3');

-- ----------------------------
-- Table structure for xs
-- ----------------------------
DROP TABLE IF EXISTS `xs`;
CREATE TABLE `xs` (
  `stuno` char(6) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `major` varchar(255) DEFAULT NULL,
  `sex` int(11) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `credit` int(11) DEFAULT NULL,
  `picture` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`stuno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of xs
-- ----------------------------
INSERT INTO `xs` VALUES ('081101', '王林', '计算机', '1', '1990-02-10', '50', null, null);
INSERT INTO `xs` VALUES ('081102', '程明', '计算机', '1', '1991-02-01', '50', null, null);
INSERT INTO `xs` VALUES ('081103', '王燕', '计算机', '0', '1989-10-06', '50', null, null);
INSERT INTO `xs` VALUES ('081104', '韦严平', '计算机', '1', '1990-08-26', '50', null, null);
INSERT INTO `xs` VALUES ('081106', '李方方', '计算机', '1', '1990-11-20', '50', null, '');
INSERT INTO `xs` VALUES ('081107', '李明', '计算机', '1', '1990-05-01', '54', null, '提前修完《数据结构》');
INSERT INTO `xs` VALUES ('081108', '李一帆', '计算机', '1', '1989-08-05', '52', null, '已提前修完一门课');
INSERT INTO `xs` VALUES ('081109', '张强民', '计算机', '1', '1989-08-11', '50', null, null);
INSERT INTO `xs` VALUES ('081110', '张蔚', '计算机', '0', '1991-07-22', '50', null, '三好生');
INSERT INTO `xs` VALUES ('081111', '赵琳', '计算机', '0', '1990-03-18', '50', null, null);
INSERT INTO `xs` VALUES ('081113', '严红', '计算机', '0', '1989-06-10', '48', null, '有一门功课不及格，待补考');
INSERT INTO `xs` VALUES ('081201', '王敏', '通信工程', '1', '1989-06-10', '42', null, null);
INSERT INTO `xs` VALUES ('081202', '王林', '通信工程', '1', '1989-01-29', '40', null, '有一门功课不及格，待补考');

-- ----------------------------
-- Table structure for xs_kc
-- ----------------------------
DROP TABLE IF EXISTS `xs_kc`;
CREATE TABLE `xs_kc` (
  `stuno` char(6) NOT NULL,
  `course` char(3) NOT NULL,
  `score` int(11) DEFAULT NULL,
  `credit` int(11) DEFAULT NULL,
  PRIMARY KEY (`stuno`,`course`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of xs_kc
-- ----------------------------
INSERT INTO `xs_kc` VALUES ('081101', '101', '80', '5');
INSERT INTO `xs_kc` VALUES ('081101', '102', '78', '4');
INSERT INTO `xs_kc` VALUES ('081101', '206', '76', '4');
INSERT INTO `xs_kc` VALUES ('081102', '102', '78', '4');
INSERT INTO `xs_kc` VALUES ('081102', '206', '78', '4');
INSERT INTO `xs_kc` VALUES ('081103', '101', '62', '5');
INSERT INTO `xs_kc` VALUES ('081103', '102', '70', '4');
INSERT INTO `xs_kc` VALUES ('081103', '206', '81', '4');

数据如下图

课程表.png 学生_课程表.png 学生表.png

---

二、习题

1. 查询xs表中各个同学的姓名、专业名和总学分(列控制)
2. 查询xs表中计算机系同学的学号、姓名和总学分，结果中各列的标题分别为number、name、和mark（别名）
3. 查询xs表中计算机系各同学的学号、姓名和总学分，对其总学分按以下规则进行替换:若总学分为空值，替换为“尚未选课”":若总学分小于50,替换为“不及格":若总学分在50-52之间，替换为“合格”;若总学分大于52,替换为“优秀”。 列标题更改为“等级”（结果替换）
4. 按120分计算成绩，显示xs_kc表中学号为081101的学生课程信息（列运算）
5. 对xs表只选择专业名和总学分，消除结果集中的重复行（distinct）
6. 求学生的总人数（count）
7. 统计备注不为空的学生数目(is not null <>)
8. 统计总学分在50分以上的人数 (条件查询)
9. 求选修101课程的学生的最高分和最低分（max、min）
10. 求学号081101的学生所学课程的总成绩(sum)
11. 求选修101课程的学生的平均成绩(avg)
12. 查询XS表中学号为081101的学生的情况 (条件查询)
13. 查询XS表中总学分大于50的学生的情况 (条件查询)
14. 查询XS表中备注为空的同学的情况(is null <=>)
15. 查询XS表中专业为计算机，性别为女(0)的同学的情况 (条件查询 && and)
16. 查询XS_ KC表中102和206课程中大于80分的同学的记录 (条件查询 or || )
17. 查询XS表中姓“王”的学生学号、姓名及性别(模糊查询)
18. 查询XS表中学号倒数第2个数字为0的学生学号、姓名及专业名(模糊查询)
19. 查询XS表中名字包含下划线的学生学号和姓名(模糊查询，特殊字符处理)
20. 查询XS表中不在1989年出生的学生情况（比较日期）
21. 查询XS表中专业名为“计算机”“通信工程”的学生的情况（or ||）
22. 查询XS中总学分尚不定的学生情况(模糊查询)
23. 查找所有学生选过的课程名和课程号（多表查询）
24. 查找选修了206课程且成绩在80分以上的学生姓名及成绩（多表查询）
25. 查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩（多表查询）
26. 查找课程不同、成绩相同的学生的学号、课程号和成绩（子查询）
27. 查找KC表中所有学生选过的课程名
28. 查找所有学生情况及他们选修的课程号，若学学生未选修任何课，也要包括其情况（外连接）
29. 查找被选修了的课程的选修情况和所有开设的课程名（外连接）
30. 查找选修了课程号为206课程的学生的姓名、学号（多表查询）
31. 查找未选修离散数学的学生的姓名、学号和专业名（多表查询）
32. 查找选修了离散数学的学生学号（多表查询）
33. 查找xs表中比所有计算机系的学生年龄都大的学生的学号、姓名、专业名和出生日期（子查询ALL ， limit）
34. 查找xs_kc表中课程号206的成绩不低于课程号101的最低成绩的学生的学号(子查询 ANY， limit)
35. 查找选修206号课程的学生姓名(多表查询)
36. 将XS中各专业名输出（分组或去重）
37. 求XS中各专业的学生数（分组，count）
38. 求被选修的各门课程的平均成绩和选修该课程的人数(分组、count、sum)
39. 在XSCJ数据库上产生一个结果集，包括每个专业的男生人数、女生人数、总人数，以及学生总人数（with roolup P98）
40. 查找平均成绩在85分以上的学生的学号和平均成绩(多表查询)
41. 查找选修课程超过2门且成绩都在80分以上的学生的学号（分组后加条件）
42. 将通信工程专业的学生按出生日期先后排序（排序）
43. 将计算机专业学生的“计算机基础”课程成绩按降序排列（排序）
44. 将计算机专业学生按其平均成绩排列（排序）
45. 查找XS表中学号最靠前的5位学生的信息（limit）
46. 查找XS表中从第4位同学开始的5位学生的信息(limit)