欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

day35作业

程序员文章站 2022-08-19 20:38:53
[toc] 一、 创建图中的表 二、完成下列需求 1. 查询所有大于60分的学生的姓名和学号 (DISTINCT: 去重) 2. 查询每个老师教授的课程数量 和 老师信息 3. 查询学生的信息以及学生所在的班级信息 4. 学生中男生的个数和女生的个数 5. 获取所有学习'生物'的学生的学号和成绩;姓 ......

目录

一、 创建图中的表

day35作业

# 创建班级表
create table class (
cid int unsigned auto_increment primary key,
caption varchar(255) not null default ''
)charset utf8;

insert into class (caption) values 
('三年六班'),('一年二班'),('三年十二班');

select * from class;


# 创建学生表,并外键关联班级表
create table student(
sid int unsigned auto_increment primary key,
sname varchar(255) not null default '',
gender enum ('男','女'),
class_id int unsigned not null default 0,
constraint fk_stu_class foreign key (class_id) references class(cid)
)charset utf8;

insert into student (sname,gender,class_id) values
('宋冬野','女',1),('*','男',1),('赵雷','男',2);

select * from student;

# 创建老师表
create table teacher(
tid int unsigned auto_increment primary key,
tname varchar(255) not null default ''
)charset utf8;

insert into teacher(tname) values('剥夺'),('苍空'),('饭岛');

select * from teacher;

#创建课程表,并关联老师表
create table course(
cid int unsigned auto_increment primary key,
cname varchar(255) not null default '',
teacher_id int unsigned not null default 0,
constraint fk_course_tea foreign key (teacher_id) 
references teacher(tid)
)charset utf8;

insert into course(cname,teacher_id) values
('生物',1),('体育',1),('物理',2);

select * from course;


# 创建成绩表,并关联学生表和课程表
create table score(
sid int unsigned auto_increment primary key,
student_id int unsigned not null default 0,
course_id int unsigned not null default 0,
number decimal(3,0) not null default 0,

constraint fk_score_stu foreign key (student_id) references student(sid),
constraint fk_score_course foreign key (course_id) references course(cid)
)charset utf8;


insert into score(student_id,course_id,number) values(1,1,60),(1,2,59),(2,2,100);

select * from score;

二、完成下列需求

  1. 查询所有大于60分的学生的姓名和学号 (distinct: 去重)

    select distinct student.sid,student.sname from score left join student 
    on score.student_id = student.sid where score.number > 60;
  2. 查询每个老师教授的课程数量 和 老师信息

    select tname,count(tname) as course_num from teacher right join course 
    on teacher_id = tid group by tname ;
  3. 查询学生的信息以及学生所在的班级信息

    select sname,caption from student left join class on cid = class_id;
  4. 学生中男生的个数和女生的个数

    select gender,count(gender) as number from student group by gender;
  5. 获取所有学习'生物'的学生的学号和成绩;姓名

    select student.sid,number,sname from student left join course on cid = class_id left join score on student_id = student.sid where course_id=1;
  6. 查询平均成绩大于60分的同学的学号和平均成绩;

    select student_id,avg(number) from score 
    group by student_id having avg(number) > 60;
  7. 查询姓“李”的老师的个数;

    select count(tname) as num from teacher where tname like'李%';
  8. 查询课程成绩小于60分的同学的学号、姓名;

    select student.sid,sname from score left join student 
    on student_id = student.sid where number < 60;
  9. 删除学习“叶平”老师课的sc表记录

    delete from score where course_id in 
    (select cid from course left join teacher on tid = teacher_id where tname = '剥夺');
  10. 查询各科成绩最高和最低的分:以如下形式显示:课程id,最高分,最低分;

    select course_id,max(number),min(number) from score group by course_id;
  11. 查询每门课程被选修的学生数

    select cname,count(cname) as student_num from course right join student 
    on class_id=cid group by cname;
  12. 查询姓“张”的学生名单;

    select sname from student where sname like'张%';
  13. 查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列

    select cname ,avg(number) from score left join course 
    on course_id = cid order by cname asc,cid desc;
  14. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩

    select student.sid,sname,avg(number) from score left join student 
    on student_id = student.sid  group by sname,student.sid having avg(number) > 85;
  15. 查询课程编号为3且课程成绩在80分以上的学生的学号和姓名;

    select student.sid,sname from score left join student 
    on student_id = student.sid where course_id=3 and number >80;
  16. 查询各个课程及相应的选修人数

    select cname,count(cname) from course right join student 
    on class_id = cid group by cname;
  17. 查询“4”课程分数小于60,按分数降序排列的同学学号

    select student.sid from score join student 
    on student_id = student.sid  where number <60 order by student.sid desc ;
  18. 删除学号为“2”的同学的“1”课程的成绩

    delete number from score where student_id=2 and course_id = 1;