Mysql 数据库查询语句基本应用（二）

该文章所用数据库结构如下

/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 50561
 Source Host           : localhost:3306
 Source Schema         : blog

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

 Date: 13/05/2024 11:58:10
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for xscj
-- ----------------------------
DROP TABLE IF EXISTS `xscj`;
CREATE TABLE `xscj`  (
  `学号` char(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `课程号` char(3) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `成绩` tinyint(1) NULL DEFAULT NULL,
  `学分` tinyint(1) NULL DEFAULT NULL,
  PRIMARY KEY (`学号`, `课程号`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of xscj
-- ----------------------------
INSERT INTO `xscj` VALUES ('02020101', '101', 85, 4);
INSERT INTO `xscj` VALUES ('02020101', '102', 70, 5);
INSERT INTO `xscj` VALUES ('02020102', '101', 90, 4);
INSERT INTO `xscj` VALUES ('02020102', '102', 80, 5);
INSERT INTO `xscj` VALUES ('02020201', '101', 86, 4);
INSERT INTO `xscj` VALUES ('02020201', '208', 80, 4);
INSERT INTO `xscj` VALUES ('02020202', '216', 60, 4);

-- ----------------------------
-- Table structure for xskc
-- ----------------------------
DROP TABLE IF EXISTS `xskc`;
CREATE TABLE `xskc`  (
  `课程号` char(3) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `课程名` char(18) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `开课学期` tinyint(1) NOT NULL,
  `学时` tinyint(1) NOT NULL,
  `学分` tinyint(1) NULL DEFAULT NULL,
  PRIMARY KEY (`课程号`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of xskc
-- ----------------------------
INSERT INTO `xskc` VALUES ('101', '计算机文化基础', 1, 86, 4);
INSERT INTO `xskc` VALUES ('102', 'Qbasic', 1, 68, 6);
INSERT INTO `xskc` VALUES ('206', 'VC', 2, 68, 4);
INSERT INTO `xskc` VALUES ('208', '数据结构', 2, 68, 4);
INSERT INTO `xskc` VALUES ('210', '操作系统', 3, 64, 4);
INSERT INTO `xskc` VALUES ('212', '计算机组成', 4, 86, 5);
INSERT INTO `xskc` VALUES ('216', '数据库原理', 2, 68, 4);
INSERT INTO `xskc` VALUES ('301', '计算机网络', 5, 56, 3);

-- ----------------------------
-- Table structure for xsqk
-- ----------------------------
DROP TABLE IF EXISTS `xsqk`;
CREATE TABLE `xsqk`  (
  `系别` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `班级` char(12) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `专业` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `学号` char(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `姓名` char(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `性别` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '男',
  `出生年月` date NULL DEFAULT NULL,
  `总学分` tinyint(1) NULL DEFAULT NULL,
  `备注` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  PRIMARY KEY (`学号`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of xsqk
-- ----------------------------
INSERT INTO `xsqk` VALUES ('计算机', '计算机0203', '计算机应用与维护', '02020101', '王玲玲', '女', '1981-08-26', 9, NULL);
INSERT INTO `xsqk` VALUES ('计算机', '计算机0203', '计算机应用与维护', '02020102', '张燕红', '女', '1981-10-20', 9, NULL);
INSERT INTO `xsqk` VALUES ('计算机', '计算机0203', '计算机应用与维护', '02020103', '杨勇', '男', '1982-03-15', NULL, NULL);
INSERT INTO `xsqk` VALUES ('计算机', '计算机0203', '计算机应用与维护', '02020104', '王红庆', '男', '1983-05-17', NULL, NULL);
INSERT INTO `xsqk` VALUES ('计算机', '计算机0203', '计算机应用与维护', '02020105', '陈园', '女', '1982-04-12', NULL, NULL);
INSERT INTO `xsqk` VALUES ('计算机', '信息管理0201', '信息管理', '02020201', '黄薇娜', '女', '1983-08-19', 8, NULL);
INSERT INTO `xsqk` VALUES ('计算机', '信息管理0201', '信息管理', '02020202', '沈昊', '男', '1982-03-18', 8, NULL);
INSERT INTO `xsqk` VALUES ('计算机', '信息管理0201', '信息管理', '02020203', '傅亮达', '男', '1983-01-22', NULL, NULL);
INSERT INTO `xsqk` VALUES ('计算机', '信息管理0201', '信息管理', '02020204', '任建刚', '男', '1981-12-21', NULL, NULL);
INSERT INTO `xsqk` VALUES ('计算机', '信息管理0201', '信息管理', '02020205', '叶小红', '女', '1983-07-16', NULL, NULL);

SET FOREIGN_KEY_CHECKS = 1;

一、目的

通过对SELECT的使用，掌握SELECT语句的结构及其应用，掌握连接查询。

二、原理解析

语法格式为：
SELECT〈目标列组〉
FROM〈数据源〉
[WHERE〈元组选择条件〉]
[GROUP BY〈分列组〉[HAVING 〈组选择条件〉]]
ORDER BY〈排序列1〉〈排序要求1〉 [，…n] SELECT子句
SELECT子句用于指明查询结果集的目标列。
(2) FROM子句
FROM子句用于指明查询的数据源。
(3) WHERE子句
WHERE子句通过条件表达式描述关系中元组的选择条件。
(4) GROUP BY子句
GROUP BY子句的作用是按分组列的值对结果集分组。
(5) ORDER BY子句ORDER BY子句的作用是对结果集进行排序。

三、内容

1、查询所有学生的姓名和课程名称。
2、查询平均成绩在60分以上的学生姓名。
3、查询每个学生的姓名、所选修的课程名及成绩。
4、查询选修了课程的学生姓名。
5、查询选修课程数在两门以上的学生姓名。（用连接查询和嵌套查询两种方法实现）
6、查询成绩及格的学生人数。
7、查询成绩及格的学生人次。
8、查询所有学生都没有选修的课程名称。
9、查询每个学生的平均成绩，并按平均成绩的降序排序。
10、查询每个同学的最高分，要求显示学号、姓名和分数等信息。
11、查询选修同一门课程的学生学号。
12、查询所有课程成绩都及格的学生姓名。
13、查询没有选修“操作系统”课程的学生姓名。
14、查询没有选修任何课程的学生姓名。
15、查询没有成绩的学生姓名。
16、查询同时选修了“101”和“208”课程的学生姓名。
17、求至少选修“101”和“208”其中一门课程的学生姓名。
18、查询成绩在所有同学成绩平均分之上的学生姓名。
19、查询有学生选修的课程名称。
20、查询同时选修了“101”和“208”课程的学生姓名。（用自身连接查询实现）
21、查询选修了课程名为“计算机文化基础”或“数据结构”的学生学号和姓名。（用联合查询实现）
22、查询每个同学的最高分。（姓名、成绩）
23、查询平均成绩及格的学生姓名。
24、查询每个同学成绩及格的课程门数。（姓名、及格课程数）

四、代码

1、查询所有学生的姓名和课程名称。

SELECT xsqk.姓名, xskc.课程名
FROM xscj
JOIN xsqk ON xscj.学号 = xsqk.学号
JOIN xskc ON xscj.课程号 = xskc.课程号;

2、查询平均成绩在60分以上的学生姓名。

SELECT xsqk.姓名
FROM xsqk
JOIN (
    SELECT 学号, AVG(成绩) as 平均成绩
    FROM xscj
    GROUP BY 学号
) AS avg_scores ON xsqk.学号 = avg_scores.学号
WHERE avg_scores.平均成绩 >= 60;

3、查询每个学生的姓名、所选修的课程名及成绩。

SELECT xsqk.姓名, xskc.课程名, xscj.成绩
FROM xscj
JOIN xsqk ON xscj.学号 = xsqk.学号
JOIN xskc ON xscj.课程号 = xskc.课程号;

4、查询选修了课程的学生姓名。

SELECT DISTINCT xsqk.姓名
FROM xsqk
JOIN xscj ON xsqk.学号 = xscj.学号;

5、查询选修课程数在两门以上的学生姓名。（用连接查询和嵌套查询两种方法实现）

-- 连接查询
SELECT xsqk.姓名
FROM xsqk
JOIN (
    SELECT 学号
    FROM xscj
    GROUP BY 学号
    HAVING COUNT(课程号) > 2
) AS course_counts ON xsqk.学号 = course_counts.学号;

-- 嵌套查询
SELECT 姓名
FROM xsqk
WHERE 学号 IN (
    SELECT 学号
    FROM xscj
    GROUP BY 学号
    HAVING COUNT(课程号) > 2
);

6、查询成绩及格的学生人数。

SELECT COUNT(DISTINCT 学号)
FROM xscj
WHERE 成绩 >= 60;

7、查询成绩及格的学生人次。

SELECT COUNT(*)
FROM xscj
WHERE 成绩 >= 60;

8、查询所有学生都没有选修的课程名称。

SELECT 课程名
FROM xskc
WHERE 课程号 NOT IN (
    SELECT DISTINCT 课程号
    FROM xscj
);

9、查询每个学生的平均成绩，并按平均成绩的降序排序。

SELECT 学号, AVG(成绩) as 平均成绩
FROM xscj
GROUP BY 学号
ORDER BY 平均成绩 DESC;

10、查询每个同学的最高分，要求显示学号、姓名和分数等信息。

SELECT xsqk.学号, xsqk.姓名, MAX(xscj.成绩) as 最高分
FROM xsqk
JOIN xscj ON xsqk.学号 = xscj.学号
GROUP BY xsqk.学号, xsqk.姓名;

11、查询选修同一门课程的学生学号。

SELECT 课程号, GROUP_CONCAT(学号) as 学生学号
FROM xscj
GROUP BY 课程号;

12、查询所有课程成绩都及格的学生姓名。

SELECT xsqk.姓名
FROM xsqk
WHERE xsqk.学号 IN (SELECT 学号 FROM xscj) -- 确定有选课记录的学生
AND NOT EXISTS (
    -- 然后对于这些学生，检查是否有不及格的成绩记录
    SELECT 1
    FROM xscj
    WHERE xsqk.学号 = xscj.学号 AND xscj.成绩 < 60
);

13、查询没有选修“操作系统”课程的学生姓名。

SELECT 姓名
FROM xsqk
WHERE 学号 NOT IN (
  SELECT 学号
  FROM xscj
  JOIN xskc ON xscj.课程号 = xskc.课程号
  WHERE 课程名 = '操作系统'
);

14、查询没有选修任何课程的学生姓名。

SELECT 姓名
FROM xsqk
WHERE 学号 NOT IN (
  SELECT DISTINCT 学号
  FROM xscj
);

15、查询没有成绩的学生姓名。

SELECT 姓名
FROM xsqk
WHERE 学号 NOT IN (
  SELECT DISTINCT 学号
  FROM xscj
  WHERE 成绩 IS NOT NULL
);

16、查询同时选修了“101”和“208”课程的学生姓名。

SELECT DISTINCT a.姓名
FROM xsqk a
JOIN xscj b ON a.学号 = b.学号 AND b.课程号 = '101'
JOIN xscj c ON a.学号 = c.学号 AND c.课程号 = '208';

17、求至少选修“101”和“208”其中一门课程的学生姓名。

SELECT 姓名
FROM xsqk
WHERE 学号 IN (
  SELECT 学号
  FROM xscj
  WHERE 课程号 IN ('101', '208')
);

18、查询成绩在所有同学成绩平均分之上的学生姓名。

SELECT xsqk.姓名
FROM xscj
JOIN xsqk ON xscj.学号 = xsqk.学号
GROUP BY xscj.学号, xsqk.姓名
HAVING AVG(xscj.成绩) > (
  SELECT AVG(成绩)
  FROM xscj
);

19、查询有学生选修的课程名称。

SELECT DISTINCT 课程名
FROM xskc
WHERE 课程号 IN (
  SELECT DISTINCT 课程号
  FROM xscj
);

20、查询同时选修了“101”和“208”课程的学生姓名。（用自身连接查询实现）

SELECT DISTINCT a.姓名
FROM xsqk a, xscj b, xscj c
WHERE a.学号 = b.学号
AND a.学号 = c.学号
AND b.课程号 = '101'
AND c.课程号 = '208';

21、查询选修了课程名为“计算机文化基础”或“数据结构”的学生学号和姓名。（用联合查询实现）

SELECT 学号, 姓名
FROM xsqk
WHERE 学号 IN (
  SELECT 学号 FROM xscj JOIN xskc ON xscj.课程号 = xskc.课程号 WHERE 课程名 = '计算机文化基础'
  UNION
  SELECT 学号 FROM xscj JOIN xskc ON xscj.课程号 = xskc.课程号 WHERE 课程名 = '数据结构'
);

22、查询每个同学的最高分。（姓名、成绩）

SELECT xsqk.姓名, MAX(xscj.成绩) as 最高分
FROM xscj
JOIN xsqk ON xscj.学号 = xsqk.学号
GROUP BY xsqk.学号, xsqk.姓名;

23、查询平均成绩及格的学生姓名。

SELECT xsqk.姓名
FROM xscj
JOIN xsqk ON xscj.学号 = xsqk.学号
GROUP BY xscj.学号, xsqk.姓名
HAVING AVG(xscj.成绩) >= 60;

24、查询每个同学成绩及格的课程门数。（姓名、及格课程数）

SELECT xsqk.姓名, COUNT(*) as 及格课程数
FROM xscj
JOIN xsqk ON xscj.学号 = xsqk.学号
WHERE xscj.成绩 >= 60
GROUP BY xscj.学号, xsqk.姓名;