《数据库技术与应用》实验报告

给定如表1、表2和表3所示的学生信息。

表1  学生表student

学号	姓名	性别	专业班级	出生日期	联系电话
0433	张艳	女	生物04	1986-9-13
0496	李越	男	电子04	1984-2-23	1381290××××
0529	赵欣	男	会计05	1984-1-27	1350222××××
0531	张志国	男	生物05	1986-9-10	1331256××××
0538	于兰兰	女	生物05	1984-2-20	1331200××××
0591	王丽丽	女	电子05	1984-3-20	1332080××××
0592	王海强	男	电子05	1986-11-1

表2  课程表course

课程号	课程名	学分数	学时数	任课教师
K001	计算机图形学	2.5	40	胡晶晶
K002	计算机应用基础	3	48	任泉
K006	数据结构	4	64	马跃先
M001	政治经济学	4	64	孔繁新
S001	高等数学	3	48	赵晓尘

表3  学生作业表score

课程号	学号	作业1成绩	作业2成绩	作业3成绩
K001	0433	60	75	75
K001	0529	70	70	60
K001	0531	70	80	80
K001	0591	80	90	90
K002	0496	80	80	90
K002	0529	70	70	85
K002	0531	80	80	80
K002	0538	65	75	85
K002	0592	75	85	85
K006	0531	80	80	90
K006	0591	80	80	80
M001	0496	70	70	80
M001	0591	65	75	75
S001	0531	80	80	80
S001	0538	60		80

一、对表1，表2和表3，分别以下表的方式给出各字段的属性定义和说明。

字段名	数据类型	长度或者精度	默认值	完整性约束
表1	……	……		……
学号	Char(9)	9字节	sno	Primary key
姓名	Char(20)	20字节	sname	unique
性别	Char(2)	2字节	ssex
专业班级	Char(20)	20字节	sclass
出生日期	date		sbirth
联系电话	Char(11)	11字节	tell
表2	……	……		……
课程号	Char(9)	9字节	cno
课程名	Char(20)	20字节	cname	Primary key
学分数	Numeric(2.1)	小数点一位	ccredit	unique
学时数	smallint	2字节	chour
任课教师	Char(20)	20字节	cteacher
表3	……	……		……
课程号	Char(9)	9字节	cno	Primary key,foreign key
学号	Char(9)	9字节	sno	Primary key,foreign key
作业1成绩	int	4字节	cj1
作业2成绩	int	4字节	cj2
作业3成绩	int	4字节	cj3

• 使用SQL命令在学生作业管理数据库中建立学生表、课程表和学生作业表，在实验报告中给出SQL代码。

Create table student (sno char(9) primary key,sname char(20) unique,ssex char(2),sclass char(20),sbirth date,tell char(11));

Create table course (cno char (9) primary key,cname char(20) unique,ccredit numeric(2.1),chour smallint,cteacher char(20));

