多对多查询练习 MySQL

最新推荐文章于 2024-07-07 21:56:46 发布

余玄机ㅤ

最新推荐文章于 2024-07-07 21:56:46 发布

阅读量132

点赞数

文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/qq_52319681/article/details/129308247

版权

#一个查询练习

# 多对多
/* student(sid,sname,sage,ssex) 
course(cid,cname,tid) 课程表
teacher(tid,tname) 教师表
score(id,sid,cid,score) 成绩表 */
create table student(
sid int unsigned primary key auto_increment comment '编号',
sname varchar(30) not null comment '学生名',
sage  tinyint unsigned not null default 18 comment '年龄',
ssex tinyint unsigned not null default 0 comment '性别'
);
insert student(sname) values
('tom'),
('alice'),
('tina');

create table course(
cid int unsigned primary key auto_increment comment '编号',
cname varchar(30) not null comment '课程名',
tid int unsigned  not null comment '教师编号'
);
insert course(cname,tid) values
('数学',1),
('语文',2),
('英语',3);

create table score(
id int unsigned primary key auto_increment comment '编号',
sid int unsigned  not null comment '学员编号',
cid int unsigned  not null comment '课程编号',
score int unsigned not null default 0 comment '成绩',
foreign key(sid) references student(sid),
foreign key(cid) references course(cid)
);
insert score(sid,cid,score) values
(1,1,90),
(1,2,88),
(1,3,90),
(2,1,95),
(2,2,91),
(2,3,98),
(3,1,75),
(3,2,81),
(3,3,78);

create table teacher(
tid int unsigned primary key auto_increment comment '编号',
tname varchar(30) not null comment '课程名'
);
insert teacher(tname) values
('张红'),
('刘丽'),
('陈浩');

1.查询“1”课程的所有学生的学号与分数；
SELECT sid,score from score where cid=1

2.查询平均成绩大于60分的同学的学号和平均成绩；
SELECT sid,avg(score) as avg
from score 
GROUP BY sid
HAVING avg>60
3.查询所有同学的学号、姓名、选课数、总成绩  取班级第一名总成绩
select student.sid,student.sname,count(cid),sum(score) as 总成绩
from student 
INNER JOIN score on  student.sid=score.sid
GROUP BY student.sid

ORDER BY  总成绩 DESC
limit 0,1
4.查询姓“李”的老师的个数；
SELECT count(*)
from teacher 
WHERE tname like '李%'
5.查询学过“张红”老师课的同学的学号、姓名
SELECT student.sid,student.sname,teacher.tname
from score
INNER JOIN teacher on score.cid=teacher.tid
INNER JOIN student on student.sid=score.sid
where tname='张红'
GROUP BY student.sid
6.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
第一种 SELECT course.cid 课程ID,max(score) 最高分,min(score) 最低分
from course 
INNER JOIN  score on score.cid=course.cid
GROUP BY course.cid
第二种 SELECT cid ,max(score),min(score) from score  GROUP BY cid