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 版本中引入的