SQL基础五（作业代码）

最新推荐文章于 2025-04-02 23:22:23 发布

转载最新推荐文章于 2025-04-02 23:22:23 发布 · 106 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/tcheng/p/6076342.html

文章标签：

#数据库

本文档详细介绍了如何使用SQL创建学生选课系统的数据库结构，包括学生表、课程表及成绩表，并通过具体实例展示了数据的插入过程。此外，还提供了多种查询方式，如查询学生各科目的最高分、不及格的成绩等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create database stuinfo
create table student
(
   mid char(10) not null primary key,
   mname char(50) not null
)
create table  course
(
  fid char(10) not null primary key,
  fname char(50) not null
)
create table score
(
   sid int identity(1,1) primary key,
   fid char(10) not null,
   mid char(10) not null,
   score int,
   foreign key(fid) references course(fid),
   foreign key(mid) references student(mid)
)
INSERT INTO course(FID,FName)VALUES('F001','语文') 
INSERT INTO course(FID,FName)VALUES('F002','数学') 
INSERT INTO course(FID,FName)VALUES('F003','英语') 
INSERT INTO course(FID,FName)VALUES('F004','历史') 
--学生表中插入数据-- 
INSERT INTO student(MID,MName)VALUES('M001','张萨') 
INSERT INTO student(MID,MName)VALUES('M002','王强') 
INSERT INTO student(MID,MName)VALUES('M003','李三') 
INSERT INTO student(MID,MName)VALUES('M004','李四') 
INSERT INTO student(MID,MName)VALUES('M005','阳阳') 
INSERT INTO student(MID,MName)VALUES('M006','虎子') 
iNSERT INTO student(MID,MName)VALUES('M007','夏雪') 
INSERT INTO student(MID,MName)VALUES('M008','璐璐') 
INSERT INTO student(MID,MName)VALUES('M009','珊珊') 
INSERT INTO student(MID,MName)VALUES('M010','香奈儿')

INSERT INTO Score(FID,MID,Score)VALUES('F001','M001',78) 
INSERT INTO Score(FID,MID,Score)VALUES('F002','M001',67) 
INSERT INTO Score(FID,MID,Score)VALUES('F003','M001',89) 
INSERT INTO Score(FID,MID,Score)VALUES('F004','M001',76) 
INSERT INTO Score(FID,MID,Score)VALUES('F001','M002',89) 
INSERT INTO Score(FID,MID,Score)VALUES('F002','M002',67) 
INSERT INTO Score(FID,MID,Score)VALUES('F003','M002',84) 
INSERT INTO Score(FID,MID,Score)VALUES('F004','M002',96) 
INSERT INTO Score(FID,MID,Score)VALUES('F001','M003',70) 
INSERT INTO Score(FID,MID,Score)VALUES('F002','M003',87) 
INSERT INTO Score(FID,MID,Score)VALUES('F003','M003',92) 
INSERT INTO Score(FID,MID,Score)VALUES('F004','M003',56) 
INSERT INTO Score(FID,MID,Score)VALUES('F001','M004',80) 
INSERT INTO Score(FID,MID,Score)VALUES('F002','M004',78) 
INSERT INTO Score(FID,MID,Score)VALUES('F003','M004',97) 
INSERT INTO Score(FID,MID,Score)VALUES('F004','M004',66) 
INSERT INTO Score(FID,MID,Score)VALUES('F001','M006',88) 
INSERT INTO Score(FID,MID,Score)VALUES('F002','M006',55)
INSERT INTO Score(FID,MID,Score)VALUES('F003','M006',86) 
INSERT INTO Score(FID,MID,Score)VALUES('F004','M006',79) 
INSERT INTO Score(FID,MID,Score)VALUES('F002','M007',77) 
INSERT INTO Score(FID,MID,Score)VALUES('F003','M008',65) 
INSERT INTO Score(FID,MID,Score)VALUES('F004','M007',48) 
INSERT INTO Score(FID,MID,Score)VALUES('F004','M009',75) 
INSERT INTO Score(FID,MID,Score)VALUES('F002','M009',88)   
select * from score
select mname,语文=
             max(case
               when course.fname='语文' then score.score
             end)
            ,数学=
             max(case
               when course.fname='数学' then score
             end)
            ,英语=max(case
               when course.fname='英语' then (score)
             end)
            ,历史=max(case
               when course.fname='历史' then (score)
             end)
from student,score,course 
where student.mid=score.mid and score.fid=course.fid
group by mname

select   姓名=mname,课程=fname,成绩=score  from student,course,score where score<70 and student.mid=score.mid and score.fid=course.fid
select 姓名=(select mname from student where mid=score.mid ),
       课程=(select fname from course where fid=score.fid),
       成绩=score
from score where score<70
--select * from score where score<70

select 姓名=(select mname from student where mid=score.mid),平均分=avg(score)  from score group by mid order by 平均分 desc


select distinct mid from score 
select mid from student