数据库经典练习及springboot+mybatisplus代码实现

Oh. my sunshine

已于 2023-12-29 12:42:29 修改

阅读量495

点赞数 13

文章标签：数据库 spring boot 后端

于 2023-12-29 11:56:05 首次发布

本文链接：https://blog.csdn.net/sunshaisaa/article/details/135287285

版权

MySQL 经典练习及springboot+mybatisplus代码实现

准备数据

-- 删除已存在的数据库（如果存在）
DROP DATABASE IF EXISTS mysql_test;

-- 创建新的数据库 mysql_test，并设置字符集为 utf8mb4 以支持中文
CREATE DATABASE mysql_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 使用新创建的数据库 mysql_test
USE mysql_test;

-- 创建教师表
CREATE TABLE teacher (
    t_id INT PRIMARY KEY COMMENT '教师ID',
    t_name VARCHAR(20) COMMENT '教师名字'
) COMMENT='教师信息表';

-- 向教师表中插入数据
INSERT INTO teacher VALUES
(1,'张三'),
(2,'李四'),
(3,'王五');

-- 创建课程表
CREATE TABLE course (
    c_id INT PRIMARY KEY COMMENT '课程ID',
    c_name VARCHAR(20) COMMENT '课程名称',
    t_id INT COMMENT '授课教师ID',
    FOREIGN KEY (t_id) REFERENCES teacher(t_id)
) COMMENT='课程信息表';

-- 向课程表中插入数据
INSERT INTO course VALUES
(1,'语文',2),
(2,'数学',1),
(3,'英语',3);

-- 创建学生表
CREATE TABLE student (
    s_id INT PRIMARY KEY COMMENT '学生ID',
    s_name VARCHAR(20) COMMENT '学生姓名',
    s_birth DATE COMMENT '出生日期',
    s_sex VARCHAR(4) COMMENT '性别'
) COMMENT='学生信息表';

-- 向学生表中插入数据
INSERT INTO student VALUES
(1,'赵雷','1990-01-01','男'),
(2,'钱电','1990-12-21','男'),
(3,'孙风','1990-05-20','男'),
(4,'李云','1990-08-06','男'),
(5,'周梅','1991-12-01','女'),
(6,'吴兰','1992-03-01','女'),
(7,'郑竹','1989-07-01','女'),
(8,'王菊','1990-01-20','女');

-- 创建成绩表
CREATE TABLE score (
    s_id INT COMMENT '学生ID',
    c_id INT COMMENT '课程ID',
    s_score INT COMMENT '成绩',
    PRIMARY KEY (s_id, c_id),
    FOREIGN KEY (s_id) REFERENCES student(s_id),
    FOREIGN KEY (c_id) REFERENCES course(c_id)
) COMMENT='学生成绩表';

-- 向成绩表中插入数据
INSERT INTO score VALUES
(1,1,80),
(1,2,90),
(1,3,99),
(2,1,70),
(2,2,60),
(2,3,65),
(3,1,80),
(3,2,80),
(3,3,80),
(4,1,50),
(4,2,30),
(4,3,40),
(5,1,76),
(5,2,87),
(6,1,31),
(6,3,34),
(7,2,89),
(7,3,98);

在这里插入图片描述

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

使用inner join

分析：
1、找出有01成绩的学生成绩信息
2、找出有02成绩的学生成绩信息
3、通过SId将两表取别名t1、t2进行左连接
4、加上满足01‘语文’ > 02’数学’的条件

select stu.*,sco1.s_score as 课程1,sco2.s_score as 课程2 from student stu 
inner join score sco1  on stu.s_id=sco1.s_id and sco1.c_id=1 
inner join score sco2  on stu.s_id=sco2.s_id and sco2.c_id=2
where sco1.s_score > sco2.s_score;

ON 子句:决定了如何连接两个表
WHERE 子句用于过滤已连接的行，决定哪些行应该出现在查询结果中。
它在 JOIN 操作之后进行，对结果进行额外的过滤。

//注意：相当于三张表组成一张大表进行单表查询

在这里插入图片描述

代码实现(springboot+mybatisplus )

学生类

/**
 * 学生信息表
 * @TableName student
 */
@Component
@TableName(value ="student")
@Data
public class Student implements Serializable {
    /**
     * 学生ID
     */
    @TableId(value = "s_id")
    private Integer sId;

    /**
     * 学生姓名
     */
    @TableField(value = "s_name")
    private String sName;

    /**
     * 出生日期
     */
    @TableField(value = "s_birth")
    private Date sBirth;

