Mysql 基础练习大全(建表到基本查询)

àì夳堔傛蜴生んèń 2024-04-01 14:45 17阅读 0赞

## 开篇: ##

## 最近打算把sql 练习下,故找了个"SQL题大全",每个sql 都有执行, 希望看到这篇文章的你也能练习一下, 相信每一分学习都会有收获,让学习使我们感到充实. ##

文中习题参考自: [https://blog.csdn.net/cpc784221489/article/details/105102005][https_blog.csdn.net_cpc784221489_article_details_105102005]

在原有基础上对表做了稍微改动,所有SQL都重新执行了一遍,部分函数要参照具体版本,下面是数据库表DDL:

/*
     Navicat Premium Data Transfer
    
     Source Server         : local
     Source Server Type    : MySQL
     Source Server Version : 50724
     Source Host           : localhost:3306
     Source Schema         : ken
    
     Target Server Type    : MySQL
     Target Server Version : 50724
     File Encoding         : 65001
    
     Date: 01/11/2022 11:16:08
    */
    
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for course
    -- ----------------------------
    DROP TABLE IF EXISTS `course`;
    CREATE TABLE `course`  (
      `id` int(11) NOT NULL DEFAULT 0 AUTO_INCREMENT,
      `cid` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      `cname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '课程名称',
      `tid` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '教师表编号',
      `create_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
      `create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
      `update_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新人',
      `update_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '更新时间',
      `is_delete` tinyint(1) NULL DEFAULT 0 COMMENT '是否有效: 0: 无效; 1有效',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '课程表' ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of course
    -- ----------------------------
    INSERT INTO `course` VALUES (1, '01', '语文', '02', 'admin', '2022-11-01 11:02:38', NULL, '2022-11-01 11:02:38', 0);
    INSERT INTO `course` VALUES (2, '02', '数学', '01', 'admin', '2022-11-01 11:02:56', NULL, '2022-11-01 11:02:56', 0);
    INSERT INTO `course` VALUES (3, '03', '英语', '03', 'admin', '2022-11-01 11:03:11', NULL, '2022-11-01 11:03:11', 0);
    
    -- ----------------------------
    -- Table structure for student
    -- ----------------------------
    DROP TABLE IF EXISTS `student`;
    CREATE TABLE `student`  (
      `id` int(11) NOT NULL DEFAULT 0 AUTO_INCREMENT COMMENT '主键ID',
      `sid` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '学号',
      `sname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '学生姓名',
      `sex` tinyint(1) UNSIGNED NULL DEFAULT 0 COMMENT '0: 未知; 1: 男; 2:女',
      `birthday` date NULL DEFAULT NULL COMMENT '生日',
      `image_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '头像',
      `create_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
      `create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
      `update_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新人',
      `update_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '更新时间',
      `is_delete` tinyint(1) NULL DEFAULT 0 COMMENT '是否有效: 0: 无效; 1有效',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of student
    -- ----------------------------
    INSERT INTO `student` VALUES (1, '00', '果酱', 1, '2012-11-01', 'http:/oss.anyitech.ltd/ws_oss_service/storage/file_download/7318f730c05211ec92cf577b05c735c0.jpg', 'admin', '2022-11-01 10:31:19', 'admin', '2022-11-01 10:31:19', 0);
    INSERT INTO `student` VALUES (2, '01', '赵倩倩', 1, '1990-01-01', NULL, 'admin', '2022-11-01 10:36:41', NULL, '2022-11-01 10:36:41', 0);
    INSERT INTO `student` VALUES (3, '02', '钱滴滴', 1, '1990-12-21', NULL, 'admin', '2022-11-01 10:36:41', NULL, '2022-11-01 10:36:41', 0);
    INSERT INTO `student` VALUES (4, '03', '孙芊芊', 1, '1990-05-20', NULL, 'admin', '2022-11-01 10:36:41', NULL, '2022-11-01 10:36:41', 0);
    INSERT INTO `student` VALUES (5, '04', '李芸芸', 1, '1990-08-06', NULL, 'admin', '2022-11-01 10:36:41', NULL, '2022-11-01 10:36:41', 0);
    INSERT INTO `student` VALUES (6, '05', '周五', 2, '1991-12-01', NULL, 'admin', '2022-11-01 10:36:41', NULL, '2022-11-01 10:36:41', 0);
    INSERT INTO `student` VALUES (7, '06', '吴优', 2, '1992-03-01', NULL, 'admin', '2022-11-01 10:36:41', NULL, '2022-11-01 10:36:41', 0);
    INSERT INTO `student` VALUES (8, '07', '郑板桥', 2, '1989-07-01', NULL, 'admin', '2022-11-01 10:36:41', NULL, '2022-11-01 10:36:41', 0);
    INSERT INTO `student` VALUES (9, '08', '王天一', 2, '1990-01-20', NULL, 'admin', '2022-11-01 10:36:41', NULL, '2022-11-01 10:36:41', 0);
    
    -- ----------------------------
    -- Table structure for student_course
    -- ----------------------------
    DROP TABLE IF EXISTS `student_course`;
    CREATE TABLE `student_course`  (
      `id` int(11) NOT NULL DEFAULT 0 AUTO_INCREMENT COMMENT '主键id',
      `sid` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '学生编号',
      `cid` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '课程编号',
      `score` int(255) NULL DEFAULT NULL COMMENT '得分',
      `create_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
      `create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
      `update_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新人',
      `update_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '更新时间',
      `is_delete` tinyint(1) NULL DEFAULT 0 COMMENT '是否有效: 0: 无效; 1有效',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 19 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '课程表' ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of student_course
    -- ----------------------------
    INSERT INTO `student_course` VALUES (1, '01', '01', 80, 'admin', '2022-11-01 11:10:56', NULL, '2022-11-01 11:10:56', 0);
    INSERT INTO `student_course` VALUES (2, '01', '02', 90, 'admin', '2022-11-01 11:11:06', NULL, '2022-11-01 11:11:06', 0);
    INSERT INTO `student_course` VALUES (3, '01', '03', 99, 'admin', '2022-11-01 11:11:17', NULL, '2022-11-01 11:11:17', 0);
    INSERT INTO `student_course` VALUES (4, '02', '01', 70, 'admin', '2022-11-01 11:11:29', NULL, '2022-11-01 11:11:29', 0);
    INSERT INTO `student_course` VALUES (5, '02', '02', 60, 'admin', '2022-11-01 11:11:43', NULL, '2022-11-01 11:11:43', 0);
    INSERT INTO `student_course` VALUES (6, '02', '03', 80, 'admin', '2022-11-01 11:11:51', NULL, '2022-11-01 11:11:51', 0);
    INSERT INTO `student_course` VALUES (7, '03', '01', 80, 'admin', '2022-11-01 11:12:06', NULL, '2022-11-01 11:12:06', 0);
    INSERT INTO `student_course` VALUES (8, '03', '02', 80, 'admin', '2022-11-01 11:12:18', NULL, '2022-11-01 11:12:18', 0);
    INSERT INTO `student_course` VALUES (9, '03', '03', 80, 'admin', '2022-11-01 11:12:24', NULL, '2022-11-01 11:12:24', 0);
    INSERT INTO `student_course` VALUES (10, '04', '01', 50, 'admin', '2022-11-01 11:12:33', NULL, '2022-11-01 11:12:33', 0);
    INSERT INTO `student_course` VALUES (11, '04', '02', 30, 'admin', '2022-11-01 11:12:41', NULL, '2022-11-01 11:12:41', 0);
    INSERT INTO `student_course` VALUES (12, '04', '03', 20, 'admin', '2022-11-01 11:12:49', NULL, '2022-11-01 11:12:49', 0);
    INSERT INTO `student_course` VALUES (13, '05', '01', 76, 'admin', '2022-11-01 11:12:58', NULL, '2022-11-01 11:12:58', 0);
    INSERT INTO `student_course` VALUES (14, '05', '02', 87, 'admin', '2022-11-01 11:13:05', NULL, '2022-11-01 11:13:05', 0);
    INSERT INTO `student_course` VALUES (15, '06', '01', 31, 'admin', '2022-11-01 11:13:20', NULL, '2022-11-01 11:13:20', 0);
    INSERT INTO `student_course` VALUES (16, '06', '03', 34, 'admin', '2022-11-01 11:13:29', NULL, '2022-11-01 11:13:29', 0);
    INSERT INTO `student_course` VALUES (17, '07', '02', 89, 'admin', '2022-11-01 11:13:39', NULL, '2022-11-01 11:13:39', 0);
    INSERT INTO `student_course` VALUES (18, '07', '03', 98, 'admin', '2022-11-01 11:13:47', NULL, '2022-11-01 11:13:47', 0);
    
    -- ----------------------------
    -- Table structure for teacher
    -- ----------------------------
    DROP TABLE IF EXISTS `teacher`;
    CREATE TABLE `teacher`  (
      `id` int(11) NOT NULL DEFAULT 0 AUTO_INCREMENT,
      `tid` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '教师表 id',
      `tname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '课程名称',
      `create_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
      `create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
      `update_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新人',
      `update_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '更新时间',
      `is_delete` tinyint(1) NULL DEFAULT 0 COMMENT '是否有效: 0: 无效; 1有效',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '课程表' ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of teacher
    -- ----------------------------
    INSERT INTO `teacher` VALUES (1, '01', '张三', 'admin', '2022-11-01 11:05:28', NULL, '2022-11-01 11:05:28', 0);
    INSERT INTO `teacher` VALUES (2, '02', '李四', 'admin', '2022-11-01 11:08:57', NULL, '2022-11-01 11:08:57', 0);
    INSERT INTO `teacher` VALUES (3, '03', '王五', 'admin', '2022-11-01 11:09:06', NULL, '2022-11-01 11:09:06', 0);
    
    SET FOREIGN_KEY_CHECKS = 1;
    
    # Mysql 查询训练
    USE ken;
    select * from ken.student;
    select * from ken.teacher;
    select * from ken.course;
    select * from ken.student_course;

执行完,检查下数据是否都正确插入:![c8c750422e2c4253bfb3320bd24e6d58.png][]

![4e15261526804ad3ae76884c4fef3123.png][]

![169d2c37297842f9a187903b3ee07155.png][]

![91f82092445b479f8286e1b4bc31a1d6.png][]

#### 1. 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数 ####

# 1. 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
    SELECT
    	s.sid '学生学号', s.sname '学生姓名',
    	CASE s.sex
    	WHEN 1 THEN
    		'男'
    	WHEN 2 THEN
    		'女'
    	ELSE
    		'未知'
    	END '性别',
    a.score AS '语文分数', b.score AS '数学分数'
    FROM
    	student s
    INNER JOIN student_course AS a ON s.sid = a.sid
    AND a.cid = '01'
    INNER JOIN student_course AS b ON s.sid = b.sid
    AND b.cid = '02'
    LEFT JOIN course c on a.cid = c.cid
    WHERE a.score > b.score;
    # Tip1:表的自联结 通常如果一张表中不同列之间的信息要对比的时候需要进行自联结,如本题中的"01"课程分数与"02"分数对比.

![30c33ed730214449b7dc2e5388ce727a.png][]

#### 1.1 查询同时学习了" 01 “课程和” 02 "课程的学生 ####

# 1.1 查询同时学习了" 01 “课程和” 02 "课程的学生
    SELECT
    	s.sname '学生姓名'
    FROM
    	student_course a
    inner JOIN student_course b ON a.sid = b.sid
    AND a.cid = '01'
    AND b.cid = '02'
    left join ken.student s on a.sid = s.sid;
    
    # Tip1 ：表的自联结 通常如果一张表中不同列之间的信息要对比的时候需要进行自联结，如本题中的“01”课程与“02”分数对比。
    # Tip2 ：INNER JOIN 如果要求两列信息同时存在的情况，需要inner join，它可以保证某列信息为null，则该行为空。

![6e1e5756416849cc9ac96140a7a6a4e0.png][]

#### 1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为null) ####

# 1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为null)
    SELECT
    	*
    FROM
    	student_course a
    LEFT JOIN student_course b ON a.Cid = '01'
    AND a.Sid = b.Sid
    AND b.Cid = '02';
    
    # 1.3 查询不存在" 01 “课程但存在” 02 "课程的情况
    SELECT
    	*
    FROM
    	student_course a
    RIGHT JOIN student_course b ON a.Cid = '01'
    AND a.Sid = b.Sid
    AND b.Cid = '02';

![b4f84ff15a754448a28ada2af278955a.png][]

####  2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩 ####

# 2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
    select sc.sid,s.sname, avg(sc.score)  avgScore from student_course sc left join student s on sc.sid = s.sid group by sc.sid having avg(sc.score) >= 60;
    select sc.sid,s.sname, avg(sc.score)  avgScore from student_course sc, student s where sc.sid = s.sid group by sc.sid having avg(sc.score) >= 60;
    # Tip1 ：group by
    # Tip2 ：执行顺序，from，where，group by having select order by limit
    # Tip3 ：select 中的别名 having中可以使用。

![e080fb61eea34165b1777fc0f7fd19cf.png][]

####  3. 查询在 SC 表存在成绩的学生信息 ####

# 3. 查询在 SC 表存在成绩的学生信息
    select * from student where sid in(select sc.sid from student_course sc where score is not null);
    SELECT DISTINCT
    	s.*
    FROM
    	student s
    WHERE
    	s.Sid IN (SELECT sc.Sid FROM student_course sc);
    # Tip1 子查询，根据一个表中的信息来查找另一个表中的信息，这里记得查找的id的关键词要用in来包含。而非 =。
    # Tip2 distinct 的使用

![b9bba6e7f95445419a3e76901e932210.png][]

#### 4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null) ####

# 4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null)
    select  s.sid, s.sname, count(sc.sid), sum(score) from student s left join student_course sc on  s.sid = sc.sid group by s.sid;
    -- 注意: 上面要用count(sc.sid), 要是用count(1)就错了
    SELECT
    	s.Sid,
    	s.Sname,
    	count(sc.Cid) AS course_count,
    	sum(sc.score) AS score_sum
    FROM
    	student s
    LEFT JOIN student_course sc ON s.Sid = sc.Sid
    GROUP BY
    	s.Sid;
    # Tip1 left join 与 on 、where之间的关系，如何才能返回null

![a0733d144a2544bf9aaf7e2b41b62968.png][]

####  6. 查询学过「张三」老师授课的同学的信息 ####

# 6. 查询学过「张三」老师授课的同学的信息
    select s.* from student s, student_course sc, course c, teacher t where s.sid = sc. sid and sc.cid = c.cid and c.tid = t.tid and t.tname ='张三';
    SELECT
    	s.*
    FROM
    	student s
    WHERE
    	s.Sid IN (
    		SELECT
    			sc.Sid
    		FROM
    			student_course sc inner join
    			course AS c
    ON
    			sc.Cid = c.Cid
    inner join
    			teacher AS t
    
    		ON c.Tid = t.Tid
    		WHERE t.Tname = '张三'
    	);

![be9e9f5217db4837b0fe2ab847f9fc01.png][]

####  7. 查询没有学全所有课程的同学的信息 ####

# ☆☆☆ 7. 查询没有学全所有课程的同学的信息
    SELECT
    	s.*
    FROM
    	student s
    WHERE
    	s.Sid NOT IN (
    		SELECT
    			s.Sid
    		FROM
    			student s,
    			student_course sc
    		WHERE
    			s.Sid = sc.Sid
    		GROUP BY
    			s.Sid
    		HAVING
    			count(1) = (SELECT count(1) FROM course)
    	);
    # 注意不能按下面这样写,会把没学的学生给漏掉,注意取反逻辑:
    # select s.* from student_course sc, student s where sc.sid =s.sid group by sid having count(sc.cid)< (select count(1) from course);
    # 可以换成左连接:
    select s.* from student s left join student_course sc on sc.sid =s.sid group by sid having count(sc.cid)< (select count(1) from course);

![8d404d8c87524f47be602cc9fdc6cc92.png][]

####  8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息 ####

# ☆☆8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
    SELECT s.* FROM student s WHERE s.Sid IN(
        select sc.sid from student_course sc where sc.cid in(select cid from student_course sc1 where sc1.sid = '01')
    );

![1b1366b765c64f009580cf0f26a80baa.png][]

#### 9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息 ####

# ☆☆☆☆☆9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
    explain SELECT s.* FROM student s WHERE s.Sid IN(
        select a.sid from (select sid ,group_concat(cid) courses from student_course sc group by sc.sid) a where a.courses =(
        select group_concat(cid) from student_course sc group by sc.sid having sc.sid ='01')
    );

![3aa32c25f4684fe39944e34e2a73f1e7.png][]

#### 10. 查询没学过"张三"老师讲授的任一门课程的学生 ####

# ☆☆☆☆10. 查询没学过"张三"老师讲授的任一门课程的学生
     SELECT s.sid, s.sname FROM student s WHERE s.Sid not IN(
         select sc.sid from student_course sc where sc.cid IN (
            select cid  from course c left join teacher t on c.tid = t.tid where t.tname ='张三')
    );
    
    SELECT
    	s.Sname
    FROM
    	student s
    WHERE
    	s.Sid NOT IN (
    		SELECT
    			sc.Sid
    		FROM
    			student_course sc,
    			course c,
    			teacher t
    		WHERE
    			sc.Cid = c.Cid
    		AND c.Tid = t.Tid
    		AND t.Tname = '张三'
    	);

![40c3219a3f814c2aa8f6d298d44c8fca.png][]

####  11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 ####

# ☆☆☆11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
     SELECT s.sid, s.sname FROM student s WHERE s.Sid IN (
        select sid from student_course sc where sc.score < 60 group by sc.sid having count(1)>= 2
    );
    SELECT
    	s.Sid,
    	s.Sname,
    	avg(sc.score) AS 平均成绩
    FROM
    	student s,
    	student_course sc
    WHERE
    	s.Sid = sc.Sid
    AND sc.score < 60
    GROUP BY
    	s.Sid
    HAVING
    	count(sc.score) >= 2;
    # Tip 1、 from后可以派生虚拟表但是要在括号外给别名  2、聚合函数必须group by

![89a768468c3a444f84e431a9d31c601b.png][]

#### 12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息 ####

# 12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
     SELECT s.* FROM student s WHERE s.Sid IN (
        select sid from student_course sc where sc.cid = '01' and sc.score <60
    );
    
    SELECT s.*, sc.score FROM student s, student_course sc WHERE s.sid = sc.sid and sc.cid = '01' and sc.score <60 order by sc.score desc;

![79eb82fc45ed4300a300a0a03be4bb9f.png][]

####  13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 ??? ####

# 13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 ???
    select sc.sid, sum(sc.score), avg(sc.score) from student_course sc group by sc.sid order by avg(sc.score) desc;
    
    SELECT sc1.score as score_01,sc2.score as score_02,sc3.score as score_03,avg(sc.score)as 平均成绩
    FROM student_course sc,student_course sc1,student_course sc2,student_course sc3
    WHERE sc.Sid = sc1.Sid
    and sc.Sid = sc1.Sid
    and sc.Sid = sc2.Sid
    and sc.Sid = sc3.Sid
    and sc1.Cid = '01'
    and sc2.Cid = '02'
    and sc3.Cid = '03'
    GROUP BY sc.Sid
    ORDER BY 平均成绩 desc;

![50b5aa306b784e8c93c6e801cc7b944d.png][]

####  14. 查询各科成绩最高分、最低分和平均分 ####

# ☆☆☆☆☆14. 查询各科成绩最高分、最低分和平均分：
    select sc.cid, max(sc.score), min(sc.score), avg(sc.score) from student_course sc group by sc.cid;
    
    SELECT
    	c.Cid,
    	c.Cname,
    	max(sc.score) AS 最高分,
    	min(sc.score) AS 最低分,
    	avg(sc.score) AS 平均分,
    	count(sc.sid) AS 选修人数,
    sum(case when sc.score >=60 then 1 else 0 end)/count(sc.sid) as 及格率,
    sum(case when sc.score >=70 and sc.score <=80 then 1 else 0 end)/count(sc.sid)as 中等率,
    sum(case when sc.score >=80 and sc.score <=90 then 1 else 0 end)/count(sc.sid) as 优良率,
    sum(case when sc.score >=90 and sc.score <=100 then 1 else 0 end)/count(sc.sid) as 优秀率
    FROM
    	course c,
    	student_course sc
    WHERE
    	c.Cid = sc.Cid
    GROUP BY
    	c.Cid
    ORDER BY
    	选修人数 DESC,c.Cid asc;
    # Tip 1.遇到区间或率的，想起来case when then else end  2.可以多重排序中间逗号相隔

![7f3c496fd38246cb955fb351c226fc8b.png][]

####  15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺 ####

# ☆☆☆☆☆15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
    SELECT s.*, rank_01,rank_02,rank_03,rank_total FROM student s LEFT JOIN
    (SELECT Sid,rank () over (PARTITION BY Cid ORDER BY score DESC) AS rank_01 FROM student_course sc WHERE Cid = '01') AS a ON a.Sid = s.Sid LEFT JOIN
    (SELECT Sid,rank () over (PARTITION BY Cid ORDER BY score DESC) AS rank_02 FROM student_course sc WHERE Cid = '02') AS b ON b.Sid = s.Sid LEFT JOIN
    (SELECT Sid,rank () over (PARTITION BY Cid ORDER BY score DESC) AS rank_03 FROM student_course sc WHERE Cid = '03') AS c ON c.Sid = s.Sid LEFT JOIN
    (SELECT Sid,rank () over (ORDER BY avg(score) DESC) AS rank_total FROM student_course sc GROUP BY Sid) AS d ON d.Sid = s.Sid
    ORDER BY rank_total ASC;
    -- 5.7.24-log       Mysql 8.0之前是不支持 partition by 语法的。
    select version();
    # TIPs: 1、row_num()、rank()、dense_rank()等窗口分析函数的应用。2、select 连接字段(例sid),rank() over (partition by 字段1 order by 字段2 desc) as rank
    #       3、自联结+rank()函数的结合运用。
    
    # 15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次

#### 16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺 ####

#16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
    SELECT
    	Sid,
    	sum(sc.score) AS 总成绩,
    	rank () over (ORDER BY sum(sc.score) DESC) AS rank0
    FROM
    	student_course sc
    GROUP BY
    	Sid;
    # 注意: 以上语法5.7版本是不支持的.
    SELECT
    	t.*, @rank := @rank + 1 AS 排名
    FROM
    	(SELECT @rank := 0) r,
    	(
    		SELECT
    			sc.sid,
    	        sum(sc.score) AS totalScore
    		FROM
    			student_course sc
    		group by sc.sid
    	) AS t order by  t.totalScore desc;

![294da27f384d4f2d987c6ada0736035a.png][]

#### 17. 统计各科成绩各分数段人数：课程编号，课程名称，\[100-85\]，\[85-70\]，\[70-60\]，\[60-0\] 及所占百分比 ####

# 17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
    SELECT c.cid,c.cname,
           count(sc.sid) as '选修人数',
    concat(round(sum(CASE WHEN sc.score >= 85 THEN 1 ELSE 0 END) / count(sc.sid)*100,2),'%') AS '[85-100]%',
    concat(round(sum(CASE WHEN sc.score >= 70 AND sc.score < 85 THEN 1 ELSE 0 END) / count(sc.sid)*100,2),'%') AS '[70-85)%',
    concat(round(sum(CASE WHEN sc.score >= 60 AND sc.score < 70 THEN 1 ELSE 0 END) / count(sc.sid)*100,2),'%') AS '[60-70)%',
    concat(round(sum(CASE WHEN sc.score < 60 THEN 1 ELSE 0 END) / count(sc.sid)*100,2),'%') AS '[0-60)%'
    FROM course c, student_course sc
    WHERE sc.cid = c.cid GROUP BY sc.cid;
    # -- 舍掉小数点后几位: select concat(truncate(0.8799*100,2),'%')  结果 87.00%
    # -- 四舍五入: select concat(round(0.8799*100,2),'%')  结果 88.00%
    # -- 舍掉小数点后几位: select concat(floor(0.8799*100),'%')  结果 87%

![f797af78d8cb40518cabc13afacd498a.png][]

####  18、查询各科成绩前三名的记录 ####

# 18、查询各科成绩前三名的记录
    SELECT * FROM
    (SELECT *, rank () over (PARTITION BY Cid ORDER BY score DESC) AS grade
    FROM student_course sc) AS t
    WHERE t.grade <= 3;
    # 注意: 以上语法5.7版本是不支持的.
    # 如果要对rank的结果进行进一步选择的话，采用结构select * from (select rank()) where 的句式
    # partition by 是在保留所有记录的情况下，按字段顺序排序，类似于excel的筛选功能。
    
    # 19. 查询每门课程被选修的学生数 [上面17 已经查出来]
    
    # 20. 查询出只选修两门课程的学生学号和姓名
    select s.sid, s.sname, count(cid) AS 'courseCount' from student_course sc, student s where sc.sid = s.sid group by sid having count(cid) = 2;

![aac6471f73d84e5abf3c5e89e41cd4a6.png][]

#### 21. 查询男生、女生人数 ####

# 21. 查询男生、女生人数
    select CASE WHEN s.sex = 1 THEN '男' WHEN s.sex = 2 THEN '女' ELSE '未知' END as '性别', count(1) AS 'count' from student s group by s.sex;

![413d1cdfcf3c4d488faa78c181bac0fa.png][]

#### 22. 查询名字中含有「果」字的学生信息 ####

#22. 查询名字中含有「果」字的学生信息
    SELECT * FROM student WHERE Sname LIKE '%果%';

![188b0afc78174894bc81671ebe9e7565.png][]

#### 23. 查询同名同性学生名单，并统计同名人数 ####

# 23. 查询同名同性学生名单，并统计同名人数
    -- 2个名字一样的,所以 count(1)> 1
    select Sname, count(1) from student group by Sname having  count(1)> 1;

![afa21ed41a374c58b3eec84ad2fd63d2.png][]

#### 24. 查询 1990 年出生的学生名单 ####

# 24. 查询 1990 年出生的学生名单
    select sname as '姓名', CASE WHEN s.sex = 1 THEN '男' WHEN s.sex = 2 THEN '女' ELSE '未知' END as '性别' from student s where year(birthday) = 1990;

![ecfe4d1cdaa14b01807f4f3b46ad961b.png][]

####  25. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 ####

# 25. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
    SELECT SC.cid, avg(sc.score) FROM student_course sc group by SC.cid ORDER BY avg(sc.score) DESC,SC.cid;

![384de68d714c424ba9feca528486f8f1.png][]

####  26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩 ####

# 26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
    select s.sid, s.sname, avg(sc.score) from student_course sc left join student s on sc.sid = s.sid group by sc.sid having avg(sc.score) >=85;
    SELECT
    	s.Sid,
    	s.Sname,
    	AVG(sc.score) AS 平均成绩
    FROM
    	student s,
    	student_course sc
    WHERE
    	s.Sid = sc.Sid
    GROUP BY
    	sc.Sid
    HAVING
    	平均成绩 >= 85;

![034f427db86d4594938a115cda95ad3c.png][]

####  27. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数 ####

# 27. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
    select s.sname, sc.score from student_course sc, student s where sc.sid = s.sid and sc.cid =(
        select c.cid from course c where c.cname = '数学') and sc.score < 60;
    SELECT
    	s.Sname,
    	sc.score
    FROM
    	student s,
    	student_course sc,
    	course c
    WHERE
    	s.Sid = sc.Sid
    AND sc.Cid = c.Cid
    AND c.Cname = '数学'
    AND sc.score <= 60;

![0b458faa557b48579a313a1f7801a6ef.png][]

####  28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况） ####

# 28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
    SELECT DISTINCT
    	s.sid, s.sname, c.cname, sc.score
    FROM
    	student s
    LEFT JOIN student_course sc ON s.Sid = sc.Sid
    LEFT JOIN course c ON sc.Cid = c.Cid order by s.sid;
    # 要查询每一科的成绩所以不能用group by 分组, group by分组要排序的话 排序字段要是聚合函数,如avg(),sum(),max()...

![ff391dc2bd7d44c290f3d6b3752777dc.png][]

####  29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数 ####

# 29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
    select sc.sid, s.sname, c.cid, c.cname, sc.score FROM student_course sc,student s, course c where sc.sid = s.sid and sc.cid =c.cid and sc.score >70;

![7fef95765edb4a2b8b43e01818e3486d.png][]

####  30.查询有学生不及格的课程信息（课程id和课程名字） ####

# 30.查询有学生不及格的课程信息（课程id和课程名字）
    select distinct sc.cid, c.cname from student_course sc, course c where sc.cid =c.cid and sc.score < 60;
    select distinct sc.cid, c.cname from student_course sc right join course c on sc.cid =c.cid and sc.score < 60;
    # 注: 上面即使是右连接也要使用 distinct

![0afb1fd4c3e4450abd82e9e245a67a83.png][]

####  31. 查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名 ####

# 31. 查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名
    SELECT
    	s.Sid,
    	s.Sname
    FROM
    	student s,
    	student_course sc
    WHERE
    	s.Sid = sc.Sid
    AND sc.Cid = '01'
    AND sc.score >= 80;

![56226e1fe4c94d29bb577869e17ab875.png][]

#### 32. 查每门课程的学生人数 ####

# 32. 查每门课程的学生人数
    SELECT
    	c.Cname,
    	count(1) AS '选课人数'
    FROM
    	course c,
    	student_course sc
    WHERE
    	c.Cid = sc.Cid
    GROUP BY
    	sc.Cid;

![ccc5ae268827419498423d4c4b1107c1.png][]

#### 33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩 ####

# 33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
    select sc.sid, s.sname, max(sc.score) from student_course sc, course c, teacher t, student s where sc.cid = c.cid and c.tid = t.tid and sc.sid = s.sid and t.tname = '张三';
    
    SELECT
    	s.*, sc.score
    FROM
    	student s,
    	student_course sc,
    	course c,
    	teacher t
    WHERE
    	s.Sid = sc.Sid
    AND c.Cid = sc.Cid
    AND c.Tid = t.Tid
    AND t.Tname = '张三'
    HAVING
    	sc.score = max(sc.score);
    # 对于求最高或最低的情况两种思路 1. having score =max(score) 2.score = (select score from order by score desc limit 1) 。
    # 对于求排名第二或其他情况的则取上面的第二种方法。

![641a01020a9741108c4f0db243388362.png][]

####  35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 ####

# 35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
    SELECT DISTINCT
    	sc.Sid, s.sname,
    	sc.Cid, c.cname,
    	sc.score
    FROM
    	student_course sc,
    	student_course sc1,
    	student s,
    	course c
    WHERE
    	sc.sid = sc1.Sid and sc.sid =s.sid and sc.cid = c.cid
    AND sc.Cid <> sc1.Cid
    AND sc.score = sc1.score;

![8c3181c786bb41cb97ec4524eb1d86b1.png][]

####  36. 查询每门课程成绩最好的前两名 ####

# 36. 查询每门课程成绩最好的前两名
    SELECT * FROM(
    SELECT *, rank () over (PARTITION BY Cid ORDER BY score DESC) AS grade
    FROM student_course sc) AS t
    WHERE t.grade < 3 ORDER BY Cid
    # 以上窗口函数 5.7 不支持
    -- 5.7.24-log
    select version();
    # 遇到选前几名的肯定要用窗口函数，谨记，如果对前几名有限制的select * from（select rank() over(partition by order by )as grade from ）as where grade 模式

#### 37. 统计每门课程的学生选修人数（超过 5 人的课程才统计） ####

# 37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）
    SELECT
    	cid,
    	count(1)
    FROM
    	student_course sc
    GROUP BY
    	Cid
    HAVING
    	count(1) > 5;

![418b6a25ebc44cc984e3c0c83dca012b.png][]

#### 38. 检索至少选修两门课程的学生学号 ####

# 38. 检索至少选修两门课程的学生学号
    SELECT
    	Sid
    FROM
    	student_course
    GROUP BY
    	Sid
    HAVING
    	count(1) > 2;

![68c40db915c04eff9d9f24f9b7b55b76.png][]

#### 39. 查询选修了全部课程的学生信息 ####

# 39. 查询选修了全部课程的学生信息
    SELECT
    	s.sid, s.sname, count(sc.cid) num
    FROM
    	student s,
    	student_course sc
    WHERE
    	s.Sid = sc.Sid
    GROUP BY
    	sc.Sid
    HAVING
    	num = (SELECT count(1) FROM course);

![71c8a4e042d74f28829c1ef8ee96e283.png][]

#### 40. 查询各学生的年龄，只按年份来算 ####

# 40. 查询各学生的年龄，只按年份来算
    SELECT
    	s.id, s.sname, YEAR (s.birthday), YEAR (now()) - YEAR (s.birthday) AS age
    FROM
    	student s;

![b066e79e1142421596ea77e4a16b65b1.png][]

#### 41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一 ####

# 41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
    SELECT
    	s.id, s.sname,  (
    		CASE WHEN DAY (now()) < DAY (s.birthday) THEN
    			YEAR (now()) - YEAR (s.birthday) - 1
    		ELSE
    			YEAR (now()) - YEAR (s.birthday)
    		END
    	) AS age
    FROM
    	student s;
    
    SELECT
    	s.id, s.sname,  (
        IF(DAY(now()) < DAY(s.birthday), YEAR(now()) - YEAR(s.birthday) - 1, YEAR(now()) - YEAR(s.birthday))
    	) AS age
    FROM
    	student s;

![04b11d73b1e343f6a3b35a8dd06e138e.png][]

#### 42. 查询本周过生日的学生 ####

# 42. 查询本周过生日的学生
    SELECT
    	s.id, s.sname, WEEK (now())
    FROM
    	student s
    WHERE
    	WEEK (s.birthday) = WEEK (now()) ;

![bbe29cf2ef564672b8915d07a07a6ef7.png][]

#### 43. 查询下周过生日的学生 ####

# 43. 查询下周过生日的学生
    SELECT
    	s.id, s.sname, WEEK (now())+1 as 'nextWeek'
    FROM
    	student s
    WHERE
    	WEEK (s.birthday) = WEEK (now()) +1;

![574806d25b684fe78269167c944da938.png][]

####  44. 查询本月过生日的学生 ####

# 44. 查询本月过生日的学生
    SELECT
    	s.id, s.sname, month (now())
    FROM
    	student s
    WHERE
    	month (s.birthday) = month (now()) ;

![e49e59c502b54ec0bc3b8e59fe18b12d.png][]

## 结语: ##

最后推荐一款mysql 连接工具: datagrip, 它 和idea 类似的设计,好用到爆!

[https_blog.csdn.net_cpc784221489_article_details_105102005]: https://blog.csdn.net/cpc784221489/article/details/105102005
[c8c750422e2c4253bfb3320bd24e6d58.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/6fe1ac2bcd004bd4843228640284156f.png
[4e15261526804ad3ae76884c4fef3123.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/3a5d306c6e4e4938b8c8391450e331a3.png
[169d2c37297842f9a187903b3ee07155.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/2c29a79822fb486eaddbd5583de30db2.png
[91f82092445b479f8286e1b4bc31a1d6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/04cbd03c37de4162bc59ce3aa37e9191.png
[30c33ed730214449b7dc2e5388ce727a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/1065675ae41449a3b4df92d00c834af3.png
[6e1e5756416849cc9ac96140a7a6a4e0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/9efd87ef46934480887ded155ade410e.png
[b4f84ff15a754448a28ada2af278955a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/20fe99e03eac433f8dbb589f98955985.png
[e080fb61eea34165b1777fc0f7fd19cf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/e0c9ec582365407599f20b615967e28e.png
[b9bba6e7f95445419a3e76901e932210.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/73bc1a1c1d9745ce9e16bf0ceb497f76.png
[a0733d144a2544bf9aaf7e2b41b62968.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/5f730adf05ca4920b3c737f3a4e2edd7.png
[be9e9f5217db4837b0fe2ab847f9fc01.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/7d606ead1ee844f3a8ef6f0d16df09b6.png
[8d404d8c87524f47be602cc9fdc6cc92.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/ece6940c22c3488cb390f9c60f4d5178.png
[1b1366b765c64f009580cf0f26a80baa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/65797ff1ebe94d039502f1dc6169dd5b.png
[3aa32c25f4684fe39944e34e2a73f1e7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/6ff41734472d4f3cb35ad19b9b504155.png
[40c3219a3f814c2aa8f6d298d44c8fca.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/bca272f166c945e6bb53cb5c10a8e35a.png
[89a768468c3a444f84e431a9d31c601b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/41c7558d9e92416caa12a656191a7350.png
[79eb82fc45ed4300a300a0a03be4bb9f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/e8a7b36cdd2e432ebdae3e1cd25a6b51.png
[50b5aa306b784e8c93c6e801cc7b944d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/7a72328d06904d7dbaab5002bd43d02a.png
[7f3c496fd38246cb955fb351c226fc8b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/0ef4979d09e94b29baf0c93a8c1c17c2.png
[294da27f384d4f2d987c6ada0736035a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/7b4d805807494072aabf144fc2518b96.png
[f797af78d8cb40518cabc13afacd498a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/13df95a93e994f4aaf30dd6b04899cd3.png
[aac6471f73d84e5abf3c5e89e41cd4a6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/42c5eb95a4a349b1b3edcaed9bae4791.png
[413d1cdfcf3c4d488faa78c181bac0fa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/51429eb1d874494abee5fca901df6371.png
[188b0afc78174894bc81671ebe9e7565.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/812d39d98ad8413593548e7f06a6be43.png
[afa21ed41a374c58b3eec84ad2fd63d2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/8c7b370f91614e37b6cb3ad2fd9b50d0.png
[ecfe4d1cdaa14b01807f4f3b46ad961b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/b8b59c779d7f40329a3245200dc2952a.png
[384de68d714c424ba9feca528486f8f1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/7f190f1883514d2d9fab72dd18595040.png
[034f427db86d4594938a115cda95ad3c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/1242c8bf43bd4893bdaa70b38373c852.png
[0b458faa557b48579a313a1f7801a6ef.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/c1fcbde81aa94edca9d01d944248f564.png
[ff391dc2bd7d44c290f3d6b3752777dc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/ac08dfa11e4e43599fb3b6829e0e1f29.png
[7fef95765edb4a2b8b43e01818e3486d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/accbf63e7dac4c5daebc05d381ccc4ed.png
[0afb1fd4c3e4450abd82e9e245a67a83.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/5c144d14322a4c079221cd676c082851.png
[56226e1fe4c94d29bb577869e17ab875.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/d938d76b5aac4c5c981f1f4a166017ca.png
[ccc5ae268827419498423d4c4b1107c1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/c8948af2f6114d35b2225875b0838493.png
[641a01020a9741108c4f0db243388362.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/c67971a6817741a392962cf995e885a5.png
[8c3181c786bb41cb97ec4524eb1d86b1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/d0c32dad582b477ebe0654fa3ffa5502.png
[418b6a25ebc44cc984e3c0c83dca012b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/e274736f1e0c49dd8142787a41a2d6da.png
[68c40db915c04eff9d9f24f9b7b55b76.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/d998ab3a87e645d4a45b66fa0f7d6b04.png
[71c8a4e042d74f28829c1ef8ee96e283.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/51f3d848e1b74a3abcebb4cd1892c37a.png
[b066e79e1142421596ea77e4a16b65b1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/1c7a56e325634849a2bbac2622a91169.png
[04b11d73b1e343f6a3b35a8dd06e138e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/6b255b954f734e94a4c1662771f426be.png
[bbe29cf2ef564672b8915d07a07a6ef7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/67831059899544aeaa7053d39df330a3.png
[574806d25b684fe78269167c944da938.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/5ac7579c362a4c3c8055db81988fb8d5.png
[e49e59c502b54ec0bc3b8e59fe18b12d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/6caa8d146f1e423d9df5ec8cca6781e0.png