数据库实验（学生选课系统）

数据库实验（学生选课系统）

文章目录

• • • 数据库实验（学生选课系统）
    • • 一、使用管理工具创建数据库
      • 二、使用T-SQL创建数据库
      • 三、SQL语句
      • • 1.简单查询（一）
        • 2、简单查询 （二）
        • 3.连接查询
        • 4.嵌套查询
        • 5.组合查询和统计查询
      • 四、代码：

一、使用管理工具创建数据库

1）使用 SQL Server Management Studio 建立学生选课数据库（XSXK）。

2）在 SQL Server Management Studio 中查看学生选课数据库的属性。

3）根据学生选课数据库物理结构设计，定义基本表、索引，实现相应的约束条件。

二、使用T-SQL创建数据库

（1） student(学生表)

主键：sno

索引：sname(升序)

check 约束：年龄大于 18, 性别的取值只能为男或女

default 约束：性别默认为男

更改学生表的结构，取消姓名不允许为空的约束

（2） Course （课程表）

主键：cno

索引： cno (升序)+ccredit(降序)

更改课程表的结构，增加属性列教师 ctech（类型是 char ，长度 20）

（3） SC（选课表）

主键：sno+cno

（4） 各个表的参照完整性约束

FK_SC_ student

主 键 表 ：student 外 键 表 ：SC 主 键 ：sno 外 键 ：sno

FK_SC_ course

主 键 表 ：course 外 键 表 ：SC 主 键 ：cno 外 键 ：cno

3、SQL Server Management Studio 管理工具，完成图书借阅数据库的录入、数据修改或删除操作。

1） 要求各表录入 10 条以上记录。

2） 修改某位读者的还书日期。

3） 删除指定读者的借还记录。

三、SQL语句

1.简单查询（一）

（1） 对于每个学生，求学生的选课门数和平均成绩，并把结果存入学生选课数据库中。

（2） 将数据库原理与应用课程的学分修改为 4.

（3） 将姓名为“王华”的学生选修数据库原理及应用课程的成绩增加 5 分。

（4） 删除选课表中成绩低于 40 分的记录。

（5） 删除学号为 140001 的学生记录，并讨论该删除操作所受到的约束。

（6） 删除所有选修课程“JAVA”的选课记录。

（7） 将管理学院全体学生的成绩置为空。

（8） 删除学生李萍的所有选课记录。

2、简单查询 （二）

在学生选课数据库中实现其数据查询操作。

（1）查询数计学院学生的学号和姓名。

（2）查询选修了课程的学生学号。

（3） 查询选修 C1 课程的学生学号和成绩,结果按成绩降序排列,如果成绩相同按学号升序排序。

（4）查询选修 C1 课程，成绩在 80~90 之间的学生学号和成绩,并将成绩乘以 0.8 输出。

（5）查询生工学院或数计学院系姓张的学生的信息。

（6）查询缺少了成绩的学生的学号和课程号。

3.连接查询

在学生选课库中实现其数据连接查询操作。

(1)查询学生的学号、姓名、选修的课程名称及成绩。

(2)查询数计学院学生选修的课程学分大于 2 的课程详细信息。

(3) 查询所有学生的信息以及他(她)所选课的学生学号和成绩（要求查询结果也显示出没有选修课程的学生信息）。

(4)查询选修课程号为 C1 且成绩在 90 分以上的学生学号、姓名及成绩。

(5) 查询每一门课的间接先行课（即先行课的先行课）。言的查询语句的理解。

4.嵌套查询

(1)查询选修了’c语言’课程的学生学号和姓名

(2)查询比唐伯虎年龄大的学生学号和姓名

(3)查询数据结构课程的成绩低于唐伯虎的学生学号和成绩

(4)查询其他学院中比数计学院学生年龄都小的学生

(5)查询选修了操作系统课程的学生姓名

(6)查询没有选修操作系统课程的学生姓名

(7)查询选修了全部课程的学生姓名

(8)查询至少选修了学生为张飞的学生所选修的全部课程的学生学号和姓名

(9)查询既选修了数据库原理又选修了操作系统课程的学生姓名

(10)查询既选修了数据库原理或选修了操作系统课程的学生学号

(11)查询既选修了数据库原理而没有选修操作系统课程的学生学号

(12)查询全是男同学选修的课程号

5.组合查询和统计查询

(1)使用集合运算查询既选修了数据结构课程又选修了数据库原理课程的学生姓名。
(2)使用集合运算查询选修了数据结构课程或选修了数据库原理课程的学生学号。

(3)使用集合运算查询选修了数据结构课程而没有选修了数据库原理课程的学生

