2023年计科专升本-数据库作业.doc

实验报告

学 科： 计算机科学与技术
课程名称： 数据库原理
姓 名： 王 辉
学 号：

信息技术学院
实验二：管理SQL Server 2023数据库

一、实验目的
1. 了解数据库常用对象及组成；
2. 熟悉SQL的基本概念和特点；
3. 纯熟掌握T-SQL 语句中的数据定义的使用方法；
4. 熟悉在SQL Server 2023中创建及编辑数据库；
5. 纯熟掌握SQL的数据定义子句的使用方法。
二、实验环境
已安装SQL Server 2023公司版的计算机（13台）；
具有局域网网络环境，有固定ip地址；
三、实验学时
2学时
四、实验规定
1. 纯熟使用Management Stuio界面方式创建及编辑数据库；
2. 纯熟使用T-SQL 语句创建及编辑数据库；
3. 完毕实验报告。
五、实验准备知识（也可参考SQL Server联机丛书）
1. 数据库相关属性
1) 逻辑名称：数据文献和日记文献名称
2) 文献类型：数据文献及日记文献
3) 文献组：各个数据文献所属的文献组名称
4) 初始大小：文献的最原始大小
5) 自动增长：文献按指定的大小增长，也可限制到最大容量。
6) 途径：存放文献的物理目录
7) 文献名：显示数据文献和日记文献的物理名称
2. 默认数据库属性
1) 主数据文献为xx.mdf ，日记文献为xx_log.ldf；
2) 只有一个文献组Primary；
3) 主数据文献初始大小为3MB，增量为1MB；
4) 日记文献初始大小为1MB，增量为10%；
5) 主数据文献和日记文献都存放在C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA目录下，文献分别为xx.mdf 和xx_log.ldf 。
3. 使用界面创建数据库环节
【SQL Server Management Studio】→【对象资源管理器】→【数据库】→【新建数据库】→设立数据库相关属性（名称、大小、位置）
4. 启动SQL Server 2023查询分析器
【SQL Server Management Studio】→【新建查询】
5. 命令方式下创建数据库
CREATE DATABASE database_name /*指定数据库名*/
[ON 子句 ] /*指定数据库文献和文献组属性
[LOG ON 子句 ] /*指定日记文献属性*/
6. 命令方式修改数据库
Alter database database_name
{add file [,…n] [to filegroup filegroupname]
|add log file [,…n]
|remove file logical_file_name [with delete]
|modify file
六、实验内容及环节
1、假设SQL Server服务已启动，并以Administrator身份登录计算机（文献名称自定）；请分别使用Management界面方式和T-SQL语句实现以下操作：
1) 规定在本地磁盘D创建一个学生-课程数据库（名称为student），只有一个数据文献和日记文献，文献名称分别为stu和stu_log，物理名称为stu_data.mdf 和stu_log.ldf，初始大小都为3MB，增长方式分别为10%和1MB，数据文献最大为800MB，日记文献大小不受限制。
create database student
on primary(
name = stu,
filename = 'd:\stu.mdf',
size = 3,
maxsize = 500,
filegrowth =10%)
log on(
name = stu_log,
filename = 'd:\stu_log.ldf',
size = 3,
maxsize = unlimited,
filegrowth =1)
2) 创建一个Company数据库，该数据库的主数据文献逻辑名称为Company_data，物理文献为Company.mdf，初始大小为10MB，最大尺寸为无限大，增长速度为10%；数据库的日记文献逻辑名称为Company_log，物理文献名为Company.ldf，初始大小为1MB，最大尺寸为80MB，增长速度为1MB。
create database company
on primary(
name = company,
filename = 'd:\company.mdf',
size = 10,
maxsize = unlimited,
filegrowth =10%)
log on(
name = company_log,
filename = 'd:\company_log.ldf',
size = 1,
maxsize = 50,
filegrowth =1)
3) 创建数据库DB，具有2个数据文献，文献逻辑名分别为DB_data1和DB_data2，文献初始大小均为8MB，最大为100MB，按10%增长；只有一个日记文献，初始大小为3MB，按10%增长；所有文献都存储在D盘文献夹ceshi中。
create database DB
on primary(
name = DB_data1,
filename = 'd:\ceishi\DB_data1.mdf',
size = 5,
maxsize = 100,
filegrowth =10%),(
name = DB_data2,
filename = 'd:\ceishi\DB_data2.ndf',
size = 5,
maxsize = 100,
filegrowth =10%)
log on(
name = company_log,
filename = 'd:\ceishi\DB_log.ldf',
size = 3,
filegrowth =10%)
4) 在数据库student中增长数据文献db2，初始大小为10MB，最大大小为80 MB，按10%增长；
use student
alter database student
add file(
name = db2,
filename = 'd:\DB2.ndf',
size = 5,
maxsize = 50,
filegrowth =10%)

