多对多查询练习 MySQL

桃扇骨 2024-03-26 16:46 50阅读 0赞

\#一个查询练习

# 多对多
    /* 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