MySQL四表查询

数据结构说明：

-- 学生表：student(学号,学生姓名,出生年月,性别)
-- 成绩表：score(学号,课程号,成绩)
-- 课程表：course(课程号,课程名称,教师号)
-- 教师表：teacher(教师号,教师姓名)

四表关系图.png

创建数据

-- 1)创建学生表(student)
CREATE TABLE `student` (
`学号` varchar(255) NOT NULL,
`姓名` varchar(50) DEFAULT NULL,
`出生日期` date DEFAULT NULL,
`性别` varchar(10) DEFAULT NULL,
PRIMARY KEY (`学号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 2)创建成绩表(score)
CREATE TABLE `score` (
`学号` varchar(255) NOT NULL,
`课程号` varchar(255) NOT NULL,
`成绩` float(3,0) DEFAULT NULL,
PRIMARY KEY (`学号`,`课程号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 3)创建课程表(course)
CREATE TABLE `course` (
`课程号` varchar(255) NOT NULL,
`课程名称` varchar(255) DEFAULT NULL,
`教师号` varchar(255) DEFAULT NULL,
PRIMARY KEY (`课程号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 4)教师表(teacher)

CREATE TABLE `teacher` (
`教师号` varchar(255) NOT NULL,
`教师姓名` varchar(50) DEFAULT NULL,
PRIMARY KEY (`教师号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

-- 1)学生表添加数据
insert into student values
('0001' , '猴子' , '1989-01-01' , '男'),
('0002' , '猴子' , '1990-12-21' , '女'),
('0003' , '马云' , '1991-12-21' , '男'),
('0004' , '王思聪' , '1990-05-20' , '男');
-- 2)成绩表添加数据
insert into score values
('0001' , '0001' , 80),
('0001' , '0002' , 90),
('0001' , '0003' , 99),
('0002' , '0002' , 60),
('0002' , '0003' , 80),
('0003' , '0001' , 80),
('0003' , '0002' , 80),
('0003' , '0003' , 80);
-- 3)课程表添加数据
insert into course values
('0001' , '语文' , '0002'),
('0002' , '数学' , '0001'),
('0003' , '英语' , '0003');
-- 4)教师表添加数据
insert into teacher values
('0001' , '孟扎扎'),
('0002' , '马化腾'),
('0003' , null),
('0004' , '');

一、简单查询

-- 查询姓“猴”的学生名单

select * from student where 姓名 like "猴%";

-- 查询姓名中最后一个字是“猴”字的学生名单

select * from student where 姓名 like "%猴";

-- 查询姓名中带“猴”字的学生名单

select * from student where 姓名 like "%猴%";

-- 查询姓“孟”老师的个数

select count("教师个数") from teacher where 教师姓名 like '孟%';

-- 查询课程编号为“0002”的总成绩

select sum(成绩) from score where 课程号 = "0002";

-- 查询选了课程的学生人数

select count(*) from (select distinct 学号 from score) s;

select count(distinct 学号) 选了课程的学生人数 from score;

二、分组查询

-- 查询各科成绩的最高分和最低分, 以如下的形式显示：课程号，最高分，最低分

select 课程号,max(成绩) 最高分,min(成绩) 最低分 from score group by 课程号;

-- 查询每门课程选修的学生数

select 课程号,count(学号) 选修的学生数 from score group by 课程号;

-- 查询男生、女生人数
select 性别, count(学号) 人数 from student group by 性别;

-- 查询平均成绩大于60分学生的学号和平均成绩

select 学号, avg(成绩) 平均成绩 from score group by 学号 having avg(成绩) > 60;

-- 查询至少选修两门课程的学生学号

select 学号 from score group by 学号 having count(课程号) >= 2;

-- 查询同名同姓学生名单并统计同名人数

select  姓名, count(*) from student group by 姓名 having count(*)>=2;

-- 查询不及格的课程并按课程号从大到小排列

select 课程号 from score  where 成绩 < 60 order by 课程号 desc;

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

select 课程号, avg(成绩) 平均成绩 from score group by 课程号 order by  平均成绩 asc ,课程号 desc;

-- 检索课程编号为“0004”且分数小于60的学生学号，结果按按分数降序排列

select 学号, 成绩 from score where 成绩<60 and 课程号 = 0004 order by 成绩 desc;
-- 统计每门课程的学生选修人数(超过2人的课程才统计)，要求输出课程号和选修人数，查询结果
按人数降序排序，若人数相同，按课程号升序排序