5) 在数据库student中添加日记文献，保存在D盘中，初始大小为1MB，最大无限制，增长方式按照1MB增长；
use student
alter database student
add log file
name = stulog2,
filename = 'd:\stulog2_log.ldf',
size = 1,
maxsize = unlimited,
filegrowth =1)
6) 修改数据库student主数据文献的大小，将主数据文献的初始大小修改为10Mb，增长方式为20%；
use student
alter database student
modify file(
name = stu,
size = 10,
filegrowth =20%)
7) 修改数据库student辅助数据文献初始大小为3MB，最大为100MB，按照10%增长，名称为db;
use student
alter database student
modify file(
name = db2,
newname=db,
size = 10,
maxsize =100,
filegrowth =10%)
8) 删除数据库student辅助数据文献和第二个日记文献；
use student
alter database student
remove file db
alter database student
remove file stulog2
9) 删除数据库company和DB。
drop database company
drop database DB
2、将数据库student的所有物理文献移动到‘D:\xxx‘,并且保证逻辑数据库不受影响，请具体描述解决办法。
七、出现问题及解决办法
如某些操作无法执行，如何解决？

实验三：使用SQL Server管理数据表

一、实验目的
1. 熟悉数据表的特点；
2. 熟悉在Management Stuio中创建、编辑及删除数据表；
3. 熟悉使用T-SQL创建、编辑及删除数据表；
二、实验环境
已安装SQL Server 2023 公司版的计算机(13台)；
具有局域网环境,有固定IP;
三、实验学时
2学时
四、实验规定
1. 了解表的结构特点；
2. 了解SQL Server的基本数据类型；
3. 熟悉使用T-SQL语法；
4. 完毕实验报告。
五、实验内容
请分别使用Management Stuio及T-SQL完毕以下内容：
1、 请在指定数据库内完毕以下内容：
1） 依据数据表的结构创建相相应的数据表，表结构如下所示；
学生信息表（student）
字段名称
字段类型及长度
说明
备注
Sno
Char（9）
学生学号
主关键字

Sname
Char（6）
学生姓名
非空
Ssex
Char（2）
学生性别
可为空
Sage
Int
学生年龄
可为空
Sdept
VarChar（8）
学生所在院系
可为空
create table student(
Sno char(9) primary key,
Sname char(6) not null,
Ssex char(2),
Sage int,
Sdept varchar(8)
)

课程信息表（course）
字段名称
字段类型及长度
说明
备注
Cno
Char（4）
课程编号
主关键字
Cname

VarChar（20）
课程名称
非空
Cpno
Char（4）
先行课
可为空
Ccredit
Int
学分
可为空
create table course(
Cno char(4) primary key,
Cname varchar(20) not null,
Cpno char(4),
Ccredit int
)
选课信息表（sc）
字段名称
字段类型及长度
说明
备注
Sno
Char（9）
学生学号
主关键字
Cno
Char（4）
课程编号
主关键字
Grade
Int
成绩
可为空

