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

计科专升本_数据库作业资料仅供参考实验报告学 科： 计算机科学与技术 课程名称： 数据库原理 姓 名： 王 辉 学 号： 187033 信息技术学院实验二：管理SQL Server 数据库一、实验目的1. 了解数据库常见对象及组成；2. 熟悉SQL的基本概念和特点；3. 熟练掌握T-SQL 语句中的数据定义的使用方法；4. 熟悉在SQL Server 中创立及编辑数据库；5. 熟练掌握SQL的数据定义子句的使用方法。二、实验环境已安装SQL Server 企业版的计算机（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 查询分析器【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 企业版的计算机(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 企业版的计算机(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. 将学号为“ 18010”的学生信息重新设置为“王丹丹、女、20、MA”；update student set sname=王丹丹,ssex=女,sage=20,sdept=MAwhere sno= 150107. 删除数据表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 企业版的计算机(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. 显示平均成绩大于“ 18001”学生平均成绩的各个学生的学号、平均成绩；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= 15001)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 企业版的计算机(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. 列出所有课程被选修的详细情况，包括