    /**
     * 性别
     */
    @TableField(value = "s_sex")
    private String sSex;

    @TableField(exist = false)
    private static final long serialVersionUID = 1L;
}

dto

package com.genarator.dto;

import lombok.Data;
import org.springframework.stereotype.Component;

@Component
@Data
public class StudentScoreDTO {

    private Long studentId;
    private String studentName;
    // 课程01和02的成绩
    private Integer scoreCourse01;
    private Integer scoreCourse02;
}

mapper

package com.genarator.mapper;

import com.genarator.domain.Student;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.genarator.dto.StudentScoreDTO;
import java.util.List;
/**
* @description 针对表【student(学生信息表)】的数据库操作Mapper
* @Entity com.genarator.domain.Student
*/
public interface StudentMapper extends BaseMapper<Student> {
    List<StudentScoreDTO> selectStudentsWithHigherScoresInCourse01Than02();

}

XML

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.genarator.mapper.StudentMapper">

    <resultMap id="BaseResultMap" type="com.genarator.domain.Student">
            <id property="sId" column="s_id" jdbcType="INTEGER"/>
            <result property="sName" column="s_name" jdbcType="VARCHAR"/>
            <result property="sBirth" column="s_birth" jdbcType="DATE"/>
            <result property="sSex" column="s_sex" jdbcType="VARCHAR"/>
    </resultMap>
    <sql id="Base_Column_List">
        s_id,s_name,s_birth,
        s_sex
    </sql>

    <select id="selectStudentsWithHigherScoresInCourse01Than02" resultType="com.genarator.dto.StudentScoreDTO">
        SELECT stu.*, sco1.s_score AS 课程1, sco2.s_score AS 课程2
        FROM student stu
                 INNER JOIN score sco1 ON stu.s_id = sco1.s_id AND sco1.c_id = 1
                 INNER JOIN score sco2 ON stu.s_id = sco2.s_id AND sco2.c_id = 2
        WHERE sco1.s_score > sco2.s_score;
    </select>
</mapper>

service接口

package com.genarator.service;

import com.genarator.domain.Student;
import com.baomidou.mybatisplus.extension.service.IService;
import com.genarator.dto.StudentScoreDTO;
import java.util.List;
public interface StudentService extends IService<Student> {
    public List<StudentScoreDTO> getStudentsWithHigherScoresInCourse01Than02();
}

实现类

package com.genarator.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.genarator.domain.Student;
import com.genarator.dto.StudentScoreDTO;
import com.genarator.service.StudentService;
import com.genarator.mapper.StudentMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class StudentServiceImpl extends ServiceImpl<StudentMapper, Student> implements StudentService{

    @Autowired
    private StudentMapper studentMapper;
   
    @Override
    public List<StudentScoreDTO> getStudentsWithHigherScoresInCourse01Than02() {
        return studentMapper.selectStudentsWithHigherScoresInCourse01Than02();
    }
}

测试接口

@Test
void test(){
  List<StudentScoreDTO> studentsWithHigherScoresInCourse01Than02 =              studentService.getStudentsWithHigherScoresInCourse01Than02();
  System.out.println(studentsWithHigherScoresInCourse01Than02);
}

结果

在这里插入图片描述

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

//having对分组之后的条件进行过滤

select stu.s_id,stu.s_name,avg(sco.s_score) 平均成绩 from student stu 
inner join score sco on sco.s_id=stu.s_id
group by stu.s_id,stu.s_name
having avg(sco.s_score)>60

在这里插入图片描述

3：查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

//不一定所有人都选了课
select stu.s_id,stu.s_name,sco.s_id from student stu 
left join score sco on sco.s_id=stu.s_id 
  
//COALESCE(AVG(sc.s_score), 0) AS avg_score 计算每个学生的平均成绩。如果学生没有成绩记录，AVG(sc.s_score) 将返回 NULL，COALESCE 函数将其转换为 0。
  
select stu.s_id,stu.s_name,coalesce(avg(sco.s_score),0) from student stu 
left join score sco on sco.s_id=stu.s_id 
group by stu.s_id,stu.s_name
having coalesce(avg(sco.s_score),0)<60

在这里插入图片描述

4：查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select stu.s_id,stu.s_name,coalesce(sum(sco.s_score),0) 总成绩 ,coalesce(count(sco.s_id),0) 选课总数  from student stu 
left join score sco on sco.s_id=stu.s_id 
group by stu.s_id,stu.s_name

在这里插入图片描述

5：查询"李"姓老师的数量