create table sc(
Sno char(9),
Cno char(4),
Grade int,
primary key(Sno,Cno),
foreign key(Sno) references student(Sno),
foreign key(Cno) references course(Cno)
)
2） 在表student中增长新字段 “班级名称（sclass）“；
alter table student add sclass char(10)
3） 在表student中删除字段“班级名称（sclass）”；
alter table student drop column sclass
4） 修改表student中字段名为“sname”的字段长度由本来的6改为8；
alter table student alter column sname char(8)
5） 修改表student中字段“sdept”名称为“dept”，长度为20；

6） 修改表student中sage字段名称为sbirth，类型为smalldatetime；
7） 修改表student新名称为stu_info；
8） 删除数据表student；
drop table student
2、创建教师授课管理数据库JSSK，并完毕以下内容；
1） 在数据库JSSK中创建下列三张表；
表名：teachers
列名
数据类型
说明
描述
Tno
字符型，长度7
主键
教师号
Tname
字符型，长度10
非空
姓名
Tsex
字符型，长度2
默认取值为“男”
性别
Birthday
小日期时间型
允许空
出生日期
Dept
字符型，长度20
允许空
所在部门
Sid
字符型，长度18

身份证号
表名： lessons
列名
数据类型
说明
描述
Cno
字符型，长度10
主键
课程号
Cname
字符型，长度20
非空
课程名
Credit
短整型

学分
property
字符型，长度为10

课程性质
表名： shouke
列名
数据类型
说明
描述
Tno
字符型，长度7
主键
教师号
Cno
字符型，长度10
主键
课程名
Hours
整数

课时
use JSSK;
create table teacher(
Tno char(7) primary key,
Tname char(10) not null,
Tsex char(2) default'男',
Birthday date,
Dept varchar(20),
Tsid varchar(18)not null
)

create table lessons(
Cno char(10) primary key,
Cname char(20) not null,
Credit smallint,
property char(10)
)

create table shouke(
Tno char(7),
Cno char(10),
Hours int ,
primary key(Tno,Cno),
foreign key (Tno) references teacher(Tno),
foreign key (Cno) references lessons(Cno)
)

2） 在shouke表里添加一个授课类别字段，列名为Type，类型为Char，长度为4；
alter table shouke add type char(4)
3） 将shouke表的Hours的类型改为smallint；
alter table shouke alter column hours smallint
4） 删除lessons表中的property列；
alter table shouke drop column property
3、(选做)创建产品销售数据库CPXS，数据文献的逻辑文献名为cpxs_data，物理文献名为D:\sql\cpxs.mdf；文献初始大小为2MB，自动增长，每次增长1MB；日记文献逻辑文献名为cpxs_log，物理文献为D:\sql\cpxs.ldf；文献初始大小2MB，自动增长，每次增长18%；
1） 在数据库CPXS中创建下列三张表；
表名：产品表（cp）表结构
列名
数据类型
说明
描述
Cpbh
字符型，长度6
主键
产品编号
Cpmc
字符型，长度30
非空
产品名称
Jg
浮点型，长度8
允许空
价格
Kcl
整型，长度4
允许空
库存量
表名：销售商（xss）表结构
列名
数据类型
说明
描述
Xsbh
字符型，长度6
主键
销售商编号
Xsmc
字符型，长度30
非空
销售商名称
Dq
字符型，长度10
允许空
地区
Fzr
字符型，长度8
允许空
负责人
Dh
字符型，长度12
允许空
电话
Bz
文本，长度16
允许空
备注
表名： 产品销售（xss）表结构
列名
数据类型
说明
描述
Cpbh
字符型，长度6
主键
产品编号
Xsbh
字符型，长度6
主键
销售商编号
Xssj
Datetime，长度8
非空
销售时间
Sl
整型，长度4
非空
数量
Je
浮点型，长度8
非空
金额