Create table score (cno char(9),sno char(9),cj1 int,cj2 int,cj3 int, primary key(cno,sno),foreign key(sno) references student(sno,foreign key(cno) references course(cno));

三、在各个表中输入表1、表2和表3中的相应内容。

四、根据第一部分实验中创建的学生作业管理数据库以及其中的学生表、课程表和学生作业表，进行以下的查询操作（每一个查询都要给出SQL语句，列出查询结果）。

1．查询各位学生的学号、班级和姓名。

Select sno,sclass,sname from student;

1. 查询课程的全部信息。

Select * from course;

1. 查询数据库中有哪些专业班级。

Select distinct sclass from student;

1. 查询学时数大于60的课程信息。

Select * from course where chour>60;

1. 查询在1986年出生的学生的学号、姓名和出生日期。

Select sno,sname,sbirth from student where sbirth>=‘1986-1-1’and sbirth<‘1987-1-1’;

1. 查询三次作业的成绩都在80分以上的学号、课程号.

Select score.sno,score.cno from score where cj1>80 and cj2>80 and cj3>80;

1. 查询姓张的学生的学号、姓名和专业班级。

Select sno,sname,sclass from student where sname like ‘张%’;

1. 查询05级的男生信息。

Select * from student where sclass like ‘%05’and ssex=‘男’;

1. 查询没有作业成绩的学号和课程号。

Select sno,cno from score where cj1 is null or cj2 is null or cj3 is null;

1. 查询学号为0538的学生的作业1总分。

Select sum(cj1) as 学号为0538的学生的作业1总分 from score where sno=0538;

1. 查询选修了K001课程的学生人数。

Select count(sno) as 选修K001课程的学生人数 from score where cno=’K001’;

1. 查询数据库中共有多少个班级。

Select count(distinct sclass) as 数据库中共有多少个班级 from student;

1. 查询选修三门以上（含三门）课程的学生的学号和作业1平均分、作业2平均分和作业3平均分。

Select sno,avg(cj1) as ‘作业1平均分’,avg(cj2) as ‘作业2平均分’,avg(cj3) as ‘作业3平均分’ from score where sno in (select sno from score group by sno having (count(*)>2)) group by sno;

1. 查询于兰兰的选课信息，列出学号、姓名、课程名（使用两种连接查询的方式）。

Select student.sno,sname,cno from student,course,score where student.sno=score.sno and course.cno=score.cno and sname in (‘于兰兰’);

五、根据第一部分实验中创建的学生作业管理数据库以及其中的学生表、课程表和学生作业表，进行以下操作。

1．使用查询语句完成以下任务（每一个查询都要给出SQL语句，并且列出查询结果）。

（1）查询与“张志国”同一班级的学生信息（使用连接查询和子查询方式）。

子查询：select * from student where sclass=(select sclass from student where sname=’张志国’);

1. 查询比“计算机应用基础”学时多的课程信息（使用连接查询和子查询方式）。

子查询：select * from course where chour >(select chour from course where cname=’计算机应用基础’);

1. 查询选修课程号为K002的学生的学号、姓名（使用连接查询、普通子查询、相关子查询、使用exists关键字的相关子查询）。

连接查询：select student.sno,student.sname from score,student where score.cno=’K002’ and score.sno=student.sno;

普通子查询：select distinct student.sno,student.sname from score,student where student.sno in(select student.sno from score where score.cno=’K002’and score.sno=student.sno);

相关子查询：select distinct student.sno,student.sname from student,score where student.sno in (select score.sno from score where score.cno=’K002’);

使用exists关键字的相关子查询：select distinct student.sno,student.sname from score,student where exists(select * from score where score.cno=’K002’ and score.sno=student.sno);

1. 查询没有选修K001和M001课程的学号、课程号和三次成绩（使用子查询）。

Select distinct sno,cno,cj1,cj2,cj3 from score where sno not in (select sno from score where cno=’K001’ or cno=’M001’);

2．使用数据操纵完成以下任务（每一个任务都要给出SQL语句，并且列出查询结果）。

（1）在学生表中添加一条学生记录，其中，学号为0593，姓名为张乐，性别为男，专业班级为电子05。

Insert into student(sno,sname,ssex,sclass) values(‘0593’,‘张乐’，‘男’，‘电子05’);

（2）将所有课程的学分数变为原来的两倍。

Update course set credit=credit*2;

（3）删除张乐的信息。

Delete from student where sname=’张乐’;

六、根据第一部分实验中创建的学生作业管理数据库以及其中的学生表、课程表和学生作业表，进行以下操作。

1．创建一个电子05的学生视图（包括学号、姓名、性别、专业班级、出生日期）。

Create view dz05 as select sno,sname,ssex,sclass,sbirth from student where sclass=’电子05’;

1. 创建一个生物05的学生作业情况视图（包括学号、姓名、课程名、作业1成绩、作业2成绩、作业3成绩）。

Create view sw05 as select student.sno,sname,cname,cj1,cj2,cj3 from student,course,score where student.sno=score.sno and course.cno=score.cno and sclass=’生物05’;

1. 创建一个学生作业平均成绩视图（包括学号、作业1平均成绩、作业2平均成绩、作业3平均成绩）。

Create view avgzy as select sno,avg(cj1),avg(cj2),avg(cj3) from score group by sno;

1. 修改第2题中生物05的学生作业情况视图，将作业2成绩和作业3成绩去掉。

Alter view sw05 as select student.sno,sname,cname,cj1 from student,course,score where student.sno=course.sno and course.cno=student.cno and sclass=’生物05’;

5．向电子05的学生视图中添加一条记录，其中学号为0596，姓名为赵亦，性别为男，专业班级为电子05，出生日期为1986-6-8（除了电子05的学生视图发生变化之外，看看学生表中发生了什么变化？）。

Create view DZ05(sno,sname,sex,class,sdate)

as select sno,sname,sex,class,sdate from student where class=’ 电子05’

select * from DZ05

Insert into Dz05(sno,sname,sex,class,sdate) values(‘0596’，’赵亦’，’男’，’电子05’， 1986-6-8)

学生表变化：学生表新增了赵亦的一条记录

6．将电子05的学生视图中赵亦的性别改为“女”（除了电子05的学生视图发生变化之外，看看学生表中发生了什么变化？）。

Update Dz05 set sex=’女’ where sname=’ 赵亦’

学生表变化：学生表中赵亦性别变为女

7．删除电子05的学生视图中赵亦的记录。

Delete from Dz05 where sname=’ 赵亦’

8．删除电子05的学生视图（给出SQL语句即可）。

Drop view Dz05