//like
select count(*) from teacher t where t.t_name like '李%'

6：学过"张三"老师授课的同学的信息

SELECT * FROM course c INNER JOIN teacher t ON c.t_id = t.t_id

在这里插入图片描述

SELECT s.*
FROM student s
WHERE s_id in
(SELECT s_id
FROM score sc
INNER JOIN 
(SELECT c_id FROM course c INNER JOIN teacher t ON c.t_id = t.t_id WHERE t_name = '张三') t1
ON sc.c_id = t1.c_id)

在这里插入图片描述

7：查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

	select * from student where student.s_id in 
	(select s_id
	from score
	where c_id in (1,2)
	group by s_id
	having count(*) = 2)

在这里插入图片描述

8：查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

select stu.* from student stu where stu.s_id in 
(select sco.s_id from score sco where c_id=1 ) and stu.s_id not in (select sco2.s_id from score sco2 where c_id=2)

在这里插入图片描述

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

select * from score s inner join (select c_id from score where s_id = 1) t1 on s.c_id = t1.c_id

在这里插入图片描述

select distinct s.s_id from score s inner join (select c_id from score where s_id = 1) t1 on s.c_id = t1.c_id

在这里插入图片描述

select * from student where s_id in (

select distinct s.s_id from score s inner join (select c_id from score where s_id = 1) t1 on s.c_id = t1.c_id
)

在这里插入图片描述

10：查询和"01"号的同学学习的课程完全相同的其他同学的信息

select * from student stu  where stu.s_id in (

SELECT s_id FROM score s INNER JOIN  (SELECT c_id FROM score WHERE s_id = 1) t1 on s.c_id = t1.c_id WHERE s_id != 1

group by s_id
)

在这里插入图片描述

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

select s.s_id,s.s_name,round(avg(s_score), 2) as avg_score
from student s
inner join (
	select s_id
	from score
	where s_score < 60
	group by s_id
	having count(*) >= 2
) t1
on s.s_id = t1.s_id
inner join score sc on s.s_id = sc.s_id
group by s.s_id;
//第一个Inner先找符合条件的学生
//第二个inner找成绩

在这里插入图片描述

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

//order by score_01 desc
select s.*,s_score as score_01
from student s
inner join (
	select s_id,s_score
	from score
	where c_id = 1
	and s_score < 60
) t1
on s.s_id = t1.s_id
order by score_01 desc;

在这里插入图片描述

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

select
	s.s_id as 学号,s.s_name as 姓名,
	sum(case c_id when 1 then s_score else 0 end) as 语文,
	sum(case c_id when 2 then s_score else 0 end) as 数学,
	sum(case c_id when 3 then s_score else 0 end) as 英语,
	ifnull(round(avg(s_score), 2), 0) as 平均成绩
from student s
left join score sc on s.s_id = sc.s_id
group by s.s_id
order by 平均成绩 desc;

在这里插入图片描述

14：查询各科成绩最高分、最低分和平均分，以如下形式显示：

课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
– 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

select
  c.c_id as 课程ID,c.c_name as 课程name,
	max(s_score) as 最高分,
	min(s_score) as 最低分,
	round(avg(s_score), 2) as 平均分,
	concat(round(sum(case when s_score >= 60 then 1 else 0 end) / count(*) * 100, 2), '%') as 及格率,
	concat(round(sum(case when s_score between 70 and 80 then 1 else 0 end) / count(*) * 100, 2), '%') as 中等率,
	concat(round(sum(case when s_score between 80 and 90 then 1 else 0 end) / count(*) * 100, 2), '%') as 优良率,
	concat(round(sum(case when s_score >= 90 then 1 else 0 end) / count(*) * 100, 2), '%') as 优秀率
from course c
inner join score s on c.c_id = s.c_id
group by c.c_id;

//ROUND(AVG(s_score), 2) AS 平均分 计算每门课程的平均分，结果四舍五入到小数点后两位。
//concat()合并%

在这里插入图片描述

15：按各科成绩进行排序，并显示排名

SELECT 
    s.s_id,
    s.s_name,
    sc.s_score,
    ROW_NUMBER() OVER (ORDER BY sc.s_score DESC) AS 排名
FROM 
    student s
INNER JOIN 
    score sc ON s.s_id = sc.s_id
WHERE 
    sc.c_id = 1
ORDER BY 
    sc.s_score DESC;
//ROW_NUMBER() 为每行提供一个唯一的排名，即使有并列，而 RANK() 在并列时会提供相同的排名。 MySQL 8.0 版本中引入的