use CPXS
create table
六、出现问题及解决方法
如数据表无法创建或删除等问题；

实验四：管理SQL Server表数据
一、实验目的
1. 熟悉数据表结构及使用特点；
2. 熟悉使用Management Stuio界面方式管理数据表数据；
3. 熟悉使用T-SQL语句管理数据表数据。
二、实验环境
已安装SQL Server 2023 公司版的计算机(13台)；
具有局域网环境,有固定IP;
三、实验学时
2学时
四、实验规定
1. 了解SQL Server数据表数据的管理方法；
2. 了解SQL Server数据类型；
3. 完毕实验报告（部分题只需给出关键语句）。
五、实验内容及环节
以课本指定的数据库为例，并依据数据表的结构创建相相应的数据表（student、course、sc），请分别使用Management Stuio界面方式及T-SQL 语句实现进行以下操作：
1. 向各个数据表中插入如下记录：
学生信息表（student）
Sno
Sname
Ssex
Sage
Sdept

赵菁菁
女
23
CS

李勇
男
20
CS

张力
男
19
CS

张衡
男
18
IS

张向东
男
20
IS

张向丽
女
20
IS

王芳
女
20
CS

王民生
男
28
MA

王小民
女
18
MA

李晨
女
22
MA

张毅
男
20
WM

杨磊
女
20
EN

李晨
女
19
MA

张丰毅
男
22
CS

李蕾
女
21
EN

刘社
男
21
CM

刘星耀
男
18
CM

李贵
男
19
EN

林自许
男
20
WM

马翔
男
21

刘峰
男
28
CS

牛站强
男
22

李婷婷
女
18

严丽
女
20

朱小鸥
女
30
WM
课程信息表（course）
Cno
Cname
Cpno
Ccredit
1
数据库
8
4
2
数学

2
3
信息系统
1
4
4
操作系统
6
3
8
数据结构
7
4
6
数据解决

2
7
PASCAL语言
6
4
8
大学英语

4
9
计算机网络

4
10
人工智能

2
选课信息表（sc）
Sno
Cno
Grade

1
78

1
88

3
83

1
86

1
74

1
88

1
84

2
46

2
89

2
68

2
72

2
76

2
96

8
86

8
62

8
0

8
88

4
62

4
88

9
84

8
88

6
88

7
70

10
68

8
Null

8
Null
2. 修改CS系姓名为“李勇”的学生姓名为“李咏”；
update student set sname='李咏'
where sname='李勇'
3. 修改课程“数据解决”的学分为3学分；
update course set ccredit=3
where cname='数据解决'
4. 将选修课程“1”的同学成绩加8分；
update sc set grade=grade+5
where cno='1'
5. 将选修课程“大学英语”的同学成绩加8分；
update sc set grade=grade+5
where cno=(select cno from course where cname='大学英语')
6. 将学号为“”的学生信息重新设立为“王丹丹、女、20、MA”；
update student set sname='王丹丹',ssex='女',sage20,sdept='MA'
where sno=''
7. 删除数据表student中无专业的学生记录；
delete from student
where sdept is null
8. 删除数据表student中计算机系年龄大于28的男同学的记录；
delete from student
where sage>25 and ssex='男' and sdept='MA'
9. 删除数据表course中学分低于1学分的课程信息；
delete from course
where ccredit<1;
六、出现问题及解决办法
如：插入数据记录除了Insert 尚有什么方法？如何导出数据表中的数据，或者某些操作无法执行，如何解决？

实验五：数据库单表查询

