数据库原理及安全技术教学实验报告SQL实践（一）
目录
1.在SQL Server中使用对象资源管理器和SQL命令创建学生作业管理数据库，数据库的名称自定。
2.对表3.6，表3.7和表3.8，分别以下表的方式给出各字段的属性定义和说明。
3.使用SQL命令在学生作业管理数据库中建立学生表、课程表和学生作业表，在实验报告中给出SQL代码。
4.在各个表中输入表3.6、表3.7和表3.8中的相应内容。
5..进行以下的查询操作（每一个查询都要给出SQL语句，列出查询结果）。
1.在SQL Server中使用对象资源管理器和SQL命令创建学生作业管理数据库，数据库的名称自定。
2.对表3.6，表3.7和表3.8，分别以下表的方式给出各字段的属性定义和说明。
3.使用SQL命令在学生作业管理数据库中建立学生表、课程表和学生作业表，在实验报告中给出SQL代码。
4.在各个表中输入表3.6、表3.7和表3.8中的相应内容。
5.进行以下的查询操作（每一个查询都要给出SQL语句，列出查询结果）。
一、实验目的
1．掌握在SQL Server中使用对象资源管理器和SQL命令创建数据库与修改数据库的方法、创建数据表和修改数据表的方法。
2．掌握在SQL Server中使用对象资源管理器或者SQL命令创建数据表和修改数据表的方法（以SQL命令为重点）。
3．掌握无条件查询、条件查询、库函数及汇总查询、分组查询、查询的排序、连接查询的使用方法。
二、实验软硬件要求
1、SQL Server 2008
三、实验预习
建库、建表、表约束、数据插入、单表查询、表连接等SQL语句。
四、实验内容（实验步骤、测试数据等）
给定如表3.6、表3.7和表3.8所示的学生信息。
表3.6 学生表S
|
学号 |
姓名 |
性别 |
专业班级 |
出生日期 |
联系电话 |
|
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 |
|
表3.7 课程表C
|
课程号 |
课程名 |
学分数 |
学时数 |
任课教师 |
|
K001 |
计算机图形学 |
2.5 |
40 |
胡晶晶 |
|
K002 |
计算机应用基础 |
3 |
48 |
任泉 |
|
K006 |
数据结构 |
4 |
64 |
马跃先 |
|
M001 |
政治经济学 |
4 |
64 |
孔繁新 |
|
S001 |
高等数学 |
3 |
48 |
赵晓尘 |
表3.8 学生作业表W
|
课程号 |
学号 |
作业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 |
续表
|
课程号 |
学号 |
作业1成绩 |
作业2成绩 |
作业3成绩 |
|
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．在SQL Server中使用对象资源管理器和SQL命令创建学生作业管理数据库，数据库的名称自定。
（1）使用对象资源管理器创建数据库，请给出重要步骤的截图。
（2）删除第（1）步创建的数据库，再次使用SQL命令创建数据库，请给出SQL代码。
（3）创建数据库之后，如果有需要，可以修改数据库。
2．对表3.6，表3.7和表3.8，分别以下表的方式给出各字段的属性定义和说明。
|
字段名 |
数据类型 |
长度或者精度 |
默认值 |
完整性约束 |
|
…… |
…… |
…… |
|
…… |
|
…… |
…… |
…… |
|
…… |
3．使用SQL命令在学生作业管理数据库中建立学生表、课程表和学生作业表，在实验报告中给出SQL代码。
4．在各个表中输入表3.6、表3.7和表3.8中的相应内容。
5. 进行以下的查询操作（每一个查询都要给出SQL语句，列出查询结果）。
（1）查询各位学生的学号、班级和姓名。
（2）查询课程的全部信息。
（3）查询数据库中有哪些专业班级。
（4）查询学时数大于60的课程信息。
（5）查询在1986年出生的学生的学号、姓名和出生日期。
（6）查询三次作业的成绩都在80分以上的学号、课程号。
（7）查询姓张的学生的学号、姓名和专业班级。
（8）查询05级的男生信息。
（9）查询没有作业成绩的学号和课程号。
（10）查询学号为0538的学生的作业1总分。
（11）查询选修了K001课程的学生人数。
（12）查询数据库中共有多少个班级。
（13）查询选修三门以上（含三门）课程的学生的学号和作业1平均分、作业2平均分和作业3平均分。
（14）查询于兰兰的选课信息，列出学号、姓名、课程名（使用两种连接查询的方式）。
运行结果：
1.在SQL Server中使用对象资源管理器和SQL命令创建学生作业管理数据库，数据库的名称自定。
(1)使用对象资源管理器创建数据库，请给出重要步骤的截图。
(2)删除第（1）步创建的数据库，再次使用SQL命令创建数据库，请给出SQL代码。
create database 学生作业管理数据库;
2.对表3.6，表3.7和表3.8，分别以下表的方式给出各字段的属性定义和说明。
表3.6
|
字段名 |
数据类型 |
长度或者精度 |
默认值 |
完整性约束 |
|
学号 |
Char(9) |
9字节 |
|
Primary key |
|
姓名 |
Char(20) |
20字节 |
|
unique |
|
性别 |
Char(2) |
20字节 |
|
|
|
专业班级 |
Char(20) |
20字节 |
|
|
|
出生日期 |
date |
|
|
|
|
联系电话 |
Char(11) |
11字节 |
|
|
表3.7
|
字段名 |
数据类型 |
长度或者精度 |
默认值 |
完整性约束 |
|
课程号 |
Char(9) |
9字节 |
|
Primary key |
|
课程名 |
Char(20) |
20字节 |
|
unique |
|
学分数 |
Numeric(2.1) |
小数点一位 |
|
|
|
学时数 |
smallint |
2字节 |
|
|
|
任课教师 |
Char(20) |
20字节 |
|
|
表3.8
|
字段名 |
数据类型 |
长度或者精度 |
默认值 |
完整性约束 |
|
课程号 |
Char(9) |
9字节 |
|
Primary key，foreign key |
|
学号 |
Char(9) |
9字节 |
|
Primary key，foreign key |
|
作业1成绩 |
Int |
4字节 |
|
|
|
作业2成绩 |
Int |
4字节 |
|
|
|
作业3成绩 |
Int |
4字节 |
|
|
3．使用SQL命令在学生作业管理数据库中建立学生表、课程表和学生作业表，在实验报告中给出SQL代码。
create table S
(Sno char(9) primary key,
Sname char(20) unique,
Ssex char(2),
Sclass char(20),
Sbirth date,
tell char(11)
);
create table C
(Cno char(9) primary key,
Cname char(20) unique,
Ccredit numeric(2,1),
Chour smallint,
Cteacher char(20)
);
create table W
(Cno char(9) ,
Sno char(9) ,
Wn1 int,
Wn2 int,
Wn3 int,
primary key(Cno,Sno),
foreign key(Sno) references S(Sno),
foreign key(Cno) references C(Cno)
);
4．在各个表中输入表3.6、表3.7和表3.8中的相应内容。
5. 进行以下的查询操作（每一个查询都要给出SQL语句，列出查询结果）。
（1）查询各位学生的学号、班级和姓名。
select Sno,Sclass,Sname
from S;
（2）查询课程的全部信息。
select *
from C;
（3）查询数据库中有哪些专业班级。
select distinct Sclass
from S;
（4）查询学时数大于60的课程信息。
select *
from C
where Chour>60;
（5）查询在1986年出生的学生的学号、姓名和出生日期。
select Sno,Sname,Sbirth
from S
where Sbirth>='1986-1-1'
and Sbirth<'1987-1-1';
或者
select Sno,Sname,Sbirth
from S
where Sbirth like '%1986%';
来自2023年1月数据库培训记录，g，我还再学数据库O-O
% 表示任意字符出现任意次数。（这里的任意当然包括 0 、1、无数）
_ 只匹配单个字符。
（6）查询三次作业的成绩都在80分以上的学号、课程号。
select W.Sno,W.Cno
from W
where Wn1>80
and Wn2>80
and Wn3>80;
（7）查询姓张的学生的学号、姓名和专业班级。
select Sno,Sname,Sclass
from S
where Sname like'张%';
（8）查询05级的男生信息。
select *
from S
where Sclass like'%05'
and Ssex='男';
（9）查询没有作业成绩的学号和课程号。
select Sno,Cno
from W
where Wn1 is NULL
or Wn2 is NULL
or Wn3 is NULL;
（10）查询学号为0538的学生的作业1总分。
select Sum(Wn1) as 总分数
from W
where Sno=0538;
（11）查询选修了K001课程的学生人数。
select count(Sno) as 选修K001课程的学生人数
from W
where Cno ='K001';
（12）查询数据库中共有多少个班级。
select count(distinct Sclass) as 班级数
from S;
（13）查询选修三门以上（含三门）课程的学生的学号和作业1平均分、作业2平均分和作业3平均分。
select Sno,AVG(Wn1)as Wn1平均分,AVG(Wn2)as Wn2平均分,AVG(Wn3)as Wn3平均分
FROM W
GROUP BY Sno/*按学生分组 */
Having COUNT(Cno)>=3
（14）查询于兰兰的选课信息，列出学号、姓名、课程名（使用两种连接查询的方式）。
select S.Sno,S.Sname,C.Cname
FROM W
inner join S
on S.Sno=W.Sno
inner join C
on C.Cno=W.Cno
where S.Sname='于兰兰';
select S.Sno,S.Sname,C.Cno
from W,S,C
where S.Sno=W.Sno
and W.Cno=C.Cno
and S.Sname='于兰兰';
五、实验体会
操作不够熟练，需要加强练习。