select 课程号, count(学号) 选修人数 from score group by 课程号 having count(学号) > 2 order by 
count(学号) desc, 课程号 asc;

-- 查询两门以上不及格课程的同学的学号及其平均成绩

select 学号, avg(成绩) as 平均成绩 from score where 学号 in (select 学号 from score 
where 成绩 < 60 group by 学号 having count(学号) >= 2) group by 学号;

SELECT 学号,AVG(成绩) 平均成绩 FROM score where 成绩<60 GROUP BY 学号 
HAVING COUNT(课程号)>=2;

三、复杂查询（查询结果排序，分组的指定条件）

-- 查询学生的总成绩并进行排名
select 学号, sum(成绩) as 总成绩 from score group by 学号 order by 总成绩 desc;

-- 查询平均成绩大于60分的学生的学号和平均成绩
select 学号, avg(成绩) as 平均成绩 from score group by 学号 having 平均成绩 > 60;

-- 查询课程成绩小于60分学生的学号、姓名
select 学号, 姓名 from student where 学号 in (select 学号 from score where 成绩 < 60)

-- 查询没有学全所有课的学生的学号、姓名
select 学号, 姓名 from student where 学号 in  (select 学号 from  score group by 学号 
having count(课程号)<3);

-- 查询出只选修了两门课程的全部学生的学号和姓名
select 学号, 姓名 from student where 学号 in  (select 学号 from  score group by 学号 
having count(课程号)=2);

四、日期函数

-- 查找1990年出生的学生名单
select * from student where year(出生日期) = '1990';

-- 查询各学生的年龄
select 学号 ,(year(curdate())-year(出生日期)-1) 年龄 from student;
select 学号 ,(datediff(curdate(),出生日期) div 365) 年龄 from student;

-- 查询各学生的年龄(精确到月份)
select 学号 ,timestampdiff(month ,出生日期 ,now())/12 from student ;

-- 查询本月过生日的学生
select * from student where month(出生日期) = month(now());

-- 查询各年龄段学生个数
SELECT (CASE
WHEN a.age IS NULL THEN '未知'
WHEN a.age BETWEEN 0 AND 18 THEN '0-18岁'
WHEN a.age BETWEEN 18 AND 30 THEN '18-30岁'
WHEN a.age BETWEEN 31 AND 45 THEN '30-45岁'
WHEN a.age >= 45 THEN '45岁以上'
END) AS 年龄范围,
COUNT(*) 'value' FROM
(SELECT(YEAR (curdate())- YEAR ( 出生日期 )- 1) age FROM student) a GROUP BY age;

五、 top-N查询：分组取每组最大值、最小值，每组最大的N条(top N)记录

-- 可以使用关联子查询来实现：
-- 分组取每组最小值，按课程号分组取成绩最大值所在行的数据
select * from score as a
where 成绩 = ( select max(成绩) from score as b where b.课程号 = a.课程号);

-- 分组取每组最小值，按课程号分组取成绩最小值所在行的数据
 select * from score as a
 where 成绩 = (select min(成绩) from score as b where  b.课程号 = a.课程号)
 
--  每组最大的N条记录，查询各科成绩前两名的记录

 (select * from score where 课程号 = '0001' order by 课程号,成绩 desc limit 2)
 union all
 (select * from score where 课程号 = '0002' order by 课程号,成绩 desc limit 2)
 union all
 (select * from score where 课程号 = '0003' order by 课程号,成绩 desc limit 2)

六、多表查询

-- 查询所有学生的学号、姓名、选课数、总成绩

select st.学号,st.姓名,count(sc.课程号) as 选课数,sum(sc.成绩) as 总成绩 from student st 
inner join score sc on st.学号 = sc.学号 group by st.学号;

-- 查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select st.学号,st.姓名,avg(sc.成绩) as 平均成绩 from student st inner join score sc 
on st.学号 = sc.学号 group by st.学号 having 平均成绩 > 85;

-- 查询学生的选课情况：学号，姓名，课程号，课程名称

select a.学号, a.姓名, c.课程号, c.课程名称 from student a  join score b on a.学号 = b.学号 
inner join course c on b.课程号 = c.课程号;

根据CSDN博主「草头余」文章练习、学习
原文链接：
https://blog.csdn.net/weixin_42314399/article/details/113399629