一、实验目的
1. 掌握SELECT语句的基本语法和查询条件表达方法；
2. 掌握查询条件表达式和使用方法；
3. 掌握GROUP BY 子句的作用和使用方法；
4. 掌握HAVING子句的作用和使用方法；
5. 掌握ORDER BY子句的作用和使用方法。
二、实验环境
已安装SQL Server 2023 公司版的计算机(13台)；
具有局域网环境,有固定IP;
三、实验学时
2学时
四、实验规定
1. 了解数据库查询；
2. 了解数据库查询的实现方式；
3. 完毕实验报告；
五、实验内容及环节
以数据库原理实验4数据库中数据为基础，请使用T-SQL 语句实现以下操作：
1. 列出所有不姓刘的所有学生；
select * from student where Sname not like '刘%'
2. 列出姓“沈”且全名为3个汉字的学生；
select * from student where sname like '沈__'
3. 显示在1988年以后出生的学生的基本信息；
select * from student where Sage <2023-1985
4. 按照“性别、学号、姓名、年龄、院系”的顺序列出学生信息，其中性别按以下规定显示：性别为男显示为男 生，性别为女显示为女 生，其他显示为“条件不明”；
select '性别'= case when ssex='男' then '男生'
when Ssex='女' then '女生' end,
Sname as'姓名',
Sage as '年龄',sdept as '院系'
from student
5. 查询出课程名具有“数据”字串的所有课程基本信息；
select * from course where cname like '%数据%'
6. 显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及院系；
select sno 学号,sname 姓名,ssex 性别,sage 年龄,Sdept 院系
from student
where Sno like '_______[12349]%'or Sno like '________[12349]%'
7. 列出同时选修“1”号课程和“2”号课程的所有学生的学号；
select x.sno from sc x,sc y where o='1'and o='2'
8. 列出课程表中所有信息，按先修课的升序排列；
select * from course order by cpno
9. 列出年龄超过平均值的所有学生名单，按年龄的降序显示；
select *
from student
where sage> (select avg(sage) from student)
order by sage desc
10. 按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在结果集中列标题分别指定为“学号，姓名，性别，出生年份，院系”；
sno 学号, sname 姓名,ssex 性别,sage 年龄
from student
order by sdept desc
11. 按照院系降序显示所有学生的 “院系，学号、姓名、性别、年龄”等信息，其中院系按照以下规定显示：院系为CS显示为计算机系，院系为IS显示为信息系，院系为MA显示为数学系，院系为EN显示为外语系，院系为CM显示为中医系，院系为WM显示为西医系，其他显示为院系不明；
select 院系= case when sdept='CS' then '计算机系'
when sdept='IS' then '信息系'
when sdept='MA' then '数学系'
when sdept='EN' then '外语系'
when sdept='CM' then '中医系'
when sdept='WM' then '西医系'
else '院系不明'
end,
sno 学号, sname 姓名,ssex 性别,sage 年龄
from student
order by sdept desc
12. 显示所有院系（规定不能反复，不涉及空值），并在结果集中增长一列字段“院系规模”，其中若该院系人数>=8则该字段值为“规模很大”，若该院系人数大于等于4小于8则该字段值为“规模一般”， 若该院系人数大于等于2小于4则该字段值为“规模稍小”，否则显示“规模很小”；
select distinct sdept 所有院系,
院系规模=case when COUNT(sdept)>=5 then '规模很大'
when 4<=COUNT(sdept)and COUNT(sdept)<5 then '规模一般'
when 2<=COUNT(sdept)and COUNT(sdept)<4 then '规模稍小'
else '规模很小'
end
from student
where sdept is not null
group by sdept
13. 按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩；
select * from sc
where grade>=70and grade<=80
order by cno,grade desc
14. 显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”；
select COUNT(sno)as '学生总人数',AVG(sage)as '平均年龄'
from student
15. 显示选修的课程数大于3的各个学生的选修课程数；
select COUNT(cno)as '选修课程数'
from sc
group by sno
having COUNT(cno)>3
16. 按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；
select COUNT(sno)as'总人数',MAX(grade)'最高成绩',
MIN(grade)as'最低成绩',AVG(grade)as '平均成绩'
from sc
group by cno
order by cno desc
17. 显示平均成绩大于“”学生平均成绩的各个学生的学号、平均成绩；
select sno as '学号',AVG(grade)as '平均成绩' from sc group by sno having AVG(grade)>(
select AVG(grade) from sc,student where sc.sno=student.sno and sc.sno='')
18. 显示选修各个课程的及格的人数、及格比率；
select count(sno)as'及格人数',count(sno)/COUNT(*)as '及格比率'
from sc
where grade>=60
group by cno
19. 显示选修课程数最多的学号及选修课程数最少的学号；
select sno,COUNT(cno)
from sc
group by sno
having count(cno)in(select COUNT(cno) from sc group by sno)