(4)统计选修了课程的学生人数。

(5)查询选修成绩合格的课程超过4门以上学生的学生学号、总成绩。

(6)统计各院系的学生人数。

(7)统计各年龄的学生人数。

(8)统计每个学生的选修课程数目和平均成绩。

(9)查询每门课程的详细信息及选课人数。

四、代码：

create table student(sno char(10) not null comment '学号',sname char(8)not null comment '姓名',ssexchar(2) default ' 男' not null check ( ssex = '男' or ssex = '女') comment '性别',sdept char(30) null comment '所在院系',sagesmallint null check ( sage > 18 ) comment '年龄',stelchar(13) null comment '联系电话',constraint student_pkprimary key (sno))comment '学生';create index student_sname_indexon student (sname);create table Course(cno char(10) not null comment '课程编号',cname char(16) not null comment '课程名称',ccredit smallint not null comment '学分',cpnochar(10) null comment '先行课',constraint Course_pkprimary key (cno))comment '课程表';create index Course_cno_ccredit_indexon Course (cno asc, ccredit desc);create table SC(sno char(10) not null comment '学号',grade smallint null comment '成绩',cno char(10) not null comment '课程编号',constraint SC_pkprimary key (sno, cno))comment '选课';create index student_sname_indexon student (sname);create table Course(cno char(10) not null,cname char(16) not null,ccredit smallint not null,cpnochar(10) null,constraint Course_pkprimary key (cno));create index Course_cno_ccredit_index on Course (cno asc, ccredit desc);create table SC(sno char(10) not null,grade smallint null,cno char(10) not null,constraint SC_pkprimary key (sno, cno));alter table SCadd constraint FK_SC_student foreign key (sno) references student (sno);alter table SCadd constraint FK_SC_Course foreign key (cno) references Course (cno);insert into studentvalues ('2001124001', '张飞', '男', '管理学院', 23, '1464312');insert into studentvalues ('2001124002', '郭旭', '男', '管理学院', 89, '1145114');insert into studentvalues ('2001124004', '周波', '男', '数计学院', 23, '4654654');insert into studentvalues ('2001124005', '张柏芝', '女', '数计学院', 30, '154666');insert into studentvalues ('2001124006', '风清扬', '男', '管理学院', 28, '14645463');insert into studentvalues ('2001124007', '唐伯虎', '男', '材料学院', 26, '1454643');insert into studentvalues ('2001124008', '于占鳌', '男', '电气学院', 25, '7842289');insert into studentvalues ('2001124009', '九儿', '女', '电气学院', 19, '774512');insert into studentvalues ('2001124010', '李茂贞', '女', '电气学院', 29, '6659873');insert into studentvalues ('2001124011', '紫霞', '女', '材料学院', 19, '4654562');INSERT INTO CourseVALUES ('001', 'java', '4', '003') , ('002', 'c语言', '4', '003') , ('003', '数据结构', '4', NULL) , ('004', '数据库原理', '3', '002') , ('005', '计算机组成原理', '2', NULL) , ('006', '操作系统', '2', '005') , ('007', '计算机网络原理', '3', NULL) , ('008', '高等数学', '5', NULL) , ('009', '大学英语一', '2', NULL) , ('010', '大学物理三', '3', NULL);insert into SCvalues ('2001124001', null, '001'), ('2001124001', 82, '002'), ('2001124001', 78, '003'), ('2001124001', 96, '004'), ('2001124001', 76, '005'), ('2001124001', 39, '006'), ('2001124001', 88, '007'), ('2001124001', null, '008'), ('2001124001', 82, '009'), ('2001124001', 69, '010');insert into SCvalues ('2001124002', 98, '001'), ('2001124002', 92, '002'), ('2001124002', 78, '003'), ('2001124002', 96, '004'), ('2001124002', null, '005'), ('2001124002', 88, '006'), ('2001124002', 88, '007'), ('2001124002', 81, '008'), ('2001124002', 82, '009'), ('2001124002', 79, '010');insert into SCvalues ('2001124005', 66, '001'), ('2001124005', 72, '002'), ('2001124005', 88, '003'), ('2001124005', 76, '004'), ('2001124005', 76, '005'), ('2001124005', 68, '006'), ('2001124005', 68, '007'), ('2001124005', 86, '008'), ('2001124005', 80, '009'), ('2001124005', null, '010');insert into SCvalues ('2001124004', 46, '001'), ('2001124004', null, '002'), ('2001124004', null, '003'), ('2001124004', 87, '004'), ('2001124004', null, '005'), ('2001124004', 58, '006'), ('2001124004', 68, '007'), ('2001124004', 61, '008'), ('2001124004', 72, '009'), ('2001124004', 59, '010');insert into SCvalues ('2001124006', 49, '001'), ('2001124006', 52, '002'), ('2001124006', 88, '003'), ('2001124006', 87, '004'), ('2001124006', 66, '005'), ('2001124006', 88, '006'), ('2001124006', 68, '007'), ('2001124006', 61, '008'), ('2001124006', 72, '009'), ('2001124006', null, '010');insert into SCvalues ('2001124007', 86, '001'), ('2001124007', 42, '002'), ('2001124007', 69, '003'), ('2001124007', 87, '004'), ('2001124007', 65, '005'), ('2001124007', null, '006'), ('2001124007', 58, '007'), ('2001124007', 61, '008'), ('2001124007', 72, '009'), ('2001124007', 69, '010');insert into SCvalues ('2001124008', 87, '001'), ('2001124008', 72, '002'), ('2001124008', 68, '003'), ('2001124008', 87, '004'), ('2001124008', 96, '005'), ('2001124008', 58, '006'), ('2001124008', 68, '007'), ('2001124008', 61, '008'), ('2001124008', 72, '009'), ('2001124008', 59, '010');insert into SCvalues ('2001124009', 86, '001'), ('2001124009', 92, '002'), ('2001124009', 68, '003'), ('2001124009', 87, '004'), ('2001124009', 66, '005'), ('2001124009', 58, '006'), ('2001124009', 68, '007'), ('2001124009', 61, '008'), ('2001124009', 72, '009'), ('2001124009', 79, '010');insert into SCvalues ('2001124010', 96, '001'), ('2001124010', null, '002'), ('2001124010', 88, '003'), ('2001124010', 87, '004'), ('2001124010', 86, '005'), ('2001124010', null, '006'), ('2001124010', null, '007'), ('2001124010', 79, '008'), ('2001124010', 72, '009'), ('2001124010', null, '010');insert into SCvalues ('2001124011', 86, '001'), ('2001124011', 82, '002'), ('2001124011', 58, '003'), ('2001124011', 87, '004'), ('2001124011', 66, '005'), ('2001124011', 98, '006'), ('2001124011', 78, '007'), ('2001124011', 81, '008'), ('2001124011', null, '009'), ('2001124011', 79, '010');alter table studentadd 平均成绩 double;alter table studentadd 选课门数 int;# 录入平均成绩update studentset 平均成绩=(select round(avg(grade), 1) from SC where sno = '2001124001')where sno = '2001124001';update studentset 平均成绩=(select round(avg(grade), 1) from SC where sno = '2001124002')where sno = '2001124002';update studentset 平均成绩=(select round(avg(grade), 1) from SC where sno = '2001124004')where sno = '2001124004';update studentset 平均成绩=(select round(avg(grade), 1) from SC where sno = '2001124005')where sno = '2001124005';update studentset 平均成绩=(select round(avg(grade), 1) from SC where sno = '2001124006')where sno = '2001124006';update studentset 平均成绩=(select round(avg(grade), 1) from SC where sno = '2001124007')where sno = '2001124007';update studentset 平均成绩=(select round(avg(grade), 1) from SC where sno = '2001124008')where sno = '2001124008';update studentset 平均成绩=(select round(avg(grade), 1) from SC where sno = '2001124009')where sno = '2001124009';update studentset 平均成绩=(select round(avg(grade), 1) from SC where sno = '2001124010')where sno = '2001124010';update studentset 平均成绩=(select round(avg(grade), 1) from SC where sno = '2001124011')where sno = '2001124011';# 查询一下所有的平均成绩可以对比一下是否录入正确select SC.sno, AVG(grade) as 平均成绩from SCgroup by SC.sno;# 录入选课门数update studentset 选课门数 = (select count(grade) from SC where SC.sno = '2001124001')where sno = '2001124001';update studentset 选课门数 = (select count(grade) from SC where SC.sno = '2001124002')where sno = '2001124002';update studentset 选课门数 = (select count(grade) from SC where SC.sno = '2001124004')where sno = '2001124004';update studentset 选课门数 = (select count(grade) from SC where SC.sno = '2001124005')where sno = '2001124005';update studentset 选课门数 = (select count(grade) from SC where SC.sno = '2001124006')where sno = '2001124006';update studentset 选课门数 = (select count(grade) from SC where SC.sno = '2001124007')where sno = '2001124007';update studentset 选课门数 = (select count(grade) from SC where SC.sno = '2001124008')where sno = '2001124008';update studentset 选课门数 = (select count(grade) from SC where SC.sno = '2001124009')where sno = '2001124009';update studentset 选课门数 = (select count(grade) from SC where SC.sno = '2001124010')where sno = '2001124010';update studentset 选课门数 = (select count(grade) from SC where SC.sno = '2001124011')where sno = '2001124011';# 默认情况下null值不参与计算，所以不需要过滤select count(grade)from SCwhere SC.sno = '2001124001';# 修改'数据库原理'的学分为4update courseset ccredit=4where cname = '数据库原理';# 将姓名为唐伯虎的学生选修数据库原理课程的成绩加5update scset grade =grade + 5where sno = (select student.sno from student where sname = '唐伯虎')and cno = (select cno from course where cname = '数据库原理');# 删除选课表中成绩低于40分的记录deletefrom scwhere grade  0;# 3.查询选修高等数学的课程的学生学号和成绩，结果按成绩降序排列，如果成绩相同按学号升序排列select sno as 学号, grade as 成绩from scwhere cno = (select cno from course where cname = '高等数学')order by grade desc, sno asc;# 4.查询选修高等数学课程，成绩在80~90之间的学生学号和成绩，并将成绩乘以0.8输出select sno as 学号, grade * 0.8 as 成绩from scwhere cno = (select cno from course where cname = '高等数学')and grade between 80 and 90;# 5.查询管理学院或数计学院姓张的学生的信息select *from studentwhere (sdept in ('管理学院', '数计学院'))and (sname like '张%');# 6.查询少了成绩的学生的学号和课程号select sno 学号, cno 课程号, gradefrom scwhere grade is null;# （二）# 1.查询学生的学号，姓名，选修的课程名称及成绩select distinct sc.sno学号,student.sname 姓名,Course.cname选修的课程,sc.grade成绩from sc left join student on sc.sno = student.sno left join course on sc.cno = Course.cno;# 2.查询数计学院学生选修的课程学分大于2的课程的详细信息select distinct Course.*from sc left join student on sc.sno = student.sno left join course on sc.cno = Course.cnowhere student.sdept = '数计学院'and Course.ccredit > 2;# 3.查询所有学生信息以及他所选课的课程号和成绩# (1)select distinct student.*,Course.*,sc.gradefrom sc left join student on sc.sno = student.sno left join course on sc.cno = Course.cno;# (2)要求查询结果也显示即使没有选课的学生的信息select distinct student.*,Course.*,sc.gradefrom student left join sc on sc.sno = student.sno left join course on sc.cno = Course.cno;# 4.查询选修课课程号为002且成绩在90分以上的学生学号，姓名和成绩select distinct sc.sno学号,student.sname 姓名,sc.grade成绩from sc left join student on sc.sno = student.sno left join course on sc.cno = Course.cnowhere SC.cno = 002and sc.grade > 90;# 5.查询每一门课的间接先行课select c4.*, c5.cpno 间接先行课from course c4 left join (select distinct c1.cno,c2.cpnofrom course c1, course c2, course c3where c1.cpno = c2.cnoand c2.cpno = c3.cno) c5 on c4.cno = c5.cno;# 实验三# 1.嵌套查询# (1)查询选修了'c语言'课程的学生学号和姓名select sc.sno, (select sname from student where sno = sc.sno) 姓名from scwhere sc.cno = (select course.cno from course where cname = 'c语言');# (2)查询比唐伯虎年龄大的学生学号和姓名select s.sno 学号, s.sname 姓名from student swhere s.sage > (select sage from student where sname = '唐伯虎');# (3)查询数据结构课程的成绩低于唐伯虎的学生学号和成绩select *from student swhere sno in (select SC.snofrom scwhere grade < (select grade from sc,student s,course c where sc.sno = s.sno and c.cno = sc.cno and s.sname = '唐伯虎' and c.cname = '数据结构'));# (4)查询其他学院中比数计学院学生年龄都小的学生select *from studentwhere sage = 60group by sc.snohaving count(sc.cno) >= 4;#(6)统计各院系的学生人数。select s.sdept, count(s.sdept) sumfrom student sgroup by s.sdept;#(7)统计各年龄的学生人数。select s.sage, count(s.sage) sumfrom student sgroup by s.sage;#(8)统计每个学生的选修课程数目和平均成绩。select sno, count(sc.sno) stusum, avg(sc.grade) avggradefrom scgroup by sno;#(9)查询每门课程的详细信息及选课人数。select Course.*, 选课人数from Course left join (select cno, count(*) 选课人数from SCgroup by cno) as a on a.cno = Course.cno;