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

实验报告学 科： 计算机科学与技术 课程名称： 数据库原理 姓 名： 王 辉 学 号： 信息技术学院实验二：管理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 FilesMicrosoft SQL ServerMSSQL.2MSSQLDATA目录下，文献分别为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_nameadd 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 studenton 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 companyon 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 DBon primary(name = DB_data1,filename = d:ceishiDB_data1.mdf,size = 5,maxsize = 100,filegrowth =10%),(name = DB_data2,filename = d:ceishiDB_data2.ndf,size = 5,maxsize = 100,filegrowth =10%)log on(name = company_log,filename = d:ceishiDB_log.ldf,size = 3,filegrowth =10%)4) 在数据库student中增长数据文献db2，初始大小为10MB，最大大小为80 MB，按10%增长；use studentalter database studentadd file(name = db2,filename = d:DB2.ndf,size = 5,maxsize = 50,filegrowth =10%)5) 在数据库student中添加日记文献，保存在D盘中，初始大小为1MB，最大无限制，增长方式按照1MB增长；use studentalter database studentadd log file(name = stulog2,filename = d:stulog2_log.ldf,size = 1,maxsize = unlimited,filegrowth =1)6) 修改数据库student主数据文献的大小，将主数据文献的初始大小修改为10Mb，增长方式为20%；use studentalter database studentmodify file(name = stu,size = 10,filegrowth =20%)7) 修改数据库student辅助数据文献初始大小为3MB，最大为100MB，按照10%增长，名称为db;use studentalter database studentmodify file(name = db2,newname=db,size = 10,maxsize =100,filegrowth =10%)8) 删除数据库student辅助数据文献和第二个日记文献；use studentalter database studentremove file dbalter database studentremove file stulog29) 删除数据库company和DB。drop database companydrop database DB2、将数据库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）字段名称字段类型及长度说明备注SnoChar（9）学生学号主关键字SnameChar（6）学生姓名非空SsexChar（2）学生性别可为空SageInt学生年龄可为空SdeptVarChar（8）学生所在院系可为空create table student(Sno char(9) primary key,Sname char(6) not null,Ssex char(2),Sage int,Sdept varchar(8)课程信息表（course）字段名称字段类型及长度说明备注CnoChar（4）课程编号主关键字CnameVarChar（20）课程名称非空CpnoChar（4）先行课可为空CcreditInt学分可为空create table course(Cno char(4) primary key,Cname varchar(20) not null,Cpno char(4),Ccredit int )选课信息表（sc）字段名称字段类型及长度说明备注SnoChar（9）学生学号主关键字CnoChar（4）课程编号主关键字GradeInt成绩可为空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 sclass4） 修改表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 student2、创建教师授课管理数据库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 smallint4） 删除lessons表中的property列；alter table shouke drop column property 3、(选做)创建产品销售数据库CPXS，数据文献的逻辑文献名为cpxs_data，物理文献名为D:sqlcpxs.mdf；文献初始大小为2MB，自动增长，每次增长1MB；日记文献逻辑文献名为cpxs_log，物理文献为D:sqlcpxs.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主键销售商编号XssjDatetime，长度8非空销售时间Sl整型，长度4非空数量Je浮点型，长度8非空金额use CPXScreate 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）SnoSnameSsexSageSdept赵菁菁女23CS李勇男20CS张力男19CS张衡男18IS张向东男20IS张向丽女20IS王芳女20CS王民生男28MA王小民女18MA李晨女22MA张毅男20WM杨磊女20EN李晨女19MA张丰毅男22CS李蕾女21EN刘社男21CM刘星耀男18CM李贵男19EN林自许男20WM马翔男21刘峰男28CS牛站强男22李婷婷女18严丽女20朱小鸥女30WM课程信息表（course）CnoCnameCpnoCcredit1数据库842数学23信息系统144操作系统638数据结构746数据解决27PASCAL语言648大学英语49计算机网络410人工智能2选课信息表（sc）SnoCnoGrade1781883831861741881842462892682722762968868628088846248898488868877010688Null8Null2. 修改CS系姓名为“李勇”的学生姓名为“李咏”；update student set sname=李咏where sname=李勇3. 修改课程“数据解决”的学分为3学分；update course set ccredit=3where cname=数据解决4. 将选修课程“1”的同学成绩加8分；update sc set grade=grade+5where cno=15. 将选修课程“大学英语”的同学成绩加8分；update sc set grade=grade+5where cno=(select cno from course where cname=大学英语)6. 将学号为“”的学生信息重新设立为“王丹丹、女、20、MA”；update student set sname=王丹丹,ssex=女,sage=20,sdept=MAwhere sno=7. 删除数据表student中无专业的学生记录；delete from studentwhere sdept is null8. 删除数据表student中计算机系年龄大于28的男同学的记录；delete from studentwhere sage25 and ssex=男 and sdept=MA9. 删除数据表course中学分低于1学分的课程信息；delete from course where ccredit1;六、出现问题及解决办法如：插入数据记录除了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 (select avg(sage) from student)order by sage desc10. 按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在结果集中列标题分别指定为“学号，姓名，性别，出生年份，院系”；sno 学号, sname 姓名,ssex 性别,sage 年龄from studentorder by sdept desc11. 按照院系降序显示所有学生的 “院系，学号、姓名、性别、年龄”等信息，其中院系按照以下规定显示：院系为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 studentorder by sdept desc12. 显示所有院系（规定不能反复，不涉及空值），并在结果集中增长一列字段“院系规模”，其中若该院系人数=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)=70and grade316. 按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；select COUNT(sno)as总人数,MAX(grade)最高成绩,MIN(grade)as最低成绩,AVG(grade)as 平均成绩from scgroup by cnoorder by cno desc17. 显示平均成绩大于“”学生平均成绩的各个学生的学号、平均成绩；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 scwhere grade=60group by cno 19. 显示选修课程数最多的学号及选修课程数最少的学号；select sno,COUNT(cno)from sc group by snohaving 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 swhere s.sno=sc.sno and o=o and sname like _阳%3. 列出选修了数学或者大学英语的学生学号、姓名、所在院系、选修课程号及成绩；select s.sno,sname,sdept,o,grade from sc,course c,student swhere 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,scwhere s.sno=sc.sno group by sc.snohaving avg(grade)(select avg(grade) from student s,scwhere s.sno=sc.snogroup by sc.sno)7. 按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和；select s.sno,sname,sdept,count(Ccredit) from student s,course c,scwhere s.sno=sc.sno and o=o and grade=60group by sc.sno8. 列出只选修一门课程的学生的学号、姓名、院系及成绩；select s.sno,sname,sdept,grade from student s,scwhere s.sno=sc.sno and s.sno in(select sno from sc group by snohaving 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,scs.sno=sc.sno and sname=张力)10. 只选修“数据库”和“数据结构”两门课程的学生的基本信息；select * from studentwhere sno =(select sno from sc,course c1,course c2where o=o and o=o and ame=数据库 and ame=数据结构)11. 至少选修“数据库”或“数据结构”课程的学生的基本信息；select * from studentwhere sno =(select sno from sc,course c1where o=o and o=o and ame=数据库 or ame=数据结构)12. 列出所有课程被选修的具体情况，涉及课程号、课程名、学号、姓名及成绩；select o,cname,s.sno,sname,grade from student s,course c,scwhere s.sno=sc.sno and o=o and cno in(select cno from sc)13. 查询只被一名学生选修的课程的课程号、课程名；select sno,cno from sc group by snohaving count(cno)=114. 检索所学课程包含学生张向东所学课程的学生学号、姓名；select sno,cno from sc where cno in(select cno from sc,student s where s.sno=sc.sno and sname=张向东)15. 使用嵌套查询列出选修了“数据结构”课程的学生学号和姓名；select * from student where sno in(select sno from course c,scwhere o=o and cname=数据结构)16. 使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系；select Sname,Sage,sdeptfrom Studentwhere Sage (select MAX(Sage)from Studentwhere Sdept= CS)AND Sdept CS17. 使用ANY、ALL 查询，列出其他院系中比CS系所有学生年龄小的学生；select Sname,Sage,sdeptfrom Studentwhere Sage (select MAX(Sage)from Studentwhere Sdept= CS)AND Sdept CSselect Sname,Sage,sdept from Studentwhere Sage ANY (select Sagefrom Studentwhere Sdept= CS)and Sdept CS18. 分别使用连接查询和嵌套查询，列出与张力在一个院系的学生的信息；select *from Studentwhere Sdept (select Sdept from Studentwhere sname= 张力)19. 使用集合查询列出CS系的学生以及性别为女的学生名单；select * from studentwhere sdept=CSUNIONselect * from studentwhere ssex=女20. 使用集合查询列出CS系的学生与年龄不大于19岁的学生的交集、差集；select * from studentwhere sdept=CSINTERSECTselect * from studentwhere sage=19select * from studentwhere sdept=CSEXCEPTselect * from studentwhere sage=1921. 使用集合查询列出选修课程1的学生集合与选修课程2的学生集合的交集； select s.sno,sname,sage,ssex from student s,scwhere s.sno=sc.sno and cno=1INTERSECTselect s.sno,sname,sage,ssex from student s,scwhere s.sno=sc.sno and cno=2六、出现问题及解决办法如：某些查询操作无法执行，如何解决？