六、出现问题及解决办法
如：某些查询操作无法执行，如何

实验六：数据库综合查询

一、实验目的
1. 掌握SELECT语句的基本语法和查询条件表达方法；
2. 掌握查询条件种类和表达方法；
3. 掌握连接查询的表达及使用；
4. 掌握嵌套查询的表达及使用；
5. 了解集合查询的表达及使用。
二、实验环境
已安装SQL Server 2023 公司版的计算机(13台)；
具有局域网环境,有固定IP;
三、实验学时
2学时
四、实验规定
1. 了解SELECT语句的基本语法格式和执行方法；
2. 了解连接查询的表达及使用；
3. 了解嵌套查询的表达及使用；
4. 了解集合查询的表达及使用；
5. 完毕实验报告；
五、实验内容及环节
以数据库原理实验8数据为基础，请使用T-SQL 语句实现进行以下操作：
1. 查询以‘DB_’开头,且倒数第3个字符为‘s’的课程的具体情况；
select * from course where cname like 'DB_%s__'
2. 查询名字中第2个字为‘阳’的学生姓名和学号及选修的课程号、课程名；
select o,cname from sc, course c,student s
where s.sno=sc.sno and o=o and sname like '_阳%'
3. 列出选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩；
select s.sno,sname,sdept,o,grade from sccourse c,student s
where s.sno=sc.sno and o=o and cname in(‘数学’,‘大学英语’)
4. 查询缺少成绩的所有学生的具体情况；
select * from student
where sno in(select sno from sc where grade is null)
5. 查询与‘张力’(假设姓名唯一)年龄不同的所有学生的信息；
select * from student
where sage <> (select sage from student where sname=‘张力’)
6. 查询所选课程的平均成绩大于张力的平均成绩的学生学号、姓名及平均成绩；
select s.sno,sname avg(grade) from student s,sc
where s.sno=sc.sno
group by sc.sno
having avg(grade)>(select avg(grade) from student s,sc
where s.sno=sc.sno
group by sc.sno)
7. 按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和；
select s.sno,sname,sdept,count(Ccredit) from student s,course c,sc
where s.sno=sc.sno and o=o and grade>=60
group by sc.sno
8. 列出只选修一门课程的学生的学号、姓名、院系及成绩；
select s.sno,sname,sdept,grade from student s,sc
where s.sno=sc.sno and s.sno in(select sno from sc
group by sno
having count(cno)=1)
9. 查找选修了至少一门和张力选修课程同样的学生的学号、姓名及课程号；
select s.sno,sname,cno from student s,sc
where s.sno=sc.sno and cno in(select cno from student s,sc
s.sno=sc.sno and sname='张力')
10. 只选修“数据库”和“数据结构”两门课程的学生的基本信息；
select * from student
where sno =(select sno from sc,course c1,course c2
where o=o and o=o and
ame='数据库' and ame='数据结构')
11. 至少选修“数据库”或“数据结构”课程的学生的基本信息；
select * from student
where sno =(select sno from sc,course c1
where o=o and o=o and
ame='数据库' or ame='数据结构')
12. 列出所有课程被选修的具体情况，涉及课程号、课程名、学号、姓名及成绩；
select